Home Sign Up Sign In
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
Sign Up Now
For Existing Member  Sign In
checgg
V2EX  ›  问与答

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

  •   
  •   checgg · Jan 8, 2018 · 2975 views
    This topic created in 3038 days ago, the information mentioned may be changed or developed.
    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)

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

    Supplement 1  ·  Jan 9, 2018
    ```
    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='?????????????' |
    ```
    Supplement 2  ·  Jan 9, 2018
    集中补充一下楼下的问题。
    1 更多文字解释，我感觉没什么好解释的了，现象就是一个索引字段的区别导致时间相差巨大。
    2 like '%'其实的 like'呵呵%',终端没显示出来。
    3 type 字段没有空数据
    4 根据 count(*)可以看到，year 和 type 的行数是差不多的。
  • 2017-02-15
  • 15:03:54
  • null
  • Year
    12 replies  •  2018-01-09 10:03:47 +08:00
    stabc
        1
    stabc  
       Jan 8, 2018
    你能稍微就数据解释一下以便节省别人的分析时间么？
    kn007
        2
    kn007  
       Jan 8, 2018
    建个联合索引看看？
    kn007
        3
    kn007  
       Jan 8, 2018
    如果联合索引还是慢，可以复合 select 看看。
    kn007
        4
    kn007  
       Jan 8, 2018
    另外你可以看到，第一个查询并没有用到 index，而是直接 where 了。
    kn007
        5
    kn007  
       Jan 8, 2018
    感觉还是少了联合索引，把 year 删掉，建个 (year,type)吧。
    lyog
        6
    lyog  
       Jan 8, 2018 via Android
    like % 导致索引失效了吧
    odirus
        7
    odirus  
       Jan 8, 2018
    瞎猜一下，你的 type 字段是不是存在大量的 null 数据？
    simple2025
        8
    simple2025  
       Jan 8, 2018
    感觉是 MMR 的问题吧
    simple2025
        9
    simple2025  
       Jan 8, 2018
    不过第二个为什么会有 using index condition 啊
    alcarl
        10
    alcarl  
       Jan 8, 2018 via Android
    用手机看得我眼快瞎了，也没看明白你在哪里建了什么索引。。。。。第一个慢可能是因为 mysql 傻傻的觉着走 type 索引更好，但没想到 type 弄出来的数太多了，在里面找了好久才找到 2017 年的吧。mysql 的大量数据查询一直是让人着急的弱
    bigpigeon
        11
    bigpigeon  
       Jan 9, 2018
    你是不是用了组合 index，能不能用 show create table 看看表结构
    checgg
        12
    checgg  
    OP
       Jan 9, 2018
    谢谢楼上的回答。
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Solana   ·   2885 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 65ms · UTC 06:53 · PVG 14:53 · LAX 23:53 · JFK 02:53
    ♥ Do have faith in what you're doing.