【面经】SQL优化方法论

逃离我推掉我的手 2024-02-19 10:16 4阅读 0赞

## SQL优化方法论 ##

### 1.检查有没有索引或索引失效 ###

#### 打开MySQL慢查询日志 ####

要打开MySQL慢查询日志，可以按照以下步骤进行操作：

1.  打开MySQL配置文件。慢查询日志的配置项通常位于MySQL的配置文件my.cnf或my.ini中。你可以在MySQL安装目录下的"bin"或"etc"文件夹中找到这个文件。
2.  找到`[mysqld]`部分。这是MySQL服务器的配置部分。
3.  添加或修改`slow_query_log`选项。在`[mysqld]`部分中，找到或添加以下行：

slow_query_log = 1

这将启用慢查询日志。  
4. 添加或修改`slow_query_log_file`选项。这指定了慢查询日志文件的路径和名称。例如，可以将它设置为：

slow_query_log_file = /var/log/mysql/mysql-slow.log

根据你的需要，你可以选择不同的路径和文件名。  
5. 添加或修改`long_query_time`选项。这定义了慢查询的阈值，以秒为单位。默认值是0.001秒（1毫秒）。你可以根据需要将其设置为适当的值。例如，将阈值设置为2秒：

long_query_time = 2

1.  保存并关闭配置文件。
2.  重启MySQL服务器。要使配置更改生效，你需要重新启动MySQL服务器。你可以使用以下命令重启MySQL服务：

sudo service mysql restart

或者，根据你的操作系统和安装方式，使用适当的命令来重启MySQL服务。

完成上述步骤后，MySQL将开始记录慢查询日志到指定的文件中。你可以使用常规的文本编辑器打开日志文件进行查看和分析。

也可以通过SQL检查数据库服务器有没有开启慢查询日志

show variables like '%slow_query_log%';

以上SQL执行后若返回结果里显示ON，表示已经开启了MySQL慢查询日志

#### 用explain查看SQL是否命中索引 ####

在MySQL中，可以使用`EXPLAIN`语句来查看查询语句的执行计划，从而了解查询是否命中了索引。`EXPLAIN`的结果提供了关于查询执行方式的详细信息，包括使用的索引、表之间的连接方式、数据的读取顺序等。

下面是如何查看`EXPLAIN`结果的方法：

1.  运行`EXPLAIN`语句：

EXPLAIN SELECT * FROM your_table WHERE your_column = 'your_value';

将`your_table`替换为实际的表名，`your_column`替换为实际的列名，`your_value`替换为实际的值。  
2. 查看结果：

`EXPLAIN`的结果将返回一个表格，其中包含以下信息：

* `id`：表示查询的标识符，对于简单查询，通常为1。
    * `select_type`：表示查询的类型，常见的类型包括`SIMPLE`（简单查询）、`PRIMARY`（外层查询）、`SUBQUERY`（子查询）等。
    * `table`：表示相关的表名。
    * `type`：表示表的连接类型，常见的类型包括`ALL`（全表扫描）、`index`（索引扫描）、`range`（范围扫描）、`ref`（索引引用）等。
    * `possible_keys`：表示可能使用的索引列表。
    * `key`：表示实际使用的索引。
    * `key_len`：表示使用的索引长度。
    * `ref`：表示与索引比较的列或常量。
    * `rows`：表示预计扫描的行数。
    * `Extra`：表示其他附加信息，例如是否使用了临时表、是否使用了文件排序等。

1.  通过查看`type`字段来判断是否命中了索引：

如果`type`字段的值是`index`或`range`，则表示命中了索引。如果`type`字段的值是`ALL`，则表示全表扫描，没有使用索引。其他类型的值可能是组合查询或特定类型的连接方式。

此外，还可以关注以下几点：

*  如果`key`字段的值与查询的列名相同，则表示使用了相应的索引。
 *  如果`key_len`字段的值与索引的长度相同，则表示使用了整个索引。如果值较小，则表示只使用了索引的一部分。
 *  如果`Extra`字段包含`Using index`或类似的提示，则表示使用了覆盖索引（Covering Index）。这通常意味着索引包含了所有需要的数据，而无需再访问表数据。

