MySQL数据库

糖不焦

已于 2022-07-04 18:34:39 修改

阅读量647

点赞数

文章标签：数据库 mysql sql

于 2022-07-02 15:29:12 首次发布

本文链接：https://blog.csdn.net/qq_40667289/article/details/125286055

版权

MySQL数据库

MySQL核心技术内容

MySQL核心技术内容

相关数据库排名：https://db-engines.com/en/ranking
官网地址：https://www.mysql.com
属于Oracle旗下的产品
支持多线程、体积小、速度快、提供多语言支持、支持多种操作系统、为各种变成语言提供API语言、使用标准的SQL数据语言形式

基础

语法描述

<>：表示在语句中必须指定的数据对象，是不可缺少的
[]：表示可以根据需要进行选择，也可以不选
|：表示多个选项只能选择其一
{}：表示必选项

语句分类

DDL：数据定义语言，操纵表、数据库等相关：create、drop、alter等
DML：数据操纵语言，操纵数据相关：insert、delete、update、select等

数据库操作

创建数据库：

CREATE DATABASE [IF NOT EXISTS] <数据库名> [[DEFAULT] CHARACTER SET <字符集> | [DEFAULT] COLLATE <校对规则名>];
示例：
CREATE DATABASE IF NOT EXISTS TEST CHARACTER SET utf8;

查看数据库：

SHOW DATABASES [LIKE <数据库名>];
示例：
SHOW DATABASES LIKE TEST;

修改数据库：

ALTER DATABASE [数据库名] {[DEFAULT] CHARACTER SET <字符集> | [DEFAULT] COLLECT <校对规则名>};
示例：
ALTER DATABASE TEST CHARACTER SET utf8;

删除数据库：

DROP DATABASE [IF EXISTS] <数据库名>;
示例：
DROP DATABASE IF EXISTS TEST;

其他SQL：

USE DATABASE_NAME;  -- 切换数据库
SELECT DATABASE();  -- 查看数据库
DESC TABLE_NAME;  -- 查看表结构

字符集

一般使用utf8mb4字符集

本人不常用：

SHOW VARIABLES LIKE '%char%';  -- 查看字符集
SHOW CREATE DATABASE TEST;  -- 查看数据库创建语句

外部临时表

提升查询效率
在创建于语句加TEMPORARY，例如

CREATE TEMPORARY TABLE TEST2(ID INT(2));  -- 创建
INSERT INTO TEST2 SELECT * FROM TEST;  -- 使用
SELECT * FROM TEST2 GROUP BY * ORDER BY *;  -- 查询

备注：临时表不会出现在数据库中，且只会在当前session生效。

快速建表

CREATE TABLE A LIKE B;  -- 仅创建表结构
CREATE TABLE A SELECT * FROM B [WHERE ****];  -- 创建表结构和复制数据

海量数据转储

先修改原表名，再创建相同的表

RENAME TABLE A TO B;  -- 修改原表名
CREATE TABLE B LIKE A;  -- 仅创建表结构

清空表数据

-- DML；会留存记录，产生大量日志，可以回滚；执行后会返回结果（删除多少条）；仅是删除数据
DELETE FROM TABLE_NAME;
-- DDL；不留日志，无法回滚；执行后不会返回结果（仅返回0）；从结构上完全重置
TRUNCATE TABLE TABLE_NAME;

快速复制数据

INSERT IGNORE INTO ****;  -- 如果存在重复数据，则忽略，并返回0，否则返回成功条数
REPLACE INTO ****;  -- 如果存在重复数据，则更新，否则插入
INSERT INTO TEST2 SELECT * FROM TEST;  -- 不能再操作TEST表，会锁死TEST表；进操作小数据量（不超1w）

帮助文档

HELP 'CREATE%';
SELECT * FROM MYSQL.HELP_TOPIC WHERE NAME LIKE '%BETWEEN%';

数据类型

整数类型

TYPE	STORAGE(BYTES)	MINIMUM VALUE SIGNED	MAXIMUM VALUE SIGNED	MAXIMUM VALUE UNSIGNED
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607	16777215
INT	4	-2147483648	2147483647	4294967295
BIGINT	8	$2^{63}$	$2^{63}-1$	$2^{64}-1$

备注：

挑合适类型，不能一味选择int或者bigint
如果不存在负值，则指定无符号数
int(M)，M对于存储的最大值没有任何关系，只是在整数类型前补充0，显得整齐。

浮点数类型

TYPE	STORAGE(BYTES)	MINIMUM VALUE SIGNED	MINIMUM VALUE UNSIGNED	MAXIMUM VALUE SIGNED	MAXIMUM VALUE UNSIGNED
FLOAT	4	-3.40E+38	0和-1.18E-38	-1.18E-38	-3.40E+38
DOUBLE	8	-1.80E+308	0和-2.23E-308	-2.23E-308	-1.80E+308
DECIMAL(M,D)	M+2	同上	同上	同上	同上

备注：

会有精度损失
FLOAT和DOUBLE不指定精度时，由计算机硬件和操作系统决定；DECIMAL不指定精度，默认为(10,0)

日期和时间类型

TYPE	STORAGE(BYTES)	MINIMUM VALUE	MINIMUM VALUE	ZERO VALUE	FORMATE
YEAR	1	1901	2155	0	YYYY
TIME	3	-838:59:59	838:59:59	00:00:00	HH:MM:SS
DATE	3	1000-01-01	9999-12-31	0000-00-00	YYYY-MM-DD
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00	YYYY-MM-DD HH;MM;SS
TIMESTAMP	4	1970-01-01 00:00:01UTC	2038-01-19 03:14:07UTC	0000-00-00 00:00:00	YYYY-MM-DD HH;MM;SS
DATETIME

备注：

插入数据时可以使用日期或者字符串
任何标点符号都可用作日期部分或时间部分间隔符，固定使用标准格式
不建议使用YEAR和TIMESTAMP

-- timestamp可以在更新的时候自动更新
-- timestamp时间有限，因此5.6升级成了datetime
CREATE TABLE TIMETEST(
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`TIMESTAMP_COL` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`DATETIME_COL` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

字符串类型

TYPE	STORAGE(BYTES)	DESC
CHAR(m)	255	固定长度非二进制字符串
VARCHAR(m)	0~65535	变长非二进制字符串
TINYTEXT	$2^{8}-1$	非常小的非二进制字符串
TEXT	$2^{16}-1$	小的非二进制字符串
MEDIUMTEXT	$2^{24}-1$	中等大小的非二进制字符串
LONGTEXT	$2^{32}-1$	大的非二进制字符串
ENUM	65535	枚举类型
SET	64	一个集合，可以有0个或多个成员

枚举类型

值	索引
NULL	NULL
‘’	‘0’
‘A’	1
‘B’	2
‘C’	3

CREATE TABLE TIMETEST(
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`ENUM_COL` ENUM('A','B','C') DEFAULT NULL, --只能是字符串，最大长度65535
PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

集合类型

值	索引
NULL	NULL
‘’	‘0’、0
‘A’	1
‘B’	2
‘A’,‘B’	3
‘C’’	3

CREATE TABLE TIMETEST(
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`ENUM_COL` SET('A','B','C') DEFAULT NULL, --只能是字符串，最大长度64
PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;

备注：

枚举类型和集合类型用的少，一般用tinyint、int、char代替
会增加可读性

函数

长度和字符转换

-- 求长度
SELECT LENGTH();  -- 求占了多少字节
SELECT CHAR_LENGTH();  -- 仅求字符个数
-- 字符串转换，都只处理第一位
SELECT ASCII();
SELECT ORD();
-- ASCII转换回字符
SELECT CHAR();

字符串拼接和查找函数

-- 拼接，不能有空格
SELECT CONCAT('ARE', 'YOU', 'OK');
-- 分割符拼接
SELECT CONCAT_WS('*', 'ARE', 'YOU', 'OK');
-- 返回第一个字符串s在字符串列表（s1，s2……）中的位置；全匹配；null无法匹配
SELECT FIELD('A', 'CDE', 'ABC', 'MMM');
-- 查找字符串s1在集合中的位置，集合只能用逗号分隔
SELECT FIND_IN_SET('A', 'A,B,C');
-- 查找字符串位置；没有找到返回0，对于null返回null
SELECT LOCATE('AB', 'AB AND');
SELECT POSITION('AB' IN 'AB AND');

字符串截取类函数

-- 左右截取
SELECT LEFT('ARE', 1);
SELECT RIGHT('ARE', 1);
-- 任意位置截取
SELECT MID ('AREDC', 1, 3);

SELECT SUBSTR ('AREDC', 1, 3);
SELECT SUBSTR ('AREDC' FROM 1 FOR 3);
SELECT SUBSTR ('AREDC', 3);
SELECT SUBSTR ('AREDC' FROM 3);

SELECT SUBSTRING('AREDC', 1, 3);
SELECT SUBSTRING('AREDC' FROM 1 FOR 3);
SELECT SUBSTRING('AREDC', 3);
SELECT SUBSTRING('AREDC' FROM 3);
-- 正数返回前面的所有内容，复数返回后面的所有内容
SELECT SUBSTRING_INDEX('A*B*C*D', '*', 1);

字符串修改类函数

-- 替换字符串
SELECT REPLACE('ABVCDE', 'ABV', 'ABC');
SELECT INSERT('HELLO', 2, 2, 'AA')
-- 逆向
SELECT REVERSE('ABCD');
-- 大小写转换
SELECT LCASE('ABCD');
SELECT LOWER('ABCD');
SELECT UCASE('abcd');
SELECT UPPER('abcd');
-- 左右填充
SELECT LAPD('ABC', 6, '0');
SELECT RPAD('ABC', 6, '0');
-- 去空格
SELECT LTRIM('   ABC');
SELECT RTRIM('ABC   ');
SELECT TRIM('  ABC  ');
-- 数字格式化
SELECT FORMAT(1223.455677, 2);
-- 字符串插入
SELECT INSERT('HELLO', 2, 0, 'AA');
-- 大小对比
SELECT STRCMP('ABC', 'ABD');
-- 绝对值
SELECT ABS(-12);
-- 向上取整，返回大于等于的最小整数
SELECT CELL(1.3);
-- 向下取整，返回小于等于的最大整数
SELECT FLOOR(1.3);
-- 四舍五入和截取（精确到小数点后几位）
SELECT ROUND(1.234, 2);
SELECT TRUNCATE(1.234, 2);
-- 查询表的记录数
SELECT COUNT(*) FROM TEST;
SELECT COUNT(1) FROM TEST;
SELECT COUNT(COL_NAME) FROM TEST;
SELECT COUNT(DISTINCT COL_NAME) FROM TEST;
-- 最大最小值
SELECT GREATEST(COL_NAME1, COL_NAME2) FROM TEST;  -- 横向对比最大值
SELECT LEATEST(COL_NAME1, COL_NAME2) FROM TEST;  -- 横向对比最小值
SELECT MAX(COL_NAME) FROM TEST;  -- 纵向对比最大值
SELECT MIN(COL_NAME) FROM TEST;  -- 纵向对比最小值
-- 求和、平均值
SELECT SUM(COL_NAME) FROM TEST;
SELECT AVG(COL_NAME) FROM TEST;
-- 取余
SELECT MOD(10, 7);
-- 随机数
SELECT RAND();

日期函数

SELECT CURDATE();
SELECT CURRENT_DATE;  -- 同CURDATE
SELECT CURRENT_DATE();  -- 同CURDATE
SELECT CURTIME();
SELECT CURRENT_TIME;  -- 同CURTIME
SELECT CURRENT_TIME();  -- 同CURTIME
SELECT NOW();
SELECT NOW(6);  -- 同NOW
SELECT CURRENT_TIMESTAMP;  -- 同NOW
SELECT CURRENT_TIMESTAMP();  -- 同NOW
SELECT CURRENT_TIMESTAMP(3);  -- 同NOW
SELECT LOCALTIME;  -- 同NOW
SELECT LOCALTIMESTAMP;  -- 同NOW
SELECT LOCALTIME(3);  -- 同NOW
SELECT LOCALTIMESTAMP(6);  -- 同NOW
SELECT SYSDATE();  -- 不推荐使用
-- SYSDATE()是基于系统当前时间，其他的是语句执行时间
-- 加减天
SELECT ADDDATE(NOW(), 10);
SELECT SUBDATE(NOW(), 10);
-- 加减时间
SELECT ADDTIME(NOW(), '1:1:1');
SELECT SUBTIME(NOW(), '1:1:1');
-- 日期差
SELECT DATEDIFF(NOW(), '2021-12-12');
-- 时间差
SELECT TIMEDIFF(2021-12-13 00:00:00, '2021-12-12 00:00:00');
-- 月份差，加
SELECT PERIOD_DIFF(202112, 202111);
SELECT PERIOD_ADD(202112, 1);
-- 提取日期、时间、年月日时分秒、毫秒、季度、星期、周数
SELECT 
	DATE(NOW(6))			'日期', 
	TIME(NOW(6))			'日期',
	YEAR(NOW(6))			'年', 
	MONTH(NOW(6))			'月', 
	DAY(NOW(6))				'天', 
	HOUR(NOW(6))			'时', 
	MINUTE(NOW(6))			'分钟', 
	SECOND(NOW(6))			'秒', 
	MICROSECOND(NOW(6))		'毫秒', 
	QUARTER(NOW(6))			'季度', 
	WEEKDAY(NOW(6))			'星期几（0为星期一）', 
	YEARWEEK(NOW(6))		'年和周数';
-- 根据类型进行提取
SELECT EXTRACT(YEAR FROM NOW());
-- 计算日期为年、周、月的第几天
SELECT 
	DAYOFYEAR(NOW())		'当年的第几天',
	DAYOFWEEK(NOW())		'当周的第几天，周日为第一天',
	DAYOFMONTH(NOW())		'当月的第几天'，
	WEEK(NOW())				'当年的第几周，从0开始',
	WEKKOFYEAR(NOW())		'当年的第几周，从1开始';
-- 构建日期、构建时间
SELECT 
	MAKEDATE(2020, 50) 		'2020年第50天',
	MAKETIME(12, 23, 23)	'构建时间'；
-- 秒的换算
SELECT 
	SEC_TO_TIME(2000)		'秒换算成时分秒',
	TIME_TO_SEC('2:20:00')	'时分秒换算成秒';
-- 格式转换
SELECT STR_TO_DATE('2020-12-29 12:12:12.123', '%Y-%m-%d %H:%i:%s.%f');
SELECT DATE_FORMAT(NOW(6), '%Y-%m-%d %H:%i:%s.%f');

其他函数

-- 查找数据是否存在集合中
SELECT 1 IN (1,2,3);
-- 返回第一个非空数据
SELECT COALESCE(NULL, 'A', 'B');
-- 返回第一个大于n的索引位置，索引从0开始
SELECT INTERVAL(5, 1, 2, 3, 4, 5, 6, 7);
-- 流程函数
SELECT 
	IF (2 > 1, 'AAA', 'BBB'),
	IF (2 > 3, COL_NAME1, COL_NAME2)
FROM
	TEST LIMIT 1O;
SELECT IFNULL(COL_NAME1, COL_NAME2);
SELECT 10 DIV 3, 10/3;  -- 整除
SELECT CHARSER('A');  -- 查看字符集
SELECT 
	CONNECTION ID()		'当前连接ID',
	USER()				'当前用户',
	CURRENT_USR()		'当前用户',
	DATABASE()			'当前数据库';
SELECT FOUND_ROWS();  -- 查询上次查询的行数（不使用）
SELECT 
	ROW_COUNT()			'查询上次插入影响的行数（不使用）',
	LAST_INSERT_ID()	'查询上次插入查询的行数（不使用）';
-- 行转列
	GROUP_CONCAT([DISTINCT] 要连接得字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])
-- 加密和解密
	-- 对称加密
		SELECT AES_ENCRYPT('SJJSK', '密钥');
		SELECT AES_DECRYPT(AES_ENCRYPT('SJJSK', '密钥'), '密钥');
	-- HASH算法：
		SELECT 
			MD5('123456'),
			SHA('123456'),
			SHA1('123456'),
			SHA2('123456'),
			SHA2('123456', 224),
			SHA2('123456', 256),
			SHA2('123456', 384),
			SHA2('123456', 512),
			SHA2('123456', 0);
-- 压缩和解压
	SELECT COMPRESS('SLJKFJSLKFJL');
	SELECT UNCOMPRESS(COMPRESS('SLJKFJSLKFJL'));
-- 锁函数
	SELECT GET_LOCK('USER1', 10);  -- 加锁
	SELECT RELEASE_LOCK('USER1');  -- 释放锁
	SELECT IS_FREE_LOCK('USER1');  -- 是否释放锁
	SELECT IS_USED_LOCK('USER1');  -- 锁是否被使用

引擎

体系架构图

使用缓存，加快数据得读取；
使用缓冲，减少IO开销；
引擎怎么把数据存储在文件系统中；

存储引擎

在这里插入图片描述

查看和设置存储引擎

-- 查看引擎
SHOW ENGINES;  
SELECT * FROM INFORMATION_SCHEMA.ENGINES;
SHOW VARIABLES LIKE '%ENGIN%';

在这里插入图片描述

-- 设置引擎
	-- 仅对当前会话有用
SET SESSION DEFAULT_STORAGE_ENGINE=INNODB;
SET DEFAULT_STORAGE_ENGINE=MYISAM;
	-- 仅对新开得会话
SET GLOBAL DEFAULT_STORAGE_ENGINE=MYISAM;
-- 如果新开不会生效，需要修改配置后重启
linux->my.cnf或者window->my.ini

innoDB存储引擎

架构图

引擎特点

在这里插入图片描述

优化策略

在这里插入图片描述

InooDB的事务控制

具体执行逻辑
已update语句为例
① 事务处理请求后，根据条件找到该数据所在的页
② 将该页数据缓存到Buffer Pool当中
③ 执行sql语句，修改在Buffer Pool中的数据
④ 针对Update语句创建一个Redo Log对象，将这个对象存入Log Buffer当中
⑤ 针对Update语句创建一个Undo Log日志，用于事务回滚
⑥ 当事务提交时，将Redo Log对象进行持久化，后续使用其他机制将Buffer Pool中缓存的数据页持久化到硬盘当中
⑦ 如果事务回滚，则利用Undo Log日志进行事务的回滚
备注：如果不是往缓存写，则会产生随机写入磁盘；顺序写的效率，要远大于随机写。
为什么使用该方式实现事务控制
① 因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。
② 因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。
③ 更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。
Buffer pool？
是一块内存区域，当数据库操作数据的时候，把硬盘上的数据加载到Buffer pool，不直接和硬盘打交道，操作的是Buffer pool里面的数据
从一条sql的执行流程来详细了解Buffer Pool

MyISAM存储引擎

引擎特点

在这里插入图片描述

Memory存储引擎

引擎特点

在这里插入图片描述

Archive存储引擎

引擎特点

在这里插入图片描述
备注：不允许在最大值前，插入数据，例如数据库中有2条数据，id=1和10，插入的时候不能插入id=2~9之间的数据，只能追加数据

存储引擎的选择原则

在这里插入图片描述

索引

存储结构

逻辑存储结构

在这里插入图片描述

-- 查看逻辑存储结构
SHOW VARIABLES LIKE '%INNODB_FILE_PER_TABLE%';

物理存储结构

在这里插入图片描述

磁盘存取原理

在这里插入图片描述

线性查找（全表扫描）和二分法查找

二叉树、红黑树、BTree索引

二叉树

先找到物理地址之后，再去查找数据
当数据存在线性关系的时候，就会导致全表扫描，因此又使用到了红黑树

红黑树（平衡二叉树）

数据量越大则树的高度越高，检索速度越慢

BTree树

在这里插入图片描述

局部性原理和磁盘预读

在这里插入图片描述

B+Tree树

在这里插入图片描述
4. 可以进行范围查找，例如查找15~30之间的数据，根据指针就可以很容易找到

聚簇索引/聚集索引

在这里插入图片描述

非聚簇索引/非聚集索引

在这里插入图片描述

前缀索引

使用非int类型搞出来的索引：占用空间大、效率低
前缀索引：使用截取N位方式作为索引、对于TEXT和BLOB类型必须使用前缀索引、对于CHAR和VARCHAR根据字符串长度增加前缀索引

自增主键为什么比UUID好

在这里插入图片描述

索引使用原则

索引优缺点

优点：加快数据的查找、排序、分组速度
缺点：需要额外维护索引、占用额外的空间

索引分类

单列索引：主键索引（主键自动创建，非空且唯一）、唯一索引（值唯一但是允许为空）、普通索引（没有额外限制）
复合索引：允许在多个字段上创建索引
全文索引

索引创建原则

在这里插入图片描述

调优工具

-- 查看dml语句执行次数
SHOW GLOBAL STATUS;

explain调优工具

EXPLAIN sql语句;

id

id相同时从上往下，id不同时从下往上

select_type

SIMPLE——简单查询，不使用UNION或子查询
PRIMARY——子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记位PRIMARY
UNION——UNION中的第二个或后面的SELECT语句
DEPENDENT UNION——UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT——UNION的结果
SUBQUERY——子查询中的第一个SELECT语句，结果不依赖于外部查询
DEPENDENT SUBQUERY——子查询中的第一个SELECT语句，结果依赖于外部查询
DERIVED——派生表的SELECT，FROM子句的子查询
UNCACHEABLE SUBQUERY——一个子查询结果不能被缓存，必须重新评估外链接的第一行

table

表名

partitions

分区

possible_keys

可能使用到的索引

key

真正使用到的索引

key_len

索引长度

rows

扫描多少行

filtered

扫描了百分比数据

type

type时数据连接类型（join type），代表MySQL使用何种方式进行了数据查找，是性能调优的重要指标。
性能从高到底：system, const, eq_ref, fulltext, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL
参数说明：

system：只有MyISAM和MEMORY引擎的表，缺只有一行数据情况下使用system类型（使用不大）
const：使用唯一单列索引、唯一复合索引、单列主键索引、复合主键索引时，查询结果最多有1条记录时，使用const查询类型，其他数据库也叫唯一索引；使用复合索引时，必须完全覆盖
eq_ref：多表关联时，使用主键或非空唯一索引进行关联查询
ref：只要使用到了索引
fulltext：全文检索
ref_or_null：与ref类似，只是增加了null值得比较，使用的不多
index_merge：使用了2个以上得索引，最后取交集或并集，性能不一定比range好
unique_subquery：属于eq_ref的一个分支，用于where中的in形式子查询，子查询返回主键或唯一索引
index_subquery：属于eq_ref的一个分支，用于where中的in形式子查询，子查询返回存在重复值
range：索引范围扫描。常用于=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等
index：索引全表扫描，把索引从头到尾扫一遍
ALL：全表扫描

etra

额外的信息

using index：索引覆盖，无论查询过程或返回结果完全使用索引，而且没有回表查询，性能最好
using index condition：使用索引去尽量的过滤数据，然后再使用where条件中的其他条件去查询，其他条件也在索引中
using where：在查找使用索引的情况下，where条件中需要回表查询所需数据
using filesort：无法用索引来完成的排序，而是进行文件排序，性能低，需要优化
using temporary：使用临时表，性能低，需要优化

profiles工具

必须在命令行使用，客户端会观察不到

-- 查看参数
SHOW VARIABLES LIKE '%PROFILING%'
-- 开启
SET PROFILING=ON;
-- 显示query耗时
SHOW PROFILES;
-- 显示每一步的执行耗时，其中sending data占时长，是从整理到打包到发送给客户端整个流程
SHOW PROFILE FOR QUERY 1;

参数和索引调优

执行流程
在这里插入图片描述

最大允许数据包优化

数据包过大，导致报错
在这里插入图片描述

查询缓存优化

在这里插入图片描述

-- 查看查询次数和缓存查询次数
SHOW GLOBAL STATUS WHERE VARIABLE_NAME IN ('QCACHE_HITS', 'COM_SELECT');
-- 使用缓存查询
SELECT SQL_CACHE * FROM TABLE_NAME WHERE COL_NAME = '';
-- 不使用缓存查询
SELECT SQL_NO_CACHE * FROM TABLE_NAME WHERE COL_NAME = '';

主键调优

使用主键匹配查询是最快
使用主键范围查找
使用主键覆盖索引范围查询
使用主键依然可能全表扫描

复合索引最左前缀原则

-- 查看一个表得索引
SHOW INDEX FROM TABLE_NAME;
-- 创建索引
CREATE INDEX INDEX_NAME ON TABLE_NAME(COL_NAME, COL_NAME);

创建了一个复合索引A_B_C，相当于创建了A，A_B，A_B_C三个索引。所以最左是指，创建的复合索引，最左边的索引会生效，如果单独用B或B_C来查数据，索引是不生效的。

复合索引范围查找右侧索引失效

使用>, >=等范围查找，会使右侧的索引失效。这里的右侧是以索引的创建循序。

复合索引覆盖索引查询

尽量使用覆盖索引，减少对select *的使用，也不要查询不使用的字段。尽量查询结果都从索引字段中获取，目的是减少回表操作。

使用or语句导致索引失效

or语句只要有一个没有走索引（即索引的一部分）就会导致索引失效。

使用like语句可能导致索引失效

%仅加在最右侧才能使用到索引

根据区分度自动优化是否使用索引

相同列中，区分度大的数据走索引，区分度小的数据走全表扫描（主要是数据的分布i情况）

null数据的索引使用

本质上是符合区分度

in与not in

in会使用索引，not in不会使用索引，避免使用not in

多种索引自动优化

尽量使用复合索引少使用单列索引，当有多个索引可以使用的时候，数据库会选择一个最优的索引来使用。

隐式类型转换会导致索引失效

在进行字段对比，多表关联的时候，必须保证数据类型相同，否则会触发隐式的类型转换，可能会导致索引失效。

不等式会导致索引失效

同in与not in

索引字段上不要使用函数

SQL调优

大批量数据导入优化

使用load方法导入数据，数据尽量是有序的或者自增的。

-- load数据导入
LOAD DATA LOCAL INFILE '文件位置' INTO TABLE TABLE_NAME FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
-- 导入数据前关闭唯一性校验，数据导入完成后，再开启
SET UNIQUE_CHECKS = OFF;
SET UNIQUE_CHECKS = ON;
SET AUOTCOMMIT = OFF;
SET AUOTCOMMIT = ON;

insert优化

减少单条语句插入方式，反而使用批量方式。

order by优化

使用覆盖索引的方式进行查询和排序，利用索引的顺序性。
使用索引，必须索引全部升序或者全部降序。
排序时，也需要按照最左前缀原则。

Filsort排序算法优化

两次扫描算法（在4.1版本前存在）
- 读取所有满足条件的数据
- 在buffer中执行qsort排序
- 排完序后，再根据row pointer去读取相应的行数据
  每次排序都需要读2次表，而根据row pointer去毒镖往往都是随机离散读的，其开销非常大。
一次扫描算法
- 读取所需数据，包含sort key，row pointer和查询所需要访问的字段
- 根据sort key 排序按排序后的舒徐读取数据，由于sort_buffer_size中包含了所需要的字段，因此不需要再回表了，可以直接返回结果给客户端。
  仅需读取1次表，这种改进方法对sort_buffer_size的需求也大大增加。
使用sort_buffer_size和max_length_for_sort_data判断走一次还是二次扫描算法

-- 缓冲区
SHOW VARIABLES LIKE '%sort_buffer_size%';
-- 字段长度大小
SHOW VARIABLES LIKE '%max_length_for_sort_data%';

group by优化

group by是先进行排序，在进行分组，排序之后再使用一些聚合函数进行计算，所以同order by的优化原则。

子查询优化

使用表关联来代替子查询

limit优化

在进行分页查询使用。

使用子查询方式进行查询
使用大于/小于号来进行查询

SELECT * FROM TEST LIMIT 1999999, 10;
SELECT * FROM TEST A, (SELECT ID FROM TEST LIMIT 1999999, 10) B WHERE A.ID = B.ID;
SELECT * FROM TEST WHERE ID >= 1999999 AND ID < 1999999+10;

索引建议指令优化

-- 建议使用索引
SELECET COL_NAME FROM TABLE_NAME USE INDEX(INDEX_NAME);
-- 忽略使用索引
SELECET COL_NAME FROM TABLE_NAME IGNORE INDEX(INDEX_NAME);
-- 强制使用索引
SELECET COL_NAME FROM TABLE_NAME FORCE INDEX(INDEX_NAME);

分库分表

分库分表的原则和初衷

在这里插入图片描述

垂直分库表原理

在这里插入图片描述

垂直分表

备注：拆分热点和非热点数据，将热点数据放在一张表中，加快查询速度。改变了表结构。
垂直分库

备注：对政哥架构会产生影响，需要考虑分布式事务问题，需要业务进行更加细致的设计。
垂直分库分表：对于单表数据量没有减少

水平分库分表原理

水平分表

备注：取模、范围或者自定义进行水平分表
水平分库

总结：分库分表对于数据的关联查询、跨库分页和排序、事务一致性、数据迁移等有着重要的影响。

merge引擎分表

在这里插入图片描述

-- 创建TEST1表
CREATE TABLE `TEST1`
(
    `ID`   INT(10)     NOT NULL,
    `NAME` VARCHAR(50) NOT NULL
) ENGINE = MyISAM
  DEFAULT CHARSET = UTF8;
-- 创建TEST2表
CREATE TABLE TEST2 LIKE TEST1;
-- 创建插入方式为first方法的TEST_MERGE_FIRST表
CREATE TABLE `TEST_MERGE_FIRST`
(
    `ID`   INT(10)     NOT NULL,
    `NAME` VARCHAR(50) NOT NULL
) ENGINE = MRG_MyISAM
  DEFAULT CHARSET = UTF8
  INSERT_METHOD = FIRST
  UNION = (TEST1, TEST2);
-- 创建插入方式为last方法的TEST_MERGE_LAST表
CREATE TABLE `TEST_MERGE_LAST`
(
    `ID`   INT(10)     NOT NULL,
    `NAME` VARCHAR(50) NOT NULL
) ENGINE = MRG_MyISAM
  DEFAULT CHARSET = UTF8
  INSERT_METHOD = LAST
  UNION = (TEST1, TEST2);
-- 创建不定义插入方式的TEST_MERGE_DEFAULT表
CREATE TABLE `TEST_MERGE_DEFAULT`
(
    `ID`   INT(10)     NOT NULL,
    `NAME` VARCHAR(50) NOT NULL
) ENGINE = MRG_MyISAM
  DEFAULT CHARSET = UTF8
  UNION = (TEST1, TEST2);

可以往TEST1、TEST2、TEST_MERGE_FIRST、TEST_MERGE_LAST表中插入数据，但是不能在TEST_MERGE_DEFAULT插入数据；
TEST_MERGE_FIRST、TEST_MERGE_LAST和TEST_MERGE_DEFAULT都能进行查询、删除、更新操作
MRG_MyISAM引擎可以不关注底层
MRG_MyISAM引擎表可以使用相关索引进行查找

使用场景和优缺点分析

使用场景
优点
缺点
注意事项

分区表

在这里插入图片描述

-- 查看分区
SHOW PLUGINS;

HASH分区

-- 创建分区表
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
  PARTITION BY HASH (ID)  -- 自增主键用来分区（含有即可）
  PARTITIONS 4;  -- 分区数量
-- 查看分区表
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = database() AND PARTITION_NAME IS NOT NULL AND TABLE_NAME = 'TEST';

通过hash方式，数据分布均匀，但是对于范围查找性能较低
HASH (expr)，expr必须为一个返回整数得表达式，只能使用数字类型得字段

LINEAR HASH线性哈希分区

-- 创建分区表
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID)
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
  PARTITION BY LINEAR HASH (ID)  -- 自增主键用来分区（含有即可）
  PARTITIONS 4;  -- 分区数量

比HASH算法更加复杂
优点：增加、删除、合并和拆分分区更加快捷，有利于处理含有大量数据得表
缺点：数据分布不均匀

KEY分区和线性KEY分区

-- 创建KEY分区表
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID, NAME)  -- 联合主键
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
  PARTITION BY KEY (ID)  -- 指定要分区的列，必须为主键或非空唯一索引列（含有即可）
  PARTITIONS 4;

