sql优化

获取有问题的sql：通过用户反馈，通过慢查询日志，实时获取存在问题的sql

 

实时获取有性能问题的sql :

information_schema数据库下的processlist表

select id,'user','host',DB,command,'time',state,info FROM information_schema.PROCESSLIST WHERE TIME>=60    （查询>60秒的sql)

 

 

使用慢查询日志：

slow_query_log  启动记录慢查询日志

slow_query_log_file  指定慢查询日志的存储路径及文件

建议设置，将日志文件和mysql数据文件分开，最好可以放到不同的磁盘分区中

long_query_time  指定记录慢查询日志sql执行时间的伐值

默认十秒，通常改为1毫秒（0.001秒），不过要注意日志大小，以防日志太短影响磁盘读取性能

log_queries_not_using_indexes  是否记录未使用索引的SQL

 

 

常用慢查询日志分析工具：mysqldumpslow

汇总除查询条件外其他完全相同的sql.并将分析结构按照参数中所指定的顺序输出

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

 

 

pt-query-digest --explain h=127.0.0.1 slow-mysql.log > slow.rep

 

搞清除这些查询问什么慢：

 

 

query_cache_type设置查询缓存是否可用

设置成DEMAND表示只有在查询语句中使用sql_cache和sql_no_cache来控制是否需要缓存

query_cache_size 设置查询缓存的内存大小

query_cache_limit 设置查询缓存可用存储的最大值

如果知道缓存太大，缓存不上时加上sql_no_cache提示字可用提高效率

query_cache_wlock_invalidate  设置数据表被锁后是否返回缓存中的数据

query_cache_min_res_unit   设置查询缓存分配的内存块最小单位

 

在读写比较频繁时建议关闭缓存 query_cache_type=OFF  query_cache_size=0

 

MySQL优化器可优化的SQL类型：

1、重新定义表的关联顺序

2、将外链接转成内链接

3、使用等价交换规则（a=5 and 5>5)将被改写成a>5

4、优化count（）、min()和max()

5、将一个表达式转换为常数表达式

6、子查询优化

7、提前终止查询

8、对in（）条件进行优化

 

如何确定查询处理各个阶段所消耗 的时间：

1、使用profile: 

set profiling=1;       （set  profiling = 1)

执行查询 ;     

show profiles;           查看每一个查询所消耗的总时间的信息  

show profile  for query  N;              查询的每个阶段所消耗的时间

show profile cpu for query N;                查看cpu信息

 

 

2、使用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 'event%';

 

特点sql的优化：

大表数据分批处理

如何修改大表的表结构：

pt-online-schema-change \

--alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT '' " \

--user=root --password=PassWord D=imooc,t=sbtest4 \

--charset = utf8 --execute

 

例如：pt-online-schema-change  --alter="modify c varchar(150) not null default '' " --user=root --password=PassWord D=imooc,t=sbtest4 --charset =utf8 --execute

 

 

 

 使用汇总表优化查询