通过仔细分析`EXPLAIN`的结果，你可以了解查询是否命中了索引，以及查询的性能如何。这有助于优化查询语句和数据库性能。

#### 优化SQL语句,使SQL走索引 ####

优化SQL语句以使其能够有效地使用索引，可以提高数据库的查询性能。以下是一些优化SQL查询的注意事项：

1.  确保正确使用索引：要确保你的数据库表上存在正确的索引。在创建索引时，要考虑到查询中最常见的查询条件和排序条件，以及经常用于联接的表列。
2.  避免使用SELECT \*：避免在查询中使用SELECT \*，而是显式地指定所需的列。这可以减少查询的数据量，并可能触发索引的使用。
3.  避免在WHERE子句中使用非索引列：如果WHERE子句中使用的列没有索引，那么索引将不会被使用。因此，要确保你的WHERE子句中使用的列已经被正确地索引。
4.  避免在查询中使用函数或计算：在查询中使用函数或计算会使数据库无法使用索引。尽可能避免在查询中使用函数或计算，除非没有其他选择。
5.  考虑使用覆盖索引（Covering Index）：覆盖索引是指包含所有需要的数据的索引，而不仅仅是索引列本身。使用覆盖索引可以减少对表数据的访问，提高查询性能。
6.  优化联接查询：对于涉及多个表的查询，确保使用了正确的联接方式（如INNER JOIN、LEFT JOIN等），并确保联接列已经被正确地索引。
7.  考虑使用索引提示：根据你使用的数据库管理系统，可以考虑使用索引提示来指导数据库优化器选择更有效的查询执行计划。
8.  定期分析表和重新组织表：定期对表进行分析和重新组织，可以维护表的结构和性能。
9.  使用EXPLAIN或类似的工具：通过使用数据库的EXPLAIN或类似的工具，可以分析查询的性能并了解查询是如何与索引交互的。这可以帮助你优化查询并改进查询性能。
10. 避免在索引列上使用IS NULL或者IS NOT NULL
11. 尽量去掉"(>"，避免全表扫描，如果数据是枚举值，且取值范围固定，可以使用"or"方式
12. 尽量去掉"IN",“OR”

含有"IN"、"OR"的where子句常会使用工作表，使索引失效，如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引；
    
    select count(*) from stuff where id_no in('0','1')
    
    可以拆开为：
    
    select count(*) from stuff where id_no='0'
    
    select count(*) from stuff where id_no='1'
    
    然后在做一个简单的加法

1.  不要在选择性较低的字段建立索引

在选择性较低的字段使用索引，不但不会降低逻辑I/O，相反，往往会增加大量逻辑I/O降低性能。比如，性别列，男和女

1.  尽量使用前端匹配的模糊查询

例如，column1 like 'ABC%'方式，可以对column1字段进行索引范围扫描；
    而column1 kike '%ABC%'方式，即使column1字段上存在索引，也无法使用该索引，只能走全表扫描

1.  使用索引来避免排序操作

在执行频度高，又含有排序操作的sql语句，建议适用索引来避免排序。
    排序是一种昂贵的操作，在一秒钟执行成千上万次的sql语句中，如果带有排序操作，往往会消耗大量的系统资源，性能低下。
    索引是一种有序结果，如果order by后面的字段上建有索引，将会大大提升效率

1.  用exists、not exists和in、not in相互替代

原则是哪个的子查询产生的结果集小，就选哪个
    
    select * from t1 where x in (select y from t2)
    
    select * from t1 where exists (select null from t2 where y =x)
    
    IN适合于外表大而内表小的情况；exists适合于外表小而内表大的情况

1.  使用exists替代distinct

