MySQL 数据查询慢的解决方案

1. 开启慢查询日志

目的：

记录执行时间超过设定阈值的 SQL 查询，以便后续分析。

操作步骤：

1. 查看慢查询日志设置

sql SHOW VARIABLES LIKE '%slow_query_log%';
- 用于检查当前慢查询日志是否开启。

2. 开启慢查询日志
   sql SET GLOBAL slow_query_log = 1;
   - 启用慢查询日志功能。

3. 设置长查询时间阈值
   sql SHOW VARIABLES LIKE 'long_query_time%'; SET GLOBAL long_query_time = 3;
   - 设置慢查询时间阈值为 3 秒，可以根据实际需要调整。

4. 确认长查询时间设置
   sql SHOW GLOBAL VARIABLES LIKE 'long_query_time';
   - 确认当前设置的长查询时间。

2. 检查索引和查询计划

目的：

优化查询性能，确保索引使用合理。

操作步骤：

1. 使用 EXPLAIN 分析查询
   sql EXPLAIN SELECT * FROM t_query_user WHERE q_id = 1;
   - 查看查询的执行计划，判断是否使用索引。

2. 查看表索引
   sql SHOW INDEX FROM t_query_user;
   - 检查 t_query_user 表的索引情况，确保有利于优化查询。

3. 分析慢查询日志

目的：

找出性能瓶颈，优化查询。

操作步骤：

1. 获取返回记录集最多的 10 个 SQL
   bash mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
   - 按返回记录集数量排序，找出返回数据量大的查询，可能影响性能。

2. 获取访问次数最多的 10 个 SQL
   bash mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
   - 按访问次数排序，找出频繁执行的查询，可能导致负载。

3. 获取前 10 条包含左连接的慢查询
   bash mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
   - 特别关注复杂查询，识别性能问题。

4. 防止屏幕溢出

bash mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more
- 使用管道和 more 命令分页显示，防止输出过多信息导致溢出。

4. 改进方案

单表优化

单表优化手段有：

1. 建立并使用索引：索引是提升查询效率最有效的手段，所以对比查询比较频繁的字段一定要创建合适的索引。创建了索引之后，还要能正常的使用索引。
2. 优化查询语句：避免 SELECT *，只查询需要的字段。使用小表驱动大表，比如，当 B 表的数据小于 A 表时，先查 B 表，再查 A 表，查询语句：select * from A where id in (select id from B)。
3. 优化表结构和数据类型：单表不要有太多字段，建议在 20 个字段以内，使用可以存下数据最小的数据类型，尽可能使用 not null 定义字段，因为 null 占用 4 字节空间。

多表优化

多表优化手段有：

1. 表拆分：也就是分表，让每张表的数据量变小，从而提高查询效率。表拆分又分为：垂直分隔和水平分隔。
2. 垂直拆分：是指数据表列的拆分，把一张列比较多的表拆分为多张表，比如，用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中，插入数据时，使用事务确保两张表的数据一致性。
   1. 垂直拆分的原则：
      1. 把不常用的字段单独放在一张表；
      2. 把 text，blob 等大字段拆分出来放在附表中；
      3. 经常组合查询的列放在一张表中。
3. 水平拆分：指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。通常情况下，我们使用取模的方式来进行表的拆分，比如，一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，users3，users4，然后通过用户 ID 取模的方法，同时查询、更新、删除也是通过取模的方法来操作。
4. 读写分离：一般情况下对数据库而言都是“读多写少”，换言之，数据库的压力多数是因为大量的读取数据的操作造成的，我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。