一篇就够mysql高阶知识总结

一、事务的ACID原则

序号	原则	说明
1	原子性（Atomicity）	事务是数据库的逻辑工作单位，事务中包括的诸操作要么都做，要么都不做
2	一致性（Consistency）	事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态
3	隔离性（Isolation）	一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
4	持久性（Durability）	持久性也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，接下来的其他操作或故障不应该对其有任何影响

二、数据库设计的三大范式

序号	范式	说明
1	第一范式（1NF）	确保每列保持原子性
2	第二范式（2NF）	在满足第一范式的前提下，非主属性完全依赖于码
3	第三范式（3NF）	在满足第二范式的前提下，非主属性不传递依赖于码

三、索引

序号	索引类型	说明
1	二叉树搜索树	基础数据结构，用于快速查找、插入和删除
2	红黑树	平衡二叉搜索树，保持树的平衡以维持操作的高效性
3	B树	适用于大量数据的磁盘读写操作，保持树的平衡
4	B+树	B树的变种，非叶子节点不存储数据，更适合作为数据库索引结构
5	索引概念	数据库中用于提高数据检索效率的数据结构

四、SQL解析

序号	内容	说明
1	SQL解析	SQL语句被数据库管理系统解析、编译和执行的过程

五、锁机制

序号	锁类型	说明
1	行锁	锁定一行数据
2	表锁	锁定整个表
3	范围锁	锁定一定范围的数据
4	悲观锁	假设最坏情况，数据在修改前会被锁定
5	乐观锁	假设最好的情况，只在更新操作时检查数据是否被其他事务修改
6	读写锁	分为读锁和写锁，读锁允许多个读操作并发，写锁则独占

六、JOIN查询

序号	内容	说明
1	7种常见的JOIN查询	包括INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CROSS JOIN等
2	SQL语句	展示具体的SQL JOIN查询语句
3-9	每种JOIN查询的说明与示例	分别解释每种JOIN的用法及示例
10	Union和Union All	介绍UNION和UNION ALL的区别及使用场景

七、索引优化

序号	内容	说明
1	索引分类	单值索引、唯一索引、主键索引、复合索引
2	Explain性能分析	使用EXPLAIN分析SQL查询性能
3	索引优化入门案例	展示不同场景下的索引优化案例
4	索引失效分析	分析导致索引失效的常见场景
5	分组排序优化	优化ORDER BY和GROUP BY的性能

索引分类

序号	索引类型	说明
1	单值索引	索引列中的单个值
2	唯一索引	索引列中的值必须是唯一的，允许NULL值但最多只能有一个
3	主键索引	特殊的唯一索引，一个表只能有一个主键，且不允许NULL值
4	复合索引（组合索引）	索引由两个或两个以上的列组成，列的组合值必须唯一或满足特定条件

Explain性能分析

序号	字段	说明
1	sql	执行的SQL语句
2	id	SELECT的标识符，如果查询包含子查询，则会出现多个id
3	select_type	SELECT的类型（SIMPLE, PRIMARY, SUBQUERY等）
4	table	输出行所引用的表
5	type	连接类型（ALL, index, range, ref, eq_ref, const/system等）
6	possible_keys	显示可能应用在这张表上的索引，但不一定实际使用
7	key	实际使用的索引
8	key_len	使用的索引的长度
9	ref	显示索引的哪一列或常数被用于查找值
10	rows	MySQL认为必须检查的用来返回请求数据的行数估计值
11	Extra	包含不适合在其他列中显示但十分重要的额外信息，如是否使用索引等

索引优化入门案例

1. 驱动表与被驱动表的选择

在连接查询（如JOIN）中，优化器会决定哪个表作为驱动表（驱动其他表进行连接的表），哪个表作为被驱动表。选择正确的驱动表和被驱动表可以显著提高查询性能。通常，驱动表应该是数据量较小、过滤条件较多、索引效果好的表。

案例：
假设有两个表，employees（员工表，包含10万条记录）和departments（部门表，包含100条记录）。如果要查询每个部门下的员工信息，最好让departments表作为驱动表，因为它更小，可以更快地遍历。

SELECT employees.*, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id
WHERE departments.region = 'Asia';

在这个例子中，尽管employees表在JOIN条件中，但如果departments.region = 'Asia'这个条件能显著减少departments表的结果集，那么优化器可能会选择departments作为驱动表。

