MySQL按期剖析檢討與優化表的辦法小結。本站提示廣大學習愛好者：（MySQL按期剖析檢討與優化表的辦法小結）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL按期剖析檢討與優化表的辦法小結正文
按期剖析表
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]
本語句用於剖析和存儲表的症結字散布。在剖析時代，應用一個讀取鎖定對表停止鎖定。這關於MyISAM, BDB和InnoDB表有感化。關於MyISAM表，本語句與應用myisamchk -a相當。
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)
按期檢討表
CHECK TABLE tbl_name [, tbl_name] [option]
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
檢討一個或多個表能否有毛病。CHECK TABLE對MyISAM和InnoDB表有感化。關於MyISAM表，症結字統計數據被更新。
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> create view a_view as select * from a;
Query OK, 0 rows affected (0.02 sec)
然後CHECK一下該視圖，發明沒有成績
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> drop table a;
Query OK, 0 rows affected (0.01 sec)
再CHECK一下適才的視圖，發明報錯了
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)
ERROR:
No query specified
按期優化表
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> 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的贊助手冊，這裡只是簡略指出幾種按期優化的方法，須要留意的是不管是ANALYZE，CHECK照樣OPTIMIZE在履行時代將對表停止鎖定，是以請留意這些操作要在數據庫不忙碌的時刻履行
****
參考
《MySQL 5.1參考手冊》
by 陳於喆
show table status
mysql官方文檔在
http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html
這裡的rows行是表的行數，然則現實上是禁絕的。myisam是准的，其他的存儲引擎是禁絕的。要精確的行數就須要應用count(*) 來獲得了。
mysql履行年夜批量刪除
履行年夜批量刪除的時刻留意要應用上limit
由於假如不消limit，刪除年夜量數據很有能夠形成逝世鎖
假如delete的where語句不在索引上，可以先找主鍵，然後依據主鍵刪除數據庫
ps: 日常平凡update和delete的時刻最好也加上limit 1 來避免誤操作
optimize、Analyze、check、repair保護操作
optimize 數據在拔出，更新，刪除的時刻不免一些數據遷徙，分頁，以後就湧現一些碎片，一朝一夕碎片積聚起來影響機能，這就須要DBA按期的優化數據庫削減碎片，這就經由過程optimize敕令。
如對MyisAM表操作：optimize table 表名
關於InnoDB表是不支撐optimize操作，不然提醒“Table does not support optimize, doing recreate + analyze instead”，固然也能夠經由過程敕令：alter table one type=innodb; 來替換。
Analyze 用來剖析和存儲表的症結字的散布，使得體系取得精確的統計信息，影響 SQL 的履行籌劃的生成。關於數據根本沒有產生變更的表，是不須要常常停止表剖析的。然則假如表的數據質變化很顯著，用戶感到現實的履行籌劃和預期的履行籌劃不 同的時刻，履行一次表剖析能夠有助於發生預期的履行籌劃。
Analyze table 表名
Check檢討表或許視圖能否存在毛病，對 MyISAM 和 InnoDB 存儲引擎的表有感化。關於 MyISAM 存儲引擎的表停止表檢討，也會同時更新症結字統計數據
Repair optimize須要有足夠的硬盤空間，不然能夠會損壞表，招致不克不及操作，那就要用上repair，留意INNODB不支撐repair操作
生成亂序的id
辦法：
應用預設表
好比id和toid的映照
個中id是固定的，toid是隨機的。
然後在redis或memcache中記載一個指針值，指向id
當要獲得一個新toid的時刻，掏出指針值，加1，然後去預設表中獲得toid
查詢和索引
查詢的時刻必需要斟酌到若何射中索引
好比有幾個小招：
1 不要在索引列中應用表達式
where mycol *2 < 4
2 不要在like形式的開端地位應用通配符%
where col_name like ‘%string%'
不如
where col_name like ‘string%'
3 防止過量應用mysql主動轉換類型，有能夠沒法用到index
好比
select * from mytbl where str_col=4
然則str_col為字符串，這裡其實就隱含了字符串變更
應當應用
select * from mytbl where str_col='4'
索引比表還年夜就不須要樹立索引了嗎
否
索引是依照次序分列的。所以即便索引比表年夜，也是可以加速查詢速度的。
固然假如索引比表還年夜重要的義務必需是檢討下索引樹立地能否有成績
Char和varchar若何選擇
char是定長，varchar變長
varchar除設置了數據以外，還多應用1兩個字節界說了數據現實長度。
char會在前面空余的行填充上空字符串
myisam建議應用char。myisam中有個靜態表的概念。應用char比應用varchar的查詢效力高許多。
innodb建議應用varchar。重要是從節儉空間的方面斟酌
多個TimeStamp設置默許值
一個表中至少只能有一個字段設置CURRENT_TIMESTAMP
關於上面的需求：
一個表中，有兩個字段，createtime和updatetime。
1 當insert的時刻，sql兩個字段都不設置，會設置為以後的時光
2 當update的時刻，sql中兩個字段都不設置，updatetime會變革為以後的時光
如許的需求是做不到的。由於你沒法防止在兩個字段上設置CURRENT_TIMESTAMP
處理方法有幾個：
1 應用觸發器。
2 將第一個timestamp的default設置為0
3 老誠實其實sql語句中應用時光戳。
http://www.jb51.net/article/31872.htm
查詢數據表有若干行，若干容量
不要應用select count(*)
應用show table status like ‘table_name' 然則innodb的話會有50%閣下的浮動，是個預估值
AUTO_INCREMENT的設置
1 不要設置為int，請設置為unsinged int，auto_increment的規模是依據類型來剖斷的
2 auto_increment數據列必需要有索引，而且包管獨一性。
3 auto_increment必需有NOT NULL屬性
4 auto_increment可使用
UPDATE table SET seq = LAST_INSERT_ID(seq -1)
mysql的表現時光的字段用甚麼類型
表現時光可使用timestamp和datetime來應用
datetime表現的時光可以從0000-00-00：00：00 到9999-12-31：00:00:00
timestamp表現的時光為1970-01-01 08：00：01到2038-01-19 11：14：07
timestamp占用的空間比datetime少，且可以設置時區等功效，所以能應用timestamp的處所盡可能應用timestamp
應用timestamp還可以設置
[ON UPDATE CURRENT_TIMESTAMP]
[DEFAULT CURRENT_TIMESTAMP]
myisam和innodb支撐外鍵
myisam不支撐外鍵，innodb支撐；
假如你應用創立外鍵的敕令對myisam的表操作，操作不會前往掉敗，然則是沒有外鍵聯系關系樹立起來的。
對一個字段加減語句
常常有需求對一個字段加減會應用
update table set a = a+1
如許是對的
然則假如如許設置：
select a from table
掏出數據後a為1
update table set a =2
如許會招致假如在select和update之間有其他事務操作修正這個字段的話，招致最初的設置能夠失足。
