《MYSQL數(shù)據(jù)庫MySQL定期分析檢查與優(yōu)化表的方法小結(jié)》要點(diǎn)：
本文介紹了MYSQL數(shù)據(jù)庫MySQL定期分析檢查與優(yōu)化表的方法小結(jié)，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

定期分析表MYSQL入門

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]MYSQL入門

本語句用于分析和存儲(chǔ)表的關(guān)鍵字分布.在分析期間,使用一個(gè)讀取鎖定對(duì)表進(jìn)行鎖定.這對(duì)于MyISAM, BDB和InnoDB表有作用.對(duì)于MyISAM表,本語句與使用myisamchk -a相當(dāng).MYSQL入門

MySQL使用已存儲(chǔ)的關(guān)鍵字分布來決定,當(dāng)您對(duì)除常數(shù)以外的對(duì)象執(zhí)行聯(lián)合時(shí),表按什么順序進(jìn)行聯(lián)合.MYSQL入門

mysql> analyze table a;
+--------+---------+----------+-----------------------------+
| Table? | Op????? | Msg_type | Msg_text??????????????????? |
+--------+---------+----------+-----------------------------+
| test.a | analyze | status?? | Table is already up to date |
+--------+---------+----------+-----------------------------+
1 row in set (0.00 sec)MYSQL入門

定期檢查表MYSQL入門

CHECK TABLE tbl_name [, tbl_name]? [option]MYSQL入門

option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
檢查一個(gè)或多個(gè)表是否有錯(cuò)誤.CHECK TABLE對(duì)MyISAM和InnoDB表有作用.對(duì)于MyISAM表,關(guān)鍵字統(tǒng)計(jì)數(shù)據(jù)被更新.MYSQL入門

mysql> check table a;
+--------+-------+----------+----------+
| Table? | Op??? | Msg_type | Msg_text |
+--------+-------+----------+----------+
| test.a | check | status?? | OK?????? |
+--------+-------+----------+----------+
1 row in set (0.00 sec)
CHECK TABLE也可以檢查視圖是否有錯(cuò)誤,比如在視圖定義中被引用的表已不存在.
我們?yōu)樯厦娴谋韆創(chuàng)建一個(gè)視圖MYSQL入門

mysql> create view a_view as select * from a;
Query OK, 0 rows affected (0.02 sec)MYSQL入門

然后CHECK一下該視圖,發(fā)現(xiàn)沒有問題MYSQL入門

mysql> check table a_view;
+-------------+-------+----------+----------+
| Table?????? | Op??? | Msg_type | Msg_text |
+-------------+-------+----------+----------+
| test.a_view | check | status?? | OK?????? |
+-------------+-------+----------+----------+
1 row in set (0.00 sec)
現(xiàn)在刪掉視圖依賴的表MYSQL入門

mysql> drop table a;
Query OK, 0 rows affected (0.01 sec)
再CHECK一下剛才的視圖,發(fā)現(xiàn)報(bào)錯(cuò)了MYSQL入門

mysql> check table a_view\G;
*************************** 1. row ***************************
?? Table: test.a_view
????? Op: check
Msg_type: Error
Msg_text: Table 'test.a' doesn't exist
*************************** 2. row ***************************
?? Table: test.a_view
????? Op: check
Msg_type: Error
Msg_text: View 'test.a_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
*************************** 3. row ***************************
?? Table: test.a_view
????? Op: check
Msg_type: error
Msg_text: Corrupt
3 rows in set (0.00 sec)MYSQL入門

ERROR:
No query specified
定期優(yōu)化表MYSQL入門

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
如果您已經(jīng)刪除了表的一大部分,或者如果您已經(jīng)對(duì)含有可變長度行的表(含有VARCHAR, BLOB或TEXT列的表)進(jìn)行了很多更改,則應(yīng)使用OPTIMIZE TABLE.被刪除的記錄被保持在鏈接清單中,后續(xù)的INSERT操作會(huì)重新使用舊的記錄位置.您可以使用OPTIMIZE TABLE來重新利用未使用的空間,并整理數(shù)據(jù)文件的碎片.
在多數(shù)的設(shè)置中,您根本不需要運(yùn)行OPTIMIZE TABLE.即使您對(duì)可變長度的行進(jìn)行了大量的更新,您也不需要經(jīng)常運(yùn)行,每周一次或每月一次即可,只對(duì)特定的表運(yùn)行.
OPTIMIZE TABLE只對(duì)MyISAM, BDB和InnoDB表起作用.
對(duì)于MyISAM表,OPTIMIZE TABLE按如下方式操作：
如果表已經(jīng)刪除或分解了行,則修復(fù)表.
如果未對(duì)索引頁進(jìn)行分類,則進(jìn)行分類.
如果表的統(tǒng)計(jì)數(shù)據(jù)沒有更新(并且通過對(duì)索引進(jìn)行分類不能實(shí)現(xiàn)修復(fù)),則進(jìn)行更新.MYSQL入門

