為什麼要關注索引統計誤差

導讀

由一個不可思議的索引統計資訊誤差案例引發的監控需求。

事情的起因是，我的朋友小明同學有一天突然發現有個SQL的執行計劃出問題了。經過一番排查，居然發現是該表的輔助索引統計資訊存在嚴重偏差。

我們知道，InnoDB表裡每個輔助索引都會同時存儲聚集索引列值，這就是所謂的 Index Extensions特性。那麼，在統計索引資訊時，包含聚集索引列的統計值就應該和聚集索引列的值幾乎一樣的才對，比如：（建議橫屏觀看）

[[email protected]]>select * from mysql.innodb_index_stats;
+------------+------------+------------+-------------+------------------+
| table_name | index_name | stat_value | sample_size | stat_description |
+------------+------------+------------+-------------+------------------+
...
| zst        | PRIMARY    |      40002 |          20 | id               |
...
| zst        | k1         |      40376 |          20 | uid,id           |
...
+------------+------------+------------+-------------+------------------+      

可以看到k1索引的 (uid, id) 統計值（stat_value列）和主鍵索引是幾乎差不多的。

這次小明遇到的問題，也是我這麼多年來頭一次遇到過，而且這還是在國内某知名公有雲資料庫上發生的，簡直有點不太可思議。送出工單後，工程師給的答複也表示以前沒遇到過，暫時不确定是什麼原因引起的。

既然這種問題不能避免，那就自己主動加個監控吧，于是就有了本文。

解決方案

找出索引統計資訊中，輔助索引統計資訊和主鍵索引相差太大的情況，也就是輔助索引的基數和主鍵索引相差太大的現象，發出告警，并且手動執行

ANALYZE TABLE t

更新索引統計資訊，一般就能解決問題了。

如何監控

1. 每個非唯一輔助索引都會包含主鍵列，正常情況下，包含主鍵列的那行統計資訊和主鍵索引的統計資訊相差不會太大。
2. 唯一索引比較特殊，因為在 mysql.innodb_index_stats 表中，唯一索引列統計資訊不會再包含主鍵列，但其基準值和主鍵列的基準值也不能相差太大。

假設有個表t3的索引統計資料如下（建議橫屏觀看）

[[email protected]] [mysql]>select database_name as db,
   table_name as tbl, index_name as idx, stat_name,
   stat_value, stat_description
    from innodb_index_stats where
    database_name = 'zhishutang' and table_name = 't3';
+------------+-----+---------+--------------+------------+-----------------------------------+
| db         | tbl | idx     | stat_name    | stat_value | stat_description                  |
+------------+-----+---------+--------------+------------+-----------------------------------+
| zhishutang | t3  | PRIMARY | n_diff_pfx01 |       1900 | id                                |
| zhishutang | t3  | PRIMARY | n_leaf_pages |          1 | Number of leaf pages in the index |
| zhishutang | t3  | PRIMARY | size         |          1 | Number of pages in the index      |
| zhishutang | t3  | name    | n_diff_pfx01 |          1 | name                              |
| zhishutang | t3  | name    | n_diff_pfx02 |         19 | name,id                           |
| zhishutang | t3  | name    | n_leaf_pages |          1 | Number of leaf pages in the index |
| zhishutang | t3  | name    | size         |          1 | Number of pages in the index      |
| zhishutang | t3  | nu      | n_diff_pfx01 |       1900 | nu                                |
| zhishutang | t3  | nu      | n_leaf_pages |          1 | Number of leaf pages in the index |
| zhishutang | t3  | nu      | size         |          1 | Number of pages in the index      |
+------------+-----+---------+--------------+------------+-----------------------------------+      

以上面為例，希望得到的結果是

1. 唯一索引nu的統計資訊和主鍵索引統計資訊一樣，沒問題。
2. 輔助索引name的第二條（含主鍵列的那條）統計資訊 (name, id) 和主鍵索引統計資訊相差太遠，屬于異常，要能被發現。

實作該目的的SQL方法如下：（建議橫屏觀看）

set @statdb = 'yejr';
select
a.database_name ,
a.table_name ,
a.index_name ,
a.stat_value SK,
b.stat_value PK,
round((a.stat_value/b.stat_value)*100,2) stat_pct
from
(
select
b.database_name  ,
b.table_name  ,
b.index_name ,
b.stat_value
from
(
select database_name  ,
table_name  ,
index_name ,
max(stat_name) stat_name
from innodb_index_stats
where   database_name = @statdb
and stat_name not in ( 'size' ,'n_leaf_pages' )
group by
database_name  ,
table_name  ,
index_name
) a join innodb_index_stats b on a.database_name=b.database_name
and a.table_name=b.table_name
and a.index_name=b.index_name
and a.stat_name=b.stat_name
and b.index_name !='PRIMARY'
) a left join
(
select
b.database_name  ,
b.table_name  ,
b.index_name ,
b.stat_value
from
(
select database_name  ,
table_name  ,
index_name ,
max(stat_name) stat_name
from innodb_index_stats
where   database_name = @statdb
and stat_name not in ( 'size' ,'n_leaf_pages' )
group by
database_name  ,
table_name  ,
index_name
) a join innodb_index_stats b
on a.database_name=b.database_name
and a.table_name=b.table_name
and a.index_name=b.index_name
and a.stat_name=b.stat_name
and b.index_name ='PRIMARY'
) b
on a.database_name=b.database_name
and a.table_name=b.table_name
where b.stat_value is not null
and  a.stat_value >0
order by stat_pct;

+---------------+-------------------+--------------+--------+--------+----------+
| database_name | table_name        | index_name   | SK     | PK     | stat_pct |
+---------------+-------------------+--------------+--------+--------+----------+
| zhishutang    | t_json_vs_vchar   | c1vc         |  37326 |  39825 |    93.73 |
| zhishutang    | t_json_vs_vchar   | c2vc         |  37371 |  39825 |    93.84 |
| zhishutang    | t1                | name         | 299815 | 299842 |    99.99 |
| zhishutang    | t4                | c2           |      2 |      2 |   100.00 |
+---------------+-------------------+--------------+--------+--------+----------+      

上面的SQL邏輯過于複雜，我是搞不定的，也是請知數堂SQL優化班鄭松華老師幫忙給寫的。

這個SQL腳本，我也已放在知數堂github庫裡“檢視索引統計偏差”。