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

免费领取阿里云优惠券 我的直播 - 《PHP 进阶之路》

有张表

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)

嗨,老铁,欢迎来到我的博客!

如果觉得我的内容还不错的话,可以关注下我在 segmentfault.com 上的直播。我主要从事 PHP 和 Java 方面的开发,《深入 PHP 内核》作者之一。

[视频直播] PHP 进阶之路 - 亿级 pv 网站架构的技术细节与套路 直播中我将毫无保留的分享我这六年的全部工作经验和踩坑的故事,以及会穿插着一些面试中的 考点难点加分点

评论列表