MySQL原理（四）索引（3）索引失效与索引区分度

一、索引失效：

首先未使用索引列作为查询条件索引是肯定会生效的，还有其他的情况，索引列做为了查询条件也失效了：

 ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME, age, pos);

1、select 语句、order by语句：和索引无关；

2、where语句索引失效：

（1）组合索引失效：如果索引了多列，要遵守最左前缀法则，Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但必须包含最左侧的一个。例如索引是index (a,b,c)，可以支持a | a,b| a,b,c |a,c这几种组合进行查找，但不支持 b|b,c|c进行查找 。如

注：

组合索引最好全值匹配(查询条件的顺序和索引的顺序一致，这种方式最好，能够充分发挥索引的作用，当然使用and连接的查询提交也可以不与索引的顺序一致，mysql会自动优化，如index(a,b,c)，where a= and b= and c= 与where b= and c= and a=，...效果是一样的 

（2）列类型是字符串，查询条件未加引号；

（3）使用like时通配符在前会导致索引失效，通配符在后面时效率不受影响，所以一般使用右模糊：

（4）查询条件中使用or会使索引失效，要想是索引生效，需要将or中的每个列都加上索引；

（5）对索引列进行计算、函数、(自动or手动)类型转换会导致索引失效，如where substr(a, 1, 3) = ‘hhh’、where a = a+1、DATE_FORMAT函数等；

注意：索引失效指的是where条件不当引起的失效，如这里计算放在select后面是不会引起索引失效的。

（6）mysql 在使用不等于(!= )的时候无法使用索引，会导致全表扫描；

（7）使用in查询，当in()括号里面只有一个时，索引有效；否则无效。如表zt_test现有数据和索引如下：

 现在查询：

当in的条件多于1个时，

小结：假设index(a,b,c)：

sql	有没有用到索引，有的话用到了哪些
where c=1 and b=1 and a='aaa'	用到了a,b,c
where a = 'aaa'  and c=1	用到了a，没有用到c
where a like '%aaa%' and b=1 and c=1	没有用到
where a like 'aaa%' and b=1 and c=1	用到
where a ='aaa' and b like '1%' and c=1	用到了a和b， c在模糊查询的通配符之后，断了
where a = 111 and b =1 and c=1	没有用到
where a = 'aaa' or b =1 or c=1	没有用到
where a!='aaa' and b =1 and c=1	没有用到
where (a+'aa')!='aaa'	没有用到
ORDER BY a	没有用到
GROUP  BY a	用到

二、索引长度与索引区分度

在SQL执行计划中，key_len 表示索引长度，经常用于判断复合索引是否被完全使用。

注：在utf8编码方式下，一个字符占3个字节；utf8mb4一个字符占4个字节；gbk中一个字符占2个字节；latin中一个字符占1个字节。索引长度可以指定，不指定的情况下会按照规则使用默认的长度。

1、默认索引长度定义：

在没有指定索引长度的情况下，如果索引字段不为空且长度不可变，索引长度等于该字段的长度；可以为null，mysql会用1个字节标识；长度可变，MySQL会使用2个字节标识。以utf8编码为例

（1）如果索引字段不为空且长度不可变，索引长度等于该字段的长度；

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

随便插入几条数据：

    

 索引查询： key_len = 60，索引段name的长度是20个字符，key_len = 20*3 = 60。

   

（2）如果索引字段不为空，长度可变：改成varchar：

   

查询：key_len = 20*3 +2= 62

（3）如果索引字段可以为空，长度不可变：

    

  查询：key_len = 20*3 +1= 61

  

（4）以此类推，字段既可以为空长度也可变，索引长度+2+1：

   

查询：

  

（5）复合索引：索引列长度之和

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_address` (`name`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

   

 key_len长度为63，说明只用到了复合索引的前半部分；

 key_len长度为126，说明该SQL查询语句用了整个复合索引。

 综上，key_len 表示索引长度，经常用于判断复合索引是否被完全使用。

2、指定索引长度：

CREATE INDEX index_name ON table_name column_name(length)；

 如对于表

 设置索引长度：

ALTER TABLE test ADD INDEX index_name_address (`name`(10),`address`(10));

 

 key_len = 10*3+2+1+10*3+2+1=66。

3、索引区分度：

区分度百分比 = select count(distinct left(索引字段,索引长度))/count(1) from table。区分度越高，查询越快，如主键索引，主键是唯一的，主键索引的区分度就是1。区分度低的索引原则上已失去意义，没有明显的查询效率，而且添加了索引每次查询会先走索引树，再回表查询，增加了额外的io消耗，就不如直接查询原表来的效率高

4、总结：

索引长度和区分度是相互矛盾的，索引长度太短，那么区分度就很低，把索引长度加长，区分度就高，但是索引也是要占内存的，所以我们需要找到一个平衡点。

举个例子：（张，张三，张三哥），如果索引长度取1的话，那么每一行的索引都是 张 这个字，完全没有区分度，无法排序，结果这样三行完全是随机排的，因为索引都一样；如果长度取2，那么排序的时候至少前两个是排对了的，如果取3，区分度达到100%，排序完全正确；但是并不是索引越长越好，比如 (张,李,王) 和 （张三啦啦啦，张三呵呵呵，张三呼呼呼）；前者在内存中排序占得空间少，排序也快，后者明显更慢更占内存，在大数据应用中这一点点影响都是很大的。