MySQL调优「连环问」

折叠伞Y
116 阅读13分钟

1、线上SQL调优经验？

  1. slow_query_log 日志中收集到的慢SQL,结合 explain 分析是否命中索引。
  2. 减少索引扫描行数，有针对性的优化慢SQL。
  3. 建立联合索引，由于联合索引的每个叶子节点包含检索字段的信息，按最左前缀原则匹配后，再按其它条件过滤，减少回表的数据量。
  4. 还可以使用虚拟列和联合索引来提升复杂查询的执行效率。

2、如何做索引优化？

  1. 分页优化。比如电梯直达，limit 100000,10 先查找起始的主键id，再通过id>#{value}往后取10条
  2. 尽量使用覆盖索引，索引的叶节点中已经包含要查询的字段，减少回表查询
  3. SQL优化（索引优化、小表驱动大表、虚拟列、适当增加冗余字段减少连表查询、联合索引、排序优化、慢日志 Explain 分析执行计划）。
  4. 设计优化（ 避免使用NULL、用简单数据类型如int、减少 text 类型）。
    • 如果数据量太大，可以采用分库分表
  5. 硬件优化（使用SSD 减少 I/O 时间、足够大的网络带宽、尽量大的内存）
  6. 可以使用force index()，防止优化器选错索引

3、如何定位慢查询？

慢查询的情况：聚合查询、多表查询、表数据量过大查询、深度分页查询

表现形式：页面加载过慢、接口测试的时候接口响应时间过长(超过1s)

定位慢查询通常有两种方案：

  • 方案一：开源工具

    • 测试工具：Arthas（它可以使用命令的方式来去监控已经上线的项目，可以追踪执行比较慢的方法，然后查看方法的执行时间，最终就可以确定哪里出现了问题）
    • 运维工具：Prometheus、Skywalking（Skywalking为例，在监控中，它会有一些指标数据，我们可以实时查看接口的情况）

    如果说你在项目中使用过类似工具，那么你就可以跟面试官阐述这些工具的使用方式，若没有使用过，也没有关系，我们来看MySQL自带的工具，也就是方案二。

  • 方案二：MySQL自带日志

    慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志

    MySQL是默认没有开启慢日志查询的功能，如果要开启慢查询日志，我们需要在MySQL的配置文件(/etc/my.cnf)中配置两项功能：

    -- 1.开启MySQL慢日志查询开关
slow_query_log=1  -- 1代表开启了慢日志查询，0代表没有开启
-- 2.设置慢日志的时间为2秒，SQL语句执行时问超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

    配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow. log。

面试中如何回答“如何定位慢查询？”：

  1. 介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟）
  2. 我们系统中当时采用了运维工具（Skywalking），可以监测出哪个接口，最终因为是sql的问题
  3. 在mysql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段）

4、SQL语句执行很慢，如何分析呢？

如果一条sql执行很慢的话，我们通常会使用mysql自动的执行计划explain来去查看这条sql的执行情况，比如在这里面可以通过 key和key_len 检查是否命中了索引，如果本身已经添加了索引，也可以判断索引是否有失效的情况，第二个，可以通过 type 字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描，第三个可以通过 extra 建议来判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复

5、SQL慢查询原因

  1. 没有用到索引
    • 比如函数导致的索引失效，或者本身就没有加索引
  2. 表数据量太大
    • 考虑分库分表
  3. 优化器 选错了索引
    • 考虑使用 force index 强制走索引

6、SQL查询偶尔慢会是什么原因？

  • 1. 数据库在刷新 脏页
    • 比如 redo log 写满了，内存 不够用了释放内存如果是脏页也需要刷，mysql 正常空闲状态刷脏页
  • 2. 没有拿到锁

7、MySQL慢查询怎么优化？

  1. 合理设计索引： 在合适的字段上建立索引，例如在where和order by命令上涉及的列建立索引。
  2. 索引优化，SQL优化。索引要符合最左匹配原则、尽量使用覆盖索引等。
  3. 适时清理数据： 数据库中的数据越多，查询速度就越慢。因此，在数据库设计时，需要考虑定期清理历史数据。同时，对于暂时不用的数据，应该及时清理，以免影响查询效率。
  4. 建立分区。对关键字段建立水平分区，比如时间字段，若查询条件往往通过时间范围来进行查询，能提升不少性能
  5. 优化查询语句： 在设计数据库查询语句时，应该尽量简洁明了。同时，避免使用重复或者多余的语句，尽可能减少数据库的负载。另外，也可以根据查询场景使用不同的查询方式，例如利用 JOIN 代替两个以上的 SELECT 语句、使用 UNION ALL 代替 UNION 、使用 LIMIT 控制结果集大小等等。
  6. 使用缓存： 利用Redis等缓存热点数据，提高查询效率。
  7. 读写分离。经典的数据库拆分方案，主库负责写，从库负责读。通过分库分表的方式进行优化，主要有垂直拆分和水平拆分。
  8. 数据异构到es
  9. 冷热数据分离。几个月之前不常用的数据放到冷库中，最新的数据比较新的数据放到热库中
  10. 升级数据库类型，换一种能兼容MySQL的数据库（OceanBase、TiDB等）

8、有哪些常见的SQL优化手段？

  1. 索引优化：使用索引可以加速查询操作，提高SQL语句的执行效率。优化索引可以包括使用合适的索引类型、优化索引列的选择、减少索引的数量等。
  2. 查询优化：使用合适的查询语句可以避免不必要的查询，减少数据库的负载。优化查询可以包括使用合适的关键字、避免使用子查询、避免使用全表扫描等。
  3. 数据表优化：对于大型的数据表，可以通过分区技术、分表技术等来提高数据库的性能。
  4. 数据库参数优化：通过调整数据库参数可以优化SQL的执行效率，例如调整缓存大小、调整查询缓存等。
  5. 数据库架构优化：通过优化数据库的架构，例如使用主从复制、分布式数据库等，可以提高数据库的性能和可靠性。
  6. 数据库连接优化：减少数据库连接的开销，例如使用连接池、复用数据库连接等。

