mysql的性能优化工具（源自韩锋大师，针对5.7修改）

千家信息网最后更新 2024年12月12日mysql的性能优化工具（源自韩锋大师，针对5.7修改）韩锋大师分享了一个mysql的性能优化工具，一个能自动采集SQL优化相关各种信息的python脚本，对于优化人员而言，这能省不少事，赞！
试用了一下，发现在mysql5.7下，运行不起来，细节上还是有好几个坑的，费了一些周折，终于把坑踩完了，现在把细节说明一下，并把修改后的SQL分享出来；
问题1：调用脚本时，若SQL是用单引号包含（韩老师就是这么示范的：python mysql_tuning.py -p tuning_sql.ini -s 'select xxx），但这样会报错，解决办法：用双引号分隔，如：python mysql_tuning.py -p tuning_sql.ini -s "select * from employees.dept_emp"这样就没问题；

问题2：没有引用string单元，在使用string.atoi时会报错，解决办法：import string；

问题3：mysql5.7后，infomation_schema的几个表INFORMATION_SCHEMA.GLOBAL_VARIABLES、INFORMATION_SCHEMA.SESSION_VARIABLES、 INFORMATION_SCHEMA.SESSION_STATUS要替换成performance_schema下的；

问题4：在显示执行计划时，table与type也是有可能是NULL的，要做空值处理，另外没有显示partitions栏位；

问题5：p_after_status[key]有可能是小数，所以用int去转换会报错，需要用float;

问题6：db_name显示成user_pwd了，这个或者不算问题；

修改后的脚本如下：

点击(此处)折叠或打开

1. #!/usr/local/bin/python
   
2. import datetime
   
3. import getopt
   
4. import sys
   
5. import string
   
6. import pprint
   
7. from warnings import filterwarnings
   
8. import MySQLdb
   
9. import ConfigParser
   
10. import sqlparse
    
11. from sqlparse.sql import IdentifierList, Identifier
    
12. from sqlparse.tokens import Keyword, DML
    
13. 
    
14. filterwarnings('ignore', category = MySQLdb.Warning)
    
15. 
    
16. seq1="+"
    
17. seq2="-"
    
18. seq3="|"
    
19. 
    
20. SYS_PARM_FILTER = (
    
21. 'BINLOG_CACHE_SIZE',
    
22. 'BULK_INSERT_BUFFER_SIZE',
    
23. 'HAVE_PARTITION_ENGINE',
    
24. 'HAVE_QUERY_CACHE',
    
25. 'INTERACTIVE_TIMEOUT',
    
26. 'JOIN_BUFFER_SIZE',
    
27. 'KEY_BUFFER_SIZE',
    
28. 'KEY_CACHE_AGE_THRESHOLD',
    
29. 'KEY_CACHE_BLOCK_SIZE',
    
30. 'KEY_CACHE_DIVISION_LIMIT',
    
31. 'LARGE_PAGES',
    
32. 'LOCKED_IN_MEMORY',
    
33. 'LONG_QUERY_TIME',
    
34. 'MAX_ALLOWED_PACKET',
    
35. 'MAX_BINLOG_CACHE_SIZE',
    
36. 'MAX_BINLOG_SIZE',
    
37. 'MAX_CONNECT_ERRORS',
    
38. 'MAX_CONNECTIONS',
    
39. 'MAX_JOIN_SIZE',
    
40. 'MAX_LENGTH_FOR_SORT_DATA',
    
41. 'MAX_SEEKS_FOR_KEY',
    
42. 'MAX_SORT_LENGTH',
    
43. 'MAX_TMP_TABLES',
    
44. 'MAX_USER_CONNECTIONS',
    
45. 'OPTIMIZER_PRUNE_LEVEL',
    
46. 'OPTIMIZER_SEARCH_DEPTH',
    
47. 'QUERY_CACHE_SIZE',
    
48. 'QUERY_CACHE_TYPE',
    
49. 'QUERY_PREALLOC_SIZE',
    
50. 'RANGE_ALLOC_BLOCK_SIZE',
    
51. 'READ_BUFFER_SIZE',
    
52. 'READ_RND_BUFFER_SIZE',
    
53. 'SORT_BUFFER_SIZE',
    
54. 'SQL_MODE',
    
55. 'TABLE_CACHE',
    
56. 'THREAD_CACHE_SIZE',
    
57. 'TMP_TABLE_SIZE',
    
58. 'WAIT_TIMEOUT'
    
59. )
    
