基础
MySQL概述
数据库相关概念
-
数据库
数据存储的仓库
-
数据库管理系统（DBMS）
操纵和管理数据库的大型软件
-
SQL
操作关系型数据库的编程语言，是一套标准
centos7安装MySQL
-
rpm -qa | grep mysql // 这个命令会查看该操作系统上是否已经安装了mysql数据库
-
rpm -e mysql // 普通删除模式
rpm -e --nodeps mysql // 强力删除模式，如果使用上面命令删除时，提示有依赖的其它文件，则用该命令可以对其进行强力删除
-
rpm -qa | grep mysql //查看是否删除成功
-
centos7示例：yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
检查是否安装：yum repolist enabled | grep "mysql.-community."
-
需要选择版本时
查看源启用情况：yum repolist all | grep mysql
安装需要的包：yum install -y yum-utils
禁用5.7版本：yum-config-manager --disable mysql57-community
安装8.0版本：yum-config-manager --enable mysql80-community
-
yum install -y mysql-server mysql mysql-devel //下载安装MySQL
-
查看版本: rpm -qi mysql-server
-
启动服务: service mysqld start
重启服务: service mysqld restart
停止服务: service mysqld stop
-
chkconfig --list | grep mysqld //查看是否开机启动
mysqld 0:关闭 1:关闭 2:关闭 3:关闭 4:关闭 5:关闭 6:关闭
启用开机自启：chkconfig mysqld on
-
修改密码
grep 'temporary password' /var/log/mysqld.log //查看密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '000000'; //登陆以后进行修改（8.0需要大小写字母加数字）
-
8.0新特性：
-
修改密码校验规则与密码长度
set global validate_password.policy=0;
set global validate_password.length=1;
-
默认无法远程连接
update mysql.user set host='%' where user="root"; //修改root可以远程连接
flush privileges;
-
mysql8 之前的版本中加密规则是mysql_native_password, 而在mysql8之后,加密规则是caching_sha2_password
ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; //修改加密规则
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '000000'; //刷新用户密码
-
授权方式的改变
grant all privileges on . to 'kevin'@'%' with grant option;
-
docker安装MySQL
启动时出现Data Dictionary initialization failed.
解决步骤如下
rm -rf /var/lib/mysql
# 初始化数据库
mysqld --initialize --console //随机密码
mysqld --initialize-insecure //空密码
# 目录授权
chown -R mysql:mysql /var/lib/mysql/
# 启动MySQL
mysqld --user root &
MySQL数据库
关系型数据库
概念：建立在关系模型上，由多张相互连接的二维表组成的数据库。
特点：
- 使用SQL语言操作，标准统一，使用方便
- 使用表存储数据，格式统一，便于维护
数据模型
SQL
SQL通用语法
- SQL语句可以单行或多行书写，以分号结尾
- SQL语句可以使用空格/缩进来增强语句的可读性
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
- 注释：
- 单行注释：-- 注释内容 或 # 注释内容（MySQL特有）
- 多行注释：/* 注释内容 */
SQL分类
注：后文所有[ ... ]都为可选参数
DDL
数据库操作
查询
-
查询所有数据库
SHOW DATABASES; -
查询当前数据库
SELECT DATABASE();
创建
CREATE DATABASE [IF NOT EXISTS（如果存在则不进行任何操作）] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
删除
DROP DATABASE [IF EXISTS] 数据库名;
使用
USE 数据库名;
表操作
查询
-
查询当前数据库所有表
SHOW TABLES; -
查询表结构
DESC 表名; -
查询指定表的建表语句
SHOW CREATE TABLE 表名;
创建
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
字段3 字段3类型 [COMMENT 字段3注释],
字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];
数据类型
修改
-
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT注释] [约束]; -
修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度); -
修改字段名和数据类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束]; -
删除字段
ALTER TABLE 表名 DROP 字段名; -
修改表名
ALTER TABLE 表名 RENAME TO 新表名; -
删除表
DROP TABLE [IF EXISTS] 表名; -
删除指定表，并重新创建该表
TRUNCATE TABLE 表名;
DML
添加数据
-
给指定字段添加数据
INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...); -
给全部字段添加数据
INSERT INTO 表名 VALUES (值1，值2,...); -
给指定字段批量添加数据
INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...); -
给全部字段批量添加数据
INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...);
修改数据
UPDATE 表名 SET 字段名=值1, 字段名2=值2,...[WHERE 条件];
删除数据
DELETE FROM 表名 [WHERE 条件]
注意：
- WHERE是可选的，如果没有则会删除整张表的数据
- DELETE语句不能删除某一个字段的值，如需要可以使用UPDATE
DQL
语法
基本查询
-
查询多个字段
SELECT 字段1, 字段2, 字段3... FROM 表名;SELECT * FROM 表名; //查询全部字段 -
设置别名
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2] ... FROM 表名; SELECT 字段1 别名1, 字段2 别名2 ... FROM 别名; -
去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
条件查询
-
语法
SELECT 字段列表 FROM 表名 WHERE 条件列表; -
条件列表
分组查询
-
语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]; -
WHERE 和 HAVING 区别
- 判断时机不同：WHERE 是分组前进行过滤，不满足WHERE 条件，不参与分组；而HAVING 是分组以后对结果进行过滤。
- 判断条件不同：WHERE 不能对聚合函数进行判断，而HAVING 可以。
-
注意：
- 执行顺序：WHERE > 聚合函数 > HAVING
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
排序查询
-
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2; -
排序方式
- ASC:升序(默认值)
- DESC:降序
-
注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
分页查询
-
语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数; -
注意：
- 起始页索引从 0 开始，起始页索引 = (查询页码 - 1) * 每页记录数
- 分页查询是数据库的方言，不同的数据库有不同的实现方式，MySQL中是LIMIT;
- 如果查询的是第一页数据，起始索引可以省略，直接简写为LIMIT 10。
DCL
用户管理
-
查询用户
USE mysql; SELECT * FROM user; -
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; -
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'; //mysql_native_password为本地密码处理方式 -
删除用户
DROP USER '用户名'@'主机名';
权限控制
-
查询权限
SHOW GRANTS FOR '用户名'@'主机名'; -
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; -
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'; -
权限列表
-
注意
- 多个权限之间，使用逗号分隔
- 授权时，数据库名和表名可以使用*进行通配，代表所有
函数
聚合函数
-
介绍：将一列数据作为一个整体，进行纵向计算。
-
常见聚合函数
-
语法
SELECT 聚合函数(字段列表) FROM 表名;注意：null值不参与聚合函数计算
字符串函数
数值函数
日期函数
流程控制函数
约束
概述
-
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
-
目的：保证数据库中数据的正确、有效性和完整性。
-
语法
CREATE TABLE 表名( 字段名 约束 COMMENT 注释, 字段名 约束 约束 COMMENT 注释 )COMMENT 表注释 注：同一字段多个约束可用空格隔开 -
分类
-
注意：约束是作用于表中字段的，可以在创建表/修改表的时候添加约束。
外键约束
-
概念：外键用来让两张表的数据建立连接，从而保证数据的一致性和完整性。
-
添加外键
-
建表时添加
CREATE TABLE 表名( 字段名 数据类型 ... [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ); -
已有表添加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);
-
-
删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称; -
外键更新_删除行为
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;
多表查询
多表关系
- 一对多(多对一)
- 案例：部门与员工的关系
- 关系：一个部门对应多个员工，一个员工对应一个部门
- 实现：在多的一方建立外键，指向一的一方的主键
- 多对多
- 案例：学生和课程的关系
- 关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
- 实现：建立第三张中间表，中间至少包含两个外键，分别关联两方主键
- 一对一
- 案例：用户与用户详情的关系
- 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
- 实现：在任意一方加入外键，关联另外一方的主键，并设置外键为唯一的(UNIQUE)
多表查询概述
-
概述：指从多张表中查询数据
-
笛卡尔积：笛卡尔积是指在数学中，两个集合 A集合和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)
-
多表查询分类
-
连接查询
-
内连接：相当于查询A 、B交集部分数据
-
隐式内连接：语法简单，方便书写
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...; -
显式内连接：减少字段的扫描，有更快的执行速度
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
-
-
外连接
-
左外连接：查询左表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...; -
右外连接：查询右表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;
-
-
自连接：当前表与自身的连接查询，自连接必须使用表别名
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件自连接查询可以是内连接查询，也可以是外连接查询。
-
-
联合查询：把多次查询结果合并，形成一个新的查询结果集
SELECT 字段列表 FROM 表A ... UNION [ALL] //UNION ALL 直接将结果合并，UNION 将结果合并以后会进行去重操作 SELECT 字段列表 FROM 表B ...;对于联合查询的多张表的列数必须保持一致，字段列表也需要保持一致。
-
子查询
概念：SQL语句中嵌套SELECT语句,称为嵌套查询,又称子查询
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
-
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=
-
列子查询
子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询
常用操作符：
-
行子查询
子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询。
常用的操作符：= 、 <> 、 IN 、 NOT IN
SELECT 字段列表 FROM 表名 WHERE (字段1, 字段2...) = (SELECT 字段1, 字段2... FROM 表名 WHERE 条件) -
表子查询
子查询返回的结果是多行多列，这种子查询被称为表子查询。
常用的操作符：IN
-
-
事务
事务简介
事务是一组操作的集合，它是一个不可分割的工作单位，事务会将所有操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
注：MySQL的事务是默认自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务
事务操作
方式一
-
查看/设置事务提交方式
SELECT @@AUTOCOMMIT; //查看事务提交方式 SET @@AUTOCOMMIT = 0; //设置事务提交方式，0 为手动提交，1 为自动提交 -
提交事务
COMMIT; -
回滚事务
ROLLBACK;
方式二
-
开启事务
START TRANSACTION 或 BEGIN; -
提交事务
COMMIT; -
回滚事务
ROLLBACK;
区别：方式一直接将所有操作改为手动提交事务；方式二则是单独开启一组事务，仅影响当前这组操作的提交。
事务四大特性
- 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读到另一个事务还没提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在 |
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read Uncommitted | √ | √ | √ |
| Read Committed | × | √ | √ |
| Repeatable Read(默认) | × | × | √ |
| Serializable | × | × | × |
- √表示在当前隔离级别下该问题会出现
- Serializable 性能最低；Read uncommitted 性能最高，数据安全性最差
查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL 隔离级别;
注：SESSION 是会话级别，表示只针对当前会话有效，GLOBAL 表示对所有会话有效
进阶
存储引擎
MySQL体系结构
存储引擎简介
简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
语法
-
创建表时指定存储引擎
CREATE TABLE 表名( 字段1 字段1类型 [COMMENT 字段1注释] 字段2 字段2类型 [COMMENT 字段2注释] )ENGINE=INNODB [COMMENT 表注释] -
查看数据库支持的存储引擎
SHOW ENGINES;
存储引擎特点
InnoDB
-
介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的MySQL存储引擎
-
特点
DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键 FOREIGN KEY 约束，保证数据的完整性和正确性；
-
文件
xxx.idb：xxx代表表名，InnoDB引擎的每张表都会对应这样的一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
参数：innodb_file_per_table //默认打开
该参数开启代表一张表对应一个单独的表空间；关闭代表所有表共用一个表空间。
-
逻辑存储结构
MYISAM
-
介绍
MyISAM是MySQL早期的默认存储引擎
-
特点
不支持事务，不支持外键
支持表锁，不支持行锁
访问速度快
-
文件
xxx.sdi：存储表结构信息
xxx.MYD：存储数据
xxx.MYI：存储索引
Memory
-
介绍
Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
-
特点
内存存放，速度快
hash索引（默认）
-
文件
xxx.sdi：存储表结构信息
总览
存储引擎选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合
- InnoDB:是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致
性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
心 - MISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那
么选择这个存储引擎是非常合适的。 - MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表
无法缓存在内存中，而且无法保障数据的安全性。
索引
概述
介绍
索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库还维护着满足特定查找算法的数据结构，这些数据结构是以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
优缺点
| 优势 | 劣势 |
|---|---|
| 提高检索的效率，降低数据库的IO成本 | 索引列也要占用空间 |
| 通过索引列对数据进行排序，降低数据排序成本，降低cpu的消耗 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低 |
结构
介绍
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+Tree索引 |
| Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-Tree（空间索引） | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text（全文索引） | 是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene，Solr,ES |
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-Tree | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本以后支持 | 支持 | 不支持 |
-
二叉树
-
B-Tree
-
B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
-
Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到同一个相同的槽位上，他们就产生了冲突（也称为hash碰撞），可以通过链表解决。
hash索引特点
- Hash索引只能用于对等比较（=，in），不支持范围查询（between，>，<）
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以了，效率要高于B+Tree索引
分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键字，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引（Clustered Index） | 将数据结构与索引放到一起，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引（Secondary Index） | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引规则
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
语法
创建
CREATE [索引类型] INDEX index_name ON table_name (index_col_name,...);
- 如果不指定索引类型，则代表创建一个常规索引
- 一个索引关联一个字段叫单列索引
- 一个索引关联多个字段叫联合索引或组合索引
查看
SHOW INDEX FROM table_name;
删除
DROP INDEX index_name ON table_name;
SQL性能分析
-
SQL执行频率
MySQL客户端连接成功后，通过show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次
session表示当前会话，global表示全局
SHOW GLOBAL STATUS LIKE 'Com_______'; //七个下划线 -
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件中配置
慢查询日志文件存放位置：/var/lib/mysql/localhost-slow.log
SHOW VARIABLES LIKE 'show_query_log'; //查看慢查询日志是否开启slow_query_log=1 //开启MySQL慢日志开关 long_query_time=2 //设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志 -
profile
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
SELECT @@have_profiling;默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：
SET profiling=1;执行一系列的业务SQL操作，然后通过如下指令查看指令的执行耗时：
//查看每一条SQL的耗时基本情况 show profiles; //查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; //查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id; -
explain执行计划
EXPLAIN 或者 DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法：
// 直接在SELECT语句之前加上关键字 EXPLAIN / DESC EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;EXPLAIN执行计划各字段含义：
-
id
select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
-
select_type
表示SELECT的类型，常见的取值有SIMPLE（简单表，即不用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
-
type（重点）
表示连接类型，性能由好到差的连接类型为NULL（不访问表）、system（访问系统表）、const（访问主键或者唯一索引）、eq_ref、ref（使用非唯一性的索引查询）、range、index（对整个索引树进行扫描遍历）、all（全表扫描）
-
possible_key（重点）
显示可能应用在这张表上的索引，一个或多个。
-
key（重点）
实际用到的索引，如果没有则显示NULL
-
key_len（重点）
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
-
rows
MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
-
filtered
表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。
-
Extra
额外信息
-
索引使用规则
最左前缀法则
如果索引了多列（联合查询），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列字段开始（最左字段必须存在，与位置无光），并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引将失效）。
范围查询
联合查询中，出现范围查询（<，>），范围查询右侧的列索引失效。（可以使用<=或者>=来规避这种情况）
索引列运算
不要在索引列上进行运算操作，索引将失效。
字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。（存在隐式类型转换，进行了运算）
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
or连接的条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引
SQL提示
SQL提示，是优化数据库的一个主要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的
use index：建议使用某个索引（不代表一定会使用）
explain select 字段名 from 表名 use index(索引名) where 条件;
ignore index：忽略某个索引
explain select 字段名 from 表名 ignore index(索引名) where 条件;
force index：强制使用某个索引
explain select 字段名 from 表名 force index(索引名) where 条件;
覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经能全部找到），减少select *。（本质上为了不进行回表查询）
回表查询
前缀索引
当前字段类型为字符串（varchar、text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率
-
语法
create index 索引名 on 表名(字段名(前缀长度)); -
前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct 字段名) / count(*) from 表名; select count(distinct substring(字段名,1,5)) / count(*) from 表名; -
前缀索引查询流程
单列索引与联合索引
单列索引：即一个索引只包含单个列
联合索引：即一个索引包含多个列
在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引
索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引。
- 针对于常作为查询条件（where)、排序(order by)、分组(group by)操作的字段建立索引。
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
SQL优化
insert优化
批量插入
//一次性插入多条数据性能更好，建议一次插入500-1000条，性能最优
INSERT INTO 表名 VALUES(数据1),(数据2);
手动提交事务
MySQL默认自动提交事务，每次进行插入数据都有一次事务的开关，影响性能。多次插入数据时建议手动提交事务
START TRANSACTION;
INSERT INTO 表名 VALUES(数据1),(数据2);
INSERT INTO 表名 VALUES(数据1),(数据2);
INSERT INTO 表名 VALUES(数据1),(数据2);
COMMIT;
主键顺序插入
主键顺序插入数据性能更好
主键乱序插入:8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入:1 2 3 4 5 7 8 9 15 2l 88 89
大批量插入数据
如果一次性需要插入大批量数据，使用insert语句插入性能较差，此时可以使用MySQL数据库提供的load指令进行插入
//客户端连接服务端时，加上参数--local-infile
mysql --local-infile -u -p
//设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile=1;
//执行load指令将准备好的数据，加载到表结构中
load data local infile '数据路径' into table '表名' fields terminated by '数据中字段的分割符' lines terminated by '每条数据的分割符';
主键优化
数据组织方式
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。
页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。
-
主键顺序插入（通过对比数据插入流程可以得出顺序插入性能更高）
-
主键乱序插入
在乱序中插入一条数据时，第一页满了，会创建第三页
选中第一页大于50%的数据，将其移动到第三页，同时数据也插入第三页
最后将主键顺序排列，将第二页和第三页调换顺序，这个过程称为页分裂
页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录为被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 MERGE_THRESHOLD （默认为页的50%），innodb会开始寻找靠近的页（前或后）看看是否可以将两个页进行合并以优化空间使用。
注：MERGE_THRESHOLD：页合并的阈值，可以自己设置，在创建表或者创建索引时指定，默认为50%
删除记录时，进行标记
达到页的一半后，将两页进行合并
主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。（降低检索时的IO损耗）
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
- 尽量不要使用UUID（随机性）做主键或者其他自然主键，如身份证号（长度过长）
- 在业务操作时，尽量避免对主键的修改
order by优化
Using filesort
通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
Using index
通过有序索引顺序扫描直接返回有序数据，这种情况为using index，不需要额外排序，操作效率高。
优化规则
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC / DESC）
- 如果不可避免的出现filesort，大数据量排序时，可以适当增加排序缓存区大小 sort_buffer_size (默认256k)
group by优化
- 在分组操作时，可以通过索引来提高效率
- 分组操作时，索引的使用也是满足最左前缀法则的
limit by优化
一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化思路：一般分页查询时，通过创建索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
EXPLAIN SELECT * FROM 表一 表一别名, (SELECT 字段名 FROM 表一 ORDER BY 字段名 LIMIT 200000,10) 表二别名 WHERE 表一别名.字段名 = 表二.字段名
count优化
MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行count(*)时会直接返回这个数，效率很高（前提：不存在where）
InnoDB 引擎执行count(*)时，需要把数据一行一行地从引擎里面读出来，然后累计计数，效率较低
count的几种用法
-
count(主键)
InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)（性能第三）
-
count(字段)
没有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层服务层判断是否为null，不为null，计数累加。（性能最差）
有not null约束: InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
-
count(1)
InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。（性能类似count*）
-
count(*)
InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。（性能最高）
update优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁，大大影响并发性能。
视图/存储过程/触发器
视图
视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上
语法
创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
查询
查看创建视图语句：
SHOW CREATE VIEW 视图名称;
查看视图数据：
SELECT * FROM 视图名称 ...;
修改
方式一：
CREATE OR REPLACE VIEW 视图名称[(列名列表)] AS SELECT 语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
// 修改视图时 OR REPLACE参数是必选的
方式二：
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPION]
删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
视图的检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项:CASCADED和LOCAL，默认值为CASCADED。
CASCADED
该关键字会检查所有依赖视图规定的范围（即使依赖视图并没有添加WITH CHECK OPTION）
LOCAL
该关键字会检查所有定义了WITH CHECK OPTION的依赖视图规定的范围
视图的更新
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新
- 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）
- DISTINCT
- GROUP BY
- HAVING
- UNION 或者 UNION ALL
作用
-
简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作指定全部的条件
-
安全
数据库可以授权，但不能授权到数据库特定行和特定列上。通过视图用户只能查询和修改他们所能见到的数据
-
数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响
存储过程
介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用
特点
- 封装，复用
- 可以接收参数，也可以返回数据
- 减少网络交互，效率提升
语法
创建
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
--SQL语句
END;
注：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符；语法：delimiter $$将结束符改为$$
调用
CALL 名称 ([参数]);
查看
// 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名';
// 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;
删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;
变量
系统变量
系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量（GLOBAL）、会话变量（SESSION）（默认）
-
查看系统变量
//查看所有系统变量 SHOW [SESSION|GLOBAL] VARIABLES; //可以通过LIKE模糊匹配方式查找变量 SHOW [SESSION|GLOBAL] VARIBLES LIKE '...'; //查看指定变量的值 SELECT @@[SESSION|GLOBAL] 系统变量名; -
设置系统变量
SET [SESSION|GLOBAL] 系统变量名=值; SET @@[SESSION|GLOBAL] 系统变量名=值; -
注意
如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量
mysql服务重新启动后，所设置的全局参数会失效，想要不失效，需要在/etc/my.cnf中配置
用户变量
用户变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用即可。其作用域为当前连接
-
赋值
SET @变量名1=参数[,@变量名2=参数2]...; SET @变量名1:=参数[,@变量名2:=参数2]...; //推荐使用 原因:MySQL的比较运算符也是=，为了区分SELECT @变量名1:=参数1[,@变量名2:=参数2]...; SELECT 字段名 INTO @变量名 FROM 表名; //将查询的结果赋值给变量 -
使用
SELECT @变量名; -
注意
用户定义的变量无需对其声明或初始化，只不过获取到的值为NULL
局部变量
局部变量是根据需要定义在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其声明的BEGIN ... END块
-
声明
DECLARE 变量名 变量类型[DEFAULT ...]; // 变量类型就是数据库字段类型 -
赋值
SET 变量名=值; SET 变量名:=值; SELECT 字段名 INTO 变量名 FROM 表名 ...;
if判断
-
语法
IF 条件1 THEN ... ELSELF 条件2 THEN ... ELSE ... END IF;
参数
| 类型 | 含义 | 备注 |
|---|---|---|
| IN | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 | |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |
-
用法
CREATE PROCEDURE 存储过程名称 ([IN/OUT/INOUT 参数名 参数类型]) BEGIN --SQL语句 END;
case
-
语法一
CASE case_value WHEN when_value1 THEN statement_list1 [WHEN when_value2 THEN statement_list2] [ELSE statement_list] END CASE; -
语法二
CASE WHEN search_condition1 THEN statement_list1 [WHEN search_condition2 THEN statement_list2] [ELSE statement_list] END CASE;
while
while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：
//先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑
END WHILE;
repeat
repeat是有逻辑的循环控制语句，当满足条件的时候退出循环。具体语法为：
// 先执行一次逻辑，然后判断逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL逻辑
UNTIL 条件
END REPEAT;
loop
LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合以下两个语句使用
-
LEAVE：配合循环使用，退出循环
-
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环
-
语法
[begin_label:] LOOP SQL逻辑 END LOOP [end_label];
游标
游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法如下
-
声明游标
DECLARE 游标名称 CURSOR FOR 查询语句; -
打开游标
OPEN 游标名称; -
获取游标记录
FETCH 游标名称 INTO 变量[,变量]; -
关闭游标
CLOSE 游标名称;
条件处理程序
存储函数
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic ...]
BEGIN
--SQL语句
RETURN ...;
END;
characteristic说明：
DETERMINISTIC：相同的输入参数总是产生相同的结果
NO SQL：不包含SQL语句
READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句
触发器
介绍
触发器是与表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发
| 触发器类型 | NEW 和 OLD |
|---|---|
| INSERT 型触发器 | NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 | OLD 表示修改之前的数据，NEW 表示将要或以及修改后的数据 |
| DELETE 型触发器 | OLD 表示将要或以及删除的数据 |
语法
-
创建
CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW --行级触发器（固定语法） BEGIN trigger_stmt; END; -
查看
SHOW TRIGGERS; -
删除
DROP TRIGGER [schema_name.]trigger_name; //如果没有指定schema_name，默认为当前数据库
锁
介绍
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言尤其重要也更加复杂。
全局锁
介绍
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将阻塞。
其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
全库备份演示
特点
数据库中加全局锁，是一个比较重的操作，存在以下问题
- 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就停摆
- 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟
在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份
mysqldump --single-transaction -uroot -p 数据库名 > 数据库名.sql
表级锁
介绍
表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中
表锁
表共享读锁（read lock）
当前客户端可以读，不能写
其他客户端可以读，不能写
表独占写锁（write lock）
当前客户端可以读，可以写
其他客户端不能读，不能写
语法
- 加锁：lock tables 表名... read/write
- 释放锁：unlock tables // 客户端断开连接
元数据锁（meta data lock，MDL）
MDL加锁过程是系统自动控制的，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
在MySQL5.5中引入了MDL，当对一张表进行增删查改的时候，加MDL读锁（共享）；当对表结构进行变更操作时，加MDL写锁（排他）
查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
意向锁
如果一个任务需要在一张表上应用共享或排他锁时，需要检查该表中每一行是否有排他锁，效率慢。有意向锁后，这个任务则会被意向锁阻塞，无需检查各行是否有排他锁
意向锁不会与行级共享/排他锁互斥
- 意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排他锁（write）互斥
- 意向排他锁（IX）：与表锁共享锁（read）及排他锁（write）都互斥。意向锁之间不会互斥。
查看意向锁及行锁的加锁情况
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
行级锁
行级锁，每次操作之前锁住对应的行数据。锁定力度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB引擎中
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类
- 行锁（Record Lock）：锁定单个记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
- 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持
- 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持
默认情况下，InnoDB在REPEATABLE READ 事务隔离级别下运行，InnoDB使用 next-key 锁进行搜索和索引扫描以防止幻读
行锁
InnoDB实现了一下两种类型的行锁
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同的数据集的共享锁和排他锁
| 当前锁类型/请求锁类型 | S（共享锁） | X（排他锁） |
|---|---|---|
| S（共享锁） | 兼容 | 冲突 |
| X（排他锁） | 冲突 | 冲突 |
- 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
- InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中所有数据记录都加锁，此时就会升级为 表锁
间隙锁/临键锁
间隙锁的唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁
- 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
- 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
- 索引上的范围查询（唯一索引）--会访问到不满足条件的第一个值为止
InnoDB核心
逻辑存储结构
架构
内存结构
Buffer Pool
缓存池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度
缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型
- free page：空闲page，未被使用
- clean page：被使用page，数据没有被修改过
- dirty page：脏页，被使用过page，数据被修改过，页中数据与磁盘的数据产生了不一致
Change Buffer
更改缓冲区（针对非唯一二级索引页），在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区Change Buffer中，在未来数据被读取时，再将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中
Change Buffer的意义
与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了ChangeBuffer之后，我们可以再缓冲池中进行合并处理，减少磁盘IO
Adaptive Hash Index
自适应hash索引，用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称为自适应hash索引
自适应哈希索引，无需人工干预，是系统根据情况自动完成
参数：adaptive_hash_index
Log Buffer
日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、 undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除多行的事务，增加日志缓冲区的大小可以节省磁盘I/O
参数
innodb_log_buffer_size：缓冲区大小
innodb_flush_log_at_trx_commit：日志刷新到磁盘时机
1：日志在每次事务提交时写入并刷新到磁盘
0：每秒将日志写入并刷新到磁盘一次
2：日志在每次事务提交后写入，并每秒刷新到磁盘一次
磁盘结构
System Tablespace
系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。（在MySQL5.x版本中还包含InnoDB数据字典、undolog等）
参数：innodb_data_file_path
File-Per-Table Tablespaces
每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中
参数：innodb_file_per_table
General Tablespaces
通用表空间，需要通过CREATE TABLESPACE语法创建通用表空间，在创建表时可以指定该表空间
创建表空间
CREATE TABLESPACE 表空间名 ADD
DATAFILE "表空间关联的表空间文件"
ENGINE = 存储引擎
创建表空间时指定表空间
CREATE TABLE ... TABLESPACE 表空间名;
Undo Tablespaces
撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志
Temporary Tablespaces
InnoDB使用会话临时表空间和全局临时表空间，存储用户创建的临时表等数据
Doublewrite Buffer Files
双写缓冲区，InnoDB引擎将数据页从Buffer Pool刷新到磁盘之前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据
Redo Log
重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中，用于刷新脏页到磁盘时，发生错误时，进行数据恢复使用
后台线程
Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保存数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收
IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求，这样可以极大地提高数据库的性能，而IO Thread主要负责这些IO请求的回调
| 线程类型 | 默认个数 | 职责 |
|---|---|---|
| Read thread | 4 | 负责读操作 |
| Write thread | 4 | 负责写操作 |
| Log thread | 1 | 负责将日志缓冲区刷新到磁盘 |
| Insert buffer thread | 1 | 负责将写缓冲区内容刷新到磁盘 |
Purge Thread
主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就使用它来回收
Page Cleaner Thread
协助Master Thread 刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞
事务原理（一致性）
redo log（持久性）
重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会将所有信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。
undo log（原子性）
回滚日志，用于记录数据被修改前的信息，作用包括两个：提供回滚和MVCC
undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录（保持记录前的数据），当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚
-
undo log销毁：undo log在事务执行时产生，事务提交时，可立即删除，但是并不会立即删除undo log，因为这些日志可能还用于MVCC
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也被需要，不会立即被删除
-
undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment
MVCC（隔离性）
基本概念
-
当前读
读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：
select ... lock in share mode（共享锁），select ... for update、update、insert、delete（排他锁）都是一种当前读
-
快照读
简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读
- Read Committed：每次select，都生成一个快照读
- Repeatable Read：开启一个事务后第一个select语句才是快照读的地方
- Serializable：快照读会退化为当前读
-
MVCC
全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖与记录中的三个隐式字段，undo log日志、readView
隐式字段
| 隐藏字段 | 含义 |
|---|---|
| DB_TRX_ID | 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID |
| DB_ROLL_PTR | 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本 |
| DB_ROW_ID | 隐藏主键，如果表结构没指定主键，将会生成该隐藏字段 |
注：在命令行中可以使用ibd2sdi命令查看ibd文件信息
MVCC实现原理
-
undo log版本链
不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录
-
readView
ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护当前活跃的事务（未提交的）id
ReadView中包含了四个核心字段
字段 含义 m_ids 当前活跃的ID集合 min_trx_id 最小活跃事务ID max_trx_id 预分配事务ID，当前最大事务ID+1（事务ID是自增的） creator_trx_id ReadView创建者的事务ID 
MySQL管理
系统数据库介绍
MySQL数据库安装完成后，自带了以下四个数据库，具体作用如下
| 数据库 | 含义 |
|---|---|
| mysql | 存储MySQL服务器正常运行所需要的各种信息（时区、主从、用户、权限等） |
| information_schema | 提供了访问数据库元数据的各种表和视图，包括数据库、表、字段类型及访问权限等 |
| performance_schema | 为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数 |
| sys | 包含了一系列方便DBA和开发人员利用performance_schema 性能数据库进行性能调优和诊断的视图 |
常用工具
mysql
mysqladmin
mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等
//通过帮助文档查看选项
mysqladmin --help
mysqlbinlog
mysqlshow
mysqldump
mysqlimport/source
运维
日志
错误日志
错误日志是MySQL中最重要的日志之一，它记录了当mysqld启动或停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志
该日志是默认开启的，默认存放目录/var/log/ ，默认的日志文件名为 mysqld.log 。查看日志位置：
show variables like '%log_error%'
二进制日志
介绍
二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。
作用：
- 灾难时的数据恢复
- MySQL的主从复制
在MySQL8版本中，默认二进制日志是开启的，涉及到的参数如下
show variable like '%log_bin%'
日志格式
MySQL服务器中提供了多种格式来记录二进制日志，具体格式及特点如下
| 日志格式 | 含义 |
|---|---|
| STATEMENT | 基于SQL语句的日志记录，记录的是SQL语句，对数据进行修改的SQL语句都会记录在日志文件中 |
| ROW | 基于行的日志记录，记录的是每一行的数据变更 |
| MIXED | 混合了STATEMENT和ROW两种数据格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录 |
show variables like '%binlog_format%'
日志查看
由于日志是以二进制方式存储的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog来查看，具体语法查看MySQL管理常用工具
日志删除
对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果不长时间清除，将会占用大量磁盘空间。可以通过以下几种方式清理日志
| 指令 | 含义 |
|---|---|
| reset master | 删除全部binlog日志，删除之后，日志编号，将从binlog.000001重新开始 |
| purge master logs to 'binlog.******' | 删除******编号之前的所有日志 |
| purge master logs before 'yyyy-mm-dd hh24:mi:ss' | 删除日志为yyyy-mm-dd hh24:mi:ss之前产生的所有日志 |
也可以在MySQL的配置文件中配置二进制日志的过期时间，设置以后，二进制日志过期自动删除
//单位默认为秒
show variables like '%binlog_expire_logs_seconds%'
查询日志
查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。默认情况下，查询日志是不开启的，如果需要开启查询日志，可以设置以下配置
修改MySQL的配置文件，添加以下内容
// 该选项用来开启查询日志，可选值：0 或 1; 0代表关闭，1代表开启
general_log=1
// 设置日志的文件名，如果未指定，默认的文件名为host_name.log
general_log_file=/var/lib/mysql/***.log
慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件中配置
慢查询日志文件存放位置：/var/lib/mysql/localhost-slow.log
SHOW VARIABLES LIKE 'show_query_log'; //查看慢查询日志是否开启
slow_query_log=1 //开启MySQL慢日志开关
long_query_time=2 //设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用两个参数更改此行为
// 记录执行比较慢的管理语句
log_slow_admin_statements=1
// 记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1
主从复制
概述
主从复制是指将数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步
MySQL支持一台主库同时向多台从库进行复制，从库也可以作为其他从服务器的主库，实现链状复制
MySQL复制的特点
- 主库出现问题，可以快速切换到从库提供服务
- 实现读写分离，降低主库的访问压力
- 可以在从库中执行备份，以避免备份期间影响主库服务
原理
主从复制原理如下
- Master 主库在事务提交时，会把数据变更记录在二进制日志文件Binlog 中
- 从库读取主库的二进制日志 Binlog，写入到从库的中继日志Relay Log
- slave 重做中继日志中的事件，将改变反映它自己的数据
搭建
服务器准备环境
//开放指定的3306端口号
firewall-cmd --zone=public --add-port=3306/tcp -permanent
firewall-cmd -reload
//关闭服务器的防火墙
systemctl stop firewalld
systemctl disable firewalld
主库配置
-
修改配置文件 /etc/my.cnf
// mysql服务ID，保证整个集群环境中唯一，取值范围：1-2^32-1，默认为1 server-id=1 // 是否只读，1代表只读，0代表读写 read-only=0 // 忽略的数据，指不需要同步的数据库 binlog-ignore-db=mysql // 指定同步的数据库 binlog-do-db=db01 -
重启MySQL
systemctl restart mysqld -
登录mysql，创建远程连接账号，并授予主从复制权限
// 创建test用户，并设置密码，该用户可在任意主机连接该MySQL服务 CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '000000'; // 为'test'@'%'用户分配主从复制权限 GRANT REPLICATION SLAVE ON *.* TO 'test'@'%'; -
通过指令，查看二进制日志坐标
show master status;字段含义说明
file：从哪个日志开始推送日志文件
position：从哪个位置开始推送文件（依据该位置进行同步）
binlog_ignore_db：指定不需要同步的数据库
从库配置
-
修改配置文件/etc/my.cnf
// mysql服务ID，保证整个集群环境中唯一，取值范围：1-2^32-1，和主库不一样即可 server-id=2 // 是否只读，1代表只读，0代表读写 read-only=1 -
重新启动mysql
systemctl restart mysqld -
登录mysql，设置主从配置
CHANGE REPLICATION SOURCE TO SOURCE_HOST='主库ip地址',SOURCE_USER='主库账号',SOURCE_PASSWORD='主库密码',SOURCE_LOG_FILE='binlog文件名',SOURCE_LOG_POS='binlog日志文件位置';上述是8.0.23中的语法。如果MySQL是8.0.23之前的版本，使用下面的语法（8.0.23之后的版本也可能兼容下面的语法）
CHANGE MASTER TO MASTER_HOST='主库ip地址',MASTER_USER='主库账号',MASTER_PASSWORD='主库密码',MASTER_LOG_FILE='binlog文件名',SOURCE_LOG_POS='binlog日志文件位置'; -
开启同步操作
start replica; //8.0.22之后 start slave; //8.0.22之前
分库分表
读写分离
Q.E.D.