Mysql高级

尚硅谷_JavaEE_MySQL8高级

第01章 Linux下MySQL的安装与使用

1、版本说明

MySQL Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。

MySQL Enterprise Edition 企业版本，需付费，不能在线下载，可以试用30天。提供了更多的功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。

MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个Server。需要在社区版或企业版的基础上使用。

MySQL Cluster CGE 高级集群版，需付费。

我们使用rpm离线安装的方式，在CentOS7操作系统上，安装MySQL8社区版。

2、下载MySQL

2.1、官网

https://www.mysql.com

2.2、下载页面

https://dev.mysql.com/downloads/mysql/

下载MySQL 8.0.xx

这里没有CentOS7系统的版本，所以选择与之对应的Red Hat Enterprise Linux 7

点击Download下载RPM Bundle全量包，包括了所有组件：

点击：No thanks, just start my download.

2.3、安装包

mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

3、检查安装环境

3.1、卸载系统自带的mariaDB

#CentOS7中默认安装了mariaDB
#检查是否安装，-i 表示忽略大小写，如果显示完整的rpm的名字，则表示已安装
rpm -qa|grep -i mariaDB
#卸载， --nodeps 表示排除依赖，否则如果别的软件依赖mysql，则无法删除
rpm -e --nodeps mariadb-libs

3.2、卸载已安装的MySQL

需要的时候参考

3.2.1、卸载MySQL5.7

1.停止MySQL服务

systemctl stop mysqld

2.查看当前MySQL安装状况

rpm -qa|grep -i mysql

3.卸载已安装程序

#卸载上述命令查询出的所有已安装程序
rpm -e --nodeps mysql57-community-release
rpm -e --nodeps mysql-community-server
rpm -e --nodeps mysql-community-common
rpm -e --nodeps mysql-community-libs
rpm -e --nodeps mysql-community-client

4.删除残留文件

查找相关文件

find / -name mysql

删除上述命令查找出的相关文件

rm -rf /var/lib/mysql
rm -rf /usr/share/mysql
rm -rf /etc/selinux/targeted/active/modules/100/mysql
rm -rf /etc/selinux/targeted/tmp/modules/100/mysql

5.删除配置文件和日志

rm -f /etc/my.cnf*
rm -f /var/log/mysqld.log

3.2.2、卸载MySQL8

1.停止MySQL服务

systemctl stop mysqld

2.查看当前MySQL安装状况

rpm -qa|grep -i mysql

3.卸载已安装程序

#卸载上述命令查询出的所有已安装程序
rpm -e --nodeps mysql-community-common
rpm -e --nodeps mysql-community-server
rpm -e --nodeps mysql-community-libs
rpm -e --nodeps mysql-community-icu-data-files
rpm -e --nodeps mysql-community-client
rpm -e --nodeps mysql-community-client-plugins

4.删除残留文件

查找相关文件

find / -name mysql

删除上述命令查找出的相关文件

rm -rf /etc/selinux/targeted/active/modules/100/mysql
rm -rf /etc/selinux/targeted/tmp/modules/100/mysql
rm -rf /usr/lib64/mysql
rm -rf /var/lib/mysql

5.删除配置文件和日志

rm -f /etc/my.cnf* #如果有的话
rm -f /var/log/mysqld.log

3.3、检查必要依赖

查询是否安装了如下依赖

rpm -qa|grep libaio
rpm -qa|grep net-tools

如果存在，则如下所示：

如果不存在，则需要安装

yum install libaio
yum install net-tools

3.4、检查/tmp临时目权限

由于MySQL安装过程中，会通过MySQL用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。

权限检查：

ll -a /

如果权限不是777则授权为777：

chmod -R 777 /tmp

4、安装

4.1、解压

将安装程序上传至到/opt目录下，并解压：

cd /opt
tar xvf mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

4.2、安装

在安装文件目录下执行以下命令（必须按照顺序执行）：

rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.29-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.29-1.el7.x86_64.rpm

# -i, --install    安装软件包
# -v, --verbose    提供更多的详细信息输出
# -h, --hash       软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

4.3、查看是否安装成功

rpm -qa|grep -i mysql

4.4、查看版本

mysqladmin --version

4.5、安装后的目录结构

路径	解释
/usr/bin	相关命令目录，mysqladmin等
`/var/lib/mysql/`	MySQL数据库文件的存放路径
/usr/lib64/mysql/plugin	MySQL
`/var/log/mysqld.log`	MySQL日志路径
/var/run/mysqld/mysqld.pid	进程pid文件
/var/lib/mysql/mysql.sock	本地连接时用的套接字文件
/etc/systemd/system/multi-user.target.wants/mysqld.service	服务启停相关脚本
`/etc/my.cnf`、/usr/share/mysql-8.0	MySQL配置文件

