MySQL-SQL优化

SQL 优化

第一节 准备数据

1、修改配置

为了能够看出 SQL 优化前后的性能差异，我们需要较为大量的数据。生成这些数据我们需要用到 MySQL 中的自定义函数。

但是 MySQL 默认关闭了自定义函数功能，所以我们需要通过修改配置文件来开启这项功能。

# 使用 vim 编辑器打开配置文件
vim /etc/my.cnf

在配置文件末尾增加如下内容：

# 设置为 1 表示开启这项功能
log_bin_trust_function_creators=1

然后重启 MySQL 服务：

systemctl restart mysqld.service

2、执行 SQL 语句生成数据

# 创建数据库
create database db_hr_sys;

# 使用数据库
use db_hr_sys;

# 创建数据库表：部门表
CREATE TABLE `dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 `ceo` INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 创建数据库表：员工表
CREATE TABLE `emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

# 声明函数：生成随机字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO  
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$

# 声明函数：生成随机数字
DELIMITER $$
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
DECLARE i INT DEFAULT 0;  
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))   ;
RETURN i;  
END$$

# 创建存储过程：插入员工数据
DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    #设置手动提交事务
 REPEAT  #循环
 SET i = i + 1;  #赋值
 INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i),rand_string(6),rand_num(30,50),rand_num(1,10000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  #提交事务
END$$

# 创建存储过程：插入部门数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(  max_num INT )
BEGIN  
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));  
 UNTIL i = max_num  
 END REPEAT;  
 COMMIT; 
END$$

# 调用存储过程，向部门表插入1万条数据
CALL insert_dept(10000);

# 调用存储过程，向员工表插入50万条数据
CALL insert_emp(100000,500000);

第二节 慢查询日志和视图

1、需求

在实际开发和项目运行的过程中，需要尽量准确的把查询时间消耗较大的 SQL 语句给找出来。然后有针对性的建立索引，再使用 explain 技术进行分析，找到性能瓶颈，最后调整 SQL 语句。

2、慢查询日志介绍

由 MySQL 负责以日志的形式记录那些执行时间超过阈值的 SQL 语句。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

3、查看及开启

慢查询日志记录功能默认关闭。

#默认情况下slow_query_log的值为OFF
SHOW VARIABLES LIKE '%slow_query_log%';  

命令行开启：

set global slow_query_log=1; 

慢查询日志记录long_query_time时间

SHOW VARIABLES LIKE '%long_query_time%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SET GLOBAL long_query_time=0.1; 

注意: 运行时间正好等于long_query_time的情况，并不会被记录下来。

永久生效

修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下四行配置进my.cnf文件 
slow_query_log=1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log 
long_query_time=3
log_output=FILE

4、查看捕获到的记录

①执行一条慢 SQL

select DISTINCT * from emp UNION (SELECT * from emp) UNION (SELECT * from emp)

②查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%'; 

显示结果是：

③查看日志信息内容

④使用mysqldumpslow命令导出

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

• -a: 不将数字抽象成N，字符串抽象成S
• -s: 是表示按照何种方式排序；
• c: 访问次数
• l: 锁定时间
• r: 返回记录
• t: 查询时间
• al:平均锁定时间
• ar:平均返回记录数
• at:平均查询时间
• -t: 即为返回前面多少条的数据；
• -g: 后边搭配一个正则匹配模式，大小写不敏感的；

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

第三节 Explain 分析

1、概念

①MySQL 内优化器

MySQL 体系结构中，包含 SQL 解析器、优化器等组件。SQL 解析器解析 SQL 之后，生成解析树。经过验证，解析树正确后，由优化器进一步优化解析树。

②Explain 分析

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。 分析你的查询语句或是表结构的性能瓶颈。

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

2、准备测试数据

create database db_hr;

use db_hr;

