深入浅出MYSQL查询索引失效

mysql索引原理

B+树

索引优缺点

优点

  • 大大减少了服务器需要扫描的数据量
  • 可以帮助服务器避免排序或减少使用临时表排序
  • 索引可以随机I/O变为顺序I/O

缺点

  • 需要占用磁盘空间，因此冗余低效的索引将占用大量的磁盘空间
  • 降低DML性能，对于数据的任意增删改都需要调整对应的索引，甚至出现索引分裂
  • 索引会产生相应的碎片，产生维护开销

索引失效场景

  1. 字符串型字段，比较时用了整形
  2. like的通配符在前
  3. 查询条件中使用了or，但是没有把or中所有字段加上索引
  4. 对索引列进行函数运算
  5. 联合索引abc问题
    Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。
    例如索引是index (a,b,c)，可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c或c进行查找
    最左原则是指：mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，所以这两条都是会命中索引的。要记住和顺序是无关的。
  6. 使用i查询时，in查询条件超过数据库表的一半
  7. where条件中使用NOT IN
  8. where 语句中使用 is null 或者 is not null，当查询量达到总表的30%以上时
  9. 如果排序使用了索引，而select列未使用索引列，则该索引失效
    这是因为优化器执行直接执行全表扫描速度更快。主键索引除外，任何一张表都有一个唯一索引primary,索引列为主键列。

对创建索引的一些技巧总结

  1. 首先数据量小的表不需要建立索引
    因为数据量小的表即使建立索引也不会有大的用处，还会增加额外的索引开销 。
  2. 不经常引用的列不要建立索引，因为不常用，即使建立了索引也没有多大意义
  3. **经常频繁更新的列不要建立索引，因为肯定会影响插入或更新的效率 **
  4. 尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描
  5. 数据类型越小越简单的索引更好
    越小越简单的数据类型通常在磁盘、内存和cpu缓存中需要的空间更少，处理起来更快。
  6. 字段内容尽量避免NULL
    在组合索引中某一索引列有null值，则索引失效。这句话其实是不对的，在单列索引中索引列有null值不会失效。在组合索引中索引列有null值也是可以使用组合索引的，MySQL难以优化引用了可空列的查询，它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。当可空列被索引的时候，每条记录都需要一个额外的字节，还可能导致 MyISAM 中固定大小的索引(例如一个整数列上的索引)变成可变大小的索引，所以尽量避免null值。
    SQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。可以采用0、一个特殊的值或者一个空串代替空值 。
  7. 有大量重复的列不要创建索引。
    mysql查询结果要小于30%才会使用索引，不然会使用全表扫描。mysql优化器认为全表扫描的成本小于索引，所以放弃索引，这是很多情况下没使用索引的原因。具有唯一性或者重复性很少的列建立索引会非常有效。
  8. 减少索引长度
    设置索引时可能的话应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

在实际应用的过程中，mysql并不总会选择合理的索引进行查询，此时便可以使用force index(index name)来强制告诉mysql选择哪一个索引。使用一下sql查询：

select * from t_test3 force INDEX (t_test_name) where name in ('a','b');

其对应的执行计划与上图的执行计划相同，采用的是sql中指定的索引。
因此我们在一些情况下首先可以适当的使用force index(indexname) 强制告诉mysql使用什么索引。force index( index name )指令可以指定本次查询使用哪个索引！一条sql只会用到一个索引，mysql优化器会计算出一个合适的索引，但是这个索引不一定是最好的。force index()指令可以避免MySql优化器用到了一个低效的索引，并可以提高sql的执行效率。

终极优化思路：干掉join，优化业务逻辑

参考：mysql的索引及索引误区详解