浅谈MySQL中的索引

什么是索引?为什么需要用索引?

在MySQL中,索引（index）也叫做“键（key）”,它是存储引擎用于快速找到记录的一种数据结构. 他是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址.
在数据量十分大的时候,索引可以大大加快查询的速度,因为使用索引后可以不用扫描全表来定位某行的数据,而是通过索引表找到该行数据对应的物理地址然后访问.
索引的优缺点
优点:

• 快速检索,快速查找到所需要的数据
• 最常见的B-Tree索引,按照顺序存储数据,所以,MySQL可以用来做Order By和Group By操作.可以加快分组和排序
• 因为索引中也存储了实际的列值,所以某些查询只使用索引就能够获取到全部的数据,无需再回表查询.减少了I/O次数

缺点:

• 索引本身也是表,因此会占用存储空间
• 索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大
• 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表

索引的类型

在mysql中,常见的索引类型有:主键索引,唯一索引,普通索引,全文索引,外键索引,组合索引.

• 主键索引: 简称主键,可以提高查询效率并提供唯一性约束,一张表只能有一个主键. 被标记为自动增长的字段一定是主键,但主键不一定是自动增长. 一般把主键定义在无意义的字段上,主键的数据类型最好是数值.
• 唯一索引: 也可以提供唯一性约束. 但是一张表可以有多个唯一索引. 索引列的值必须唯一,但允许有空值.
• 普通索引: 用表中的普通列构建的索引,没有任何限制. 一张表可以有多个普通索引,如果没有明确指明索引的类型,所说的索引就是指普通索引.
• 全文索引: 用大文本对象的列构建的索引. 在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引.
• 外键索引: 简称外键,外键会自动和对应的其他表的主键关联. 外键的主要作用是保证记录的一致性和完整性. 只有InnoDB存储引擎的表才支持外键. 如果要删除父表中的记录,必须先删除子表(带外键的表)中的相应记录.
• 组合索引: 用多个列组合构建的索引,与单列索引相对应(即一个索引只包含单个列).

注意:组合索引遵循"最左匹配"原则,把最常用作为检索或排序的列放在最左,依次递减,组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的. 在使用组合索引的时候可能因为列名长度过长而导致索引的key太大,导致效率降低,在允许的情况下,可以只取列名的前几个字符作为索引.

索引的实现原理

mysql包含了多种存储引擎,而每个存储引擎支持的索引类型也不同,总体,mysql数据库支持的索引结构如BTree索引,B+Tree索引,哈希索引,全文索引等.
哈希索引:只有memory(内存)存储引擎支持哈希索引,一个值只能对应一个hashcode,而且是散列的分布方式,因此不支持范围查找和排序的功能.
全文索引:仅可用于myisam和innodb,针对较大的数据.
从一开始的二叉查找树—>平衡二叉查找树(左右子节点高度相差不大于1,是相对平衡的树)—>多路平衡查找树(绝对平衡树,左右子树相差为0. 即BTree,若有n个关键字,就有n+1路)—>B+Tree
二叉树的缺陷:(1) 搜索时的I/O次数太多. (2) 节点数据内容太少.
BTree
BTree是平衡搜索多叉树. 可以用二分查找方式查找,因此BTree是一个非常高效的查找结构. 其要满足以下条件:

• 每个叶子结点的高度一样.
• 每个非叶子结点由n-1个key和n个指针组成,key和指针相互间隔.
• 叶子结点的指针都为null
• 非叶子结点的key都是[key,data]二元组,key表示作为索引的键,data为键值所在行的数据.

B+Tree
他是BTree的一个变种,主要的不同点在于:

• B+Tree中的非叶子结点不存储数据,只存储键值.
• 叶子结点没有指针,所有键值都会出现在叶子结点上,且key存储的键值对应data数据的物理地址.
• 每个非叶子结点由n个key和n个指针组成,因为结点关键字搜索采用左闭右开区间.
• 叶子结点是顺序排序的并且相邻结点具有顺序引用关系(很多存储引擎在B+Tree的基础上进行了优化, 添加了指向相邻叶节点的指针, 形成了带有顺序访问指针的B+Tree, 这样做是为了提高区间查找的效率, 只要找到第一个值那么就可以顺序的查找后面的值.)

优点:

