【mysql】索引优化总结

在工作中我们常常会使用到查询的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后出现

        

总结:索引优化的经验需要工作中不断的积累,本文有错误或者不足之处请告知,我愿意积极改正,和大家一起学习进步