发布日期: 2023-06-06

文章字数: 2.2k

阅读时长: 8 分

1 调优金字塔

1.1 架构调优

调优金字塔

在进行优化时，首先需要关注和优化的应该是架构，如果架构不合理，即使是 DBA 能做的事情其实是也是比较有限的。

对于架构调优，在系统设计时首先需要充分考虑业务的实际情况：

是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做？
然后考虑写的并发量有多大，是否需要采用分布式；
最后考虑读的压力是否很大，是否需要读写分离。
对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失。

采用更适合业务场景的架构能最大程度地提升系统的扩展性和可用性。在设计中进行垂直拆分能尽量解耦应用的依赖，对读压力比较大的业务进行读写分离能保证读性能线性扩展，而对于读写并发压力比较大的业务在 MySQL 上也有采用读写分离的大量案例。

作为金字塔的底部，在底层硬件系统、SQL 语句和参数都基本定型的情况下，单个 MySQL 数据库能提供的性能、扩展性等就基本定型了。但是通过架构设计和优化，却能承载几倍、几十倍甚至百倍于单个 MySQL 数据库能力的业务请求能力。

1.2 MySQL 调优

需要确认业务表结构设计是否合理；
SQL 语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引等等。

1.3 系统、硬件优化

系统瓶颈在哪里？
哪些系统参数需要调整优化？
进程资源限制是否提到足够高？
在硬件方面是否需要更换为具有更高 I/O 性能的存储硬件？
是否需要升级内存、CPU、网络等。

2 查询性能优化

2.1 慢查询

指mysql 记录所有执行超过 long_query_time 参数设定的时间阈值的SQL语句的日志。

默认是关闭的.

2.2 优化数据访问

最基本的原因是访问的数据太多。
请求了不需要的数据？
- 查询不需要的记录
- 总是取出全部列
- 重复查询相同的数据
是否在扫描额外的记录？
- 衡量查询开销的三个指标如下：响应时间、扫描的行数、返回的行数

2.3 重构查询的方式

2.3.1 一个复杂查询还是多个简单查询？

MySQL 内部每秒能够扫描内存中上百万行数据，相比之下，MySQL 响应数据给客户端就慢得多了。在其他条件都相同的时候，使用尽可能少的查询当然是更好的。但是有时候，将一个大查询分解为多个小查询是很有必要的。

不过，在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询，显然是不明智的。

2.3.2 切分查询

对于一个大查询我们需要“分而治之”，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

比如：定期清理大量旧数据。

2.3.3 分解关联查询

可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。

优势：

让缓存的效率更高；
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。查询本身效率也可能会有所提升。
可以减少冗余记录的查询：在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复地访问一部分数据。从这点看，这样的重构还可能会减少网络和内存的消耗。
相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高很多。比如：
- 当应用能够方便地缓存单个查询的结果的时候；
- 当可以将数据分布到不同的MySQL服务器上的时候；
- 当能够使用IN()的方式代替关联查询的时候；
- 当查询中使用同一个数据表的时候。

2.4 慢查询配置

2.4.1 慢日志开启

查询：

show VARIABLES like 'slow_query_log';

开启：

set GLOBAL slow_query_log=1;

2.4.2 多久算慢？

MySQL中可以设定一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志中。long_query_time参数就是这个阈值。默认值为10，代表10秒。

查询（默认是10秒）：

show VARIABLES like '%long_query_time%';

设定自定义值：

set global long_query_time=15;

2.4.3 是否记录未使用索引的SQL?

show VARIABLES like '%log_queries_not_using_indexes%';

2.5 慢查询解读分析

a slow log

2.5.1 格式分析

Time: 2021-04-05T07:50:53.243703Z：查询执行时间；
User@Host: root[root]@localhost [] Id: 3：用户名、用户的IP信息、线程ID号；
Query_time: 0.000495：执行花费的时长【单位：毫秒】；
Lock_time: 0.000170：执行获得锁的时长；
Rows_sent：获得的结果行数；
Rows_examined：扫描的数据行数；
SET timestamp：这SQL执行的具体时间；
最后一行：执行的SQL语句。

2.5.2 慢查询分析

语法：

mysqldumpslow -s r -t 10 slow-mysql.log -s order (c,t,l,r,at,al,ar)
c:总次数
t:总时间
l:锁的时间
r:获得的结果行数
at,al,ar: 指 t,l,r 平均数 【例如:at = 总时间/总次数】
-s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为 at
-t NUM just show the top n queries：仅显示前 n 条查询
-g PATTERN grep: only consider stmts that include this string:通过 grep 来筛选语句。

2.6 Explain 执行计划

通过 EXPLAIN 我们可以:

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

explain sample

2.6.1 字段详解

id: 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id；
select_type: SELECT 关键字对应的那个查询的类型；
- SIMPLE:简单的 select 查询，不使用 union 及子查询
- PRIMARY:最外层的 select 查询
- UNION:UNION 中的第二个或随后的 select 查询,不依赖于外部查询的结果集
- UNION RESULT:UNION 结果集
- SUBQUERY:子查询中的第一个 select 查询,不依赖于外部查询的结果集
- DEPENDENT UNION:UNION 中的第二个或随后的 select 查询,依赖于外部查询的结果集
- DEPENDENT SUBQUERY:子查询中的第一个 select 查询,依赖于外部查询的结果集
- DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会递归执行这些子查询, 把结果放在临时表里。
- MATERIALIZED:物化子查询
- UNCACHEABLE SUBQUERY:结果集不能被缓存的子查询,必须重新为外层查询的每一行进行评估，出现极少。
- UNCACHEABLE UNION:UNION 中的第二个或随后的 select 查询，属于不可缓存的子查询，出现极少。
table: 表名；
partitions: 匹配的分区信息；
type: 针对单表的访问方法：结果值从最好到最坏依次是: system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL；
possible_keys: 可能用到的索引；
key: 实际上使用的索引；
key_len: 实际使用到的索引长度；
ref: 当使用索引列等值查询时，与索引列进行等值匹配的对象信息；
rows: 预估的需要读取的记录条数；
filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比；
Extra: 一些额外的信息。