把学习当成一种习惯
选择往往大于努力,越努力越幸运
MySQL 索引
Aug 14, 2020

本文内容是基于MySQL的InnoDB存储引擎.

什么是索引？

  索引是一种提高磁盘IO查询数据的一种数据结构,对于MySQL,如果合理的利用索引可以帮助我们大幅度的提升查询效率,避免在查询中全表扫描数据.如果存在表中有大量数据,就需要花时间研究建立最优秀的索引.MySQL在单表里的索引总数限制为16个.
  在InnoDB存储引擎中,表都是根据主键顺序(聚集索引)为索引的形式存放的,也就是说数据都是存储在B+Tree中,每一个索引在InnoDB存储引擎里面对应一棵B+Tree.

索引的数据结构

  InnoDB存储引擎使用的是B+Tree数据结构.本文不重点细说B+Tree,会在另外一篇文章细讲 ------> B树

聚集索引和非聚集索引

  聚集索引 : 创建表的时候会以主键创建一棵聚集索引树,如果没有显示的创建主键,InnoDB存储引擎会选择非空唯一索引来做主键(多个的情况下选择建表时第一个定义的非空唯一索引),如果没有符合唯一索引且不为空的列,那么InnoDB存储引擎内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键列占用6个字节,该列的值会随着数据的插入自增.

  非聚集索引 : 普通索引(单列索引)常用、唯一索引(唯一性,不可重复)、联合索引(多个列组合成的索引)优先推荐、前缀索引看场景、全文索引看场景都是非聚集索引.单表中可以多个非聚集索引.

聚集索引和非聚集索引有什么区别？

  InnoDB存储引擎的聚集索引和非聚集索引的存储结构都是B+Tree结构,且非叶子节点存储的都是索引列的值和孩子指针.通过非叶子节点(索引列)的查找,最终在叶子节点拿到符合条件的数据.聚集索引的叶子节点存储的是(整行的数据)整行,非聚集索引的叶子节点存储的是(索引列和主键值)不是整行,例如在单列索引中,非聚集索引的叶子节点只存储单列索引的值和主键值,而在联合索引中,非聚集索引的叶子节点存储的是创建的多个索引列的的值和主键值.在这里需要说明一下,B+Tree的节点相当于一个page(页),每个页里面可以存多个行数据.B+Tree只能定位到page,而每个page内部的数据是以一条有序链表来存储,通过二分查找可以快速法定位到行数据.

画图大概表示一下聚集索引和非聚集索引的存储结构

user表

  从user表我们可以知道,在聚集索引中,每行的数据是跟主键值的顺序是有关系的.

把上面的数据表转为聚集索引的数据结构存储图 

// 当前执行这条SQL语句 查询条件是主键值(聚集索引),
// 所以优化器会告诉执行器走聚集索引,
// 也就是根据下面的图去索引查询数据
select * from user u where u.id = 20;

  从上图我们可以知道 : 每个page可以多行数据,每行数据存放的是整行的数据,也就是可以获取到全部列的数据.而且叶子节点上的数据根据主键值有序的排列着,MySQL推荐主键自增(有序),且最好选择整型.

非聚集索引的数据结构存储图

// 当我执行这条SQL语句 查询条件是普通索引(非聚集索引),
// Mysql的优化器会告诉执行器走非聚集索引查询,
// 也就是根据下面的图去索引查询数据
select * from user u where u.username = 'abin';

  从上图我们可以知道非聚集索引的叶子节点存储的是主键值和索引列，也就是只能获取到主键值和索引列的数据.而且叶子节点上的数据是根据索引列有序的排列着.

回表查询

  但是你发现了没有,走非聚集索引查询的时候,叶子结点只可以获取到主键值和索引列的数据,你上面查询的语句是 全部列,那么sex字段如何拿到呢？这里就涉及到了一个新词：回表查询,如下图.

  也就是说我还需要根据拿到的主键ID再次去扫描一次索引树(聚集索引树),才可以拿到sex这个字段的数据,查询数据大的时候,还是挺消耗性能的

联合索引和覆盖索引

联合索引

  通过上面我们已经知道，根据非聚集索引查询只能查询到主键值和索引列的值,如果想要其他列的值需要回表查询才能拿到,但是回表是一种第二次索引树扫描的操作,会降低了我们的查询效率,那有什么办法解决不需要回表也能拿到我要的列值呢?答案就是建立联合索引.下面是创建联合索引的语句:

// 删除username索引
drop index idx_name on user;
// 重新建立联合索引
alter table user add index(username,sex);
// （或用key关键字也一样的）
alter table user add key(username,sex);

覆盖索引 

// 这种叫做覆盖索引，因为刚好username可以拿到.
// 覆盖索引在统计查询中也可以发挥提高查询性能的作用.
select id,username from user where username='abin';

疑问

  1. 为什么InnoDB存储引擎中的每个表必须有主键,并且推荐使用整型的自增主键 : MySQL的设计者就是用InnoDB存储引擎把数据和主键索用B+Tree引组织起来,也就是说如果没有主键就没有结构来存储全部数据.
    • MySQL的主键就是聚集索引吗 : 是的,MySQL创建的主键默认会创建一颗聚集索引树.
    • 为什么建表的时候即使没有创建主键,表也可以创建成功 : 不建议建表的时候不创建主键,没有创建主键的情况下InnoDB存储引擎会把一个非空唯一索引字段作为主键索引,如果存在多个非空唯一索引,则选择建表时第一个定义的非空唯一索引作为主键,如果没有符合这种条件的列(唯一索引且不为空),InnoDB存储引擎会生成一个唯一的列,类似于rowid,只不过你看不到,他会用生成的这个唯一列,维护B+Tree的结构,查数据的时候还是用B+Tree的结构去查找.
    • 为什么推荐整型呢 : 在查找数据的时候,要不断的去进行数据的比对,如果是一个字符串且不自增.再者,整型的比较是位运算(例如1<2),而字符串是"abin"和"abin"的比较,两者中明显整形的比较效率高.
    • 为什么推荐自增呢 : B+Tree的插入可能会引起数据页的分裂,删除可能会引起数据页的合并,二者都是比较重的磁盘IO消耗,所以比较好的方式是顺序插入数据,这也是我们一般使用自增主键的原因之一.自增保证了数据的有序性,插入数据的时候,按着顺序插进去就好了,而没有自增的情况下,B+Tree的插入数据中引起的分裂操作会影响性能的消耗和数据页的空间使用.
  2. 为什么非聚集索引结构叶子节点存储的不是整行数据呢 : 前面我们已经知道,创建表的时候,InnoDB存储引擎会帮我们维护一个聚集索引的B+Tree数据结构(叶子节点存储的是整行数据),而非聚集索引的叶子节点存储的是索引列的值和主键值(不是整行数据),如果非聚集索引存储的也跟聚集索引一样的存储的是整行数据就不用回表查询了,不是更好吗?这是为了节省空间,因为继续存数据的话,那就会导致一份数据存了多份,空间占用就会翻倍.另一方面也是一致性的考虑,都通过主键索引来找到最终的数据,避免维护多份数据导致不一致的情况.
  3. 什么是回表查询 : 第2个问题我们已经知道,非聚集索引的叶子节点只存储索引列的值和主键值,如果我们查询的时候需要拿到其他列的信息,需要根据拿到的主键值再次遍历一遍聚集索引的B+Tree拿到信息,这个就是回表查询.
  4. 联合索引是一个怎么样的规则去存储到B+Tree结构的？(将用一篇文章详细说明单列索引和联合索引的存储结构规则)

结束语

  • 原创不易
  • 希望看完这篇文章的你有所收获!

目录