《MYSQL數據庫MySQL定期分析檢查與優化表的方法小結》要點：
本文介紹了MYSQL數據庫MySQL定期分析檢查與優化表的方法小結，希望對您有用。如果有疑問，可以聯系我們。
定期分析表MYSQL入門
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]MYSQL入門
本語句用于分析和存儲表的關鍵字分布.在分析期間,使用一個讀取鎖定對表進行鎖定.這對于MyISAM, BDB和InnoDB表有作用.對于MyISAM表,本語句與使用myisamchk -a相當.MYSQL入門
MySQL使用已存儲的關鍵字分布來決定,當您對除常數以外的對象執行聯合時,表按什么順序進行聯合.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}
檢查一個或多個表是否有錯誤.CHECK TABLE對MyISAM和InnoDB表有作用.對于MyISAM表,關鍵字統計數據被更新.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也可以檢查視圖是否有錯誤,比如在視圖定義中被引用的表已不存在.
我們為上面的表a創建一個視圖MYSQL入門
mysql> create view a_view as select * from a;
Query OK, 0 rows affected (0.02 sec)MYSQL入門
然后CHECK一下該視圖,發現沒有問題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)
現在刪掉視圖依賴的表MYSQL入門
mysql> drop table a;
Query OK, 0 rows affected (0.01 sec)
再CHECK一下剛才的視圖,發現報錯了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
定期優化表MYSQL入門
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
如果您已經刪除了表的一大部分,或者如果您已經對含有可變長度行的表(含有VARCHAR, BLOB或TEXT列的表)進行了很多更改,則應使用OPTIMIZE TABLE.被刪除的記錄被保持在鏈接清單中,后續的INSERT操作會重新使用舊的記錄位置.您可以使用OPTIMIZE TABLE來重新利用未使用的空間,并整理數據文件的碎片.
在多數的設置中,您根本不需要運行OPTIMIZE TABLE.即使您對可變長度的行進行了大量的更新,您也不需要經常運行,每周一次或每月一次即可,只對特定的表運行.
OPTIMIZE TABLE只對MyISAM, BDB和InnoDB表起作用.
對于MyISAM表,OPTIMIZE TABLE按如下方式操作：
如果表已經刪除或分解了行,則修復表.
如果未對索引頁進行分類,則進行分類.
如果表的統計數據沒有更新(并且通過對索引進行分類不能實現修復),則進行更新.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的中文手冊,詳細可以直接查看MySQL的幫助手冊,這里只是簡單指出幾種定期優化的方式,需要注意的是無論是ANALYZE,CHECK還是OPTIMIZE在執行期間將對表進行鎖定,因此請注意這些操作要在數據庫不繁忙的時候執行MYSQL入門
****
參考
《MySQL 5.1參考手冊》MYSQL入門
by 陳于MYSQL入門
show table status
mysql官方文檔在MYSQL入門
http://dev.mysql.com/doc/refman/5.1/en/show-table-status.htmlMYSQL入門
這里的rows行是表的行數,但是實際上是不準的.myisam是準的,其他的存儲引擎是不準的.要準確的行數就需要使用count(*) 來獲取了.MYSQL入門
mysql執行大批量刪除
執行大批量刪除的時候注意要使用上limitMYSQL入門
因為如果不用limit,刪除大量數據很有可能造成死鎖MYSQL入門
如果delete的where語句不在索引上,可以先找主鍵,然后根據主鍵刪除數據庫MYSQL入門
ps: 平時update和delete的時候最好也加上limit 1 來防止誤操作MYSQL入門
optimize、Analyze、check、repair維護操作MYSQL入門
optimize 數據在插入,更新,刪除的時候難免一些數據遷移,分頁,之后就出現一些碎片,久而久之碎片積累起來影響性能,這就需要DBA定期的優化數據庫減少碎片,這就通過optimize命令.MYSQL入門
如對MyisAM表操作：optimize table 表名MYSQL入門
對于InnoDB表是不支持optimize操作,否則提示“Table does not support optimize, doing recreate + analyze instead”,當然也可以通過命令：alter table one type=innodb; 來替代.MYSQL入門
Analyze 用來分析和存儲表的關鍵字的分布,使得系統獲得準確的統計信息,影響 SQL 的執行計劃的生成.對于數據基本沒有發生變化的表,是不需要經常進行表分析的.但是如果表的數據量變化很明顯,用戶感覺實際的執行計劃和預期的執行計劃不 同的時候,執行一次表分析可能有助于產生預期的執行計劃.MYSQL入門
Analyze table 表名MYSQL入門
Check檢查表或者視圖是否存在錯誤,對 MyISAM 和 InnoDB 存儲引擎的表有作用.對于 MyISAM 存儲引擎的表進行表檢查,也會同時更新關鍵字統計數據MYSQL入門
Repair optimize需要有足夠的硬盤空間,否則可能會破壞表,導致不能操作,那就要用上repair,注意INNODB不支持repair操作
生成亂序的id
方法：MYSQL入門
使用預設表MYSQL入門
比如id和toid的映射MYSQL入門
其中id是固定的,toid是隨機的.MYSQL入門
然后在redis或memcache中記錄一個指針值,指向idMYSQL入門
當要獲取一個新toid的時候,取出指針值,加1,然后去預設表中獲取toidMYSQL入門
查詢和索引
查詢的時候必須要考慮到如何命中索引MYSQL入門
比如有幾個小招：MYSQL入門
1 不要在索引列中使用表達式MYSQL入門
where mycol *2 < 4MYSQL入門
2 不要在like模式的開始位置使用通配符%MYSQL入門
where col_name like ‘%string%'MYSQL入門
不如MYSQL入門
where col_name like ‘string%'MYSQL入門
3 避免過多使用mysql自動轉換類型,有可能無法用到indexMYSQL入門
比如MYSQL入門
select * from mytbl where str_col=4MYSQL入門
但是str_col為字符串,這里其實就隱含了字符串變化MYSQL入門
應該使用MYSQL入門
select * from mytbl where str_col='4'MYSQL入門
索引比表還大就不需要建立索引了嗎
否MYSQL入門
索引是按照順序排列的.所以即使索引比表大,也是可以加快查詢速度的.MYSQL入門
當然如果索引比表還大首要的任務必須是檢查下索引建立地是否有問題MYSQL入門
Char和varchar如何選擇
char是定長,varchar變長
varchar除了設置了數據之外,還多使用1兩個字節定義了數據實際長度.MYSQL入門
char會在后面空余的行填充上空字符串MYSQL入門
myisam建議使用char.myisam中有個靜態表的概念.使用char比使用varchar的查詢效率高很多.MYSQL入門
innodb建議使用varchar.主要是從節省空間的方面考慮MYSQL入門
多個TimeStamp設置默認值
一個表中至多只能有一個字段設置CURRENT_TIMESTAMPMYSQL入門
對于下面的需求：MYSQL入門
一個表中,有兩個字段,createtime和updatetime.MYSQL入門
1 當insert的時候,sql兩個字段都不設置,會設置為當前的時間
2 當update的時候,sql中兩個字段都不設置,updatetime會變更為當前的時間MYSQL入門
這樣的需求是做不到的.因為你無法避免在兩個字段上設置CURRENT_TIMESTAMP?MYSQL入門
解決辦法有幾個：MYSQL入門
1 使用觸發器.MYSQL入門
2 將第一個timestamp的default設置為0MYSQL入門
3 老老實實在sql語句中使用時間戳.MYSQL入門
/article/31872.htmMYSQL入門
查詢數據表有多少行,多少容量
不要使用select count(*)MYSQL入門
使用show table status like ‘table_name'? 但是innodb的話會有50%左右的浮動,是個預估值MYSQL入門
AUTO_INCREMENT的設置MYSQL入門
1 不要設置為int,請設置為unsinged int,auto_increment的范圍是根據類型來判定的
2 auto_increment數據列必須要有索引,并且保證唯一性.
3 auto_increment必須有NOT NULL屬性
4 auto_increment可以使用MYSQL入門
UPDATE table SET seq = LAST_INSERT_ID(seq -1)MYSQL入門
mysql的表示時間的字段用什么類型
表示時間可以使用timestamp和datetime來使用MYSQL入門
datetime表示的時間可以從0000-00-00：00：00 到9999-12-31：00:00:00MYSQL入門
timestamp表示的時間為1970-01-01 08：00：01到2038-01-19 11：14：07MYSQL入門
timestamp占用的空間比datetime少,且可以設置時區等功能,所以能使用timestamp的地方盡量使用timestampMYSQL入門
使用timestamp還可以設置MYSQL入門
[ON UPDATE CURRENT_TIMESTAMP]MYSQL入門
[DEFAULT CURRENT_TIMESTAMP]MYSQL入門
myisam和innodb支持外鍵
myisam不支持外鍵,innodb支持；MYSQL入門
如果你使用創建外鍵的命令對myisam的表操作,操作不會返回失敗,但是是沒有外鍵關聯建立起來的.MYSQL入門
對一個字段加減語句
經常有需求對一個字段加減會使用MYSQL入門
update table set a = a+1MYSQL入門
這樣是對的MYSQL入門
但是如果這樣設置：MYSQL入門
select a from tableMYSQL入門
取出數據后a為1MYSQL入門
update table set a =2MYSQL入門
這樣會導致如果在select和update之間有其他事務操作修改這個字段的話,導致最后的設置可能出錯.MYSQL入門
