MySQL

最新推荐文章于 2025-06-13 23:20:28 发布

yadanuof

最新推荐文章于 2025-06-13 23:20:28 发布

阅读量693

点赞数 10

分类专栏： yy的学习之路文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/weixin_74191696/article/details/145502879

版权

yy的学习之路专栏收录该内容

10 篇文章

订阅专栏

慢查询

表象：页面加载过慢、接口压测响应时间过长（超过1s）

1. MySQL中，如何定位慢查询？
候选人：
嗯，我们当时在做压力测试时发现有些接口响应时间非常慢，超过了2秒。因为我们的系统部署了运维监控系统Skywalking，在它的报表展示中可以看到哪个接口慢，并且能分析出接口中哪部分耗时较多，包括具体的SQL执行时间，这样就能定位到出现问题的SQL。
如果没有这种监控系统，MySQL本身也提供了慢查询日志功能。可以在MySQL的系统配置文件中开启慢查询日志，并设置SQL执行时间超过多少就记录到日志文件，比如我们之前项目设置的是2秒，超过这个时间的SQL就会记录在日志文件中，我们就可以在那里找到执行慢的SQL。

分析慢查询

通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）
通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描
通过extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

优化慢查询

索引
- 索引（index）是帮助MySQL高效获取数据的数据结构(有序)
- 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
索引的底层结构
- 采用B+树的数据结构存储索引
  - 路径更短
  - B+树磁盘读写代价更低, 只有叶子节点存储数据
  - 便于扫库和区间查询(叶子节点是双向链表)
聚集索引与二级索引
- 聚集索引:
  - 数据存储在索引的叶子节点中
- 二级索引(非聚集索引):
  - 索引的叶子节点只存储对应的主键值
  - 二级索引需要回表查询–通过叶子节点的主键值在聚集索引中找到对应的数据
覆盖索引
- 指查询中使用了索引且索引中包含了需要返回的列
  - 使用id查询直接用聚集索引一次索引扫描直接返回数据, 性能高
  - 如果返回到列中没有创建索引, 有可能会触发回表查询, 所以尽量避免使用select *
- MySQL超大分页处理
  - 在数据量比较大时, 如果进行limit分页查询, 查询越往后, 分页查询效率越低
  - 优化思路:
    - 通过创建覆盖索引能够比较好地提高性能, 可以通过覆盖索引加子查询的形式进行优化
    - ```
    select *
    from tb_sku t,
    (select id from tb_sku order by id limit 9000000,10) a
    where t.id = a.id;
```
索引创建
- 针对数据量较大, 且查询比较频繁的表建立索引 (单表超过10万数据)
- 针对于常作为查询条件, 排序, 分组操作的字段建立索引
- 尽量选择区分度高的列作为索引(列数据差异大)
- 尽量使用联合索引, 减少单列索引, (索引中包含的key更多的), 可以覆盖索引, 避免回表查询
- 控制索引数量
- 对于不能够存储NULL值的索引, 用NOT NULL约束
索引失效
- 失效情况
  - 违反最左前缀法则
  - 范围查询右边的列,不能使用索引
  - 不要在索引列上进行运算操作，
  - 索引将失效字符串不加单引号，造成索引失效。
  - (类型转换)以%开头的Like模糊查询，索引失效

sql的优化

表的设计优化(参考优化手册)
- 设置合适的数值(tinyint, int, bigint)
- 设置合适的字符串类型(char , varchar)
SQL语句优化
- 避免直接使用select *
- 避免造成索引失效的写法
- 使用union all , 避免union的多一次过滤
- 避免在where子句中对字段进行表达式操作
- left 或right join时将小表放在外面, 减少连接次数(外连接不会调整连接次序)
主从复制, 读写分离
- 写操作不会影响查询的效率

事务

事务时一组操作的集合, 是不可分割的工作单位, 事务会把所有的操作作为一个整体一起向系统提交或撤销操作, 这些操作要么同时成功要么全部失败

事务的特性
- Atomicity 原子性, 事务时不可分割的最小单元
- Consistency一致性, 事务完成时必须使所有数据保持一致状态
- Isolation隔离性, 事务在不受外部并发操作影响的独立环境下运行
- Durability持久性, 事务一旦提交或回滚, 它对数据库中的数据的改变就是永久的
并发事务
- 问题:
  - 脏读: 一个事务读到另外一个事务还没有提交的数据
  - 不可重复读: 同一事务内, 两次读取同一行数据, 第二次读到的值变化了, 影响的时同一行的数据
  - 幻读:同一事务内, 两次查询相同条件, 结果集的行数变化了, 影响的时整个结果集的行数
- 解决方案: 对事物进行隔离
  - 可重复读(默认)
  - 串行化
- 缓冲池(buffer pool): 主内存中的一个区域, 存储缓存的是经常操作的磁盘中缓存的数据, 在执行SQL操作时, 先操作缓冲池中的数据, 如果池中未查找到需要的数据, 则从磁盘中加载并缓存
- 数据页: 是InnoDB存储引擎磁盘管理的最小单元, 存储的是行数据.
- redo log:
  - 重做日志, 记录的是事务提交时数据也的物理修改, 用来实现事务的持久性
  - 当事务提交后会把所有修改信息都存到该日志文件中, 用于在刷新脏页到磁盘发生错误时进行数据恢复使用
- undo log:
  - 回滚日志, 用于记录数据修改前的信息
  - 提供回滚和MVCC(多版本并发控制), 实现事务的一致性和原子性

redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据
undo log ：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据
redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

好的，事务中的隔离性是如何保证的呢？
锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）
mvcc : 多版本并发控制

MVCC

Multi-Version Concurrency Control 多版本并发控制, 指维护一个数据的多个版本, 使得读写操作没有冲突

MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

隐藏字段：
- trx_id(事务id)，记录每一次操作的事务id，是自增的
- roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址
undo log：
- 回滚日志，存储老版本数据
- 版本链：多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表
readView解决的是一个事务查询选择版本的问题
- 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据
- 不同的隔离级别快照读是不一样的，最终的访问的结果不一样
  - RC ：每一次执行快照读时生成ReadView
  - RR：仅在事务中第一次执行快照读时生成ReadView，后续复用

主从同步原理

核心是二进制日志
- 二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但不包括数据查询（SELECT、SHOW）语句。
主库在事务提交成功后, 会将数据变更记录在二进制文件BINLOG中->从库读取主库的BINLOG文件, 并且写入到从库的中继日志RelayLog中-> 从库重做中继日志中的语句来改变从库的数据

分库分表

时机:
- 前提，项目业务数据逐渐增多，或业务发展比较迅速
- 优化已解决不了性能问题（主从读写分离、查询索引…）
- IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）
垂直分库:
- 以表为依据, 根据业务将不同表拆分到不同库中
- 按业务对数据分级管理维护
垂直分表:
- 以字段为依据, 分局字段属性将不同字段拆分到不同表中
  - 把不常用的字段单独放在一张表
  - 把大字段拆分
- 冷热数据分离, 减少IO过渡争抢, 量表互不影响
水平分库
- 将一个库的数据拆分到多个库中
- 解决单库大数量高并发的问题
- 提高系统稳定性可用性
水平分表
- 将一个表数据拆分到多个表中
- 优化单一表数据量过大而产生的性能问题;
- 避免IO争抢并减少锁表的几率;