60. 
    
61. def is_subselect(parsed):
    
62. if not parsed.is_group():
    
63. return False
    
64. for item in parsed.tokens:
    
65. if item.ttype is DML and item.value.upper() == 'SELECT':
    
66. return True
    
67. return False
    
68. 
    
69. def extract_from_part(parsed):
    
70. from_seen = False
    
71. for item in parsed.tokens:
    
72. #print item.ttype,item.value
    
73. if from_seen:
    
74. if is_subselect(item):
    
75. for x in extract_from_part(item):
    
76. yield x
    
77. elif item.ttype is Keyword:
    
78. raise StopIteration
    
79. else:
    
80. yield item
    
81. elif item.ttype is Keyword and item.value.upper() == 'FROM':
    
82. from_seen = True
    
83. 
    
84. def extract_table_identifiers(token_stream):
    
85. for item in token_stream:
    
86. if isinstance(item, IdentifierList):
    
87. for identifier in item.get_identifiers():
    
88. yield identifier.get_real_name()
    
89. elif isinstance(item, Identifier):
    
90. yield item.get_real_name()
    
91. # It's a bug to check for Keyword here, but in the example
    
92. # above some tables names are identified as keywords...
    
93. elif item.ttype is Keyword:
    
94. yield item.value
    
95. 
    
96. def extract_tables(p_sqltext):
    
97. stream = extract_from_part(sqlparse.parse(p_sqltext)[0])
    
98. return list(extract_table_identifiers(stream))
    
99. 
    
100. def f_find_in_list(myList,value):
     
101. try:
     
102. for v in range(0,len(myList)):
     
103. if value==myList[v]:
     
104. return 1
     
105. return 0
     
106. except:
     
107. return 0
     
108. 
     
109. def f_get_parm(p_dbinfo):
     
110. conn = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
     
111. cursor = conn.cursor()
     
112. cursor.execute("select lower(variable_name),variable_value from performance_schema.global_variables where upper(variable_name) in ('"+"','".join(list(SYS_PARM_FILTER))+"') order by variable_name")
     
113. records = cursor.fetchall()
     
114. cursor.close()
     
115. conn.close()
     
116. return records
     
117. 
     
118. def f_print_parm(p_parm_result):
     
119. print "===== SYSTEM PARAMETER ====="
     
120. status_title=('parameter_name','value')
     
121. print "+--------------------------------+------------------------------------------------------------+"
     
122. print seq3,status_title[0].center(30),
     
123. print seq3,status_title[1].center(58),seq3
     
124. print "+--------------------------------+------------------------------------------------------------+"
     
125. 
     
126. for row in p_parm_result:
     
127. print seq3,row[0].ljust(30),
     
128. if 'size' in row[0]:
     
129. if string.atoi(row[1])>=1024*1024*1024:
     
130. print seq3,(str(round(string.atoi(row[1])/1024/1024/1024,2))+' G').rjust(58),seq3
     
131. elif string.atoi(row[1])>=1024*1024:
     
132. print seq3,(str(round(string.atoi(row[1])/1024/1024,2))+' M').rjust(58),seq3
     
133. elif string.atoi(row[1])>=1024:
     
134. print seq3,(str(round(string.atoi(row[1])/1024,2))+' K').rjust(58),seq3
     
135. else:
     
