快捷導(dǎo)航

mysql不走索引的幾個(gè)問(wèn)題小結(jié)

更新時(shí)間：2023年08月14日 10:49:29 作者：風(fēng)破冬

MySQL中不走索引的問(wèn)題通常發(fā)生在查詢(xún)中使用了函數(shù),這會(huì)使索引失效,從而影響查詢(xún)性能,本文就介紹了mysql不走索引的幾個(gè)問(wèn)題小結(jié),感興趣的可以了解一下

一、類(lèi)型不匹配導(dǎo)致不走索引

這類(lèi)問(wèn)題往往是因?yàn)閿?shù)據(jù)定義與使用上面的偏差，比如工號(hào)，定義成varchar，然而用的時(shí)候又不講工號(hào)打上引號(hào)

創(chuàng)建表代碼舉例如下：

----創(chuàng)建表：
CREATE TABLE `test_a`(
    `id` int(11) NOT NULL,
    `work_no` varchar(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_work_no`(`work_no`)
)ENGINE=InnoDB;

查詢(xún)條件對(duì)比如下：

----查詢(xún)工號(hào)，執(zhí)行一下-----
explain SELECT * from test_a where work_no=1000;
explain  SELECT * from test_a where work_no="1000";

結(jié)論，可以自己運(yùn)行一下代碼，會(huì)發(fā)現(xiàn)上面的因?yàn)槎x成了varchar，但是用的時(shí)候又在把它當(dāng)int用，沒(méi)有加引號(hào)，導(dǎo)致無(wú)法走索引

二、索引用錯(cuò)的問(wèn)題

這個(gè)是索引用錯(cuò)的問(wèn)題，創(chuàng)建表及初始化數(shù)據(jù)代碼如下：

----創(chuàng)建表：
CREATE TABLE `test_t`(
    `id` int(11) NOT NULL,
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_a`(`a`),
    KEY`index_b`(`b`)
)ENGINE=InnoDB;
-----定義初始化10w數(shù)據(jù)函數(shù)：
delimiter;;
create procedure idata()
begin
    declare i int;
    set i=1;
    while(i<=100000) do
    insert into test_t values(i,i,i);
    set i=i+1;
    end while;
end;;
delimiter;
call idata();
----刪除存儲(chǔ)過(guò)程
drop procedure idata;

相關(guān)查詢(xún)對(duì)比：

----查詢(xún)條件
explain select * from test_t where (a BETWEEN 1 and 1000) and (b BETWEEN 50000 and 100000) order by b limit 1;
explain select * from test_t force index(index_a) where (a BETWEEN 1 and 1000) and (b BETWEEN 50000 and 100000) order by b limit 1;

結(jié)論，最終出現(xiàn)第一個(gè)走錯(cuò)了索引，導(dǎo)致查詢(xún)時(shí)候會(huì)變長(zhǎng)的問(wèn)題。

而下面的我們加強(qiáng)制走索引，會(huì)發(fā)現(xiàn)執(zhí)行掃描行數(shù)要少很多，主要原因還是發(fā)生在order上

如果做下面的修改不強(qiáng)制走索引也能夠走正確的索引就是order by b, a。同時(shí)放上去也是有效的

三、條件字段為函數(shù)的操作導(dǎo)致不走索引

函數(shù)作用在索引字段上導(dǎo)致不走索引，其實(shí)感覺(jué)mysql盡量少用函數(shù)，雖然提供了，往往還是占用的mysql自己的資源做的計(jì)算。mysql數(shù)據(jù)庫(kù)的資源多貴呀，物理機(jī)多便宜呀

----創(chuàng)建表：
CREATE TABLE `trade_log`(
    `id` int(11) NOT NULL,
    `created_time` datetime NOT NULL DEFAULT,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_created_time`(`created_time`)
)ENGINE=InnoDB;
-----定義初始化10w數(shù)據(jù)函數(shù)：
delimiter;;
create procedure c_trade_log()
begin
    declare i int;
    set i=1;
    while(i<=100000) do
    insert into trade_log values(i,from_unixtime(1539123415 + 1000*i),i);
    set i=i+1;
    end while;
end;;
delimiter;
call c_trade_log();
----刪除存儲(chǔ)過(guò)程
DROP PROCEDURE c_trade_log

查詢(xún)條件：

explain SELECT count(*) from trade_log where month(created_time)=7;
SELECT count(*) from trade_log where month(created_time)=7;
-----優(yōu)化-----
explain SELECT count(*) from trade_log where created_time BETWEEN "2018-07-01" and "2018-08-01"
or created_time BETWEEN "2019-07-01" and "2019-08-01"
or created_time BETWEEN "2020-07-01" and "2020-08-01"
or created_time BETWEEN "2021-07-01" and "2021-08-01" ;

四、錯(cuò)誤計(jì)算不走索引

查詢(xún)條件：繼續(xù)沿用上面的trade_log表

索引字段做減法在等號(hào)前，與等號(hào)后的區(qū)別

--不走索引
explain SELECT * from trade_log where id -1000=3030300
--走索引
explain SELECT * from trade_log where id =3030300-1000

五、in后面的公式

繼續(xù)使用test_t表

--不走索引 因?yàn)橛衜ax，不要究竟這兒為什么要加max公式，主要是為了突出這兒不走索引給的一個(gè)例子
explain select * FROM test_t WHERE id IN
( SELECT max(id) FROM test_t WHERE a BETWEEN 1000 and 2000 GROUP BY a )
--走索引
explain select * FROM test_t WHERE id IN
( SELECT id FROM test_t WHERE a BETWEEN 1000 and 2000 GROUP BY a )

不走索引的結(jié)果：

走索引的結(jié)果：

其他：慢sql相關(guān)配置開(kāi)啟命令

----慢查詢(xún)開(kāi)啟情況---
show variables like "slow_query_log";
----設(shè)置開(kāi)啟----
set global slow_query_log = "ON";
---慢查詢(xún)?nèi)罩镜刂?--
show variables like "slow_query_log_file";

到此這篇關(guān)于mysql不走索引的幾個(gè)問(wèn)題小結(jié)的文章就介紹到這了,更多相關(guān)mysql不走索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: