MySQL索引

1. MySQL8环境搭建

1.1. docker安装mysql

docker run -id \
-p 3307:3306 \
-v mysql8_conf:/etc/mysql/conf.d \
-v mysql8_data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=1234 \
--name atguigu-mysql8 \
--restart=always \
mysql:latest

1.2. 测试远程连接

连接时出现如下问题:

mysql5.7之前密码加密使用的插件是mysql_native_password，mysql8修改为caching_sha2_password

解决方法： 登录你的 mysql 数据库，然后 执行这条SQL：

docker  exec -it atguigu-mysql8 /bin/bash
mysql -uroot -p1234
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234';
flush privileges;

1.3. SQL大小写规范

1.3.1. Windows和Linux的区别

Windows环境：

全部不区分大小写

Linux环境：

1、数据库名、表名、表的别名、变量名严格区分大小写；

2、列名与列的别名不区分大小写。

3、关键字、函数名称不区分大小写。

1.3.2. Linux下大小写规则设置（了解）

在MySQL 8中设置的具体步骤为：

1. 停止MySQL服务 systemctl stop mysqld

2. 删除数据目录，即删除 /var/lib/mysql 目录

3. 在MySQL配置文件my.cnf的 [mysqld] 中添加 lower_case_table_names=1

   [mysqld] 
   lower_case_table_names=1
   

4. 启动MySQL服务 systemctl start mysqld

注意：不建议在开发过程中修改此参数，将会丢失所有数据。

1.4 字符集

• utf8与utf8mb4

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。

字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷的定义了两个概念：

utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。（无法存储emoji表情）

MySQL5.7中的utf8是utf8mb3字符集

utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

MySQL8.0中的utf8是utf8mb4字符集

• mysqll默认字符集

MySQL 8版本之前，默认字符集为 latin1（ISO-8859-1） ，不支持中文，使用前必须设置字符集为utf8（utf8mb3）或utf8mb4。

从MySQL 8开始，数据库的默认字符集为 utf8mb4 ，从而避免中文乱码的问题。

SHOW VARIABLES LIKE '%char%';

1.5. sql_mode

sql_mode是一组语法校验规则。

1.5.1、宽松模式 vs 严格模式

宽松模式：

执行错误的SQL或插入不规范的数据，也会被接受，并且不报错。

严格模式：

执行错误的SQL或插入不规范的数据，会报错。MySQL5.7版本开始就将sql_mode默认值设置为了严格模式。

1.5.2、查看和设置sql_mode

• 查询sql_mode的值：

--session级别
SHOW VARIABLES LIKE 'sql_mode'; 	

• 临时设置sql_mode的值：

SET SESSION sql_mode = 'mode1,model2,...'; --当前会话生效，关闭当前会话就不生效了。可以省略SESSION关键字

• 在mysql配置文件中配置，永久生效：在宿主机上执行一下命令，创建配置文件：

vim /atguigu/mysql/mysql8/conf/my.cnf

编辑配置文件

[mysqld]
sql-mode = "mode1,model2,..."

重启mysql容器

docker restart atguigu-mysql8

1.5.3、错误开发演示

建表并插入数据：

CREATE DATABASE atguigudb;
USE atguigudb;
CREATE TABLE employee(id INT, `name` VARCHAR(16),age INT,dept INT);
INSERT INTO employee VALUES(1,'zhang3',33,101);
INSERT INTO employee VALUES(2,'li4',34,101);
INSERT INTO employee VALUES(3,'wang5',34,102);
INSERT INTO employee VALUES(4,'zhao6',34,102);
INSERT INTO employee VALUES(5,'tian7',36,102);

需求：查询每个部门年龄最大的人

-- 错误演示
SELECT `name`, dept, MAX(age) FROM employee GROUP BY dept; 

以上查询语句在 “ONLY_FULL_GROUP_BY” 模式下查询出错，因为select子句中的name列并没有出现在group by子句中，也没有出现在函数中：

c

在非 “ONLY_FULL_GROUP_BY” 模式下可以正常执行，但是得到的是错误的结果：

SET SESSION sql_mode = ''; 

• 正确的查询方式：查询应该分两个步骤

1、查询每个部门最大的年龄

2、查询人

正确的语句：

SELECT e.* 
FROM employee e
INNER JOIN (SELECT dept, MAX(age) age FROM employee GROUP BY dept) AS maxage 
ON e.dept = maxage.dept AND e.age = maxage.age;

测试完成后再将sql_mode设置回来：

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

1.5.4、sql_mode中各参数值介绍（了解）

• ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，SELECT子句中只能包含函数和 GROUP BY 中出现的字段。
• STRICT_TRANS_TABLES：
  • 对于支持事务的表(innodb存储引擎)，如果发现某个值缺失或非法，MySQL将抛出错误，语句会停止运行并回滚。
  • 对于不支持事务的表，不做限制，提高性能。
• NO_ZERO_IN_DATE：不允许日期和月份为零。（'2021-00-01', '2021-01-00'）
• NO_ZERO_DATE：MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告（0000-00-00）。
• ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则报错。如果未给出该模式，那么数据被零除时MySQL返回NULL。
• NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或不存在，那么抛出错误。不设置此值时，用默认的存储引擎替代。

2. MySQL逻辑架构

2.1. 逻辑架构总览

下面是MySQL5.7使用的经典架构图，MySQL 8中去掉了Caches&Buffers部分。

1、Connectors（客户端）

