MySQL：索引的设计原则和索引失效的分析

一、索引的设计原则

在MySQL数据库中，创建索引时通常考虑以下事项：

1、对于查询频率高的字段创建索引，对于更新频率高的字段不要创建索引；

2、对排序、分组、连接查询频率高的字段创建索引； 单独order by 用不了索引，需要配合where条件。

3、如果需要将多个列设置为索引时，可以采用多列索引(组合索引)
如：某个表(假设表名为Student)，存在多个字段(StudentNo, StudentName, Sex, Address, Phone, BirthDate)，其中需要对StudentNo,StudentName字段进行查询，对Sex字段进行分组，对BirthDate字段进行排序，此时可以创建多列索引。下述语句中只创建了一个索引，但是对4个字段都赋予了索引的功能。

 #index_name为索引名
 create index index_name (StudentNo, StudentName, Sex, BirthDate); 

注意： 创建多列索引，需要遵循BTree类型，即第一列使用时，才启用索引。在上面的创建语句中，只有mysql语句在使用到StudentNo字段时，索引才会被启用。如:

#使用到了StudentNo字段，索引被启用。
select * from Student where StudentNo = 1000;      

可以使用explain查看执行计划，来检测索引是否被启用，如：

explain select * from Student where StudentNo = 1000;

4、选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

5、尽量使用长度小的索引
选择越小的数据类型越好，因为通常越小的数据类型通常在磁盘、内存、cpu、缓存中占用的空间很少，处理起来更快。

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。在创建索引的时候指定前缀长度，比如前10个字符 (前提是多数值是唯一的)。那么短索引可以提高查询速度，并且可以减少磁盘的空间，也可以减少I/0操作。

6、尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7、删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

8、索引的数目不宜太多
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
原因-a：每创建一个索引都会占用相应的物理控件。
原因-b：过多的索引会导致insert、update、delete语句的执行效率降低。

注意
（1）很少数据的列(类似枚举)也不应该建立索引，比如一个性别字段 0或者1，在查询中结果集的数据占了表中数据行的比例比较大，mysql需要扫描的行数很多，增加索引，并不能提高效率。
（2）定义为text和image和bit数据类型的列不应该增加索引，或者使用前缀索引。
（3）当表的修改(UPDATE、INSERT、DELETE)操作远远大于检索(SELECT)操作时不应该创建索引，这两个操作是互斥的关系。

二、索引失效的分析

1、查询中很少使用到的列不应该创建索引，如果建立了索引，还会降低mysql的性能和增大了空间需求。
2、like语句中，like ‘%ABC%’ 不会使用索引；而like ‘ABC%’ 可以用到索引。
3、如果列中包含NULL值，则不会用到索引。复合索引中如果有一列含有NULL值那么这个组合索引都将失效，一般需要给默认值0或者’'字符串。
4、不要在列上进行运算(表达式+函数)，这样会使得mysql索引失效，也会进行全表扫描。

select id from t where num/2=100    
应改为:    
select id from t where num=100*2

select id from t where substring(name,1,3)='abc'    
应改为:    
select id from t where name like 'abc%

5、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用unio替换。

select id from t where num=10 or num=20    
   
select id from t where num=10    
union all    
select id from t where num=20

6、in 和 not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用 between 就不要用 in 了

select id from t where num in(1,2,3)    
    
select id from t where num between 1 and 3

7、复合索引，必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。