mysql> OPTIMIZE table a;
+--------+----------+----------+-----------------------------+
| Table? | Op?????? | Msg_type | Msg_text??????????????????? |
+--------+----------+----------+-----------------------------+
| test.a | optimize | status?? | Table is already up to date |
+--------+----------+----------+-----------------------------+
1 row in set (0.00 sec)MYSQL入門

****
以上某些的段落是直接摘自MySQL的中文手冊(cè),詳細(xì)可以直接查看MySQL的幫助手冊(cè),這里只是簡單指出幾種定期優(yōu)化的方式,需要注意的是無論是ANALYZE,CHECK還是OPTIMIZE在執(zhí)行期間將對(duì)表進(jìn)行鎖定,因此請(qǐng)注意這些操作要在數(shù)據(jù)庫不繁忙的時(shí)候執(zhí)行MYSQL入門

****
參考
《MySQL 5.1參考手冊(cè)》MYSQL入門

by 陳于MYSQL入門

show table status
mysql官方文檔在MYSQL入門

http://dev.mysql.com/doc/refman/5.1/en/show-table-status.htmlMYSQL入門

這里的rows行是表的行數(shù),但是實(shí)際上是不準(zhǔn)的.myisam是準(zhǔn)的,其他的存儲(chǔ)引擎是不準(zhǔn)的.要準(zhǔn)確的行數(shù)就需要使用count(*) 來獲取了.MYSQL入門

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

因?yàn)槿绻挥胠imit,刪除大量數(shù)據(jù)很有可能造成死鎖MYSQL入門

如果delete的where語句不在索引上,可以先找主鍵,然后根據(jù)主鍵刪除數(shù)據(jù)庫MYSQL入門

ps: 平時(shí)update和delete的時(shí)候最好也加上limit 1 來防止誤操作MYSQL入門

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

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

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

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

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

Analyze table 表名MYSQL入門

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

Repair optimize需要有足夠的硬盤空間,否則可能會(huì)破壞表,導(dǎo)致不能操作,那就要用上repair,注意INNODB不支持repair操作
生成亂序的id
方法：MYSQL入門

使用預(yù)設(shè)表MYSQL入門

比如id和toid的映射MYSQL入門

其中id是固定的,toid是隨機(jī)的.MYSQL入門

然后在redis或memcache中記錄一個(gè)指針值,指向idMYSQL入門

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

查詢和索引
查詢的時(shí)候必須要考慮到如何命中索引MYSQL入門

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

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

where mycol *2 < 4MYSQL入門

2 不要在like模式的開始位置使用通配符%MYSQL入門

where col_name like ‘%string%'MYSQL入門

不如MYSQL入門

where col_name like ‘string%'MYSQL入門

3 避免過多使用mysql自動(dòng)轉(zhuǎn)換類型,有可能無法用到indexMYSQL入門

比如MYSQL入門

select * from mytbl where str_col=4MYSQL入門

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

應(yīng)該使用MYSQL入門

select * from mytbl where str_col='4'MYSQL入門

索引比表還大就不需要建立索引了嗎
否MYSQL入門

索引是按照順序排列的.所以即使索引比表大,也是可以加快查詢速度的.MYSQL入門

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

Char和varchar如何選擇
char是定長,varchar變長
varchar除了設(shè)置了數(shù)據(jù)之外,還多使用1兩個(gè)字節(jié)定義了數(shù)據(jù)實(shí)際長度.MYSQL入門

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

myisam建議使用char.myisam中有個(gè)靜態(tài)表的概念.使用char比使用varchar的查詢效率高很多.MYSQL入門

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

多個(gè)TimeStamp設(shè)置默認(rèn)值
一個(gè)表中至多只能有一個(gè)字段設(shè)置CURRENT_TIMESTAMPMYSQL入門

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

一個(gè)表中,有兩個(gè)字段,createtime和updatetime.MYSQL入門

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í)間MYSQL入門

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

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

1 使用觸發(fā)器.MYSQL入門

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

3 老老實(shí)實(shí)在sql語句中使用時(shí)間戳.MYSQL入門

/article/31872.htmMYSQL入門

查詢數(shù)據(jù)表有多少行,多少容量
不要使用select count(*)MYSQL入門

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

AUTO_INCREMENT的設(shè)置MYSQL入門

1 不要設(shè)置為int,請(qǐng)?jiān)O(shè)置為unsinged int,auto_increment的范圍是根據(jù)類型來判定的
2 auto_increment數(shù)據(jù)列必須要有索引,并且保證唯一性.
3 auto_increment必須有NOT NULL屬性
4 auto_increment可以使用MYSQL入門

UPDATE table SET seq = LAST_INSERT_ID(seq -1)MYSQL入門

mysql的表示時(shí)間的字段用什么類型
表示時(shí)間可以使用timestamp和datetime來使用MYSQL入門

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

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

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

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

[ON UPDATE CURRENT_TIMESTAMP]MYSQL入門

[DEFAULT CURRENT_TIMESTAMP]MYSQL入門

myisam和innodb支持外鍵
myisam不支持外鍵,innodb支持；MYSQL入門

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

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

update table set a = a+1MYSQL入門

這樣是對(duì)的MYSQL入門

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

select a from tableMYSQL入門

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

update table set a =2MYSQL入門

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

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/1664.html