SELECT * FROM uchome_feed USE INDEX(dateline)
WHERE uid IN ('0',20662,19796,17003,15594,15400,12243,11283,10543,10040,7886,7030,1869,834)
ORDER BY dateline DESC
LIMIT 0,100;这是UCHOME社区的查询,uchome_feed和uchome_member都是20万的数据mysql> explain SELECT * FROM uchome_feed USE INDEX(dateline)
-> WHERE uid IN ('0',20662,19796,17003,15594,15400,12243,11283,10543,10040,7886,7030,1869,834)
-> ORDER BY dateline DESC
-> LIMIT 0,100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: uchome_feed
type: index
possible_keys: NULL
key: dateline
key_len: 4
ref: NULL
rows: 229768
Extra: Using where去掉use index时:mysql> explain SELECT * FROM uchome_feed WHERE uid IN ('0',20662,19796,17003,15594,15400,12243,11283,10543,10040,7886,7030,1869,834) ORDER BY dateline DESC LIMIT 0,100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: uchome_feed
type: range
possible_keys: uid
key: uid
key_len: 3
ref: NULL
rows: 19
Extra: Using where; Using filesort
1 row in set (0.00 sec)1 row in set (0.00 sec)问题是去掉了以后不再是慢查询了,但是有Using filesort。要是数据量大了也会引起慢查询。
UCHOME_FEED的索引如下:
mysql> show index from uchome_feed;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| uchome_feed | 0 | PRIMARY | 1 | feedid | A | 230274 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | uid | 1 | uid | A | 4699 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | uid | 2 | dateline | A | 230274 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | dateline | 1 | dateline | A | 230274 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | hot | 1 | hot | A | 341 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | id | 1 | id | A | 14392 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | id | 2 | idtype | A | 32896 | NULL | NULL | | BTREE | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)
特来请教 该如何优化,避免排序,谢谢
WHERE uid IN ('0',20662,19796,17003,15594,15400,12243,11283,10543,10040,7886,7030,1869,834)
ORDER BY dateline DESC
LIMIT 0,100;这是UCHOME社区的查询,uchome_feed和uchome_member都是20万的数据mysql> explain SELECT * FROM uchome_feed USE INDEX(dateline)
-> WHERE uid IN ('0',20662,19796,17003,15594,15400,12243,11283,10543,10040,7886,7030,1869,834)
-> ORDER BY dateline DESC
-> LIMIT 0,100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: uchome_feed
type: index
possible_keys: NULL
key: dateline
key_len: 4
ref: NULL
rows: 229768
Extra: Using where去掉use index时:mysql> explain SELECT * FROM uchome_feed WHERE uid IN ('0',20662,19796,17003,15594,15400,12243,11283,10543,10040,7886,7030,1869,834) ORDER BY dateline DESC LIMIT 0,100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: uchome_feed
type: range
possible_keys: uid
key: uid
key_len: 3
ref: NULL
rows: 19
Extra: Using where; Using filesort
1 row in set (0.00 sec)1 row in set (0.00 sec)问题是去掉了以后不再是慢查询了,但是有Using filesort。要是数据量大了也会引起慢查询。
UCHOME_FEED的索引如下:
mysql> show index from uchome_feed;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| uchome_feed | 0 | PRIMARY | 1 | feedid | A | 230274 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | uid | 1 | uid | A | 4699 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | uid | 2 | dateline | A | 230274 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | dateline | 1 | dateline | A | 230274 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | hot | 1 | hot | A | 341 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | id | 1 | id | A | 14392 | NULL | NULL | | BTREE | |
| uchome_feed | 1 | id | 2 | idtype | A | 32896 | NULL | NULL | | BTREE | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
7 rows in set (0.00 sec)
特来请教 该如何优化,避免排序,谢谢
SELECT * FROM uchome_feed USE INDEX(dateline)
WHERE uid IN ('0',20662,19796,17003,15594,15400,12243,11283,10543,10040,7886,7030,1869,834)
ORDER BY dateline DESC
LIMIT 0,100;
来说。不应该用INDEX(dateline),