666.11.调优

予早 2025-09-01 00:51:32

Categories： Tags：

数据库优化

软优化

1.查询语句优化

一般查询

首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息。

EXPLAIN SELECT * FROM `user`

其中会显示索引和查询数据读取数据条数等信息。

子查询

在MySQL中，尽量使用JOIN来代替子查询。因为子查询需要嵌套查询，嵌套查询时会建立一张临时表，临时表的建立和删除都会有较大的系统开销，而连接查询不会创建临时表，因此效率比嵌套子查询高。

或者将逻辑移交给后端代码完成，压力给到后端。

慢查询

慢查询，查询语句优化的主要目标

关于order by和limit联用

并非是先order by全部排完序，然后limit

实际上进行了优化，使用堆的形式，对于非排序字段会造成顺序的不确定性，每次limit结果不太一样，最好的优化是order by category,id，附加通过主键排序，这样有一个唯一的顺序

https://blog.csdn.net/weixin_39604280/article/details/111689218

mysql数据库中允许select后含有非group by中的列

0000

2. 插入语句优化

针对插入，我们可以通过禁用索引、禁用检查等方式来提高插入速度，在插入之后再启用索引和检查。影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况，可以分别进行优化。

对于MyISAM引擎的表，常见的优化方法如下：

禁用索引

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。或者对于innodb而言，主键最好是自增的，b+tree插入更高效，uuid就不是，可以使用雪花算法
禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。
使用批量插入

插入多条记录时，可以使用一条INSERT语句插入一条记录，也可以使用一条INSERT语句插入多条记录。使用一条INSERT语句插入多条记录的情形如下，而这种方式的插入速度更快。
```
INSERT INTO fruits VALUES ('x1', '101', 'mongo2', '5.7'), ('x2', '101', 'mongo3', '5.7'), ('x3', '101', 'mongo4', '5.7');
```
使用LOAD DATA INFILE批量导入

当需要批量导入数据时，如果能用LOAD DATA INFILE语句，就尽量使用。因为LOAD DATA INFILE语句导入数据的速度比INSERT语句快。

对于InnoDB引擎的表，常见的优化方法如下：

禁用唯一性检查

插入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_checks=1。这个和MyISAM引擎的使用方法一样。
禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。
禁用自动提交

插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

3.合理使用索引

索引是提高数据库查询速度最重要的方法之一，使用索引的三大注意事项包括：

LIKE关键字匹配’%’开头的字符串，不会使用索引；
OR关键字的两个字段必须都是用了索引，该查询才会使用索引；
使用多列索引必须满足最左匹配。

4.重构表，分解表、中间表、冗余字段

分解表：对于字段较多的表，如果某些字段使用频率较低，此时应当将其分离出来从而形成新的表。

中间表：对于将大量连接查询的表可以创建中间表，从而减少在查询时造成的连接耗时。

冗余字段：类似于创建中间表，增加冗余也是为了减少连接查询。

5.分析表、检查表、优化表

分析表主要是分析表中关键字的分布；检查表主要是检查表中是否存在错误；优化表主要是消除删除或更新造成的表空间浪费。

分析表：使用 ANALYZE 关键字，如ANALYZE TABLE user

Op：表示执行的操作；
Msg_type：信息类型，有status、info、note、warning、error；
Msg_text：显示信息。

检查表：使用 CHECK关键字，如CHECK TABLE user [option]。 option 只对MyISAM有效。共五个参数值：

QUICK：不扫描行，不检查错误的连接；
FAST：只检查没有正确关闭的表；
CHANGED：只检查上次检查后被更改的表和没被正确关闭的表；
MEDIUM：扫描行，以验证被删除的连接是有效的，也可以计算各行关键字校验和；
EXTENDED：最全面的的检查，对每行关键字全面查找。

优化表：使用OPTIMIZE关键字，如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志，优化表只对VARCHAR、BLOB和TEXT有效，通过OPTIMIZE TABLE语句可以消除文件碎片，在执行过程中会加上只读锁。

硬优化

基础硬件：CPU、内存、磁盘
数据库参数
架构调整：分库分表、读写分离，缓存集群

基础硬件：CPU、内存、磁盘

配置多核心和频率高的cpu，多核心可以执行多个线程；
配置大内存，提高内存，即可提高缓存区容量，因此能减少磁盘I/O时间，从而提高响应速度；
配置高速磁盘或合理分布磁盘：高速磁盘提高I/O，分布磁盘能提高并行操作的能力。

数据库参数

优化数据库参数可以提高资源利用率，从而提高MySQL服务器性能。MySQL服务的配置参数都在my.cnf或my.ini，下面列出性能影响较大的几个参数：

key_buffer_size：索引缓冲区大小；
table_cache：能同时打开表的个数；
query_cache_size和query_cache_type：前者是查询缓冲区大小，后者是前面参数的开关，0表示不使用缓冲区，1表示使用缓冲区，但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区，2表示在查询中明确指出使用缓冲区才用缓冲区，即SQL_CACHE；
sort_buffer_size：排序缓冲区

架构调整

分库分表、读写分离

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。

另外一个，压力过大把你的数据库给搞挂了怎么办？

所以此时你必须得对系统做分库分表+读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

缓存集群

由缓存集群承载大部分读操作

优化场景

表中包含几千万条数据

建议按照如下顺序进行优化：

优化SQL和索引；
增加缓存，如memcached、redis；
读写分离，可以采用主从复制，也可以采用主主复制；
使用MySQL自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的；
做垂直拆分，即根据模块的耦合度，将一个大的系统分为多个小的系统；
做水平拆分，要选择一个合理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。

潜在慢查询拖慢调用链路

优化MySQL的慢查询，可以按照如下步骤进行：

开启慢查询日志：

MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开，也可以在MySQL服务启动的时候使用–log-slow-queries[=file_name]启动慢查询日志。

启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

分析慢查询日志：

直接分析mysql慢查询日志，利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。

常见慢查询优化：

索引没起作用的情况
- 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置，索引才会起作用。
- MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段时索引才会被使用。
- 查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。
优化数据库结构
- 对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
- 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
分解关联查询

很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。
优化LIMIT分页

当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。