MySQL的Linux版安装
MySQL的4大版本
下载MySQL指定版本
- 下载地址 https://dev.mysql.com/downloads/mysql/
- Linux系统下安装MySQL的几种方式
- Linux系统下安装软件的常用三种方式：
- rpm命令
- 使用rpm命令安装扩展名为".rpm"的软件包
- .rpm包的一般格式：
1.
- yum命令
- 需联网，从 互联网获取 的yum源，直接使用yum命令安装。
- 编译安装源码包
- 针对 tar.gz 这样的压缩格式，要用tar命令来解压；如果是其它压缩格式，就使用其它命令。
- Linux系统下安装MySQL，官方给出多种安装方式
- 这里不能直接选择CentOS 7系统的版本，所以选择与之对应的 Red Hat Enterprise Linux https://downloads.mysql.com/archives/community/ 直接点Download下载RPM Bundle全量 包。包括了所有下面的组件。不需要一个一个下载了。
- 下载的tar包，用压缩工具打开
- 解压后rpm安装包 （红框为抽取出来的安装包）
- CentOS7下检查MySQL依赖
- 检查/tmp临时目录权限（必不可少）
- 由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执 行 ：
chmod -R 777 /tmp
- 安装前，检查依赖
1. rpm -qa|grep libaio
2. 如果存在libaio包如下：
3. rpm -qa|grep net-tools
4.
5.
6. 如果存在net-tools包如下：
7. rpm -qa|grep net-tools
8. 
9. 如果不存在需要到centos安装盘里进行rpm安装。安装linux如果带图形化界面，这些都是安装好 的。
- CentOS7下MySQL安装过程
- 将安装程序拷贝到/opt目录下
1. 在mysql的安装文件目录下执行：（必须按照顺序执行） 
- 注意: 如在检查工作时，没有检查mysql依赖环境在安装mysql-community-server会报错
- rpm 是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的 文件形式，易于安装。
- -i , --install 安装软件包
- -v , --verbose 提供更多的详细信息输出
- -h , --hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条
- 安装过程截图
- 安装过程中可能的报错信息：
- 一个命令：yum remove mysql-libs 解决，清除之前安装过的依赖即可
- 查看MySQL版本
- 执行如下命令，如果成功表示安装mysql成功。类似java -version如果打出版本等信息
- mysql --version #或 mysqladmin --version
- 执行如下命令，查看是否安装成功。需要增加 -i 不用去区分大小写，否则搜索不到。
- rpm -qa|grep -i mysql
- 服务的初始化
1. 为了保证数据库目录与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执 行下面的命令初始化：
2. mysqld --initialize --user=mysql- 说明： --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将 该密码标记为过 期 ，登录后你需要设置一个新的密码。生成的 临时密码 会往日志中记录一份。 查看密码：
- cat /var/log/mysqld.log
1.
- root@localhost: 后面就是初始化的密码
- 启动MySQL，查看状态
#加不加.service后缀都可以
启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：systemctl restart mysqld.service
查看状态：systemctl status mysqld.service
mysqld 这个可执行文件就代表着 MySQL 服务器程序，运行这个可执行文件就可以直接启动一个
服务器进程。
查看进程：
ps -ef | grep -i mysql
查看MySQL服务是否自启动
systemctl list-unit-files|grep mysqld.service
默认是enabled。
如不是enabled可以运行如下命令设置自启动
systemctl enable mysqld.service
如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld.service
数据目录
MySQL8的主要目录结构
安装好MySQL 8之后，我们查看如下的目录结构：
find / -name mysql
- 数据库文件的存放路径
- MySQL数据库文件的存放路径：/var/lib/mysql/
1.
- 从结果中可以看出，在我的计算机上MySQL的数据目录就是 /var/lib/mysql/ 。
- 相关命令目录
- 相关命令目录：/usr/bin（mysqladmin、mysqlbinlog、mysqldump等命令）和/usr/sbin。
- 配置文件目录
- 配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/mysql（如my.cnf）
数据库和文件系统的关系
- 查看默认数据库
- 查看一下在我的计算机上当前有哪些数据库：
1. mysql> SHOW DATABASES;
2. 可以看到有4个数据库是属于MySQL自带的系统数据库。- mysql
1. MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定 义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。 - information_schema
- MySQL 系统自带的数据库，这个数据库保存着MySQL服务器 维护的所有其他数据库的信息 ，比如有 哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些 描述性信息，有时候也称之为 元数据 。在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表，用于表示内部系统表。
- mysql
mysql> USE information_schema;
Database changed
mysql> SHOW TABLES LIKE 'innodb_sys%';
+--------------------------------------------+
| Tables_in_information_schema (innodb_sys%) |
+--------------------------------------------+
| INNODB_SYS_DATAFILES |
| INNODB_SYS_VIRTUAL |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_SYS_TABLESPACES |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+--------------------------------------------+
10 rows in set (0.00 sec)
1. performance_schema
1. MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以 用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都 花费了多长时间，内存的使用情况等信息。
2. sys
1. MySQL 系统自带的数据库，这个数据库主要是通过 视图 的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。
- 数据库在文件系统中的表示
- 看一下我的计算机上的数据目录下的内容：
[root@localhost mysql]# cd /var/lib/mysql
[root@localhost mysql]# ll
总用量 189980
-rw-r-----. 1 mysql mysql 56 7月 28 00:27 auto.cnf
-rw-r-----. 1 mysql mysql 179 7月 28 00:27 binlog.000001
-rw-r-----. 1 mysql mysql 820 7月 28 01:00 binlog.000002
-rw-r-----. 1 mysql mysql 179 7月 29 14:08 binlog.000003
-rw-r-----. 1 mysql mysql 582 7月 29 16:47 binlog.000004
-rw-r-----. 1 mysql mysql 179 7月 29 16:51 binlog.000005
-rw-r-----. 1 mysql mysql 179 7月 29 16:56 binlog.000006
-rw-r-----. 1 mysql mysql 179 7月 29 17:37 binlog.000007
-rw-r-----. 1 mysql mysql 24555 7月 30 00:28 binlog.000008
-rw-r-----. 1 mysql mysql 179 8月 1 11:57 binlog.000009
-rw-r-----. 1 mysql mysql 156 8月 1 23:21 binlog.000010
-rw-r-----. 1 mysql mysql 156 8月 2 09:25 binlog.000011
-rw-r-----. 1 mysql mysql 1469 8月 4 01:40 binlog.000012
-rw-r-----. 1 mysql mysql 156 8月 6 00:24 binlog.000013
-rw-r-----. 1 mysql mysql 179 8月 6 08:43 binlog.000014
-rw-r-----. 1 mysql mysql 156 8月 6 10:56 binlog.000015
-rw-r-----. 1 mysql mysql 240 8月 6 10:56 binlog.index
-rw-------. 1 mysql mysql 1676 7月 28 00:27 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 7月 28 00:27 ca.pem
-rw-r--r--. 1 mysql mysql 1112 7月 28 00:27 client-cert.pem
-rw-------. 1 mysql mysql 1676 7月 28 00:27 client-key.pem
drwxr-x---. 2 mysql mysql 4096 7月 29 16:34 dbtest
-rw-r-----. 1 mysql mysql 196608 8月 6 10:58 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql 8585216 7月 28 00:27 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql 3486 8月 6 08:43 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月 6 10:56 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月 6 10:58 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 7月 28 00:27 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月 6 10:56 ibtmp1
drwxr-x---. 2 mysql mysql 4096 8月 6 10:56 #innodb_temp
drwxr-x---. 2 mysql mysql 4096 7月 28 00:27 mysql
-rw-r-----. 1 mysql mysql 26214400 8月 6 10:56 mysql.ibd
srwxrwxrwx. 1 mysql mysql 0 8月 6 10:56 mysql.sock
-rw-------. 1 mysql mysql 5 8月 6 10:56 mysql.sock.lock
drwxr-x---. 2 mysql mysql 4096 7月 28 00:27 performance_schema
-rw-------. 1 mysql mysql 1680 7月 28 00:27 private_key.pem
-rw-r--r--. 1 mysql mysql 452 7月 28 00:27 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 7月 28 00:27 server-cert.pem
-rw-------. 1 mysql mysql 1680 7月 28 00:27 server-key.pem
drwxr-x---. 2 mysql mysql 4096 7月 28 00:27 sys
drwxr-x---. 2 mysql mysql 4096 7月 29 23:10 temp
-rw-r-----. 1 mysql mysql 16777216 8月 6 10:58 undo_001
-rw-r-----. 1 mysql mysql 16777216 8月 6 10:58 undo_002
这个数据目录下的文件和子目录比较多，除了 information_schema 这个系统数据库外，其他的数据库 在 数据目录 下都有对应的子目录。 以我的 temp 数据库为例，在MySQL5.7 中打开：
[root@localhost mysql]# cd ./temp
[root@localhost temp]# ll
总用量 1144
-rw-r-----. 1 mysql mysql 8658 8月 18 11:32 countries.frm
-rw-r-----. 1 mysql mysql 114688 8月 18 11:32 countries.ibd
-rw-r-----. 1 mysql mysql 61 8月 18 11:32 db.opt
-rw-r-----. 1 mysql mysql 8716 8月 18 11:32 departments.frm
-rw-r-----. 1 mysql mysql 147456 8月 18 11:32 departments.ibd
-rw-r-----. 1 mysql mysql 3017 8月 18 11:32 emp_details_view.frm
-rw-r-----. 1 mysql mysql 8982 8月 18 11:32 employees.frm
-rw-r-----. 1 mysql mysql 180224 8月 18 11:32 employees.ibd
-rw-r-----. 1 mysql mysql 8660 8月 18 11:32 job_grades.frm
-rw-r-----. 1 mysql mysql 98304 8月 18 11:32 job_grades.ibd
-rw-r-----. 1 mysql mysql 8736 8月 18 11:32 job_history.frm
-rw-r-----. 1 mysql mysql 147456 8月 18 11:32 job_history.ibd
-rw-r-----. 1 mysql mysql 8688 8月 18 11:32 jobs.frm
-rw-r-----. 1 mysql mysql 114688 8月 18 11:32 jobs.ibd
-rw-r-----. 1 mysql mysql 8790 8月 18 11:32 locations.frm
-rw-r-----. 1 mysql mysql 131072 8月 18 11:32 locations.ibd
-rw-r-----. 1 mysql mysql 8614 8月 18 11:32 regions.frm
-rw-r-----. 1 mysql mysql 114688 8月 18 11:32 regions.ibd
- 在MySQL8.0中打开：
[root@atguigu01 mysql]# cd ./temp
[root@atguigu01 temp]# ll
总用量 1080
-rw-r-----. 1 mysql mysql 131072 7月 29 23:10 countries.ibd
-rw-r-----. 1 mysql mysql 163840 7月 29 23:10 departments.ibd
-rw-r-----. 1 mysql mysql 196608 7月 29 23:10 employees.ibd
-rw-r-----. 1 mysql mysql 114688 7月 29 23:10 job_grades.ibd
-rw-r-----. 1 mysql mysql 163840 7月 29 23:10 job_history.ibd
-rw-r-----. 1 mysql mysql 131072 7月 29 23:10 jobs.ibd
-rw-r-----. 1 mysql mysql 147456 7月 29 23:10 locations.ibd
-rw-r-----. 1 mysql mysql 131072 7月 29 23:10 regions.ibd
- 表在文件系统中的表示
- InnoDB存储引擎模式
- 为了保存表结构， InnoDB 在 数据目录 下对应的数据库子目录下创建了一个专门用于 描述表结构的文 件 ，文件名是这样： 表名.frm
- 比方说我们在 qinzheng 数据库下创建一个名为 test 的表：
mysql> USE qinzheng;
Database changed
mysql> CREATE TABLE test (
-> c1 INT
-> );
Query OK, 0 rows affected (0.03 sec)
那在数据库 qinzheng 对应的子目录下就会创建一个名为 test.frm 的用于描述表结构的文件。.frm文件 的格式在不同的平台上都是相同的。这个后缀名为.frm是以 二进制格式 存储的，我们直接打开是乱码 的。
表中数据和索引
系统表空间（system tablespace） 默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应 的 系统表空间 在文件系统上的表示。怎么才12M？注意这个文件是 自扩展文件 ，当不够用的时候它会自 己增加文件大小。 当然，如果你想让系统表空间对应文件系统上多个实际文件，或者仅仅觉得原来的 ibdata1 这个文件名 难听，那可以在MySQL启动时配置对应的文件路径以及它们的大小，比如我们这样修改一下my.cnf 配置 文件：
[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend
- 独立表空间(file-per-table tablespace)
- 在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为 每 一个表建立一个独立表空间 ，也就是说我们创建了多少个表，就有多少个独立表空间。使用 独立表空间 来 存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表 名相同，只不过添加了一个 .ibd 的扩展名而已，所以完整的文件名称长这样： 表名.ibd
- 比如：我们使用了 独立表空间 去存储 atguigu 数据库下的 test 表的话，那么在该表所在数据库对应 的 atguigu 目录下会为 test 表创建这两个文件
- test.frm test.ibd
- 其中 test.ibd 文件就用来存储 test 表中的数据和索引。
- 系统表空间与独立表空间的设置 我们可以自己指定使用 系统表空间 还是 独立表空间 来存储数据，这个功能由启动参数
- innodb_file_per_table 控制，比如说我们想刻意将表数据都存储到 系统表空间 时，可以在启动 MySQL服务器的时候这样配置：
- [server] innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间
- 默认情况：
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.01 sec)
1. 其他类型的表空间 随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用 表空间（general tablespace）、临时表空间（temporary tablespace）等。
- MyISAM存储引擎模式
1. 表结构- 在存储表结构方面， MyISAM 和 InnoDB 一样，也是在 数据目录 下对应的数据库子目录下创建了一个专 门用于描述表结构的文件： 表名.frm
2. 表中数据和索引
1. 在MyISAM中的索引全部都是 二级索引 ，该存储引擎的 数据和索引是分开存放 的。所以在文件系统中也是 使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。假如 test 表使用MyISAM存储引擎的话，那么在它所在数据库对应的 atguigu 目录下会为 test 表创建这三个文 件：
1. test.frm 存储表结构 test.MYD 存储数据 (MYData) test.MYI 存储索引 (MYIndex)
3. 举例：创建一个 MyISAM 表，使用 ENGINE 选项显式指定引擎。因为 InnoDB 是默认引擎。
CREATE TABLE `student_myisam` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3;
- 小结
1. 举例： 数据库a ， 表b 。 1、- 如果表b采用 InnoDB ，data\a中会产生1个或者2个文件：
- b.frm ：描述表结构文件，字段长度等
- 如果采用 系统表空间 模式的，数据信息和索引信息都存储在 ibdata1 中
- 如果采用 独立表空间 存储模式，data\a中还会产生
- b.ibd 文件（存储数据信息和索引信息） 此外：
- ① MySQL5.7 中会在data/a的目录下生成 db.opt 文件用于保存数据库的相关配置。比如：字符集、比较 规则。而MySQL8.0不再提供db.opt文件。
- ② MySQL8.0中不再单独提供b.frm，而是合并在b.ibd文件中。
- 如果表b采用 MyISAM ，data\a中会产生3个文件
- MySQL5.7 中：
- b.frm ：描述表结构文件，字段长度等。
- MySQL8.0 中
- b.xxx.sdi ：描述表结构文件，字段长度等
- b.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)
- b.MYI (MYIndex)：存放索引信息文件
- MySQL5.7 中：
- 如果表b采用 InnoDB ，data\a中会产生1个或者2个文件：
用户与权限管理
- 用户管理
- 登录MySQL服务器
1.- 启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下：
mysql –h hostname|hostIP –P port –u username –p DatabaseName –e “SQL语句”
2. 下面详细介绍命令中的参数：
1. -h参数 后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。
2. -P参数 后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是3306， 不使用该参数时自动连接到3306端口，port为连接的端口号。
3. -u参数 后面接用户名，username为用户名。
4. -p参数 会提示输入密码。 DatabaseName参数 指明登录到哪一个数据库中。如果没有该参数，就会直接登录到MySQL数据库 中，然后可以使用USE命令来选择数据库。
5. -e参数 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL 服务器。
3. 举例：
1. mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"
- 创建用户
1. CREATE USER语句的基本语法形式如下：- CREATE USER 用户名 [IDENTIFIED BY ‘密码’][,用户名 [IDENTIFIED BY ‘密码’]];
1. 用户名参数表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成；
2. “[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户 可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用 IDENTIFIED BY指定明文密码值。
3. CREATE USER语句可以同时创建多个用户。
- CREATE USER 用户名 [IDENTIFIED BY ‘密码’][,用户名 [IDENTIFIED BY ‘密码’]];
2. 举例：
1. CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %
2. CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';
- 修改用户
1. 修改用户名：- UPDATE mysql.user SET USER=‘li4’ WHERE USER=‘wang5’;
- FLUSH PRIVILEGES;
- 删除用户
1. 方式1：使用DROP方式删除（推荐）- 使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：
- DROP USER li4 ; # 默认删除host为%的用户
- DROP USER ‘kangshifu’@‘localhost’;
- 使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：
2. 方式2：使用DELETE方式删除
1. DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
2. 执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：
1. FLUSH PRIVILEGES;
3. 举例：
1. DELETE FROM mysql.user WHERE Host='localhost' AND User='Emily'; FLUSH PRIVILEGES;
4. 注意：不推荐通过 DELETE FROM USER u WHERE USER='li4' 进行删除，系统会有残留信息保 留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表 的相应记录都消失了。
2. 这里介绍 推荐的写法 ：
1. 使用ALTER USER命令来修改当前用户密码 用户可以使用ALTER命令来修改自身密码，如下语句代表修 改当前登录用户的密码。基本语法如下：
1. ALTER USER USER() IDENTIFIED BY 'new_password';
2. 使用SET语句来修改当前用户密码 使用root用户登录MySQL后，可以使用SET语句来修改密码，具体 SQL语句如下：
1. SET PASSWORD='new_password';
3. 该语句会自动将密码加密后再赋给当前用户。
- 修改其它用户密码
1. 使用ALTER语句来修改普通用户的密码 可以使用ALTER USER语句来修改普通用户的密码。基本语法形 式如下：- ALTER USER user [IDENTIFIED BY ‘新密码’] [,user[IDENTIFIED BY ‘新密码’]]…;
2. 使用SET命令来修改普通用户的密码 使用root用户登录到MySQL服务器后，可以使用SET语句来修改普 通用户的密码。SET语句的代码如下：
1. SET PASSWORD FOR 'username'@'hostname'='new_password';
3. 使用UPDATE语句修改普通用户的密码（不推荐）
1. UPDATE MySQL.user SET authentication_string=PASSWORD("123456") WHERE User = "username" AND Host = "hostname";
- MySQL8密码管理
1. 密码过期策略- 在MySQL中，数据库管理员可以 手动设置 账号密码过期，也可以建立一个 自动 密码过期策略。
- 过期策略可以是 全局的 ，也可以为 每个账号 设置单独的过期策略。
- ALTER USER user PASSWORD EXPIRE;
2. 练习：
1. ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE;
1. 方式①：使用SQL语句更改该变量的值并持久化
1. SET PERSIST default_password_lifetime = 180; # 建立全局策略，设置密码每隔180天过期
2. 方式②：配置文件my.cnf中进行维护
1. [mysqld] default_password_lifetime=180 #建立全局策略，设置密码每隔180天过期
3. 手动设置指定时间过期方式2：单独设置
1. 每个账号既可延用全局密码过期策略，也可单独设置策略。在 CREATE USER 和 ALTER USER 语句上加 入 PASSWORD EXPIRE 选项可实现单独设置策略。下面是一些语句示例。
#设置kangshifu账号密码每90天过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
#设置密码永不过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
#延用全局密码过期策略：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
- 密码重用策略
1. 手动设置密码重用方式1：全局- 方式①：使用SQL
1. SET PERSIST password_history = 6; #设置不能选择最近使用过的6个密码
2. SET PERSIST password_reuse_interval = 365; #设置不能选择最近一年内的密码
3.
- 方式②：my.cnf配置文件
- 方式①：使用SQL
[mysqld]
password_history=6
password_reuse_interval=365
- 手动设置密码重用方式2：单独设置
#不能使用最近5个密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
#不能使用最近365天内的密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
#既不能使用最近5个密码，也不能使用365天内的密码
CREATE USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
- 权限列表
- 权限列表
1. MySQL到底都有哪些权限呢？- mysql> show privileges;
1. CREATE和DROP权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将 MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
2. SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作。
3. SELECT权限 只有在它们真正从一个表中检索行时才被用到。
4. INDEX权限 允许创建或删除索引，INDEX适用于已 有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
5. ALTER权 限 可以使用ALTER TABLE来更改表的结构和重新命名表。
6. CREATE ROUTINE权限 用来创建保存的 程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限 用来执行保存的 程序。
7. GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序。
8. FILE权限 使用 户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权 限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务 器可以访问这些文件）。
- mysql> show privileges;
- 授予权限的原则
权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：
1. 只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可 以了，不要给用户赋予update、insert或者delete权限。
2. 创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
3. 为每个用户 设置满足密码复杂度的密码 。
4. 定期清理不需要的用户 ，回收权限或者删除用户。
- 授予权限
给用户授权的方式有 2 种，分别是通过把 角色赋予用户给用户授权 和 直接给用户授权 。用户是数据库的 使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全 隐患。
1. 授权命令：
1. GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];
2. 该权限如果发现没有该用户，则会直接新建一个用户。
3. 比如：
1. 给li4用户用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
1. GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost ;
2. 授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包 括grant的权限
1. GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
3. 我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的 分组。
1. 所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据；
2. 所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是 删除。
- 查看权限
1. 查看当前用户权限
SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();
1. 查看某用户的全局权限
1. SHOW GRANTS FOR 'user'@'主机地址' ;
- 收回权限
1. 收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全 性。MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从 db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中 的账户记录使用DROP USER语句）。
2. 注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。- 收回权限命令
- REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
- 举例
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;
注意： 须用户重新登录后才能生效
- 权限表
user表是MySQL中最重要的一个权限表， 记录用户账号和权限信息 ，有49个字段。如下图：
- 范围列（或用户列）
1. host ： 表示连接类型- % 表示所有远程通过 TCP方式的连接
- IP 地址 如 (192.168.1.2、127.0.0.1) 通过制定ip地址进行的TCP方式的连接
- 机器名 通过制定网络中的机器名进行的TCP方式的连接
- ::1 IPv6的本地ip地址，等同于IPv4的 127.0.0.1
- localhost 本地方式通过命令行方式的连接 ，比如mysql -u xxx -p xxx 方式的连接。
2. user ： 表示用户名，同一用户通过不同方式链接的权限是不一样的。
3. password ： 密码
1. 所有密码串通过 password(明文字符串) 生成的密文字符串。MySQL 8.0 在用户管理方面增加了 角色管理，默认的密码加密方式也做了调整，由之前的 SHA1 改为了 SHA2 ，不可逆 。同时 加上 MySQL 5.7 的禁用用户和用户过期的功能，MySQL 在用户管理方面的功能和安全性都较之 前版本大大的增强了。
2. mysql 5.7 及之后版本的密码保存到 authentication_string 字段中不再使用password 字 段。
- 权限列
1. Grant_priv字段- 表示是否拥有GRANT权限
2. Shutdown_priv字段
1. 表示是否拥有停止MySQL服务的权限
3. Super_priv字段
1. 表示是否拥有超级权限
4. Execute_priv字段
1. 表示是否拥有EXECUTE权限。拥有EXECUTE权限，可以执行存储过程和函数。
5. Select_priv , Insert_priv等
1. 为该用户所拥有的权限。
- 安全列
1. 安全列只有6个字段，其中两个是ssl相关的（ssl_type、ssl_cipher），用于 加密 ；两个是x509 相关的（x509_issuer、x509_subject），用于 标识用户 ；另外两个Plugin字段用于 验证用户身份 的插件， 该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。 - 资源控制列
1. 资源控制列的字段用来 限制用户使用的资源 ，包含4个字段，分别为： ①max_questions，用户每小时允许执行的查询操作次数； ②max_updates，用户每小时允许执行的更新 操作次数； ③max_connections，用户每小时允许执行的连接操作次数； ④max_user_connections，用户 允许同时建立的连接次数。
2. 查看字段：- DESC mysql.user;
3. 查看用户, 以列的方式显示数据：
1. SELECT * FROM mysql.user \G;
4. 查询特定字段：
1. SELECT host,user,authentication_string,select_priv,insert_priv,drop_priv FROM mysql.user;
5. 
- db表
1. 使用DESCRIBE查看db表的基本结构：- DESCRIBE mysql.db;
2. 用户列
1. db表用户列有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库 名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。
3. 权限列
1. Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限。
- tables_priv表和columns_priv表
1. tables_priv表用来 对表设置操作权限 ，columns_priv表用来对表的 某一列设置权限 。tables_priv表和 columns_priv表的结构分别如图：
2. desc mysql.tables_priv;
3. tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和 Column_priv，各个字段说明如下：- Host 、 Db 、 User 和 Table_name 四个字段分别表示主机名、数据库名、用户名和表名。
- Grantor表示修改该记录的用户。 Timestamp表示修改该记录的时间。
- Table_priv 表示对象的操作权限。包括Select、Insert、Update、Delete、Create、Drop、Grant、
- References、Index和Alter。
- Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。
4. desc mysql.columns_priv;
- procs_priv表
1. procs_priv表可以对 存储过程和存储函数设置操作权限 ，表结构如图：- desc mysql.procs_priv;
2. 
- 访问控制
- 连接核实阶段
1. 当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确 定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器 接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信 息。
2. 服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受 连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待 用户请求。 - 请求核实阶段
1. 一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请 求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发 挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表。
2. 确认权限时，MySQL首先 检查user表 ，如果指定的权限没有在user表中被授予，那么MySQL就会继续 检 查db表 ，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指 定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续 检查tables_priv表 以 及 columns_priv表 ，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将 返回错 误信息 ，用户请求的操作不能执行，操作失败。 - 提示： MySQL通过向下层级的顺序（从user表到columns_priv表）检查权限表，但并不是所有的权 限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时只 涉及管理权限，因此MySQL只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继 续检查下一层级的表。
- 角色管理
- 角色的理解
1. 引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关 重要的
- 创建角色
1. 创建角色使用 CREATE ROLE 语句，语法如下：- CREATE ROLE ‘role_name’[@‘host_name’] [,‘role_name’[@‘host_name’]]…
2. 角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为 空。
3. 练习：我们现在需要创建一个经理的角色，就可以用下面的代码：
1. CREATE ROLE 'manager'@'localhost';
- 给角色赋予权限
1. 创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是：- GRANT privileges ON table_name TO ‘role_name’[@‘host_name’];
2. 上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称，图11-43 列出了部分权限列表。
1. SHOW PRIVILEGES\G
3. 
4. 练习1：我们现在想给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用下面的代码 来实现：
GRANT SELECT ON demo.settlement TO 'manager';
GRANT SELECT ON demo.goodsmaster TO 'manager';
GRANT SELECT ON demo.invcount TO 'manager';
- 查看角色的权限
1. 赋予角色权限之后，我们可以通过 SHOW GRANTS 语句，来查看权限是否创建成功了：
mysql> SHOW GRANTS FOR 'manager';
+-------------------------------------------------------+
| Grants for manager@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%` |
+-------------------------------------------------------+
只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是 连接登录数据库的权限 。代码的 最后三行代表了我们给角色“manager”赋予的权限，也就是对商品信息表、盘点表和应付账款表的只读权 限。 结果显示，库管角色拥有商品信息表的只读权限和盘点表的增删改查权限。
- 回收角色的权限
1. 角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色 授权相同。撤销角色或角色权限使用REVOKE语句。 修改了角色的权限，会影响拥有该角色的账户的权限。 撤销角色权限的SQL语法如下：- REVOKE privileges ON tablename FROM ‘rolename’;
2. 练习1：撤销school_write角色的权限。 （1）使用如下语句撤销school_write角色的权限。
1. REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';
3. 撤销后使用SHOW语句查看school_write对应的权限，语句如下。
1. SHOW GRANTS FOR 'school_write';
4.
- 删除角色
- 当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角 色。删除角色的操作很简单，你只要掌握语法结构就行了。
1. DROP ROLE role [,role2]… - 注意， 如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限 。
- 练习：执行如下SQL删除角色school_read。
1. DROP ROLE ‘school_read’; - 给用户赋予角色
- 角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。给用户添加角色可使用GRANT语句，语 法形式如下：
1. GRANT role [,role2,…] TO user [,user2,…]; - 在上述语句中，role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。 练习：给kangshifu用户添加角色school_read权限。 （1）使用GRANT语句给kangshifu添加school_read权 限，SQL语句如下。
1. GRANT ‘school_read’ TO ‘kangshifu’@‘localhost’; - 添加完成后使用SHOW语句查看是否添加成功，SQL语句如下。
1. SHOW GRANTS FOR ‘kangshifu’@‘localhost’; - 使用kangshifu用户登录，然后查询当前角色，如果角色未激活，结果将显示NONE。SQL语句如 下。
1. SELECT CURRENT_ROLE();
2.
- 激活角色
- 方式1：使用set default role 命令激活角色
- 举例：
1. SET DEFAULT ROLE ALL TO ‘kangshifu’@‘localhost’; - 举例：使用 SET DEFAULT ROLE 为下面4个用户默认激活所有已拥有的角色如下：
1. SET DEFAULT ROLE ALL TO ‘dev1’@‘localhost’, ‘read_user1’@‘localhost’, ‘read_user2’@‘localhost’, ‘rw_user1’@‘localhost’; - 方式2：将activate_all_roles_on_login设置为ON
1. 默认情况：
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF |
+-----------------------------+-------+
1 row in set (0.00 sec)
设置：
SET GLOBAL activate_all_roles_on_login=ON;
这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有 权限。
- 撤销用户的角色
- 撤销用户角色的SQL语法如下：
1. REVOKE role FROM user; - 练习：撤销kangshifu用户的school_read角色。
- 撤销的SQL语句如下
- REVOKE ‘school_read’ FROM ‘kangshifu’@‘localhost’;
- 撤销后，执行如下查询语句，查看kangshifu用户的角色信息
1. SHOW GRANTS FOR ‘kangshifu’@‘localhost’ - 执行发现，用户kangshifu之前的school_read角色已被撤销。
- 设置强制角色(mandatory role)
- 方式1：服务启动前设置
1. [mysqld] mandatory_roles=‘role1,role2@localhost,r3@%.qinzheng.com’ - 方式2：运行时设置
1. SET PERSIST mandatory_roles = ‘role1,role2@localhost,r3@%.example.com’; #系统重启后仍然 有效 SET GLOBAL mandatory_roles = ‘role1,role2@localhost,r3@%.example.com’; #系统重启后失效
逻辑架构
- 服务器处理客户端请求
- 那服务器进程对客户端进程发送的请求做了什么处理，才能产生最后的处理结果呢？这里以查询请求为 例展示：
1.
2.
- Connectors
- 3 第1层：连接层
- 系统（客户端）访问 MySQL 服务器前，做的第一件事就是建立 TCP 连接。 经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证、权限获取。
1. 用户名或密码不对，会收到一个Access denied for user错误，客户端程序结束执行
2. 用户名密码认证通过，会从权限表查出账号拥有的权限与连接关联，之后的权限判断逻辑，都将依 赖于此时读到的权限
3. TCP 连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后 面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。 - 第2层：服务层
- SQL Interface: SQL接口
1. 接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface
2. MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定 义函数等多种SQL语言接口 - 种SQL语言接口 P
1. 在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构 传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错 误，那么就说明这个SQL语句是不合理的。
2. 在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建 语法树 ，并根据数据字 典丰富查询语法树，会 验证该客户端是否具有执行该查询的权限 。创建好语法树后，MySQL还 会对SQl查询进行语法上的优化，进行查询重写。 - Optimizer: 查询优化器
1. SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划 。
2. 这个执行计划表明应该 使用哪些索引 进行查询（全表检索还是使用索引检索），表之间的连 接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将 查询结果返回给用户。
3. 它使用“ 选取-投影-连接 ”策略进行查询。例如：- SELECT id,name FROM student WHERE gender = ‘女’;
4. 这个SELECT查询先根据WHERE语句进行 选取 ，而不是将表全部查询出来以后再进行gender过 滤。 这个SELECT查询先根据id和name进行属性 投影 ，而不是将属性全部取出以后再进行过 滤，将这两个查询条件 连接 起来生成最终查询结果。
- Caches & Buffers： 查询缓存组
1. MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结 果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过 程了，直接将结果反馈给客户端。
2. 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等 。 这个查询缓存可以在 不同客户端之间共享 。
3. 从MySQL 5.7.20开始，不推荐使用查询缓存，并在 MySQL 8.0中删除 。 - 第3层：引擎层
插件式存储引擎层（ Storage Engines），真正的负责了MySQL中数据的存储和提取，对物理服务器级别 维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样 我们可以根据自己的实际需要进行选取。
MySQL 8.0.25默认支持的存储引擎如下：
- 存储层
- 所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在 文件系统 上，以 文件 的方式存 在的，并完成与存储引擎的交互。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设 备，但现代文件系统的实现使得这样做没有必要了。在文件系统之下，可以使用本地磁盘，可以使用 DAS、NAS、SAN等各种存储系统。
- 小结
- MySQL架构图本节开篇所示。下面为了熟悉SQL执行流程方便，我们可以简化如下：
- 简化为三层结构：
1. 1. 连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
2. 2. SQL 层（服务层）：对 SQL 语句进行查询处理；与数据库文件的存储方式无关；
3. 3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。
SQL执行流程
- MySQL 中的 SQL执行流程
MySQL的查询流程：
- 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没 有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃 了这个功能。
- 大多数情况查询缓存就是个鸡肋，为什么呢？
1. SELECT employee_id,last_name FROM employees WHERE employee_id = 101;
2. 查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在 MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的 鲁棒性大大降 低 ，只有 相同的查询操作才会命中查询缓存 。两个查询请求在任何字符上的不同（例如：空格、注释、 大小写），都会导致缓存不会命中。因此 MySQL 的 查询缓存命中率不高 。
3. 同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、 information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。以某些系统函数 举例，可能同样的函数的两次调用会产生不一样的结果，比如函数 NOW ，每次调用都会产生最新的当前 时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次 查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询 的结果就是错误的！
4. 此外，既然是缓存，那就有它 缓存失效的时候 。MySQL的缓存系统会监测涉及到的每张表，只要该表的 结构或者数据被修改，如对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高 速缓存中删除！对于 更新压力大的数据库 来说，查询缓存的命中率会非常低。 - 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
1.
2. 分析器先做“ 词法分析 ”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面 的字符串分别是什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语 句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。 接着，要做“ 语法分析 ”。根据词法分析的结果，语法分析器（比如：Bison）会根据语法规则，判断你输 入的这个 SQL 语句是否 满足 MySQL 语法 。 select department_id,job_id,avg(salary) from employees group by department_id; 如果SQL语句正确，则会生成一个这样的语法树：
3.
- 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据 全表检索 ，还是根据 索引检索 等。 举例：如下语句是执行两个表的 join：
1. select * from test1 join test2 using(ID) where test1.name=‘zhangwei’ and test2.name=‘mysql高级课程’;
2. 方案1：可以先从表 test1 里面取出 name='zhangwei’的记录的 ID 值，再根据 ID 值关联到表 test2，再判 断 test2 里面 name的值是否等于 ‘mysql高级课程’。 方案2：可以先从表 test2 里面取出 name=‘mysql高级课程’ 的记录的 ID 值，再根据 ID 值关联到 test1， 再判断 test1 里面 name的值是否等于 zhangwei。 这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化 器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。 如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等。后面讲到索引我们再谈。
3. 在查询优化器中，可以分为 逻辑查询 优化阶段和 物理查询 优化阶段。 - 执行器：
1. 截止到现在，还没有真正去读写真实的表，仅仅只是产出了一个执行计划。于是就进入了 执行器阶段 。
2.
3. 在执行之前需要判断该用户是否 具备权限 。如果没有，就会返回权限错误。如果具备权限，就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。- select * from test where id=1;
4. 比如：表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：
1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是1，如果不是则跳过，如果是则将这行存在结果集中； 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
5. 至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。 SQL 语句在 MySQL 中的流程是： SQL语句→查询缓存→解析器→优化器→执行器 。
6. 
- MySQL8中SQL执行原理
- 确认profiling 是否开启
1. mysql> select @@profiling;
2. mysql> show variables like ‘profiling’;
3.
4. profiling=0 代表关闭，我们需要把 profiling 打开，即设置为 1：
5. mysql> set profiling=1 - 多次执行相同SQL查询
1. 然后我们执行一个 SQL 查询（你可以执行任何一个 SQL 查询）：- mysql> select * from employees;
- 查看profiles
1. 查看当前会话所产生的所有 profiles：
2. mysql> show profiles; # 显示最近的几次查询
3.
- 查看profile
1. 显示执行计划，查看程序的执行步骤：
2. mysql> show profile;
3.
4. 当然你也可以查询指定的 Query ID，比如：- mysql> show profile for query 7;
5. 查询 SQL 的执行时间结果和上面是一样的。 此外，还可以查询更丰富的内容：
1. mysql> show profile cpu,block io for query 6;
6. 
7. 继续：
1. mysql> show profile cpu,block io for query 7
8. 
- MySQL5.7中SQL执行原理
上述操作在MySQL5.7中测试，发现前后两次相同的sql语句，执行的查询过程仍然是相同的。不是会使用 缓存吗？这里我们需要 显式开启查询缓存模式 。在MySQL5.7中如下设置：
- 配置文件中开启查询缓存
1. 在 /etc/my.cnf 中新增一行： query_cache_type=1 - 重启mysql服务
1. systemctl restart mysqld - 开启查询执行计划
1. 由于重启过服务，需要重新执行如下指令，开启profiling。
2. mysql> set profiling=1; - 执行语句两次：
1. mysql> select * from locations;
2. mysql> select * from locations; - 查看profiles
1.
- 显示执行计划，查看程序的执行步骤：
1. mysql> show profile for query 1;
\
2. mysql> show profile for query 2;
1. 
3. 结论不言而喻。执行编号2时，比执行编号1时少了很多信息，从截图中可以看出查询语句直接从缓存中 获取数据。
- SQL语法顺序
随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同 而动态调整执行顺序。
需求：查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人，显示人数最多的第一名部门信息 下面是经常出现的查询顺序：
- Oracle中的SQL执行流程
- Oracle 中采用了 共享池 来判断 SQL 语句是否存在缓存和执行计划，通过这一步骤我们可以知道应该采用 硬解析还是软解析。 我们先来看下 SQL 在 Oracle 中的执行过程：
- 从上面这张图中可以看出，SQL 语句在 Oracle 中经历了以下的几个步骤。
1. 语法检查：检查 SQL 拼写是否正确，如果不正确，Oracle 会报语法错误。
2. 语义检查：检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统 就会提示错误。语法检查和语义检查的作用是保证 SQL 语句没有错误。
3. 权限检查：看用户是否具备访问该数据的权限。
4. 共享池检查：共享池（Shared Pool）是一块内存池，最主要的作用是缓存 SQL 语句和该语句的执行计 划。Oracle 通过检查共享池是否存在 SQL 语句的执行计划，来判断进行软解析，还是硬解析。那软解析 和硬解析又该怎么理解呢？- 在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算 ，然后根据 Hash 值在库缓存（Library Cache）中 查找，如果 存在 SQL 语句的执行计划 ，就直接拿来执行，直接进入“执行器”的环节，这就是 软解析 。
- 如果没有找到 SQL 语句和执行计划，Oracle 就需要创建解析树进行解析，生成执行计划，进入“优化器” 这个步骤，这就是 硬解析 。
1. 优化器：优化器中就是要进行硬解析，也就是决定怎么做，比如创建解析树，生成执行计划。
2. 执行器：当有了解析树和执行计划之后，就知道了 SQL 该怎么被执行，这样就可以在执行器中执 行语句了。 - 共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲区等。我们上面已经讲到了库缓存区，它主要 缓存 SQL 语句和执行计划。而 数据字典缓冲区 存储的是 Oracle 中的对象定义，比如表、视图、索引等对 象。当对 SQL 语句进行解析的时候，如果需要相关的数据，会从数据字典缓冲区中提取。
- 库缓存 这一个步骤，决定了 SQL 语句是否需要进行硬解析。为了提升 SQL 的执行效率，我们应该尽量 避免硬解析，因为在 SQL 的执行过程中，创建解析树，生成执行计划是很消耗资源的。
- 你可能会问，如何避免硬解析，尽量使用软解析呢？在 Oracle 中， 绑定变量 是它的一大特色。绑定变量 就是在 SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的执行结果。这样做的好处是能 提升软解 析的可能性 ，不足之处在于可能会导致生成的执行计划不够优化，因此是否需要绑定变量还需要视情况 而定。
- 举个例子，我们可以使用下面的查询语句：
1. SQL> select * from player where player_id = 10001; - 你也可以使用绑定变量，如：
1. SQL> select * from player where player_id = :player_id; - 这两个查询语句的效率在 Oracle 中是完全不同的。如果你在查询 player_id = 10001 之后，还会查询 10002、10003 之类的数据，那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变 量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。
- 因此，我们可以通过使用绑定变量来减少硬解析，减少 Oracle 的解析工作量。但是这种方式也有缺点， 使用动态 SQL 的方式，因为参数不同，会导致 SQL 的执行效率不同，同时 SQL 优化也会比较困难。 Oracle的架构图：
- 小结
- Oracle 和 MySQL 在进行 SQL 的查询上面有软件实现层面的差异。Oracle 提出了共享池的概念，通过共享 池来判断是进行软解析，还是硬解析。
数据库缓冲池(buffer pool)
InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页 面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，而在内存中进行操 作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请 占用内存来作为 数据缓冲池 ，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访 问。
这样做的好处是可以让磁盘活动最小化，从而 减少与磁盘直接进行 I/O 的时间 。要知道，这种策略对提 升 SQL 语句的查询性能来说至关重要。如果索引的数据在缓冲池里，那么访问的成本就会降低很多。
- 缓冲池 vs 查询缓存
缓冲池和查询缓存是一个东西吗？不是。
- 缓冲池（Buffer Pool）
1. 首先我们需要了解在 InnoDB 存储引擎中，缓冲池都包括了哪些。 在 InnoDB 存储引擎中有一部分数据会放到内存中，缓冲池则占了这部分内存的大部分，它用来存储各种 数据的缓存，如下图所示：
- 从图中，你能看到 InnoDB 缓冲池包括了数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典 信息等。
2. 缓存池的重要性：
3. 缓存原则：
1. “ 位置 * 频次 ”这个原则，可以帮我们对 I/O 访问效率进行优化。 首先，位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据。 其次，频次决定优先级顺序。因为缓冲池的大小是有限的，比如磁盘有 200G，但是内存只有 16G，缓冲 池大小只有 1G，就无法将所有数据都加载到缓冲池里，这时就涉及到优先级顺序，会 优先对使用频次高 的热数据进行加载 。
4. 缓冲池的预读特性
- 查询缓存
1. 那么什么是查询缓存呢？- 查询缓存是提前把 查询结果缓存 起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在 MySQL 中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表 发生变化，查询缓存就会失效，因此命中率低。
- 缓冲池如何读取数据
缓冲池管理器会尽量将经常使用的数据保存起来，在数据库进行页面读操作的时候，首先会判断该页面 是否在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面存放到缓冲池中再进 行读取。
- 缓存在数据库中的结构和作用如下图所示：
1.
2. 如果我们执行 SQL 语句的时候更新了缓存池中的数据，那么这些数据会马上同步到磁盘上吗？ - 查看/设置缓冲池的大小
- 如果你使用的是 InnoDB 存储引擎，可以通过查看 innodb_buffer_pool_size 变量来查看缓冲池的大 小。命令如下：
1. show variables like ‘innodb_buffer_pool_size’;
2.
- 你能看到此时 InnoDB 的缓冲池大小只有 134217728/1024/1024=128MB。我们可以修改缓冲池大小，比如 改为256MB，方法如下：
1. set global innodb_buffer_pool_size = 268435456;
2.
- 或者：
1. [server] innodb_buffer_pool_size = 268435456
2. 然后再来看下修改后的缓冲池大小，此时已成功修改成了 256 MB：
3.
- 多个Buffer Pool实例
- [server] innodb_buffer_pool_instances = 2
- 这样就表明我们要创建2个 Buffer Pool 实例。 我们看下如何查看缓冲池的个数，使用命令：
1. show variables like ‘innodb_buffer_pool_instances’;
2.
3. 那每个 Buffer Pool 实例实际占多少内存空间呢？其实使用这个公式算出来的：
4. innodb_buffer_pool_size/innodb_buffer_pool_instances
5. 也就是总共的大小除以实例的个数，结果就是每个 Buffer Pool 实例占用的大小。 - 引申问题
Buffer Pool是MySQL内存结构中十分核心的一个组成，你可以先把它想象成一个黑盒子。
- 黑盒下的更新数据流程
- 我更新到一半突然发生错误了，想要回滚到更新之前的版本，该怎么办？连数据持久化的保证、事务回 滚都做不到还谈什么崩溃恢复？ 答案：Redo Log & Undo Log
存储引擎
查看存储引擎
- 查看mysql提供什么存储引擎：
- show engines;
- show engines \G;
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: N
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
设置系统默认的存储引擎
- 查看默认的存储引擎：
- show variables like ‘%storage_engine%’;
- SELECT @@default_storage_engine;
- 修改默认的存储引擎
- 如果在创建表的语句中没有显式指定表的存储引擎的话，那就会默认使用 InnoDB 作为表的存储引擎。 如果我们想改变表的默认存储引擎的话，可以这样写启动服务器的命令行：
- SET DEFAULT_STORAGE_ENGINE=MyISAM;
- 或者修改 my.cnf 文件：
1. default-storage-engine=MyISAM
2. 重启服务
3. systemctl restart mysqld.service
设置表的存储引擎
存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为 不同的表设置不同的存储引擎 ，也就是 说不同的表可以有不同的物理存储结构，不同的提取和写入方式。
- 创建表时指定存储引擎
- 我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎 InnoDB 。如果我们想显 式的指定一下表的存储引擎，那可以这么写：
1. CREATE TABLE 表名( 建表语句; ) ENGINE = 存储引擎名称; - 修改表的存储引擎
- 如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：
1. ALTER TABLE 表名 ENGINE = 存储引擎名称; - 比如我们修改一下 engine_demo_table 表的存储引擎：
1. mysql> ALTER TABLE engine_demo_table ENGINE = InnoDB; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 - 这时我们再查看一下 engine_demo_table 的表结构：
1. mysql> SHOW CREATE TABLE engine_demo_table\G *************************** 1. row *************************** Table: engine_demo_table Create Table: CREATE TABLEengine_demo_table(iint(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec
引擎介绍
- InnoDB 引擎：具备外键支持功能的事务存储引擎
- MySQL从3.23.34a开始就包含InnoDB存储引擎。 大于等于5.5之后，默认采用InnoDB引擎 。
- InnoDB是MySQL的 默认事务型引擎 ，它被设计用来处理大量的短期(short-lived)事务。可以确保事务 的完整提交(Commit)和回滚(Rollback)。
- 除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
- 除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
- 数据文件结构：（在《第02章_MySQL数据目录》章节已讲） 表名.frm 存储表结构（MySQL8.0时，合并在表名.ibd中） 表名.ibd 存储数据和索引
- InnoDB是 为处理巨大数据量的最大性能设计 。 在以前的版本中，字典数据以元数据文件、非事务表等来存储。现在这些元数据文件被删除 了。比如： .frm ， .par ， .trn ， .isl ， .db.opt 等都在MySQL8.0中不存在了。
- 对比MyISAM的存储引擎， InnoDB写的处理效率差一些 ，并且会占用更多的磁盘空间以保存数据和 索引。
- MyISAM只缓存索引，不缓存真实数据；InnoDB不仅缓存索引还要缓存真实数据， 对内存要求较 高 ，而且内存大小对性能有决定性的影响。
- MyISAM 引擎：主要的非事务处理存储引擎
- MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级 锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复 。
- 5.5之前默认的存储引擎
- 优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、INSERT为主的应用
- 针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高
- 数据文件结构：（在《第02章_MySQL数据目录》章节已讲）
- 表名.frm 存储表结构
- 表名.MYD 存储数据 (MYData)
- 表名.MYI 存储索引 (MYIndex)
- 应用场景：只读应用或者以读为主的业务
- Archive 引擎：用于数据存档
- 下表展示了ARCHIVE 存储引擎功能
1.
- Blackhole 引擎：丢弃写操作，读操作会返回空内容
- CSV 引擎：存储数据时，以逗号分隔各个数据项
- 使用案例如下
mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL) ENGINE = CSV;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+---+------------+
| i | c |
+---+------------+
| 1 | record one |
| 2 | record two |
+---+------------+
2 rows in set (0.00 sec)
- 创建CSV表还会创建相应的 元文件 ，用于 存储表的状态 和 表中存在的行数 。此文件的名称与表的名称相 同，后缀为 CSM 。如图所示
- 如果检查 test.CSV 通过执行上述语句创建的数据库目录中的文件，其内容使用Notepad++打开如下：
- 1",“record one” “2”,“record two”
- Memory 引擎：置于内存的表
- 概述：
1. Memory采用的逻辑介质是 内存 ， 响应速度很快 ，但是当mysqld守护进程崩溃的时候 数据会丢失 。另 外，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。 - 主要特征：
1. Memory同时 支持哈希（HASH）索引 和 B+树索引 。
2. Memory表至少比MyISAM表要 快一个数量级 。
3. MEMORY 表的大小是受到限制 的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默 认为16MB，可以按需要进行扩大。
4. 数据文件与索引文件分开存储。
5. 缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。 - 使用Memory存储引擎的场景：
1. 目标数据比较小 ，而且非常 频繁的进行访问 ，在内存中存放数据，如果太大的数据会造成 内存溢 出 。可以通过参数 max_heap_table_size 控制Memory表的大小，限制Memory表的最大的大 小。
2. 如果 数据是临时的 ，而且 必须立即可用 得到，那么就可以放在内存中。 | 1 | record one | | 2 | record two | ±–±-----------+ 2 rows in set (0.00 sec) “1”,“record one” “2”,“record two”
3. 存储在Memory表中的数据如果突然间 丢失的话也没有太大的关系 。 - Federated 引擎：访问远程表
- Federated引擎是访问其他MySQL服务器的一个 代理 ，尽管该引擎看起来提供了一种很好的 跨服务 器的灵活性 ，但也经常带来问题，因此 默认是禁用的 。
- Merge引擎：管理多个MyISAM表构成的表集合
- NDB引擎：MySQL集群专用存储引擎
也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群 环境，类似于 Oracle 的 RAC 集 群。
- 引擎对比
- MySQL中同一个数据库，不同的表可以选择不同的存储引擎。如下表对常用存储引擎做出了对比。
1.
2.
3. 其实这些东西大家没必要立即就给记住，列出来的目的就是想让大家明白不同的存储引擎支持不同的功 能。 其实我们最常用的就是 InnoDB 和 MyISAM ，有时会提一下 Memory 。其中 InnoDB 是 MySQL 默认的存 储引擎。
MyISAM和InnoDB
- 很多人对 InnoDB 和 MyISAM 的取舍存在疑问，到底选择哪个比较好呢？
- MySQL5.5之前的默认存储引擎是MyISAM，5.5之后改为了InnoDB
阿里巴巴、淘宝用哪个
- Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有很显著的提升。 该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外 有更多的参数和命令来控制服务器行为。 该公司新建了一款存储引擎叫 Xtradb 完全可以替代 Innodb ，并且在性能和并发上做得更好 阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。
课外补充：
- InnoDB表的优势
- InnoDB存储引擎在实际应用中拥有诸多优势，比如操作便利、提高了数据库的性能、维护成本低等。如 果由于硬件或软件的原因导致服务器崩溃，那么在重启服务器之后不需要进行额外的操作。InnoDB崩溃 恢复功能自动将之前提交的内容定型，然后撤销没有提交的进程，重启之后继续从崩溃点开始执行。
- nnoDB存储引擎在主内存中维护缓冲池，高频率使用的数据将在内存中直接被处理。这种缓存方式应用 于多种信息，加速了处理进程。
- 在专用服务器上，物理内存中高达80%的部分被应用于缓冲池。如果需要将数据插入不同的表中，可以 设置外键加强数据的完整性。更新或者删除数据，关联数据将会被自动更新或删除。如果试图将数据插 入从表，但在主表中没有对应的数据，插入的数据将被自动移除。如果磁盘或内存中的数据出现崩溃， 在使用脏数据之前，校验和机制会发出警告。当每个表的主键都设置合理时，与这些列有关的操作会被 自动优化。插入、更新和删除操作通过做改变缓冲自动机制进行优化。 InnoDB不仅支持当前读写，也会 缓冲改变的数据到数据流磁盘 。
- nnoDB的性能优势不只存在于长时运行查询的大型表。在同一列多次被查询时，自适应哈希索引会提高 查询的速度。使用InnoDB可以压缩表和相关的索引，可以 在不影响性能和可用性的情况下创建或删除索 引 。对于大型文本和BLOB数据，使用动态行形式，这种存储布局更高效。通过查询 INFORMATION_SCHEMA库中的表可以监控存储引擎的内部工作。在同一个语句中，InnoDB表可以与其他 存储引擎表混用。即使有些操作系统限制文件大小为2GB，InnoDB仍然可以处理。 当处理大数据量时， InnoDB兼顾CPU，以达到最大性能 。
- InnoDB和ACID模型
- ACID模型是一系列数据库设计规则，这些规则着重强调可靠性，而可靠性对于商业数据和任务关键型应 用非常重要。MySQL包含类似InnoDB存储引擎的组件，与ACID模型紧密相连，这样出现意外时，数据不 会崩溃，结果不会失真。如果依赖ACID模型，可以不使用一致性检查和崩溃恢复机制。如果拥有额外的 软件保护，极可靠的硬件或者应用可以容忍一小部分的数据丢失和不一致，可以将MySQL设置调整为只 依赖部分ACID特性，以达到更高的性能。下面讲解InnoDB存储引擎与ACID模型相同作用的四个方面。
1. 原子方面 ACID的原子方面主要涉及InnoDB事务，与MySQL相关的特性主要包括：- 自动提交设置。
- COMMIT语句。
- ROLLBACK语句。
- 操作INFORMATION_SCHEMA库中的表数据。
2. . 一致性方面 ACID模型的一致性主要涉及保护数据不崩溃的内部InnoDB处理过程，与MySQL相关的特性 主要包括： 、
1. InnoDB双写缓存。
2. InnoDB崩溃恢复。
3. 隔离方面 隔离是应用于事务的级别，与MySQL相关的特性主要包括：
1. 自动提交设置。
2. SET ISOLATION LEVEL语句。 I
3. nnoDB锁的低级别信息。
4. 耐久性方面 ACID模型的耐久性主要涉及与硬件配置相互影响的MySQL软件特性。由于硬件复杂多样 化，耐久性方面没有具体的规则可循。与MySQL相关的特性有：
1. innoDB双写缓存，通过innodb_doublewrite配置项配置。
2. 配置项innodb_flush_log_at_trx_commit。
3. 配置项sync_binlog。
4. 配置项innodb_file_per_table。
5. 存储设备的写入缓存。
6. 存储设备的备用电池缓存。
7. 运行MySQL的操作系统。
8. 持续的电力供应。
9. 备份策略。
10. 对分布式或托管的应用，最主要的在于硬件设备的地点以及网络情况
- InnoDB架构
- 缓冲池
1. 缓冲池是主内存中的一部分空间，用来缓存已使用的表和索引数据。缓冲池使得经常被使用的 数据能够直接在内存中获得，从而提高速度。 - 更改缓存
- 更改缓存是一个特殊的数据结构，当受影响的索引页不在缓存中时，更改缓存会缓存辅助索 引页的更改。索引页被其他读取操作时会加载到缓存池，缓存的更改内容就会被合并。不同于集群索 引，辅助索引并非独一无二的。当系统大部分闲置时，清除操作会定期运行，将更新的索引页刷入磁 盘。更新缓存合并期间，可能会大大降低查询的性能。在内存中，更新缓存占用一部分InnoDB缓冲池。 在磁盘中，更新缓存是系统表空间的一部分。更新缓存的数据类型由innodb_change_buffering配置项管 理。
- 自适应哈希索引
- 自适应哈希索引将负载和足够的内存结合起来，使得InnoDB像内存数据库一样运行， 不需要降低事务上的性能或可靠性。这个特性通过innodb_adaptive_hash_index选项配置，或者通过-- skip-innodb_adaptive_hash_index命令行在服务启动时关闭。 4.
- 重做日志缓存
- 重做日志缓存存放要放入重做日志的数据。重做日志缓存大小通过 innodb_log_buffer_size配置项配置。重做日志缓存会定期地将日志文件刷入磁盘。大型的重做日志缓存 使得大型事务能够正常运行而不需要写入磁盘。
- 系统表空间
- 系统表空间包括InnoDB数据字典、双写缓存、更新缓存和撤销日志，同时也包括表和索引 数据。多表共享，系统表空间被视为共享表空间。
- 双写缓存
- 双写缓存位于系统表空间中，用于写入从缓存池刷新的数据页。只有在刷新并写入双写缓存 后，InnoDB才会将数据页写入合适的位置。
- 撤销日志
- 撤销日志是一系列与事务相关的撤销记录的集合，包含如何撤销事务最近的更改。如果其他 事务要查询原始数据，可以从撤销日志记录中追溯未更改的数据。撤销日志存在于撤销日志片段中，这 些片段包含于回滚片段中。
- 每个表一个文件的表空间
- 每个表一个文件的表空间是指每个单独的表空间创建在自身的数据文件中， 而不是系统表空间中。这个功能通过innodb_file_per_table配置项开启。每个表空间由一个单独的.ibd数 据文件代表，该文件默认被创建在数据库目录中。
- 通用表空间
- 使用CREATE TABLESPACE语法创建共享的InnoDB表空间。通用表空间可以创建在MySQL数 据目录之外能够管理多个表并支持所有行格式的表。
- 撤销表空间
1. 撤销表空间由一个或多个包含撤销日志的文件组成。撤销表空间的数量由 innodb_undo_tablespaces配置项配置。 - 临时表空间
1. 用户创建的临时表空间和基于磁盘的内部临时表都创建于临时表空间。 innodb_temp_data_file_path配置项定义了相关的路径、名称、大小和属性。如果该值为空，默认会在 innodb_data_home_dir变量指定的目录下创建一个自动扩展的数据文件。 - 重做日志
1. 重做日志是基于磁盘的数据结构，在崩溃恢复期间使用，用来纠正数据。正常操作期间， 重做日志会将请求数据进行编码，这些请求会改变InnoDB表数据。遇到意外崩溃后，未完成的更改会自 动在初始化期间重新进行
索引的数据结构
为什么使用索引
- 假如给数据使用 二叉树 这样的数据结构进行存储，如下图所示
索引及其优缺点
- 索引概述
- MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
- 索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构”，满足特定查找算法。 这些数据结构以某种方式指向数据， 这样就可以在这些数据结构的基础上实现 高级查找算法 。
- 优点
- （1）类似大学图书馆建书目索引，提高数据检索的效率，降低 数据库的IO成本 ，这也是创建索引最主 要的原因。
- （2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。
- （3）在实现数据的 参考完整性方面，可以 加速表和表之间的连接 。换句话说，对于有依赖关系的子表和父表联合查询时， 可以提高查询速度。
- （4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时 间 ，降低了CPU的消耗。
- 缺点
- 增加索引也有许多不利的方面，主要表现在如下几个方面：
1. （1）创建索引和维护索引要 耗费时间 ，并 且随着数据量的增加，所耗费的时间也会增加。
2. （2）索引需要占 磁盘空间 ，除了数据表占数据空间之 外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就可能比数据文 件更快达到最大文件尺寸。
3. （3）虽然索引大大提高了查询速度，同时却会 降低更新表的速度 。当对表 中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。 因此，选择使用索引时，需要综合考虑索引的优点和缺点。
InnoDB中索引的推演
- 索引之前的查找
- 先来看一个精确匹配的例子：
1. SELECT [列名列表] FROM 表名 WHERE 列名 = xxx; - 在一个页中的查找
- 在很多页中查找
1. 在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录 所在的页，所以只能 从第一个页 沿着 双向链表 一直往下找，在每一个页中根据我们上面的查找方式去查 找指定的记录。因为要遍历所有的数据页，所以这种方式显然是 超级耗时 的。如果一个表有一亿条记录 呢？此时 索引 应运而生。 - 设计索引
- 建一个表：
mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;
- 这个新建的 index_demo 表中有2个INT类型的列，1个CHAR(1)类型的列，而且我们规定了c1列为主键， 这个表使用 Compact 行格式来实际存储记录的。这里我们简化了index_demo表的行格式示意图：
1.
- 我们只在示意图里展示记录的这几个部分：
1. record_type ：记录头信息的一项属性，表示记录的类型， 0 表示普通记录、 2 表示最小记 录、 3 表示最大记录、 1 暂时还没用过，下面讲。
2. next_record ：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们用 箭头来表明下一条记录是谁。
3. 各个列的值 ：这里只记录在 index_demo 表中的三个列，分别是 c1 、 c2 和 c3 。
4. 其他信息 ：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。 - 将记录格式示意图的其他信息项暂时去掉并把它竖起来的效果就是这样：
1.
- 把一些记录放到页里的示意图就是：
1.
- 一个简单的索引设计方案
- 我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规 律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。所以如果 我们 想快速的定位到需要查找的记录在哪些数据页 中该咋办？我们可以为快速定位记录所在的数据页而 建 立一个目录 ，建这个目录必须完成下边这些事：
1. 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。
2. 给所有的页建立一个目录项。- 所以我们为上边几个页做好的目录就像这样子：
3. 以 页28 为例，它对应 目录项2 ，这个目录项中包含着该页的页号 28 以及该页中用户记录的最小主 键值 5 。我们只需要把几个目录项在物理存储器上连续存储（比如：数组），就可以实现根据主键 值快速查找某条记录的功能了。比如：查找主键值为 20 的记录，具体查找过程分两步：
1. 先从目录项中根据 二分法 快速确定出主键值为 20 的记录在 目录项3 中（因为 12 < 20 < 209 ），它对应的页是 页9 。
2. 2再根据前边说的在页中查找记录的方式去 页9 中定位具体的记录。
4. 至此，针对数据页做的简易目录就搞定了。这个目录有一个别名，称为 索引 。
- InnoDB中的索引方案
- 迭代1次：目录项纪录的页
1. 我们把前边使用到的目录项放到数据页中的样子就是这样
2. 从图中可以看出来，我们新分配了一个编号为30的页来专门存储目录项记录。这里再次强调 目录项记录 和普通的 用户记录 的不同点：
1. 目录项记录 的 record_type 值是1，而 普通用户记录 的 record_type 值是0。
2. 目录项记录只有 主键值和页的编号 两个列，而普通的用户记录的列是用户自己定义的，可能包含 很 多列 ，另外还有InnoDB自己添加的隐藏列。
3. 了解：记录头信息里还有一个叫 min_rec_mask 的属性，只有在存储 目录项记录 的页中的主键值 最小的 目录项记录 的 min_rec_mask 值为 1 ，其他别的记录的 min_rec_mask 值都是 0 。
3. 相同点：两者用的是一样的数据页，都会为主键值生成 Page Directory （页目录），从而在按照主键 值进行查找时可以使用 二分法 来加快查询速度。
4. 现在以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：
1. 先到存储 目录项记录 的页，也就是页30中通过 二分法 快速定位到对应目录项，因为 12 < 20 <209 所以定位到对应的记录所在的页就是页9。
2. 再到存储用户记录的页9中根据 二分法 快速定位到主键值为 20 的用户记录。
- 迭代2次：多个目录项纪录的页
1.
2. 从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页：- 为存储该用户记录而新生成了 页31 。
- 因为原先存储目录项记录的 页30的容量已满 （我们前边假设只能存储4条目录项记录），所以不得 不需要一个新的 页32 来存放 页31 对应的目录项。
3. 现在因为存储目录项记录的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步 骤，以查找主键值为 20 的记录为例：
1. 确定 目录项记录页
2. 我们现在的存储目录项记录的页有两个，即 页30 和 页32 ，又因为页30表示的目录项的主键值的 范围是 [1, 320) ，页32表示的目录项的主键值不小于 320 ，所以主键值为 20 的记录对应的目 录项记录在 页30 中。
3. 通过目录项记录页 确定用户记录真实所在的页
1. 在一个存储 目录项记录 的页中通过主键值定位一条目录项记录的方式说过了。
4. 在真实存储用户记录的页中定位到具体的记录。
- 迭代3次：目录项记录页的目录页
1.
2. 如图，我们生成了一个存储更高级目录项的 页33 ，这个页中的两条记录分别代表页30和页32，如果用 户记录的主键值在 [1, 320) 之间，则到页30中查找更详细的目录项记录，如果主键值 不小于320 的 话，就到页32中查找更详细的目录项记录。
3. 我们可以用下边这个图来描述它：
4. 这个数据结构，它的名称是 B+树 。
1. 一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第 0 层， 之后依次往上加。之前我们做了一个非常极端的假设：存放用户记录的页 最多存放3条记录 ，存放目录项 记录的页 最多存放4条记录 。其实真实环境中一个页存放的记录数量是非常大的，假设所有存放用户记录 的叶子节点代表的数据页可以存放 100条用户记录 ，所有存放目录项记录的内节点代表的数据页可以存 放 1000条目录项记录 ，那么：
1. 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
2. 如果B+树有2层，最多能存放 1000×100=10,0000 条记录。
3. 如果B+树有3层，最多能存放 1000×1000×100=1,0000,0000 条记录。
4. 如果B+树有4层，最多能存放 1000×1000×1000×100=1000,0000,0000 条记录。相当多的记 录！！！
2. 你的表里能存放 100000000000 条记录吗？所以一般情况下，我们 用到的B+树都不会超过4层 ，那我们 通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又 因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过 二分法 实现快速 定位记录。
- 常见索引概念
- 索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和非聚簇（非聚集）索引。我们也把非聚集 索引称为二级索引或者辅助索引。
- 聚簇索引
1. 特点：- 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
1. 页内 的记录是按照主键的大小顺序排成一个 单向链表 。
2. 各个存放 用户记录的页 也是根据页中用户记录的主键大小顺序排成一个 双向链表 。
3. 存放 目录项记录的页 分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键 大小顺序排成一个 双向链表 。
- 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
2. 优点：
1.
1. 数据访问更快 ，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非 聚簇索引更快
2. 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
3. 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多 个数据块中提取数据，所以 节省了大量的io操作 。
3. 缺点：
1.
1. 插入速度严重依赖于插入顺序 ，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影 响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2. 更新主键的代价很高 ，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为 不可更新
3. 二级索引访问需要两次索引查找 ，第一次找到主键值，第二次根据主键值找到行数据
- 二级索引（辅助索引、非聚簇索引）
- 概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根 据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就 是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！
- 问题：为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不OK吗？
1.
- 联合索引
1. 我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按 照 c2和c3列 的大小进行排序，这个包含两层含义：- 先把各个记录和页按照c2列进行排序。
- 在记录的c2列相同的情况下，采用c3列进行排序
2. 注意一点，以c2和c3列的大小为排序规则建立的B+树称为 联合索引 ，本质上也是一个二级索引。它的意 思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：
1. 建立 联合索引 只会建立如上图一样的1棵B+树。
2. 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。
- InnoDB的B+树索引的注意事项
- 根页面位置万年不动
- 内节点中目录项记录的唯一性
- 一个页面最少存储2条记录
MyISAM中的索引方案
- B树索引适用存储引擎如表所示：
- 即使多个存储引擎支持同一种类型的索引，但是他们的实现原理也是不同的。Innodb和MyISAM默认的索 引是Btree索引；而Memory默认的索引是Hash索引。
- MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是 数据记录的地址
- MyISAM索引的原理
- 下图是MyISAM索引的原理图
1.
- 如果我们在Col2上建立一个二级索引，则此索引的结构如下图所示：
1.
- MyISAM 与 InnoDB对比
- MyISAM的索引方式都是“非聚簇”的，与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区 别：
1. ① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中建立的索引相当于全部都是 二级索引 。
2. ② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数 据记录的地址。
3. ③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地址 。换句话说， InnoDB的所有非聚簇索引都引用主键作为data域。
4. ④ MyISAM的回表操作是十分 快速 的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通 过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问。
5. ⑤ InnoDB要求表 必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐 含字段作为主键，这个字段长度为6个字节，类型为长整型。 
索引的代价
索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：
- 空间上的代价
- 每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会 占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。
- 时间上的代价
- 每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每 层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录，还 是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序 而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需 要额外的时间进行一些 记录移位 ， 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果 我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。
MySQL数据结构选择的合理性
- 全表遍历
- Hash结构
- 上图中哈希函数h有可能将两个不同的关键字映射到相同的位置，这叫做 碰撞 ，在数据库中一般采用 链 接法 来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示：
- 实验：体会数组和hash表的查找方面的效率区别
// 算法复杂度为 O(n)
@Test
public void test1(){
int[] arr = new int[100000];
for(int i = 0;i < arr.length;i++){
arr[i] = i + 1;
}
long start = System.currentTimeMillis();
for(int j = 1; j<=100000;j++){
int temp = j;
for(int i = 0;i < arr.length;i++){
if(temp == arr[i]){
break;
}
}
}
long end = System.currentTimeMillis();
System.out.println("time： " + (end - start)); //time： 823
}
//算法复杂度为 O(1)
@Test
public void test2(){
HashSet<Integer> set = new HashSet<>(100000);
for(int i = 0;i < 100000;i++){
set.add(i + 1);
}
long start = System.currentTimeMillis();
for(int j = 1; j<=100000;j++) {
int temp = j;
boolean contains = set.contains(temp);
}
long end = System.currentTimeMillis();
System.out.println("time： " + (end - start)); //time： 5
}
- Hash结构效率高，那为什么索引结构要设计成树型呢？
- Hash索引适用存储引擎如表所示：
1.
- Hash索引的适用性：
1.
- 采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+ 树比较深的时 候，通过自适应 Hash 索引可以明显提高数据的检索效率。
- 我们可以通过 innodb_adaptive_hash_index 变量来查看是否开启了自适应 Hash，比如：
1. mysql> show variables like ‘%adaptive_hash_index’; 
- 二叉搜索树
如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。
- 二叉搜索树的特点
- 查找规则
1.
- 创造出来的二分搜索树如下图所示：
1.
- 为了提高查询效率，就需要 减少磁盘IO数 。为了减少磁盘IO的次数，就需要尽量 降低树的高度 ，需要把 原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好
- AVL树
- 针对同样的数据，如果我们把二叉树改成 M 叉树 （M>2）呢？当 M=3 时，同样的 31 个节点可以由下面 的三叉树来进行存储：
- B-Tree
- 一个 M 阶的 B 树（M>2）有以下的特性：
1. 1. 根节点的儿子数的范围是 [2,M]。
2. 2. 每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为 [ceil(M/2), M]。
3. 3. 叶子节点包括 k-1 个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。
4. 4. 假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i]
5. 所有叶子节点位于同一层。 - 上面那张图所表示的 B 树就是一棵 3 阶的 B 树。我们可以看下磁盘块 2，里面的关键字为（8，12），它 有 3 个孩子 (3，5)，(9，10) 和 (13，15)，你能看到 (3，5) 小于 8，(9，10) 在 8 和 12 之间，而 (13，15) 大于 12，刚好符合刚才我们给出的特征。
- 然后我们来看下如何用 B 树进行查找。假设我们想要 查找的关键字是 9 ，那么步骤可以分为以下几步：
1. 1. 我们与根节点的关键字 (17，35）进行比较，9 小于 17 那么得到指针 P1；
2. 2. 按照指针 P1 找到磁盘块 2，关键字为（8，12），因为 9 在 8 和 12 之间，所以我们得到指针 P2；
3. 3. 按照指针 P2 找到磁盘块 6，关键字为（9，10），然后我们找到了关键字 9。 - 你能看出来在 B 树的搜索过程中，我们比较的次数并不少，但如果把数据读取出来然后在内存中进行比 较，这个时间就是可以忽略不计的。而读取磁盘块本身需要进行 I/O 操作，消耗的时间比在内存中进行 比较所需要的时间要多，是数据查找用时的重要因素。 B 树相比于平衡二叉树来说磁盘 I/O 操作要少 ， 在数据查询中比平衡二叉树效率要高。所以 只要树的高度足够低，IO次数足够少，就可以提高查询性能 。
- B+Tree
- MySQL官网说明：
1.
- B+ 树和 B 树的差异：
1. 有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数 +1。 索引 / 存储引擎 MyISAM InnoDB Memory R-Tree索引 支持 支持 不支持
2. 非叶子节点的关键字也会同时存在在子节点中，并且是在子节点中所有关键字的最大（或最 小）。
3. 非叶子节点仅用于索引，不保存数据记录，跟记录有关的信息都放在叶子节点中。而 B 树中， 非 叶子节点既保存索引，也保存数据记录 。
4. 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大 小从小到大顺序链接。
5. B 树和 B+ 树都可以作为索引的数据结构，在 MySQL 中采用的是 B+ 树。 但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然。
6. 思考题：为了减少IO，索引树会一次性加载吗？ 、
7. 思考题：B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次磁盘IO 、
8. 考题：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？ 、
9. 思考题：Hash 索引与 B+ 树索引的区别
10. 思考题：Hash 索引与 B+ 树索引是在建索引的时候手动指定的吗？ - R树
- R-Tree在MySQL很少使用，仅支持 geometry数据类型 ，支持该类型的存储引擎只有myisam、bdb、 innodb、ndb、archive几种。举个R树在现实领域中能够解决的例子：查找20英里以内所有的餐厅。如果 没有R树你会怎么解决？一般情况下我们会把餐厅的坐标(x,y)分为两个字段存放在数据库中，一个字段记 录经度，另一个字段记录纬度。这样的话我们就需要遍历所有的餐厅获取其位置信息，然后计算是否满 足要求。如果一个地区有100家餐厅的话，我们就要进行100次位置计算操作了，如果应用到谷歌、百度 地图这种超大数据库中，这种方法便必定不可行了。R树就很好的 解决了这种高维空间搜索问题 。它把B 树的思想很好的扩展到了多维空间，采用了B树分割空间的思想，并在添加、删除操作时采用合并、分解 结点的方法，保证树的平衡性。因此，R树就是一棵用来 存储高维数据的平衡树 。相对于B-Tree，R-Tree 的优势在于范围查找。
- ** 算法的时间复杂度 **
- ** 同一问题可用不同算法解决，而一个算法的质量优劣将影响到算法乃至程序的效率。算法分析的目的在 于选择合适算法和改进算法 **
InnoDB的数据存储结构
数据页内部结构
-
- File Header（文件头部）（38字节）
- 作用：描述各种页的通用信息。（比如页的编号、其上一页、下一页是谁等） 大小：38字节 构成：
- FIL_PAGE_OFFSET（4字节）
- 每一个页都有一个单独的页号，就跟你的身份证号码一样，InnoDB通过页号可以唯一定位一个页
- FIL_PAGE_TYPE（2字节）
- 这个代表当前页的类型。
- FIL_PAGE_PREV（4字节）和FIL_PAGE_NEXT（4字节
- InnoDB都是以页为单位存放数据的，如果数据分散到多个不连续的页中存储的话需要把这些页关联起来，FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了，保证这些页之间不需要是物理上的连续，而是逻辑上的连续
- FIL_PAGE_SPACE_OR_CHKSUM（4字节）
- 代表当前页面的校验和（checksum）。 什么是校验和？就是对于一个很长的字节串来说，我们会通过某种算法来计算一个比较短的值来代表这个很长的字节串，这个比较短的值就称为校验和。在比较两个很长的字节串之前，先比较这两个长字节串的校验和，如果校验和都不一样，则两个长字节串肯定是不同的，所以省去了直接比较两个比较长的字节串的时间损耗。 文件头部和文件尾部都有属性：FIL_PAGE_SPACE_OR_CHKSUM 作用：InnoDB存储引擎以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。但是在同步了一半的时候断电了，造成了该页传输的不完整。为了检测一个页是否完整（也就是在同步的时候有没有发生只同步一半的尴尬情况），这时可以通过文件尾的校验和（checksum 值）与文件头的校验和做比对，如果两个值不相等则证明页的传输有问题，需要重新进行传输，否则认为页的传输已经完成。具体的：每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为File Header在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。如果写了一半儿断电了，那么在File Header中的校验和就代表着已经修改过的页，而在File Trailer中的校验和代表着原先的页，二者不同则意味着同步中间出了错。这里，校验方式就是采用 Hash 算法进行校验。
- FIL_PAGE_LSN（8字节）
- 页面被最后修改时对应的日志序列位置（英文名是：Log Sequence Number）
- File Trailer（文件尾部）（8字节）
- 前4个字节代表页的校验和：这个部分是和File Header中的校验和相对应的。 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）：这个部分也是为了校验页的完整性的，如果首部和尾部的LSN值校验不成功的话，就说明同步过程出现了问题。
- File Header（文件头部）（38字节）
-
- Free Space (空闲空间)
- 我们自己存储的记录会按照指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。
- User Records (用户记录)
- User Records中的这些记录按照指定的行格式一条一条摆在User Records部分，相互之间形成单链表。 用户记录里的一条条数据如何记录？这里需要讲讲记录行格式的记录头信息。
- Infimum + Supremum（最小最大记录）
- 记录可以比较大小吗？是的，记录可以比大小，对于一条完整的记录来说，比较记录的大小就是比较主键的大小。比方说我们插入的4行记录的主键值分别是：1、2、3、4，这也就意味着这4条记录是从小到大依次递增。 InnoDB规定的最小记录与最大记录这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的，如图所示： 这两条记录不是我们自己定义的记录，所以它们并不存放在页的User Records部分，他们被单独放在一个称为Infimum + Supremum的部分，如图所示：
-
- Page Directory（页目录）
- 为什么需要页目录？在页中，记录是以单向链表的形式进行存储的。单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此在页结构中专门设计了页目录这个模块，专门给记录做一个目录，通过二分查找法的方式进行检索，提升效率。 需求：根据主键值查找页中的某条记录，如何实现快速查找呢？SELECT * FROM page_demo WHERE c1 = 3;方式1：顺序查找从Infimum记录（最小记录）开始，沿着链表一直往后找，总有一天会找到（或者找不到），在找的时候还能投机取巧，因为链表中各个记录的值是按照从小到大顺序排列的，所以当链表的某个节点代表的记录的主键值大于你想要查找的主键值时，你就可以停止查找了，因为该节点后边的节点的主键值依次递增。 如果一个页中存储了非常多的记录，这么查找性能很差。 方式2：使用页目录，二分法查找1. 将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。2. 第 1 组，也就是最小记录所在的分组只有 1 个记录； 最后一组，就是最大记录所在的分组，会有 1-8 条记录； 其余的组记录数量在 4-8 条之间。这样做的好处是，除了第 1 组（最小记录所在组）以外，其余组的记录数会尽量平分。3. 在每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。4. 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。 举例1：举例2：现在的page_demo表中正常的记录共有6条，InnoDB会把它们分成两组，第一组中只有一个最小记录，第二组中是剩余的5条记录。如下图： 从这个图中我们需要注意这么几点：现在页目录部分中有两个槽，也就意味着我们的记录被分成了两个组，槽1中的值是112，代表最大记录的地址偏移量（就是从页面的0字节开始数，数112个字节）；槽0中的值是99，代表最小记录的地址偏移量。注意最小和最大记录的头信息中的n_owned属性最小记录的n_owned值为1，这就代表着以最小记录结尾的这个分组中只有1条记录，也就是最小记录本身。最大记录的n_owned值为5，这就代表着以最大记录结尾的这个分组中只有5条记录，包括最大记录本身还有我们自己插入的4条记录。 用箭头指向的方式替代数字，这样更易于我们理解，修改后如下：再换个角度看一下：（单纯从逻辑上看一下这些记录和页目录的关系）
- 页目录分组的个数如何确定？
- 为什么最小记录的n_owned值为1，而最大记录的n_owned值为5呢？ InnoDB规定：对于最小记录所在的分组只能有1条记录，最大记录所在的分组拥有的记录条数只能在1~8条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。 分组是按照下边的步骤进行的：初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。
- 页目录结构下如何快速查找记录？
- 现在向page_demo表中添加更多的数据。如下：INSERT INTO page_demo VALUES(5, 500, ‘zhou’), (6, 600, ‘chen’), (7, 700, ‘deng’), (8, 800, ‘yang’), (9, 900, ‘wang’), (10, 1000, ‘zhao’), (11, 1100, ‘qian’), (12, 1200, ‘feng’), (13, 1300, ‘tang’), (14, 1400, ‘ding’), (15, 1500, ‘jing’), (16, 1600, ‘quan’);添加了12条记录，现在页里一共有18条记录了（包括最小和最大记录），这些记录被分成了5个组，如图所示：这里只保留了16条记录的记录头信息中的n_owned和next_record属性，省略了各个记录之间的箭头。现在看怎么从这个页目录中查找记录。因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用二分法来进行快速查找。5个槽的编号分别是：0、1、2、3、4，所以初始情况下最低的槽就是low=0，最高的槽就是high=4。比方说我们想找主键值为6的记录，过程是这样的： 1. 计算中间槽的位置：(0+4)/2=2，所以查看槽2对应记录的主键值为8，又因为8 > 6，所以设置high=2，low保持不变。2. 重新计算中间槽的位置：(0+2)/2=1，所以查看槽1对应的主键值为4，又因为4 < 6，所以设置low=1，high保持不变。3. 因为high - low的值为1，所以确定主键值为6的记录在槽2对应的组中。此刻我们需要找到槽2中主键值最小的那条记录，然后沿着单向链表遍历槽2中的记录。但是我们前边又说过，每个槽对应的记录都是该组中主键值最大的记录，这里槽2对应的记录是主键值为8的记录，怎么定位一个组中最小的记录呢？别忘了各个槽都是挨着的，我们可以很轻易的拿到槽1对应的记录（主键值为4），该条记录的下一条记录就是槽2中主键值最小的记录，该记录的主键值为5。所以我们可以从这条主键值为5的记录出发，遍历槽2中的各条记录，直到找到主键值为6的那条记录即可。由于一个组中包含的记录条数只能是1~8条，所以遍历一个组中的记录的代价是很小的。 小结：在一个数据页中查找指定主键值的记录的过程分为两步：1. 通过二分法确定该记录所在的槽，并找到该槽所在分组中主键值最小的那条记录。2. 通过记录的next_record属性遍历该槽所在的组中的各个记录。
- Page Header（页面头部）
- 为了能得到一个数据页中存储的记录的状态信息，比如本页中已经存储了多少条记录，第一条记录的地址是什么，页目录中存储了多少个槽等等，特意在页中定义了一个叫Page Header的部分，这个部分占用固定的56个字节，专门存储各种状态信息。
- PAGE_DIRECTION
- 假如新插入的一条记录的主键值比上一条记录的主键值大，我们说这条记录的插入方向是右边，反之则是左边。用来表示最后一条记录插入方向的状态就是PAGE_DIRECTION。
- PAGE_N_DIRECTION
- 假设连续几次插入新记录的方向都是一致的，InnoDB会把沿着同一个方向插入记录的条数记下来，这个条数就用PAGE_N_DIRECTION这个状态表示。当然，如果最后一条记录的插入方向改变了的话，这个状态的值会被清零重新统计。
InnoDB行格式（或记录格式）
- 指定行格式的语法
- 在创建或修改表的语句中指定行格式：CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称ALTER TABLE 表名 ROW_FORMAT=行格式名称 举例：mysql> CREATE TABLE record_test_table ( -> col1 VARCHAR(8), -> col2 VARCHAR(8) NOT NULL, -> col3 CHAR(8), -> col4 VARCHAR(8) -> ) CHARSET=ascii ROW_FORMAT=COMPACT;Query OK, 0 rows affected (0.03 sec) 向表中插入两条记录：INSERT INTO record_test_table(col1, col2, col3, col4) VALUES(‘zhangsan’, ‘lisi’, ‘wangwu’, ‘songhk’), (‘tong’, ‘chen’, NULL, NULL);
- COMPACT行格式
- 在MySQL 5.1版本中，默认设置为Compact行格式。一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。
- 变长字段长度列表
- MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。 注意：这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10)，b(15)。那么在变长字段长度列表中存储的长度顺序就是15，10，是反过来的。 以record_test_table表中的第一条记录举例：因为record_test_table表的col1、col2、col4列都是VARCHAR(8)类型的，所以这三个列的值的长度都需要保存在记录开头处，注意record_test_table表中的各个列都使用的是ascii字符集（每个字符只需要1个字节来进行编码）。 又因为这些长度值需要按照列的逆序存放，所以最后变长字段长度列表的字节串用十六进制表示的效果就是（各个字节之间实际上没有空格，用空格隔开只是方便理解）：06 04 08 把这个字节串组成的变长字段长度列表填入上边的示意图中的效果就是：
- NULL值列表
- Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。为什么定义NULL值列表？之所以要存储NULL是因为数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据，格式如下： 1. 二进制位的值为1时，代表该列的值为NULL。2. 二进制位的值为0时，代表该列的值不为NULL。 例如：字段 a、b、c，其中a是主键，在某一行中存储的数依次是 a=1、b=null、c=2。那么Compact行格式中的NULL值列表中存储：01。第一个0表示c不为null，第二个1表示b是null。这里之所以没有a是因为数据库会自动跳过主键，因为主键肯定是非NULL且唯一的，在NULL值列表的数据中就会自动跳过主键。 record_test_table的两条记录的NULL值列表就如下： 第一条记录：第二条记录：
- 记录头信息（5字节）
- mysql> CREATE TABLE page_demo( -> c1 INT, -> c2 INT, -> c3 VARCHAR(10000), -> PRIMARY KEY (c1) -> ) CHARSET=ascii ROW_FORMAT=Compact;Query OK, 0 rows affected (0.03 sec) 这个表中记录的行格式示意图：这些记录头信息中各个属性如下：简化后的行格式示意图：插入数据：INSERT INTO page_demo VALUES(1, 100, ‘song’), (2, 200, ‘tong’), (3, 300, ‘zhan’), (4, 400, ‘lisi’);图示如下：
- delete_mask
- 这个属性标记着当前记录是否被删除，占用1个二进制位。值为0：代表记录并没有被删除值为1：代表记录被删除掉了 被删除的记录为什么还在页中存储呢？你以为它删除了，可它还在真实的磁盘上。这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后其他的记录在磁盘上需要重新排列，导致性能消耗。所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。
- min_rec_mask
- B+树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask值为1。我们自己插入的四条记录的min_rec_mask值都是0，意味着它们都不是B+树的非叶子节点中的最小记录。
- record_type
- 这个属性表示当前记录的类型，一共有4种类型的记录： 0：表示普通记录 1：表示B+树非叶节点记录 2：表示最小记录 3：表示最大记录 从图中我们也可以看出来，我们自己插入的记录就是普通记录，它们的record_type值都是0，而最小记录和最大记录的record_type值分别为2和3。至于record_type为1的情况，我们在索引的数据结构章节讲过。
- heap_no
- 这个属性表示当前记录在本页中的位置。 从图中可以看出来，我们插入的4条记录在本页中的位置分别是：2、3、4、5。 怎么不见heap_no值为0和1的记录呢？MySQL会自动给每个页里加了两个记录，由于这两个记录并不是我们自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。最小记录和最大记录的heap_no值分别是0和1，也就是说它们的位置最靠前。
- n_owned
- 页目录中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段。详情见page directory。
- next_record
- 记录头信息里该属性非常重要，它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比如：第一条记录的next_record值为32，意味着从第一条记录的真实数据的地址处向后找32个字节便是下一条记录的真实数据。注意，下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定Infimum记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）。下图用箭头代替偏移量表示next_record。
- 演示：删除操作
- 删除操作：从表中删除掉一条记录，这个链表也是会跟着变化：mysql> DELETE FROM page_demo WHERE c1 = 2;Query OK, 1 row affected (0.02 sec)删掉第2条记录后的示意图就是： 从图中可以看出来，删除第2条记录前后主要发生了这些变化：- 第2条记录并没有从存储空间中移除，而是把该条记录的delete_mask值设置为1。- 第2条记录的next_record值变为了0，意味着该记录没有下一条记录了。- 第1条记录的next_record指向了第3条记录。- 最大记录的n_owned值从 5 变成了 4 。 所以，不论我们怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的。
- 演示：添加操作
- 添加操作：主键值为2的记录被我们删掉了，但是存储空间却没有回收，如果我们再次把这条记录插入到表中，会发生什么事呢？mysql> INSERT INTO page_demo VALUES(2, 200, ‘tong’);Query OK, 1 row affected (0.00 sec) 我们看一下记录的存储情况： 直接复用了原来被删除记录的存储空间。 说明：当数据页中存在多条被删除掉的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表，以备之后重用这部分存储空间。
- 记录的真实数据
- 记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列： 实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。事务ID和回滚指针在后面的《第14章_MySQL事务日志》章节中讲解。 举例：分析Compact行记录的内部结构：CREATE TABLE mytest(col1 VARCHAR(10),col2 VARCHAR(10),col3 CHAR(10),col4 VARCHAR(10))ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT; INSERT INTO mytestVALUES(‘a’,‘bb’,‘bb’,‘ccc’); INSERT INTO mytestVALUES(‘d’,‘ee’,‘ee’,‘fff’); INSERT INTO mytestVALUES(‘d’,NULL,NULL,‘fff’); 在Windows操作系统下，可以选择通过程序UltraEdit打开表空间文件mytest.ibd这个二进制文件。内容如下：------------------------------------------------------------------------------------------0000c070 73 75 70 72 65 6d 75 6d 03 02 01 00 00 00 10 00|supremum…|0000c080 2c 00 00 00 2b 68 00 00 00 00 00 06 05 80 00 00|,…+h…|0000c090 00 32 01 10 61 62 62 62 62 20 20 20 20 20 20 20|.2…abbbb|0000c0a0 20 63 63 63 03 02 01 00 00 00 18 00 2b 00 00 00|ccc…+…|0000c0b0 2b 68 01 00 00 00 00 06 06 80 00 00 00 32 01 10|+h…2…|0000c0c0 64 65 65 65 65 20 20 20 20 20 20 20 20 66 66 66|deeeefff|0000c0d0 03 01 06 00 00 20 ff 98 00 00 00 2b 68 02 00 00|…+h…|0000c0e0 00 00 06 07 80 00 00 00 32 01 10 64 66 66 66 00|…2…dfff.|------------------------------------------------------------------------------------------该行记录从0000c078开始，若整理一下，相信大家会有更好的理解：---------------------------------------------------------------------03 02 01 /变长字段长度列表，逆序/00 /NULL标志位，第一行没有NULL值/00 00 10 00 2c /Record Header，固定5字节长度/00 00 00 2b 68 00 /RowID InnoDB自动创建，6字节/00 00 00 00 06 05 /TransactionID/80 00 00 00 32 01 10 /Roll Pointer/61 /列1数据’a’/62 62 /列2数据’bb’/62 62 20 20 20 20 20 20 20 20/列3数据’bb’/63 63 63 /列4数据’ccc’/---------------------------------------------------------------------注意1：InnoDB每行有隐藏列TransactionID和Roll Pointer。:注意2：固定长度CHAR字段在未能完全占用其长度空间时，会用0x20来进行填充。 接着再来分析下Record Header的最后两个字节，这两个字节代表next_recorder，0x2c代表下一个记录的偏移量，即当前记录的位置加上偏移量0x2c就是下条记录的起始位置。 第二行将不做整理，除了RowID不同外，它和第一行大同小异，现在来分析有NULL值的第三行：---------------------------------------------------------------------03 01 /变长字段长度列表，逆序/06 /NULL标志位，第三行有NULL值/00 00 20 ff 98 /Record Header/00 00 00 2b 68 02 /RowID/00 00 00 00 06 07 /TransactionID/80 00 00 00 32 01 10 /Roll Pointer/64 /列1数据’d’/66 66 66 /列4数据’fff’/---------------------------------------------------------------------第三行有NULL值，因此NULL标志位不再是00而是06，转换成二进制为00000110，为1的值代表第2列和第3列的数据为NULL。在其后存储列数据的部分，用户会发现没有存储NULL列，而只存储了第1列和第4列非NULL的值。因此这个例子很好地说明了：不管是CHAR类型还是VARCHAR类型，在compact格式下NULL值都不占用任何存储空间。
- 变长字段长度列表
- Dynamic和Compressed行格式
- 行溢出
- InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。 很多DBA喜欢MySQL数据库提供的VARCHAR(M)类型，认为可以存放65535字节。这是真的吗？如果我们使用 ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用。CREATE TABLE varchar_size_demo( c VARCHAR(65535) ) CHARSET=ascii ROW_FORMAT=Compact;结果如下：ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 报错信息表达的意思是：MySQL对一条记录占用的最大存储空间是有限制的，除BLOB或者TEXT类型的列之外， 其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。 这个65535个字节除了列本身的数据之外，还包括一些其他的数据，以Compact行格式为例，比如说我们为了存储一个VARCHAR(M)类型的列，除了真实数据占有空间以外，还需要记录的额外信息。 如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为变长字段的长度占用 2个字节，NULL值标识需要占用1个字节。CREATE TABLE varchar_size_demo( c VARCHAR(65532)) CHARSET=ascii ROW_FORMAT=Compact; #如果有not null属性，那么就不需要NULL值标识，也就可以多存储一个字节，即65533个字节CREATE TABLE varchar_size_demo( c VARCHAR(65533) not null) CHARSET=ascii ROW_FORMAT=Compact; 通过上面的案例，我们可以知道一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录，这种现象称为行溢出。 在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中进行分页存储，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。这称为页的扩展，举例如下：
- Dynamic和Compressed行格式
- 在MySQL 8.0中，默认行格式就是Dynamic，Dynamic、Compressed行格式和Compact行格式挺像，只不过在处理行溢出数据时有分歧： Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。如图，在数据页中只存放20个字节的指针（溢出页的地址），实际的数据都存放在Off Page（溢出页）中。Compact和Redundant两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）。 Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储。
- Redundant行格式
- Redundant是MySQL 5.0版本之前InnoDB的行记录存储方式，MySQL 5.0支持Redundant是为了兼容之前版本的页格式。 现在我们把表record_test_table的行格式修改为Redundant：ALTER TABLE record_test_table ROW_FORMAT=Redundant;Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0从上图可以看到，不同于Compact行记录格式，Redundant行格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序放置的。 下边我们从各个方面看一下Redundant行格式有什么不同的地方。
- 字段长度偏移列表
- 注意Compact行格式的开头是变长字段长度列表，而Redundant行格式的开头是字段长度偏移列表，与变长字段长度列表有两处不同： 少了“变长”两个字：Redundant行格式会把该条记录中所有列（包括隐藏列）的长度信息都按照逆序存储到字段长度偏移列表。多了“偏移”两个字：这意味着计算列值长度的方式不像Compact行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。 举例：比如第一条记录的字段长度偏移列表就是：2B 25 1F 1B 13 0C 06 因为它是逆序排放的，所以按照列的顺序排列就是：06 0C 13 17 1A 24 25按照两个相邻数值的差值来计算各个列值的长度的意思就是： 第一列(row_id)的长度就是 0x06个字节，也就是6个字节。第二列(transaction_id)的长度就是 (0x0C - 0x06)个字节，也就是6个字节。第三列(roll_pointer)的长度就是 (0x13 - 0x0C)个字节，也就是7个字节。第四列(col1)的长度就是 (0x1B - 0x13)个字节，也就是8个字节。第五列(col2)的长度就是 (0x1F - 0x1B)个字节，也就是4个字节。第六列(col3)的长度就是 (0x25 - 0x1F)个字节，也就是6个字节。第七列(col4)的长度就是 (0x2B - 0x25)个字节，也就是6个字节。
- 记录头信息（record header）
- 不同于Compact行格式，Redundant行格式中的记录头信息固定占用6个字节（48位），每位的含义见下表。 与Compact行格式的记录头信息对比来看，有两处不同：Redundant行格式多了n_field和1byte_offs_flag这两个属性。Redundant行格式没有record_type这个属性。 其中，n_fields：代表一行中列的数量，占用10位，这也很好地解释了为什么MySQL一个行支持最多的列为1023。另一个值为1byte_offs_flags，该值定义了偏移列表占用1个字节还是2个字节。当它的值为1时，表明使用1个字节存储。当它的值为0时，表明使用2个字节存储。1byte_offs_flag的值是怎么选择的我们前边说过每个列对应的偏移量可以占用1个字节或者2个字节来存储，那到底什么时候用1个字节，什么时候用2个字节呢？其实是根据该条Redundant行格式记录的真实数据占用的总大小来判断的：当记录的真实数据占用的字节数值不大于127（十六进制0x7F，二进制01111111）时，每个列对应的偏移量占用1个字节。当记录的真实数据占用的字节数大于127，但不大于32767（十六进制0x7FFF，二进制0111111111111111）时，每个列对应的偏移量占用2个字节。 有没有记录的真实数据大于32767的情况呢？有，不过此时的记录已经存放到了溢出页中，在本页中只保留前768个字节和20个字节的溢出页面地址。因为字段长度偏移列表处只需要记录每个列在本页面中的偏移就好了，所以每个列使用2个字节来存储偏移量就够了。大家可以看出来，Redundant行格式还是比较简单粗暴的，直接使用整个记录的真实数据长度来决定使用1个字节还是2个字节存储列对应的偏移量。只要整条记录的真实数据占用的存储空间大小大于127，即使第一个列的值占用存储空间小于127，那对不起，也需要使用2个字节来表示该列对应的偏移量。简单粗暴，就是这么简单粗暴（所以这种行格式有些过时了）。为了在解析记录时知道每个列的偏移量是使用1个字节还是2个字节表示的，Redundant行格式特意在
记录头信息里放置了一个称之为1byte_offs_flag的属性：Redundant行格式中NULL值的处理因为Redundant行格式并没有NULL值列表，所以Redundant行格式在字段长度偏移列表中的各个列对应的偏移量处做了一些特殊处理 —— 将列对应的偏移量值的第一个比特位作为是否为NULL的依据，该比特位也可以被称之为NULL比特位。也就是说在解析一条记录的某个列时，首先看一下该列对应的偏移量的NULL比特位是不是为1。如果为1，那么该列的值就是NULL，否则不是NULL。这也就解释了上边介绍为什么只要记录的真实数据大于127（十六进制0x7F，二进制01111111）时，就采用2个字节来表示一个列对应的偏移量，主要是第一个比特位是所谓的NULL比特位，用来标记该列的值是否为NULL。但是还有一点要注意，对于值为NULL的列来说，该列的类型是否为定长类型决定了NULL值的实际存储方式，我们接下来分析一下record_test_table表的第二条记录，它对应的字段长度偏移列表如下： A4 A4 1A 17 13 0C 06按照列的顺序排放就是：06 0C 13 17 1A A4 A4我们分情况看一下：如果存储NULL值的字段是定长类型的，比方说CHAR(M)数据类型的，则NULL值也将占用记录的真实数据部分，并把该字段对应的数据使用0x00字节填充。如图第二条记录的c3列的值是NULL，而c3列的类型是CHAR(10)，占用记录的真实数据部分10字节，所以我们看到在Redundant行格式中使用0x00000000000000000000来表示NULL值。另外，c3列对应的偏移量为0xA4，它对应的二进制实际是：10100100，可以看到最高位为1，意味着该列的值是NULL。将最高位去掉后的值变成了0100100，对应的十进制值为36，而c2列对应的偏移量为0x1A，也就是十进制的26。36 - 26 = 10，也就是说最终c3列占用的存储空间为10个字节。如果该存储NULL值的字段是变长数据类型的，则不在记录的真实数据处占用任何存储空间。比如record_test_table表的c4列是VARCHAR(10)类型的，VARCHAR(10)是一个变长数据类型，c4列对应的偏移量为0xA4，与c3列对应的偏移量相同，这也就意味着它的值也为NULL，将0xA4的最高位去掉后对应的十进制值也是36，36 - 36 = 0，也就意味着c4列本身不占用任何记录的实际数据处的空间。 除了以上的几点之外，Redundant行格式和Compact行格式还是大致相同的。
- 不同于Compact行格式，Redundant行格式中的记录头信息固定占用6个字节（48位），每位的含义见下表。 与Compact行格式的记录头信息对比来看，有两处不同：Redundant行格式多了n_field和1byte_offs_flag这两个属性。Redundant行格式没有record_type这个属性。 其中，n_fields：代表一行中列的数量，占用10位，这也很好地解释了为什么MySQL一个行支持最多的列为1023。另一个值为1byte_offs_flags，该值定义了偏移列表占用1个字节还是2个字节。当它的值为1时，表明使用1个字节存储。当它的值为0时，表明使用2个字节存储。1byte_offs_flag的值是怎么选择的我们前边说过每个列对应的偏移量可以占用1个字节或者2个字节来存储，那到底什么时候用1个字节，什么时候用2个字节呢？其实是根据该条Redundant行格式记录的真实数据占用的总大小来判断的：当记录的真实数据占用的字节数值不大于127（十六进制0x7F，二进制01111111）时，每个列对应的偏移量占用1个字节。当记录的真实数据占用的字节数大于127，但不大于32767（十六进制0x7FFF，二进制0111111111111111）时，每个列对应的偏移量占用2个字节。 有没有记录的真实数据大于32767的情况呢？有，不过此时的记录已经存放到了溢出页中，在本页中只保留前768个字节和20个字节的溢出页面地址。因为字段长度偏移列表处只需要记录每个列在本页面中的偏移就好了，所以每个列使用2个字节来存储偏移量就够了。大家可以看出来，Redundant行格式还是比较简单粗暴的，直接使用整个记录的真实数据长度来决定使用1个字节还是2个字节存储列对应的偏移量。只要整条记录的真实数据占用的存储空间大小大于127，即使第一个列的值占用存储空间小于127，那对不起，也需要使用2个字节来表示该列对应的偏移量。简单粗暴，就是这么简单粗暴（所以这种行格式有些过时了）。为了在解析记录时知道每个列的偏移量是使用1个字节还是2个字节表示的，Redundant行格式特意在
索引的创建与设计原则
索引的声明与使用
- 索引的分类
- MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
1. 从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
2. 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
3. 按照 作用字段个数 进行划分，分成单列索引和联合索引。 - 普通索引
- 唯一性索引
- 主键索引
- 单列索引
- 多列(组合、联合)索引
- 全文索引
- 补充：空间索引
- 小结：不同的存储引擎支持的索引类型也不一样 InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM ： 支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory ：支持 B-tree、Hash 等 索引，不支持 Full-text 索引； NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive ：不支 持 B-tree、Hash、Full-text 等索引；
- 创建索引
- 创建表的时候创建索引举例：
CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);
CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);
- 但是，如果显式创建表时创建索引的话，基本语法格式如下：
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]
1. UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
2. INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；
3. index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
4. col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
5. length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
6. ASC 或 DESC 指定升序或者降序的索引值存储。
- 创建普通索引
1. 在book表中的year_publication字段上建立普通索引，SQL语句如下：
CREATE TABLE book(
book_id INT ,
book_name VARCHAR(100),
authors VARCHAR(100),
info VARCHAR(100) ,
comment VARCHAR(100),
year_publication YEAR,
INDEX(year_publication)
);
2. 创建唯一索引 举例：
CREATE TABLE test1(
id INT NOT NULL,
name varchar(30) NOT NULL,
UNIQUE INDEX uk_idx_id(id)
);
3. 该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：
1. SHOW INDEX FROM test1 \G
- 主键索引
1. 设定为主键后数据库会自动建立索引，innodb为聚簇索引，语法：- 随表一起建索引：
CREATE TABLE student (
id INT(10) UNSIGNED AUTO_INCREMENT ,
student_no VARCHAR(200),
student_name VARCHAR(200),
PRIMARY KEY(id)
);
- 删除主键索引：
ALTER TABLE student
drop PRIMARY KEY
1. 修改主键索引：必须先删除掉(drop)原索引，再新建(add)索引
- 创建单列索引 举例：
CREATE TABLE test2(
id INT NOT NULL,
name CHAR(50) NULL,
INDEX single_idx_name(name(20))
);
该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：
SHOW INDEX FROM test2 \G
- . 创建组合索引
举例：创建表test3，在表中的id、name和age字段上建立组合索引，SQL语句如下：
CREATE TABLE test3(
id INT(11) NOT NULL,
name CHAR(30) NOT NULL,
age INT(11) NOT NULL,
info VARCHAR(255),
INDEX multi_idx(id,name,age)
);
该语句执行完毕之后，使用SHOW INDEX 查看：
SHOW INDEX FROM test3 \G
- 创建全文索引
举例1：创建表test4，在表中的info字段上建立全文索引，SQL语句如下：
CREATE TABLE test4(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;
在MySQL5.7及之后版本中可以不指定最后的ENGINE了，因为在此版本中InnoDB支持全文索引。
- 举例2：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT index (title, body)
) ENGINE = INNODB ;
创建了一个给title和body字段添加全文索引的表。
举例3：
CREATE TABLE `papers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
不同于like方式的的查询：
SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;
全文索引用match+against方式查询：
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);
注意点
1. 使用全文索引前，搞清楚版本支持情况；
2. 全文索引比 like + % 快 N 倍，但是可能存在精度问题；
3. 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
- 创建空间索引
1. 空间索引创建中，要求空间类型的字段必须为 非空 。
2. 举例：创建表test5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：
CREATE TABLE test5(
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;
- 在已经存在的表上创建索引
- 在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。
- 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
- 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中， CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
- 删除索引
- 使用ALTER TABLE删除索引 ALTER TABLE删除索引的基本语法格式如下：
- ALTER TABLE table_name DROP INDEX index_name;
- 使用DROP INDEX语句删除索引 DROP INDEX删除索引的基本语法格式如下：
- DROP INDEX index_name ON table_name
- 提示 删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成 索引的所有列都被删除，则整个索引将被删除。
MySQL8.0索引新特性
- 支持降序索引
- 举例：分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1，结果如下：
- CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
- 在MySQL 5.7版本中查看数据表ts1的结构，结果如下：
从结果可以看出，索引仍然是默认的升序。
在MySQL 8.0版本中查看数据表ts1的结构，结果如下：
从结果可以看出，索引已经是降序了。下面继续测试降序索引在执行计划中的表现。 分别在MySQL 5.7版本和MySQL 8.0版本的数据表ts1中插入800条随机数据，执行语句如下：
DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 800
DO
insert into ts1 select rand()*80000,rand()*80000;
SET i = i + 1;
END WHILE;
commit;
END //
DELIMITER ;
#调用
CALL ts_insert();
在MySQL 5.7版本中查看数据表ts1的执行计划，结果如下：
EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;
从结果可以看出，执行计划中扫描数为799，而且使用了Using filesort。
提示 Using filesort是MySQL中一种速度比较慢的外部排序，能避免是最好的。多数情况下，管理员 可以通过优化索引来尽量避免出现Using filesort，从而提高数据库执行速度。
在MySQL 8.0版本中查看数据表ts1的执行计划。从结果可以看出，执行计划中扫描数为5，而且没有使用 Using filesort。
注意 降序索引只对查询中特定的排序顺序有效，如果使用不当，反而查询效率更低。例如，上述 查询排序条件改为order by a desc, b desc，MySQL 5.7的执行计划要明显好于MySQL 8.0。
将排序条件修改为order by a desc, b desc后，下面来对比不同版本中执行计划的效果。 在MySQL 5.7版本 中查看数据表ts1的执行计划，结果如下：
EXPLAIN SELECT * FROM ts1 ORDER BY a DESC,b DESC LIMIT 5;
在MySQL 8.0版本中查看数据表ts1的执行计划。 从结果可以看出，修改后MySQL 5.7的执行计划要明显好于MySQL 8.0。
- 隐藏索引
- 在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能 通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较 大，这种操作就会消耗系统过多的资源，操作成本非常高。
- 从MySQL 8.x开始支持 隐藏索引（invisible indexes） ，只需要将待删除的索引设置为隐藏索引，使 查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引）， 确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 这种通过先将索引设置为隐藏索 引，再删除索引的方式就是软删除 。
- 创建表时直接创建 在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现，其语法形式如下：
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen,
INDEX [indexname](propname1 [(length)]) INVISIBLE
);
- 上述语句比普通索引多了一个关键字INVISIBLE，用来标记索引为不可见索引。
- 在已经存在的表上创建
- 可以为已经存在的表设置隐藏索引，其语法形式如下：
CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE
- 通过ALTER TABLE语句创建
- 语法形式如下：
1. ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE; - 切换索引可见状态 已存在的索引可通过如下语句切换可见状态：
1. ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
2. ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引 - 如果将index_cname索引切换成可见状态，通过explain查看执行计划，发现优化器选择了index_cname索 引。
1. 注意 当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐 藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。 - 通过设置隐藏索引的可见性可以查看索引对调优的帮助。
- 使隐藏索引对查询优化器可见
- 在MySQL 8.x版本中，为索引提供了一种新的测试方式，可以通过查询优化器的一个开关 （use_invisible_indexes）来打开某个设置，使隐藏索引对查询优化器可见。如果 use_invisible_indexes 设置为off(默认)，优化器会忽略隐藏索引。如果设置为on，即使隐藏索引不可见，优化器在生成执行计 划时仍会考虑使用隐藏索引。
1. 在MySQL命令行执行如下命令查看查询优化器的开关设置。- mysql> select @@optimizer_switch \G
2. 在输出的结果信息中找到如下属性配置。
1. use_invisible_indexes=off
3. 此属性配置值为off，说明隐藏索引默认对查询优化器不可见。
1. 使隐藏索引对查询优化器可见，需要在MySQL命令行执行如下命令：
1. mysql> set session optimizer_switch="use_invisible_indexes=on"; Query OK, 0 rows affected (0.00 sec)
2. SQL语句执行成功，再次查看查询优化器的开关设置。
mysql> select @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_
intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_co
st_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on
,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on
,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_ind
exes=on,skip_scan=on,hash_join=on
1 row in set (0.00 sec)
- 此时，在输出结果中可以看到如下属性配置。
1. use_invisible_indexes=on - use_invisible_indexes属性的值为on，说明此时隐藏索引对查询优化器可见。
- （3）使用EXPLAIN查看以字段invisible_column作为查询条件时的索引使用情况。
1. explain select * from classes where cname = ‘高一2班’; - 查询优化器会使用隐藏索引来查询数据。
- 如果需要使隐藏索引对查询优化器不可见，则只需要执行如下命令即可。
1. mysql> set session optimizer_switch=“use_invisible_indexes=off”; Query OK, 0 rows affected (0.00 sec) - 再次查看查询优化器的开关设置。
1. mysql> select @@optimizer_switch \G - 此时，use_invisible_indexes属性的值已经被设置为“off”。
索引的设计原则
- 数据准备
- 创建数据库、创建表
CREATE DATABASE atguigudb1;
USE atguigudb1;
#1.创建学生表和课程表
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `course` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`course_id` INT NOT NULL ,
`course_name` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
- 创建模拟数据必需的存储函数
#函数1：创建随机产生字符串函数
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
创建函数，假如报错：
由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。
主从复制，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使
用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设
置。
查看mysql是否允许创建函数：
命令开启：允许创建函数设置：
mysqld重启，上述参数又会消失。永久方法：
windows下：my.ini[mysqld]加上：
linux下：/etc/my.cnf下my.cnf[mysqld]加上：
第3步：创建插入模拟数据的存储过程
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 ;
#函数2：创建随机数函数
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 ;
- 创建函数，假如报错：
1. This function has none of DETERMINISTIC… - 由于开启过慢查询日志bin-log, 我们就必须为我们的function指定一个参数。 主从复制，主机会将写操作记录在bin-log日志中。从机读取bin-log日志，执行语句来同步数据。如果使 用函数来操作数据，会导致从机和主键操作时间不一致。所以，默认情况下，mysql不开启创建函数设 置。
1. 查看mysql是否允许创建函数：- show variables like ‘log_bin_trust_function_creators’;
2. 命令开启：允许创建函数设置：
1. set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
3. mysqld重启，上述参数又会消失。永久方法：
1. windows下：my.ini[mysqld]加上：
1. log_bin_trust_function_creators=1
2. linux下：/etc/my.cnf下my.cnf[mysqld]加上：
1. log_bin_trust_function_creators=1
4. 创建插入模拟数据的存储过程
# 存储过程1：创建插入课程表存储过程
DELIMITER //
CREATE PROCEDURE insert_course( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO course (course_id, course_name ) VALUES
(rand_num(10000,10100),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
# 存储过程2：创建插入学生信息表存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student_info (course_id, class_id ,student_id ,NAME ) VALUES
(rand_num(10000,10100),rand_num(10000,10200),rand_num(1,200000),rand_string(6));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
- 调用存储过程
1. CALL insert_course(100); CALL insert_stu(1000000); - 哪些情况适合创建索引
- 字段的数值有唯一性的限制
1. 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba） 说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。 - 频繁作为 WHERE 查询条件的字段
1. 某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在 数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。 比如student_info数据表（含100万条数据），假设我们想要查询 student_id=123110 的用户信息。 - 经常 GROUP BY 和 ORDER BY 的列
1. 索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者 使用 ORDER BY 对数据进行排序的时候，就需要 对分组或者排序的字段进行索引 。如果待排序的列有多 个，那么可以在这些列上建立 组合索引 。 - UPDATE、DELETE 的 WHERE 条件列
1. 对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就 能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或 删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更 新不需要对索引进行维护。 - DISTINCT 字段需要创建索引
1. 有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。 比如，我们想要查询课程表中不同的 student_id 都有哪些，如果我们没有对 student_id 创建索引，执行 SQL 语句：- SELECT DISTINCT(student_id) FROM
student_info;
- SELECT DISTINCT(student_id) FROM
2. 运行结果（600637 条记录，运行时间 0.683s ）：
3. 如果我们对 student_id 创建索引，再执行 SQL 语句：
1. SELECT DISTINCT(student_id) FROM `student_info`;
4. 运行结果（600637 条记录，运行时间 0.010s ）：
1. 你能看到 SQL 查询效率有了提升，同时显示出来的 student_id 还是按照 递增的顺序 进行展示的。这是因 为索引会对数据按照某种顺序进行排序，所以在去重的时候也会快很多。
- 多表 JOIN 连接操作时，创建索引注意事项
1. 首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增 长会非常快，严重影响查询的效率。
2. 其次， 对 WHERE 条件创建索引 ，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下， 没有 WHERE 条件过滤是非常可怕的。
3. 最后， 对用于连接的字段创建索引 ，并且该字段在多张表中的 类型必须一致 。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。
4. 举个例子，如果我们只对 student_id 创建索引，执行 SQL 语句：
SELECT course_id, name, student_info.student_id, course_name
FROM student_info JOIN course
ON student_info.course_id = course.course_id
WHERE name = '462eed7ac6e791292a79';
1. 运行结果（1 条数据，运行时间 0.189s ）： 这里我们对 name 创建索引，再执行上面的 SQL 语句，运行时间为 0.002s 。
- 使用列的类型小的创建索引
- 使用字符串前缀创建索引
1. 创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引
2. create table shop(address varchar(120) not null);
3. alter table shop add index(address(12)); - 问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的；截取得少了，重复内容太多，字 段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢？ 先看一下字段在全部数据中的选择度：
1. select count(distinct address) / count(*) from shop; - 通过不同长度去计算，与全表的选择性对比：
1. count(distinct left(列名, 索引长度))/count(*) - 例如：
select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;
- 引申另一个问题：索引列前缀对排序的影响
1. 拓展：Alibaba《Java开发手册》
2. 【 强制 】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本 区分度决定索引长度。 说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达 90% 以上 ，可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度来确定。 - 区分度高(散列性高)的列适合作为索引 10. 使用最频繁的列放到联合索引的左侧 这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。 11. 在多个字段都要创建索引的情况下，联合索引优于单值索引 3.3 限制索引的数目 3.4 哪些情况不适合创建索引 1. 在where中使用不到的字段，不要设置索引
- 数据量小的表最好不要使用索引
1. 举例：创建表1：
CREATE TABLE t_without_index(
a INT PRIMARY KEY AUTO_INCREMENT,
b INT
);
- 提供存储过程1：
#创建存储过程
DELIMITER //
CREATE PROCEDURE t_wout_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 900
DO
INSERT INTO t_without_index(b) SELECT RAND()*10000;
SET i = i + 1;
END WHILE
COMMIT;
END //
DELIMITER ;
#调用
CALL t_wout_insert();
创建表2：
CREATE TABLE t_with_index(
a INT PRIMARY KEY AUTO_INCREMENT,
b INT,
INDEX idx_b(b)
);
创建存储过程2：
#创建存储过程
DELIMITER //
CREATE PROCEDURE t_with_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 900
DO
INSERT INTO t_with_index(b) SELECT RAND()*10000;
SET i = i + 1;
END WHILE;
COMMIT;
END //
DELIMITER ;
#调用
CALL t_with_insert();
查询对比：
mysql> select * from t_without_index where b = 9879;
+------+------+
| a | b |
+------+------+
| 1242 | 9879 |
+------+------+
1 row in set (0.00 sec)
mysql> select * from t_with_index where b = 9879;
+-----+------+
| a | b |
+-----+------+
| 112 | 9879 |
+-----+------+
1 row in set (0.00 sec)
你能看到运行结果相同，但是在数据量不大的情况下，索引就发挥不出作用了。
结论：在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。
- 有大量重复数据的列上不要建立索引
- 举例1：要在 100 万行数据中查找其中的 50 万行（比如性别为男的数据），一旦创建了索引，你需要先 访问 50 万次索引，然后再访问 50 万次数据表，这样加起来的开销比不使用索引可能还要大。 举例2：假设有一个学生表，学生总数为 100 万人，男性只有 10 个人，也就是占总人口的 10 万分之 1。 学生表 student_gender 结构如下。其中数据表中的 student_gender 字段取值为 0 或 1，0 代表女性，1 代 表男性。
CREATE TABLE student_gender(
student_id INT(11) NOT NULL,
student_name VARCHAR(50) NOT NULL,
student_gender TINYINT(1) NOT NULL,
PRIMARY KEY(student_id)
)ENGINE = INNODB;
- 如果我们要筛选出这个学生表中的男性，可以使用：
1. SELECT * FROM student_gender WHERE student_gender = 1 - 运行结果（10 条数据，运行时间 0.696s ）：
- 结论：当数据重复度大，比如 高于 10% 的时候，也不需要对这个字段使用索引。
- 避免对经常更新的表创建过多的索引
- 不建议用无序的值作为索引
- 例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字 符串等。
- 删除不再使用或者很少使用的索引
- 不要定义冗余或重复的索引
冗余索引
举例：建表语句如下
CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number),
KEY idx_name (name(10))
);
我们知道，通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索，再创建一
个专门针对 name 列的索引就算是一个 冗余索引 ，维护这个索引只会增加维护的成本，并不会对搜索有
什么好处。
② 重复索引
另一种情况，我们可能会对某个列 重复建立索引 ，比方说这样：
CREATE TABLE repeat_index_demo (
col1 INT PRIMARY KEY,
col2 INT,
UNIQUE uk_idx_c1 (col1),
INDEX idx_c1 (col1)
)
我们看到，col1 既是主键、又给它定义为一个唯一索引，还给它定义了一个普通索引，可是主键本身就 会生成聚簇索引，所以定义的唯一索引和普通索引是重复的，这种情况要避免。
性能分析工具的使用
数据库服务器的优化步骤
当我们遇到数据库调优问题的时候，该如何思考呢？这里把思考的流程整理成下面这张图。 整个流程划分成了 观察（Show status） 和 行动（Action） 两个部分。字母 S 的部分代表观察（会使 用相应的分析工具），字母 A 代表的部分是行动（对应分析可以采取的行动）
查看系统性能参数
在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。 SHOW STATUS语句语法如下：
SHOW [GLOBAL|SESSION] STATUS LIKE ‘参数’;
一些常用的性能参数如下： • Connections：连接MySQL服务器的次数。 • Uptime：MySQL服务器的上 线时间。 • Slow_queries：慢查询的次数。 • Innodb_rows_read：Select查询返回的行数 • Innodb_rows_inserted：执行INSERT操作插入的行数 • Innodb_rows_updated：执行UPDATE操作更新的 行数 • Innodb_rows_deleted：执行DELETE操作删除的行数 • Com_select：查询操作的次数。 • Com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。 • Com_update：更新操作 的次数。 • Com_delete：删除操作的次数。
统计SQL的查询成本：last_query_cost
我们依然使用第8章的 student_info 表为例：
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
如果我们想要查询 id=900001 的记录，然后看下查询成本，我们可以直接在聚簇索引上进行查找：
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;
运行结果（1 条记录，运行时间为 0.042s ）
然后再看下查询优化器的成本，实际上我们只需要检索一个页即可：
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
如果我们想要查询 id 在 900001 到 9000100 之间的学生记录呢？
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;
运行结果（100 条记录，运行时间为 0.046s ）：
然后再看下查询优化器的成本，这时我们大概需要进行 20 个页的查询。
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| Last_query_cost | 21.134453 |
+-----------------+-----------+
你能看到页的数量是刚才的 20 倍，但是查询的效率并没有明显的变化，实际上这两个 SQL 查询的时间
基本上一样，就是因为采用了顺序读取的方式将页面一次性加载到缓冲池中，然后再进行查找。虽然 页
数量（last_query_cost）增加了不少 ，但是通过缓冲池的机制，并 没有增加多少查询时间 。
使用场景：它对于比较开销是非常有用的，特别是我们有好几种查询方式可选的时候。
定位执行慢的 SQL：慢查询日志
- 开启慢查询日志参数
- 开启slow_query_log
1. mysql > set global slow_query_log=‘ON’; - 然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：
1.
- 你能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/atguigu02-slow.log 文件 中。
- . 修改long_query_time阈值
1. 接下来我们来看下慢查询的时间阈值设置，使用如下命令：- mysql > show variables like ‘%long_query_time%’;
2. 
- 这里如果我们想把时间缩短，比如设置为 1 秒，可以这样设置：
1. #测试发现：设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并 执行下述语句 mysql > set global long_query_time = 1; mysql> show global variables like ‘%long_query_time%’; mysql> set long_query_time=1; mysql> show variables like ‘%long_query_time%’;
2.
- 查看慢查询数目
- 查询当前系统中有多少条慢查询记录
1. SHOW GLOBAL STATUS LIKE ‘%Slow_queries%’; - 案例演示
步骤1. 建表
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
步骤2：设置参数 log_bin_trust_function_creators
创建函数，假如报错：
This function has none of DETERMINISTIC......
命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
步骤3：创建函数
随机产生字符串：（同上一章）
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 ;
#测试
SELECT rand_string(10);
产生随机数值：（同上一章）
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 ;
#测试：
SELECT rand_num(10,100);
步骤4：创建存储过程
DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
步骤5：调用存储过程
#调用刚刚写好的函数, 4000000条记录,从100001号开始
CALL insert_stu1(100001,4000000);
- 测试及分析
- 测试
mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 3523633 | 3455655 | oQmLUr | 19 | 39 |
+---------+---------+--------+------+---------+
1 row in set (2.09 sec)
mysql> SELECT * FROM student WHERE name = 'oQmLUr';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 1154002 | 1243200 | OQMlUR | 266 | 28 |
| 1405708 | 1437740 | OQMlUR | 245 | 439 |
| 1748070 | 1680092 | OQMlUR | 240 | 414 |
| 2119892 | 2051914 | oQmLUr | 17 | 32 |
| 2893154 | 2825176 | OQMlUR | 245 | 435 |
| 3523633 | 3455655 | oQmLUr | 19 | 39 |
+---------+---------+--------+------+---------+
6 rows in set (2.39 sec)
从上面的结果可以看出来，查询学生编号为“3455655”的学生信息花费时间为2.09秒。查询学生姓名为
“oQmLUr”的学生信息花费时间为2.39秒。已经达到了秒的数量级，说明目前查询效率是比较低的，下面
的小节我们分析一下原因。
分析
show status like 'slow_queries';
- 慢查询日志分析工具：mysqldumpslow
- 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 。
- 查看mysqldumpslow的帮助信息
- mysqldumpslow --help
- mysqldumpslow 命令的具体参数如下：
1. a: 不将数字抽象成N，字符串抽象成S
2. -s: 是表示按照何种方式排序：- c: 访问次数
- l: 锁定时间
- r: 返回记录
- t: 查询时间
- al:平均锁定时间
- ar:平均返回记录数
- at:平均查询时间 （默认方式）
- ac:平均查询次数
- -t: 即为返回前面多少条的数据；
- g: 后边搭配一个正则匹配模式，大小写不敏感的；
3. 举例：我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：
mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log
[root@bogon ~]# mysqldumpslow -s t -t 5 /var/lib/mysql/atguigu01-slow.log
Reading mysql slow query log from /var/lib/mysql/atguigu01-slow.log
Count: 1 Time=2.39s (2s) Lock=0.00s (0s) Rows=13.0 (13), root[root]@localhost
SELECT * FROM student WHERE name = 'S'
Count: 1 Time=2.09s (2s) Lock=0.00s (0s) Rows=2.0 (2), root[root]@localhost
SELECT * FROM student WHERE stuno = N
Died at /usr/bin/mysqldumpslow line 162, <> chunk 2.
工作常用参考：
#得到返回记录集最多的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
- 关闭慢查询日志
- MySQL服务器停止慢查询日志功能有两种方法：
- 方式1：永久性方式
1. [mysqld] slow_query_log=OFF - 或者，把slow_query_log一项注释掉 或 删除
1. [mysqld] #slow_query_log =OFF - 重启MySQL服务，执行如下语句查询慢日志功能。
1. SHOW VARIABLES LIKE ‘%slow%’; #查询慢查询日志所在目录
2. SHOW VARIABLES LIKE ‘%long_query_time%’; #查询超时时长 - 方式2：临时性方式
1. 使用SET语句来设置。 （1）停止MySQL慢查询日志功能，具体SQL语句如下。- SET GLOBAL slow_query_log=off;
2. 重启MySQL服务，使用SHOW语句查询慢查询日志功能信息，具体SQL语句如下
1. SHOW VARIABLES LIKE '%slow%';
2. #以及
3. SHOW VARIABLES LIKE '%long_query_time%';
- 删除慢查询日志
查看 SQL 执行成本：SHOW PROFILE
- mysql > show variables like ‘profiling’;
- 通过设置 profiling='ON’ 来开启 show profile：
- mysql > set profiling = ‘ON’;
- 然后执行相关的查询语句。接着看下当前会话都有哪些 profiles，使用下面这条命令：
- mysql > show profiles;
- 你能看到当前会话一共有 2 个查询。如果我们想要查看最近一次查询的开销，可以使用：
- ysql > show profile
mysql> show profile cpu,block io for query 2;
show profile的常用查询参数： ① ALL：显示所有的开销信息。 ② BLOCK IO：显示块IO开销。 ③ CONTEXT SWITCHES：上下文切换开 销。 ④ CPU：显示CPU开销信息。 ⑤ IPC：显示发送和接收开销信息。 ⑥ MEMORY：显示内存开销信 息。 ⑦ PAGE FAULTS：显示页面错误开销信息。 ⑧ SOURCE：显示和Source_function，Source_file， Source_line相关的开销信息。 ⑨ SWAPS：显示交换次数开销信息。
分析查询语句：EXPLAIN
- 概述
- 基本语法
- EXPLAIN 或 DESCRIBE语句的语法形式如下：
1. EXPLAIN SELECT select_options
2. 或者
3. DESCRIBE SELECT select_options - 如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：
1. mysql> EXPLAIN SELECT 1; - EXPLAIN 语句输出的各个列的作用如下：
1.
- 数据准备
1. 建表
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2)
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
2. 设置参数 log_bin_trust_function_creators
创建函数，假如报错，需开启如下命令：允许创建函数设置：
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
创建函数
DELIMITER //
CREATE FUNCTION rand_string1(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 ;
4. 创建存储过程
创建往s1表中插入数据的存储过程：
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
创建往s2表中插入数据的存储过程：
DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
5. 调用存储过程
s1表数据的添加：加入1万条记录：
CALL insert_s1(10001,10000);
s2表数据的添加：加入1万条记录：
CALL insert_s2(10001,10000);
- EXPLAIN各列作用
为了让大家有比较好的体验，我们调整了下 EXPLAIN 输出列的顺序。
- table
1. 不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所 以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该 表的表名（有时不是真实的表名字，可能是简称）。 - . id
1. 我们写的查询语句一般都以 SELECT 关键字开头，比较简单的查询语句里只有一个 SELECT 关键字，比 如下边这个查询语句：
2. SELECT * FROM s1 WHERE key1 = ‘a’;
3. 稍微复杂一点的连接查询中也只有一个 SELECT 关键字，比如：
4. SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = ‘a’;
5. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’;
6.
7. mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
8.
9. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = ‘a’;
10.
11. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = ‘a’);
12.
13. mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
14.
15. mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
16.
17. 小结:- d如果相同，可以认为是一组，从上往下顺序执行
- 在所有组中，id值越大，优先级越高，越先执行
- 关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好
- select_type
1.
2. mysql> EXPLAIN SELECT * FROM s1;
3.
4. 当然，连接查询也算是 SIMPLE 类型，比如：
5. mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
6.
7. PRIMARY- mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
8. 
9. UNION
10. UNION RESULT
11. SUBQUERY
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
2. 
12. DEPENDENT SUBQUERY
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
2. 
13. DEPENDENT UNION
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
14. 
15. DERIVED
1. mysql> EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;
16. 
17. MATERIALIZED
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);
18. 
19. UNCACHEABLE SUBQUERY 不常用。 UNCACHEABLE UNION 不常用
- partitions
-- 创建分区表，
-- 按照id分区，id<100 p0分区，其他p1分区
CREATE TABLE user_partitions (id INT auto_increment,
NAME VARCHAR(12),PRIMARY KEY(id))
PARTITION BY RANGE(id)(
PARTITION p0 VALUES less than(100),
PARTITION p1 VALUES less than MAXVALUE
);
- DESC SELECT * FROM user_partitions WHERE id>200;
1. 查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则 
- type ☆
1. 完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。 - 我们详细解释一下：
1. system
mysql> CREATE TABLE t(i int) Engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO t VALUES(1);
Query OK, 1 row affected (0.01 sec)
然后我们看一下查询这个表的执行计划：
mysql> EXPLAIN SELECT * FROM t;
- const
1. mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;
- eq_ref
1. mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
2. 从执行计划的结果中可以看出，MySQL打算将s2作为驱动表，s1作为被驱动表，重点关注s1的访问 方法是 eq_ref ，表明在访问s1表的时候可以 通过主键的等值匹配 来进行访问。
- ref
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’;
2.
- fulltext
1. 全文索引 - ref_or_null
- mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key1 IS NULL;
- index_merge
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ OR key3 = ‘a’;
2.
- unique_subquery
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = ‘a’;
2.
- index_subquery
1. mysql> EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = ‘a’;
2.
- range
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (‘a’, ‘b’, ‘c’);
2.
3. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > ‘a’ AND key1 < ‘b’;
4.
- index
1. mysql> EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = ‘a’;
2.
- ALL
1. mysql> EXPLAIN SELECT * FROM s1
2.
- 结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见上图中的蓝 色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）
- possible_keys和key
- mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’ AND key3 = ‘a’;
1.
- key_len ☆
- mysql> EXPLAIN SELECT * FROM s1 WHERE id = 10005;
- mysql> EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
- mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’;
- mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = ‘a’
- mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = ‘a’ AND key_part2 = ‘b’;
- 练习： key_len的长度计算公式：
varchar(10)变长字段且允许NULL = 10 * ( character set：
utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)
char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
- ref
- mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’;
- mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
- mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
- rows ☆
- mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’;
- filtered
- mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’ AND common_field = ‘a’;
- mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = ‘a’;
- Extra ☆
- mysql> EXPLAIN SELECT 1;
- Impossible WHERE
- mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
- Using where
1. mysql> EXPLAIN SELECT * FROM s1 WHERE common_field = ‘a’;
2.
3. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = ‘a’ AND common_field = ‘a’;
4.
- No matching min/max row
1. mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = ‘abcdefg’;
2.
- Using index
1. mysql> EXPLAIN SELECT key1 FROM s1 WHERE key1 = ‘a’;
2.
- Using index condition
1. SELECT * FROM s1 WHERE key1 > ‘z’ AND key1 LIKE ‘%a’;
2. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > ‘z’ AND key1 LIKE ‘%b’;
3.
4. Using join buffer (Block Nested Loop)- mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
5. Not exists
1. mysql> EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
2. 
6. Using intersect(...) 、 Using union(...) 和 Using sort_union(...)
1. mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
2. 
7. Zero limit
1. mysql> EXPLAIN SELECT * FROM s1 LIMIT 0;
2. 
8. Using filesort
1. mysql> EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
2. 
3. mysql> EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
4. 
9. Using temporary
1. mysql> EXPLAIN SELECT DISTINCT common_field FROM s1;
2. 
3. mysql> EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
4. 
5. mysql> EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
6. 
7. 从 Extra 的 Using index 的提示里我们可以看出，上述查询只需要扫描 idx_key1 索引就可以搞 定了，不再需要临时表了。 其它 其它特殊情况这里省略。
8. 小结
1. EXPLAIN不考虑各种Cache
2. EXPLAIN不能显示MySQL在执行查询时所作的优化工作
3. EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
4. 部分统计信息是估算的，并非精确值
EXPLAIN的进一步使用 LAIN的进一步使用
- EXPLAIN四种输出格式
这里谈谈EXPLAIN的输出格式。EXPLAIN可以输出四种格式： 传统格式 ， JSON格式 ， TREE格式 以及 可 视化输出 。用户可以根据需要选择适用于自己的格式。
- 传统格式
1. 传统格式简单明了，输出是一个表格形式，概要说明查询计划。
2. mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
3.
- JSON格式
1. JSON格式：在EXPLAIN单词和真正的查询语句中间加上 FORMAT=JSON 。- EXPLAIN FORMAT=JSON SELECT …
2. 我们使用 # 后边跟随注释的形式为大家解释了 EXPLAIN FORMAT=JSON 语句的输出内容，但是大家可能 有疑问 "cost_info" 里边的成本看着怪怪的，它们是怎么计算出来的？先看 s1 表的 "cost_info" 部 分：
"cost_info": {
"read_cost": "1840.84",
"eval_cost": "193.76",
"prefix_cost": "2034.60",
"data_read_per_join": "1M"
}
3. read_cost 是由下边这两部分组成的：
1. IO 成本
2. 检测 rows × (1 - filter) 条记录的 CPU 成本
4. 小贴士： rows和filter都是我们前边介绍执行计划的输出列，在JSON格式的执行计划中，rows 相当于rows_examined_per_scan，filtered名称不变。
1. eval_cost 是这样计算的： 检测 rows × filter 条记录的成本。
2. prefix_cost 就是单独查询 s1 表的成本，也就是：
3. read_cost + eval_cost
4. data_read_per_join 表示在此次查询中需要读取的数据量。
5. 对于 s2 表的 "cost_info" 部分是这样的：
"cost_info": {
"read_cost": "968.80",
"eval_cost": "193.76",
"prefix_cost": "3197.16",
"data_read_per_join": "1M"
}
由于 s2 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次 s2 表后累 加起来的值，大家主要关注里边儿的 prefix_cost 的值代表的是整个连接查询预计的成本，也就是单 次查询 s1 表和多次查询 s2 表后的成本的和，也就是 968.80 + 193.76 + 2034.60 = 3197.16
- TREE格式
- TREE格式是8.0.16版本之后引入的新格式，主要根据查询的 各个部分之间的关系 和 各部分的执行顺序 来描 述如何查询。
mysql> EXPLAIN FORMAT=tree SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE
s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=1360.08 rows=990)
-> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75
rows=990)
-> Table scan on s1 (cost=1013.75 rows=9895)
-> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index
condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1)
1 row in set, 1 warning (0.00 sec)
- 可视化输出
- 可视化输出，可以通过MySQL Workbench可视化查看MySQL的执行计划。通过点击Workbench的放大镜图 标，即可生成可视化的查询计划。 上图按从左到右的连接顺序显示表。红色框表示 全表扫描 ，而绿色框表示使用 索引查找 。对于每个表， 显示使用的索引。还要注意的是，每个表格的框上方是每个表访问所发现的行数的估计值以及访问该表 的成本。
- SHOW WARNINGS的使用
- mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL;
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `atguigu`.`s1`.`key1` AS `key1`,`atguigu`.`s2`.`key1`
AS `key1` from `atguigu`.`s1` join `atguigu`.`s2` where ((`atguigu`.`s1`.`key1` =
`atguigu`.`s2`.`key1`) and (`atguigu`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)
分析优化器执行计划：trace
- SET optimizer_trace=“enabled=on”,end_markers_in_json=on;
- set optimizer_trace_max_mem_size=1000000;
- 开启后，可分析如下语句：
- SELECT
- INSERT
- REPLACE
- UPDATE
- DELETE
- EXPLAIN
- SET
- DECLARE
- CASE
- IF
- RETURN
- CALL
- 测试：执行如下SQL语句
- select * from student where id < 10;
- 最后， 查询 information_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的 ：
- select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
//第1部分：查询语句
QUERY: select * from student where id < 10
//第2部分：QUERY字段对应语句的跟踪信息
TRACE: {
"steps": [
{
"join_preparation": { //预备工作
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `student`.`id` AS
`id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS
`age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { //进行优化
"select#": 1,
"steps": [
{
"condition_processing": { //条件处理
"condition": "WHERE",
"original_condition": "(`student`.`id` < 10)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`student`.`id` < 10)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`student`.`id` < 10)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`student`.`id` < 10)"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": { //替换生成的列
} /* substitute_generated_columns */
},
{
"table_dependencies": [ //表的依赖关系
{
"table": "`student`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [ //使用键
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ //行判断
{
"table": "`student`",
"range_analysis": {
"table_scan": {
"rows": 3973767,
"cost": 408558
} /* table_scan */, //扫描表
"potential_range_indexes": [ //潜在的范围索引
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [ //设置范围条件
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": { //分析范围选项
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"id < 10"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 9,
"cost": 1.91986,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": { //选择范围访问摘要
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 9,
"ranges": [
"id < 10"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 9,
"cost_for_plan": 1.91986,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [ //考虑执行计划
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`student`",
"best_access_path": { //最佳访问路径
"considered_access_paths": [
{
"rows_to_scan": 9,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
} /* range_details */,
"resulting_rows": 9,
"cost": 2.81986,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100, //行过滤百分比
"rows_for_plan": 9,
"cost_for_plan": 2.81986,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": { //将条件附加到表上
"original_condition": "(`student`.`id` < 10)",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [ //附加条件概要
{
"table": "`student`",
"attached": "(`student`.`id` < 10)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"finalizing_table_conditions": [
{
"table": "`student`",
"original_table_condition": "(`student`.`id` < 10)",
"final_table_condition ": "(`student`.`id` < 10)"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [ //精简计划
{
"table": "`student`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { //执行
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
//第3部分：跟踪信息过长时，被截断的跟踪信息的字节数。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 //丢失的超出最大容量的字节
//第4部分：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空，一般在
调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下，会出现此问题。
INSUFFICIENT_PRIVILEGES: 0 //缺失权限
1 row in set (0.00 sec)
MySQL监控分析视图-sys schema
- Sys schema视图摘要
-
- 主机相关：以host_summary开头，主要汇总了IO延迟的信息。
-
- Innodb相关：以innodb开头，汇总了innodb buffer信息和事务等待innodb锁的信息。
-
- I/o相关：以io开头，汇总了等待I/O、I/O使用量情况。
-
- 内存使用情况：以memory开头，从主机、线程、事件等角度展示内存的使用情况
-
- 连接与会话信息：processlist和session相关视图，总结了会话相关信息。
-
- 表相关：以schema_table开头的视图，展示了表的统计信息。
-
- 索引信息：统计了索引的使用情况，包含冗余索引和未使用的索引情况。
-
- 语句相关：以statement开头，包含执行全表扫描、使用临时表、排序等的语句信息。
-
- 用户相关：以user开头的视图，统计了用户使用的文件I/O、执行语句统计信息。
-
- 等待事件相关信息：以wait开头，展示等待事件的延迟情况。
- Sys schema视图使用场景
- 索引情况
#1. 查询冗余索引
select * from sys.schema_redundant_indexes;
#2. 查询未使用过的索引
select * from sys.schema_unused_indexes;
#3. 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;
- 表相关
# 1. 查询表的访问量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查询占用bufferpool较多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 查看表的全表扫描情况
select * from sys.statements_with_full_table_scans where db='dbname';
- 语句相关
#1. 监控SQL执行的频率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 监控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 监控使用了临时表或者磁盘临时表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;
- IO相关
#1. 查看消耗磁盘IO的文件
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;
- Innodb 相关
#1. 行锁阻塞情况
select * from sys.innodb_lock_waits;
索引的优化与查询优化
数据准备
学员表 插 50万 条， 班级表 插 1万 条。
步骤1：建表
CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
步骤2：设置参数
命令开启：允许创建函数设置：
set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。
步骤3：创建函数
保证每条数据都不同。
#随机产生字符串
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 ;
#假如要删除
#drop function rand_string;
随机产生班级编号
#用于随机产生多少到多少的编号
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 ;
#假如要删除
#drop function rand_num;
步骤4：创建存储过程
#创建往stu表中插入数据的存储过程
DELIMITER //
CREATE PROCEDURE insert_stu( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #设置手动提交事务
REPEAT #循环
SET i = i + 1; #赋值
INSERT INTO student (stuno, name ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事务
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_stu;
创建往class表中插入数据的存储过程
#执行存储过程，往class表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO class ( classname,address,monitor ) VALUES
(rand_string(8),rand_string(10),rand_num(1,100000));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#假如要删除
#drop PROCEDURE insert_class;
步骤5：调用存储过程
class
#执行存储过程，往class表添加1万条数据
CALL insert_class(10000);
stu
#执行存储过程，往stu表添加50万条数据
CALL insert_stu(100000,500000);
步骤6：删除某表上的索引
创建存储过程
DELIMITER //
CREATE PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT '';
DECLARE _cur CURSOR FOR SELECT index_name FROM
information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND index_name <>'PRIMARY' ;
#每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2 ;
#若没有数据返回,程序继续,并将变量done设为2
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index<>'' DO
SET @str = CONCAT("drop index " , _index , " on " , tablename );
PREPARE sql_str FROM @str ;
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index='';
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END //
DELIMITER ;
执行存储过程
CALL proc_drop_index("dbname","tablename");
索引失效案例
- 全值匹配我最爱
- 最佳左前缀法则
- 拓展：Alibaba《Java开发手册》 索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
- 主键插入顺序
- 如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图：
- 可这个数据页已经满了，再插进来咋办呢？我们需要把当前 页面分裂 成两个页面，把本页中的一些记录 移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果我们想尽量 避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入 ， 比如： person_info 表：
CREATE TABLE person_info(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
birthday DATE NOT NULL,
phone_number CHAR(11) NOT NULL,
country varchar(100) NOT NULL,
PRIMARY KEY (id),
KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);
我们自定义的主键列 id 拥有 AUTO_INCREMENT 属性，在插入记录时存储引擎会自动为我们填入自增的 主键值。这样的主键占用空间小，顺序写入，减少页分裂。
- 计算、函数、类型转换(自动或手动)导致索引失效
- EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE ‘abc%’;
- EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = ‘abc’;
- 创建索引
1. CREATE INDEX idx_name ON student(NAME); - 第一种：索引优化生效
1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE ‘abc%’;
mysql> SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 |
| 7170042 | 3102064 | ABcHeB | 199 | 161 |
| 1901614 | 1833636 | ABcHeC | 226 | 275 |
| 5195021 | 1127043 | abchEC | 486 | 72 |
| 4047089 | 3810031 | AbCHFd | 268 | 210 |
| 4917074 | 849096 | ABcHfD | 264 | 442 |
| 1540859 | 141979 | abchFF | 119 | 140 |
| 5121801 | 1053823 | AbCHFg | 412 | 327 |
| 2441254 | 2373276 | abchFJ | 170 | 362 |
| 7039146 | 2971168 | ABcHgI | 502 | 465 |
| 1636826 | 1580286 | ABcHgK | 71 | 262 |
| 374344 | 474345 | abchHL | 367 | 212 |
| 1596534 | 169191 | AbCHHl | 102 | 146 |
...
| 5266837 | 1198859 | abclXe | 292 | 298 |
| 8126968 | 4058990 | aBClxE | 316 | 150 |
| 4298305 | 399962 | AbCLXF | 72 | 423 |
| 5813628 | 1745650 | aBClxF | 356 | 323 |
| 6980448 | 2912470 | AbCLXF | 107 | 78 |
| 7881979 | 3814001 | AbCLXF | 89 | 497 |
| 4955576 | 887598 | ABcLxg | 121 | 385 |
| 3653460 | 3585482 | AbCLXJ | 130 | 174 |
| 1231990 | 1283439 | AbCLYH | 189 | 429 |
| 6110615 | 2042637 | ABcLyh | 157 | 40 |
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (0.01 sec)
- 第二种：索引优化失效
1. mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = ‘abc’;
2.
mysql> SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 5301379 | 1233401 | AbCHEa | 164 | 259 |
| 7170042 | 3102064 | ABcHeB | 199 | 161 |
| 1901614 | 1833636 | ABcHeC | 226 | 275 |
| 5195021 | 1127043 | abchEC | 486 | 72 |
| 4047089 | 3810031 | AbCHFd | 268 | 210 |
| 4917074 | 849096 | ABcHfD | 264 | 442 |
| 1540859 | 141979 | abchFF | 119 | 140 |
| 5121801 | 1053823 | AbCHFg | 412 | 327 |
| 2441254 | 2373276 | abchFJ | 170 | 362 |
| 7039146 | 2971168 | ABcHgI | 502 | 465 |
| 1636826 | 1580286 | ABcHgK | 71 | 262 |
| 374344 | 474345 | abchHL | 367 | 212 |
| 1596534 | 169191 | AbCHHl | 102 | 146 |
...
| 5266837 | 1198859 | abclXe | 292 | 298 |
| 8126968 | 4058990 | aBClxE | 316 | 150 |
| 4298305 | 399962 | AbCLXF | 72 | 423 |
| 5813628 | 1745650 | aBClxF | 356 | 323 |
| 6980448 | 2912470 | AbCLXF | 107 | 78 |
| 7881979 | 3814001 | AbCLXF | 89 | 497 |
| 4955576 | 887598 | ABcLxg | 121 | 385 |
| 3653460 | 3585482 | AbCLXJ | 130 | 174 |
| 1231990 | 1283439 | AbCLYH | 189 | 429 |
| 6110615 | 2042637 | ABcLyh | 157 | 40 |
+---------+---------+--------+------+---------+
401 rows in set, 1 warning (3.62 sec)
type为“ALL”，表示没有使用到索引，查询时间为 3.62 秒，查询效率较之前低很多。
- 再举例：
1. student表的字段stuno上设置有索引- CREATE INDEX idx_sno ON student(stuno);
2. EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
3. 运行结果：
4. 
- 索引优化生效：
1. EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000;
2. 再举例：- student表的字段name上设置有索引
- CREATE INDEX idx_name ON student(NAME);
- EXPLAIN SELECT id, stuno, name FROM student WHERE SUBSTRING(name, 1,3)=‘abc’;
- EXPLAIN SELECT id, stuno, NAME FROM student WHERE NAME LIKE ‘abc%’;
- 类型转换导致索引失效
- 下列哪个sql语句可以用到索引。（假设name字段上设置有索引）
- 未使用到索引
- EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
-
使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=‘123’;
- name=123发生类型转换，索引失效。
- 范围条件右边的列索引失效
ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_age;
ALTER TABLE student DROP INDEX idx_age_classid;
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;
create index idx_age_name_classid on student(age,name,classid);
将范围查询条件放置语句最后：
- 不等于(!= 或者<>)索引失效
- is null可以使用索引，is not null无法使用索引
- EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
- EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
- like以通配符%开头索引失效
- OR 前后存在非索引的列，索引失效
-
未使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
- #使用到索引 EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR name = ‘Abel’;
- 数据库和表的字符集统一使用utf8mb4
- 统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不 同的 字符集 进行比较前需要进行 转换 会造成索引失效。
关联查询优化
- 采用左外连接
- 下面开始 EXPLAIN 分析
- EXPLAIN SELECT SQL_NO_CACHE * FROM
typeLEFT JOIN book ON type.card = book.card; 
- 结论：type 有All
- 添加索引优化
- ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描
- EXPLAIN SELECT SQL_NO_CACHE * FROM
typeLEFT JOIN book ON type.card = book.card; - 
708
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