• 扫库,扫表更强,因为只需要扫最低一层
• 磁盘读写能力强,索引结构的结点被设计为一个页的大小(4k的整数倍),提升查找速度的关键就在于尽可能少的磁盘I/O, 那么可以知道,每个节点中的key个数越多,那么树的高度越小,需要I/O的次数越少,因此一般来说B+Tree比BTree更快, 因为B+Tree的非叶节点中不存储data, 就可以存储更多的key.
• 排序能力强
• 查询速度更加稳定, 树有多高就需要多少次I/O, 而BTree有可能在上层就能直接搜索到结果返回, B+Tree只能查询到叶子结点才能得到data, 而叶子结点高度都是一样的, 所以所有数据的查询速度都是一样的, 即查询效率更稳定.

索引的不同实现

mysql中的myisam和innodb分别实现了非聚簇索引和聚簇索引.
另外,在索引的键是否为主键来分可以分为"主索引"和"辅助索引", 因此主索引只能有一个, 辅助索引可以有很多个.
聚簇索引
数据库表行中的数据的物理存储顺序与键值的逻辑(索引)顺序一致.
InnoDB存储引擎采用的是聚簇索引.
聚簇索引的主索引的叶子结点存储的是键值对应的数据本身,占空间较大,分布范围更大,辅助索引的叶子结点存储的是键值对应的数据的主键键值. 聚簇索引的数据和主键索引存储在一块. 聚簇索引的数据是根据主键顺序保存,因此适合按主键索引的区间查找,可以有更少的磁盘I/O,但是也是因为这个原因,聚簇索引的插入顺序最好也是按照主键的顺序插入,否则会引起页分裂. 使用主索引的时候最好使用聚簇索引,因为聚簇索引只需要查找一次,而非聚簇在查到数据后还要根据数据(地址)再进行一次I/O. 因为聚簇索引的辅助索引存储的是主键键值,因此在数据行移动的时候不需要维护辅助索引,降低成本.
非聚簇索引
索引顺序与数据物理排列顺序无关.
MyISAM存储引擎采用非聚簇索引.
非聚簇索引的主索引和辅助索引几乎一样,只是主索引不允许重复不允许空值,他们的叶子结点的key都存储指向键值对应的数据的物理地址. 非聚簇索引的数据表和索引表是分开存储的(数据文件MYD,索引文件MYI). 插入顺序不受键值影响,更适合单个数据的查询. 如果查询条件不是主键的时候就需要用到辅助索引了.
总结:

由上图可知:

• 聚簇索引的主索引叶子结点的data存的是数据本身,即数据和索引存储在一块,那么索引顺序和数据顺序自然就是一样的呢. 而辅助索引的叶子结点的data存储的是主键的值.
• 非聚簇索引的主索引和辅助索引的叶子结点的data都存储的是数据的物理地址(指针一样),即索引和数据不是存储在一块,因而索引和数据的顺序并没有任何关系.

索引失效与优化

索引失效的几种情况

• 在select语句中,索引只能用一次,在where中使用了则在order by中就不要用了.
• like语句中,通配符(%)放在第一位时索引会失效.
• 在索引的列上使用表达式或者函数会使索引失效.
• 条件是字符串并且不加单引号会导致索引失效.其实就是类型不匹配导致的失效.
• 查询条件中使用or会使索引失效，要想是索引生效，需要将or中的每个列都加上索引.
• 在查询条件中使用IS NULL或IS NOT NULL会导致索引失效.
• 注意:查询条件中如果使用不等于会导致索引失效,但是,如果对主键索引使用不等于,并且如果占总记录的比例很小的话,不会失效!

索引的优化

• 最左前缀原则
• 使用短索引,如果列值的前一部分字符内的多数值是唯一的,则不需要对整个列进行索引
• 尽量使用覆盖索引(只查询索引的列,即索引列和查询列一致),减少select *
• 列的离散性越高越好(即列的每个值越不同越好),所以说%的离散性差. 在B+Tree中相同关键值的选择性就会越差,建议不设为索引

对于联合索引: 最左匹配原则>离散度>最少空间
(单列索引就是特殊的联合索引)
最后来一个优化小总结:
全职匹配我最爱, 最左前缀要遵守;
带头大哥不能死, 中间兄弟不能断;
索引列上少计算, 范围之后全失效;
LIKE百分写最右, 覆盖索引不写星;
不等空值还有Or, 索引失效要少用;
VAR引号不可丢, SQL高级也不难.