Mysql索引详解

[TOC]

一、什么是索引

　　索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
　　MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。
　　创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，而不是在select的字段中，实际上，索引也是一张“表”，该表保存了主键与索引字段，并指向实体表的记录，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。说白了索引就是用来提高速度的，但是就需要维护索引造成资源的浪费，所以合理的创建索引是必要的。

二、索引的本质

​ 索引的本质就是一种数据结构——B+Tree（平衡树）

注：BTree与B+Tree的区别

1. BTree就是正常的平衡树，阔以快速地定位查询，而 B+Tree则是在BTree基础上，在所有的叶节点上从第一个到最后一个叶节点上挂上一个链表，这样在区间查询时，不用像BTree那样区间查询时需要回溯节点，在一步步的获取数据，这样的优化使得定点查询数据和区间查询数据的速度都很快。
2. Btree 的数据全树皆存储数据，这样导致数据查询的性能不稳定，而B+Tree只有叶节点存放数据，查询性能稳定

三、索引的优缺点

优点：

​ 寻址快【基于索引数据的定点查询与区间查询速度快】

缺点：

1. ​ 占用表空间
2. ​ 维系增删操作后索引树的平衡开销大
3. ​ 查询时索引不一定生效

四、常用索引有哪些

• 聚簇索引

  ​ 定义：唯一决定数据物理存储顺序的索引【相当于数据存储时，直接在当前索引的末尾加上当前行数据的物理存储位置】，相当于拼音查字目录

  ​ primary key ：自动添加聚簇索引

• 唯一索引

  ​ 定义：辅助索引，相当于偏旁部首查字目录

  ​ unique key ：自动添加唯一索引

• 全文索引

  ​ 定义：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

• 普通索引

  定义：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

  • 单列索引

    ​ 定义：单列索引即一个索引只包含单个列

  • 组合索引

    ​ 定义：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

五、适合创建索引的列类型

1. 频繁作为检索条件的列
2. 数据变化比较大的数据列
3. 有一定的数据规模

六、索引何时会失效

1. 多个索引字段作为条件，尤其是组合索引，不能使用 or
2. 字符串索引列作为条件，模糊查询时，左侧不能出现 %
3. 对索引列的值进行修改后(数值类型的列进行数学计算，对列进行函数调用)
4. 联结查询，主外键字段编码不一致
5. 逻辑计划优化阶段，评测结果为全表检索更优
6. 多表联查，引擎优化后的物理计划主表
7. 组合索引(a,b,c)，条件组合方式非(a,ab,abc)
8. where FIELD is Null

七、创建索引

create [primary/unique/fulltext...] index IX_TABLE_FIELDS on TABLE_NAME(FIELD[,...]);

八、为什么建立索引

不建立索引：

1. ​ 查询单条数据时，需要进行全表查询，查询的性能差异较大（0 ~ ∞）

建立索引：

索引	性能	备注
BTree	Log n	单行查询速度快，区间查询性能也很优
Hash索引	0	因为hash无序，所以单行查询速度快，区间查询性能很低