数据库学习（二）之SQL优化

发表于 | 阅读次数:
字数统计: 6.7k | 阅读时长 ≈ 30

一、SQL优化

原因：性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）

1、SQL编写过程与解析过程

编写过程：

1
select distinct ... from ... join .. on .. where .. group by ... having ...order by ... limit ...

解析过程：

1
from ... on .... join ... where ... group by ... having ... select distinct ... order by ... limit ...

2、SQL优化

主要是在 优化索引

索引：相当于书的目录

索引：index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B树（MYSQL默认）、Hash树）

3、索引的优劣势

3.1 优势

  • 提高查询效率（降低IO使用率）
  • 降低CPU使用率（….order by age desc，因为B树索引 本身是一个排好序的结构，因此在排序时，可以直接使用）

3.2 劣势

  • 索引本身很大，可以存放在内存/硬盘（通常为硬盘）
  • 索引不是所有情况均适用：（1）少量数据（2）频繁更新的字段（3）很少使用的字段
  • 索引会降低增删改的效率（需要维护索引）

4、索引

4.1 索引分类

（1）单值索引：单列，age；一个表可以有多个单值索引，name

（2）唯一索引：不能重复。id，可以是null

​ 主键索引：不能重复。id，不能是null

（3）复合索引：多个列构成的索引（相当于二级目录：z:zhao）(name,age)

4.2 创建索引

4.2.1 方式一

1
2
3
4
5
6
7
8
9
10
-- 创建表
create table tb(id int,name varchar(5),dept varchar(5),primary key(id));

-- create 索引类型 索引名 on 表（字段）
-- 单值索引：
create index dept_index on tb(dept);
-- 唯一索引：
create unique index name_index on tb(name);
-- 复合索引：
create index dept_name_index on tb(dept,name);

4.2.2 方式二

1
2
3
4
5
6
7
-- alter table  表名 add  索引类型 索引名（字段）
-- 单值索引：
alter table tb add index dept_index(dept);
-- 唯一索引：
alter table tb add unique index name_index(name);
-- 复合索引：
alter table tb add  index dept_name_index(dept,name);

注意：如果一个字段是 primary key，则该字段默认就是 主键索引

4.3 删除索引

1
2
3
-- 删除索引：
-- drop index 索引名 on 表名
drop index name_index on tb;

4.4 查询索引

1
2
--查询索引：
show index from tb;

img

5、SQL性能问题—explain详解（Mysql 8.0.26）

（1）分析SQL的执行计划：explain，可以模拟SQL优化器执行SQL语句，从而让开发人员 知道自己编写的SQL状况

（2）MYSQL查询优化器会干扰我们的优化

优化方法，官网

查询执行计划：explain + SQL语句

explain select * from tb;

img

5.1 准备数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
create table course(
cid int,
cname varchar(20),
tid int
);

create table teacher(
tid int,
tname varchar(20),
tcid int
);

create table teacherCard(
tcid int,
tcdesc varchar(200)
);

insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc');
insert into teacherCard values(2,'twdesc');
insert into teacherCard values(3,'tldesc');

img

5.2 id 编号

查询课程编号为2 或教师证编号为3的老师信息

（1）id值相同，从上往下，顺序执行

​ 先找t表，然后找tc表，最后找c表（从表结构图中可知:t3-tc3-c4,t表和tc表都是3条数据，course表是4条）

1
explain select t.* from teacher t,course c,teachercard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);

img

若给teacher表增加一点数据

1
2
3
insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',5);
1
explain select t.* from teacher t,course c,teachercard tc where t.tid = c.tid and t.tcid = tc.tcid and (c.cid = 2 or tc.tcid = 3);

mysql 5.7.28 执行结果如下

本应该是小表驱动大表原则。但我这未按照t3-c4-tc6来输出，可能MySQL5.6是这个规则

数据小的表，优先查询

img

（2）id值不同，id值越大越优先查询（本质：在嵌套子查询时，先查内层，再查完成）

​ 查询教SQL课程的老师的描述

1
2
3
explain select tc.tcdesc from teachercard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql';
-- 多表查询转换为子查询
explain select tc.tcdesc from teachercard tc where tc.tcid = (select t.tcid from teacher t where t.tid = (select c.tid from course c where c.cname = 'sql'));

img

（3）id值部分相同，部分不同：（id值越大越优先，id值相同，从上往下，顺序执行）