当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在select子句中使用distinct，
    一般可以考虑使用exists代替，exists使查询更为迅速，因为子查询的条件一旦满足，立马返回结果。
    
    低效写法：
    
    select distinct dept_no,dept_namefrom dept d,emp e where d.dept_no=e.dept_no
    
    高效写法：
    
    select dept_no,dept_name from dept d where  exists (select 'x' from emp e where e.dept_no=d.dept_no)
    
    备注：其中x的意思是：因为exists只是看子查询是否有结果返回，而不关心返回的什么内容，因此建议写一个常量，性能较高！
    
    用exists的确可以替代distinct，不过以上方案仅适用dept_no为唯一主键的情况，如果要去掉重复记录，需要参照以下写法：
    
    select * from emp  where dept_no exists (select Max(dept_no)) from dept d, emp e where e.dept_no=d.dept_no group by d.dept_no)

总之，优化SQL查询需要考虑到多个方面，包括索引设计、查询语句的编写以及数据库表的结构。通过仔细分析和调整这些因素，可以显著提高查询性能。

如果SQL结构没有办法优化，可以考虑在表上再添加对应的索引，在优化SQL或添加索引时，都需要符合最左匹配原则。

索引最左匹配原则是指在使用索引进行查询时，应该尽量使用索引的左侧列进行匹配。这个原则是由于索引是按照列的顺序进行排序的，而左侧列的值变化范围较小，因此可以更快地定位到需要查询的数据。
    
    例如，假设有一个包含姓名、年龄、性别等信息的表，其中姓名是索引列。如果要查询姓名为“张三”且年龄为“20”的记录，那么应该先使用姓名进行匹配，再使用年龄进行筛选。这样可以利用索引的左侧列进行匹配，提高查询效率。
    
    最左匹配原则在复合索引中也同样重要。在复合索引中，索引首先按照第一个索引字段进行排序，然后再按照第二个索引字段进行排序。因此，如果查询条件中包含了复合索引的第一个字段，那么整个复合索引都可以被利用，从而提高了查询效率。
    
    需要注意的是，最左匹配原则并不总是适用。例如，如果查询条件中包含了范围查询（例如BETWEEN...AND...），那么最左匹配原则可能会失效，因为范围查询会跳过索引的顺序，导致无法利用索引中的其他列。在这种情况下，需要根据具体情况来考虑是否需要重新设计索引或者调整查询语句。

#### MySQL 索引失效的情况 ####

1.  索引列没有被使用：查询的条件不符合索引的最左匹配原则，或查询的数据量很小导致直接全表扫描更有效
2.  索引列有表达式计算：如where条件中使用了mysql日期函数 或vg/max/min/round等函数
3.  SQL字段类型与索引列不匹配
4.  索引列上进行了排序或加密
5.  使用了not in 或 <> 操作符
6.  索引列上的数据分布不均匀
7.  高基数列：索引列上存在多个重复的值

### 2.检查是不是单表数据量过多，导致查询瓶颈 ###

单表数据量过高如超过2000w，可以考虑分表/分库方案

#### 分表 ####

1.  水平分表：对单表按主键把数据分片为多个表，如把tb\_a表分为 tb\_a\_0 , tb\_a\_1 ；tb\_a\_0 存1,3,5奇数行 ，tb\_a\_1 存2,4,6偶数行 。
2.  垂直分表：对单表中的多个列拆分，把业务关联性比较大的列放到同一个表中，如把一个 **用户表** 拆分为 **用户基础信息表** 和 **用户扩展信息表**

#### 分库 ####

1.  水平分库：对某类数据库按id取模把数据分配到不同的数据库 ，如 user\_db\_0 , user\_db\_1, user\_db\_2
2.  垂直分库: 按业务拆分成不同的数据库，如 把1个大的sysdb 拆分为 user\_db , order\_db , account\_db 等等

### 3.检查网络原因或机器负载较高 ###

#### 检查MySQL服务器的网络情况 ####

要检查MySQL服务器的网络情况和负载情况，可以采取以下步骤：

