MySQL--聚集索引/辅助索引/回表查询/覆盖索引--原理/优化

​

简介

        本文介绍如下内容：聚集索引；辅助索引；什么是回表查询，如何优化回表查询；什么是覆盖索引，覆盖索引的应用场景等。

表结构和数据

为了便于展示概念，本处先建好一个用户表（t_user），表建好后是这样的：

 语句

DROP DATABASE IF EXISTS demo;
CREATE DATABASE demo DEFAULT CHARACTER SET utf8;
USE demo;

DROP TABLE IF EXISTS t_user;

CREATE TABLE `t_user`
(
  `id`   BIGINT(0) AUTO_INCREMENT,
  `user_name` VARCHAR(64),
  `code` VARCHAR(20),
  `age`  INT,
  PRIMARY KEY (`id`),
  KEY index_user_name (`user_name`),
  KEY index_code (`code`)
) ENGINE = InnoDB;

INSERT INTO `t_user` VALUES (1, 'LiLei', 'aa', 21);
INSERT INTO `t_user` VALUES (2, 'HanMeimei', 'bb', 23);
INSERT INTO `t_user` VALUES (3, 'Lucy', 'cc', 25);
INSERT INTO `t_user` VALUES (4, 'Lili', 'dd', 28);
INSERT INTO `t_user` VALUES (5, 'WeiHua', 'ee', 24);
INSERT INTO `t_user` VALUES (6, 'ZhangWei', 'ff', 30);
INSERT INTO `t_user` VALUES (7, 'Anna', 'gg', 26);
INSERT INTO `t_user` VALUES (8, 'Lisa', 'hh', 21);
INSERT INTO `t_user` VALUES (9, 'ZhangWei', 'ii', 24);
INSERT INTO `t_user` VALUES (10, 'Kate', 'jj', 29);

聚集索引和辅助索引

        在介绍回表和覆盖索引之前，需要先介绍聚集索引和辅助索引。

        InnoDB有两大类索引：聚集索引（Clustered Index）和辅助索引（Secondary Index）。

​项​	​聚集索引​	​辅助索引​
​别名​	聚簇索引	二级索引、普通索引、非聚集索引、非聚簇索引
​结构​	叶子页保存了整个行数据。 所以也将聚集索引的叶子节点称为数据页。	叶子节点只存储聚集索引的非叶子节点存储的值（一般是主键ID）。 想拿到行数据，要根据主键去聚集索引取行数据。
​数量​	一张表必须有且只有一个聚集索引。	一张表可以有任意个普通索引。（没有也可以）
​优点​	基于主键的查询非常快。 因为直接定位行记录。	更新代价比聚集索引要小
​缺点​	1. 更新代价大（可忽略此缺点，因为主键一般不变） 2. 依赖于有序的数据	若需要回表，则速度慢。 （若覆盖索引，则速度快。）

​聚集索引​

​简介​

对于本文用户表来说，聚集索引是这样的：

聚集索引的生成规则：

1. 1. 
      
2. 如果表定义了PK（Primary Key，主键），那么PK就是聚集索引。
3. 如果表没有定义PK，则第一个NOT NULL UNIQUE的列就是聚集索引。
4. 否则InnoDB会另外创建一个隐藏的ROWID作为聚集索引。
   1. 
      

​优点​

基于主键的查询非常快。因为直接定位行记录。

​缺点​

1. 1. 
      
2. ​更新代价大​

1. 如果对索引列的数据被修改时，那么对应的索引也将会被修改，可能涉及自旋操作维护平衡， 而且聚集索引的叶子节点还存放着数据，修改代价肯定是较大的， 所以对于主键索引来说，主键一般都是不可被修改的。

2. ​依赖于有序的数据​

1. 因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或UUID这种又长又难比较的数据，插入或查找的速度肯定比较慢。

3. 
   

​辅助索引​

​简介​

        InnoDB辅助索引的叶子节点存储主键值。想拿到行数据，要根据主键去聚集索引取行数据。

注意：InnoDB不是存储行记录头指针，MyISAM的索引叶子节点存储记录指针。

对于本文t_user表来说，辅助索引是这样的：

​优点​

1. 1. 
      
2. 只读取索引时IO负载低

1. 1. 
      
2. 索引项通常远小于数据行大小，若只读取索引，会极大地减少数据访问量。
3. 这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。辅助索引对于I/O 密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中（这对于M ylSAM尤其正确，因为MylSAM 能压缩索引以变得更小）。
   1. 
      

2. I/O 密集型的范围査询性能高。

1. 1. 
      
2. 索引是按照列值大小顺序存储的（至少在单个页内是如此），对于I/O 密集型的范围査询会比随机从磁盘读取每一行数据的I/O 要少得多。
3. 对于某些存储引擎，例如 MylSAM和 Percona XtraDB，甚至可以通过OPTIMIZE命令使得索引完全顺序排列 ，这让简单的范围査询能使用完全顺序的索引访问。
   1. 
      

3. 数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

1. 1. 
      
2. 一些存储引擎如MylSAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。
3. 这可能会导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
   1. 
      

4. 由于InnoDB的聚簇索引，辅助索引对InnoDB表特别有用。

1. InnoDB的辅助索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖索引，则可以避免对主键索引的二次査询。

