> # 索引失效的场景、索引优化方案 > ### 索引失效的场景 - 字段类型不同(隐式类型转换): 给一个字符串字段加索引, where 字段=123456 时候, 字段是 varchar(255), 而输入的参数是整型,需要类型转换导致全表扫描(类型符合转换规则还是可以使用的 不同位数的类型互相转换) - 表字符集不同(隐式字符编码转换): 通过字段关联两种表时, 一张表是utf8, 另一张表是utf8mb4也会导致全表扫描 - 字段使用了函数或表达式 where ABS(字段) = 7 , where 字段+1 > 9 - !=,<>,is null,is not null不会走索引 - 条件中带有or, or中的每个列都有索引时才有用（要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引 ）（每一个or单处建索引，查询的使用索引合并，用到多列索引） - 不符合最左匹配原则, 建立了组合索引, where 时没出现前面的索引前面的列 - 在进行范围查询（比如>、=、<=、in等条件）时，当Mysql发现通过索引扫描的行记录数超过全表的10%-30%时，优化器可能会放弃走索引，自动变成全表扫描 - like (like 'XXX%' 符合最左匹配原则可以用到索引， like '%XXX', '%XXX%' 无法使用索引) - 查询数据量大的时候，导致优化器认为走全表查询时间效率更佳 - NOT条件 - 我们知道建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists > ### 索引优化方案 - 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一直）），减少select \* - 索引列字符很长,会让索引变大和慢, 这时可以通过前缀或后缀去优化(有时也会用到字段倒序存储, Hash字段存储) - 创建组合索引时, 把第一时间过滤完后数据量少的字段放在前面 - 组合索引a,b, 在建立a索引是冗余, 建立b索引不是, 组合索引a,b, 中的b是一个个比对 - 唯一索引比普通索引差, 如果业务在保存数据时能确保唯一就采用普通索引 - 查询全表字段时, select 字段名 会比 select * 快 - 优化器选择错误索引时, 强制优化器使用某个索引 : select * from table force index(索引名称) - 聚簇索引的主键长度小, 有序自增, 避免修改和删除 - 最左匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、like、between),就停止匹 - 主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响 - 分页数据如何优化? - 取出来数据的ID是有序自增, 可以通过代码算出每一页的ID范围, 然后 select * from table where id bwteen 范围1, 范围2 - 取出来的数据是 ID无序, 可以先 select id from table limit xxx,xxx 然后 select * from table where id in (id值), 这样两次查询都能用到索引 - [MySql千万级limit优化方案](https://blog.csdn.net/sunct/article/details/90441965) - order by 优化 - 没有order by时, 执行SQL返回的数据顺序, 会按照使用到的索引排序返回, 需要回表的话会用主键排序返回 - [MySQL中order by 原理](https://www.szhulian.com/new/1646.html) - [Mysql学习之order by的工作原理](https://www.cnblogs.com/lamp01/p/10770172.html) - 按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(\*) - count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。 - 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。