MySQL 5.7 ANALYZE TABLE分析索引的統計資訊
在生產環境中，索引的更新操作可能非常頻繁。如果再每次索引發生更新操作時就對其進行Cardinality的統計，那麼將會對資料庫帶來很大的負擔。另外需要考慮的是，如果一張表的資料量非常大，比如一張表有50GB的資料，
那麼統計一次Cardinality資訊所需要的時間可能非常長。這在生產環境的應用中也是不能接受的。因此，資料庫對於Cardinality的統計都是透過取樣(sample)的方法來完成的。
在InnoDB儲存引擎中，Cardinality統計資訊的更新發生在兩個操作中：INSERT和UPDATE。根據前面的敘述，不可能在每次發生INSERT和UPDATE時都去更新Cardinality的資訊，
這會增加資料庫系統的負荷，同事對大表進行統計時，時間上也不允許。
因此InnoDB儲存引擎對於更新Cardinality資訊的策略為：
表中1/16的資料已發生變化
stat_modified_counter > 2 000 000 000
ANALYZE TABLE語句分析並儲存表中索引的分佈情況。對於InnoDB、MyISAM儲存引擎，在分析中，表會被加只讀鎖。這個語句適用於InnoDB、NDB和MyISAM表。對於MyISAM表，這個語句等同於myisamchk --analyze。這個語句對
檢視無效。
在執行非constant型別的表連線時，MySQL使用儲存的索引分佈資訊來決定表的關聯順序。另外，索引的分佈情況也可以用來決定在查詢中使用哪個表的特定索引。
執行這個語句需要對指定表的SELECT和INSERT許可權。
對於分割槽表，需要執行ALTER TABLE ... ANALYZE PARTITION語句。
在MySQL 5.7.1，在執行ANALYZE TABLE語句之前，需要將gtid_next引數設為AUTOMATIC。這個限制在MySQL 5.7.2版本以後被修復。
可以使用SHOW INDEX語句來顯示索引的分佈情況。
預設，MySQL服務會將ANALYZE TABLE語句寫到二進位制日誌中，並傳輸到複製環境中的slave節點。想關閉這個日誌記錄，可以在語句中加上NO_WRITE_TO_BINLOG或LOCAL。
--分析非分割槽表的索引分佈情況
mysql> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept | 0 | PRIMARY | 1 | DEPTNO | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> show keys from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept | 0 | PRIMARY | 1 | DEPTNO | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> analyze table dept;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.dept | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept | 0 | PRIMARY | 1 | DEPTNO | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
--分析分割槽表的索引分佈情況
mysql> select table_schema,table_name,partition_name from information_schema.partitions where table_name = 'trb1';
+--------------+------------+----------------+
| table_schema | table_name | partition_name |
+--------------+------------+----------------+
| fire | trb1 | p0 |
| fire | trb1 | p1 |
| fire | trb1 | p2 |
| fire | trb1 | p3 |
+--------------+------------+----------------+
4 rows in set (0.00 sec)
mysql> show index from trb1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| trb1 | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> alter table trb1 analyze partition p0;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.trb1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> alter table trb1 analyze partition p1;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.trb1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> alter table trb1 analyze partition p2;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.trb1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> alter table trb1 analyze partition p3;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.trb1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> show index from trb1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| trb1 | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
那麼統計一次Cardinality資訊所需要的時間可能非常長。這在生產環境的應用中也是不能接受的。因此，資料庫對於Cardinality的統計都是透過取樣(sample)的方法來完成的。
在InnoDB儲存引擎中，Cardinality統計資訊的更新發生在兩個操作中：INSERT和UPDATE。根據前面的敘述，不可能在每次發生INSERT和UPDATE時都去更新Cardinality的資訊，
這會增加資料庫系統的負荷，同事對大表進行統計時，時間上也不允許。
因此InnoDB儲存引擎對於更新Cardinality資訊的策略為：
表中1/16的資料已發生變化
stat_modified_counter > 2 000 000 000
ANALYZE TABLE語句分析並儲存表中索引的分佈情況。對於InnoDB、MyISAM儲存引擎，在分析中，表會被加只讀鎖。這個語句適用於InnoDB、NDB和MyISAM表。對於MyISAM表，這個語句等同於myisamchk --analyze。這個語句對
檢視無效。
在執行非constant型別的表連線時，MySQL使用儲存的索引分佈資訊來決定表的關聯順序。另外，索引的分佈情況也可以用來決定在查詢中使用哪個表的特定索引。
執行這個語句需要對指定表的SELECT和INSERT許可權。
對於分割槽表，需要執行ALTER TABLE ... ANALYZE PARTITION語句。
在MySQL 5.7.1，在執行ANALYZE TABLE語句之前，需要將gtid_next引數設為AUTOMATIC。這個限制在MySQL 5.7.2版本以後被修復。
可以使用SHOW INDEX語句來顯示索引的分佈情況。
預設，MySQL服務會將ANALYZE TABLE語句寫到二進位制日誌中，並傳輸到複製環境中的slave節點。想關閉這個日誌記錄，可以在語句中加上NO_WRITE_TO_BINLOG或LOCAL。
--分析非分割槽表的索引分佈情況
mysql> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept | 0 | PRIMARY | 1 | DEPTNO | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> show keys from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept | 0 | PRIMARY | 1 | DEPTNO | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> analyze table dept;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.dept | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> show index from dept;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dept | 0 | PRIMARY | 1 | DEPTNO | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
--分析分割槽表的索引分佈情況
mysql> select table_schema,table_name,partition_name from information_schema.partitions where table_name = 'trb1';
+--------------+------------+----------------+
| table_schema | table_name | partition_name |
+--------------+------------+----------------+
| fire | trb1 | p0 |
| fire | trb1 | p1 |
| fire | trb1 | p2 |
| fire | trb1 | p3 |
+--------------+------------+----------------+
4 rows in set (0.00 sec)
mysql> show index from trb1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| trb1 | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> alter table trb1 analyze partition p0;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.trb1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.03 sec)
mysql> alter table trb1 analyze partition p1;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.trb1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> alter table trb1 analyze partition p2;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.trb1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> alter table trb1 analyze partition p3;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| fire.trb1 | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> show index from trb1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| trb1 | 0 | PRIMARY | 1 | id | A | 10 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2115026/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle表table與索引index的分析及索引重建及統計資訊匯入匯出Oracle索引Index
- Analyze分析表或者索引索引
- MySQL:Analyze table導致'waiting for table flush'MySqlAI
- 觀察analyze table compute statistics 都對什麼物件統計了資訊 - 轉物件
- MySQL中Innodb如何計算索引的統計資訊？MySql索引
- MySQL索引統計資訊更新相關的引數MySql索引
- MySQL 5.7 索引優化MySql索引優化
- ORACLE表統計資訊與列統計資訊、索引統計資訊Oracle索引
- 資料庫效能調優之始： analyze統計資訊資料庫
- Analyze table對Oracle效能的提升Oracle
- Oracle analyze table的使用總結Oracle
- MySQL5.7 Waiting FOR TABLE FLUSHMySqlAI
- oracle dbms_stat與analyze 獲取有效的統計資訊(5)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(4)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(3)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(2)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(1)Oracle
- oracle dbms_stat與analyze 獲取有效的統計資訊(6)Oracle
- MySQL5.7統計資訊更新的相關引數解釋和測試MySql
- MySQL的索引分析MySql索引
- 【實驗】【analyze】分析特定使用者的表和索引索引
- 【索引】使用索引分析快速得到索引的基本資訊索引
- MySQL 統計資訊MySql
- 【轉】dbms_stats.gather_table_stats與analyze table 的區別
- 【Mysql】MySQL 5.7新特性之Generated Column（函式索引）MySql函式索引
- MySQL5.7審計功能windows系統MySqlWindows
- 分析索引快速獲取索引資訊索引
- dbms_stats.gather_table_stats與analyze table 的區別[轉貼]
- MySQL索引效能分析MySql索引
- mysql 執行計劃索引分析筆記MySql索引筆記
- MySQL · 特性分析 · MySQL 5.7新特性系列一MySql
- SQL優化之統計資訊和索引SQL優化索引
- MySQL統計資訊系列MySql
- MySQL統計資訊簡介MySql
- mysql如收集統計資訊MySql
- MySQL 5.5 統計資訊收集MySql
- analyze table/index validate structure加鎖的問題IndexStruct
- Oracle資料庫的資料統計(Analyze)Oracle資料庫