MySQL服务器之外的客户端程序，与具体的语言相关，例如Java中的JDBC，图形用户界面SQLyog等。本质上都是在TCP连接上通过MySQL协议和MySQL服务器进行通信。

2、MySQL Server（服务器）

第1层：连接层

• 客户端访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。
• 经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
• 客户端建立好连接后，在线程池中分配一个线程给当前客户端，去执行后面的流程。

第2层：服务层

（1）Management Services & Utilities： 系统管理和控制工具

（2）SQL Interface：SQL接口：

• 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT ... FROM就是调用SQL Interface
• MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口

（3）Parser：解析器：对sql进行语法和语义的解析，并创建对应的解析树。预处理器则根据MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看是否有歧义等，并生成一棵新解析树。

典型的解析树如下：

（4）Optimizer：查询优化器：

• SQL语句在语法解析后、查询前，MySQL优化程序会对我们的语句做一些优化，将查询的IO成本和CPU成本降到最低。优化的结果就是生成一个执行计划。这个执行计划表明了应该使用哪些索引执行查询，以及表之间的连接顺序是啥样，必要时将子查询转换为连接、表达式简化等等。

（5）Caches & Buffers： 查询缓存组件：

• MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、查询优化和执行的整个过程了，直接将结果反馈给客户端。
• 问：大多数情况查询缓存就是个鸡肋，为什么呢？
  • 只有相同的SQL语句才会命中查询缓存。两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。
  • 在两条查询之间 有 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句也会导致缓存失效
  • 因此 MySQL的查询缓存命中率不高。所以在MySQL 8之后就抛弃了这个功能。

第3层：引擎层

存储引擎层（ Storage Engines），负责MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，管理的表有不同的存储结构，采用的存取算法也不同，这样我们可以根据自己的实际需要进行选取。例如MyISAM引擎和InnoDB引擎。

3、存储层

所有的数据、数据库、表的定义、表的每一行的内容、索引，都是存在文件系统 上，以文件的方式存在，并完成与存储引擎的交互。

2.2. 查询流程说明

​

mysql的查询流程大致是：SQL语句 → 查询缓存（mysql8已废弃） → 解析器 → 优化器 → 执行器  

首先，MySQL客户端通过协议与MySQL服务器建连接，通过SQL接口发送SQL语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析。也就是说，在解析查询之前，服务器会先访问查询缓存，如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。

接下来是解析过程，MySQL解析器通过关键字将SQL语句进行解析，并生成一棵对应的解析树，解析器使用MySQL语法规则验证和解析SQL语句。例如，它将验证是否使用了错误的关键字，或者使用关键字的顺序是否正确，引号能否前后匹配等；预处理器则根据MySQL规则进一步检查解析树是否合法，例如，这里将检查数据表和数据列是否存在，还会解析名字和别名，看是否有歧义等，并生成一棵新解析树，新解析树可能和旧解析树结构一致。

然后是优化过程，MySQL优化程序会对我们的语句做一些优化，将查询的IO成本和CPU成本降到最低。优化的结果就是生成一个执行计划。这个执行计划表明了应该使用哪些索引执行查询，以及表之间的连接顺序是啥样，必要时将子查询转换为连接、表达式简化等等。我们可以使用EXPLAIN语句来查看某个语句的执行计划。

最后，进入执行阶段。完成查询优化后，查询执行引擎会按照生成的执行计划调用存储引擎提供的接口执行SQL查询并将结果返回给客户端。在MySQL8以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存，再返回给客户端。

3. MySQL存储引擎

3.1、查看存储引擎

存储引擎 真正的 负责了 MySQL 中 数据的 存储 和 提取 。

不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

# 查看mysql提供的所有存储引擎
show engines;
# 查看当前默认的存储引擎
show variables like '%storage_engine%';

下面的结果表示MySQL中默认使用的存储引擎是InnoDB，支持事务，行锁，外键，支持分布式事务(XA)，支持保存点(回滚)。

3.2、设置存储引擎

方法1：

设置默认存储引擎：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

方法2：

或者修改 my.cnf 文件：vim /etc/my.cnf
在[mysqld]节点下新增一行：default-storage-engine=MyISAM
重启MySQL：systemctl restart mysqld

方法3：

我们可以为 不同的表设置不同的存储引擎

CREATE TABLE 表名( 建表语句 ) ENGINE = 存储引擎名称;
ALTER TABLE 表名 ENGINE = 存储引擎名称;

-- 例如：
CREATE TABLE student(id INT, `name` VARCHAR(16),age INT,dept INT) ENGINE = MyISAM;

3.3、各种引擎介绍

1. InnoDB存储引擎

• InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。

• 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。

• InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。

2. MyISAM存储引擎

• MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

• 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用。

• MyISAM只缓存索引，不缓存真实数据。

3. Archive引擎

• Archive档案存储引擎只支持INSERT和SELECT操作。
• Archive表适合日志和数据采集（档案）类应用。
• 根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

4. Blackhole引擎（黑洞）

• Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。
• 但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

5. CSV引擎

• CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
• CSV引擎可以作为一种数据交换的机制，非常有用。
• CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

6. Memory引擎

• 如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。
• Memory表至少比MyISAM表要快一个数量级。

7. Federated引擎

• Federated引擎是访问其他MySQL服务器的一个代理（跨库关联查询），尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

3.4、MyISAM和InnoDB的区别

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。支持聚簇索引
关注点	并发查询，节省资源、消耗少、简单业务	并发写、事务、更大更复杂的资源操作
默认使用	N	Y

4. SQL练习

