[MySQL]ANALYZE TABLE 更新索引基數

MySQL使用存盤的鍵分布基數來確定表連接順序
在決定對查詢中的特定表使用哪些索引時，也會使用使用鍵分布基數

ANALYZE TABLE 表名 可以更新表的索引基數，使其更接近非重復的記錄數，記錄數可以使用show index from 表 來查詢cardinality欄位

mysql> show index from index_test;
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_test |          0 | PRIMARY            |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | score_index        |            1 | score       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | name_gid_age_index |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | name_gid_age_index |            2 | gid         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | name_gid_age_index |            3 | age         | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql> select * from index_test;
+----+------------+-----+-----+-------+
| id | name       | gid | age | score |
+----+------------+-----+-----+-------+
|  1 | taoshihan  |   2 |   0 |     0 |
|  2 | taoshihan1 |   2 |   0 |     0 |
|  3 | taoshihan2 |   3 |  10 |    10 |
|  4 | taoshihan  |   2 |   1 |     0 |
|  5 | taoshihan  |   2 |   2 |     0 |
|  6 | taoshihan  |   2 |   3 |     0 |
+----+------------+-----+-----+-------+
6 rows in set (0.03 sec)

mysql> ANALYZE TABLE index_test;
+--------------------+---------+----------+----------+
| Table              | Op      | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| my_test.index_test | analyze | status   | OK       |
+--------------------+---------+----------+----------+
1 row in set (0.13 sec)

mysql> show index from index_test;
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| index_test |          0 | PRIMARY            |            1 | id          | A         |           6這里變了 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | score_index        |            1 | score       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | name_gid_age_index |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | name_gid_age_index |            2 | gid         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| index_test |          1 | name_gid_age_index |            3 | age         | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.07 sec)

標籤：MySQL

上一篇：mysql 使用 limit ，從指定條數讀取完，-1失效

下一篇：資料庫系統概論--資料模型