def users(request):
instance_id = request.POST.get('instance_id')
instance_name = Instance.objects.get(id=instance_id).instance_name
sql_get_user = '''select concat("\'", user, "\'", '@', "\'", host,"\'") as query from mysql.user;'''
dao = Dao(instance_name=instance_name, flag=True)
db_users = dao.mysql_query('mysql', sql_get_user)['rows']
# 获取用户权限信息
data = []
for db_user in db_users:
user_info = {}
user_priv = dao.mysql_query('mysql', 'show grants for {};'.format(db_user[0]))['rows']
user_info['user'] = db_user[0]
user_info['privileges'] = user_priv
data.append(user_info)
# 关闭连接
dao.close()
result = {'status': 0, 'msg': 'ok', 'data': data}
return HttpResponse(json.dumps(result, cls=ExtendJSONEncoder, bigint_as_string=True),
content_type='application/json')
class SqlTuning(object):
def __init__(self, instance_name, db_name, sqltext):
self.dao = Dao(instance_name=instance_name, flag=True)
self.db_name = db_name
self.sqltext = sqltext
self.sql_variable = '''
select
lower(variable_name),
variable_value
from performance_schema.global_variables
where upper(variable_name) in ('%s')
order by variable_name;''' % ('\',\''.join(SQLTuning.SYS_PARM_FILTER))
self.sql_optimizer_switch = '''
select variable_value
from performance_schema.global_variables
where upper(variable_name) = 'OPTIMIZER_SWITCH';
'''
self.sql_table_info = '''
select
table_name,
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 = '%s' and table_name = '%s'
'''
self.sql_table_index = '''
select
table_name,
index_name,
non_unique,
seq_in_index,
column_name,
collation,
cardinality,
nullable,
index_type
from information_schema.statistics
where table_schema = '%s' and table_name = '%s'
order by 1, 3;
'''
@staticmethod
def __is_subselect(parsed):
if not parsed.is_group:
return False
for item in parsed.tokens:
if item.ttype is DML and item.value.upper() == 'SELECT':
return True
return False
def __extract_from_part(self, parsed):
from_seen = False
for item in parsed.tokens:
# print item.ttype,item.value
if from_seen:
if self.__is_subselect(item):
for x in self.__extract_from_part(item):
yield x
elif item.ttype is Keyword:
raise StopIteration
else:
yield item
elif item.ttype is Keyword and item.value.upper() == 'FROM':
from_seen = True
@staticmethod
def __extract_table_identifiers(token_stream):
for item in token_stream:
if isinstance(item, IdentifierList):
for identifier in item.get_identifiers():
yield identifier.get_real_name()
elif isinstance(item, Identifier):
yield item.get_real_name()
# It's a bug to check for Keyword here, but in the example
# above some tables names are identified as keywords...
elif item.ttype is Keyword:
yield item.value
def __extract_tables(self, p_sqltext):
stream = self.__extract_from_part(sqlparse.parse(p_sqltext)[0])
return list(self.__extract_table_identifiers(stream))
def basic_information(self):
return self.dao.mysql_query(sql="select @@version")
def sys_parameter(self):
# 获取mysql版本信息
version = self.basic_information()['rows'][0][0]
server_version = tuple(
[numeric_part(n) for n in version.split('.')[:2]])
if server_version < (5, 7):
sql = self.sql_variable.replace('performance_schema',
'information_schema')
else:
sql = self.sql_variable
return self.dao.mysql_query(sql=sql)
def optimizer_switch(self):
# 获取mysql版本信息
version = self.basic_information()['rows'][0][0]
server_version = tuple(
[numeric_part(n) for n in version.split('.')[:2]])
if server_version < (5, 7):
sql = self.sql_optimizer_switch.replace('performance_schema',
'information_schema')
else:
sql = self.sql_optimizer_switch
return self.dao.mysql_query(sql=sql)
def sqlplan(self):
plan = self.dao.mysql_query(self.db_name,
"explain extended " + self.sqltext)
optimizer_rewrite_sql = self.dao.mysql_query(sql="show warnings")
return plan, optimizer_rewrite_sql
# 获取关联表信息存在缺陷，只能获取到一张表
def object_statistics(self):
tableistructure = {'column_list': [], 'rows': []}
tableinfo = {'column_list': [], 'rows': []}
indexinfo = {'column_list': [], 'rows': []}
for index, table_name in enumerate(self.__extract_tables(
self.sqltext)):
tableistructure = self.dao.mysql_query(
db_name=self.db_name,
sql="show create table {};".format(
table_name.replace('`', '').lower()))
tableinfo = self.dao.mysql_query(
sql=self.sql_table_info %
(self.db_name, table_name.replace('`', '').lower()))
indexinfo = self.dao.mysql_query(
sql=self.sql_table_index %
(self.db_name, table_name.replace('`', '').lower()))
return tableistructure, tableinfo, indexinfo
def exec_sql(self):
result = {
"EXECUTE_TIME": 0,
"BEFORE_STATUS": {
'column_list': [],
'rows': []
},
"AFTER_STATUS": {
'column_list': [],
'rows': []
},
"SESSION_STATUS(DIFFERENT)": {
'column_list': ['status_name', 'before', 'after', 'diff'],
'rows': []
},
"PROFILING_DETAIL": {
'column_list': [],
'rows': []
},
"PROFILING_SUMMARY": {
'column_list': [],
'rows': []
}
}
sql_profiling = "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"
# 获取mysql版本信息
version = self.basic_information()['rows'][0][0]
server_version = tuple(
[numeric_part(n) for n in version.split('.')[:2]])
if server_version < (5, 7):
sql = sql_profiling.replace('performance_schema',
'information_schema')
else:
sql = sql_profiling
self.dao.mysql_query(sql="set profiling=1")
records = self.dao.mysql_query(
sql=
"select ifnull(max(query_id),0) from INFORMATION_SCHEMA.PROFILING")
query_id = records['rows'][0][0] + 3 # skip next sql
# 获取执行前信息
result['BEFORE_STATUS'] = self.dao.mysql_query(sql=sql)
# 执行查询语句,统计执行时间
t_start = time.time()
self.dao.mysql_query(sql=self.sqltext)
t_end = time.time()
cost_time = "%5s" % "{:.4f}".format(t_end - t_start)
result['EXECUTE_TIME'] = cost_time
# 获取执行后信息
result['AFTER_STATUS'] = self.dao.mysql_query(sql=sql)
# 获取PROFILING_DETAIL信息
result['PROFILING_DETAIL'] = self.dao.mysql_query(
sql=
"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")
result['PROFILING_SUMMARY'] = self.dao.mysql_query(
sql=
"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")
# 处理执行前后对比信息
before_status_rows = [
list(item) for item in result['BEFORE_STATUS']['rows']
]
after_status_rows = [
list(item) for item in result['AFTER_STATUS']['rows']
]
for index, item in enumerate(before_status_rows):
if before_status_rows[index][1] != after_status_rows[index][1]:
before_status_rows[index].append(after_status_rows[index][1])
before_status_rows[index].append(
str(
float(after_status_rows[index][1]) -
float(before_status_rows[index][1])))
diff_rows = [item for item in before_status_rows if len(item) == 4]
result['SESSION_STATUS(DIFFERENT)']['rows'] = diff_rows
return result
