mysql那些招：執(zhí)行大批量刪除、查詢(xún)和索引等操作

Access to MySQL (ACCESS轉(zhuǎn)Mysql)v4.3 英文綠色版

• 類(lèi)型：數(shù)據(jù)庫(kù)類(lèi)大�。�1.7M語(yǔ)言：英文 評(píng)分：6.6
• 標(biāo)簽：

立即下載

mysql執(zhí)行大批量刪除

執(zhí)行大批量刪除的時(shí)候注意要使用上limit

因?yàn)槿绻�不用limit，刪除大量數(shù)據(jù)很有可能造成死鎖

如果delete的where語(yǔ)句不在索引上，可以先找主鍵，然后根據(jù)主鍵刪除數(shù)據(jù)庫(kù)

PS: 平時(shí)update和delete的時(shí)候最好也加上limit 1 來(lái)防止誤操作

optimize、Analyze、check、repair維護(hù)操作

optimize 數(shù)據(jù)在插入，更新，刪除的時(shí)候難免一些數(shù)據(jù)遷移，分頁(yè)，之后就出現(xiàn)一些碎片，久而久之碎片積累起來(lái)影響性能，這就需要DBA定期的優(yōu)化數(shù)據(jù)庫(kù)減少碎片，這就通過(guò)optimize命令。

如對(duì)MyisAM表操作：optimize table 表名

對(duì)于InnoDB表是不支持optimize操作，否則提示“Table does not support optimize, doing recreate + analyze instead”，當(dāng)然也可以通過(guò)命令：alter table one type=innodb; 來(lái)替代。

Analyze 用來(lái)分析和存儲(chǔ)表的關(guān)鍵字的分布，使得系統(tǒng)獲得準(zhǔn)確的統(tǒng)計(jì)信息，影響 SQL 的執(zhí)行計(jì)劃的生成。對(duì)于數(shù)據(jù)基本沒(méi)有發(fā)生變化的表，是不需要經(jīng)常進(jìn)行表分析的。但是如果表的數(shù)據(jù)量變化很明顯，用戶(hù)感覺(jué)實(shí)際的執(zhí)行計(jì)劃和預(yù)期的執(zhí)行計(jì)劃不 同的時(shí)候，執(zhí)行一次表分析可能有助于產(chǎn)生預(yù)期的執(zhí)行計(jì)劃。

Analyze table 表名

Check檢查表或者視圖是否存在錯(cuò)誤，對(duì) MyISAM 和 InnoDB 存儲(chǔ)引擎的表有作用。對(duì)于 MyISAM 存儲(chǔ)引擎的表進(jìn)行表檢查，也會(huì)同時(shí)更新關(guān)鍵字統(tǒng)計(jì)數(shù)據(jù)

Repair optimize需要有足夠的硬盤(pán)空間，否則可能會(huì)破壞表，導(dǎo)致不能操作，那就要用上repair，注意INNODB不支持repair操作

生成亂序的id

方法：

使用預(yù)設(shè)表

比如id和toid的映射

其中id是固定的，toid是隨機(jī)的。

然后在redis或memcache中記錄一個(gè)指針值，指向id

當(dāng)要獲取一個(gè)新toid的時(shí)候，取出指針值，加1，然后去預(yù)設(shè)表中獲取toid

查詢(xún)和索引

查詢(xún)的時(shí)候必須要考慮到如何命中索引

比如有幾個(gè)小招：

1 不要在表達(dá)式中使用索引列

where mycol *2 < 4

2 不要在like模式的開(kāi)始位置使用通配符%

where col_name like ‘%string%’

不如

where col_name like ‘string%’

3 避免過(guò)多使用mysql自動(dòng)轉(zhuǎn)換類(lèi)型，有可能無(wú)法用到index

比如

select * from mytbl where str_col=4

但是str_col為字符串，這里其實(shí)就隱含了字符串變化

應(yīng)該使用

select * from mytbl where str_col=’4’

