mysql索引优化

在讨论索引优化前，需要先了解一下mysql索引失效的场景

• 在where子句中使用not、 != 或 <> 操作符，如数字列需要判断不等于，将a<>0 改为 a>0 or a<0
• 在where子句中使用数据库函数，如where day(create_time)=...
• 数据类型出现隐式转化，如varchar不加单引号的话可能会自动转换为int型，会使索引无效，产生全表扫描
• join操作虽然两张表都建立了索引，但类型不同，如A表age字段为int类型，B表age字段为varchar类型
• like左模糊匹配，如 user_name like '%zhang'，需要使用右模糊查询，如 user_name like 'zhang%'
• or语句前后没有同时使用索引，如where id = 12 or code = 'xxx'，id与code必须都建立索引
• 在索引列上使用IS NULL 或IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可
• where条件单独引用复合索引里非第一位置的索引列

where条件顺序对性能的影响

-- 假设a=3有1万条记录， b='x'有5条记录
select * from user where a = 3 and b ='x';

• 如果a和b都没有建立索引，那么一般情况下where b ='x' and a = 3效率更高。特别注意，如果b字段存放的字符较长(大文本)，此时先查b需要全量比较大文本，即使a筛选后的粒度只降低一半也比先查b效率高。对于较长字段进行精确查询，一般我们可以增加对应的md5列并建立索引，查询时，计算md5后匹配md5列。
• 如果a和b至少有一个建立了索引，那么where条件的顺序对效率没有影响
• 如果a和b是联合索引，where条件的顺序需要和索引建立的顺序一致，这样效率更高

limit海量数据对分页性能的影响

场景描述：

-- 很快
select * from user limit 0, 10 
-- 很慢
select * from user limit 1000000, 10 

分析

limit1000000,10的意思扫描满足条件的1000010行，扔掉前面的1000000行，返回最后的10行，导致查询缓慢

优化方案

select * from user where (id >= 1000000) limit 10;
select * from user where (id >= (select id from user limit 1000000, 1)) limit 10;

需要使用自增索引进行大于小于过滤，不够灵活，改进方案使用inner join

SELECT * FROM user a inner join (SELECT id from user limit 1000000, 10)b on a.id = b.id;
-- 或者
SELECT * FROM user a, (SELECT id from user limit 1000000, 10)b where a.id = b.id;

order by 对性能的影响

order by的字段需要建立索引，如果有多个字段需要order by，需要建立联合索引，且索引中字段的顺序需要与order by中字段的顺序一致
以下四种场景order by索引将起作用，其中key_part表示联合索引中的一部分，key表示单独建立索引的字段，constant为常量

-- 如果select 的结果集包括非索引字段，order by索引将不起作用，如select * ...将不起作用
1. SELECT key_part1,key_part2,... FROM t1 ORDER BY key_part1,key_part2,... ; 
2. SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
-- 与第一种类似，select 的结果集包括非索引字段，order by索引将不起作用
3. SELECT key_part1,key_part2,...  FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
4. SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; 

特别的，以下场景，索引不起作用

-- 虽然key1，key2都分别建立了索引，但order by却无法使用索引
1. SELECT * FROM t1 ORDER BY key1, key2; 
-- 如果联合索引中的排序方向不一致，那么order by无法使用索引
2. SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
-- order by 单个索引列不起作用，但是如果order by的是主键id，索引会起作用
3. SELECT * FROM t1 ORDER BY key1 ; 

参考： mysql中order by优化的那些事儿

以上，如有错误，非常迫切希望能留言指正。