周梦康 发表于 2018-11-22 792 次浏览

有张表

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  `c` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_abc` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  2115625 |
+----------+
1 row in set (0.31 sec)

mysql> explain select * from test where a=2356 and b in (2,3) order by c desc limit 1000,10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 8       | NULL | 9784 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from test where a=2356 and b in (2,10) order by c desc limit 1000,10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 8       | NULL | 8965 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

评论列表