136. print seq3,(row[1]+' B').rjust(58),seq3
     
137. else:
     
138. print seq3,row[1].rjust(58),seq3
     
139. print "+--------------------------------+------------------------------------------------------------+"
     
140. print
     
141. 
     
142. def f_print_optimizer_switch(p_dbinfo):
     
143. print "===== OPTIMIZER SWITCH ====="
     
144. db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
     
145. cursor = db.cursor()
     
146. cursor.execute("select variable_value from performance_schema.global_variables where upper(variable_name)='OPTIMIZER_SWITCH'")
     
147. rows = cursor.fetchall()
     
148. print "+------------------------------------------+------------+"
     
149. print seq3,'switch_name'.center(40),
     
150. print seq3,'value'.center(10),seq3
     
151. print "+------------------------------------------+------------+"
     
152. for row in rows[0][0].split(','):
     
153. print seq3,row.split('=')[0].ljust(40),
     
154. print seq3,row.split('=')[1].rjust(10),seq3
     
155. print "+------------------------------------------+------------+"
     
156. cursor.close()
     
157. db.close()
     
158. print
     
159. 
     
160. def f_exec_sql(p_dbinfo,p_sqltext,p_option):
     
161. results={}
     
162. conn = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
     
163. cursor = conn.cursor()
     
164. 
     
165. if f_find_in_list(p_option,'PROFILING'):
     
166. cursor.execute("set profiling=1")
     
167. cursor.execute("select ifnull(max(query_id),0) from INFORMATION_SCHEMA.PROFILING")
     
168. records = cursor.fetchall()
     
169. query_id=records[0][0] +2 #skip next sql
     
170. 
     
171. if f_find_in_list(p_option,'STATUS'):
     
172. #cursor.execute("select concat(upper(left(variable_name,1)),substring(lower(variable_name),2,(length(variable_name)-1))) var_name,variable_value var_value from performance_schema.session_status where variable_name in('"+"','".join(tuple(SES_STATUS_ITEM))+"') order by 1")
     
173. cursor.execute("select concat(upper(left(variable_name,1)),substring(lower(variable_name),2,(length(variable_name)-1))) var_name,variable_value var_value from performance_schema.session_status order by 1")
     
174. records = cursor.fetchall()
     
175. results['BEFORE_STATUS']=dict(records)
     
176. 
     
177. cursor.execute(p_sqltext)
     
178. 
     
179. if f_find_in_list(p_option,'STATUS'):
     
180. cursor.execute("select concat(upper(left(variable_name,1)),substring(lower(variable_name),2,(length(variable_name)-1))) var_name,variable_value var_value from performance_schema.session_status order by 1")
     
181. records = cursor.fetchall()
     
182. results['AFTER_STATUS']=dict(records)
     
183. 
     
184. if f_find_in_list(p_option,'PROFILING'):
     
185. cursor.execute("select STATE,DURATION,CPU_USER,CPU_SYSTEM,BLOCK_OPS_IN,BLOCK_OPS_OUT ,MESSAGES_SENT ,MESSAGES_RECEIVED ,PAGE_FAULTS_MAJOR ,PAGE_FAULTS_MINOR ,SWAPS from INFORMATION_SCHEMA.PROFILING where query_id="+str(query_id)+" order by seq")
     
186. records = cursor.fetchall()
     
187. results['PROFILING_DETAIL']=records
     
188. 
     
189. cursor.execute("SELECT STATE,SUM(DURATION) AS Total_R,ROUND(100*SUM(DURATION)/(SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID="+str(query_id)+"),2) AS Pct_R,COUNT(*) AS Calls,SUM(DURATION)/COUNT(*) AS R_Call FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID="+str(query_id)+" GROUP BY STATE ORDER BY Total_R DESC")
     
190. records = cursor.fetchall()
     
191. results['PROFILING_SUMMARY']=records
     
192. 
     