1
explain select t.tname ,tc.tcdesc from teacher t,teachercard tc where t.tcid = tc.tcid and t.tid = (select c.tid from course c where cname = 'sql');

img

5.3 select_type 查询类型

5.3.1 PRIMARY

包含子查询SQL中的 主查询（最外层）

5.3.2 SUBQUERY

包含子查询SQL中的 子查询（非最外层）

5.3.3 SIMPLE

简单查询（不包含子查询、UNION）

1
explain select * from course;

img

5.3.4 DERIVED

衍生查询（使用到了临时表）

在from子查询中，如果有table1 union table2，则table1 就是DERIVED，table2就是UNION

1
explain select cr.cname from (select * from course where tid = 1 union select * from course where tid = 2) cr;

img

5.3.5 UNION

见5.3.4

5.3.6 UNION RESULT

就是告知开发人员，哪些表之间存在union查询

5.4 table 表

5.5 type 类型（索引类型）

system > const > eq_ref > ref > range > index > all；

要对type进行优化的前提：有索引

其中：system,const只是理想情况；实际能达到 ref > range;

  • （1）system（忽略）：只有一条数据的系统表；或衍生表只有一条数据的主查询

  • （2）const：仅仅能查到一条数据的SQL，用于Primary key 或unique索引（类型 与索引类型有关）

1
2
3
4
5
6
7
create table test01(
	tid int(3),
	tname varchar(20)
);
insert into test01 values(1,'a');
-- 增加索引
alter table test01 add constraint tid_pk primary key(tid);
1
explain select * from test01 where tid = 1;

image-20210910223125561

若将主键索引修改，则type类型就不会是const

1
2
3
-- 修改索引
alter table test01 drop primary key;
create index test01_index on test01(tid);
1
explain select * from test01 where tid = 1;

image-20210910223557044

  • （3）eq_ref：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多，不能0）

    常见于唯一索引和主键索引

1
2
3
-- 给teachercard表添加主键索引
alter table teachercard add constraint pk_tcid primary key(tcid);
alter table teacher add constraint uk_tcid unique index(tcid);
1
explain select t.tcid from teacher t,teachercard tc where t.tcid = tc.tcid;

下图中，tc表的type类型不是eq_ref

因为tc表中有6条数据，t表中有3条数据，用tc表查询时，有3条数据的返回值是0，导致不满足条件

image-20210910230443162

1
2
3
4
5
-- 删除teacher表中的后3条语句
delete from teacher where tcid > 3;

-- 继续执行explain + SQL
explain select t.tcid from teacher t,teachercard tc where t.tcid = tc.tcid;

image-20210910230905284

说明：以上SQL，用到的索引是t.tcid，即teacher表中的tcid字段；如果teacher表中的数据个数和连接查询的数据个数一致（都是3条数据），则有可能满足eq_ref级别（数据要保持完全一致），否则无法满足。

  • （4）ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
1
2
3
4
5
6
7
8
9
-- 增加数据
insert into teacher values(4,'tz',4);
insert into teachercard values(4,'tzdesc');

-- 添加索引
alter table teacher add index index_name(tname);

-- explain + SQL
explain select * from teacher where tname = 'tz';

image-20210910231739292

  • （5）range：检索指定范围的行，where后面是一个范围查询（between，in，>，<，>=）

    特殊：in有时候会失效，从而转为 无索引all

1
2
3
4
-- 创建索引
alter table teacher add index tid_index(tid);
-- explain + SQL
explain select t.* from teacher t where t.tid < 3;

image-20210911201255772

  • （6）index：查询全部索引中的数据
1
2
-- tid是索引，只需要扫描索引表，不需要所有表中的所有数据
explain select tid from teacher;

image-20210911201519588

  • （7）all：查询全部表中的数据
1
2
-- cid不是索引，需要全表扫描，即需要所有表中的所有数据
explain select cid from course;

image-20210911201710375

总结：

  • system/const：结果只有一条数据
  • eq_ref：结果多条，但是每条数据是唯一的
  • ref：结果多条，电脑上每条数据是0或多条

5.6 possible_keys 可能用到的索引

是一种预测，不准

1
2
3
4
-- 给course表添加一个索引
alter table course add index cname_index(cname);
-- explain +SQL，实际和预测的保持一致
explain select tc.tcdesc from teachercard tc where tc.tcid = (select t.tcid from teacher t where t.tid = (select c.tid from course c where c.cname = 'sql'));

