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(列名)
-