193. cursor.close()
     
194. conn.close()
     
195. return results
     
196. 
     
197. def f_print_status(p_before_status,p_after_status):
     
198. print "===== SESSION STATUS (DIFFERENT) ====="
     
199. status_title=('status_name','before','after','diff')
     
200. print "+-------------------------------------+-----------------+-----------------+-----------------+"
     
201. print seq3,status_title[0].center(35),
     
202. print seq3,status_title[1].center(15),
     
203. print seq3,status_title[2].center(15),
     
204. print seq3,status_title[3].center(15),seq3
     
205. print "+-------------------------------------+-----------------+-----------------+-----------------+"
     
206. 
     
207. for key in sorted(p_before_status.keys()):
     
208. if p_before_status[key]<>p_after_status[key]:
     
209. print seq3,key.ljust(35),
     
210. print seq3,p_before_status[key].rjust(15),
     
211. print seq3,p_after_status[key].rjust(15),
     
212. print seq3,str(float(p_after_status[key])-float(p_before_status[key])).rjust(15),seq3
     
213. print "+-------------------------------------+-----------------+-----------------+-----------------+"
     
214. print
     
215. 
     
216. def f_print_time(p_starttime,p_endtime):
     
217. print "===== EXECUTE TIME ====="
     
218. print timediff(p_starttime,p_endtime)
     
219. print
     
220. 
     
221. 
     
222. def f_print_profiling(p_profiling_detail,p_profiling_summary):
     
223. print "===== SQL PROFILING(DETAIL)====="
     
224. status_title=('state','duration','cpu_user','cpu_sys','bk_in','bk_out','msg_s','msg_r','p_f_ma','p_f_mi','swaps')
     
225. print "+--------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+"
     
226. print seq3,status_title[0].center(30),
     
227. print seq3,status_title[1].center(8),
     
228. print seq3,status_title[2].center(8),
     
229. print seq3,status_title[3].center(8),
     
230. print seq3,status_title[4].center(8),
     
231. print seq3,status_title[5].center(8),
     
232. print seq3,status_title[6].center(8),
     
233. print seq3,status_title[7].center(8),
     
234. print seq3,status_title[8].center(8),
     
235. print seq3,status_title[9].center(8),
     
236. print seq3,status_title[10].center(8),seq3
     
237. print "+--------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+"
     
238. 
     
239. for row in p_profiling_detail:
     
240. print seq3,row[0].ljust(30),
     
241. print seq3,str(row[1]).rjust(8),
     
242. print seq3,str(row[2]).rjust(8),
     
243. print seq3,str(row[3]).rjust(8),
     
244. print seq3,str(row[4]).rjust(8),
     
245. print seq3,str(row[5]).rjust(8),
     
246. print seq3,str(row[6]).rjust(8),
     
247. print seq3,str(row[7]).rjust(8),
     
248. print seq3,str(row[8]).rjust(8),
     
249. print seq3,str(row[9]).rjust(8),
     
250. print seq3,str(row[10]).rjust(8),seq3
     
251. print "+--------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+"
     
252. print 'bk_in: block_ops_in'
     
253. print 'bk_out: block_ops_out'
     
254. print 'msg_s: message sent'
     
255. print 'msg_r: message received'
     
256. print 'p_f_ma: page_faults_major'
     
257. print 'p_f_mi: page_faults_minor'
     
258. print
     
259. 
     
260. print "===== SQL PROFILING(SUMMARY)====="
     
261. status_title=('state','total_r','pct_r','calls','r/call')
     
262. print "+-------------------------------------+-----------------+------------+-------+-----------------+"
     
263. print seq3,status_title[0].center(35),
     
264. print seq3,status_title[1].center(15),
     
265. print seq3,status_title[2].center(10),
     
266. print seq3,status_title[3].center(5),
     
267. print seq3,status_title[4].center(15),seq3
     
