MySQL

摘要：是一种关系型数据库，建立在关系模型的基础上，主要用于持久化存储系统中的数据。

MySQL

关键字用大写，表名、字段名用小写。

是一种关系型数据库，建立在关系模型的基础上，主要用于持久化存储系统中的数据。

MySQL 的默认端口号是 3306。版本号 5.7.21。

主要分为三大类：423

SQL 使用相关：数据类型、SQL 语句、索引视图触发器、数据库性能优化；4
原理：MySQL 基础架构、存储引擎；2
事务、锁机制、MVCC。3

简介

优势

开源。
用 C 和 C++ 编写，并用了多种编译器进行测试，保证源代码的可移植性。
跨平台：支持 Windows、Linux、Mac OS 等操作系统。SQL Server 只支持 Windows。
为多种编程语言提供了API，包括 C、C++、C#、Java、PHP、Python、Ruby 等。
支持多语言编码，如中文的 GB2312、BIG5、日文的 Shift JIS 等都可用作数据表名和列名。
提供 TCP/IP、ODBC 和 JDBC 接口等多种数据库连接途径，用于解耦。
支持多线程，充分利用 CPU资源，支持多用户。
基于 C/S 客户端-服务端模式的 DBMS。既能作为单独的应用程序在 C/S 网络环境中运行，也能作为程序库嵌入其他软件。
- ~~另一类是基于共享文件系统的 DBMS，如 Microsoft Access 和 FileMaker，主要用于桌面用途，不适合高端或更关键的应用。~~
提供用于管理、检查、优化数据库操作的管理工具。如默认 MyCli，Navicat、MySQL Workbench 图形客户端；phpMyAdmin。
优化的SQL查询算法，有效地提高查询速度。可处理拥有上千万条记录的大型数据库。

安装

下载MySQL

到 MySQL官网下载对应操作系统32/64位的.zip压缩包到\Java\MyMSQL

.msi格式的安装包直接点击安装即可，不用再配置配置文件

版本号

MySQL 的命名机制由 3 个数字和 1 个后缀组成，如 mysql-5.7.21：

源自Github 上面的语义化版本标准

第 1 个数字 5 是主版本号：用于描述文件的格式，所有版本 5 的发行版都有相同的文件夹格式。表示做了不兼容的 API 修改。
第 2 个数字 7 是发行级别：表示做了向下兼容的功能性新增；+ 主版本号组合 = 发行序列号，描述了稳定的特征集。
第 3 个数字 20 是在此发行系列的版本号：随每次新发行的版本递增。表示做了向下兼容的问题修正。通常选择已发行的最新版本。

解压

解压到目标安装目录（比如统一安装到同一文件夹下`\Java`）

设置环境变量

1	在->系统变量->`PATH` *追加*

;安装目录\Java\mysql-5.7.21-winx64\bin;

比如：
;D:\Develop\Java\mysql-5.7.21-winx64\bin;

配置文件

在安装目录\Java\mysql-5.7.21-winx64新建my.ini配置文件，另存为为 ANSI 编码。如下：

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8

[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录，比如：
basedir=D:\Develop\Java\mysql-5.7.21-winx64
# 设置mysql数据库的数据的存放目录 \data，比如：
datadir=D:\Develop\Java\mysql-5.7.21-winx64\data

# 允许最大连接数
max_connections=120
# 默认值是28800秒，也就是当连接空闲时长超过8小时就会自动断开
wait_timeout = 600

# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

skip-grant-tables #无密码登录mysql，在最后一行添加

MySQL ERROR:Access denied for user 'root'@'localhost' (using password:YES)
- 解决：skip-grant-tables #无密码登录mysql，在最后一行添加
mysql报错mysqld: [ERROR] Found option without preceding group in config file mysqld: MySQL服务无法启动
- 解决：my.ini文件编码是utf8,只要把他编码变成ANS编码即可。

以管理员身份运行cmd

- 进入`C:\Windows\System32`
- 右键单击cmd.exe
- 选择“以管理员身份运行”

如果不用管理员身份运行，将会因为权限不够而出现错误： Install/Remove of the Service Denied!

`cd`到安装目录`\bin`下]

d:
cd 安装目录\Java\mysql-5.7.21-winx64\bin

比如：
d:
cd D:\Develop\Java\mysql-5.7.21-winx64\bin

安装

1	`mysqld -install`

显示Service successfully installed.即为安装成功

启动MySQL

1	`net start mysql`

登陆

mysql -u root -p
Enter password:

注意密码为空（直接回车）

新建测试数据库

mysql>CREATE DATABASE CampusDate;   //创建一个数据库
mysql>use CampusDate;  //指定test为当前要操作的数据库
mysql>CREATE TABLE user (UseID VARCHAR(20),PassWord VARCHAR(20));   //创建一个表user，设置两个字段。
mysql>INSERT INTO user VALUES('daixiaoke','shishazi'); //插入一条数据到表中

用 Navicat 导出导入 MySQL

使用

MySQL Client 的可执行程序是 mysql，在其中输入的 SQL 语句通过TCP 可靠的连接发送到 MySQL Server；
MySQL Server 的可执行程序是 mysqld，本机默认IP地址为 127.0.0.1:3306，可用远程IP。

# 登录
mysql -h 127.0.0.1:3306 -u root -p
# 服务器
mysqld start/stop/restatr/status

问题

Too many connections

添加数据源报错 Data source rejected establishment of connection, message from server: "Too many connections"

修改 MySQL 配置文件（Windows下为 my.ini， Linux下为 my.cnf ）中的参数：

max_connections= 1000 
wait_timeout = 5

如果没有可以自行添加，修改后重启 MySQL ，如果经常性的报此错误，请您按照服务器环境搭建教程检查下你的服务器。

windows下重启mysql的方法

cd D:\Develop\Java\mysql-5.7.21-winx64\bin
net stop mysql
net start mysql

mysql 服务正在启动或停止中

管理员身份打开命令行窗口，注意是管理员身份，不然无权限访问。

tasklist| findstr "mysql" 		#用于查找mysql的残留进程
taskkill/f /t /im mysqld.exe 	#将mysql残留进程全部杀死

netstat -aon|findstr "3306"  	#看端口是否被占用
taskkill -pid 9396 -f   // -t(结束该进程) -f(强制结束该进程以及所有子进程) 

解决：删除data文件夹（或改my.ini中的路径），然后执行mysqld - initialize、mysqld --initialize-insecure （不设置root密码，建议使用），再启动mysql。再登录

mysql -u root -p
net start mysql

数据类型

MySQL 数据类型、及对应的 Java 包装类、JDBC 类型

注意：类型应为大写，暂用小写便于记忆；大于显示宽度也可插入。

	MySQL数据类型	JDBC类型	Java包装类	占用空间	范围
	bit		Boolean		默认值 b’0’
整型	`tinyint`	TINYINT	`Byte`	1 Byte	0 ~ 255
	`smallint`	SMALLINT	`Short`	2 Byte	0 ~ 65535
	`mediumint`	INTEGER	`Integer`	3 Byte	0 ~ 1677w
	`int/integer`	INTEGER	`Integer`	4 Byte	0 ~ 42亿
	`bigint`	BIGINT	`Long`	8 Byte
浮点型	`float(M, D)`	REAL	`Float`	4 Byte
	`double(M, D)`	DOUBLE	`Double`	8 Byte
定点数	`decimal(M, D)`	DECIMAL	`BigDecimal`	由定义的宽度决定	每 4 字节可存储 9 位数字，且小数点占用1 Byte
	`numberic`	DECIMAL	`BigDecimal`	由定义的宽度决定
字符串	`char(M)`	CHAR	`String`	固定长度，1 <= M <= 255
	`varchar(N)`	VARCHAR	`String`	可变长度
	`tinytext`	VARCHAR	`String`	可变长度
	`text`
	`blob`
	`binary`	BINARY
日期时间	`date`	DATE	`Date`	3 Byte	YYYY-MM-DD
	`year`/`LocalDate`	DATE	`Date`		YYYY
	`time`/`LocalTime`	TIME	`Date`		HH:MM:SS
	`datetime` /`LocalDateTime`	datetime /TIMESTAMP	`Date`	8 Byte	YYYY-MM-DD HH:MM:SS，DEFAULT CURRENT_TIMESTAMP
	`timestamp`	TIMESTAMP	`Date`	4 Byte	YYYY-MM-DD HH:MM:SS？

三大类数据类型

字段类型选择原则

数值类型：可用 UNSIGNED 修饰；
1. 整型：
  1. tinyint unsigned（1 Byte）：0 ~ 255；对应 Java 中的 Byte 类型；可表示 bool；
  2. smallint（2 Byte）：0 ~ 65535；对应 Java 中的 Short 类型；
  3. mediumint（3 Byte）：0 ~ 1677w；对应 Java 中的 Integer 类型；
  4. int/integer（4 Byte）/int(M位数)：0 ~ 42亿；对应 Java 中的 Integer 类型；
  5. bigint（8 Byte）：对应 Java 中的 Long 类型，否则可能会 Integer 溢出。
    - 如 id unsigned bigint not null auto_increment, primary key (id)。
2. 浮点型：可用(精度M, 标度D)，分别表示（总位数/显示宽度，小数位数）。注意浮点数相减和比较运算时易出误差问题。
  1. float(M, D小数位数)：比较粗略但开销相对较少，常用浮点型；对应 JDBC 中的real（4 Byte）；
  2. double(M, D)（8 Byte）
3. 定点数
  - decimal(M, D小数位数)：优点是不存在舍入误差，值和计算都是精确的。
    - 不同于浮点数的四舍五入，实际上是以字符串的形式存放；用于对精度要求较高时，如金额、货币、科学数据；
    - 占用空间由定义的宽度决定，每 4 个字节可存储 9 位数字，且小数点占用1 Byte。
  - numeric
字符串类型：电话号码、信用卡号含非数字字符（空格和短划线），及避免丢失开头的 0，不能存为数值类型。对应 Java 中的 String 类，length() 返回的是 char（占 2Byte ）的数量。
1. CHAR(M)：固定长度，存入的数据均占 M 字节，1 <= M <= 255；会自动删除插入数据尾部的空格；处理速度快，但浪费空间。
2. ~~VARCHAR(M)~~：可变长度，按照实际的长度存储；MySql 5.0 之前的版本，指最多存储 M 个字节，1 <= M <= 255；
3. VARCHAR(N)：可变长度，按照实际的长度存储；MySql 5.0 之后的版本，指最多存储 N 个字符（数字、字母或汉字）的数据；
  1. MySQL 规定：一行数据的最大长度是 65535 字节，是所有列共享（相加）的，所以 VARCHAR 的最大值受此限制；UTF-8 编码中，一个英文字符占 1 Byte，中文占 3 Byte，故 1 <= 中文字符数 N <= 65535 / 3；
  2. 另外需 1-2 个额外字节记录/存储长度 N 的值：N <= 255 时需 1 Byte（2^8）；N >255 时则需 2 Byte；varchar(255) 比 varchar(256)少占用 1 个（记录 N 的额外）字节；
4. 其它文本字符串类型：text、blob、binary等
  1. tinytext(255), text(65535，64k), mediumtext, longtext(4GB)
  2. tinyblob, blob, mediumblob, longblob：避免使用
5. binary, varbinary
日期时间类型：统一对应 Java 中的 Date 类；
1. date：YYYY-MM-DD
  - year：YYYY：对应 JDBC 中的 DATE；
2. time：HH:MM:SS
3. datetime（8Byte）：YYYY-MM-DD HH:MM:SS，create_time；对应 JDBC 中的 TIMESTAMP；
4. timestamp（4Byte）：YYYY-MM-DD HH:MM:SS；用于需记录时区的 create_time；
  - 时间？数据： int(timestamp) > datetime > char > varchar。
  - 用 int 存储时间取值范围为 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07，超出范围的用 DATETIME，还需转换为时间，若访存频繁则用 datetime。
  1
  2
  # 用于需记录时区的 create_time INSERT INTO time_zone_test(date_time, time_stamp) VALUES(NOW(), NOW());
其它类型
1. enum(‘v1’, ...)：最多 65535 个元素；从多个值中取一个时用 ENUM；避免使用；
2. set(‘v1’, ...)：最多 64 个成员；取多个值时用SET；
  1
  2
  CREATE TABLE tab ( gender SET('man', 'woman', 'no') ); INSERT INTO tab VALUES ('man', 'man');

`char VS varchar`

字符字段类型的选择原则：

