Mysql04-MySQL索引优化

Mysql04-MySQL索引优化

   索引单表优化案例
   索引两表优化案例
   索引三表优化案例
   索引优化
   索引优化总结口诀与其他

一、索引单表优化案例
   BTree，多字段复合索引工作原理，首先排序第一个字段，遇到相同的第一个字段再排序第二个字段；遇到相同的第二个字段再排序第三个字段...
   复合索引中，尽量避免选用where条件中，用于范围查找的字段比如评论数量comment_cnt>100，创建时间create_time>20201016 ，这些字段不要再复合索引中出现
   查询1:
   CREATE INDEX idx_article_ccv ON article(category_id,comments,views);
   EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
   Mysql04-MySQL索引优化
   查询2：
   DROP INDEX idx_article_ccv ON article;
   新建一个索引，不带comments
   CREATE INDEX idx_article_cv ON article(category_id,views);
   EXPLAIN SELECT id,author_id FROM article WHERE category_id=1 AND comments>1 ORDER BY views DESC LIMIT 1;
   Mysql04-MySQL索引优化
   此时，检索+排序都用到了索引

二、索引两表优化案例
   结论：左连接，右表对on key建索引；右连接，左表对on key建索引
   解释：左连接，左表的数一定全有，此时，left join条件用于确定如何从右表进行搜索，所以右表是关键，要建立索引！
   另外，如果在写左连接语句的时候，发现左表在on key上已经建了索引，可以改为使用右连接查询，灵活使用，避免了新建索引

三、索引三表优化案例
   永远用小结果集驱动大结果集，减少IO访问次数（=驱动表的记录数）
   保证Join语句中被驱动表上Join条件字段已经被索引，即左连接，除了第一张左表，其他表尽量在on key上建立索引
   当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，调大my.cnf中 JoinBuffer 的设置

四、索引优化（如何避免索引失效）
   1.最好全值匹配
   2.最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指查询从索引的最左列开始，并且不跳过索引中的列
   3.不要在索引列上做任何操作（计算、函数、自动或手动的类型转换），会导致索引失效而转向全表扫描
   4.存储引擎不能使用索引中范围条件右边的列
       场景：EXPLAIN SELECT * FROM staffs WHERE NAME='July' AND age >25 AND pos='manager';
       其中，索引列：NAME,AGE,POS，三个索引，第二个是范围条件，第二个索引只用到了排序，没用到检索。且第二个之后的索引都失效了
   5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*
   Mysql04-MySQL索引优化
   6.mysql在使用不等于(!=或<>)时无法使用索引会导致全表扫描
       EXPLAIN SELECT * FROM staffs WHERE NAME!='July'; 无法使用索引
   7.is null，is not null也无法使用索引
   8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描
       %写在左侧或两侧都写会使索引失效，写在右边不会失效，但可能不满足业务要求，查不到数据
       如果必须在两边都写%，那么可以使用覆盖索引来解决索引失效的问题。
       我们创建的索引是name,age,pos，select 别写 *，最好是查询的字段和建立的索引在个数顺序上一致，如果不一致，索引和主键也需要能覆盖到查询的列
       EXPLAIN SELECT id FROM staffs WHERE NAME LIKE '%July%'; -- 索引没失效
       EXPLAIN SELECT age FROM staffs WHERE NAME LIKE '%July%'; -- 索引没失效
       EXPLAIN SELECT id,age FROM staffs WHERE NAME LIKE '%July%'; -- 索引没失效
   Mysql04-MySQL索引优化


       但如果，加上没建立索引的字段，索引就会失效！

   9.字符串不加单引号索引失效

   10.少用or，用它连接索引失效
   EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' or NAME = 'Que'; -- 全表扫描，索引失效！
五、索引优化总结口诀与其他
   Mysql04-MySQL索引优化
   1.索引inx(a,b,c) where a=3 and b like 'kk%' and c=4; => 会用到 a,b,c三个索引！
   索引inx(a,b,c) where a=3 and b like 'k%kk%' and c=4; => 会用到 a,b,c三个索引！
   2.等值where条件mysql自身查询优化，但尽量和建索引一个顺序使用
   Mysql04-MySQL索引优化
   3.其他案例（基于建立c1,c2,c3,c4复合索引）


   group by表面上是分组，但分组之前必排序，其排序法则与order by几乎是一致的。group by的顺序与索引不一致的话，会有临时表产生

   4.一般优化建议
       1)对于单键索引，尽量选择针对当前查询过滤性好的索引
       2)在建组合索引时，当前查询过滤性能最好的字段在索引字段顺序中的最前面
       3)在使用组合索引的时候，尽量选择可以包含当前查询中where条件中更多字段的索引
       4)尽可能通过分析统计信息和调整查询写法来达到选择合适索引的目的

Mysql04-MySQL索引优化

相关推荐