268. print "+-------------------------------------+-----------------+------------+-------+-----------------+"
     
269. 
     
270. for row in p_profiling_summary:
     
271. print seq3,row[0].ljust(35),
     
272. print seq3,str(row[1]).rjust(15),
     
273. print seq3,str(row[2]).rjust(10),
     
274. print seq3,str(row[3]).rjust(5),
     
275. print seq3,str(row[4]).rjust(15),seq3
     
276. print "+-------------------------------------+-----------------+------------+-------+-----------------+"
     
277. print
     
278. 
     
279. def f_get_sqlplan(p_dbinfo,p_sqltext):
     
280. results={}
     
281. 
     
282. db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
     
283. cursor = db.cursor()
     
284. cursor.execute("explain extended "+p_sqltext)
     
285. records = cursor.fetchall()
     
286. results['SQLPLAN']=records
     
287. cursor.execute("show warnings")
     
288. records = cursor.fetchall()
     
289. results['WARNING']=records
     
290. cursor.close()
     
291. db.close()
     
292. return results
     
293. 
     
294. def f_print_sqlplan(p_sqlplan,p_warning):
     
295. plan_title=('id','select_type','table','partitions','type','possible_keys','key','key_len','ref','rows','filtered','Extra')
     
296. 
     
297. print "===== SQL PLAN ====="
     
298. print "+--------+------------------+------------+------------+------------+---------------+------------+------------+------------+------------+------------+------------+"
     
299. print seq3,plan_title[0].center(6),
     
300. print seq3,plan_title[1].center(16),
     
301. print seq3,plan_title[2].center(10),
     
302. print seq3,plan_title[3].center(10),
     
303. print seq3,plan_title[4].center(10),
     
304. print seq3,plan_title[5].center(10),
     
305. print seq3,plan_title[6].center(10),
     
306. print seq3,plan_title[7].center(10),
     
307. print seq3,plan_title[8].center(10),
     
308. print seq3,plan_title[9].center(10),
     
309. print seq3,plan_title[10].center(10),
     
310. print seq3,plan_title[11].center(10),seq3
     
311. print "+--------+------------------+------------+------------+------------+---------------+------------+------------+------------+------------+------------+------------+"
     
312. for row in p_sqlplan:
     
313. print seq3,str(row[0]).rjust(6), # id
     
314. print seq3,row[1].ljust(16), # select_type
     
315. #print seq3,row[2].ljust(10), # table
     
316. if not "NonyType" in str(type(row[2])):
     
317. print seq3,row[2].ljust(10),
     
318. else:
     
319. print seq3,"NULL".ljust(10),
     
320. 
     
321. if not "NoneType" in str(type(row[3])): #partitions
     
322. print seq3,row[3].ljust(10),
     
323. else:
     
324. print seq3,"NULL".ljust(10),
     
325. 
     
326. #print seq3,row[3].ljust(10),
     
327. if not "NoneType" in str(type(row[4])): #type
     
328. print seq3,row[4].ljust(10),
     
329. else:
     
330. print seq3,"NULL".ljust(10),
     
331. 
     
332. if not "NoneType" in str(type(row[5])): # possible_keys
     
333. print seq3,row[5].ljust(13),
     
334. else:
     
335. print seq3,"NULL".ljust(13),
     
336. 
     
337. if not "NoneType" in str(type(row[6])): # key
     
338. print seq3,row[6].ljust(10),
     
339. else:
     
340. print seq3,"NULL".ljust(10),
     
341. 
     
342. if not "NoneType" in str(type(row[7])): # key_len
     
343. print seq3,row[7].ljust(10),
     
344. else:
     
345. print seq3,"NULL".ljust(10),
     
346. 
     
347. if not "NoneType" in str(type(row[8])): # ref
     
348. print seq3,row[8].ljust(10),
     
349. else:
     
350. print seq3,"NULL".ljust(10),
     
351. 
     