4.1、创建测试数据

CREATE TABLE `t_dept` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
);
 
CREATE TABLE `t_emp` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT DEFAULT NULL,
 `deptId` INT DEFAULT NULL,
`empno` INT NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
);

INSERT INTO t_dept(id,deptName,address) VALUES(1,'华山','华山');
INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');
INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');
INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');
INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);

4.2、常见七种JOIN查询

需求1：查询所有有部门的员工信息以及他所在的部门信息

在A、和B中都存在的数据 => 查询A、B两表交集

SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptid = b.id;

需求2：查询所有用户，并显示其部门信息（如果员工没有所在部门，也会被列出） => 查询A的全集

SELECT * 
FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id;

需求3：列出所有部门，并显示其部门的员工信息（如果部门没有员工，也会被列出）=> 查询B的全集

SELECT * 
FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id;

需求4：查询没有加入任何部门的员工（先查询所有员工，再过滤掉包含部门的数据） => 查询A且不包含B

SELECT * FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id 
WHERE b.id IS NULL;

需求5：查询没有任何员工的部门 => 查询B且不包含A

SELECT * FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id 
WHERE a.id IS NULL;

需求6：查询所有员工和所有部门 => AB全有

MySQL FULL JOIN 的实现：因为MySQL不支持FULL JOIN，下面是替代方法

LEFT JOIN + UNION(合并并去重) + RIGHT JOIN

注意：

• UNION和UNION ALL要求字段数量和顺序都一致
• 如果确定两表结果不会重复，则使用UNION ALL提升效率

SELECT * 
FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id 
UNION 
SELECT * 
FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id;

需求7：查询没有加入任何部门的员工，以及查询出部门下没有任何员工的部门 => A的独有+B的独有

SELECT * 
FROM t_emp a 
LEFT JOIN t_dept b ON a.deptid = b.id 
WHERE b.id IS NULL 

UNION ALL

SELECT * 
FROM t_emp a 
RIGHT JOIN t_dept b ON a.deptid = b.id 
WHERE a.id IS NULL;

4.3、扩展掌门人

4.3.1、增加掌门人字段

ALTER TABLE t_dept ADD CEO INT(11);
UPDATE t_dept SET CEO=2 WHERE id=1;
UPDATE t_dept SET CEO=4 WHERE id=2;
UPDATE t_dept SET CEO=6 WHERE id=3;
UPDATE t_dept SET CEO=8 WHERE id=4;
UPDATE t_dept SET CEO=9 WHERE id=5;

4.3.2、练习

需求1：求各个门派对应的掌门人

SELECT b.deptname, a.name FROM t_dept b LEFT JOIN t_emp a ON b.ceo = a.id;

需求2：求所有掌门人的平均年龄

SELECT AVG(a.age) FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo;

需求3：求所有人物对应的掌门名称（4种写法分析）

• 三表左连接方式

-- 员工表(t_emp)、部门表(t_dept)、ceo(t_emp)表 关联查询
SELECT emp.name, ceo.name AS ceoname 
FROM t_emp emp
LEFT JOIN t_dept dept ON emp.deptid = dept.id 
LEFT JOIN t_emp ceo ON dept.ceo = ceo.id;

• 子查询方式

SELECT 
emp.name, 
(SELECT ceo.name FROM t_emp ceo WHERE ceo.id = dept.ceo) AS ceoname
FROM t_emp emp
LEFT JOIN t_dept dept ON emp.deptid = dept.id

• 临时表连接方式1

SELECT emp_with_ceo_id.name, emp.name AS ceoname FROM 
-- 查询所有员工及对应的ceo的id
( 
SELECT emp.name, dept.ceo 
FROM t_emp emp 
LEFT JOIN t_dept dept ON emp.deptid = dept.id 
) emp_with_ceo_id
LEFT JOIN t_emp emp ON emp_with_ceo_id.ceo = emp.id;

• 临时表连接方式2

SELECT emp.name, ceo.ceoname FROM t_emp emp LEFT JOIN
-- 查询并创建临时表ceo：包含ceo的部门id和ceo的name
( 
SELECT emp.deptId AS deptId, emp.name AS ceoname 
FROM t_emp emp 
INNER JOIN t_dept dept ON emp.id = dept.ceo 
) ceo
ON emp.deptId = ceo.deptId;

5. MySQL索引

5.1、索引简介

5.1.1、什么是索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”。这些数据结构以某种方式指向数据， 可以在这些数据结构的基础上实现高级查找算法 。

5.1.2、索引的优缺点

优势：

1、提高数据检索的效率，降低数据库的IO成本。

2、使用索引列进行排序或分组，降低了CPU的消耗。

3、加速表和表之间的连接，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

劣势：

1、创建索引和维护索引都是需要消耗一定的时间，这种时间会随着数据量的增加而增加；

2、索引需要占一定的物理空间，除了数据表存储数据占用空间，每一条索引都会占用一定的空间，所以索引创建的越多空间占用的就会越大;

5.1.3、索引分类

• 从功能逻辑上划分，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
• 按照作用字段个数划分，索引可以分为单列索引和联合索引。
• 按照物理实现方式划分 ，索引可以分为 2 种，分别是聚簇索引和非聚簇索引。

5.2、树

5.2.1、二叉树

（1）二叉树

树有很多种，每个节点最多只能有两个子节点的一种形式称为二叉树。二叉树的子节点分为左节点和右节点。

（2）二叉搜索树BST

