在工作中我们常常会使用到查询的sql语句,在大数据量级的查询下为了提高查询sql的速度,给表添加索引是必须的,但是没有添加在合适的地方的话,失效是在所难免的,今天在这里给大家分享一下我个人的编写sql和索引优化的一点小心得.
索引介绍:
单值索引:一个索引只为一个列或者字段建立的索引叫做单值索引
复合索引:为多个列建立的索引称为复合索引
唯一索引:通常每个表的主键为该表的唯一索引
索引失效的注意事项:
1.复合索引的使用一定是从建立索引时列的顺序开始,1,2,3...排序,使用时1不能少,第一位索引没有,则后面索引失效,或者第一位索引存在,后面紧接第三位索引,通常从第一位索引后开始索引失效,最好的状态是三个使用都能够使用到,也就是能够全职匹配
下图是该表的索引情况
下图是该表的索引使用情况,因为没有使用第一位索引,而直接使用第二位导致,该sql全表扫描,为使用索引( 不懂explain的可以自行学习,本文中不做介绍,后期给大家分享)
2.不允许在索引列上做任何的函数或者类型转换操作,否则会导致索引失效
3.尽量避免在sql中使用范围查找,如使用范围查找极大可能会导致,范围查找后的缩小失效
下图是未使用范围查找的索引使用情况,改索引全职匹配
下图是使用范围查找后索引的使用情况,该sql,只使用到第一位索引,第二位索引失效
4.尽量使用覆盖索引,什么是覆盖索引呢,也就是在select 后面的查询列尽量和建立的索引列一致,查询列只能比索引列少,不能多,一旦查询列比索引列多,则无法使用覆盖索引
下图的索引本该无效,但是因为使用了覆盖索引,使得即使使用了<>返回查询,索引依然被使用
5.mysql在使用了 != 或者<> 后索引容易失效
6.mysql使用 is null 或者 is not null 也会导致索引失效
7.mysql的sql查询中,字符串数据不加单引号索引也会失效,上面已经提到过了,索引列不允许做函数和数据类型转换,字符串不加单引号虽然也可以使用,那是因为mysql在底层为你做了数据类型转换
8.like 以 "%123" 通配符开头的sql,like后面的索引将会失效,而如果%不放在第一位,比如"123%",索引失效的概率较小.
9.少用or,用它来连接时索引为失效
表连接时,索引的使用情况
1.当两张表使用左连接时,索引应该尽量建立在右边中,因为左连接左边的表示必须全部显示出来的,也就是说左边的表必须是全表扫描,所以左连接索引最好建立在右边中,反之右连接也是如此
2.多表连接的话,有左连接永远先加右边的索引
group by 和 order by 索引排序:
如果对某一个列进行排序或者分组,且排序或者分组的不是第一位索引,那么第一位的索引需要出现在where后面,且不能是范围查询,这样排序或者分组的索引才能够生效,单独使用某一个索引进行排序极大可能会出现失效的情况
下图便是索引排序,第一位索引在where后出现
总结:索引优化的经验需要工作中不断的积累,本文有错误或者不足之处请告知,我愿意积极改正,和大家一起学习进步