总结几个mysql不走索引的问题

一、类型不匹配导致不走索引

这类问题往往是因为数据定义与使用上面的偏差，比如工号，定义成varchar，然而用的时候又不讲工号打上引号

创建表代码举例如下：

----创建表：
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;

查询条件对比如下：

----查询工号，执行一下-----
explain SELECT * from test_a where work_no=1000;
explain  SELECT * from test_a where work_no="1000";

结论，可以自己运行一下代码，会发现上面的因为定义成了varchar，但是用的时候又在把它当int用，没有加引号，导致无法走索引

二、索引用错的问题

这个是索引用错的问题，创建表及初始化数据代码如下：

----创建表：
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数据函数：
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();
----删除存储过程
drop procedure idata;

相关查询对比：

----查询条件
 
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;

结论，最终出现第一个走错了索引，导致查询时候会变长的问题。

而下面的我们加强制走索引，会发现执行扫描行数要少很多，主要原因还是发生在order上

如果做下面的修改不强制走索引也能够走正确的索引就是order by b, a。同时放上去也是有效的

三、条件字段为函数的操作导致不走索引

函数作用在索引字段上导致不走索引，其实感觉mysql尽量少用函数，虽然提供了，往往还是占用的mysql自己的资源做的计算。mysql数据库的资源多贵呀，物理机多便宜呀

----创建表：
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数据函数：
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();
 
----删除存储过程
DROP PROCEDURE c_trade_log

查询条件：

explain SELECT count(*) from trade_log where month(created_time)=7;
 
SELECT count(*) from trade_log where month(created_time)=7;
 
-----优化-----
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" ;

四、错误计算不走索引

查询条件：继续沿用上面的trade_log表

索引字段做减法在等号前，与等号后的区别

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

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

五、in后面的公式

继续使用test_t表

--不走索引 因为有max，不要究竟这儿为什么要加max公式，主要是为了突出这儿不走索引给的一个例子
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 )

不走索引的结果：

走索引的结果： 

其他：慢sql相关配置开启命令

----慢查询开启情况---
show variables like "slow_query_log";
----设置开启----
set global slow_query_log = "ON";
 
---慢查询日志地址---
show variables like "slow_query_log_file";