千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2025-01-24 作者：千家信息网编辑
千家信息网最后更新 2025年01月24日，韩锋大师分享了一个mysql的性能优化工具，一个能自动采集SQL优化相关各种信息的python脚本，对于优化人员而言，这能省不少事，赞！试用了一下，发现在mysql5.7下，运行不起来，细节上还是有好
千家信息网最后更新 2025年01月24日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

  14. filterwarnings('ignore', category = MySQLdb.Warning)

  16. seq1="+"
  17. seq2="-"
  18. seq3="|"

  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. )

  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

  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

  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

  96. def extract_tables(p_sqltext):
  97. stream = extract_from_part(sqlparse.parse(p_sqltext)[0])
  98. return list(extract_table_identifiers(stream))

  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

  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

  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 "+--------------------------------+------------------------------------------------------------+"

  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

  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

  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()

  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

  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)

  177. cursor.execute(p_sqltext)

  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)

  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

  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

  193. cursor.close()
  194. conn.close()
  195. return results

  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 "+-------------------------------------+-----------------+-----------------+-----------------+"

  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

  216. def f_print_time(p_starttime,p_endtime):
  217. print "===== EXECUTE TIME ====="
  218. print timediff(p_starttime,p_endtime)
  219. print


  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 "+--------------------------------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+"

  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

  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 "+-------------------------------------+-----------------+------------+-------+-----------------+"

  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

  279. def f_get_sqlplan(p_dbinfo,p_sqltext):
  280. results={}

  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

  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')

  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),

  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),

  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),

  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),

  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),

  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),

  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),

  352. print seq3,str(row[9]).rjust(10), # rows

  354. print seq3,str(row[10]).rjust(10), # filters

  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

  362. print "+--------+------------------+------------+------------+------------+---------------+------------+------------+------------+------------+------------+------------+"
  363. print

  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

  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

  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()

  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()

  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]

  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

  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

  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 "+"

  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 "|",

  496. for k in p_option.keys():
  497. v = p_option[k]
  498. print "+",
  499. print int(v.split(',')[1])*"-",
  500. print "+"

  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 "|",

  515. for k in p_option.keys():
  516. v = p_option[k]
  517. print "+",
  518. print int(v.split(',')[1])*"-",
  519. print "+"
  520. '''

  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

  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

  536. if __name__=="__main__":
  537. dbinfo=["","","",""] #dbhost,dbuser,dbpwd,dbname
  538. sqltext=""
  539. option=[]
  540. config_file=""
  541. mysql_version=""

  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

  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")

  557. mysql_version = f_get_mysql_version(dbinfo)

  559. f_print_title(dbinfo,mysql_version,sqltext)

  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)

  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'])

  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

  577. if config.get("option","ses_status")=='ON':
  578. option.append('STATUS')

  580. if config.get("option","sql_profile")=='ON':
  581. option.append('PROFILING')

  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()

  588. if config.get("option","ses_status")=='ON':
  589. f_print_status(exec_result['BEFORE_STATUS'],exec_result['AFTER_STATUS'])

  591. if config.get("option","sql_profile")=='ON':
  592. f_print_profiling(exec_result['PROFILING_DETAIL'],exec_result['PROFILING_SUMMARY


很赞哦！
问题 脚本 会报 办法 引号 细节 大师 工具 性能 人员 信息 单元 周折 完了 小数 就是 老师 还是 处理 示范 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 滴滴软件开发商 服务器返回代码300 易语言利用网页做激活码服务器 上传文件到服务器路径 ctf网络安全大赛今年 部门对软件开发部门评价 软件开发需求量化评估案例 上海访客管理软件开发定制 武侯区佳和软件开发工作室 科技互联网分享 大摩软件开发 服务器设备io性能低 网络安全训练营未授权访问 网络安全实战教学 单位网络安全职能 网站安全证书服务器 学软件开发去当兵 菁彩校园服务器异常 抖音数据库公司 上海正和网络技术有限公司 经验教训数据库管理办法 永兴租房软件开发 维普数据库的特点 沂源外贸erp软件开发公司 镇江经贸网络技术 数据库判断该字段不在另一个表中 aspx测试数据库连接 域服务器策略管理模板 云服务器20t空间 杭州智付家网络技术有限公司

相关文章

0