MySql INNODB引擎下的索引使用原则或注意事项

1、 mysql的索引存储类型
BTREE、HASH
INNODB、MyISAM引擎下，只支持BTREE索引

2、 索引的分类-基于INNDB引擎

1） 普通索引，MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

2） 唯一索引：索引列中的值必须是唯一的，但是允许为空值，如果是组合索引，则列值的组合必须唯一（普通索引和唯一索引该怎么选择：这两种索引查询性能差别不大，主要是对更新性能的影响，建议选择普通索引；change buffer只能用于普通索引）

3） 主键索引：是一种特殊的唯一索引，不允许有空值。

4） 联合索引：
多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

注意：①如果查询结果中只有联合索引中的字段，则不需要最左前缀原则，where、group by 或order by中的条件只要是联合索引中的字段就行
示例：SELECT c3 FROM test_index WHERE c2 = ‘1’ （c2对应的联合索引为c1_c2_c3）
②如果对不同的索引做order by 或 group by，就算查询结果只有这两个索引字段，也不能使用索引

5） 前缀索引：取索引字段的前面几个字母作为索引

3、 BTREE存储结构

在InnoDB中，聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；


而非聚集索引存储的结构树，其叶子节点存储的值为聚集索引的值，通过非聚集索引找到聚集索引，再通过聚集索引找到其他数据的流程称为回表。

4、 索引的使用原则或注意事项

1）对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引

2）数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果

3）在不同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引

4）尽量只列出需要查询的字段（如果没有查询条件的情况下，查询的结果只有一个索引字段，则索引生效），看情况建立联合索引，实现索引覆盖，避免回表

5）对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少

6）不要对包含null值的列加索引（只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。）

5、 索引失效

1)、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引，这个时候会使用索引合并（explain SELECT * FROM test_index where c4 = ‘1’ or c5 = ‘1’ 其中c4、c5分别建立索引）

2)、联合索引，需满足最左前缀原则

3)、like查询以%开头的列索引会失效

4)、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5)、不等于(！= ，<> )，EXISTS，not in,is not null,>,<索引都会失效