MySQL索引大揭秘：优化查询的秘籍（下）

本文为《MySQL归纳学习》专栏的第十篇文章，同时也是关于《MySQL索引》知识点的第二篇文章。

相关文章：

MySQL索引大揭秘：优化查询的秘籍（上）

在上一篇文章中，我们介绍了MySQL索引的基础知识，如今，迎来引领数据库查询速度的下篇！从覆盖索引、最左前缀原则，到索引下推，本文将用丰富案例演示，手把手教你优化查询性能，让你的应用轻松应对海量数据。而不容错过的是，还详细讲解了普通索引和唯一索引的区别，让你对索引的理解更上一层楼！

普通索引与唯一索引

我们基于如下表结构进行讲解：

CREATE TABLE t (
  id int(11) NOT NULL,
  k int(11) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

查询过程

普通索引跟唯一索引执行上的区别： 普通索引的等值查询，会继续遍历到第一个不相等的值才会结束，而唯一索引等值查询，命中则结束（性能差距微乎其微）。

InnoDB 按数据页读取数据，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

那么普通索引的等值查询又分为哪些情况呢？为什么与唯一索引查询相比性能差距很小？

• 一种要查询的记录不在页尾；
• 一种是恰好在页尾(因索引的有序性，需要读取下一个数据页，此种情况复杂一些)

更新过程

在《Change Buffer内幕：从Merge到持久化的操作解析》一文中详细讲解了 change buffer ，我们再来看看它与普通索引、唯一索引之间的关联关系。

什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。

要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

那么普通索引和唯一索引在处理更新操作有什么区别呢？

如果要在表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

凡事并无绝对，普通索引和唯一索引的选择还是要结合具体的业务场景。比如说写多读少使用 changebuffer 可以加快执行速度(减少数据页磁盘 io)；但是，如果业务模型是写后立马会做查询，则会触发 changebuff 立即 merge 到磁盘, 这样 的场景磁盘 io 次数不会减少，反而会增加 changebuffer 的维护代价。

**在写多读少的场景下，**如果将普通索引改为唯一索引，导致磁盘 IO 次数增多，进而导致某个业务的库内存命中率降低，整个系统处于阻塞状态，更新语句全部堵住。

覆盖索引

假设有如下表结构：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `init_t`()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE j INT DEFAULT 0;
        DECLARE k INT DEFAULT 0;
  DECLARE city_list VARCHAR(200) DEFAULT '杭州,上海,武汉,北京';
  DECLARE name_len INT DEFAULT 0;
  DECLARE city VARCHAR(16);
  DECLARE namef VARCHAR(16);
  DECLARE age INT;
  DECLARE addr VARCHAR(128);
  
  SET name_len = LENGTH('abcdefghijklmnopqrstuvwxyz');
  
  -- 第一层循环：按照city遍历
  loop_city: LOOP
    SET i = i + 1;
    SET city = SUBSTRING_INDEX(SUBSTRING_INDEX(city_list, ',', i), ',', -1); -- 获取城市
    
    -- 第二层循环：生成数据
    while(k<=4000) do
      SET j = j + 1;
      SET namef = CONCAT(
        SUBSTR('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND() * name_len) + 1, 1),
        SUBSTR('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND() * name_len) + 1, 1),
        SUBSTR('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND() * name_len) + 1, 1)
      ); -- 随机生成姓名
      
      SET age = FLOOR(RAND() * 60) + 18; -- 随机生成年龄
      
      SET addr = CONCAT(
        CASE FLOOR(RAND() * 5)
        WHEN 0 THEN '杭州市某某区某某街道'
        WHEN 1 THEN '上海市某某区某某街道'
        WHEN 2 THEN '武汉市某某区某某街道'
        ELSE '北京市某某区某某街道' END,
        city,
        CASE FLOOR(RAND() * 5)
        WHEN 0 THEN '西路'
        WHEN 1 THEN '东路'
        WHEN 2 THEN '南路'
        ELSE '北路' END,
        FLOOR(RAND() * 100) + 1,
        '号'
      ); -- 随机生成地址
      
      -- 插入数据
      INSERT INTO t VALUES(j, city, namef, age, addr);
      SET k = k + 1;
                end while;
    SET k = 0;
    
    IF i = 4 THEN -- 第一层循环结束
      LEAVE loop_city;
    END IF;
  END LOOP loop_city;
END

执行以下语句：

select * from t where city = '杭州';

由于 city 不是主键索引，在查询过程中，需要先搜索 city 索引树，得到 id 的值，再到 id 索引树搜索一次。这个过程称为回表。

接着观察下述语句：

select id from t where city = '杭州';

这时只需要查 id 的值，而 id 的值已经在 city 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 city 已经“覆盖了”我们的查询需求，我们称为覆盖索引。 总结来说就是：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

我们知道 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是 key +主键。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢，覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

如下述 SQL 代码所示：

alter table t add index city_user(city, name);
select city,name from t where city = '杭州' and name='raa';

创建索引(city,name)，查询数据时，就不用做回表操作。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引。

最左前缀原则

MySQL 中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如 t 表的 name 和 city 加联合索引就是(city,name)。最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from t where city=xx and name=xx; ／／可以命中索引
select * from t where city=xx ; // 可以命中索引
select * from t where name=xx ; // 无法命中索引            

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 name= xx and city ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

我们再来看一下以下 SQL 案例：

select * from t where city like '杭州' and name='xx';

上述 SQL 代码只能用到 city 这个普通索引，无法使用（city，name）联合索引，因为索引向右匹配到范围索引（like，＞，＜，between），就会停止继续向右匹配，从而导致联合索引失效。

在建立联合索引的时候，如何安排索引内的字段顺序？

1、如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

2、在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

3、考虑空间问题，比如说在t表中，如果你既需要（name,age）联合索引，也需要 name 和 age 单独查询，由于 name 字段空间比 age 大，那么建议创建一个（name,age）的联合索引和一个（age）的单字段索引。

接下来我们通过一个案例来为大家详细展示前缀索引的使用。

一张表两个字段 id, uname，id 主键，uname 为普通索引。

SELECT * FROM test_like WHERE uname LIKE  'hr'/ 'hr%' / '%hr'/ '%hr%'

情况一：模糊查询 like 后面四种写法都可以用到 uname 的普通索引。

因为查询的是 *，会查询所有字段（id，uname），而二级索引中恰恰包含这些数据（二级索引叶子节点存放uname 和主键 id），二级索引树比主键索引树小很多，所以直接挨个查询二级索引要比挨个查询主键索引要快的多，故用的是二级索引。

当表添加一个age字段，引出情况二

情况二：like 后面的'%hr'/ '%hr%'这两种情况用不到索引 uname。

经过测试发现，单个的普通索引 uname 也是联合索引，所以也适用于最左匹配原则，'hr'/ 'hr%' 查询才可以用到 uname。

把 select * 改为 select id / select uname / select id,uname，引出情况三

情况三：like 后面'j'/ 'j%' / '%j'/ '%j%' 这四种情况又都可以用到 uname 普通索引。

如果返回值只包含（id，uname）的话，覆盖索引就能拿到所有数据，所以这种情况下 like 后面的多种条件都可以走到普通索引。但是如果select * 的话，返回数据由于包含了 age 字段，而 age 字段在 uname 的覆盖索引中查询不到需要二次回表，因此便走不到索引。

建立 uname,age 的联合索引，引出情况四

情况四：模糊查询还是 LIKE 'j'/ 'j%' / '%j'/ '%j%'四种情况，其中select id / select uname / select id,uname 会用到 uname的普通索引；select * 会用到 uname,age 的组合索引。

如果返回值只包含（id，uname）的话，覆盖索引就能拿到所有数据，所以这种情况下 like 后面的多种条件都可以走到普通索引。如果查询条件变为 select * 的话联合索引在这时候就是最优的覆盖索引了，所以走到了联合索引。

索引下推

索引下推是 MySQL5.6 版本引入的技术优化，该技术只能用在联合索引上。

还是以t表来举例，其中（name,age）为联合索引，如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的记录”。那么，SQL 语句是这么写的：

select * from t where name like '张%' and age=10;

你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。当然，这还不错，总比全表扫描要好。那接下来呢？

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

扩展

范围查询比 in 查询效率高

如下案例：

1.select * from T where k in(1,2,3,4,5)
2.select * from T where k between 1 and 5

1.in 内部的数字是未知的，不知道是否有序，是否连续等，所以需要搜索 5次。

2.一个已知的升序、范围查询，只需搜索一次，定位第一个值，后面遍历就行了。

对于下述表结构中关于索引的配置，你有什么建议？

CREATE TABLE geek (
  a int(11) NOT NULL,
  b int(11) NOT NULL,
  c int(11) NOT NULL,
  d int(11) NOT NULL,
  PRIMARY KEY (a,b),
  KEY c (c),
  KEY ca (c,a),
  KEY cb (c,b)
) ENGINE=InnoDB;

因为有这样的业务需求：

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

首先给出答案：建议去除索引 ca。

主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。

–a--|–b--|–c--|–d--
1 2 3 d
1 3 2 d
1 4 3 d
2 1 3 d
2 2 2 d
2 3 4 d

InnoDB 会把主键字段放到索引定义字段后面，所以当主键为（a，b）时，定义为 c 的索引，实际上是（c，a，b），定义为（c，a）的索引，实际上是（c，a，b）。

索引 c 是先按 c 排序，因为记录了主键 a，b，所以再按 a，b排序

–c--|–a--|–b-- 
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键

–c--|–a--|–主键部分b-- （注意，这里不是 ab，而是只有 b）
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3

这个跟索引 c 的数据是一模一样的。

索引 cb 的组织是先按 c 排序，再按 b 排序，同时记录主键

–c--|–b--|–主键部分a-- （注意，这里不是 ab，而是只有 a）
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2

所以，结论是 ca 可以去掉，cb 需要保留。

关于索引的选择，如何选择主键索引还是二级索引？

CREATE TABLE `t_ma` (
  `id` int(11) NOT NULL,
  `age` int(11),
  PRIMARY KEY (`id`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t_ma values(3,33);
insert into t_ma values(1,11);
insert into t_ma values(5,55);
insert into t_ma values(2,22);

select * from t_ma;

insert into t_ma values(4,null);
insert into t_ma values(6,null);

select * from t_ma;
explain select * from t_ma;

上述 SQL 语句， 每个语句explain下，看看怎么使用索引的，对照再分析看看。

因为二级索引中包含所有显示字段信息，两次检索走的都是二级索引，null值在二级索引中是排在最左边的，所以null会显示在前面。

普通索引范围查询可能走全表扫描

// 假设t1中有100条数据
explain select * from t1 where t1.a < 19;

N 值大于 19则会走全表扫描。

参考文献

Innodb页合并和页分裂

mysql删除那点事