5、启动

5.1、服务的初始化

#初始化数据目录并生成初始密码
mysqld --initialize --user=mysql
#查看数据目录
ls /var/lib/mysql/

5.2、服务的启动和停止

#启动，安装完MySQL第一次启动服务时，也会同时执行服务的初始化，因此也可以省略5.1
#如果省略了步骤5.1，并且启动失败，则说明之前安装的MySQL没有卸载干净，数据目录/var/lib/mysql/没有删除
#请删除数据目录后再重新启动
systemctl start mysqld
#关闭
systemctl stop mysqld
#重启
systemctl restart mysqld
#查看服务状态
systemctl status mysqld

5.3、查看进程

#进程存在则说明MySQL启动成功
ps -ef|grep -i mysql

5.4、设置开机启动

#查看服务是否自动启动（是：enabled | 否：disabled）
systemctl list-unit-files|grep mysqld.service 

#设置开机启动：如不是enabled可以运行如下命令设置自启动
systemctl enable mysqld
#重新加载服务配置
systemctl daemon-reload 

#如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld
#重新加载服务配置
systemctl daemon-reload

6、配置密码

6.1、查找初始密码

#mysql安装完成之后，在/var/log/mysqld.log文件中给root生成了一个默认密码。通过下面的方式找到root默认密码，然后登录mysql。
grep 'temporary password' /var/log/mysqld.log

6.2、登录MySQL客户端

mysql -uroot -p
#然后输入上面找到的默认密码

6.3、修改密码

因为初始密码只是一个临时密码，默认只可以登录，无法做其他操作，因此我们需要重置密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
-- 或者
SET password FOR 'root'@'localhost'= '123456';

6.4、忘记root密码

需要的时候参考

在/etc/my.cnf 文件[mysqld]中加上：

skip_grant_tables=1

重新启动mysql服务使配置生效：

systemctl restart mysqld

就可以无需密码登录MySQL客户端

mysql -uroot

接下来可以执行修改密码的语句重置密码

7、远程连接

7.1、当前问题

在用SQLyog或Navicat中配置远程连接MySQL数据库时遇到如下报错信息，这是由于MySQL默认不支持远

程连接。

7.2、解决方案

查看系统数据库MySQL中的user表：

USE mysql;
SELECT Host,User FROM user;

可以看到root用户的当前主机配置信息为localhost。修改Host为通配符%

Host列指定了允许用户登录所使用的IP：

Host=localhost，表示只能通过本机客户端去访问。
Host=% ，表示所有IP都有连接权限。

UPDATE user SET Host = '%' WHERE User ='root';
FLUSH PRIVILEGES; -- Host修改完成后记得执行FLUSH PRIVILEGES使配置立即生效：

注意：在生产环境下不能为了省事将host设置为%，这样做会存在安全问题，可以设置为生产环境IP。

测试连接：MySQL 8 版本，连接时还会出现如下问题

配置新连接报错：错误号码 2058，出现这个原因是MySQL 8 之前的版本中加密规则是mysql_native_password，而在MySQL 8之后，加密规则是caching_sha2_password。

解决方案有两种，一种是升级SQLyog和Navicat（因此，新版SQLyog不会出现此问题），另一种是把MySQL用户登录密码加密规则还原成mysql_native_password。

解决方法：Linux下 mysql -uroot -p 登录你的 MySQL 数据库，然后执行这条SQL：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '任意密码';

然后再重新配置SQLyog的连接，重新填写密码，则可连接成功了。

7.3、排查其他连接问题

注意：如果上述解决方案无效，则需要按照以下步骤确认MySQL服务器网络、端口号和防火墙的状态

确认网络连接：在本机 ping 远程IP地址保证网络畅通，如果网络不畅同则需要确认IP地址，网关等：

ping MySQL主机IP

确认防火墙状态：关闭防火墙或开放端口：

方式一：关闭防火墙

#查看防火墙状态
firewall-cmd --state 
#或 
systemctl status firewalld

#关闭防火墙
systemctl stop firewalld

#禁用开机启动
systemctl disable firewalld

方式二：启用防火墙并开放3306端口

#查看防火墙状态
firewall-cmd --state

#开启防火墙
systemctl start firewalld

#设置开机自启
systemctl enable firewalld

#查看开放的端口
firewall-cmd --list-ports

#设置开放的端口号
firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent
#重启防火墙
firewall-cmd --reload 

#查看开放的端口
firewall-cmd --list-ports

8、密码强度设置（了解）

8.1、安装强度校验插件

