SQL优化

1 MySQL逻辑分层

1.1大致分为四层

1.2 InnoDB与MyISAM的区别

设计时
InnoDB是事务优先（适合高并发操作；行锁）
MyISAM是性能优先（表锁）

1. MyISAM不支持事务，而InnoDB支持。
2. InnoDB支持数据行锁定，MyISAM不支持行锁定，只支持锁定整个表。
3. InnoDB支持外键，MyISAM不支持。
4. InnoDB的主键范围更大，最大是MyISAM的2倍。
5. InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
6. InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实没啥用，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
7. MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point,Line,Polygon,Surface等。
8. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

查看数据库引擎：

show engines；

查看当前使用的引擎

show variables like ‘%storage_engine%’

1.3 MySQL索引设计原则

1. 对于经常查询的字段，建议创建索引。
2. 索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能。
3. ·避免对经常更新的表进行过多的索引，因为当表中数据更改的同时，索引也会进行调整和更新，十分消耗系统资源。
4. 不要在区分度低的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果。
5. 当唯一性是某字段本身的特征时，指定唯一索引能提高查询速度。
6. 在频繁进行排序分组（即进行 group by 或 order by操作）的列上建立索引，如果待排序有多个，可以在这些列上建立组合索引。

2 SQL优化

原因：性能低、执行时间长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器测试不合适

2.1 SQL

编写过程：

select dinstinct ...form ... join ...on ...where ...group by having ...order by ...

解析过程

form ... join ...on ...where ...group by having ...select dinstinct ...order by ... 

2.2 SQL优化

SQL优化：主要是索引优化
索引：相当于书的目录（查字典）

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

索引的弊端
1.索引本身很大，会使用更多的空间
2.索引不是所有情况都适用：少量数据、频繁更新的字段、很少被使用的字段都不宜使用索引
3.索引会提高查询的效率但是也会增删改的效率

索引的优势
1.提高查询效率（降低IO使用率）
2.降低CPU使用率（因为索引本身是排好序的结构，所以排序时可以减少CPU开支）

3 Btree与索引

3.1 B树与B+树

3.1.1 B树
B树：二叉搜索树

特征：
a.所有非叶子结点至多拥有两个儿子（Left和Right）；
b.所有结点存储一个关键字；
c.非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

3.1.2 B+树
数据全部存放在叶子节点中（查询任意的数据次数都为n次 n是B+树的高度）
a.非叶子结点的子树指针与关键字个数相同；
b.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树
c.为所有叶子结点增加一个链指针；
c.所有关键字都在叶子结点出现；

特征：
a.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
b.不可能在非叶子结点命中；(只能在叶子节点命中)
c.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

3.2.索引

索引的分类
1.单值索引：单列；一个表可以有多个单值索引
2.唯一索引：不能重复，可以是null，age、sex就不行
3.复合索引：多个列构成的索引（相当于多级目录），最佳左前缀原则

如何创建索引
创建方式一：

create 索引类型 索引名 on 表(字段)
# 单值
create index dept_index on table_name(dept);
#唯一索引
create unique index name_index on table_name(name);
#复合索引
create index depte_name_index on table_name(dept,name);

创建方式二：修改表

alter table 表名 索引类型 索引名（字段）
#单值
alter table table_name add index dept_index(dept);
#唯一索引
alter table table_name add  unique index name_index(name);
#复合索引
alter table table_name add index dept_name_index(dept,name);

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

删除索引：

drop index 索引名 on 表名；
drop index name_index on table_name;

查询索引：

show index from 表名;
show index from table_name;

4 SQL性能问题

a.分析SQL的执行计划：语句前加 explain
b.MySQL查询优化器会干扰我们的优化
Mysql官网的优化方法

查询执行计划：

explain + SQL语句;
explain select * from table_name;

4.1 explain后的反馈信息

id:编号
select_type:查询类型
table：表
type：类型
possible_keys:预测用到的索引
key：实际使用的索引
key_len：实际使用索引的长度
ref：表之间的引用
row：通过索引查询到的数据量
Extra：额外的信息

4.1.1 id
多表查询时，id值相同，从上往下顺序执行（数据量小的表先执行——笛卡尔积）

id值不同时，id值越大越先执行

id值有相同，又有不同，id值越大越优先；id值相同，从上往下顺序执行

4.1.2 table
当前执行使用的表

