Mysql索引 索引有什么用( 五 )

  • 普通索引:ALTER TABLE student ADD INDEX index_name(name);
  • 2.4 索引失效场景创建了索引并不意味着高枕无忧,在很多场景下,索引会失效 。下面列举了一些导致索引失效的情形,是我们写SQL语句时应尽量避免的 。
    1、条件字段原因
    • 单字段有索引,WHERE条件使用多字段(含带索引的字段),例如 SELECT * FROM student WHERE name ='张三' AND addr = '北京市'语句,如果name有索引而addr没索引,那么SQL语句不会使用索引 。
    • 多字段索引,违反最佳左前缀原则 。例如,student表如果建立了(name,addr,age)这样的索引,WHERE后的第一个查询条件一定要是name,索引才会生效 。
    2、<>、NOT、in、not exists
    当查询条件为等值或范围查询时,索引可以根据查询条件去找对应的条目 。否则,索引定位困难(结合我们查字典的例子去理解),执行计划此时可能更倾向于全表扫描,这类的查询条件有:<>、NOT、in、not exists
    3、查询条件中使用OR
    如果条件中有or,即使其中有条件带索引也不会使用(因此SQL语句中要尽量避免使用OR) 。要想使用OR,又想让索引生效,只能将OR条件中的每个列都加上索引 。
    4、查询条件使用LIKE通配符
    SQL语句中,使用后置通配符会走索引,例如查询姓张的学生(SELECT * FROM student WHERE name LIKE '张%'),而前置通配符(SELECT * FROM student WHERE name LIKE '%东')会导致索引失效而进行全表扫描 。
    5、索引列上做操作(计算,函数,(自动或者手动)类型装换)
    有以下几种例子:
    • 在索引列上使用函数:例如select * from student where upper(name)='ZHANGFEI';会导致索引失效,而select * from student where name=upper('ZHANGFEI');是会使用索引的 。
    • 在索引列上计算:例如select * from student where age-1=17;
    6、在索引列上使用mysql的内置函数,索引失效
    例如,SELECT * FROM student WHERE create_time
    7、索引列数据类型不匹配
    例如,如果age字段有索引且类型为字符串(一般不会这么定义,此处只是举例)但条件值为非字符串,索引失效,例如SELECT * FROM student WHERE age=18会导致索引失效 。
    8、索引列使用IS NOT NULL或者IS NULL可能会导致无法使用索引
    B-tree索引IS NULL不会使用索引,IS NOT NULL会使用,位图索引IS NULL、IS NOT NULL都会使用索引 。
    最后,对索引的使用做一个总结吧:
    1. 索引有利于查询,但不能随意加索引,因为索引不仅会占空间,而且需要在写库时进行维护 。
    2. 如果多个字段常常需要一起查询,那么在这几个字段上建立联合索引是个好办法,同时注意最左匹配原则 。
    3. 不要在重复度很高的字段上加索引,例如性别 。
    4. 避免查询语句导致索引失效,哪些情况会导致索引失效请见前文 。
    作者:云深i不知处
    原文链接:https://blog.csdn.net/mu_wind/article/details/110128016