快捷導(dǎo)航

MySql范圍查找時索引不生效問題的原因分析

更新時間：2020年12月14日 11:22:35 作者：qq_25188255

這篇文章主要給大家介紹了關(guān)于MySql范圍查找時索引不生效問題的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

1 問題描述

本文對建立好的復(fù)合索引進(jìn)行排序,并取記錄中非索引字段，發(fā)現(xiàn)索引不生效，例如，有如下表，DDL語句為：

CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` enum('M','F') NOT NULL,
 `hire_date` date NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`emp_no`),
 KEY `unique_birth_name` (`first_name`,`last_name`) USING BTREE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

復(fù)合索引為unique_birth_name (first_name,last_name) 。使用以下語句：

EXPLAIN SELECT
 gender
FROM
 employees
ORDER BY
 first_name,
 last_name

這里寫圖片描述

根據(jù)上圖：type:all 及 Extra:Using filesort 可得，索引沒有生效。

繼續(xù)進(jìn)行試驗(yàn)，對查詢語句進(jìn)一步改寫，加上一個范圍查找：

EXPLAIN SELECT
 gender
FROM
 employees
WHERE first_name > 'Leah'
ORDER BY
 first_name,
 last_name

執(zhí)行計劃顯示如下圖：

這里寫圖片描述

這里發(fā)現(xiàn)結(jié)果和第一次sql分析無異。繼續(xù)試驗(yàn)。

改寫sql語句：

EXPLAIN SELECT
 gender
FROM
 employees
WHERE first_name > 'Tzvetan'
ORDER BY
 first_name,
 last_name

這里寫圖片描述

此時，令人驚訝的是，索引生效了。

2 問題分析

此時，我們做一個大膽的猜測：

第一次進(jìn)行sql分析時，因?yàn)榈谝淮蝟rder by 后，得到的還是全表數(shù)據(jù)，如果根據(jù)復(fù)合索引中攜帶的主鍵查找每一個gender進(jìn)行拼接，自然很費(fèi)資源和時間，mysql不會做如此蠢的事。不如直接進(jìn)行全表掃描，把掃描到的每條數(shù)據(jù)和order by得到的臨時數(shù)據(jù)進(jìn)行拼接，從而得到需要的數(shù)據(jù)。

為了驗(yàn)證上述想法的正確性，我們對三次sql進(jìn)行分析。

第一次sql根據(jù)復(fù)合索引得到的數(shù)據(jù)量為：300024，為全表數(shù)據(jù)

SELECT
 COUNT(first_name)
FROM
 employees
ORDER BY
 first_name,
 last_name

這里寫圖片描述

第二次改寫的sql根據(jù)復(fù)合索引得到的數(shù)據(jù)量為：159149 ，為全表數(shù)據(jù)量的1/2。

SELECT
 COUNT(first_name)
FROM
 employees
WHERE first_name > 'Leah'
ORDER BY
 first_name,
 last_name

這里寫圖片描述

第三次改寫的sql根據(jù)復(fù)合索引得到的數(shù)據(jù)量為：36731，為全表數(shù)據(jù)量的1/10。

SELECT
  COUNT(first_name)
FROM
  employees
WHERE first_name > 'Tzvetan'
ORDER BY
  first_name,
  last_name

這里寫圖片描述

通過對比發(fā)現(xiàn)，第二次改寫的sql根據(jù)復(fù)合索引得到的數(shù)據(jù)量是全表數(shù)據(jù)量的1/2。此時還沒有達(dá)到mysql使用索引進(jìn)行二次查找的量級。第三次改寫的sql根據(jù)復(fù)合索引得到的數(shù)據(jù)量是全表數(shù)據(jù)量的1/10，達(dá)到了mysql使用索引進(jìn)行二次查找的量級，于是從執(zhí)行計劃上可以看到，第三次改寫sql是走了索引的。