4.1.3 select_type：查询类型
PRIMARY:包含子查询SQL中的 主查询（最外层）
SUBQUERY：包含子查询SQL中的 子查询（非最外层）
SIMPLE：简单查询（不包含子查询和union）
DERIVED：衍生查询（使用到了临时表）
UNION:
UNION RESULT:告知开发者，哪些表之间存在UNION查询

触发衍生查询的两种情况：
a.在from子查询中只有一张表

select s.sname from (select * from student where sid in (1,2,3)) s;

b.在from的子查询中，有 table1 union table2，则table1就是derived

select s.sname from (select * from student where sid =1 union select * from student where sid = 2) s;

4.1.4 type：索引类型
system > const > eq_ref > ref > range > index > all
越往左性能越高，其中system，const至少理想情况；实际能达到ref > range

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

• system:只有一条数据的系统表；或衍生表只有一条数据的主查询时能过达到。
• const: 仅仅能查到一条数据的SQL，用于Primary key 或 unique索引（如果不是这两种索引类型就达不到这个级别）
• eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且仅有一个，不能多，也不能少），这种情况常见于唯一索引和主键索引
• ref:非唯一性索引，对于每个索引键返回匹配的所有行（可以0个，也可以多个）。
• range：检索指定范围的行，where后面是一个范围查询（between，>,<,>=, in有时候会失效，从而转为无索引）
• index : 查询索引的全部数据，只扫描索引表，不需要扫描表的全部数据
• all: 查询全部表的数据

4.1.5 possible_keys：可能用到的索引，只是一种预测，不准确
如果为NULL，则说明没有用索引
4.1.6.key：实际使用到的索引

4.1.7 key_len:索引的长度；
作用：用于判断复合索引是否被完全使用

4.1.8 ref
作用：指明当前表所参照的字段（const表示是常量）

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

4.1.10 Extra
常见的值有：

• using filesort：性能消耗大；需要’额外’的一次排序（查询）

#不会有using filesort:
explain select * from table_name where a = "" order by a;

#会出现using filesort:
explain select * from table_name where a = "" order by a1;

1. 对于单索引，如果排序和查找是同一字段，则不会出现using filesort；				
2. 如果不是同一字段，则会出现using filesort
如何避免：where哪些字段就order by哪些字段，就可以避免出现using filesort

3. 复合索引：不能跨列（不能违背  最佳左前缀）
where和order by按照复合索引的顺序使用，不要跨列或无序使用

• using temporary：性能会降低；原因：需要额外再多使用一张表，一般出现在group by语句中；
• using index：出现它性能会提升；索引覆盖（覆盖索引），性能提升的原因是不读取原文件，只从索引中获取数据；使用到的列全部都在索引中，就是索引覆盖。

#假如有复合索引 (a,b,c)

#下面情况会出现索引覆盖
select a,b from table_name where a = 1 and b = 2;

下面情况不会出现索引覆盖
select a,c from table_name where a = 1 and b = 2;

• using where ：在索引查询数据后还需要回原表查询数据

#假设age是索引列，name不是索引列
#下面情况就会出现usiing where 
select age,name from table_name where age =...;
select age,name from table_name where name=...;

• impossible where : where子句永远为false

#出现impossible  where 的情况
select * from table_name where a ='1' and a = '2';

5 优化案例

5.1 单表优化

将要用到的表

create table book(
	bid int primary key,
	name varchar(20) not null,
	authorid int not null,
	publicId int not null,
	typeid int not null
);

5.1.1 查询authorid=1且typeid为2或3的bid

#优化器前
select bid from book where typeid in (2,3) and authorid = 1 order by typeid desc;

优化：加索引，将type由ALL提升为INDEX

#加索引
alter table book add index index_bta (typeid,authorid，bid);

这里之所以要把bid放在最后是因为这样可以形成覆盖索引（using index），就不用回原表查询数据了。

二次优化：因为范围查询in有时会失效，因此交换typeid，与authorid的位置(当范围失效后，任然符合复合索引的最佳左前缀原则，故还能使用索引)。

#删除索引
drop index idx_tab on book;
#加索引
alter table book add index index_atb(authorid,typeid,bid);

5.2 两表优化

将要用到的表

#教师表
create table teacher(
	tid int primary key,
	cid int not null
);

#课程表
create tanle course(
	cid int,
	cname varchar(20)
) ;

5.2.1 在那张表加索引?
小表驱动大表，加索引到小表中

5.3 三表优化