和HASH分区相似，使用HASH算法实现
支持除TEXT和BLOB之外的所有数据类型分区
不允许用户自定义表达式进行分区
当表中存在主键或者唯一键时，如果没有指定分区，则选择主键列分区，如果不存在主键列，则选非空唯一键列分区

  -- 创建线性KEY分区表
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID, NAME)
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
  PARTITION BY KEY (ID)  -- 指定要分区的列，必须为主键或非空唯一索引列（含有即可）
  PARTITIONS 4;

比KEY算法更加复杂
优点：增加、删除、合并和拆分分区更加快捷，有利于处理含有大量数据得表
缺点：数据分布不均匀

RANGE分区

  -- 创建线性RANGE分区表
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL AUTO_INCREMENT,
    `DATE` DATE        NOT NULL,
    `NAME` VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID, DATE)
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
    PARTITION BY RANGE (year(DATE)) ( -- 必须返回的是整数
        PARTITION P0 VALUES LESS THAN (1990),
        PARTITION P1 VALUES LESS THAN (2000),
        PARTITION P2 VALUES LESS THAN (2010),
        PARTITION P3 VALUES LESS THAN (2020),
        PARTITION P4 VALUES LESS THAN MAXVALUE);
-- 查询数据
SELECT * FROM TEST PARTITION (p0);
-- 查看各种信息
SELECT TABLE_NAME,
       PARTITION_NAME                '分区名字',
       SUBPARTITION_NAME             '子分区名字',
       PARTITION_ORDINAL_POSITION    '分区顺序',
       SUBPARTITION_ORDINAL_POSITION '子分区顺序',
       PARTITION_METHOD              '分区方法',
       SUBPARTITION_METHOD           '子分区方法',
       PARTITION_EXPRESSION          '分区表达式',
       SUBPARTITION_EXPRESSION       '子分区表达式',
       PARTITION_DESCRIPTION         '分区描述',
       TABLE_ROWS                    '数据行数'
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND PARTITION_NAME IS NOT NULL
  AND TABLE_NAME = TEST;

