7.单表优化案例

数据准备

CREATE TABLE IF NOT EXISTS `article` (
  `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `author_id` INT(10) UNSIGNED NOT NULL ,     -- 作者id
  `category_id` INT(10) UNSIGNED NOT NULL ,   -- 分类id
  `views` INT(10) UNSIGNED NOT NULL ,         -- 被查看的次数
  `comments` INT(10) UNSIGNED NOT NULL ,      -- 评论备注
  `title` VARCHAR(255) NOT NULL ,             -- 标题
  `content` TEXT NOT NULL                     -- 内容
);

INSERT INTO `article` (`author_id`, `category_id`,`views`, `comments`, `title`, `content`) VALUES
  (1,1,1,1,1,1),
  (2,2,2,2,2,2),
  (1,1,3,3,3,3);

SELECT * from article;

需求

查询category_id为1,且commonts大于1的情况下,views最多的article_id

SELECT id, author_id FROM article WHERE category_id = 1 and comments >1 ORDER BY views DESC LIMIT 1;

分析

mysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 and comments >1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.01 sec)


  • 由于type为ALL,所以进行了全表扫描。
  • extra字段包含了Using filesort,所以进行了文件内排序。
  • 以上两点证明了,sql性能并不好

开始优化

1.建索引

把where后面的字段全部建上索引

两种建索引的方式

ALTER TABLE `article` ADD INDEX idx_article_ccv(`category_id`, `comments`, `views`)
create index idx_article_ccv on article(category_id, comments, views)

查看索引

mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

再次分析

mysql>  EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 and comments >1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

  • 查询用到了索引
  • 但是文件内排序依然存在。

结论

  • type变成range,这是可以忍受的,但是extra里使用Using filesort仍是无法接受的
  • 但是我们已经建立了索引,为啥没用呢 ?
  • 这是因为按照BTree索引的工作原理,先排序category_id,如果遇到相同category_id,再排序comments,如果遇到相同的comments则再排序views.
  • 当comments字段在联合索引里处于中间位置时,因comments > 1条件是一个范围值(所谓range),MySQL无法利用索引再对后面的views进行检索,即range类型查询的字段后面的索引无效。

##进一步优化

1.删除索引

DROP INDEX idx_article_ccv ON article;

2.再建索引

create index idx_article_cv on article(category_id , views)

3.再分析

mysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 and comments >1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_article_cv | idx_article_cv | 4       | const |    2 |    33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

此时文件内排序已经没有了。