MySQL优化方法整理备忘

最新推荐文章于 2024-09-13 10:10:28 发布

wangnan229

最新推荐文章于 2024-09-13 10:10:28 发布

阅读量114

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/wangnan229/article/details/105417245

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本篇介绍MySQL的优化方法

文章目录

优化目标：

① 加快响应速度 ☆☆☆☆☆
② 减少数据冗余 ☆☆
③ 提高MySQL语句本身的可读性、易维护性 ☆☆
④ 为进一步提升性能提供参考，比如感觉单机性能已经没有升级余地，于是升级成主从复制。 ☆☆☆
大概思路：
将优化分为几个大角度、大块，然后挑选其中常见的、自己用过的、前人的经验自己也实践过的、自认为挺管用的地方重点分享下，中间对于各个小块都有例子。主要是分享具体的实现方法，对于实现原理就不去多深究了，每一个点几乎展开说的话都有很多细节，所以感兴趣的童靴就请自行钻研文档、谷歌吧。

优化的几个大块/大方向：

一、 MySQL数据库运行环境优化-服务器（附录4 优化服务器设置）
二、 MySQL数据库参数优化
三、 MySQL表优化
四、 MySQL查询语句优化

自己接触较多的是三、四，即表优化、语句优化，这二者其实是相辅相成的。

MySQL表优化：

可以分为表Schema优化、索引优化等。

① Schema优化：
就是在最初建立表Schema，也就是表结构的时候，字段类型是否合理，数据冗余是否合理（附录①建模）。对于数据库来说，比如若有ODS加工明细层、DW按主题汇总层、DM展示层三层。如果我们要优化的表位于DM层，且反映的是实时查询的指标，需要经过ODS->DW->DM的计算过程且耗时必须可以接受，优化目标是加快响应速度，那可能会考虑：
a. ODS层尽量减少数据/字段冗余，DW层保留适当的冗余，DM层保留适当的冗余；
b. 合理设计选择字段类型、长度，减少自动类型转换（较新版本的MySQL，这个问题已经不是很明显）；
c. 建议选择默认的InnoDB存储引擎。
（Q:表Scema设计的规范有哪些呢？附录①建模）
② 索引优化：
MySQL数据流(InnoDB)： MySQL服务器层（数据表/索引） 存储引擎层（索引字段直接读取数据）  应用层
一般遵循的原则：
a. 对经常在WHERE、GROUP BY、ORDER BY、ON 中出现的字段进行索引，而对输出字段尽量避免索引，但覆盖索引除外，即输出字段全部为索引字段的情况。（附录2索引b）
b. 对于数据列基数较高的字段创建索引，类似于 “性别”，只有 “男、女”2个值的字段来说，一般可以不用创建索引。因为寻找该字段数据的概率基本相等，但是实际当中最好还是加上索引，因为多数情况下加上的好处更多些。（附录2索引a）
c. 对于经常组团出现在条件中的字段，可以将其设置为组合索引（附录2索引a）。
d. 小规模的表，最好不要创建索引（百、千以下）；中到大规模的表创建索引是比较合适的，特大规模的表不适合创建索引。
Sample：
Q&A:多大规模就不适合建立索引了呢？举例还是BSEG表， 2000w行，数据大小4G，加索引效果很好。基本上，对于超大表，可以先加索引测试，如果提速效果不明显再去除索引。

MySQL语句优化：

总结的几点：
① 尽量让最少的行、列数据参与查询：只选择需要的字段，避免 select * from tb，保证临时表的数据量在满足需求的情况下为最少。这样可以减少IO，也能减少计算量。
Sample：BSEG表
② 索引的合理使用，以及避免不走索引的写法。Where语句中存在 !=、null、>、<、is null、like ‘%a’、or(union 替换)、not in，字段不要写表达式。
Sample：BSEG表
③ 检查逻辑是否有优化的空间：
a. 比如复杂过程的计算，将其拆分成多个较简单的计算步骤，避免重复查询计算。
Sample：P_RPT_DATA_YUFU_DETAIL 对比 P_RPT_DATA_YUFU_DETAIL_2017
b. 再就是检查执行慢的逻辑能否替换。本人遇到过，原代码是从依赖表A中while循环取每条数据，然后从B表中取出与这条数据对应的数据，然后拼接插入到目标表C中，每次执行时面对几千条数据，就是扫描循环几千次，效率很低。后优化为将A、B表进行join，直接插入C中，时间大幅缩短。就是建议大家，对待语句要有怀疑精神，不要被现有代码左右自己的想法。
Sample：P_RPT_E2ESY_XW 对比 P_RPT_E2ESY_XW_2016。
④ 在应用层进行优化。如果数据库瓶颈很难突破，可以考虑下将逻辑运算放在应用层进行。
Sample：暂无
⑤ 尝试不同的写法，比较哪种写法的优化效果最优，不符合预期的写法要重新分析。
因为MySQL版本的不同，部分优化方法可能在较新版本中效果并不明显，所以在优化时可以反复尝试不同的方法。尤其是在5.6以后的版本。

参数优化：

① InnoDB缓冲池：innodb_buffer_pool_size 、innodb_buffer_pool_instances
Sample：
查询缓存：query_cache 5.7版本已经没有了
② datadir : 数据库表文件路径，尽量设置在性能较好的MySQL独用的磁盘上。

附录：

①建模：
a. 《高性能MySQL（第3版）》：4.3 范式和反范式
b.数据仓库的几种建模方法：https://segmentfault.com/a/1190000012882641
②索引：
a. 《高性能MySQL（第3版）》：5.4.1 支持多种过滤条件（第5章创建高性能的索引整章值得一读）
b. 《高性能MySQL（第3版）》：6.2.2 MySQL 是否在扫描额外的记录P200
③参数：
a. 《高性能MySQL（第3版）》：8.4.5 InnoDB缓冲池(buffer pool) P342
b. 官方文档：8.5.9优化InnoDB配置变量
https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-configuration-variables.html
③ 优化服务器设置：第8章优化服务器设置 P325

新增：mysqlslap命令的使用等：

http://smilejay.com/2013/04/mysqlslap-mysql-stress-test/

mysqlslap -a -c 100 -uroot -p0000

show variables like ‘%innodb_buffer_pool_reads%’;

show variables like ‘%Innodb_row_lock_waits%’;

116692804
SHOW GLOBAL STATUS LIKE ‘Questions’;
15822062
SHOW GLOBAL STATUS LIKE ‘Uptime’;
QPS:每秒的查询数:
select 116692804/15822062 #= 7.38

TPS:每秒的事物量（commit与rollback的之和）：
Com_commit = SHOW GLOBAL STATUS LIKE ‘Com_commit’; 1783475
Com_rollback = SHOW GLOBAL STATUS LIKE ‘Com_rollback’; 1752451
Uptime = SHOW GLOBAL STATUS LIKE ‘Uptime’; 15822147
TPS=(Com_commit + Com_rollback)/Uptime
select (1783475+1752451)/15822147; 0.2235

SHOW GLOBAL STATUS LIKE ‘Threads_running’;