索引比表還大就不需要建立索引了嗎

否

索引是按照順序排列的。所以即使索引比表大，也是可以加快查詢(xún)速度的。

當(dāng)然如果索引比表還大首要的任務(wù)必須是檢查下索引建立地是否有問(wèn)題

Char和varchar如何選擇

char是定長(zhǎng)，varchar變長(zhǎng) 
varchar除了設(shè)置了數(shù)據(jù)之外，還多使用1兩個(gè)字節(jié)定義了數(shù)據(jù)實(shí)際長(zhǎng)度。

char會(huì)在后面空余的行填充上空字符串

myisam建議使用char。myisam中有個(gè)靜態(tài)表的概念。使用char比使用varchar的查詢(xún)效率高很多。

innodb建議使用varchar。主要是從節(jié)省空間的方面考慮

多個(gè)TimeStamp設(shè)置默認(rèn)值

一個(gè)表中至多只能有一個(gè)字段設(shè)置CURRENT_TIMESTAMP

對(duì)于下面的需求：

一個(gè)表中，有兩個(gè)字段，createtime和updatetime。

1 當(dāng)insert的時(shí)候，sql兩個(gè)字段都不設(shè)置，會(huì)設(shè)置為當(dāng)前的時(shí)間

2 當(dāng)update的時(shí)候，sql中兩個(gè)字段都不設(shè)置，updatetime會(huì)變更為當(dāng)前的時(shí)間

這樣的需求是做不到的。因?yàn)槟銦o(wú)法避免在兩個(gè)字段上設(shè)置CURRENT_TIMESTAMP

解決辦法有幾個(gè)：

1 使用觸發(fā)器。

2 將第一個(gè)timestamp的default設(shè)置為0

3 老老實(shí)實(shí)在sql語(yǔ)句中使用時(shí)間戳。

查詢(xún)數(shù)據(jù)表有多少行，多少容量

不要使用select count(*)

使用show table status like ‘table_name’  但是innodb的話會(huì)有50%左右的浮動(dòng)，是個(gè)預(yù)估值

AUTO_INCREMENT的設(shè)置

1 不要設(shè)置為int，請(qǐng)?jiān)O(shè)置為unsinged int，auto_increment的范圍是根據(jù)類(lèi)型來(lái)判定的

2 auto_increment數(shù)據(jù)列必須要有索引，并且保證唯一性。

3 auto_increment必須有NOT NULL屬性

4 auto_increment可以使用

UPDATE table SET seq = LAST_INSERT_ID(seq -1)

mysql的表示時(shí)間的字段用什么類(lèi)型

表示時(shí)間可以使用timestamp和datetime來(lái)使用

datetime表示的時(shí)間可以從0000-00-00：00：00 到9999-12-31：00:00:00

timestamp表示的時(shí)間為1970-01-01 08：00：01到2038-01-19 11：14：07

timestamp占用的空間比datetime少，且可以設(shè)置時(shí)區(qū)等功能，所以能使用timestamp的地方盡量使用timestamp

使用timestamp還可以設(shè)置

[ON UPDATE CURRENT_TIMESTAMP]

[DEFAULT CURRENT_TIMESTAMP]

myisam和innodb支持外鍵

myisam不支持外鍵，innodb支持；

如果你使用創(chuàng)建外鍵的命令對(duì)myisam的表操作，操作不會(huì)返回失敗，但是是沒(méi)有外鍵關(guān)聯(lián)建立起來(lái)的。

對(duì)一個(gè)字段加減語(yǔ)句

經(jīng)常有需求對(duì)一個(gè)字段加減會(huì)使用

update table set a = a+1

這樣是對(duì)的

但是如果這樣設(shè)置：

select a from table

取出數(shù)據(jù)后a為1

update table set a =2

這樣會(huì)導(dǎo)致如果在select和update之間有其他事務(wù)操作修改這個(gè)字段的話，導(dǎo)致最后的設(shè)置可能出錯(cuò)。