Mysql索引优化单表、两表、三表实践

hanshangzhi 2020-01-12

单表

新建表

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 INT(10) UNSIGNED 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‘);

查询要求 

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

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

使用EXPLAIN查看执行计划

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

Mysql索引优化单表、两表、三表实践

结论:type为ALL,显示为全部查询,即最坏的情况,Extra信息中出现Using filesort也是最坏的情况,需要优化。

开始优化

第一次优化
查看索引:

show index from article;

此时我们通过建立索引优化,但是我们不清楚索引建立到哪些列上是最合适的,所以需要尝试。

我们第一次建立联合索引,在三个字段上:category_id,comments,views。

新建与删除索引:

create index ide_article_ccv on article(category_id,comments,views);
DROP INDEX ide_article_ccv ON article;

Mysql索引优化单表、两表、三表实践

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

第二次优化:
create index ide_article_cv on article(category_id,views);

结论:type变成ref,Extra中的Using filesort也消失了,结果很理想。

Mysql索引优化单表、两表、三表实践

保持更新中。。。

相关推荐