CREATE TABLE t1
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t2
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t3
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t4
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
INSERT INTO t1(content)
VALUES (CONCAT('t1_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t2(content)
VALUES (CONCAT('t2_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t3(content)
VALUES (CONCAT('t3_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t4(content)
VALUES (CONCAT('t4_', FLOOR(1 + RAND() * 1000)));

3、字段

3、id 字段

①id 字段含义

• 一个 select 对应一个 id 唯一的值
• id 值每出现一次，就代表 执行SQL语句的一个大步骤
• 总体的 SQL 语句执行的『大步骤』越少越好
• 多个 id 值
  • id 值相同：代表是小步骤。按照从上到下的顺序依次执行
  • id 值不同：代表有多个大步骤，大步骤按照id从大到小顺序执行

②举例

[1]id 值相同的情况

SQL 本身：

select t1.id,t2.id,t3.id,t4.id from t1,t2,t3,t4

应用 Explain 分析：

explain select t1.id,t2.id,t3.id,t4.id from t1,t2,t3,t4

执行结果：

[2]id 值不同的情况

EXPLAIN
SELECT t1.id
FROM t1
WHERE t1.id = (SELECT t2.id FROM t2 WHERE t2.id = (SELECT t3.id FROM t3 WHERE t3.content = 't3_354'))

[3]相同的 id 值和不同的 id 值都有

EXPLAIN
SELECT t1.id, (select t4.id from t4 where t4.id = t1.id) id4
FROM t1,
     t2

[4]有子查询但是 id 值相同

explain
select t1.id
from t1
where t1.id in (select t2.id from t2);

这是因为查询优化器将子查询转换为了连接查询。

4、select_type 字段

①字段含义介绍

一条 SQL 语句总体来看：其中可能会包含很多个 select 关键字。每一个 select代表整个SQL语句执行计划中的一次小的查询，而每一个 select 关键字的每一次查询都有可能是不同类型的查询。

select_type 字段就是用来描述每一个 select 关键字的查询类型，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。而通过查看各个小查询部分扮演的角色，我们可以了解到整体 SQL 语句的结构，从而判断当前 SQL 语句的结构是否存在问题。

②介绍字段取值含义

取值	含义
SIMPLE	简单的 select 查询，查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询
DEPENDENT SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层
UNCACHEABLE SUBQUREY	表示这个 subquery 的查询要受到外部表查询的影响
DERIVED	在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)。MySQL 会递归执行这些子查询，把结果放在临时表里
UNION	这是 UNION 语句其中的一个 SQL 元素
UNION RESULT	从 UNION 表获取结果的 SELECT，也就是在 UNION 合并查询结果的基础上，不使用全部字段，选取一部分字段。

具体分析如下：

• SIMPLE

  查询语句中不包含UNION、不包含子查询的查询都算作是SIMPLE类型，比方说下边这个单表查询的select_type的值就是SIMPLE：

  mysql> EXPLAIN SELECT * FROM s1;

  

  当然，连接查询也算是SIMPLE类型，比如：

  mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

  

• PRIMARY

  对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY，比方说：

  mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

  

  从结果中可以看到，最左边的小查询SELECT * FROM s1对应的是执行计划中的第一条记录，它的select_type值就是PRIMARY。

• UNION

  对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果，这就不多举例子了。

• UNION RESULT

  MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT，例子上边有。

• SUBQUERY

  如果包含子查询的查询语句不能够转为对应的semi-join的形式（不用管什么是 semi-join，只需要知道这是进一步优化），并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，比如下边这个查询：

  mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

  

  可以看到，外层查询的select_type就是PRIMARY，子查询的select_type就是SUBQUERY。需要大家注意的是，由于 select_type 为 SUBQUERY 的子查询会被物化（将子查询结果作为一个临时表来加快查询执行速度），所以只需要执行一遍。

• DEPENDENT SUBQUERY

  如果整体 SQL 语句执行的顺序是：

  • 先执行外层查询
  • 再执行内层子查询
  • 然后外层查询结果中的每一条再去匹配内层子查询结果的每一条

  这样，内外两层的查询结果就是相乘的关系。相乘就有可能导致总的查询操作次数非常大。所以经过 explain 分析后，如果发现查询类型是 DEPENDENT SUBQUERY 就需要引起各位注意了——这是一个危险的信号，通常是需要修复的一个问题！

  当然，就实际工作中来说：别说 DEPENDENT SUBQUERY，就连 SUBQUERY 都不应该出现。

EXPLAIN
SELECT t1.id, (select t4.id from t4 where t4.id = t1.id) id4
FROM t1,
     t2;

5、table 字段

显示当前这一步查询操作所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是别名。不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以 MySQL 规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

6、partitions 字段

代表分区表中的命中情况。如果是非分区表，该项为 null。逻辑上是一个整体的数据，可以在物理层保存时，拆分成很多个分片。分片在分区中保存。数据分片的好处是：

• 方便在很多个不同分区之间方便的移动分片，分摊负载，弹性扩容。
• 给主分片创建复制分片可以增强数据安全性。

7、type 字段 [重要]

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。具体取值参见下表（从上到下，性能越来越好）：

取值	含义
ALL	全表扫描
index	在索引表（聚簇索引、非聚簇索引都算）中做全表扫描
range	在一定范围内查询索引表
ref	通过普通的二级索引列与常量进行等值匹配时来查询某个表
eq_ref	在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref
const	根据主键或者唯一二级索引列与常数进行等值匹配
system	表仅有一行记录，这是const类型的特例，查询起来非常迅速
null	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

在上述查询方式中，从 eq_ref 开始，条件就很苛刻了，不容易达到。所以实际开发时要求，至少能达到 range 水平，最好能达到 ref。

下面是可以参考的例子：

# 创建数据库表
create table t_emp
(
    emp_id     int auto_increment primary key,
    emp_name   char(100),
    emp_salary double(10, 5),
    dept_id    int
);

create table t_dept
(
    dept_id   int auto_increment primary key,
    dept_name char(100)
);

# emp_id 主键索引
# emp_name 唯一索引
create unique index idx_emp_name_unique on t_emp (emp_name);
# dept_name 普通索引
create index idx_dept_name on t_dept (dept_name);

# 情况一：type 的值是 all
# 原因：由于没有用到任何索引，所以执行全表扫描
explain
select emp_salary
from t_emp;

# 情况二：type 的值是 index
# 原因：查询的是建立了索引的字段，而且没有指定 where 条件。
# 在执行查询时扫描索引的整个 B+Tree
explain
select emp_id
from t_emp;

explain
select emp_name
from t_emp;

explain
select dept_name
from t_dept;

# 情况三：type 的值是 range
# 原因：在一定范围内访问索引 B+Tree

# emp_salary 普通索引
create index idx_emp_salary on t_emp (emp_salary);

explain
select emp_id, emp_name
from t_emp
where emp_salary between 1000 and 5000;

# 情况四：type 的值是 ref
# 原因：通过普通的二级索引列与常量进行等值匹配时来查询
explain
select dept_name
from t_dept
where dept_name = '研发部';

# 情况五：对 t_dept 表的查询中，type 的值是 eq_ref
# 原因：在进行关联查询时，被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问
explain
select emp_id, emp_name, emp_salary
from t_emp e
         left join t_dept td on e.dept_id = td.dept_id;

# type 的值是 const
# 原因：使用常量值查询一个唯一索引，返回唯一一条记录
explain
select emp_id, emp_name, emp_salary
from t_emp
where emp_name = 'aaa';

8、possible_keys 字段

在查询中有可能会用到的索引列。如果没有任何索引显示 null。

9、key 字段

key 列显示 MySQL 实际决定使用的键（索引），包含在 possible_keys 中。

10、key_len 字段[重要]

key_len 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要，值越大索引的效果越好——因为值越大说明索引被利用的越充分。

字节数计算方式：

• 索引对应字段类型：
  • 数值类型：最终结果就是数值类型字段宽度（我们关心的是字节数）本身
    • int（11） ：我们关心的是int类型占四个字节而不是 11本身
    • double(10 , 5) :我们关心的是 double类型占 8 个字节， 而不是 10,5
  • 字符串类型：查看字符集类型
    • UTF-8：需要给字段长度 × 3
    • GBK：需要给字段长度 × 2
  • 如果是 varchar 这样的变长字符串类型：再 + 2
  • 如果是允许为空的字段：再 + 1

举例：customer_name 字段声明的类型是 varchar(200)，允许为空。

200 × 3 + 2 + 1 = 603

举例：

# 下面分析结果的 key_len 字段的值是 310，我们来看看是怎么算出来的
# 先看 emp_name
# emp_name 是字符串类型，它的字段宽度是 100，字符集是 UTF-8 需要乘 3，是定长字段不需要 +2，允许为空需要 +1，所以：100×3+1 = 301
# emp_salary 是数值类型，本身占 8 个字节，允许为空需要 + 1，所以：8 + 1 = 9
# 总和：301 + 9 = 310
explain select emp_name,emp_salary from t_emp
where emp_name = '李四' or emp_salary = 1000;

11、ref 字段

表示查询条件中，我们的索引列和谁去比较，是常量还是另一张表的字段。

①const

explain select emp_id,emp_name,emp_salary from t_emp where emp_id=5;

②某个字段

explain
select emp_id, emp_name, emp_salary
from t_emp e
         left join t_dept d on d.dept_id = e.dept_id
where emp_id = d.dept_id;

12、rows 字段

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。从SQL优化的角度来说这个值越小越好。

13、filtered 字段

通过存储引擎从硬盘加载数据到服务层时，受限于内存空间，有可能只能加载一部分数据。filtered 字段显示的值是：已加载数据 / 全部数据 的百分比。只是不显示百分号。

14、extra 字段

顾名思义，Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。

下面画粗字体需要适当留意一下：

取值	含义
using where	不用读取表中所有信息，仅通过索引就可以获取所需数据。 言外之意是 select 查询的字段都带有索引。 不管 select 查询多少个字段，这些字段都在索引中。
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort	当语句中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序” 这里的文件指的是保存在硬盘上的文件。 之所以会用到硬盘，是因为如果查询的数据量太大，内存空间不够，需要在硬盘上完成排序。 如果确实是很大数据量在硬盘执行排序操作，那么速度会非常慢。
Using join buffer	buffer 指缓冲区，该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 举例来说：where t_name like “%xxx%”，这个条件中的 t_name 字段没有加索引
Impossible where	where 子句中指定的条件查询不到数据的情况
Select tables optimized away	这个值表示目前的查询使用了索引，然后经过优化器优化之后，最终执行的是一个聚合函数，从而让最终的查询结果只返回一行
No tables used	查询语句中使用 from dual 或不含任何 from 子句

15、使用建议

• EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN 不考虑各种 Cache
• EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划

第四节 索引失效

1、取消缓存功能

为了便于测试，避免 MySQL 内的缓存机制干扰分析结果，我们在分析 SQL 语句时取消 SQL 语句的缓存功能。

使用 SQL_NO_CACHE 关键字：

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30;

当然，实际开发时肯定是不会加的。

2、最左原则 [重要]

①有可能用到索引的子句

所有有过滤功能的子句都会将相关字段去和索引尝试匹配：

• ON 子句
• WHERE 子句
• GROUP BY 子句
• HAVING 子句
• LIMIT 子句

②最左原则

简单来说就是：MySQL 在决定是否要应用索引时，会对照 SQL 语句中要过滤的字段的顺序和索引中字段的顺序。那么具体是怎么对照的呢？请看下面的细节：

[1]创建索引

CREATE INDEX idx_age_deptid_name ON emp(age, deptid, NAME);

按照这个索引创建方式，索引中字段的顺序是：age、deptid、NAME

[2]仅查询 age

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30;

索引生效！

TIP

key_len 的值为什么是 5 ？

因为我们现在用到的索引字段就是 age 这一个字段，int 类型的字段占 4 个字节，可以为空再 +1。所以是 5。

[3]查询 age 和 deptId

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  and deptid = 4;

索引生效！

[4]查询 age、deptId 和 name

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  and deptid = 4
  AND emp.name = 'abcd';

索引生效！

TIP

单值索引和复合索引的选择

在实际开发中，一个数据库表包含多个字段，其中有若干个字段有很大几率出现在 where 或其他有可能触发索引的子句中。那么我们倾向于创建『复合索引』，而不是『单值索引』。

因为一个复合索引能够涵盖到其中包含的每个字段；而给每一个字段分别创建单值索引会生成更多的索引表，增加切换、磁盘存储、I/O 等方面的开销。

[5]查询 deptId、name 和 age

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE
  deptid = 4
  AND emp.name = 'abcd'
  and  emp.age = 30
;

看上去索引是生效的，但是很明显顺序不一致，不满足最左原则，本来是不应该生效的：

• 查询顺序：deptId、name、age
• 索引顺序：age、deptid、NAME

但是为什么索引生效了呢？其实原本是不应该生效的，但是此时是 MySQL 的 SQL 优化器调整了解析树，使查询字段符合了索引顺序，这才让索引生效了。

但是尽管优化器能够帮助我们进行调整，但是调整本身也需要付出代价，产生系统开销。所以我们开发时还是要尽量和索引中字段顺序一致。

[6]仅查询 deptId

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE deptId = 5
;

索引没有生效！

但是很奇怪，deptId 这个字段明明是在索引中呀？这是因为本次查询没有满足最左原则。

在索引中，age 字段在最左边，现在查询的 deptId 作为第一个查询的字段不是 age，这就违背了最左原则。

TIP

为什么 MySQL 会如此执着于『最左』字段？

这是因为生成索引所在的 B+Tree 的时候，需要对索引值进行排序。那么如果我们指定的是联合索引，那么将涉及到多个字段的排序。例如：age、deptId、name这三个字段要排序的话，肯定优先根据 age 排序；然后在 age 值有相同数据时对 deptId 排序，以此类推。

所以我们在实际查询时，需要首先根据 age 字段在索引 B+Tree 中进行二分法查找。此时如果没有提供 age 字段，那将无法使用索引。

[7]查询 deptId 和 name

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE deptId = 5 and name = "aaa"
;

索引没有生效！同样是因为违背了最左原则。

[8]查询 deptId 和 name 并按 age 排序

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE deptId = 5 and name = "aaa" order by age
;

索引没有生效！这是因为 order by 没有过滤功能，不会触发索引。相当于查询过程中没有 age 字段参与。

[9]查询 age 和 name

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  AND emp.name = 'abcd';

此时联合索引生效，但是 key_len 字段的值是 5，而不是 68，说明 name 字段并没有按照索引去查询。对 name 字段来说，索引没有生效。

[10]结论

要遵循最左原则，查询字段中至少要有索引中的最左字段作为过滤条件存在。而且就最左原则本身来说，它要求索引最左字段在查询顺序中也最左。只不过只要最左字段出现，优化器会帮我们调整到查询顺序中的最左。而且还有一个要求是：中间不能断。中间一旦断开，后面的就都无法使用索引了。

TIP

where 子句部分和最左原则对照，看是否生效的口诀：带头大哥不能死，中间兄弟不能断

3、索引失效的其他情况

①在 where 子句条件中使用函数

首先删除上例索引：

drop index idx_age_deptid_name on emp;

创建新索引：

create index idx_name on emp(name);

分析查询（left 函数表示取 name 字段的前三个字符）：

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE left(name, 3) = "abc"
;

结论：使用了 left() 函数导致了索引失效。

②范围查询

首先删除上例索引：

drop index idx_name on emp;

创建新索引：

CREATE INDEX idx_age_deptid_name ON emp(age, deptid, NAME);

分析查询：

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  AND emp.deptId > 20
  AND emp.name = 'abc';

分析结果：

看起来仍然是生效的，但是我们再另外创建一个索引，把范围查询对应的 deptId 放在后面：

CREATE INDEX idx_age_deptid_name_2 ON emp(age, NAME, deptid);

把查询顺序也改一下：

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.age = 30
  AND emp.name = 'abc'
  AND emp.deptId > 20;

分析结果：

请大家注意两点变化：

• 变化1：MySQL 会优先选择新创建的索引 idx_age_deptid_name_2
• 变化2：key_len 数值变大了很多。

分析：

• key_len 的计算角度：age 字段贡献了 5，deptId 字段贡献了 5，而 name 字段如果应用了索引，那么它应该贡献 63。但是最终 key_len 是 10，说明索引生效的字段是 age 和 deptId
• type 的取值角度：range，说明查询的类型是范围查询，在当前 SQL 语句中 deptId 做的就是范围查询
• 解决方案角度：把 deptId 放在后面，三个字段的索引就都生效了。说明范围查询并不是祸害自己，而是祸害后面的查询条件。

结论：

• 注意点1：创建索引时应该把容易做范围查询的字段往后放
• 注意点2：实际编写 SQL 语句时，做范围查询的字段参照索引中的顺序，也是往后放

进一步的问题：如果范围查询有多个呢？

创建索引：

CREATE INDEX idx_age_deptid_name_3 ON emp(NAME, age, deptid);

执行分析：

EXPLAIN
SELECT SQL_NO_CACHE *
FROM emp
WHERE emp.name = 'abc'
  AND emp.age > 30
  AND emp.deptId > 20;

查看分析结果，发现 key_len 是 68，68 = 63 + 5。说明生效的字段是 name、age，deptId 还是没有生效。这就说明范围查询即使放在后面也只有第一个生效。

③不等于

所有不等于操作都会导致索引失效：

• !=
• <>
• not

测试的语句：

# 删除上例的索引
drop index idx_age_deptid_name on emp;
drop index idx_age_deptid_name_2 on emp;

# 只针对 name 这一个字段创建一个新的索引
create index idx_name on emp(name);

# 分析对 name 查询的 SQL 语句
explain select sql_no_cache * from emp where name != 'aaa';
explain select sql_no_cache * from emp where name <> 'bbb';
explain select sql_no_cache * from emp where name is not null;

分析结果：

④like

并不是所有 like 查询都会导致索引失效：

• 不会导致索引失效的 like 用法：
  • 不使用 %
  • % 放在中间或后面
• 会导致索引失效的 like 用法：% 放在前面

[1]情况一：like 条件没有 %

explain select sql_no_cache * from emp where name like 'aaa';

索引生效！

[2]情况二：like 条件把 % 放右边

explain select sql_no_cache * from emp where name like 'aaa%';

索引生效！

[3]情况三：like 条件把 % 放中间

explain select sql_no_cache * from emp where name like 'aaa%bbb';

索引生效！

[4]情况四：like 条件把 % 放左边

explain select sql_no_cache * from emp where name like '%bbb';

索引没有生效！

当然，左右都有 % 的情况和这里一样。

⑤涉及类型转换

所谓类型转换就是指：我们给查询条件传入的参数和原本的类型不一致。但是这种情况不是必然会导致索引失效。

• 失效情况举例：让 char 类型的字段和 123 比较
• 有效情况举例：让 int 类型的字段和 ‘123’ 比较

[1]失效情况

explain select sql_no_cache * from emp
where name=123;

分析结果：

[2]仍然有效的情况

explain
select sql_no_cache *
from emp
where age = '123';

分析结果：

4、小练习

假设目前我们有索引的情况是：index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 where b = 3 and c = 4 where c = 4	N，违背最左原则
where a = 3 and c = 5	使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，c 被 b 断给了
where a is null and b is not null	is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引，因为使用了函数
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c。虽然用到了 like，但是左边是确定的。
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a, %不能在最左边
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a,%不能在最左边
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

5、小结

• 对于单键索引，尽量选择过滤性更好的索引（例如：手机号，邮件，身份证）。这样更容易过滤掉大量数据，为后面操作减轻负担。
• 在选择组合索引的时候，过滤性最好的字段在索引字段顺序中，位置越靠前越好。这样可以更早过滤掉大量数据。
• 选择组合索引时，尽量包含where中更多字段的索引。
• 组合索引出现范围查询时，尽量把这个字段放在索引次序的最后面
• 尽量避免造成索引失效的情况

第五节 关联查询优化

1、数据准备

#分类
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
#图书
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
 
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
 
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

2、left join

①测试

开始是没有加索引的情况。下面开始explain分析：

EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;

分析结果：

添加索引优化

ALTER TABLE book ADD INDEX Y (card); 
ALTER TABLE class ADD INDEX X (card); 

重新分析的结果：

看这个分析结果发现：在 class 表上添加的索引起的作用不大。

③结论

• 小表驱动大表
  • 小表：相对来说记录较少的表
  • 大表：相对来说记录较多的表
• 驱动方式识别
  • left join：左边驱动右边（此时把小表放在左边）
  • right join：右边驱动左边（此时把小表放在右边）
• 加索引的方式：通常建议在大表（被驱动）的表加索引，效率提升更明显。
• 原因：
  • 原因1：被驱动表加了索引之后，收益更大。从 ALL -> ref
  • 原因2：外连接首先读取驱动表的全部数据，被驱动只读取满足连接条件的数据。

3、inner join

换成inner join（MySQL自动选择驱动表）

# 特意将 book 放在 from 子句，去对 class 表做内连接
EXPLAIN
SELECT SQL_NO_CACHE *
FROM book
         inner JOIN class ON class.card = book.card;

分析结果：

MySQL 还是选择了 class 作为驱动表。

需要给book表多添加记录，让这个表数据流有明显差异才有效果

4、小结

• 保证被驱动表的 join 字段被索引。join 字段就是作为连接条件的字段。
• left join 时，选择小表作为驱动表（放左边），大表作为被驱动表（放右边）
• inner join 时，mysql 会自动将小结果集的表选为驱动表。
• 子查询尽量不要放在被驱动表，衍生表建不了索引
• 能够直接多表关联的尽量直接关联，不用子查询

第六节 子查询优化

1、结论

在实际开发中，能够不用子查询就尽量不用。

2、实际测试

①测试子查询效果

添加索引：

create index idx_ceo on dept(ceo);

分析语句：

explain SELECT *
FROM emp a
WHERE a.id NOT IN
      (SELECT b.ceo FROM dept b WHERE b.ceo IS NOT NULL);

分析结果：

②测试关联查询效果

分析语句：

explain SELECT a.*
FROM emp a
         LEFT JOIN dept b
                   ON a.id = b.ceo;

分析结果：

③综合对比

• 从执行顺序来说：关联查询的 id(1,1) 比 子查询的 id(1,2) 要好
• 从查询的类型来说：ref 比 index 要好两个级别

第七节 排序分组优化

1、排序

①结论

• order by 子句需要配合 limit 子句才能让索引生效。
• 如果涉及多个字段的排序，那么这些字段排序的方向要一致（要升序都升序，要降序都降序），否则索引失效。

②测试基本情况

索引情况：

[1]只有 order by 的情况

分析语句：

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER  BY age;

分析结果：

[2]配合 limit 子句的情况

分析语句：

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age limit 10;

③测试排序方向的影响

删除上例索引：

drop index idx_age on emp;

创建新索引：

create index idx_age_deptId on emp(age, deptId);

分析语句：排序方向一致的情况

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age desc,deptId desc limit 10;

分析结果：

分析语句：排序方向不一致的情况

EXPLAIN  SELECT SQL_NO_CACHE * FROM emp ORDER BY age desc,deptId asc limit 10;

分析结果：

2、双路排序和单路排序

①filesort

如果 order by 指定的字段没有建立索引，此时 MySQL 就无法在内存完成排序了，而是执行 filesort——也就是操作硬盘完成排序。

执行 filesort 又分两种不同情况：

• 双路排序：
  • 特征：两批 I/O
  • 对应的 MySQL 版本：4.1 之前
  • 大致工作机制：
    • 第一次读取硬盘：读取『行指针』以及『order by 子句指定的字段』
    • 排序：对已读取的『order by 子句指定的字段』进行排序
    • 第二次读取硬盘：根据『行指针』读取 select 子句中指定的其他字段
  • I/O方式：随机 I/O
• 单路排序：
  • 特征：一批 I/O
  • 对应的 MySQL 版本：4.1 之后
  • 大致工作机制：
    • 读取硬盘：读取 select 子句指定的所有列
    • 排序：按照 order by 列在 buffer 中对它们进行排序
  • I/O方式：顺序I/O

②单路排序需要注意的问题

单路排序在内存的缓冲区中执行排序，所以需要更大的内存空间。我们管这个缓冲区叫：sort_buffer。此时需要注意：如果为了排序而取出的数据体积大于 sort_buffer，这就会导致每次只能取 sort_buffer 容量大小的数据。所以这种情况下，数据的加载和排序是分段完成的。在这个过程中，MySQL 会创建临时文件，最后再把每段数据合并到一起。

所以 sort_buffer 容量太小时会导致排序操作产生多次 I/O。单路本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

③单路排序的优化

调整下面的三个参数：

参数名称	参数含义	调整建议
sort_buffer_size	单路排序缓冲区的大小	适当增大
max_length_for_sort_data	select 子句要查询的所有字段的总宽度和该参数比较： 大于该参数：使用双路排序 小于等于该参数且排序字段不是TEXT、BLOB类型：使用单路排序	适当增大
select 子句中查询的字段		尽量减少

TIP

对 sort_buffer_size 的补充说明：

不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL5.7，InnoDB 存储引擎默认值是 1048576 字节，1MB。

对 max_length_for_sort_data 的补充说明：

max_length_for_sort_data 不能设的太高，否则数据总容量超出 sort_buffer_size 的概率就增大。明显症状是高的磁盘 I/O 活动和低的处理器使用率。建议设置在 1024-8192 字节之间。

最终目标：

• 尽量能够基于索引执行排序
• 如果确实要执行 filesort，那么尽量使用单路排序，而且尽量让单路排序只做一次 I/O。

④select * 会影响排序

在实际业务功能开发过程中，禁止在 select 子句中使用 * 号代表全部字段。如果确实需要查询全部字段，那就把全部字段都写明。其实这个时候更要注意的是：是不是真的要查全部字段。

具体从 SQL 优化的角度来说，select * 会导致我们加载很多没有创建索引的字段到内存中，增加了数据体积超过 sort_buffer_size 的风险。有可能会导致单路排序变成双路排序，性能下降。

3、GROUP BY优化

Group by 分组优化原则如下：

• group by 先排序再分组，同样遵照最左原则
• 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
• where 高于 having，能写在 where 限定的条件就不要写在 having 中了

举个例子帮助大家理解：

• 假设有 100W 条记录待筛选
• 有一个 X 条件能够过滤掉 90W 条
  • X 条件用在 where 子句：后续 GROUP BY 操作针对 10W 条数据操作
  • X 条件用在 having子句：后续 GROUP BY 操作还是针对原来的 100W 条数据操作

所以在整个 SQL 查询语句中，能够将数据过滤掉的条件在不影响查询结果的前提下都要尽早使用，尽早过滤数据，缩小要操作的数据量，让后续操作减轻负担。

关闭 ONLY_FULL_GROUP_BY 模式：

• 查看当前 SQL 模式

select @@GLOBAL.sql_mode;

• 关闭 ONLY_FULL_GROUP_BY 模式

  修改 /etc/my.cnf 配置文件，在配置文件末尾增加一行：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

• 重启 MySQL 服务
• 查看修改完成后的效果

select @@GLOBAL.sql_mode;

TIP 示例

关闭 ONLY_FULL_GROUP_BY 模式

查看当前 SQL 模式
mysql> select @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

关闭 ONLY_FULL_GROUP_BY 模式
修改 /etc/my.cnf 配置文件，在配置文件末尾增加一行：

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
重启 MySQL 服务
查看修改完成后的效果
mysql> select @@GLOBAL.sql_mode; +------------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +------------------------------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> use db_hr_sys; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> mysql> select * from emp group by deptid limit 0,10; +-------+--------+--------+------+--------+ | id | empno | name | age | deptId | +-------+--------+--------+------+--------+ | 10893 | 110893 | MJTDjl | 41 | 1 | | 523 | 100523 | VOizXo | 41 | 2 | | 7118 | 107118 | VthHLL | 39 | 3 | | 14788 | 114788 | PTasQX | 37 | 4 | | 16297 | 116297 | gLpozF | 41 | 5 | | 7597 | 107597 | ZItShh | 35 | 6 | | 817 | 100817 | mxzhmN | 31 | 7 | | 1673 | 101673 | demYgL | 34 | 8 | | 30032 | 130032 | fYDUPn | 50 | 9 | | 1169 | 101169 | wjFANm | 49 | 10 | +-------+--------+--------+------+--------+ 10 rows in set (0.53 sec)

视图

实际开发时，现在越来越多『长、难、复杂』SQL。这种 SQL 语句编写、维护较为困难。所以我们可以将这一的 SQL 语句创建为『视图』，这个视图生成了一张虚拟的表。下次再有需要时，执行这个视图即可得到相同的结果。

视图是将一段查询 SQL 封装为一个虚拟的表。 这个虚拟表只保存了 SQL 逻辑，不会保存任何查询结果。

主要作用:

• 封装复杂sql语句，提高复用性
• 逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活

常用场景:

• 共用查询结果
• 报表

语法:

创建

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name  WHERE condition  

使用

#查询
select * from view_name 
#更新
CREATE OR REPLACE VIEW view_name 
AS SELECT column_name(s) FROM table_name WHERE condition