MYSQL5.6 5.7處理數(shù)據(jù)分布不均的問題分析

本篇內容主要講解“MYSQL5.6 5.7處理數(shù)據(jù)分布不均的問題分析”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“MYSQL5.6 5.7處理數(shù)據(jù)分布不均的問題分析”吧!

處理數(shù)據(jù)分布不均，orace數(shù)據(jù)庫使用額外的統(tǒng)計數(shù)據(jù)直方圖來完成，而MYSQL
中統(tǒng)計數(shù)據(jù)只有索引的不同值這樣一個統(tǒng)計數(shù)據(jù)，那么我們制出如下數(shù)據(jù)：
mysql> select * from test.testf;
+------+----------+
| id   | name     |
+------+----------+
|    1 | gaopeng  |
|    2 | gaopeng1 |
|    3 | gaopeng1 |
|    4 | gaopeng1 |
|    5 | gaopeng1 |
|    6 | gaopeng1 |
|    7 | gaopeng1 |
|    8 | gaopeng1 |
|    9 | gaopeng1 |
|   10 | gaopeng1 |
+------+----------+
10 rows in set (0.00 sec)
name 上有一個普通二級索引
mysql> analyze table test.testf;
+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| test.testf | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set (0.21 sec)

分別作出如下執(zhí)行計劃：
mysql> explain select * from test.testf where name='gaopeng';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | testf | NULL       | ref  | name          | name | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from test.testf where name='gaopeng1';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | testf | NULL       | ALL  | name          | NULL | NULL    | NULL |   10 |    90.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到執(zhí)行計劃是正確的，name='gaopeng'的只有一行選擇了索引，name='gaopeng1'的有9行走了全表。
按理說如果只是記錄不同的那么這兩個語句的選擇均為1/2,應該會造成執(zhí)行計劃錯誤，而MYSQL 5.6 5.7中
都做了正確的選擇，那是為什么呢？
其實原因就在于 eq_range_index_dive_limit這個參數(shù)，我們來看一下trace
T@2: | | | | | | | | | | | opt: (null): "gaopeng1 <= name <=  | T@3: | | | | | | | | | | | opt: (null): "gaopeng <= name <= g
T@2: | | | | | | | | | | | opt: ranges: ending struct         | T@3: | | | | | | | | | | | opt: ranges: ending struct
T@2: | | | | | | | | | | | opt: index_dives_for_eq_ranges: 1  | T@3: | | | | | | | | | | | opt: index_dives_for_eq_ranges: 1
T@2: | | | | | | | | | | | opt: rowid_ordered: 1              | T@3: | | | | | | | | | | | opt: rowid_ordered: 1
T@2: | | | | | | | | | | | opt: using_mrr: 0                  | T@3: | | | | | | | | | | | opt: using_mrr: 0
T@2: | | | | | | | | | | | opt: index_only: 0                 | T@3: | | | | | | | | | | | opt: index_only: 0
T@2: | | | | | | | | | | | opt: rows: 9                       | T@3: | | | | | | | | | | | opt: rows: 1
T@2: | | | | | | | | | | | opt: cost: 11.81                   | T@3: | | | | | | | | | | | opt: cost: 2.21


我們可以看到 index_dives_for_eq_ranges均為1，rows: 9 rows: 1都是正確的，那么可以確定是index_dives_for_eq_ranges的作用，實際上
這是一個參數(shù)eq_range_index_dive_limit來決定的(equality range optimization of many-valued comparisions)，默認為
mysql> show variables like '%eq%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| eq_range_index_dive_limit            | 200   |

在官方文檔說這個取值是等值范圍比較的時候有多少個需要比較的值
如：
id=1 or id=2 or id=3 那么他取值就是3+1=4
而這種方法會得到精確的數(shù)據(jù)，但是增加的是時間成本，如果將
eq_range_index_dive_limit 設置為1：則禁用此功能
eq_range_index_dive_limit 設置為0：則始終開啟
eq_range_index_dive_limit 設置為N：則滿足N-1個這樣的域。
那么我們設置為eq_range_index_dive_limit=1 后看看
mysql> explain select * from test.testf where name='gaopeng1';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | testf | NULL       | ref  | name          | name | 63      | const |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from test.testf where name='gaopeng';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | testf | NULL       | ref  | name          | name | 63      | const |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到執(zhí)行計劃已經(jīng)錯誤 name='gaopeng1' 明顯不應該使用索引，我們再來看看trace
T@3: | | | | | | | | | | | opt: ranges: ending struct
T@3: | | | | | | | | | | | opt: index_dives_for_eq_ranges: 0
T@3: | | | | | | | | | | | opt: rowid_ordered: 1
T@3: | | | | | | | | | | | opt: using_mrr: 0
T@3: | | | | | | | | | | | opt: index_only: 0
T@3: | | | | | | | | | | | opt: rows: 5
T@3: | | | | | | | | | | | opt: cost: 7.01
index_dives_for_eq_ranges: 0 rows: 5這個5就是10*1/2導致的,而index_dives_for_eq_ranges=0就是禁用了

到此，相信大家對“MYSQL5.6 5.7處理數(shù)據(jù)分布不均的問題分析”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續(xù)學習！