废话几句
提到SQL Tuning，对于DBA同学来说是再熟悉不过的名词了，但不管是经验丰富的老鸟还是我这种弱不拉几的新人，应该都会面临一个问题，就是，当大家对一个SQL进行性能诊断分析的时候，如果想要全面的分析一个问题，势必要在命令行输入一堆的命令，而我们需要的只是它的返回结果。如果有一款工具，能够一次性，快速而又准确地采集到我们所需要的这些信息，并且提供结构化的输出，那么，我们的SQL Tuning工作效率将会得到极大提升。
这两天无意之中发现了这样一款工具，是由宜信的韩锋老师写的，下载研究了一番，感觉还不错，输出信息很全面，内容也比较直观易懂，不过想要适合自己使用，代码需要稍作修改，相关的环境也有一些差异
这里是韩锋老师提供的源码地址，http://pan.baidu.com/s/1slF3zS5
环境配置
这个工具是用python语言编写的，因此，OS必须具备python环境才能运行这个工具。建议使用2.7版本,当然2.6也可以，但是需要安装一些模块。
我的测试机环境是2.6.6
[root@mycentos pip-1.5.4]# python --version
Python 2.6.6安装包：
mysql-devel
MySQL-python
注：或者直接安装MySQLdb模块
还需要安装如下两个模块：
sqlparse（这里提供一个下载地址https://pypi.python.org/pypi/sqlparse/）
argparse（直接easy_install argparse即可）
注:先安装setuptools，https://pypi.python.org/pypi/setuptools
用法介绍
使用比较简单，命令行需提供两个参数
-p 制定参数文件路径
-s SQL语句
如：
[root@mycentos tools]# python mysql_tuning.py -p mysql_tuning.ini -s 'select * from test.t1'
参数文件
这里提供一个模板
[database]
server_ip = 127.0.0.1
db_user = testuser
db_pwd = testpwd
db_name = test
[option]
sys_parm = ON //是否显示系统参数
sql_plan = ON //是否显示执行计划
obj_stat = ON //是否显示相关对象(表、索引)统计信息
ses_status = ON //是否显示运行前后状态信息(激活后会真实执行SQL)
sql_profile = ON //是否显示PROFILE跟踪信息(激活后会真实执行SQL)
使用时去掉后面的解释部分即可
代码调整
1、数据库连接
从配置文件可以看出，这个数据库连接是没有端口信息的，那么我们就需要加上端口以适应多实例的数据库使用。如，针对3306端口的mysql实例，如下方式修改，能够正常连接
1)参数文件[database]项添加
db_port = 33062)代码部分修改
找到如下的代码块
db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
读取到参数文件的[database]部分被赋值给p_dbinfo数组，我们在后面添加内容，获取参数文件添加的port部分
修改之后如下
db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3],port=int(p_dbinfo[4]))注意：MySQLdb模块的connect方法，port参数输入应该是整数类型，因此需要使用int()函数进行转换
2、这里应该算是一个错误了吧。。
可能是环境不同导致，我的测试环境中首次执行是报了这个错
TypeError: 'bool' object is not callable我一看就惊呆了，马上查看代码，大概是60多行的位置
62 if not parsed.is_group():
63 return False
本代码中没有关于这个is_group()函数的定义，那么根据这个报错，这个is_group应该是个实例化后的对象变量，而不是个方法，修改后，再运行不再报错
62 if not parsed.is_group:
63 return False
3、对于执行计划的处理
代码情况如下
for row in p_sqlplan:
print seq3,str(row[0]).rjust(6), # id
print seq3,row[1].ljust(16), # select_type
print seq3,row[2].ljust(10), # table
print seq3,row[3].ljust(10), # type可见代码是对执行计划每个列都进行了处理的，但是对于某些sql语句，比如insert，执行计划会比较特别，如
mysql> explain insert into test.t1 values(1);
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
因此，我们需要加上对空值的判断。由于其他的字段都有处理，这里只需修改table以及type
if not "NoneType" in str(type(row[2])):
print seq3,row[2].ljust(10), # table
else:
print seq3,"NULL".ljust(10),
if not "NoneType" in str(type(row[3])):
print seq3,row[3].ljust(10), # type
else:
print seq3,"NULL".ljust(10),
执行
现在，这个小工具可以正式为我们效命了，看到这样的输出结果，你是否已经爱上了它
[root@mycentos tools]# python mysql_tuning.py -p mysql_tuning.ini -s 'select * from test.t1'
****************************************************************************************************
* MySQL SQL Tuning Tools v1.0 (by hanfeng) *
****************************************************************************************************
===== BASIC INFORMATION =====
+----------------------+------------+------------+------------+
| server_ip | user_name | db_name | db_version |
+----------------------+------------+------------+------------+
| 127.0.0.1 | root | qwe123 | 5.6.30-log |
+----------------------+------------+------------+------------+
===== ORIGINAL SQL TEXT =====
SELECT *
FROM test.t1
===== SYSTEM PARAMETER =====
+--------------------------------+------------------------------------------------------------+
| parameter_name | value |
+--------------------------------+------------------------------------------------------------+
| binlog_cache_size | 32.0 K |
| bulk_insert_buffer_size | 8.0 M |
| have_query_cache | YES |
| interactive_timeout | 28800 |
| join_buffer_size | 128.0 K |
| key_buffer_size | 64.0 M |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1.0 K |
| key_cache_division_limit | 100 |
| large_pages | OFF |
| locked_in_memory | OFF |
| long_query_time | 1.000000 |
| max_allowed_packet | 134217728 |
| max_binlog_cache_size | 2.0 G |
| max_binlog_size | 500.0 M |
| max_connections | 5000 |
| max_connect_errors | 65535 |
| max_join_size | 17179869183.0 G |
| max_length_for_sort_data | 1024 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 5000 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| query_cache_size | 1.0 M |
| query_cache_type | OFF |
| query_prealloc_size | 8.0 K |
| range_alloc_block_size | 4.0 K |
| read_buffer_size | 128.0 K |
| read_rnd_buffer_size | 128.0 K |
| sort_buffer_size | 256.0 K |
| sql_mode | STRICT_ALL_TABLES |
| thread_cache_size | 256 B |
| tmp_table_size | 16.0 M |
| wait_timeout | 28800 |
+--------------------------------+------------------------------------------------------------+
===== OPTIMIZER SWITCH =====
+------------------------------------------+------------+
| switch_name | value |
+------------------------------------------+------------+
| index_merge | on |
| index_merge_union | on |
| index_merge_sort_union | on |
| index_merge_intersection | on |
| engine_condition_pushdown | on |
| index_condition_pushdown | on |
| mrr | on |
| mrr_cost_based | on |
| block_nested_loop | on |
| batched_key_access | off |
| materialization | on |
| semijoin | on |
| loosescan | on |
| firstmatch | on |
| subquery_materialization_cost_based | on |
| use_index_extensions | on |
+------------------------------------------+------------+
===== SQL PLAN =====
+--------+------------------+------------+------------+----------------------+------------+------------+------------+------------+------------+------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------+------------------+------------+------------+----------------------+------------+------------+------------+------------+------------+------------+
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 6 | 100.0 | Using index |
+--------+------------------+------------+------------+----------------------+------------+------------+------------+------------+------------+------------+
===== OPTIMIZER REWRITE SQL =====
SELECT `test`.`t1`.`id` AS `id`
FROM `test`.`t1`
===== OBJECT STATISTICS =====
+-----------------+------------+------------+------------+------------+------------+------------+------------+
| table_name | engine | format | table_rows | avg_row | total_mb | data_mb | index_mb |
+-----------------+------------+------------+------------+------------+------------+------------+------------+
| t1 | InnoDB | Compact | 6 | 2730 | 0.02 | 0.02 | 0.00 |
+-----------------+------------+------------+------------+------------+------------+------------+------------+
+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+
| index_name | non_unique | seq_in_index | column_name | collation | cardinality | nullable | index_type |
+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+
| PRIMARY | 0 | 1 | id | A | 6 | | BTREE |
+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+
===== SESSION STATUS (DIFFERENT) =====
+-------------------------------------+-----------------+-----------------+-----------------+
| status_name | before | after | diff |
+-------------------------------------+-----------------+-----------------+-----------------+
| Bytes_received | 387 | 605 | 218 |
| Bytes_sent | 396 | 9698 | 9302 |
| Com_select | 2 | 4 | 2 |
| Created_tmp_tables | 2 | 3 | 1 |
| Handler_commit | 0 | 1 | 1 |
| Handler_external_lock | 0 | 2 | 2 |
| Handler_read_first | 0 | 1 | 1 |
| Handler_read_key | 0 | 1 | 1 |
| Handler_read_next | 0 | 2 | 2 |
| Handler_read_rnd | 0 | 341 | 341 |
| Handler_read_rnd_next | 6 | 348 | 342 |
| Handler_write | 186 | 527 | 341 |
| Innodb_buffer_pool_read_requests | 5648 | 5652 | 4 |
| Innodb_rows_read | 25 | 27 | 2 |
| Qcache_not_cached | 163 | 165 | 2 |
| Queries | 535 | 537 | 2 |
| Questions | 5 | 7 | 2 |
| Select_scan | 2 | 4 | 2 |
| Sort_rows | 0 | 341 | 341 |
| Sort_scan | 0 | 1 | 1 |
| Table_locks_immediate | 123 | 124 | 1 |
| Table_open_cache_hits | 0 | 1 | 1 |
+-------------------------------------+-----------------+-----------------+-----------------+
===== SQL PROFILING(DETAIL)=====
+--------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| state | duration | cpu_user | cpu_sys | bk_in | bk_out | msg_s | msg_r | p_f_ma | p_f_mi | swaps |
+--------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
| starting | 0.000082 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| checking permissions | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Opening tables | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| init | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| statistics | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| preparing | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Sorting result | 0.000003 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| executing | 0.001023 | 0.000000 | 0.003000 | 0 | 0 | 0 | 0 | 0 | 243 | 0 |
| Sending data | 0.000031 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Creating sort index | 0.001339 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 35 | 0 |
| end | 0.000017 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| closing tables | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| removing tmp table | 0.000310 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| closing tables | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| freeing items | 0.000431 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| cleaning up | 0.000029 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+--------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
bk_in: block_ops_in
bk_out: block_ops_out
msg_s: message sent
msg_r: message received
p_f_ma: page_faults_major
p_f_mi: page_faults_minor
===== SQL PROFILING(SUMMARY)=====
+-------------------------------------+-----------------+------------+-------+-----------------+
| state | total_r | pct_r | calls | r/call |
+-------------------------------------+-----------------+------------+-------+-----------------+
| Creating sort index | 0.001339 | 39.71 | 1 | 0.0013390000 |
| executing | 0.001023 | 30.34 | 1 | 0.0010230000 |
| freeing items | 0.000431 | 12.78 | 1 | 0.0004310000 |
| removing tmp table | 0.000310 | 9.19 | 1 | 0.0003100000 |
| starting | 0.000082 | 2.43 | 1 | 0.0000820000 |
| Sending data | 0.000031 | 0.92 | 1 | 0.0000310000 |
| Opening tables | 0.000029 | 0.86 | 1 | 0.0000290000 |
| cleaning up | 0.000029 | 0.86 | 1 | 0.0000290000 |
| init | 0.000023 | 0.68 | 1 | 0.0000230000 |
| end | 0.000017 | 0.50 | 1 | 0.0000170000 |
| preparing | 0.000013 | 0.39 | 1 | 0.0000130000 |
| statistics | 0.000013 | 0.39 | 1 | 0.0000130000 |
| closing tables | 0.000010 | 0.30 | 2 | 0.0000050000 |
| System lock | 0.000005 | 0.15 | 1 | 0.0000050000 |
| checking permissions | 0.000005 | 0.15 | 1 | 0.0000050000 |
| optimizing | 0.000005 | 0.15 | 1 | 0.0000050000 |
| query end | 0.000004 | 0.12 | 1 | 0.0000040000 |
| Sorting result | 0.000003 | 0.09 | 1 | 0.0000030000 |
+-------------------------------------+-----------------+------------+-------+-----------------+
===== EXECUTE TIME =====
0 day 0 hour 0 minute 0 second 15 microsecond 总结
这个工具，它更像是一个sql诊断命令的集合，而对于推送出的消息，或多或少还是融入了一些这个DBA老将的经验在里面，可大大节省SQL Tuning的时间，推荐大家使用。毕竟开源嘛，修修改改就成自己的了~~哈哈！
时间： 2024-11-02 08:22:24