在一次又一次的失败中, 我总结了这份万字的《MySQL 性能调优笔记》-CSDN博客

本文链接：https://blog.csdn.net/m0_74931226/article/details/128144942

MySQL 性能调优学习导图》概要

说明：本文篇幅有限，故只展示部分内容，《MySQL 性能调优学习导图》资料已整理成文档，需要获取的小伙伴可以直接转发+关注后私信（学习）即可获取哦

一、性能监控

1. 使用 show profile 查询剖析工具，可以指定具体的 type

此工具默认是禁用的，可以通过服务器变量在绘画级别动态的修改

set profiling=1;

当设置完成之后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。

select * from emp;

在 mysql 的命令行模式下只能显示两位小数的时间，可以使用如下命令查看具体的执行时间

show profiles;

执行如下命令可以查看详细的每个步骤的时间：

show profile for query 1;

2. 使用 performance schema 来更加容易的监控 mysql

MYSQL performance schema 详解

performance_schema 的介绍
performance schema 入门
performance_schema 表的分类
performance_schema 的简单配置与使用
常用配置项的参数说明
重要配置表的相关说明
performance_schema 实践操作

3. 使用 show processlist 查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征

id 表示 session id
user 表示操作的用户
host 表示操作的主机
db 表示操作的数据库
command 表示当前状态
info 表示详细的 sq1 语向
time 表示相应命令执行时间
state 表示命令执行状态

二、schema 与数据类型优化

1. 数据类型的优化

更小的通常更好：应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和 CPU 缓存，并且处理时需要的 CPU 周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型

简单就好：简单数据类型的操作通常需要更少的 CPU 周期，例如，①、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂；②、使用 mysql 自建类型而不是字符串来存储日期和时间；③、用整型存储 IP 地址

尽量避免 null：如果查询中包含可为 NULL 的列，对 mysql 来说很难优化，因为可为 null 的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下 null 的列改为 not null 带来的性能提升比较小，所有没有必要将所有的表的 schema 进行修改，但是应该尽量避免设计成可为 null 的列

实际细则：

2. 合理使用范式和反范式

①、范式

优点：

范式化的更新通常比反范式要快
当数据较好的范式化后，很少或者没有重复的数据
范式化的数据比较小，可以放在内存中，操作比较快

缺点：

通常需要进行关联

②、反范式

优点

所有的数据都在同一张表中，可以避免关联
可以设计有效的索引；

缺点

表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失

③、注意

3. 主键的选择

代理主键：与业务无关的，无意义的数字序列

自然主键：事物属性中的自然唯一标识

推荐使用代理主键

它们不与业务耦合，因此更容易维护
一个大多数表，最好是全部表，通用的键策略能够减少需要编写的源码数量，减少系统的总体拥有成本

4. 存储引擎的选择

5. 适当的数据冗余

被频繁引用且只能通过 Join 2 张(或者更多)大表的方式才能得到的独立小字段。
这样的场景由于每次 Join 仅仅只是为了取得某个小字段的值，Join 到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

6. 适当拆分

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR 类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。