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
stabc 2018-01-08 20:44:01 +08:00
你能稍微就数据解释一下以便节省别人的分析时间么?
|
2
kn007 2018-01-08 20:51:34 +08:00
建个联合索引看看?
|
3
kn007 2018-01-08 20:52:41 +08:00
如果联合索引还是慢,可以复合 select 看看。
|
4
kn007 2018-01-08 20:53:52 +08:00
另外你可以看到,第一个查询并没有用到 index,而是直接 where 了。
|
5
kn007 2018-01-08 20:54:39 +08:00
感觉还是少了联合索引,把 year 删掉,建个 (year,type)吧。
|
6
lyog 2018-01-08 20:59:10 +08:00 via Android
like % 导致索引失效了吧
|
7
odirus 2018-01-08 21:07:51 +08:00
瞎猜一下,你的 type 字段是不是存在大量的 null 数据?
|
8
chenqh 2018-01-08 21:10:02 +08:00
感觉是 MMR 的问题吧
|
9
chenqh 2018-01-08 21:11:26 +08:00
不过第二个为什么会有 using index condition 啊
|
10
alcarl 2018-01-08 22:11:26 +08:00 via Android
用手机看得我眼快瞎了,也没看明白你在哪里建了什么索引。。。。。第一个慢可能是因为 mysql 傻傻的觉着走 type 索引更好,但没想到 type 弄出来的数太多了,在里面找了好久才找到 2017 年的吧。mysql 的大量数据查询一直是让人着急的弱
|
11
bigpigeon 2018-01-09 08:39:24 +08:00
你是不是用了组合 index,能不能用 show create table 看看表结构
|
12
checgg OP 谢谢楼上的回答。
|