image-20210911202716144

1
2
-- 预测使用两个索引，但实际只用到一个
explain select tc.tcdesc from teachercard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql';

image-20210911202858525

5.7 key 实际使用到的索引

5.8 key_len 索引的长度

作用：用于判断复合索引是否被完全使用

1
2
3
4
5
6
7
8
create table test_kl(
	name char(20) not null default ''
);
alter table test_kl add index index_name(name);

-- key_len是60
-- utf8：一个字符占3个字节 （20 * 3）
explain select * from test_kl where name = '';

image-20210911203203152

1
2
3
4
alter table test_kl add column name1 char(20);
alter table test_kl add index index_name1(name1);
-- 如果索引字段可以为Null，则会使用1个字节用于标识，因此下列key_len为61
explain select * from test_kl where name1 = '';

image-20210911203421345

1
2
3
4
5
-- 删除索引
drop index index_name on test_kl;
drop index index_name1 on test_kl;
-- 增加复合索引
alter table test_kl add index name_name1_index(name,name1);
1
2
3
-- key_len = 121，使用的是name1
-- 复合索引，要用name1，必须先用name，name和name1各自都是60个字节，因为name1可以为空（1个字节标识），索引长度是121
explain select * from test_kl where name1 = '';

image-20210911203732218

1
2
3
-- key_len = 60，使用的是name
-- 查询的是name，20个字符，1个字符占3个字节，且不能为空，所以是60
explain select * from test_kl where name = '';

image-20210911203806591

1
2
3
4
5
alter table test_kl add column name2 varchar(20);
alter table test_kl add index name2_index(name2);

-- key_len为63； 20 * 3 + 1（可以为空） + 2（用2个字节标识可变长度） = 63
explain select * from test_kl where name2 = '';

image-20210911204155821

总结：

  • utf8：1个字符3个字节
  • gbk：1个字符2个字节
  • latin：1个字符1个字节

5.9 ref 表之间的引用

注意与type中的ref值进行区分

作用：指明当前表 所参照的字段

1
select ... where a.c = b.x;(其中b.x可以是常量，const)
1
2
3
4
-- t表 是用的'tw'进行比较，是常量，因此是const
-- c表 是用t.tid进行比较，这里是null
-- 分析排查可知，t表中的tid是索引，但是c表中的tid不是索引，因此这里为null（必须要有索引）
explain select * from course c,teacher t where c.tid = t.tid and t.tname = 'tw';

image-20210911204737647

1
2
3
-- 给course的tid添加索引
alter table course add index index_tid(tid);
explain select * from course c,teacher t where c.tid = t.tid and t.tname = 'tw';

image-20210911205104005

5.10 rows 行

被索引优化查询的数据个数（实际通过索引查询到的数据个数）

1
2
3
4
-- t表的个数为2 是因为通过判断条件 可以从t表中查询到2个
-- c表的个数为1 是因为通过判断条件 可以从c表中查询到1个
-- 可以简单的理解为1对多
explain select * from course c,teacher t where c.tid = t.tid and t.tname = 'tz';

image-20210911205348630

5.11 filtered 后续待学习

5.1.12 Extra 额外信息

  • （1）using filesort：性能消耗大；需要“额外”的一次排序（查询）。常见于order by语句
1
2
3
4
5
6
7
8
create table test02(
	a1 char(3),
	a2 char(3),
	a3 char(3),
	index idx_a1(a1),
	index idx_a2(a2),
	index idx_a3(a3)
);
1
2
-- Extra 为空 是因为先对a1 进行查询，然后对a1进行排序
explain select * from test02 where a1 = '' order by a1;

image-20210911205929408

1
2
-- Extra需要一次额外排序，是因为先对a1进行查询，然后对a2进行排序，所以需要一次额外的排序
explain select * from test02 where a1 = '' order by a2;

image-20210911205952225

1
2
3
4
5
6
-- 删除单索引
drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;
-- 复合索引，不能跨列（最佳左排序）
alter table test02 add index idx_a1_a2_a3(a1,a2,a3);
1
2
-- 复合索引，列序为a1,a2,a3；但是查询的时候没有用到a2，直接用到a3，跨列使用，因此会出现Using filesort
explain select * from test02 where a2 = '' order by a3;