优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()这类函数进行优化选择
是常用的分区选择

LIST分区

-- 创建线性List分区表
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL AUTO_INCREMENT,
    `DATE` DATE        NOT NULL,
    `NAME` VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID, DATE)
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
    PARTITION BY LIST (year(DATE)) ( -- 必须返回的是整数
        PARTITION P0 VALUES IN (1999, 2000, 2001), -- 使用IN表示分区范围
        PARTITION P1 VALUES IN (2020, 2023));
-- 查询数据
SELECT * FROM TEST PARTITION (p0);

只是用来分区离散值
list分区使用VALUES IN ，所以每个分区的值都是离散的，只能是定义的值
如果插入的数据不在分区内，会报错，不能入表
适合插入一些明确字段的数据，例如执行成功/失败的数据
当数据发生变化后，则会自动改变分区

COLUMNS分区

-- 创建线性COLUMNS分区表（单字段）
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL AUTO_INCREMENT,
    `DATE` DATE        NOT NULL,
    `NAME` VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID, DATE)
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
    PARTITION BY RANGE COLUMNS (DATE)(
        PARTITION P0 VALUES LESS THAN ('1980-10-01'),
        PARTITION P1 VALUES LESS THAN ('1990-10-01'),
        PARTITION P2 VALUES LESS THAN ('2000-10-01')
        );