352. print seq3,str(row[9]).rjust(10), # rows
     
353. 
     
354. print seq3,str(row[10]).rjust(10), # filters
     
355. 
     
356. if not "NoneType" in str(type(row[11])): # Extra
     
357. print seq3,row[11].ljust(10),
     
358. else:
     
359. print seq3,"NULL".ljust(10),
     
360. print seq3
     
361. 
     
362. print "+--------+------------------+------------+------------+------------+---------------+------------+------------+------------+------------+------------+------------+"
     
363. print
     
364. 
     
365. print "===== OPTIMIZER REWRITE SQL ====="
     
366. for row in p_warning:
     
367. print sqlparse.format(row[2],reindent=True, keyword_case='upper',strip_comments=True)
     
368. print
     
369. 
     
370. def f_get_table(p_dbinfo,p_sqltext):
     
371. r_tables=[]
     
372. db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
     
373. cursor = db.cursor()
     
374. cursor.execute("explain "+p_sqltext)
     
375. rows = cursor.fetchall ()
     
376. for row in rows:
     
377. table_name = row[2]
     
378. if '<' in table_name:
     
379. continue
     
380. if len(r_tables)==0:
     
381. r_tables.append(table_name)
     
382. elif f_find_in_list(r_tables,table_name) == -1:
     
383. r_tables.append(table_name)
     
384. cursor.close()
     
385. db.close()
     
386. return r_tables
     
387. 
     
388. def f_print_tableinfo(p_dbinfo,p_tablename):
     
389. plan_title=('table_name','engine','format','table_rows','avg_row','total_mb','data_mb','index_mb')
     
390. db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
     
391. cursor = db.cursor()
     
392. stmt = "select engine,row_format as format,table_rows,avg_row_length as avg_row,round((data_length+index_length)/1024/1024,2) as total_mb,round((data_length)/1024/1024,2) as data_mb,round((index_length)/1024/1024,2) as index_mb from information_schema.tables where table_schema='"+p_dbinfo[3]+"' and table_name='"+p_tablename+"'"
     
393. cursor.execute(stmt)
     
394. rows = cursor.fetchall ()
     
395. print "+-----------------+------------+------------+------------+------------+------------+------------+------------+"
     
396. print seq3,plan_title[0].center(15),
     
397. print seq3,plan_title[1].center(10),
     
398. print seq3,plan_title[2].center(10),
     
399. print seq3,plan_title[3].center(10),
     
400. print seq3,plan_title[4].center(10),
     
401. print seq3,plan_title[5].center(10),
     
402. print seq3,plan_title[6].center(10),
     
403. print seq3,plan_title[7].center(10),seq3
     
404. print "+-----------------+------------+------------+------------+------------+------------+------------+------------+"
     
405. for row in rows:
     
406. print seq3,p_tablename.ljust(15),
     
407. print seq3,row[0].ljust(10),
     
408. print seq3,row[1].ljust(10),
     
409. print seq3,str(row[2]).rjust(10),
     
410. print seq3,str(row[3]).rjust(10),
     
411. print seq3,str(row[4]).rjust(10),
     
412. print seq3,str(row[5]).rjust(10),
     
413. print seq3,str(row[6]).rjust(10),seq3
     
414. print "+-----------------+------------+------------+------------+------------+------------+------------+------------+"
     
415. cursor.close()
     
416. db.close()
     
417. 
     
418. def f_print_indexinfo(p_dbinfo,p_tablename):
     
419. plan_title=('index_name','non_unique','seq_in_index','column_name','collation','cardinality','nullable','index_type')
     
420. db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
     
421. cursor = db.cursor()
     
422. stmt = "select index_name,non_unique,seq_in_index,column_name,collation,cardinality,nullable,index_type from information_schema.statistics where table_schema='"+p_dbinfo[3]+"' and table_name='"+p_tablename+"' order by 1,3"
     
