MySQL物理查詢最佳化技術---index dive辨析

MySQL物理查詢最佳化技術---index dive辨析

一 引子

在MySQL的官方手冊上有這麼一句話:

the optimizer can estimate the row count for each range using dives into the index or index statistics.

這是在說: 最佳化器為每一個範圍段(如“a IN (10, 20, 30)”是等值比較, 括3個範圍段實則簡化為3個單值,分別是10,20,30)估計每個範圍段（用範圍段來表示是因為MySQL的“range”掃描方式多數做的是範圍掃描，此處單值可視為範圍段的特例）中包括的元組數, 而估計方法有2種,一是dive到index中即利用索引完成元組數的估算,簡稱index dive; 二是使用索引的統計數值,進行估算.

相比這2種方式,在效果上:

1 index dive: 速度慢,但能得到精確的值（MySQL的實現是數索引對應的索引項目個數，所以精確）

2 index statistics: 速度快,但得到的值未必精確

二深入

為什麼要區分這2種方式呢?

簡單地說:

1 查詢最佳化工具使用代價估算模型計算每個計劃的代價,選擇其中代價最小的

2 單表掃描時,需要計算代價;所以單表的索引掃描也需要計算代價

3 單表的計算公式通常是:代價=元組數*IO平均值

4 所以不管是哪種掃描方式,都需要計算元組數

5 當遇到“a IN (10, 20, 30)”這樣的運算式的時候，發現a列存在索引，則需要看這個索引可以掃描到的元組數由多少而計算其索引掃描代價，所以就用到了本文提到的“index dive”、“index statistics”這2種方式。

三最佳化

MySQL據此，提供了一個參數“eq_range_index_dive_limit”，指示MySQL在這種情況下使用哪種方式。用法如下：

This variable indicates the number of equality ranges in an equality comparison condition when the optimizer should switch from using index dives to index statistics in estimating the number of qualifying rows. It applies to evaluation of expressions that have either of these equivalent forms, where the optimizer uses a nonunique index to look up col_name values:

col_name IN(val1, ..., valN)

col_name = val1 OR ... OR col_name = valN

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：