目录
1、隐式类型转换&Or条件导致索引失效案例
1.1 问题描述
1.2 优化思路
1.3 解决方案
2、left join导致使用了大表作为驱动表生成慢sql案例
3、使用非谓词导致索引失效案例
3.1 背景
3.2 问题描述
3.3 总结
4、对索引列使用范围查询结果较多时索引失效案例
4.1 理论基础
4.2 案例分析
1、隐式类型转换&Or条件导致索引失效案例
1.1 问题描述
SELECT DISTINCT
m1.meetingid AS meetingid,
m1.starttime AS START,
m1.endtime AS END,
m1.title AS title,
j.userid AS uid,
j.type AS TYPE,
m1.cuserid AS cuserid
FROM
MEETINGINFO m1
INNER JOIN JOININFO j
ON (m1.meetingid = j.meetingid)
AND (
m1.cuserid IN (192379)
OR j.userid IN (192379)
)
AND m1.dostatus IN (2, 4)
ORDER BY m1.starttime DESC
LIMIT 0, 10
MEETINGINFO有三十万条数据，JOININFO有将近98万条数据。查询需要2.3秒。
执行计划如下：
1.2 优化思路
看到查询条件里有m1.cuserid IN (192379)，却选择了starttime这个索引来扫描m1，遂查询下该列上是否有索引：
SHOW INDEXES FROM MEETINGINFO #看到没有相关索引
再看下该列是否适合创建索引：
重复率不算高，还可以。
尝试在该列上创建索引：
CREATE INDEX ind_cuserid ON MEETINGINFO(cuserid);
发现执行计划依然没选择走该索引，原因有二：
① cuserid是varchar类型的，而sql里192379是数值类型，发生数据类型转换，会导致索引失效
② OR j.userid IN (192379)里的OR会导致无法走索引，需要用UNION ALL来代替OR。
1.3 解决方案
SELECT DISTINCT aa.meetingid,aa.start,aa.end,aa.title,aa.uid,aa.type,aa.cuserid
FROM
(SELECT
m1.meetingid AS meetingid,
m1.starttime AS START,
m1.endtime AS END,
m1.title AS title,
j.userid AS uid,
j.type AS TYPE,
m1.cuserid AS cuserid
FROM
MEETINGINFO m1
INNER JOIN JOININFO j
ON (m1.meetingid = j.meetingid)
AND (m1.cuserid IN ('192379'))
AND m1.dostatus IN (2, 4)
UNION ALL
(SELECT
m1.meetingid AS meetingid,
m1.starttime AS START,
m1.endtime AS
END,
m1.title AS title,
j.userid AS uid,
j.type AS TYPE,
m1.cuserid AS cuserid
FROM
MEETINGINFO m1
INNER JOIN JOININFO j
ON (m1.meetingid = j.meetingid)
AND j.userid IN ('192379')
AND m1.dostatus IN (2, 4)
)
) aa
ORDER BY aa.start DESC LIMIT 0,10
查询时间从2秒多降到了0.02秒。
执行计划如下：
2、left join导致使用了大表作为驱动表生成慢sql案例
参考文档：left join导致使用了大表作为驱动表生成慢sql
原始超时SQL:
SELECT v.breed,v.material,v.spec,v.factory,v.bale_num,v.warehouse_id,v.type,IFNULL(SUM(v.num),0) lock_num FROM
(
SELECT t.*,SUM(i.num)num FROM
(SELECT e.inventory_no,e.type,e.breed,e.material,e.spec,e.factory,e.bale_num,e.warehouse,e.`warehouse_id`,e.`member_id`
FROM tbl_inventory_item e WHERE e.member_id=#{memberId} GROUP BY e.`inventory_no`,e.`type`) t
LEFT JOIN `tbl_inventory_locked_info` i ON t.`inventory_no`=i.inventory_no AND t.`type`=i.inventory_type
GROUP BY t.type,t.`inventory_no`
)v
GROUP BY v.breed,v.material,v.spec,v.factory,v.bale_num,v.warehouse_id,v.type
优化后的SQL语句:
SELECT v.breed,v.material,v.spec,v.factory,v.bale_num,v.warehouse_id,v.type,IFNULL(SUM(v.num),0) lock_num FROM
(
SELECT t.*,SUM(i.num)num FROM
(SELECT e.inventory_no,e.type,e.breed,e.material,e.spec,e.factory,e.bale_num,e.warehouse,e.`warehouse_id`,e.`member_id`
FROM tbl_inventory_item e WHERE e.member_id=#{memberId} GROUP BY e.`inventory_no`,e.`type`) t
INNER JOIN `tbl_inventory_locked_info` i ON t.`inventory_no`=i.inventory_no AND t.`type`=i.inventory_type
GROUP BY t.`inventory_no`,t.type
)v WHERE v.num>0
GROUP BY v.breed,v.material,v.spec,v.factory,v.bale_num,v.warehouse_id,v.type
这里可以看到只是把Left Join 改成了Inner Join 和加上一个Where条件（业务需要）优化前SQL的执行时间是13秒，优化后SQL的执行时间是200ms,显然执行效率是指数级的增长
原因分析：当使用Left Join的时候
左表是主表：
SELECT e.inventory_no,e.type,e.breed,e.material,e.spec,e.factory,e.bale_num,e.warehouse,e.`warehouse_id`,e.`member_id`
FROM tbl_inventory_item e WHERE e.member_id=#{memberId} GROUP BY e.`inventory_no`,e.`type`
也就是上面这个子查询产生的临时表。
而Left Join的特点是以左表作为驱动表，当左表比较大的时候，那么自然循环次数也会变多，效率会下降。
再结合上面这个具体的SQL语句：
当inventory_no和type的种类比较多的时候，左表就会相对比较大，所以效率相对也会比较低。
为什么改为Inner Join就能提高效率：inner join在连接的时候，mysql会自动选择较小的表来作为驱动表，从而达到减少循环次数的目的。
但是很多人应该都看出来了，优化前和优化后的查询结果是不同的，认为这样对比是没有意义的，确实结果是不同的，但是优化前的结果集是包括优化后SQL的结果集，而我们刚好又是只需要优化后的结果集，很多人又会说，那为什么一开始不写成这样呢，我想很可能写这条SQL的主人很可能是在代码层对数据进行了处理，所以当时SQL这样写了，也没想到后面会超时。
那为什么测试的时候为什么没有测试出来：数据量的问题，测试的时候inventory_no和type种类比较少，导致左表比较小，不会超时。所以这个案例同时也提醒我们，线上数据不可预测，我们在写完SQL后要更多地不同的数据量会不会导致超时。
3、使用非谓词导致索引失效案例
3.1 背景
emp_title员工任职表，大概5万条数据，
CREATE TABLE `emp_title` (
`emp_no` int(11) NOT NULL COMMENT ' 员工编号',
`title` varchar(50) NOT NULL COMMENT '职务头衔',
`from_date` date NOT NULL COMMENT '开始日期',
`to_date` date DEFAULT NULL COMMENT '截止日期',
`status` char(1) DEFAULT NULL COMMENT '状态(1:在职 2:离职)',
PRIMARY KEY (`emp_no`),
KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='员工任职表';这里只是出于演示目的，对status建立了索引，一般对于这种区分度比较低的字段，是不能创建索引的。
3.2 问题描述
执行以下sql语句
SELECT * FROM emp_title where status != '1';执行计划如下：
执行计划说明：对表emp_title执行的访问方式type=all全表扫描，查询优化器知道有索引possible_keys=status，但是没有使用，所以key=null key_length=null ref=null，预估扫描记录行数为rows=47571条记录，通过status!=1过滤剩下filtered=50%的数据，extra=Using where说明，通过where条件进行了筛选，一般filtered不是100，则extra中一般有Using where。
3.3 总结
在索引上，避免使用not、!=、<>、!<、!>、not exists、not in、not like 等非谓词，这些非谓词会导致索引失效。
4、对索引列使用范围查询结果较多时索引失效案例
4.1 理论基础
4.2 案例分析
查询薪资在(40000,60000)之间的数据记录，salaries表定义如下：
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`),
KEY `salary` (`salary`),
CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;执行的sql如下：
SELECT * FROM salaries where salary > 40000 and salary < 60000 ;执行结果：耗时369 s
执行计划如下：
分析过程：
salary列上创建了索引，但是查询分析器分析按照salary > 40000 and salary < 60000这个条件进行查询会查询出2838426*50%的数据，数据量比较大，大于表数据量的20%，这样的数据量，如果使用了salary索引，因为要返回所有字段，使用完salary索引之后，又得访问聚簇索引进行回表，而回表的操作是进行磁盘随机访问效率极其低下，所以查询分析器就会放弃使用salary索引而改用
对聚簇索引进行全表扫描，然后对salary进行条件筛选，所以就会有filtered50% extra=Using where。
对sql进行改写，进行索引提示，告诉查询优化器使用salary索引
SELECT * FROM salaries force index (salary) where salary > 40000 and salary < 60000 ;执行结果如下：耗时 350s
执行计划如下：
总结：
查询优化器最终选择的进行全表扫描是基于最低成本选择的一个结果，只是一个预估值，并不一定，是最优的，为什么使用salary二级索引比全表扫描快的一个原因，是我的机器的硬盘是固态硬盘，如果是机械硬盘，查询分析器替我们选择的全表扫描的方案要更好。