-- 创建线性COLUMNS分区表（多字段）
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL,
    `DATE` DATE        NOT NULL,
    `NAME` VARCHAR(50) NOT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
    PARTITION BY RANGE COLUMNS (ID, DATE)(
        PARTITION P0 VALUES LESS THAN (1000, '1980-10-01'),
        PARTITION P1 VALUES LESS THAN (2000, '1990-10-01'),
        PARTITION P2 VALUES LESS THAN (3000, '2000-10-01'),
        PARTITION P3 VALUES LESS THAN (MAXVALUE , MAXVALUE )
        );

分区条件：可以直接使用非整形的数据
执行的类型有：所有整形除float和decimal、部分日期仅date和datetime其他不支持、所有字符类型除blob和text
RANGE COLUMNS分区可以对多个列的值进行分区（很少使用，增加分区复杂性）
可以不使用主键
当使用多字段分区时，优先使用第一个字段进行分区

子分区、复合分区

在这里插入图片描述

创建的子分区数量相同
每个子分区的名字不能重复，必须是唯一的
如果一个分区拆分了子分区，必须所有的分区都拆分子分区
子分区名字不指定的话，就是分区名+sp0/1/2

-- 创建子分区（第一种方式）
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL,
    `DATE` DATE        NOT NULL,
    `NAME` VARCHAR(50) NOT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
    PARTITION BY RANGE (year(DATE))
        SUBPARTITION BY HASH (year(DATE))
        SUBPARTITIONS 2
        (
        PARTITION P0 VALUES LESS THAN (1980),
        PARTITION P1 VALUES LESS THAN (1990),
        PARTITION P2 VALUES LESS THAN (2000),
        PARTITION P3 VALUES LESS THAN (MAXVALUE)
        );