MySQL使用的是validate_password插件检测、验证账号密码强度，保障账号的安全性。

-- 安装插件
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

此方法也会注册到元数据，也就是mysql.plugin表中，所以不用担心MySQL重启后插件会失效。

此时修改密码可能报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，例如：

ALTER USER 'root'@'%' IDENTIFIED BY '123456';

8.2、查看密码强度

SHOW VARIABLES LIKE 'validate_password%';

变量说明

选项	默认值	参数描述
validate_password_check_user_name	ON	设置为ON的时候表示能将密码设置成当前用户名。
validate_password_dictionary_file		用于检查密码的字典文件的路径名，默认为空
validate_password_length	8	密码的最小长度，也就是说密码长度必须大于或等于8
validate_password_mixed_case_count	1	密码必须包含的小写和大写字符的最小数量。
validate_password_number_count	1	密码必须包含的数字个数
validate_password_policy	MEDIUM	密码强度等级，可以使用0、1、2或LOW、MEDIUM、STRONG指定。 0/LOW ：只检查长度。1/MEDIUM ：检查长度、数字、大小写、特殊字符。 2/STRONG ：检查长度、数字、大小写、特殊字符、字典文件。
validate_password_special_char_count	1	密码必须包含的特殊字符个数

8.3、修改密码强度

SET GLOBAL validate_password_policy=LOW; --只检查长度
SET GLOBAL validate_password_length=6; --密码的最小长度是6

此时再次设置密码为123456，可以成功。

ALTER USER 'root'@'%' IDENTIFIED BY '123456';

8.4、卸载强度校验插件

 UNINSTALL PLUGIN validate_password;

9、字符集

9.1、默认字符集

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

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

SHOW VARIABLES LIKE '%char%';

9.2、utf8与utf8mb4

utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷的定义了两个概念：

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

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

10、 SQL大小写规范

10.1、Windows和Linux的区别

Windows环境：

全部不区分大小写

Linux环境：

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

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

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

10.2、Linux下大小写规则设置（了解）

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

1、停止MySQL服务 
2、删除数据目录，即删除 /var/lib/mysql 目录 
3、在MySQL配置文件（/etc/my.cnf ）的 [mysqld] 中添加 lower_case_table_names=1 
4、启动MySQL服务

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

11、sql_mode

11.1、宽松模式 vs 严格模式

宽松模式：

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

严格模式：

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

11.2、查看和设置sql_mode

查询sql_mode的值：

SELECT @@session.sql_mode; 
SELECT @@global.sql_mode; 
-- 或者 
SHOW VARIABLES LIKE 'sql_mode'; --session级别

临时设置sql_mode的值：

SET GLOBAL sql_mode = 'modes...'; --全局，要重新启动客户端生效，重启MySQL服务后失效
SET SESSION sql_mode = 'modes...'; --当前会话生效效，关闭当前会话就不生效了。可以省略SESSION关键字

在 /etc/my.cnf 中配置，永久生效：

[mysqld]
sql-mode = "modes..."

11.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子句中，也没有出现在函数中：

在非 “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';

11.4、sql_mode常用值（了解）

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

第02章用户与权限管理

1、用户管理

1.1、登录MySQL服务器

启动MySQL服务后，可以通过MySQL命令来登录MySQL服务器，命令如下：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"

下面详细介绍命令中的参数：

-h参数 后面接主机名或者主机IP。
-P参数 后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306，不使用该参数时自动连接到3306端口。
-u参数 后面接用户名。
-p参数 会提示输入密码。
DatabaseName参数 指明登录到哪一个数据库中。如果没有该参数，登录后使用USE命令来选择数据库。
-e参数 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务器。

举例：

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"

1.2、创建用户

语法：

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

