MySQL 数据库优化二三事

VOL 172

2020-10

今天距2021年56天

这是ITester软件测试小栈第172次推文

点击上方蓝字“ ITester软件测试小栈 “关注我，每周一、三、五早上 08:3 0 准时推送，每月不定期 赠送技术书籍 。

微信公众号后台回复“ 资源 ” 、 “ 测试工具包 ”领取测试资源，回复“ 微信群 ”一起进群打怪。

本文4317字，阅读约需11分钟

平时在开发新项目时，有时因为工期紧张，经常会以实现功能为目标，不太注意效率问题，特别是在SQL语句上。

常见的数据库优化方法有哪些？简单来说是加索引，重建结构，杀进程，杀DBA……如果在一个没有DBA的公司，上线一时爽，事后火葬场，卑微测试一不小心背黑锅。

何以解忧？唯有学习和实践。测试人员也会和数据打交道，今天总结数据库的优化知识。主要介绍可以从哪些方面优化数据库，提高数据库的执行效率。

一

系统现存问题

问题背景

“系统慢不是问题，只要不崩溃就行”，这可能这是大多数懒癌技术派的想法。但是，如果系统经常抛出一些故障（硬件问题除外，不过如果磁盘经常坏，可能也和性能有关）。很多时候就是因为：没有使用绑定变量、错误的设置了一些优化器参数、并发过大、缺少索引（最普遍）、统计信息不准确、SQL写法不佳、RAC系统按照单节点设计等等一系列性能问题，导致系统压力过大而出现的状况。但是懒癌晚期们往往宁愿出故障时救火，却不愿意花时间去优化数据库。试想如果系统经过全面优化，负载很小，还会经常出各种问题吗？100%的数据库都是可以优化的，CPU降低，资源争用小，系统就会更加稳定；IO压力降低，SQL执行速度加快，磁盘寿命也会更长。

问题分析

设计上的问题：单列索引太多，总索引量太多，容易出现索引合并，优化器无法选中最优索引，间接导致大家使用 force index ，又导致了优化器无法自动智能选则执行计划。

使用上的问题：万能查询，多个接口并用，查询所有列，force index 滥用，单表数据量过大，SQL写法不规范。

二

数据库查询慢的探索

问题现象

SQL语句执行得很慢的原因有哪些？这个问题可以涉及到 MySQL 的很多核心知识，就像要考查计算机网络总问“输入URL回车之后，究竟发生了什么”一样。

SQL 语句执行的很慢，那是每次执行都很慢呢？还是大多数情况下是正常的，偶尔出现很慢呢？我们得分以下 2 种情况来讨论：

在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。
大多数情况是正常的，只是偶尔会出现很慢的情况。

针对这两种情况，我们来分析下可能是哪些原因导致的。

原因分析

总体来说，SQL 语句执行的很慢可能是由于以下原因：

扫描行数太多；
返回行数太多；
额外的操作（排序，分组，计算）；

根据SQL 执行慢的程度，我们再分2种情况具体讨论：

这条 SQL 语句一直执行的很慢，有如下原因：

没有用上索引：由于对字段进行运算、函数操作导致无法用索引。
数据库选错了索引。

大多数情况下很正常，偶尔很慢，有如下原因：

数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
执行的时候，遇到锁，如表锁、行锁。

问题定位

我们可以通过开启慢查询日志的方式去定位有问题的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. 新建表必须设置主键，推荐自增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 '备注，保留字段'

索引优化

1.最左前缀匹配原则，where条件的顺序应该尽量与索引列的顺序保持一致。

2.尽量选择区分度高的列作为索引。

3. 新建索引时，常用的范围查询，最好放在索引的最后面。

4.查看索引离散度 show index from his.tb_api_log。

5.索引尽量不在字符类型上使用，不频繁更新的字段上。

6.索引以 idx_字段1_字段2_字段n 命名字段可简写，顺序不能乱。

数据库配置优化

连接数 (connection)配置：当发现MySQL有能力处理更多的并发的时候，建议调大max_connections这个值，相应给服务器带来更高的负载(CPU/IO/内存)。

查询缓存 (query_cache)配置：MySQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化。

临时表缓存 (tmp_table_size)配置： MySQL 进行复杂查询或者做高级GROUP BY操作的时候，系统为了优化查询，生成一些临时表。一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表。

索引缓冲区 （k ey_buffer_size）配置：是对MyISAM表性能影响最大的一个参数，key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。

硬件资源优化

硬件层面的优化是最后的手段，主要需考虑CPU、存储、网络等几个方面。

CPU

：CPU并不是越多越好，增加CPU数量并不能提高性能。

存储 ：机械磁盘 or SSD（当然是SSD更快），单个大磁盘 or 多个小磁盘组合使用（单个1T的磁盘应该没有2个500G磁盘的组合快，因为磁盘的转速都是固定的，两个磁盘相当于可以并行的读取）。

网络 ：一般不是问题，但是在分布式的集群环境中，各个数据库节点之间的网络环境经常会称为系统的瓶颈。另外，如果服务端和数据库分布在不同的城市，一条简单SQL传输的时间可能就要几十毫秒。

四

数据库优化总结

其实在需求分析阶段就要预估可能的业务量和数据量，这样才能在建表时有针对的进行设计。否则抛开需求谈优化都是耍流氓，就像这个世界上没有包治百病的神药一样，也不会有解决一切问题的完美技术。所以数据库的设计必须和需求有关系，因为表结构也得符合需求，一套数据库的设计也是和需求有密切关系。一个需求里面会体现对某个表是侧重读取还是侧重写入，数据设计应尽可能遵循如下原则：