• 8.2.1.7 Use of Index Extensions 索引扩展适用


    8.2.1.7 Use of Index Extensions 索引扩展适用

    InnoDB 自动扩展每个secondary index 通过添加主键列到secondary index ,考虑这个表定义:

    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;

    这个表定义了主键列(i1,i2),也定义了一个secondary index k_d 在列(d),

    但是内部的InnoDB 扩展这个index 对待为列(d,i1,i2)

    在MySQL 5.6.9之前, 优化器不考虑把primary key 列加入到扩展 secondary index

    当决定如何和是否使用那个索引

    在5.6.9, 优化器把primary key 列加进去,从而更有效的查询执行计划和更好的性能。

    优化器可以使用 extended secondary indexes 用于ref,range,和index_merge 索引访问,

    对于松散的index scans, 对于关联和排序优化, MIN()/MAX() optimization.

    下面的例子显示执行计划如何被影响通过是否优化器使用extended secondary indexes.

    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’);
    Now consider this query:

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

    优化器不能使用主键在这种情况下,因为组合列(i1,i2),但是查询不涉及i2.

    代替的是,优化器可以使用secondary index k_d on (d), 执行计划取决于是否extended index被使用:

    当优化器不考虑 index extensions, 它对待 index k_d 只作为 (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

    当优化器使用 index extensions ,它对待k_d 索引为(d, i1, i2).

    在这种情况下,它能使用最左边的前缀 (d, i1) 来产生更好的执行计划:

    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

    在这两种情况下, 索引表明优化师使用secondary index k_d但是在优化器输出改进通过使用扩展索引;

    key_len 从4字节到8字节, 表明索引查找使用列 d和i1,不止是d

    ref 值随着常数改变而改变,常值因为key 寻找使用两个key,而不是一个。

    行记录从5降到1, 表明InnoDB 应该需要检测更少的记录来产生结果.

    额外值随着使用WHERE 而改变, 使用index来使用index.这意味着 记录可以被读取只使用一个index,

    而不需要访问表数据的记录。

  • 相关阅读:
    【读书笔记】房龙-人类的故事
    【读书笔记】曼昆-经济学原理:宏观经济学分册
    【读书笔记】曼昆-经济学原理:微观经济学分册
    ES6数组去重
    switch语句
    if语句的优化
    程序的三大结构
    NaN
    js中常见的数据类型
    变量的命名规则
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13351290.html
Copyright © 2020-2023  润新知