MySQL调优主要分为三个步骤：监控报警、 排查慢SQL、MySQL调优
一、监控报警
监控工具（例如Prometheus+Grafana）监控MySQL，发现查询性能变慢，报警提醒运维人员
二、排查慢SQL
2.1 开启慢查询日志
查看慢查询次数：
show status like 'slow_queries';
开启慢查询日志，修改慢查询阈值：
set slow_query_log='ON'; #开启慢查询日志
set long_query_time = 1; #设置慢查询阈值
2.2 找出最慢的几条SQL
慢查询日志分析工具mysqldumpslow找到最慢的几条语句
mysqldumpslow 命令的具体参数如下：
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间 （默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；
-g: 后边搭配一个正则匹配模式，大小写不敏感的
示例： 按照查询时间排序，查看前五条 慢查询SQL 语句
#命令行，按照查询时间排序，查看前五条 慢查询SQL 语句
mysqldumpslow -s t -t 5 /var/lib/mysql/xxx-slow.log
2.3 分析查询计划
explan分析sql执行计划（访问类型、记录条数、索引长度等）；主要关注字段：
1.possible_keys：查询可能用到的索引
2.key：实际使用的索引
3.key_len：实际使用的索引的字节数长度。
4.type：访问类型，看有没有走索引。all（全表扫描），ref（命中非唯一索引），const（命中主键/唯一索引）、range(范围索引查询)、index_merge(使用多个索引)、 system(一行记录时,快速查询)。
5.Extra：额外信息。看有没有走索引。
1.using index：覆盖索引，不回表。
2.using filesort：需要额外的排序。排序分为索引排序和filesort排序，索引排序一般更快，深分 页 等 查询数据量大时filesort更快。
3.using index condition：索引下推。MySQL5.6开始支持。联合索引某字段是模糊查询（非左模 糊）时，该字段进行条件判断后，后面几个字段可以直接条件判断，判断过滤后再回表对不包含在联合索引内的字段条件进行判断。
using where：不走索引，全表扫描。
三、MySQL调优
3.1 基础优化
3.1.1 缓存优化
mysql调整缓冲池大小等参数；引入redis。tip：InnoDB使用缓冲池缓存记录和索引
3.1.2 硬件优化
服务器加内存条、升级SSD固态硬盘、把磁盘I/O分散在多个设备、配置多处理器。
3.1.3 参数优化
关闭不必要的服务和日志：调优结束关闭慢查询日志；
调整最大连接数：max_connections ；
线程池缓存线程数：thread_cache_size，缓存空闲线程，有连接时直接分配该线程处理连接；
缓冲池大小：innodb_buffer_pool_size 。
3.1.4 定期清理垃圾
对于不再使用的表、数据、日志、缓存等，应该及时清理，避免占用过多的MySQL资源，从而提高MySQL的性能。
3.1.5 使用合适的存储引擎
MyISAM：适合读取频繁，写入较少的场景（因为表级锁、B+树叶存地址）
InnoDB：适合并发写入的场景（因为行级锁、B+树叶存记录）。
3.1.6 读写分离
读写分离：读写分离能有效提高查询性能。主从同步用到bin log和relay log。
3.1.7 分库分表
分库分表：数据量级到达千万级以上后，垂直拆分（分库）、水平拆分（分表）、垂直+水平拆分（分库分表）。
概念：
只分表：单表数据量大，读写出现瓶颈，这个表所在的库还可以支撑未来几年的增长。
只分库：整个数据库读写出现性能瓶颈，将整个库拆开。
分库分表：单表数据量大，所在库也出现性能瓶颈，就要既分库又分表。
垂直拆分：把字段分开。例如spu表的pic字段特别长，建议把这个pic字段拆到另一个表（同库或不同库）。
水平拆分：把记录分开。例如表数据量到达百万，我们拆成四张20万的表。
拆分原则：
分库分表步骤：
1.MySQL调优：数据量能稳定在千万级，近几年不会到达亿级，其实是不用着急拆的，先尝试MySQL调优，优化读写性能。
2.目标评估：评估拆几个库、表，举例: 当前20亿，5年后评估为100亿。分几个表? 分几个库?解答:一个合理的答案，1024个表，16个库按1024个表算，拆分完单表200万，5年后为1000万.1024个表*200w≈100亿
3.表拆分：
1.业务层拆分：混合业务拆分为独立业务、冷热分离
2.数据层拆分：
1.按日期拆分：这种使用方式比较普遍，尤其是按照日期维度的拆分，其实在程序层面的改动很 小，但是扩展性方面的收益很大。
日维度拆分，如test_20191021
月维度拆分,如test_201910
年维度拆分,如test_2019
2.按主键范围拆分：例如【1,200w】主键在一个表，【200w，400w】主键在一个表。优点是单表数据量可控。缺点是流量无法分摊，写操作集中在最后面的表。
3 .中间表映射：表随意拆分，引入中间表记录查询的字段值，以及它对应的数据在哪个表里。优点是灵活。确定是引入中间表让流程变复杂。
4.hash切分：sharding_key%N。优点是数据分片均匀，流量分摊。缺点是扩容需要迁移数据，跨节点查询问题。
5.按分区拆分：hash,range等方式。不建议，因为数据其实难以实现水平扩展。
4.sharding_key（分表字段）选择：尽量选择查询频率最高的字段，然后根据表拆分方式选择字段。
5.代码改造：修改代码里的查询、更新语句，以便让其适应分库分表后的情况。
6.数据迁移：最简单的就是停机迁移，复杂点的就是不停机迁移，要考虑增量同步和全量同步的问题。
全量同步：老库到新库的数据迁移，要控制好迁移效率，解决增量数据的一致性。
定时任务：定时任务查老库写新库
中间件：使用中间件迁移数据
增量同步：老库迁移到新库期间，新增删改命令的落库不能出错
同步双写：同步写新库和老库；
异步双写（推荐）： 写老库，监听binlog异步同步到新库
中间件同步工具：通过一定的规则将数据同步到目标库表
数据一致性校验和补偿：假设采用异步双写方案，在迁移完成后，逐条对比新老库数据，一致则跳过，不一致则补偿：
新库存在，老库不存在：新库删除数据
新库不存在，老库存在：新库插入数据
新库存在、老库存在：比较所有字段，不一致则将新库更新为老库数据
灰度切读：灰度发布指黑（旧版本）与白（新版本）之间，让一些用户继续用旧版本，一些用户开始用新版本，如果用户对新版本没什么意见，就逐步把所有用户迁移到新版本，实现平滑过渡发布。原则：
有问题及时切回老库
灰度放量先慢后快，每次放量观察一段时间
支持灵活的规则：门店维度灰度、百 (万)分比灰度
停老用新：下线老库，用新库读写
3.2 表设计优化
3.2.1 混合业务分表、冷热数据分表
例如把一个大的任务表，分离成任务表和历史任务表，任务表里任务完成后移动到历史任务表。任务表是热数据，历史任务表是冷数据，提高查询性能。
3.2.2 联合查询改为中间关系表
例如属性表和属性分组表，不使用连接查询，使用“属性-属性分组表”存储每条属性与“属性关系”的id。
3.2.3 遵循三个范式
每个属性不可再分、表必须有且只有一个主键、非主键列必须直接依赖于主键
3.2.4 字段建议非空约束
①可能查询出现空指针问题；
②导致聚合函数不准确，因为它会忽略null
③不能用“=”判断，只能用is null判断；
④null和其他值运算只能是null，可能让你不小心把它当成0；
⑤null值比空字符更占用空间，空值长度是0，null长度是1bit；
⑥不覆盖索引情况下，is not null无法用索引
3.2.5 使用冗余字段
虽然列字段不能太多，但为查询效率可增加冗余字段
3.2.6 数据类型优化
整数类型：
考虑好数值范围，前期可以使用int保证稳定性。非负数类型要用UNSIGNED；同样字节数，存储的数值范围更大。主键一般使用bigint,布尔类型tinint
能整数就不要用文本类型：
跟文本类型数据相比，大整数往往占用更少的存储空间。
避免使用TEXT、BLOB数据类：
这两个大数据类型，排序时不能使用临时内存表，只能使用磁盘临时表，效率很差，建议别用，或分表到单独扩展表里。LongBlob类型能存储4G文件；
避免使用枚举类型：
排序很慢
使用TIMESTAMP存储时间：
TIMESTAMP使用4字节，DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。 缺点是只能存到2038年，MySQL5.6.4版本可以参数配置，自动修改它为BIGINT类型。
DECIMAL存浮点数：
Decimal类型为精准浮点数，在计算时不会丢失精度，尤其是财务相关的金融类数据。占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。
3.3 索引优化
3.3.1 考虑索引失效的11个场景
详细请参考：
超大分页：
扫一扫
38
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
