Mysql 全面总结-由浅入深

博主： ChengYu Wang
发布时间：2022 年 09 月 22 日
1812 次浏览
暂无评论
33513字数
分类：默认分类

数据库相关概念

三个概念：数据库、数据库管理系统、SQL。

SQL语言是操作关系型数据库的统一标准。所以即使我们现在学习的是MySQL，假如我们以后到了公司，使用的是别的关系型数据库，如：Oracle、DB2、SQLServer，也完全不用担心，因为操作的方式都是一致的。

下载地址：https://downloads.mysql.com/archives/installe

数据模型

关系型数据库（RDBMS）

概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。而所谓二维表，指的是由行和列组成的表，如下图 （就类似于Excel表格数据，有表头、有列、有行，还可以通过一列关联另外一个表格中的某一列数据） 。我们之前提到的MySQL、Oracle、DB2、 SQLServer这些都是属于关系型数据库，里面都是基于二维表存储数据的。简单说，基于二维表存储数据的数据库就成为关系型数据库，不是基于二维表存储数据的数据库，就是非关系型数据库。

特点

A. 使用表存储数据，格式统一，便于维护。

B. 使用SQL语言操作，标准统一，使用方便。

数据模型

MySQL是关系型数据库，是基于二维表进行数据存储的，具体的结构图下

我们可以通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库。
可以使用SQL语句，通过数据库管理系统操作数据库，以及操作数据库中的表结构及数据。
一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包含多行记录。

SQL语法

全称 Structured Query Language，结构化查询语言。

操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
注释：单行注释：-- 注释内容或 # 注释内容
多行注释：/ 注释内容 */

Sql分类

DDL

DDL Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

数据库操作

查询所有数据库

show databases ;

查询当前数据库

select database() ;

创建数据库

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;

以数据库名称为hand举例
create database hand;
create database if not extists hand;             #不存在则创建
create database hand default charset utf8mb4; #指定字符集
drop database [ if exists ] hand;                #存在则删除
use hand ;                                       #切换到hand库

表操作

查询当前数据库所有表

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 hand ADD nickname varchar(20) COMMENT '昵称';

表操作-修改-数据类型

ALTER TABLE 表名 MODIFY 字段名新数据类型 (长度);

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名新字段名类型 (长度) [ COMMENT 注释 ] [ 约束 ];

删除字段

ALTER TABLE 表名 DROP 字段名;

修改表名

ALTER TABLE 表名 RENAME TO 新表名;

ALTER TABLE hand CHANGE nickname username varchar(30) COMMENT '昵称';
#修改字段名称和数据类型
ALTER TABLE hand DROP username;
#删除表字段
ALTER TABLE hand RENAME TO employee;
#修改表名

表操作-删除

删除表

DROP TABLE [ IF EXISTS ] 表名;

可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不加该参数项，删除一张不存在的表，执行将会报错)。

示例

DROP TABLE IF EXISTS hand; #存在则删除
TRUNCATE TABLE 表名;        #初始化

注意: 在删除表的时候，表中的全部数据也都会被删除。

DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

添加数据（INSERT）
修改数据（UPDATE）
删除数据（DELETE）

添加数据-insert

给指定字段添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);

给全部字段添加数据

INSERT INTO 表名 VALUES (值1, 值2, ...);

批量添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值 1, 值2, ...) ;

INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

添加示例

#字段添加
insert into hand(id,workno,name,gender,age,idcard,entrydate)
values(1,'1','hand','男',10,'123456789012345678','2000-01-01');
#批量添加
insert into hand values(3,'3','韦一笑','男',38,'123456789012345670','2005-01-
01'),(4,'4','赵敏','女',18,'123456789012345670','2005-01-01');

注意事项:

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。

• 字符串和日期型数据应该包含在引号中。

• 插入的数据大小，应该在字段的规定范围内。

修改数据-update

修改数据

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

修改示例

update hand set name = 'hand' where id = 1;
update hand set name = '小昭' , gender = '女' where id = 1;

注意事项:

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据-delete

删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;

删除示例

delete from hand where gender = '女';
delete from hand; #全部删除

注意事项:

• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。

• 当进行删除全部数据操作时，datagrip会提示我们，询问是否确认删除，我们直接点击 Execute即可。

DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。

查询关键字: SELECT 在一个正常的业务系统中，查询操作的频次是要远高于增删改的，当我们去访问企业官网、电商网站，在这些网站中我们所看到的数据，实际都是需要从数据库中查询并展示的。而且在查询的过程中，可能还会涉及到条件、排序、分页等操作。

基本语法

SELECT
     字段列表
FROM
     表名列表
WHERE
     条件列表
GROUP BY
     分组字段列表
HAVING
     分组后条件列表
ORDER BY
     排序字段列表
LIMIT
     分页参数

语法类型

基本查询（不带任何条件）
条件查询（WHERE）
聚合函数（count、max、min、avg、sum）
分组查询（group by）
排序查询（order by）
分页查询（limit）

基础查询

# 查询多个字段
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 条件列表 ;

比较运算符

逻辑运算符

# 查询年龄等于 88 的员工
select * from hand where age = 88;
# 查询年龄小于等于 20 的员工信息
select * from hand where age <= 20;
# 查询没有身份证号的员工信息
select * from hand where idcard is null;
# 查询有身份证号的员工信息
select * from hand where idcard is not null;
# 查询年龄不等于 88 的员工信息
select * from hand where age != 88;
# 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from hand where age >= 15 && age <= 20;
# 查询性别为 女 且年龄小于 25岁的员工信息
select * from hand where gender = '女' and age < 25;
# 查询年龄等于18 或 20 或 40 的员工信息
select * from hand where age = 18 or age = 20 or age =40;
# 查询姓名为两个字的员工信息 _ %
select * from hand where name like '__';
# 查询身份证号最后一位是X的员工信息
select * from hand where idcard like '%X';
select * from hand where idcard like '_________________X';

聚合函数

将一列数据作为一个整体，进行纵向计算。

SELECT 聚合函数(字段列表) FROM 表名 ;
# 统计该企业员工数量
select count(*) from hand; -- 统计的是总记录数
select count(idcard) from hand; -- 统计的是idcard字段不为null的记录数
# 统计该企业员工的平均年龄
select avg(age) from hand;
# 统计该企业员工的最大年龄
select max(age) from hand;
# 统计该企业员工的最小年龄
select min(age) from hand;
# 统计西安地区员工的年龄之和
select sum(age) from hand where workaddress = '西安';

分组查询

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
后过滤条件 ];

where 和 having 区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

• 执行顺序: where > 聚合函数 > having 。

• 支持多字段分组, 具体语法为 : group by columnA,columnB

示例

# 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) from hand group by gender ;
# 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from hand group by gender ;
# 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from hand where age < 45 group by
workaddress having address_count >= 3;
# 统计各个工作地址上班的男性及女性员工的数量
select workaddress, gender, count(*) '数量' from hand group by gender , workaddress
;

排序查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

排序方式

ASC : 升序(默认值)
DESC: 降序

注意事项

• 如果是升序, 可以不指定排序方式ASC ;

• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;

示例

# 根据年龄对公司的员工进行升序排序
select * from hand order by age asc; #默认
select * from hand order by age;
# 根据入职时间, 对员工进行降序排序
select * from hand order by entrydate desc;
# 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from hand order by age asc , entrydate desc;

分页查询

分页操作在业务系统开发时，也是非常常见的一个功能，我们在网站中看到的各种各样的分页条，后台都需要借助于数据库的分页操作。

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:

• *起始索引从0开始，起始索引 = （查询页码 - 1） 每页显示记录数*

• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

示例

# 查询第1页员工数据, 每页展示10条记录
select * from hand limit 0,10;
select * from hand limit 10;
# 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
select * from hand limit 10,10;

DCL

查询用户

select * from mysql.hand;

其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户

DROP USER '用户名'@'主机名' ;

注意事项:

• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。

• 主机名可以使用 % 通配

• 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员）使用。

代码示例

#  创建用户hand, 只能够在当前主机localhost访问, 密码123456;
create user 'hand'@'localhost' identified by '123456';
#  创建用户hand, 可以在任意主机访问该数据库, 密码123456;
create user 'hand'@'%' identified by '123456';
#  修改用户hand的访问密码为1234;
alter user 'hand'@'%' identified with mysql_native_password by '1234';
#  删除 hand@localhost 用户
drop user 'hand'@'localhost';

权限控制

查询权限

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

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项：

• 多个权限之间，使用逗号分隔

*• 授权时，数据库名和表名可以使用 进行通配，代表所有。**

示例

# 查询 'hand'@'%' 用户的权限
show grants for 'hand'@'%';
# 授予 'hand'@'%' 用户hand数据库所有表的所有操作权限
grant all on hand.* to 'hand'@'%';
# 撤销 'hand'@'%' 用户的hand数据库的所有权限
revoke all on hand.* from 'hand'@'%';

函数

MySQL中的函数主要分为以下四类

字符串函数
数值函数
日期函数
流程函数

字符串函数

数值函数

日期函数

流程函数

约束

删除更新行为

多表查询

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多(多对一) 。
多对多
一对一

一对多

案例: 部门与员工的关系
关系: 一个部门对应多个员工，一个员工对应一个部门
实现: 在多的一方建立外键，指向一的一方的主键

多对多

案例: 学生与课程的关系
关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例: 用户与用户详情的关系
关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询描述

多表查询就是指从多张表中查询数据。

*原来查询单表数据，执行的SQL形式为：select from Score;**

那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，

*如： select from Score, Student;**

这种现象称之为笛卡尔积。

在SQL语句中，如何来去除无效的笛卡尔积呢？我们可以给多表查询加上连接查询的条件即可

select * from Student stu , Score sc where stu.s_id = sc.s_id;

连接分类

连接查询

内连接：相当于查询A、B交集部分数据

外连接：

左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询

内连接

内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据) 内连接的语法分为两种: 隐式内连接、显式内连接。

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

表的别名:

①. tablea as 别名1 , tableb as 别名2 ;

②. tablea 别名1 , tableb 别名2 ;

注意事项

一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

外连接

外连接分为两种，分别是：左外连接和右外连接。

左外连接

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

右外连接

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

注意事项

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。

自连接

自连接查询

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

注意事项:

在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。 union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个

根据结果分类

标量子查询（子查询结果为单个值）
列子查询(子查询结果为一列)
行子查询(子查询结果为一行)
表子查询(子查询结果为多行多列)

根据子查询位置分类

WHERE之后
FROM之后
SELECT之后

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。

常用的操作符：= <> > >= < <=

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。

常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。常用的操作符：IN

Mysql

Mysql体系结构

1). 连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2). 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3). 引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。

4). 存储层

数据存储层，主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

存储引擎

大家可能没有听说过存储引擎，但是一定听过引擎这个词，引擎就是发动机，是一个机器的核心组件。比如，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎一样。

而对于存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎。

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果没有指定将自动选择默认的存储引擎。

建表时指定存储引擎

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

查询当前数据库支持的存储引擎

show engines;

实例演示

查询建表语句 --- 默认存储引擎: InnoDB

show create table account;

我们可以看到，创建表时，即使我们没有指定存储疫情，数据库也会自动选择默认的存储引擎。

查询当前数据库支持的存储引擎

show engines ;

创建表 my_myisam , 并指定MyISAM存储引擎

create table my_myisam(
id int,
name varchar(10)
) engine = MyISAM ;

创建表 my_memory , 指定Memory存储引擎

create table my_memory(
id int,
name varchar(10)
) engine = Memory ;

存储引擎特点

三种存储引擎 InnoDB、MyISAM、Memory的特点

InnoDB

1). 介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

2). 特点

DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能
支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

3). 文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的、sdi-新版的）、数据和索引。

参数：innodb_file_per_table

show variables like 'innodb_file_per_table';

如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。我们直接打开MySQL的数据存放目录：

C:\ProgramData\MySQL\MySQL Server 8.0\Data ，这个目录下有很多文件夹，不同的文件夹代表不同的数据库，我们直接打开xx文件夹。

可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表 1 show variables like 'innodb_file_per_table'; 的表结构。

4). 逻辑存储结构

表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以包含多个Segment段。
段 : 表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
区 : 区是表空间的单元结构，每个区的大小为1M。默认情况下， InnoDB存储引擎页大小为 16K，即一个区中一共有64个连续的页。
页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区
行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

MYISAM

1). 介绍 MyISAM是MySQL早期的默认存储引擎。

2). 特点不支持事务，不支持外键支持表锁，不支持行锁访问速度快

3). 文件 xxx.sdi：存储表结构信息 xxx.MYD: 存储数据 xxx.MYI: 存储索引

Memory

1). 介绍 Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。

2). 特点内存存放 hash索引（默认）

3).文件 xxx.sdi：存储表结构信息

区别及其特点

面试题

InnoDB引擎与MyISAM引擎的区别 ?

①. InnoDB引擎, 支持事务, 而MyISAM不支持。

②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁

③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。

主要是上述三点区别，当然也可以从索引结构、存储限制等方面，更加深入的回答，具体参考如下官方文档：

https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
MyISAM ：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

Mysql执行顺序

一条SQL查询语句是如何执行的

1、首先客户端发送请求到服务端，建立连接。

2、服务端先看下查询缓存是否命中，命中就直接返回，否则继续往下执行。

3、接着来到解析器，进行语法分析，一些系统关键字校验，校验语法是否合规(正确)。

4、然后优化器进行SQL优化，比如怎么选择索引之类，然后生成执行计划。

5、最后执行引擎调用存储引擎API查询数据，返回结果。

详细：

sql语句：select user_id、username from t_user where username = "张三" and sex = 1

1、客户端发起查询请求，与连接器建立连接，连接器确定用户是否有查询权限，没有权限，直接返回错误信息，有执行下一步。

2、查询缓存(MySQL8.0 以前)，以这条SQL语句为key在内存缓冲池中是否有结果，有直接返回结果，无则执行下一步。

3、分析器进行词法分析，提取出操作为select, 表名为 t_user, 查询字段为user_id、username，查询条件为username=“张三” 和 sex=1 ,把提取的Token转换为抽象语法树接下来判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

4、优化器列出可能的执行方案

a. 先查询t_user表中username=“张三”的学生，然后判断是否性别为男。

b. 先找出学生中性别是男的学生，然后再查询username为“张三”的学生。

接下来计算两个查询计划的成本，若username字段为索引，usrname和sex为联合索引、或查询条件可能致使索引失效，查询字段为*，造成成全表扫描，都有可能影响执行方案的选择。

5、执行器进行权限校验，如果没有权限就会返回错误信息，如果有权限就执行器会调用数据库引擎接口，返回引擎的执行结果。

6、执行引擎根据执行计划查询数据，并把结果集返回客户端

DQL语句执行顺序

从哪张表中查，from
筛选出要求的信息 where
再进行分组 group by
分组后再筛选出有符合要求的信息 having
然后根据字段查出来 select
最后排序输出
限制信息

select 
    ...         5
from
    ...         1
where
    ...         2
group by
    ...         3
having
    ...         4
order by
    ...         6
limit
    ...         7

from > where > group by > having select >order by > limit

数据库三大范式

数据库范式是设计数据库时，需要遵循的一些规范。各种范式是条件递增的联系，越高的范式数据库冗余越小。常用的数据库三大范式为：

第一范式（1NF）：每个列都不可以再拆分，强调的是列的原子性。第一范式要求数据库中的表都是二维表。
第二范式（2NF）：在第一范式的基础上，一个表必须有一个主键，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式（3NF）：在第二范式的基础上，非主键列只依赖（直接依赖）于主键，不依赖于其他非主键。

触发器

触发器（trigger）是与表相关的数据库对象，是用户定义在关系表上的一类由事件驱动的特殊的存储过程，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据库的完整性。使用场景

可以通过数据库中的相关表实现级联更改；
实时监控某张表中的某个字段的更改，并需要做出相应的处理。

触发器的作用举例：
1.可在写入数据表前，强制检验或转换数据。
2.触发器发生错误时，异动的结果会被撤销。

索引

索引介绍

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引的优点

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；
可以加快数据的检索速度，这也是创建索引的主要原因；
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统性能。

索引的缺点

时间上 : 创建和维护索引都要耗费时间，这种时间随着数据量的增加而增加，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；
空间上 : 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引为什么可以加快查询速度？

因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据
同时，由于索引底层实现的有序性，使得在进行数据查询时，能够避免在磁盘不同扇区的随机寻址
使用索引后能够通过磁盘预读使得在磁盘上对数据的访问大致呈顺序的寻址。这本质上是依据局部性原理所实现的

特点

总结

索引大大减少了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机I/O变成顺序I/O（局部性原理）

索引语法

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name 
ON table_name (
   index_col_name,... 
) ;

查看索引

SHOW INDEX FROM table_name ;

删除索引

DROP INDEX index_name ON table_name ;

索引的数据结构

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持情况。

注意：我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。

假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下

如果主键是顺序插入的，则会形成一个单向链表，结构如下

所以，如果选择二叉树作为索引结构，会存在以下缺点

顺序插入时，会形成一个链表，查询性能大大降低。
大数据量情况下，层级较深，检索速度慢

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数据，最终形成的数据结构也是一颗平衡的二叉树,结构如下

但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：大数据量情况下，层级较深，检索速度慢。所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，那么什么是 B+Tree呢？在详解B+Tree之前，先来介绍一个B-Tree。

B 树索引

B 树索引，又称平衡树索引，是 MySQL 数据库中使用最频繁的索引类型，MySQL、Oracle 和 SQL Server
数据库默认的都是 B 树索引（实际是用 B+ 树实现的，因为在查看表索引时，MySQL 一律打印 BTREE，所以简称为 B 树索引）。
B 树索引以树结构组织，它有一个或者多个分支结点，分支结点又指向单级的叶结点。其中，分支结点用于遍历树，叶结点则保存真正的值和位置信息。
B+ 树是在 B 树基础上的一种优化，使其更适合实现外存储索引结构。

