如何理解MySQL索引cardinalit

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

創(chuàng)新互聯(lián)堅持“要么做到，要么別承諾”的工作理念，服務(wù)領(lǐng)域包括：網(wǎng)站設(shè)計制作、成都網(wǎng)站制作、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣等服務(wù)，滿足客戶于互聯(lián)網(wǎng)時代的河?xùn)|網(wǎng)站設(shè)計、移動媒體設(shè)計的需求，幫助企業(yè)找到有效的互聯(lián)網(wǎng)解決方案。努力成為您成熟可靠的網(wǎng)絡(luò)建設(shè)合作伙伴！

查看一個表的索引：

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，可以通過觀察它來評估索引是否合理。它會估計索引中不重復(fù)記錄，如果這個相對值很小，可能就要評估索引是否有意義。

       查看表的總行數(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個索引列，估算出來的值/總數(shù)都趨近于0。估算出來的值/總數(shù)=占比，我們稱占比為相對值。

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

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)都會走索引，而且ref都是const。難道是cardinality不準(zhǔn)？是的，因為它是一個預(yù)估值！

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

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

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

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

  • 如果stat_modified_counter>200 000 0000

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

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

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

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

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

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

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

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

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

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

手動更新統(tǒng)計值

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

analyze table tablename;

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

選擇性

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

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

select * from user where sex = 'M';

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

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

分享文章：如何理解MySQL索引cardinalit
文章路徑：http://muchs.cn/article22/ijoccc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務(wù)器托管、網(wǎng)站收錄、搜索引擎優(yōu)化、微信公眾號、全網(wǎng)營銷推廣、移動網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)

搜索引擎優(yōu)化