BST(Binary Sort(Search) Tree)：对于二叉排序树的任何一个非叶子节点，要求左子节点的值比当前节点的值小，右子节点的值比当前节点的值大。

特别说明：如果有相同的值，可以将该节点放在左子节点或右子节点。

BST的生成演示：https://www.cs.usfca.edu/~galles/visualization/BST.html

（3）一种可能的索引方式示例

• 一张数据表，共有两列七条记录，最左边的是数据记录的物理地址：

  假设我们要查询Col2=89的数据 select * from Table where Col2 = 89，需要从第一行开始读取，需要7次磁盘IO操作，这样磁盘读取是很耗时的。

• 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找获取到相应数据，从而快速的检索出符合条件的记录：

（4）BST的问题

• 上面演示的实际上是一个特殊的二叉搜索树，叫做平衡 二叉搜索树， 如果二叉树不平衡，例如左子树全部为空，从形式上看，更像一个单链表，不能发挥BST的优势。
• 解决方案：平衡二叉树(AVL)

（5）平衡二叉树（AVL）

AVL树全称G.M. Adelson-Velsky和E.M. Landis，这是两个人的人名。

平衡二叉树也叫平衡二叉搜索树（Self-balancing binary search tree）又被称为AVL树， 可以保证查询效率较高。

具有以下特点：

• 它是一棵空树或它的左右两个子树的高度差的绝对值不超过1
• 并且左右两个子树都是一棵平衡二叉树。

AVL的生成演示：https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

（6）AVL的问题

众所周知，IO操作的效率很低，在大量数据存储中，查询时我们不能一下子将所有数据加载到内存中，只能逐节点加载（一个节点一次IO）。如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，进而导致效率低下。

为了提高查询效率，就需要 减少磁盘IO数 。为了减少磁盘IO的次数，就需要尽量降低树的高度 ，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。针对同样的数据，如果我们把二叉树改成 三叉树：

上面的例子中，我们将二叉树变成了三叉树，降低了树的高度。如果能够在一个节点中存放更多的数据，我们还可以进一步减少节点的数量，从而进一步降低树的高度。这就是多叉树。

5.2.2、多叉树

（1）B Tree索引

B Tree（B树），全名是多路平衡查找树（每个节点可以有多个子节点）。一页存储多个键值。

案例：主键字段使用B树的索引结构。

【初始化介绍】
一颗b树，一个磁盘块就是mysql中的一页数据（MySQL的一页大小默认是16KB）；

每个磁盘块包含：键值（紫色所示），数据项（黄色所示），和指向其他磁盘块的指针（蓝色所示）

如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

【数据项29的查找过程】
（1）首先会把磁盘块1由磁盘加载到内存，此时发生一次磁盘IO；

（2）在内存中针对多个键值使用二分查找算法（内存时间因为非常短，可以忽略不计），确定29在17和35之间，锁定磁盘块1的P2指针；

（3）通过磁盘块1的P2指针，将磁盘块3由磁盘加载到内存，发生第二次IO；在内存中二分查找，确定29在26和30之间，锁定磁盘块3的P2指针

（4）通过磁盘块3的P2指针，加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的B树可以组织上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

（2）B+Tree索引

​ 在B树中，非叶子磁盘块是要存储data数据的；如果行数据较大，会导致非叶子磁盘块存储的键值个数降低，树的高数就会变高，IO的次数也就变高。

​ B+树就解决了这个问题。

​ 所有的data数据都存储到了叶子节点，非叶子节点只会存储指向下级的指针。

​ 叶子节点使用双向链表进行关联，我们只需要大致查询到存储在哪个叶子节点，然后进行有序遍历即可。

innodb类型的表数据，其实就是在主键字段建立了这样一颗B+树。

（3）区别

（1）B树的关键字（键值）和 data 是放在一起的；

​ B+树的非叶子节点中只有键值和指向下一个磁盘块的地址，data只放在叶子节点中。

（2）B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可找到data记录；

​ 而 B+ 树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，查询效率更稳定。

5.3、聚簇/非聚簇索引

B+树索引又可以细分为：

（1）聚簇索引（也称聚集索引，clustered index）

（2）非聚簇索引（也称辅助索引或二级索引，secondary index，non-clustered index）。

聚簇/非聚簇索引 并不是 一种单独的索引类型，而是指叶子磁盘块各个数据的data中存放的到底是啥。
聚集索引的叶子节点data存放着一整行的数据。
非聚集索引的叶子节点data存放的是该键值所对应的主键值。

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构（.ibd文件），聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据。

InnoDB要求表必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个非空且唯一标识数据记录的列作为主键。
如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

由于聚簇索引存储的是完整的行数据，所以每个表只有一个聚簇索引（并且是在主键上建立的）。

在innodb表的非主键字段上建立的索引是非聚簇索引，叶子节点存储的并非是完整行数据，而是主键值。

所以如果通过非聚簇索引查询行数据总是要回表的，也就是从非聚簇索引上找到主键，再根据主键从聚簇索引上查询行数据。

在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引，索引和数据是分开存储的。

MyISAM存储引擎将记录按照插入顺序单独存储在一个.MYD文件中，称之为「数据文件」。

MyISAM存储引擎会把索引信息另外存储到一个.MYI文件中，称之为「索引文件」。

MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是「主键值 ＋ 数据记录地址」的组合，索引MyISAM引擎表查询数据时，总是要回表。

5.4、回表

非聚集索引如何获取行数据？回表。

每次查非聚集索引得到主键值，再通过主键再次去聚集索引查询完整行数据。

