慢查询
MySQL慢查询是指执行时间超过阈值的SQL语句,这些慢查询会严重影响数据库的性能。
主要的特点包括:
- 执行时间超过了mysql设置的long_query_time阈值,默认是10秒。
- 不会被MySQL的查询缓存命中,需要每次都经过查询优化器优化然后执行。
- 需要扫描大量的数据行或者访问大量的表。
- 由于表结构设计不合理,导致执行计划低效。
- 服务器参数设置不当,导致系统资源不足,响应慢。
慢查询的主要危害包括:
- 使得用户的请求响应时间过长,直接影响到用户体验。
- 任务队列会因执行慢查询而阻塞,造成资源浪费,其他查询等待。
- 慢查询会占用大量CPU及IO资源,造成系统拥堵。
- 极端情况下可能会造成连接数用尽,服务不可用。
所以数据库应该监控和发现慢查询,并及时进行SQL调优或者架构优化,以提升系统性能。
排查
排查MySQL慢查询的常用方法有:
- 开启并查看慢查询日志,慢查询日志记录了所有执行时间超过阈值的SQL语句,可以找出主要的慢查询SQL。
- 使用show processlist命令查看当前正在执行的线程,找出执行时间过长的线程。
- 对重点慢查询进行EXPLAIN分析,查看查询执行计划,判断执行过程中是否存在全表扫描、缺失索引等情况。
- 使用show profiles和show profile for query命令查看SQL执行过程中的每个步骤所消耗的时间,定位慢查询的具体原因。
- 分析慢查询的SQL语句本身,检查是否可以进行SQL优化,例如添加索引、调整连接条件等。
- 检查数据库服务器参数配置,确保key_buffer_size、query_cache大小等系统参数设置合理,避免成为瓶颈。
- 使用trace工具跟踪记录慢查询的整个执行过程,进一步找出性能瓶颈。
- 检查磁盘IO情况,判断磁盘读写性能是否是慢查询的原因。
- 使用升级更高配置的服务器,测试性能提升情况,判断硬件资源是否限制了数据库性能。
通过综合运用这些排查手段,可以确定慢查询的具体原因,针对性地进行SQL优化和系统调优以提高查询性能。
EXPLAIN分析
EXPLAIN命令在MySQL中可以用于分析SELECT查询语句的执行计划,以检查 SQL 语句性能瓶颈。
使用EXPLAIN命令可以查看以下信息:
- select_type:查询类型,包括SIMPLE、PRIMARY、UNION等。
- table:查询是关联的表。
- partitions:匹配的表分区。
- type:访问类型,包括全表扫描、索引查找等。
- possible_keys:此查询可使用的索引。
- key:实际使用的索引。
- key_len:使用索引字段的长度。
- rows:预计扫描的行数。
- filtered:查询条件过滤出的行数比例。
- extra:额外信息。
通过分析这些信息,可以检查查询是否存在全表扫描、索引是否被使用、扫描行数是否过多等情况,来判断及优化查询性能。
EXPLAIN SELECT * FROM employees WHERE salary > 10000;
show processlist 命令
show processlist是MySQL提供的一个显示当前连接线程信息的命令。
show processlist显示内容包括:
- Id - 连接的ID号
- User - 用户名
- Host - 客户端的IP地址
- db - 连接所使用的数据库名
- Command - 线程正在执行的命令类型
- Time - 线程已执行的时间
- State - 线程当前所处的状态
- Info - 语句的详细信息
mysql> show processlist;
+----+-------------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+------+---------+------+-------+------------------+
| 51 | event_sched | localhost:57806 | NULL | Daemon | 1695 | NULL | event scheduler |
| 61 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+-----------------+------+---------+------+-------+------------------+
show profiles 命令
MySQL中的show profiles命令用于显示最近执行过的语句或函数的资源消耗情况。
show profiles显示的主要信息包括:
- Query_ID:语句的ID号
- Duration:执行事件总时间
- CPU_user:用户态CPU时间
- CPU_system:系统态CPU时间
- Context_voluntary:自愿上下文切换次数
- Context_involuntary:非自愿上下文切换次数
- Block_ops_in:输入块操作次数
- Block_ops_out:输出块操作次数
- Messages_sent:发出消息数
- Messages_received:收到消息数
- Page_faults_major:主要页错误数
- Swaps:交换次数
- Source_function:产生事件的源代码位置
mysql> show profiles;
+----------+----------+--------------------------+
| Query_ID | Duration | Query |
+----------+----------+--------------------------+
| 1 | 0.000219 | show tables |
| 2 | 0.230491 | select * from employees |
+----------+----------+--------------------------+
show profile for query 命令
MySQL中的show profile for query命令可以更详细地显示出一条SQL语句在执行过程中每个状态所消耗的时间和资源使用情况。
show profile for query 需要指定某个查询的Query_ID。查询ID可以通过show profiles命令得到。
show profile for query显示的信息包括:
- Status: SQL执行各个阶段的状态信息
- Duration:每个状态消耗的时间
- CPU_user:用户态CPU时间
- CPU_system:系统态CPU时间
- Context_voluntary:自愿上下文切换次数
- Context_involuntary:非自愿上下文切换次数
- Block_ops_in:输入块操作数
- Block_ops_out:输出块操作数
- Messages_sent:发出的消息数
- Messages_received:收到的消息数
- Page_faults_major:主要页错误数
- Swaps:交换次数
- Source_function:函数名
mysql> show profile for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000092 |
| checking permissions | 0.000005 |
| Opening tables | 0.000036 |
| init | 0.000052 |
| System lock | 0.000057 |
| optimizing | 0.000010 |
| statistics | 0.000082 |
| preparing | 0.000084 |
| executing | 0.000003 |
| Sending data | 0.213464 |
| end | 0.000037 |
| query end | 0.000006 |
| closing tables | 0.000009 |
| freeing items | 0.000032 |
| cleaning up | 0.000015 |
+----------------------+----------+
