MySQL查询优化–索引优化资源-CSDN文库

158 浏览量 2020-12-14 12:49:21 上传评论收藏 263KB PDF 举报

资源推荐

资源详情

资源评论

MySQL查询优化查询优化–索引优化索引优化

废话就不多说了，先建一张表

CREATE TABLE IF NOT EXISTS `article`(

`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,#文章id

`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` 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'),(3,1,1,3,'3','3');

查看下全表数据，就三条：

接着我们有这样一个需求：

查询分类查询分类id为为1，评论数大于，评论数大于1，阅读数最多的文章的，阅读数最多的文章的id和作者和作者id，，sql语句很简单，如下所示语句很简单，如下所示

SELECT id, author_id

FROM article

WHERE category_id = 1

AND comments > 1

ORDER BY views DESC

LIMIT 0, 1;

结果是出来了，是我们想要的结果，但是别急，我们来查看下这条sql语句的性能如何

EXPLAIN SELECT id, author_id

FROM article

WHERE category_id = 1

AND comments > 1

ORDER BY views DESC

LIMIT 0, 1;

惊不惊喜？意不意外？ type=ALL是所有类型中最差的：全表查询。Using filesort，文件外部排序。这两个放一起基本上这条SQL语句就废了，不用一百万条数据，一万条基本上就

就可以看出性能下降了。

我们看一下索引情况：

SHOW INDEX FROM article;

除了主键的索引外没有其他索引，所以这条SQL语句性能差也是在情理之中。

根据索引优化的经验，我们知道where 后面的字段，order by、group by 后面的字段，用于确定范围的字段等一般都需要加索引，所以我们先来创建一个复合索引

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

SHOW INDEX FROM article;

没有任何问题，创建成功，我们再看下刚刚那条SQL语句的查询性能如何

EXPLAIN SELECT id, author_id

FROM article

WHERE category_id = 1

AND comments > 1

ORDER BY views DESC

LIMIT 0, 1;

优化了一点点，但这并不是我们想要的结果，而且，最让人讨厌的文件外部排序filesort依然还在。我们不是创建了views的索引了吗？而且观察key=idx_ccv表示我们创建的索引也

用上了，为什么性能还是贼烂呢？

分析一下，我们创建的复合索引:idx_ccv(category_id，comments，views)，在我们执行查询的时候，mysql会先根据category_id的索引找到category_id = 1的记录，然后紧接着根据

comments进行范围查找，这里最多也就是个range级别，所以我们的优化就在这里断了，后面views的索引根本用不上，这也是导致了文件外部排序的直接原因。

那怎么优化呢？我们能不能改变一下复合索引的顺序呢？把views放前面一点。

DROP INDEX idx_ccv ON article;

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

本内容试读结束，登录后可阅读更多

下载后可阅读完整内容，剩余1页未读，立即下载

内容反馈

weixin_38678550

粉丝: 3
资源: 955

最新资源

资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！点击此处反馈

feedback-tip