菜单开关

周梦康 发表于 2018-01-09 953 次浏览 标签 : Mysql未解之谜

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

背景

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)

实验结果

  1. 在分页查询时,当 offset 不是太大的时候,使用强制优势比较明显
  2. 当 offset 到了整个数据行95%左右,使用强制索引反而不如全表扫描了
  3. 当 offset 到了整个数据行95%左右,把分布比较小的字段(比如is_delete 0/1 分布的情况)添加到联合索引,速度也会有所提升
  4. datetime 对比 int 类型的索引,性能损耗可忽略

实验过程

当 offset 不是太大的时候(比如10000),使用强制优势还是比较明显

mysql> explain SELECT id  FROM seo_ziliao_article force index(`index_createtime`) WHERE is_delete=0 ORDER BY create_time DESC LIMIT 10000,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 | 10020 | Using where |
+----+-------------+--------------------+-------+---------------+------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT id  FROM seo_ziliao_article WHERE is_delete=0 ORDER BY create_time DESC LIMIT 10000,20;
+----+-------------+--------------------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table              | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+--------------------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | seo_ziliao_article | ALL  | NULL          | NULL | NULL    | NULL | 172736 | Using where; Using filesort |
+----+-------------+--------------------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

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

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

当 offset 继续增大的时候(比如170000,数据库总行数178523),使用强制优势反而变慢了

mysql> SELECT id  FROM seo_ziliao_article force index(`index_createtime`) WHERE is_delete=0 ORDER BY create_time DESC LIMIT 170000,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 170000,20;
...
20 rows in set (0.14 sec)

分布比较小的字段(比如is_delete 0/1 分布的情况)添加到联合索引,速度是使用强制索引的三倍

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

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

datetime 对比 int 类型的索引,性能损耗可忽略

已知的是分页末尾导致的数据查询变慢,但是20万行不到也不算什么大表吧?

如果把 create_time 的 datetime 类型换成 int 类型,会快一些吗?

我把数据转换之后,并且添加了索引,查询响应时间来看没有什么变化。也就是说我们使用datetime类型建立索引是没关系的。

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

mysql> SELECT id  FROM seo_ziliao_article force index(`create_ts`) WHERE is_delete=0 ORDER BY create_ts DESC LIMIT 90000,20;
...
20 rows in set (0.15 sec)

问题还未解决

暂时把排好序的列表用 zset 存放了。

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

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

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

评论列表