MySQL索引：类型、使用、设计原则及失效场景

一、什么是索引

二、使用索引的优缺点

优点：

1. 减少扫描行数，大幅提升查询速度
2. 避免排序、分组、临时表
3. 索引可以将随机 IO 变成顺序 IO

缺点：

1. 创建和维护索引需要耗费时间，并且数据量越大消耗的时间越长
2. 索引需要占用物理空间，索引越多则占用磁盘空间越多、增、删、改表中的数据时动态维护索引的成本越大

三、索引类型

按存储方式

• B+Tree索引
  对比B-Tree区别：

1. B+树中间节点只存索引不存数据，叶子节点才存数据；B树所有节点既存索引也存数据
2. 更少的IO读写次数：因为B+树的非叶子节点不存数据，所以节点存储的索引更多，一次读到内存的索引也更多，因此减少了IO读写次数
3. 更适合范围查询：B+树中所有数据都存在叶子节点，且是有序的，所以范围查询时只需要遍历叶子节点即可；而B树需要对整个树从上限到下限做遍历

• 哈希索引

按逻辑区分

• 普通索引：没有限制，允许插入重复值、空值
• 唯一索引：允许空值，但是值必须唯一
• 主键索引：以主键作为索引
• 空间索引：只能在存储引擎为 MyISAM 的表中使用
• 全文索引：只有 MyISAM 存储引擎支持全文索引

按实际使用区分

• 单列索引：索引只包含一列
• 多列索引：又叫组合索引、复合索引，在多个列上建立一个索引

四、查看索引

• 查看表的索引：SHOW INDEX FROM
• 查看SQL语句使用的索引：在SQL语句前加上explain即可

五、创建索引

• 创建表时（在CREATE TABLE语句中）

1. KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)
2. UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
3. FOREIGN KEY <索引名> <列名>
4. PRIMARY KEY [索引类型] (<列名>,…)

• 创建表后（在ALTER TABLE语句中）

1. ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
2. ADD PRIMARY KEY [<索引类型>] (<列名>,…)
3. ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
4. ADD FOREIGN KEY [<索引名>] (<列名>,…)

六、索引设计原则（尽量符合这些原则）

1. 选择唯一性索引
2. 为经常用来查询的字段建立索引（JOIN子句、WHERE子句中经常使用的字段建立索引）
3. 为经常需要排序、分组的字段建立索引
4. 数据量小的表不要建立索引、索引选择性很低的不要建立索引
5. 如果字段值较长，可使用前缀索引（涉及索引选择性的概念）
6. 不要建立过多的索引，索引越多，需要的磁盘空间越大、更新表的时间越长

七、索引失效的场景

1. 索引是表达式或函数的一部分【一定失效】
2. 隐式类型转换、隐式编码转换【一定失效】
3. where语句中包含or时【可能失效】
4. like ‘ABC%’【不会失效】，like ‘%abc’【一定失效】
5. 联合索引中，where子句违背最左匹配原则【一定失效】
6. 数据库自身进行了优化，比如数据库判断要回表查询之类的原因