1.  连接到MySQL服务器：可以使用命令行工具（如ssh）或图形界面工具（如phpMyAdmin）连接到MySQL服务器。
2.  检查网络配置：通过网络配置文件（如my.cnf）检查MySQL服务器的网络配置，包括IP地址、端口号、用户名和密码等。确保这些配置正确无误。
3.  检查连接数：使用以下命令检查当前连接数：SHOW STATUS LIKE ‘Threads\_connected’;该命令将返回一个名为Threads\_connected的状态变量，表示当前已连接的客户端数。
4.  检查活动查询数：使用以下命令检查当前活动查询数：SHOW STATUS LIKE ‘Threads\_running’;该命令将返回一个名为Threads\_running的状态变量，表示当前正在执行的查询数。
5.  检查数据库运行时间：使用以下命令检查数据库运行的时间：SHOW STATUS LIKE ‘Uptime’;该命令将返回一个名为Uptime的状态变量，表示数据库服务器已经运行的秒数。
6.  计算平均连接时间：通过将已连接的客户端数除以数据库运行的秒数，可以计算出平均连接时间。以下是一个示例查询：SELECT Threads\_connected/Uptime AS Avg\_Connection\_Time FROM information\_schema.global\_status WHERE variable\_name=‘Threads\_connected’;该查询将返回一个名为Avg\_Connection\_Time的值，表示平均连接时间。
7.  检查负载情况：通过以上步骤可以了解MySQL服务器的网络配置、连接数、活动查询数、数据库运行时间和平均连接时间等信息，从而评估服务器的负载情况。如果负载过高，可能需要采取相应措施来优化数据库性能或增加服务器资源。

#### 高负载情况下可以用MySQL读写分离和主从复制 ####

> 读写分离

使用mysql一主多从的分布式部署，主库写，从库读。
    在大流量场景中可以增加从库来提高数据库的负载能力

> 主从复制

主库上执行SQL，执行结果复制到从库。
    
    主从复制有3种类型：
    1.基于SQL语句的复制（默认），即执行相同的SQL语句
    2.基于行的复制 （将主库改变的内容复制到从库）
    3.混合类型复制（1,2两种都有）

### 4.检查热点数据导致单点负载不均衡 ###

#### 用监控工具检查MySQL热点数据 ####

用监控工具检查MySQL热点数据，可以采取以下步骤：

1.  监控数据库性能：使用监控工具（如Percona Monitoring and Management、MySQL Enterprise Monitor等）或操作系统工具（如top、htop等）监控数据库服务器的性能指标，包括CPU使用率、内存使用率、磁盘I/O等。
2.  分析查询日志：查看MySQL的查询日志，分析查询的热点数据和访问模式。可以使用工具（如MySQL Enterprise Query Analyzer）或编写脚本解析查询日志，找出哪些表、列或时间段是查询的热点。
3.  使用慢查询日志：启用MySQL的慢查询日志功能，记录执行时间超过一定阈值的查询。通过分析慢查询日志，可以发现哪些查询导致数据库负载过高，进而找出热点数据和潜在的性能瓶颈。
4.  使用性能剖析器：使用性能剖析器（如MySQL Enterprise Performance Schema、pt-mysql-summary等）收集数据库服务器上的性能数据，包括查询执行时间、锁等待、缓存命中率等。通过分析性能数据，可以找出导致负载不均衡的热点数据和潜在的性能问题。
5.  优化数据库架构：根据分析结果，针对热点数据进行优化。可以采取的措施包括：
    
     *  添加索引：为热点数据表添加适当的索引，提高查询效率。
     *  缓存数据：将热点数据缓存到内存中，减少磁盘访问次数。
     *  分区分表：将热点数据分散到不同的数据库或表中，减轻单点负载压力。
     *  水平扩展：增加数据库服务器数量，实现负载均衡。
6.  调整数据库配置：根据实际情况调整MySQL服务器的配置参数，如增加缓冲区大小、调整连接数限制等，以提高数据库的性能和可扩展性。
7.  定期评估和优化：定期检查数据库服务器的性能和负载情况，根据需要进行优化和调整。同时，关注数据库服务器的新技术和工具，及时采用更高效和先进的技术来提高数据库的性能和可用性。

#### 增加多级缓存，将热点数据预存到缓存中 ####

1.  一级缓存技术（内存缓存如Caffenine）
2.  二级缓存技术（如Redis ，MongoDB ,ElasticSearch等）
3.  三级缓存 \[ mybatis sqlsession(1级)+ mapper（2级）\]