image-20210911210511515

1
explain select * from test02 where a2 = '' order by a3;

image-20210911210644783

1
explain select * from test02 where a1 = '' order by a2;

image-20210911210721678

总结：

  • 对于单索引，如果排序和查找是同一个字段，则不会出现 Using filesort；如果排序和查找不是同一个字段，则需要额外一次排序。避免 ：where 哪些字段，就order by哪些字段

  • 复合索引，要按照复合索引的顺序使用，不要跨列或无序使用

  • （2）using temporary：性能损耗大，用到了临时表。一般出现在group by 语句中；已经有表了，但不适用，必须再来一张表（需要额外使用一张表）。

1
2
3
-- 查a1,用a1，但根据a2进行分组，就会出现临时表
-- 避免：查询哪些列，就根据哪些列 group by
explain select a1 from test02 where a1 in ('1','2','3') group by a2;

image-20210912171932075

  • （3）using index：性能提升；是指索引覆盖（覆盖索引）。

    原因：不读取原文件，只从索引文件中获取数据（不需要回表查询）。只要使用到的列全部都在索引中，就是索引覆盖。

1
2
-- 例如，test02表中有一个复合索引（a1,a2,a3）
explain select a1,a2 from test02 where a1= '' or a2 = '';

image-20210912172936319

1
2
3
4
5
6
-- 删除复合索引
drop index idx_a1_a2_a3 on test02;
-- 增加索引
alter table test02 add index idx_a1_a2(a1,a2);
-- a1,a2无法覆盖a1和a3
explain select a1,a3 from test02 where a1= '' or a3 = '';

image-20210912173205652

using index时，会对possible_keys和key造成影响：

  • 如果没有where，则索引只出现在key中

    1
    explain select a1,a2 from test02 ;

    image-20210912173417760

  • 如果有where，则索引出现在key和possible_key中

    1
    explain select a1,a2 from test02 where a1= '' or a2 = '';

    image-20210912173450391

  • （4）using where：需要回表查询

    假设age是索引列，但查询语句 select age,name from … where age = ….;此语句中必须回原表查Name，因此会显示using where。

1
explain select a1,a3 from test02 where a3 = '';

image-20210912173752224

  • （5）impossible where：where子句永远为false
1
explain select * from test02 where a1= 'x' and a1= 'y';

image-20210912173926085

6、SQL优化举例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table test03(
	a1 int(4) not NULL,
	a2 int(4) not NULL,
	a3 int(4) not NULL,
	a4 int(4) not NULL
);
alter table test03 add index idx_a1_a2_a3_a3(a1,a2,a3,a4);

-- 推荐写法，索引的使用顺序（where后面的顺序）和复合索引的顺序一致
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4;
-- 如果where后面的进行倒序，其结果一致
-- 原因：虽然编写的顺序和索引的顺序不一致，但是sql在真正执行前，经过了SQL优化器的调整，也就与上面一致
explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4;

-- 以上两个sql使用了全部的复合索引，因此key_len为16

image-20210912174431453

1
2
3
4
5
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=3 order by a3;
-- 解释：
-- using index：不用回表查询，因为索引用到了a1和a2，因此不用回表，跨列导致a4索引失效，因此其key_len为8
-- using where：需要回表查询，查询a4的时候，其a4是无效索引，所以需要回表查询
-- 其索引使用情况可以通过key_len进行校验，因为长度为8

image-20210912174951462

1
2
3
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=3 order by a3;
-- 以上SQL出现了using filesort（文件内排序，多了一次额外的查找/排序）：不要跨列使用（where 和 order by 拼起来，不要跨列使用）
-- 以上a1用到索引，a4失效，因此 where 和order by 为a1和a3，a3也跨列了，所以出现using filesort

image-20210912175424004

1
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=3 order by a2,a3;

image-20210912175850207

总结：

  • 如果（a,b,c,d）复合索引 和 使用的顺序全部一致（且不跨列使用），则复合索引全部使用。如果部分一致，则使用部分索引
  • where 和 order by拼起来，不要跨列

6.1 单表优化

1
2
3
4
5
6
7
8
9
10
11
12
create table book(
	bid int(4) primary key,
	name varchar(20) not null,
	authorid int(4) not null,
	publicid int(4) not null,
	typeid int(4) not null
);