423. cursor.execute(stmt)
     
424. rows = cursor.fetchall ()
     
425. if len(rows)>0:
     
426. print "+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+"
     
427. print seq3,plan_title[0].center(15),
     
428. print seq3,plan_title[1].center(15),
     
429. print seq3,plan_title[2].center(15),
     
430. print seq3,plan_title[3].center(15),
     
431. print seq3,plan_title[4].center(15),
     
432. print seq3,plan_title[5].center(15),
     
433. print seq3,plan_title[6].center(15),
     
434. print seq3,plan_title[7].center(15),seq3
     
435. print "+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+"
     
436. for row in rows:
     
437. print seq3,row[0].ljust(15),
     
438. print seq3,str(row[1]).rjust(15),
     
439. print seq3,str(row[2]).rjust(15),
     
440. print seq3,str(row[3]).rjust(15),
     
441. print seq3,str(row[4]).rjust(15),
     
442. print seq3,str(row[5]).rjust(15),
     
443. print seq3,str(row[6]).rjust(15),
     
444. print seq3,str(row[7]).rjust(15),seq3
     
445. print "+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+-----------------+"
     
446. cursor.close()
     
447. db.close()
     
448. 
     
449. def f_get_mysql_version(p_dbinfo):
     
450. db = MySQLdb.connect(host=p_dbinfo[0], user=p_dbinfo[1], passwd=p_dbinfo[2],db=p_dbinfo[3])
     
451. cursor = db.cursor()
     
452. cursor.execute("select @@version")
     
453. records = cursor.fetchall ()
     
454. cursor.close()
     
455. db.close()
     
456. return records[0][0]
     
457. 
     
458. def f_print_title(p_dbinfo,p_mysql_version,p_sqltext):
     
459. print '*'*100
     
460. print '*','MySQL SQL Tuning Tools v1.0 for mysql5.7(created by hanfeng modified by ycc)'.center(96),'*'
     
461. print '*'*100
     
462. 
     
463. print
     
464. print "===== BASIC INFORMATION ====="
     
465. title=('server_ip','user_name','db_name','db_version')
     
466. print "+----------------------+------------+------------+------------+"
     
467. print seq3,title[0].center(20),
     
468. print seq3,title[1].center(10),
     
469. print seq3,title[2].center(10),
     
470. print seq3,title[3].center(10),seq3
     
471. print "+----------------------+------------+------------+------------+"
     
472. print seq3,p_dbinfo[0].center(20),
     
473. print seq3,p_dbinfo[1].center(10),
     
474. print seq3,p_dbinfo[3].center(10),
     
475. print seq3,p_mysql_version.center(10),seq3
     
476. print "+----------------------+------------+------------+------------+"
     
477. print
     
478. print "===== ORIGINAL SQL TEXT ====="
     
479. print sqlparse.format(p_sqltext,reindent=True, keyword_case='upper')
     
480. print
     
481. 
     
482. '''
     
483. def f_print_table(p_value,p_option): #p_option "(key-n => title,max_len,align_value)"
     
484. for k in p_option.keys():
     
485. v = p_option[k]
     
486. print "+",
     
487. print int(v.split(',')[1])*"-",
     
488. print "+"
     
489. 
     
490. for k in p_option.keys():
     
491. v = p_option[k]
     
492. print "|",
     
493. print v.split(',')[0].center(int(v.split(',')[0])-2),
     
494. print "|",
     
495. 
     
496. for k in p_option.keys():
     
497. v = p_option[k]
     
498. print "+",
     
499. print int(v.split(',')[1])*"-",
     
500. print "+"
     
501. 
     
502. for row in p_value:
     
503. k=0
     
504. for col in row:
     
505. k+=1
     
506. print "|",
     
507. if p_option[k].split(',')[2]=='l':
     
508. print col.ljust(p_option[k].split(',')[1]),
     
509. elif p_option[k].split(',')[2]=='r':
     
510. print col.rjust(p_option[k].split(',')[1]),
     
511. else
     
512. print col.center(p_option[k].split(',')[1]),
     
513. print "|",
     
514. 
     
515. for k in p_option.keys():
     
516. v = p_option[k]
     
517. print "+",
     
518. print int(v.split(',')[1])*"-",
     
519. print "+"
     
520. '''
     