-- 创建子分区（第二种方式）
CREATE TABLE `TEST`
(
    `ID`   INT(10)     NOT NULL,
    `DATE` DATE        NOT NULL,
    `NAME` VARCHAR(50) NOT NULL
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8
    PARTITION BY RANGE (year(DATE))
        SUBPARTITION BY HASH (year(DATE))
        (
        PARTITION P0 VALUES LESS THAN (1980)(
            SUBPARTITION S1,
            SUBPARTITION S2
            ),
        PARTITION P1 VALUES LESS THAN (1990)(
            SUBPARTITION S3,
            SUBPARTITION S4
            ),
        PARTITION P2 VALUES LESS THAN (2000)(
            SUBPARTITION S5,
            SUBPARTITION S6
            ),
        PARTITION P3 VALUES LESS THAN (MAXVALUE)(
            SUBPARTITION S7,
            SUBPARTITION S8
            )
        );

分区表的查询性能

-- 查看当前数据库分区表信息（全部）同上面的部分信息
SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = database() AND PARTITION_NAME IS NOT NULL;
-- 按照单分区查
SELECT * FROM TEST PARTITION (p0);
-- 按照多分区查
SELECT * FROM TEST PARTITION (p0, P1, P2);

添加RANGE分区

-- 新增分区
ALTER TABLE TEST ADD PARTITION (PARTITION P6 VALUES LESS THAN (2020));

一般不写MAXVALUE，这样就会导致大于最大值后，数据全部分布到最后一个分区

调整哈希分区数量

-- 调整分区数量
ALTER TABLE TEST PARTITION BY HASH ( ID ) PARTITIONs 5;
ALTER TABLE TEST COALESCE PARTITION 3;  -- 合并3个分区，假设原来5个分区，执行后，就剩下2个分区，不建议用

会自动将数据分配到调整后的分区

分区合并拆分重组

可以将多个分区合并成一个，也可以将一个分区拆成多个

-- 将P3分区拆成两个
ALTER TABLE TEST REORGANIZE PARTITION P3 INTO (
    PARTITION P31 VALUES LESS THAN (2010),
    PARTITION P32 VALUES LESS THAN (MAXVALUE)
    );
-- 将多个分区合成一个：仅能使用最大分区的值
ALTER TABLE TEST REORGANIZE PARTITION P1, P2 INTO (
    PARTITION P31 VALUES LESS THAN (2000),
    );
-- 将3个分区拆成2个分区：必须是连续分区
ALTER TABLE TEST REORGANIZE PARTITION P1, P2, P3 INTO (
    PARTITION P31 VALUES LESS THAN (2000),
    PARTITION P31 VALUES LESS THAN (2010)
    );

截断分区

-- 常用来数据清理，适合用来清理不用分区的数据
ALTER TABLE TEST TRUNCATE PARTITION P0;
ALTER TABLE TEST TRUNCATE PARTITION P0, P1;

分区分析检查优化重建

-- 分析分区：读取和存储分区中值的分布情况
ALTER TABLE TEST ANALYZE PARTITION P1, P2;
-- 检查分区：检查分区中是否存在错误
ALTER TABLE TEST CHECK PARTITION P1, P2;
-- 修复分区：修复被损坏的分区
ALTER TABLE TEST REPAIR PARTITION P1, P2;
-- 优化分区：相当于执行了上面3个，主要用于回收空闲空间和分区的碎片整理
ALTER TABLE TEST OPTIMIZE PARTITION P1, P2;

普通表转为分区表

-- 转换成hash分区表，其他的类似
ALTER TABLE TEST PARTITION BY HASH ( ID ) PARTITIONS 4;

优缺点对比及分库分表优劣

分区表优点
①与单个磁盘或文件系统分区相比，可以存储更多的数据
②很容易就能删除不用或者过时的数据，只需要删除指定的分区就可以了
③一些查询可以得到极大的优化，因为扫描的数据量更小
④涉及到SUM()/COUNT()等聚合函数时，可以并行进行
⑤IO吞吐量更大，因为拆分为多个文件
⑥分区表对业务透明，只需要维护一个表的数据结构。
⑦DML操作加锁仅影响操作的分区，不会影响未访问分区。
⑧通过TRUNCATE操作快速清理特定分区数据。
⑨通过强制分区仅访问特定分区数据，减少操作影响。
⑩通过大数据量分区能有效降低索引层数，提高查询性能。
分区表缺点
①DDL操作需要锁定所有分区，导致所有分区上操作都被阻塞。
②当表数据量较小时，分区表和非分区表性能相近，可能非分区表性能更高。
③当表数据量较大时，对分区表进行DDL或其他运维操作难度大风险高。
④当单台服务器性能无法满足时，对分区表进行分拆的成本较高，而分库分表能很容易实现横向分拆。
⑤当分区表操作不当导致访问所有分区时，会导致严重的性能问题，而分库分表操作不当仅影响访问的表。
⑥使用分库分表可以有效运维降低运维操作影响，对1亿数据量表做DDL操作需要谨慎评估，而对10万数据量表做DDL操作可以默认其很快完成。
⑦使用分库分表可以有效减小宕机或其他故障影响，将数据分库分表到10套群集上，一套群集发生故障仅影响业务的一成。

高可用和安全（暂时性了解）

5种高可用架构模式

1、主备架构

在这里插入图片描述

主备模式主要是为了实时备份数据，防止数据丢失。
解决了冷备份导致的备份数据不全，不及时的问题。
主备模式可以同城部署、也可以异地部署达到容灾的目的。

缺点：

备份库不提供任何服务，始终处于闲置状态，对资源是一种浪费。
当主库发生故障时，无法自动故障转移。不能将备份库切换为主库，如果要这样做必须DBA手动处理。

2、一主一从架构

在这里插入图片描述

一个主库一个从库
主从架构师一种读写分离架构，与主备架构不同的是，从库依然可以对外提供服务，但是只能提供读服务。
对于读多写少的情况，可以大大的降低主库的压力。
主库一般只用于写，但是一些实时性要求较高的业务，也可以强制读取主库。

3、一主多从架构

在这里插入图片描述

—个主库多个从库
每个从库都可以支持读操作，均从主库同步数据，从而进一步分摊数据库压力

4、主从从架构

在这里插入图片描述

一个主库、多个从库，但是并不是所有从库都从主库同步数据。而是一个从库A从主库同步数据.而其它从库从这个从库A继续同步数据。
目的是降低主库同步的压力，同时也提供多个从库的读能力，分摊系统压力。

5、主主/双主/互为主从架构

在这里插入图片描述

两个数据库服务器互为主从架构，两个数据库节点都可以支持数据的读写操作。
重点是两个节点数据的主键不能够冲突，一点出现数据不一致将很难恢复。

3种数据复制策略

1、异步复制

MySQL默认的数据同步策略就是异步同步。
主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理。
优点:响应速度最快，客户端延迟最低
缺点:一旦数据数据还未同步到从库，而主库宕机，就会导致从库数据丢失。当把从库切换为主库的时候，就会存在问题。

2、全同步复制

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。
优点:主从数据保持严格的一致性，避免了数据一致性问题。
缺点:因为需要等待所有从库执行完该事务才能返回，所以客户端响应速度变慢，性能下降。尤其从节点有很多的时候，性能下降就会更加的严重。

3、半同步复制

介于全同步复制与全异步复制之间的一种策略，主库只需要等待至少一个从库节点收到并且FlushBinlog到Relay Log文件即可，主库不需要等待所有从库给主库反馈。
同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间。

读写分离架构的产生和原理

在这里插入图片描述

为什么：降低单个MySQL服务压力，分摊压力
原理：将写请求和读请求分离；将写请求全部由master节点完成；所有的读请求由salve节点完成
方法：应用程序实现分发算法，比如JDBC驱动种完成；增加数据库代理节点，由代理节点负责读写请求的分发算法实现
问题：主从延迟、主从数据不一致的问题

MySQL主从同步的原理

在这里插入图片描述

主从同步延迟

现象：主库已经存在数据，而从库还未产生相应的变化；主从库在一段时间内数据不一致
原因：解决方案
① 网络延迟问题：部署在同一个机房或者拉专线
② 机器性能差别，从库性能偏低：提升从服务库性能，或者优于主库
③ 读写压力差别大：应用层面进行限流或者增加分库数量分摊压力
④ 写入时间较长，备库入库时间就会晚：大事务拆分成小事务
⑤ SQL Thread在重放日志的时候，使用的是单现成也会导致延迟问题：MTS——Multi-Thread Slave
⑥ 从库在进行数据同步的时候，由于存在并行查询服务，可能会产生锁的争抢：没有办法

SHOW VARIABLES LIKE '%SLAVE%'

MTS是从5.6版本后支持的。
5.6版本支持的是：库级别的同步——global.slave_parallel_type = ‘DATABASE’
在这里插入图片描述
5.7版本支持的是：
① 行级别的同步——global.slave_parallel_type = ‘LOGICAL_CLOCK’
——global.slave_parallel_type = ‘DATABASE’（默认值）
② coordinater+workers模型：规则1更新同一个数据中的多个事务，必须提交给同一个worker执行。规则2同一个事务不同SQL语句，不可以分发给不同的worker。规则1/2是为了避免数据错乱。
③ 组提交
在这里插入图片描述

数据库管理安全

加密存储
① 对于密码字段必须进行加密存储
② 对于重要敏感字段，如果想避免开发人员查看，也需要加密存储。
可以使用MySQL自带的加密函数（效率较高)
可以在应用程序中进行加密和解密(效率较低)
③ 注意:加解密会带来额外的性能开销，所以只针对必要字段加解密即可。
数据脱敏
① 对于客户信息要注意脱敏，例如客户姓名、手机号、银行卡号、详细住址等。
② 脱敏方式可以在SQL中脱敏，也可以在应用程序脱敏，但是不可以在前端页面脱敏（不安全)。
③ 脱敏样例：187*8787（手机号），37678989（银行号）
④ 对于数据报表、数据导出类功能，一定要警惕脱敏问题。
⑤ 对于包含客户敏感信息，但是不需要脱敏的功能，一定要准确记录访问轨迹、导出记录，以便追查。
数据权限
① 防止用户跨资权限访问数据，例如我是不同的管理部门用户只能查看自己的数据。但是我们在接口设计中，往往缺少这种考虑。
② 例如一个用户信息查询接口，只可以查询用户自己的用户信息，然而的接口设计为让前端传递用户ID给后端，后端根据ID进行查询，一旦ID被串改，则可以查询到其他的用户信息。
③ 这种情况比比皆是，所以对于用户信息一定只能依赖于服务端存储的当前登录状态的用户信息，而不能使用服务端携带的用户信息。例如可以使用cookie、token等。

