开发者学堂课程【MySQL 高级应用 - 索引和锁：索引单表优化案例】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/598/detail/8613
索引单表优化案例
目录：
三.一般性建议
一.释义
①索引分析
②索引失效【应该避免】
③一般性建议
索引分析分为
1.单表
2.两表
3.三表
——建表sql/案例
CREATE TABLE IF NOT EXISTs article
`id INT (10) UNSIGNED NO Т NULL PRIMARY KEY AU Т o _ INCREMENT ,` author _ idINT (10) UNSIGNED NO Т NULL ,
' category _ id INT (10) UNSIGNED NO Т NULL ,` views ` INT (10) UNSIGNED NOT NULL ,
'comments ` INT (10) UNSIGNED NOT NULL ,` title ` VARBINARY (255) NOT NULL ,
' content ` TEXT NO Т NULLH
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;
>` comments ` INT (10) UNSIGNED No Т NULL ,>` title ` VARBINARY (255)хот NULL ,
->` content `ТЕХТот NULL >);
uery ok ,0 rows affected ( o .05 sec )
mysq1>
mysq1> INSER Т IN Т o ` article `(` author _ id `,` category _ id `,` views `,` comments `,` title `,` content `) VALUES
->(1,1,1,1,'1','1'),→>(2,2,2,2,2,12),一>(,1,3,3,‘3,3);
uery OK ,3 rows affected ( o .00 sec ) ecords :3 Duplicates :0 Warnings :0l
mysq1>
mysq1> SELEC Т“ FRO artic1e;
id I author _ id I category _ id I views I comments I title content
Query OK ,0rows affected (0.07 sec ) Records :0 Duplicates :0 Narni ngs :
mysql > show index from articlet
Table INon _ unf que Key _ na В e I Seq _ in _ index I Col unn _ name I Collation I Cardinality I Sub _ part I Packed INul1 I Index _ type I Com В ent I Index comment I
aricle PRIMARY d NULLI NUL BTREE
article idx _ article _ ccv category _ id NtLL NULL BTREE
article idx _ article _ ccv coents NLLL NUL BTREE
article idx _ article _ ccv vies NtLL NULL BTREE
4 ros In set sCc
mysq1> EXPLAIx SELECT id . author _ id FRO article WHERE category _ id =1AND co ВВ ents >1 ORDER BY viess DESC LINIT 1:
I id I select _ type I table Iposstblekeys key key _ len lref Гo0s Extra
1ISIMPLE I article I range I idx _ article _ ccv I iLarticle _ ccv ｜8 NULLI 1I Using where : tsing filesort I
I ro In set ( O .00 sec )
mysql > EXPLAIx SELEC Т d , author _ Id FRO article WHERE category _ d =1AND co В ments = IoRDER BY viess DESC LINI Т1;
id Iselect type ItableItype Ipossible keyskey keylen L ref Г oW
1 ISIMPLE I article I ref Iidx _ article _ ccv I idx _ article _ ccv I 8 Iconst , const
#1.2第2次 EXPLAIN
EXPLAIN SELECT id , author _ id FROM ` article ` WHERE category _ id =1AND comments >1ORDER BY views DESC LIMIT 1; EXPLAIN SELECT id , author _ id FROM ` article ` WHERE category _ id =1AND comments =3 ORDER BY views DESc LIMIT 1
#结论:
# type 变成了 range ,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
#但是我们已经建立了索引,为啥没用呢?
#这是因为按照 BTree 素引的工作原理,#先排序 category _ id ,
#如果遇到相同的 category id 则再排序 comments ,如果遇到相同的 comments 则再排序 views 。
#当 comments 字段在联合素引里处于中间位置时,
#因 comments >1条件是一个范围值(所谓 range ),
# MySQL 无法利用索引再对后面的 views 部分进行检素,即 range 类型查询字段后面的索引无效。
#当 comments 字段在联合索引里处于中间位置时,#因 comments >1条件是一个范围值(所谓 range ),
HMySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
#1.3删除第一次建立的索引
DROP INDEX idx _ article _ ccv ON article ;
并1.4第2次新建索引
# ALTER TABLE ` article ` ADD INDEX idx _ article _ cv (` category _ id , views `); create index idx _ article _ cv on article ( category _ id , views );
#1.5第3次 EXPLAIN
EXPLAIN SELEC Т id , author _ id FROM article WHERE category _ id =1AND comments >1ORDER BY views DESc LIMIT 1;
#结论:
可以看到, ype 变为了 ref , Extra 中的 Using filesort 也消失了,结果非常理想。
DROP INDEX idx _ article _ cv ON article ;
