MySQL数据库优化二三事

平时在开发新项目时，有时因为工期紧张，经常会以实现功能为目标，不太注意效率问题，特别是在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 查看执行计划;