MySQL数据库优化二三事
VOL 172
05
2020-10
今天距2021年56天
这是ITester软件测试小栈第172次推文
点击上方蓝字“ITester软件测试小栈“关注我，每周一、三、五早上 08:30准时推送，每月不定期赠送技术书籍。
微信公众号后台回复“资源”、“测试工具包”领取测试资源，回复“微信群”一起进群打怪。
本文4317字，阅读约需11分钟
平时在开发新项目时，有时因为工期紧张，经常会以实现功能为目标，不太注意效率问题，特别是在SQL语句上。
常见的数据库优化方法有哪些？简单来说是加索引，重建结构，杀进程，杀DBA……如果在一个没有DBA的公司，上线一时爽，事后火葬场，卑微测试一不小心背黑锅。
何以解忧？唯有学习和实践。测试人员也会和数据打交道，今天总结数据库的优化知识。主要介绍可以从哪些方面优化数据库，提高数据库的执行效率。
一
系统现存问题
1
问题背景
“系统慢不是问题，只要不崩溃就行”，这可能这是大多数懒癌技术派的想法。但是，如果系统经常抛出一些故障（硬件问题除外，不过如果磁盘经常坏，可能也和性能有关）。很多时候就是因为：没有使用绑定变量、错误的设置了一些优化器参数、并发过大、缺少索引（最普遍）、统计信息不准确、SQL写法不佳、RAC系统按照单节点设计等等一系列性能问题，导致系统压力过大而出现的状况。但是懒癌晚期们往往宁愿出故障时救火，却不愿意花时间去优化数据库。试想如果系统经过全面优化，负载很小，还会经常出各种问题吗？100%的数据库都是可以优化的，CPU降低，资源争用小，系统就会更加稳定；IO压力降低，SQL执行速度加快，磁盘寿命也会更长。
2
问题分析
设计上的问题：单列索引太多，总索引量太多，容易出现索引合并，优化器无法选中最优索引，间接导致大家使用force index ，又导致了优化器无法自动智能选则执行计划。
使用上的问题：万能查询，多个接口并用，查询所有列，force index 滥用 ，单表数据量过大，SQL写法不规范。
二
数据库查询慢的探索
1
问题现象
SQL语句执行得很慢的原因有哪些？这个问题可以涉及到 MySQL 的很多核心知识，就像要考查计算机网络总问“输入URL回车之后，究竟发生了什么”一样。
SQL 语句执行的很慢，那是每次执行都很慢呢？还是大多数情况下是正常的，偶尔出现很慢呢？我们得分以下2种情况来讨论：
- 在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。
- 大多数情况是正常的，只是偶尔会出现很慢的情况。
针对这两种情况，我们来分析下可能是哪些原因导致的。
2
原因分析
总体来说，SQL 语句执行的很慢可能是由于以下原因：
- 扫描行数太多；
- 返回行数太多；
- 额外的操作（排序，分组，计算）；
根据SQL 执行慢的程度，我们再分2种情况具体讨论：
这条 SQL 语句一直执行的很慢，有如下原因：
- 没有用上索引：由于对字段进行运算、函数操作导致无法用索引。
- 数据库选错了索引。
大多数情况下很正常，偶尔很慢，有如下原因：
- 数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
- 执行的时候，遇到锁，如表锁、行锁。
3
问题定位
我们可以通过开启慢查询日志的方式去定位有问题的SQL ，找到问题的根源。
（1）查看MySQL是否开启慢查询日志：
show variables like 'slow_query_log';
（2）设置没有索引的记录到慢查询日志：
set global log_queries_not_using_indexes=on;
（3）查看超过多长时间的SQL进行记录到慢查询日志：
show variables like 'long_query_time';
（4）开启慢查询日志：
set global slow_query_log=on;
（5）设置超时时间：
set global long_query_time=5;
--超过5s的语句才记录日志
（6）查看慢查询日志的位置 ：
show variables like 'slow%';
三
数据库优化原则
1
表结构优化
1. 新建表必须设置主键，推荐自增id，无符号bigint类型。
2. 所有字段必须要有注释，表必须要有注释。
3. 所有字段尽量设置为not null 限制，默认值有则给，无则不写，禁止使用默认 NULL，字符类型推荐默认，以最小满足使用为宜。
4. 表必须包含逻辑删除，创建人，创建时间，修改时间，备注 ；
5.顺序固定一致，且一直保持在表的最后。
例如，如下复制：
is_delete TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否删除，默认0：有效，1：失效。',
createdby MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建人',
created INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '创建时间',
changed_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
remarks VARCHAR(100) NOT NULL DEFAULT '' COMMENT '备注，保留字段'
2
索引优化
1.最左前缀匹配原则，where条件的顺序应该尽量与索引列的顺序保持一致。
2.尽量选择区分度高的列作为索引。
3. 新建索引时，常用的范围查询，最好放在索引的最后面。
4.查看索引离散度 show index from his.tb_api_log。
5.索引尽量不在字符类型上使用，不频繁更新的字段上。
6.索引以 idx_字段1_字段2_字段n 命名 字段可简写，顺序不能乱。
3
数据库配置优化
连接数(connection)配置：当发现MySQL有能力处理更多的并发的时候， 建议调大max_connections这个值，相应给服务器带来更高的负载(CPU/IO/内存)。
查询缓存(query_cache)配置：MySQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接 返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化。
临时表缓存(tmp_table_size)配置：MySQL进行复杂查询或者 做高级GROUP BY操作的时候，系统为了优化查询，生成一些临时表。一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表。
索引缓冲区（key_buffer_size）配置：是对MyISAM表性能影响最大的一个参数，key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。
4
硬件资源优化
硬件层面的优化是最后的手段，主要需考虑CPU、存储、网络等几个方面。
CPU：CPU并不是越多越好，增加CPU数量并不能提高性能。
存储：机械磁盘 or SSD（当然是SSD更快），单个大磁盘 or 多个小磁盘组合使用（单个1T的磁盘应该没有2个500G磁盘的组合快，因为磁盘的转速都是固定的，两个磁盘相当于可以并行的读取）。
网络：一般不是问题，但是在分布式的集群环境中，各个数据库节点之间的网络环境经常会称为系统的瓶颈。另外，如果服务端和数据库分布在不同的城市，一条简单SQL传输的时间可能就要几十毫秒。
四
数据库优化总结
其实在需求分析阶段就要预估可能的业务量和数据量，这样才能在建表时有针对的进行设计。否则抛开需求谈优化都是耍流氓，就像这个世界上没有包治百病的神药一样，也不会有解决一切问题的完美技术。所以数据库的设计必须和需求有关系，因为表结构也得符合需求，一套数据库的设计也是和需求有密切关系。一个需求里面会体现对某个表是侧重读取还是侧重写入，数据设计应尽可能遵循如下原则：
- SQL语句尽可能简单，大的SQL想办法拆成小的SQL语句;
- 事务要简单，整个事务的时间长度不要太长，不同事务更新表的顺序需要一致;
- 注意非按照索引的update造成大面积锁（应当先查，再按主键更新）;
- 避免使用触发器、函数、存储过程、事件;
- 降低业务耦合度（避免万能查询，比较严重）;
- 慎用范围查询;
- 避免在数据库中进数学运算(MySQL不擅长数学运算和逻辑判断);
- 不要用select *，查询哪几个字段就select 这几个字段;
- 禁止不同类型字段做比较，避免隐式转换;
- like的参数以通配符开头时;
- like 尽量使用全文索引(分区表不支持全文索引);
- in里面数字的个数建议控制在1000以内;
- limit分页注意效率。limit越大，效率越低,可改成延迟关联,这是大数据量单表查询中最有效最常用的优化方法;
- 避免使大表的join;
- 对大数据的更新要分批次更新，不要一次更新太多数据（否则可能造成阻塞，锁争抢）;
- 减少与数据库的交互次数（连接池）;
- 注意使用性能分析工具;
- 注意程序捕获异常,打印日志;
- 请格式化SQL语句;
- 多使用explain 查看执行计划;
以上
That‘s all