Mysql條件字段有索引,但使用不了索引的場(chǎng)景

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

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

一、前期準(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; /* 如果存在存儲(chǔ)過(guò)程insert_t1，則刪除 */
delimiter ;;
create procedure insert_t1()        /* 創(chuàng)建存儲(chǔ)過(guò)程insert_t1 */
begin
  declare i int;                    /* 聲明變量i */
  set i=1;                          /* 設(shè)置i的初始值為1 */
  while(i<=10000)do                 /* 對(duì)滿(mǎn)足i<=10000的值進(jìn)行while循環(huán) */
    insert into t1(a,b) values(i,i);  /* 寫(xiě)入表t1中a、b兩個(gè)字段，值都為i當(dāng)前的值 */
    set i=i+1;                        /* 將i加1 */
  end while;
end;;
delimiter ;
call insert_t1();                    /* 運(yùn)行存儲(chǔ)過(guò)程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í)驗(yàn)使用 */

二、函數(shù)操作

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

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

結(jié)果如下所示：

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

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

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

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

三、隱式轉(zhuǎn)換

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

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

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

比如我們要查詢(xún) a 字段等于 1000 的值， 仔細(xì)對(duì)比下面兩個(gè)查詢(xún)：

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

所以建議在寫(xiě)SQL時(shí)，先看字段類(lèi)型，然后根據(jù)字段類(lèi)型寫(xiě)SQL；

四、模糊查詢(xún)

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

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

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

五、范圍查詢(xún)

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

結(jié)論二：?jiǎn)未尾樵?xún)的數(shù)據(jù)量過(guò)大，優(yōu)化器將不走索引，優(yōu)化范圍查詢(xún)：降低單次查詢(xún)范圍，分多次查詢(xún)：

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

六、計(jì)算操作

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

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

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

總結(jié)

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

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

最新評(píng)論