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

有人能解释一下 mysql 加了索引反而超级慢吗?

  •  
  •   checgg · 2018-01-08 20:24:40 +08:00 · 2557 次点击
    这是一个创建于 2489 天前的主题,其中的信息可能已经有所发展或是发生改变。
    mysql> desc channel_details;
    +---------------+-------------+------+-----+-------------------+----------------+
    | Field         | Type        | Null | Key | Default           | Extra          |
    +---------------+-------------+------+-----+-------------------+----------------+
    | id            | int(11)     | NO   | PRI | NULL              | auto_increment |
    | year          | int(11)     | NO   | MUL | NULL              |                |
    | month         | int(11)     | NO   |     | NULL              |                |
    | point_of_time | varchar(32) | NO   |     | NULL              |                |
    | channel       | varchar(32) | NO   | MUL | NULL              |                |
    | ratings       | float       | NO   |     | NULL              |                |
    | type          | int(11)     | NO   | MUL | NULL              |                |
    | age           | int(11)     | NO   | MUL | NULL              |                |
    | time_at       | datetime    | NO   |     | CURRENT_TIMESTAMP |                |
    +---------------+-------------+------+-----+-------------------+----------------+
    9 rows in set (0.01 sec)
    
    mysql> select * from channel_details where channel like "%" and year>=2017 and year<=2017 and type=29 limit 10;
    +---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
    | id      | year | month | point_of_time | channel     | ratings | type | age | time_at             |
    +---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
    | 7124305 | 2017 |     1 | 02:00         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124306 | 2017 |     1 | 02:01         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124307 | 2017 |     1 | 02:02         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124308 | 2017 |     1 | 02:03         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124309 | 2017 |     1 | 02:04         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124310 | 2017 |     1 | 02:05         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124311 | 2017 |     1 | 02:06         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124312 | 2017 |     1 | 02:07         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124313 | 2017 |     1 | 02:08         | ??????????? |   0.001 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124314 | 2017 |     1 | 02:09         | ??????????? |   0.001 |   29 |  40 | 2017-02-15 15:03:54 |
    +---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
    10 rows in set (19.53 sec)
    
    mysql> select * from channel_details where channel like "%" and year>=2017 and year<=2017 limit 10;
    +---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
    | id      | year | month | point_of_time | channel     | ratings | type | age | time_at             |
    +---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
    | 7124305 | 2017 |     1 | 02:00         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124306 | 2017 |     1 | 02:01         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124307 | 2017 |     1 | 02:02         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124308 | 2017 |     1 | 02:03         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124309 | 2017 |     1 | 02:04         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124310 | 2017 |     1 | 02:05         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124311 | 2017 |     1 | 02:06         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124312 | 2017 |     1 | 02:07         | ??????????? |       0 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124313 | 2017 |     1 | 02:08         | ??????????? |   0.001 |   29 |  40 | 2017-02-15 15:03:54 |
    | 7124314 | 2017 |     1 | 02:09         | ??????????? |   0.001 |   29 |  40 | 2017-02-15 15:03:54 |
    +---------+------+-------+---------------+-------------+---------+------+-----+---------------------+
    10 rows in set (0.07 sec)
    
    mysql> explain select * from channel_details where channel like "%" and year>=2017 and year<=2017 and type=29 limit 10;
    +----+-------------+-----------------+------------+------+---------------+------+---------+-------+---------+----------+-------------+
    | id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref   | rows    | filtered | Extra       |
    +----+-------------+-----------------+------------+------+---------------+------+---------+-------+---------+----------+-------------+
    |  1 | SIMPLE      | channel_details | NULL       | ref  | type,year     | type | 4       | const | 4969150 |     5.56 | Using where |
    +----+-------------+-----------------+------------+------+---------------+------+---------+-------+---------+----------+-------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> explain select * from channel_details where channel like "%" and year>=2017 and year<=2017 limit 10;
    +----+-------------+-----------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------------------------------+
    | id | select_type | table           | partitions | type  | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                         |
    +----+-------------+-----------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------------------------------+
    |  1 | SIMPLE      | channel_details | NULL       | range | year          | year | 4       | NULL | 4969150 |    11.11 | Using index condition; Using where; Using MRR |
    +----+-------------+-----------------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------------------------------+
    
    mysql> select count(*) from channel_details group by year;
    +----------+
    | count(*) |
    +----------+
    |   141218 |
    |  6498910 |
    |  4226453 |
    +----------+
    3 rows in set (4.36 sec)
    
    mysql> select count(*) from channel_details group by type;
    +----------+
    | count(*) |
    +----------+
    |  3573680 |
    |  3647171 |
    |  3645730 |
    +----------+
    3 rows in set (2.54 sec)
    
    mysql> select count(*) from channel_details group by year,type;
    +----------+
    | count(*) |
    +----------+
    |   141218 |
    |  2072158 |
    |  2213376 |
    |  2213376 |
    |  1360304 |
    |  1433795 |
    |  1432354 |
    +----------+
    7 rows in set (27.02 sec)
    

    都是同样的索引类型,速度却相差巨大。有人可以解释一下吗?

    第 1 条附言  ·  2018-01-09 09:58:17 +08:00
    ```
    mysql> show create table channel_details;

    CREATE TABLE `channel_details` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `year` int(11) NOT NULL COMMENT '?',
    `month` int(11) NOT NULL COMMENT '?',
    `point_of_time` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '???',
    `channel` varchar(32) COLLATE utf8_bin NOT NULL COMMENT '???',
    `ratings` float NOT NULL COMMENT '???',
    `type` int(11) NOT NULL COMMENT '?????',
    `age` int(11) NOT NULL COMMENT '???',
    `time_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `age` (`age`),
    KEY `channel` (`channel`),
    KEY `year` (`year`),
    KEY `type` (`type`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11350758 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='?????????????' |
    ```
    第 2 条附言  ·  2018-01-09 10:01:40 +08:00
    集中补充一下楼下的问题。
    1 更多文字解释,我感觉没什么好解释的了,现象就是一个索引字段的区别导致时间相差巨大。
    2 like '%'其实的 like'呵呵%',终端没显示出来。
    3 type 字段没有空数据
    4 根据 count(*)可以看到,year 和 type 的行数是差不多的。
    12 条回复    2018-01-09 10:03:47 +08:00
    stabc
        1
    stabc  
       2018-01-08 20:44:01 +08:00
    你能稍微就数据解释一下以便节省别人的分析时间么?
    kn007
        2
    kn007  
       2018-01-08 20:51:34 +08:00
    建个联合索引看看?
    kn007
        3
    kn007  
       2018-01-08 20:52:41 +08:00
    如果联合索引还是慢,可以复合 select 看看。
    kn007
        4
    kn007  
       2018-01-08 20:53:52 +08:00
    另外你可以看到,第一个查询并没有用到 index,而是直接 where 了。
    kn007
        5
    kn007  
       2018-01-08 20:54:39 +08:00
    感觉还是少了联合索引,把 year 删掉,建个 (year,type)吧。
    lyog
        6
    lyog  
       2018-01-08 20:59:10 +08:00 via Android
    like % 导致索引失效了吧
    odirus
        7
    odirus  
       2018-01-08 21:07:51 +08:00
    瞎猜一下,你的 type 字段是不是存在大量的 null 数据?
    chenqh
        8
    chenqh  
       2018-01-08 21:10:02 +08:00
    感觉是 MMR 的问题吧
    chenqh
        9
    chenqh  
       2018-01-08 21:11:26 +08:00
    不过第二个为什么会有 using index condition 啊
    alcarl
        10
    alcarl  
       2018-01-08 22:11:26 +08:00 via Android
    用手机看得我眼快瞎了,也没看明白你在哪里建了什么索引。。。。。第一个慢可能是因为 mysql 傻傻的觉着走 type 索引更好,但没想到 type 弄出来的数太多了,在里面找了好久才找到 2017 年的吧。mysql 的大量数据查询一直是让人着急的弱
    bigpigeon
        11
    bigpigeon  
       2018-01-09 08:39:24 +08:00
    你是不是用了组合 index,能不能用 show create table 看看表结构
    checgg
        12
    checgg  
    OP
       2018-01-09 10:03:47 +08:00
    谢谢楼上的回答。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2762 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 12:25 · PVG 20:25 · LAX 05:25 · JFK 08:25
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.