一棵 m 阶 B-Tree 的特性如下：

每个结点最多 m 个子结点；
除了根结点和叶子结点外，每个结点最少有 m/2（向上取整）个子结点；
所有的叶子结点都位于同一层；
每个结点都包含 k 个元素（关键字），这里 m/2≤k<m，这里 m/2 向下取整；
每个节点中的元素（关键字）从小到大排列；
每个元素子左结点的值，都小于或等于该元素，右结点的值都大于或等于该元素。

数据库以 B-Tree 的数据结构存储数据的图示如下：

B+ Tree 与 B-Tree 的结构很像，但是也有自己的特性

所有的非叶子结点只存储关键字信息；
所有具体数据都存在叶子结点中；
所有的叶子结点中包含了全部元素的信息；
所有叶子节点之间都有一个链指针。

数据库以 B+ Tree 的数据结构存储数据的图示如下：

Hash 索引

哈希索引采用一定的哈希算法（常见哈希算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的 Hash 值，与这条数据的行指针一并存入 Hash 表的对应位置，如果发生 Hash 碰撞（两个不同关键字的 Hash 值相同），则在对应 Hash 键下以链表形式存储。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

检索时不需要类似 B+ 树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，平均检索时间为 O(1)。

位图索引

B 树索引擅长于处理包含许多不同值的列，但是在处理基数较小的列时会变得很难使用。如果用户查询的列的基数非常的小，即只有几个固定值，如性别、婚姻状况、行政区等等，要么不使用索引，查询时一行行扫描所有记录，要么考虑建立位图索引。

位图索引为存储在某列中的每个值生成一个位图。例如针对表中婚姻状况这一列，生成的位图索引大致如下所示

Value 1 2 3 4 5 6 ……
未婚 1 1 0 0 0 0
已婚 0 0 0 1 1 1
离婚 0 0 1 0 0 0
对于婚姻状况这一列，索引包含 3 个位图，即生成有 3 个向量，分别属于每一个取值，每个位图为每一个人（行）都分配了 0/1 值（每一行有且仅有一个 1 ），未婚为 110000……，已婚为 000111……，离婚为001000……。

当进行数据查找时，只要查找相关位图中的所有 1 值即可（可根据查询需求进行与、或运算）。

例如， Oracle 用户可以通过为 create index 语句简单地添加关键词 bitmap 生成位图：

CREATE BITMAP INDEX acc_marital_idx ON account (marital_cd);
除了上述提及的，位图索引适合只有几个固定值的列，还需注意，位图索引适合静态数据，而不适合索引频繁更新的列。

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

B+树的好处

由于 B+ 树的内部结点只存放键，不存放值，因此，一次读取，可以在同一内存页中获取更多的键，有利于更快地缩小查找范围。

B+ 树的叶结点由一条链相连，因此当需要进行一次全数据遍历的时候，B+ 树只需要使用 O(logN) 时间找到最小结点，然后通过链进行 O(N) 的顺序遍历即可；或者，在找大于某个关键字或者小于某个关键字的数据的时候，B+ 树只需要找到该关键字然后沿着链表遍历即可。

还有一个优点，因为数据都在叶子节点上，B+树查找时稳定为O(log n)

Hash 索引和 B+ 树索引的区别

Hash 索引和 B+ 树索引有以下几点显见的区别

Hash 索引进行等值查询更快（一般情况下），但是却无法进行范围查询；
Hash 索引不支持使用索引进行排序；
Hash 索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为 Hash 函数的不可预测；
Hash 索引任何时候都避免不了回表查询数据，而 B+ 树在符合某些条件（聚簇索引，覆盖索引等）的时候可以只通过索引完成查询；
Hash 索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生 Hash 碰撞，此时效率可能极差；而
B+ 树的查询效率比较稳定，对于所有的查询都是从根结点到叶子结点，且树的高度较低。

前缀索引

有时需要索引很长的字符列，它会使索引变大并且变慢，一个策略就是索引开始的几个字符，而不是全部值，即被称为前缀索引，以节约空间并得到好的性能。使用前缀索引的前提是此前缀的标识度高，比如密码就适合建立前缀索引，因为密码几乎各不相同。

前缀索引需要的空间变小，但也会降低选择性。索引选择性（INDEX SELECTIVITY）是不重复的索引值（也叫基数）和表中所有行数（T）的比值，数值范围为 1/T ～1。高选择性的索引有好处，因为在查找匹配的时候可以过滤掉更多的行，唯一索引的选择率为 1，为最佳值。对于前缀索引而言，前缀越长往往会得到好的选择性，但是短的前缀会节约空间，所以实操的难度在于前缀截取长度的抉择，可以通过调试查看不同前缀长度的平均匹配度，来选择截取长度。

最左前缀匹配原则

在 MySQL 建立联合索引（多列索引）时会遵守**最左前缀匹配原则，即最左优先**，在检索数据时从联合索引的最左边开始匹配。 例如有一个 3 列索引（a,b,c），则已经对（a）、（a,b）、（a,b,c）上建立了索引 所以在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边

根据最左前缀匹配原则，MySQL 会一直向右匹配直到遇到 **范围查询（>、<、between、like）就停止匹配**，比如采用查询条件 where a = 1 and b = 2 and c > 3 and d = 4 时，如果建立（a,b,c,d）顺序的索引，d 是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，并且 where 子句中 a、b、d 的顺序可以任意调整

(应该是底层优化器自动调整顺序或者优化)。

如果建立的索引顺序是（a,b），那么根据最左前缀匹配原则，直接采用查询条件 where b = 1 是无法利用到索引的。

添加索引的原则

索引虽好，但也不是无限制使用的，以下为添加索引时需要遵循的几项建议性原则：

在查询中很少使用或者参考的列不要创建索引。由于这些列很少使用到，增加索引反而会降低系统的维护速度和增大空间需求。
只有很少数据值的列也不应该增加索引。由于这些列的取值很少，区分度太低，例如人事表中的性别（男/女），在查询时，需要在表中搜索的数据行的比例很大，反而会增加搜索的时间。增加索引，并不能明显加快检索速度。
定义为 text、image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这时因为，二者是相互矛盾的，当增加索引时，会提高检索性能，但是会降低修改性能。定义有外键的数据列一定要创建索引。

聚簇索引

聚簇索引，又称聚集索引，首先并不是一种索引类型，而是一种数据存储方式。具体的，聚簇索引指将数据存储和索引放到一起，找到索引也就找到了数据。

MySQL 里只有 INNODB 表支持聚簇索引，**INNODB 表数据本身就是聚簇索引**，非叶子节点按照主键顺序存放，叶子节点存放主键以及对应的行记录。所以对 INNODB 表进行全表顺序扫描会非常快。

特点

因为索引和数据存放在一起，所以具有更高的检索效率；
相比于非聚簇索引，**聚簇索引可以减少磁盘的 IO 次数**；
表的物理存储依据聚簇索引的结构，所以一个数据表只能有一个聚簇索引，但可以拥有多个非聚簇索引；
一般而言，会在频繁使用、排序的字段上创建聚簇索引。

非聚簇索引

除了聚簇索引以外的其他索引，均称之为非聚簇索引 。非聚簇索引也是 B 树结构，与聚簇索引的存储结构不同之处在于，非聚簇索引中不存储真正的数据行，只包含一个指向数据行的指针

就简单的 SQL 查询来看，分为 SELECT 和 WHERE 两个部分，索引的创建也是以此为根据的，分为复合索引和覆盖索引

事务管理

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。

如果任意一个操作失败，那么整组操作即为失败，会回到操作前状态或者是上一个节点。

因此，事务是保持逻辑数据一致性和可恢复性的重要利器。而锁是实现事务的关键，可以保证事务的完整性和并发性

事务状态

事务在其整个生命周期中会经历不同的状态，这些状态也称为事务状态。

活跃状态：事务的第一个状态，任何正在执行的事务都处于此状态，所做的更改存储在主内存的缓冲区（buffer pool）中。
部分提交状态：执行上次操作后，事务进入部分提交状态。之所以是部分提交，是因为所做的更改仍然在主内存的缓冲区中。**-----redo log**
失败状态：如果某个检查在活动状态下失败，在活动状态或部分提交状态发生一些错误，并且事务无法进一步执行，则事务进入失败状态。
中止状态：如果任何事务已达到失败状态，则恢复管理器将数据库回滚到开始执行的原始状态.**-----undo log**
提交状态：如果所有操作成功执行，则来自部分提交状态的事务进入提交状态。无法从此状态回滚，它是一个新的一致状态。

四大特性 ACID

事务具有 4 个特性，即

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

这 4 个特性通常简称为 ACID，关系型数据库需要遵循 ACID 规则。

原子性

事务是最小的执行单位，不可分割的（原子的）。**事务的原子性确保动作要么全部执行，要么全部不执行**。
以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新；如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，回到此操作前状态，即事务不能部分提交。

一致性

当事务完成时，数据必须处于一致状态，多个事务对同一个数据读取的结果是相同的。
以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。但是当事务完成以后，账户余额的总额再次恢复到一致状态。

隔离性

并发访问数据库时，一个用户的事务不被其他事务所干扰，各个事务不干涉内部的数据。
修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

持久性

一个事务被提交之后，它**对数据库中数据的改变是持久的**，即使数据库发生故障也不应该对其有任何影响。

如何实现四大特性

事务的 ACID 特性是由关系数据库管理系统来实现的。

DBMS 采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。

DBMS 采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许持有锁的事务能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

三大日志与四大特性的关系

归档日志 bin log
回滚日志 undo log
重做日志 redo log

原子性

undo log日志记录sql操作，当发生回滚时，逆序执行逆操作来完成回滚

持久性

bin log & redo log日志

redo log: 为了避免每次读取数据都进行磁盘IO，MySQL的InnoDB引擎采用缓存buffer方式

redo log分为两个部分：缓冲区的redolog buffer和磁盘上的redologfile

磁盘上的redo log不受宕机影响，**在每次真正执行DML操作之前先更新redo log（称为预写式日志）**，也就是WAL技术

WAL技术（Write-Ahead Logging）：在真正把数据写入到磁盘前，先记录日志

redo log（undo log跟redo log的机制一样）都经历一次写，一次刷

写过程将引擎用户层buffer记录的数据写入核心层buffer
刷过程将核心层buffer数据真正写入磁盘

由此分为三种写刷时机，同样由参数控制：延迟写 / 实时写，实时刷 / 实时写，延迟刷

bin log有类似的刷盘时机机制，同样由参数控制：不强制要求 / 每次提交都刷盘（默认） / 集齐N次提交后刷盘

由于redo log采用循环写的方式记录下最近的操作，但要用到更久以前的操作来完成恢复时，就需要另一种日志对更久之前的操作归档

这个日志就是bin log, 两者要配合使用才能保证当数据库发生宕机重启时，数据不会丢失

MySQL提交时，分为两阶段提交，第一阶段提交时，执行器先写redo log(prepare),再写bin log,第二阶段提交时正式更新redo log(commit),保证两个日志数据的安全与同步

一致性

bin log 也有类似的缓冲区，binlog刷盘时机同样是靠配置参数来控制

可以选择不实时更新，牺牲一定的一致性来换取更好的性能，默认每次提交都刷盘（保证一致性）

隔离性

事务的隔离级别

为了尽可能的避免上述事务之间的相互影响，从而达到事务的四大特性，SQL 标准定义了 4 种不同的事务隔离级别（TRANSACTION ISOLATION LEVEL），即并发事务对同一资源的读取深度层次

读取未提交

最低的隔离级别，一个事务可以读到另一个事务未提交的结果，所有的并发事务问题都会发生。

读取已提交

只有在事务提交后，其更新结果才会被其他事务看见，可以解决脏读问题，但是不可重复读或幻读仍有可能发生。Oracle 默认采用的是该隔离级别。

可重复读

在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交，除非数据是被本身事务自己所修改。可以解决脏读、不可重复读。MySQL 默认采用可重复读隔离级别。

可串行化

事务串行化执行，隔离级别最高，完全服从 ACID，牺牲了系统的并发性，也就是说，所有事务依次逐个执行，所以可以解决并发事务的所有问题。

脏读，不可重复读与幻读

脏读：读未提交
不可重复读：同一事务两次读的结果不同（针对update）
幻读：前后多次读取，数据总量不一致（针对insert、delete）

MVCC

MVCC：多版本并发控制，不同事务的读–写、写–读操作并发执行，从而提升系统性能

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。类似于对某个时间节点创建快照。

版本链、ReadView(读视图,从代码层面其实是一个结构体,主要做事务可见性判断)

版本链:

在InnoDB引擎中，每行记录的后面会保存两个隐藏的列：trx_id、roll_pointer。

trx_id: 用于保存每次对该记录进行修改的事务的id。

roll_pointer: 存储一个指针，指向这条记录上一个版本的地址，获取到该记录上一个版本的数据信息。

ReadView:

读已提交和可重复读的区别就在于它们生成的ReadView的策略不同.

读已提交每次查询时都会生成一个新的ReadView

而可重复读每次查询都复用第一次生成的ReadView

然后分别按照ReadView的访问规则最终实现读已提交和可重复读。

下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。

SELECT : InnoDB会根据以下两个条件检查每行记录：只有符合上述两个条件的记录，才能返回作为查询结果

InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。

INSERT : InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE : InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE : InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作

读未提交：就全读最新的就是读未提交，不加任何判断

读已提交和可重复读的实现

核心处理逻辑就是判断所有版本中哪个版本是当前事务可见的，称为 ReadView

这个过程通过事务IDtrx_id来判断，这个ID严格递增

读已提交：仅判断trx_id，在本次事务之前的都有效修改，可查

可重复读：利用undo log，配合ReadView，当该事务执行相同读操作的时候，依据undo log恢复到开始时的数据，保证永远是第一次查询时的结果

串行化的实现：锁实现串行化，牺牲并发，保证ACID

注意：以上三大日志都仅记录写入性操作，不包括查询操作（因为查询不更改数据库，也就没必要记录了）

bin log与redo log区别

redo log循环写，大小固定，会丢失
bin log追加写，大小通过配置参数决定，追加写到超过文件大小后会将后续日志记录到新的bin log不丢失
redo log 适用于崩溃恢复，只有redo log是crush-safe的；binlog 除了配合崩溃恢复保证持久性之外，还适用于主从复制（也是重点）
bin log所有引擎都支持，server层实现， redo log仅InnoDB支持，引擎层实现,这可能是innodb支持事务的根本原因所在
bin log支持三种记录方式（Statement / Row / Mixed），日志类型为逻辑日志；
redo log日志类型为物理日志（物理日志用于恢复速度会快很多）

事务之间的相互影响

脏读（Dirty Read）
一个事务读取了另一个事务未提交的数据。
不可重复读（Non-repeatable Read）
就是在一个事务范围内，两次相同的查询会返回两个不同的数据，这是因为在此间隔内有其他事务对数据进行了修改。
幻读（Phantom Read）
幻读是指当事务不是独立执行时发生的一种现象，例如有一个事务对表中的数据进行了修改，这种修改涉及到表中的全部数据行，同时，第一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。
丢失更新（Lost Update）
两个事务同时读取同一条记录，事务 A 先修改记录，事务 B 也修改记录（B 是不知道 A 修改过），当 B 提交数据后，其修改结果覆盖了 A 的修改结果，导致事务 A 更新丢失。

举例：

事务A和事务B操作同一个银行账号，初始余额为0。

脏读：事务B存入100元，此时事务A查余额，为100，但事务B发生了错误，回滚了，余额又重新变成了0，出现了不一致的情况，此时便说事务A读到了脏数据。

不可重复读：事务A查余额，为0，事务B存入100元并成功提交，此时事务A再查余额，变成了100，同样的查询得到不同的结果，这就是不可重复读。

幻读：与不可重复读有点像，但幻读强调的是查询结果为多条数据的场景。A查余额明细，有20条，B存入100元，使得余额明细多了一条，然后提交成功，此时A再查余额明细，得到了21条，这就是幻读。

丢失更新：事务A和B同时开始，A存入100元，B存入50元，A先于B提交成功，此时余额为100，但当B提交成功后，余额变成了50，A对余额的更新就不见了，这就是丢失更新。

锁

锁的分类

从数据库系统的角度，锁模式可分为以下6 种类型：

共享锁（S）：又叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁时，所有的事务都不能对该数据进行修改，直到数据读取完成，共享锁释放。
排它锁（X）：又叫独占锁、写锁。对数据资源进行增删改操作时，不允许其它事务操作这块资源，直到排它锁被释放，从而防止同时对同一资源进行多重操作。
更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取再修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁就可以避免死锁的出现。
资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排它锁，否则变为共享锁。
意向锁：表示 SQL Server 需要在层次结构中的某些底层资源上获取共享锁或排它锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它锁。
意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁，而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。
意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
架构锁：在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)，执行表的数据定义语言（DDL）操作（例如添加列或除去表）时使用架构修改锁，当编译查询时，使用架构稳定性锁。
大容量更新锁（BU）：向表中大容量复制数据并指定了 TABLOCK 提示时使用。大容量更新锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。

事务隔离级别与锁的关系

在读取未提交隔离级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；
在读取已提交隔离级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
在可重复读隔离级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；
可串行化是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

如果不同程序并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

乐观锁和悲观锁

DBMS 中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。这对于长事务来讲，可能会严重影响系统的并发处理能力。实现方式：使用数据库中的锁机制。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。实现方式：一般会使用版本号机制或 CAS 算法实现。
乐观锁虽然“乐观”，但也有他的保护措施，判断是否有其他线程对数据进行修改。
两种判断方式：