insert into book values(1,'tjava',1,1,2);
insert into book values(2,'tc',2,1,2);
insert into book values(3,'wx',3,2,1);
insert into book values(4,'math',4,2,3);
1
2
-- 查询authorid = 1 且typeid 为2 或者3的bid
explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;

image-20210912180736698

1
2
3
-- 优化1：加索引，优化后，type由all升级到index了
alter table book add index idx_bta(bid,typeid,authorid);
explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;

image-20210912214849891

1
2
3
4
5
6
7
8
9
-- 根据SQL实际解析的顺序，调整索引的顺序
-- from ... on .... join ... where ... group by ... having ... select distinct ... order by ... limit ...
-- 索引一旦进行升级优化，需要将之前废弃的索引删掉，防止干扰
-- 虽然可以回表查询bid，但是将bid放到索引中，可以提升使用using index
drop index idx_bta on book;
alter table book add index idx_bta(typeid,authorid,bid);
-- 没有using filesort，因为不需要再额外排序
-- 这里type为range，是因为复合索引全部都使用到，特殊时候，in可能会失效，从而导致后面的索引都失效
explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;

image-20210912220330220

1
2
3
4
5
-- 再次优化
-- 思路：因为范围查询in有时会失效，因此交换索引的顺序，将typeid in (2,3)放到最后
drop index idx_tab on book;
alter table book add index idx_atb(authorid,typeid,bid);
explain select bid from book where authorid = 1 and typeid in (2,3) order by typeid desc;

image-20210912221435659

小结：

  • 索引不能跨列使用（最佳左前缀），保持索引的定义和使用的顺序一致性
  • 索引需要逐步优化
  • 将含In的范围查询放到where条件的最后，防止失效

本例中同时出现了Using where（需要回原表）；Using index （不需要回原表）：原因，where authorid = 1 and typeid in (2,3)中authorid在索引（authorid,typeid,bid）中，因此不需要回原表（直接在索引表中查到），而typeid虽然也在索引（authorid,typeid,bid）中，但是含in的范围查询已经使typeid索引失效，因此相当于没有typeid这个索引，所以需要回原表

证明：

1
2
-- 将in修改为=；没有了in，则不会出现using where
explain select bid from book where authorid = 1 and typeid =3 order by typeid desc;

image-20210912222134900

6.2 双表优化

image-20210912222742412

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table teacher2(
	tid int(4) primary key,
	cid int(4) not null
);
insert into teacher2 values(1,2);
insert into teacher2 values(2,1);
insert into teacher2 values(3,3);

create table course2(
	cid int(4),
	cname varchar(20)
);
insert into course2 values(1,'java');
insert into course2 values(2,'python');
-- 左连接
select * from teacher2 t left outer join course2 c on t.cid = c.cid where c.cname = 'java';

image-20210912223110073

1
2
3
4
5
-- 索引该往那张表加？- 小表驱动大表 -索引建立在经常使用的字段上（本题t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段添加索引）【一般情况对于左外连接，给左表加索引；右外连接，给右表加索引】
-- 结论 where 小表.x = 大表.x;
-- 当编写.. on t.cid = c.cid 时，将数据量小的表 放左边（假设此时t表数据量小）
-- Using join buffer:Extra的一个选项，作用:MySQL引擎使用了链接缓存，底层优化了我们的SQL语句
explain select * from teacher2 t left outer join course2 c on t.cid = c.cid where c.cname = 'java';

image-20210912225656949

1
2
alter table teacher2 add index index_teacher2_cid(cid);
explain select * from teacher2 t left outer join course2 c on t.cid = c.cid where c.cname = 'java';

image-20210912225814596

1
2
alter table course2 add index index_course2_cname(cname);
explain select * from teacher2 t left outer join course2 c on t.cid = c.cid where c.cname = 'java';

image-20210913223141084

6.3 三表优化

  • 小表驱动大表
  • 索引建立在经常查询的字段上