用户名参数表示新建用户的账户，由用户（User）和主机名（Host）构成；
“[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
CREATE USER语句可以同时创建多个用户。

举例：

CREATE USER zhang3 IDENTIFIED BY '123456'; -- 默认host是 %

CREATE USER 'li4'@'localhost' IDENTIFIED BY '123456';

1.3、修改用户

修改用户名：

UPDATE mysql.user SET USER='wang5' WHERE USER='li4'; 
FLUSH PRIVILEGES;

1.4、删除用户

语法：

DROP USER user[,user]…;

举例：

DROP USER zhang3; -- 默认删除host为%的用户

DROP USER 'wang5'@'localhost';

注意：不推荐通过 DELETE FROM user WHERE User='zhang3' 进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

2、权限管理

2.1、权限列表

MySQL到底都有哪些权限呢？

SHOW PRIVILEGES;

（1） CREATE和DROP权限 可以创建新的数据库和表，或删除已有的数据库和表。
（2） SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作。
（3） INDEX权限 允许创建或删除索引。
（4） ALTER权限 可以使用ALTER TABLE来更改表的结构和重新命名表。
（5） GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序。

2.2、授予权限

语法：

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

如果发现没有该用户，则会直接新建一个用户，并设置密码。
如果已经有了该用户，则会授予权限，如果后面有IDENTIFIED，则修改密码，否则只授予权限。

举例：

使用root用户给zhao6授予atguigudb库下所有表的增改查的权限

CREATE USER zhao6 IDENTIFIED BY '123456'; -- 创建用户
-- 此时用zhao6登录，并 执行show databases; 只能看到information_schema数据库

GRANT SELECT, INSERT, UPDATE ON atguigudb.* TO 'zhao6'@'%'; --授权
-- 此时用zhao6登录，并 执行show databases; 可以看到atguigudb数据库


-- 用zhao6登录可以执行SELECT, INSERT, UPDATE
USE atguigudb;
SELECT * FROM employee;
UPDATE employee SET NAME = 'helen' WHERE id = 1;
INSERT INTO employee VALUES(6, 'annie', 20, 101) ;

-- 不可以执行 DELETE
DELETE FROM employee WHERE id = 1;

--如果再次授予zhao6 DELETE权限，则需要退出客户端重新登录再进行测试

使用root用户给tian7授予所有库下所有表的所有权限

CREATE USER tian7 IDENTIFIED BY '123456'; -- 创建用户
GRANT ALL PRIVILEGES ON *.* TO 'tian7'@'%'; --授权

注意：tian7虽然拥有所有的权限，但是没有给其他人授权的能力

使用root用户给tian7授予所有库下所有表的所有权限，并可以给他人授权,使用 WITH GRANT OPTION

GRANT SYSTEM_USER ON *.* TO 'root'@'%'; -- root先给自己授予SYSTEM_USER权限
GRANT ALL PRIVILEGES ON *.* TO 'tian7'@'%' WITH GRANT OPTION; --授权

2.3、查看权限

查看当前用户权限：

SHOW GRANTS;

查看某用户的权限：

SHOW GRANTS FOR '用户名'@'主机地址';

2.4、回收权限

语法：

REVOKE  权限1, 权限2, …权限n ON 数据库名称.表名称 FROM '用户名'@'主机地址';

举例：

收回zhao6在atguigudb库下的所有表的SELECT权限

REVOKE SELECT ON atguigudb.* FROM 'zhao6'@'%';

收回zhao6全库全表的所有权限

REVOKE ALL PRIVILEGES ON *.* FROM 'zhao6'@'%';

收回tian7全库全表的所有权限

-- GRANT SYSTEM_USER ON *.* TO 'root'@'%'; -- root先给自己授予SYSTEM_USER权限
REVOKE ALL PRIVILEGES ON *.* FROM 'tian7'@'%'; -- 再收回tian7的所有权限

第03章逻辑架构

1、逻辑架构剖析

1.1、服务器处理客户端请求

服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为例展示：

下面具体展开看一下（下面是MySQL5.7使用的经典架构图，MySQL 8中去掉了Caches&Buffers部分）：

b7a8eb46-1168-434c-b63b-ec775affab7b

1.2、Connectors（客户端）

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

1.3、MySQL Server（服务器）

第1层：连接层

系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。
经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
- 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
- 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限
TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

第2层：服务层

Management Serveices & Utilities：系统管理和控制工具

SQL Interface：SQL接口：

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

Parser：解析器：

在SQL命令传递到解析器的时候会被解析器验证和解析。解析器中SQL 语句进行语法分析、语义分析，并为其创建语法树。典型的解析树如下：

Optimizer：查询优化器：

SQL语句在语法解析后、查询前会使用查询优化器对查询进行优化，确定SQL语句的执行路径，生成一个执行计划。

Caches & Buffers：查询缓存组件：

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

第3层：引擎层

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

1.4、存储层

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

1.5、查询流程说明

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

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

接下来，查询优化器将解析树转化成执行计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，他将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

例如： select uid, name from user where gender = 1; 优化器来决定先投影还是先过滤。

最后，进入执行器阶段。通过查询执行引擎调用存储引擎执行SQL查询并返回结果。在MySQL8以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

2、SQL执行计划

利用SHOW VARIABLES 可以查看SQL的执行计划。使用前需要先开启该功能：

2.1、MySQL8

1.开启profiling

确认profiling是否开启

SHOW VARIABLES LIKE '%profiling%';

profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1：

SET profiling = 1;  -- profiling = ON

2.显示查询

执行任意SQL语句：

SELECT * FROM atguigudb.employee; 
SELECT * FROM atguigudb.employee WHERE id = 5;

显示最近的几次查询：

SHOW PROFILES;

3.查看执行计划

查看最后一个SQL的执行计划：

SHOW PROFILE;

主要步骤：
- checking permissions：检查权限
- Opening tables：打开表
- init ：初始化
- System lock ：系统锁
- optimizing ：优化sql
- statistics ：统计
- preparing ：准备执行
- executing ：执行sql
- Sending data ：发送数据
- Sorting result ：排序
- end ：结束
- query end ：查询结束
- closing tables ：关闭表／去除TMP 表
- freeing items ：释放
- cleaning up ：清理

查看指定SQL的执行计划：查询指定的 Query ID

SHOW PROFILE FOR QUERY 3;

查询更丰富的内容：

SHOW PROFILE cpu,block io FOR QUERY 3;

SHOW PROFILE 的常用查询参数:
- ①ALL：显示所有的开销信息。
- ②BLOCK IO：显示块IO开销。
- ③CONTEXT SWITCHES：上下文切换开销。
- ④CPU：显示CPU开销信息。
- ⑤IPC：显示发送和接收开销信息。
- ⑥MEMORY：显示内存开销信息。
- ⑦PAGE FAULTS：显示页面错误开销信息。
- ⑧SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。
- ⑨SWAPS：显示交换次数开销信息。

2.2、MySQL5.7（了解）

1.开启查询缓存

修改配置文件：vim /etc/my.cnf
新增一行：query_cache_type=1
重启MySQL：systemctl restart mysqld

2、执行SQL并查看执行计划

参考MySQl8中执行计划的启用和查看方式，在MySQL5.7中查看执行计划

第一次执行查询SQL：

第二次执行相同的SQL：

3、存储引擎

3.1、查看存储引擎

查看MySQL提供什么存储引擎

SHOW ENGINES;

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

也可以通过以下语句查看默认的存储引擎：

SHOW VARIABLES LIKE '%default_storage_engine%';

3.2、设置存储引擎（了解）

方法1：

设置默认存储引擎：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

方法2：

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

方法3：

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

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

3.3、各种引擎介绍

1. InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
数据文件结构：
- 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中）
- 表名.ibd 存储数据和索引
InnoDB不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。

2. MyISAM存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用。
数据文件结构：
- 表名.frm 存储表结构
- 表名.MYD 存储数据
- 表名.MYI 存储索引
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
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点	并发查询，节省资源、消耗少、简单业务	并发写、事务、更大资源
默认安装	Y	Y
默认使用	N	Y
自带系统表使用	Y	N

3.5、阿里巴巴、淘宝用哪个

Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。
该版本提升了在高负载情况下的 InnoDB 的性能，为 DBA 提供一些非常有用的性能诊断工具。另外有更多的参数和命令来控制服务器行为。
该公司新建了一款存储引擎叫Xtradb完全可以替代Innodb，并且在性能和并发上做得更好。
阿里巴巴大部分MySQL数据库其实使用的Percona的原型加以修改。

第04章 SQL预热

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

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提升效率

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;

3、扩展掌门人

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;

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;

子查询方式

-- step1：查询所有的员工和对应的ceo的id
SELECT 
emp.name,
dept.ceo
FROM t_emp emp 
LEFT JOIN t_dept dept ON emp.deptid = dept.id;

-- step2：使用子查询将ceo的id列替换成ceo的name
SELECT 
emp.name,
-- dept.ceo
(SELECT emp.name FROM t_emp emp WHERE emp.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;

第05章 MySQL索引

1、索引简介

1.1、什么是索引

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

1.2、索引的优缺点

优点：

（1）类似大学图书馆书目索引，提高数据检索的效率，降低数据库的IO成本 ，这也是创建索引最主要的原因。
（2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性 。
（3）在实现数据的参考完整性方面，可以加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。
（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低CPU的消耗。

缺点：
（1）创建索引和维护索引要耗费时间 ，并且随着数据量的增加，所耗费的时间也会增加。
（2）索引需要占磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度 。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

因此，选择使用索引时，需要综合考虑索引的优点和缺点。

1.3、索引的使用场景

哪些情况需要创建索引：

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不要创建索引：

表记录太少
经常增删改的表或者字段。
- Why：索引虽然提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引
有大量重复数据的列上

1.4、索引分类

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

1.5、索引操作

1. 创建索引

随表一起创建索引：

CREATE TABLE customer (
    
  id INT UNSIGNED AUTO_INCREMENT,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200),
    
  PRIMARY KEY(id), -- 主键索引：列设定为主键后会自动建立索引，唯一且不能为空。
  UNIQUE INDEX uk_no (customer_no), -- 唯一索引：索引列值必须唯一，允许有NULL值，且NULL可能会出现多次。
  KEY idx_name (customer_name), -- 普通索引：既不是主键，列值也不需要唯一，单纯的为了提高查询速度而创建。
  KEY idx_no_name (customer_no,customer_name) -- 复合索引：即一个索引包含多个列。
);

单独建创索引：

CREATE TABLE customer1 (
  id INT UNSIGNED,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200)
);

