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

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

  •   
  •   checgg · 2018-01-08 20:24:40 +08:00 · 2481 次点击
    这是一个创建于 2300 天前的主题，其中的信息可能已经有所发展或是发生改变。
    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 的行数是差不多的。
  • 2017-02-15
  • 15:03:54
  • null
  • Year
    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   ·   我们的愿景   ·   实用小工具   ·   977 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 19:59 · PVG 03:59 · LAX 12:59 · JFK 15:59
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.