7、避免索引失效的一些原则

  • 复合索引，不要跨列或无序使用（最佳左前缀）

  • 复合索引，尽量使用全索引匹配（a,b,c，尽量全部使用）

  • 不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

    1
    2
    3
    4
    -- 举例
    select ... where A.x = ... ; -- 假设A.x是索引
    -- 不要：select ... where A.x * 3 = ...;
    explain select * from book where authorid = 1 and typeid = 2;

    image-20210913223844468

    1
    2
    -- 上一个为8，这一个索引长度为4，因为后面进行了计算，导致typeid索引失效
    explain select * from book where authorid = 1 and typeid * 2 = 2;

    image-20210913223922050

    1
    2
    -- 索引都失效，长度为0
    explain select * from book where authorid * 2 = 1 and typeid * 2 = 2;

    image-20210913224027531

    1
    2
    3
    -- 用到了0个索引
    -- 原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果b失效，则b,c同时失效
    explain select * from book where authorid * 2 = 1 and typeid = 2;

    image-20210913224126165

  • 索引不能使用不等于（!= 、<>）或 is null (is not null)，否则自身会失效

    1
    2
    3
    4
    5
    6
    7
    8
    drop index idx_atb on book;
    alter table book add index idx_authorid(authorid);
    alter table book add index idx_typeid(typeid);

    -- SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行预测。
    -- 比如下列explain + SQL，和我们认为的一样，预测的索引是authorid和typeid，但实际使用的只是authorid
    -- 因此长度为4
    explain select * from book where authorid = 1 and typeid = 2;

    image-20210913224955417

    1
    2
    -- 使用 != ,authorid索引失效
    explain select * from book where authorid != 1 and typeid = 2;

    image-20210913225051078

  • 补救：尽量使用索引覆盖（usintg index）;

  • like尽量以“常量开头”，不要以’%’开头，否则索引失效

    1
    2
    select * from xx where name like '%x%'; --name索引失效
    explain select * from teacher where tname like '%x%';-- tname索引失效

    image-20210913230324063

    1
    explain select * from teacher where tname like 'x%';

    image-20210913230406454

    1
    2
    -- 如果必须使用like '%x%'进行模糊查询，可是使用索引覆盖挽救一部分
    explain select tname from teacher where tname like '%x%';

    image-20210913230445023

  • 尽量不要使用类型转换（显示、隐式），否则索引失效

    1
    2
    3
    -- tname是一个字符串，这里用数字进行条件判断，要进行转换，导致索引失效
    -- 程序底层将 123 ->'123'
    explain select * from teacher where tname = 123;

    image-20210913230658181

  • 尽量不要使用or，否则索引失效

    1
    2
    -- 将or左侧的tname失效
    explain select * from teacher where tname = '' or tcid > 1;

    image-20210913230851365

8、一些其他的优化方法

（1）exist 和in

  • exist语法：将主查询的结果，放到子查询中进行条件校验（看子查询是否有数据，如果有数据，则校验成功），如果符合校验，则保留数据

    1
    2
    3
    select tname from teacher where exists(select * from teacher);
    -- 如果select * from teacher有数据，等价于下面的
    select tname from teacher

  • in：

    1
    2
    select ... from table where tid in (1,3,5);
    select ... from A where id in (select id from B);
1
select ... from table where exist/in (子查询);

如果主查询的数据集大，则使用in，效率高

如果子查询的数据集大，则使用exist，效率高

（2）order by 优化

​ using filesort有两种算法：双路排序、单路排序（根据IO的次数）

​ MySQL4.1之前，默认使用双路排序；双路：扫描2次磁盘（第一次扫描排序字段，对排序字段进行排序（在buffer中进行的排序）。第二次扫描其他字段） — IO比较消耗性能，因此后续采用单路排序

​ MySQL4.1之后，默认使用单路排序：只读取一次（全部字段），在buffer中进行排序，但该单路排序，会有一定的隐患（不一定真的是“单路|一次IO”，有可能多次IO）— 若数据量过大，则无法将所有字段的数据一次性读取完毕，因此会进行“分片读取，多次读取”。

注意：单路排序比双路排序会占用更多的buffer。单路排序在使用时，如果数据大，可以考虑调大Buffer的容量大小：

1
set max_length_for_sort_data = 1024; -- 单位byte

如果max_length_for_sort_data 值太低（太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数），则MySQL会自动从 单路->双路。

提高order by查询的策略：

  • 选择使用单路、双路，调整buffer的容量大小
  • 避免使用select * ……
  • 复合索引，不要跨列使用，避免using filesort
  • 保证全部的排序字段，排序的一致性（都是升序或降序）

9、SQL排查—慢查询日志

MySQL提供的一种日志记录，用于记录MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阈值的SQL语句（long_query_time，默认10秒），慢查询日志默认是关闭的；建议：开发调优时打开，最终部署时关闭。

  • 检查是否开启了慢查询日志：

    1
    show variables like '%slow_query_log%';

    image-20210913233526784

  • 临时开启

    1
    2
    set global slow_query_log  = 1;--在内存中开启
    show variables like '%slow_query_log%';

    image-20210913233636910

  • 临时设置阈值

    1
    2
    show variables like '%long_query_time%';
    set global long_query_time = 5; -- 设置完毕后，重启登录生效（不需要重启服务）

    image-20210913234248123

  • 永久开启：Windows下在mysql安装目录的my.ini中添加修改

    image-20210913234428869

10、模拟分析海量数据

10.1 模拟海量数据

存储过程（无return）/存储函数（有return）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
create database testdata;
use testdata;
create table dept(
	dno int(5) primary key default 0,
	dname varchar(20) not null default '',
	loc varchar(30) default ''
)engine = innodb default charset = utf8;

create table emp(
	eid int(5) primary key,
	ename varchar(20) not null default '',
	job varchar(20) not null default '',
	deptno int(5) not null default 0
)engine = innodb default charset = utf8;

-- 通过存储函数 插入海量数据：
-- 创建存储函数：

-- randstring(6) ->aXiayx 用于模拟员工名称
delimiter $
set global log_bin_trust_function_creators=TRUE;
create function randstring(n int) returns varchar(255)
begin
	declare all_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	declare return_str varchar(255) default '';
	declare i int default 0;
	while i < n
	DO
		set return_str = concat(return_str,substring(all_str,floor(rand()*52)+1,1));
		set i = i+1;
	end while;
	return return_str;

end $

-- 产生随机整数
delimiter $ --指定程序结尾为$
set global log_bin_trust_function_creators=TRUE;
create function ran_num() returns int(5)
BEGIN
	declare i int default 0;
	set i = floor(rand()*100);
	return i;
end $

-- 通过存储过程插入海量数据：emp表中
-- 关闭自动提交 set autocommit = 0; 
delimiter $ --指定程序结尾为$
set global log_bin_trust_function_creators=TRUE;
create procedure insert_emp(in eid_start int(10), in data_times int(10))
BEGIN
	declare i int default 0;
	set autocommit = 0;
	repeat 
		insert into emp values(eid_start + i,randstring(5),'other',ran_num());
		set i = i+1;
		until	i = data_times
	end repeat;
	commit;
END $

-- 通过存储过程插入海量数据：dept表中
delimiter $ --指定程序结尾为$
set global log_bin_trust_function_creators=TRUE;
create procedure insert_dept(in dno_start int(10), in data_times int(10))
BEGIN
	declare i int default 0;
	set autocommit = 0;
	repeat 
		insert into dept values(dno_start+i,randstring(6),randstring(8));
		set i = i+1;
		until i = data_times
	end repeat;
commit;
end $

插入数据

1
2
call insert_emp(1000,800000);
call insert_dept(10,30);

10.2 分析海量数据

（1）profiles;

1
2
3
4
5
6
show profiles; -- 默认关闭
show variables like '%profiling%'; -- 查看profiling的状态
set profiling = on; -- 打开

select count(1) from emp;
show profiles; -- 会记录所有profiling打开之后的 全部SQL查询语句所花费的时间;缺点：不够精确，只能看到总共消费的时间，不能看到各个硬件消费的时间（cpu  io）

image-20210922234814134

（2）-精确分析：sql诊断

1
show profile all for query 91;

image-20210922234845173

1
show profile cpu,block io for query 91;

image-20210922235001910

（3）全局查询日志：记录开启之后的全部SQL语句（这次全局的记录操作，仅仅在调优、开发过程中打开即可，在最终的部署实施时一定要关闭）。

1
show variables like '%general_log%';

image-20210922235353256

1
2
3
4
set global general_log = 1; --开启全局日志
set global log_output = 'table';-- 记录到表中，开启后，会记录所有SQL：会被记录mysql.general_log表中
show variables like '%general_log%'; 
select * from mysql.general_log;-- 发现 argument全是一堆Blob

image-20210923000224975

1
2
alter table mysql.general_log modify column argument mediumtext not null; -- 修改argument的类型
select * from mysql.general_log;

image-20210923000946671

0%