5. 
   

回表查询

        回表查询需要扫描两次索引树：先通过普通索引列扫描普通索引树获取到主键，再通过主键扫描聚集索引树获得数据，它的性能比扫一遍索引树低。

        对于本文的t_user，id为主键，user_name为普通索引，code为普通索引，age不建索引。

下边这条语句就会进行回表查询

SELECT * FROM t_user WHERE user_name = 'Lisa';

        这条语句会先通过user_name列的普通索引找到主键，然后再拿这个主键去聚集索引中取行数据。因为第一次通过普通索引只能获得user_name这一列和主键这两个数据，所以必须去聚集索引获取其他数据，如下图所示：

因为这两个索引数的高度都是2，所以，这次回表要经过4次IO才能获得到数据。

覆盖索引（优化回表查询）

简介

​什么是覆盖索引​

        索引覆盖是一种避免回表查询的优化策略。就是把所有需要查询的字段都放到普通索引中，这样普通索引查到的叶子结点中已经能够得到所需的所有字段，就不会再去聚集索引中查询。

        在Explain时，输出的Extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引。

​索引覆盖适用范围​

        不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列值（因为SQL查询结果和查询条件的都涉及到具体的值，而覆盖索引需要覆盖查询结果和查询条件），而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree或B+Tree索引做覆盖索引。

        不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

实例

​1.WHERE列有索引，SELECT的列有没有索引的（不是覆盖索引）​

EXPLAIN SELECT * FROM t_user WHERE user_name = 'Lisa';

​2.WHERE列有索引，SELECT WHERE列以及主键（是覆盖索引）​

EXPLAIN SELECT id, user_name FROM t_user WHERE user_name = 'Lisa';

​3.WHERE列有索引，SELECT有单独索引的列（不是覆盖索引）​

EXPLAIN SELECT user_name, code FROM t_user WHERE user_name = 'Lisa';

​4.WHERE列有单独索引，只SELECT id（不是覆盖索引）​

EXPLAIN SELECT id FROM t_user WHERE user_name = 'Lisa' AND code = 'hh';

​5.WHERE的条件都为联合索引中的字段，且符合最左前缀原则（是覆盖索引）​

DROP DATABASE IF EXISTS demo;
CREATE DATABASE demo DEFAULT CHARACTER SET utf8;
USE demo;

DROP TABLE IF EXISTS t_user;

CREATE TABLE `t_user`
(
  `id`   BIGINT(0) AUTO_INCREMENT,
  `user_name` VARCHAR(64),
  `code` VARCHAR(20),
  `age`  INT,
  PRIMARY KEY (`id`),
  KEY index_user_name_code (`user_name`, `code`)
) ENGINE = InnoDB;

INSERT INTO `t_user` VALUES (1, 'LiLei', 'aa', 21);
INSERT INTO `t_user` VALUES (2, 'HanMeimei', 'bb', 23);
INSERT INTO `t_user` VALUES (3, 'Lucy', 'cc', 25);
INSERT INTO `t_user` VALUES (4, 'Lili', 'dd', 28);
INSERT INTO `t_user` VALUES (5, 'WeiHua', 'ee', 24);
INSERT INTO `t_user` VALUES (6, 'ZhangWei', 'ff', 30);
INSERT INTO `t_user` VALUES (7, 'Anna', 'gg', 26);
INSERT INTO `t_user` VALUES (8, 'Lisa', 'hh', 21);
INSERT INTO `t_user` VALUES (9, 'ZhangWei', 'ii', 24);
INSERT INTO `t_user` VALUES (10, 'Kate', 'jj', 29);

EXPLAIN SELECT user_name, code FROM t_user WHERE user_name = 'Lisa';

优化实例

​分页优化​

​        ​有一个日志表t_log，需要进行分页查询，于是很容易就想到了limit [offset偏移量] [count数量]这个查询方式。当我们偏移量比较小时没什么问题

SELECT  * FROM  t_log WHERE type = 1 LIMIT 5, 50
-- 查询时间:0.45s

        随着offset的增加，查询时间越来越长，但是每次查出的数据都只有50条

SELECT  * FROM  t_log WHERE type = 1 LIMIT 500000, 50
-- 查询时间:57.252s
SELECT  * FROM  t_log WHERE type = 1 LIMIT 1000000, 50
-- 查询时间:89.15s

        查阅资料发现“limit”的工作方式是：第一步：查询offset+count条数据；第二步：抛弃前offset条数据。

        但是全字段查询肯定会有回表查询操作，这就导致了进行百万次的回表查询，速度肯定会很慢，解决思路是，在“第一步”时不进行回表查询，这样效率会提高很多，于是把sql改成下面的等效查询：

SELECT  *
FROM  t_log t
RIGHT JOIN (
  SELECT uid
  FROM t_log
  WHERE type = 1
  LIMIT 1000000,50
) tmp ON tmp.uid = t.uid
-- 查询时间：0.64

        这下时间缩短了一百倍多，查出来的结果也是正确的，达到了我们要的效果，到此sql已经优化好了。 

​

另见 《高性能MySQL 第三版》=> 5.3 高性能的索引策略   

        《MySQL技术内幕 InnoDB存储引擎 第2版》=> 5.4 B+树索引