mysql优化思路

+----------+------------+--------------------------------------+
| Query_ID | Duration   | Query                                |
+----------+------------+--------------------------------------+
|        2 | 0.00009200 | set profiling=1                      |
|        5 | 0.02003525 | select count(*) from user where id>2 |
+----------+------------+--------------------------------------+

包含一个query_id和执行时间和query语句
通过query_id可以查看到更详细的信息；

show profile cpu ,block io  for query 5;

+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000170 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000033 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000061 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000021 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.010826 | 0.000000 |   0.000000 |          184 |             0 |
| preparing            | 0.000041 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.008731 | 0.008000 |   0.000000 |            0 |             0 |
| end                  | 0.000020 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000032 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000027 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+

 
对mysql服务器的优化不要一上来就去优化sql语句，应该首先观察全局情况，至少要先搞清楚
问题出在哪，应该使用脚本来观察服务器一段时间（一天或更长）的健康状况，比如cpu，io，进程连接数等
最后才分析具体原因处在哪里；针对解决;

通过 mysqladmin来查看mysql的状态；

mysqladmin -P3306 -uroot -p123456 -h127.0.0.1 -r -i 1 ext |awk -F"|" "BEGIN{ count=0; }"‘{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){    print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --";    print "---Time---|---QPS---|select insert update delete|  read inserted updated deleted|   logical    physical";}else if ($2 ~ /Queries/){queries=$3;}else if ($2 ~ /Com_select /){com_select=$3;}else if ($2 ~ /Com_insert /){com_insert=$3;}else if ($2 ~ /Com_update /){com_update=$3;}else if ($2 ~ /Com_delete /){com_delete=$3;}else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}else if ($2 ~ /Uptime / && count >= 2){  printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);  printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);  printf("|%10d %11d\n",innodb_lor,innodb_phr);}}‘

还可以在 printf的输出重定向到一个文件，这样就可以通过文件数据进行可视化分析；

mysql优化思路

标签：roc   tool   可视化   ted   查询   ops   slow   update   健康