V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
andybest
V2EX  ›  问与答

MySQL 关于查询时扫描行数与索引的疑问

  •  
  •   andybest · 2015-03-27 12:41:22 +08:00 · 2220 次点击
    这是一个创建于 3368 天前的主题,其中的信息可能已经有所发展或是发生改变。
    测试表:
    CREATE TABLE `table_1` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` text NOT NULL,
    `category_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;

    其中 id 字段是自增主键

    插入 30 行用于测试的数据:
    insert into table_1 (`category_id`)values(1);
    insert into table_1 (`category_id`)values(1);
    insert into table_1 (`category_id`)values(1);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(2);
    insert into table_1 (`category_id`)values(3);
    insert into table_1 (`category_id`)values(3);
    insert into table_1 (`category_id`)values(3);
    insert into table_1 (`category_id`)values(4);
    insert into table_1 (`category_id`)values(4);
    insert into table_1 (`category_id`)values(4);
    insert into table_1 (`category_id`)values(5);
    insert into table_1 (`category_id`)values(5);
    insert into table_1 (`category_id`)values(5);

    mysql> explain select * from `table_1` order by `id` DESC limit 0,5;
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
    | 1 | SIMPLE | table_1 | index | NULL | PRIMARY | 4 | NULL | 5 | |
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------+
    1 row in set

    这个很好理解,因为 id 是主键,查询中只使用了 order by id ,查询涉及记录行数 rows 5,因为 limit 0,5

    mysql> explain select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | table_1 | index | NULL | PRIMARY | 4 | NULL | 5 | Using where |
    +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
    1 row in set

    这个就无法理解了,为什么使用了 where `category_id`=2 ,用一个非索引字段 where ,该查询涉及的记录数仍然是 5 ?将 `category_id`=2 改为任何数字,rows 都为 5,实际记录前几条并不是 `category_id`=2 ,按理应该先跳过 `category_id`!=2 的然后筛选出符合的结果返回,这样涉及的行数应该大于 5 啊

    更无法理解的是,如果使用该表 category_id 建立索引,同样该 SQL 执行结果:
    mysql> explain select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;
    +----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
    | 1 | SIMPLE | table_1 | ref | category_id | category_id | 4 | const | 18 | Using where |
    +----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
    1 row in set

    也就是 where `category_id`=2 涉及行数成了 `category_id`=2 记录的总数!也就是 18 条

    那么如果数据库中有1千万条数据,均分至 category_id 1-10 的话,这时候需要执行:
    select * from `table_1` where `category_id`=2 order by `id` DESC limit 0,5;
    是否需要建立 category_id 索引呢?如果建立每次都要扫描 100 万条索引记录吗?如果不建立任何索引,该 SQL 是否会存在性能问题?
    1 条回复    2015-03-27 14:56:51 +08:00
    coosir
        1
    coosir  
       2015-03-27 14:56:51 +08:00   ❤️ 1
    第二个用到了索引是id,因为limit所以5条就够了,type为index
    第三个用到了索引category_id,虽然行数多,但是type是ref
    按说type为index还是要扫全表,那么还是建议给category_id加索引
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2389 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 03:40 · PVG 11:40 · LAX 20:40 · JFK 23:40
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.