• explain 的作用

    • 表的读取顺序
    • 数据读取操作的操作类型
    • 哪些索引可以使用
    • 哪些索引被实际使用
    • 表之间的引用
    • 每张表有多少行被优化器查询
  • explain 使用

    • explain + SQL语句
    • explain 包含信
  • type 访问类型排列

    • 从最好到最差依次是

      • system --> const --> eq_ref --> ref --> range --> index --> all
      • 一般来说,保证查询至少达到range级别,最好能达到ref
  • 索引单表优化

    create table if not exists article (
    id int(10) unsigned not null primary key auto_increment,
    author_id int(10) unsigned not null,
    category_id int(10) unsigned not null,
    views int(10) unsigned not null,
    comments int(10) unsigned not null,
    title varbinary(255) not null,
    content text not null
    );
    
    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,4,'3','3');
    
    select * from article;
    
    # 查询 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 index idx_article_categoryid_comments_views 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;
    # 结论: 还是出现Using filesort(文件排序)
    # 删除索引,重新创建索引
    drop index idx_article_categoryid_comments_views on article;
    create index idx_article_categoryid_views 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;
    
    # 结论: 可以看到,type 变为了 ref, extra 中的 using filesort 也消失了,结果非常理想
    
  • 索引两表优化

    create table if not exists class (
    id int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key(id)
    );
    
    create table if not exists book (
    bookid int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key(bookid)
    );
    
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    insert into class(card) values(floor(1 + (rand() * 20)));
    
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    insert into book(card) values(floor(1 + (rand() * 20)));
    
    select * from book inner join class on book.card=class.card;
    select * from book left join class on book.card=class.card;
    
    # explain 分析 left join
    explain select * from book left join class on book.card=class.card;
    # 结论: type 有ALL
    
    # 添加索引优化
    alter table book add index idx_y_card (card);
    explain select * from book left join class on book.card=class.card;
    # 结论: type 变为了ref,rows 行数 没有变化
    # 删除索引,在右表添加索引
    alter table class add index idx_y_card (card);
    explain select * from book left join class on book.card=class.card;
    # 结论: type 变为了ref,rows 行数变化比较明显
    # 这是有左连接特性决定的。left join 条件用于确定如何从右表搜索行,左表一定都有
    # 所以右边表是关键,一定需要建立索引
    # 反之右连接 rigth join 条件用于确定如何从左表搜索行,右表一定都有,索引就要建立在左表上
    
  • 索引三表优化

    # 在两表上在添加一个表
    create table if not exists phone (
    phoneid int(10) unsigned not null auto_increment,
    card int(10) unsigned not null,
    primary key(phoneid)
    ) engine=InnoDB;
    
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    insert into phone(card) values(floor(1 + (rand() * 20)));
    
    # 删除 class 表的索引,恢复环境
    drop index idx_y_card on class;
    
    # 未索引
    explain select * from book left join class on book.card=class.card left join phone on book.card=phone.card;
    # 结论: type 全部为ALL,extra 为using join buffer 使用了连接缓存,需要优化
    
    # 添架索引
    # 因是left join 需要添加右表建立索引,那么需要建立2个右表索引
    alter table class add index idx_y_card(card);
    alter table phone add index idx_p_phone(card);
    
    explain select * from book left join class on book.card=class.card left join phone on book.card=phone.card;
    # 结论: type 变为ref extra 已经没有 using join buffer 了
    
  • join 语句优化

    • 尽可能减少join语句中的NestedLoop的循环总次数: "永远用小结果集驱动大的结果集"
    • 优先优化NestedLoop的内层循环
    • 保证join语句中被驱动表上join条件字段已经被索引
    • 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝啬joinbuffer的设置
  • 索引失效优化(应该避免)

    # 创建表测试
    create table staffs (
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年龄',
    pos varchar(20) not null default '' comment '职位',
    add_time timestamp not null default current_timestamp comment '入职时间'
    ) charset utf8 comment '员工记录表';
    
    insert into staffs(name,age,pos,add_time) values('张三',22,'manager',now());
    insert into staffs(name,age,pos,add_time) values('July',23,'dev',now());
    insert into staffs(name,age,pos,add_time) values('2000',23,'dev',now());
    select * from staffs;
    
    alter table staffs add index idx_staffs_name_age_pos(name,age,pos);
    show index from staffs;
    
    ## 测试1
    explain select * from staffs where name='July';
    explain select * from staffs where name='July' and age=23;
    explain select * from staffs where name='July' and age=23 and pos='dev';
    # type 为ref  ref 中的常量为 const,有几个索引就有几个 const
    
    # 下面两条语句 索引失效
    explain select * from staffs where age=23 and pos='dev';
    explain select * from staffs where pos='dev';
    # type 为ALL key 为NULL 全表扫描并且未使用到索引
    # 结论: 索引了多列,要从索引的最左前列开始并且不跳过索引中的列
    
    ## 测试2 计算
    explain select * from staffs where name='July';
    ## 索引失效
    explain select * from staffs where left(name, 4)='July';
    # type 为ALL key 为NULL 全表扫描并且未使用到索引
    # 结论: 索引列上少使用计算
    
    ## 测试3 范围条件右边的列索引失效
    explain select * from staffs where name='July' and age>23 and pos='dev';
    # type 为 range  ref 中的常量为 null
    
    ## 测试3 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
    explain select name,age,pos from staffs where name='July' and age=23 and pos='dev';
    # Extra 为 Using index 表示从索引树种读取索引列信息
    explain select name,age,pos from staffs where name='July' and age>23 and pos='dev';
    explain select name,age,pos from staffs where name='July' and age>23;
    # type 为 ref Extra 为 Using index 表示从索引树种读取索引列信息
    
    ## 测试4 在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描
    explain select * from staffs where name !='July';
    explain select * from staffs where name <> 'July';
    # type 为 ALL  key 实际使用索引为null
    
    ## 测试5 is null, is not null 也无法使用索引会导致全表扫描
    explain select * from staffs where name is null;
    # type 为 null 
    explain select * from staffs where name is not null;
    # type 为 ALL  key 实际使用索引为null
    
    ## 测试6 like以通配符开头('%abc...') mysql 索引失效会变成全表扫描
    explain select * from staffs where name like '%July%';
    explain select * from staffs where name like '%July';
    # type 为 ALL  key 实际使用索引为null
    explain select * from staffs where name like 'July%';
    # type 为 range key 使用上了索引
    # 结论: like  % 写在右边
    
    ## 解决like '%字符串%' 时索引不被使用的方法
    create table tbl_user (
    id int(11) not null auto_increment,
    name varchar(20) default null,
    age int(11) default null,
    email varchar(20) default null,
    primary key(id)
    ) engine=InnoDB auto_increment=1 default charset=utf8;
    
    insert into tbl_user(name,age,email) values('1aa1',21,'b@163.com');
    insert into tbl_user(name,age,email) values('2aa2',2222,'a@163.com');
    insert into tbl_user(name,age,email) values('3aa3',265,'c@163.com');
    insert into tbl_user(name,age,email) values('4aa4',21,'d@163.com');
    
    # 解决办法
    create index idx_user_name_age on tbl_user(name,age);
    show index from tbl_user;
    
    # 使用覆盖索引
    explain select name,age from tbl_user where name like '%aa%';
    # type 为 index 
    
    ## 测试7 字符串不加单引号索引失效
    explain select * from staffs where name='2000';
    # type 为 ref  key 有使用上索引
    explain select * from staffs where name=2000;
    # type 为 ALL  key 为 null
    
    ## 测试8 少用or, 用它来连接时会索引失效
    explain select * from staffs where name='July' or name='张三';
    # type 为 ALL  key 为 null
  • in 和 exists

    select * from tbl_emp e where e.deptid in (select id from tbl_dept d);
    select * from tbl_emp e where exists (select 1 from tbl_dept d where d.id=e.deptid);
    
    # 结论: 实现结果一样
最后修改:2022 年 03 月 25 日
如果觉得我的文章对您有用,请随意赞赏!