如何理解MySQL索引cardinalit

本篇內容主要講解“如何理解MySQL索引cardinalit”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“如何理解MySQL索引cardinalit”吧!

查看一個表的索引：

mysql> show index from rank_item;
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rank_item |          0 | PRIMARY            |            1 | id          | A         |     5665508 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | idx_city_category  |            1 | city        | A         |        2713 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | idx_city_category  |            2 | category    | A         |        3798 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_artisan_id     |            1 | artisan_id  | A         |       33916 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | index_weight       |            1 | weight      | A         |       11680 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | product_id_plan_id |            1 | product_id  | A         |     1480432 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | product_id_plan_id |            2 | plan_id     | A         |     5590288 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_cat_ci_art     |            1 | category    | A         |        3170 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_cat_ci_art     |            2 | city        | A         |       11417 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | idx_cat_ci_art     |            3 | artisan_id  | A         |       46514 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_ca_ci_pid_wei  |            1 | category    | A         |        3187 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_ca_ci_pid_wei  |            2 | city        | A         |       10869 |     NULL | NULL   |      | BTREE      |         |               |
| rank_item |          1 | idx_ca_ci_pid_wei  |            3 | plan_id     | A         |       17403 |     NULL | NULL   | YES  | BTREE      |         |               |
| rank_item |          1 | idx_ca_ci_pid_wei  |            4 | weight      | A         |      659306 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

      上面有一個屬性Cardinality，可以通過觀察它來評估索引是否合理。它會估計索引中不重復記錄，如果這個相對值很小，可能就要評估索引是否有意義。

       查看表的總行數：

mysql> select count(*) as total from rank_item;
+---------+
| total   |
+---------+
| 5581872 |
+---------+

觀察以下信息：

id列：Cardinality/total=5608506/5581872=1.005

city列：Cardinality/total=2713/5581872=0.0000486

category列：Cardinality/total=3170/5581872=0.0000568

       列id由于是主鍵，通過cardinality估算出來的值/總數接近于1；而另外2個索引列，估算出來的值/總數都趨近于0。估算出來的值/總數=占比，我們稱占比為相對值。

通過上面表格做一個大膽推測，查詢id列是很快，查詢另外2列是很慢；現在我們看下相應的執行計劃。

mysql> explain select * from rank_item where id=2419;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | rank_item | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from rank_item where city=4967;
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+--------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys     | key               | key_len | ref   | rows   | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+--------+----------+-------+
|  1 | SIMPLE      | rank_item | NULL       | ref  | idx_city_category | idx_city_category | 4       | const | 556680 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+-------------------+-------------------+---------+-------+--------+----------+-------+
1 row in set, 1 warning (0.04 sec)

      但是發現都會走索引，而且ref都是const。難道是cardinality不準？是的，因為它是一個預估值！

cardinality是怎么預估的？

      上面提到cardinality是索引中不重復記錄的預估值，那么它是怎么實現的呢？由于Mysql的B+索引在每個存儲引擎中實現的都不一樣，所以cardinality干脆放到存儲引擎層面實現的！

對于innodb來說，達到以下2點就會重新計算cardinality

• 如果表中1/16的數據發生變化 

• 如果stat_modified_counter>200 000 0000

       這是為什么呢？因為真實環境中，索引的更新可能非常頻繁，比如一個表中數據的插入，更新，刪除等，每次都去統計cardinality會帶來很大的負擔；另外如果是一個大表，統計一次可能非常耗時。基于此，采用基于上面2個條件的"抽樣"統計的方式。

那上面2種有什么區別呢？

       如果表中1/16數據發生變化則會更新；第2種情況比較特別，如果某一千數據頻繁更新，但是數據并沒有增加，則第一種無法適用，所以設置stat_modified_counter為發生變化的次數；如果次數達到200 000 0000，也會更新統計值。

那具體是如何采樣統計的呢？

• 獲取B+樹葉子節點的數據，記為A

• 隨機獲得B+樹索引中8個葉子節點。統計每個頁不同記錄的個數，分別記為P1,P2...P8

• 計算cardinality = (P1+P2+...P8)A/8

從而得出索引中不同記錄的數量。從上面可以發現，有2個問題

1、由于是隨機采樣的方式，所以會出現，連續2次統計，數量都不同。只有在表數據非常少，葉子節點不多于8個時，每次采樣都是取到相同的頁，統計值才會相同。
2、由于統計值是基于上面2個條件去更新的，可能出現系統運行了一段時間之后，數據發生了很大變化，統計值偏差比較大了，那么索引的效率會下降。

那對于問題2，該怎么處理呢？

手動更新統計值

       如果系統運行一段時間之后，我們可以通過執行下面的sql，重新計算cardinality值。

analyze table tablename;

不過，如果表很大，重新統計可能會非常耗時間，建議對于核心表，在非高峰時段操作

選擇性

       現在又回到前面的例子，我們通過觀察執行計劃發現，不論cardinality大小，相對值大小，發現還是會走索引，那為什么要說對于相對值非常小的不建議建索引呢？這就涉及到一個選擇性的問題

       比如有一個用戶表，有一列性別sex，現在要查詢所以性別為male的用戶(假定只有男人-male，女人-female，沒有其它不明性別),可能的sql：

select * from user where sex = 'M';

          對于這個sql，雖然sex上有索引，但是執行的時候，讀取的數據可能會超過一半，甚至在極端情況下(比如程序員的網站)，大部分數據都需要讀取，所以還是會走全表掃描，這種數據稱為低選擇性。反之，如果是高選擇性的，建議建索引 ，比如user表中用戶，一般來說很少重復；

到此，相信大家對“如何理解MySQL索引cardinalit”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！