2. 单表索引优化

单表索引优化通常涉及为查询中经常作为条件、连接键或排序键的列添加索引。

案例：
假设employees表有一个last_name列，经常需要根据员工的姓氏来查询员工信息。

SELECT * FROM employees WHERE last_name = 'Smith';

为了提高这个查询的效率，可以在last_name列上添加索引。

CREATE INDEX idx_last_name ON employees(last_name);

3. 两表索引优化

在两表连接查询中，优化索引可以显著提高性能。通常，应该在连接键和过滤条件上添加索引。

案例：
继续上面的employees和departments表的例子，如果经常需要根据部门ID和员工的姓氏来查询信息，可以在employees.department_id和departments.id（如果尚未索引）以及employees.last_name上添加索引。

-- 假设departments.id已经是主键，因此默认有索引
CREATE INDEX idx_emp_dept_id ON employees(department_id);
CREATE INDEX idx_emp_last_name ON employees(last_name);SELECT employees.*, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.id
WHERE employees.last_name = 'Smith';

4. 三表及以上索引优化

对于涉及三个或更多表的连接查询，索引优化的策略与两表类似，但更复杂。通常需要考虑查询中所有表之间的连接键以及过滤条件。

案例：
假设还有一个projects表（项目表），其中包含项目信息，每个员工可以参与多个项目。现在需要查询参与特定项目且姓氏为’Smith’的员工及其部门信息。

SELECT employees.*, departments.department_name, projects.project_name
FROM employees
JOIN departments ON employees.department_id = departments.id
JOIN projects ON employees.id = projects.employee_id
WHERE employees.last_name = 'Smith' AND projects.project_name = 'ProjectX';

为了优化这个查询，可以在employees.department_id、employees.id（如果尚未作为主键索引）、employees.last_name、projects.employee_id以及projects.project_name上添加索引。

-- 假设employees.id和departments.id已经是主键，因此默认有索引
CREATE INDEX idx_emp_dept_id ON employees(department_id);
CREATE INDEX idx_emp_last_name ON employees(last_name);
CREATE INDEX idx_proj_emp_id ON projects(employee_id);
CREATE INDEX idx_proj_name ON projects(project_name);

注意：虽然索引可以显著提高查询性能，但它们也会占用额外的磁盘空间，并且会降低写操作的性能（如INSERT、UPDATE、DELETE）。因此，在设计索引时需要权衡这些因素。

索引失效分析

序号	场景	说明
1	最佳左前缀法则	复合索引中，查询条件需要按照索引列的顺序进行
2	避免在索引字段上做计算	如WHERE YEAR(column) = 2023，这会导致索引失效
3	避免在索引字段上做范围查询	范围查询后的列将无法使用索引（如WHERE a > 10 AND b = 2）
4	查询字段和索引字段尽量一致	避免使用SELECT *，尽量只选择需要的列
5	慎用IS NULL和IS NOT NULL	对于索引列，这些条件可能导致索引失效
6	LIKE的前后模糊匹配	如LIKE '%keyword%'，这将导致索引失效，但LIKE 'keyword%'则有效
7	使用UNION或UNION ALL代替OR	在某些情况下，UNION/UNION ALL可以更有效地利用索引

八、SQL优化

序号	内容	说明
1	SQL语句优化	提供SQL语句优化的方法和技巧

序号	内容	说明
1	ORDER BY之前先使用WHERE等条件	过滤掉不需要排序的数据，减少排序的数据量
2	WHERE和ORDER BY所用到的索引	尽量使用索引来加速WHERE和ORDER BY的操作
3	排序的方向必须一致	如果WHERE条件中使用了索引的某一列进行排序，确保排序方向一致

SQL语句优化

• 避免SELECT * ：尽量指定需要查询的列，减少数据传输量。
• 使用表连接代替子查询：在可能的情况下，使用JOIN代替子查询可以提高查询效率。
• 优化WHERE子句：确保WHERE子句中的条件能够利用索引。
• 使用LIMIT限制结果集：如果只需要部分数据，使用LIMIT来减少处理的数据量。
• 合理使用聚合函数：聚合函数（如SUM, COUNT, AVG等）

九、额外优化技术

序号	内容	说明
1	截取查询分析	分析慢查询日志，优化大量数据插入等场景
2	开启慢SQL查询日志	监控并优化慢查询
3	show profile	使用MySQL的show profile命令分析查询性能