一般 `<` 50个**字节**时用 `char` （除个别很少用到的字段，也可用 `varchar` 来节省空间）；
对于 `MyISAM `存储引擎，最好用**固定长度**的数据列（CHAR ）。可使整个表**静态化**，从而使数据**检索更快**，用空间换时间；
对于 **`InnoDB` 存储引擎**，最好用**可变长度**的数据列（VARCHAR）。`InnoDB `数据表的存储格式不分固定长度和可变长度，因此用 CHAR 不一定比用 VARCHAR 更好，但由于 VARCHAR 是按照**实际的长度**存储，更**节省空间**，对磁盘 I/O 和数据存储总量更好。

字符集和字符编码

数据库和表的字符集统一用 UTF8，兼容性更好。

1	`CHARACTER SET UTF8`

乱码的本质：编码和解码时用了不同或不兼容的字符集。

字节：1 个字节（Byte）等于 8 个 bit 位，可表示 256 个状态，是计算机里最基本的存储单位；
字符：指计算机中使用的英文字母、数字、符号、中文等；

二者表示的含义不同，在不同编码中，字符和字节的对应关系不同。

ASCII 字符集和编码：用 7 bits 位表示一个字符，共 128 个字符，包括控制字符、英文大小写字符、阿拉伯数字、英文标点符号等；
1. ASCII 扩展字符集用 8 bits 位表示一个字符，共 256 个字符；一个英文字符占 1Byte；
2. 缺点：只支持英文和部分西欧语言，不支持其他语言。
GB2312 字符集和编码：一个英文字符占 1Byte，和 ASCII 码相同；非英字符（如中文）占 2Byte。涵盖了绝大部分常用汉字，不支持部分生僻字和繁体字。
1. GBK：兼容并扩展 GB2312 字符集；
2. 缺点：各地区的本地编码方案（如 GB2312）互相不兼容，容易出现乱码。
BIG5 字符集&编码：支持港澳台繁体中文。
Unicode 字符集和 UTF 编码：包含了世界上几乎所有已知字符。
1. UTF-8 字符编码方案：是一种针对 Unicode 的可变长度字符编码（定长码），也是一种前缀码，可用来表示 Unicode 标准中的任何字符，且其编码中的第一个字节仍与 ASCII 兼容；用 1-4 个字节为每个字符编码；目前使用最广，所有互联网协议都支持；
2. UTF-16 字符编码方案：一个英文字母字符或一个汉字字符存储都需 2 个字节（Unicode 扩展区的一些汉字存储需 4 个字节）；
3. UTF-32 字符编码方案：任何字符的存储都需 4 个字节。

MySQL 字符集中有两套 UTF-8 编码的实现：

~~utf8~~ ：只支持1-3个字节；一个英文字符占 1Byte，和 ASCII 码相同；一个中文字符占 3Byte；
utf8mb4 ： UTF-8 的完整实现，可存储 emoji 符号、较复杂的文字、繁体字，占 4 个字节。

SELECT LENGTH("轻松工作");            -- 返回为 12 位
SELECT CHARACTER_LENGTH("轻松工作");  -- 返回为 4 字节

SQL 语句

SQL（Structured Query Language)结构化查询语言：标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。

方言：各数据库支持的各自扩展功能。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。

SQL 语法注意事项

SQL 语句不区分大小写，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。例如：SELECT 与 select、Select 是相同的。关键字用大写，表名、字段名用小写。
多条 SQL 语句必须以分号（;）分隔。
处理 SQL 语句时，所有空格都被忽略。
SQL 语句可以写成一行，也可以分写为多行。
反引号 ` ` 用于区分MySQL的保留字与普通字符、识别符，如用于表名、列名等，如 \status\ ；单引号 ` ‘’ `、双引号用于字符串。
SQL 支持三种注释：

## 注释1
-- 注释2
/* 注释3 */

SQL 语言分类

DDL（Data Definition Language）数据定义语言：主要用于数据库/数据表结构定义、对象定义（索引、视图、触发器等）。主要由 DBA 数据库管理员使用。核心指令是 CREATE、ALTER、DROP 语句。
DML（Data Manipulation Language）数据操作语言：主要功能是访问数据和对象。用于插入、更新、删除表记录。核心指令是 CRUD INSERT、UPDATE、DELETE、SELECT 语句。
- DQL（Data Query Language）数据查询语言：用于查询表记录，最频繁的数据库操作。核心指令是 SELECT 语句。
TCL （Transaction Control Language）事务控制语言：用于管理数据库中的事务。它还允许将语句分组为逻辑事务。核心指令是 COMMIT、ROLLBACK。
1. COMMIT：确认对数据库中的数据进行的变更；
2. ROLLBACK：取消对数据库中的数据进行的变更。
DCL（Data Control Language）：数据控制语言。主要用于控制用户的访问权限。核心指令是：
1. GRANT：增加用户权限；
2. REVOKE：收回用户权限；

-- 允许远程连接的 IP 地址，“%”表示不限制连接的 IP
-- 123456 为用户密码
MySQL> grant all PRIVILEGES on <数据库名.表名> to root@'127.0.0.1/%' identified by '123456' [WITH GRANT OPTION];

-- 开启MySQL的远程帐号，立即生效
FLUSH PRIVILEGES;

DDL 数据库/表结构定义

数据库操作

登录数据库：mysql -uroot -proot
创建数据库：create database test
查看所有数据库：show databases
删除数据库：drop database test
使用数据库：use test
查看所有数据表：show tables

表操作

创建表：create table \dept\(deptno int(2), deptname varchar(10));
查看表的定义：desc emp
查看表定义（详细）：show create table emp \G
删除表结构：drop table emp，释放表占用的空间；
清空表中数据：truncate table tb_user
修改表名：alter table emp rename emp1

字段：见下面的常用语句

添加表字段：alter table emp add column age int(3)
删除表字段：alter table emp drop column age
修改表字段：alter table emp modify ename varchar(20)
字段改名： alter table emp change age age1 int(4)

常用语句

创建：CREATE database [IF NOT EXISTS] <数据库名> / table <表名>

 [[DEFAULT] CHARACTER SET <字符集名>] 
 [[DEFAULT] COLLATE <校对规则名 utf8_chinese_ci>];

删除：DROP database [ IF EXISTS ] <数据库名> / table 表名;
选择：USE <数据库名>;
修改数据库：比较麻烦，不支持直接修改，需要通过其它方式间接修改。比如先创建新库，再导出导入数据。
修改：ALTER TABLE <表名> [修改选项]，修改选项有：
1. 改表名：RENAME TO <新表名>，rename table old_db.tb to new_db.tb;
2. ADD COLUMN <新列名> <类型> [约束条件] [FIRST | after <已存在的列名>]
3. DROP COLUMN <列名>
4. MODIFY COLUMN <列名> <类型> { SET DEFAULT <默认值>}：修改现有列（字段）的定义，数据类型和约束，如默认值、是否允许 NULL 等；
  - ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10, 2) NOT NULL;
5. CHANGE COLUMN <旧列名> <新列名> <新列类型不能为空>：用于重命名列；用于修改现有列的定义时，要对字段进行完整的定义。
6. CHARACTER SET <字符集名>
7. COLLATE <校对规则名>
修改存储引擎：ALTER TABLE <表名> ENGINE=<存储引擎名>;

CHARSET、COLLATE

在MySQL中对这些属性的配置是会逐层继承的，比如已经指定数据库的默认编码，那么创建表时就不需要指定，会自动从上层继承下来。

优先级：顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。

不过，在系统设计中，还是要尽量避免让系统严重依赖中文字段的排序结果，在mysql的查询中也应该尽量避免使用中文做查询条件。

CHARSET 属性：表示字符编码。
- 业务场景中因为经常会涉及到中文，常用的编码方式就是utf-8。
- 在MySQL中utf8编码，最多只能支持3 bytes长度的字符编码，对于一些需要占用4 bytes的字符需要用utf8mb4才行，这是MySQL的一个遗留问题。
- 5.7及以前的版本，需要手动将编码方式配置为utf8mb4，版本8+则默认就是该编码。
COLLATE 属性：校对规则名，用来排序的规则。
- 总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。
  1. 对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。
  2. 会影响到ORDER BY语句的顺序，WHERE条件中大于小于号筛选出来的结果，DISTINCT、GROUP BY、HAVING语句的查询结果。
  3. 另外建索引时，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。
- 通常是和字符编码CHARSET相关的，每种CHARSET都对应了多种它所支持的COLLATE，并且会指定一个默认。
  - 在国内比较常用的是：对于中文场景来说，二者没有什么区别。结论：推荐使用utf8mb4_unicode_ci，对于已经用了utf8mb4_general_ci的系统，也没有必要花时间改造。
    1. utf8mb4_general_ci（utf8mb4编码默认）：不区分大小写，在对特殊字符排序时可能会不一致，速度快。
      1. 问题：如果不特别指定的话，在用字符串作为主键的情况下，就会出现不一致的key（大小不一致）却产生冲突的现象。
      2. 解决：只能从字段上修改排序方式。因为在创建该字段时，已经隐形继承了上一级的排序方式，此时当上级（实例、库、表）修改时，不会影响该字段的排序方式了，只能显示指定该字段的排序方式。
    2. utf8mb4_unicode_ci：不区分大小写，精准比较，速度较慢。注意，在MySQL 8+版本中，默认的CHARSET直接改成了utf8mb4，对应的默认COLLATE也改成了utf8mb4_0900_ai_ci，这是unicode的一个细分，就相当于utf8mb4_unicode_ci。
    3. utf8mb4_bin：大小写敏感，也区分e和é这类字符。二进制数据存储
  - _ci后缀：是Case Insensitive的缩写，表示大小写无关。对应的_cs后缀表示大小写敏感。
  - 可以使用命令show collation;查看MySQL所支持的COLLATE。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `username` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '账号',
  `password` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '密码',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `table1` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

NULL、NOT NULL、DEFAULT 约束

NULL 约束：表示当前列可设置为空 null；MySQL默认情况下指定字段用NULL修饰符。
- MySQL中的NULL其实是占用空间的；
- "" 空字符串不等同于NULL，表示长度为0的字符或字符串，是可以计算的，比如 SELECT length(””); SELECT “”+1; 都有结果输出。
NOT NULL 非空约束：表示当前字段不能设置为空值（即不传值或传null），设置为空值就会报错；常搭配 AUTO_INCREMENT 用于 id 字段；
DEFAULT <默认值>：当前字段的默认值。不给字段传值、传了**`NULL`值**时使用默认值。传了`NULL`值时：
1. 如果设置为NOTNULL约束，则会报错。
2. ~~如果未设置NOTNULL约束，会使用DEFAULT值；~~

NOT NULL和DEFAULT是两个独立的约束条件，可以用在一个字段上，但不会交互使用。

姓名字段上不给值可以，用默认值。但是要给的话，一定不能给NULL值。NOT NULL DEFAULT ‘张三’

如：NOT NULL DEFAULT <默认值>

其它列属性/列约束

AUTO_INCREMENT：自动增长约束，必须为索引（主键或 unique），只能用于一个字段、不可为空（NOT NULL ）；只能是~~整数类型~~（bigint？），默认从1开始，通过表属性 auto_increment = x 设置起始值；
PRIMARY KEY：主键，不可空、不可重复；
FOREIGN KEY：外键约束；使用 SET FOREIGN_KEY_CHECKS=0; 来禁用外键约束。
UNIQUE：唯一索引（唯一约束），可空、不可重复，可在多个字段定义；主键和唯一约束的创建需依靠索引，没有已建好的索引，Oracle 会自动建立唯一索引。
CHECK：检查约束；
COMMENT：注释；

-- 在定义完列后直接用关键字指定约束
<字段名> <数据类型> 约束关键字/UNIQUE
ALTER TABLE <数据表名> ADD CONSTRAINT <约束名> UNIQUE(<列名>);
ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <数据类型> NULL DEFAULT <默认值>;
ALTER TABLE <表名> DROP INDEX <约束名>;

创建数据库表、定义表结构

INT和BIGINT的区别：

存储空间：BIGINT比INT占用更多的存储空间。INT 占 4 Byte，范围 -2^31-1 ~ 2^31-1，约21亿；BIGINT 占 8 Byte，范围 -2^63-1 ~ 2^63-1。
精度：由于BIGINT可以存储更大的整数，因此它在需要存储大量整数值的场景下可能更有用。然而，这也意味着它可能不适合存储精确的小数值。
自动增长：在INT和BIGINT之间，只有BIGINT可以作为自动增长的字段类型。

-- 如果数据库中存在user_accounts表，就从数据库中删除
DROP TABLE IF EXISTS `user_accounts`;