这里我们再引申出一个概念那就是回表，我们上图所描述的流程就是回表。回表的原因是我们需要获取的是整行或者是包含非索引字段的数据，因非聚集索引没有该字段所以需要回表查询。

问题：
为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？
如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。(相当于我要一片树叶,你把一整棵树砍了下来)

问题:为什么我们要尽量避免select * 操作？ 尽量避免回表。

例如我们查询SELECT * FROM USER WHERE name LIKE '张%'，但是我们其实想要的只是名字的集合而已，那么我们就可以改造成SELECT name FROM USER WHERE name LIKE '张%',前者会回表查询而后者不会，这应就减少了数据查询的时间同时也减少了数据库的压力。

问题：索引是不是创建的越多越好呢？并不是。

因为索引即数据，创建过多的索引就会导致数据量的增加。

在更新数据的同时，索引也是需要重新维护的。

5.5、覆盖索引

5.5.1. 什么是覆盖索引

通过读取索引就可以得到想要的数据，那就不需要回表读取了。一个索引中包含了满足查询结果的数据就叫做覆盖索引。

简单说就是：select 到 from 之间查询的列 <= 使用的索引列 + 主键

好处：避免Innodb表进行索引的二次查询（回表）

如果建立一个组合索引，让覆盖索引发生的几率变大，回表的几率变小，这样是不是更好呢？

复合索引字段越多，维护的代价就越大。

5.5.2. 案例演示

#age和empno建立了组合索引
#从非聚簇索引直接返回数据，不需要回表
EXPLAIN SELECT id,age,empno FROM `emp` 

type=index：只扫描索引树。直接从索引树上返回数据。

5.6 索引的使用场景总结

（1）哪些情况需要创建索引：

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题， 组合索引性价比更高
• 排序字段、分组字段建立索引

（2）哪些情况不要创建索引：

• 表记录太少

• 经常增删改的表或者字段。

  Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

• Where条件里用不到的字段不创建索引

• 过滤性不好的字段不适合建索引

5.7 MyISAM和InnoDB索引结构对比

MyISAM允许数据库表没有主键，因此允许没有主键索引；
MyISAM主键索引为非聚簇索引，叶子节点的data域存放的是数据记录的地址。
MyISAM非主键索引，叶子节点的data域存放的是数据记录的地址。
MyISAM中主键索引与非主键索引的唯一区别在于，主键索引要求key是唯一的，非主键索引的key可以重复。

6. 数据库优化方案

问题：

哪些方法可以进行数据库调优？

解决方案：

• 索引失效，没有充分利用到索引：索引建立

• 关联查询太多JOIN（设计缺陷或不得已的需求）：SQL优化

• 数据过多500W，2GB：分库分表

• 服务器调优及各个参数设置（缓冲、线程数等）：调整my.cnf

join_buffer_size这个参数定义了在执行JOIN操作时使用的缓冲区大小,适当调整此参数可以显著提高JOIN操作的性能,尤其是在处理大量数据时。
max_connections这个参数定义了MySQL服务器允许的最大并发连接数，根据服务器的负载和硬件配置适当调整此参数，可以避免因连接数不足或过多而导致的性能问题。

7. 索引实战

7.1. explain工具

MySQL 的 EXPLAIN 是一个用于查询优化的关键字。它用于分析和评估查询语句的执行计划，帮助开发者理解查询语句的性能问题以及优化查询的方式。但是部分统计信息是估算的，并非精确值 .

7.1.1. 用法

用法： explain + SQL语句

数据准备：

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));

# 以下新增sql多执行几次，以便演示
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)));

演示：explain select * from t1, t2, t3 where t1.id=t2.id and t2.id=t3.id;

7.1.2. 各字段解释

（1）id查询序列号*

select查询的序列号，包含一组数字，表示查询中 执行select子句 或 操作表 的 顺序。

三种情况：

1. id相同，执行顺序由上至下。
2. id不同，id值越大优先级越高，越先被执行；
3. id为null最后执行

explain select * from t1 where t1.content=(select t2.content from t2 where t2.content=(select t3.content from t3 where t3.content=''));

（2）select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等

查询类型	描述
SIMPLE	简单的 SELECT (没有 使用UNION或者 子查询（PS：单表查询）) EXPLAIN SELECT * FROM t1
PRIMARY	最外层的SELECT查询。
SUBQUERY	在SELECT或WHERE列表中包含了子查询。
DERIVED	在from 查询语句中的子查询。需要临时表。 EXPLAIN select * from t1, (select 2) as v;
DEPENDENT SUBQUERY	第一个查询是子查询，依赖于外部查询.
UNION	UNION中的第二个SELECT语句。
UNION RESULT	结果集是通过union 而来的。

（3）table*

显示这一行的数据是关于哪表的。

（4）partitions

代表分区表中的命中情况，非分区表，该项为null

（5）type*

type是指访问类型，是较为重要的一个指标：

常见值，结果值从最好到最坏依次是： const > eq_ref > ref > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

const：一般针对主键的等值查询，最多只返回一行数据， 查询速度非常快。例如：explain select * from t1 where t1.id=1

eq_ref：多表连接中使用primary key（唯一索引）或者 unique key（唯一索引）作为关联条件。例如：EXPLAIN SELECT * FROM t1,t2 WHERE t1.id=t2.id

ref：通过普通二级索引列与常量进行等值匹配时

CREATE INDEX idx_content ON t1(content); 
EXPLAIN SELECT * FROM t1 WHERE t1.content='t1_962';

