分享
  • 收藏
    X
    关于覆盖索引,多列联合索引,不满足最左前缀原则也能命中索引?
    53
    0

    有一张 file 表,(shareid,uk,pid) 三个字段建立了联合索引,表数据量大概500万条。

    CREATE TABLE `file` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `fs_id` bigint(20) unsigned NOT NULL COMMENT '文件ID',
      `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '文件名',
      `shareid` bigint(20) unsigned NOT NULL,
      `uk` bigint(20) NOT NULL DEFAULT '0' COMMENT '用户ID',
      `pid` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_share_uk_pid` (`shareid`,`uk`,`pid`) USING BTREE
    ) ENGINE=InnoDB

    现在要通过 pid 查询 idfs_id 两列:

    方法一、

    使用 pid 作为查询条件进行查询,执行计划显示使用了覆盖索引。

    mysql> EXPLAIN SELECT SQL_NO_CACHE `id` FROM file WHERE pid='fRLr9QLMjDKgwkHF0f1lmg' limit 1;
    +----+-------------+-------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
    | id | select_type | table | partitions | type  | possible_keys | key              | key_len | ref  | rows    | filtered | Extra                    |
    +----+-------------+-------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
    |  1 | SIMPLE      | file  | NULL       | index | NULL          | idx_share_uk_pid | 50      | NULL | 5351369 |    10.00 | Using where; Using index |
    +----+-------------+-------+------------+-------+---------------+------------------+---------+------+---------+----------+--------------------------+
    1 row in set (0.07 sec)

    查询 id 使用的实际时间是 1.08 秒

    mysql> SELECT SQL_NO_CACHE `id` FROM file WHERE pid='fRLr9QLMjDKgwkHF0f1lmg' limit 1;
    +---------+
    | id      |
    +---------+
    | 5416222 |
    +---------+
    1 row in set (1.08 sec)

    再通过 id 查 fs_id 所用的之间只有 0.08 秒。

    mysql> SELECT SQL_NO_CACHE `id`,`fs_id` FROM file WHERE id=5416222;
    +---------+-----------------+
    | id      | fs_id           |
    +---------+-----------------+
    | 5416222 | 773216656487988 |
    +---------+-----------------+
    1 row in set (0.08 sec)

    方法二

    当企图直接用一次查询查出 id 和 fs_id 时:

    mysql> EXPLAIN SELECT SQL_NO_CACHE `id`,`fs_id` FROM file WHERE pid='fRLr9QLMjDKgwkHF0f1lmg' limit 1;
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    |  1 | SIMPLE      | file  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5351369 |    10.00 | Using where |
    +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
    1 row in set (0.31 sec)

    执行计划显示没有用到索引

    mysql> SELECT SQL_NO_CACHE `id`,`fs_id` FROM file WHERE pid='fRLr9QLMjDKgwkHF0f1lmg' limit 1;
    +---------+-----------------+
    | id      | fs_id           |
    +---------+-----------------+
    | 5416222 | 773216656487988 |
    +---------+-----------------+
    1 row in set (20.89 sec)

    这条语句消耗了 20 秒的时间,将近是上面两条语句消耗时间的和的20倍。


    问题:

    1. 为什么不满足最左前缀原则也能命中覆盖索引?
    2. InnoDB的查询不就是先从普通索引中查出主键,再利用主键回表去查询其他的列。方法一就是模拟的这个过程,为什么方法二回比方法一慢这么多?
    5
    打赏
    收藏
    点击回答
    您的回答被采纳后将获得:提问者悬赏的 10 元积分
        全部回答
    • 0
    更多回答
    扫一扫访问手机版

    回答动态

    设计达人:发布了悬赏问题9414754预计能赚取 0积分收益
    ID也是麻烦事:发布了悬赏问题9414754预计能赚取 0积分收益
    视觉设计:发布了悬赏问题9414754预计能赚取 0积分收益
    爱素材:发布了悬赏问题9414754预计能赚取 0积分收益
    顺姐财姐:发布了悬赏问题9414754预计能赚取 0积分收益
    還好有妳、:发布了悬赏问题9414754预计能赚取 0积分收益
    傷感男丶:发布了悬赏问题9414754预计能赚取 0积分收益
    wcjadmin:发布了悬赏问题9414754预计能赚取 0积分收益
    ╰╮琉璃苣:发布了悬赏问题9414754预计能赚取 0积分收益
    第一财经:发布了悬赏问题9414754预计能赚取 0积分收益