如何理解MySQL索引cardinalit

本篇內(nèi)容主要講解“如何理解MySQL索引cardinalit”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“如何理解MySQL索引cardinalit”吧!

查看一個(gè)表的索引：

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      |         |               |
+-----------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

      上面有一個(gè)屬性Cardinality，可以通過觀察它來評(píng)估索引是否合理。它會(huì)估計(jì)索引中不重復(fù)記錄，如果這個(gè)相對(duì)值很小，可能就要評(píng)估索引是否有意義。

       查看表的總行數(shù)：

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估算出來的值/總數(shù)接近于1；而另外2個(gè)索引列，估算出來的值/總數(shù)都趨近于0。估算出來的值/總數(shù)=占比，我們稱占比為相對(duì)值。

通過上面表格做一個(gè)大膽推測(cè)，查詢id列是很快，查詢另外2列是很慢；現(xiàn)在我們看下相應(yīng)的執(zhí)行計(jì)劃。

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)

      但是發(fā)現(xiàn)都會(huì)走索引，而且ref都是const。難道是cardinality不準(zhǔn)？是的，因?yàn)樗且粋€(gè)預(yù)估值！

cardinality是怎么預(yù)估的？

      上面提到cardinality是索引中不重復(fù)記錄的預(yù)估值，那么它是怎么實(shí)現(xiàn)的呢？由于Mysql的B+索引在每個(gè)存儲(chǔ)引擎中實(shí)現(xiàn)的都不一樣，所以cardinality干脆放到存儲(chǔ)引擎層面實(shí)現(xiàn)的！

對(duì)于innodb來說，達(dá)到以下2點(diǎn)就會(huì)重新計(jì)算cardinality

• 如果表中1/16的數(shù)據(jù)發(fā)生變化 

• 如果stat_modified_counter>200 000 0000

       這是為什么呢？因?yàn)檎鎸?shí)環(huán)境中，索引的更新可能非常頻繁，比如一個(gè)表中數(shù)據(jù)的插入，更新，刪除等，每次都去統(tǒng)計(jì)cardinality會(huì)帶來很大的負(fù)擔(dān)；另外如果是一個(gè)大表，統(tǒng)計(jì)一次可能非常耗時(shí)?；诖?，采用基于上面2個(gè)條件的"抽樣"統(tǒng)計(jì)的方式。

那上面2種有什么區(qū)別呢？

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

那具體是如何采樣統(tǒng)計(jì)的呢？

• 獲取B+樹葉子節(jié)點(diǎn)的數(shù)據(jù)，記為A

• 隨機(jī)獲得B+樹索引中8個(gè)葉子節(jié)點(diǎn)。統(tǒng)計(jì)每個(gè)頁不同記錄的個(gè)數(shù)，分別記為P1,P2...P8

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

從而得出索引中不同記錄的數(shù)量。從上面可以發(fā)現(xiàn)，有2個(gè)問題

1、由于是隨機(jī)采樣的方式，所以會(huì)出現(xiàn)，連續(xù)2次統(tǒng)計(jì)，數(shù)量都不同。只有在表數(shù)據(jù)非常少，葉子節(jié)點(diǎn)不多于8個(gè)時(shí)，每次采樣都是取到相同的頁，統(tǒng)計(jì)值才會(huì)相同。
2、由于統(tǒng)計(jì)值是基于上面2個(gè)條件去更新的，可能出現(xiàn)系統(tǒng)運(yùn)行了一段時(shí)間之后，數(shù)據(jù)發(fā)生了很大變化，統(tǒng)計(jì)值偏差比較大了，那么索引的效率會(huì)下降。

那對(duì)于問題2，該怎么處理呢？

手動(dòng)更新統(tǒng)計(jì)值

       如果系統(tǒng)運(yùn)行一段時(shí)間之后，我們可以通過執(zhí)行下面的sql，重新計(jì)算cardinality值。

analyze table tablename;

不過，如果表很大，重新統(tǒng)計(jì)可能會(huì)非常耗時(shí)間，建議對(duì)于核心表，在非高峰時(shí)段操作

選擇性

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

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

select * from user where sex = 'M';

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

到此，相信大家對(duì)“如何理解MySQL索引cardinalit”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！