相关MySQL 博客, 当前主要讲述 MySQL 实践和技术方案
联表查询
以学生和学生报的课程为例，一个学生表 student、一个选课表 lesson、一个选修表 lesson_student。一个学生可以选择多门课程放到选修表中
# 直接使用临时表查询
SELECT * from student WHERE id in (SELECT student.id from lesson_student where lesson. id=2)
# 笛卡尔积连接 查询性能比不上第三种
SELECT student.id as id,student.name as `name` from student,lesson_student WHERE student.id = lesson_student.student_id
# 内连接(join) ---- 左连接(left join)、右连接(right join)
SELECT student.id as id,student.name as `name` from student INSERT JOIN lesson_student ON student.id = lesson_student.student_id覆盖索引查询优化
覆盖索引是指一个索引包含了查询所需的所有列，这样查询时只需要访问索引，而不需要回表查询数据行。
- 避免回表操作，减少磁盘 I /0
- 提高查询性能，特别是大数据量场景
- 减少锁竞争，提升并发能力
- 减少内存，只加载必要的数据
回表索引与覆盖索引的区别
回表索引
- 在二级索引中查找满足条件的记录
- 获取对应的主键值
- 根据主键值回到聚簇索引查找完整数据
- 返回所需的列数据
EXPLAIN SELECT * FROM employees WHERE department = '技术部' AND salary > 12000;
// 避免使用 * 的回表索引 先返回的是根据二级索引返回主键索引数据然后过滤覆盖索引
- 在二级索引中查找满足条件的记录
- 直接从索引中获取所需的所有列数据
- 返回结果(无需回表)
减少了 50% 的索引访问次数，避免了随机 I / O 操作，大幅提升查询效率
-- 这个查询使用了覆盖索引
EXPLAIN SELECT department, salary
FROM employees
WHERE department = '技术部' AND salary > 12000;
-- 这个查询需要回表，因为需要获取 name 字段
EXPLAIN SELECT name, department, salary
FROM employees
WHERE department = '技术部' AND salary > 12000;
-- 创建包含 name 字段的复合索引
CREATE INDEX idx_department_salary_name ON employees(department, salary, name);
-- 现在这个查询也能使用覆盖索引
EXPLAIN SELECT name, department, salary
FROM employees
WHERE department = '技术部' AND salary > 12000;覆盖索引 设计原则
- 包含查询列: 索引必须包含 SELECT 的所有列
- 包含条件列: 索引必须包含 WHERE 的条件列
- 考虑排序列:ORDER BY 的列也应包含在索引中
- 平衡索引大小: 避免索引过大影响维护性能
EXPLAIN 输出特征：覆盖索引的识别标志
- Extra 字段显示 ”Using index”: 表示使用了覆盖索引
- type 字段为 index: 当进行全表扫描时使用索引
- key 字段显示具体索引名: 实际使用的索引
超大分页优化
传统的分页使用 LIMIT OFFSET, 例如：LIMIT 10000,20 的执行过程
- MySQL 需要找到前 10000 条记录
- 将这 10000 条记录全部丢弃返回接下来的 20 条记录
- 缺点：
- 消耗大量无效的磁盘 I / O 操作
- 内存中临时存储大量无用数据
- CPU 资源浪费在数据跳转上
优化方案：游标优化、
游标分页
使用 WHERE 条件代替 OFFSET，基于上一页的最后一条记录作为起点:
传统方式:
SELECT*FROM user ORDER BY id LIMIT 10000,20
游标优化方式
SELECT* FROM user WHERE id > last id ORDER BY id LIMIT 20优势
- 避免了 OFFSET 的跳跃扫描
- 查询时间保持稳定，不随页码增长
- 充分利用主键索引的有序性
缺点：不支持跳页，只能顺序翻页
场景
- 有自增主键或时间戳字段
- 用户主要访问较新的数据
- 移动端滑动加载场景
延迟关联
先通过覆盖索引获取主键，再关联查询完整数据，就是先获取一个临时表，通过临时表再次获取数据
传统深分页查询:
SELECT*FROM user ORDER BY create time LlMIT 50000,20
子查询优化:
SELECT u.* FROM user u
INNER JOIN(SELECT id FROM user ORDER BY create time LlMIT 50000,20)tmp ON u.id = tmp.id提升原理
- 子查询只涉及索引，避免访问数据页
- 大大减少了需要扫描的数据量
- 主查询只需要根据 20 个 ID 查询具体数据
场景
- 无法使用游标分页的复杂查询
- 需要支持任意页面跳转
- 查询列较多，但排序列有索引
断电为什么不会数据丢失
MySQL 在数据提交的适合有两个机制 redolog 和 binlog 进行保证数据的准确性。redolog 记录了修改磁盘的物理日志，binlog 是逻辑日志用于主从同步和时间点恢复。
执行的顺序是事务提交时先执行 redolog 的 prepare 阶段然后执行 binlog 最后执行 redolog 的 commit 阶段。因为先执行 binlog 再执行 redolog，假设 binlog 阶段后崩了，redolog 没记录。MySQL 会认为未提交事务，但是 binlog 已经执行到从库了，导致主从不一致。而两个 redolog 的 prepare 和 binlog 阶段后，commit 阶段前崩溃，MySQL 会检查 redolog 是否完整，不完整就回滚，解决了数据一致性问题。
redolog 是循环写的，满了会覆盖旧日志，binlog 是追加写的。如果 redolog 满了没有刷盘，会触发 MySQL 的 checkpoint，把 redolog 的记录刷到磁盘，这时候性能会骤降。生产环境合理设置 redolog 的文件大小。太大崩溃恢复满，太小则频繁触发 checkpoint
MySQL 表三层 B + 树最大可以存储多少数据
Innodb 的 page 每页是 16kb，每条数据 1kb，往上一层是索引键 + 子节点指针，不存储数据，一个指针大概的 6 字节加个 bigint 索引键 8 个字节，16kb/14 大概是 1170 个指针，再往上也是 1170 个指针。所以合理的数据是 16*1170*1170 得到大约数据是 2190 万
但是在实际项目中，每行数据的大小是不一样的，有的几十字节，有的几十 kb，所以这个要看实际情况才能判断出一个表可以存储的合理数据。若是超出就出现第四层树了
MySQL 到 ES 的数据一致性
用到了 ES(elastic)说明数据量已经非常大，进行了分库分表进行大数据的快速检索。注意 ES 采用到排序索引使用内存比较大的开销
- 同步双写
- 在操作 MySQL 的同时往 ES 进行相同的操作，都操作完成后返回给用户成功消息
- 逻辑简单、实时性高，适合时效高，业务逻辑简单的场景 - 金融交易的实时同步
- 优点：保证数据一致性
- 缺点：在并发场景中影响性能，堆积请求影响客户端响应，需要在操作 ES 失败的情况下进行补偿机制（1- 回滚 MySQL，2- 补偿 ES 操作）
- 定时同步
- 分布式定时任务调度同步 MySQL 数据到 ES 的操作
- 优点：提升了性能
- 缺点：时效性差
- MQ 异步
- MySQL 数据操作后向 MQ 发送一条数据库变更消息，由同步服务消费 MQ 消息处理对 ES 数据操作
- 优点：性能高，业务隔离
- 缺点：硬编码，对 MySQL 数据的处理都要加上消息队列发送了；系统的复杂度提升
- 监听 binlog
- 不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。使用阿里开源框架 cannal 支持 binlog 日志同步到 ES 功能，若是数据量过大，可以通告 cannal-server 发送到 MQ，然后通过 cannal-adapter 同步到 ES（若是对存量数据同步可以使用阿里的 Flink，不仅对增量数据同步，还支持对存量数据同步）
- 优点：无代码侵入、性能高（阿里背书）、业务隔离
- 缺点：系统复杂度提升
死锁
线上系统核心业务数据库发生死锁，导致核心业务功能间歇性失败，怎么排除和解决
- SHOW ENGINE INNODB STATUS:MySQL 的命令查看最近死锁详情分析冲突事务和 SQL
- 检查事务设计确认是否存在不同顺序的加锁操作或长事务持有锁时间过长
- 审视索引设计：判断是否因缺失合适的索引导致锁范围扩大
- 业务层做并发控制：对关键资源操作实行队列化处理
- 在业务允许的情况下考虑调整事务隔离级别降低死锁概率