MySQL 思维导图模板_ProcessOn思维导图、流程图

MySQL常用数据类型

日期和时间类型

date：3字节，日期，格式：2014-09-18

time：3字节，时间，格式：08:42:30

datetime：8字节，日期时间，格式：2014-09-18 08:42:30，范围为1000-01-01 00:00:00/9999-12-31 23:59:59

timestamp：4字节，自动存储记录修改的时间，时间戳，范围为1970-01-01 00:00:00/2038

year：1字节，年份

数值类型

整型（有符号或者无符号）

tinyint：1字节，范围（-128 ~ 127）

smallint：2字节，范围（-32768 ~ 32767）

mediumint：3字节，范围（-8388608 ~ 8388607）

int或integer：4字节，范围（-2147483648 ~ 2147483647）

bigint：8字节，范围（+|-9.22 * 10的18次方）

注意：上面定义的都是有符号的

加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就要翻翻了。例如tinyint unsigned的取值范围为0 ~ 255

表示数量的字段建议加上unsigned关键字，例如库存、余额等，如果程序计算库存小于0了，在UPDATE或者是INSERT的时候直接报错，保证库存不超卖

数据范围

浮点型

float(m, d)：4字节，单精度浮点型，m总个数，d小数位

double(m, d)：8字节，双精度浮点型，m总个数，d小数位

decimal(m, d)：m + 2字节，decimal是存储为字符串的浮点数，numeric和decimal类似，可以近似等价于decimal

DECIMAL和NUMERIC 类型的存储精确的数值数据。这些类型用于保持精确精度很重要的情况，例如货币数据

在MySQL中建立了一个表，有一列为float(5, 3)，那么该列的最大值就是99.999，做了以下试验

插入123.45678，最后查询得到的结果为99.999。高版本MySQL直接报错

插入123.456，最后查询结果为99.999。高版本MySQL直接报错

插入12.34567，最后查询结果为12.346

在使用浮点型的时候，还是要注意陷阱的，要以插入数据库中的实际结果为准

字符串数据类型

char(n)：固定长度，最多255个字符

varchar(n)：可变长度，最多65535个字符

tinytext：可变长度，最多255个字符

text：可变长度，最多65535个字符

mediumtext：可变长度，最多2的24次方-1个字符

longtext：可变长度，最多2的32次方-1个字符

注意

char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候（UTF8）意味着可以插入n个中文，但是实际会占用n * 3个字节

同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n

超过char和varchar的n设置后，直接报错

对于char类型的字段不要设置的太大，如果不确定字段是否有值，建议设置成varchar(255)

基本SQL语句

DCL（数据库控制语言）

数据控制语句，用于控制不同数据段直接的许可和访问级别的语句

这些语句定义了数据库、表、字段、用户的访问权限和安全级别

一般是由DBA进行设置

创建用户

给用户授权

撤销授权

查看权限

删除用户

DDL（Data Definition Language）

数据定义语言，用来定义数据库对象：库、表、列等。功能：创建、删除、修改库和表结构

数据库操作

创建数据库

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

查询数据库

查询所有数据库

SHOW DATABASES

查询数据库建表时的sql

SHOW CREATE DATABASE db_name;

选择数据库

USE db_name;

删除数据库

DROP DATABASE db_name;

修改数据库的字符编码和排序方式

ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

命令行设置之后操作的编码格式

SET NAMES UTF8

数据库表操作

创建表

SQL : CREATE TABLE tb_name (字段名、类型、长度、约束、默认、注释)

约束

主键

PRIMARY KEY

自增

AUTO_INCREMENT

唯一

UNIQUE

唯一约束，取值不允许重复，可以为NULL

并不是指只有一行字段为NULL，可以有多个为NULL

非空

NOT NULL

非负

UNSIGNED

只能用于数值

对于库存或者是余额意义的字段，建议加上该约束，如果程序出现错误新增或者修改后的值为负数，直接报错

外键

FOREIGN KEY

现在很少使用物理外键，一般使用逻辑外键

可以在开发环境或者测试环境加上外键，在正式环境去除外键

默认

DEFAULT

注释

COMMENT

表字段索引

唯一索引

添加

创建索引

CREATE UNIQUE INDEX index_name(field_name);

表字段修改

ALTER TABLE table_name ADD UNIQUE INDEX index_name(field_name);

删除

DROP INDEX index_name ON table_name

普通索引

添加

表字段修改

ALTER TABLE table_name ADD INDEX index_name(field_name);

创建索引

CREATE INDEX index_name ON table_name(field_name);

删除

DROP INDEX index_name ON table_name

主键

添加

ALTER TABLE table_name ADD PRIMARY KEY (field_name)

删除

ALTER TABLE table_name DROP PRIMARY KEY;

联合索引

添加

ALTER TABLE table_name ADD index_name (field_name1, field_name2);

删除

DROP INDEX index_name ON table_name

修改表

表字段的增删改查

字段添加

ALTER TABLE tb_name ADD address VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';

ALERT TABLE tb_name ADD 添加字段字段类型  非空约束  默认  注释

字段类型修改

ALTER TABLE tb_name MODIFY address VARCHAR (50) NOT NULL DEFAULT '' COMMENT '地址';

ALERT TABLE tb_name MODIFY 字段名称新的字段类型  非负  非空  默认注释

字段名称类型修改

ALTER TABLE tb_name CHANGE address addr VARCHAR (100) NOT NULL DEFAULT '' COMMENT '地址';

ALTER TABLE tb_name CHANGE 旧的字段名  新的字段名  新的类型  约束  默认  注释

字段类型查询

DESC tb_name;

字段删除

ALTER TABLE tb_name DROP addr;

ALTER TABLE 表名 DROP 删除的字段名

表修改

表名修改

ALTER TABLE tb_name RENAME TO new_tb_name;

ALTER TABLE 旧表名 RENAME TO 新表名

引擎修改

ALTER TABLE tb_name ENGINE = InnoDB;

ALTER TABLE 表名 ENGINE = 新的引擎名称

删除表

DROP TABLE tb_name;

查询表

查询所有表

SHOW TABLES;

查询建表时的sql

SHOW CREATE TABLE tb_name;

DML（Data Manipulation Language）（重点）

数据操作语言，用来定义数据库记录：增、删、改表记录

增

添加单条

INSERT INTO tb_name(`field1`,`field2`,....) VALUES('value1','value2',.....);

添加多条

INSERT INTO tb_name(`field1`,`field2`,....) VALUES('value1','value2',.....), ('value1','value2',.....),('value1','value2',.....),....;

删

sql

DELETE FROM tb_name WHERE ...

注意

删除时必须加WHERE条件

改

sql

UPDATE tb_name SET field1 = value1, field2 = value2, ..... WHERE  ....

注意

修改时必须加WHERE条件

DQL（Data Query Language）（重点）

数据查询语言，用来查询记录，查询数据并不会改变原有的数据

基础的查询

SELECT * FROM tb_name

WHERE子句

比较运算符

大于、小于、等于、不等于、大于等于、小于等于

SELECT * FROM tb_name WHERE user_id >10;

逻辑运算符

逻辑运算符是用来拼接其他条件的。用and或者or来连接两个条件，如果用or来连接的时候必须使用小括号

SELECT * FROM tb_name WHERE user_id > 10 AND sex = '男'

LIKE模糊查询

通配符

%（百分号）匹配零个或者多个任意字符

_（下划线）匹配一个任意字符

sql

SELECT * FROM tb_name WHERE username LIKE '张%';查找username开头是张的数据

SELECT * FROM tb_name WHERE username LIKE '%张%';查询username中含有张的数据

SELECT * FROM tb_name WHERE username LIKE '%张';查询username字段的数据以张结尾的

SELECT   *  FROM  tb_name WHERE username LIKE '张_';查询username以张开头后边有一个字符的数据

IN字段指定多个值查询

IN (value1, value2, value3, ....)

SELECT   *   FROM   tb_name  WHERE   user_id  IN (1, 3, 5, 7, 9, 11);查询user_id是1, 3, 5, 7, 9, 11的所有数据

BETWEEN AND 区间查询

field BETWEEN value1 AND value2

字段的值大于等于value1同时小于等于value2

SELECT * FROM user WHERE user_id  BETWEEN  2 AND 9;查询user表中user_id大于等于2小于等于9的所有值

GROUP BY分组查询

聚合函数

COUNT(field)：获取符合条件出现的非NULL值的次数

COUNT(*)：统计所有行

SUM(field)：获取所有符合条件的数据的总和

AVG(field)，取平均值

MAX(field)，取最大值

MIN(field)，取最小值

GROUP_CONCAT(field)，可以将分组的字符串以", "连接起来

SELECT sex, COUNT(*) count FROM class GROUP BY sex;获取class表中男生和女生的数量

HAVING对聚合值进行过滤

对聚合值或者是字段进行过滤

WHERE不能对聚合值进行过滤

ORDER BY查询排序

查询顺序

ORDER BY field DESC;降序查询

ORDER BY field ASC;升序查询

SELECT  *  FROM  tb_name  ORDER BY   id  DESC; 查询tb_name表中所有数据，按id的降序来查找

ORDER BY是否使用索引的严格要求

通过有序索引顺序扫描直接返回有序数据，通过explain分析显示Using Index，不需要额外的排序，操作效率比较高

索引的顺序和ORDER BY子句的顺序完全一致

索引中所有列的方向（升序、降序）和ORDER BY子句完全一致

当多表连接查询时ORDER BY中的字段必须在关联表中的第一张表中

通过对返回数据进行排序，也就是FileSort排序，所有不是通过索引直接返回排序结果的都叫FileSort排序

FileSort是通过相应的排序算法将取得的数据在sort_buffer_size系统变量设置的内存排序中进行排序

如果内存装载不下，就会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集

可以理解为归并排序

LIMIT查询结果截取

参数

LIMIT后边可以跟两个参数，如果只写一个表示从零开始查询指定长度，如果两个参数就是从第一个参数开始查询查询长度是第二个参数的值，俩个参数必须是整形

SELECT * FROM tb_name LIMIT 5;查询tb_name表中的所有数据，只要前边的5条数据

SELECT * FROM tb_name LIMIT 5, 5;查询tb_name中所有的数据，返回的结果是从第五条开始截取五条数据

分页查询一般会全表扫描，优化的目的应尽可能减少扫描； 第一种思路：在索引上完成排序分页的操作，最后根据主键关联回原表查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的id，覆盖索引效率高些 第二中思路：limit m,n 转换为 n 之前分页查询是传pageNo页码, pageSize分页数量， 当前页的最后一行对应的id即last_row_id，以及pageSize，这样先根据条件过滤掉last_row_id之前的数据，然后再去n挑记录,此种方式只能用于排序字段不重复唯一的列，如果用于重复的列，那么分页数据将不准确

JOIN连接查询

JOIN连接查询总共有7种（内连接、左连接（全A）、右连接（全B）、左连接（只A）、右连接（只B）、全外连接、交叉外连接）

内连接：SELECT <select...list> FROM TableA A INNER JOIN TableB B ON A.Key=B.Key;

左连接（全A）：SELECT <select...list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key;

右连接（全B）：SELECT <select...list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;

左连接（只A）：SELECT <select…list> FROM TableA a LEFT JOIN TableB b ON A.Key=B.Key WHERE B.Key IS NULL;

右连接（只B）：SELECT <select…list> FROM TableA a RIGHT JOIN TableB b ON A.Key=B.Key WHERE A.Key IS NULL;

全外连接：SELECT <select…list> FROM TableA a FULL OUTER JOIN TableB b ON A.Key=B.Key;

由于MySQL不支持FULL JOIN这种语法（在orcale可行），所以使用UNION关键字拼接左连接（全A）和右连接（全B）结果并去重来达到效果

SELECT <select...list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Key; UNION SELECT <select...list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Key;

交叉外连接

同理，使用UNION关键字拼接左连接（只A）和右连接（只B）达到效果

SELECT <select…list> FROM TableA a LEFT JOIN TableB b ON A.Key=B.Key WHERE B.Key IS NULL; UNION SELECT <select…list> FROM TableA a RIGHT JOIN TableB b ON A.Key=B.Key WHERE A.Key IS NULL;

JOIN时使用ON和WHERE过滤的区别

INNER JOIN

如果是内连接将过滤条件写在ON和WHERE的效果一样

LEFT JOIN

RIGHT JOIN

FULL JOIN

事务

关键词

BEGIN：开启事务

ROLLBACK：事务回滚

COMMIT：事务提交

必备条件

表的存储引擎为InnoDB

SQL语句的执行顺序（重点）

(8) SELECT  (9) DISTINCT  (10)  <TOP_specification> <select_list> （窗口函数）   (1)  FROM <left_table>     (3) <join_type> JOIN <right_table>    (2) ON <join_condition> （表连接条件）   (4) WHERE <where_condition> （一级过滤，WHERE后面不能使用别名，不能过滤聚合函数）   (5) GROUP BY <group_by_list> （识别SELECT中的别名，这里只能识别简单的SELECT字段的别名，不包含窗口函数的别名，后面的语句中都可以使用）   (6) WITH {CUBE | ROLLUP} (对分组后的数据进行汇总，对分组的字段取别名)   (7) HAVING <having_condition> （二级过滤，过滤聚合函数的值，以及普通字段） (11) ORDER BY <order_by_list> （对数据集进行排序，可以识别窗口函数的别名） (12) LIMIT OFFSET NUMBER   (分页功能)

了解SQL语句的执行顺序对于书写SQL非常重要

WHERE和ON的区别，ON为表连接条件，WHERE进行字段的一级过滤，将过滤条件能够写到ON的尽量写到ON中，减少连接的数据集

WHERE和HAVING的区别，WHERE只是对字段进行简单过滤，HAVING除了能对字段简单过滤以外，还能够对聚合值进行过滤

MySQL函数（重要）

数学函数

ABS()：绝对值

PI()：π值

