1 汇总
1.1 [1]函数：7类
00.函数
单行函数 字符函数、数值函数、日期函数、转换函数、通用函数
多行函数/聚合函数/数字函数/组函数 从一组记录中返回一条记录，可出现在select列表、ORDER BY和HAVING子句中
01.字符串函数
CONCAT(): 连接两个或多个字符串
LENGTH(): 返回字符串的长度
SUBSTRING(): 从字符串中提取子字符串
REPLACE(): 替换字符串中的某部分
LOWER() 和 UPPER(): 分别将字符串转换为小写或大写
TRIM(): 去除字符串两侧的空格或其他指定字符
02.数值函数
ABS(): 返回一个数的绝对值
CEILING(): 返回大于或等于给定数值的最小整数
FLOOR(): 返回小于或等于给定数值的最大整数
ROUND(): 四舍五入到指定的小数位数
MOD(): 返回除法操作的余数
03.日期和时间函数
NOW(): 返回当前的日期和时间
CURDATE(): 返回当前的日期
CURTIME(): 返回当前的时间
DATE_ADD() 和 DATE_SUB(): 在日期上加上或减去指定的时间间隔
DATEDIFF(): 返回两个日期之间的天数
DAY(), MONTH(), YEAR(): 分别返回日期的日、月、年部分
04.汇总函数
SUM(): 计算数值列的总和
AVG(): 计算数值列的平均值
COUNT(): 计算某列的行数
MAX() 和 MIN(): 分别返回列中的最大值和最小值
GROUP_CONCAT(): 将多个行值连接为一个字符串
05.逻辑函数
IF(): 如果条件为真，则返回一个值；否则返回另一个值
CASE: 根据一系列条件返回值
COALESCE(): 返回参数列表中的第一个非 NULL 值
06.格式化函数
FORMAT(): 格式化数字为格式化的字符串，通常用于货币显示
07.类型转换函数
CAST(): 将一个值转换为指定的数据类型
CONVERT(): 类似于CAST()，用于类型转换
1.2 [1]子句：9个
01.子句
1.DISTINCT子句
2.SELECT子句
3.WHERE子句
3.WHERE子句AND、OR
4.GROUP BY子句
4.GROUP BY 聚合函数/数字函数
5.HAVING子句
6.ORDER BY子句
7.TOP/LIMIT/ROWNUM
1.3 [1]子查询：4类
00.查询
范围查询 between
模糊查询 like 数字、字母、日期
分组查询+多行函数/聚合函数/数字函数/组函数 group by
排序查询 order by
1.4 [1]子查询：定义
01.什么是子查询
一种嵌套在【其他SQL查询】的【WHERE子句中的查询】
子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。
子查询可以在【SELECT、INSERT、UPDATE、DELETE】语句中，同=、<、>、>=、<=、IN、BETWEEN等运算符一起使用
02.子查询规则
1.子查询必须括在圆括号中
2.子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较
3.子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用
4.返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符
5.SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用
6.子查询不能直接用在集合函数中
7.BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中
1.5 [1]集合运算：4个
01.集合运算
UNION（并集）：返回各个查询的所有记录，不包括重复记录
UNION ALL（并集）：返回各个查询的所有记录，包括重复记录
INTERSECT（交集）：返回两个查询共有的记录
MINUS（差集）：返回包含在第一个查询中，但不包含在第二个查询中的记录
1.6 [1]多表连接，6个
01.支持情况
不同的数据分析工具 支持的表连接方式
Oracle/ sql server/ Tableau/ Python 内连接(inner join)、左连接(left join)、右连接(right join)、全连接(full join)
MySQL 内连接(inner join)、左连接(left join)、右连接(right join)
Power BI 内连接、左连接、右连接、全连接、左反连接、右反连接
02.多表连接
1.交叉连接（笛卡儿积） CROSS JOIN
2.内连接 INNER JOIN
等值连接：ON A.id=B.id
不等值连接：ON A.id > B.id
3.外连接
左外连接 LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，简写成LEFT JOIN
右外连接 RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，简写成RIGHT JOIN
4.全外连接=左外连接+右外连接+去重 FULL JOIN
MySQL不支持全连接，可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用
5.自连接
02.JOIN连接
a.说明
JOIN 连接用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段
最常见的 JOIN 类型：INNER JOIN（简单的 JOIN）
INNER JOIN 从多个表中返回满足 JOIN 条件的所有行
b.语法
CROSS JOIN 交叉连接/笛卡儿积 其他4种都是以笛卡儿积为基准
INNER JOIN 内连接 如果表中有至少一个匹配，则返回行
LEFT JOIN 左连接 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN 右连接 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN 全连接 只要其中一个表中存在匹配，则返回行
c.交叉连接/笛卡儿积（CROSS JOIN）
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A
CROSS JOIN table2 CROSS JOIN B
ON table1.column_name = table2.column_name; ON A.Num_A = B.NUM_B
d.内连接（INNER JOIN）
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B 等价于 SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A FROM A,B
INNER JOIN table2 INNER JOIN B WHERE A.name = B.name
ON table1.column_name = table2.column_name; ON A.Num_A = B.NUM_B
e.左连接（LEFT JOIN）
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A A主表
LEFT JOIN table2 LEFT JOIN B
ON table1.column_name=table2.column_name; ON A.Num_A = B.NUM_B
f.右连接（RIGHT JOIN）
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A B主表
RIGHT JOIN table2 RIGHT JOIN B
ON table1.column_name = table2.column_name; ON A.Num_A = B.NUM_B
g.全连接（FULL JOIN）=左连接+右连接+去重
SELECT column_name(s) SELECT A.NUM_A, B.NUM_B
FROM table1 FROM A
FULL JOIN table2 FULL JOIN B
ON table1.column_name = table2.column_name; ON A.Num_A = B.NUM_B
1.7 [1]执行顺序：10步
00.SQL执行顺序
SELECT
FROM
JOIN
ON
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT
---------------------------------------------------------------------------------------------------------
(8) SELECT
(9) DISTINCT
(11) <Top Num> <select list>
(1) FROM [left_table]
(3) <join_type> JOIN <right_table>
(2) ON <join condition>
(4) WHERE <where condition>
(5) GROUP BY <group_by_list>
(6) WITH <CUBE | RollUP>
(7) HAVING <having_condition>
(10) ORDER BY <order by_list> ASC升序 | DESC降序
---------------------------------------------------------------------------------------------------------
f o j w g w h s d o top
from on join where groupby with having select distinct orderby top
---------------------------------------------------------------------------------------------------------
from 2张表生成一个笛卡尔积 生成“虚表1”
on 2张表进行一个关联匹配 生成“虚表2”
join 左/右关联，根据左/右表添加一个外部行 生成“虚表3”
where 行筛选 生成“虚表4”
groupby 列分组 生成“虚表5”
with 所选列中值的组合聚合 生成“虚表6”
having 符合分组条件进行筛选 生成“虚表7”
select 选择列 生成“虚表8”
distinct 删除重复数据行 生成“虚表9”
orderby 排序生成游标 生成“虚表10”
<Top 数字> 选择前几条 生成“虚表11”
<select list> 将执行list结果返回
1.8 [1]执行计划：explain
00.汇总
id 查询中每个操作的标识符，值越大表示操作越复杂
select_type 查询的类型，SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）
table 查询涉及的表名
type 连接类型，ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（引用索引）
possible_keys 查询中可能使用的索引
key 实际使用的索引
key_len 使用的索引的长度
ref 连接条件中使用的列或常量
rows 估计需要扫描的行数
Extra 额外的信息
01.id
a.定义
查询中每个操作的标识符
b.作用
标识查询中各个步骤的执行顺序。值越大，优先级越高，表示操作越复杂
c.对比
多个id值表示子查询或联合查询的不同部分
02.select_type
a.定义
查询的类型
b.常见值
SIMPLE：简单查询，不包含子查询或UNION
PRIMARY：主查询，最外层的查询
SUBQUERY：子查询
DERIVED：派生表（子查询中的FROM子句）
UNION：UNION中的第二个或后续查询
UNION RESULT：UNION的结果
c.对比
用于区分不同类型的查询结构
03.table
a.定义
查询涉及的表名
b.作用
显示当前操作涉及的表
c.对比
帮助识别查询中各个步骤操作的目标表
04.type
a.定义
连接类型，表示查询的效率
b.常见值
ALL：全表扫描，效率最低
index：索引扫描，扫描整个索引
range：范围扫描，使用索引的范围
ref：使用非唯一索引或唯一索引的前缀
eq_ref：使用唯一索引
const/system：表中最多有一个匹配行，效率最高
c.对比
type值越靠近const，查询效率越高
05.possible_keys
a.定义
查询中可能使用的索引
b.作用
显示查询优化器考虑使用的索引
c.对比
用于评估查询优化器的选择
06.key
a.定义
实际使用的索引
b.作用
显示查询优化器最终选择的索引
c.对比
与possible_keys对比，了解优化器的决策
07.key_len
a.定义
使用的索引的长度
b.作用
显示索引中使用的字节数
c.对比
帮助评估索引的使用效率
08.ref
a.定义
连接条件中使用的列或常量
b.作用
显示索引查找时使用的列
c.对比
用于理解索引查找的具体条件
09.rows
a.定义
估计需要扫描的行数
b.作用
显示查询优化器估计的扫描行数
c.对比
用于评估查询的潜在开销
10.Extra
a.定义
额外的信息
b.常见值
Using where：使用了WHERE子句进行过滤
Using index：使用了覆盖索引
Using temporary：使用了临时表
Using filesort：使用了文件排序
c.对比
提供关于查询执行的额外信息，帮助识别潜在的性能问题
1.9 [2]命令：数据库
01.数据库
创建数据库：CREATE DATABASE database_name;
删除数据库：DROP DATABASE database_name;
选择数据库：USE database_name;
1.10 [2]命令：表操作
01.表操作
创建表：CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
删除表：DROP TABLE table_name;
显示所有表：SHOW TABLES;
查看表结构：DESCRIBE table_name;
修改表（添加列）：ALTER TABLE table_name ADD column_name datatype;
1.11 [2]命令：CRUD
01.CRUD
插入数据：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
查询数据：SELECT column_names FROM table_name WHERE condition;
更新数据：UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
删除数据：DELETE FROM table_name WHERE condition;
1.12 [2]命令：索引
01.索引
创建索引：CREATE INDEX index_name ON table_name (column_name);
添加主键约束：ALTER TABLE table_name ADD PRIMARY KEY (column_name);
添加外键约束：ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name);
1.13 [2]命令：事务
01.事务
开始事务：START TRANSACTION;
提交事务：COMMIT;
回滚事务：ROLLBACK;
1.14 [2]命令：用户、权限
01.用户、权限
创建用户：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
授予权限：GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host';
撤销权限：REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host';
删除用户：DROP USER 'username'@'host';
1.15 [3]DQL、DML、DDL、DCL
01.分类1
DQL：数据查询语言 select
DML：数据操作语言 insert delete update --> 可以回退（可以进行事务操作）
DDL：数据定义语言 create drop truncate alter --> 不可以回退（可以进行事务操作）
DCL：数据控制语言 grant revoke commit rollback
00.分类2
DQL：数据查询语言 select 查询操作，以select关键字，各种简单查询，连接查询等都属于DQL
DML：数据操作语言 insert delete update DQL与DML共同构建了常用的增删改查操作，而查询是较为特殊的一种被划分到DQL中
DDL：数据定义语言 create drop truncate alter 对逻辑结构等有操作的，其中包括表结构，视图和索引
DCL：数据控制语言 grant revoke commit rollback 对数据库安全性完整性等有操作的，可以简单的理解为权限控制
1.16 [3]DELETE、TRUNCATE、DROP
01.drop、delete、truncate
a.Delete
类型：属于DML
回滚：可回滚
删除内容：表结构还在，删除表的全部或部分数据
删除速度：删除速度慢，需要逐行删除
b.Truncate
类型：属于DDL
回滚：不可回滚
删除内容：表结构还在，删除表中的所有数据
删除速度：删除速度快
c.Drop
类型：属于DDL
回滚：不可回滚
删除内容：从数据库中删除表，所有的数据、索引和权限也会被删除
删除速度：删除速度最快
d.总结
在不再需要一张表的时候，用drop
在想删除部分数据时，用delete
在保留表而删除所有数据的时候，使用truncate
02.delete、truncate
a.对比
delete from emp; 可以回退 不会释放空间（换两个地方存储数据[undo空间]，回收站） 产生碎片
truncate table emp; 不能回退 释放空间（清空回收站） 不会产生碎片
b.原因
DDL：数据定义语言 create drop truncate alter 不可以回退（可以进行事务操作）
DML：数据操作语言 insert delete update 可以回退（可以进行事务操作）
c.测试二者执行时间
打开执行时间：set timing on/off
对于少量数据： delete效率高，一行一行删除
对于海量数据： truncate效率高，1.drop table 丢弃整张表 2.重新创建表
1.17 [3]内连接、外连接、交叉连接
00.汇总
内连接，INNER JOIN 只返回两个表中匹配的记录
左外连接，LEFT JOIN 返回左表中的所有记录，即使右表中没有匹配的记录。没有匹配的记录在右表中显示为NULL
右外连接，RIGHT JOIN 返回右表中的所有记录，即使左表中没有匹配的记录。没有匹配的记录在左表中显示为NULL
全外连接，FULL JOIN 返回左表和右表中的所有记录。如果在一个表中没有匹配记录，另一个表中的字段会显示为NULL
交叉连接，CROSS JOIN 返回左表和右表的笛卡尔积，即左表的每一条记录与右表的每一条记录都配对
笛卡尔积，CARTESIAN PRODUCT 与交叉连接相同，即返回两个表的所有可能组合
1.18 [3]三个模型、三大范式、六大约束
01.三个模型
概念模型 E-R图主要是由实体、属性和联系三个要素构成的
逻辑模型 一个实体转换为一个关系；一个联系也转换为一个关系 1：1 1：n m：n
物理模型 针对上述逻辑模型所说的内容，在具体的物理介质上实现出来。如：数据库使用SQL Server 2000，编写具体的SQL脚本在数据库服务器上将数据库建立起来
02.三大范式
第一范式（1NF）：确保每个字段都是原子的，即每个列都不可再分割，且表中的每一行都是唯一的。
第二范式（2NF）：在满足1NF的基础上，确保每个非主属性完全依赖于主键（而不是主键的一部分）。
第三范式（3NF）：在满足2NF的基础上，确保每个非主属性不依赖于其他非主属性（消除传递依赖）。
03.六大约束
约束是作用于数据表中列上的规则，用于限制表中数据的类型
约束有列级和表级之分：列级约束作用于单一的列，而表级约束作用于整张数据表
约束类型 约束描述 约束命名
主键约束（Primary key） 唯一标识数据表中的行/记录 PK_stuno
外键约束（Foreign Key） 唯一标识其他表中的一条行/记录 FK_子表_父表
非空约束（Not null） 保证列中数据不能有 NULL 值 NN_字段名
默认约束（Default） 提供该列数据未指定时所采用的默认值 一般不需要命名
唯一约束（Unique） 保证列中的所有数据各不相同 UQ_字段名
检查约束（Check） 此约束保证列中的所有值满足某一条件 CK_字段名
1.19 [4]与Oracle区别
01.区别
a.主键id自增
Mysql中，在主键列上设置AUTO_INCREMENT即可实现主键自增长
Oracle相比较Mysql，需创建序列，通过使用序列来完成自增主键，在Mapper中需要使用序列来获取生成的主键
b.对事务的提交
MySQL默认是自动提交
Oracle默认不自动提交，需要用户手动提交，需要在写commit;指令或者点击commit按钮
c.分页查询
MySQL是直接在SQL语句中写"select... from ...where...limit x, y",有limit就可以实现分页
Oracle使用 ROWNUM 或 ROW_NUMBER()
d.事务隔离级别
MySQL有4中隔离级别：读未提交，读已提交，可重复读，串行化
Oracle只有2中隔离级别：读已提交、串行化
e.最重要的区别
MySQL是轻量型数据库，并且免费，没有服务恢复数据，并且开源
Oracle是重量型数据库，收费，Oracle公司对Oracle数据库有任何服务
02.区别
a.日期函数
a.Oracle
使用 TO_DATE() 和 SYSDATE
SELECT TO_DATE('2024-07-27', 'YYYY-MM-DD') FROM dual;
SELECT SYSDATE FROM dual;
b.MySQL
使用 STR_TO_DATE() 和 NOW()
SELECT STR_TO_DATE('2024-07-27', '%Y-%m-%d');
SELECT NOW();
b.分页查询
a.Oracle
使用 ROWNUM 或 ROW_NUMBER()
SELECT * FROM (SELECT a.*, ROWNUM rnum FROM (SELECT * FROM table) a WHERE ROWNUM <= 10) WHERE rnum >= 1;
b.MySQL
使用 LIMIT
SELECT * FROM table LIMIT 10 OFFSET 0;
c.字符串连接
a.Oracle
使用 ||
SELECT 'Hello' || ' World' FROM dual;
b.MySQL
使用 CONCAT()
SELECT CONCAT('Hello', ' World');
04.自动增长字段
a.Oracle
使用 SEQUENCE 和触发器
CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1;
b.MySQL
使用 AUTO_INCREMENT
CREATE TABLE table_name (id INT AUTO_INCREMENT PRIMARY KEY);
05.事务提交
a.Oracle
需要commit
b.MySQL
不需要commit，可以直接增删改
1.20 [4]数据库性能优化
01.数据库表设计优化
1.合理的表结构：比如选择合适的数据类型，例如能用int的不要用bigint,,varchar和char的选择等等
2.合理冗余字段：在适当的情况下进行反规范化设计，冗余部分数据，减少关联查询
3.索引优化：根据查询频率和条件，创建合适的索引
02.SQL优化
1.避免SELECT *，只查询必要的字段
2.避免在SQL中进行函数计算等操作，使得无法命中索引
3.避免使用%LIKE
4.联合索引不满足最左匹配原则
5.无索引字段进行order by
6.连表查询但对应字段字符集不同
建议使用EXPLAIN分析查询执行计划，确认是否用上索引，是否用对索引
03.参数调整
max_connections：增加最大连接数
quey_cache_size：适当调整查询缓存大小
table_.open_cache：增加打开表的缓存大小
thread_cache_size：调整线程缓存大小以减少线程创建的开销
innodb_buffer_.pool_size：增大InnoDB的缓冲池大小，一般设置为物理内存的70-80%
04.分库分表
对于超大规模的数据库系统，可以采用分库分表策略，将数据拆分到多个数据库或表中，提高读写性能和扩展性
05.缓存
本地缓存、Redis分布式缓存
06.数据库维护
1.重建索引：定期重建索引，保特索引高效
2.定期备份：保证数据安全，防止数据丢失
3.定期清理：删除不必要的数据和日志，释放存储空间（例如历年的数据可以剥离存档，释放当前表的大小）
4.分析表和优化表：定期运行ANALYZE TABLE和OPTIMIZE TABLE,保持表的统计信息更新和碎片整理，使得优化器更加准确
1.21 [4]数据库为什么要使用
01.为什么要使用数据库
a.数据保存在内存
优点：存取速度快
缺点：数据不能永久保存
b.数据保存在文件
优点：数据永久保存
缺点：1.速度比内存操作慢，频繁的IO操作；2.查询数据不方便
c.数据保存在数据库
1.数据永久保存
2.使用SQL语句，查询方便效率高。
3.管理数据方便
1.22 [4]存储过程一般禁止使用
01.可移植性差
数据库依赖性：存储过程是在数据库服务器上执行的，通常使用数据库特定的SQL方言和功能，这会导致应用程序的数据库依赖性增加，迁移到其他数据库系统时有成本
跨平台问题：不同的数据库系统实现存储过程的方式和支持的功能不完全相同，维护比较复杂
02.调试困难
调试工具有限：相比应用层代码，数据库层的存储过程缺乏良好的调试工具和环境。常规的代码调试方法(如设置断点、逐步执行)在存储过程中无法直接应用，导致复杂业务场景，不容易定位错误
03.维护复杂
存储过程通常与应用程序代码分离，维护起来需要同时管理数据库层和应用层的逻辑，增加了代码管理的复杂性
1.23 [4]单表不能超过2000万行
00.回答
不准确
实际上，MySQL单表可以支持远超过2000万行的数据
然而，随着数据量的增加，性能可能会受到影响
01.影响性能的因素
a.硬件限制
内存：内存不足会导致更多的磁盘I/O操作，从而降低性能
磁盘：磁盘的读写速度直接影响数据库的性能
b.索引
索引数量和类型：适当的索引可以提高查询速度，但过多或不当的索引会降低插入、更新和删除操作的性能
索引碎片：随着数据的插入和删除，索引可能会变得碎片化，影响查询性能
c.查询复杂度
复杂查询：复杂的JOIN操作、大量的子查询或未优化的查询语句会导致性能下降
全表扫描：缺乏索引或索引未被使用时，查询可能会导致全表扫描
d.表设计
表结构：不合理的表结构设计可能导致数据冗余和性能问题
数据类型：选择合适的数据类型可以节省存储空间和提高性能
e.配置参数
MySQL配置：不当的MySQL配置（如缓冲池大小、连接数限制等）会影响性能
02.性能优化建议
a.优化索引
创建合适的索引以支持常用查询
定期重建索引以减少碎片
b.优化查询
使用EXPLAIN分析查询计划，优化慢查询
避免SELECT *，只选择需要的列
c.分区和分表
使用分区表将数据分割到多个物理文件中，提高查询性能
对于非常大的表，考虑垂直或水平分表
d.硬件升级
增加内存和使用更快的磁盘（如SSD）以提高I/O性能
e.调整配置
根据工作负载调整MySQL配置参数，如innodb_buffer_pool_size
1.24 [5]数据导入后，如何与库中数据去重
00.思路
先把新数据插入到临时表里，临时表结构和原始表一致
用SQL的方式把不重复的数据DataA查询出来
把DataA插入到原始表里
01.思路1：临时表
a.not exists
SELECT
*
FROM
tableTemp t1
WHERE
NOT EXISTS ( SELECT 1 FROM tableName WHERE columnA = t1.columnA AND columnB = t1.columnB )
b.left join
SELECT
*
FROM
tableTemp t1
LEFT JOIN tableName t2 ON t2.columnA = t1.columnA AND t2.columnB = t2.columnB
WHERE
t2.columnA IS NULL
c.总结
临时表数据量少时， not exists 用时较少，随着数据量越多用时越久。当数据达到10w时，用时25s
临时表数据量少时，left join 用时30s，随着数据量越多变化不大，当数据达到10w时，用时40s
结论：单表去重时，只要导入的数据量不是特别特别大（20w级以上），优先使用 not exists 做去重
02.思路2：对两个表做去重
a.代码
SELECT
*
FROM
tableTemp t1
WHERE
NOT EXISTS (
SELECT 1 FROM tableNameA WHERE columnA = t1.columnA AND columnB = t1.columnB
union all
select 1 from tableNameB WHERE columnA = t1.columnA AND columnB = t1.columnB
);
SELECT
*
FROM
tableTemp t1
LEFT JOIN tableNameA t2 ON t2.columnA = t1.columnA AND t2.columnB = t1.columnB
LEFT JOIN tableNameB t3 ON t3.columnA = t1.columnA AND t3.columnB = t1.columnB
WHERE
t2.columnA IS NULL
AND t3.columnA IS NULL
b.总结
临时表数据少时，not exists 用时较少，随着数据量越多用时越久。当数据达到10w时，用时150s！！！
临时表数据少时，left join 用时仍然是30s，随着数据量越多用时越久。当数据达到10w时，用时仍然是40s
两者在数据量为3w时，用时不相上下
结论：双表去重时，当导入的数据在3w以下时，用 not exists，在3w以上时，用 left join
1.25 [5]最大连接数：默认151，最大16384
01.默认最大连接数
MySQL的默认最大连接数是151
这意味着在默认配置下，MySQL服务器最多允许151个客户端同时连接
02.最大连接数限制
MySQL允许的最大连接数可以设置到16384
然而，实际能够支持的最大连接数可能受到操作系统和硬件资源的限制
03.配置最大连接数
a.yong'j
[mysqld]
max_connections = 500
b.临时
这种修改在MySQL重启后会失效，除非同时更新配置文件。
1.26 [5]in存放个数？默认值67108864，64M
01.示例
a.sql
select * from user_info where id in (1,2);
b.mybatis
int size = 1000;
List<Integer> list = IntStream.range(0, size)
.boxed()
.collect(Collectors.toList());
// 注意使用的是in条件
List<UserInfo> result = ChainWrappers.queryChain(userMapper)
.in("id", list)
.list();
c.测试
测试了size=1000、size=10000、size=100000都不会报错，直到测试size=1000000
也就是in中有100百万个数据，才会报错，报错信息如下
com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (6,888,944 > 4,194,304).
You can change this value on the server by setting the 'max_allowed_packet' variable.
d.说明
从错误信息可以看出，报错原因并不是in的数据太多了（已经有一百万个了）
而是sql语句太长了，超过了4,194,304个字节，也就是4M，在我的MySQL中max_allowed_packet变量的值为67108864
02.官网
a.描述
根据MySQL官网中的描述，max_allowed_packet默认值为67108864，也就是64M
b.说明
从测试可以看出，MySQL的in中并没有数量上的限制，但是有sql语句整体大小的限制
不过还是建议in条件中不要超过200个数据，原因跟查询优化器计算成本有关
2 逻辑外键、逻辑删除、引擎、主键
2.1 [1]外键
01.定义
外键是一种用于保证表间数据关系完整性的约束
虽然外键有助于维护数据的完整性和一致性，并且可以简化级联操作，但在某些情况下，使用外键可能会带来一些缺陷和挑战
02.外键的优点
1.数据完整性：外键确保引用的完整性，防止孤立或不一致的数据
2.级联操作：可以自动处理级联删除或更新，减少手动维护的复杂性
3.减少代码量：将数据完整性判断交给数据库，减少程序代码量
03.外键的缺点
1.并发问题：每次修改数据时需要检查另一个表的数据，获取额外的锁。在高并发场景下，容易导致死锁
2.扩展性问题：外键依赖于数据库的特性，迁移数据库（如从MySQL到Oracle）时可能不方便
3.不利于分库分表：在水平拆分和分库的情况下，外键无法生效。将数据关系维护放入应用程序中，可以减少分库分表的麻烦
2.2 [1]外键：使用建议
01.外键使用建议
1.当父表中没有相对应数据时，不要向子表增加数据（如果sub表没有编号为2的课程，那么子表student不要去选择2号课程）
2.不要更改父表的数据，导致子表孤立
3.建议：在创建外键时直接设置成级联删除或级联置空
4.删除表？先删除子表，再删除父表
2.3 [1]物理外键
01.物理外键的缺点
1.维护成本：数据库需要维护外键的内部管理
2.资源消耗：涉及外键字段的增删改操作需要触发检查，消耗资源
3.死锁风险：请求对其他表加锁时容易出现死锁
4.表类型限制：所有表必须是InnoDB类型，不能是临时表
5.索引限制：不支持外键列的索引前缀，BLOB和TEXT列不能包含在外键中
6.约束检查：InnoDB不检查外键或包含NULL列的被引用键值
02.物理外键的实现步骤
1.创建两个表，其中一个表使用外键约束来指定关联关系
2.在创建表时，使用外键约束来指定关联关系
3.插入数据时，保证外键的引用完整性
2.4 [1]逻辑外键
01.逻辑外键的实现步骤
1.创建两个表，其中一个表包含一个字段用于保存另一个表的关联数据
2.在创建表时，使用字段约束来指定关联关系
3.插入数据时，保证逻辑外键的引用完整性
2.5 [1]外键、物理外键、逻辑外键
00.总结
外键：泛指用于维护表间关系的机制，可以通过物理或逻辑方式实现
物理外键：由数据库管理系统直接管理，适合需要强数据完整性和简单架构的场景
逻辑外键：由应用程序逻辑管理，适合需要高灵活性、分布式系统和分库分表的场景
01.外键
定义：外键是一个或多个列的组合，用于建立和强制表之间的链接
作用：确保引用完整性，防止孤立或不一致的数据
实现方式：可以通过物理外键（数据库约束）或逻辑外键（应用程序逻辑）实现
02.物理外键
定义：物理外键是数据库中显式定义的外键约束，直接在数据库表结构中实现
实现方式：通过数据库管理系统（DBMS）提供的外键约束功能，在创建表时定义外键
优点：自动维护数据完整性，防止孤立或不一致的数据；支持级联操作（如级联删除或更新）
缺点：增加数据库的复杂性和资源消耗；在高并发环境中可能导致锁争用和死锁；不利于数据库的迁移和分库分表
03.逻辑外键
定义：逻辑外键是通过应用程序逻辑而非数据库约束来维护的表间关系
实现方式：在表中使用字段保存关联数据，但不在数据库中定义外键约束。数据完整性由应用程序代码负责
优点：灵活性高，适合分布式系统和分库分表；减少数据库的锁争用和死锁风险；便于数据库的迁移和扩展
缺点：需要在应用程序中手动维护数据完整性，增加了开发复杂性；可能导致数据不一致，如果应用程序逻辑未正确实现
2.6 [2]逻辑删除
01.逻辑删除
逻辑删除是一种将数据标记为已删除但实际不会从数据库中移除的删除方式
通常是在表中添加一个表示删除状态的字段（如 is_deleted），默认值为0表示未删除，1表示已删除
逻辑删除便于后续的数据分析和追溯
2.7 [2]逻辑删除：物理删除
01.逻辑删除：数据仍然保留在数据库中，只是标记为已删除
a.优点
可恢复：可以通过修改标记恢复数据
支持审计和追溯：保留数据的历史状态
b.缺点
占用存储空间：数据仍然存在，占用存储空间
查询性能可能下降：需要额外的条件过滤已删除的数据
02.物理删除：直接从数据库中删除记录
a.优点
节省存储空间：释放了数据占用的存储空间
查询性能提高：查询时不需要过滤已删除的数据
b.缺点
不可恢复：一旦数据被删除，无法恢复，除非有备份
缺乏审计和追溯：无法追溯数据的历史状态和变更记录
2.8 [2]逻辑删除：出现唯一性问题
01.出现唯一性问题
在使用逻辑删除时，可能会遇到唯一性问题
例如，某个表需要设置唯一索引来保证数据的唯一性（如 userId+shopId）
如果记录被逻辑删除（标记为已删除），但用户再次尝试插入相同的记录，就会导致唯一索引冲突
02.解决逻辑删除与唯一性问题的方法
a.使用 deleted_at 字段
将 is_deleted 改为 deleted_at，存储删除时间戳，默认为空
将 userId+shopId+deleted_at 作为唯一索引。删除时设置当前时间到 deleted_at，避免唯一索引冲突
b.使用 is_deleted 存储主键
将 is_deleted 改为 bigint，存储主键，默认为0
删除时，将当前记录的 id 设置到 is_deleted。将 userId+shopId+is_deleted 作为唯一索引，解决重复问题
c.复用记录+日志表
使用日志表记录用户操作，不做修改和删除，只新增
活动表设置 userId+shopId 为唯一索引。用户参加活动时 is_deleted 为0，退出活动改为1，再次参加改为0。审计和追溯通过日志表实现
2.9 [3]存储引擎：4种
00.总结
InnoDB：支持事务、行级锁、外键，适合高可靠性应用
MyISAM：不支持事务、表级锁，适合读多写少的应用
Memory：不支持事务，速度快，适合缓存和临时数据
01.InnoDB（默认）
事务支持：支持ACID事务，提供高可靠性
锁机制：支持行级锁，减少锁竞争，提高并发性能
外键约束：支持外键，可以维护数据完整性
存储方式：数据和索引存储在一个表空间中，支持数据行的动态扩展
适用场景：适用于需要事务支持和数据完整性的应用，如金融系统
02.MyISAM
事务支持：不支持事务，数据操作不具备原子性
锁机制：支持表级锁，可能导致高并发时的性能瓶颈
外键约束：不支持外键约束
存储方式：数据和索引分别存储在文件中，支持压缩
适用场景：适用于读多写少的应用，如数据分析和日志记录
03.Memory
事务支持：不支持事务
锁机制：支持表级锁
外键约束：不支持外键
存储方式：数据存储在内存中，速度快，但数据在重启后会丢失
适用场景：适用于需要高速读写且数据可丢失的场景，如缓存和临时数据存储
2.10 [3]InnoDB：BufferPool缓冲池
01.定义
Buffer Pool 是内存中的一个区域，用于缓存数据库表和索引的数据页。它帮助减少磁盘访问次数，提高查询性能
02.原理
a.缓存机制
Buffer Pool 缓存了从磁盘读取的数据页。当查询需要访问数据时，首先检查缓冲池中是否存在该数据页
如果存在，则直接从内存中读取；如果不存在，则从磁盘读取并缓存到缓冲池
b.LRU算法
Buffer Pool 使用 LRU（Least Recently Used）算法来管理缓存数据页，确保最近使用的数据页优先保留在内存中
03.常用API
MySQL Buffer Pool 的管理主要通过配置参数和命令行工具进行，没有直接的编程 API
常用的配置参数包括：
innodb_buffer_pool_size：设置缓冲池的大小
innodb_buffer_pool_instances：设置缓冲池的实例数量
04.使用步骤
配置缓冲池大小：根据服务器的内存大小和数据库负载，调整 innodb_buffer_pool_size 参数以优化性能
监控缓冲池使用情况：使用 MySQL 的性能监控工具（如 SHOW ENGINE INNODB STATUS）查看缓冲池的使用情况
调整缓冲池实例：根据需要调整 innodb_buffer_pool_instances 参数，以提高并发性能
05.场景代码示例
a.配置缓冲池大小
[mysqld]
innodb_buffer_pool_size=2G
b.查看缓冲池状态
SHOW ENGINE INNODB STATUS;
c.调整缓冲池实例
[mysqld]
innodb_buffer_pool_instances=4
06.使用场景
高读写负载：在高读写负载的场景下，适当增大缓冲池可以显著提高性能
大数据集：对于大数据集，增加缓冲池大小可以减少磁盘 I/O，提高查询速度
多核服务器：在多核服务器上，增加缓冲池实例数量可以提高并发性能
2.11 [3]MyISAM、InnoDB
01.InnoDB索引
聚簇索引：InnoDB的主键索引是聚簇索引，叶子节点存储着行数据。因此，主键索引查询非常高效
非主键索引：叶子节点存储的是主键和其他带索引的列数据。查询时，如果能做到覆盖索引（即查询的列都在索引中），会非常高效
02.MyISAM索引
非聚簇索引：MyISAM的索引是非聚簇索引，叶子节点存储的是行数据的地址。查询时需要通过地址再寻址一次才能得到数据
03.汇总
InnoDB支持事务，MyISAM不支持事务
InnoDB支持外键，MyISAM不支持外键
InnoDB 支持 MVCC(多版本并发控制)，MyISAM 不支持
select count(*) from table时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描
Innodb不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）
InnoDB支持表、行级锁，而MyISAM支持表级锁
InnoDB表必须有主键，而MyISAM可以没有主键
Innodb表需要更多的内存和存储，而MyISAM可被压缩，存储空间较小
Innodb按主键大小有序插入，MyISAM记录插入顺序是，按记录插入顺序保存
InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引
2.12 [3]用B+树，而不用B树
01.B+树相比较 B树，有这些优势
a.更高的查询效率
B+树的所有值（数据记录或指向数据记录的指针）都存在于叶子节点，并且叶子节点之间通过指针连接，形成一个有序链表
这种结构使得 B+树非常适合进行范围查询——一旦到达了范围的开始位置，接下来的元素可以通过遍历叶子节点的链表顺序访问，而不需要回到树的上层。如 SQL 中的 ORDER BY 和 BETWEEN 查询
而 B 树的数据分布在整个树中，进行范围查询时可能需要遍历树的多个层级
b.更高的空间利用率
在 B+树中，非叶子节点不存储数据，只存储键值，这意味着非叶子节点可以拥有更多的键，从而有更多的分叉
这导致树的高度更低，进一步降低了查询时磁盘 I/O 的次数，因为每一次从一个节点到另一个节点的跳转都可能涉及到磁盘 I/O 操作
c.查询效率更稳定
B+树中所有叶子节点深度相同，所有数据查询路径长度相等，保证了每次搜索的性能稳定性
而在 B 树中，数据可以存储在内部节点，不同的查询可能需要不同深度的搜索
2.13 [3]用B+树，而不用普通二叉树
01.用B+树，而不用普通二叉树
a.核心原因
普通二叉树存在退化的情况，如果它退化成链表，就相当于全表扫描
b.读取顺序
读取数据的时候，是从磁盘先读到内存
平衡二叉树的每个节点只存储一个键值和数据，而 B+ 树可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就会下降，查询效率就快
2.14 [3]用B+树，InnoDB作为引擎
00.总结
InnoDB：支持事务、行级锁、外键，适合高可靠性应用
MyISAM：不支持事务、表级锁，适合读多写少的应用
Memory：不支持事务，速度快，适合缓存和临时数据
01.为什么InnoDB要使用B+树作为索引？
a.减少磁盘I/O
B+树的叶节点链表结构使其可以顺序访问叶节点，充分利用磁盘的预读特性，减少磁盘I/O操作
b.支持复合索引
B+树可以很容易地实现多列索引，有效支持复合查询，提高查询效率
c.高效的查询性能
B+树结构适合范围查询和顺序访问，可以快速定位记录，并支持高效的范围扫描
d.稳定的插入和删除性能
B+树的插入和删除操作会保持树的平衡，确保操作的时间复杂度为O(log n)，在大数据量下性能稳定
e.综上所述
B+树索引能提供高效的查询性能、稳定的插入删除性能、减少磁盘I/O，以及良好的复合索引支持，因此被InnoDB采用
2.15 [3]用B+树，一棵B+树存储条数
01.一棵B+树能存储的数据量主要取决于以下几个因素
磁盘页大小：InnoDB通常使用16KB的页大小
索引项大小：索引项的大小决定了一个页能存储多少个索引项
节点的扇出数（Fan-out）：每个节点包含的子节点数，B+树的扇出数越高，能存储的数据量越大
02.计算示例
假设每个索引项为 1KB，一个 16KB 的页可以存储 16 个索引项
如果扇出数为 16，那么高度为 3 的 B+ 树大约可以存储 16*16*16 = 4096条数据
03.实际情况
在实际应用中，由于各种开销（如页头信息），一棵高度为3的B+树大约能存储 几千 到 几百万 条数据
B+树的高度一般不会太高，通常在2-4之间，以确保高效查询
2.16 [4]主键自增：7种
00.总结
a.汇总
1.UUID
2.数据库自增ID
3.批量生成ID
4.Redis生成lD
5.Twitter的snowflake算法
6.百度UidGenerator
7.美团Leaf
b.插入效率
自增主键 > 雪花算法生成主键 > UUID主键
c.图示
主键生成方式 使用场景 优点 缺点及解决方案
自增主键 单机或简单分布式环境 简单易用 不适合分布式环境，可能导致数据分布不均
插入性能高 跨库查询可能冲突
索引效率高
UUID 分布式系统 保证全局唯一 存储效率低，长度较长
索引性能差，导致索引树不平衡
页分裂频繁，影响查询性能
雪花算法 高性能分布式系统 全局唯一 时钟回拨问题可能导致ID重复
趋势递增，有助于索引性能 使用时间同步服务确保时间准确
信息安全，不暴露业务信息 检测到时钟回拨时暂停ID生成，等待时间恢复正常
01.自增主键（AUTO_INCREMENT）
a.特点
自动生成唯一的整数值，每插入一行数据，值自动递增
b.优点
简单易用，无需手动管理
插入性能高，因为新记录总是追加到表的末尾
索引效率高，特别是在InnoDB中，自增主键有助于保持B+树索引的平衡
c.适用场景
适用于大多数需要唯一标识的场景
d.代码示例
CREATE TABLE example (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255)
);
02.UUID
a.特点
生成全局唯一标识符，通常用于分布式系统
b.优点
保证全局唯一性，适合分布式环境
c.缺点
UUID长度较长，存储和索引效率较低
无序性可能导致B+树索引不平衡，影响性能
d.适用场景
需要全局唯一标识的分布式系统
e.代码示例
CREATE TABLE example (
id CHAR(36) PRIMARY KEY,
name VARCHAR(255)
);
-- 插入时生成UUID
INSERT INTO example (id, name) VALUES (UUID(), 'example_name');
03.雪花算法（Snowflake）
a.特点
一种分布式ID生成算法，生成有序的64位整数
b.优点
高性能，生成速度快
有序性有助于索引性能
c.缺点
实现较复杂，需要依赖外部服务或库
适用场景：需要高性能、有序ID的分布式系统
2.17 [4]主键自增：mysql、oracle
01.MySQL：默认支持自增主键
a.表设计
CREATE TABLE user (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
b.XML：使用 useGeneratedKeys 和 keyProperty 属性
<insert id="insert" parameterType="Person" useGeneratedKeys="true" keyProperty="id">
INSERT INTO person(name, pswd)
VALUE (#{name}, #{pswd})
</insert>
c.实体类：使用 @TableId 注解标记主键，并设置 type 为 IdType.AUTO
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
public class User {
@TableId(type = IdType.AUTO) // 设置主键自增
private Long id;
private String name;
// 其他字段和方法
}
d.插入数据：当插入新记录时，无需手动设置主键字段的值，数据库会自动生成
User user = new User();
user.setName("Tom");
userMapper.insert(user); // id 会自动生成
02.Oracle：不支持自增字段，需要通过序列（Sequence）和触发器（Trigger）来模拟自增
a.使用序列（Sequence）
a.创建序列
使用 SQL 语句创建一个序列，用于生成主键值。
CREATE SEQUENCE user_seq
START WITH 1
INCREMENT BY 1
NOCACHE; -- 可选，表示不缓存序列值
b.创建表
创建表时，不需要设置自增，主键值在插入时通过序列获取。
CREATE TABLE user (
id NUMBER PRIMARY KEY,
name VARCHAR2(100)
);
c.插入数据
在插入记录时，通过调用序列的 NEXTVAL 来获取下一个主键值。
INSERT INTO user (id, name) VALUES (user_seq.NEXTVAL, 'Tom');
b.使用触发器（Trigger）
a.创建触发器
CREATE OR REPLACE TRIGGER user_trigger
BEFORE INSERT ON user
FOR EACH ROW
BEGIN
:NEW.id := user_seq.NEXTVAL; -- 使用序列的下一个值
END;
b.插入数据
现在可以在插入时不需要显式地指定主键值。
INSERT INTO user (name) VALUES ('Tom'); -- id 自动赋值
2.18 [4]自增主键：数据类型
01.TINYINT(8位)
最大值是127（有符号）或255（无符号）
02.SMALLINT(16位)
最大值是32,767（有符号）或65,535（无符号）
03.MEDIUMINT(24位)
最大值是8,388,607（有符号）或16,777,215（无符号）。
04.INT(32位)
最大值是2,147,483,647（有符号）或4,294,967,295（无符号）
05.B1G1NT(64位)
最大值是9,223,372,036,854,775,807（有符号）或18,446,744,073,709,551,615（无符号）
2.19 [4]自增主键：为什么使用
01.自增主键的主要好处
a.递增顺序插入
自增主键使得主键索引尽量保持递增顺序插入，避免了页分裂
b.索引紧凑
由于索引更紧凑，查询效率更高
c.简单易用
无需手动管理主键值，数据库自动生成
2.20 [4]自增主键：保存在什么地方
01.不同的存储引擎对自增值的保存策略不同
a.MyISAM引擎
自增值保存在数据文件中
b.InnoDB引擎（MySQL 8.0以前）
自增值保存在内存中，重启后会丢失。重启后第一次打开表时，会通过查找最大值max(id)并加1来恢复
c.InnoDB引擎（MySQL 8.0及以后）
自增值的变更记录在redo log中，重启时通过redo log恢复
2.21 [4]主键自增：ID用完？报主键冲突
01.自增ID用完会怎样？
a.情况1：主键自增ID用完，报错提示主键冲突
当主键自增 ID 达到上限后，再新增下一条数据时，它的 ID 不会变（还是最大的值），
只是此时再添加数据时，因为主键约束的原因，ID 是不允许重复的，所以就会报错提示主键冲突
b.情况2：非主键rowid
如果表没有设置主键，InnoDB会自动创建一个全局隐藏的row_id，其长度为6个字节，
当row_id达到上限后，它的执行流程和主键ID不同，它是再次归零，然后重新递增，
如果出现相同的row_id，后面的数据会覆盖之前的数据
2.22 [4]自增主键：一定是连续的吗？不是
01.回答
不一定
02.以下情况会导致不连续
a.唯一键冲突
插入数据时违反唯一约束，导致插入失败，自增值滚动
b.事务回滚
插入数据后事务回滚，自增值滚动
c.批量插入
批量插入操作可能导致自增值不连续
d.自增步长
自增步长设置不是1，也会导致不连续
2.23 [4]自增主键：普通环境，推荐自增主键
01.使用InnoDB引擎下应该尽可能的按主键的自增顺序插入
a.自增主键
页面按顺序填充，不浪费空间；减少页分裂、碎片的产生；MySQL 行记录的定位和寻址很快
b.UUID
无顺序，数据分布离散；随机IO寻址和页定位；页分裂；数据碎片
c.雪花算法
自增有序，适合分布式环境，数据量大时，效率更高
该算法生成19位的long型有序数字，MySQL中用bigint来存储（bigint长度为20位），
导致主键索引空间会很大，这样二级索引占用空间也会很大（InnoDB引擎），
MySQL有限的缓冲区，存储的索引与数据会减少，磁盘IO的概率也会增加
2.24 [4]自增主键：分布式环境，不推荐使用自增主键
01.在分布式环境中，自增主键存在以下问题
a.数据分布不均
不同节点生成的自增ID可能导致数据分布不均
b.性能损耗
需要协调多个节点的自增值，增加复杂性
c.跨库查询冲突
不同数据库实例可能生成相同的自增ID，导致冲突
2.25 [5]超键、候选键、主键、外键
01.超键、候选键、主键、外键
a.超键
在关系中能唯一标识元组的属性集称为关系模式的超键
一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键
b.候选键
最小超键，即没有冗余元素的超键
c.主键
数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合
一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）
d.外键
在一个表中存在的另一个表的主键称此表的外键
3 区别
3.1 [1]text：4类
01.分类
a.TINYTEXT
最大长度：255字节
适用场景：适合存储非常短的文本，如简短的备注或标签
b.TEXT
最大长度：65,535字节（约64KB）
适用场景：适合存储中等长度的文本，如普通文章或评论
c.MEDIUMTEXT
最大长度：16,777,215字节（约16MB）
适用场景：适合存储较长的文本，如长篇文章或文档
d.LONGTEXT
最大长度：4,294,967,295字节（约4GB）
适用场景：适合存储非常长的文本，如书籍或大型文档
02.使用建议
a.选择合适的类型
根据实际存储需求选择合适的TEXT类型，以优化存储空间和性能
b.注意性能
虽然TEXT类型可以存储大量数据，但在查询和处理时可能会影响性能，尤其是LONGTEXT
c.索引限制
TEXT类型的字段不能直接创建索引，除非指定前缀长度
3.2 [1]text、blob
01.text
用于存储大字符串
考虑字符集和排序规则
02.blob
用于存储二进制数据
没有字符集
3.3 [1]text不建议使用
01.不建议使用TEXT
a.性能问题
TEXT在内部由单独分配的对象表示，存储/检索时会有额外的操作和资源消
若TEXT字段特别大，读取时会导致内存压力增大，影响系统整体性
MEMORY存储引擎不支持TEXT类型，使用临时表处理时会从磁盘中读取TEXT字段的数据，而不会直接从内存中读
b.索引限制
索引能够提高查询性能，但对TEXT字段进行索引存在一些限制和复杂性
作为唯一索引时，可能会导致重复键错误
创建全文索引需要额外的计算和空间来维护，TEXT字段数据过大时可能会对性能产生影响
02.如要使用，可以参考以下几种方式
尽可能把TEXT字段拆到独立的表中，用PK与主表关联
非必要不读取TEXT字段，比如：禁止使用 SELECT *
大字段建议放到oss
3.4 [2]bigint、decimal
00.总结
bigint：整数
decimal：小数
01.bigint代码中用long
用途：适用于存储整数金额，通常以最小货币单位（如分、厘）存储，以避免小数运算中的精度问题
代码中对应类型：在Java中对应long类型
范围：可以存储的整数范围为-2^63到2^63-1（在MySQL中为64位有符号整数）
存储空间：占用8字节（64位）
精度：精确存储整数，不支持小数部分
适用场景：适合不需要小数的金额存储，或者金额可以转换为整数存储的场景（如以分为单位）
02.decimal代码中使用BigDecimal
用途：适用于需要存储小数的金额，支持高精度的小数运算
代码中对应类型：在Java中对应BigDecimal类型
范围：可以存储的数字范围和小数位数由定义的精度和标度决定
存储空间：存储空间取决于定义的精度和标度，存储较大数值时会占用更多空间
精度：支持高精度的小数运算，精确存储定点数，常用定义如DECIMAL(18,6)，其中18是总位数，6是小数位数。
适用场景：适合需要精确小数运算的金额存储，如金融、会计等领域
3.5 [2]datetime、timestamp
01.回答
a.相同点
两个数据类型都包含「日期」和「时间」部分
两个数据类型都可以存储微秒的小数秒，秒后 6 位小数秒
两个数据类型存储时间的表现格式一致，均为 YYYY-MM-DD HH:MM:SS
b.不同点
a.时区影响
DATETIME不受时区影响，存储的是绝对时间
TIMESTAMP受时区影响，存储的是相对时间，会根据时区进行转换
b.存储空间
DATETIME占用8个字节
TIMESTAMP占用4个字节
c.自动更新
DATETIME不会自动更新
TIMESTAMP可以设置为自动更新
d.使用场景
DATETIME适合需要存储绝对时间的场景，如事件的具体时间
TIMESTAMP适合需要记录数据变更时间的场景，如记录创建或更新时间
01.DATETIME
a.用途
默认值为null
用于存储日期和时间，格式为YYYY-MM-DD HH:MM:SS
b.范围
可以存储从1000-01-01 00:00:00到9999-12-31 23:59:59
c.存储方式
以字符串形式存储，不受时区影响
占用8个字节的存储空间
d.特点
适合需要存储历史日期或未来日期的场景
不会自动更新，除非在SQL语句中明确指定
02.TIMESTAMP
a.用途
默认不为空，为当前时间(CURRENT_TIMESTAMP)
用于存储时间戳，通常用于记录数据的创建或修改时间
b.范围
可以存储从1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC
c.存储方式
以UTC格式存储，受时区影响
占用4个字节的存储空间
d.特点
会根据服务器的时区自动转换
可以设置为在记录创建或更新时自动更新（使用CURRENT_TIMESTAMP）
3.6 [3]char(10)、int(10)
01.char(10)
存储数据的长度
用于定义固定长度的字符串存储，影响实际存储空间
02.int(10)
用于定义显示宽度
不影响实际存储范围，显示宽度在使用ZEROFILL时才有实际效果
3.7 [3]char(10)、varchar(10)
00.总结
都是存储【字符串】，只是char(10) 不可变，varchar(10) 动态分配
01.char
长度固定；无论实际存储的字符串长度是多少，都会占用100个字符的空间；如果存储的字符串长度小于100，数据库会在后面填充空格以达到指定长度
适用场景：适用于存储长度固定的字符串，如国家代码、固定长度的标识符等
最大存放：字符个数为255，和编码无关
02.varchar
动态分配；根据实际存储的字符串长度来分配空间；不会填充空格，节省空间
适用场景：适用于存储长度不固定的字符串，如姓名、地址等
最大存放：字符个数为65532
3.8 [3]varchar(50)、varchar(500)
01.网络
Varchar(50)和varchar(500)存储空间上是一样的
02.结论
当我们最该字段进行排序操作的时候，Mysql会根据该字段的设计的长度进行内存预估
如果设计过大的可变长度，会导致内存预估的值超出sort_buffer_size的大小
导致mysql采用磁盘临时文件排序，最终影响查询性能
3.9 [4]in、exists
00.回答
a.相同点
exists和in都用于子查询
b.不同点
a.in进行数据判断，in字句均可以转换为exisit子句
用于检查某个值是否在子查询返回的结果集中
返回具体的值
适合子查询结果较小的情况
b.exists是否存在数据行
用于检查子查询是否返回结果集
返回布尔值（TRUE 或 FALSE）
适合子查询结果较大的情况
01.工作原理
a.执行方式
IN：MySQL会将外表和内表作哈希连接
EXISTS：MySQL对外表作循环，每次循环再对内表进行查询
b.性能考虑
如果两个表大小相当，IN 和 EXISTS 的性能差别不大
如果子查询表较大，使用 EXISTS 更高效
如果子查询表较小，使用 IN 更高效
c.not in、not exists
NOT IN：内外表都进行全表扫描，通常不使用索引
NOT EXISTS：子查询可以使用索引，通常比 NOT IN 更快
02.代码示例
a.in
SELECT *
FROM employees
WHERE department_id IN (10, 20)
AND salary > 50000
AND hire_date >= '2020-01-01'
AND job_id = 'IT_PROG';
b.exists
SELECT *
FROM employees e
WHERE EXISTS (
SELECT 1
FROM dual
WHERE e.department_id = 10 OR e.department_id = 20
);
c.in转换为exists
SELECT * FROM T WHERE T.OBJ_ID IN (SELECT OBJ_ID FROM TMP WHERE TMP.SBID='1234');
SELECT * FROM T WHERE EXISTS (SELECT 1 FROM TMP WHERE TMP.SBID='1234' AND TMP.OBJ_ID=T.OBJ_ID);
d.not in、not exists
SELECT * FROM T WHERE RQ = '2024-01-01' AND JZID NOT IN (SELECT JZID FROM P WHERE RQ = '2024-02-18');
SELECT * FROM T WHERE RQ = '2024-01-01' AND NOT EXISTS (SELECT 1 FROM P WHERE P.JZID = T.JZID AND RQ = '2024-02-18');
03.代码示例
a.示例1
a.in
SELECT *
FROM employees
WHERE department_id IN (10, 20)
AND salary > 50000
AND hire_date >= '2020-01-01'
AND job_id = 'IT_PROG';
b.in转换为exists
SELECT *
FROM employees e
WHERE EXISTS (
SELECT 1
FROM dual
WHERE e.department_id = 10 OR e.department_id = 20
);
b.示例2
a.in
SELECT * FROM T WHERE T.OBJ_ID IN (SELECT OBJ_ID FROM TMP WHERE TMP.SBID='1234')
b.in转换为exists
SELECT * FROM T WHERE EXISTS (SELECT 1 FROM TMP WHERE TMP.SBID='1234' AND TMP.OBJ_ID=T.OBJ_ID)
c.示例3
a.in
SELECT * FROM T WHERE RQ = '2024-01-01' AND JZID NOT IN (SELECT JZID FROM P WHERE RQ = '2024-02-18');
b.in转换为exists
SELECT * FROM T WHERE RQ = '2024-01-01' AND NOT EXISTS (SELECT 1 FROM P WHERE P.JZID = T.JZID AND RQ = '2024-02-18');
3.10 [4]union、union all
00.总结
UNION：表链接后筛选掉重复的记录行
UNION ALL：不会合并重复的记录行
从效率上说，UNION ALL要比UNION快很多
01.UNION
功能：合并多个查询的结果集，并自动去除重复的记录行
效率：由于需要去除重复行，UNION在执行时会对结果集进行排序和比较，这会增加计算开销
使用场景：适用于需要确保结果集中没有重复记录的情况
02.UNION ALL
功能：合并多个查询的结果集，不去除重复的记录行
效率：UNION ALL不需要进行去重操作，因此执行速度通常比UNION快
使用场景：适用于不需要去重的情况，或者已经确定结果集中不会有重复记录
3.11 [4]drop、delete、truncate
01.drop、delete、truncate
a.Delete
类型：属于DML
回滚：可回滚
删除内容：表结构还在，删除表的全部或部分数据
删除速度：删除速度慢，需要逐行删除
b.Truncate
类型：属于DDL
回滚：不可回滚
删除内容：表结构还在，删除表中的所有数据
删除速度：删除速度快
c.Drop
类型：属于DDL
回滚：不可回滚
删除内容：从数据库中删除表，所有的数据、索引和权限也会被删除
删除速度：删除速度最快
d.总结
在不再需要一张表的时候，用drop
在想删除部分数据时，用delete
在保留表而删除所有数据的时候，使用truncate
02.delete、truncate
a.对比
delete from emp; 可以回退 不会释放空间（换两个地方存储数据[undo空间]，回收站） 产生碎片
truncate table emp; 不能回退 释放空间（清空回收站） 不会产生碎片
b.原因
DDL：数据定义语言 create drop truncate alter 不可以回退（可以进行事务操作）
DML：数据操作语言 insert delete update 可以回退（可以进行事务操作）
c.测试二者执行时间
打开执行时间：set timing on/off
对于少量数据： delete效率高，一行一行删除
对于海量数据： truncate效率高，1.drop table 丢弃整张表 2.重新创建表
3.12 [4]count(1)、count(※)、count(列)
01.执行效果
a.COUNT(*)
统计结果集中的所有行数，包括所有列
不会忽略列值为NULL的行
通常用于统计表中的总行数
b.COUNT(1)
统计结果集中的所有行数，使用1作为代表
不会忽略列值为NULL的行
与COUNT(*)效果相同，但在某些数据库实现中可能有微小的性能差异
c.COUNT(列名)
统计指定列中非NULL值的行数
忽略列值为NULL的行
用于统计某个字段的非空值数量
02.执行速度
a.列名为主键
COUNT(列名)会比COUNT(1)快，因为主键列通常有索引，统计主键列时可以利用索引加速
b.列名不为主键
COUNT(1)会比COUNT(列名)快，因为不需要检查列值是否为NULL
c.表有多个列且没有主键
COUNT(1)的执行效率优于COUNT(*)，因为COUNT(1)不需要检查所有列
d.表有主键
SELECT COUNT(主键)的执行效率是最优的，因为可以利用主键索引
e.表只有一个字段
SELECT COUNT(*)最优，因为只有一个字段，不需要考虑其他列
3.13 [5]join：不建议过多
01.性能问题
每个join操作都需要对两个或多个表进行连接操作，消耗大量的计算资源和时间
如果join操作过多，会导致C口的执行效率下降，从而影响整个系统的性能
02.可读性/维护性问题
join操作会使得SQL语句变得很复杂，难以理解和维护
特别是当join操作涉及到多个表时，seek语句的复杂度会呈现指数增长，给代码的可读性和可维护性带来挑战
3.14 [5]not null：设置默认值
00.总结
个人开发建议，大多数字段应该设置 NOT NULL，配合默认值 DEFAULT 使用更好
例如：用户状态字段 status，只有两个值：0 禁用，1 正常。应该设置 NOT NULL 并设置默认值
01.语义不清晰
NULL 和 NOT NULL 使用空值代表的含义是不一样的。
NULL：表示这一列的值是未知的。
空字符串：表示我们知道这个值，只不过它是空的。
例如，一张表的 name 字段是 NULL，可以认为不知道名字是什么；反之如果是空字符串，表示知道没有名字，它就是一个空值。
02.聚合函数计算不准确
对于 NULL 值的列，使用聚合函数时会忽略 NULL 值。
例如，表里有两条数据，其中一条数据的 name 字段值为 NULL，如果对 name 进行 COUNT，结果为 1，因为统计的是非 NULL 列。这显然不符合预期。但在实际开发中，一般是对 id 字段进行统计数量。
03.导致等号表达式失效
例如，表的 name 字段是 NULL，使用等号查询是查不到的，必须使用 IS NULL 才能查询出来。
04.导致值运算失效
例如，表的 age 字段值是 NULL，加 1 之后结果还是 NULL；name 字段值是 NULL，CONCAT 之后结果也变成了 NULL，这也是不符合预期的。
05.一些意想不到的问题
例如，表里只有一条 name 为 “高启强” 的数据，但查询不等于 “高启强” 的数据时，显示只有一条，这显然不符合预期。
3.15 [5]limit：3-10行，limit 2, 8
01.3-10行
SELECT * FROM table_name LIMIT 2, 8;
02.limit
limit语句用于限制查询结果的数量，偏移量表示从哪条记录开始，行数表示返回的记录数量
2：偏移量，表示跳过前两条记录，从第三条记录开始
8：行数，表示从偏移量开始，返回 8 条记录
4 应用
4.1 [0]基础架构
01.Server层：核心服务枢纽
a.核心功能
连接管理：处理客户端连接、身份认证和权限验证。
SQL 接口：接收并解析 SQL 命令，返回执行结果。
查询处理：包括查询解析、优化和执行。
内置函数：提供日期、时间、数学、加密等各类函数。
跨引擎功能：实现存储过程、触发器、视图等高级特性。
b.组成组件
包括连接器、查询缓存、分析器、优化器、执行器等。
02.存储引擎层：数据存储解决方案
a.功能
负责数据的物理存储和提取，采用插件式架构，支持多种存储引擎。
b.常见存储引擎
InnoDB：MySQL 5.5.5+ 的默认引擎，支持事务和行级锁。
MyISAM：适用于读密集型场景。
Memory：数据存储在内存中，读写速度极快。
其他引擎：如 Archive、CSV 等特定用途引擎。
c.架构特点
存储引擎是基于表的，而不是数据库。
所有存储引擎共享同一个 Server 层，开发者可根据业务需求选择合适的存储引擎。
03.连接器：连接管理与权限控制
a.连接建立过程
TCP 三次握手建立网络连接。
身份认证（用户名密码验证）。
权限信息获取和缓存。
连接状态维护。
b.连接权限特性
权限缓存机制：已建立的连接使用旧权限设置，修改权限后需重新连接生效。
c.连接策略优化
长连接：适合频繁请求场景。
短连接：适合低频访问场景。
推荐策略：优先使用长连接，减少连接建立的开销。
d.长连接内存管理
问题：长连接可能导致内存占用持续增长。
解决方案：
定期断开重连。
使用 mysql_reset_connection 重置会话状态。
配置连接池，合理设置最大连接数和空闲超时时间。
04.查询缓存：历史功能的演进与淘汰
a.工作原理
以 Key-Value 形式缓存查询结果。
Key 为查询语句，Value 为查询结果。
b.淘汰原因
缓存失效问题：表的更新操作会导致所有查询缓存失效。
MySQL 8.0 移除了查询缓存功能，建议通过其他方式优化查询性能。
05.分析器：SQL解析与语法验证
a.词法分析
将 SQL 字符串分解为有意义的标记（tokens）。
b.语法分析
根据 MySQL 语法规则验证语句结构，生成抽象语法树（AST）。
错误排查：关注错误信息中 "use near" 后的内容。
06.优化器：执行计划生成与优化
a.优化决策
索引选择：根据统计信息选择最合适的索引。
连接顺序：决定多表连接的顺序和方式。
查询重写：对查询进行等价变换以提高性能。
b.执行计划分析
使用 EXPLAIN 命令查看优化器生成的执行计划。
关键指标：
type：连接类型（性能从优到差：const > eq_ref > ref > range > index > ALL）。
rows：预估扫描行数。
key：实际使用的索引。
Extra：额外信息（如 Using where、Using index 等）。
07.执行器：查询执行与结果返回
a.执行流程
权限验证：验证用户对目标表的操作权限。
引擎调用：根据表定义的存储引擎调用相应接口。
结果返回：处理结果集并返回给客户端。
b.执行示例
示例查询：SELECT * FROM users WHERE id = 100;
执行步骤：
调用存储引擎接口获取第一行数据。
判断条件是否符合，符合则加入结果集。
遍历完成后返回结果集。
c.性能监控
慢查询分析：通过慢查询日志监控执行性能。
重要指标：rows_examined 表示实际扫描的行数。
08.存储引擎层详解与选型指南
a.InnoDB：事务安全首选
适用场景：需要事务支持、高并发读写、数据一致性要求高的应用。
核心特性：
支持 ACID 事务。
行级锁设计，支持高并发。
外键约束支持。
MVCC 多版本并发控制。
崩溃恢复能力。
b.MyISAM：读密集型应用
适用场景：读多写少的业务、不需要事务支持的日志记录。
特点：
表级锁设计，并发性能有限。
数据和索引分离存储。
不支持事务和外键。
c.Memory：内存临时存储
适用场景：临时数据存储、高速缓存层、中间结果处理。
特点：
数据存储在内存中，读写极快。
服务重启后数据丢失。
不支持 TEXT 和 BLOB 类型。
d.存储引擎对比与选型
| 特性 | InnoDB | MyISAM | Memory |
|----------------|--------------|--------------|--------------|
| 事务支持 | ✅ | ❌ | ❌ |
| 锁粒度 | 行级锁 | 表级锁 | 表级锁 |
| 外键支持 | ✅ | ❌ | ❌ |
| 崩溃恢复支持 | ✅ | ❌ | ❌ |
| 并发性能 | 高 | 低 | 中 |
| 存储限制 | 64TB | 256TB | RAM大小 |
| 适用场景 | 事务型应用 | 读密集型 | 临时数据 |
e.选型建议
默认选择 InnoDB：适用于大多数业务场景。
读密集型考虑 MyISAM：但需注意其不支持事务和崩溃恢复。
临时数据使用 Memory：注意数据持久性问题。
09.实践总结与优化建议
a.连接管理最佳实践
使用连接池：减少连接建立开销，控制连接数量。
合理配置超时：根据业务特点设置连接超时时间。
监控连接状态：定期检查连接使用情况，避免泄漏。
连接重用：使用连接重置代替重新建立连接。
b.查询性能优化
索引优化：为常用查询条件创建合适索引。
避免全表扫描：通过 EXPLAIN 分析执行计划。
分批处理：大数据量操作分批次进行。
查询重写：优化复杂查询，避免不必要的连接和子查询。
c.存储引擎选择策略
事务需求：需要事务支持时选择 InnoDB。
并发考量：高并发写入场景选择 InnoDB。
读性能：纯读场景可考虑 MyISAM。
数据量：大数据量场景选择 InnoDB。
临时数据：临时处理选择 Memory 引擎。
d.监控与维护
SHOW PROCESSLIST; -- 查看当前连接。
SHOW ENGINE INNODB STATUS; -- InnoDB 状态。
SHOW GLOBAL STATUS LIKE 'Handler_read%'; -- 索引使用情况。
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'; -- 缓冲池状态。
4.2 [1]读写分离
00.主从复制、读写分离
a.范围
主从复制：实现读写分离的一种技术手段
读写分离：一个更高层次的架构设计概念，通常依赖于主从复制来实现
b.目的
主从复制：主要关注数据同步和备份
读写分离：主要关注性能优化和负载均衡
c.实现方式
主从复制：数据库层面的技术
读写分离：可能需要在应用层或通过中间件进行实现
01.读写分离的基本实现
a.数据库服务器搭建主从集群
可以采用一主一从或一主多从的架构
主数据库负责处理所有的写操作（INSERT、UPDATE、DELETE等）
从数据库负责处理读操作（SELECT）
b.主从数据库的角色分配
主数据库（Master）：负责处理写操作，并将数据变更同步到从数据库
从数据库（Slave）：负责处理读操作，减轻主数据库的读负载
c.数据同步
主数据库通过复制机制将数据变更同步到从数据库
每台数据库服务器都存储了所有的业务数据，确保数据的一致性
d.业务服务器的操作分配
业务服务器将写操作（如数据插入、更新、删除）发送给主数据库
业务服务器将读操作（如数据查询）发送给从数据库
4.3 [2]主从复制：定义
00.汇总
主：一个是读写分离，分担"master"的读写压力
从：一个是方便做容灾恢复
01.工作机制
1.主服务器记录数据更改到二进制日志（binlog）
2.从服务器将主服务器的二进制日志复制到自己的中继日志（relay log）
3.从服务器读取中继日志的事件，并将其应用到自己的数据库中
4.4 [2]主从复制：原理
00.汇总
主：一个是读写分离，分担"master"的读写压力
从：一个是方便做容灾恢复
01.基本原理
1.主服务器：binlog线程：记录所有改变数据库数据的语句，并将其放入主服务器的binlog中
2.从服务器：io线程：在执行start slave命令后，负责从主服务器拉取binlog内容，并放入自己的relay log中
sql执行线程：执行relay log中的语句，将更改应用到从服务器的数据库中
4.5 [2]主从复制：实现
00.汇总
主：一个是读写分离，分担"master"的读写压力
从：一个是方便做容灾恢复
01.主从复制如何实现
1.主服务器（master）记录数据更改到二进制日志（binlog）
2.从服务器（slave）复制主服务器的二进制日志到自己的中继日志（relay log）
3.从服务器重做中继日志中的日志，将更改应用到自己的数据库上，实现数据的一致性
4.6 [2]主从复制：优点
01.数据分布
可以随意开始或停止复制，并在不同地理位置分布数据备份
02.负载均衡
降低单个服务器的压力
03.高可用和故障切换
帮助应用程序避免单点失败
04.升级测试
可以使用更高版本的MySQL作为从库进行测试
4.7 [2]主从复制：缺点
01.数据一致性问题
a.问题
延迟：主从复制通常是异步的，这意味着从库的数据可能会滞后于主库。对于需要强一致性的应用，这可能会导致数据不一致的问题
读写分离的挑战：在读写分离的架构中，应用程序需要处理从库数据延迟带来的不一致性
b.解决
延迟监控：实时监控主从延迟，确保从库的数据尽可能接近主库；使用工具（如pt-heartbeat）来测量和报告延迟
读写分离策略：对于需要强一致性的读操作，直接从主库读取，实现智能路由，在延迟可接受的情况下从从库读取
02.故障切换复杂性
a.问题
手动切换：在主库故障时，需要手动将从库提升为主库，这可能导致服务中断
自动化难度：实现自动故障切换需要复杂的监控和管理机制
b.解决
自动故障切换：使用自动化工具（如MHA、Orchestrator）来实现主从故障自动切换，定期测试故障切换流程，确保其可靠性
手动切换预案：制定详细的手动故障切换步骤和预案，确保在自动化工具失效时能够快速响应
03.管理和维护复杂性
a.问题
配置和监控：设置和维护主从复制需要额外的配置和监控，尤其是在多从库环境中
数据恢复：在主库故障后，从库可能需要重新同步，增加了恢复时间和复杂性
b.解决
配置管理：使用配置管理工具（如Ansible、Chef）来自动化主从复制的配置和部署；定期检查复制配置，确保其正确性和一致性
监控和报警：部署监控系统（如Prometheus、Zabbix）来监控复制状态、延迟和错误；设置报警机制，及时通知运维人员处理异常。
04.性能开销
a.问题
复制开销：主库需要将数据更改写入二进制日志，并将其传输到从库，这会增加主库的I/O负担
网络带宽：在地理位置分布的环境中，复制可能会消耗大量网络带宽
b.解决
优化主库性能：优化主库的查询和索引，减少写入负担；使用SSD等高速存储设备，提高I/O性能
网络优化：在地理位置分布的环境中，使用专用网络或VPN来提高复制的网络性能；压缩复制流量，减少带宽消耗
05.架构复杂性
a.问题
拓扑管理：在多主多从的复杂拓扑中，管理复制关系和数据流动变得更加困难
版本兼容性：主从库之间的版本差异可能导致复制问题，尤其是在进行升级测试时
b.解决
拓扑管理：使用拓扑管理工具（如Orchestrator）来可视化和管理复制拓扑；定期审查和优化复制拓扑，简化数据流动
版本兼容性：在升级前进行充分的测试，确保主从库之间的版本兼容性；使用从库进行升级测试，验证新版本的稳定性和性能
4.8 [2]主从复制：延迟问题
00.汇总
主：一个是读写分离，分担"master"的读写压力
从：一个是方便做容灾恢复
01.主从同步延迟的原因
一个服务器开放N个链接给客户端来连接的，这样有会有大并发的更新操作
但是从服务器的里面读取binlog的线程仅有一个
当某个SQL在从服务器上执行的时间稍长 或者由于某个 SQL 要进行锁表就会导致
主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟
02.主从同步延迟的解决办法
a.写操作后的读操作指定发给数据库主服务器
例如，注册账号完成后，登录时读取账号的读操作也发给数据库主服务器
这种方式和业务强绑定，对业务的侵入和影响较大，如果哪个新来的程序员不知道这样写代码，就会导致一个bug
b.读从机失败后再读一次主机
这就是通常所说的 "二次读取" ，二次读取和业务无绑定
只需要对底层数据库访问的 API 进行封装即可，实现代价较小
不足之处在于如果有很多二次读取，将大大增加主机的读操作压力
例如，黑客暴力破解账号，会导致大量的二次读取操作，主机可能顶不住读操作的压力从而崩溃
c.关键业务读写操作全部指向主机，非关键业务采用读写分离
例如，对于一个用户管理系统来说，注册 + 登录的业务读写操作全部访问主机
用户的介绍、爰好、等级等业务，可以采用读写分离，因为即使用户改了自己的自我介绍
在查询时却看到了自我介绍还是旧的，业务影响与不能登录相比就小很多，还可以忍受
4.9 [3]垂直水平
00.汇总
a.垂直拆分：根据字段进行拆分
数据表：单表复杂度
数据库：功能拆分
b.水平拆分：根据某个字段的值进行拆分
分表：单表大数据量
分库：单库性能
01.数据分片
a.定义
数据分片是将存放在单一数据库中的数据分散到多个数据库或表中，以提升性能和可用性。分片可以通过分库和分表实现
b.分库和分表的作用
分库：有效分散对数据库单点的访问量，避免单库性能瓶颈
分表：将分布式事务转化为本地事务，减少复杂性
c.分片方式
垂直分片：按业务拆分，专库专用
水平分片：按某个字段规则分散数据
02.垂直分片
a.定义
垂直分片（纵向拆分）是根据业务逻辑将数据表分布到不同的数据库中
b.优点
库表职责单一，易于维护
降低单库或单表压力
c.缺点
部分表关联需在程序中完成
单表大数据量问题仍存在
事务处理复杂，需分布式事务
03.水平分片
a.定义
水平分片（横向拆分）是通过某个字段将数据分散到多个库或表中
b.优点
解决单表单库大数据量问题
架构改动少，事务处理简单
c.缺点
拆分规则复杂，维护难度增加
产品逻辑复杂化
4.10 [3]分库分表
00.汇总
a.只分表
单表数据量较大，单表读写性能出现瓶颈
经过评估单库的容量和性能可以支撑未来几年的增长
b.只分库
数据库（读）写压力较大，数据库出现存储性能瓶颈
c.分库分表
单表数据量较大，单表读写性能出现瓶颈
数据库（读）写压力较大，数据库出现存储性能瓶颈
d.相关中间件
Cobar：阿里开源
MyCAT：Cobar改造
Atlas：360开源
TDDL：淘宝团队
Sharding Sphere：最好的开源的分库分表
04.分库
a.定义
将数据分散到多个数据库中，以解决单库性能问题
b.策略
垂直分库：按业务模块拆分，例如用户表、订单表、商品表等分到不同的库中
水平分库：按策略（如用户ID的hash值）拆分，例如按照用户id的hash值将用户表拆分到不同的库中
c.优点
性能提升：通过将数据分散到多个数据库中，减轻单个数据库的负担，提高整体性能
负载均衡：分散对数据库的访问量，避免单点瓶颈
高可用性：通过多数据库的冗余，提高系统的可用性和容错能力
d.缺点
事务问题：分库后，单机事务无法使用，需要依赖分布式事务来保证事务完整性，增加了系统复杂性
跨库JOIN问题：无法直接使用SQL的JOIN进行跨库查询，需要在业务代码中实现关联查询，或者通过数据冗余和异构等方式解决
05.分表
a.定义
分表是将数据分散到多个表中，以减轻单表的查询压力
b.分表策略
垂直拆分：按数据类型拆分
水平拆分：按分片键（如主键）拆分
c.路由方式
高区分度：避免数据倾斜
查询频率高：提高查询效率
写入频率高：均匀分布写入负载
d.优点
性能提升：通过将数据分散到多个表中，减轻单表的查询和写入压力，提高性能
扩展性：可以根据需要增加新的表，方便扩展
e.缺点
复杂查询问题：跨节点的COUNT、ORDER BY、GROUP BY以及聚合函数等复杂查询需要在业务代码中实现，或者使用中间件进行数据汇总和处理
数据迁移和扩容问题：数据迁移和容量规划变得复杂，需要考虑未来的扩容需求
ID问题：需要设计全局唯一的ID生成策略，以避免分表后ID冲突
4.11 [4]分库分表：查询慢
00.说明
数据查询慢，你真的能确定是数据量的原因吗，你有做过 explain 的语句分析么，是索引引起的么？
还是你单次查询量过大啊？还是你业务代码诉求联合多表查询导致的慢啊？
01.使用 EXPLAIN 分析查询语句
首先使用 EXPLAIN 来分析查询语句，查看是否使用了索引
如果没有使用索引，根据业务需求判断是否需要创建或调整索引
02.优化索引
确保查询中涉及的字段有合适的索引
检查索引的选择性和覆盖情况，避免不必要的全表扫描
03.减少单次查询的数据量
检查查询是否返回了过多的数据，尽量只查询需要的字段
使用分页查询来限制返回的数据量，避免深分页带来的性能问题
04.优化 SQL 语句
检查 SQL 语句的复杂度，避免不必要的多表关联和子查询
尽量简化查询逻辑，使用合适的连接方式（如 INNER JOIN、LEFT JOIN 等）
05.业务逻辑调整
与业务团队沟通，确认查询需求是否合理
考虑是否可以通过业务逻辑调整来减少数据库的压力
4.12 [4]分库分表：数据倾斜
01.说明
倾斜咋能算问题，只是一种现象，你们现在数据量多少了，要解决倾斜这种现象
数据倾斜不是问题，数据库的性能出现了瓶颈才是问题，你是查询慢了，还是新增慢了，要是查询不慢，新增不慢，我解决啥
02.分库分表有数据倾斜，如何解决？
a.合理设计分片键
选择合适的分片键可以有效减少数据倾斜
分片键应该具有较好的散列特性，使得数据能够均匀分布在各个分片上
b.使用哈希算法
通过哈希算法对分片键进行处理，可以使数据更均匀地分布
例如，可以使用一致性哈希算法来分配数据
c.动态分片
根据数据量的变化动态调整分片数量和分片策略，以确保数据均匀分布
d.热点数据迁移
对于某些热点数据，可以考虑将其迁移到专门的分片或数据库中，以减少对其他分片的影响
e.监控和调整
定期监控各个分片的数据量和访问情况，根据实际情况进行调整和优化。
4.13 [4]分库分表：500w行、2GB
01.什么情况下使用分库分表
阿里巴巴开发手册：【单表行数超过500万行】或者【单表容量超过2GB】，推荐【分表】
02.说明
实际的业务流程中，在我们业务上线的时候就可以考虑未来的业务数据量，是否有必要进行分库分表
如果未来的数据增长有可能达到预期，那么就可以提前进行设计
4.14 [4]分库分表：数据一致性
00.汇总
事务
分布式锁
消息队列
01.定义
数据一致性是指在分库分表的环境下，确保数据在不同库和表之间保持一致，不出现数据丢失、重复或错误的情况
02.原理
a.分库分表
将数据分散到多个数据库和表中，以提高查询性能和数据处理能力
b.数据一致性
通过事务、分布式锁、消息队列等机制，确保数据在分库分表后的操作中保持一致
03.常用API
a.事务管理
使用数据库事务来确保数据操作的原子性
b.分布式锁
使用Redis等工具实现分布式锁，确保数据操作的互斥性
c.消息队列
使用Kafka、RabbitMQ等消息队列实现异步数据同步和一致性校验
04.使用步骤
a.设计分库分表策略
根据数据量和访问模式设计合理的分库分表策略
b.实现事务管理
在数据操作中使用事务，确保操作的原子性
c.使用分布式锁
在需要互斥的数据操作中使用分布式锁，确保操作的互斥性
d.使用消息队列
在异步数据同步中使用消息队列，确保数据的一致性
05.场景及代码示例
a.场景1：使用事务管理
a.说明
假设有一个订单系统，订单数据分散在多个库和表中。在创建订单时，需要确保订单和库存的操作是原子性的
b.代码
public void createOrder(Order order) {
try {
// 开启事务
connection.setAutoCommit(false);
// 插入订单数据
insertOrder(order);
// 更新库存数据
updateInventory(order);
// 提交事务
connection.commit();
} catch (SQLException e) {
// 回滚事务
connection.rollback();
throw new RuntimeException("订单创建失败", e);
} finally {
// 关闭连接
connection.setAutoCommit(true);
}
}
b.场景2：使用分布式锁
a.说明
在分库分表环境中，使用分布式锁确保数据操作的互斥性
b.代码
public void updateInventory(Order order) {
String lockKey = "inventory_lock_" + order.getProductId();
try {
// 获取分布式锁
if (redisLock.tryLock(lockKey)) {
// 更新库存数据
// ...
} else {
throw new RuntimeException("获取锁失败");
}
} finally {
// 释放锁
redisLock.unlock(lockKey);
}
}
c.场景3：使用消息队列
a.说明
在分库分表环境中，使用消息队列实现异步数据同步和一致性校验
b.代码
public void syncData(Order order) {
// 发送订单数据到消息队列
kafkaTemplate.send("order_topic", order);
// 消费者异步处理订单数据
@KafkaListener(topics = "order_topic")
public void handleOrder(Order order) {
// 同步订单数据到其他库和表
// ...
}
}
06.注意事项
a.事务边界
在分库分表环境中，事务的边界可能需要跨库处理，确保事务的一致性
b.锁粒度
分布式锁的粒度需要合理设计，避免锁争用和性能问题
c.消息可靠性
消息队列需要确保消息的可靠性和顺序性，避免数据丢失和重复
