MySQL索引简介

索引

什么是索引

• 索引（Index）是帮助MySQL高效获取数据的数据结构。

• 索引的本质： 数据结构。

• 可以简单理解为“排好序地快速查找的数据结构”。

• 除数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现搞基查找算法。这种数据结构，就是索引。（典型B树索引）

• 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

• 平常所说的索引，无特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引。其中聚集、符合、前缀、唯一索引默认都是使用B+树索引。

索引的目的

提高数据查询效率，可以类比字典。

索引的优劣势

优势

• 提高数据检索的效率，降低数据库的IO成本

• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU消耗

劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占空间。
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度。MySQL不仅要更新数据，还要保存索引文件每次更新添加了索引列的字段，调整因为更新所带来的键值变化后的索引信息。
• 当表字段很多时，就要考虑建立更高效率的索引结构，这不是一朝一夕就能完成的。

索引分类

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引

索引列的值必须唯一，但允许有空值（相当于主键值不能重复）

复合索引

即一个索引包含多个列

基本语法

• 创建
  • create [unique] index indexName on mytable(columnname(length));
  • alter mytable add [unique] index [indexName] on (columnname(length));
• 删除
  • drop index [indexName] on mytable;
• 查看
  • show index from table_name;

索引结构

• BTree

• Hash

• full_text全文

• R-Tree

哪些情况需要建立索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引（各种系统手机号等）
• 查询中与其它表关联的字段，外键关系建立索引（学号和课程号的关系）
• 单值/组合索引的选择问题（高并发场景倾向建立组合索引）
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

哪些情况不要创建索引

• 频繁更新的字段不适合创建索引 （更新索引会消耗更多cpu资源）
• where条件里用不到的字段不创建索引（浪费）
• 表记录太少
• 数据重复且分布平均的字段（比如性别只有男女）

索引优化

索引失效（避免）

1. 全值匹配
2. 最佳左前缀法则
   • 如果索引了多列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。也就是所建的索引的第一个字段必须在查询语句中。就好像火车，火车有12345列车厢，车头1号车厢不能少，车身2345不能跳过。少了车厢索引将失效，跳过车身索引也只部分使用。
3. 不在索引列上做任何操作（计算、函数、自动/手动类型转换），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列。 MySql将会在范围条件中进行全表扫描，因此用不到范围条件后面的条件语句，所以范围之后索引失效。
5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*。 用什么取什么，需要A，就select A，不要select多余的列。
6. mysql在使用不等于（ != 或者 <>）的时候无法使用索引会导致索引失效而造成全表扫描。
7. is null， is not null 也无法使用索引
8. like以通配符开头(’%abc…’) mysql索引失效变成全表扫描，但是%结尾却不会有影响。查询是范围查询。
   • 解决 like ‘%str%’ 时索引失效的方法。
     • 可使用覆盖索引（建的索引和查的字段相同，全相同或部分相同，但是查的字段不能比索引字段多）
9. 字符串不加单引号索引失效（数据类型隐式转换，相当于上面第三条）
10. 少用or，用它来连接时会索引失效

建议

• 对于单值索引，尽量选择真的当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段的顺序中，越靠左越好
• 在选择组合索引的时候，尽量选择可以包含当前query中的where字句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法老达到选择合适索引的目的