查看mysql的执行计划_MySQL
程序员文章站
2022-05-21 09:51:59
...
bitsCN.com
查看mysql的执行计划 这条SQL执行4分钟，message_message有数据1000w，学写了下mysql的执行计划。
select * from message_message where id in(select message_id
from message_message_tags where messagetag_id=59885) and (category=9 or category=1)
order by sum(like_count,favorite_count) desc limit 15; 在开发的过程中随着数据量的增大而感受到数据库的性能比较差从而延伸到响应速度慢，
如果是开发人员很多时候估计是处于一种茫然状态，或者直接交给DBA去处理这问题，如果有DBA您很幸运，
但是如果没有DBA的前提下我们怎么去处理这问题，可能唯一的方法就是看执行计划
(也可以直接用explain SQL来分析...)：默认情况下Mysql的profiling是关闭的，所以首先必须打开profiling Sql代码 set profiling="ON" mysql> show variables like "%profi%"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | ON | show processlist; 查看现在在运行的所有进程列表，在进程列表中我们唯一需要的是ID mysql> show processlist; +----+------+----------------+-----------+---------+------+-------+------------- -----+ | Id | User | Host | db | Command | Time | State | Info | +----+------+----------------+-----------+---------+------+-------+------------- -----+ | 3 | root | localhost:2196 | click_log | Query | 0 | NULL | show process list | +----+------+----------------+-----------+---------+------+-------+------------- mysql> show profile cpu,memory for query 3; +--------------------+------------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +--------------------+------------+----------+------------+ | freeing items | 0.00001375 | NULL | NULL | | logging slow query | 0.00001375 | NULL | NULL | | cleaning up | 0.00000050 | NULL | NULL | +--------------------+------------+----------+------------+ SHOW PROFILES Syntax: SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS 作者 san_yun bitsCN.com
查看mysql的执行计划 这条SQL执行4分钟，message_message有数据1000w，学写了下mysql的执行计划。
select * from message_message where id in(select message_id
from message_message_tags where messagetag_id=59885) and (category=9 or category=1)
order by sum(like_count,favorite_count) desc limit 15; 在开发的过程中随着数据量的增大而感受到数据库的性能比较差从而延伸到响应速度慢，
如果是开发人员很多时候估计是处于一种茫然状态，或者直接交给DBA去处理这问题，如果有DBA您很幸运，
但是如果没有DBA的前提下我们怎么去处理这问题，可能唯一的方法就是看执行计划
(也可以直接用explain SQL来分析...)：默认情况下Mysql的profiling是关闭的，所以首先必须打开profiling Sql代码 set profiling="ON" mysql> show variables like "%profi%"; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | profiling | ON | show processlist; 查看现在在运行的所有进程列表，在进程列表中我们唯一需要的是ID mysql> show processlist; +----+------+----------------+-----------+---------+------+-------+------------- -----+ | Id | User | Host | db | Command | Time | State | Info | +----+------+----------------+-----------+---------+------+-------+------------- -----+ | 3 | root | localhost:2196 | click_log | Query | 0 | NULL | show process list | +----+------+----------------+-----------+---------+------+-------+------------- mysql> show profile cpu,memory for query 3; +--------------------+------------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +--------------------+------------+----------+------------+ | freeing items | 0.00001375 | NULL | NULL | | logging slow query | 0.00001375 | NULL | NULL | | cleaning up | 0.00000050 | NULL | NULL | +--------------------+------------+----------+------------+ SHOW PROFILES Syntax: SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS 作者 san_yun bitsCN.com