SQL优化总结(MySQL版)
1 MySQL版本(此次使用)
2 MySQL数据库
MySQL我们都是简单的理解MySQL为一款数据库,但是问到什么是数据库,这个概念就有点大了,简而言之,我们理解的数据库就是用于"存储大量数据的仓库";
先举个栗子:
你想把自己的的钱存储起来,首先就会想到的是,我们需要将钱存到银行(银行就是存钱的地方),但是有的人将钱存到了不同的银行(工商,农业,邮政等等),那这些品牌的银行都是为了存钱的,只是他们内部的存钱流程和员工分配可能不一样,但最后,你存储的钱数绝对不会发生变化,他们会对你的钱进行处理,每一个人的钱都有序的存储起来,永远不会丢失,除非银行倒闭了.而我们要使用某一家银行进行存钱,就需要办张这家银行的卡.
推此即彼,那数据库确实就是存储我们数据地方,而MySQL就是一个数据库品牌,我们使用这个品牌就需要安装这个品牌的软件,也就是MySQL数据库.这个软件其实就是一套系统,他能够将我们数据进行处理,并规范化处理之后进行存储;
3 MySQL数据库会怎样存储我们的数据?
首先我们需要搞明白的是,我们使用了MySQL数据库进行存储我们数据,那么我们的数据存储到哪里了?其实我们的数据会以文件的形式存储到我们服务器中,再具体点就是存储到我们的服务器硬盘里.只不过这些文件被MySQL错了特殊处理,也就是更加规律了,MySQL能够有效的读取这些文件.那么问题就来了,再好的硬件也是有限度的,所以MySQL总会因为数据的问题导致读取的速度慢或者写入的速度慢等.而我们已经知道,MySQL读取数据其实就是依靠一句一句的指令(这里的指令表面上看就是我们写的SQL语句)
4 SQL优化的产生理由
上面我们已经知道,我们是通过写SQL的方式来读取数据的,但是对于同一条数据每个人写的SQL可能都不一样,比如:select * from student;这句SQL其实可以有很多写法比如select id,name,age,sex from student ; /select t.* from student t; 查的数据虽然是一样的,但是语句的在mysq的系统内部执行方式却天壤之别.获取数据一样的几条SQL有的执行起来慢的和蜗牛一样,有的则相对快很多,所以这个时候,在我们没办法改变我们的硬件设备的前提下,我们就可以考虑从SQL的写法上入手,来尽量提高SQL语句的执行速度,这就是SQL优化;在现在大数据时代的背景下,SQL优化的产生只是时间的问题.
5 MySQL的一些原理
想要了解MySQL的SQL优化,我们必须要了解一点MySQL的原理:
5.1 MySQL的逻辑分层
MySQL也是有喝多逻辑分层的,一条SQL进来之后会通过不同的逻辑层处理,最后得到我们想要的数据,或者插入更改我们的数据;
连接层: 提供与客户端连接的服务(说白了就是我们想要存储数据就需要先打开仓库的大门)
服务层: 提供用户使用的接口(说白了就是你想要对数据进行怎样的操作,增?删?查?改? 需要啥操作,就会调用啥接口,这里也是对SQL进行操作的一层);
引擎层:提供各种存储数据的方式.(就像安卓和苹果的引擎,各有各的优点和缺点.这里可以简单理解为我们的数据存储之后具有什么特点,这些数据会被怎样进行规范化处理之后存储.)
存储层: 很好理解就是存储数据的地方;
5.2 MySQL的引擎
两个主要的引擎:
(1) InnoDB : 事务优先 （适合高并发操作；行锁） 图中可见它是默认引擎;
(2) MyISAM ：性能优先 （表锁）
我们也可以通过SQL查看当前使用的引擎
show variables like '%storage_engine%' ;
创建表时指定数据库引擎
create table tb(
id int(4) auto_increment ,
name varchar(5),
dept varchar(5) ,
primary key(id)
)ENGINE=MyISAM AUTO_INCREMENT=1
DEFAULT CHARSET=utf8 ;
6 SQL优化前分析
对一个SQL进行优化的时候需要需要了解SQL语句的编写和解析过程是不一样的;
编写: select (distinct …)… from 表名 (left join…on… ) where … group by … having … order by …(limit…) 简记:私发我更好哦
解析过程 from 表名 on … left join … where … group by … having … select distinct … order by … limit …
其实解析过程就是先从from后面找到主表表,然后解析查询条件,接着是分组,最后是查询的字段的数据并加上排序等.值得注意的是order by 无论是书写还是解析都是在最后.
6.0 SQL优化最主要的内容
SQL优化主要就是优化索引,因为我们知道所以就相当于我们书本的目录,有了目录我们就能很快的定位到我们想要看的内容;索引就是一种数据结构,其实就是B树
6.1 索引好处与弊端
索引的好处和弊端:
1 索引本身就很大,可以存放在内存或者硬盘中;
2 经常更新字段,数据少的情况,很少使用的字段,都不适合使用索引;
3 索引会降低增删改的执行效率,因为一个数据发生变化所以也会发生变化;甚至可能失效;
好处:
索引可以大大提高查询的速率,我们在数据库中操作最频繁的往往也是查询操作;
6.2 索引种类
主键索引:一般就是ID主键 不能重复, 不能为null
唯一索引: 也不能重复,一般也是用主键ID建立索引,但是允许为null
符合索引:多个字段构成的索引(也就相当书本中的二级目录)
6.3 创建索引
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);
注意:
主键(primary key)本来就是默认有索引的;所以不需要创建;
6.4 查看索引
show index from 表名 ;
show index from 表名 \Gshow index from 表名 \g
6.5 删除索引
drop index 索引名 on 表名 ;
7 SQL性能分析
7.0 explain解析后的各个属性
如何分析一条SQL语句是否需要进行优化呢?需要怎样进行优化呢?
这里就需要我们先了解MySQL是怎样的对我们SQL执行的,就需要分析这条SQL的执行性能;
在SQL语句前面使用explain 关键字,这个关键字可以模拟SQL优化器执行SQL语句,从而让开发人员 知道自己编写的SQL状况;
explain +SQL语句
id : 编号
select_type ：查询类型
table ：表
type ：类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref :表之间的引用
rows ：通过索引查询到的数据量
Extra :额外的信息
7.1 准备数据
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);
create table teacherCard
(
tcid int(3),
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') ;
7.2 id:编号
查询课程编号为2 或 教师证编号为3 的老师信息
select distinct t.* from teacher t , course c , teacherCard tc where c.tid = t.tid and t.tcid = tc.tcid and c.cid =2 or tc.tcid = 3;
看到这三个查询的id都是一样的,此时就是从上往下依次执行,row的行数Mysql执行了三次,而且是查询到的数据少的先进行了查询,3,3,4 这就是笛卡尔运算查询
所以先查询的teacher表,接着是teacherCard表最后是course表
结论:
多表查询时id值相同的情况下从上往下依次执行,而且数据小的表优先查询
查询教授SQL课程的老师的描述（desc）
#多表查询
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')
);
结论:
将多表查询换成子查询后id值不再相同了,这时候id值约到的会先执行;
id值越大越优先查询 (本质：在嵌套子查询时，先查内层 再查外层)
#子查询+多表：
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') ;
结论:
id值有相同，又有不同： id值越大越优先；id值相同，从上往下 顺序执行
7.3 select_type:查询类型
PRIMARY:包含子查询SQL中的 主查询 （最外层）
SUBQUERY：包含子查询SQL中的 子查询 （非最外层）
simple:简单查询（不包含子查询、union）
derived:衍生查询(使用到了临时表)
衍生查询示例:
union 查询示例:在from子查询中， 如果有table1 union table2 ，则table1 就是derived,table2就是union
7.4 type:索引类型、类型
不同的索引类型SQL的执行效率是不一样的,下面是不同类型的执行效率由大到小的排序
system > const > eq_ref > ref > range > index > all
其中：system,const只是理想情况；实际能达到 ref>range
create table test01
(
tid int(3),
tname varchar(20)
);
insert into test01 values(1,'a') ;
commit;
system（忽略）: 只有一条数据的系统表 ；或 衍生表只有一条数据的主查询
const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 （类型 与索引类型有关）
alter table test01 add constraint tid_pk primary key(tid) ; explain select * from (select * from test01 )t where tid =1 ;
eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）
alter table teacherCard add constraint pk_tcid primary key(tcid); alter table teacher add constraint uk_tcid unique index(tcid) ; explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;以上SQL，用到的索引是 t.tcid,即teacher表中的tcid字段；
如果teacher表的数据个数 和 连接查询的数据个数一致（都是3条数据），则有可能满足eq_ref级别；否则无法满足。
ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
insert into teacher values(4,'tz',4) ; insert into teacherCard values(4,'tz222'); alter table teacher add index index_name (tname) ; explain select * from teacher where tname = 'tz';
range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ，从而转为 无索引all)
alter table teacher add index tid_index (tid) ; explain select t.* from teacher t where t.tid in (1,2) ; explain select t.* from teacher t where t.tid <4 ; explain select t.* from teacher t where t.tid between 1 and 2 ;
index：查询全部索引中数据
注意:当我们建立索引的时候其实索引字段已经建立了一张索引表,当我们查询只有索引列时候就会从索引表中进行查询,所以时间就会节省很多;
explain select cid from course ; #tid 是索引， 只需要扫描索引表，不需要所有表中的所有数据
all：查询全部表中的数据
explain select cid from course ; #cid不是索引，需要全表所有，即需要所有表中的所有数据
总结:
从上面可以看书select_type中的system和const一般我们写的SQL语句很难达到那种级别(太优秀!!!)一般都是一些系统表,并且查询的结果只有一条;
eq_ref :查询结果可以是多条,但是每一条数据都以唯一的;
ref:查询结果是多条;但是数据可以是>=0条;
7.5 possible_keys:可能用到的索引
顾名思义,就是可能用到的索引,但是不一定是准确的;有可能没有用到;
alter table course add index cname_index (cname); 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') ;
注意:
如果possible_keys和key是null证明是没有索引的;
7.6 key:实际使用的索引
就是实际使用到的索引;
7.7 key_len ：索引的长度
作用：用于判断复合索引是否被完全使用 （a,b,c）
其实就是使用到的索引的长度,跟索引所在字段的长度有关,字段越长如果使用到该索引就会越长;
create table test_kl
(
name char(20) not null default ''
);
alter table test_kl add index index_name(name) ;
explain select * from test_kl where name ='' ;
注意key_的长度适合mysql的是跟MySQL使用的字符集是有关的,如果使用的是utf-8 这里的key_len就是20*3=60个字节,因为utf-8 的每一个字符栈三个字节;
utf8:1个字符3个字节
gbk:1个字符2个字节
latin:1个字符1个字节
alter table test_kl add column name1 char(20) ; #name1可以为null
alter table test_kl add index index_name1(name1) ;
explain select * from test_kl where name1 ='' ;
可以看出如果索引字段可以为Null,则会使用1个字节用于标识。key_len = 21
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) ;
explain select * from test_kl where name1 = '' ;
explain select * from test_kl where name = '' ;
可以看到当使用符合索引进行查询数据的时候,key_len的长度是和我们创建索引时的先后顺序有关的,这里因为name在复合索引 的第一个位置,所以当查询时只用到了这样一个索引字段所以长度是20,但是当查询的字段是name1的时候就会用到name索字段,加上因为name字段没有默认值,所以会在字段长度上加1 所以就我们看到key_len=20*2+1=41
alter table test_kl add column name2 varchar(20) ; --可以为Null
alter table test_kl add index name2_index (name2) ;
explain select * from test_kl where name2 = '' ;
上图可知name2字段为varchar类型字段,这个类型是可变长度字段,可变长度的字段key_len长度会加2 所以这里的key_len= 20+1(可为Nul需要占用一个字节)+2(varchar为可变长度字段,索引站位字节+2)=23
7.8 ref属性
作用： 指明当前表所 参照的 字段。注意与type中的ref值区分.
select …where a.c = b.x ;(其中b.x可以是常量，const)
alter table course add index tid_index (tid) ;
explain select * from course c,teacher t where c.tid = t.tid and t.tname ='tw' ;
可以看到这里的const就是常量tw
7.9 rows属性
被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)
explain select * from course c,teacher t where c.tid = t.tid and t.tname = 'tz' ;
7.10 Extra属性
属性是看一个SQL查表的时候究竟是怎么查的,也就是说,是按照索引查询的,还是全表扫描的等等…
它是衡量一个SQL对mysql性能消耗大小的标准之一,后期我们也是会参照这个来判断我们的SQL语句是不是需要优化;(另一个就是type属性,通过判断索引类型来判断我们的SQL需不需要优化)
(1) using filesort : 性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。
create table test02 ( a1 char(3), a2 char(3), a3 char(3), index idx_a1(a1), index idx_a2(a2), index idx_a3(a3) ); explain select * from test02 where a1 ='' order by a1 ; explain select * from test02 where a1 ='' order by a2 ;
总结:
从上面两个语句的解析可以看到,对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort;
如果排序和查找不是同一个字段，则会出现using filesort；
如何避免:
where哪些字段，就order by哪些字段
复合索引的order by排序注意事项:
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) ; explain select *from test02 where a1='' order by a3 ; --using filesort explain select *from test02 where a2='' order by a3 ; --using filesort explain select *from test02 where a1='' order by a2 ; explain select *from test02 where a2='' order by a1 ; --using filesort
总结:
在复合索引中,应该避免where字段后order by 跨列和无序使用;
(2) using temporary:性能损耗大 ，用到了临时表。一般出现在group by 语句中。
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
explain select a1 from test02 where a1 in ('1','2','3') group by a2 ;
可以看出在查询的复合索引中,查询哪些列，就根据哪些列 group by
(3) useing index :using index :性能提升; 索引覆盖（覆盖索引）。
性能提升的原因是 只是从索引文件中获取的数据,没有从源文件中获取数据,也就是不需要回表查询,只要使用到的列全都在索引中就是索引覆盖.
show index in test02;
explain select a1,a2 from test02 where a1='' or a2= '' ;
(3) useing where :（需要回表查询）
假设age是索引列
但查询语句select age,name from …where age =…,此语句中必须回原表查Name，因此会显示using where.
drop index idx_a1_a2_a3 on test02;
alter table test02 add index idx_a1_a2(a1,a2) ;
explain select a1,a3 from test02 where a1='' or a3= '' ;--a3需要回原表查询
(4) impossible where ：where子句永远为false
explain select * from test02 where 1>3 ;
8 优化案例
8.0 单表优化
准备数据
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) ;
commit;
查询authorid为1,typeid为2或者3的bid
explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;
优化:
为用到的三个字段加索引之后再进行查询
alter table book add index index_bta (bid,typeid,authorid);
explain select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;
上图发现还是存在useing where,但是已经出现了useing index 可以进一步优化,这里需要将索引进行审计优化
# 首先删除老的索引
drop index index_bta on book;
# 升级新的索引(根据SQL实际解析的顺序，调整索引的顺序)
alter table book add index idx_tab (typeid,authorid,bid);
# 再次查询看效果
explain select bid from book where typeid in(2,3) and authorid=1 order by typeid desc ;
可以看出已经只有useing where 和useiing index 并且type属性是index
还可以继续优化
再次优化（之前是index级别）：思路。因为范围查询in有时会实现，因此交换 索引的顺序，将typeid in(2,3) 放到最后。
这样优化可以将type属性升级为ref效率更高.
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 ;
小结:
a.最佳做前缀，保持索引的定义和使用的顺序一致性 b.索引需要逐步优化 c.将含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这个索引，所以需要回原表（using where）；
也就是说in(2,3)会使索引失效,
下面是不使用in关键字的语句
explain select bid from book where authorid=1 and typeid = 3 order by typeid desc ;
8.1 两张表优化
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');
insert into course2 values(3,'kotlin');
commit;
不创建索引的情况下使用左连接查询
explain select * from teacher2 t left join course2 c on t.cid = c.cid where c.cname = 'java' ;
可以看到使用了useing where 和useing join buffer(表示mysql使用了连接缓存) 那么怎么写索引呢?怎么进行优化呢?
这里就相当于我们在代码中写的双重循环,两张表中,数据多的表称之为大表,数据小的表称之为小表,那么我们在写for循环的时候就会有两种循环的写法,最终循环的次数都以一样的,这里比方说,大表中有100条数据,小表中有50条数据那么循环程序如下:
//大表为外循环
for(int i = 0 ; i<=大表.length;i++){
for(j= 0 ; j<= 小表.length ; j++){
//循环了100*50=500次
}
}
//小表为外循环
for(int i = 0 ; i<=小表.length;i++){
for(j= 0 ; j<= 大表.length ; j++){
//循环了50*100=500次
}
}
但是我们看到这两个循环的区别之处就在于,外层循环/内层循环执行的顺序不一样,在代码中我=我们建议将小的循环放在外面,大的循环放在内部,在mysql中也不例外,只不过我们习惯在使用join on的时候on关键字后面的条件,建议将数据小的表放在前面,数据大的表放在后面,这里假设teacher中的数据少,那on后面循环就写作 on tcid = c.cid
那么我们的索引怎么建立呢?
其实就是小表驱动大表, 索引建立经常使用的字段上 （本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引） [一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]
alter table teacher2 add index index_teacher2_cid (cid);
alter table course2 add index index_course2_cname (cname);
explain select * from teacher2 t left join course2 c on t.cid = c.cid where c.cname = 'java' ;
可以看到type属性中的类型有明显提升,开始没建立索引之前是ALL,现在是ref
8.2 三张表优化(多表)
三张表优化的思路和两张表优化的思路一样,其实就是还是小表驱动大表,建立索引的时候需要将索引建立在经常查询的字段上,和两张表建立索引的方式一样,就不再详细写了.
8.3 索引优化的示例
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_4(a1,a2,a3,a4) ;
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4 ;
explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4 ;
可以看出这两个SQL的explain其实是一样的,但还是推荐使用第一种按照索引顺序的,第二种虽然都是ref,但是底层是经过了mysql的优化器自动优化了.
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;
上图中的useing where是因为where后面的条件中出现跨列使用索引,导致a3,a4索引失效,只有a1,a2索引有效,这一点我们从key_len=8可以看出,只有两个索引的字段长度.
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3;
上图可以看出看到有useing filesort 和useing where 以及useing index 这是因为where后面的a1索引没有失效所以是useing where 但是a4出现跨列(跨过a2,a3)索引导致会表查询,所以是useing where 而索引失效后使用order by a3就会在查询时在文件的内部进行一次数据排序,所以是useing filesort(文件内排序，“多了一次额外的查找/排序”)
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2 , a3;
这句没有出现useing filesort是因为是因为where 和 order by 后面的字段没有跨列使用,值得注意的是起始我们说的不跨列使用说的是where和order by 拼起来之后不允许跨列,所以这里就能理解了,useing where是因为后面的a4跨列使用(跨过a2,a3) 导致a4和a2,a3的索引失效,所以是useing where ,但是order by 后面使用了a2 所以where和order by 加起来a1 和a2并没有跨列,所以order by 的排序并不需要在文件内部进行排序,所以没有出现useing filesort
8.4 以上章节重点回顾
总结:
1 如果 (a,b,c,d)复合索引 和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用。如果部分一致(且不跨列使用)，则使用部分索引。
select a,c where a = and b= and d=2 where和order by 拼起来，不要跨列使用
3 using temporary:需要额外再多使用一张表. 一般出现在group by语句中；已经有表了，但不适用，必须再来一张表。
explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;--没有using temporary explain select * from test03 where a2=2 and a4=4 group by a3 ;--有using temporary4 SQL解析过程：
from … on… join …where …group by …having …select dinstinct …order by limit …
9 避免索引失效的一些原则
9.0 复合索引(建议)
a.复合索引，不要跨列或无序使用（最佳左前缀）
b.复合索引，尽量使用全索引匹配
9.1 避免对索引列的改动操作
不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效;
错误操作:
select …where A.x = … ; --假设A.x是索引
不要：select …where A.x*3 = … ;
explain select * from book where authorid = 1 and typeid = 2 ;--用到了at2个索引
explain select * from book where authorid = 1 and typeid*2 = 2 ;--用到了a1个索引
explain select * from book where authorid*2 = 1 and typeid*2 = 2 ;----用到了0个索引
explain select * from book where authorid*2 = 1 and typeid = 2 ;----用到了0个索引,原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。
上面是复合索引,所以左边索引失效右边也就失效了,但是对于对每一个字段分别加索引,就不会造成左边索引失效右边也失效的情况了,示例如下:
drop index idx_atb on book ;
alter table book add index idx_authroid (authorid) ;
alter table book add index idx_typeid (typeid) ;
explain select * from book where authorid*2 = 1 and typeid = 2 ;
9.2 复合索引避免对索引列进行比较运算符操作(个别比较运算符)
对于符合索引不能使用不等于(<> !=) 或者is null is, not null 等运算符操作,否则会导致自身以及右侧全部索引都会失效; 还有运算符 > 也会导致自身和右侧索引全部失效;
(注意这里的表达可能有点不对,后面我会对这句话进行验证)
show index in test03;
explain select * from test03 where a1=1 and a2>1 and a3=3 and a4=4;
从key_len=4 可以看出,只有a1索引没有失效;其他索引已经失效因为使用了大于号;
验证
explain select * from test03 where a1!=1 and a2=2 and a3>3 and a4=4;
explain select * from test03 where a1=1 and a2=2 and a3>3 and a4=4;
通过上面可以看出来,第一个第一句的key_len=16证明所有索引都没有失效,但是第二句的key_len=8 证明a3 和a4 的索引失效了,
所以看出,使用比较运算符会导致自身和右边全部索引失效的前提是,但第一个索引不使用比较运算符,后面索引使用的情况下会出现这种索引 失效的问题;
至于为什么出现这种情况我觉得是因为MySQL的优化器在内部帮我们对SQL进行了优化处理;
总结:
SQL的优化是一种概率问题,是否需要优化需要使用explain进行判断是不是需要进行优化该SQL
9.3 尽量使用索引覆盖(useing index)
select a,b,c where a=... and b=... and c=...; # a,b,c 都是索引
9.4 like模糊查询优化
like后面尽量以常量开头,不要以%开头,否则会造成索引失效
explain select * from teacher where tname like '%x%' ; # tname索引失效
explain select * from teacher where tname like 'x%' ; # tname索引不失效
如果我们在使用中必须使用模糊查询,那么能使用索引覆盖最好使用索引覆盖,这样能够挽救一份的索引失效情况;
如下所示:
explain select tname from teacher where tname like '%x%';
9.5 尽量不要使用类型转换
explain select * from teacher where tname = 'abc' ;
explain select * from teacher where tname = 123 ;#程序底层将 123 -> '123'，即进行了类型转换，因此索引失效
9.6 尽量不要使用 or 否则可能索引失效
explain select * from teacher where tname ='' or tcid >1 ; #将or左侧的tname 失效。
10 SQL优化的其他方法
10.1 exist 和 in
如果主查询的数据集大，则使用In ,效率高。
如果子查询的数据集大，则使用exist,效率高。select …from table where exist (子查询) ;
select …from table where 字段 in (子查询) ;
exist语法： 将主查询的结果，放到子查需结果中进行条件校验（看子查询是否有数据，如果有数据 则校验成功)，
如果 符合校验，则保留数据；
select tname from teacher where exists (select * from teacher) ;
select tname from teacher where exists (select * from teacher where tid =9999) ;
10.2 order by 优化
如果order by 使用不当会造成 useing filesort 文件内部排序;
其实useing filesort 内部排序也是有排序算法的,一种是单路排序,一种是双路排序,MySQL4.1 之前默认是双路排序,4.1之后默认使用的是单路排序;
双路排序: 其实就扫描两次磁盘,第一次扫描会读取排序字段也就是order by 后面的字段,排序字段进行排序(在buffer中进行排序),
第二次会读取其他字段,可以看出双路排序会有两次IO操作,这样就会比较消耗性能;
单路排序: 其实就是只读取一次磁盘,对所有字段进行读取排序;放到内存中,但是也有一定的安全隐患,如果数据量较大时,buffer缓冲区不能够一次读取完毕就会分多次进行读取;这样其实也会消耗很大性能;
综上所述,单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小
set max_length_for_sort_data = 1024 单位byte如果max_length_for_sort_data值太低，则mysql会自动从 单路->双路 （太低：需要排序的列的总大小超过了max_length_for_sort_data定义的字节数）
总结:
a.选择使用单路、双路 ；调整buffer的容量大小；
b.避免select * … //这是因为MySQL还需要解析*号
c.复合索引 不要跨列使用 ，避免using filesort
d.保证全部的排序字段 排序的一致性（都是升序 或 降序）
11 SQL排查
SQL排查可以判断一个SQL是不是属于慢SQL,并了解具体SQL语句详情;
慢查询日志:MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句 （long_query_time，默认10秒）
慢查询日志默认是关闭的；建议：开发调优时 进行临时打开,临时打开就是当服务重启的时候会自动恢复默认状态(关闭)
## 检查是否开启慢查询
show variables like '%slow_query_log%' ;
## 临时开启
set global slow_query_log = 1 ; # 在内存中开启
## 关闭临时开启的慢查询
exit;
service mysql restart
## 永久开启(不建议使用)
/etc/my.cnf 中追加配置：
vi /etc/my.cnf
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
慢查询阀值,并临时设置阈值
## 查询慢查询阈值
show variables like '%long_query_time%' ;
## 临时设置阈值
set global long_query_time = 5 ; --设置完毕后，重新登陆后起效 （不需要重启服务）
## 重新登录mysql后查询阈值是否修改成功
show variables like '%long_query_time%' ;
##永久设置阀值：(不建议使用)
/etc/my.cnf 中追加配置：
vi /etc/my.cnf
[mysqld]
long_query_time=3
模拟慢查询语句
select sleep(4);
select sleep(5);
select sleep(3);
select sleep(3);
## 查询超过阀值的SQL：
show global status like '%slow_queries%' ;
通过以上方式我们不能看到慢查询的具体信息,下面有两种查看详细信息的具体方式
1 直接查询日志文件
cat /var/lib/mysql/你的主机名-slow.log
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7ljZZuPe-1600590970941)(sql优化imgs\通过日志查看慢查询具体信息.png)]
2 通过mysql 的mysqldumpslow工具查看(推荐方案)
mysqldumpslow --help
s: 排序方式
r: 逆序
l: 锁定时间
g: 正则匹配模式
## 获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/你的主机名-slow.log
## 获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/你的主机名-slow.log
## 按照时间排序，前10条包含left join查询语句的SQL
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/你的主机名-slow.log
## 查看慢SQL
mysqldumpslow /var/lib/mysql/mycentosone-slow.log
12 锁机制
锁机制的出现就是为了解决因为资源共享而出现的并发问题,
简单的理解就是当你想操作同一条数据的时候,另一个人也在同一时间操作了该条数据,这时候,就是并发问题,如果另一个人在修改数据,那想要查询的数据就可能发生改变.
12.0 数据库锁分类
按照操作的类型来分可以分为:读锁和写锁
读锁:就是对同一条数据进行读操作,多个读取操作可以同时进行;
写锁: 当前写操作没有执行完毕不允许执行记性其他操作(读和写),因为可能数据可能发生改变,出现脏读等情况;
按照锁的操作范围可以分为:表锁和行锁
表锁: 对整张表的数据都进行锁定,好处就是加锁比较快,开销不大,不会出现死锁的问题,坏处就是容易发生锁冲突,并发能力不太好;如MyISAM存储引擎使用表锁
行锁: 顾名思义,就是对每一条数据加锁,开销大，加锁慢；容易出现死锁,锁的范围较小，不易发生锁冲突，并发度高（很小概率 发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）如InnoDB存储引擎使用行锁
12.1 表锁
create table tablelock
(
id int primary key auto_increment ,
name varchar(20)
)engine myisam;
insert into tablelock(name) values('a1');
insert into tablelock(name) values('a2');
insert into tablelock(name) values('a3');
insert into tablelock(name) values('a4');
insert into tablelock(name) values('a5');
commit;
## 加锁操作
lock table 表1 read/write ,表2 read/write ,...
## 查看锁
show open tables ;
## 加读锁
lock table tablelock read ;
select * from tablelock; --读（查），可以
delete from tablelock where id =1 ; --写（增删改），不可以
select * from emp ; --读，不可以
delete from emp where eid = 1; --写，不可以
可以看出加了read锁之后同一个会话中,只能对加锁的表进行操作,不能对其他表进行操作,并且对加锁的表只能金慈宁宫读操作,不能进行写操作;
再次打开一个会话2 看能否对加锁的表进行操作
select * from tablelock; --读（查），可以
delete from tablelock where id =1 ; --写，会“等待”会话0将锁释放
可以看出,第二个会话,可以对该加锁的表进行读操作,但是不能进行写操作,会等到会话已将锁释放之后才能执行删除操作;
## 释放锁
unlock tables ;
## 加写锁:
lock table tablelock write ;
当前会话（会话0） 可以对加了写锁的表 进行任何操作（增删改查）；但是不能 操作（增删改查）其他表
其他会话,对会话0中加写锁的表 可以进行增删改查的前提是：等待会话0释放写锁(也就是加锁的会话将锁释放了之后才能进行操作(增删改查))
总结:
MySQL表级锁的锁模式
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，
在执行更新操作（DML）前，会自动给涉及的表加写锁。
所以对MyISAM表进行操作，会有以下情况：
a、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，
但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
b、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，
只有当写锁释放后，才会执行其它进程的读写操作。
查看哪些表加了锁： show open tables ; 1代表被加了锁
分析表锁定的严重程度： show status like ‘table%’ ;
Table_locks_immediate :即可能获取到的锁数
Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)
Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎(当需要加的锁数大于5000时就采用innoDB引擎,否则采用MyISAM引擎)
12.2 行锁
create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb ; -- 注意这里的inodb引擎
insert into linelock(name) values('1') ;
insert into linelock(name) values('2') ;
insert into linelock(name) values('3') ;
insert into linelock(name) values('4') ;
insert into linelock(name) values('5') ;
因为mysql会自动提交commit,而oracle不会自动提交
为了研究行锁，暂时将自动commit关闭; set autocommit =0 ; 以后需要通过commit
1.如果会话x对某条数据a进行 DML操作（研究时：关闭了自动commit的情况下），则其他会话必须等待会话x结束事务(commit/rollback)后 才能对数据a进行操作。
2.表锁 是通过unlock tables，也可以通过事务解锁 ; 行锁 是通过事务解锁。行锁，一次锁一行数据；因此 如果操作的是不同数据，则不干扰。
12.2.1 行锁的注意事项
1 当表中没有索引时行锁会自动改变为表锁;
2 行锁的一种特殊情况：间隙锁：值在范围内，但却不存在
3 如果索引类 发生了类型转换，则索引失效。 操作会从行锁 转为表锁。
12.2.2 总结
行锁：
InnoDB默认采用行锁；
缺点： 比表锁性能损耗大。
优点：并发能力强，效率高。
因此建议，高并发用InnoDB，否则用MyISAM。
行锁分析：
show status like ‘%innodb_row_lock%’ ;
Innodb_row_lock_current_waits :当前正在等待锁的数量
Innodb_row_lock_time：等待总时长。从系统启到现在 一共等待的时间
Innodb_row_lock_time_avg ：平均等待时长。从系统启到现在平均等待的时间
Innodb_row_lock_time_max ：最大等待时长。从系统启到现在最大一次等待的时间
Innodb_row_lock_waits ： 等待次数。从系统启到现在一共等待的次数
13 结束语
本次学习是结合B站老师视频中的资料进行学习的,后面会放上视频老师地址,本人在老师资料的基础上进行了,重新学习和笔记总结,如果侵权请联系(私信CSDN即可)本人删除,
本人学习过程中将重点放在了mysql的逻辑分层和索引分析,以及优化方案等上面,知识是学不完的只有不断总结,在实际开发中运用上去,慢慢也就能掌握了,所以这里留下了一份资料以便后期使用到的时候更加方便,能够直接在里面进行查看.B站视频地址:(视频不适合初学者,需要有mysql基础)
1424
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