ALTER TABLE customer1 ADD PRIMARY KEY customer1(id); -- 主键索引
CREATE UNIQUE INDEX uk_no ON customer1(customer_no); -- 唯一索引
CREATE INDEX idx_name ON customer1(customer_name);  -- 普通索引
CREATE INDEX idx_no_name ON customer1(customer_no,customer_name); -- 复合索引

使用ALTER命令：

ALTER TABLE customer1 ADD PRIMARY KEY (id); -- 主键索引
ALTER TABLE customer1 ADD UNIQUE INDEX uk_no (customer_no); -- 唯一索引
ALTER TABLE customer1 ADD INDEX idx_name (customer_name);   -- 普通索引
ALTER TABLE customer1 ADD INDEX idx_no_name (customer_no,customer_name);  -- 复合索引

2. 查看索引

SHOW INDEX FROM customer;

3. 删除索引

DROP INDEX idx_name ON customer; -- 删除单值、唯一、复合索引

ALTER TABLE customer MODIFY id INT, DROP PRIMARY KEY; -- 删除主键索引(有主键自增)
ALTER TABLE customer DROP PRIMARY KEY;  -- 删除主键索引(没有主键自增)

2、树

2.1、二叉树

二叉树

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

二叉搜索树BST

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

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

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

下图就是一种可能的索引方式示例：

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

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

