专栏首页数据和云MySQL中InnoDB引擎对索引的扩展

MySQL中InnoDB引擎对索引的扩展

摘要:InnoDB引擎对索引的扩展,自动追加主键值及其对执行计划的影响。

MySQL中,使用InnoDB引擎的每个表,创建的普通索引(即非主键索引),都会同时保存主键的值。

比如语句

CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = InnoDB;

创建了t1表,其主键为(i1, i2),同时创建了基于d列的索引k_d,但其实在底层,InnoDB引擎将索引k_d扩展成(d,i1,i2)。

InnoDB引擎这么做,是用空间换性能,优化器在判断是否使用索引及使用哪个索引时会有更多列参考,这样可能生成更高效的执行计划,获得更好的性能。

优化器在ref、range和index_merge类型的访问,Loose Index Scan访问,连接和排序优化, MIN()/MAX()优化时使都会使用扩展列。

我们来看个例子:

root@database-one 15:15:  [gftest]> CREATE TABLE t1 (
    ->   i1 INT NOT NULL DEFAULT 0,
    ->   i2 INT NOT NULL DEFAULT 0,
    ->   d DATE DEFAULT NULL,
    ->   PRIMARY KEY (i1, i2),
    ->   INDEX k_d (d)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.06 sec)

root@database-one 15:15:  [gftest]> INSERT INTO t1 VALUES
    -> (1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
    -> (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
    -> (1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
    -> (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
    -> (2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
    -> (3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
    -> (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
    -> (3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
    -> (4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
    -> (4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
    -> (5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
    -> (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
    -> (5, 5, '2002-01-01');
Query OK, 25 rows affected (0.01 sec)
Records: 25  Duplicates: 0  Warnings: 0

root@database-one 15:21:  [gftest]> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | i1          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | PRIMARY  |            2 | i2          | A         |          25 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_d      |            1 | d           | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

在普通索引中追加扩展主键是InnoDB在底层做的,show index等语句不显示追加列,但我们可以通过其它方式来验证。看这个SQL

SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = ‘2000-01-01’

如果InnoDB没有扩展索引,索引k_d为(d),生成的执行计划应该类似这样,使用k_d索引找到d为’2000-01-01’的5行数据,再回表过滤出i1为3的,最后计算count。或者使用主键索引找到i1为3的5行数据,再回表过滤出d为’2000-01-01’的,最后计算count。下面仅示意走k_d索引的情况:

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 4
          ref: const
         rows: 5
        Extra: Using where; Using index

如果InnoDB扩展了索引,索引k_d为(d,i1,i2),这时,优化器可以使用最左边的索引前缀(d,i1),生成的执行计划应该类似这样,使用k_d索引找到d为’2000-01-01’及i1为3的1行数据,然后计算count

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using index

并且d列是DATE类型占4个字节,i1是INT类型占4个字节,所以查询中使用的键值长度就是8个字节(key_len: 8)。

我们看看实际生成的执行计划

root@database-one 15:35:  [gftest]> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

果然跟我们的判断一致,注意执行计划中的细节:

  • key_len从4字节变为8字节,表明键查找使用列d和i1,而不仅仅是d。
  • ref从const更改为const,const,表明查找使用两个键值,而不是一个。
  • rows从5减少到1,表明检索更少的行。
  • Extra从Using where; Using index改为Using index,表示只用索引读取,不必回表。

InnoDB引擎底层扩展普通索引的情况,也可以通过跟MyISAM引擎对比来进行旁证:

root@database-one 16:07:  [gftest]> CREATE TABLE t1MyISAM (
    ->   i1 INT NOT NULL DEFAULT 0,
    ->   i2 INT NOT NULL DEFAULT 0,
    ->   d DATE DEFAULT NULL,
    ->   PRIMARY KEY (i1, i2),
    ->   INDEX k_d (d)
    -> ) ENGINE = MyISAM;
Query OK, 0 rows affected (0.01 sec)

root@database-one 16:07:  [gftest]> INSERT INTO t1myisam VALUES
    -> (1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
    -> (1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
    -> (1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
    -> (2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
    -> (2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
    -> (3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
    -> (3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
    -> (3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
    -> (4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
    -> (4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
    -> (5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
    -> (5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
    -> (5, 5, '2002-01-01');
Query OK, 25 rows affected (0.02 sec)
Records: 25  Duplicates: 0  Warnings: 0

root@database-one 16:07:  [gftest]> EXPLAIN SELECT COUNT(*) FROM t1myisam WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1myisam
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 4
     filtered: 16.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

可以看到,同样的结构同样的数据,因为MyISAM引擎不会在底层自动扩展普通索引,所以执行计划还是通过主键索引进行处理。

按照官方手册的说明,也可以用SHOW STATUS命令来验证

root@database-one 16:12:  [gftest]> FLUSH TABLE t1;
Query OK, 0 rows affected (0.00 sec)

root@database-one 16:12:  [gftest]> FLUSH STATUS;
Query OK, 0 rows affected (0.14 sec)

root@database-one 16:12:  [gftest]> SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.03 sec)

root@database-one 16:12:  [gftest]> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.01 sec)

root@database-one 16:13:  [gftest]> FLUSH TABLE t1myisam;
Query OK, 0 rows affected (0.01 sec)

root@database-one 16:13:  [gftest]> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

root@database-one 16:13:  [gftest]> SELECT COUNT(*) FROM t1myisam WHERE i1 = 3 AND d = '2000-01-01';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

root@database-one 16:13:  [gftest]> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

Handler_read_next表示在进行索引扫描时,按照索引从数据文件里取数据的次数。使用MyISAM引擎的t1myisam表,Handler_read_next值为5,使用InnoDB引擎的t1表,Handler_read_next值减小到1,就是因为InnoDB引擎对索引进行了主键扩展,读取的次数少,效率更好。

默认情况下,优化器分析InnoDB表的索引时会考虑扩展列,但如果因为特殊原因让优化器不考虑扩展列,可以使用SET optimizer_switch = 'use_index_extensions=off’设置。

root@database-one 16:26:  [gftest]> SET optimizer_switch = 'use_index_extensions=off';
Query OK, 0 rows affected (0.01 sec)

root@database-one 16:26:  [gftest]> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY,k_d
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.02 sec)

墨天轮原文链接:https://www.modb.pro/db/22927

本文分享自微信公众号 - 数据和云(OraNews),作者:巩飞

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-03-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 案例分析:闰秒带来的BUG是否影响了你?

    闰秒如何影响了IT世界?在2016年底我们写下的文章里曾经提到2017开年多出这一秒,大家是否平稳度过?欢迎大家留言讲诉你遇到的真实故事。 毫无疑问,根据墨菲...

    数据和云
  • 案例分析:倾斜值传入导致 SQL 资源消耗升高

    作者 | 邓秋爽:云和恩墨技术工程师,有超过七年超大型数据库专业服务经验,擅长 Oracle 数据库优化、SQL 优化和 Troubleshooting。

    数据和云
  • 经典故障:四个雷,3*2*2*3种随机方法的特殊恢复案例

    墨墨导读:恢复专家前辈给我们精心准备了个故障,埋了四个雷,整个恢复过程感觉像是过山车,整理分享至此,希望对大家有帮助。

    数据和云
  • 一个有趣的时间段重叠问题

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.n...

    用户1148526
  • pytorch基础知识:张量(下)

    其中一维标量主要用于Bias(偏差)中,如在构建神经元中多组数据导入到一个神经元中,由激活函数激活输出一个数值,则该神经元主要使用bias功能。线性层输入(Li...

    用户6719124
  • pytorch基础知识-维度变换-(上)

    维度变换是pytorch中的重要操作,尤其是在图片处理中。本文对pytorch中的维度变换进行讲解。

    用户6719124
  • 丢给你个环形队列玩玩

    假设我需要处理10000个字节的数据,就是串口一次性会发过来10000个字节,然后单片机每次取10个字节处理,然后处理1000次就处理完了

    杨奉武
  • 重叠时间段问题优化算法详解

    这是一个实际业务需求中的问题。某一直播业务表中记录了如下格式的用户进出直播间日志数据:

    用户1148526
  • 十分钟快速了解Pandas的常用操作!

    原文 | https://pandas.pydata.org/pandas-docs/version/0.18.0/

    刘早起
  • 案例分析:闰秒带来的BUG是否影响了你?

    闰秒如何影响了IT世界?在2016年底我们写下的文章里曾经提到2017开年多出这一秒,大家是否平稳度过?欢迎大家留言讲诉你遇到的真实故事。 毫无疑问,根据墨菲...

    数据和云

扫码关注云+社区

领取腾讯云代金券