菜单开关

周梦康 发表于 2018-01-08 600 次浏览 标签 : Mysql

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

接着上篇 https://mengkang.net/1123.html 继续说这个

CREATE TABLE `seo_ziliao_article` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `user_id` bigint(20) NOT NULL DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `is_delete` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `viewcount` int(11) unsigned NOT NULL DEFAULT '0',
  `data_from` varchar(255) NOT NULL DEFAULT '' COMMENT '数据来源',
  `clean_level` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '数据清理等级',
  `is_short` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '文章太短',
  PRIMARY KEY (`id`),
  KEY `index_title` (`title`),
  KEY `index_createtime` (`create_time`),
  KEY `index_userid` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=178524 DEFAULT CHARSET=utf8 COMMENT='SEO站'

is_delete字段在本例中分布情况,只有0和1

mysql> select is_delete,count(*) from seo_ziliao_article group by is_delete;
+-----------+----------+
| is_delete | count(*) |
+-----------+----------+
|         0 |   176313 |
|         1 |     2210 |
+-----------+----------+
2 rows in set (0.08 sec)

当我在通过create_time排序的时候,有和没有is_delete,从 explain 上看没区别

mysql> explain SELECT id  FROM seo_ziliao_article force index(`index_createtime`) WHERE is_delete=0 ORDER BY create_time DESC LIMIT 170477,20;
+----+-------------+--------------------+-------+---------------+------------------+---------+------+--------+-------------+
| id | select_type | table              | type  | possible_keys | key              | key_len | ref  | rows   | Extra       |
+----+-------------+--------------------+-------+---------------+------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | seo_ziliao_article | index | NULL          | index_createtime | 6       | NULL | 170497 | Using where |
+----+-------------+--------------------+-------+---------------+------------------+---------+------+--------+-------------+
1 row in set (0.01 sec)


mysql> explain SELECT id  FROM seo_ziliao_article ORDER BY create_time DESC LIMIT 170477,20;
+----+-------------+--------------------+-------+---------------+------------------+---------+------+--------+-------------+
| id | select_type | table              | type  | possible_keys | key              | key_len | ref  | rows   | Extra       |
+----+-------------+--------------------+-------+---------------+------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | seo_ziliao_article | index | NULL          | index_createtime | 6       | NULL | 170497 | Using index |
+----+-------------+--------------------+-------+---------------+------------------+---------+------+--------+-------------+
1 row in set (0.00 sec)

实际查询

mysql> SELECT id  FROM seo_ziliao_article ORDER BY create_time DESC LIMIT 170477,20;
...
20 rows in set (0.06 sec)

mysql> SELECT id  FROM seo_ziliao_article force index(`index_createtime`) WHERE is_delete=0 ORDER BY create_time DESC LIMIT 170477,20;
...
20 rows in set (0.23 sec)

上面的查询不是偶然,我连续测试N次都是下面的sql特别慢。

增加索引

mysql> ALTER TABLE `seo_ziliao_article` ADD INDEX (`is_delete`, `create_time`);
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT id  FROM seo_ziliao_article force index(`index_createtime`) WHERE is_delete=0 ORDER BY create_time DESC LIMIT 170477,20;
...
20 rows in set (0.26 sec)

mysql> SELECT id  FROM seo_ziliao_article WHERE is_delete=0 ORDER BY create_time DESC LIMIT 170477,20;
...
20 rows in set (0.09 sec)

实验证明,即使这个字段分布范围特别小,比如本例中的01,还是要建立联合索引,才能查询的更快。
背后的原理,希望能联系到 dba 和我们一起分析。

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

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

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

评论列表