mysql中sql调优

1.查看一个 SQL 的执行耗时

• 如果是 MySQL 8.0之前，可以用SHOW PROFILES

• 如果是 MySQL 8.0之后的版本，可以用EXPLAIN ANALYZE

• 以下以SHOW PROFILES为例子，一般会关注如下指标

  • Opening tables：

    • 如果在这个阶段花费的时间较长，可能意味着表的打开操作比较耗时。

    • 可能需要检查表的状态（如存在大量的碎片或未完成的事务）、文件系统性能（如磁盘读写速度慢），表的大小，表的缓存（缓存配置不当或缓存命中率较低），并发访问（有大量并发访问请求，系统可能需要更多的时间来处理这些请求并管理锁定机制）等。

  • optimizing：

    • 这是查询优化的关键阶段。

    • 如果在此花费了较多时间，可能需要检查查询的复杂度（如多表join）和索引的使用情况(如索引失效，索引区分度不高)等。

  • statistics：

    • 这个阶段涉及收集表和索引的统计信息。

    • 如果时间过长，可能需要检查表的统计信息是否需要更新。

  • Sending data：

    • 这是查询执行的核心阶段，包含数据的读取和处理。

    • 如果在这里花费了大量时间，通常需要检查查询的效率（如查询语句本身可能不够高效）、数据量（如使用LIMIT 100000 10）和网络传输性能(如网络带宽有限或延迟较高)等。

2.查看sql的执行计划

• 使用explain查看sql的执行计划
• 一个执行计划中，共有12个字段

  • id：执行计划中每个操作的唯一标识符。对于一条查询语句，每个操作都有一个唯一的id。但是在多表join的时候，一次explain中的多条记录的id是相同的。

  • select_type：操作的类型。常见的类型包括SIMPLE、PRIMARY、SUBQUERY、UNION等。不同类型的操作会影响查询的执行效率。

  • table：当前操作所涉及的表。

  • partitions：当前操作所涉及的分区。

  • type：表示查询时所使用的索引类型，包括ALL、index、range、ref、eq_ref、const等。

    • 快到慢：system> const > eq_ref >ref>range> index >ALL

  • possible_keys：表示可能被查询优化器选择使用的索引。

  • key：表示查询优化器选择使用的索引

  • key_len：表示索引的长度。索引的长度越短，查询时的效率越高。

  • ref：用来表示哪些列或常量被用来与key列中命名的索引进行比较。

  • rows：表示此操作需要扫描的行数，即扫描表中多少行才能得到结果。

  • filtered：表示此操作过滤掉的行数占扫描行数的百分比。该值越大，表示查询结果越准确。

  • Extra：表示其他额外的信息，包括Using index、Using filesort、Using temporary等。

• 那么如何判断一条SQL走没有索引

  • 首先看key字段有没有值，有值表示用到了索引树，但是具体是怎么用的，还得看type和extra

3.定位具体问题与解决

• 索引失效

  • 一般是先通过执行计划分析是否走了索引，以及所走的索引是否符合预期，如果因为索引设计的不合理、或者索引失效导致的

    • 修改索引

    • 调整SQL语句

    • 强制执行使用某个索引

• 多表join

  • 减少JOIN表数量

  • 优化JOIN条件

  • 分解查询：将复杂的JOIN查询分解为多个简单的查询

• 查询字段太多

  • 精简查询字段

  • 垂直拆分表：将大表拆分为多个包含不同字段的小表，减少单次查询的字段数。

• 表中数据量太大

  • 数据归档

  • 分库分表、分区

  • 使用第三方的数据库

• 索引区分度不高

  • 优化索引设计：选择区分度高的列作为索引，或创建复合索引

• 数据库连接数不够

  • 分库：将数据分散到多个数据库，分担查询压力

  • 增加连接数：调整数据库配置，增加最大连接数。

• 数据库的表结构不合理

  • 重构表结构：根据查询需求和数据关系，优化表结构。

  • 分表：将大表拆分为多个小表，减小单表规模

• 数据库IO或者CPU比较高

  • 增加硬件资源：升级服务器的CPU和IO性能

  • 使用缓存：在应用层使用缓存，减少数据库访问频率

• 数据库参数不合理

  • 调整内存大小、缓存大小、线程池大小

• 事务比较长

  • 拆分事务：将长事务拆分为多个短事务，减少锁持有时间

• 锁竞争导致的等待

  • 使用乐观锁

• 其他

  • InnoDB 参数优化（如order by 使用Using filesort 可以通过修改参数优化）

  • 用 COUNT(*) 代替count(列名)