查看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; www.2cto.com
在開發的過程中隨著數據量的增大而感受到數據庫的性能比較差從而延伸到響應速度慢，
如果是開發人員很多時候估計是處於一種茫然狀態，或者直接交給DBA去處理這問題，如果有DBA您很幸運，
但是如果沒有DBA的前提下我們怎麼去處理這問題，可能唯一的方法就是看執行計劃
(也可以直接用explain SQL來分析...)：
默認情況下Mysql的profiling是關閉的，所以首先必須打開profiling
Sql代碼
set profiling="ON"
mysql> show variables like "%profi%";
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| profiling | ON |
www.2cto.com
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] www.2cto.com
[LIMIT row_count [OFFSET offset]]
type:
ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS
作者 san_yun
