SQL编写规范及性能排查一些方法

SQL 语句编写规范

1. 避免使用select *，对于宽表来说，这是灾难；
2. 严禁不加任何where条件读取数据；
3. MySQL中的text类型字段独立存储，数据量少的表除外：
4. Where条件中的过滤条件字段上严禁使用任何函数，包括数据类型转换函数；
5. 分页查询语句全部都需要带有排序条件 , 否则很容易引起乱序；
6. 用in()/union替换or；
7. 严禁使用%前缀进行模糊前缀查询；
8. 避免使用子查询，可以把子查询优化为join操作；
9. 分页查询，当limit起点较高时，可先用过滤条件进行过滤；

MySQL 语句性能排查方法

1、.排查sql排序数最多语句

select schema_name,digest_text,count_star,sum_rows_sent,sum_sort_rows,first_seen,last_seen 
from performance_schema.events_statements_summary_by_digest 
where
schema_name is not null and schema_name!='information_schema' order by sum_sort_rows desc limit 10;

2、排查IO消耗最多的文件或表

select file_name,event_name,count_read,sum_number_of_bytes_read,count_write,sum_number_of_bytes_write 
from performance_schema.file_summary_by_instance 
order by sum_number_of_bytes_read+sum_number_of_bytes_write desc limit 10;

3、索引使用排行

select object_name, index_name, count_fetch, count_insert, count_update, count_delete 
from performance_schema.table_io_waits_summary_by_index_usage order by sum_timer_wait desc limit 1;

4、排查IO等待事件消耗最多

SELECT event_name, count_star, sum_timer_wait, avg_timer_wait 
FROM performance_schema.events_waits_summary_global_by_event_name 
WHERE event_name!='idle' ORDER BY sum_timer_wait DESC LIMIT 10;

5、排查临时表使用最多

SELECT schema_name,digest_text,sum_created_tmp_disk_tables,sum_created_tmp_tables,first_seen,last_seen 
FROM performance_schema.events_statements_summary_by_digest 
WHERE schema_name IS NOT NULL AND schema_name!='information_schema' 
ORDER BY sum_created_tmp_disk_tables DESC LIMIT 10;

6、排查返回的结果集最多

SELECT schema_name,digest_text,count_star,sum_rows_sent,sum_rows_sent,first_seen,last_seen 
FROM performance_schema.events_statements_summary_by_digest 
WHERE schema_name IS NOT NULL AND schema_name!='information_schema' 
ORDER BY sum_rows_sent DESC LIMIT 10;

7、排查响应最长SQL

schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,first_seen,last_seen 
from performance_schema.events_statements_summary_by_digest 
where schema_name is not null and schema_name!='information_schema'
order by avg_timer_wait desc limit 10;