优化SQL的一般步骤

2024-07-21 02:51:28

字体：大中小

来源：转载

供稿：网友

1，通过show status 命令了解各种SQL的执行频率比如Com_select 记录执行select查询的操作次数，一次查询只累加12，定位执行效率较低的SQL语句 1通过慢查询日志定位，2慢查询在查询结束后才记录，使用show PRocesslist查看3，通过EXPLAIN分析低效SQL的执行计划

4，通过show profile 分析SQL（>MySQL5.0.37） mysql> select @@have_profiling;+------------------+| @@have_profiling |+------------------+| YES |+------------------+1 row in set (0.00 sec) 表示支持mysql> select @@profiling;+-------------+| @@profiling | +-------------+| 0 |+-------------+1 row in set (0.00 sec) 默认关闭可以set profiling=1;开启mysql> show profiles;+----------+------------+--------------------------------------------+| Query_ID | Duration | Query |+----------+------------+--------------------------------------------+| 1 | 0.02342575 | select * from br_member where user_id<1000 |+----------+------------+--------------------------------------------+1 row in set (0.00 sec)mysql> show profile for query 1;（这里的1 代表show profiles查出的query_id的值）+----------------------+----------+| Status | Duration |+----------------------+----------+| starting | 0.000115 || checking permissions | 0.000013 || Opening tables | 0.000044 || System lock | 0.000025 || init | 0.000097 || optimizing | 0.000019 || statistics | 0.000084 || preparing | 0.000020 || executing | 0.000005 || Sending data | 0.022893 || end | 0.000012 || query end | 0.000006 || closing tables | 0.000013 || freeing items | 0.000071 || logging slow query | 0.000005 || cleaning up | 0.000004 |+----------------------+----------+16 rows in set (0.00 sec)在获取到最消耗时间的线程状态以后，mysql支持进一步选择all,cpu,block io,context,switch,page faults等明细类型来查看mysql在使用什么资源上耗费了过高的时间，如下选择查看cpu的消耗时间mysql> show profile cpu for query 1;+----------------------+----------+----------+------------+| Status | Duration | CPU_user | CPU_system |+----------------------+----------+----------+------------+| starting | 0.000115 | 0.000000 | 0.000000 || checking permissions | 0.000013 | 0.000000 | 0.000000 || Opening tables | 0.000044 | 0.000000 | 0.000000 || System lock | 0.000025 | 0.000000 | 0.000000 || init | 0.000097 | 0.000000 | 0.000000 || optimizing | 0.000019 | 0.000000 | 0.000000 || statistics | 0.000084 | 0.000000 | 0.000000 || preparing | 0.000020 | 0.000000 | 0.000000 || executing | 0.000005 | 0.000000 | 0.000000 || Sending data | 0.022893 | 0.015600 | 0.000000 || end | 0.000012 | 0.000000 | 0.000000 || query end | 0.000006 | 0.000000 | 0.000000 || closing tables | 0.000013 | 0.000000 | 0.000000 || freeing items | 0.000071 | 0.000000 | 0.000000 || logging slow query | 0.000005 | 0.000000 | 0.000000 || cleaning up | 0.000004 | 0.000000 | 0.000000 |+----------------------+----------+----------+------------+16 rows in set (0.00 sec)5、通过trace分析优化器如何选择执行计划6、确定问题并采取相应的措施