背景
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)
实验结果
- 在分页查询时,当 offset 不是太大的时候,使用强制优势比较明显
- 当 offset 到了整个数据行95%左右,使用强制索引反而不如全表扫描了
- 当 offset 到了整个数据行95%左右,把分布比较小的字段(比如
is_delete
0/1 分布的情况)添加到联合索引,速度也会有所提升 - 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 存放了。