3MySQL数据库----------SQL语句优化 - 索引、join、order by、limit -全栈式开发36

一、index索引优化

（一）知识补充

索引的分类

• 只需要记住逻辑层面

分类的角度	索引名称
数据结构	B+树、Hash索引、B-tree等
存储层面	聚簇索引、非聚簇索引
逻辑索引	主键索引、普通索引、复合索引（mysql组合索引和联合索引都是一个东西）唯一索引、空间索引等

复合索引

• create [unique] index 索引名字 on 表名字(字段1,字段2…);
• 将字段1,字段2…的依赖关系想象成楼层，字段1就是楼层1，后面累加

  create index idx_  on 表(字段)
  

回表

• 从普通索引查出主键索引，然后查询出数据的过程就叫做回表，由于回表需要多执行一次查询，这也就是为什么主键索引要比普通索引要快的原因，所以，我们要尽量使用主键查询

  -- 数据库表students在两个字段 id(主键)，name，age，addres，deptno 在 name建立一个普通索引，然后
  select * from test where name =‘aaa’：
  -- name先走索引查询出name对应的id，然后拿id在回到表中查出所有的值。
  -- 这个过程就是回表，数据量大了才会看到效率会增加，但是小的数据量反而会降低查询速度。
  

索引覆盖

• 如果一个索引包含了需要查询的字段，那么我们就叫做“覆盖索引”，不用触发回表。

  select * from test where name =‘aaa’：
  select id from test where name =‘aaa’： 
  

（二）优化方法

口诀

• 全值匹配我最爱,最左前缀要遵守
  带头大哥不能挂,中间兄弟不能断
  索引列上少计算，范围之后全失效
  like百分写最右,覆盖索引不写星*
  不等空值还有or,索引失效要少用
  varchar引号不可丢,SQL高级也不难

总结

• 第一：条件尽量使用主键索引，全值索引，遵循最左前缀，设置合理的索引、不要在字段上做计算
• 第二：取什么要什么，最好带索引的字段
• 第三：避免使用范围查询、模糊查询、!=、null、or
• 第四：对于字符串字段，查询条件一定要带引号（否则会内部隐式转换，失去索引效果）

（三）口诀解释

create table article(
    id int unsigned not null primary key auto_increment,
    author_id int unsigned not null,
    category_id int unsigned not null,
    views int unsigned not null,
    comments int unsigned not null,
    title varchar(255) not null,
    content text not null
);
insert into article(`author_id`,`category_id`,`views`,`comments`,`title`,`content`) values 
(1,1,1,1,'1','funny'),
(2,2,2,2,'2','action'),
(1,1,3,3,'3','love');
-- 建立复合索引
create index index_idx_article_ccv on article(category_id,comments,views);
-- 查看索引
show index from article;

• 全值匹配：就是针对复合索引，创建的时候有几个索引查询的时候就用到几个索引
  • explain select * from article where category_id=1 and comments=2 and views=3 \G; – 三个索引查询的放置顺序和结果无关
    
• 最左前缀要遵守：就是复合索引字段顺序要遵守，字段1,字段2…的依赖关系想象成楼层，所以如果没有第一层，索引全部失效，所以要遵守最左边的
  • explain select * from article where comments=2 and views=3 \G;
  • 没有第一层，索引全部失效
    
• 中间兄弟不能断：同时如果楼层断了的话，后面的索引就失效了
  • explain select * from article where category_id=1 and views=3 \G;
    
• 索引列上少计算：对索引字段做计算会失效索引查找的功能，变成全表扫描
  • explain select * from article where category_id-1=1\G;