菜单开关

周梦康 发表于 2017-12-26 738 次浏览 标签 : Mysql

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

建表语句

mysql> show create table seo_php_article\G;
*************************** 1. row ***************************
       Table: seo_php_article
Create Table: CREATE TABLE `seo_php_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=135276 DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

根据一般的经验,这种分布范围非常小的字段is_delete不建议加索引

mysql> select is_delete,sum(1) from seo_php_article group by is_delete;
+-----------+--------+
| is_delete | sum(1) |
+-----------+--------+
|         0 | 135233 |
|         2 |     42 |
+-----------+--------+

比如下面的查询能命中主键

mysql> explain SELECT id FROM seo_php_article where is_delete=0 ORDER BY id DESC limit 5000,500;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | seo_php_article | index | NULL          | PRIMARY | 4       | NULL | 5500 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+

但是下面这个就不能使用到index_createtime索引了

mysql> explain SELECT id FROM seo_php_article WHERE is_delete=0 ORDER BY create_time DESC limit 5000,500;
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | seo_php_article | ALL  | NULL          | NULL | NULL    | NULL | 137630 | Using where; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+

方案 1

这样的话,我就加个索引吧

ALTER TABLE `seo_php_article` ADD INDEX `idx_is_del_ts` (`is_delete`, `create_time`);

上面的查询没有再有filesort了,也不再是全表扫描了。

mysql> explain SELECT id FROM seo_php_article where is_delete=0 ORDER BY create_time DESC limit 5000,500;
+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+--------------------------+
| id | select_type | table           | type | possible_keys | key           | key_len | ref   | rows  | Extra                    |
+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+--------------------------+
|  1 | SIMPLE      | seo_php_article | ref  | idx_is_del_ts | idx_is_del_ts | 1       | const | 68815 | Using where; Using index |
+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+--------------------------+

但是注意到这里居然扫描了68815行。

新的问题

但是之前 is_delete 作为筛选条件,然后根据 id 排序的查询则会自动命中新增加的索引

mysql> explain SELECT id FROM seo_php_article where is_delete=0 ORDER BY id DESC limit 5000,500;
+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+------------------------------------------+
| id | select_type | table           | type | possible_keys | key           | key_len | ref   | rows  | Extra                                    |
+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+------------------------------------------+
|  1 | SIMPLE      | seo_php_article | ref  | idx_is_del_ts | idx_is_del_ts | 1       | const | 68815 | Using where; Using index; Using filesort |
+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+------------------------------------------+

方案 2

这里我们使用强制指定其索引为主键,就好了,需要注意。

mysql> explain SELECT id FROM seo_php_article force index(PRI) where is_delete=0 ORDER BY id DESC limit 5000,500;
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | seo_php_article | index | NULL          | PRIMARY | 4       | NULL | 5500 | Using where |
+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+

也就是说前面那个查询我们也可以不加索引,而是直接强制指定其索引为index_createtime,我又测试了下:

ALTER TABLE `seo_php_article` DROP INDEX `idx_is_del_ts`;

mysql> explain SELECT id FROM seo_php_article where is_delete=0 ORDER BY create_time DESC limit 5000,500;
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table           | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | seo_php_article | ALL  | NULL          | NULL | NULL    | NULL | 137630 | Using where; Using filesort |
+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.04 sec)

mysql> explain SELECT id FROM seo_php_article force index(index_createtime) where is_delete=0 ORDER BY create_time DESC limit 5000,500;
+----+-------------+-----------------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table           | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+----+-------------+-----------------+-------+---------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | seo_php_article | index | NULL          | index_createtime | 9       | NULL | 5500 | Using where |
+----+-------------+-----------------+-------+---------------+------------------+---------+------+------+-------------+
1 row in set (0.04 sec)

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

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

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

评论列表