MYSQL优化
参考：reference
1.MYSQL逻辑分层 ：连接层 服务层 引擎层 存储层
1.1引擎层分类
InnoDB(默认) ：事务优先 （适合高并发操作；行锁），同时处理多条请求。
MyISAM ：性能优先 （表锁），一次处理多条数据。
1.2指定数据库引擎(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 ;
2.SQL优化
2.1优化原因
性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理
(缓冲、线程数）
sql语句编写和解析的顺序(重要)
每一步骤产生一个虚拟表，这些虚拟表对于调用者来说是不能用的，仅仅作用于下一步骤，而只有最后的查询结果表才能被调用者所使用。
-- 编写过程：
select distinct -> from -> join -> on -> where -> group by -> having -> order by -> limit
-- 解析过程：
from -> on -> join -> where -> group by -> having -> select distinct -> order by -> limit
sql语句执行时是按照从右到左的顺序处理from子句中的表名，from子句中写在最后的表也即是基础表将被最先处理，因此在from子句中包含多个表的情况下，选择记录条数最少的表作为基础表，在某种程度上将会极大的提高其性能。如果有3个以上的表，则选择交叉表作为基础表。此处对性能优化来说相当重要。
2.2优化实质
SQL优化， 主要就是 在优化索引
索引： index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B树(默认)、Hash树…）
弊端：
1.索引本身很大， 可以存放在内存/硬盘（通常为 硬盘）
2.索引不是所有情况均适用： a.少量数据 b.频繁更新的字段 c.很少使用的字段
3.索引会降低增删改的效率（增删改 查）
优势:
1.提高查询效率（降低IO使用率）
2.降低CPU使用率 （…order by age desc,因为 B树索引 本身就是一个好排序的结构，因此在排序时 可以直接使用）
3.索引
3.1索引分类
主键索引 ：不能重复。id 不能是null
唯一索引 ：不能重复。id 可以是null
单值索引 ：单列索引 ;一个表可以多个单值索引
复合索引 ：多个列构成的索引 (name,age)
3.2创建索引
方式一: 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);
方式二: alter table 表名 索引类型 索引名（字段）
单值: 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，则该字段默认就是 主键索引
删除索引：
drop index 索引名 on 表名 ;
drop index name_index on tb ;
查询索引：
show index from 表名 ;
4.SQL性能问题
4.1SQL的执行计划
explain，可以模拟SQL优化器执行SQL语句，从而让开发人员 知道自己编写的SQL状况
-- 查询执行计划语法
explain +SQL语句(explain select * from tb)
执行之后的结果如下,我们可以看到有很多参数
-- 参数解释
id : 编号
select_type ：查询类型
table ：表
type ：类型
possible_keys ：预测用到的索引
key ：实际使用的索引
key_len ：实际使用索引的长度
ref :表之间的引用
rows ：通过索引查询到的数据量
Extra :额外的信息
4.2MySQL查询优化其会干扰我们的优化
MySQL内部服务层有一个sql优化器，有些情况下会对我们的sql语句进行优化,所以sql优化是一个概率事件。
4.3举例解释(Course,Teacher,TeacherCard)
解释一些比较重要的参数
4.3.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') ;
4.3.2参数id
Question1:查询课程编号为2或者教室编号为3的老师信息
explain SELECT teacher.* from teacher,course,
teachercard WHERE teacher.tid=course.tid AND
teacher.tcid=teachercard.tcid AND
(course.cid=2 or teachercard.tcid=3);
Step1:执行上述语句，会得到以下结果,执行顺序为t-tc-c
Step2:我们给teachercard加入两条数据，再次执行相同语句，会得到以下结果,执行顺序为t-c-tc
insert into teacherCard values(4,'tzdesc') ;
insert into teacherCard values(5,'twdesc') ;
结论一：id值相同，看数据量的大小，越小越先执行(本质上是笛卡尔积的问题，sql希望中间数据量尽量小 )。
Question2:查询教授sql课程的老师信息
explain SELECT teachercard.tcdesc from teachercard
,course ,teacher WHERE teacher.tid=course.tid
AND teacher.tcid=teachercard.tcid
AND course.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'));
执行第二条语句，会得到以下结果，执行顺序为c-t-tc
结论二：id值不同：id值越大越优先查询(本质:先执行内层，再执行外层)
Question3:查询教授sql课程的老师信息
-- 子查询+多表：
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') ;
执行语句，会得到以下结果，执行顺序为c-t-tc
结论三：id值有相同，又有不同： id值越大越优先；id值相同，看数据量的大小，越小越先执行
4.3.3参数select_type(查询类型)
PRIMARY:包含子查询SQL中的 主查询(最外层) SUBQUERY：包含子查询SQL中的 子查询 （非最外层）
SIMPLE:简单查询（不包含子查询、union） DERIVED:衍生查询(使用到了临时表)
触发衍生查询DERIVED的条件
a.在from子查询中只有一张表
explain select cr.cname from ( select * from
course where tid in (1,2) ) cr ;
table栏derived2中的2为第二张表的id
b.在from子查询中， 如果有table1 union table2 ，则table1 就是derived,table2就是union
explain select cr.cname from
( select * from course where tid = 1
union select * from course where tid = 2 ) cr ;
4.3.4参数type(索引类型)
system>const>eq_ref>ref>range>index>all ，要对type进行优化的前提：有索引
其中：system,const只是理想情况；实际能达到 ref,range左右，以下选择一些类型进行解释
A. eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）
常见于唯一索引 和主键索引,增加索引并执行语句。
-- 给teacherCard增加主键tcid
alter table teacherCard add constraint pk_tcid
primary key(tcid);
-- 给teacher增加唯一索引
alter table teacher add constraint uk_tcid
unique index(tcid) ;
-- 查询tcid
explain select t.tcid from teacher t,teacherCard tc
where t.tcid = tc.tcid ;
B.ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，多）
-- 插入数据
delete from teacherCard where id =4 or 5;
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';
C.range：检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效，从而转为 无索引all)
-- 增加索引
alter table teacher add index tid_index (tid) ;
-- 测试1
explain select t.* from teacher t
where t.tid in (1,2) ;
-- 测试2
explain select t.* from teacher t where t.tid <3 ;
D.index：查询全部索引中数据
-- 测试
explain select tid from teacher ;
-- tid 是索引， 只需要扫描索引表，不需要所有表中的所有数据
E.all：查询全部表中的数据
-- 测试
explain select cid from course ;
-- cid不是索引，需要全表所有，即需要所有表中的所有数据
4.3.5参数key_len(索引的长度)
作用：主要用于判断复合索引是否被完全使用
A.单个索引
-- 创建新表
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_len :60 在utf8：1个字符站3个字节
-- 加一个列
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个字节用于标识。
如果类型为varchar(20),会额外用2个字节 标识可变长度
B.复合索引
-- 删除单个索引
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 = '' ; -- key_len :121
explain select * from test_kl where name = '' ; -- key_len :60
第一条用到全部索引(查name1，会用到name)，第二个用到一半。
4.3.6参数ref
注意与type中的ref值区分。作用： 指明当前表所 参照的 字段。
-- 添加索引
alter table course add index tid_index (tid) ;
-- 测试，此时c.tid和t.tid都有索引
explain select * from course c,teacher t
where c.tid = t.tid and t.tname ='tw' ;
指出table c的字段tid 用到了 table t 的字段tid.
4.3.7参数Extra
(1)using filesort ： 性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。
A.单索引
准备数据
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。
B.复合索引
不能跨列（最佳左前缀原则）
-- 删除单索引
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 ;
结论：避免： 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 ; --using temporary
结论：避免：查询那些列，就根据那些列 group by .
(3)using index :性能提升; 索引覆盖（覆盖索引）。
只要使用到的列 全部都在索引中，就是索引覆盖using index
-- test02表中有一个复合索引(a1,a2,a3)
-- 测试一
explain select a1,a2 from test02 where a1='' or a2= '' ; --using index
-- 操作索引
drop index idx_a1_a2_a3 on test02;
alter table test02 add index idx_a1_a2(a1,a2) ;
-- 测试二
explain select a1,a2,a3 from test02 where a1='' or a2= '' or a3='';
-- 覆盖不了，所以没有using index.
(4)using where （需要回表查询）
假设age是索引列。但查询语句select age,name from …where age =…,此语句中必须回原表查Name，因此会显示using where.
5.优化示例
5.1单表优化
准备数据
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;
Question:查询authorid=1且 typeid为2或3的bid
explain select bid from book where typeid in(2,3)
and authorid=1 order by typeid desc
Step1:加索引
alter table book add index idx_bta (bid,typeid,authorid);
Step2:根据SQL实际解析的顺序，需要调整索引的顺序
/*索引一旦进行 升级优化，需要将之前废弃的索引删掉，防止干扰。*/
drop index idx_bta on book;
/*虽然可以回表查询bid，但是将bid放到索引中 可以提升使用using index*/
alter table book add index idx_tab (typeid,authorid,bid);
Step3:再次优化（之前是index级别)，范围查询有时候会失效
思路。因为范围查询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 ;
小结：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，则不会出现using where
explain select bid from book where authorid=1 and
typeid =3 order by typeid desc ;
5.2双表优化
准备数据
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;
Question:左连接left outer join
explain select *from teacher2 t left outer join course2 c
on t.cid=c.cid where c.cname='java';
加索引
alter table teacher2 add index index_teacher2_cid(cid) ;
alter table course2 add index index_course2_cname(cname);
原则：小表驱动大表 ，索引建立在经常查询的字段上
6.避免索引失效的一些原则
6.1复合索引
a.复合索引，不要跨列或无序使用（最佳左前缀）
b.复合索引，尽量使用全索引匹配
6.2不要在索引上进行任何操作
(计算、函数、类型转换)，否则索引失效
explain select * from book where authorid = 1 and typeid = 2 ;/*用到了a和t 2个索引*/
explain select * from book where authorid = 1 and typeid*2 = 2 ;/*用到了a 1个索引*/
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同时失效。
6.3复合索引不能使用不等于（!= <>）或is null (is not null)
复合索引中如果有>，则自身(概率)和右侧索引全部失效。
6.4like尽量以“常量”开头，不要以’%'开头，否则索引失效
explain select * from xx where name like '%x%' ; /*name索引失效*/
explain select * from teacher where tname like 'x%'; /*name索引成功*/
explain select tname from teacher where tname like '%x%';
/*如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。*/
6.5尽量不要使用类型转换（显示、隐式）
explain select * from teacher where tname = 'abc' ;
/*索引成功*/
explain select * from teacher where tname = 123 ;
/*程序底层将 123 -> '123'，即进行了类型转换，因此索引失效*/
6.6尽量不要使用or，否则索引失效
select * from teacher where tname = " " or tcid>1;
or会导致以左的索引失效，也就是tname失效。
15万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