BST的问题

左子树全部为空，从形式上看，更像一个单链表，不能发挥BST的优势。
解决方案：平衡二叉树(AVL)

平衡二叉树（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

AVL的问题

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

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

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

2.2、多叉树

多叉树（multiway tree）允许每个节点可以有更多的数据项和更多的子节点。2-3树，2-3-4树就是多叉树，多叉树通过重新组织节点，减少节点数量，增加分叉，减少树的高度，能对二叉树进行优化。

2-3树

下面2-3树就是一颗多叉树

2-3树具有如下特点：

2-3树的所有叶子节点都在同一层。
有两个子节点的节点叫二节点，二节点要么没有子节点，要么有两个子节点。
有三个子节点的节点叫三节点，三节点要么没有子节点，要么有三个子节点。
2-3树是由二节点和三节点构成的树。
对于三节点的子树的值大小仍然遵守 BST 二叉排序树的规则。

2-3-4树

2.3、B树

B-Tree即B树，Balance Tree，平衡树,它的高度远小于平衡二叉树的高度。
2-3树是最简单的B树结构。
B树的阶：节点的最多子节点个数。比如2-3树的阶是3，2-3-4树的阶是4。

上图所表示的 B 树就是一棵 3 阶的 B 树。我们可以看下磁盘块 2，里面的关键字为（8，12），它有 3 个孩子 (3，5)，(9，10) 和 (13，15)，你能看到 (3，5) 小于 8，(9，10) 在 8 和 12 之间，而 (13，15)大于 12，三节点的子树的值大小仍然遵守 BST 二叉排序树的规则。

假设我们想要查找的数据项是 9 ，那么步骤可以分为以下几步：

第一次磁盘IO：找到根节点磁盘块1，读入内存，执行二分查找，9 小于 17 ，得到指针 P1；
第二次磁盘IO：按照指针P1找到磁盘块 2，读入内存，执行二分查找， 9 在 8 和 12 之间，得到指针 P2；
第三次磁盘IO：按照指针P2找到磁盘块 6，读入内存，执行二分查找，找到了数据项 9。

你能看出来在 B 树的搜索过程中，我们比较的次数并不少，但如果把数据读取出来然后在内存中进行比较，这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行比较所需要的时间要多，是数据查找用时的重要因素。 B 树相比于平衡二叉树来说磁盘 I/O 操作要少，在数据查询中比平衡二叉树效率要高。所以只要树的高度足够低，IO次数足够少，就可以提高查询性能。

再举例：

假设使用B树作为索引结构，存放MySQL中的数据：

蓝色部分表示数据的主键，黄色部分表示除主键外的其他数据，紫色部分表示指向子节点的指针

3、MySQL的索引结构：B+树

3.1、InnoDB中的索引

3.1.1、设计索引

