mysql 性能 c_MySQL性能优化（慢查询处理以及优化）

1、配置慢查询日志

set global slow_query_log=on #动态参数，不需要重启就可以打开慢查询日志

slow_query_log_file #指定慢查询日志的路径和文件，默认在数据目录下

long_query_time #指定SQL执行时间的阈值，默认10s，修改后重新连接才可看到更新后的值

log_queries_not_using_indexes #记录未使用索引的sql

2、mysqldumpslow 分析慢查询日志

mysqldumpslow -s r -t 10 slow-mysql.log

-s参数：指定按哪种排序方式输出结果,c:总次数,t:总时间,l:锁时间,r:总行数, at,al,ar:t,l,r平均数

-t参数：指定前几条输出

3、实时获取有性能问题的SQL

select id, `user`, `host`, DB, command, `time`, state, info

from information_schema.PROCESSLIST where TIME > 60

4、使用performance_schema

在performance_schema数据库下执行

UPDATE `setup_instruments` SET enabled='YES', TIMED='YES' WHERE NAME LIKE 'stage%';

UPDATE `setup_consumers` SET enabled = 'YES' WHERE NAME LIKE 'events%';

5、特定SQL的查询优化

a、大表的数据修改分批处理

b、大表表结构的修改

建立新表，从老表导入数据，创建老表触发器，同步修改到新表，给老表上排他锁，老表重命名，新表重命名

c、not in 和 <> 查询

下面sql会对payment进行多次查询

select customer_id, first_name, last_name, email from customer

where customer_id not in (select customer_id from payment);

改为left join后，查询一次即可

select a.customer_id, a.first_name, a.last_name, a.email

from customer a left join payment b on a.customer_id = b.customer_id

where b.customer_id is null;

d、使用汇总表优化查询

提前将要统计的数据进行汇总，并记录到表中，以备后续的查询使用