RAND()：返回０到１内的随机值,可以通过提供一个参数（种子）使RAND()随机数生成器生成一个指定的值

ROUND(x, y)：返回参数x的四舍五入的有y位小数的值

MOD(x, y)：返回x / y的模（余数）

FORMAT(x, y): x为数值，也可以为字符串但是必须是纯数字, y为需要保留的小数。如果没有或以0补齐

字符串函数

CONCAT(str1, str2, ...)：将str1、str2进行拼接，如果有任意字符串的值为NULL，则返回NULL

CONCAT_WS(separator,str1,str2,…)：指定拼接的分隔符，同时忽略为NULL的字符串，如果输入的字符串全为NULL，则返回空字符串。如果输入的分隔符为NULL，则返回NULL

FIELD(str,str1,str2,str3,...)后续参数中第一个参数的索引（位置）

FIND_IN_SET(char, charlist)：假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。一个字符串列表就是一个由一些被 ‘,' 符号分开的子链组成的字符串

FORMAT(X,D[,locale]): 返回格式化为指定小数位数的数字

UCASE(str)或UPPER(str)：返回将字符串str中所有字符转变为大写后的结果

TRIM(str)：去除字符串首部和尾部的所有空格

RTRIM(str)：返回字符串str尾部的空格

LTRIM(str)：从字符串str中切掉开头的空格

LENGTH(str)：返回字符串str中的字符数

ASCII(char)：返回字符的ASCII码值

日期和时间函数（重要）

CURDATE()或CURRENT_DATE()：返回当前的日期

CURTIME()或CURRENT_TIME()：返回当前的时间

FROM_UNIXTIME(时间戳)：格式化传入的时间戳，转成日期格式

UNIX_TIMESTAMP()：获取系统当前的时间戳

NOW()：返回当前的时间的日期

本周周一和周日

周一

SUBDATE(CURRENT_DATE, IF(DATE_FORMAT(CURRENT_DATE, '%w') = 0, 7, DATE_FORMAT(CURRENT_DATE, '%w')) - 1)

周日

SUBDATE(CURRENT_DATE, IF(DATE_FORMAT(CURRENT_DATE, '%w') = 0, 7, DATE_FORMAT(CURRENT_DATE, '%w')) - 7)

本月第一天和最后一天

第一天

DATE_ADD(CURRENT_DATE, INTERVAL - DAY(CURRENT_DATE) + 1 DAY)

最后一天

LAST_DAY(CURRENT_DATE)

当天零点和当天最大时间

当天零点

DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d 00:00:00')

当天最大时间

DATE_FORMAT(CURRENT_TIMESTAMP, '%Y-%m-%d 23:59:59')

日期、日期字符串、时间戳互相转换

控制函数（重要）

IF(expr, v1, v2)：如果表达式expr成立，返回结果v1；否则，返回结果v2

IFNULL(v1, v2)：如果v1的值不为NULL，则返回v1，否则返回v2

ISNULL(expression)：判断表达式是否为 NULL，为NULL返回1也就是true，否则返回0，也就是false

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END AS 别名

简单CASE函数

枚举这个字段所有可能的值，如果没有返回default，同时取别名

如果要判断字段的值是否为NULL，只能用搜索CASE函数

CASE WHEN [expr1] THEN [result1]...ELSE [default] END AS 别名

搜索CASE函数

如果exprN表达式为真，则返回resultN，否则返回default，同时取别名

返回值类型的CASE WHEN函数两种用法的场景

简单CASE函数，适用于可以枚举col_name的值情况，然后根据col_value的值，设置另外的值。例如根据用户级别字段的值（1、2、3），设置普通用户、会员用户、VIP用户，不能判断字段的值为NULL的情况，需要判断字段是否为NULL，要使用搜索CASE函数

搜索CASE函数，适用于复杂场景，可以通过表达式进行各种判断，进而设置值或者返回SQL语句

CASE WHNE函数案例

建表语句

