MySQL索引

索引分类

1. 普通索引： index :加速查找
2. 唯一索引：
   主键索引：primary key ：加速查找+约束（不为空且唯一）
   唯一索引：unique：加速查找+约束 （唯一）
3. 联合索引
   -primary key(id,name):联合主键索引
   -unique(id,name):联合唯一索引
   -index(id,name):联合普通索引
4. 全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5. 空间索引spatial :了解就好，几乎不用

索引的两大类型hash与btree

可以在创建上述索引的时候，为其指定索引类型，分两类：

• hash类型的索引：查询单条快，范围查询慢，哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
• btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

不同的存储引擎支持的索引类型也不一样：

1. InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
2. MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
3. Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
4. NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
5. Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash 索引的查询效率要远高于 B-Tree索引。
Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
Hash 索引无法被用来避免数据的排序操作。
Hash索引不能利用部分索引键查询。
Hash索引在任何时候都不能避免表扫描。
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

innodb表的索引会存放于s1.ibd文件中，而MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，myisam表的索引有单独的索引文件table1.MYI.

因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），
如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，
如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，
这字段的长度为6个字节，类型为长整型.

索引失效情况

1. like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
2. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
3. 组合索引，不是使用第一列索引，索引失效。最左前缀法则。
4. 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
5. 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
6. 对索引字段进行计算操作、字段上使用函数。
7. 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
8. 使用索引排序时，ORDER BY也要遵守“最左前缀”原则

可以使用explain命令加在要分析的sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引。