range：使用索引字段进行范围查询。

#需要先向t1表添加大量数据，起码超过10条
EXPLAIN SELECT * FROM t1 WHERE t1.id<10

index：只扫描索引树。直接从索引树上返回数据。

CREATE INDEX idx_content ON t1(content); 
EXPLAIN SELECT content FROM t1

all：全表扫描（最差情况）。

CREATE INDEX idx_content ON t1(content); 
EXPLAIN SELECT * FROM t2   --all
#再试试 EXPLAIN SELECT * FROM t1   --为什么是index？

（6）possible_keys

显示当前查询可能用到的索引，一个或多个。但不一定被查询实际使用。

（7）key*

当前查询实际使用的索引。如果为NULL则没有使用索引。

（8）key_len*

表示索引中使用的字节数，通过这个值可以算出具体使用了索引中的哪些列（否充分的利用上了索引）。

--创建age和name的组合索引
CREATE INDEX idx_emp_name ON t_emp(age,NAME);

EXPLAIN SELECT * FROM t_emp WHERE age = 30 AND NAME = 'ab'
EXPLAIN SELECT * FROM t_emp WHERE age = 30 

68 = 20*3 + 2 + 1 + 4 + 1

索引长度计算：

1. 字段的类型不同

   数值类型：tinyint=1；smallint=2；int=4；bigint=8

   时间类型：date=3；timestamp=4；datetime=8

   字符串：char(n)=n；varchar(n)=n

2. 如果是varchar或者char这种字符串字段，视字符集要乘不同的值。

   比如：utf8mb4要乘4，utf-8mb3要乘 3，GBK要乘2

3. varchar要额外加2个字节

4. 允许为NULL的字段额外加1个字节。

   索引字段最好定义成不允许NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。

key_len不包含order by/group by使用到的索引列。

（9）ref

显示与key中的索引进行比较的列或常量。

• const： 与索引列进行等值比较的东西是啥，const表示一个常数

EXPLAIN SELECT * FROM t4 WHERE content1 = 'a';

• ref=atguigudb.t1.id 关联查询时出现，t2表和t1表的哪一列进行关联

EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;

（10）rows*

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

MySQL认为它执行查询时实际从索引树中查找到的行数。值越小越好。

-- 如果是全表扫描，rows的值就是表中数据的估计行数
EXPLAIN SELECT * FROM t_emp WHERE empno = '100001';

-- 如果是使用索引查询，rows的值就是预计扫描索引记录行数
EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;

（11）filtered

最后查询出来的数据占所有服务器端（server）检查行数（rows）的百分比。值越大越好。

（12）extra*

包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息来理解MySQL到底将如何执行当前的查询语句。MySQL提供的额外信息有好几十个，这里只挑比较重要的介绍。

• Using where：使用了where，但在where上有字段没有创建索引。也可以理解为如果数据从引擎层被返回到server层进行过滤，那么就是Using where。

EXPLAIN SELECT * FROM t_emp WHERE `name` = '风清扬';

• Using filesort：

如果出现了Using filesort 说明排序没有使用上索引，如下所示：

EXPLAIN SELECT * FROM t1 ORDER BY id;

如果排序操作无法使用到索引，只能在内存中（记录较少时）或者磁盘中（记录较多时）进行排序（filesort），如下所示：

EXPLAIN SELECT * FROM t1 ORDER BY content;

• Using index：使用了覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表

 EXPLAIN SELECT id, content1 FROM t4;

• Using index condition：叫作 Index Condition Pushdown Optimization （索引下推优化）
  • 如果没有索引下推（ICP），那么MySQL在存储引擎层找到满足content1 > 'z'条件的第一条二级索引记录。主键值进行回表，返回完整的记录给server层，server层再判断其他的搜索条件是否成立。如果成立则保留该记录，否则跳过该记录，然后向存储引擎层要下一条记录。
  • 如果使用了索引下推（ICP），那么MySQL在存储引擎层找到满足content1 > 'z'条件的第一条二级索引记录。不着急执行回表，而是在这条记录上先判断一下所有关于idx_content1索引中包含的条件是否成立，也就是content1 > 'z' AND content1 LIKE '%a'是否成立。如果这些条件不成立，则直接跳过该二级索引记录，去找下一条二级索引记录；如果这些条件成立，则执行回表操作，返回完整的记录给server层。

-- content1列上有索引idx_content1
EXPLAIN SELECT * FROM t4 WHERE content1 > 'z' AND content1 LIKE '%a';

注意：如果这里的查询条件只有content1 > 'z'，那么找到满足条件的索引后也会进行一次索引下推的操作，判断content1 > 'z'是否成立（这是源码中为了编程方便做的冗余判断）

• Using join buffer：在连接查询时，当被驱动表（t2）不能有效的利用索引时，MySQL会提前申请一块内存空间（join buffer）存储驱动表的数据，来加快查询速度

EXPLAIN  SELECT * FROM t1, t2 WHERE t1.content = t2.content;
# 创建了索引就不会出现Using join buffer！
create index idx_content on t2(content);

下面这个例子就是被驱动表使用了索引，此时Extra中就没有Using join buffer了：

EXPLAIN SELECT * FROM t_emp, t_dept WHERE t_dept.id = t_emp.deptId;

)

7.2. 数据准备

执行 资料/data.sql ，用于创建两张表dept和emp，并往员工表里插入50W数据，部门表中插入1W条数据。

7.3. 单表查询优化

在查询语句的where部分涉及到的列表建立单键索引，如果有多个字段则建立组合索引。

