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: checkMsg_type: ErrorMsg_text: Table 'test.a' doesn't exist*************************** 2. row ***************************   Table: test.a_view      Op: checkMsg_type: ErrorMsg_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: checkMsg_type: errorMsg_text: Corrupt3 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) ****需要注意的是無論是ANALYZE，CHECK還是OPTIMIZE在執行期間將對錶進行鎖定，因此請注意這些操作要在資料庫不繁忙的時候執行****  

2012-11-22 13:04 by 軒脈刃, 1318 閱讀, 5 評論, 收藏, 編輯

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維護操作

l optimize 資料在插入，更新，刪除的時候難免一些資料移轉，分頁，之後就出現一些片段，久而久之片段積累起來影響效能，這就需要DBA週期性最佳化資料庫減少片段，這就通過optimize命令。

如對MyisAM表操作：optimize table 表名

對於InnoDB表是不支援optimize操作，否則提示“Table does not support optimize, doing recreate + analyze instead”，當然也可以通過命令：alter table one type=innodb; 來替代。

l Analyze 用來分析和儲存表的關鍵字的分布，使得系統獲得準確的統計資訊，影響 SQL 的執行計畫的產生。對於資料基本沒有發生變化的表，是不需要經常進行表分析的。但是如果表的資料量變化很明顯，使用者感覺實際的執行計畫和預期的執行計畫不 同的時候，執行一次表分析可能有助於產生預期的執行計畫。

Analyze table 表名

l Check檢查表或者視圖是否存在錯誤，對 MyISAM 和 InnoDB 儲存引擎的表有作用。對於 MyISAM 儲存引擎的表進行表檢查，也會同時更新關鍵字統計資料

l 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會在後面空餘的行填充上Null 字元串

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.cnblogs.com/yjf512/archive/2012/11/02/2751058.html

查詢資料表有多少行，多少容量

不要使用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之間有其他事務操作修改這個欄位的話，導致最後的設定可能出錯。

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維護操作

l optimize 資料在插入，更新，刪除的時候難免一些資料移轉，分頁，之後就出現一些片段，久而久之片段積累起來影響效能，這就需要DBA週期性最佳化資料庫減少片段，這就通過optimize命令。

如對MyisAM表操作：optimize table 表名

對於InnoDB表是不支援optimize操作，否則提示“Table does not support optimize, doing recreate + analyze instead”，當然也可以通過命令：alter table one type=innodb; 來替代。

l Analyze 用來分析和儲存表的關鍵字的分布，使得系統獲得準確的統計資訊，影響 SQL 的執行計畫的產生。對於資料基本沒有發生變化的表，是不需要經常進行表分析的。但是如果表的資料量變化很明顯，使用者感覺實際的執行計畫和預期的執行計畫不 同的時候，執行一次表分析可能有助於產生預期的執行計畫。

Analyze table 表名

l Check檢查表或者視圖是否存在錯誤，對 MyISAM 和 InnoDB 儲存引擎的表有作用。對於 MyISAM 儲存引擎的表進行表檢查，也會同時更新關鍵字統計資料

l 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會在後面空餘的行填充上Null 字元串

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.cnblogs.com/yjf512/archive/2012/11/02/2751058.html

查詢資料表有多少行，多少容量

不要使用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之間有其他事務操作修改這個欄位的話，導致最後的設定可能出錯。