开发者可以根据实际情况选择合适的手段进行优化，从而提高数据库的性能和可靠性。

9、索引下推

索引下推（Index Condition Pushdown） 是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

10、如何分析SQL的性能？

分析SQL的性能一般可以从以下几个方面进行：

  1. 执行计划分析：可以使用explain语句来获取SQL的执行计划。EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。
EXPLAIN SELECT score,name FROM cus_order ORDER BY score DESC;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcus_orderNULLALLNULLNULLNULLNULL997572100.00Using filesort

1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

列名含义
idSELECT 查询的序列标识符
select_typeSELECT 关键字对应的查询类型
table用到的表名
partitions匹配的分区，对于未分区的表，值为 NULL
type表的访问方法
possible_keys可能用到的索引
key实际用到的索引
key_len所选索引的长度
ref当使用索引等值查询时，与索引作比较的列或常量
rows预计要读取的行数
filtered按表条件过滤后，留存的记录数的百分比
Extra附加信息（额外的优化建议）

补充：

Extra含义
Using where;Using Index查找使用了索引，需要的数据都在索引列中能找到，不需要回表查询数据
Using index condition查找使用了索引，但是需要回表查询数据

  1. 监控查询日志：MySQL可以记录查询日志，通过查看查询日志，可以了解SQL的执行时间、执行次数、返回结果集的大小等信息。可以使用慢查询日志和一般查询日志来监控SQL的性能。

  2. 监控系统资源使用情况：SQL的执行性能还与系统资源的使用情况相关，例如CPU、内存、磁盘IO等。可以使用top、vmstat等工具来监控系统资源使用情况，以判断是否存在系统资源瓶颈。

  3. 性能分析工具：MySQL提供了一些性能分析工具，例如Percona Toolkit、MySQL Tuner等，可以帮助我们分析SQL的性能，并提供性能优化建议。

  4. 压力测试工具：可以使用压力测试工具，例如Apache JMeter、Apache AB等来模拟多用户、高并发的情况，测试SQL的性能表现，并找出性能瓶颈。

综上所述，分析SQL的性能需要结合多方面的因素，包括执行计划、查询日志、系统资源使用情况、性能分析工具和压力测试工具等，通过分析这些因素来确定SQL的性能瓶颈，并进行优化。

11、大表中有一条sql语句查询比较慢，怎么排查？

  1. 检查查询语句是否有语法错误或逻辑错误。可以使用数据库提供的工具或在线工具检查语句。
  2. 查看数据库表的索引情况，如果没有索引或者索引不合理的话，会影响查询效率。可以使用EXPLAIN命令来查看查询语句的执行计划，找到哪些表没有使用索引。
  3. 检查数据库服务器的性能和负载情况，如果服务器负载过高，会导致查询变慢。可以查看服务器的CPU、内存、磁盘等资源使用情况。
  4. 如果查询涉及到大量的数据，可以考虑对数据进行分页或者分区，避免一次性查询所有数据。
  5. 对于复杂的查询语句，可以考虑优化查询语句的结构或者拆分成多个简单的查询语句。
  6. 可以考虑使用缓存技术，把查询结果缓存起来，避免多次查询。

12、MySQL的性能调优方法？

MySQL调优可以从四个方面来做：

  • 表结构和索引优化：主要可以从几个方面来做，比如分库分表，读写分离，为字段选择合适的数据类型，适当的反范式设计，为查询操作创建必要的索引等。
  • SQL语句优化：可以从几方面来做，通过慢查询分析需要优化的SQL，利用explain或者profile这些工具去分析SQL的执行计划，避免使用SELECT * 的查询，尽可能使用索引扫描等。
  • MySQL参数优化：主要可以设置Buffer_pool的大小，官方建议是占总内存的50%-70%左右，设置刷盘策略，平衡好数据安全性和性能的关系。
  • 硬件及系统配置：主要可以从CPU的核心数，磁盘的读写性能，网卡，内存的大小等

13、缓存与数据库不一致怎么解决？

缓存数据库不一致是指应用程序中的缓存数据与实际数据库中的数据不同步造成的情况。通常解决这种问题可以采取以下几种方法：

  1. 设置缓存过期时间：如果应用程序中的缓存数据会随着时间的推移而失效，那么可以通过设置缓存数据的过期时间来保证缓存数据与数据库数据的一致性。
  2. 强制刷新缓存：当数据库中的数据发生变化时，可以手动使缓存数据失效或强制刷新缓存，确保缓存数据与数据库数据保持一致。
  3. 使用缓存和数据库双写：在应用程序中同时使用缓存和数据库，每次修改操作都将同时更新缓存和数据库中的数据，确保两者数据的一致性。
  4. 使用分布式锁：在多个节点同时访问同一个缓存数据时，可以使用分布式锁来控制并发，避免数据不一致的情况发生。

综上所述，如何解决缓存数据库不一致问题，要根据具体的情况选取不同的解决方案，以保证数据的准确性和一致性。

14、MySQL超大分页怎么处理？

候选人：嗯，超大分页一般都是在数据量比较大时，我们使用了limit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。

先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了因为查询id的时候，走的覆盖索引，所以效率可以提升很多

avatar
文章
阅读
粉丝