如下操作会导致索引失效：

1. 在索引列上有其他操作会导致索引失效，例如：计算、函数、类型转换等。
2. like以通配符开头（'%abc'）会导致索引失效
3. 不等于（!=或者<>）会导致索引失效
4. is not null 导致索引失效，但是is null是可以使用索引的
5. 字符串不加单引号会导致索引失效
6. 使用组合索引时，违背组合索引原则会导致索引失效

一般性建议：

1. 对于单键索引，尽量选择针对当前query过滤性更好的索引
3. 在选择组合索引的时候，尽量选择where中更多字段的索引
4. 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面

7.4. 组合索引原则

什么是组合索引？由多个列构成的索引。

最左前缀法则：查询从索引的最左前列开始并且不跳过索引中的列。一旦跳过某个字段，索引后面的字段都无法被使用。

组合索引使用原则：

1. 尽可能全值匹配，充分利用索引
2. 符合最左原则：不跳过索引中的列。
3. 如果where条件中是OR关系，加索引不起作用
4. 范围条件右边的列 不走索引
5. 过滤性最好的字段在组合索引字段顺序中位置越靠前越好。

案例1：全值匹配，索引被充分利用

#删除之前创建的索引
CALL proc_drop_index("mydb","emp");

# 没有索引的情况下，执行时间在我本地是不到0.1秒（emp虽然有50万数据，也算不多）
# explain
SELECT  * FROM emp WHERE age=30 and deptId=1 and name='abc';

#创建组合索引之后，再来查询，执行时间大概0.001秒
create index idx_age_deptId_name on emp(age, deptId, name);

案例2：最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

-- 组合索引字段的顺序：age, deptId, name
-- 保留前两个字段（使用到了组合索引中的前两列） key_len=10
EXPLAIN SELECT  * FROM emp WHERE age=30 AND deptId=1; 

-- 保留后两个字段，不走索引（跳过了）
EXPLAIN SELECT  * FROM emp WHERE  deptId=1 AND NAME = 'abc'; 

-- 使用age和name，跳过了组合索引的中间一个，key_len=5; 只是用了组合索引的第一列
EXPLAIN SELECT  * FROM emp WHERE age=30  AND NAME='abc';

案例3：OR关联

# and关联，走索引
EXPLAIN SELECT  * FROM emp WHERE age=30 AND deptId=1;
# or关联，不走索引
EXPLAIN SELECT  * FROM emp WHERE age=30 OR deptId=1;

案例4：范围条件右边的列失效

# 组合索引中的三个列都被利用到
EXPLAIN SELECT  * FROM emp WHERE age=30 AND deptId=1 AND NAME='abc'; 
# age和deptId走索引，name没有被利用索引
EXPLAIN SELECT  * FROM emp WHERE age=30 AND deptId>10000 AND NAME='abc'; 

假设index(a,b,c)

7.5. 关联查询优化

7.5.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 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)));

此时class有16条数据，book有20条数据。

7.5.2 驱动表和被驱动表

（1）什么是驱动表? 什么是被驱动表？

驱动表在SQL语句执行的过程中，总是先读取。而被驱动表在SQL语句执行的过程中，总是后读取。

在驱动表数据读取后，放入到join_buffer后，再去读取被驱动表中的数据，来和驱动表中的数据进行匹配。如果匹配上则作为结果集返回，否则丢弃。

（2）如何区分驱动表和被驱动表

我们对于一个已有的SQL语句，我们应该怎么判断这个SQL语句中哪个表示驱动表？哪个表示被驱动表呢？

可以使用explain命令查看一下SQL语句的执行计划。在输出的执行计划中，排在第一行的表是驱动表，排在第二行的表是被驱动表。

（3）join中如何写驱动表和被驱动表

left join的左表示驱动表，右表示被驱动表。

right join的右表示驱动表，左表示被驱动表。

对于inner join而言，MySQL会选择小表作为驱动表，大表作为被驱动表，前提是关联字段都有索引。

7.5.3 关联查询优化建议

1. 保证被驱动表的join字段已经被索引
2. left/right join 时，选择小表作为驱动表，大表作为被驱动表。
3. inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
4. 子查询尽量不要放在被驱动表，有可能使用不到索引。
5. 能够直接多表关联的尽量直接关联，不用子查询。

7.5.4 案例

explain分析一下几个sql：

#book和class都没有索引。
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;
# book表20条数据，class表16条数据
EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

案例1：给book.card创建索引：

ALTER TABLE `book` ADD INDEX idx_card ( `card`);

然后explain分析：

案例2：删除旧索引，添加新索引：

drop index idx_card on book;
ALTER TABLE class ADD INDEX index_class_card (card);

再次explain分析：

思想：给class表的card字段建立的索引，左关联中左表是驱动表，驱动表不需要走索引，但是为什么type=index？
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card; 

案例3：同时给两张表的card字段添加索引：（class(card)索引已有：index_class_card，只需给book（card）添加索引）

ALTER TABLE `book` ADD INDEX idx_card ( `card`);

最后explain分析：

7.6. 子查询优化

执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。

查询完毕后，再撤销这些临时表 。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

MySQL通常会尝试重写子查询为连接查询（JOIN），以提高查询效率。这种优化称为“子查询展开”或“子查询优化”。如果子查询可以转换为JOIN，MySQL会自动进行优化。

但是，这种转换是依赖于MySQL的优化器进行的，并不保证总是发生。

7.7. 排序优化

7.7.1 两种排序方式