假设有一个表index_demo，表中有2个INT类型的列，1个CHAR(1)类型的列，c1列为主键：

CREATE TABLE index_demo(c1 INT,c2 INT,c3 CHAR(1),PRIMARY KEY(c1)) ;

index_demo表的简化的行格式示意图如下：

我们只在示意图里展示记录的这几个部分：

record_type：表示记录的类型， 0是普通记录、 2是最小记录、 3 是最大记录、1是B+树非叶子节点记录。
next_record：表示下一条记录的相对位置，我们用箭头来表明下一条记录。
各个列的值：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

将其他信息项暂时去掉并把它竖起来的效果就是这样：

把一些记录放到页里的示意图就是（这里一页就是一个磁盘块，代表一次IO）：

MySQL InnoDB的默认的页大小是16KB，因此数据存储在磁盘中，可能会占用多个数据页。如果各个页中的记录没有规律，我们就不得不依次遍历所有的数据页。如果我们想快速的定位到需要查找的记录在哪些数据页中，我们可以这样做：

下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值
给所有的页建立目录项

以页28为例，它对应目录项2 ，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值 5。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键值快速查找某条记录的功能了。比如：查找主键值为 20 的记录，具体查找过程分两步：

先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 ≤ 20 < 209 ），对应页9。
再到页9中根据二分法快速定位到主键值为 20 的用户记录。

至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为索引 。

3.1.2、InnoDB中的索引方案

我们新分配一个编号为30的页来专门存储目录项记录，页10、28、9、20专门存储用户记录：

目录项记录和普通的用户记录的不同点：

目录项记录的 record_type 值是1，而普通用户记录的 record_type 值是0。
目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，包含很多列，另外还有InnoDB自己添加的隐藏列。

现在查找主键值为 20 的记录，具体查找过程分两步：

先到页30中通过二分法快速定位到对应目录项，因为 12 ≤ 20 < 209 ，就是页9。
再到页9中根据二分法快速定位到主键值为 20 的用户记录。

更复杂的情况如下：