521. 
     
522. def timediff(timestart, timestop):
     
523. t = (timestop-timestart)
     
524. time_day = t.days
     
525. s_time = t.seconds
     
526. ms_time = t.microseconds / 1000000
     
527. usedtime = int(s_time + ms_time)
     
528. time_hour = usedtime / 60 / 60
     
529. time_minute = (usedtime - time_hour * 3600 ) / 60
     
530. time_second = usedtime - time_hour * 3600 - time_minute * 60
     
531. time_micsecond = (t.microseconds - t.microseconds / 1000000) / 1000
     
532. 
     
533. retstr = "%d day %d hour %d minute %d second %d microsecond " %(time_day, time_hour, time_minute, time_second, time_micsecond)
     
534. return retstr
     
535. 
     
536. if __name__=="__main__":
     
537. dbinfo=["","","",""] #dbhost,dbuser,dbpwd,dbname
     
538. sqltext=""
     
539. option=[]
     
540. config_file=""
     
541. mysql_version=""
     
542. 
     
543. opts, args = getopt.getopt(sys.argv[1:], "p:s:")
     
544. for o,v in opts:
     
545. if o == "-p":
     
546. config_file = v
     
547. elif o == "-s":
     
548. sqltext = v
     
549. 
     
550. config = ConfigParser.ConfigParser()
     
551. config.readfp(open(config_file,"rb"))
     
552. dbinfo[0] = config.get("database","server_ip")
     
553. dbinfo[1] = config.get("database","db_user")
     
554. dbinfo[2] = config.get("database","db_pwd")
     
555. dbinfo[3] = config.get("database","db_name")
     
556. 
     
557. mysql_version = f_get_mysql_version(dbinfo)
     
558. 
     
559. f_print_title(dbinfo,mysql_version,sqltext)
     
560. 
     
561. if config.get("option","sys_parm")=='ON':
     
562. parm_result = f_get_parm(dbinfo)
     
563. f_print_parm(parm_result)
     
564. f_print_optimizer_switch(dbinfo)
     
565. 
     
566. if config.get("option","sql_plan")=='ON':
     
567. sqlplan_result = f_get_sqlplan(dbinfo,sqltext)
     
568. f_print_sqlplan(sqlplan_result['SQLPLAN'],sqlplan_result['WARNING'])
     
569. 
     
570. if config.get("option","obj_stat")=='ON':
     
571. print "===== OBJECT STATISTICS ====="
     
572. for table_name in extract_tables(sqltext):
     
573. f_print_tableinfo(dbinfo,table_name)
     
574. f_print_indexinfo(dbinfo,table_name)
     
575. print
     
576. 
     
577. if config.get("option","ses_status")=='ON':
     
578. option.append('STATUS')
     
579. 
     
580. if config.get("option","sql_profile")=='ON':
     
581. option.append('PROFILING')
     
582. 
     
583. if config.get("option","ses_status")=='ON' or config.get("option","sql_profile")=='ON':
     
584. starttime = datetime.datetime.now()
     
585. exec_result = f_exec_sql(dbinfo,sqltext,option)
     
586. endtime = datetime.datetime.now()
     
587. 
     
588. if config.get("option","ses_status")=='ON':
     
589. f_print_status(exec_result['BEFORE_STATUS'],exec_result['AFTER_STATUS'])
     
590. 
     
591. if config.get("option","sql_profile")=='ON':
     
592. f_print_profiling(exec_result['PROFILING_DETAIL'],exec_result['PROFILING_SUMMARY