Mysql條件字段有索引,但使用不了索引的場景

對于 MySQL 而言，如果需要查找某一行的值，可以先通過索引找到對應(yīng)的值，然后根據(jù)索引匹配的記錄找到需要查詢的數(shù)據(jù)行。然而，有時會發(fā)現(xiàn)，即使查詢條件有索引，也會查詢很慢；

下面會講解幾種有索引但是查詢不走索引導(dǎo)致查詢慢的場景。

一、前期準(zhǔn)備

drop table if exists t1;        /* 如果表t1存在則刪除表t1 */

CREATE TABLE `t1` (             /* 創(chuàng)建表t1 */
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(20) DEFAULT NULL,
  `b` int(20) DEFAULT NULL,
  `c` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`) USING BTREE,
  KEY `idx_b` (`b`) USING BTREE,
  KEY `idx_c` (`c`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

drop procedure if exists insert_t1; /* 如果存在存儲過程insert_t1，則刪除 */
delimiter ;;
create procedure insert_t1()        /* 創(chuàng)建存儲過程insert_t1 */
begin
  declare i int;                    /* 聲明變量i */
  set i=1;                          /* 設(shè)置i的初始值為1 */
  while(i<=10000)do                 /* 對滿足i<=10000的值進行while循環(huán) */
    insert into t1(a,b) values(i,i);  /* 寫入表t1中a、b兩個字段，值都為i當(dāng)前的值 */
    set i=i+1;                        /* 將i加1 */
  end while;
end;;
delimiter ;
call insert_t1();                    /* 運行存儲過程insert_t1 */

update t1 set c = '2019-05-22 00:00:00';  /* 更新表t1的c字段，值都為'2019-05-22 00:00:00' */
update t1 set c = '2019-05-21 00:00:00' where id=10000;	 /* 將id為10000的行的c字段改為與其它行都不一樣的數(shù)據(jù)，以便后面實驗使用 */

二、函數(shù)操作

在使用 MySQL 查詢數(shù)據(jù)時，可能很多時候會借助一些函數(shù)實現(xiàn)查詢。有時可能我們關(guān)注的重心在是否能查出結(jié)果，往往忽略了查詢的效率；

對于上面創(chuàng)建的測試表，比如要查詢測試表 t1 單獨某一天的所有數(shù)據(jù)，SQL如下：

結(jié)果如下所示：

type 為 ALL，key 字段結(jié)果為 NULL，因此知道該 SQL 是沒走索引的全表掃描；

結(jié)論一：對條件字段做函數(shù)操作走不了索引；

如果需要優(yōu)化的話，改成 c 字段實際值相匹配的形式。因為 SQL 的目的是查詢 2019-05-21 當(dāng)天所有的記錄，因此可以改成范圍查詢，結(jié)果如下所示：

類似求某一天或者某一個月數(shù)據(jù)的需求，建議寫成類似上例的范圍查詢，可讓查詢能走索引。避免對條件索引字段做函數(shù)處理；

三、隱式轉(zhuǎn)換

隱式轉(zhuǎn)換：當(dāng)操作符與不同類型的操作對象一起使用時，就會發(fā)生類型轉(zhuǎn)換以使操作兼容。

某些轉(zhuǎn)換是隱式的；更多信息可以參考官網(wǎng)：MySQL :: MySQL 5.7 Reference Manual :: 12.3 Type Conversion in Expression Evaluation

隱式轉(zhuǎn)換估計是很多 MySQL 使用者踩過的坑，比如聯(lián)系方式字段。由于有時電話號碼帶加、減等特殊字符，有時需要以 0 開頭，因此一般設(shè)計表時會使用 varchar 類型存儲，并且會經(jīng)常做為條件來查詢數(shù)據(jù)，所以會添加索引；

比如我們要查詢 a 字段等于 1000 的值， 仔細對比下面兩個查詢：

a 字段類型是 varchar(20)，而語句中 a 字段條件值沒加單引號，導(dǎo)致 MySQL 內(nèi)部會先把a轉(zhuǎn)換成int型，再去做判斷，再次印證了結(jié)論一：對索引字段做函數(shù)操作時，優(yōu)化器會放棄使用索引；

所以建議在寫SQL時，先看字段類型，然后根據(jù)字段類型寫SQL；

四、模糊查詢

很多時候我們想根據(jù)某個字段的某幾個關(guān)鍵字查詢數(shù)據(jù)，比如會有如下 SQL：結(jié)果如下圖所示：

模糊查詢優(yōu)化建議：修改業(yè)務(wù)，讓模糊查詢必須包含條件字段前面的值；如果條件只知道中間的值，需要模糊查詢?nèi)ゲ?，那就建議使用ElasticSearch或其它搜索服務(wù)器。

優(yōu)化后結(jié)果如下：

五、范圍查詢

拿測試表舉例，比如要取出b字段1到3000范圍數(shù)據(jù)，SQL 如下 ：

結(jié)論二：單次查詢的數(shù)據(jù)量過大，優(yōu)化器將不走索引，優(yōu)化范圍查詢：降低單次查詢范圍，分多次查詢：

實際這種范圍查詢而導(dǎo)致使用不了索引的場景經(jīng)常出現(xiàn)，比如按照時間段抽取全量數(shù)據(jù)，每條SQL抽取一個月的；或者某張業(yè)務(wù)表歷史數(shù)據(jù)的刪除。遇到此類操作時，應(yīng)該在執(zhí)行之前對SQL做explain分析，確定能走索引，再進行操作；

六、計算操作

有時我們與有對條件字段做計算操作的需求，在使用 SQL 查詢時，就應(yīng)該小心了；

優(yōu)化后結(jié)果：

結(jié)論三：一般需要對條件字段做計算時，建議通過程序代碼實現(xiàn)，而不是通過MySQL實現(xiàn)。如果在MySQL中計算的情況避免不了，那必須把計算放在等號后面

總結(jié)

應(yīng)該避免隱式轉(zhuǎn)換、like查詢不能以%開頭，范圍查詢時，包含的數(shù)據(jù)比例不能太大，不建議對條件字段做運算及函數(shù)操作；

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論