版本号，数据每次更新，版本号都会更新，记下初始时的版本号，要修改前查看版本号，跟之前的不一样就说明有其他线程修改了该数据，本线程不再更新，更新失败
CAS算法，比较并交换。思路是用三个变量：需要读写的内存位置V、进行比较的预期原值A、拟写入的新值B，如果位置V的值仍为预期的A时说明没有其他线程改动它，可以修改为新值B，否则不做修改

Mysql相关用法与比较

**[SELECT* ] 和[SELECT 全部字段]的2 种写法有何优缺点?**

1. 前者要解析数据字典，后者不需要

2. 结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序

3. 表字段改名，前者不需要修改，后者需要改

4. 后者可以建立索引进行优化，前者无法优化

5. 后者的可读性比前者要高

HAVNG 子句和 WHERE 的异同点?

语法上：where 用表中列名，having 用select 结果别名
影响结果范围：where 从表读出数据的行数，having 返回客户端的行数
索引：where 可以使用索引，having 不能使用索引，只能在临时结果集操作
where 后面不能使用聚集函数，having 是专门使用聚集函数的。

BETWEEN AND 对比 <= and >=

ORDER BY 执行时机

order by 经过过滤后得到结果集，对结果集进行排序。

ORDER BY 多列顺序对结果影响

如果有多列排序，第一个是优先排序，第二个则是在第一个排序的基础上对相同数值部分进行二次排序。而不是在第一个排序完了以后再全部重新排序。

现在都有那些数据库，每种数据库的区别、应用场景、优点是什么

eg: 关系型、文档型、内存型、分布式…

Mysql Oracle 达梦 Hbase…

关系型

Oracle 、MySql 、Microsoft SQL Server 、SQLite 、 PostgreSQL 、 IBM DB2

非关系型

键值数据库：Redis、Memcached、Riak
列族数据库：Bigtable、HBase、Cassandra
文档数据库：MongoDB、CouchDB、MarkLogic
图形数据库：Neo4j、InfoGrid

Mysql 优化与配置

连接-配置优化

第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题?有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个

Mysql: error 1040: Too many connections

可以从两个方面来解决连接数不够的问题:
1、从服务端来说，我们可以增加服务端的可用连接数。
如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以:
(1）修改配置参数增加可用连接数，修改max_connections的大小:

show variables like 'max_connections'; --修改最大连接数，当有多个应用连接的时候

(2）或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。

show global variables like 'wait_timeout"':--及时释放不活动的连接，注意不要释放连接池还在使用的连接

2、从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每一次执行SQL都创建一个新的连接，可以引入连接池，实现连接的重用。

缓存-架构优化

缓存

在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题:一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。

我们可以用第三方的缓存服务来解决这个问题，例如Redis。

集群主从复制

主从复制通过bin log实现

如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。

做了主从复制的方案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做读写分离。

读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。

读写分离

读多写少

业务读多写少，数据库读首先会成为瓶颈
不经常变化的数据，如单据数据，事务数据等

数据查询

报表数据查询
页面数据查询
数据统计分析

数据服务

对外提供查询数据服务
对外提供分发数据服务

◆需要增加额外数据库硬件和维护成本

技术设计需要考虑数据库连接读还是写

主从数据一致性问题，可能存在延迟

需要考虑从库的高可用，实现故障转移

一般弱致性(最终一致性)要求的功能实现读写分离

主数据，基础数据等热数据读，优先考虑缓存技术

大数据量表的读写分离并不是万能的，可考虑分库分表

读写分离技术方案

读写方案

适当放弃-致性，在一些实时性要求不高的场合，适当放弃-致性要求，充分利用读写分离提高系统吞吐量

强一致性:将强一致性要求的功能(如取钱、存钱或读写一起的事务)的读写操作均指向主库
弱一致性:将最终一致性要求的功能(如新增信息、查询信息)的读和写分离

同步方案

主从同步，延迟尽可能降到最低

异步复制主库将变更信息写入日志文件，从库开启线程读取日志文件的变更事件同步到数据库中
半同步复制:事务在主库执行完成后，必须确保至少-台从库上执行完成，才算提交成功;从库响应超时则自动降级为异步复制
并行复制:通过对事务进行分组，如果事务能同时提交功能，则从库可以并行执行，大大降级了延迟
复制软件:采用专业的数据库复制产品进行数据库同步，如GoldenGate

读写分离部署架构方案

读写分离中间层实现方式

读写数据库选型方案

读写分离中间件方案

Sharding_JDBC

Sharding-JDBC优势

举例

read操作路由到从库，write操作路由到主库
部署架构为双主单活，使用两个VIP，一个定位到主库，- 个定位到从库，Sharding-JDBC连接VIP实现双主高可用
业务特殊需求场最需要强制读取主库情况下，可以基于sharding JDBC的hintManager实现自主灵活控制逻辑模块。

Shrading-Proxy

Shrading-Proxy

优势

Sharding-Proxy社区活跃， Apache顶级项目，持续迭代，Issue跟进及时
支持读写分离+分库分表，支持任意语言
Sharding-Proxy兼容所有MySQL/PostgreSQL协议的访问客户端(如MySQL Command Client, MySQL Workbench, Navicat)直接进行操作

读写分离基础要点

主库: 目前仅支持单主库，用于添加。更新以及删除数据操作所使用的數据库
从库:可支持多从库，用于查询数据锁使用的数据库
主从同步:将主库的数据异步的同步到从库的操作。由于主从同步的异步性，从库与主库的数据会短时间内不-致
负载均衢策略: 通过负载均衡策略将查询请求疏导至不同从库

读写分离核心功能

提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用
独立使用读写分离支持SQL透传
同线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据致性
基于Hint的强制主库路由

读写分离不支持

主库和从库的数据同步
主库和从库的数据同步延迟导致的数据不-致
主库双写或多写
跨主库和从库之间的事务的数据不一致。主从模型中，事务中读写均用主库

MyCat

优点

功能比较丰富，对读写分高和分库分表都有支持，使用相对简单
对原有应用系统侵入性较小
支持多种全局序列号方式
可以基于心跳自动故障切换
未来Mycat2.0针对1. 对于性能，配置方式等存在较大更新

缺点

所有SQL都需要通过MyCat路由，在库较多情况下，CPU和内存压力相对增大
社区活跃度相对不高，issue关注度不够

读写分离案例

案例

分库分表技术方案

策略建议

**确定分不分库，分哪些库,什么规则分，分多少片**

分库规则，哪些表放在哪个库中。一般考虑级联关系表存放在同一个数据分片中。例如订单(order) 跟订单明细(order detail) ,明细表会依赖于订单，也就是说会存在表的主从关系，这类似业务的切分可以抽象出合适的切分规则，比如根据用户ID切分其他相关的表都依赖于用户ID,再或者根据订单D切分，总之部分业务总会可以抽象出父子关系的表。这类表适用于ER分片表，子表的记录与所关联的父表记录存放在同一个数据分片上,避免数据Join跨库操作

能不分就不分，1000万以内的表，不建议分片，通过合适的索引,读写分离等方式，可以很好的解决性能问题

分片数量尽量少,分片尽量均匀分布在多个数据库上，因为-一个查询SQL跨分片越多, 则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量

分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访问模式,分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容

尽量不要在一个事务中的SQL跨越多个分片，避免大量分布式事务场景

查询条件尽量优化，尽量避免Select 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引*

水平分库

分库规则:以字段为依据，按照一定策略(hash. range等)进行分片，将一个库中的数据拆分到多个库中

分库结果:

每个库的结构是一一样的
每个库的数据都不一样，没有交集
所有库的并集是全量数据

分库优势:

成倍缓解了数据库I0和CPU的压力

水平分表

分表规则:以字段为依据，按照一定策略(hash, range等) 进行分片，将一个表中的数据拆分到多个表中

分表结果:

每个表的结构是一样的
每个表的数据都不-一样，没有交集
所有表的并集是全量数据

分表优势:

表的数据量少了，单次SQL执行效率高，自然喊轻了CPU的负担

垂直分库

分库规则:以表为依据，按照业务归属不同，将不同的表拆分到不同的库中

分库结果:

每个库的结构都不一-样
每个库的数据都不一样，没有交集
所有库的并集是全量数据

分库优势:

可以按业务归属进行服务化, 即抽象出单独的业务模块，随着业务的发展孵化出了-套业务模式

垂直分表

分表规则: 以字段为依据，按照字段使用频率/活跃性，将表中字段拆到不同的表(主表和扩展表)中

分表结果:

每个表的结构都不一-样
每个表的数据都不一一样，-般来说，每个表的字段至少有一列交集(-般是主键)，用于关联数据
所有表的并集是全量数据

分表优势:

将热点数据(可能会冗余经常-起查询的数据)放在-起作为主表，非热点数据放在一起作为扩展表, 这样更多

的热点数据就能被缓存，进而减少了随机读I0

架构方案

MyCat常见分片规则

分片枚举

通过在配置文件中配置可能的枚举id,自己配置分片,本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的

固定分片hash算法

本条规则类似于十进制的求模运算，区别在于是二进制的操作是取id的二进制低10位,即id二进制1111111111
此算法的优点在于如果按照10进制取模运算，在连续插入1-10时候1-10会被分到1-10个分片,增大了插入的事务控制难度，而此算法根据二进制则可能会分到连续的分片，减少插入事务事务控制难度

范围约定

此分片适用于，提前规划好分片字段某个范围属于哪个分片
start <= range <= end
range start-end data node index
K= 1000,M= 10000
0-10000000=0，1000001 -20000000=1， 20000001-30000000=2

取模

此规则为对分片字段求摸运算

按日期(天)分片

此规则为按天分片

取模范围约束

此种规则是取模运算与范围约束的结合，主要为了后续数据迁移做准备，即可以自主决定取模后数据的节点分布

截取数字做hash求模范围约束

此种规则类似于取模范围约束，此规则支持数据符号字母取模

应用指定

此规则是在运行阶段有应用自主决定路由到那个分片

截取数字hash解析

此规则是截取字符串中的int数值hash分片

-致性hash

-致性hash 预算有效解决了分布式数据的容问题

按单月小时拆分

此规则是单月内按照小时拆分，最小粒度是小时，可以一天最多24个分片,最少1个分片,一个月完后下月从头开始循环;每个月月尾，需要手工清理数据

范围求模分片

先进行范围分片计算出分片组，组内再求模
优点可以避免扩容时的数据江移，又可以- -定程度上避免范围分片的热点问题
综合了范围分片和求模分片的优点，分片组内使用求模可以保证组内数据比较均匀，分片组之间是范围分片可以兼顾范围查询
最好事先规划好分片的数量，数据扩容时按分片组扩容，则原有分片组的数据不需要迁移。由于分片组内数据比较均匀，所以分片组内可以避免热点数据问题

日期范围hash分片

思想与范围求模一致，当由于日期在取模会有数据集中问题，所以改成hash方法
先根据日期分组，再根据时间hash使得短期内数据分布的更均优点可以避免扩容时的数据工移，又可以一定程度上避免范围分片的热点问题.要求日期格式尽量精确些,不然达不到局部均匀的目的

冷热数据分片

根据日期查询日志数据冷热数据分布，最近n个月的到实时交易库查询，超过n个月的按照m天分片

自然月分片

按月份列分区，每个自然月一个分片，格式between 操作解析的范例

有状态分片算法

有状态分片算法与之前的分片算法不同,它是为数据自动迁移而设计的
一个有状态分片算法在使用过程中暂时存在两个操作
-一种是初始化，状态分片算法会根据表的dataNode的数量划分分片范围并生成ruledata下的文件，- 个表对应一个状态对应一一个有状态分片算法实例;
一种是添加操作，即数据打容,添加节点，有状态分片算法根据节点的变化，重新分配范围规则，之后执行数据自动迁移任务

crc32slot分片算法

crc32solt是有状态分片算法的实现之- ,数据自动迁移方案设计

优化器-sql语句分析与优化

我们的服务层每天执行了这么多SQL语句，它怎么知道哪些SQL语句比较慢呢?

对于某些mysql工具例如navicat ，可以直接在某些地方查看

在整个刨析的过程中可以看到整个sql执行的时间分布

慢查询日志slow query log

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

打开慢日志开关

因为开启慢查询日志是有代价的（跟bin log.optimizer-trace一样)，所以它默认是关闭的:

show variables like 'slow_query%';

除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL。

show variables like '%long_query%';

可以直接动态修改参数(重启后失效)。

set a@global.slow_query_log=l;--1开启，О关闭，重启后失效
set a@global.long_query_time=3;--mysql默认的慢查询时间是10秒，另开一个窗口后才会查到最新值
show variables like "%long_query%';
show variables like '%oslow_query%';

或者修改配置文件 my.cnf。
以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。

slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysqI/localhost-slow.log

模拟慢查询:

select sleep(10);

查询user_innodb表的500万数据（没有索引)。

SELECT* FROM 'user_innodb` where phone = '136';

慢日志分析

日志内容

show global status like 'slow_queries'; --查看有多少慢查询
show variables like 1%slow_query%'; --获取慢日志目录

mysqldumpslow
https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html
MySQL提供了mysqldumpslow 的工具，在MySQL的 bin目录下。

mysqldumpslow --help

例如:查询用时最多的10条慢SQL:

mysqldumpslow -s t -t 10 -g 'select'/var/lib/mysqI/localhost-slow.log

show profile

https://dev.mysql.com/doc/refman/5.7/en/show-profile.html
SHOW PROFILE是谷歌高级架构师Jeremy Cole贡献给MySQL社区的，可以查看SQL语句执行的时候使用的资源，比如CPU、IO的消耗情况。
在SQL中输入 help profile可以得到详细的帮助信息。

查看是否开启
select @@profiling;
set @@profiling=l;
查看profile 统计
(命令最后带一个s)
show profiles

查看最后一个SQL的执行详细信息，从中找出耗时较多的环节(没有s)。

show profile

其他系统命令
分析Server层的运行信息，可以用show status。
show status 服务器运行状态
说明:https://dev.mysql.com/doc/refman/5.7/en/show-status.html
详细参数:https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
SHOW STATUS用于查看MySQL服务器运行状态（重启后会清空)。

SHOW GLOBAL STATUS ;

可以用like带通配符过滤，例如查看select语句的执行次数。

SHOW GLOBAL STATUS LIKE 'com_select'; --查看select次数

show processlist运行线程
如果要分析服务层的连接信息，可以用

show processlist

SQL与索引优化

Full Table Scan，如果没有索引或者没有用到索引，type就是ALL。代表全表扫描。

