MySql性能优化(排查慢查询SQL)
-
以下为MS SQLSERVER慢查询排查方法:
SqlServer 使用sys.dm_exec_query_stats查看开销较高的语句
SELECT TOP 10 total_worker_time / 1000 AS [自编译以来执行所用的CPU时间总量(ms)], total_elapsed_time/1000 as [完成执行此计划所用的总时间], total_elapsed_time / execution_count/1000 as [平均完成执行此计划所用时间], execution_count as [上次编译以来所执行的次数], creation_time as [编译计划的时间], deqs.total_worker_time / deqs.execution_count / 1000 AS [平均使用CPU时间(ms)], last_execution_time AS [上次开始执行计划的时间], total_physical_reads [编译后在执行期间所执行的物理读取总次数], total_logical_reads/execution_count [平均逻辑读次数], min_worker_time /1000 AS [单次执行期间所用的最小CPU时间(ms)], max_worker_time / 1000 AS [单次执行期间所用的最大 CPU 时间(ms)], SUBSTRING(dest.text, deqs.statement_start_offset / 2 + 1, (CASE WHEN deqs.statement_end_offset = -1 THEN DATALENGTH(dest.text) ELSE deqs.statement_end_offset END - deqs.statement_start_offset ) / 2 + 1) AS [执行SQL], dest.text as [完整SQL], db_name(dest.dbid) as [数据库名称], object_name(dest.objectid, dest.dbid) as [对象名称] ,deqs.plan_handle [查询所属的已编译计划] FROM sys.dm_exec_query_stats deqs WITH(NOLOCK) CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest --平均使用CPU时间降序 ORDER BY (deqs.total_worker_time / deqs.execution_count / 1000) DESC --执行时间降序 --ORDER BY total_elapsed_time / execution_count DESC;
平均使用CPU时间降序
执行时间降序
查看执行计划
select * from sys.dm_exec_query_plan(0x05000A0045E84551103903112400000001000000000000000000000000000000000000000000000000000000)
-
以下为MYSQL优化技巧: