MySQL 数据库优化二三事

微信扫一扫,分享到朋友圈

MySQL 数据库优化二三事

VOL 172

05

2020-10

今天距2021年56天

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

点击上方蓝字“
ITester软件测试小栈
“关注我,每周一



五早上 
08:3
0

准时推送,每月不定期 赠送技术书籍

微信公众号后台回复“
资源



测试工具包

”领取测试资源, 回复“
微信群
”一起进群打怪。

本文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会将内存临时表导出到磁盘临时表。

索引缓冲区
(k
ey_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

更多系列文章

<<  滑动查看下一张图片  >>

后台回复”
资源”
取干货

回复”


微信群”


一起打怪升级

个人微信:Cc2015123

添加请注明来意 🙂

真爱三连,上线无Bug
~

微信扫一扫,分享到朋友圈

MySQL 数据库优化二三事

搞清楚这8个问题,再去PK天猫营销IP

上一篇

看脸的时代,请尊重主播们“不露脸”的自由

下一篇

你也可能喜欢

MySQL 数据库优化二三事

长按储存图像,分享给朋友