CREATE TABLE `tb_hotel_user` ( `customer_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '旅客id', `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名', `user_id` bigint(10) NULL DEFAULT NULL COMMENT '用户id', `check_in_time` datetime(0) NULL DEFAULT NULL COMMENT '入住时间', `check_out_time` datetime(0) NULL DEFAULT NULL COMMENT '离店时间', PRIMARY KEY (`customer_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT语句

INSERT INTO `tb_hotel_user` VALUES (1, '张三', 1, '2019-12-02 14:18:57', NULL); INSERT INTO `tb_hotel_user` VALUES (2, '刘大', 2, '2019-11-08 14:19:07', NULL); INSERT INTO `tb_hotel_user` VALUES (3, '关二', 3, '2019-10-17 14:19:21', NULL); INSERT INTO `tb_hotel_user` VALUES (4, '关二', 3, '2019-12-02 14:19:44', NULL); INSERT INTO `tb_hotel_user` VALUES (5, '赵四', 4, '2019-11-29 14:19:54', NULL);

统计2019-10-21 00:00:00~2019-12-02 23:59:59时间段内的用户并标记新老用户，user_id重复即为老用户

统计SQL

SELECT   a.user_id,   CASE     WHEN ISNULL(b.user_id) THEN '新用户'     ELSE '老用户'   END AS user_type FROM (   SELECT     DISTINCT user_id   FROM     tb_hotel_user   WHERE     check_in_time >= '2019-10-21 00:00:00' AND check_in_time <= '2019-12-02 23:59:59' ) a LEFT JOIN (   SELECT     user_id   FROM     tb_hotel_user   WHERE     check_in_time <= '2019-12-02 23:59:59'   GROUP BY     user_id   HAVING count( * ) > 1 ) b ON a.user_id = b.user_id

两种CASE WHEN函数除了返回特定的值，也可以返回SQL语句，然后在WHERE语句中进行过滤，进行一些复杂条件的判断；或者是ORDER BY语句中，进行特殊的排序

建表语句

CREATE TABLE `category` ( `category_id` tinyint NOT NULL, `name` varchar(25) COLLATE utf8mb4_general_ci NOT NULL, `last_update` timestamp NULL DEFAULT NULL, PRIMARY KEY (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

数据SQL

INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (1, 'Action', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (2, 'Animation', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (3, 'Children', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (4, 'Classics', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (5, 'Comedy', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (6, 'Documentary', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (7, 'Drama', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (8, 'Family', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (9, 'Foreign', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (10, 'Games', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (11, 'Horror', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (12, 'Music', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (13, 'New', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (14, 'Sci-Fi', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (15, 'Sports', '2006-02-14 20:46:27'); INSERT INTO `demo`.`category`(`category_id`, `name`, `last_update`) VALUES (16, 'Travel', '2006-02-14 20:46:27');

使用简单CASE WHEN函数进行过滤

SELECT * FROM category WHERE CASE category_id WHEN 1 THEN last_update = '2006-02-14 20:46:27' ELSE `name` LIKE 'A%' END

这里想要查询当category_id等于1时，last_update等于2006-02-14 20:46:27或者name以A开头的category，可以理解为两种WHERE过滤条件后的数据集进行UNION去重的竖向拼接，这对于一些特殊场景的过滤非常有用

查询得到的结果

使用搜索CASE WHEN函数进行过滤

SELECT * FROM category WHERE CASE WHEN category_id = 1 THEN last_update = '2006-02-14 20:46:27' ELSE `name` LIKE 'C%' END

这里想要查询当category_id等于1时，last_update等于2006-02-14 20:46:27的category或者是name以'C'开头的category，可以理解为两种WHERE过滤条件后的数据集进行UNION去重的竖向拼接，这对于一些特殊场景的过滤非常有用

查询得到的结果

聚合函数

AVG(col)：返回指定列的平均值，如果为NULL不进行计数

COUNT(DISTINCT col)：返回指定列中非NULL值的个数，DISTINCT可以进行去重

COUNT(*)返回检索到的行数的计数，无论它们是否包含 NULL值

SUM(col)：返回指定列的所有值之和

结果集为空时，则SUM结果为NULL

累加字段出现NULL时，不参与累加计算

对非数值字段使用SUM，返回0而非NULL

MAX(col)：最大值

MIN(col)：最小值

GROUP_CONCAT(col)：将分组之后的值，使用","拼接起来

JSON函数

加密函数

MD5(str)：计算字符串str的MD5校验和

PASSWORD(str)：返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法

SHA(str)：计算字符串str的安全散列算法(SHA)校验和

系统信息函数

DATABASE()：返回当前数据库名

USER()或SYSTEM_USER()：返回当前登陆用户名

VERSION()：返回MySQL服务器的版本

CONNECTION_ID()：返回当前客户的连接ID

BENCHMARK(count, expr)：将表达式expr重复运行count次

窗口函数（重要）

前言

在进行报表统计分析时常常会遇见如下需求：用户上次下单时间、topN、百分比计算等

对于这样的需求，使用传统的SQL实现起来比较困难

这类需求都有一个共同的特点，需要在单表中满足某些条件的记录集内部做一些函数操作，不是简单的表连接，也不是简单的聚合可以实现的

要解决此类问题，最方便的就是使用窗口函数

简介

MySQL从8.0开始支持窗口函数，这个功能在大多商业数据库中早已支持，也叫分析函数（OLAP函数）

窗口函数常用语统计topN、排名、累积计算等

在报表等分析型查询中窗口函数能优雅地表达某些需求，发挥不可替代的作用

窗口函数只能在SELECT语句中或者ORDER BY语句中

窗口和窗口函数概念

窗口可以理解为根据OVER()表达式到当前行额外的数据集（多行数据）

与发生函数求值的当前行相关的查询行构成当前行的窗口

窗口函数为每行数据进行一次计算：输入多行（窗口范围内的数据），返回一个值

窗口函数对一组查询行执行类似聚合的操作。但是，聚合操作将查询行分组为单个结果行，而窗口函数为每个查询行生成一个结果

对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口

有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口

特点

窗口函数不改变原有数据集，可能会改变顺序

对窗口范围内的数据集进行统计计算

同时具有分组和排序的功能

执行时机

(8) SELECT  (9) DISTINCT  (10)  <TOP_specification> <select_list> （窗口函数）   (1)  FROM <left_table>     (3) <join_type> JOIN <right_table>    (2) ON <join_condition> （表连接条件）   (4) WHERE <where_condition> （一级过滤，WHERE后面不能使用别名，不能过滤聚合函数）   (5) GROUP BY <group_by_list> （识别SELECT中的别名，这里只能识别简单的SELECT字段的别名，不包含窗口函数的别名，后面的语句中都可以使用）   (6) WITH {CUBE | ROLLUP} (对分组后的数据进行汇总，对分组的字段取别名)   (7) HAVING <having_condition> （二级过滤，过滤聚合函数的值，以及普通字段） (11) ORDER BY <order_by_list> （对数据集进行排序，可以识别窗口函数的别名） (12) LIMIT OFFSET NUMBER   (分页功能)

在窗口函数执行时，数据集已经确定也就是说WHERE和HAVING已经执行完毕（窗口函数在ORDER BY和LIMIT前执行）

如果想要对窗口函数计算的值进行过滤，只能使用子查询然后使用WHERE进行过滤

如果想要对窗口函数计算的值进行排序，使用ORDER BY 窗口函数计算值别名即可

基本语法

window_function(expression) OVER (     [PARTITION BY part_list]     [ORDER BY order_list]     [{ROWS | RANGE } BETWEEN frame_start AND frame_end] )

window_function

专用窗口函数

排名函数

ROW_NUMBER()

其分区内的当前行数，数值相同时排名没有重复

RANK()

其分区内当前行的排名，有间隙，数值相同时，排名重复，例如（1、2、2、4）

DENSE_RANK()

其分区内当前行的排名，没有间隙，数值相同时，排名重复，例如（1、2、2、3）

分布函数

PERCENT_RANK()

百分比排名值

(rank - 1) / (rows - 1)，其中rank是行排名也就是RANK()函数的排名， rows是窗口范围内数据集的行数

CUME_DIST()

返回一组值中某个值的累积分布

这表示窗口分区的窗口排序中当前行之前或与之对等的行数除以窗口分区中的总行数。返回值范围从0到1

即分区值小于或等于当前行中的值的百分比

前后函数

LAG(col, N)

窗口范围内前N行的参数值

LEAD(col, N)

窗口范围内后N行的参数值

头尾函数

FIRST_VALUE(col)

窗口范围内第一行的参数值

LAST_VALUE(col)

窗口范围内最后一行的参数值

分桶函数

NTH_VALUE(col, N)

窗口范围内第N行参数值（N从1开始）

NFILE()

聚合函数

SUM()

AVG()

MAX()

MIN()

COUNT()

PARTITION BY子句

表示将数据先按part_list进行分区，PARTITION BY子句并不是必须的，如果没有则表示，执行HAVING过滤后的全部数据集

ORDER BY子句

按照order_list进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和PARTITION BY子句配合使用，也可以单独使用

FRAME子句

FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用

滑动窗口的范围指定方式有两种基于行和基于范围

基于行

通常使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录

CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用

基于范围

命名窗口

窗口函数和聚合函数区别

聚合函数是将多条记录聚合为一条

窗口函数是每条记录都会执行，不会改变原有的数据集

聚合函数也可以用于窗口函数中

案例

建表语句

CREATE TABLE `order_tab` ( `order_id` int NOT NULL, `user_no` int DEFAULT NULL, `amount` decimal(10,5) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

插入SQL语句

INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (1, 1, 100.00000, '2018-01-01 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (2, 1, 300.00000, '2018-01-02 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (3, 1, 500.00000, '2018-01-02 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (4, 1, 800.00000, '2018-01-03 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (5, 1, 900.00000, '2018-01-04 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (6, 2, 500.00000, '2018-01-03 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (7, 2, 600.00000, '2018-01-04 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (8, 2, 300.00000, '2018-01-10 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (9, 2, 800.00000, '2018-01-16 00:00:00'); INSERT INTO `order_tab`(`order_id`, `user_no`, `amount`, `create_time`) VALUES (10, 2, 800.00000, '2018-01-22 00:00:00');

案例一（3个排名函数）

需求：查询订单信息并且给出每个用户订单金额的排名

SQL语句

SELECT   *,   RANK() OVER(PARTITION BY user_no ORDER BY amount DESC) AS `rank`,   DENSE_RANK() OVER(PARTITION BY user_no ORDER BY amount DESC) AS `dense_rank`,   ROW_NUMBER() OVER(PARTITION BY user_no ORDER BY amount DESC) AS `row_num` FROM   order_tab

执行效果

三个排名函数的区别与联系

如果比较的值没有相同，三个函数的执行效果一致

如果比较的值相同

rank函数，当值重复时，排名也重复，后续排名不连续。如果有并列名次的行，会占用下一名次的位置

dense_rank函数，当值重复时，排名也重复，后续排名连续。如果有并列名次的行，不占用下一名次的位置。dense就是稠密的意思

row_number函数，当值重复时，排名并不重复，不考虑并列的情况

注意事项

具体使用哪个排名函数需要根据具体的业务需求来定

使用排名函数时必须有ORDER BY子句，不然没有排序依据没法进行排名

是否有PARTITION BY子句需要根据业务需求来定，一般而言都是需要的

案例二（topN）

需求：查询每个用户订单金额最高的前三个订单

SQL语句

SELECT   t1.* FROM (   SELECT     *,     ROW_NUMBER() OVER(PARTITION BY user_no ORDER BY amount DESC) AS `row_num`   FROM   order_tab ) t1 WHERE   t1.row_num <= 3

执行效果

分析

这里只能用子查询后进行WHERE过滤

窗口函数的执行时机在SELECT之后，ORDER BY之前。因此不能使用直接WHERE过滤

排名函数，只能使用ROW_NUMBER()，不能使用RANK()或者DENSE_RANK()。因为可能存在多个并列的排名，无法确定WHERE过滤的值

案例三（聚合函数作为窗口函数）

聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响

常见SQL书写技巧（重要）

SUM(IF)和SUM(CASE WHEN)

CASE WHEN函数技巧

ORDER BY函数技巧

ORDER BY

ORDER BY FIELD

https://blog.csdn.net/ManyPeng/article/details/105387061?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_baidulandingword-1&spm=1001.2101.3001.4242

ORDER BY IF

https://blog.csdn.net/weixin_38626799/article/details/80107697

ORDER BY IN

https://blog.csdn.net/weixin_38626799/article/details/80107697

ORDER BY CASE WHEN

WITH ROLLUP

CTE（通用表达式）

子查询

ALL（SOME）、IN、ANY

行子查询

数据库三范式

为了建立冗余较小、结构合理的表，设计数据库时必须遵循一定的规则

在关系型数据库中这种规则就称为范式

范式是符合某一种设计要求的总结

要想设计一个结构合理的关系型数据库，必须满足一定的范式。比较常见的是三范式，第一范式、第二范式和第三范式

第一范式

表中必须要有主键，不能出现重复记录，每个字段都是原子性的不能再分

不符合第一范式的实例

联系方式列不是原子性的，可以再次拆分成邮箱和手机号

解决方案

关于列不可再分，应该根据具体的业务情况来决定。但是一个表必须要有主键，且数据不能出现重复

第二范式

第二范式是建立在第一范式基础上的，要求所有非主键字段完全依赖主键，不能产生部分依赖，一张表只描述一件事

不符合第二范式的案例：其中学生编号和课程编号为联合主键

这张表描述了三件事：学生信息、课程信息、学生的成绩信息

虽然存在着主键（学生编号 + 课程编号），但是表中出现了大量冗余数据，例如课程名称中java、mysql、html多次出现

出现冗余的原因在于，学生信息部分依赖了主键的一个字段学生编号，和课程编号没有关系。同时课程的信息只是依赖课程id，和学生id没有关系。只有成绩一个字段完全依赖主键的两个部分，这就是第二范式部分依赖

上面的表其实是多对多的关系，多对多一般存在联合主键

解决方案：将表进行拆分：学生表、课程表、成绩表

学生表：学生编号为主键

课程表：课程编号为主键

成绩表：中间表，学生编号和课程编号为联合主键

第三范式

建立在第二范式基础上的，所有非主键字段和主键字段之间不能产生传递依赖

不满足第三范式的例子：其中学生编号是主键

何为传递依赖：专业编号依赖学生编号，应为该学生学的就是这个专业啊。但是专业名称和学生其实没多大关系，专业名称依赖于专业编号。某一个字段不直接依赖主键，而是另一个字段（外键），外键依赖于主键，形成传递依赖

解决方法

学生表，学生编号为主键

专业表，专业编号为主键

以上设计是典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方

总结

一张表必须要有主键，最好是和业务无关的字段，例如自增主键

一般而言所有的表都会满足第一范式，不满足第一范式的表一眼都能看出来

关于列是否必须为原子性，可以根据具体的业务需求来定

如果感觉表很奇怪，一般都是不满足第二范式，没有做到一张表只描述一件事

在设计表的时候一般满足第一和第二范式即可，如果需要冗余部分字段来减少关联查询可能不满足第三范式。由于进行了字段冗余，就需要必须要做到多张表数据的一致性

常见表关系

一对一

一个表和另一张表存在的关系是一对一

如何设计表

两张表共享主键，其实就是纵向拆分，将一张表拆成多张表，将部分字段拆分出去形成多张表

两张表，各自有主键，外键唯一

使用这种方式，一般存在主表和次表。主表的数据不会发生弃用，例如不会删除或者是status设置为0

次表的数据可能发生弃用

外键字段添加到哪里？

外键字段添加到主表中，引用次表的主键。可以清晰地看到使用的次表的数据

外键字段添加到次表中，关联主表的主键。可以看到主表使用的历史次表数据。需要保证次表数据只能引用一次主表数据（例如主表 A JOIN 次表B ON 主表A.bid = 次表B.id AND 次表B.status = 1）

一对多

第三范式的例子

如何设计表

两张表，外键建在多的一方，引用主表的主键

三张表，中间表分别引用另外两张表的主键形成联合主键，或者使用自增主键

这种可以记录两张表之间的引用关系

优点：如果表之间关系发生变更，从一对多变成多对多，这样不用修改表结构和数据

缺点：增加额外的表，查询起来较为复杂

多对多

第二范式的例子

如何设计表

三张表，外键设置在中间表，分别引用主表的主键

使用联合主键（引用另外两张表的外键）或者是自增主键

MySQL架构

架构图

MySQL分为Server和存储引擎两大部分

服务层（Server）

大部分的服务都在这一层：包括查询解析、分析、优化、缓存、内置函数、存储过程、触发器、视图等

存储引擎层（Storage Engine）

Server层通过API和存储引擎通信，不同的存储引擎之间不会通信

可以理解为面向接口编程，屏蔽底层实现细节。存储引擎可以按照需求进行设置

存储引擎层负责数据的存储和提取，支持InnoDB、MyISAM、Memory等存储引擎

不同的存储引擎共用同一个Server层

存储引擎设置在表上

注意：存储引擎是不会解析SQL的

客户端

连接到MySQL服务器终端。例如JDBC的Java程序、Navicat、命令行终端等

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

分析器

检查SQL语法是否正确

词法分析

MySQL需要识别出里面的字符串分别是什么，代表什么

MySQL从你输入的"SELECT"这个关键字识别出来，这是一个查询语句

它也要把字符串"T"识别成"表名"T"，把字符串"ID"识别成"列ID"

语法分析

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法

经过词法分析和语法分析形成抽象语法树

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引

或者在一个语句有多表关联（JOIN）的时候，决定各个表的连接顺序

优化器阶段完成后，这个语句的执行方案就确定了

执行器

进行权限检查，判断是否具有查询表的权限

调用存储引擎的接口，获取数据

查询缓存

查询数据时，先去缓存找，如果缓存命中，直接返回；如果没有命中，走解析器 -> 优化器 -> 存储引擎

MySQL存储引擎（重点）

MySQL属于关系型数据库，而关系型数据库的存储是以表的形式进行的，对于表的创建，数据的存储、检索、更新等都是由MySQL存储引擎完成的，这也是MySQL存储引擎在MySQL中扮演的重要角色

MySQL的存储引擎种类比较多，如MyISAM、InnoDB和Memory存储引擎

在MySQL架构中执行器调用存储引擎的API获取和保存数据，可以让存储引擎以插件的方式存在，按需设置存储引擎，存储引擎设置的级别是表

存储引擎的作用：管理表创建、数据检索、数据保存和修改、索引创建和索引修改等等

InnoDB存储引擎

从MySQL5.5版本之后，MySQL的默认内置存储引擎已经是InnoDB了

具有如下特点

支持事务。默认的事务隔离级别为可重复度，通过MVCC（多并发版本控制）来实现读写不冲突

使用的锁粒度为行级锁，可以支持更高的并发，但是容易发生死锁

支持外键

在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度

对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，在.ibd文件中

MyISAM存储引擎

在5.5版本之前，MyISAM是MySQL的默认存储引擎，该存储引擎并发性差，不支持事务，所以使用场景比较少

具有如下特点

不支持事务

不支持外键，如果强行增加外键，不会提示错误，只是外键不其作用

对数据的查询缓存只会缓存索引，不会像InnoDB一样缓存数据，而且是利用操作系统本身的缓存

默认的锁粒度为表级锁，所以并发度很差，加锁快，锁冲突较少，所以不太容易发生死锁

支持全文索引（MySQL5.6之后，InnoDB存储引擎也对全文索引做了支持），但是MySQL的全文索引基本不会使用，对于全文索引，现在有其他成熟的解决方案，比如：ElasticSearch、Solr、Sphinx等

InnoDB和MyISAM两种存储引擎的区别（重点）

事务

InnoDB支持事务

MyISAM不支持事务

外键

InnoDB支持外键

MyISAM不支持外键

锁

InnoDB有表锁和行锁，默认锁的粒度为行级锁，并发度更高，但是更容易发生死锁

MyISAM只有表锁，默认锁的粒度为表锁，并发度较低，但是发生死锁的概率较低

索引

InnoDB的索引是聚簇索引，索引和数据放在一起，B+树叶子节点放置主键和实际数据

MyISAM是非聚簇索引，索引文件和数据文件分开放置，B+树的叶子结点放置数据地址指针

文件

InnoDB的索引为聚簇索引，分为.frm（表空间）和.idb（数据和索引）两个文件

MyISAM的索引为非聚簇索引，分为.frm（表空间）、.myi（索引）和.myd（数据）三个文件

缓存

InnoDB查询会同时缓存索引和数据

MyISAM查询只有缓存索引

如何选择

使用场景是否需要事务支持

是否需要支持高并发，InnoDB的并发度远高于MyISAM

是否需要支持外键

高效缓冲数据，InnoDB对数据和索引都做了缓冲，而MyISAM只缓冲了索引

索引，不同存储引擎的索引并不太一样

如果不知道如何选择，默认选择InnoDB即可

索引（重点）

索引是什么

索引本质上是一种排好序的数据结构，是真实存在的，存储到物理磁盘文件中

帮助MySQL快速获取数据，可以将随机IO转变成顺序IO，加快数据的查询速度

对指定的列或者多列添加额外的数据结构，让查找变得更快，可能降低新增、修改、删除的时间

没有特别说明，一般说的索引都是指B树或者B+树

可以理解为书本前面的目录，通过目录快速定位对应的页码，加快查找的过程。如果没有目录就需要从开始遍历数据进行查找

使用索引的优点和缺点

优点

可以大大提高查询速度

可以显著的减少查询中分组和排序的时间

可以加速表与表的连接

可以通过创建唯一索引，可以保证每一行数据的唯一性

缺点

创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作

索引需要磁盘的空间进行存储，如果针对单表创建了大量的索引，可能比数据文件更快达到大小上限

当对表中的数据进行新增、修改或者删除时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作的性能，增加执行时间

索引的分类（重点）

按照使用字段的个数

单列索引

主键索引、辅助索引

使用主键字段的索引就是主键索引，其他都是辅助索引

辅助索引以非主键字段生成的称为辅助索引，又称为次级索引，二级索引

聚簇索引、非聚簇索引

聚簇索引只有Innodb存储引擎支持，使用主键生成的索引

如果没有主键，使用第一个非空唯一键；如果也没有，默认生成row_id（6字节）作为主键

除了聚簇索引之外的索引，其他都是非聚簇索引

唯一索引（添加了唯一约束）

一个或者多个字段添加了唯一约束，形成唯一索引

全文索引

较少使用。一般使用ElasticSearch、Solr等搜索引擎代替

复合索引（联合索引、组合索引）

多个字段组成形成复合索引

按照使用字段是否为主键

主键索引

使用的字段为主键

辅助索引（次级索引、二级索引）

使用非主键的字段

按照索引和数据是否存储在一起（重点）

聚簇索引（聚集索引）

表的存储引擎为InnoDB，且使用主键或者非空唯一键或者默认row_id作为索引

非聚簇索引（非聚集索引）

表的存储引擎为InnoDB，除了主键或者非空唯一键或者row_id，之外的其他字段，作为索引列都是非聚簇索引

表的存储引擎为MyISAM，使用到的索引都为非聚簇索引

按照底层数据结构

B树索引（B树和B+树）

Hash索引

R-索引（空间索引）

全文索引（倒排索引）

各种数据结构

数组和链表

数组数据查询时，需要将数据全部加载到内存，如果数据量较大，占据大量内存。查询时需要进行全表扫描，获取数据

链表不使用大内存，查询时仍然需要进行全表扫描

hash

类似于Java中的HashMap，字段的值通过hash函数进行散列，然后使用链地址法解决hash冲突

可以快速进行等值查询，但是涉及到范围查找，只能进行全表扫描

在特定使用常见下合适，不适合大规模使用

二叉搜索树

简单定义

首先二叉搜索树也是一棵二叉树

二叉搜索树的任意结点A，其左子树的所有结点的值都小于结点A的值，其右子树的所有结点都大于结点A的值；前提是任意结点A的左右子树不为空

二叉搜索树的左右子树也是一棵二叉搜索树

二叉搜索树没有值相等的结点

特点

二叉搜索树所存储的元素必须具有可比较性，也就是说字段的值必须存在，不能为NULL

二叉搜索树搜索元素的时间复杂度为O(logN) ~ O(N)，N为元素的个数

图示

缺点

在顺序（递增或者递减）写入的情况下，会退化成链表。查询数据时需要全表扫描，时间复杂度为O(N)

图示

AVL树

由于二叉搜索树在最坏的情况下（顺序写入）会退化成链表，搜索时的时间复杂度高

这里AVL树在节点进行插入、删除、修改的时候进行了自平衡，让整棵树不至于过于倾斜

简单定义

树的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树

 某结点的左子树与右子树的高度（深度）差即为该结点的平衡因子（BF、Balance Factor）

特点

AVL树由于自平衡的存在，使得树的高度不会太高，平均搜索的时间复杂度为O(logN)

图示

右边二叉树中节点值为10的左右子树高度差为2

自平衡手段

如果插入新节点时发现左右子树的平衡因子的绝对值大于2，通过LL、LR、RR、RL的操作保证平衡因子的绝对值小于等于1

缺点

树的高度较高，需要多次IO操作

红黑树

红黑树存在的问题和AVL树类似，在于当数据量很大的时候，树的高度太高，IO的次数太多，效率较低

B树

前言

二叉树查找的时间复杂度为O(log N)，AVL树和红黑树等通过自平衡手段让二叉树不那么倾斜，但是二叉树的高度还是太高了，需要进行多次磁盘IO操作

为了减少磁盘IO的次数，必须降低树的深度，将"瘦高"的树变得"矮胖"

基本的思路就是每个节点存储多个元素，摒弃二叉树结构使用多叉树

B树，这里的B表示balance（平衡的意思），B树是一种多路自平衡的搜索树。它类似普通的平衡二叉树，不同的一点是B树允许每个节点有更多的子节点

m阶B树（m为子树个数）基本定义

每个节点至多可以拥有m棵子树

根节点，只有至少有2个节点（要么极端情况，就是一棵树就一个根节点，单细胞生物，即是根，也是叶，也是树)

非根非叶的节点至少有的Ceil(m / 2)个子树

非叶节点中的信息包括[n, A0, K1, A1, K2, A2, … ,Kn, An]，其中n表示该节点中保存的关键字个数，K为关键字且Ki < Ki+1，A为指向子树根节点的指针

从根到叶子的每一条路径都有相同的长度，也就是说，叶子节在相同的层，并且这些节点不带信息，实际上这些节点就表示找不到指定的值，也就是指向这些节点的指针为空

B树的查询过程和二叉排序树比较类似，从根节点依次比较每个结点，因为每个节点中的关键字和左右子树都是有序的，所以只要比较节点中的关键字，或者沿着指针就能很快地找到指定的关键字，如果查找失败，则会返回叶子节点，即空指针

图示

特点

叶子结点和非叶子均存储数据

任何一个关键字只存在于一个结点中，也就是说关键字在B树内不会重复

按照索引查询，并不需要在叶子结点结束，可能在非叶子节点结束。B树的查询效率，最好为O(1)，最差为树的高度，查询效率不稳定

查询时，在关键字内全集内做一次查找，性能逼近二分查找

新增、修改、删除数据需要重新维护B树

B+树

B+树是B-树的变体，也是一种多路搜索树, 它与B树的不同之处在于

非叶子节点只存储关键字，叶子节点存储关键字和实际数据

关键字在整颗B+树内可以重复

非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字

数据库具体实现的时候，一般都会为所有叶子结点增加了双向指针

图示

特点

非叶子节点相当于叶子节点的索引，叶子节点相当于是存储（关键字）数据的数据层

不可能非叶子节点命中返回，必须命中叶子结点返回（索引覆盖除外）。查询效率较为稳定，需要IO的次数就为B+树的高度

总结各种数据结构

前面讲述了大量的数据结构，最后发现B树和B+树是较为合理的，可以作为索引底层的数据结构

评价数据结构是否适合作为索引的标准就是查询数据时磁盘IO的次数，因为磁盘IO的速度比起内存IO要慢上几个数量级

由于B树在非叶子节点同时存储数据和关键字，造成一个节点能够存储的数据个数不会太多，那么B树的高度就会比较高，磁盘IO的次数就会较多

B+树非叶子节点只存储关键字，因此能够存储的关键字更多，B+树的高度就不会太高，一般为3 ~ 4层。因此B+树的高度比B树低，磁盘IO次数更少

综上所述B+树更适合作为索引底层的数据结构

扇区、磁盘块、内存页、局部性原理、磁盘预读、InnoDB数据页

扇区

硬盘的最小读写单元，一般是4KB

磁盘块

操作系统对硬盘读取的最小单元，一般是扇区的2的N次方

内存页

操作系统对内存操作的最小单元，一般是4KB

局部性原理

当一个数据被用到时，其附近的数据也通常会马上被使用

磁盘预读

程序运行期间所需要的数据通常比较集中

通常程序读取数据，并不是只读取需要的，而是将附近的数据都读取出来

通常是读取一整个磁盘块

InnoDB数据页

和操作系统读取磁盘类似，InnoDB读取数据是以页为单位进行读取的

页（Page）是Innodb存储引擎用于管理数据的最小磁盘单位

页大小默认为16KB

可以通过SQL：SHOW GLOBAL STATUS LIKE 'innodb_page_size';进行查看

综上所述，将B+树一个节点就设置成16KB，就是一个数据页大小。读取节点数据时将整个数据页一次性加载到内存，减少IO操作的次数

B+树根节点常驻内存，搜索时，遍历整个B+树需要进行磁盘IO的次数为h - 1（h为B+树的高度），一般B+树的高度一般为3 ~ 4层，那么只需要2 ~ 3次磁盘IO就可以获取数据。通常存储引擎会缓存索引，因此查询速度会更快

索引的基本操作

索引命名

普通索引：idx_字段1_字段2_...字段N

唯一索引：ux_字段1_字段2_...字段N

新增

建表时

INDEX `索引名`(字段1, 字段2(使用的长度)) USING BTREE

建表后

ALTER TABLE db_name.tbl_name ADD INDEX idx_name(filed_name(20));

删除

ALTER TABLE db_name.tbl_name DROP INDEX idx_name;

使用索引进行查询

一般将索引列在WHERE或者是ON的后面，同时注意索引不要失效

InnoDB和MyISAM存储引擎如何使用B+树索引

MyISAM

MyISAM所有的索引都是非聚簇索引

数据的存储不是按主键顺序存放的，按写入的顺序存放

主键索引和辅助索引

主键索引

图示

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意

可以看出MyISAM的索引文件非叶子节点存放主键，叶子结点存放主键和数据地址

辅助索引

图示

主键索引和辅助索引的区别在于主键不能重复，辅助索引的值可以重复

MyISAM存储引擎的索引文件和数据文件分开存放，因此也叫作非聚簇索引

如果表使用的存储引擎为MyISAM，存在三个文件.frm（表定义文件）、.MYD（数据文件）、.MYI（索引文件）

图示

InnoDB

只有InnoDB支持聚簇索引，只有InnoDB的主键索引是聚簇索引，除此之外的所有索引都是非聚簇索引

数据写入的顺序是按照主键的大小升序写入

主键索引和辅助索引

主键索引

图示

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，如果没有主键会去寻找第一个唯一非空键，如果也没有默认生成6字节的row_id作为主键。索引数据和真实数据放置在一起，在同一个文件中

非叶子节点存放关键字，叶子节点存放关键字以及实际数据

同时叶子节点键增加指向前后节点的顺序指针，对于范围查找非常方便

对于查找如果找到主键，那么就直接找到了整行数据，不需要进行额外IO操作

如果使用InnoDB存储引擎，存在两个文件.frm（表定义文件）和.idb（数据和索引文件）

图示

辅助索引

图示

关键字可以重复，非叶子节点存放关键字，叶子节点存放关键字和主键索引的值

辅助索引查找整行数据，需要先根据辅助索引的值进行定位到主键索引的值，然后去主键索引树查找整行数据，这里需要进行两次索引树的查找，因此整个过程叫回表

MySQL单表能够存储的数据量计算

这里以InnoDB的主键索引为例

B+树的一个节点大小为一个数据页，默认为16KB

B+树非叶子节点存储的数据为关键字和指针，主键字段的数据类型为bigint（8字节），同时存在着指向下一个主键的指针（6字节）

一个节点中主键的个数为16 * 1024 / (8 + 6) ≈ 1170个

叶子节点中存储主键和实际数据，一般而言实际数据大小远远大于主键大小，主键大小可以忽略不计。实际数据大小假定为1KB，那么叶子节点可以存储16KB / 1KB = 16条数据

通常B+树的高度为3层，整颗B+树能够存储的数据就是1170 * 1170 * 16 ≈ 2200 0000，大约等于2000万

也就是说MySQL单表能够存储的数据量大概在2000万左右

为什么MySQL推荐使用自增主键，而不推荐使用UUID或者雪花ID

这里主要都是InnoDB的主键索引，也就是聚簇索引

InnoDB存储引擎在写入数据时，按照主键的值进行顺序写入，也就是说如果主键的值如果不是自增（例如UUID杂乱无序），可能会将后面的数据，写入到前面的数据页中，可能造成数据页重建以及分裂

雪花ID的插入顺序略低于自增主键

使用自增ID的缺点

由于主键自增，容易被猜出业务增长量

在高并发写入情况下，可能造成对锁的争抢严重，造成写入性能下降

联合索引（复合索引）（重要）

联合索引指多个字段共同建立索引

对于经常查询的字段例如A、B、C，通常建立(A, B, C)的联合索引，而不是为每一个字段都建立单独的索引

为什么使用联合索引

减少索引开销

例如建立联合索引(A, B, C)实际上会建立索引(A)、(A, B)、(A, B, C)三个索引

最左匹配原则（重要）

如果使用了联合索引在进行WHERE过滤时，需要注意MySQL会一直向右进行匹配，遇到范围查找就停止（>、<、BETWEEN、LIKE）

比如WHERE a = 1 AND b = 2  c > 3 AND d = 4如果建立(a, b, c, d)顺序的索引，d是用不到索引的，如果建立(a, b, d, c)的索引则都可以用到，a、b、d的顺序可以任意调整

比如WHERE a = 1 AND b = 2 AND c = 3建立(a, b, c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式

索引覆盖（重要）

又称为覆盖索引，本质上是一种现象，并不是一种实际存在的索引

查找数据时，只查询索引的值，不查询其他数据，过滤时只通过索引列进行过滤，命中索引就会直接返回索引数据，不需要查询实际数据，大大提高了查询效率

A、B、C三个字段建立了联合索引，进行如下SQL的查询：SELECT A, B, C FROM tbl WHERE A = XXX AND B = XXX;

因此在写SQL时，不需要的字段没有必要查询出来，尤其要避免SELECT *的写法

然而理想很丰满，现实很骨感。这样的SQL业务场景非常有限，几乎没有

索引失效

联合索引，没有遵守最左前缀原则

联合索引，范围（>、<、BETWEEN AND）之后的字段索引失效

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效转而向全表扫描

在高版本的MySQL似乎进行了优化

使用不等于（!=或者<>），索引失效

使用LIKE '%XXX%'进行模糊查询

可以使用LIKE 'abc%'，避免全表扫描，前面必须要有字段

字符串不使用单引号索引失效

在高版本的MySQL似乎进行了优化

使用IS NULL或者IS NOT NULL进行WHERE过滤

InnoDB存储引擎允许字段的值为NULL，也可以添加索引

在MySQL不同版本，不同数据量的情况下，优化器会根据情况进行判断是否使用索引，因此存在着4不同的情况

一般而言MySQL的版本越高，基本上都会走索引

IN关键字

在不同版本的MySQL，情况不相同，可能会走，也可能不会走索引

OR关键字

如果想要OR也是用索引，过滤的每一列都需要添加索引

MySQL判断全表扫描比索引查找更快，索引失效

总结：上述的几种情况，在高版本的MySQL中都进行了优化，同时和数据量也有一定的关系，因此走不走索引并不是绝对的

索引使用事项

应该

对于经常WHERE、ORDER BY、GROUP BY、聚合函数的字段建立索引

外键字段建立索引

字段具有唯一性，建议生成唯一索引。在数据库的层面，保证数据正确性

对于经常一起出现的字段，推荐建立联合索引，需要注意最左前缀原则，将经常过滤的字段放在前面

能够使用覆盖索引，就不要查询出无用字段，减少回表操作

不应该

数据量不大，没有必要建索引，全表扫描可能更快

对于数据区分度不高的字段，不要建立索引

例如性别，一般而言只有男、女，建立索引的意义不大

可以使用SELECT COUNT(DISTINCT col) / COUNT(*)进行判断区分度

对于频繁发生修改的字段，不要建立索引

参与计算的列，不要建立索引

没有必要为每个字段建立索引，索引存储会消耗磁盘空间

常见面试题

B树和B+树有什么区别，为什么MySQL使用B+树作为索引底层的数据结构

MyISAM和InnoDB是如何使用B+树索引的

聚簇索引和非聚簇索引有什么区别

索引覆盖是什么？什么是回表？

EXPLAIN（重点）

EXPLAIN关键字查看MySQL对SELECT类型的SQL的执行计划。从而知道MySQL是如何处理查询SQL的

当前测试的MySQL服务器版本为5.7.28，操作系统为Window10

语法

在原来的SELECT的SQL上增加EXPLAIN关键字即可

作用

查看表的读取顺序

查询类型

哪些索引被使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

测试使用的表

subject（学科表）

建表语句

create table subject( id int(10) auto_increment, name varchar(20), teacher_id int(10), primary key (id), index idx_teacher_id (teacher_id));

INSERT语句

insert into subject(name,teacher_id) values('math',1),('Chinese',2), ('English',3),('history',4);

teacher（教师表）

建表语句

create table teacher( id int(10) auto_increment, name varchar(20), teacher_no varchar(20), primary key (id), unique index unx_teacher_no (teacher_no(20)));alter table teacher add index idx_name(name(20));

INSERT语句

insert into teacher(name,teacher_no) values('wangsi','T2010001'), ('sunsi','T2010002'),('jiangsi','T2010003'),('zhousi','T2010004');

student（学生表）

建表语句

create table student( id int(10) auto_increment, name varchar(20), student_no varchar(20), primary key (id), unique index unx_student_no (student_no(20)));

INSERT语句

insert into student(name,student_no) values ('zhangsan','20200001'), ('lisi','20200002'),('yan','20200003'),('dede','20200004');

student_score（学生成绩表）

建表语句

create table student_score( id int(10) auto_increment, student_id int(10), subject_id int(10), score int(10), primary key (id), index idx_student_id (student_id), index idx_subject_id (subject_id));

INSERT语句

insert into student_score(student_id,subject_id,score) values(1,1,90),(1,2,60), (1,3,80),(1,4,100),(2,4,60),(2,3,50),(2,2,80),(2,1,90),(3,1,90),(3,4,100), (4,1,40),(4,2,80),(4,3,80),(4,5,100);

EXPLAIN EXTENDED

会在EXPLAIN的基础上额外提供一些查询优化信息

紧随其后SHOW WARNINGS命令可以查看优化后的查询语句，从而看出优化器优化了什么

EXPLAIN EXTENDED SELECT * FROM film WHERE id = 1;SHOW WARNINGS;

结果

在未来版本的MySQL可能会删除这个关键字

EXPLAIN PARTITIONS

相比EXPLAIN多了个PARTITIONS字段，如果查询是基于分区表的话，会显示查询将访问的分区

高版本的MySQL已经默认带上了PARTITIONS字段

EXPLAIN中的列

id：表示查询中执行SELECT子句或操作表的顺序

有几个SELECT就有几个id

执行顺序

id相同

执行顺序从上到下

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

执行计划

执行的顺序为teacher -> subject -> student_score，并不是按照书写的顺序查询的

id不同

如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

执行的顺序为teacher -> subject -> score

id相同又不同

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

执行的顺序为2.teacher -> 2.subject -> 1.subject -> 1.teacher

id的值为NULL

id为NULL的最后执行

union结果总是放在一个匿名临时表中，临时表不在SQL中出现，因此它的id是NULL

select_type：表示查询的类型，主要用于区别普通查询，联合查询，子查询等复杂查询

SIMPLE

简单的SELECT查询，查询中不包括子查询或者UNION

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;

执行计划

PRIMARY

查询中若包括任何复杂的子部分，最外层查询则被标记为PRIMARY

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

SUBQUERY

在select或where列表中包含了子查询

SQL语句

explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));

执行计划

DERIVED

在FROM列表中，包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里

MySQL5.7+进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

当派生子查询存在以下操作时该特性无法生效：UNION 、GROUP BY、DISTINCT、LIMIT/OFFSET以及聚合操作

SQL语句

EXPLAIN SELECT t1.* FROM (SELECT * FROM subject WHERE id = 1 GROUP BY id) t1

执行计划

UNION

若第二个SELECT出现在UNION之后，则被标记为UNION

若UNION包含在FROM子句的子查询中，外侧SELECT将被标记为DERIVE

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

UNION RESULT

从UNION表获取结果的SELECT

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

table

显示数据来自于哪个表，有时不是真实的表的名字（对表取了别名，显示别名），虚拟表最后一位是数字，代表id为多少的查询

type（重点）

在表中找到所需行的方式NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > in dex_subquery > range > index > ALL。效率从高到低

掌握常见8种即可：NULL > system > const > eq_ref > ref > range > index > ALL

一般来说达到range级别就可以，最好达到ref级别

NULL

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

SQL语句

explain select min(id) from subject;

执行计划

const、system

当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问

如将主键或者是唯一键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

SQL语句

explain select * from teacher where teacher_no = 'T2010001';

执行计划

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;

执行计划

ref

非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行。然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体

SQL语句

explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id

执行计划

range

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引一般就是在你的where语句中出现between、<>、in等的查询

SQL语句

explain select * from subject where id between 1 and 3;

执行计划

index

Full index Scan，Index与All区别：index只遍历索引树，通常比All快因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的

通常见于索引覆盖或者是JOIN连接查询中（外键设置了索引）

SQL语句

explain select id from subject;

执行计划

ALL

Full Table Scan，将遍历全表以找到匹配行

SQL语句

explain select * from subject;

执行计划

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示NULL）

可能出现possible_keys有列，而显示NULL的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询

key（重点）

实际使用到的索引，如果为NULL，则没有使用索引。查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

key_len计算规则如下

按照生成索引字段的数据类型来计算

MySQL常用数据类型（日期|时间、数值、字符串）

日期和时间类型

date：3字节，日期，格式：2014-09-18

time：3字节，时间，格式：08:42:30

datetime：8字节，日期时间，格式：2014-09-18 08:42:30，范围为1000-01-01 00:00:00/9999-12-31 23:59:59

timestamp：4字节，自动存储记录修改的时间，时间戳，范围为1970-01-01 00:00:00/2038

year：1字节，年份

整型（有符号）

tinyint：1字节，范围（-128 ~ 127）

smallint：2字节，范围（-32768 ~ 32767）

mediumint：3字节，范围（-8388608 ~ 8388607）

int或integer：4字节，范围（-2147483648 ~ 2147483647）

bigint：8字节，范围（+|-9.22 * 10的18次方）

注意：上面定义的都是有符号的

加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就要翻翻了。例如tinyint unsigned的取值范围为0 ~ 255

表示数量的字段建议加上unsigned关键字，例如库存、余额等，如果程序计算库存小于0了，在UPDATE或者是INSERT的时候直接报错，保证库存不超卖

浮点型

float(m, d)：4字节，单精度浮点型，m总个数，d小数位

double(m, d)：8字节，双精度浮点型，m总个数，d小数位

decimal(m, d)：m + 2字节，decimal是存储为字符串的浮点数

字符串数据类型

char(n)：固定长度，最多255个字符

varchar(n)：可变长度，最多65535个字符

tinytext：可变长度，最多255个字符

text：可变长度，最多65535个字符

mediumtext：可变长度，最多2的24次方-1个字符

longtext：可变长度，最多2的32次方-1个字符

注意

char(n)和varchar(n)中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候（UTF8）意味着可以插入n个中文，但是实际会占用n * 3个字节

同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n

超过char和varchar的n设置后，直接报错

对于char类型的字段不要设置的太大，如果不确定字段是否有值，建议设置成varchar(255)

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

生成索引的字段，建议设置非空约束。如果存在非空，建议使用其他值代替

不损失精确性的情况下，长度越短越好

对于字符类型的字段，如果没有必要使用全部数据，可以只是用字符前几位。例如INDEX(name(21))

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered

表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%

Extra（重要）

Using filesort

当查询中包含order by操作，而且无法利用索引完成的排序操作称为"文件排序"，这里的文件排序是指先在内存中进行排序，当内存排序无法完成时，使用临时文件帮助排序

出现了Using filesort就需要对SQL语句进行优化

SQL语句

explain select * from subject order by name;

执行计划

Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见group by、order by、distinct、union等关键字

出现Using temporary，说明使用了临时表，可能需要对SQL语句进行优化

SQL语句

explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id union select subject.* from subject right join teacher on subject.teacher_id = teacher.id;

执行计划

关于Using where、Using index、Using index & Using where、Using index condition详细解释说明：https://www.cnblogs.com/kerrycode/p/9909093.html

Using join buffer

改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能

Impossible where

这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）

Select tables optimized away

这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used

Query语句中使用from dual 或不含任何from子句

缺点

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

EXPLAIN不考虑各种Cache

EXPLAIN不能显示MySQL在执行查询时所作的优化工作

部分统计信息是估算的，并非精确值

EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

事务（重点）

简介

在MySQL数据库中只有InnoDB存储引擎支持事务，MyISAM存储引擎不支持事务。默认的存储引擎是InnoDB

事务用来保证数据的完整性、正确性，操作的原子性以及并发访问时数据的隔离性

事务用来管理INSERT、UPDATE、DELETE等DML语句

事务必须手动开启、提交、回滚，也可以进行自动提交事务

事务的作用范围是一个SESSION中，一个SESSION中可以有多个事务。常见的SESSION：JDBC中的一个Connection对象，也就是一个线程；命令行窗口也是一个SESSION

事务有中有安全点，可以理解为将大事务拆分成小事务，回滚时只回滚到对应的安全点，并不是回滚全部回滚数据

事务四大特性（ACID）

原子性（Atomicity）

事务是一个操作最基本单元，其对数据的新增、修改、删除，要么全都执行，要么全都不执行，不会结束在中间某个环节

事务一旦被提交提交，在事务期间对数据的新增、修改、删除，必须全部执行

事务一旦被回滚，在事务期间对数据的新增、修改、删除，必须全部回退。新增数据，必须删除；修改数据必须恢复；删除数据必须重新回归，就好像这个事务从来没有被执行过一样

在一个SESSION中，也就是一个会话期间，事务既没有提交也没有回滚，当会话结束时，数据并不会发生修改

隔离性（Isolation）

数据库允许多个事务同时对相同的数据进行读取和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

持久性（Durable）

事务完成之后，它对于数据的修改是永久性的，即使系统出现故障也不会丢失

一致性（Consistent）

在事务开始之前和事务结束以后，数据库数据的完整性没有被破坏

这表示写入的数据必须完全符合所有的预设规则，这包含数据的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到）

原子性、隔离性、持久性三个特性实现一致性

更新丢失（Lost Update）

两个或者多个事务，同时对一个数据进行修改，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新

更新丢失问题有两类

第一类更新丢失

事务A的回滚覆盖了事务B已提交的数据

第二类更新丢失

事务A的提交覆盖了事务B提交的数据

第一类更新丢失是错误，必须要避免。MySQL已经解决，不用担心

两个事务同时根据主键id更新数据时，一个事务会获取意向排它锁（IX）、排它锁（X锁）和记录锁（Record Lock），X锁是排他性的，当前事务没有提交或者回滚，其他事务不允许操作当前数据

第二类更新丢失并不是错误，需要根据具体业务情况来定。MySQL在默认隔离级别（RR）没有解决第二类更新丢失

如果业务允许，那就无所谓了

但是在有些业务系统中是必须要避免的。例如余额表中的金额。多个事务并发修改同一个用户的余额，如果扣款前进行判断余额是否满足扣款，结果由于并发扣款，可能导致最后余额存在负数，这是绝对不允许的

常见的方式有乐观锁、悲观锁或者设置当前事务的隔离级别为串行化

乐观锁

乐观锁本质上是无锁的方式

增加版本号或者时间戳字段（记录的修改时间），进行失败重试的方式保证更新操作的正确性

对表记录修改前先进行查询，然后更新数据时，带上WHERE判断条件版本号或者是时间戳。同时判断是否执行成功，如果没有成功说明其他事务对当前数据进行了修改，进行失败重试，当达到最大重试次数直接报错，结束运行

案例

建表语句

CREATE TABLE `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `amount` int(10) unsigned DEFAULT NULL, `version` int(11) DEFAULT '1', `create_time` datetime DEFAULT CURRENT_TIMESTAMP, `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

INSERT语句

INSERT INTO `account`(`id`, `name`, `amount`, `version`, `create_time`, `update_time`) VALUES (1, '张三', 100, 1, '2021-03-18 11:31:33', '2021-03-18 11:31:33');

开启事务，START;

先查询数据，SELECT * FROM account WHERE id = 1;

业务处理后准备更新余额

UPDATE account SET amount = 200, version = version + 1 WHERE id = 1 AND amount = SELECT字段的值 AND version = SELECT字段的值 AND update_time = SELECT字段的值;

UPDATE语句会返回0或者1。0表示失败，1表示成功

如果返回0，表示没有更新成功，有其他事务进行了修改，可以直接抛出异常，进行事务回滚。或者进行失败重试，直到达到最大重试次数后抛出异常，结束程序

如果返回1，表示更新成功，程序结束

悲观锁

显式加排它锁SELECT ... FOR UPDATE，锁住当前数据

这样在当前事务还没有提交前，其他事务不能读取该数据

串行化

直接修改当前会话的事务隔离级别为串行化，让并发的事务，串行执行

事务串行执行肯定能保证更新操作成功执行

如果并发激烈，建议使用悲观锁，减少锁的冲突；如果并发不激烈，建议使用乐观锁；对于串行化，不建议使用，效率太低

事务控制语句

SELECT @@global.tx_isolation, @@tx_isolation;

查询全局、以及当前会话的事务隔离级别

SET AUTOCOMMIT= 0;

0表示禁止自动提交，1表示自动提交事务

BEGIN;

显式地开启一个事务

COMMITT;

提交事务，并使已对数据库进行的所有修改成为永久性的

ROLLBACK;

回滚会结束用户的事务，并撤销正在进行的所有未提交的修改

SAVEPOINT identifier;

SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT

这对于复杂的大事务非常有帮助，例如批量新增1000万的数据，如果中间某一条失败，需要将1000万数据全部回滚吗

如果业务需求，只能全部回滚

如果不需要，可以设置一些保存点，将发生错误时，回滚到最近的保存点上，而不是回滚所有数据

RELEASE SAVEPOINT identifier;

删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常

ROLLBACK TO identifier;

把事务回滚到标记的保存点

SET TRANSACTION ISOLATION LEVEL 隔离级别;

修改事务隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ |SERIALIZABLE];

GLOBAL，设置全局事务隔离级别

SESSION，设置当前会话的隔离级别

如果GLOBAL和SESSION都不设置，表示修改的事务隔离级别将应用于当前session内的下一个还未开始的事务

事务隔离级别

分类

读未提交（Read uncommitted）

脏读/不可重复读/幻读都可能

读已提交（Read committed简称RC）

脏读不可能，不可重复读/幻读都可能

可重复读（Repeatable read简称RR）

不可重复读/脏读不可能，幻读都可能

可重复读的隔离级别下使用了MVCC机制，让读写不冲突，快照读的读视图相同，读取到的数据永远都是一样的

要避免幻读可以用间隙锁

串行化（Serializable）

脏读/不可重复读/幻读都不可能

MySQL中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到

脏读（Dirty Reads）

事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求

不可重读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做不可重复读

一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性

幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为"幻读"

查看事务隔离级别

show variables like 'transaction_isolation';

select @@transaction_isolation;

InnoDB存储引擎默认的事务隔离级别是Repeatable read（可重复读），同时通过MVCC + Next-Key Lock可以解决幻读问题

演示事务隔离级别

测试数据

建表SQL

-- UNSIGNED代表无符号数，不能是负数

CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, name varchar(20) DEFAULT NULL, balance decimal(10,2) unsigned DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据SQL

INSERT INTO `user` VALUES (1, '张三', 200), (2, '李四', 200);

读未提交，出现脏读

SQL执行流程

脏读违反了一致性，是必须要避免的

读已提交，避免脏读和出现不可重复读

只能够读到其他事务已经提交的事务，但是通过相同的SQL语句，多次读取可能读到的数据不相同。可能是其他事务执行了UPDATE和DELETE语句

SQL执行流程

出现不可重复读并不是错误，这需要在业务进行判定，业务上是否允许

Oracle数据库默认的隔离级别就是读已提交

可重复读，避免脏读、避免不可重复读和出现幻读

可重复读SQL执行流程

出现幻读SQL执行流程

事务A先查询所有数据，发现只有2条数据，准备插入id为3的数据

事务B插入id为3的数据，并且提交事务

事务A插入id为3的数据，报主键重复错误

这里出现了幻读，事务A查询的时候明明没有id为3的数据，但是插入数据的时候却报主键冲突

再次查询仍然没有id = 3的数据，这是可重复读机制（MVCC实现）

MySQL默认事务隔离级别为可重复读

想要解决幻读可以显示加锁SELECT ... LOCK IN SHARE MODE或者是SELECT ... FOR UPDATE

串行化，避免脏读、避免不可重复读和避免幻读

将写操作进行串行化处理，多个读操作可以同时进行，读写冲突，写写冲突，直到其中一个事务提交前

SQL执行流程

InnoDB存储引擎如何实现四大特性

原子性

undo log

隔离性

MVCC（依赖于undo log、read view、数据行的隐式字段（trx_id、回滚指针））、锁

读未提交

事务A还没有提交时，释放排它锁。事务B能够读取该数据

读已提交

事务A提交后，释放排它锁。事务B只能够读取到已提交的数据。同时read view在每次快照读重新生成一份，每次读取已提交的数据

可重复读

每次快照读复用第一次快照读生成的read view，因此每次读到的数据相同

串行化

对于每个写操作串行处理

持久性

redo log

一致性

通过原子性、隔离性、持久性实现一致性

关系型数据中的ACID与分布式理论中的CAP中，这两个C有什么区别

ACID中的C是一致性，在一个事务中，多次对数据进行操作后，数据仍然保证正确性，并不会凭空增加、减少或者消失

CAP中C也是一致性，主要指在分布式系统中，一份数据要在多个服务间进行同步。数据的同步是需要时间，复制数据可能失败，需要保证多个服务间数据的一致性。常见的有强一致性、弱一致性和最终一致性

二者目的不同，因而达到的效果也不同。ACID更强调的是单个数据的正确性、完整性，CAP更强调的是多个服务间数据的一致性

锁（重点）

数据库中的锁是为了保证并发访问时数据的一致性，使各种共享资源在被访问时变得有序而设计的一种规则

MySQL中关于锁的知识和事务隔离级别、索引、MVCC杂合在一起，显得非常乱。且锁的各种名词让人眼花缭乱

锁的分类

加锁机制

乐观锁

本质上是无锁的方式，总是乐观地认为不会发生锁冲突，如果发现更新失败，则进行失败重试，直到达到最大重试次数，回滚事务

根据版本号或者是时间戳控制

悲观锁

只要是加了锁都是悲观锁

锁定表或者行，让其他数据操作等待

读锁（共享锁）

针对同一份数据，多个读操作可以同时进行而不会互相影响

不能进行写操作

写锁（排他锁）

当前写操作没有完成前，它会阻断其他写锁和读锁

锁粒度

表锁

表锁是指对一整张表加锁。表锁由MySQL Server层实现

行锁

行锁是锁定某行、某几行或者行之间的间隙，由存储引擎实现，不同存储引擎实现不同。目前只有InnoDB存储引擎支持行锁，如没有特殊说明，行锁就是指InnoDB存储引擎的行锁

存储引擎

InnoDB存储引擎

行锁

兼容性

共享锁（Share Lock，简称S锁）

加了锁的记录，所有事务都能去读取但不能修改，同时阻止其他事务获得相同数据集的排他锁

排它锁（Exclusive Lock，简称X锁）

允许已经获得排他锁的事务去更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁

表锁

意向锁

读意向锁（IS锁）

写意向锁（IX锁）

获取共享锁、排它锁之前需要先获取意向共享锁、意向排它锁

MyISAM存储引擎

表共享读锁（Table Read Lock）

表独享写锁（Table Write Lock）

InnoDB锁详细解释

行锁

兼容性

共享锁（Share Lock，简称S锁）

普通的SELECT语句不会加共享锁

想要显式加共享锁，可以加SELECT ... LOCK IN SHARE MODE子句

为了确保自己查询的数据一定是最新的数据，不会被其他事务进行修改

被读取的行记录或者行范围其他SESSION可以读，也可以加共享锁，但是其他事务无法获取排它锁，也就是说S锁不冲突，S锁和X锁冲突

其他事务可以进行普通的SELECT、SELECT ... LOCK IN SHARE MODE，但是不能进行UPDATE、DELETE、INSERT操作

排它锁（Exclusive Lock，简称X锁）

普通的UPDATE、INSERT、DELETE语句都会加排它锁

想要对SELECT语句显式加排它锁，可以加SELECT ... FOR UPDATE子句，相当于UPDATE语句

保证读取到的数据一定是最新的，不允许其他事务进行修改

其他事务可以进行普通的SELECT，但是不能进行SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT操作

LOCK IN SHARE MODE和FOR UPDATE的相同和不同

相同

二者都可以读取到最新的数据，保证不让其他事务进行修改

可以让普通的SELECT读，UPDATE、DELETE和INSERT语句阻塞

不同

共享锁和共享锁不是互斥的，排它锁和排它锁、共享锁和排它锁是互斥的

两个事务同时进行LOCK IN SHARE MODE，且对锁住的数据执行UPDATE语句，会造成死锁

排它锁只有一个事务能进行FOR UPDATE，不会发生死锁

LOCK IN SHARE MODE适用于并发度低，且不会执行UPDATE锁住数据的场景，FOR UPDATE适用于并发度高，且执行UPDATE锁住数据的场景

锁模式

记录锁（Record Lock）

更新数据时根据索引进行更新。记录锁最简单的一种行锁形式，记录锁是加在索引上的

如果更新语句中WHERE过滤条件不走索引的话，那么它就会升级到表锁，最终造成效率低下，所以在写SQL语句时需要特别注意

间隙锁（Gap Lock）

当我们使用范围条件而不是相等条件去检索，并请求锁时，InnoDB就会给符合条件的记录的索引项加上锁

而对于键值在条件范围内但并不存在的记录，就叫做间隙，InnoDB在此时也会对间隙加锁，这种记录锁+间隙锁的机制叫Next-Key Lock

间隙锁是一个索引值的左开右开的区间

临键锁（Next-key Lock）

临键锁是记录锁与与间隙锁的结合，所以临键锁与间隙锁是一个同时存在的概念，并且临键锁是个左开右闭的区间

记录锁、间隙锁、临建锁之间的关系示意图

插入意向锁（Insert Intention Lock）

插入意图锁是一种间隙锁，在行执行INSERT之前的插入操作设置

如果多个事务INSERT到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突

锁模式下各种锁之间的兼容矩阵

第一行表示已有的锁，第一列表示要加的锁

插入意向锁不影响其他任何锁

间隙锁和Next-Key与插入意向锁冲突

间隙锁和除了插入意向锁之外的锁都不冲突

记录锁和记录锁冲突，记录锁和Next-key冲突，Next-key和Next-key冲突

表锁

读写意向锁

由于表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，必须要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）

当事务要在记录上加上行锁时，要首先在表上加上意向锁。这样判断表中是否有记录正在加锁就很简单了，只要看下表上是否有意向锁就行了，从而就能提高效率

意向锁是InnoDB自动加的，不需要用户干预

自增锁

AUTOINC 锁又叫自增锁（一般简写成 AI 锁），是一种表锁，当表中有自增列（AUTOINCREMENT）时出现。当插入表中有自增列时，数据库需要自动生成自增值，它会先为该表加 AUTOINC 表锁，阻塞其他事务的插入操作，这样保证生成的自增值肯定是唯一的

读意向锁、写意向锁、自增锁、共享锁、排它锁之间的兼容性

第一行是已存在的锁，第一列是想要获取的锁

总结

当不存在冲突时，InnoDB存储引擎并不会默认生成锁，而是当多个事务冲突后才会生成锁

表锁为意向锁，意向锁主要是为了简化表锁和行锁之间的逻辑，表锁是InooDB存储引擎自己加上的，一般不用关注

较为复杂的是行锁，行锁有两种模式，一种是S锁，一种是X锁。行锁的类型又可以细分成记录锁、间隙锁、临建锁等

如何描述一个行锁呢？现有锁的模式（共享、排他），然后有锁的类型。例如共享记录锁、排他记录锁

可以在information_schema.INNODB_LOCKS系统表中查看当前InnoDB存储引擎中存在的锁

lock_mode，表示锁模式，主要有S、X、IS、IX、GAP、AUTO_INC

lock_type，表示锁类型，主要有Record Lock、Next-key Lock、Insert Intention Lock

常见加锁场景分析

一般而言表使用的存储引擎都是InnoDB，下面所有案例都是InnoDB存储引擎，这里只描述行锁中的X锁

执行SQL后究竟加什么锁和事务隔离级别、索引、是否命中数据均存在关系

事务隔离剂级别取读已提交和可重复读

索引取主键索引、二级唯一索引、二级非唯一索引

具体场景分析

建表语句

CREATE TABLE `book` ( `id` int(11) NOT NULL, `isbn` varchar(255) DEFAULT NULL, `author` varchar(255) DEFAULT NULL, `score` double(2,1) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `isbn_unique_index` (`isbn`) USING BTREE, KEY `author_index` (`author`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

数据SQL

INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (10, 'N0001', 'Bob', 3.4); INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (18, 'N0002', 'Alice', 7.7); INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (25, 'N0003', 'Jim', 5.0); INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (30, 'N0004', 'Eric', 9.1); INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (41, 'N0005', 'Tom', 2.2); INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (49, 'N0006', 'Tom', 8.3); INSERT INTO `book`(`id`, `isbn`, `author`, `score`) VALUES (60, 'N0007', 'Rose', 8.1);

案例1：聚簇索引，查询命中

SQL执行流程图

UPDATE语句会获取X锁，同时根据主键id，命中会加记录锁

记录锁和记录锁之间是冲突的

加锁记录

由于Record Lock之间冲突，所以在information_schema.INNODB_LOCKS才能看到

在RC和RR隔离等级下的加锁，两种隔离等级下没有任何区别，都是对id = 10这个索引加排他记录锁

案例2：聚簇索引，查询未命中

SQL执行流程图

间隙锁和插入意向锁，如果插入的数据在间隙锁的区间内就冲突，否则不冲突

在RC隔离等级下，不需要加锁。而在RR隔离级别会在id = 16前后两个索引（(10, 18)）之间加上间隙锁

加锁记录

案例3：二级唯一索引，查询命中

SQL执行流程

在InnoDB存储引擎中，二级索引的叶子节点保存着主键索引的值，然后再拿主键索引去获取真正的数据行，所以在这种情况下，二级索引和主键索引都会加排他记录锁，无论是RC还是RR隔离级别

加锁记录

案例4：二级唯一索引，查询未命中

SQL执行流程

在RR隔离等级下未命中时的加锁情况，RC隔离等级下该语句未命中不会加锁。在 N0007 和 Suprenum Record 之间加了间隙锁

加锁记录

在SHOW EGINE INNODB STATUS的日志中出现了插入意向锁等待间隙锁

案例5：二级非唯一索引，查询命中

案例6：二级非唯一索引，查询未命中

案例7：无索引

案例8：聚簇索引，范围查询

案例9：二级索引，范围查询

案例10：修改索引值

案例11：DELETE语句加锁分析

案例12：INSERT语句加锁分析

行锁分析

show status like'innodb_row_lock%';

Innodb_row_ lock_current_wait

当前正在等待锁定的数量

Innodb_row_ lock_time

从系统启动到现在锁定总时间长度

Innodb_row_ lock_time_avg

每次等待所花平均时间

Innodb_row_ lock_time_max

从系统启动到现在等待最长的一次所花时间

Innodb_row_ lock_waits

系统启动后到现在总共等待的次数

死锁

Session _1执行：select *from account where i d= 1 for update; Session _2执行：select *from account where i d= 2 for update; Session _1执行：select *from account where i d= 2 for update; Session _2执行：select *from account where i d= 1 for update; 查看近期死锁日志信息：show engine innodb status;

MVCC（重点）

MVCC英文全称Multi-Version Concurrency Control，中文翻译多版本并发控制，是一种用来解决读 - 写冲突的无锁并发控制技术，同时也是解决事务中隔离性的关键

MVCC并不是为每个事务将数据全量复制一份，每个事务只读自己的数据，这样的效率太低，性能太差

InnoDB存储引擎为每个事务都分配一个自增的事务id。在事务中修改数据时，并不是覆盖修改原有数据，而是保持原有数据不变，生成一个新的数据。每次修改或者删除都产生一个新的版本（这就是多版本的含义）指向旧版本的数据，形成一个版本链。修改或者删除的数据版本与当时操作的事务id关联。Read View（读视图）决定当前事务能够读取的数据版本，它包含了多个事务id。根据读视图从数据版本链中选择合适的数据版本让当前事务读，这样即使其他事务在写，但是不会阻塞读请求，可以读取历史版本的数据，让读写不冲突

注意：begin/start transaction命令并不是一个事务的起点，执行UPDATE、DELETE、INSERT语句，事务才真正启动，才会向MySQL申请事务id。SELECT语句是不会分配事务id的。MySQL内部是严格按照事务的启动顺序来分配事务id的

数据库并发场景分为三种

读 - 读

不存在任何问题，也不需要任何并发控制

读 - 写

可能存在数据安全问题，因为一个事务在读，另外一个事务在写。从事务的隔离级别这个角度而言，可能产生脏读、不可重复读和幻读

写 - 写

可能存在数据安全问题，多个事务同时对同一个数据进行修改可能产生更新丢失问题，也就是第一类更新丢失和第二类更新丢失

MySQL数据库本身已经解决了第一类更新丢失

第二类更新丢失可以通过悲观锁或者乐观锁（版本号或者时间戳）进行解决

MVCC的作用

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写能力

同时还可以解决脏读、不可重复读、幻读事务隔离问题，但是不能解决第二类更新丢失问题。第二类更新问题需要使用悲观锁或者是乐观锁解决

小结一下：MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读 - 写冲突问题，而提出解决方案。所以在数据库中有了MVCC，可以形成如下两种组合

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

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

当前读和快照读

在学习MVCC之前需要先了解一下MySQL中InnoDB存储引擎下的当前读和快照读

当前读

像SELECT LOCK IN SHARE MODE（共享锁）、SELECT FOR UPDATE、UPDATE、INSERT、DELETE（排它锁）这些操作都是当前读

当前读读取的都是记录的最新数据版本，读取时需要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

快照读

快照读就是普通的SELECT，即不加锁的非阻塞读

快照读的前提是事务隔离级别不是串行，在串行级别下快照读会退化成当前读

快照读是为了提高并发度，让读写不冲突

快照读是基于MVCC实现，读到的是某一个版本的数据，既然是某一个版本的数据，就并不一定是最新的数据，可能是历史数据

案例

案例1

SQL执行顺序

在上表的顺序下，事务B的在事务A提交修改后的快照读是旧版本数据，而当前读是实时新数据400

案例2

SQL执行顺序

在这里的顺序中，事务B在事务A提交后的快照读和当前读都是实时的新数据400

快照读非常依赖于读操作首次出现的时机，它有决定该事务后续快照读结果的能力

MVCC实现原理

MVCC主要通过表数据中的3个隐式字段、undo log、Read View来实现

表数据3个隐式字段

每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID

DB_TRX_ID

6字节，最近修改（修改/插入）事务id，记录创建这条记录/最后一次修改该记录的事务id

DB_ROLL_PTR

7字节，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

DB_ROW_ID

6字节，隐含的自增id（隐藏主键），如果表没有主键，InnoDB会自动以DB_ROW_ID生成一个聚簇索引

删除flag隐藏字段

实际上还有一个删除flag隐藏字段，既记录被删除并不代表真的删除，而是删除flag变了

记录图示 

undo log

undo log主要分为两种insert undo log、update undo log

insert undo log

代表事务在insert新数据时产生的undo log，只在事务回滚时需要，并且在事务提交后可以被立即丢弃

update undo log

事务在进行update或者delete时产生的undo log

不仅在事务回滚时需要，在快照读时也需要，所以不能随便删除

只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

purge线程

从前面的分析可以看出，为了实现InnoDB的MVCC机制，更新或者删除操作都只是设置一下老记录的delete_bit，并不真正将过时的记录删除

为了节省磁盘空间，InnoDB有专门的purge线程来清理delete_bit为true的记录

为了不影响MVCC的正常工作，purge线程自己维护了一个read view（这个read view相当于系统中最老活跃事务的read view）

如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的

对MVCC有帮助的实质是update undo log，undo log实际上就是存在rollback segment中旧记录链

update undo log执行流程

比如persion表有一条记录，记录如下，name为Jerry，age为24岁，隐式主键是1，事务id和回滚指针，我们假设为NULL

现在来了一个事务1对该记录的name做出了修改，改为Tom

在事务1修改该行（记录）数据时，数据库会先对该行加排他锁

然后把该行数据拷贝到undo log中，作为旧记录，既在undo log中有当前行的拷贝副本

拷贝完毕后，修改该行name为Tom，并且修改隐藏字段的事务id为当前事务1的id，我们默认从1开始，之后递增，回滚指针指向拷贝到undo log的副本记录，即表示我的上一个版本就是它

事务提交后，释放锁

图示

又来了个事务2修改person表的同一个记录，将age修改为30岁

在事务2修改该行数据时，数据库也先为该行加锁

然后把该行数据拷贝到undo log中，作为旧记录，发现该行记录已经有undo log了，那么最新的旧数据作为链表的表头，插在该行记录的undo log最前面

修改该行age为30岁，并且修改隐藏字段的事务id为当前事务2的id，那就是2 ，回滚指针指向刚刚拷贝到undo log的副本记录

事务提交，释放锁

图示

从上面，我们就可以看出，不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表

undo log的链首就是最新的旧记录，链尾就是最早的旧记录（当然就像之前说的该undo log的节点可能是会purge线程清除掉，向图中的第一条insert undo log，其实在事务提交之后可能就被删除丢失了，不过这里为了演示，所以还放在这里）

Read View（读视图）

事务在进行快照读操作的时候产生的读视图，在该事务执行快照读的那一刻，会生成数据库系统当前对于事务的一个快照，记录并维护系统当前活跃事务的id

读视图并不是事务开始后就生成了，而是在进行快照读的时候才会产生，当前读（加排它锁）不会产生读视图

读视图的作用就是通过记录的事务id，在数据版本链中寻找一个合适的版本让当前事务读

在不同的隔离级别下，读视图也不相同

在可重复读隔离级别下，不管有多少个快照读，使用的都是第一个快照读进行时生成的读视图，读视图从始至终都不会发生变化

在读已提交隔离级别下，每进行一次快照读，都会产生一个读视图，也就是说每次快照读，读视图都不相同，读视图随着执行快照读而发生变化

综上所述，在读已提交隔离级别下，即使是相同的SELECT语句，由于读视图可能不同（每次产生新的读视图），读取到的数据可能不同；在可重复读隔离级别下，相同的SELECT语句，读视图一定相同（沿用第一次快照读产生的读视图），读取到的数据一定相同。这也是可重复读的含义，相同的SELECT语句，查询的数据一定相同

读视图由查询时所有未提交事务id数组（数组里最小的id为min_id）和已提交的最大事务id（max_id）组成。查询的数据结果需要和读视图做对比从而得到快照结果

事务id和事务是否提交的关系图

版本链的比对规则

trx_id并不是指当前session的事务id，而是版本链中数据行对应生成该数据的事务id

creator_trx_id：表示生成该ReadView的事务的事务id

如果是只读事务，那么该事务的事务id就是0

如果被访问版本的trx_id与creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问

当前事务自己修改的数据，对自己一定是可见的

如果落在绿色部分（trx_id < min_id），表示这个版本是已提交的事务生成的，这个数据是可见的

如果落在红色部分（trx_id > max_id），表示这个版本是由将来启动的事务生成的，是肯定不可见的

如果落在黄色部分（min_id <= trx_id <= max_id）那就包含两种情况

若trx_id在数组中，表示这个版本是由还没有提交的事务生成的，对当前事务不可见

若trx_id不在数组中，表示这个版本是已经提交的事务生成的，对当前事务可见

如果某个版本的数据对当前事务不可见，根据回滚指针，需要顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，直到找到版本链中的最后一条数据。如果最后一个版本也不可见，那意味着该条记录对该事务完全不可见，查询结果不包含该记录

对于删除的情况可以认为是update的特殊情况。会将版本链上最新的数据复制一份，然后将trx_id修改成操作的trx_id，同时在该记录的头信息（record header）里的（deleted flag）标记位写上true，意味着记录已被删除，不返回数据

整体流程

前面理解了隐式字段、undo log、Read View、当前读和快照读的概念后，整体来看MVCC的执行流程

假定存在5个session，其中三个写事务，两个读事务（默认事务隔离级别是可重复读）

三个写事务和其中一个读事务（select1）

SQL语句执行顺序

第一次快照读

select 1第一次进行快照读生成读视图readview[100, 200], 300。当前活跃事务id为100和200，已提交的最大事务id为300。min_id为100，max_id为300

此时undo log版本链。第三列是生成改行数据的事务id

版本链的比对规则

第一条数据的row的trx_id为300

min_id <= trx_id <= max_id同时trx_id不在未提交事务id的数组[100, 200]中，因此该条数据对当前事务可见，停止查找直接返回数据

查询到的数据就是lilei300

第二次快照读

由于是可重复读，读视图沿用第一次快照读的读视图readview[100, 200], 300

此时undo log版本链

版本链的比对规则

第一条也是最新的一条数据，trx_id为100。100在事务活跃数组[100, 200]中，属于事务未提交的修改，对当前事务不可见

根据回滚指针，找到第二条数据，trx_id为100。100在事务活跃数组[100, 200]中，属于事务未提交的修改，对当前事务不可见

根据回滚指针，找到第三条数据，trx_id为100。min_id <= 100 <= max_id同时trx_id不在未提交事务id的数组[100, 200]中，因此该条数据对当前事务可见，停止查找直接返回数据

第三次快照读

读视图仍然沿用第一次快照读生成的读视图，由于读视图不变，根据版本比对规则，到最后也会找到lilei300这条数据

在可重复读隔离级别下，三次快照读，无论其他事务是否提交事务，读到的数据均相同，满足了可重复读的语义，根本的原因是在RR隔离级别下，读视图均沿用第一次快照读生成的读视图

三个写事务和其中一个读事务（select2）

SQL语句执行顺序

undo log日志数据版本链

进行快照读生成读视图readview[200], 300。此时活跃事务id为200，已提交的最大事务id为300。min_id为200，max_id为300

版本比对规则

第一条数据trx_id为200，在未提交事务id数组[200]中，对当前事务不可见

顺着回滚指针找到第二条数据，trx_id为200，对当前事务同样不可见

顺着回滚指针找到第三条数据，trx_id为100，小于min_id200，属于已提交的事务，对当前事务可见，停止查找，返回lilei2

RC、RR级别下的InnoDB快照读有什么不同

在RC隔离级别下，是每个快照读都会生成并获取最新的Read View

在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是第一次快照读生成的Read View

正是由于在不同事务隔离级别下，Read View可能不同（活跃事务提交），造成读取的数据可能不同

总结

多个事务对同一条数据进行修改或者删除时，通过undo log形成数据版本链的链表，版本数据中有额外的隐式字段事务id，回滚指针。回滚指针指向老数据，形成链表，链表的头部就是最新的数据，尾部就是最老的数据

读视图就是对当前系统中所有事务进行一个快照，包含已提交和未提交事务，用来判断数据的可见性。读视图在RC和RR隔离级别生成时机不同

查找数据时，到undo log日志中进行查找，根据读视图，进行版本比对，判断可见性，找到合适的数据，进行返回

MVCC是避免读写冲突的关键技术，同时也是实现事务隔离性的关键技术

undo log、redo log和binlog（重点）

binlog

简介

binlog用于记录数据库执行的写入性操作（不包括查询）信息，以二进制的形式保存在磁盘中

binlog是MySQL的逻辑日志，并且由Server层进行记录，使用任何存储引擎的MySQL数据库都会记录binlog日志

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志

使用场景

在实际应用中， binlog的主要使用场景有两个，分别是主从复制和数据恢复

主从复制

在Master端开启 binlog ，然后将binlog发送到各个Slave端， Slave端重放binlog从而达到主从数据一致

数据恢复

通过使用mysqlbinlog工具来恢复数据

数据同步

例如canal监听mysql的binlog，然后将数据同步数据源中，例如将mysql数据导入到hive中

binlog刷盘时机

对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢

mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N

0：不去强制要求，由系统自行判断何时写入磁盘

1：每次commit的时候都要将binlog写入磁盘

N：每N个事务，才会将binlog写入磁盘

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值

但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能

binlog日志格式

binlog日志有三种格式，分别为STATMENT、ROW和MIXED

在 MySQL 5.7.7之前，默认的格式是STATEMENT，MySQL 5.7.7之后，默认值是ROW。日志格式通过binlog-format指定

STATMENT

基于SQL语句的复制（statement-based replication, SBR），每一条会修改数据的SQL语句会记录到binlog中

优点

不需要记录每一行的变化，减少了binlog日志量，节约了IO , 从而提高了性能

缺点

在某些情况下会导致主从数据不一致，比如执行sysdate() 、slepp() 等

ROW

基于行的复制（row-based replication, RBR），不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了

优点

不会出现某些特定情况下的存储过程、function、trigger的调用和触发无法被正确复制的问题

缺点

会产生大量的日志，尤其是`alter table` 的时候会让日志暴涨

MIXED

基于STATMENT和ROW两种模式的混合复制（mixed-based replication, MBR），一般的复制使用STATEMENT模式保存binlog ，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog

redo log

为什么需要redo log

事务四大特性之一为持久性，只要事务提交成功，那么对数据库的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态

最简单的做法就是每次事务提交时，将事务涉及到修改的数据页全部刷新到磁盘中

上述做法存在着严重的性能问题

InnoDB是以页为单位与磁盘进行交互，一个数据页大小为16kb，一个事务可能只修改一个数据页里面几个字节，如果将完整的数据页刷新到磁盘，太浪费资源

一个事务可能涉及到多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差

常见做法是修改数据时，先将数据读取到内存的缓冲池中，然后进行修改。数据在内存中被修改，与磁盘中相比就存在了差异，这种有差异的数据成为脏页

因此MySQL设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了（相对而言文件更小并且是顺序IO）

redo log基本概念

redo log包括两部分：一个是内存中的日志缓冲（redo log buffer），另一个是磁盘上的日志文件（redo logfile）

MySQL每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file

这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL（Write-Ahead Logging）技术

redo log刷写时机

用户空间（user space）下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过内核空间（kernel space）缓冲区（OS Buffer）

redo log buffer写入redo logfile实际上是先写入OS Buffer，然后通过系统调用fsync()将其刷到redo log file中

MySQL支持三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置，各参数值含义如下

0（延迟写）

1（实时写，实时刷）

2（实时写，延迟刷）

redo log记录形式

redo log和binlog的区别

文件大小

redo log的大小固定

binlog可通过参数max_binlog_size设置每个binlog文件的大小

实现方式

redo log是InnoDB引擎层实现的，并不是所有引擎都有

binlog是Server层实现，所有引擎都可以使用binlog日志

记录方式

redo log采用循环写的方式，当写到结尾时，会回到开头循环写日志

binlog通过追加的方式记录，当文件大小大于设定值后，后续日志会记录到新的文件上

使用场景

redo log适用于崩溃恢复（crash-safe）

binlog适用于主从复制和数据同步

undo log

一条UPDATE的SQL执行流程

MySQL常见优化手段（重点）

步骤： 1.开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并将它抓取出来; 2.EXPLAIN+慢SQL分析; 3.SHOW profile,查询SQL在MySQL服务器里面的执行细节和生命周期情况 4.具体优化

SHOW WARNINGS 在explain执行后执行，查看翻译后的sql

使用EXPLAIN关键字去查看SQL的执行计划

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

索引优化规则

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描 EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'; 使用索引 EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei'; 未使用索引

存储引擎不能使用索引中范围条件右边的列 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager'; 使用索引 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager'; 未使用索引

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句 EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager'; 只查询索引不用查询具体的数据，效率更高 EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

is null,is not null 也无法使用索引

like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

字符串不加单引号索引失效 EXPLAIN SELECT * FROM employees WHERE name = '1000'; EXPLAIN SELECT * FROM employees WHERE name = 1000;

or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的

in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内

union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表

order by如果根据多个值进行排序，那么排序方式必须保持一致，要么同时升续，要么同时降续，排序方式不一致不走索引

优化方式

优化数据库表结构的设计

字段的数据类型

不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer

数据类型的长度

数据库最终要写到磁盘上，所以字段的长度也会影响着磁盘的I/O操作，如果字段的长度很大，那么读取数据也需要更多的I/O, 所以合理的字段长度也能提升数据库的性能。比如用户的手机号11位长度，没必要用255个长度

表的存储引擎

分库分表

数据库参数配置优化

主从复制，读写分离

数据库编码: 采用utf8mb4而不使用utf8

字段名

MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。 一般所有表都要有id, id必为主键，类型为bigint unsigned,单表时自增、步长为1; 有些特殊场景下(如在高并发的情况下该字段的自增可能对效率有比价大的影响)id是通过程序计算出来的一个唯一值而不是通过数据库自增长来实现的。 一般情况下主键id和业务没关系的，例如订单号不是主键id，一般是订单表中的其他字段，一般订单号order_code为字符类型 一般情况下每张表都有着四个字段create_id,create_time,update_id,update_time, 其中create_id表示创建者id，create_time表示创建时间，update_id表示更新者id，update_time表示更是时间，这四个字段的作用是为了能够追踪数据的来源和修改 最好不要使用备用字段(个人观点), 禁用保留字，如 desc、range、match、delayed 等 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint (1 表示是，0 表示否), 任何字段如果为非负数，必须是unsigned。表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除 如果某个值能通过其他字段能计算出来就不需要用个字段来存储，减少存储的数据 为了提高查询效率，可以适当的数据冗余，注意是适当 强烈建议不使用外键, 数据的完整性靠程序来保证 单条记录大小禁止超过8k，一方面字段不要太多，有的都能上百，甚至几百个，另一方面字段的内容不易过大像文章内容等这种超长内容的需要单独存到另一张表

字段类型

字符类型

不同存储引擎对char和varchar的使用原则不同，myisam:建议使用国定长度的数据列代替可变长度。 innodb：建议使用varchar，大部分表都是使用innodb，所以varchar的使用频率更高

数值类型

金额类型的字段尽量使用long用分表示，尽量不要使用bigdecimal，严谨使用float和double因为计算时会丢失经度 如果需要使用小数严谨使用float，double，使用定点数decimal，decimal实际上是以字符串的形式存储的，所以更加精确，java中与之对应的数据类型为BigDecimal 如果值为非负数，一定要使用unsigned，无符号不仅能防止负数非法数据的保存，而且还能增大存储的范围 不建议使用ENUM、SET类型，使用TINYINT来代替

日期类型

根据实际需要选择能够满足应用的最小存储日期类型。 如果应用只需要记录年份，那么仅用一个字节的year类型。 如果记录年月日用date类型, date占用4个字节，存储范围10000-01-01到9999-12-31 如果记录时间时分秒使用它time类型 如果记录年月日并且记录的年份比较久远选择datetime，而不要使用timestamp,因为timestamp表示的日期范围要比datetime短很多 如果记录的日期需要让不同时区的用户使用，那么最好使用timestamp, 因为日期类型值只有它能够和实际时区相对应 datetime默认存储年月日时分秒不存储毫秒fraction，如果需要存储毫秒需要定义它的宽度datetime(6) timestamp与datetime 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。 都可以使用自动更新CURRENT_TIMESTAMP 对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出。 timestamp占用4个字节：timestamp所能存储的时间范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’ datetime占用8个字节：datetime所能存储的时间范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’ 总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。如果需要使用到时区就必须使用timestamp，如果不使用时区就使用datetime因为datetime存储的时间范围更大 注意： 禁止使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效多了，日期时间类型有丰富的处理函数，可以方便的对日期类型进行日期的计算 也尽量不要使用int来存储时间戳

是否为null

MySQL字段属性应该尽量设置为NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL 。 在MySql中NULL其实是占用空间的，“可空列需要更多的存储空间”：需要一个额外字节作为判断是否为NULL的标志位“需要mysql内部进行特殊处理”，而空值”“是不占用空间的。 含有空值的列很难进行查询优化，而且对表索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替null。 联表查询的时候，例如SELECT user.username, info.introduction FROM tbl_user user LEFT JOIN tbl_userinfo info ON user.id = info.user_id; 如果tbl_userinfo.introduction设置的可以为null, 假如这条sql查询出了对应的记录，但是username有值，introduction没有值，那么就不是很清楚这个introduction是没有关联到对应的记录，还是关联上了而这个值为null，null意思表示不明确，有歧义 注意：NULL在数据库里是非常特殊的，任何数跟NULL进行运算都是NULL, 判断值是否等于NULL，不能简单用=，而要用IS NULL关键字。使用 ISNULL()来判断是否为 NULL 值，NULL 与任何值的直接比较都为 NULL。 1) NULL<>NULL的返回结果是NULL，而不是false。 2) NULL=NULL的返回结果是NULL，而不是true。 3) NULL<>1的返回结果是NULL，而不是true。

实例

EXPLAIN SELECT * FROM tbl_user LIMIT 100000,2; EXPLAIN SELECT * FROM tbl_user u INNER JOIN (SELECT id FROM tbl_user ORDER BY id ASC LIMIT 10000,2) temp ON u.id = temp.id; id为主键，性能高于第一条全表扫描

where中如果有多个过滤条件，在没有索引的情况下将过滤多的写在前面，过滤少的写在后面

禁止使用select *，需要什么字段就去取哪些字段

不要使用count(列名)或 count(常量)来替代 count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL和非NULL无关。说明:count(*)会统计值为NULL 的行，而count(列名)不会统计此列为NULL值的行

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。避免使用存储过程、触发器

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户（这些操作基本上都是 CPU 处理内存中的数据比较运算）。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标

分库分表

主从同步

分区

优缺点

优点

和单个磁盘或者文件系统分区相比，可以存储更多数据 优化查询。在where子句中包含分区条件时，可以只扫描必要的一个或者多个分区来提高查询效率； 同时在涉及sum()和count()这类聚合函数的查询时，可以容易的在每个分区上并行处理，最终只需要汇总所有分区得到的结果 对于已经过期或者不需要保存的数据，可以通过删除与这些数据有关的分区来快速删除数据 跨多个磁盘来分散数据查询，以获得更大的查询吞吐量

分区方式

Range

基于属于一个给定连续区间的列值，把多行分配给分区。 这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义

CREATE TABLE employees ( id INT NOT NULL, NAME VARCHAR ( 30 ), hired DATE NOT NULL DEFAULT '2018-12-01', job VARCHAR ( 30 ) NOT NULL, dept_id INT NOT NULL ) PARTITION BY RANGE ( dept_id )( PARTITION p0 VALUES LESS THAN ( 6 ), PARTITION p1 VALUES LESS THAN ( 11 ), PARTITION p2 VALUES LESS THAN ( 16 ), PARTITION p3 VALUES LESS THAN ( 21 ) );

1）、当需要删除一个分区上的"旧的"数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用"ALTER TABLE employees DROP PARTITION p0；"来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如“DELETE FROM employees WHERE YEAR (separated) <= 1990；”这样的一个DELETE查询要有效得多。 2）、想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。 3）、经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY dept_id；”这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

List

设置若干个固定值进行分区，如果某个字段的值在这个设置的值列表中就会被分配到该分区。 适用于字段的值区分度不高的，或者值是有限的，特别是像枚举这样特点的列

CREATE TABLE employees (          id INT NOT NULL,          NAME VARCHAR ( 30 ),          hired DATE NOT NULL DEFAULT '2015-12-10',          job_code INT,          store_id INT ) PARTITION BY LIST ( store_id )( PARTITION pQY VALUES IN ( 3, 5, 6, 17 ), PARTITION pJN VALUES IN ( 1, 10, 11, 19 ), PARTITION pCH VALUES IN ( 4, 12, 14, 18 ), PARTITION pJJ VALUES IN ( 2, 9, 13, 16 ), PARTITION pGX VALUES IN ( 7, 8, 15, 20 ));

range columns

create table rc3 ( a int, b int ) partition by range columns(a, b) ( partition p01 values less than (0, 10), partition p02 values less than (10, 10), partition p03 values less than (10, 20), partition p04 values less than (10, 35) ); insert into rc3(a, b) values(1, 10);

hash分区

常规hash分区

常规hash分区使用的是取模算法，对应一个表达式expr是可以计算出它被保存到哪个分区中，N = MOD(expr, num)

线性hash分区

线性hash分区使用的是一个线性的2的幂运算法则

常规hash分区在管理上带来了的代价太大，不适合需要灵活变动分区的需求。为了降低分区管理上的代价，mysql提供了线性hash分区，分区函数是一个线性的2的幂的运算法则。同样线性hash分区的记录被存在那个分区也是能被计算出来的。线性hash分区的优点是在分区维护(增加、删除、合并、拆分分区)时，mysql能够处理的更加迅速，缺点是：对比常规hash分区，线性hash各个分区之间数据的分布不太均衡

key分区

按照key进行分区非常类似于按照hash进行分区，只不过hash分区允许使用用户自定义的表达式， 而key分区不允许使用用于自定义的表达式，需要使用mysql服务器提供的hash函数， 同时hash分区只支持整数分区，而key分区支持使用出blob or text类型外的其他类型的列作为分区键

partition by key(expr) partitions num; -- 不指定默认首选主键作为分区键，在没有主键的情况下会选择非空唯一键作为分区键 partition by key() partitions num; -- linear key partition by linear key(expr)

子分区

是分区表中对每个分区的再次分割，又被称为复合分区，支持对range和list进行子分区， 子分区即可以使用hash分区也可以使用key分区。 复合分区适用于保存非常大量的数据记录

create table ts ( id int, purchased date ) partition by range(year(purchased)) subpartition by hash(to_days(purchased)) subpartitions 2 ( partition p0 values less than (1990), partition p0 values less than (2000), partition p0 values less than maxvalue );

管理分区

-- 删除list或者range分区(同时删除分区对应的数据) alter table <table> drop partition <分区名称>; -- 新增分区 -- range添加新分区 alter table <table> add partition(partition p4 values less than MAXVALUE); -- list添加新分区 alter table <table> add partition(partition p4 values in (25,26,28)); -- hash重新分区 alter table <table> add partition partitions 4; -- key重新分区 alter table <table> add partition partitions 4; -- 子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的 alter table <table> add partition(partition p3 values less than MAXVALUE); -- range重新分区 ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE); -- list重新分区 ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));

数据库导入导出

数据库导出

打开cmd命令

打开到mysql文件夹下的bin目录

通过mysqldump来执行导出

命令：mysqldump -u root -p 数据库（class15） > 要导出的文件名如：（test.sql）

导出之后的文件会出现在bin目录下

sql文件导入

cmd打开到mysql的bin目录下

通过mysql -uroot -p 输入密码的形式进入到数据库中

选择数据库USE db_name;

执行导入命令：source d:\datafilename.sql后边路径是sql文件存放的物理路径