V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
15hop
V2EX  ›  程序员

mysql 查询类型 index 和 all 查询效率上的区别

  •  
  •   15hop · 2020-07-09 12:50:10 +08:00 · 2429 次点击
    这是一个创建于 819 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表结构

    CREATE TABLE `query_test` (
    	`ID` INT NOT NULL AUTO_INCREMENT,
    	`NAME` VARCHAR(64) DEFAULT '',
    	PRIMARY KEY (ID)
    ) ENGINE = InnoDB DEFAULT CHARSET = utf8;
    

    构造 50w 数据

    DELIMITER $$
    CREATE PROCEDURE insert_query_test(IN cnt INTEGER, IN tb VARCHAR(64))
    BEGIN
    	DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    	DECLARE return_str varchar(255) DEFAULT '';
    	DECLARE i int;
    	set i=1;
    	while i<=cnt DO
    		SET return_str = concat(return_str, substring(char_str, FLOOR(1 + RAND()*62), 1));
    		set @stmt = CONCAT('INSERT INTO ',tb,' (NAME) VALUES("',return_str,'");'); 
    		PREPARE stmt_pre FROM @stmt;
    		EXECUTE stmt_pre;
    		DEALLOCATE PREPARE stmt_pre;
    		SET i = i + 1;
    	end while;
    END$$
    DELIMITER ;
    

    对比

    • 查询 1,扫了全表,type=all,耗时 4.775s
    SELECT * FROM query_test;
    
    • 查询 2,扫了主键索引树,type=index,耗时 1.901s 。extra 提示 using index,只
    SELECT ID FROM query_test;
    

    问题

    从聚簇索引的结构上看,叶子节点实际存储了索引值和具体的行数据。有一点不解的是,两个查询应该都扫描了聚簇索引的叶子节点(包括索引键和行数据),但是为什么查询 2 和快于查询 1 呢?

    会不会是select *的查询数据量多于select id造成的?

    15 条回复    2020-07-09 21:59:17 +08:00
    flyfanc
        1
    flyfanc  
       2020-07-09 13:04:51 +08:00
    查询 2 直接从索引中获取数据,查询 1 还要回表,获取除 id 外的其它值
    limuyan44
        2
    limuyan44  
       2020-07-09 13:05:31 +08:00
    id 不是 key 吗,二为什么会扫描叶子节点呢
    JasonLaw
        3
    JasonLaw  
       2020-07-09 13:08:23 +08:00 via iPhone
    用 SELECT NAME FROM query_test 的结果如何呢?
    JasonLaw
        4
    JasonLaw  
       2020-07-09 13:09:36 +08:00 via iPhone
    @flyfanc 两个都是使用 clustered index 吧
    optional
        5
    optional  
       2020-07-09 13:26:02 +08:00
    mysql 不是只有一个 clustered index 吗。

    这里的对比不公平吧,虽然扫描方式类似,但是第二个不用拷贝返回数据啊。
    optional
        6
    optional  
       2020-07-09 13:30:25 +08:00
    clustered index 与 non-clustered 的区别是值的区别啊,一个存 id,一个存 offset,这里 select id 没必要去访问 leaf 吧。
    mayday526
        7
    mayday526  
       2020-07-09 13:46:26 +08:00
    只有第二个用到了聚簇索引,第一个是全表扫描; InnoDB 的叶子节点是链表结构连起来的,所以并没有走索引,而是直接遍历链表全表扫描了;第二个会用到聚簇索引的原因是,所查的字段刚好是索引的 key,这叫覆盖索引,直接获取索引 key 返回就行了
    chihiro2014
        8
    chihiro2014  
       2020-07-09 13:51:27 +08:00
    吐槽一句,你的 50w 数据,可能没插几条,就插不动了。

    MySQL 索引我记得默认 B+ Tree,叶子节点上保存的是对应的索引 id ( record id ),你第一个相当于全表扫描不走索引,自然慢。
    第二个走了索引,所以快,但是你返回的只是 id 也就是索引 key,又不是 id 所对应的数据。。这速度当然有问题
    wangyzj
        9
    wangyzj  
       2020-07-09 14:05:03 +08:00
    ```
    mysql> explain SELECT name FROM query_test;
    +------+-------------+------------+------+---------------+------+---------+------+--------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +------+-------------+------------+------+---------------+------+---------+------+--------+-------+
    | 1 | SIMPLE | query_test | ALL | NULL | NULL | NULL | NULL | 497500 | |
    +------+-------------+------------+------+---------------+------+---------+------+--------+-------+
    1 row in set (0.02 sec)

    mysql> explain SELECT id FROM query_test;
    +------+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +------+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+
    | 1 | SIMPLE | query_test | index | NULL | PRIMARY | 4 | NULL | 497500 | Using index |
    +------+-------------+------------+-------+---------------+---------+---------+------+--------+-------------+
    1 row in set (0.02 sec)

    mysql> explain SELECT * FROM query_test;
    +------+-------------+------------+------+---------------+------+---------+------+--------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +------+-------------+------------+------+---------------+------+---------+------+--------+-------+
    | 1 | SIMPLE | query_test | ALL | NULL | NULL | NULL | NULL | 497500 | |
    +------+-------------+------------+------+---------------+------+---------+------+--------+-------+
    1 row in set (0.00 sec)
    ```
    15hop
        10
    15hop  
    OP
       2020-07-09 16:01:10 +08:00
    @mayday526 是说第二个查询也是走主键(聚簇)索引的叶节点链表,只是没有去取其他列数据吗
    996635
        11
    996635  
       2020-07-09 16:50:45 +08:00
    只查 ID 覆盖索引,不用回表.
    Risin9
        12
    Risin9  
       2020-07-09 17:20:05 +08:00 via Android
    楼上说的对,只查 ID,索引覆盖,不用回表取数据了
    15hop
        13
    15hop  
    OP
       2020-07-09 21:19:20 +08:00 via iPhone
    @chihiro2014 是哎 本地插入 20 分钟,每秒差不多 400 条
    JasonLaw
        14
    JasonLaw  
       2020-07-09 21:55:39 +08:00
    @flyfanc #1
    @mayday526 #7
    @chihiro2014 #8
    @996635 #11
    @Risin9 #12

    以下是我做的一个测试。

    1. 创建表(注意:id 和 value 的类型是一样的)

    CREATE TABLE `t`
    (
    `id` int(11) NOT NULL,
    `value` int(11) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE = InnoDB
    DEFAULT CHARSET = utf8mb4;

    2. 插入数据(伪代码),id 和 value 的值是一样的

    for (int i = 1; i < 500000; i++) {
    insert into t values (i, i);
    }

    3. 使用命令行登录进数据库管理系统,并使用特定的数据库

    mysql -u {user} -p{password}
    use database;

    4. 开启 profiling

    SET profiling = 1;

    5. 执行以下语句

    select * from t;
    select id from t;
    select value from t;

    6. `show PROFILES;`的结果如下:

    +----------+------------+---------------------+
    | Query_ID | Duration | Query |
    +----------+------------+---------------------+
    | 1 | 0.24099925 | select * from t |
    | 2 | 0.15437950 | select id from t |
    | 3 | 0.14546525 | select value from t |
    +----------+------------+---------------------+

    参考资料:
    https://dev.mysql.com/doc/refman/5.6/en/show-profile.html
    https://dev.mysql.com/doc/refman/5.6/en/performance-schema-query-profiling.html
    JasonLaw
        15
    JasonLaw  
       2020-07-09 21:59:17 +08:00
    @optional #6
    @15hop #10

    https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

    Every InnoDB table has a special index called the clustered index where the data for the rows is stored.

    Accessing a row through the clustered index is fast because the index search leads directly to the page with all the row data.

    All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2403 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 44ms · UTC 14:16 · PVG 22:16 · LAX 07:16 · JFK 10:16
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.