我们生成了一个存储更高级目录项的页33 ，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在 [1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值不小于320 的话，就到页32中查找更详细的目录项记录。这个数据结构，它的名称是 B+树。

3.2、B树和B+树对比

B+ 树和 B 树的差异：

B+树中有 k 个孩子的节点就有 k 个关键字，也就是孩子数量 = 关键字数。而B树中，孩子数量 = 关键字数+1。
B+树中非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大值（或最小）。
B+树中非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录。
B+树中所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

B+树为什么IO的次数会更少：

上图中存放用户记录的页最多存放3条记录，存放目录项记录的页最多存放4条记录。其实真实环境中一个页存放的记录数量是非常大的（默认16KB），假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的非叶子节点代表的数据页可以存放1000条目录项记录 ，那么：

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记录！！！

你的表里能存放 100000000000 条记录吗？所以一般情况下，我们用到的B+树都不会超过4层，我们

通过主键值去查找某条记录最多只需要做4次IO操作（查找3个目录项页和1个用户记录页）。

B+树的非叶子节点不存储用户记录，只存储目录记录，相对B树每个节点可以存储更多的记录，树的高度会更矮胖，IO次数也会更少。

B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树。

但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

3.3、聚簇索引

特点：

索引和数据保存在同一个B+树中
页内的记录是按照主键的大小顺序排成一个单向链表 。
页和页之间也是根据页中记录的主键的大小顺序排成一个双向链表 。
非叶子节点存储的是记录的主键+页号。
叶子节点存储的是完整的用户记录。

优点：

数据访问更快，因为索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
聚簇索引对于主键的排序查找和范围查找速度非常快。
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库可以从更少的数据块中提取数据，节省了大量的IO操作 。

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。

限制：

只有InnoDB引擎支持聚簇索引，MyISAM不支持聚簇索引。
由于数据的物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。
如果没有为表定义主键，InnoDB会选择非空的唯一索引列代替。如果没有这样的列，InnoDB会隐式的定义一个主键作为聚簇索引。
为了充分利用聚簇索引的聚簇特性，InnoDB中表的主键应选择有序的id，不建议使用无序的id，比如UUID、MD5、HASH、字符串作为主键，无法保证数据的顺序增长。

3.4、非聚簇索引

（二级索引、辅助索引）

聚簇索引，只能在搜索条件是主键值时才发挥作用，因为B+树中的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么需要创建非聚簇索引。

例如，以c2列作为搜索条件，那么需要使用c2列创建一棵B+树，如下所示：

这个B+树与聚簇索引有几处不同：

页内的记录是按照从c2列的大小顺序排成一个单向链表 。
页和页之间也是根据页中记录的c2列的大小顺序排成一个双向链表 。
非叶子节点存储的是记录的c2列+页号。
叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。

例如：根据c2列的值查找c2=4的记录，查找过程如下：

根据根页面44定位到页42（因为2 ≤ 4 < 9）
由于c2列没有唯一性约束，所以c2=4的记录可能分布在多个数据页中，又因为 2 ≤ 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。
在页34和35中定位到具体的记录。
但是这个B+树的叶子节点只存储了c2和c1（主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

概念：回表

我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到聚簇索引中再查一遍，这个过程称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到 2棵B+树！

问题：

为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？

回答：
如果把完整的用户记录放到叶子节点是可以不用回表。但是太占地方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太浪费存储空间了。

一张表可以有多个非聚簇索引：

3.5、MyISAM中的索引

MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是数据记录的地址 。

下图是MyISAM索引的原理图（索引和数据分开存储，是非聚簇索引）：

如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示（是非聚簇索引）：

3.6、MyISAM与InnoDB对比

MyISAM的索引方式都是非聚簇的，与InnoDB包含1个聚簇索引是不同的。

在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引。
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- MyISAM的表在磁盘上存储在以下文件中： *.sdi（描述表结构）、*.MYD（数据），*.MYI（索引）
- InnoDB的表在磁盘上存储在以下文件中： .ibd（表结构、索引和数据都存在一起）
InnoDB的非聚簇索引data域存储相应记录主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域。
MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
InnoDB要求表必须有主键（ MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

3.7、索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：

空间上的代价

每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。

时间上的代价

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位、页面分裂、页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。

B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树。

但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。

第06章索引优化

1、数据库优化方案

性能下降、SQL慢、执行时间长、等待时间长

数据过多：分库分表
索引失效，没有充分利用到索引：索引建立
关联查询太多join（设计缺陷或不得已的需求）：SQL优化
服务器调优及各个参数设置（缓冲、线程数等）：调整my.cnf

2、使用Explain

3、准备数据

4、单表索引优化

5、关联查询优化

6、子查询优化

7、排序优化

8、分组优化

9、覆盖索引

第07章慢查询日志

第08章 View视图

posted @ 2022-07-10 11:35 有空就一起吃饭吧阅读(97) 评论(0) 编辑收藏举报

刷新页面返回顶部

有空就一起吃饭吧

Mysql高级

第01章 Linux下MySQL的安装与使用

1、版本说明

2、下载MySQL

2.1、官网

2.2、下载页面

2.3、安装包

3、检查安装环境

3.1、卸载系统自带的mariaDB

3.2、卸载已安装的MySQL

3.2.1、卸载MySQL5.7

3.2.2、卸载MySQL8

3.3、检查必要依赖

3.4、检查/tmp临时目权限

4、安装

4.1、解压

4.2、安装

4.3、查看是否安装成功

4.4、查看版本

4.5、安装后的目录结构

5、启动

5.1、服务的初始化

5.2、服务的启动和停止

5.3、查看进程

5.4、设置开机启动

6、配置密码

6.1、查找初始密码

6.2、登录MySQL客户端

6.3、修改密码

6.4、忘记root密码

7、远程连接

7.1、当前问题

7.2、解决方案

7.3、排查其他连接问题

8、密码强度设置（了解）

8.1、安装强度校验插件

8.2、查看密码强度

8.3、修改密码强度

8.4、卸载强度校验插件

9、字符集

9.1、默认字符集

9.2、utf8与utf8mb4

10、 SQL大小写规范

10.1、Windows和Linux的区别

10.2、Linux下大小写规则设置（了解）

11、sql_mode

11.1、宽松模式 vs 严格模式

11.2、查看和设置sql_mode

11.3、错误开发演示

11.4、sql_mode常用值（了解）

第02章 用户与权限管理

1、用户管理

1.1、登录MySQL服务器

1.2、创建用户

1.3、修改用户

1.4、删除用户

2、权限管理

2.1、权限列表

2.2、授予权限

2.3、查看权限

2.4、回收权限

第03章 逻辑架构

1、逻辑架构剖析

1.1、服务器处理客户端请求

1.2、Connectors（客户端）

1.3、MySQL Server（服务器）

第1层：连接层

第2层：服务层

第3层：引擎层

1.4、存储层

1.5、查询流程说明

2、SQL执行计划

2.1、MySQL8

2.2、MySQL5.7（了解）

3、存储引擎

3.1、查看存储引擎

3.2、设置存储引擎（了解）

3.3、各种引擎介绍

3.4、MyISAM和InnoDB的区别

第02章用户与权限管理

第03章逻辑架构

第06章索引优化

第07章慢查询日志