免费领取阿里云优惠券 我的直播 - 《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 存放了。