深入浅出MYSQL查询索引失效

最新推荐文章于 2024-05-11 11:00:05 发布

manitod

最新推荐文章于 2024-05-11 11:00:05 发布

阅读量1.1k

点赞数

分类专栏： Mysql 文章标签： mysql 数据库服务器

本文链接：https://blog.csdn.net/manitod/article/details/122458313

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

深入浅出MYSQL查询索引失效

mysql索引原理

B+树

索引优缺点

优点

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

缺点

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

索引失效场景

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

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

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

设置索引时可能的话应该指定一个前缀长度。例如，如果有一个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的索引及索引误区详解