這篇文章帶大家深入解析一下MySQL中的查詢優化器，帶大家理解一下mysql查詢優化器工作原理，希望對大家有幫助！
對於一個SQL語句，查詢優化器先看是不是能轉換成JOIN，再將JOIN進行最佳化
優化分成：1. 條件最佳化，2.計算全表掃描成本，3. 找出所有能用到的索引，4. 針對每個索引計算不同的訪問方式的成本，5. 選出成本最小的索引以及訪問方式
-- 开启 set optimizer_trace="enabled=on"; -- 执行sql -- 查看日志信息 select * from information_schema.OPTIMIZER_TRACE; -- 关闭 set optimizer_trace="enabled=off";
1、常數傳遞（ constant_propagation）
a = 1 AND b > a
上面這個sql可以轉換成：
a = 1 AND b > 1
2、等值傳遞（equality_propagation）
a = b and b = c and c = 5
上面這個sql可以轉換為：
a = 5 and b = 5 and c = 5
3、移除沒用的條件（trivial_condition_removal）
a = 1 and 1 = 1
上面這個sql可以轉換成：
a = 1
#4、基於成本
一個查詢可以有不同的執行方案，可以選擇某個索引進行查詢，也可以選擇全表掃描，查詢最佳化器會選擇其中成本最低的方案去執行查詢。
1）I/O成本
InnoDB儲存引擎都是將資料和索引都儲存到磁碟上的，當我們想要查詢表中的記錄時，需要先把資料或索引載入到記憶體中然後再操作。這個從磁碟到記憶體這個載入的過程損耗的時間稱之為I/O成本
2）CPU成本
讀取以及偵測記錄是否滿足對應的搜尋條件、對結果集進行排序等這些操作損耗的時間稱為CPU成本。
InnoDB儲存引擎規定讀取一個頁面花費的成本預設是1.0，讀取以及偵測一筆記錄是否符合搜尋條件的成本預設是0.2。
在一條單表查詢語句真正執行之前，MySQL的查詢最佳化器會找出執行該語句所有可能使用的方案，比較之後找出成本最低的方案，這個成本最低的方案就是所謂的執行計劃，之後才會呼叫儲存引擎提供的介面真正的執行查詢。
下邊我們就以一個實例來分析一下這些步驟，單表查詢語句如下：
select * from employees.titles where emp_no > '10101' and emp_no < '20000' and to_date = '1991-10-10';
1、根據搜尋條件，找出所有可能使用的索引
• emp_no > '10101'，這個搜尋條件可以使用主鍵索引PRIMARY。
• to_date = ‘1991-10-10’，這個搜尋條件可以使用二級索引idx_titles_to_date。
綜上所述，上邊的查詢語句可能會用到的索引，也就是possible keys只有PRIMARY和idx_titles_to_date。
2、計算全表掃描的代價
#對於InnoDB儲存引擎來說，全表掃描的意思就是把叢集索引中的記錄都依序和給定的搜尋條件做一下比較，把符合搜尋條件的記錄加入到結果集，所以需要將叢集索引對應的頁面載入到記憶體中，然後再偵測記錄是否符合搜尋條件。由於查詢成本=I/O成本CPU成本，因此計算全表掃描的代價需要兩個資訊：
1）叢集索引所佔用的頁數
2）該表中的記錄數
MySQL為每個表維護了一系列的統計信息，SHOW TABLE STATUS語句來查看表的統計信息。
SHOW TABLE STATUS LIKE 'titles';
Rows
表示表中的記錄條數。對於使用MyISAM儲存引擎的表來說，該值是準確的，對於使用InnoDB儲存引擎的表來說，該值是一個估計值。
Data_length
表示表格所佔用的儲存空間位元組數。使用MyISAM儲存引擎的表來說，該值就是資料檔案的大小，對於使用InnoDB儲存引擎的表來說，該值就相當於聚集索引所佔用的儲存空間大小，也就是說可以這樣計算該值的大小：
Data_length = 聚簇索引的页面数量 x 每个页面的大小
我們的titles使用預設16KB的頁面大小，而上邊查詢結果顯示Data_length的值是20512768，所以我們可以反向來推導出聚集索引的頁面數量：
聚簇索引的页面数量 = Data_length ÷ 16 ÷ 1024 = 20512768 ÷ 16 ÷ 1024 = 1252
我們現在已經得到了聚集索引佔用的頁面數量以及該表記錄數的估計值，所以就可以計算全表掃描成本了。但是MySQL在真實計算成本時會進行一些微調。
I/O成本：12521 = 1252。1252指的是叢集索引所佔用的頁數，1.0指的是載入一個頁面的成本常數。
CPU成本：4420700.2=88414。442070指的是統計資料中表的記錄數，對於InnoDB儲存引擎來說是一個估計值，0.2指的是存取一筆記錄所需的成本常數
總成本：1252 88414 = 89666。
綜上所述，對於titles的全表掃描所需的總成本就是89666。
我们前边说过表中的记录其实都存储在聚簇索引对应B+树的叶子节点中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程其实有的B+树内节点是不需要访问的，但是MySQL在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算I/O成本的依据，是不区分内节点和叶子节点的。
3、计算PRIMARY需要成本
计算PRIMARY需要多少成本的关键问题是：需要预估出根据对应的where条件在主键索引B+树中存在多少条符合条件的记录。
范围区间数
当我们从索引中查询记录时，不管是=、in、>、<这些操作都需要从索引中确定一个范围，不论这个范围区间的索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。
本例中使用PRIMARY的范围区间只有一个：(10101, 20000)，所以相当于访问这个范围区间的索引付出的I/O成本就是：1 x 1.0 = 1.0
预估范围内的记录数
优化器需要计算索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算PRIMARY在(10101, 20000)这个范围区间中包含多少条数据记录，计算过程是这样的：
步骤1：先根据emp_no > 10101这个条件访问一下PRIMARY对应的B+树索引，找到满足emp_no > 10101这个条件的第一条记录，我们把这条记录称之为区间最左记录。
步骤2：然后再根据emp_no < 20000这个条件继续从PRIMARY对应的B+树索引中找出第一条满足这个条件的记录，我们把这条记录称之为区间最右记录。
步骤3：如果区间最左记录和区间最右记录相隔不太远（只要相隔不大于10个页面即可），那就可以精确统计出满足emp_no > '10101' and emp_no < '20000'条件的记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？计算它们父节点中对应的目录项记录之间隔着几条记录就可以了。
根据上面的步骤可以算出来PRIMARY索引的记录条数，所以读取记录的CPU成本为：26808*0.2=5361.6，其中26808是预估的需要读取的数据记录条数，0.2是读取一条记录成本常数。
PRIMARY的总成本
确定访问的IO成本+过滤数据的CPU成本=1+5361.6=5362.6
4、计算idx_titles_to_date需要成本
因为通过二级索引查询需要回表，所以在计算二级索引需要成本时还要加上回表的成本，而回表的成本就相当于下面这个SQL执行：
select * from employees.titles where 主键字段 in （主键值1，主键值2，。。。，主键值3）;
所以idx_titles_to_date的成本 = 辅助索引的查询成本 + 回表查询的成本
5、比较各成本选出最优者
选择成本最小的索引
有时候使用索引执行查询时会有许多单点区间，比如使用IN语句就很容易产生非常多的单点区间，比如下边这个查询：
select * from employees.titles where to_date in ('a','b','c','d', ..., 'e');
很显然，这个查询可能使用到的索引就是idx_titles_to_date，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。计算方式我们上边已经介绍过了，就是先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index pe。
如果只有几个单点区间的话，使用index pe的方式去计算这些单点区间对应的记录数也不是什么问题，可是如果很多呢，比如有20000次，MySQL的查询优化器为了计算这些单点区间对应的索引记录条数，要进行20000次index pe操作，那么这种情况下是很耗性能的，所以MySQL提供了一个系统变量eq_range_index_pe_limit，我们看一下这个系统变量的默认值：SHOW VARIABLES LIKE ‘%pe%’;为200。
也就是说如果我们的IN语句中的参数个数小于200个的话，将使用index pe的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index pe了，要使用所谓的索引统计数据来进行估算。像会为每个表维护一份统计数据一样，MySQL也会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法。
Cardinality属性表示索引列中不重复值的个数。比如对于一个一万行记录的表来说，某个索引列的Cardinality属性是10000，那意味着该列中没有重复的值，如果Cardinality属性是1的话，就意味着该列的值全部是重复的。不过需要注意的是，对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的。可以根据这个属性来估算IN语句中的参数所对应的记录数：
1）使用SHOW TABLE STATUS展示出的Rows值，也就是一个表中有多少条记录。
2）使用SHOW INDEX语句展示出的Cardinality属性。
3）根据上面两个值可以算出idx_key1索引对于的key1列平均单个值的重复次数：Rows/Cardinality
4）所以总共需要回表的记录数就是：IN语句中的参数个数*Rows/Cardinality。
NULL值处理
上面知道在统计列不重复值的时候，会影响到查询优化器。
对于NULL，有三种理解方式：
NULL值代表一个未确定的值，每一个NULL值都是独一无二的，在统计列不重复值的时候应该都当作独立的。
NULL值在业务上就是代表没有，所有的NULL值代表的意义是一样的，所以所有的NULL值都一样，在统计列不重复值的时候应该只算一个。
NULL完全没有意义，在统计列不重复值的时候应该忽略NULL。
innodb提供了一个系统变量：
show global variables like '%innodb_stats_method%';
这个变量有三个值：
nulls_equal：认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
nulls_unequal：认为所有NULL值都是不相等的。如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
nulls_ignored：直接把NULL值忽略掉。
最好不在索引列中存放NULL值才是正解
InnoDB提供了两种存储统计数据的方式：
• 统计数据存储在磁盘上。
• 统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了。
MySQL给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中。
不过InnoDB默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式。
SELECT * FROM table1 USE|IGNORE|FORCE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3
【相关推荐：mysql视频教程】
以上是深入解析MySQL中的查詢優化器（工作原理詳解）的詳細內容。更多資訊請關注PHP中文網其他相關文章！