数据库开发安全

权限分离
① 必须只有DBA具有DDL、DCL权限，而开发人员只具有DML权限。开发人员如果需要执行DDL语句，则需按照发布流程先准备DDL脚本，然后提交给DBA执行。
② 必须应用程序连接数据库的用户为专有用户，IP地址设置白名单，也就是只允许固定地址的应用程序访问数据库。
③ 必须应用程序连接数据库的用户为专有用户，IP地址设置白名单，也就是只允许固定地址的应用程序访问数据库。
④ 建议设立只读运维账号，用于开发人员查证数据问题，当需要修正数据时，准备DML脚本提交给DBA执行。
⑤ 建议限制运维账号的执行权限，例如不可以做没有where条件的全表删除、全表更新
堡垒机
① 堡垒机提供了安全运维和安全审计两大功能，用户想要访问和操作任何服务器，都必须通过堡垒机进行，有效的起到了安全隔离作用。
② 堡垒机会可以记录用户所有的操作行为，linux环境下命令的运行，windows环境下的操作录屏。
SQL审核平台
① SQL审核平台是专门用于数据库发布、数据运维、审批的管理平台，可以让系统发布、数据运维更高效。
② 开发人员、运维人员将要执行的DDL、DML语句上传或录入平台，提交审核。
③ 由相应的管理人员、DBA进行审核，审核通过后则执行，审核不通过则打回修改。
④ SQL审核平台支持数据备份、数据回滚、风险语句拦截、消息通知等功能。
参考：Yearning开源平台（http://yearning.io/）、Archery开源平台（https://github.com/hhyo/Archery）

