查询优化之单表查询

建表

CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(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 且 comments 大于 1 的情况下,views 最多的 article_id

一般

我们一般会使用以下语句查询

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

这个sql语句的type是all，并且Extra里面出现了Using filesort，这个必须优化

添加索引

CREATE INDEX idx_article_ccv ON article(category_id,comments,views);

我们再次进行上面语句的查询

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

我们可以看到type变成了range，但是extra里面却是Using filesort ，这个性能有点差

优化索引

BTree 索引的工作原理

1. 他会先排序category_id
2. 遇到相同的category_id 在排序comments
3. 遇到相同的comments 在排序views

在上面语句中comments > 1 是一个范围值(所谓range)，range 类型查询字段后面的索引无效，所以MySQL 无法利用索引再对后面的 views 部分进行检索

进行优化

1. 我们删除索引后进行新建索引

DROP INDEX idx_article_ccv ON article;

2. 新建索引

CREATE INDEX idx_article_cv ON article(category_id,views);

3. 执行slq语句

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

我们发现type变成了ref，也没有存在Using filesort了