原理
MYSQL逻辑分层 ：连接层 服务层 引擎层 存储层
InnoDB(默认) ：事务优先 （适合高并发操作；行锁）
MyISAM ：性能优先 （表锁）
查询数据库引擎： 支持哪些引擎？ show engines ;
查看当前使用的引擎 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 ;
优化原因：
性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）
a.SQL ：
编写过程：
select dinstinct ..from ..join ..on ..where ..group by ...having ..order by ..limit ..
解析过程：
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...
b.SQL优化， 主要就是 在优化索引
索引： 相当于书的目录
索引： index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B树(默认)、Hash树...）
索引的弊端：
1.索引本身很大， 可以存放在内存/硬盘（通常为 硬盘）
2.索引不是所有情况均适用： a.少量数据 b.频繁更新的字段 c.很少使用的字段
3.索引会降低增删改的效率（增删改 查）
优势：1提高查询效率（降低IO使用率）
2.降低CPU使用率 （...order by age desc,因为 B树索引 本身就是一个 好排序的结构，因此在排序时 可以直接使用）
索引
分类：
主键索引： 不能重复。id 不能是null
唯一索引 ：不能重复。id 可以是null
单值索引 ： 单列， age ;一个表可以多个单值索引,name。
复合索引 ：多个列构成的索引 （相当于 二级目录 ： z: zhao） (name,age) (a,b,c,d,...,n)
创建索引：
方式一：
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 表名 ;
show index from 表名 \G
SQL性能问题
a.分析SQL的执行计划 : explain ，可以模拟SQL优化器执行SQL语句，从而让开发人员 知道自己编写的SQL状况
b.MySQL查询优化其会干扰我们的优化
查询执行计划： explain +SQL语句
explain select * from tb ;
优化案例
单表优化、两表优化、三表优化
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 ;
(a,b,c)
(a,b)
优化：加索引
alter table book add index idx_bta (bid,typeid,authorid);
索引一旦进行 升级优化，需要将之前废弃的索引删掉，防止干扰。
drop index idx_bta on book;
根据SQL实际解析的顺序，调整索引的顺序：
alter table book add index idx_tab (typeid,authorid,bid); --虽然可以回表查询bid，但是将bid放到索引中 可以提升使用using index ;
再次优化（之前是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 ;
还可以通过key_len证明In可以使索引失效。
（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;
左连接：
explain select *from teacher2 t left outer join course2 c
on t.cid=c.cid where c.cname='java';
索引往哪张表加？ -小表驱动大表
-索引建立经常使用的字段上 （本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引） [一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]
小表：10
大表：300
where 小表.x 10 = 大表.y 300; --循环了几次？10
大表.y 300=小表.x 10 --循环了300次
小表:10
大表:300
select ...where 小表.x10=大表.x300 ;
for(int i=0;i<小表.length10;i++)
{
for(int j=0;j<大表.length300;j++)
{
...
}
}
select ...where 大表.x300=小表.x10 ;
for(int i=0;i<大表.length300;i++)
{
for(int j=0;j<小表.length10;j++)
{
...
}
}
--以上2个FOR循环，最终都会循环3000次；但是 对于双层循环来说：一般建议 将数据小的循环 放外层；数据大的循环放内存。
--当编写 ..on t.cid=c.cid 时，将数据量小的表 放左边（假设此时t表数据量小）
alter table teacher2 add index index_teacher2_cid(cid) ;
alter table course2 add index index_course2_cname(cname);
Using join buffer:extra中的一个选项，作用：Mysql引擎使用了 连接缓存。
（3）三张表优化A B C
a.小表驱动大表 b.索引建立在经常查询的字段上
示例：
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 ; --推荐写法，因为 索引的使用顺序（where后面的顺序） 和 复合索引的顺序一致
explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4 ; --虽然编写的顺序 和索引顺序不一致，但是 sql在真正执行前 经过了SQL优化器的调整，结果与上条SQL是一致的。
--以上 2个SQL，使用了 全部的复合索引
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3;
--以上SQL用到了a1 a2两个索引，该两个字段 不需要回表查询using index ;而a4因为跨列使用，造成了该索引失效，需要回表查询 因此是using where；以上可以通过 key_len进行验证
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a3;
--以上SQL出现了 using filesort(文件内排序，“多了一次额外的查找/排序”) ：不要跨列使用( where和order by 拼起来，不要跨列使用)
explain select a1,a2,a3,a4 from test03 where a1=1 and a4=4 order by a2 , a3; --不会using filesort
--总结：i.如果 (a,b,c,d)复合索引 和使用的顺序全部一致(且不跨列使用)，则复合索引全部使用。如果部分一致(且不跨列使用)，则使用部分索引。
select a,c where a = and b= and d=
ii.where和order by 拼起来，不要跨列使用
using temporary:需要额外再多使用一张表. 一般出现在group by语句中；已经有表了，但不适用，必须再来一张表。
解析过程：
from .. on.. join ..where ..group by ....having ...select dinstinct ..order by limit ...
a.
explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;--没有using temporary
b.
explain select * from test03 where a2=2 and a4=4 group by a3 ;
在信息技术发展早期，由于数据量小，不必过于重视 SQL 性能。但是随着互联网大数据的兴起，数据量级迅速增加，系统的响应速度成为需解决的最主要问题之一。对于海量数据，劣质 SQL 语句和优质 SQL 语句之间的速度差别可以达到上百倍。因此，俺们不能停留在只实现功能的水平，还要写出高质量的 SQL 语句，提高查询效率，提升系统性能。
SQL 优化的核心是对索引的使用。我们在前面已经介绍过索引的创建，接下来我们要学习 SQL 优化原理以及使用索引时要注意的问题。
索引（index）是帮助 MySQL 高效获取数据的一种数据结构，MySQL 默认使用 B+树作为索引数据结构。B+树是应文件系统所需而产生的一种 B 树的变形，其特点是非叶子节点只保存索引，不保存实际数据，数据都保存在叶子节点中，叶子节点中的数据从左到右呈现从小到大的趋势。
首先了解 B+树中阶的概念，即非叶子结点所拥有的最大子节点数。下图所示是一个 3 阶的 B+树：
一个 m 阶 B+树的结构定义如下：
- 每个结点至多有 m 个子结点；
- 每个结点（除根结点）至少有 ceil(m/2)个子结点
- 根结点至少有两个子结点
- 有 k 个子结点的非叶子结点必有 k 个最大（或最小）关键字
上例中(50,70)结点有两个子结点，所以它包含两个关键字。
有一张员工信息表（Employee），信息如下：
| EmpId | EmpName | EmpAge |
|---|---|---|
| 1 | 老张 | 45 |
| 2 | 小李 | 22 |
| 3 | 小王 | 18 |
| 4 | 老刘 | 36 |
| 5 | 小明 | 50 |
| 6 | 老赵 | 41 |
| 7 | 小谢 | 26 |
| 8 | Lucy | 19 |
给 EmpAge 添加索引列，得到一个 3 阶的 B+树，如下图所示：
接下来，简单演示该 3 阶 B+树的形成过程（初始为空结点）： 插入第一个数据，如下图所示：
插入第二个和第三个数据，如下图所示：
插入第四个数据，如下图所示：
但是此时超出 3 阶结点的要求，所以要对该结点进行分裂操作，对进行分裂的结点从中间拆分成两个结点，并把两个结点的关键字存入到父结点中，如下图所示：
插入第五个数据，如下图所示：
此时父结点保存的关键字也发生相应变化。
插入第六个数据，如下图所示：
此时对右子结点进行分裂，结果如下图所示：
最后依次插入数据 26，19 得到上面 3 阶的 B+树所示的结果。此次演示过程中，进行分裂时保存到父结点使用的是最大关键字，读者朋友们也可以尝试使用最小关键字来绘制该树结构。
无论使用哪种关键字，对该树的深度不会有影响，且数据都存放在叶子结点中，所有的叶子结点都处在同一层，所以查询效率是相同的。
当我们在表中插入数据的时候，该树也会发生变化，例如插入一个 age=40 的员工，结果如下图所示：
把溢出的结点从中间拆分成两个结点，并把关键字存入到父节点，如下图所示：
如果父节点关键字不大于阶数，则插入数据完成，超过阶数则继续进行分裂，得到下图所示结果：
现有 SQL 语句如下：
select * from Employee where EmpAge = 50;
分添加和不添加索引两种情况来分析：
- 不添加索引，MySQL 会顺序去查找，当执行到第 5 次的时候找到所需要的数据；
- 添加索引，MySQL 在 B+树中通过第一个结点判定 50 在右侧，进入第三个子结点，在第三个子结点中找到 50，总共执行了两次磁盘 IO。
初学者需注意的是，以上讲解为示意，实际上 50 这个值代表着一行数据（或其地址）。我们定位到了 50 即定位到了一行数据。
上述案例从示意的角度让大家感觉到了查询效率的提升，但并不明显，原因是数据量较小。当推广到大量甚至海量数据时，就能明显体验到索引的优势。总的来说，全表扫描的复杂度是 O(N)，B+树查找的复杂度是 O(logmN)，其中 m 是阶数，N 是数据总量。假设 N 为 108，B+树查找大约为 17 次，而全表扫描平均需要 108 次。
那是不是可以无限制的添加索引呢？其实索引既有优点也有缺点，先看一下索引的优点：
- 提高查询效率；
- 能降低 CPU 的使用率，例如我们使用 EmpAge 排序的时候，如果没有索引，MySQL 需要获取所有的 EmpAge，然后进行计算排序；而添加索引后，根据 B+树的特点，EmpAge 已经排好了顺序（左小右大）。
索引的缺点：
- 索引本身需要占用内存空间，如上述给 EmpAge 添加的索引，需要额外的空间来存放 B 树；
- 并不是所有情况都适用索引，如少量数据、频繁更新的字段、很少适用的字段；比如我把上述案例的年龄 22 改为 42，那么相应地 B 树也要改动，而且改动比较大；
- 索引会降低增、删、改的效率；比如修改操作，在没有使用索引的时候，直接修改值即可；而使用了索引后，不但需要改值，还要修改对应的 B 树中的值，增加了额外的开销。
SQL 排查与分析
在 MySQL 中，优化器是数据库的一个核心子系统，它负责制定在当时环境下对该 SQL 最有效的执行路径和执行计划。优化器主要根据索引来提高性能。如果 SQL 语句编写的不合理，就会造成优化器放弃索引而使用全表扫描，这样的 SQL 语句，我们就称为劣质 SQL 语句。
优化已有 SQL 语句，通常要进行三个步骤：
- 定位待优化的 SQL 语句
- 分析 SQL 语句的执行效率
- 给出相应的 SQL 优化方案
找到待优化的 SQL 语句需要借助慢查询日志。慢查询日志用于记录执行时间超过设定时间的 SQL 语句，MySQL 默认关闭该日志。查看慢查询日志参数的语句如下：
SHOW VARIABLES LIKE '%query%';
输出结果：
主要关注以下参数：
slow_query_log：慢查询日志开启状态；long_query_time：设置一个单位为秒的时间数值，查询时间超出这个值的 SQL 查询被界定为慢查询；slow_query_log_file：慢查询日志的存放路径。
可以通过 set global … 语句对慢查询相关参数进行设置。设置慢查询日志为开启状态：
SET GLOBAL slow_query_log = on;
设置慢查询日志存放路径：
SET GLOBAL slow_query_log_file = '/var/lib/mysql/master-slow.log';
设置慢查询日志的时间为 0.1 秒，其 SQL 语句如下：
SET GLOBAL long_query_time = 0.1;
退出 MySQL，重新连接后查看参数值：
SHOW VARIABLES LIKE '%query%';
输出结果：
准备一张员工表（employee），并在该表中插入 300000 条记录:
CREATE DATABASE test1;
USE test1;
CREATE TABLE employee(
id INT,
name VARCHAR(20),
age INT,
INDEX ix_name(name)
);
DELIMITER $
CREATE PROCEDURE auto_employee()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE(i < 300000)DO
INSERT INTO employee VALUES(i, 'emp_demo', 20);
SET i = i + 1;
END WHILE;
END
$
DELIMITER ;
CALL AUTO_EMPLOYEE();
然后利用模糊查询，该 SQL 语句的执行将超过 0.1 秒：
SELECT * FROM employee WHERE name LIKE '%aaaa%';
查看慢查询日志，能发现这句 SQL 被记录下来：
sudo cat /var/lib/mysql/master-slow.log
使用 EXPLAIN 关键字分析 SQL 执行效率：
EXPLAIN SELECT * FROM employee WHERE name LIKE '%aaaa%';
其中各字段含义如下：
-
id：编号，表在该 SQL 语句中的执行顺序
-
select_type：查询类型
该参数常用取值如下：
- SIMPLE：简单的 SELECT 查询，查询中不包含子查询或者 UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记
- SUBQUERY：在 SELECT 或 WHERE 列表中包含了子查询
- DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)，MySQL 会递归执行这些子查询，把结果放在临时表里
- UNION：UNION 中的第二个或后面的 SELECT 语句
- UNION RESULT：从 UNION 表获取结果的 SELECT
-
table：表
-
partitions：匹配的分区；
-
type：表示 MySQL 在表中找到所需行的方式，常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好），一个好的 SQL 语句至少要达到 range 级别，杜绝出现 all 级别：
- ALL：Full Table Scan，MySQL 将遍历全表以找到匹配的行；
- index：Full Index Scan，INDEX 与 ALL 区别为 INDEX 类型只遍历索引树；
- RANGE：只检索给定范围的行，使用一个索引来选择行；
- REF：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值；
- EQ_REF：类似 REF，区别在于使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用 PRIMARY KEY 或者 UNIQUE KEY 作为关联条件；
- CONST、SYSTEM：如将主键等值查询置于 WHERE 列表中，MySQL 就能将该查询转换为一个常量，SYSTEM 是 CONST 类型的特例，当查询的表只有一行的情况下，使用 SYSTEM；
- NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成；
-
possible_keys：查询时，可能使用的索引；
-
key：实际使用到的索引名，没有使用索引则为 NULL；
-
key_len：索引字段长度；
-
表之间的引用；
-
rows：扫描行数，该值是个预估值；
-
filtered：返回结果的行占需要读到的行的百分比；
-
Extra：执行情况的描述和说明。
查看 employee 表的索引，其 SQL 语句如下：
SHOW CREATE TABLE employee \G;
输出结果：
发现 name 字段上有索引，但是通过 explain 命令查看 SQL 的执行效率时，并没有用到索引（前面执行 explain 语句是 key 字段为 null，表明未用到索引），这是因为以“%”开头的查询会导致索引失效，这是 SQL 引擎自身的一个特点。这种情况下，一种基本的优化方案是去掉左边的“%”，虽然这会改变原 SQL 的本意，但此处笔者主要想对比性能上的差异。修改后的 SQL 语句如下：
SELECT * FROM employee WHERE name LIKE 'aaaa%';
输出结果：
再次利用 explain 命令分析执行效率，结果如下：
EXPLAIN SELECT * FROM employee WHERE name LIKE 'aaaa%';
可以发现，type 的值由 ALL（MySQL 将遍历全表以找到匹配的行）变成了 range（只检索给定范围的行，使用一个索引来选择行）；possible_keys 和 key 都的值均从 NULL 变成了 ix_name，说明此时利用到了索引来完成查询。
SQL 优化的其他注意事项
下面再列举几点注意事项：
-
首先可考虑给频繁出现在 where 及 order by 后的列建立索引；
-
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select name from city where population is null;可以设置默认值 0 来代替 null，确保 population 列没有 null 值，修改后的 SQL 语句如下：
select name from city where population = 0; -
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则搜索引擎将放弃使用索引而进行全表扫描；
-
应尽量避免在 where 子句中使用 or 运算符，否则搜索引擎将放弃使用索引而进行全表扫描，如：
select name from city where population = 1000 or population = 2000;可以修改为：
select name from city where population in(1000,2000); -
in 语句也要甚用，可用 exists 替换 in，如：
select name from city c where population in(select population from country);建议修改为：
select name from city c where exists(select population from country where population=c.population); -
不建议用“%xxx%”的格式进行模糊查询，否则会导致搜索引擎放弃使用索引而进行全表扫描，如：
select name from t where countrycode like '%ab%'; -
应尽量避免在 where 子句中对字段进行表达式或函数操作，这会导致搜索引擎放弃使用索引而进行全表扫描，如：
select name from city where population / 10 = 1000;建议修改为：
select name from city where population =1000*10; -
对于连续的数值，能使用 between 就不要使用 in，如：
select name from city where id in(1,2,3,4);建议修改为：
select name from city where id between 1 and 4; -
避免频繁创建和删除临时表，以减少系统表资源的消耗；
-
只含数值信息的字段尽量不要设计为字符型，而要使用数字型字段，否则会降低查询和连接的性能，并增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而数字型则只需要比较一次；
-
查询时避免使用
*来代表所有字段，应该用具体的字段列表来代替*； -
如果操作的数据量比较大，要避免使用游标，因为游标的效率较差；
-
运行时间久，长时间未提交的事务称为大事务；应尽量避免大事务操作，因为并行情况下大事务会锁定太多的数据，造成大量的阻塞和锁超时；还会造成主从同步延迟和回滚时间长等问题。