MySQL日志（后续真正用到再补充）

MySQL的7种日志

error log——mysql服务启动、停止、运行中产生的错误
general log——客户端连接和执行的所有语句——一般不开启
slow log——执行时长超过固定时间的语句，查询语句
binlog——二进制日志
relaylog——中继日志（主从同步中使用）
undolog——存储多个版本的行数据：undo log用于解决事务的原子性。
redolog——事务提交、回滚相关，保证数据的一致性、持久性、奔溃恢复

binlog和redolog结合解决数据库崩溃恢复问题
redolog和relaylog是InnoDB引擎独有的日志

error 和general日志

error log日志：如果在服务运行期间，被关闭就不回生成，因为它仅记录服务的启动、停止、运行中的错误
general log日志：如果是客户端连接，会记录下相关的连接、执行的sql等其他日志

slow 日志

-- 查询配置
SHOW VARIABLES LIKE '%slow_query%';
-- 查询多长算慢
SHOW VARIABLES LIKE '%long_query_time%';

记录顺序：不一定是先执行先记录，是在select执行完毕后，释放所有资源后，开始记录的
日志分析：mysqldumpslow ***slow.log

binlog日志

记录范围
记录的是所有引起数据变化的语句：insert、update、delete
记录的是可能引起数据变化的语句：update、delete（没有匹配到数据）
不回记录select、show，因为不会产生数据变化
记录时机
有事务：事务执行中，先写入缓冲；事务提交的时候，一次性写到日志中
无事务：执行完成后立即写入到日志
存放位置

-- 查询binlog配置
SHOW VARIABLES LIKE '%binlog%';
-- 文件相关位置
SHOW VARIABLES LIKE '%log_bin%';

日志分析：mysqlbinlog ***bin.log

redolog日志

和binlog日志结合，避免crash safe
该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘,发生错误时, 进行数据恢复使用，从而保持持久性。

undolog日志

undo log记录的是逻辑日志(即每一步执行的是什么样子的操作)。
当delete一条记录时，undo log中会记录一条对应的insert记录，当update一条记录时，undo log它记录一条对应相反的update记录(即执行update之前什么样子的操作)；当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚，这样就保证了事务的原子性。
Undo log销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC(多版本并发控制)。
Undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的 rollback segment回滚段中，内部包含1024个undo log segment。