-- 创建数据库表
CREATE TABLE `user_accounts` (
  `id` 		  bigint(20)   			  NOT NULL AUTO_INCREMENT [primary key],
  `password`  varchar(32)  DEFAULT '' NOT NULL COMMENT '用户密码',
  `reset_pwd` tinyint(32)  DEFAULT 0  NOT NULL COMMENT '用户类型：0-不需重置密码；1-需重置',
  `status` 	  int(1) 	   DEFAULT '1' 		   COMMENT '帐号启用状态：0->禁用；1->启用'
  `mobile`    varchar(20)  DEFAULT '' NOT NULL COMMENT '手机',
  `create_at` timestamp(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL ,
  `update_at` timestamp(6) DEFAULT CURRENT_TIMESTAMP(6) NOT NULL ON UPDATE CURRENT_TIMESTAMP(6), --
  `login_time` datetime    DEFAULT   	  NULL COMMENT '最后登录时间',
  PRIMARY KEY (`id`),
  -- 创建唯一索引，不允许重复
  UNIQUE INDEX idx_user_mobile(`mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用户表信息';

INSERT INTO `ums_admin` VALUES ('1', 'test', 'pwd', 'timg.jpg', 'test@qq.com', '测试账号', null, '2018-09-29 13:55:30', '2018-09-29 13:55:39', '1');

DQL SELECT 查询语句

在表查询中，一律不要使用通配符 * 作为查询的字段列表，需要哪些字段必须明确写明。

增加查询分析器解析成本；
增减字段容易与 MyBatis 中的 resultMap 配置不一致；
无用字段增加网络消耗，尤其是 text 类型的字段；

IDEA 中使用的刷新功能（对应的查询语句）：

SELECT t.*
FROM mall.ums_admin t
ORDER BY id
LIMIT 501

子句执行顺序

SQL 子句优先级：

from：选择要操作的表。
1. on：满足连接条件的右表再 join 进来。
2. join：将不同表的记录合并起来，形成一张临时表进行查询。
where：在from后的表中设置筛选条件，筛选出符合条件的记录。
group by：把筛选出的记录进行分组。
1. count()/avg()/sum()：聚合函数。
2. having：设置条件筛选分组后的数据。（WHERE过滤的是行，HAVING过滤的是组，所以在GROUP之后）
select：选取经过上述流程后的结果集。
1. distinct：去重。
2. union：将多个查询结果联合。
3. order by：排序 asc/desc 排序 asc 升序, desc 降序。
4. limit：限制查询条数, limit(开始查询的索引,查询多少条); 根据这个可以用来做分页。

SQL 语法书写顺序：（写 SQL 语句的顺序）

select[distinct]
from
  join(如 left join)
  on
where
group by
  having
union
order by
limit

FROM 子句

AS 别名

AS 语法：用于为表、列（字段）提供临时名称(别名。用于：

投影查询：查询多列，只返回某些列：SELECT 列1 别名1, 列2 别名2 FROM ...
在 where 子句中作为条件
为子查询结果集指定别名

-- 作为 where 条件
select t1.name from table_1 as t1, table_2 as t2 where t1.id = t2.id;
-- 为子查询结果集指定别名
SELECT tmp.id FROM (SELECT * FROM result) AS tmp;

多表查询/笛卡尔查询

查询的结果是一个二维表，每一行都两两拼在一起，行数为多表的乘积，结果集可能非常巨大。

与 cross join 查询相同？

SELECT
    s.id sid,
    s.name,
    s.score,
    c.id cid,
    c.name cname
FROM students [AS] s, classes [AS] c
WHERE s.gender = 'M' AND c.id = 1;
[GROUP BY <group>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order>]
[LIMIT[<offset>,] <row count>]

JOIN ON 连接查询

JOIN 子句：将不同表的记录合并起来，形成一张临时表进行查询。

临时表仅存在于本次查询期间。
对多个表进行 JOIN 运算，先确定一个主表作为结果集（from），然后选择性地连接（join）其他表的行。
连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较（比如 = 运算符）作为连接条件（on），满足连接条件的记录才会合并为一行。
如果两张表的关联字段名相同，也可以使用 USING子句来代替 ON。

分类：

内连接 [INNER] JOIN <> ON <>：默认连接方式，交集。只有当两个表都存在满足条件的记录时才会返回行。
外连接 OUTER JOIN：
1. LEFT [OUTER] JOIN：必须返回左边表的记录，右表中满足连接条件的则有值，没有满足的则以null值取代。
2. RIGHT [OUTER] JOIN：
3. FULL [OUTER] JOIN：取并集，彼此没有对应的值为 null。只要其中有一个表存在满足条件？的记录，就返回行。
cross join 交叉连接：结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。
SELF JOIN 自连接：将一个表连接到自身，就像该表是两个表一样。为了区分两个表，在 SQL 语句中需要至少重命名一个表。

-- 左连接
SELECT * FROM TableA 
LEFT OUTER JOIN TableB 
ON TableA.name = TableB.name

select tb1.id, tb1.name, tb2.sex ...
from tb_user tb1
join tb_sex tb2
on tb1.id = tb2.id -- 连接条件，只有满足此条件的记录才会合并为一行。
order by tb1.id;

# 如果两张表的关联字段名相同，也可以使用USING子句：join....using()
select c.cust_name, o.order_num
from Customers c
inner join Orders o
using(cust_id) 		--
order by c.cust_name;

on 和 where 条件的区别：on 是 join on 的条件，先运行生成临时表，再根据 where 筛选；

on 连接条件：决定临时表的生成，条件不为真也会返回左表中的记录？。不管 on 中的条件是否为真，都会返回左边表中的记录。
where 条件：优先级低。是在临时表生成好后，再对临时表进行过滤，生成最终的结果集。这时已没有 left join 的含义（必须返回左边表的记录），条件不为真的就全部过滤掉。

WHERE 子句

用于过滤记录，即缩小访问数据的范围。
WHERE 后跟一个返回 true 或 false 的条件。

可以在 WHERE 子句中使用运算符：

逻辑运算符：and, or, not, xor，返回NULL、0、1。
比较运算符：=, !=/<>, <=>, <=, <, >=, >
1. = 表示比较两个值是否相等，但不能用于比较NULL；
  - NULL=NULL 的返回结果是 NULL，而不是 true;
2. <=>表示安全的等于；不同于=，当比较的两个值相等或都为 NULL时返回 true;
  - 当一个操作数为 NULL时，其返回值为 0 而不为 NULL;
  - NULL<=>NULL 的返回结果是 1(true);
3. NULL<>NULL 的返回结果是 false;
4. NULL<>1 的返回结果是 true;
条件判断：
1. ` is [not] null：更推荐ISNULL()`。
2. ` is [not] true/false/unknown`：检验某个值的真假。
3. ` [not] between <3> and <5>`：选取介于某个范围内的值。
4. ` [not] like [BINARY区分大小写]`：确定字符串是否匹配模式。搜索。
  1. 只有字段是文本值时才使用 LIKE
  2. 支持两个通配符匹配选项：不要滥用通配符，通配符位于开头处会非常慢。
    1. % ：表示任何字符出现任意次数、任何长度的字符串，不能匹配 NULL；
    2. _：表示任何字符出现一次。
5. [not] in ('DLL01', 'BRS01') ：在指定的几个特定值中任选一个值。尽量不用。
6. ... [NOT] EXISTS

子查询

子查询：是嵌套在较大查询中的 SQL 查询，也称内部查询或内部选择，包含子查询的语句也称为外部查询或外部选择。

简单来说，子查询就是指将一个 select 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。
子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。
子查询需要放在括号( )内。

子查询常用在 WHERE 子句和 FROM 子句后边：

当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。
当用于 FROM 子句时，一般返回多行多列数据，返回的结果相当于一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。需要使用 AS 关键字为该临时表起一个名字。

用于 WHERE 子句的子查询的基本语法如下：

select column_name [, column_name ]
from   table1 [, table2 ]
where  column_name operator ##
    (select column_name [, column_name ]
    from table1 [, table2 ]
    [where])

用于 FROM 子句的子查询的基本语法如下：

select column_name [, column_name ]
from (select column_name [, column_name ]
      from table1 [, table2 ]
      [where]) as tmp_table_name ##
where  condition

子查询的子查询：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'RGAN01'));

内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。执行过程可以参考下图：

GROUP BY 分组子句

group by：可以按一列或多列进行分组。将记录分组到汇总行中。为每个组返回一个记录 <class_id, num>。

通常还涉及聚合函数 MAX、MIN、COUNT、SUM、AVG等。
- count(字段名)：计算指定列中不为null的数量，不统计为null的值。仅仅只返回一个数字！
- count(0)/COUNT(1)/COUNT(*)：计算所有记录的数目，包括NULL值。
  1. 列名为主键，count(列名)会比count(1)快；列名不为主键，count(1)快。
  2. count(0)/count(1)效率快一些，count(*)最慢，因为它要先去找*代表的列名是什么。
- count (表达式）：分组里非空记录数。
- count(distinct password)：去重，返回指定列的不重复值的数目。共有多少种密码。
按分组字段进行排序后，order by 可以以汇总字段来进行排序。
GROUP_CONCAT(DISTINCT name [order by 排序字段 asc/desc ] [separator '分隔符-'])：拼接

SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
[ORDER BY cust_name DESC];

SELECT COUNT(*/id) 聚合列别名 FROM ...
-- 分组聚合子句，得到N行结果<class_id, num>
    SELECT COUNT(*) num  FROM students GROUP BY class_id;
-- 只支持分组的列，按class_id, gender分组:
    SELECT class_id,gender,COUNT(*) num  FROM students GROUP BY class_id, gender;

-- 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
-- group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。
-- 按性别分组，结果拼接name，如<female, Lily-Cat>
    SELECT `sex`, GROUP_CONCAT(DISTINCT name [order by 排序字段 asc/desc ] [separator '分隔符-'])  FROM tb_students_info GROUP BY sex;

COUNT 聚合函数、聚合查询：

sum ：求和，无结果或全为NULL时返回NULL，需注意 NPE 问题。下同
avg ：求平均值
max/min：求最大/小值，不限于数值类型

-- count 计数、统计，返回不同的非NULL值数目：
	count(*)：统计值不为 NULL 的行数；
    count(列名)：统计此列不为 NULL 值的行数，无结果返回0；
	count(distinct col)：计算该列除 NULL 外的不重复行数；
    count(distinct col1 , col2)：统计两列均不为 NULL 的行数（乘积）；如果其中一列全为 NULL，即使另一列有不同的值，也返回为 0。
    
SELECT COUNT(DISTINCT name,age) FROM student;

HAVING 分组条件子句

类似 where，过滤分组，对分组后/查询结果再次过滤，可用聚合函数、字段别名。

having：一般都是和 group by 连用，用于对汇总的 group by 结果进行过滤。
where 和 having 可以在相同的查询中。

having vs where：

where：过滤指定的行，后面不能加聚合函数（分组函数）。where 在group by 前。
having：过滤分组，一般都是和 group by 连用，不能单独使用。having 在 group by 之后。

-- 使用 WHERE 和 HAVING 过滤数据
SELECT cust_name, COUNT(*) AS NumberOfOrders
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) > 1;

函数

MySQL常用函数汇总

数学函数
字符串函数：LOWER()、UPPER()转换为小写或者大写。
日期和时间函数：
1. CurDate()：返回当前日期
2. CurTime()：返回当前时间
3. Date()：返回日期时间的日期部分
4. Month() ：返回一个日期的月份部分
聚合函数：见 group by中。
1. LAST_INSERT_ID()：获取最后插入的~~最新的~~记录的 id，需和 AUTO_INCREMENT 属性一起使用；当往带有 AUTO_INCREMENT 属性的字段的表中新增记录时，返回该字段的值。
  1. 可传入参数，如：LAST_INSERT_ID(10) 则返回10；
  2. 用一条 INSERT 语句插入多行时，只返回插入第一行数据时的 id，原因是这使依靠其它服务器复制同样的 INSERT语句变得简单。
条件判断函数：IF、IFNULL、CASE、WHERE 语句等；
系统信息函数：用于获取 MySQL 数据库的系统信息，包括获取数据库名、获取当前用户和获取数据库版本的函数等；
加密函数、格式化函数和锁函数等。

SELECT 结果子句

DISTINCT

DISTINCT 用于返回唯一不同的值。

它作用于所有列，也就是说所有列的值都相同才算相同。
表示对后面的所有参数的拼接取不重复的记录，即查出的参数拼接每行记录都是唯一的。

SELECT DISTINCT vend_id FROM products;

-- DISTINCT, ALL 选项：去除重复记录
SELECT DISTINCT name,age FROM student; -- 统计<name, age>二者都不相同的结果集
SELECT COUNT(DISTINCT name,age) FROM student;

UNION 合并结果

UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

UNION 基本规则：

所有查询的列数和列顺序必须相同。
每个查询中涉及表的列的数据类型必须相同或兼容。
通常返回的列名取自第一个查询。UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名。
默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

union VS union all：都用于合并两个或多个 SELECT 语句的结果集。

union：取唯一值，记录没有重复；进行表连接后会筛选掉重复的记录。
union all：直接连接，取到的是所有值，记录可能有重复；Union All 不去除重复记录。

JOIN vs UNION：

JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。
UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

ORDER BY 排序

order by：用于对结果集按照一个列或者多个列进行排序。默认为 ASC 升序。

对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。
ASC：升序（默认）
DESC：降序

SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;

LIMIT 限制结果数量

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

LIMIT <[初始位置,] 记录数>
LIMIT <记录数> OFFSET <初始位置>

-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;

-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;
-- 等价于
SELECT * FROM mytable LIMIT 2 OFFSET 3;

DML 数据操纵语句

返回 WHERE 条件匹配的行数及操作的行数。

插入记录

INSERT INTO ums_admin VALUES (‘张三’, 18, NULL);

CRUD 前先 validatePropertyExists(id) 校验是否存在。

检查表中是否已经存在这样的一条记录，如果存在则更新或者不做操作，如果没有存在记录，则需要插入一条新的记录。
Mysql提供了SQL语句实现上述逻辑的支持：
- INSERT ... SELECT
- INSERT ... ON DUPLICATE KEY UPDATE
- INSERT ... ON DUPLICATE REPLACE
比如想往表中插入一条数据，如果表中没有该条数据才插入，如果已经存在该条数据就不插入。
首先，在创建表时，将不需要重复的字段设置为unique，然后在插入时，使用insert ignore语句。

-- 指定名称插入、完整语句：（使用）id可不传值，使用了自增
insert into emp(ename, hiredate, sal, deptno) values ('zhangsan', '2018-01-01', '2000', 1);
INSERT [INTO] `user_info` name="Tom", age=21;

-- 不指定名称插入、省略名称：需要指定所有的值，包括必须指定id，无法使用自增
insert into emp values ('lisi', '2018-01-01', '2000', 1);
INSERT INTO `ums_admin` VALUES ('1', 'test', 'pwd', 'timg.jpg', 'test@qq.com', '测试账号', null, '2018-09-29 13:55:30', '2018-09-29 13:55:39', '1');

-- 批量插入数据：
insert into dept values(1,'dept1'),(2,'dept2');
INSERT [INTO] `user_info` ([name, age]) VALUES ("Tom", 21), ("Jack", 13);

-- 插入，如果已存在则更新
INSERT INTO students (id, name, gender, score) VALUES (1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;

删除记录

1	`DELETE FROM user_info WHERE ename='zhangsan'`

修改记录

UPDATE user_info  SET age=22,sal='4000',score=score+10  WHERE name="Tom" and score<80;

DQL 查询记录

主要见 DQL 语句。

查询所有记录：select * from emp
查询不重复的记录：select distinct deptno from emp
条件查询：select * from emp where deptno=1 and sal<3000
排序和限制：select * from emp order by deptno desc limit 2
聚合（查询部门人数大于1的部门编号）：select deptno,count(1) from emp group by deptno having count(1) > 1？
连接查询：select * from emp e left join dept d on e.deptno=d.deptno
子查询：select * from emp where deptno in (select deptno from dept)
记录联合：select deptno from emp union select deptno from dept

DCL 数据控制语句

可利用 DCL 控制的权限有：CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。

授予操作权限：grant select,insert on test.* to 'test'@'localhost' identified by '123'
收回操作权限：revoke insert on test.* from 'test'@'localhost'

字符集相关

查看字符集：show variables like 'character%'
创建数据库时指定字符集：create database mall character set utf8

修改时区

修改mysql全局时区为北京时间，即我们所在的东8区：set global time_zone = '+8:00';
修改当前会话时区：set time_zone = '+8:00'
立即生效：flush privileges

权限相关

-- 授予所有数据库的所有权限：
grant all privileges on *.* to z1@localhost identified by '123'
-- 授予所有数据库的所有权限（包括grant）
grant all privileges on *.* to z1@localhost with grant option
-- 授予`SUPER PROCESS FILE`权限
grant super,process,file on *.* to z3@localhost
-- 只授予登录权限
grant usage on *.* to z4@localhost

-- 查看账号权限
show grants for z1@localhost
-- 修改自己的密码
set password = password('123')
-- 管理员修改他人密码
set password for 'z1'@'localhost' = password('123')
-- 删除账号
drop user z2@localhost

TCL 事务控制语言

COMMIT：确认对数据库中的数据进行的变更；
ROLLBACK：通过undo log（回滚日志）回滚，取消对数据库中的数据进行的变更。

drop、 truncate、 delete 区别

用处不同：
1. drop table 表名（删除表结构）：释放表占用的空间。
2. truncate table 表名（清空表中数据） : 用于删除表内的数据和索引，但不删除表本身。重置自增值、索引恢复到初始大小，再插入数据时自增长 id 又从 1 开始。
3. delete from 表名 where 列名=值（删除某些行的数据） : 不加 where 子句时作用与truncate table 表名 类似。
属于不同的数据库语言，能否回滚：
1. drop 和 truncate 属于 DDL语句，操作立即生效。不支持事务回滚，数据删除后无法找回。有可能造成事故，故不建议使用。
2. delete 是 DML 语句，~~操作会放到 rollback segement 中~~，事务提交后才生效。配合事件回滚可找回数据。
执行速度不同：一般来说 drop > truncate > delete。
1. drop命令：会把表占用的空间全部释放掉。
2. truncate命令：执行时不会产生数据库日志，因此比delete要快。
3. delete命令：执行时会产生数据库的binlog日志，逐行删除记录，需要消耗时间，因此比truncate慢，但是好处是方便数据回滚恢复。

SQL查重和去重

在几千条记录里，如何用SQL语句删除掉重复的?

使用distinct；
使用group by；
使用ROW_NUMBER() OVER 或 GROUP BY 和 COLLECT_SET/COLLECT_LIST。row_number() over (partition by <用于分组的字段名> order by <用于组内排序的字段名>)。

-- 1. 使用distinct查重
select distinct id,password from ums_admin;

-- 2.1 查找表中所有的重复记录，重复记录是根据单个字段来判断
select * from people 
where peopleId in (select peopleId from people 
                   group by peopleId 
                   having count(peopleId) > 1); --此时在in后，count(0) > 1作用相同

-- 2.2 查找表中多余的重复记录，不包含rowid最小的记录
select * from people --其余同下
-- 2.3 删除表中多余的重复记录，根据单个字段来判断，只保留rowid最小的记录
delete from people 
where peopleId in (select peopleId from people 
   			group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people 
   			group by peopleId having count(peopleId) > 1)

-- 2.4 查找表中所有的重复记录（多个字段）
select * from vitae a 
where (a.peopleId,a.seq) in (select peopleId,seq from vitae 
					 group by peopleId,seq having count(*) > 1); --此*指peopleId,seq
SELECT user_id,count(1) [as num] FROM 表名
WHERE 条件  GROUP BY user_id HAVING count(*)>1;

-- 2.5 查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a --其余同下
-- 2.6 删除表中多余的重复记录（多个字段），只保留rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae 
                             group by peopleId,seq having count(*) > 1) 
           and rowid not in (select min(rowid) from vitae 
           					 group by peopleId,seq having count(*) > 1)

删除表前1W行数据

方式有：

直接 delete from T limit 10000：缺点是单条语句执行时间过长，会导致长事务，导致锁的时间变长、主从延迟等问题。
在一个连接中循环执行20次 delete from T limit 500：多个短事务串行执行，每次短暂执行就释放锁资源，相对更好一些；可优化为第3种并发执行。
在20个连接中并发（同时）执行 delete from T limit 500：会人为导致锁冲突。
- 但如果能将1W条数据先查出来，分片后分配给20个连接，通过id=xxx精确删除，各连接就不会相互竞争。
- 参考慢查询、大事务。

索引、视图、触发器、存储过程

DDL（Data Definition Language）数据库定义语言：主要用于定义数据库/数据表结构、视图、索引和触发器等对象。主要由 DBA 数据库管理员使用。CREATE、ALTER、DROP 等语句。

索引

索引：是一种用于快速查询和检索数据的数据结构，相当于目录的作用。实质上是一张描述索引列的列值与原表中记录（行）间一一对应关系的有序表。

优点：大大加快数据检索速度；
缺点：创建和维护索引耗时间和空间。当对表中的数据增删改时，需同时修改索引，降低 SQL 执行效率。
索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。

索引的数据结构分类

从数据结构角度出发，索引分为B树索引，B+树索引，哈希索引和位图索引。

在MySQL上，主要是采用B+树索引，
B树索引在NoSQL上使用较多，
哈希索引在KV数据库上较为常见。

使用索引后，会将表中的记录按照某种规则转换为平衡树结构，大大减少查询的次数。

Hash 表、哈希索引

见数据结构和手撕算法文档中。很少问。

哈希索引是利用哈希表来实现的，适用于等值查询，如等于，不等于，IN等，不支持范围查询。

哈希索引在KV数据库上比较常见。
innodb引擎不支持用户自定义哈希索引，但是有一个优化会建立自适应哈希索引。
- 自适应哈希索引，是指innodb引擎，如果发现二级索引（除了主键以外的别的索引）被经常使用，那么innodb会给这个索引建立一个哈希索引，加快查询。所以从本质上来说，innodb的自适应哈希索引是一个对索引的哈希索引。

哈希表：是键值对的集合，本质是数组 + 哈希函数。用哈希算法通过键（key）即可快速取出对应的值（value）。

缺点：

哈希冲突：~~JDK1.8 前 HashMap 用链地址法实现，JDK1.8 后 HashMap 用红黑树；~~哈希扩容。
最大缺点是：Hash 索引不支持顺序和范围查询，hash 表存储的是无序数据，范围查找时需遍历，比较耗费时间。
将所有的数据文件添加到内存，比较耗费内存空间。

B-Tree/多路平衡查找树

二叉树及其变种的其他树（BST、AVL 树）都不能支撑索引的需求，原因是插入数据的性能较低，且树的深度无法控制，过深造成 IO 次数变多，影响读取数据的效率。

B 树 VS B+ 树：

节点：B 树的所有节点存放键和数据，而 B+ 树只有叶子节点存放 key 和 data，其他非叶子节点只存放 key。
叶子节点：B 树的叶子节点是独立的；B+ 树的叶子节点有引用链指向相邻的叶子节点。
查找：B 树的检索相当于对节点的关键字做二分查找，可能不到叶子节点就找到；而 B+ 树的查找是从根节点一直到叶子节点。

面试官可能同时希望你能够横向比较B+树、B树、平衡二叉树，红黑树和跳表。

MySQL使用B+树，主要就是考虑三个角度：

和二叉树（如平衡二叉树、红黑树）相比：B+树是多叉树，高度低。比如MySQL默认是1200叉树，同样数据量，高度要比二叉树低。
和B树相比：
1. B+树的叶子节点被连接起来，形成了一个链表。这意味着，当执行范围查询时，MySQL可以利用这个特性，沿着叶子节点前进。而之所以NoSQL数据库会使用B树作为索引，也是因为它们不像关系型数据库那般大量查询都是范围查询。
2. B+树只在叶子节点存放数据，树的深度可控制，因此查询时间稳定可预测。（在工程实践中，可能倾向于追求一种稳定可预测，而不是某些数据贼快，某些数据唰一下贼慢）
和跳表相比：MySQL将B+树节点大小设置为磁盘页大小。这样可以充分利用MySQL的预加载机制，减少磁盘IO。

索引形态分类

从形态上来说，可以分成：

普通索引（INDEX）：
唯一索引（UNIQUE）：指数据库里面要求该索引值必须要唯一，一般用于业务唯一性保证；如身份证号、邮箱等；根据业务要求，不宜作为主键但有唯一性约束；可为 NULL；
主键索引（PRIMARY）：索引的 key 是数据表的主键。一般它的叶子节点要么存储了数据，要么存储了指向数据的指针。MySQL的innodb引擎存储的是数据，MyISAM放的是数据的地址；
覆盖索引：指查询的列全部命中了索引。避免回表。相当于目录。也称索引覆盖，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。
前缀索引：指只利用了数据前几个字符的索引，适用于字符串、前面几个字符区分度好的情况；
全文索引（FULLTEXT）：用于检索大文本数据中关键字的信息。现在比较少用，一般推荐使用别的中间件来完成，例如 ES 分布式搜索引擎；
联合索引：指多个列组成一个索引。创建的时候我们会考虑把区分度好的索引放在前面，因为MySQL遵循最左前缀匹配原则；
- 最左前缀匹配原则是指：MySQL会按照联合索引创建的顺序，从左至右开始匹配。
- 例如创建了一个联合索引（A，B，C)，那么本质上来说，是创建了A，（A，B），（A，B，C）三个索引。之所以如此，因为MySQL在使用索引的时候，类似于多重循环，一个列就是一个循环。
- 在这种原则下，我们会优先考虑把区分度最好的放在最左边，而区分度可以简单使用不同值的数量除以总行数来计算（distinct(a, b, c)/count(*)）。

聚集索引、非聚集索引

这两个名字虽然都叫做索引，但这并不是一种单独的索引类型，而是一种数据存储方式。

这两种索引都是使用B+树组织的。

见 MyISAM、InnoDB 引擎的区别之一为索引实现不同

从是否存储数据的角度，又可以分为聚簇索引和非聚簇索引。

MySQL的主键就是聚簇索引，每张表唯一一个，非聚簇索引的数据本质上存储的是主键。

而对于MySQL的innodb来说，它的行锁是利用索引来实现的，所以如果查询的时候没有索引，那么会导致表锁。

聚簇索引和非聚簇索引的区别：叶子节点是否存储了数据。

聚簇索引：指叶子节点存储了数据的索引。MySQL整张表可以看做是一个聚簇索引。
非聚簇索引：没有存储数据，MySQL的一般是存储了主键。

覆盖索引不需要回表。

回表：即如果查询的列包含不在索引上的列，这会引起数据库先根据非聚簇索引找出主键，而后拿着主键去聚簇索引里边捞出来数据。而根据主键找数据会引起磁盘IO，性能大幅度下降。

索引的存储方式及类型：

聚集索引

聚集索引（聚簇索引、集群索引）：索引结构和数据一起存放。B+ 树的所有节点都会存储索引（key），每个非叶子节点只存储索引，另外，叶子节点还存储索引和对应的行数据。InnoDB 使用的是聚簇索引。

优点：查询速度非常快。对于主键的排序查找和范围查找速度非常快。
1. ~~在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。~~
2. 并且由于叶子节点就是数据，所以只需要查找一次就可以得到结果。多路平衡查找树的叶子节点都是有序的，定位到索引的节点，就相当于定位到了数据。
3. 对于主键的排序查找和范围查找速度非常快：因为聚集索引在逻辑上连续的。比如查询后10条数据，由于B+树索引是双向链表，可以很快找到后一个数据页，然后取出其后的10条数据。
缺点：依赖于有序的数据；更新代价大。

非聚集索引

非聚集索引（辅助索引、二级索引）：索引结构和数据分开存放。MyISAM 使用的是非聚簇索引，索引文件和数据文件是分离的，需要根据索引去查找数据。

查找索引：
1. ~~主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。~~
2. 表数据存储在独立的地方，主键索引、辅助键索引这两颗B+树的叶子节点都使用一个地址指向真正的表数据。
3. 由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
~~通过非聚集索引查询数据时，查询到叶子节点上的主键值后，再利用这个主键值查询聚集索引，从而查询到具体的行记录，需要遍历两次树。~~
~~真实的数据行不会按照非聚集索引排序存储，但索引项的内容是按顺序存储的。~~
所有节点都会存储索引列的值。一个表可以有多个非主键索引，因此会建立多个非聚集索引，每建立一个非聚集索引，都会将该非聚集索引关联的字段数据复制出来一份，用于生成以该列为基础的平衡树。会增加表的体积，占用磁盘空间，所以不是索引越多越好。

参考：具体链表示意图

主键索引

主键索引（PRIMARY）：索引的 key 是数据表的主键。

InnoDB表中聚集索引的索引列就是主键，所以聚集索引也叫主键索引。
- 一般情况下，指定id为主键，就会生成一个以id为基础的聚集索引。真实的数据行会按照主键排序，顺序存储在磁盘上，比如id为1和2的对应记录在磁盘上相邻存储。
- 建议使用自增的字段作为主键，这样B+Tree的每一个结点都会被顺序的填满，而不会频繁的分裂调整，会有效的提升插入数据的效率。
- 主码 id 选取的基本原则：选取完全业务无关的字段，不可用身份证号、手机号、邮箱地址等（看上去唯一但可能会更改）。可用作 id 字段的类型有：
  1. 自增整数类型：INT 上限约21亿，BIGINT约922亿亿。
  2. 全局唯一GUID字符串类型：GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），
- ~~如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，~~
- ~~如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。~~
- 建
一张表只有一个主键，因此一个表只有一个聚集索引。
辅助索引：叶节点 data 域存储的数据是聚簇索引键值（通常是主键的值）而不是地址，用于回表查询该条记录，用来定位主键的位置。
查找索引：
1. 若（使用”where id = 14”这样的条件）查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name等其它列进行条件搜索，以辅助索引查找时则需要两个步骤：会先根据辅助索引找到主键，再根据主键索引找到实际的数据。
  - 因为和MyISAM不同，InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址。
  - 所以Innodb不建议使用过长的主键，否则会使辅助索引变得过大。
最大缺点：可能会二次查询（回表），当查到索引对应的指针或主键后，可能还需再到数据文件或表中查询。

假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

为什么使用自增主键

MySQL的主键是一个聚簇索引，即它的叶子节点存放了数据。

在使用自增主键的情况下，会保证树的分裂照着单方向分裂的，这会大概率导致物理页的分裂也是朝着单方向进行的，即连续的。
在不使用自增主键的情况下，如果在已经满的页里面插入，会导致MySQL页分裂，虽然逻辑上页依旧是连续的，但是物理页已经不连续了。
- 如果在使用机械硬盘的情况下，会导致范围查询经常导致机械硬盘重新定位，性能差。

关键点：单方向增长，物理页连续

添加索引的 SQL 语句

见DDL 其它列属性/列约束

1. CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC]);
2. CREATE TABLE(
	...
	KEY | INDEX | UNIQUE INDEX [<索引名>] [<索引类型>] (<列名>,…)
);
SHOW INDEX FROM <表名> [ FROM <数据库名>];
DROP INDEX <索引名> ON <表名>;

-- 3.1. 添加 主键索引、唯一索引、全文索引
ALTER TABLE `table_name` ADD PRIMARY KEY / UNIQUE / FULLTEXT ( `col` );

-- 3.2.添加 普通索引、多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `col`[,`col2`] );
-- 也可只对某一列添加唯一约束而不创建唯一索引：
ALTER TABLE students ADD CONSTRAINT uni_name UNIQUE (name);

建立和使用索引的时机

在 WHERE 和 JOIN 语句中出现的列需建立索引，但也不完全如此。

选用 JOIN、WHERE、GROUP BY、DISTINCT、ORDER BY 中的字段；

MySQL 只对<，<=，=，>，>=，BETWEEN，IN 使用索引；
某些时候的 LIKE 也会使用索引；但在 LIKE 以通配符 % 和 _ 开头做查询时，不会使用索引。如，name like '123%'会走索引。

见数据库性能优化中的1. 使用索引的注意事项。

视图

视图：是基于 SQL 语句的结果集的可视化的表。

是虚拟的表，本身不包含数据，也就不能对其进行索引操作。
数据库中只存定义，数据来自所引用的真实表。
对视图的操作和对普通表的操作一样。

作用：

简化复杂的 SQL 操作，比如复杂的联结；
只使用实际表的一部分数据；
通过只给用户访问视图的权限，保证数据的安全性；
更改数据格式和表示。

CREATE VIEW <视图名> AS <SELECT语句>;
CREATE VIEW v_stu_info (s_id, s_name, d_id, s_age) AS 
SELECT id,name,dept_id,age FROM tb_stu_info WHERE id < 10;

DESCRIBE 视图名;

触发器

create trigger <触发器名称>  
{ before | after}         -- 之前或之后触发
insert | update | delete  -- 指明激活触发程序的语句的类型  
on <表名>                  -- 操作哪张表  
for each row              -- 触发器的执行间隔，通知触发器每隔一行执行一次动作，而不是对整个表执行一次。  
<触发器SQL语句>

存储过程

存储过程：是一组为了完成特定功能（业务逻辑）的 SQL 语句集合，中间加了逻辑控制语句。更偏向于业务逻辑。

是SQL语句和控制语句的预编译集合，经编译和优化后保存在数据库中，可由应用程序调用执行。
而且允许用户声明变量、逻辑控制语句及其他强大的编程功能。
包含逻辑控制语句和数据操作语句，可以接收参数、输出参数、返回单个或多个结果值及返回值。

优点：

在业务比较复杂时非常实用，如一个操作需写一大串 SQL 语句，存储过程方便下一次的调用。
模块化程序设计，只需创建一次，以后即可调用该存储过程任意次；一旦调试完成通过后就能稳定运行；
比单纯 SQL 语句执行要快，因为存储过程是预编译过的。

缺点：难以调试和扩展，没有移植性，消耗数据库资源。禁止使用。

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>

数据库性能优化

除了索引外，有过哪些SQL优化方面的经验？如分库分表，或通过执行计划查看SQL的优化点。

最好是能结合做的实际项目来讲。

优化目标

减少 IO 次数：IO永远是数据库最容易瓶颈的地方，是 SQL 优化中需要第一优先考虑的，也是收效最明显的优化手段。
降低 CPU 计算：CPU 处理内存中的数据比较运算（如 order by,group by,distinct … 都是消耗 CPU 的大户）。

为什么数据库性能会慢？优化的整体思路

应该站在哪个层面思考优化？

考虑成本、性价比。

每一层的上层是该层的载体，因此越往顶层越能决定性能的上限，同时优化的成本也相对会比较高，性价比也随之越低。

无论是关系型数据库还是 NoSQL，任何存储系统的查询性能的决定因素主要有三种：

查找的时间复杂度，主要有两个决定因素：
1. 查找算法：1. 使用索引、2. 优化 SQL 语句。
2. 存储结构：3. 选择合适的存储系统（DBMS）：读写频繁数据少可以用SQL，高并发海量数据用NoSQL。
数据总量：数据量越多，资源的消耗（CPU、磁盘读写繁忙）越多、耗时越长。
1. 思考能否从物理表设计的层面优化：4. 合理设计表结构、字段。
高负载、高并发请求：导致 CPU、磁盘繁忙，而服务器资源不足则会导致慢查询等问题。
1. 5. 数据库架构优化、用空间换性能：
  1. 高并发、高性能：选择负载均衡、通过读写分离、分库分表、数据库连接池来减少数据量。
  2. 高可用：集群、数据冗余、分布式缓存等。
2. 6. 服务器硬件：升级磁盘、CPU。加钱上大内存。例如：增加CPU核数如32核，升级更好的网卡如万兆，升级更好的硬盘如SSD，扩充硬盘容量如2T，扩充系统内存如128G。

1. 首先使用索引

慢查询有一大部分原因都是因为没加索引导致的。

索引是提高数据库查询效率的关键，性价比最高。通过在经常查询的列上创建索引，可以加速数据的检索速度，从而提高读取查询的性能。

InnoDB引擎默认加的是行锁，但锁其实都是加在索引上的，如果筛选条件没有建立索引，会降级到表锁。

使用索引的注意事项

主键索引：
选择合适的列：只在需要高选择性、高效检索、重复值较低的列上创建索引。避免在大字段或低选择性的列上创建索引，因为这可能导致索引维护开销变大。
- 比如说，在 WHERE、GROUP BY、ORDER BY 子句中使用的列上创建索引。
使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。
不要让字段的默认值为NULL：只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。mysql查询只使用一个索引，~~因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的~~。
- 如果有多列索引（联合索引），考虑索引顺序、最左前缀匹配原则：列的顺序会影响查询性能。将选择性高、经常用于查询条件的列放在索引的前面。
排序字段使用索引，如果没有用到索引，就尽量少用排序。可以在代码中排序。~~排序操作会消耗较多的 CPU 资源，减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。~~
控制索引数量：过度使用索引、过多的索引会增加数据维护（插入、更新和删除）的开销，同时也会占用更多的存储空间。因此，只创建必要的索引。建议单张表索引不超过 5 个。
- 如果索引过多，以至于内存没法装下全部索引，那么会导致访问索引本身都会触发IO。所以索引不是越多越好。比如为了避免数据量过大，某些时候会使用前缀索引。
索引下推：指将与索引有关的条件由MySQL服务器下推到引擎。
1. 例如按照名字存取姓张的，like “张%”。在原来没有索引下推的时候，即便在用户名字上建立了索引，但是还是不能利用这个索引。
2. 而在支持索引下推的引擎上，引擎就可以利用名字索引，将数据提前过滤，避免回表。
3. 目前innodb引擎和MyISAM都支持索引下推。索引下推和覆盖索引的理念都是一致的，尽量避免回表。

适当使用视图加速查询

经常被查询的列数据，并且这些数据不被经常的修改，删除。有助于避免多重排序操作。

封装存储过程

存储过程：是一组为了完成特定功能（业务逻辑）的 SQL 语句集合，中间加了逻辑控制语句。更偏向于业务逻辑。

是SQL语句和控制语句的预编译集合，经编译和优化后保存在数据库中，可由应用程序调用执行。

2. 优化 SQL 语句

编写高效的查询语句可以显著提升性能。

避免使用通配符查询，如 select *，尽量使用精确的条件。group by 聚合函数用 count(1) 而不是 count(*)。
避免使用子查询，会产生大量的临时表、导致级联查询，也没有索引，消耗过多的 CPU 和 IO 资源，对查询性能的影响大。
- 用 JOIN 连接查询代替子查询、join 简单表不易超过3个、小表驱动大表、链接字段建索引。子查询在 in 子句中，且为简单 SQL（不包含 union、group by、order by、limit 从句）时，可优化为 join 关联查询。
- 使用合适的连接方式（如 INNER JOIN、LEFT JOIN）可以避免产生大量冗余数据。
- 尽量不要使用 join 操作。因为 join 的效率低，并且会对分库分表造成影响。对于需要用到 join 操作的地方，可以采用多次查询业务层进行数据组装的方法。
在 where 子句中不要使用某些操作符：否则将导致索引失效（存储引擎将放弃使用索引）进而进行全表扫描。
1. 索引列上做了计算：
  1. 避免使用 **!=或<> **两种逻辑比较操作符，可以用 > or < 代替；
  2. 尽量避免在 = 的左边进行函数等表达式运算；
  3. 避免使用 or 关键字来连接条件：如果一个字段有索引，另一个字段没有索引，将导致索引失效进而进行全表扫描。可以用 union all 代替；
  4. 避免使用 is null /is not null 对字段进行null值判断；
  5. 避免使用 not in，可以用 not exists 代替；
  6. 避免对字段进行函数操作，比如 COUNT 等聚合函数；select * from users where YEAR(adddate)<2007; 改为 where adddate<‘2007-01-01′;
2. 避免模糊查询：like 使用前置匹配（like ""%aaa" ）会导致全表扫描。而后缀匹配（like "aaa%"）可以使用索引。
3. 字符串没有引号导致类型转换：
4. 使用索引字段为条件时，若为复合索引，那么必须使用到该索引中的第一个字段作为条件，这样才能保证索引被正确使用。
5. 使用参数也会导致全表扫面。
用Where子句替换HAVING子句：因为HAVING只会在检索出所有记录之后才对结果集进行过滤。
能用DISTINCT的就不用GROUP BY。
尽量用 union all 代替 union：差异主要是 union 需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。当然，union all 的前提条件是两个结果集没有重复数据。
update语句尽量只更新必须的字段，减少性能消耗。

使用索引了为什么还是很慢？

本质上是因为，MySQL 的执行速度是受到很多因素影响的。准确来说，索引只是大概率能够加速这个过程而已。

索引只能帮助快速定位数据，但之后读数据、写数据都需要时间。尤其是在事务中，索引定位到数据之后，可能一直在等待锁。如果别的事务执行时间缓慢，那么即便用了索引，这一次的查询还是很慢。
另外要考虑，是否使用错了索引。如果表上面创建了多个索引，那么就会导致 MySQL 选择使用了不那么恰当的索引。
- 一种是如上面的， SQL 语句没写好，在 where 子句中不要使用某些操作符：否则将导致索引失效（存储引擎将放弃使用索引）进而进行全表扫描。
- 另一种，则是 MySQL 判断到使用索引的代价很高，比如说要全索引扫描并且回表，那么就会退化成为全表扫描。数据库数据量的大小和数据分布，会影响MySQL的决策。
- 解决：可以通过数据库的 Hint 机制提示数据库走某个索引。

启用慢查询日志定位低效率的SQL语句并优化

启用慢查询标志，识别、分析和调整执行时间超过阈值的 SQL 查询语句以提高性能。

慢查询：SQL语句响应时间超过阀值、查询的 SQL语句耗费较长的时间（超过阈值）。

慢查询日志：是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。

long_query_time 阈值的默认值为 10秒。正常情况下，MySQL 不会自动开启慢查询。

慢查询的危害：

用户体验差；
占用内存，影响性能、性能变差；
造成DDL操作阻塞：InnoDB引擎默认加的是行锁，但锁其实都是加在索引上的，如果筛选条件没有建立索引，会降级到表锁。
- 而慢查询有一大部分原因都是因为没加索引导致的，所以慢查询时间过长，就会导致表锁的时间也很长，如果这时候执行DDL就会造成阻塞。

原因、常见场景：

没加索引会造成全表扫描，又或者没利用好索引、没走到索引、走的不是最优索引，都会导致扫描行数增多，从而查询时间变慢。
单表数据量太大：可能超千万，或者达到亿级别，加了索引之后，还是存在慢查询的情况，需要查看索引设置得对不对，或者分表。
排序字段没加索引。
Limit 深分页：即从比较后面的位置开始进行分页，比如每页有10条，然后我要看第十万页的数据，这时候的分页就会比较“深”。
- 在这种情况下，即使字段加了索引，查询速度可能还是很慢（和不加差不多），因为二级索引树存的是主键ID，查到数据还需要进行回表才能决定是否丢弃。
- 解决思路：都是先查出主键字段（id），避免回表，再根据主键查出（/删除）所有字段。

3. 选择合适的存储系统

读写频繁数据少可以用SQL，高并发海量数据用NoSQL。

索引的数据结构基本固定是 B+Tree，时间复杂度是 O(log n)，存储结构是行式存储。

4. 合理设计表结构、字段

良好的表设计可以提高查询性能。

遵循范式设计表：符合3NF，比如消除冗余字段（节省空间）；
反范式优化：比如适当加冗余等（减少join）；
拆分表：
1. 分区将数据在物理上分隔开，不同分区的数据可以指定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间；
2. 另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。
3. 对数据量大时表可采取此方法，可按月自动建表分区。

字段设计注意事项：

始终定义主键，最好是使用自动递增的整数值主键：以便更快地查找、检索和排序记录，从而提升性能。主键缺失也是导致基于行的复制延迟的主要原因之一。
禁止使用外键、级联查询，外键与级联查询存在更新风暴的风险；外键约束问题必须在应用层解决；
1. ~~针对于互联网大数据量高并发量的数据存储场景来说；~~
2. 级联查询：是指一个查询的结果依赖于另一个查询的结果，通常是通过子查询或者嵌套查询实现的。
3. 更新风暴问题：是指由于某些约束，例如外键约束或者触发器约束，当一条记录被更新时，相关约束的记录也会被更新，引发一系列连锁反应，导致短时间大量更新操作引发数据库性能下降甚至死锁的问题。因此，分布式场景一般禁止使用外键约束，或者触发器约束。
尽量把所有列定义为 NOT NULL：NULL 类型比较特殊，SQL 难优化。
数据类型选择原则是：字段的长度在最大限度满足需要的前提下，尽可能短一些，以提高查询的效率，而且在建立索引时也可以减少资源的消耗。
1. 数字类型：
  1. 非万不得已不要用 DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。
  2. 同样，固定精度的小数，也不建议用 DECIMAL，可以乘以固定倍数转换成整数存储，可以大大节省存储空间。
  3. 只含有数字的字段尽量不要设计为字符串，这会降低查询和连接的性能。
2. 字符类型：尽可能使用varchar/nvachar 代替 char/nchar，因为变长字段存储空间小，可以节省存储空间。
  1. 定长字段：建议使用 CHAR 类型（查询快**，但是耗存储空间，可用于用户名、密码等长度变化不大的字段）。
  2. 不定长字段：尽量使用VARCHAR（查询相对慢一些，但是节省存储空间，可用于评论等长度变化大的字段）。
3. 时间类型：尽量用TIMESTAMP类型，因为其存储空间只需要DATETIME 类型的一半。
  1. 对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节，比TIMESTAMP还少。
  2. 不建议通过INT类型类存储一个unix timestamp 的值，因为这太不直观，会给维护带来不必要的麻烦，同时还不会带来任何好处。
4. ENUM & SET：对于状态字段，可以尝试使用 ENUM 来存放，因为可以极大的降低存储空间，而且即使需要增加新的类型，只要增加于末尾，修改结构也不需要重建表数据。
选择合适的字符编码、字符集：字符集直接决定了数据在MySQL中的存储编码方式，由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异，可以帮助我们尽可能减少数据量，进而减少IO操作次数。

5. 数据库架构优化

用空间换性能，见分布式文档中。

高负载、高并发请求：导致 CPU、磁盘繁忙，而服务器资源不足则会导致慢查询等问题。

高并发、高性能：减少数据量
1. 负载均衡：用于将如用户请求（根据负载均衡算法）分配、转发到多台服务器（集群）处理，以提高系统整体的并发处理能力和可靠性。可以解决高负载的问题。
  - 当某个节点故障时，负载均衡器会将用户请求转发到另外的节点上，从而保证所有服务持续可用。
2. ~~消息队列。~~
3. 读写分离：主要是为了将数据库的读和写操作分散到不同的数据库节点上。
  1. 部署多台数据库，主服务器负责写，从服务器负责读，通常一主多从。
  2. 主从数据库间（通过主从复制）进行数据同步。
4. 分库分表：按照业务划分数据库、按照规则拆分表到不同数据库，垂直拆分列、水平拆分行。
  - 尽量控制单表数据量的大小在 500 万、单表容量 2GB 以内。过大会造成修改表结构、备份、恢复问题。
5. 冷热数据分离、使用分布式缓存：减小表的宽度。减少磁盘 IO，保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大，会消耗更多的 IO）。
6. 数据库连接池：是一种创建和管理连接池的技术，可供任何需要它们的进程使用。连接池可显著提高应用的性能，同时减少总体资源用量。池化技术。
高可用：
1. 数据冗余：
  1. 通过高可用集群：同一份服务部署两份或者多份组成集群，使用负载均衡（根据每个节点的负载情况）将用户请求转发到集群中合适的节点上，避免单一服务器的负载压力过大导致性能降低，从而保证服务的高可用。当某个节点故障时，也能快速自动切换服务器。
  2. 同城灾备、异地灾备，同城多活、异地多活。
2. 限流、降级、熔断。
3. 超时和重试机制。

6. 服务器硬件

升级磁盘、CPU。加钱上大内存。

7. 其他优化

避免大型事务

在单个事务中对记录进行过多（更新、删除、插入）将占用过多记录的资源（锁、缓冲区）。

会导致缓冲区池中的数据过多，从而阻止进一步使用缓冲区池。可能会溢出日志缓冲区，从而导致磁盘 IO。
剩余查询将必须等待资源或锁被释放。
大型事务的回滚也会降低数据库的性能。

为克服此问题，建议将大型事务拆分为多个小型且运行速度更快的事务。

定期备份和优化

定期优化可以清理无用数据和重建索引；
定时的去清除不需要的数据、定时进行碎片整理；
日志历史数据归档；

MySQL 配置

InnoDB 缓冲区池大小
重做日志/InnoDB 日志文件大小：InnoDB 日志文件或重做日志会记录表数据的数据更改。
1. InnoDB 日志文件大小定义了单个重做日志文件的大小。
2. 对于具有较高重做日志大小的写入繁重的工作负载，可以为写入提供更多空间，而无需执行频繁的检查点刷新活动并节省磁盘 I/O，从而提高写入性能。

MySQL 基础架构

下图是 MySQL 的一个简要架构图，从下图可以很清晰的看到客户端的一条 SQL 语句在 MySQL 内部是如何执行的。

从上图可以看出， MySQL 主要由下面几部分构成：

Server 层：
1. 连接器： 身份认证和权限验证（登录 MySQL 时）。
2. 查询缓存： 执行查询语句的时候，会先查询缓存（~~MySQL 8.0 版本后移除，因为这个功能不太实用~~）。命中缓存，则直接返回。
3. 分析器（解析器）： 没有命中缓存的话，SQL 语句就会经过分析器（词法分析、语法分析）。就是要先分析 SQL 语句要干嘛，再检查 SQL 语句语法是否正确。
4. 优化器： 按照 MySQL 认为最优的方案去执行。索引选择。
5. 执行器： 执行语句，操作引擎，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
引擎层：
- 存储引擎 ：主要负责数据的存储和读取，提供读写接口。采用的是插件式架构，支持 MyISAM、InnoDB、Memory 等多种存储引擎。
- 同时还有一个日志模块（bin log，所有执行引擎都可共用），redo log 只有 InnoDB 有。

SQL语句的执行过程

执行流程：

查询语句：连接器（权限校验） => 查询缓存（如果命中缓存、直接返回）=> 分析器 => 优化器 => 执行器 => 权限校验 => 存储引擎；
1. 从磁盘文件加载缓存数据到 Buffer Pool 缓冲池；
2. 将数据旧值写入 undo log 日志，便于回滚；
3. 在 Buffer Pool 中更新内存数据；
更新语句：分析器 => 权限校验 => 执行器 => 存储引擎 => redo log（prepare 状态） => bin log => redo log（commit状态）。
1. 多了 redo log，先写 redo log 日志；
2. 再将 redo log 刷入磁盘文件；
3. ~~只有 commit 才会提交、数据页刷盘。~~

InnoDB 查询时，存储引擎内的架构及执行过程：

为什么不直接写数据文件而是写 redo log？

为什么不直接数据页刷盘，而是redo log刷盘呢？

直接用数据页刷盘：
1. 耗时：数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几Byte数据，没必要把完整的数据页刷盘。
2. 性能差：而且数据页刷盘是随机写，一个数据页对应的位置可能在硬盘文件的随机位置，所以性能很差。
如果是写redo log：
1. 数据少：一行记录可能就占几十Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值；
2. 速度快：再加上是顺序写，所以刷盘速度很快。

所以用redo log形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

为什么事务提交了但是数据没有保存

在MySQL的innodb引擎中，事务提交后，必须将数据刷盘到磁盘上，
如果在事务提交之后，没来得及刷到磁盘，就会出现事务已经提交，但是数据丢失了。

过程：commit -> log buffer -> OS cache -> fsync。

MySQL的innodb引擎，事务提交的关键是将redo log写入到Log buffer，
而后MySQL调用write写入到OS cache，
只有到最后操作系统调用fsync的时候，才会落到磁盘上。

Redis的AOF机制也面临类似的问题：

即AOF也不是立刻刷盘，而是写入到了OS cache，等到缓冲区填满，或者Redis决定刷盘才会刷到磁盘。
而redis有三种策略控制，always 永远, everysec 每秒, no 不主动。默认情况下everysec，即有一秒钟的数据可能丢失。

（最后升华一下主题）：对于大多数要和磁盘打交道的系统来说，都会面临类似的问题，要么选择性能，要么选择强持久性。

三大日志

undo log（回滚日志）：是innodb引擎产生的，主要用于解决事务回滚和实现 MVCC。
- 在事务执行失败的，对已执行的操作进行回滚
redo log（重做日志）： InnoDB 存储引擎独有的，记录在某个数据页上做了什么修改。主要用于支持MySQL事务，MySQL会先写redo log，而后在写binlog。redo log可以保证即使数据库异常重启，数据也不会丢失。用于数据库崩溃恢复。保证了事务的持久性（ACID）。
binlog（归档日志）：是物理日志，记录写入性的操作（在某个数据页上做了哪些修改）。用于数据备份和恢复、主从复制（读写分离）。
- 分成基于语句、基于行、混合模式三种复制模式。

两阶段提交：因为redo log生成到binlog写入之间有一个时间差，所以为了保证两者的一致性，MySQL引入了两阶段提交:

Prepare阶段，写入redo log；
Commit阶段，写入binlog，提交事务；

刷盘时机：

binlog 刷盘：可以通过sync_binlog参数来控制。0-系统自由判断，1-commit刷盘，N-每N个事务刷盘。
redo log刷盘：可以通过参数innodb_flush_log_at_trx_commit控制。0-写入log buffer，每秒刷新到盘；1-每次提交；2-写入到OS cache，每秒刷盘。

数据备份与还原

热备（Hot Backup）
冷备（Cold Backup）
温备（Warm Backup）

存储引擎

MySQL 核心在于存储引擎。

存储引擎：如何存储数据并建立索引，更新、查询数据等技术的实现方法。

MyISAM、InnoDB 区别

定义

MyISAM 引擎设计简单，数据以紧密格式存储。
InnoDB 引擎是 MySQL 默认的事务型存储引擎。

行级锁

MyISAM 只支持表级锁（对整张表加锁，不利于并发写）。~~读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，被称为并发插入。~~
InnoDB 支持行级锁和表级锁（高并发下性能更好），默认为行级锁。~~支持 MVCC。~~

事务、MVCC

MyISAM 引擎不支持事务。
InnoDB 引擎支持事务、MVCC：有提交（commit）、回滚（rollback）事务、~~崩溃恢复~~能力。
- 实现了四个标准的隔离级别，默认隔离级别是可重复读（REPEATABLE-READ），可以解决幻读问题发生的（基于 MVCC 多版本并发控制和 Next-Key Lock）。
- MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。
- undo log（回滚日志）：发生异常时，对已执行的操作进行回滚。用于实现 MVCC。
- 应用场景：用于计费或财务系统等对数据准确性要求比较高的。

崩溃恢复

MyISAM 引擎不支持。
InnoDB 引擎支持数据库异常崩溃后的安全恢复。数据库重启时会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。
- redo log（重做日志）： InnoDB 存储引擎独有的，记录在某个数据页上做了什么修改。用于数据库崩溃恢复。保证了事务的持久性（ACID）。

备份

MyISAM 引擎不支持。要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
InnoDB 引擎支持真正的在线热备份。
- binlog（归档日志）：是物理日志，记录”在某个数据页上做了哪些修改”。用于数据备份、主从复制（读写分离）。

外键

MyISAM 引擎不支持，而 InnoDB 引擎支持外键。但通常情况下，不建议（在实际生产项目中）使用外键，在业务代码中进行约束即可！

外键对于维护数据一致性非常有帮助，但是对性能有一定的损耗。

索引实现

二者都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样。

索引的存储方式及类型

MyISAM 引擎中，索引文件和数据文件是分离的，需要根据索引去查找数据。
InnoDB 引擎中，索引文件和数据文件是一起存放的。B+ 树的所有节点都会存储索引（key），每个非叶子节点只存储索引，另外，叶子节点还存储索引和对应的行数据。
- 支持聚集索引（聚簇索引），在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

MyISAM 和 InnoDB 如何选择？

InnoDB 引擎是 MySQL 默认的事务型存储引擎。
- 一般情况下都选择 InnoDB：支持行级锁和表级锁，事务、MVCC，崩溃恢复、在线热备份，外键，聚集索引等。
- 某些情况下需要特殊特性时，才考虑使用其它存储引擎。
MyISAM 引擎设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作。
1. 选择密集型的表。最突出的优点是在筛选大量数据时非常迅速。
2. 插入密集型的表。批量插入速度快。并发插入特性允许同时选择和插入数据。如：管理邮件数据、日志记录系统（处理用户的登录日志、操作日志、Web服务器日志）等。
3. MyISAM 引擎支持压缩表。

事务

Transaction。事务的概念起源于数据库，目前，已经成为一个比较广泛的概念。

事务：是逻辑上的一组操作，要么全都执行，要么全不执行。

数据库事务：多个数据库操作（即 SQL 语句）组成了一个逻辑上的整体，满足 ACID 四大特性。

A：多条 SQL 语句要么全部执行成功，要么全部不执行。
在MySQL中，innoDB 引擎支持事务，MyISAM不支持事务。

例如，经典的 AB 间转钱。A 转出钱与 B 收到钱，这两个操作必须都成功、或者都失败。

# 开始事务，显式事务
BEGIN; 或 START TRANSACTION;

# 多条 SQL 语句
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

# 有一条SQL执行失败、则回滚事务
ROLLBACK;

事务的四大特性（ACID）

A 原子性、I 隔离性、D 持久性决定了 C 一致性。

关系型数据库（例如：MySQL、SQL Server、Oracle 等）事务都有 ACID 特性：

A 原子性（Atomicity）：事务是最小的原子单位，不可再分割。事务中的所有操作要么全部执行成功，要么全部执行失败。
- 事务内的所有 SQL 作为一个原子单元执行。
- ~~MySQL InnoDB 引擎用 undo log（回滚日志）保证。~~
C 一致性（Consistency）：事务执行前后，状态（总数据）保持一致。如，不管 A 、B 间如何转账，事务结束后两账户的总额与事务执行前一致，都是 5000。
I 隔离性（Isolation）：当（多个用户）并发访问数据库时，多个并发事务间要相互隔离。一个事务的执行不会影响另外一个事务。如，操作同一张表时，数据库为每个用户开启的事务，不能被其他事务的操作所干扰。
- 即，要达到这么一种效果：对于任意两个并发的事务 T1 和 T2，在事务 T1 看来，T2 要么在 T1 开始前就已结束，要么在T1结束后才开始，这样每个事务都感觉不到有其他事务在并发地执行。
- 数据库提供了多种隔离级别。
- MySQL InnoDB 存储引擎通过锁机制、MVCC 等保证。
D 持久性（Durability）：一个事务被提交后，对数据库中数据的改变是持久的，即使数据库发生故障也不会丢失提交事务的操作。
- MySQL InnoDB 引擎用 redo log（重做日志）保证。

并发事务的三大一致性问题

副作用。对应隔离性。

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

四种问题按照严重性排序：脏写 > 脏读 > 不可重复读 > 幻读。

脏写、丢失更新（Lost to modify）：当前事务的更新（修改）覆盖了其它事务之前（未提交的）的更新。第二个事务的修改结果被丢失，因此称为丢失修改。
- 脏写这个问题太严重了，事务的任何隔离级别都一定不允许发生，对访问的数据加锁即可。
脏读（Dirty read）：一个事务读取到其它事务更新后（已修改）但未提交到数据库的数据。如果其它事务回滚该更新，则当前事务读到的（未提交的数据）就是脏数据。
不可重复读（Unrepeatable read）：在一个事务中，多次读同一数据，两次读取的数据可能不一致。原因：在事务还未结束时，其它事务就修改了此数据。
幻读（Phantom read）：在一个事务执行过程中，第一次未查询到某条记录，在此之后，其它事务插入/删除、并提交了此条记录，再次按相同条件可查询到。即好像发生幻觉一样，与不可重复读类似。

不可重复读和幻读有什么区别呢？

不可重复读的重点：是内容修改或者记录减少，比如多次读取一条记录发现其中某些记录的值被修改；
幻读的重点：在于记录新增，比如多次执行同一条 SQL 查询语句时，发现查到的记录增加了。

幻读其实可以看作是不可重复读的一种特殊情况，单独区分幻读的原因主要是二者解决方案不一样。

解决不可重复读：执行 delete 和 update 操作的时候，可以直接对记录加锁（Record Lock），保证事务安全。
解决幻读：而执行 insert 操作时，由于记录锁（Record Lock ）只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁（Gap Lock）。记录锁 + 间隙锁 组成了 Next-Key Lock 临键锁，来保证不出现幻读。

详细见InnoDB 的 3 种行锁定方式。

事务隔离机制、级别

MySQL是怎么保证数据不丢失的？

见 SSM 框架中 Spring 事务管理。

对应 I 隔离性（Isolation）：当（多个用户）并发访问数据库时，多个并发事务间要相互隔离。如，操作同一张表时，数据库为每个用户开启的事务，不能被其他事务的操作所干扰。

通过事务隔离机制保证数据不丢失：

并发处理带来的问题中，更新丢失可完全避免，由应用对数据加锁即可。
脏读、不可重复读、幻读，其实都是数据库的一致性问题，必须由一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大。

READ_UNCOMMITTED（未提交读）：最低，事务可以读取另一个事务未提交的数据。
- 解决了丢失更新，问题有：脏读、不可重复读、幻读。
READ_COMMITTED（已提交读）：事务只能读取到另一个已经提交的数据。
- 解决了丢失更新、脏读，问题有：不可重复读、幻读。
REPEATABLE_READ（可重复读）：事务执行过程中，（对同一字段的多次读取）查询结果都是一致的。（除非数据是被事务本身所修改）。
- MySQL InnoDB 存储引擎默认支持的隔离级别。可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;
- 解决了丢失更新、脏读、不可重复读，问题有：幻读。
SERIALIZABLE（可序列化）：最高，完全服从 ACID 四大特性。串行执行，事务依次逐个执行，事务间完全不可能产生干扰。读写都会相互阻塞，效率会大大下降。
- 解决了丢失更新、脏读、不可重复读、幻读全部一致性问题。

1	`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;`

MySQL 的隔离级别是基于锁实现的吗？

MySQL 的隔离级别基于锁和 MVCC 机制共同实现的。

SERIALIZABLE 隔离级别，是通过锁来实现的。
除了 SERIALIZABLE，其他的隔离级别都是基于 MVCC 实现。不过，可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。

innoDB 引擎利用了 Read View 来支持提交读和重复读。

已提交读：默认读取是不加锁的，只有修改才会加锁。简单来说，是每次查询都生成一个新的Read View，所以永远都能看到已经提交的事务。
可重复读：是在第一次查询生成Read View之后，后面的查询都是使用这个Read View。

Repeatable Read 有没有解决幻读

先说答案：解决了（在官方文档的暧昧中），但是又没有完全解决（在头脑清醒的开发者眼中）。

官方文档中表述 InnoDB 用临键锁 (next-key lock) 解决了幻读的问题，临键锁工作在 RR 隔离级别下，设置隔离级别为 RC 会导致 GAP 锁失效，继而导致没有临键锁。这是 InnoDB 自我定义其 RC 存在幻读，而 RR 可以避免幻读的描述。
InnoDB 作为一个优等生，在隔离级别定义要求 RR 不需要避免幻读的情况下，宣称自己实现了这个功能。但实际上受到了限制：
- 对于仅包含连续相同快照读语句的事务，MVCC 避免了幻读，但是这种场景临键锁没有用武之地，而官方文档重点强调是临键锁的实际避免了幻读，所以 InnoDB 肯定觉得自己做到了更多。
- 对于仅包含连续相同当前读语句的事务，第一个当前读会加临键锁，会阻塞别的事物的修改，也避免了幻读。
- 但是对于快照都和当前读语句交错的事务，第一个快照读后其它事务仍可以修改并提交内容，当前事务的后续当前读就会读到其他事务带来的变更。导致可以造出一些印证 InnoDB 没有解决幻读问题的例子。

事务传播行为

见 SSM 框架中 Spring 事务管理。

Spring 事务的传播行为：当多个事务同时存在时，Spring 如何处理这些事务的行为。

在enum Propagation 中，枚举取值有：

REQUIRED：支持当前事务，如果当前没有事务，新建一个事务。最常见，默认的方式；
SUPPORTS：支持当前事务，如果当前没有事务，以非事务方式执行；事务将不会发生回滚；
'MANDATORY：支持当前事务，如果当前没有事务，抛出异常；
REQUIRES_NEW：新建事务，如果当前存在事务，把当前事务挂起；
NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，把当前事务挂起；事务将不会发生回滚；
NEVER：以非事务方式执行，如果当前存在事务，抛出异常；事务将不会发生回滚；
~~NESTED~~：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，进行与 REQUIRED类似的操作。

2、5、6不能回滚。

锁机制

而锁机制，对于 innoDB 来说，有多个维度：

从关系型数据库来说：MySQL 中的是悲观锁；
从锁粒度来说：有行锁、表锁、页锁；
从独占性来说：有排他锁和共享锁；
从意向来说：有排他意向锁和共享意向锁；
从场景来说：还可以分为记录锁，间隙锁和临键锁；

悲观锁、乐观锁

详细见 Java 多线程文档中的线程安全部分。

悲观锁：每次获取数据时，都担心数据被修改，因此加独占锁，确保自己使用的过程中数据不会被别人修改，使用完成后解锁。期间对该数据读写的其他线程都会等待/阻塞。

乐观锁：对于并发操作产生的线程安全问题持乐观状态，认为竞争不总是会发生，不需持有锁；每次获取数据时，都不担心数据被修改，因此读操作前不加锁，写操作时才判断数据在此期间是否被其他线程修改。

关系型数据库中的是悲观锁：

表锁、行锁
读锁（共享锁、S 锁）、写锁（排它锁、X 锁）

粒度：表锁、行锁、页锁

表锁和行锁对比：

表级锁（table-level locking）：对当前操作的整张表加锁。对表中的所有行记录加锁。
1. 是针对非索引字段加的锁。
2. 锁定粒度最大，触发锁冲突的概率最高，并发度最低。
3. 实现简单，资源消耗也比较少，加锁快，不会出现死锁。
4. MyISAM 和 InnoDB 引擎都支持表级锁。
行级锁、记录锁（row-level locking）：只针对当前操作的行记录进行加锁。
1. 是针对索引字段加的锁。当执行 UPDATE、DELETE 语句时，如果 WHERE条件中字段没有命中唯一索引或者索引失效的话，就会退化为表锁、导致全表扫描、对表中的所有行记录进行加锁。
2. 加锁粒度最小，并发度高，能大大减少数据库操作的冲突。
3. 但加锁的开销也最大，加锁慢，会出现死锁。
4. InnoDB 不光支持表锁，还支持行锁。
页锁（Page Lock）：针对数据库表中的数据页进行加锁。通常用于处理较大数据块的并发访问问题。
- 间隙锁不是页锁。
- 会出现死锁。

-- 锁定
LOCK TABLES tbl_name [AS alias]
-- 解锁
UNLOCK TABLES

共享锁和排他锁

不论是表级锁还是行级锁，都存在共享锁和排他锁这两类：

读锁、共享锁（Share Lock，S 锁） ：别的事务可以读（锁兼容），但是不可以写。事务在读取记录时获取共享锁，允许多个事务同时读。
写锁、排他锁/独占锁（Exclusive Lock，X 锁） ：指别的事务既不可以读也不可以写。事务在修改记录时获取排他锁，不允许多个事务同时读、写。
- 如果一个记录已经被加了排他锁，那其他事务不能再加任何类型的锁（锁不兼容）。

排他锁与任何的锁都不兼容（有冲突），共享锁仅和共享锁兼容。

由于 MVCC 的存在，对于一般的 SELECT 语句，InnoDB 不会加任何锁。不过，可以通过以下语句显式加共享锁或排他锁。

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁（表锁）

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？

一行一行遍历肯定是不行，性能太差。

意向锁：用来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。

意向锁是由数据存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行所在在数据表的对应意向锁。

排它锁和其它三种都互斥；（X 排斥一切）
意向排它锁和意向锁兼容；（IX 兼容 I）意向锁之间是互相兼容的。
共享锁和（共享锁、意向共享锁）兼容；（S 兼容 S）
共享锁和意向排它锁互斥；（S 互斥 IX）这里指的是表级别的共享锁和排他锁，意向锁不会与行级的共享锁和排他锁互斥。

场景：记录锁、间隙锁、临键锁

MySQL InnoDB 支持三种行锁定方式：

记录锁（Record Lock）：对单个行记录家加锁，也是行锁。用于解决不可重复读：执行 delete 和 update 操作时，可以直接对记录加锁，保证事务安全。只能锁住已经存在的记录。
间隙锁（Gap Lock）：对记录之间的间隔加锁。防止其他事务在间隙（两个索引键之间的空白区域）中插入新记录。而执行 insert 操作时，记录锁只能锁住已经存在的记录，为了避免插入新记录，需要依赖间隙锁。
- 通常用于防止幻读问题，即在一个事务中多次执行同一查询时，查询的结果集合发生了变化。
临键锁（Next-key Lock）：由 记录锁 + 间隙锁 组成，锁定一个范围，包含记录本身。用于解决不可重复读 + 幻读。

事务隔离级别中用于解决不可重复读和幻读。

MVCC

MVCC（Multi-Version Concurrency Control）多版本并发控制：是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现读已提交和可重复读这两种隔离级别。

而读未提交总是读取最新的数据行，要求很低，无需使用 MVCC。
可串行化需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

数据库并发场景

读-读：不存在任何问题，也不需要并发控制；共享锁？
写-写：有线程安全问题，可能会存在丢失更新问题，比如第一类更新丢失，第二类更新丢失。通过加锁（悲观锁、乐观锁）解决。
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读；

MVCC解决并发哪些问题？优点

MVCC机制具有以下优点：

提高数据库并发性能：用更好的方式去处理并发读-写冲突，即使有读写冲突时，也能做到不加锁。可以做到读操作不会阻塞写操作，同时写操作也不会阻塞读操作。
降低死锁风险：由于无需使用显式锁来进行并发控制，MVCC可以降低死锁的风险。
用于实现读已提交和可重复读这两种隔离级别。解决脏读、不可重复读、幻读等事务隔离问题，但不能解决 写-写丢失更新问题。

基本思路

MVCC 用来解决读-写冲突的无锁并发控制，即为事务分配单向增长的时间戳。为每个数据修改保存一个版本，版本与事务时间戳相关联。

读操作只读取该事务开始前的数据库快照。

因此有了下面提高并发性能的组合拳：

MVCC + 悲观锁：MVCC 解决读写冲突，悲观锁解决写写冲突；
MVCC + 乐观锁：MVCC 解决读写冲突，乐观锁解决写写冲突；

加锁能解决多个事务同时执行时出现的并发一致性问题。

在实际场景中读操作往往多于写操作，因此又引入了读写锁来避免不必要的加锁操作，例如读和读没有互斥关系。
读写锁中读和写操作仍然是互斥的，而 MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系，这一点和 CopyOnWrite 类似。

脏读和不可重复读最根本的原因是：事务读取到其它事务未提交的修改。

在事务进行读取操作时，为了解决这个，MVCC 规定只能读取已经提交的快照。当然一个事务可以读取自身未提交的快照，这不算是脏读。

MVCC 的实现原理

innodb引擎主要是通过undo log 和事务版本号来实现多版本，利用锁机制来实现并发控制。

实现原理主要是（多版本）版本链、undo日志、Read View 来实现的。

快照：即记录的历史版本，每行记录可能存在多个历史版本（多版本技术）。在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。
MVCC 的多版本：指的是（数据库中某一条记录同时存在）多个版本的快照，快照存储在 Undo 日志中。
undo log（回滚日志）：简单来说可以认为存储了历史版本数据。通过回滚指针（ROLL_PTR）把一个数据行的所有快照连接起来。发生异常时，对已执行的操作进行回滚。用于实现 MVCC。一般用于事务回滚。
Read View：MVCC 维护了一个 ReadView 结构。在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针找到下一个快照。
- 读已提交：每次查询生成 Read View；
- 可重复读：第一次查询生成 Read View。

当发起查询的时候，MySQL 依据隔离级别的设置生成Read View，来判断当前查询可以读取哪个版本的数据。例如，在已提交读的隔离级别下，可以从undo log中读取到已经提交的最新数据，而不会读取到当前正在修改尚未提交的事务的数据。

当前读和快照读

InnoDB 的默认隔离级别 RR（可重复读）可以解决幻读问题，主要有下面两种情况：

当前读：使用 Next-Key Lock 临键锁来保证不出现幻读。
快照读：由 MVCC 机制来保证不出现幻读。

当前读

当前读（一致性锁定读）：就是给行记录加共享锁或独占锁。

在进行 SELECT 操作时，可以（通过 SQL 语句）强制指定进行加锁操作。
其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要加锁，从而读取最新的数据。

可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE;
# 对读的记录加一个X锁
SELECT...FOR UPDATE;

# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

快照读

快照读（一致性非锁定读）：

MVCC 的 SELECT 语句操作的是快照中的数据，不需要进行加锁操作。除手动强制指定加 S 锁、X 锁外的 SELECT 操作都是用的快照读。~~即，除当前读中的两类强制指定进行加锁的SELECT 外的。~~
如果读的记录正在执行 UPDATE/DELETE 操作，读操作不会因此去等待记录上 X 锁的释放，而是会去读行的一个快照。

1	`SELECT * FROM table ...;`

只有在事务隔离级别 RC（读已提交）和 RR（可重复读）下，InnoDB 才会使用快照读：

读已提交：对于快照数据，快照读总是读取被锁定行的最新一份快照数据。
可重复读：对于快照数据，快照读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高、且追求极致性能的业务场景。

目录

MySQL

简介

优势

安装

下载MySQL

版本号

解压

设置环境变量

配置文件

以管理员身份运行cmd

cd到安装目录\bin下]

安装

启动MySQL

登陆

新建测试数据库

用 Navicat 导出导入 MySQL

使用

问题

Too many connections

windows下重启mysql的方法

mysql 服务正在启动或停止中

数据类型

MySQL 数据类型、及对应的 Java 包装类、JDBC 类型

三大类数据类型

char VS varchar

字符集和字符编码

SQL 语句

SQL 语法注意事项

SQL 语言分类

DDL 数据库/表结构定义

数据库操作

表操作

常用语句

CHARSET、COLLATE

NULL、NOT NULL、DEFAULT 约束

其它列属性/列约束

创建数据库表、定义表结构

DQL SELECT 查询语句

子句执行顺序

FROM 子句

AS 别名

多表查询/笛卡尔查询

JOIN ON 连接查询

WHERE 子句

子查询

GROUP BY 分组子句

HAVING 分组条件子句

函数

SELECT 结果子句

DISTINCT

UNION 合并结果

ORDER BY 排序

LIMIT 限制结果数量

DML 数据操纵语句

插入记录

删除记录

修改记录

DQL 查询记录

DCL 数据控制语句

字符集相关

修改时区

权限相关

TCL 事务控制语言

drop、 truncate、 delete 区别

SQL查重和去重

删除表前1W行数据

索引、视图、触发器、存储过程

索引

索引的数据结构分类

Hash 表、哈希索引

B-Tree/多路平衡查找树

索引形态分类

聚集索引、非聚集索引

聚集索引

非聚集索引

主键索引

为什么使用自增主键

添加索引的 SQL 语句

建立和使用索引的时机

`cd`到安装目录`\bin`下]

`char VS varchar`