MySQL高级---B站学习总结---索引优化单表案例

MySQL高级—B站学习总结—索引优化单表案例

索引分析

1. 单表：
   1. 建表sql：
      
   2. 案例：

案例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,也是最坏的情况。优化是必须的。

开始优化：优化方式是新建索引+删除索引

1. 新建索引：有两种方式，一般使用CREATE方式，通过CREATE创建一个复合索引

   1. ALTER TABLE article ADD INDEX idx_article_ccv ( category_id , comments, views );
   2. CREATE INDEX idx_article_ccv on article(category_id,comments,views);
      1. 通过SHOW INDEX from article查看索引，没有创建复合索引之前
         
      2. 创建完复合索引后查看复合索引，会发现将复合索引的内容全部排好序，按照Seq_in_index顺序由小向大检索
         
      3. 插入复合索引之后在执行一遍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 类型查询字段后面的索引无效。
         

2. 删除索引（删除第一次创建的索引）

   1. 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;查看:(又变成原来的样子了)

3. 第2次新建索引（还是使用CREATE方式）

   1. #ALTER TABLE article ADD INDEX idx_article_cv ( category_id , views ) ;
   2. 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(常量),这时候才是我们的检索和排序同时用到索引了，结果非常理想