在 MySQL 中，支持两种排序方式，分别是 FileSort 排序和 Index 排序。

（1）使用到索引：就是Index 排序，索引本身就可以保证数据的有序性，无需再进行排序，效率更更高。

（2）没有使用到索引：就是FileSort 排序，这种方式一般在内存中进行排序，占用 CPU 较多。如果待排序的结果较大，会产生临时文件， I/O 到磁盘进行排序的情况，效率低。

7.7.2 案例演示

ORDER BY 排序字段应建立索引，避免使用FileSort方式排序。

# 查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序
#EXPLAIN出现Using filesort，并且type=ALL
explain SELECT * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME;

（1）尽量让where和order by使用上索引。现在过滤条件使用了两个字段（age，empno），排序使用了name，我们可以考虑建一个三个字段的组合索引。

CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);

再次explain测试： 我们发现using filesort 依然存在，所以name （ORDER BY name）并没有用到索引。什么原因？最左原则。

（2）删掉这个索引。为了去掉Using filesort，只在age和name建立索引。

DROP INDEX idx_age_empno_name ON emp
CREATE INDEX idx_age_name ON emp(age,NAME);

也就是说empno 和name这个两个字段只能二选其一。
这样我们优化掉了 using filesort（并且执行时间也有变少了，大概0.03秒）。

（3）选择创建age和empno的组合索引，会速度会怎样呢？

为什么具有 Using filesort的 sql 运行速度 却快了很多？

因为所有的排序都是在条件过滤之后才执行的；
如果条件过滤了大部分数据的话，少部分数据进行using filesort排序其实并不是很消耗性能，即使优化掉了using filesort，但实际提升性能很有限。 
如果 empno<101000 这个条件如果没有用到索引的话，就需要对更多的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论：

当where条件字段和 order by （或group by）的字段出现二选一时
优先观察where条件字段的过滤数量，如果过滤的数据足够多（需要排序的数据不多了），优先把索引放在过滤字段上。

7.7.3 了解FileSort 算法

如果 order by 字段上没有使用索引，就要使用FileSort 排序。

FileSort 有两种方式：双路排序和单路排序。

（1）双路排序

MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。

（1）读取行指针/主键和order by排序列，在内存中按照排序列的值进行排序----第一次IO

（2）然后扫描已经排序好的列表，按照列表中的指针/主键，再去读取对应的数据----第二次IO

（2）单路排序

（1）从磁盘一次性读取查询需要的所有列数据，加载到sort_buffer内存中----就这一次IO磁盘扫描

（2）按照order by列在内存中对它们进行排序

单路排序的优点：少了一次磁盘IO。

单路排序的缺点：

（1）一次性从磁盘上读取所需的完整数据到内存中，会占用更多的内存空间

（2）如果需要读取的数据量超出了sort_buffer_size的容量，则导致每次只能取sort_buffer_size大小的数据，导致更多次的磁盘IO读取，进行多次排序，每次排序都会创建tmp临时文件，最后进行多路合并。本来想节省一次I/O操作，反而导致了更多的I/O操作。

优化策略：

1、尝试提高sort_buffer_size的大小（默认1M），通常在1M-8M之间调整，或者设置为总内存的1%~2%

2、永远不要select *，只查询需要的字段，减少加载到sort_buffer的数据量。

7.8 分组优化

（1）分组字段加索引

（2）order by null 不排序

mysql5.7版本 group by默认会按照分组字段来排序，到了8.0版本，就只是分组，没有排序了。
SELECT deptId,COUNT(1) FROM `emp` GROUP BY deptId

#如果是mysql8之前，group by时是要排序的，避免排序可以提高分组效率；
group by goods_id order by null

explain 时 Extra出现Using temporary，表示分组时使用到了临时表，临时表又分为内存临时表和磁盘临时表。

首先应该做的就是在分组字段建立索引，如果依然无法去除Using temporary，则按照如下原则：

（1）尽量使用内存临时表

​ Using temporary默认使用的就是内存临时表。但是如果待分组的数据量实在过大，大到内存临时表都不够用了，这时就转向使用磁盘临时表。mysql 中 tmp_table_size 用于设置内存临时表的大小，默认是 16M，可以调整为512MB或1GB。

（2）SQL_BIG_RESULT

​ sql中加入sql_big_result关键字，放弃使用内存临时表，直接使用磁盘临时表。因为内存临时表转向磁盘磁盘临时表这个过程很耗时。

select sql_big_result goods_id, count(*) as num from order_info group by goods_id

8. View视图

8.1、是什么

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

8.2、作用

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

8.3、适用场景

• 共用查询结果
• 报表

8.4、语法

8.4.1、创建

-- 语法
CREATE VIEW view_name 
AS SELECT column_name(s) FROM table_name WHERE condition;

-- 例如：求所有人物对应的掌门名称
CREATE VIEW v_ceo AS
SELECT emp.name, ceo.name AS ceoname 
FROM t_emp emp
LEFT JOIN t_dept dept ON emp.deptid = dept.id 
LEFT JOIN t_emp ceo ON dept.ceo = ceo.id;

8.4.2、使用

查询

-- 语法
SELECT * FROM view_name; 

-- 例如：
SELECT * FROM v_ceo; 

更新

-- 语法
CREATE OR REPLACE VIEW view_name 
AS SELECT column_name(s) FROM table_name WHERE condition

-- 建议直接删除重新创建

删除

DROP VIEW view_name;

-- 例如：
DROP VIEW v_ceo;