小结:
一般来说，需要保证查询至少达到range级别，最好能达到ref。ALL(全表扫描）和index(查询全部索引)都是需要优化的。

possible_key、key

可能用到的索引和实际用到的索引。如果是NULL就代表没有用到索引。
possible_key可以有一个或者多个，比如查询多个字段上都有索引，或者一个字段同时有单列索引和联合索引。能用到的索引并不是越多越好。可能用到索引不代表一定用到索引。如果通过分析发现没有用到索引，就要检查SQL或者创建索引。

key_len

索引的长度(使用的字节数)。跟索引字段的类型、长度有关。表上有联合索引: KEY comidx_name_phone ( name `, phone')

explain select * from user_innodb where name ='root';

rows
MySQL认为扫描多少行(数据或者索引)才能返回请求的数据，是一个预估值。一般来说行数越少越好。
filtered

这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数
量的比例，它是一个百分比。如果比例很低，说明存储引擎层返回的数据需要经过大量过滤，这个是会消耗性能的，需要关注。

ref

使用哪个列或者常数和索引一起从表中筛选数据，可以参考一下。

Extra

执行计划给出的额外的信息说明。
在查询的时候，需要做去重、排序之类的工作的时候，可能会用到临时表。举几个例子:

distinct非索引列
EXPLAIN select DISTINCT(tid) from teacher t;
group by非索引列
EXPLAIN select tname from teacher group by tname;
使用join 的时候，group任意列
EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid;

Using Temporary 需要优化，例如创建复合索引。
总结一下:
模拟优化器执行SQL查询语句的过程,来知道MySQL是怎么处理一条SQL语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。如果需要具体的cost信息，可以用:

EXPLAIN FORMAT=JSON。

如果觉得EXPLAIN还不够详细，可以用开启optimizer trace。

存储引擎

存储引擎的选择

为不同的业务表选择不同的存储引擎，例如:查询插入操作多的业务表,用MylSAM。临时数据用Memeroy。常规的并发大更新多的表用InnoDB。

字段定义

原则:使用可以正确存储数据的最小数据类型。为每一列选择合适的字段类型

整数类型

INT有8种类型，不同的类型的最大存储范围是不一样的。性别?用TINYINT，因为ENUM也是整数存储。

字符类型

变长情况下，varchar 更节省空间，但是对于varchar字段，需要一个字节来记录长度。固定长度的用char，不要用varchar。

不要用外键、触发器、视图

降低了可读性
影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储;数据的完整性应该在程序中检查。

大文件存储

不要用数据库存储图片（比如base64编码）或者大文件;
把文件放在oss或者相关平台上，数据库只存储url，在应用中配置相关地址或者节点。

表拆分或字段冗余

将不常用的字段拆分出去，避免列数过多和数据量过大。
比如在业务系统中，要记录所有接收和发送的消息，这个消息是XML格式的，用blob或者text存储，用来追踪和判断重复，可以建立一张表专门用来存储报文。

优化体系

除了对于代码、SQL语句、表定义、架构、配置优化之外，业务层面的优化也不能忽视。举两个例子:
1）在某一年的双十一，为什么会做一个充值到余额宝和余额有奖金的活动，例如充300送50?
因为使用余额或者余额宝付款是记录本地或者内部数据库，而使用银行卡付款，需要调用接口，操作内部数据库肯定更快。
2)在去年的双十一，为什么在凌晨禁止查询今天之外的账单?
这是一种降级措施，用来保证当前最核心的业务。
3）最近几年的双十一，为什么提前个把星期就已经有双十一当天的价格了?预售分流。

在应用层面同样有很多其他的方案来优化，达到尽量减轻数据库的压力的目的，比如限流，或者引入MQ削峰，等等等等。
为什么同样用MySQL，有的公司可以抗住百万千万级别的并发，而有的公司几百个并发都扛不住，关键在于怎么用。所以，用数据库慢，不代表数据库本身慢，有的时候还要往上层去优化。
当然，如果关系型数据库解决不了的问题，我们可能需要用到搜索引擎或者大数据的方案了，并不是所有的数据都要放到关系型数据库存储。
这是优化的层次，如果说遇到的一个具体的慢SQL的问题，我们又应该怎么去优化呢?比如有人给你发来一条SQL，你应该怎么分析?

分析查询基本情况

涉及到表结构，字段的索引情况、每张表的数据量、查询的业务含义。
这个非常重要，因为有的时候你会发现SQL根本没必要这么写，或者表设计是有问题的。

找出慢的原因

1、查看执行计划，分析SQL的执行情况，了解表访问顺序、访问类型、索引、扫描行数等信息。
2、如果总体的时间很长，不确定哪一个因素影响最大，通过条件的增减，顺序的调整，找出引起查询慢的主要原因，不断地尝试验证。
找到原因:比如是没有走索引引起的，还是关联查询引起的，还是 order by引起的。找到原因之后:

对症下药

创建索引或者联合索引

经验积累

这里需要平时积累经验，例如:

使用小表驱动大表
用 join 来代替子查询
not exist 转换为 left join is NULL 或者 or改成union
使用 UNION ALL 代替 UNION ，如果结果集允许重复的话
大偏移的 limit ，先过滤再排序。
SQL拆分: 重点是结合平台分页工具包原理，将列表查询进行拆分处理，做好前后效率对比(订单列表/商品评价/预算报表)
SQL最简化:结合业务场景，不同的查询条件下，利用Mybatis条件选择语句将SQL最简化
警惕循环等待:千万不要在代码循环体内产生过多I0等待，如循环单条频繁读写数据库/redis/feign调用
必输查询条件:在业务设计层面合理添加必输查询条件
多线程并发处理:在CPU有能力的情况下，考虑多线程并发处理
单据权限影响/分库分表等

如果SQL本身解决不了了，就要上升到表结构和架构了。

表结构(冗余、拆分、not null等)、架构优化。
业务层的优化，必须条件是否必要。
多线程

优势:提高吞吐，提高效率,提高CPU利用率:线程池大小= CPU的数量目标CPU的使用率(1 +等待时间与计算时间的比)
问题:主要就是多线程共享数据时，如果没有采取并发访问控制措施，就可能会产生数据一致性问题, 读脏数据/丢失更新数据
关注点:线程的原子性和可见性，线程的生命周期
技术把控:并发线程应用越来越多，需注意代码质量把控

JVM:本质就是通过通过调整大小降低GC次数(FGC容易造成系统卡顿，多数java应用不需调优，但需要利用相关知识分析问题
缓存:优先考虑使用缓存优化性能，热点高频数据，分布式缓存、JVM缓存(数据量不大,更新不频繁的数据)
监控工具:容器化环境，分布式架构,线上环境等因素，必须借助相关链路监控，诊断工具等
前端优化: CDN加速，反向代理缓存，减少请求数，启用压缩..

如果没有思路，调优就是抓瞎，肯定没有任何头绪。

课后练习

五十道经典sql练习题及解答

练习初始化脚本.sql

练习题目.txt

练习答案41313-王成宇.sql

最后修改：2022 年 10 月 21 日

如果觉得我的文章对你有用，请随意赞赏

此处评论已关闭

Mysql 全面总结-由浅入深

ChengYu Wang • 2022 年 09 月 22 日

<h1>数据库相关概念</h1>
三个概念：数据库、数据库管理系统、SQL。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2221169287.png" alt="" loading="lazy" style="">
SQL语言是操作关系型数据库的 统一标准 。所以即使我们现在学习的是MySQL， 假如我们以后到了公司，使用的是别的关系型数据库，如：Oracle、DB2、SQLServer，也完全不用 担心，因为操作的方式都是一致的。
下载地址：<a class="no-external-link" href="https://downloads.mysql.com/archives/installer" target="_blank">https://downloads.mysql.com/archives/installe</a>
<h2>数据模型</h2>
<h3>关系型数据库（RDBMS）</h3>
概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。 而所谓二维表，指的是由行和列组成的表，如下图 （就类似于Excel表格数据，有表头、有列、有行， 还可以通过一列关联另外一个表格中的某一列数据） 。我们之前提到的MySQL、Oracle、DB2、 SQLServer这些都是属于关系型数据库，里面都是基于二维表存储数据的。简单说，基于二维表存储 数据的数据库就成为关系型数据库，不是基于二维表存储数据的数据库，就是非关系型数据库。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3323225378.png" alt="" loading="lazy" style="">
特点
A. 使用表存储数据，格式统一，便于维护。
B. 使用SQL语言操作，标准统一，使用方便。
<h3>数据模型</h3>
MySQL是关系型数据库，是基于二维表进行数据存储的，具体的结构图下
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1813933503.png" alt="" loading="lazy" style="">
<ul>
<li>我们可以通过MySQL客户端连接数据库管理系统DBMS，然后通过DBMS操作数据库。</li>
<li>可以使用SQL语句，通过数据库管理系统操作数据库，以及操作数据库中的表结构及数据。</li>
<li>一个数据库服务器中可以创建多个数据库，一个数据库中也可以包含多张表，而一张表中又可以包 含多行记录。</li>
</ul>
<h1>SQL语法</h1>
全称 Structured Query Language，结构化查询语言。
操作关系型数据库的编程语言，定义了 一套操作关系型数据库统一标准 。
<h2>SQL通用语法</h2>
<ul>
<li>SQL语句可以单行或多行书写，以分号结尾。</li>
<li>SQL语句可以使用空格/缩进来增强语句的可读性。</li>
<li>MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。 
注释： 单行注释：-- 注释内容 或 # 注释内容 
多行注释：/ 注释内容 */</li>
</ul>
<h2>Sql分类</h2>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/170722847.png" alt="" loading="lazy" style="">
<h2>DDL</h2>
DDL Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。
<h3>数据库操作</h3>
<h4>查询所有数据库</h4>
<code>show databases ;</code>
<h4>查询当前数据库</h4>
<code>select database() ;</code>
<h4>创建数据库</h4>
<code>create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序 规则 ] ;</code>
<pre><code>以数据库名称为hand举例
create database hand;
create database if not extists hand; #不存在则创建
create database hand default charset utf8mb4; #指定字符集
drop database [ if exists ] hand; #存在则删除
use hand ; #切换到hand库</code></pre>
<h3>表操作</h3>
<h4>查询当前数据库所有表</h4>
<code>show tables;</code>
<h4>查看表结构</h4>
<code>desc 表名;</code>
<h4>查看指定表的建表语句</h4>
<code>show create table 表名 ;</code>
<h4>创建表结构</h4>
<pre><code>CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;</code></pre>
示例
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1254152152.png" alt="" loading="lazy" style="">
<h4>表操作-修改-添加字段</h4>
修改数据类型
<code>ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];</code>
示例
<code>ALTER TABLE hand ADD nickname varchar(20) COMMENT '昵称';</code>
<h4>表操作-修改-数据类型</h4>
<code>ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);</code>
修改字段名和字段类型
<code>ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];</code>
删除字段
<code>ALTER TABLE 表名 DROP 字段名;</code>
修改表名
<code>ALTER TABLE 表名 RENAME TO 新表名;</code>
<pre><code>ALTER TABLE hand CHANGE nickname username varchar(30) COMMENT '昵称';
#修改字段名称和数据类型
ALTER TABLE hand DROP username;
#删除表字段
ALTER TABLE hand RENAME TO employee;
#修改表名</code></pre>
<h4>表操作-删除</h4>
删除表
<code>DROP TABLE [ IF EXISTS ] 表名;</code>
可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不 加该参数项，删除一张不存在的表，执行将会报错)。
示例
<pre><code>DROP TABLE IF EXISTS hand; #存在则删除
TRUNCATE TABLE 表名; #初始化</code></pre>
注意: 在删除表的时候，表中的全部数据也都会被删除。
<h2>DML</h2>
DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进 行增、删、改操作。
<ul>
<li>添加数据（INSERT）</li>
<li>修改数据（UPDATE）</li>
<li>删除数据（DELETE）</li>
</ul>
<h3>添加数据-insert</h3>
<h4>给指定字段添加数据</h4>
<code>INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);</code>
<h4>给全部字段添加数据</h4>
<code>INSERT INTO 表名 VALUES (值1, 值2, ...);</code>
<h4>批量添加数据</h4>
<code>INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值 1, 值2, ...) ;</code>
<code>INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;</code>
<h4>添加示例</h4>
<pre><code>#字段添加
insert into hand(id,workno,name,gender,age,idcard,entrydate)
values(1,'1','hand','男',10,'123456789012345678','2000-01-01');
#批量添加
insert into hand values(3,'3','韦一笑','男',38,'123456789012345670','2005-01-
01'),(4,'4','赵敏','女',18,'123456789012345670','2005-01-01');</code></pre>
注意事项:
• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。
<h3>修改数据-update</h3>
<h4>修改数据</h4>
<code>UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;</code>
<h4>修改示例</h4>
<pre><code>update hand set name = 'hand' where id = 1;
update hand set name = '小昭' , gender = '女' where id = 1;</code></pre>
注意事项:
修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
<h3>删除数据-delete</h3>
<h4>删除数据</h4>
<code>DELETE FROM 表名 [ WHERE 条件 ] ;</code>
<h4>删除示例</h4>
<pre><code>delete from hand where gender = '女';
delete from hand; #全部删除</code></pre>
注意事项:
• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数 据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即 可)。
• 当进行删除全部数据操作时，datagrip会提示我们，询问是否确认删除，我们直接点击 Execute即可。
<h2>DQL</h2>
DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。
查询关键字: SELECT 在一个正常的业务系统中，查询操作的频次是要远高于增删改的，当我们去访问企业官网、电商网站， 在这些网站中我们所看到的数据，实际都是需要从数据库中查询并展示的。而且在查询的过程中，可能 还会涉及到条件、排序、分页等操作。
<h4>基本语法</h4>
<pre><code>SELECT
 字段列表
FROM
 表名列表
WHERE
 条件列表
GROUP BY
 分组字段列表
HAVING
 分组后条件列表
ORDER BY
 排序字段列表
LIMIT
 分页参数</code></pre>
<h4>语法类型</h4>
<ul>
<li>基本查询（不带任何条件）</li>
<li>条件查询（WHERE）</li>
<li>聚合函数（count、max、min、avg、sum）</li>
<li>分组查询（group by）</li>
<li>排序查询（order by）</li>
<li>分页查询（limit）</li>
</ul>
<h4>基础查询</h4>
<pre><code># 查询多个字段
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ; 
SELECT * FROM 表名 ;# 通配符查询

#字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

#去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;</code></pre>
<h4>条件查询</h4>
<pre><code>SELECT 字段列表 FROM 表名 WHERE 条件列表 ;</code></pre>
比较运算符
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1820667650.png" alt="" loading="lazy" style="">
逻辑运算符
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1155897933.png" alt="" loading="lazy" style="">
<pre><code># 查询年龄等于 88 的员工
select * from hand where age = 88;
# 查询年龄小于等于 20 的员工信息
select * from hand where age &lt;= 20;
# 查询没有身份证号的员工信息
select * from hand where idcard is null;
# 查询有身份证号的员工信息
select * from hand where idcard is not null;
# 查询年龄不等于 88 的员工信息
select * from hand where age != 88;
# 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
select * from hand where age &gt;= 15 &amp;&amp; age &lt;= 20;
# 查询性别为 女 且年龄小于 25岁的员工信息
select * from hand where gender = '女' and age &lt; 25;
# 查询年龄等于18 或 20 或 40 的员工信息
select * from hand where age = 18 or age = 20 or age =40;
# 查询姓名为两个字的员工信息 _ %
select * from hand where name like '__';
# 查询身份证号最后一位是X的员工信息
select * from hand where idcard like '%X';
select * from hand where idcard like '_________________X';</code></pre>
<h4>聚合函数</h4>
将一列数据作为一个整体，进行纵向计算 。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/679143718.png" alt="" loading="lazy" style="">
<pre><code>SELECT 聚合函数(字段列表) FROM 表名 ;
# 统计该企业员工数量
select count(*) from hand; -- 统计的是总记录数
select count(idcard) from hand; -- 统计的是idcard字段不为null的记录数
# 统计该企业员工的平均年龄
select avg(age) from hand;
# 统计该企业员工的最大年龄
select max(age) from hand;
# 统计该企业员工的最小年龄
select min(age) from hand;
# 统计西安地区员工的年龄之和
select sum(age) from hand where workaddress = '西安';</code></pre>
<h4>分组查询</h4>
<pre><code>SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
后过滤条件 ];</code></pre>
where 和 having 区别
<ul>
<li>执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组 之后对结果进行过滤。</li>
<li>判断条件不同：where不能对聚合函数进行判断，而having可以。</li>
</ul>
注意事项:
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
• 执行顺序: where &gt; 聚合函数 &gt; having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB
示例
<pre><code># 根据性别分组 , 统计男性员工 和 女性员工的数量
select gender, count(*) from hand group by gender ;
# 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
select gender, avg(age) from hand group by gender ;
# 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from hand where age &lt; 45 group by
workaddress having address_count &gt;= 3;
# 统计各个工作地址上班的男性及女性员工的数量
select workaddress, gender, count(*) '数量' from hand group by gender , workaddress
;</code></pre>
<h4>排序查询</h4>
排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。
<code>SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;</code>
排序方式
<ul>
<li>ASC : 升序(默认值)</li>
<li>DESC: 降序</li>
</ul>
注意事项
• 如果是升序, 可以不指定排序方式ASC ;
• 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;
示例
<pre><code># 根据年龄对公司的员工进行升序排序
select * from hand order by age asc; #默认
select * from hand order by age;
# 根据入职时间, 对员工进行降序排序
select * from hand order by entrydate desc;
# 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
select * from hand order by age asc , entrydate desc;</code></pre>
<h4>分页查询</h4>
分页操作在业务系统开发时，也是非常常见的一个功能，我们在网站中看到的各种各样的分页条，后台 都需要借助于数据库的分页操作。
<code>SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;</code>
注意事项:
• *起始索引从0开始，起始索引 = （查询页码 - 1） 每页显示记录数*
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
示例
<pre><code># 查询第1页员工数据, 每页展示10条记录
select * from hand limit 0,10;
select * from hand limit 10;
# 查询第2页员工数据, 每页展示10条记录 --------&gt; (页码-1)*页展示记录数
select * from hand limit 10,10;</code></pre>
<h2>DCL</h2>
<h4>查询用户</h4>
<code>select * from mysql.hand;</code>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1629285873.png" alt="" loading="lazy" style="">
其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以 远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一 个用户。
<h4>创建用户</h4>
<code>CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';</code>
<h4>修改用户</h4>
<code>ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;</code>
<h4>删除用户</h4>
<code>DROP USER '用户名'@'主机名' ;</code>
注意事项:
• 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
• 主机名可以使用 % 通配
• 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库 管理员）使用。
<h4>代码示例</h4>
<pre><code># 创建用户hand, 只能够在当前主机localhost访问, 密码123456;
create user 'hand'@'localhost' identified by '123456';
# 创建用户hand, 可以在任意主机访问该数据库, 密码123456;
create user 'hand'@'%' identified by '123456';
# 修改用户hand的访问密码为1234;
alter user 'hand'@'%' identified with mysql_native_password by '1234';
# 删除 hand@localhost 用户
drop user 'hand'@'localhost';</code></pre>
<h3>权限控制</h3>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2514486302.png" alt="" loading="lazy" style="">
<h4>查询权限</h4>
<code>SHOW GRANTS FOR '用户名'@'主机名' ;</code>
<h4>授予权限</h4>
<code>GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';</code>
<h4>撤销权限</h4>
<code>REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';</code>
注意事项：
• 多个权限之间，使用逗号分隔
*• 授权时， 数据库名和表名可以使用 进行通配，代表所有。**
示例
<pre><code># 查询 'hand'@'%' 用户的权限
show grants for 'hand'@'%';
# 授予 'hand'@'%' 用户hand数据库所有表的所有操作权限
grant all on hand.* to 'hand'@'%';
# 撤销 'hand'@'%' 用户的hand数据库的所有权限
revoke all on hand.* from 'hand'@'%';</code></pre>
<h2>函数</h2>
 MySQL中的函数主要分为以下四类
<ul>
<li>字符串函数</li>
<li>数值函数</li>
<li>日期函数</li>
<li>流程函数</li>
</ul>
<h4>字符串函数</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2441062496.png" alt="" loading="lazy" style="">
<h4>数值函数</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/488539901.png" alt="" loading="lazy" style="">
<h4>日期函数</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/882863212.png" alt="" loading="lazy" style="">
<h4>流程函数</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/663083129.png" alt="" loading="lazy" style="">
<h3>约束</h3>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/4102320999.png" alt="" loading="lazy" style="">
<h4>删除更新行为</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1724953455.png" alt="" loading="lazy" style="">
<h2>多表查询</h2>
<h3>多表关系</h3>
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结 构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
<ul>
<li>一对多(多对一) 。</li>
<li>多对多</li>
<li>一对一</li>
</ul>
<h4>一对多</h4>
<ul>
<li>案例: 部门 与 员工的关系</li>
<li>关系: 一个部门对应多个员工，一个员工对应一个部门</li>
<li>实现: 在多的一方建立外键，指向一的一方的主键</li>
</ul>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2199067381.png" alt="" loading="lazy" style="">
<h4>多对多</h4>
<ul>
<li>案例: 学生 与 课程的关系</li>
<li>关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择</li>
<li>实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键</li>
</ul>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1032458116.png" alt="" loading="lazy" style="">
<h4>一对一</h4>
<ul>
<li>案例: 用户 与 用户详情的关系</li>
<li>关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另 一张表中，以提升操作效率</li>
<li>实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)</li>
</ul>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3684636756.png" alt="" loading="lazy" style="">
<h3>多表查询描述</h3>
多表查询就是指从多张表中查询数据。
*原来查询单表数据，执行的SQL形式为：select from Score;**
那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，
*如： select from Score, Student;**
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2407593476.png" alt="" loading="lazy" style="">
这种现象称之为笛卡尔积。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2931084365.png" alt="" loading="lazy" style="">
在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可
<code>select * from Student stu , Score sc where stu.s_id = sc.s_id;</code><img src="http://www.cynthia99.link/usr/uploads/2022/09/4257456691.png" alt="" loading="lazy" style="">
<h3>连接分类</h3>
连接查询
<ul>
<li>内连接：相当于查询A、B交集部分数据</li>
</ul>
外连接：
<ul>
<li>左外连接：查询左表所有数据，以及两张表交集部分数据</li>
<li>右外连接：查询右表所有数据，以及两张表交集部分数据</li>
<li>自连接：当前表与自身的连接查询，自连接必须使用表别名</li>
</ul>
子查询
<img src="http://www.cynthia99.link/usr/uploads/2022/09/41829843.png" alt="" loading="lazy" style="">
<h3>内连接</h3>
内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据) 内连接的语法分为两种: 隐式内连接、显式内连接。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/4126892752.png" alt="" loading="lazy" style="">
<h4>隐式内连接</h4>
<code>SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;</code>
<h4>显式内连接</h4>
<code>SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;</code>
表的别名:
①. tablea as 别名1 , tableb as 别名2 ;
②. tablea 别名1 , tableb 别名2 ;
注意事项
一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。
<h3>外连接</h3>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/4148303218.png" alt="" loading="lazy" style="">
外连接分为两种，分别是：左外连接 和 右外连接。
<h4>左外连接</h4>
<code>SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;</code>
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
<h4>右外连接</h4>
<code>SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;</code>
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
注意事项
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺 序就可以了。而我们在日常开发使用时，更偏向于左外连接。
<h3>自连接</h3>
<h4>自连接查询</h4>
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
<code>SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;</code>
而对于自连接查询，可以是内连接查询，也可以是外连接查询。
注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底 是哪一张表的字段。
<h4>联合查询</h4>
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集
<pre><code>SELECT 字段列表 FROM 表A ...
UNION [ ALL ]
SELECT 字段列表 FROM 表B ....;</code></pre>
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。 union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
<h3>子查询</h3>
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
<code>SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );</code>
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
<h4>根据结果分类</h4>
<ul>
<li>标量子查询（子查询结果为单个值）</li>
<li>列子查询(子查询结果为一列)</li>
<li>行子查询(子查询结果为一行)</li>
<li>表子查询(子查询结果为多行多列)</li>
</ul>
<h4>根据子查询位置分类</h4>
<ul>
<li>WHERE之后</li>
<li>FROM之后</li>
<li>SELECT之后</li>
</ul>
<h4>标量子查询</h4>
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= &lt;&gt; &gt; &gt;= &lt; &lt;=
<h4>列子查询</h4>
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
<img src="http://www.cynthia99.link/usr/uploads/2022/09/433184068.png" alt="" loading="lazy" style="">
<h4>行子查询</h4>
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、&lt;&gt; 、IN 、NOT IN
<h4>表子查询</h4>
子查询返回的结果是多行多列，这种子查询称为表子查询。 常用的操作符：IN
<h1>Mysql</h1>
<h2>Mysql体系结构</h2>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2281268514.png" alt="" loading="lazy" style="">
1). 连接层
最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程 池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务 器也会为安全接入的每个客户端验证它所具有的操作权限。
2). 服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部 分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解 析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大， 这样在解决大量读操作的环境中能够很好的提升系统的性能。
3). 引擎层
存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通 信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库 中的索引是在存储引擎层实现的。
4). 存储层
数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询 日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。 和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要 体现在存储引擎上，插件式的存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。 这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
<h2>存储引擎</h2>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1769775526.png" alt="" loading="lazy" style="">
大家可能没有听说过存储引擎，但是一定听过引擎这个词，引擎就是发动机，是一个机器的核心组件。 比如，对于舰载机、直升机、火箭来说，他们都有各自的引擎，是他们最为核心的组件。而我们在选择 引擎的时候，需要在合适的场景，选择合适的存储引擎，就像在直升机上，我们不能选择舰载机的引擎 一样。
而对于存储引擎，也是一样，他是mysql数据库的核心，我们也需要在合适的场景选择合适的存储引 擎。接下来就来介绍一下存储引擎。
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是 基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果 没有指定将自动选择默认的存储引擎。
建表时指定存储引擎
<pre><code>CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;</code></pre>
 查询当前数据库支持的存储引擎 
<code>show engines;</code>
实例演示
<ul>
<li>查询建表语句 --- 默认存储引擎: InnoDB</li>
</ul>
<code>show create table account;</code>
我们可以看到，创建表时，即使我们没有指定存储疫情，数据库也会自动选择默认的存储引擎。
<ul>
<li>查询当前数据库支持的存储引擎</li>
</ul>
<code>show engines ;</code>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2824697709.png" alt="" loading="lazy" style="">
<ul>
<li>创建表 my_myisam , 并指定MyISAM存储引擎</li>
</ul>
<pre><code>create table my_myisam(
id int,
name varchar(10)
) engine = MyISAM ;</code></pre>
<ul>
<li>创建表 my_memory , 指定Memory存储引擎</li>
</ul>
<pre><code>create table my_memory(
id int,
name varchar(10)
) engine = Memory ;</code></pre>
<h3>存储引擎特点</h3>
三种存储引擎 InnoDB、MyISAM、Memory的特点
<h4>InnoDB</h4>
1). 介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。
2). 特点
<ul>
<li>DML操作遵循ACID模型，支持事务；</li>
<li>行级锁，提高并发访问性能</li>
<li>支持外键FOREIGN KEY约束，保证数据的完整性和正确性；</li>
</ul>
3). 文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结 构（frm-早期的 、sdi-新版的）、数据和索引。
参数：innodb_file_per_table
<code>show variables like 'innodb_file_per_table';</code>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1311132494.png" alt="" loading="lazy" style="">
如果该参数开启，代表对于InnoDB引擎的表，每一张表都对应一个ibd文件。 我们直接打开MySQL的 数据存放目录：
C:\ProgramData\MySQL\MySQL Server 8.0\Data ， 这个目录下有很多文件 夹，不同的文件夹代表不同的数据库，我们直接打开xx文件夹。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3983057431.png" alt="" loading="lazy" style="">
可以看到里面有很多的ibd文件，每一个ibd文件就对应一张表，比如：我们有一张表 account，就 有这样的一个account.ibd文件，而在这个ibd文件中不仅存放表结构、数据，还会存放该表对应的 索引信息。 而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一 个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表 1 show variables like 'innodb_file_per_table'; 的表结构。
4). 逻辑存储结构
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2244455785.png" alt="" loading="lazy" style="">
<ul>
<li>表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以 包含多个Segment段。</li>
<li>段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管 理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。</li>
<li>区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为 16K， 即一个区中一共有64个连续的页。</li>
<li>页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默 认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区</li>
<li>行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时 所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。</li>
</ul>
<h4>MYISAM</h4>
1). 介绍 MyISAM是MySQL早期的默认存储引擎。
2). 特点 不支持事务，不支持外键 支持表锁，不支持行锁 访问速度快
3). 文件 xxx.sdi：存储表结构信息 xxx.MYD: 存储数据 xxx.MYI: 存储索引
<img src="http://www.cynthia99.link/usr/uploads/2022/09/475458707.png" alt="" loading="lazy" style="">
<h4>Memory</h4>
1). 介绍 Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为 临时表或缓存使用。
2). 特点 内存存放 hash索引（默认）
3).文件 xxx.sdi：存储表结构信息
<h3>区别及其特点</h3>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2269885949.png" alt="" loading="lazy" style="">
面试题
InnoDB引擎与MyISAM引擎的区别 ? 
①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁
③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。
主要是上述三点区别，当然也可以从索引结构、存储限制等方面，更加深入的回答，具体参 考如下官方文档：
<a class="no-external-link" href="https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html" target="_blank">https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html</a> <a class="no-external-link" href="https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html" target="_blank">https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html</a>
<h3>存储引擎选择</h3>
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据 实际情况选择多种存储引擎进行组合。
<ul>
<li>InnoDB是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要 求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操 作，那么InnoDB存储引擎是比较合适的选择。</li>
<li>MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完 整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。</li>
<li>MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是 对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。</li>
</ul>
<h2>Mysql执行顺序</h2>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/4277716811.png" alt="" loading="lazy" style="">
<h2>一条SQL查询语句是如何执行的</h2>
1、首先客户端发送请求到服务端，建立连接。
2、服务端先看下查询缓存是否命中，命中就直接返回，否则继续往下执行。
3、接着来到解析器，进行语法分析，一些系统关键字校验，校验语法是否合规(正确)。
4、然后优化器进行SQL优化，比如怎么选择索引之类，然后生成执行计划。
5、最后执行引擎调用存储引擎API查询数据，返回结果。
详细：
sql语句：<code>select user_id、username from t_user where username = "张三" and sex = 1</code>
1、客户端发起查询请求，与连接器建立连接，连接器确定用户是否有查询权限，没有权限，直接返回错误信息，有执行下一步。
2、查询缓存(MySQL8.0 以前)，以这条SQL语句为key在内存缓冲池中是否有结果，有直接返回结果，无则执行下一步。
3、分析器进行词法分析，提取出操作为select, 表名为 t_user, 查询字段为user_id、username，查询条件为username=“张三” 和 sex=1 ,把提取的Token转换为抽象语法树接下来判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
4、优化器列出可能的执行方案
a. 先查询t_user表中username=“张三”的学生，然后判断是否性别为男。
b. 先找出学生中性别是男的学生，然后再查询username为“张三”的学生。
接下来计算两个查询计划的成本，若username字段为索引，usrname和sex为联合索引、或查询条件可能致使索引失效，查询字段为*，造成成全表扫描，都有可能影响执行方案的选择。
5、执行器进行权限校验，如果没有权限就会返回错误信息，如果有权限就执行器会调用数据库引擎接口，返回引擎的执行结果。
6、执行引擎根据执行计划查询数据，并把结果集返回客户端
<h2>DQL语句执行顺序</h2>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2117955872.png" alt="" loading="lazy" style="">
<ol>
<li>从哪张表中查，from</li>
<li>筛选出要求的信息 where</li>
<li>再进行分组 group by</li>
<li>分组后再筛选出有符合要求的信息 having</li>
<li>然后根据字段查出来 select</li>
<li>最后排序输出</li>
<li>限制信息</li>
</ol>
<pre><code>select 
 ... 5
from
 ... 1
where
 ... 2
group by
 ... 3
having
 ... 4
order by
 ... 6
limit
 ... 7</code></pre>
from &gt; where &gt; group by &gt; having select &gt;order by &gt; limit
<h1>数据库三大范式</h1>
数据库范式是设计数据库时，需要遵循的一些规范。各种范式是条件递增的联系，越高的范式数据库冗余越小。常用的数据库三大范式为：
<ul>
<li>第一范式（1NF）：每个列都不可以再拆分，强调的是列的原子性。第一范式要求数据库中的表都是二维表。</li>
<li>第二范式（2NF）：在第一范式的基础上，一个表必须有一个主键，非主键列 完全依赖 于主键，而不能是依赖于主键的一部分。</li>
<li>第三范式（3NF）：在第二范式的基础上，非主键列只依赖（直接依赖）于主键，不依赖于其他非主键。</li>
</ul>
<h1>触发器</h1>
触发器（trigger）是与表相关的数据库对象，是用户定义在关系表上的一类由事件驱动的特殊的存储过程，在满足定义条件时触发，并执行触发器中定义的 语句集合。触发器的这种特性可以协助应用在数据库端确保 数据库的完整性。使用场景
可以通过数据库中的相关表实现 级联更改； 
实时监控某张表中的某个字段的更改，并需要做出相应的处理。
触发器的作用举例： 
1.可在写入数据表前，强制检验或转换数据。 
2.触发器发生错误时，异动的结果会被撤销。
<h1>索引</h1>
<h2>索引介绍</h2>
索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足 特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构 上实现高级查找算法，这种数据结构就是索引。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/4244439486.png" alt="" loading="lazy" style="">
索引的优点
<ul>
<li>通过创建 唯一性索引，可以保证数据库表中每一行数据的唯一性；</li>
<li>可以加快数据的 检索速度，这也是创建索引的主要原因；</li>
<li>可以加速表和表之间的连接，特别是在实现 数据的参考完整性 方面特别有意义；</li>
<li>通过使用索引，可以在查询的过程中，使用 优化隐藏器，提高系统性能。</li>
</ul>
索引的缺点
<ul>
<li>时间上 : 创建和维护索引都要耗费时间，这种时间随着数据量的增加而增加，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度；</li>
<li>空间上 : 索引需要占 物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。</li>
</ul>
索引为什么可以加快查询速度？
<ol>
<li>因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据</li>
<li>同时，由于索引底层实现的有序性，使得在进行数据查询时，能够避免在磁盘不同扇区的随机寻址</li>
<li>使用索引后能够通过磁盘预读使得在磁盘上对数据的访问大致呈顺序的寻址。这本质上是依据局部性原理所实现的</li>
</ol>
特点
<img src="http://www.cynthia99.link/usr/uploads/2022/09/4007327282.png" alt="" loading="lazy" style="">
总结
<ul>
<li>索引大大减少了服务器需要扫描的数据量</li>
<li>索引可以帮助服务器避免排序和临时表</li>
<li>索引可以将随机I/O变成顺序I/O（局部性原理）</li>
</ul>
<h2>索引语法</h2>
<h3> 创建索引</h3>
<pre><code>CREATE [ UNIQUE | FULLTEXT ] INDEX index_name 
ON table_name (
 index_col_name,... 
) ;</code></pre>
<h3>查看索引</h3>
<code>SHOW INDEX FROM table_name ;</code>
<h3>删除索引</h3>
<code>DROP INDEX index_name ON table_name ;</code>
<h2>索引的数据结构</h2>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/658410580.png" alt="" loading="lazy" style="">
上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持 情况。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1072383377.png" alt="" loading="lazy" style="">
注意： 我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。
假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3436831199.png" alt="" loading="lazy" style="">
如果主键是顺序插入的，则会形成一个单向链表，结构如下
<img src="http://www.cynthia99.link/usr/uploads/2022/09/4048384152.png" alt="" loading="lazy" style="">
所以，如果选择二叉树作为索引结构，会存在以下缺点
<ul>
<li>顺序插入时，会形成一个链表，查询性能大大降低。</li>
<li>大数据量情况下，层级较深，检索速度慢</li>
</ul>
此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数 据，最终形成的数据结构也是一颗平衡的二叉树,结构如下
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1871270675.png" alt="" loading="lazy" style="">
但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点： 大数据量情况下，层级较深，检索速度慢。 所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，那么什么是 B+Tree呢？在详解B+Tree之前，先来介绍一个B-Tree。
<h3>B 树索引</h3>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2927296107.png" alt="" loading="lazy" style="">
<ul>
<li>B 树索引，又称 平衡树索引，是 MySQL 数据库中使用最频繁的索引类型，MySQL、Oracle 和 SQL Server 
数据库默认的都是 B 树索引（实际是用 B+ 树实现的，因为在查看表索引时，MySQL 一律打印 BTREE，所以简称为 B 树索引）。</li>
<li>B 树索引以 树结构组织，它有一个或者多个分支结点，分支结点又指向单级的叶结点。其中，分支结点用于遍历树，叶结点则保存真正的值和位置信息。</li>
<li>B+ 树是在 B 树基础上的一种优化，使其更适合实现外存储索引结构。</li>
</ul>
一棵 m 阶 B-Tree 的特性如下：
<ol>
<li>每个结点最多 m 个子结点；</li>
<li>除了根结点和叶子结点外，每个结点最少有 m/2（向上取整）个子结点；</li>
<li>所有的叶子结点都位于同一层；</li>
<li>每个结点都包含 k 个元素（关键字），这里 m/2≤k&lt;m，这里 m/2 向下取整；</li>
<li>每个节点中的元素（关键字）从小到大排列；</li>
<li>每个元素子左结点的值，都小于或等于该元素，右结点的值都大于或等于该元素。</li>
</ol>
数据库以 B-Tree 的数据结构存储数据的图示如下： 
<img src="http://www.cynthia99.link/usr/uploads/2022/09/672228281.png" alt="" loading="lazy" style="">
B+ Tree 与 B-Tree 的结构很像，但是也有自己的特性
<ol>
<li>所有的非叶子结点只存储 关键字信息；</li>
<li>所有具体数据都存在叶子结点中；</li>
<li>所有的叶子结点中包含了全部元素的信息；</li>
<li>所有叶子节点之间都有一个链指针。</li>
</ol>
数据库以 B+ Tree 的数据结构存储数据的图示如下： 
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2334917694.png" alt="" loading="lazy" style="">
<h3>Hash 索引</h3>
哈希索引采用一定的 哈希算法（常见哈希算法有 直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的 Hash 值，与这条数据的行指针一并存入 Hash 表的对应位置，如果发生 Hash 碰撞（两个不同关键字的 Hash 值相同），则在对应 Hash 键下以 链表形式 存储。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可 以通过链表来解决。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/700156434.png" alt="" loading="lazy" style="">
检索时不需要类似 B+ 树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快，平均检索时间为 O(1)。
<h3>位图索引</h3>
B 树索引擅长于处理包含许多不同值的列，但是在处理基数较小的列时会变得很难使用。如果用户查询的列的基数非常的小， 即只有几个固定值，如性别、婚姻状况、行政区等等，要么不使用索引，查询时一行行扫描所有记录，要么考虑建立位图索引。
位图索引为存储在某列中的每个值生成一个位图。例如针对表中婚姻状况这一列，生成的位图索引大致如下所示
Value 1 2 3 4 5 6 …… 
未婚 1 1 0 0 0 0 
已婚 0 0 0 1 1 1 
离婚 0 0 1 0 0 0 
对于婚姻状况这一列，索引包含 3 个位图，即生成有 3 个向量，分别属于每一个取值，每个位图为每一个人（行）都分配了 0/1 值（每一行有且仅有一个 1 ），未婚为 110000……，已婚为 000111……，离婚为001000……。
当进行数据查找时，只要查找相关位图中的所有 1 值即可（可根据查询需求进行与、或运算）。
例如， Oracle 用户可以通过为 create index 语句简单地添加关键词 bitmap 生成位图：
CREATE BITMAP INDEX acc_marital_idx ON account (marital_cd); 
除了上述提及的，位图索引适合只有几个固定值的列，还需注意 ，位图索引适合静态数据，而不适合索引频繁更新的列。
<h2>索引分类</h2>
在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1089796017.png" alt="" loading="lazy" style="">
<h2>B+树的好处</h2>
由于 B+ 树的内部结点只存放键，不存放值，因此，一次读取，可以在同一内存页中获取更多的键，有利于更快地缩小查找范围。
B+ 树的叶结点由一条链相连，因此当需要进行一次 全数据遍历 的时候，B+ 树只需要使用 O(logN) 时间找到最小结点，然后通过链进行 O(N) 的顺序遍历即可；或者，在找 大于某个关键字或者小于某个关键字的数据 的时候，B+ 树只需要找到该关键字然后沿着链表遍历即可。
还有一个优点，因为数据都在叶子节点上，B+树查找时稳定为O(log n)
<h2>Hash 索引和 B+ 树索引的区别</h2>
Hash 索引和 B+ 树索引有以下几点显见的区别
<ul>
<li>Hash 索引进行等值查询更快（一般情况下），但是却无法进行范围查询；</li>
<li>Hash 索引不支持使用索引进行排序；</li>
<li>Hash 索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为 Hash 函数的不可预测；</li>
<li>Hash 索引任何时候都避免不了回表查询数据，而 B+ 树在符合某些条件（聚簇索引，覆盖索引等）的时候可以只通过索引完成查询；</li>
<li>Hash 索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，发生 Hash 碰撞，此时效率可能极差；而 
B+ 树的查询效率比较稳定，对于所有的查询都是从根结点到叶子结点，且树的高度较低。</li>
</ul>
<h2>前缀索引</h2>
有时需要索引很长的字符列，它会使索引变大并且变慢，一个策略就是索引开始的几个字符，而不是全部值，即被称为 前缀索引，以节约空间并得到好的性能。使用前缀索引的前提是 此前缀的标识度高，比如密码就适合建立前缀索引，因为密码几乎各不相同。
前缀索引需要的空间变小，但也会降低选择性。索引选择性（INDEX SELECTIVITY）是不重复的索引值（也叫基数）和表中所有行数（T）的比值，数值范围为 1/T ～1。高选择性的索引有好处，因为在查找匹配的时候可以过滤掉更多的行，唯一索引的选择率为 1，为最佳值。对于前缀索引而言，前缀越长往往会得到好的选择性，但是短的前缀会节约空间，所以实操的难度在于前缀截取长度的抉择，可以通过调试查看不同前缀长度的 平均匹配度，来选择截取长度。
<h2>最左前缀匹配原则</h2>
在 MySQL 建立 联合索引（多列索引） 时会遵守**最左前缀匹配原则，即 最左优先**，在检索数据时从联合索引的最左边开始匹配。 例如有一个 3 列索引（a,b,c），则已经对（a）、（a,b）、（a,b,c）上建立了索引 所以在创建 多列索引时，要根据业务需求，where 子句中 使用最频繁 的一列放在最左边
根据最左前缀匹配原则，MySQL 会一直向右匹配直到遇到 **范围查询（&gt;、&lt;、between、like）就停止匹配**，比如采用查询条件 where a = 1 and b = 2 and c &gt; 3 and d = 4 时，如果建立（a,b,c,d）顺序的索引，d 是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，并且 where 子句中 a、b、d 的顺序可以任意调整
(应该是底层优化器自动调整顺序或者优化)。
如果建立的索引顺序是 （a,b） ，那么根据最左前缀匹配原则，直接采用查询条件 where b = 1 是无法利用到索引的。
<h2>添加索引的原则</h2>
索引虽好，但也不是无限制使用的，以下为添加索引时需要遵循的几项建议性原则：
<ul>
<li>在 查询中很少使用 或者参考的列不要创建索引。由于这些列很少使用到，增加索引反而会降低系统的维护速度和增大空间需求。</li>
<li>只有很少数据值的列也不应该增加索引。由于这些列的取值很少，区分度太低，例如人事表中的性别（男/女），在查询时，需要在表中搜索的数据行的比例很大，反而会增加搜索的时间。增加索引，并不能明显加快检索速度。</li>
<li>定义为 text、image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。</li>
<li>当 修改性能远远大于检索性能 时，不应该创建索引。这时因为，二者是相互矛盾的，当增加索引时，会提高检索性能，但是会降低修改性能。 定义有外键 的数据列一定要创建索引。</li>
</ul>
<h2>聚簇索引</h2>
聚簇索引，又称 聚集索引， 首先并不是一种索引类型，而是一种数据存储方式。具体的，聚簇索引指将 数据存储 和 索引 放到一起，找到索引也就找到了数据。
MySQL 里只有 INNODB 表支持聚簇索引，**INNODB 表数据本身就是聚簇索引**，非叶子节点按照主键顺序存放，叶子节点存放主键以及对应的行记录。所以对 INNODB 表进行全表顺序扫描会非常快。
特点
<ul>
<li>因为索引和数据存放在一起，所以具有更高的检索效率；</li>
<li>相比于非聚簇索引，**聚簇索引可以减少磁盘的 IO 次数**；</li>
<li>表的物理存储依据聚簇索引的结构，所以一个数据表只能有一个聚簇索引，但可以拥有多个非聚簇索引；</li>
<li>一般而言，会在频繁使用、排序的字段上创建聚簇索引。</li>
</ul>
<h2><img src="http://www.cynthia99.link/usr/uploads/2022/09/904766179.png" alt="" loading="lazy" style=""></h2>
非聚簇索引
除了聚簇索引以外的其他索引，均称之为非聚簇索引 。非聚簇索引也是 B 树结构，与聚簇索引的存储结构不同之处在于，非聚簇索引中不存储真正的数据行，只包含一个指向数据行的指针
就简单的 SQL 查询来看，分为 SELECT 和 WHERE 两个部分，索引的创建也是以此为根据的，分为 复合索引 和 覆盖索引 
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2043911260.png" alt="" loading="lazy" style="">
<h1>事务管理</h1>
数据库的 事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。
如果任意一个操作失败，那么整组操作即为失败，会回到操作前状态或者是上一个节点。
因此，事务是保持 逻辑数据一致性 和 可恢复性 的重要利器。而锁是实现事务的关键，可以保证事务的完整性和并发性
<h2>事务状态</h2>
事务在其整个生命周期中会经历不同的状态，这些状态也称为 事务状态。
<ol>
<li>活跃状态：事务的第一个状态，任何正在执行的事务都处于此状态，所做的 更改 存储在 主内存的缓冲区（buffer pool） 中。</li>
<li>部分提交状态：执行上次操作后，事务进入部分提交状态。之所以是部分提交，是因为所做的更改仍然在主内存的缓冲区中。**-----redo log**</li>
<li>失败状态：如果某个检查在活动状态下失败，在活动状态或部分提交状态发生一些错误，并且事务无法进一步执行，则事务进入失败状态。</li>
<li>中止状态：如果任何事务已达到失败状态，则恢复管理器将数据库回滚到开始执行的原始状态.**-----undo log**</li>
<li>提交状态：如果所有操作成功执行，则来自 部分提交状态 的事务进入提交状态。无法从此状态回滚，它是一个新的 一致状态。</li>
</ol>
<h2>四大特性 ACID</h2>
事务具有 4 个特性，即
<ul>
<li>原子性（Atomicity）</li>
<li>一致性（Consistency）</li>
<li>隔离性（Isolation）</li>
<li>持久性（Durability）</li>
</ul>
<div class="tip inlineBlock info">

这 4 个特性通常简称为 ACID，关系型数据库 需要遵循 ACID 规则。
</div>
原子性
事务是最小的执行单位，不可分割的（原子的）。**事务的原子性确保动作要么全部执行，要么全部不执行**。 
以 银行转账 事务为例，如果该事务提交了，则这两个账户的数据将会更新；如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会 撤销 对任何账户余额的修改，回到此操作前状态，即事务不能部分提交。
一致性
当事务完成时，数据必须处于一致状态，多个事务对同一个数据读取的结果是相同的。 
以银行转账事务事务为例。在事务开始之前，所有 账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。但是当事务完成以后，账户余额的总额再次恢复到一致状态。
隔离性
并发访问数据库 时，一个用户的事务不被其他事务所干扰，各个事务不干涉内部的数据。 
修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
持久性
一个事务被提交之后，它**对数据库中数据的改变是持久的**，即使数据库发生故障也不应该对其有任何影响。
<h2>如何实现四大特性</h2>
事务的 ACID 特性是由关系数据库管理系统来实现的。
DBMS 采用 日志 来保证事务的 原子性、一致性 和 持久性。日志记录了事务对数据库所做的更新，如果某个事务在执行过程中发生错误，就可以根据日志，撤销事务对数据库已做的更新，使数据库退回到执行事务前的初始状态。
DBMS 采用 锁机制 来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许 持有锁的事务 能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。
<h3>三大日志与四大特性的关系</h3>
<ul>
<li>归档日志 bin log</li>
<li>回滚日志 undo log</li>
<li>重做日志 redo log</li>
</ul>
<h4>原子性 </h4>
undo log日志记录sql操作，当发生回滚时，逆序执行逆操作来完成回滚
<h4>持久性</h4>
bin log &amp; redo log日志
redo log: 为了避免每次读取数据都进行磁盘IO，MySQL的InnoDB引擎采用缓存buffer方式
redo log分为两个部分：缓冲区的redolog buffer和磁盘上的redologfile
磁盘上的redo log不受宕机影响，**在每次真正执行DML操作之前先更新redo log（称为预写式日志）**，也就是WAL技术
WAL技术（Write-Ahead Logging）：在真正把数据写入到磁盘前，先记录日志
redo log（undo log跟redo log的机制一样）都经历一次写，一次刷
<ul>
<li>写过程将引擎用户层buffer记录的数据写入核心层buffer</li>
<li>刷过程将核心层buffer数据真正写入磁盘</li>
</ul>
由此分为三种写刷时机，同样由参数控制：延迟写 / 实时写，实时刷 / 实时写，延迟刷
bin log有类似的刷盘时机机制，同样由参数控制：不强制要求 / 每次提交都刷盘（默认） / 集齐N次提交后刷盘
由于redo log采用循环写的方式记录下最近的操作，但要用到更久以前的操作来完成恢复时，就需要另一种日志对更久之前的操作归档
这个日志就是bin log, 两者要配合使用才能保证当数据库发生宕机重启时，数据不会丢失
MySQL提交时，分为两阶段提交，第一阶段提交时，执行器先写redo log(prepare),再写bin log,第二阶段提交时正式更新redo log(commit),保证两个日志数据的安全与同步
<h4>一致性</h4>
bin log 也有类似的缓冲区，binlog刷盘时机同样是靠配置参数来控制
可以选择不实时更新，牺牲一定的一致性来换取更好的性能，默认每次提交都刷盘（保证一致性）
<h4>隔离性</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3581612527.png" alt="" loading="lazy" style="">
<h3>事务的隔离级别</h3>
为了尽可能的避免上述事务之间的相互影响，从而达到事务的四大特性，SQL 标准定义了 4 种不同的事务隔离级别（TRANSACTION ISOLATION LEVEL），即 并发事务对同一资源的读取深度层次 
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2640723173.png" alt="" loading="lazy" style="">
<ul>
<li>读取未提交</li>
</ul>
最低的隔离级别，一个事务可以读到另一个事务未提交的结果，所有的并发事务问题都会发生。
<ul>
<li>读取已提交</li>
</ul>
只有在事务提交后，其更新结果才会被其他事务看见，可以解决 脏读问题，但是不可重复读或幻读仍有可能发生。Oracle 默认采用的是该隔离级别。
<ul>
<li>可重复读</li>
</ul>
在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交，除非数据是被本身事务自己所修改。可以解决 脏读、不可重复读。MySQL 默认采用可重复读隔离级别。
<ul>
<li>可串行化</li>
</ul>
事务 串行化执行，隔离级别最高，完全服从 ACID，牺牲了系统的并发性，也就是说，所有事务依次逐个执行，所以可以解决并发事务的所有问题。
<h3>脏读，不可重复读与幻读</h3>
<ul>
<li>脏读：读未提交</li>
<li>不可重复读：同一事务两次读的结果不同（针对update）</li>
<li>幻读：前后多次读取，数据总量不一致（针对insert、delete）</li>
</ul>
<h3>MVCC</h3>
MVCC：多版本并发控制，不同事务的读–写、写–读操作并发执行，从而提升系统性能
MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。类似于对某个时间节点创建快照。
版本链、ReadView(读视图,从代码层面其实是一个结构体,主要做事务可见性判断)
版本链:
在InnoDB引擎中，每行记录的后面会保存两个隐藏的列：trx_id、roll_pointer。
trx_id: 用于保存每次对该记录进行修改的事务的id。
roll_pointer: 存储一个指针，指向这条记录上一个版本的地址，获取到该记录上一个版本的数据信息。
ReadView:
读已提交和可重复读的区别就在于它们生成的ReadView的策略不同.
读已提交每次查询时都会生成一个新的ReadView
而可重复读每次查询都复用第一次生成的ReadView
然后分别按照ReadView的访问规则最终实现读已提交和可重复读。
下面看一下在REPEATABLE READ隔离级别下，MVCC具体是如何操作的。
<ul>
<li>SELECT : InnoDB会根据以下两个条件检查每行记录：只有符合上述两个条件的记录，才能返回作为查询结果</li>
</ul>
<ol>
<li>InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。</li>
<li>行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。</li>
</ol>
<ul>
<li>INSERT : InnoDB为新插入的每一行保存当前系统版本号作为行版本号。</li>
<li>DELETE : InnoDB为删除的每一行保存当前系统版本号作为行删除标识。</li>
<li>UPDATE : InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。</li>
<li>保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作</li>
</ul>
读未提交：就全读最新的就是读未提交，不加任何判断
读已提交和可重复读的实现
核心处理逻辑就是判断所有版本中哪个版本是当前事务可见的，称为 ReadView
这个过程通过事务IDtrx_id来判断，这个ID严格递增
读已提交：仅判断trx_id，在本次事务之前的都有效修改，可查
可重复读：利用undo log，配合ReadView，当该事务执行相同读操作的时候，依据undo log恢复到开始时的数据，保证永远是第一次查询时的结果
串行化的实现：锁实现串行化，牺牲并发，保证ACID
注意：以上三大日志都仅记录写入性操作，不包括查询操作（因为查询不更改数据库，也就没必要记录了）
<h3>bin log与redo log区别</h3>
<ul>
<li>redo log循环写，大小固定，会丢失</li>
<li>bin log追加写，大小通过配置参数决定，追加写到超过文件大小后会将后续日志记录到新的bin log不丢失</li>
<li>redo log 适用于崩溃恢复，只有redo log是crush-safe的；binlog 除了配合崩溃恢复保证持久性之外，还适用于主从复制（也是重点）</li>
<li>bin log所有引擎都支持，server层实现， redo log仅InnoDB支持，引擎层实现,这可能是innodb支持事务的根本原因所在</li>
<li>bin log支持三种记录方式（Statement / Row / Mixed），日志类型为逻辑日志；</li>
<li>redo log日志类型为物理日志（物理日志用于恢复速度会快很多）</li>
</ul>
<h2>事务之间的相互影响</h2>
<ul>
<li>脏读（Dirty Read） 
一个事务读取了另一个事务未提交的数据。</li>
<li>不可重复读（Non-repeatable Read） 
就是在一个事务范围内，两次相同的查询会返回两个不同的数据，这是因为在此间隔内有其他事务对数据进行了修改。</li>
<li>幻读（Phantom Read） 
幻读是指当事务 不是独立执行时 发生的一种现象，例如有一个事务对表中的数据进行了修改，这种修改涉及到表中的全部数据行，同时，第一个事务也修改这个表中的数据，这种修改是向表中 插入一行新数据。那么，第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。</li>
<li>丢失更新（Lost Update） 
两个事务同时读取同一条记录，事务 A 先修改记录，事务 B 也修改记录（B 是不知道 A 修改过），当 B 提交数据后， 其修改结果覆盖了 A 的修改结果，导致事务 A 更新丢失。</li>
</ul>
举例：
事务A和事务B操作同一个银行账号，初始余额为0。
脏读：事务B存入100元，此时事务A查余额，为100，但事务B发生了错误，回滚了，余额又重新变成了0，出现了不一致的情况，此时便说事务A读到了脏数据。
不可重复读：事务A查余额，为0，事务B存入100元并成功提交，此时事务A再查余额，变成了100，同样的查询得到不同的结果，这就是不可重复读。
幻读：与不可重复读有点像，但幻读强调的是查询结果为多条数据的场景。A查余额明细，有20条，B存入100元，使得余额明细多了一条，然后提交成功，此时A再查余额明细，得到了21条，这就是幻读。
丢失更新：事务A和B同时开始，A存入100元，B存入50元，A先于B提交成功，此时余额为100，但当B提交成功后，余额变成了50，A对余额的更新就不见了，这就是丢失更新。
<h1>锁</h1>
<h2>锁的分类</h2>
从数据库系统的角度，锁模式可分为以下6 种类型：
<ul>
<li>共享锁（S）：又叫他读锁。可以并发读取数据，但不能修改数据。也就是说当数据资源上存在共享锁时，所有的事务都不能对该数据进行修改，直到数据读取完成，共享锁释放。</li>
<li>排它锁（X）：又叫独占锁、写锁。对数据资源进行增删改操作时，不允许其它事务操作这块资源，直到排它锁被释放，从而防止同时对同一资源进行多重操作。</li>
<li>更新锁（U）：防止出现死锁的锁模式，两个事务对一个数据资源进行先读取再修改的情况下，使用共享锁和排它锁有时会出现死锁现象，而使用更新锁就可以避免死锁的出现。 
资源的更新锁一次只能分配给一个事务，如果需要对资源进行修改，更新锁会变成排它锁，否则变为共享锁。</li>
<li>意向锁：表示 SQL Server 需要在 层次结构中的某些底层资源上 获取共享锁或排它锁。例如，放置在 表级 的 共享意向锁表示事务打算在表中的页或行上放置共享锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它锁。 
意向锁可以提高性能，因为 SQL Server 仅在 表级检查意向锁来确定事务是否可以安全地获取该表上的锁，而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。 
意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。</li>
<li>架构锁：在执行 依赖于表架构的操作 时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)，执行表的数据定义语言（DDL）操作（例如添加列或除去表）时使用架构修改锁，当编译查询时，使用架构稳定性锁。</li>
<li>大容量更新锁（BU）：向表中大容量复制数据并指定了 TABLOCK 提示时使用。大容量更新锁允许进程将数据并发地大容量复制到同一表，同时防止其它不进行大容量复制数据的进程访问该表。</li>
</ul>
<h2>事务隔离级别与锁的关系</h2>
<ol>
<li>在 读取未提交 隔离级别下，读取数据不需要加 共享锁，这样就不会跟被修改的数据上的 排他锁 冲突；</li>
<li>在 读取已提交 隔离级别下，读操作需要加 共享锁，但是在语句执行完以后释放共享锁；</li>
<li>在 可重复读 隔离级别下，读操作需要加 共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；</li>
<li>可串行化 是限制性最强的隔离级别，因为该级别 锁定整个范围的键，并一直持有锁，直到事务完成。</li>
</ol>
<h2>死锁</h2>
死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。
常见的解决死锁的方法
<ol>
<li>如果不同程序并发存取多个表，尽量约定 以相同的顺序访问表，可以大大降低死锁机会；</li>
<li>在同一个事务中，尽可能做到 一次锁定所需要的所有资源，减少死锁产生概率；</li>
<li>对于非常容易产生死锁的业务部分，可以尝试使用 升级锁定颗粒度，通过 表级锁 定来减少死锁产生的概率。</li>
</ol>
<h2>乐观锁和悲观锁</h2>
DBMS 中的 并发控制 的任务是确保在 多个事务同时存取数据库中同一数据 时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。这对于长事务来讲，可能会严重影响系统的并发处理能力。实现方式：使用数据库中的锁机制。
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。乐观锁适用于 读多写少的应用场景，这样可以提高吞吐量。实现方式：一般会使用版本号机制或 CAS 算法实现。 
乐观锁虽然“乐观”，但也有他的保护措施，判断是否有其他线程对数据进行修改。 
两种判断方式：
<ol>
<li>版本号，数据每次更新，版本号都会更新，记下初始时的版本号，要修改前查看版本号，跟之前的不一样就说明有其他线程修改了该数据，本线程不再更新，更新失败</li>
<li>CAS算法，比较并交换。思路是用三个变量：需要读写的内存位置V、进行比较的预期原值A、拟写入的新值B，如果位置V的值仍为预期的A时说明没有其他线程改动它，可以修改为新值B，否则不做修改</li>
</ol>
<h1>Mysql相关用法与比较</h1>
<h2>*[SELECT ] 和[SELECT 全部字段]的2 种写法有何优缺点?**</h2>
1. 前者要解析数据字典，后者不需要
2. 结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序
3. 表字段改名，前者不需要修改，后者需要改
4. 后者可以建立索引进行优化，前者无法优化
5. 后者的可读性比前者要高
<h2>HAVNG 子句 和 WHERE 的异同点? </h2>
<ol>
<li>语法上：where 用表中列名，having 用select 结果别名</li>
<li>影响结果范围：where 从表读出数据的行数，having 返回客户端的行数</li>
<li>索引：where 可以使用索引，having 不能使用索引，只能在临时结果集操作</li>
<li>where 后面不能使用聚集函数，having 是专门使用聚集函数的。</li>
</ol>
<h2>BETWEEN AND 对比 &lt;= and &gt;=</h2>
<div class="tip inlineBlock share">

between and 等价&lt;=和&gt;=，属于闭环区间,也包括边界的比较值,即要注意的是between and是有等于的。
between and 等价&lt;=和&gt;=在查询效率上等同,没有区别; 但是&lt; &gt;等符号，可以根据具体需求来具体定义，若想都包含就再加一个等于号=，若想只包含一头，那么在想包含的那头加上一个等于号=，若都不想包含则不用加等于号。

</div>
<h2>ORDER BY 执行时机</h2>
order by 经过过滤后得到结果集，对结果集进行排序。
<h2>ORDER BY 多列顺序对结果影响</h2>
如果有多列排序，第一个是优先排序，第二个则是在第一个排序的基础上对相同数值部分进行二次排序。而不是在第一个排序完了以后再全部重新排序。
<h2>现在都有那些数据库，每种数据库的区别、应用场景、优点是什么</h2>
eg: 关系型、文档型、内存型、分布式…
Mysql Oracle 达梦 Hbase…
关系型
<ul>
<li>Oracle 、MySql 、Microsoft SQL Server 、SQLite 、 PostgreSQL 、 IBM DB2</li>
</ul>
非关系型
<ul>
<li>键值数据库：Redis、Memcached、Riak</li>
<li>列族数据库：Bigtable、HBase、Cassandra</li>
<li>文档数据库：MongoDB、CouchDB、MarkLogic</li>
<li>图形数据库：Neo4j、InfoGrid</li>
</ul>
<h1>Mysql 优化与配置</h1>
<h2>连接-配置优化</h2>
第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题?有可能是服务端连接数不够导致应用程序获取不到连接。比如报了一个
<pre><code>Mysql: error 1040: Too many connections</code></pre>
可以从两个方面来解决连接数不够的问题: 
1、从服务端来说，我们可以增加服务端的可用连接数。 
如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以: 
(1）修改配置参数增加可用连接数，修改max_connections的大小:
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3153800787.png" alt="" loading="lazy" style="">
<pre><code>show variables like 'max_connections'; --修改最大连接数，当有多个应用连接的时候</code></pre>
(2）或者，或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。
<pre><code>show global variables like 'wait_timeout"':--及时释放不活动的连接，注意不要释放连接池还在使用的连接</code></pre>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1186033304.png" alt="" loading="lazy" style="">
2、从客户端来说，可以减少从服务端获取的连接数，如果我们想要不是每一次执行SQL都创建一个新的连接，可以引入连接池，实现连接的重用。
<h2>缓存-架构优化</h2>
<h3>缓存</h3>
在应用系统的并发数非常大的情况下，如果没有缓存，会造成两个问题:一方面是会给数据库带来很大的压力。另一方面，从应用的层面来说，操作数据的速度也会受到影响。
<pre><code>我们可以用第三方的缓存服务来解决这个问题，例如Redis。</code></pre>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1672536280.png" alt="" loading="lazy" style="">
<h3>集群 主从复制</h3>
主从复制通过bin log实现
如果单台数据库服务满足不了访问需求，那我们可以做数据库的集群方案。 
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3080238081.png" alt="" loading="lazy" style=""> 
做了主从复制的方案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做读写分离。 
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2397912451.png" alt="" loading="lazy" style=""> 
读写分离可以一定程度低减轻数据库服务器的访问压力，但是需要特别注意主从数据一致性的问题。
<h3>读写分离</h3>
<h4>读多写少</h4>
<ul>
<li>业务读多写少，数据库读首先会成为瓶颈</li>
<li>不经常变化的数据，如单据数据，事务数据等</li>
</ul>
<h4>数据查询</h4>
<ul>
<li>报表数据查询</li>
<li>页面数据查询</li>
<li>数据统计分析</li>
</ul>
<h4>数据服务</h4>
<ul>
<li>对外提供查询数据服务</li>
<li>对外提供分发数据服务</li>
</ul>
◆需要增加额外数据库硬件和维护成本
技术设计需要考虑数据库连接读还是写
主从数据一致性问题，可能存在延迟
需要考虑从库的高可用，实现故障转移
一 般弱致性(最终一致性)要求的功能实现读写分离
主数据， 基础数据等热数据读，优先考虑缓存技术
大数据量表的读写分离并不是万能的，可考虑分库分表
<h4>读写分离技术方案</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1594392156.png" alt="" loading="lazy" style="">
<ol>
<li>读写方案</li>
</ol>
适当放弃-致性，在一些实时性要求不高的场合，适当放弃-致性要求，充分利用读写分离提高系统吞吐量
<ul>
<li>强一致性:将强一致性要求的功能(如取钱、存钱或读写一起的事务)的读写操作均指向主库</li>
<li>弱一致性:将最终一致性要求的功能(如新增信息、查询信息)的读和写分离</li>
</ul>
<ol start="2">
<li>同步方案</li>
</ol>
主从同步，延迟尽可能降到最低
<ul>
<li>异步复制主库将变更信息写入日志文件，从库开启线程读取日志文件的变更事件同步到数据库中</li>
<li>半同步复制:事务在主库执行完成后，必须确保至少-台从库上执行完成，才算提交成功;从库响应超时则自动降级为异步复制</li>
<li>并行复制:通过对事务进行分组，如果事务能同时提交功能，则从库可以并行执行，大大降级了延迟</li>
<li>复制软件:采用专业的数据库复制产品进行数据库同步，如GoldenGate</li>
</ul>
<h3>读写分离部署架构方案</h3>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2612595370.png" alt="" loading="lazy" style="">
读写分离中间层实现方式
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1425845754.png" alt="" loading="lazy" style="">
读写数据库选型方案
<img src="http://www.cynthia99.link/usr/uploads/2022/09/861480058.png" alt="" loading="lazy" style="">
读写分离中间件方案
<img src="http://www.cynthia99.link/usr/uploads/2022/09/144266808.png" alt="" loading="lazy" style="">
<h4>Sharding_JDBC</h4>
Sharding-JDBC优势
<img src="http://www.cynthia99.link/usr/uploads/2022/09/958904542.png" alt="" loading="lazy" style="">
举例
<ul>
<li>read操作路由到从库，write操作路由到主库</li>
<li>部署架构为双主单活，使用两个VIP，一个定位到主库，- 个定位到从库，Sharding-JDBC连接VIP实现双主高可用</li>
<li>业务特殊需求场最需要强制读取主库情况下，可以基于sharding JDBC的hintManager实现自主灵活控制逻辑模块。</li>
</ul>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1690123990.png" alt="" loading="lazy" style="">
<h4>Shrading-Proxy</h4>
Shrading-Proxy
优势
<ul>
<li>Sharding-Proxy社区活跃， Apache顶级项目，持续迭代，Issue跟进及时</li>
<li>支持读写分离+分库分表，支持任意语言</li>
<li>Sharding-Proxy兼容 所有MySQL/PostgreSQL协议的访问客户端(如MySQL Command Client, MySQL Workbench, Navicat)直接进行操作</li>
</ul>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1612284920.png" alt="" loading="lazy" style="">
读写分离基础要点
<ul>
<li>主库: 目前仅支持单主库，用于添加。更新以及删除数据操作所使用的數据库</li>
<li>从库:可支持多从库，用于查询数据锁使用的数据库</li>
<li>主从同步:将主库的数据异步的同步到从库的操作。由于主从同步的异步性，从库与主库的数据会短时间内不-致</li>
<li>负载均衢策略: 通过负载均衡策略将查询请求疏导至不同从库</li>
</ul>
读写分离核心功能
<ul>
<li>提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用</li>
<li>独立使用读写分离支持SQL透传</li>
<li>同线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据致性</li>
<li>基于Hint的强制主库路由</li>
</ul>
读写分离不支持
<ul>
<li>主库和从库的数据同步</li>
<li>主库和从库的数据同步延迟导致的数据不-致</li>
<li>主库双写或多写</li>
<li>跨主库和从库之间的事务的数据不一致。主从模型中，事务中读写均用主库</li>
</ul>
<h4>MyCat</h4>
优点
<ul>
<li>功能比较丰富，对读写分高和分库分表都有支持，使用相对简单</li>
<li>对原有应用系统侵入性较小</li>
<li>支持 多种全局序列号方式</li>
<li>可以基于心跳自动故障切换</li>
<li>未来Mycat2.0针对1. 对于性能，配置方式等存在较大更新</li>
</ul>
缺点
<ul>
<li>所有SQL都需要通过MyCat路由，在库较多情况下，CPU和内存压力相对增大</li>
<li>社区活跃度相对不高，issue关注度不够</li>
</ul>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3055758818.png" alt="" loading="lazy" style="">
<h4>读写分离案例</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3343322570.png" alt="" loading="lazy" style="">
案例
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3357815884.png" alt="" loading="lazy" style="">
<h3>分库分表技术方案</h3>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1394630790.png" alt="" loading="lazy" style="">
策略建议
<ul>
<li> **确定分不分库，分哪些库,什么规则分，分多少片**</li>
</ul>
<hr />
<ul>
<li>分库规则，哪些表放在哪个库中。一般考虑级联关系表存放在同一个数据分片中。例如订单(order) 跟订单明细(order detail) ,明细表会依赖于订单，也就是说会存在表的主从关系，这类似业务的切分可以抽象出合适的切分规则，比如根据用户ID切分其他相关的表都依赖于用户ID,再或者根据订单D切分，总之部分业务总会可以抽象出父子关系的表。这类表适用于ER分片表，子表的记录与所关联的父表记录存放在同一个数据分片 上,避免数据Join跨库操作</li>
</ul>
<hr />
<ul>
<li>能不分就不分，1000万以内的表，不建议分片，通过合适的索引,读写分离等方式，可以很好的解决性能问题</li>
</ul>
<hr />
<ul>
<li>分片数量尽量少,分片尽量均匀分布在多个数据库上，因为-一个查询SQL跨分片越多, 则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量</li>
</ul>
<hr />
<ul>
<li>分片规则需要慎重选择，分片规则的选择，需要考虑数据的增长模式，数据的访问模式,分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性Hash分片，这几种分片都有利于扩容</li>
</ul>
<hr />
<ul>
<li>尽量不要在一个事务中的SQL跨越多个分片， 避免大量分布式事务场景</li>
</ul>
<hr />
<ul>
<li>查询条件尽量优化，尽量避免Select 的方式，大量数据结果集下，会消耗大量带宽和CPU资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引*</li>
</ul>
<h4> 水平分库</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1520140214.png" alt="" loading="lazy" style="">
分库规则:以字段为依据，按照一定策略(hash. range等)进行分片，将一个库中的数据拆分到多个库中
分库结果:
<ul>
<li>每个库的结构是一一样的</li>
<li>每个库的数据都不一样，没有交集</li>
<li>所有库的并集是全量数据</li>
</ul>
分库优势:
<ul>
<li>成倍缓解了 数据库I0和CPU的压力</li>
</ul>
<h4>水平分表</h4>
 <img src="http://www.cynthia99.link/usr/uploads/2022/09/24026817.png" alt="" loading="lazy" style="">
分表规则:以字段为依据，按照一定策略(hash, range等) 进行分片，将一个表中的数据拆分到多个表中
分表结果:
<ul>
<li>每个表的结构是一 样的</li>
<li>每个表的数据都不-一样，没有交集</li>
<li>所有表的并集是全量数据</li>
</ul>
分表优势:
<ul>
<li>表的数据量少了， 单次SQL执行效率高，自然喊轻了CPU的负担</li>
</ul>
<h4>垂直分库</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3457703374.png" alt="" loading="lazy" style="">
分库规则:以表为依据，按照业务归属不同，将不同的表拆分到不同的库中
分库结果:
<ul>
<li>每个库的结构都不一-样</li>
<li>每个库的数据都不一样，没有交集</li>
<li>所有库的并集是全量数据</li>
</ul>
分库优势:
<ul>
<li>可以按业务归属进行服务化, 即抽象出单独的业务模块，随着业务的发展孵化出了-套业务模式</li>
</ul>
<h4>垂直分表</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/4211452380.png" alt="" loading="lazy" style="">
分表规则: 以字段为依据，按照字段使用频率/活跃性，将表中字段拆到不同的表(主表和扩展表)中
分表结果:
<ul>
<li>每个表的结构都不一-样</li>
<li>每个表的数据都不一一样，-般来说，每个表的字段至少有一列交集(-般是主键)，用于关联数据</li>
<li>所有表的并集是全量数据</li>
</ul>
分表优势:
<ul>
<li>将热点数据(可能会冗余经常-起查询的数据)放在-起作为主表，非热点数据放在一起作为扩展表, 这样更多</li>
</ul>
的热点数据就能被缓存，进而减少了随机读I0
<h4>架构方案</h4>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2663150999.png" alt="" loading="lazy" style="">
<h4>MyCat常见分片规则</h4>
分片枚举
<ul>
<li>通过在配置文件中配置可能的枚举id,自己配置分片,本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的</li>
</ul>
固定分片hash算法
<ul>
<li>本条规则类似于十进制的求模运算，区别在于是二进制的操作是取id的二进制低10位,即id二进制1111111111</li>
<li>此算法的优点在于如果按照10进制取模运算，在连续插入1-10时候1-10会被分到1-10个分片,增大了插入的事务控制难度，而此算法根据二进制则可能会分到连续的分片，减少插入事务事务控制难度</li>
</ul>
范围约定
<ul>
<li>此分片适用于，提前规划好分片字段某个范围属于哪个分片</li>
<li>start &lt;= range &lt;= end</li>
<li>range start-end data node index</li>
<li>K= 1000,M= 10000</li>
<li>0-10000000=0，1000001 -20000000=1， 20000001-30000000=2</li>
</ul>
取模
<ul>
<li>此规则为对分片字段求摸运算</li>
</ul>
按日期(天)分片
<ul>
<li>此规则为按天分片</li>
</ul>
取模范围约束
<ul>
<li>此种规则是取模运算与范围约束的结合，主要为了后续数据迁移做准备，即可以自主决定取模后数据的节点分布</li>
</ul>
截取数字做hash求模范围约束
<ul>
<li>此种规则类似于取模范围约束，此规则支持数据符号字母取模</li>
</ul>
应用指定
<ul>
<li>此规则是在运行阶段有应用自主决定路由到那个分片</li>
</ul>
截取数字hash解析
<ul>
<li>此规则是截取字符串中的int数值hash分片</li>
</ul>
-致性hash
<ul>
<li>-致性hash 预算有效解决了分布式数据的容问题</li>
</ul>
按单月小时拆分
<ul>
<li>此规则是单月内按照小时拆分，最小粒度是小时，可以一天最多24个分片,最少1个分片,一个月完后下月从头开始循环;每个月月尾，需要手工清理数据</li>
</ul>
范围求模分片
<ul>
<li>先进行范围分片计算出分片组，组内再求模</li>
<li>优点可以避免扩容时的数据江移，又可以- -定程度上避免范围分片的热点问题</li>
<li>综合了范围分片和求模分片的优点，分片组内使用求模可以保证组内数据比较均匀，分片组之间是范围分片可以兼顾范围查询</li>
<li>最好事先规划好分片的数量，数据扩容时按分片组扩容，则原有分片组的数据不需要迁移。由于分片组内数据比较均匀，所以分片组内可以避免热点数据问题</li>
</ul>
日期范围hash分片
<ul>
<li>思想与范围求模一致，当由于日期在取模会有数据集中问题，所以改成hash方法</li>
<li>先根据日期分组，再根据时间hash使得短期内数据分布的更均优点可以避免扩容时的数据工移，又可以一定程度上避免范围分片的热点问题.要求日期格式尽量精确些,不然达不到局部均匀的目的</li>
</ul>
冷热数据分片
<ul>
<li>根据日期查询日志数据冷热数据分布，最近n个月的到实时交易库查询，超过n个月的按照m天分片</li>
</ul>
自然月分片
<ul>
<li>按月份列分区，每个自然月一个分片，格式between 操作解析的范例</li>
</ul>
有状态分片算法
<ul>
<li>有状态分片算法与之前的分片算法不同,它是为数据自动迁移而设计的</li>
<li>一个有状态分片算法在使用过程中暂时存在两个操作</li>
<li>-一种是初始化，状态分片算法会根据表的dataNode的数量划分分片范围并生成ruledata下的文件，- 个表对应一个状态对应一一个有状态分片算法实例;</li>
<li>一种是添加操作，即数据打容,添加节点，有状态分片算法根据节点的变化，重新分配范围规则，之后执行数据自动迁移任务</li>
</ul>
crc32slot分片算法
<ul>
<li>crc32solt是有状态分片算法的实现之- ,数据自动迁移方案设计</li>
</ul>
<h2>优化器-sql语句分析与优化</h2>
我们的服务层每天执行了这么多SQL语句，它怎么知道哪些SQL语句比较慢呢?
对于某些mysql工具例如navicat ，可以直接在某些地方查看
<img src="http://www.cynthia99.link/usr/uploads/2022/09/800230895.png" alt="" loading="lazy" style="">
在整个刨析的过程中可以看到整个sql执行的时间分布
<h3>慢查询日志slow query log</h3>
<a class="no-external-link" href="https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html" target="_blank">https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html</a>
<ol>
<li>打开慢日志开关</li>
</ol>
因为开启慢查询日志是有代价的（跟bin log.optimizer-trace一样)，所以它默认是关闭的:
<pre><code>show variables like 'slow_query%';</code></pre>
除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL。
<pre><code>show variables like '%long_query%';</code></pre>
可以直接动态修改参数(重启后失效)。
<pre><code>set a@global.slow_query_log=l;--1开启，О关闭，重启后失效
set a@global.long_query_time=3;--mysql默认的慢查询时间是10秒，另开一个窗口后才会查到最新值
show variables like "%long_query%';
show variables like '%oslow_query%';</code></pre>
或者修改配置文件 my.cnf。 
以下配置定义了慢查询日志的开关、慢查询的时间、日志文件的存放路径。
<pre><code>slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysqI/localhost-slow.log</code></pre>
模拟慢查询:
<pre><code>select sleep(10);</code></pre>
查询user_innodb表的500万数据（没有索引)。
<pre><code>SELECT* FROM 'user_innodb` where phone = '136';</code></pre>
<ol start="2">
<li>慢日志分析</li>
</ol>
日志内容
<pre><code>show global status like 'slow_queries'; --查看有多少慢查询
show variables like 1%slow_query%'; --获取慢日志目录</code></pre>
mysqldumpslow 
<a class="no-external-link" href="https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html" target="_blank">https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html</a> 
MySQL提供了mysqldumpslow 的工具，在MySQL的 bin目录下。
<pre><code>mysqldumpslow --help</code></pre>
例如:查询用时最多的10条慢SQL:
<pre><code>mysqldumpslow -s t -t 10 -g 'select'/var/lib/mysqI/localhost-slow.log</code></pre>
show profile
<a class="no-external-link" href="https://dev.mysql.com/doc/refman/5.7/en/show-profile.html" target="_blank">https://dev.mysql.com/doc/refman/5.7/en/show-profile.html</a> 
SHOW PROFILE是谷歌高级架构师Jeremy Cole贡献给MySQL社区的，可以查看SQL语句执行的时候使用的资源，比如CPU、IO的消耗情况。 
在SQL中输入 help profile可以得到详细的帮助信息。
<ol>
<li>查看是否开启 
select @@profiling; 
set @@profiling=l;</li>
<li>查看profile 统计 
(命令最后带一个s) 
show profiles</li>
</ol>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/207028722.png" alt="" loading="lazy" style=""> 
查看最后一个SQL的执行详细信息，从中找出耗时较多的环节(没有s)。
<pre><code>show profile</code></pre>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2450258223.png" alt="" loading="lazy" style="">
其他系统命令 
分析Server层的运行信息，可以用show status。 
show status 服务器运行状态 
说明:<a class="no-external-link" href="https://dev.mysql.com/doc/refman/5.7/en/show-status.html" target="_blank">https://dev.mysql.com/doc/refman/5.7/en/show-status.html</a> 
详细参数:<a class="no-external-link" href="https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html" target="_blank">https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html</a> 
SHOW STATUS用于查看MySQL服务器运行状态（重启后会清空)。
<pre><code>SHOW GLOBAL STATUS ;</code></pre>
可以用like带通配符过滤，例如查看select语句的执行次数。
<pre><code>SHOW GLOBAL STATUS LIKE 'com_select'; --查看select次数</code></pre>
show processlist运行线程 
如果要分析服务层的连接信息，可以用
<pre><code>show processlist</code></pre>
<h2>SQL与索引优化</h2>
Full Table Scan，如果没有索引或者没有用到索引，type就是ALL。代表全表扫描。
小结: 
一般来说，需要保证查询至少达到range级别，最好能达到ref。ALL(全表扫描）和index(查询全部索引)都是需要优化的。
<ul>
<li>possible_key、key</li>
</ul>
可能用到的索引和实际用到的索引。如果是NULL就代表没有用到索引。 
possible_key可以有一个或者多个，比如查询多个字段上都有索引，或者一个字段同时有单列索引和联合索引。能用到的索引并不是越多越好。可能用到索引不代表一定用到索引。如果通过分析发现没有用到索引，就要检查SQL或者创建索引。
<ul>
<li>key_len</li>
</ul>
索引的长度(使用的字节数)。跟索引字段的类型、长度有关。表上有联合索引: KEY <code>&lt;span class="ne-text"&gt;comidx_name_phone&lt;/span&gt;</code> ( name `, phone')
<pre><code>explain select * from user_innodb where name ='root';</code></pre>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/1241698243.png" alt="" loading="lazy" style="">
<ul>
<li>rows 
MySQL认为扫描多少行(数据或者索引)才能返回请求的数据，是一个预估值。一般来说行数越少越好。</li>
<li>filtered</li>
</ul>
这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数 
量的比例，它是一个百分比。如果比例很低，说明存储引擎层返回的数据需要经过大量过滤，这个是会消耗性能的，需要关注。
<ul>
<li>ref</li>
</ul>
使用哪个列或者常数和索引一起从表中筛选数据，可以参考一下。
<ul>
<li>Extra</li>
</ul>
执行计划给出的额外的信息说明。 
在查询的时候，需要做去重、排序之类的工作的时候，可能会用到临时表。举几个例子:
<ol>
<li>distinct非索引列 
EXPLAIN select DISTINCT(tid) from teacher t; </li>
<li>group by非索引列 
EXPLAIN select tname from teacher group by tname; </li>
<li>使用join 的时候，group任意列 
EXPLAIN select t.tid from teacher t join course c on t.tid = c.tid group by t.tid; </li>
</ol>
Using Temporary 需要优化，例如创建复合索引。 
总结一下: 
模拟优化器执行SQL查询语句的过程,来知道MySQL是怎么处理一条SQL语句的。通过这种方式我们可以分析语句或者表的性能瓶颈。如果需要具体的cost信息，可以用:
<pre><code>EXPLAIN FORMAT=JSON。</code></pre>
如果觉得EXPLAIN还不够详细，可以用开启optimizer trace。
<h2>存储引擎</h2>
存储引擎的选择
为不同的业务表选择不同的存储引擎，例如:查询插入操作多的业务表,用MylSAM。临时数据用Memeroy。常规的并发大更新多的表用InnoDB。
字段定义
原则:使用可以正确存储数据的最小数据类型。为每一列选择合适的字段类型
整数类型
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3993026527.png" alt="" loading="lazy" style=""> 
INT有8种类型，不同的类型的最大存储范围是不一样的。性别?用TINYINT，因为ENUM也是整数存储。
字符类型
变长情况下，varchar 更节省空间，但是对于varchar字段，需要一个字节来记录长 度。固定长度的用char，不要用varchar。
不要用外键、触发器、视图
降低了可读性 
影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储;数据的完整性应该在程序中检查。
大文件存储
不要用数据库存储图片（比如base64编码）或者大文件; 
把文件放在oss或者相关平台上，数据库只存储url，在应用中配置相关地址或者节点。
表拆分或字段冗余
将不常用的字段拆分出去，避免列数过多和数据量过大。 
比如在业务系统中，要记录所有接收和发送的消息，这个消息是XML格式的，用blob或者text存储，用来追踪和判断重复，可以建立一张表专门用来存储报文。
<h1>优化体系</h1>
<img src="http://www.cynthia99.link/usr/uploads/2022/09/3285772323.png" alt="" loading="lazy" style=""> 
除了对于代码、SQL语句、表定义、架构、配置优化之外，业务层面的优化也不能忽视。举两个例子: 
1）在某一年的双十一，为什么会做一个充值到余额宝和余额有奖金的活动，例如充300送50? 
因为使用余额或者余额宝付款是记录本地或者内部数据库，而使用银行卡付款，需要调用接口，操作内部数据库肯定更快。 
2)在去年的双十一，为什么在凌晨禁止查询今天之外的账单? 
这是一种降级措施，用来保证当前最核心的业务。 
3）最近几年的双十一，为什么提前个把星期就已经有双十一当天的价格了?预售分流。
在应用层面同样有很多其他的方案来优化，达到尽量减轻数据库的压力的目的，比如限流，或者引入MQ削峰，等等等等。 
为什么同样用MySQL，有的公司可以抗住百万千万级别的并发，而有的公司几百个并发都扛不住，关键在于怎么用。所以，用数据库慢，不代表数据库本身慢，有的时候还要往上层去优化。 
当然，如果关系型数据库解决不了的问题，我们可能需要用到搜索引擎或者大数据的方案了，并不是所有的数据都要放到关系型数据库存储。 
这是优化的层次，如果说遇到的一个具体的慢SQL的问题，我们又应该怎么去优化呢?比如有人给你发来一条SQL，你应该怎么分析?
<h2>分析查询基本情况</h2>
涉及到表结构，字段的索引情况、每张表的数据量、查询的业务含义。 
这个非常重要，因为有的时候你会发现SQL根本没必要这么写，或者表设计是有问题的。
<h2>找出慢的原因</h2>
1、查看执行计划，分析SQL的执行情况，了解表访问顺序、访问类型、索引、扫描行数等信息。 
2、如果总体的时间很长，不确定哪一个因素影响最大，通过条件的增减，顺序的调整，找出引起查询慢的主要原因，不断地尝试验证。 
找到原因:比如是没有走索引引起的，还是关联查询引起的，还是 order by引起的。找到原因之后:
<h2>对症下药</h2>
<ul>
<li>创建索引或者联合索引</li>
</ul>
<h2>经验积累</h2>
<img src="http://www.cynthia99.link/usr/uploads/2022/10/2352989815.png" alt="image.png" loading="lazy" style="">
这里需要平时积累经验，例如:
<ol>
<li>使用小表驱动大表</li>
<li>用 join 来代替子查询</li>
<li>not exist 转换为 left join is NULL 或者 or改成union</li>
<li>使用 UNION ALL 代替 UNION ，如果结果集允许重复的话</li>
<li>大偏移的 limit ，先过滤再排序。</li>
<li>SQL拆分: 重点是结合平台分页工具包原理，将列表查询进行拆分处理，做好前后效率对比(订单列表/商品评价/预算报表)</li>
<li>SQL最简化:结合业务场景，不同的查询条件下，利用Mybatis条件选择语句将SQL最简化</li>
<li>警惕循环等待:千万不要在代码循环体内产生过多I0等待，如循环单条频繁读写数据库/redis/feign调用</li>
<li>必输查询条件:在业务设计层面合理添加必输查询条件</li>
<li>多线程并发处理:在CPU有能力的情况下，考虑多线程并发处理</li>
<li>单据权限影响/分库分表等</li>
</ol>
如果SQL本身解决不了了，就要上升到表结构和架构了。
<ul>
<li>表结构(冗余、拆分、not null等)、架构优化。</li>
<li>业务层的优化，必须条件是否必要。</li>
<li>多线程</li>
</ul>
<ol>
<li>优势:提高吞吐，提高效率,提高CPU利用率:线程池大小= CPU的数量目标CPU的使用率(1 +等待时间与计算时间的比)</li>
<li>问题:主要就是多线程共享数据时，如果没有采取并发访问控制措施，就可能会产生数据一致性问题, 读脏数据/丢失更新数据</li>
<li>关注点:线程的原子性和可见性，线程的生命周期</li>
<li>技术把控:并发线程应用越来越多，需注意代码质量把控 
<img src="http://www.cynthia99.link/usr/uploads/2022/09/2298031060.png" alt="" loading="lazy" style=""></li>
</ol>
<ul>
<li>JVM:本质就是通过通过调整大小降低GC次数(FGC容易造成系统卡顿，多数java应用不需调优，但需要利用相关知识分析问题</li>
<li>缓存:优先考虑使用缓存优化性能，热点高频数据，分布式缓存、JVM缓存(数据量不大,更新不频繁的数据)</li>
<li>监控工具:容器化环境，分布式架构,线上环境等因素，必须借助相关链路监控，诊断工具等</li>
<li>前端优化: CDN加速，反向代理缓存，减少请求数，启用压缩..</li>
</ul>
<img src="http://www.cynthia99.link/usr/uploads/2022/10/1889436225.png" alt="" loading="lazy" style="">
<hr />
如果没有思路，调优就是抓瞎，肯定没有任何头绪。
<h1>课后练习</h1>
五十道经典sql练习题及解答
<a class="no-external-link" href="https://www.yuque.com/attachments/yuque/0/2022/sql/32814702/1663910608515-28f7e487-3a27-4248-a4e4-7cafa5f7f065.sql" target="_blank">练习初始化脚本.sql</a>
<a class="no-external-link" href="https://www.yuque.com/attachments/yuque/0/2022/txt/32814702/1663910608431-34998b45-84b3-4714-b660-de04e0240e67.txt" target="_blank">练习题目.txt</a>
<a class="no-external-link" href="https://www.yuque.com/attachments/yuque/0/2022/sql/32814702/1663910608440-fd16507a-d291-4665-ac10-0dd6333b673f.sql" target="_blank">练习答案41313-王成宇.sql</a>