MySQL高级—B站学习总结—索引优化单表案例
索引分析
- 单表:
- 建表sql:
- 案例:
- 建表sql:
案例1: #查询 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;
结论: 很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
开始优化:优化方式是新建索引+删除索引
-
新建索引:有两种方式,一般使用
CREATE
方式,通过CREATE
创建一个复合索引- ALTER TABLE
article
ADD INDEX idx_article_ccv (category_id
,comments
,views
); - CREATE INDEX idx_article_ccv on article(category_id,comments,views);
- 通过
SHOW INDEX from article
查看索引,没有创建复合索引之前
- 创建完复合索引后查看复合索引,会发现将复合索引的内容全部排好序,按照Seq_in_index顺序由小向大检索
- 插入复合索引之后在执行一遍
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
发现模式没什么变化,type从ALL变成了range,但是 extra 里使用 Using filesort 仍是无法接受的。但是我们已经建立了索引,为啥没什么效果呢?原因是:
1. 这是因为按照 BTree 索引的工作原理,
2. 先排序 category_id,
3. 如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。
4. 当 comments 字段在联合索引里处于中间位置时,
5. 因comments > 1 条件是一个范围值(所谓 range),
6. MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
- 通过
- ALTER TABLE
-
删除索引(删除第一次创建的索引)
- DROP INDEX idx_article_ccv ON article;
由于创建了复合索引idx_article_ccv
解决了全表扫描问题(type=ALL)
,但是我们还要解决Extra中含有Using filesort
的问题,所以说idx_article_ccv
这个索引创建的还不是很完美,所以只能把它删掉重新来过,删掉之后通过EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
查看:(又变成原来的样子了)
- DROP INDEX idx_article_ccv ON article;
-
第2次新建索引(还是使用
CREATE
方式)- #ALTER TABLE
article
ADD INDEX idx_article_cv (category_id
,views
) ; - CREATE INDEX idx_article_cv on article(category_id,views);
查看索引SHOW INDEX from article
EXPLAIN SELECT id,author_id from article where category_id = '1' and comments > 1 ORDER BY views Desc limit 1
发现type变成了ref
,Extra中不包含Using filesort
,而且ref中还包含const(常量)
,这时候才是我们的检索和排序同时用到索引了,结果非常理想
- #ALTER TABLE