干货系列——SQL语句的优化
SQL语句的优化
如何索取有性能问题SQL的渠道
通过用户反馈获取存在性能问题的SQL
通过慢查日志获取存在性能问题的SQL
实时获取存在性能问题的SQL
慢查询日志介绍
slow_quey_log=on 启动记录慢查询日志
slow_query_log_file 指定慢查询日志的存储路径及文件(默认情况下保存在MySQL的数据目录中)
long_query_time 指定记录慢查询日志sql执行的阈值（默认为10秒，通常改为0.001秒比较合适）
log_queries_not_using_indexes 是否记录未使用索引的SQL
set global sql_query_log=on;
sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=tests --mysql-user=sbtest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock run
慢查询日志分析工具
mysqldumpslow
汇总除查询条件外其它完全相同的SQL并将分析结果按照参数中所指定的顺序输出
mysqldumpslow -s r -t 10 slow-mysql.log
-s order(c,t,l,r,at,al,ar)[指定按照哪种排序方式输出结果]
t top[指定取前几条作为结束输出]
c按照查询的次数排序
t按照查询的总时间排序
l按照查询中锁的时间来排序
r按照查询中返回总的数据行来排序
at、al、ar平均数量来排序
pt-query-digest
pt-query-digest \
--explain h=127.0.0.1,u=root,p=p@ssWord \
slow-mysql.log
pt-query-digest --explain h=127.0.0.1 slow-mysql.log > slow.rep
实时获取存在性能问题的SQL
select id,user,host,db,command,time,state,info
FROM information_schema.processlist
WHERE time>=60
查询速度为什么会这麽慢？
客户端发送SQL请求给服务器
服务器检查是否可以在查询缓存中命中该SQL
服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划
根据执行计划，调用存储引擎API来查询数据
将结果返回给客户端
》 对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率，建议大家不要使用查询缓存
2.其中涉及的参数： query_cache_type 设置查询缓存是否可用[ON,OFF,DEMAND] DEMAND表示只有在查询语句中使用了SQL_CACHE和SQL_NO_CACHE来控制是否需要进行缓存 query_cache_size 设置查询缓存的内存的大小 query_cache_limit 设置查询缓存可用的存储的最大值（加上SQL_NO_CACHE可以提高效率） query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据 query_cache_min_res_unit 设置查询缓存分配的内存块最小单位 3.MySQL依照这个执行计划和存储引擎进行交互 解析SQL，预处理。优化SQL的查询计划 语法解析阶段是通过关键字对MySQL语句进行解析，并生成一颗对应的解析树 MySQL解析器将使用MySQL语法规则验证和解析查询，包括检查语法是否使用了正确的关键走；关键字的顺序是否正确等等； 预处理阶段是根据MySQL规则进一步检查解析树是否合法 检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等 语法检查通过了，查询优化器就可以生成查询计划了 优化器SQL的查询计划阶段对上一步所生成的执行计划进行选择基于成本模型的最优的执行计划【下面是影响选择最优的查询计划的7因素】 1.统计信息不准确 2.执行计划中的成本估算不等于实际的执行计划的成本 3.MySQL优化器认为的最优的可能与你认为最优的不一样【基于成本模型选择最优的执行计划】 4.MySQL从不考虑其他的并发的查询，这可能会影响当前查询的速度 5.MySQL有时候也会基于一些固定的规则来生成执行计划 6.MySQL不会考虑不受其控制的成本 查询优化器在目前的版本中可以进行优化的SQL的类型： 1.重新定义表的关联顺序 2.将外连接转化为内连接 3.使用等价变换规则 4.优化count(),min()和max()[select tables optimozed away] 5.将一个表达式转化为一个常数表达式 6.子查询优化 7.提前终止查询 8.对in()条件进行优化
如何确定查询处理各个阶段所消耗的时间
使用profile[不建议使用，未来mysql中将被移除]
set profiling = 1;[启动profile,这是一个session级别的配置]
执行查询
show profiles;[查看每一个查询所消耗的总的时间的信息]
show profile for query N;[查询的每个阶段所消耗的时间]
show profile cpu for query N;[查看每个阶段所消耗的时间信息和所消耗的cpu的信息]
使用performance_schema
启动所需要的监控和历史记录表的信息
update setup_instruments set enabled='yes',timed='yes' where name like 'stage%';
update setup_consumers set enabled='yes' where name like 'events%';
SELECT
a.thread_id,
sql_text,
c.event_name,
(c.timer_end - c.timer_start) / 1000000000 AS 'duration(ms)'
FROM
events_statements_history_long a
JOIN threads b on a.thread_id=b.thread_id
JOIN events_stages_history_long c ON c.thread_id=b.thread_id
AND c.event_id between a.event_id and a.end_event_id
WHERE b.processlist_id=CONNECTION_ID()
AND a.event_name='statement/sql/select'
ORDER BY a.thread_id,c.event_id
特定的SQL查询优化
大表的更新和删除
delimiter $$ use 'imooc'$$ drop procedure if exists 'p_delete_rows'$$ create definer='root'@'127.0.0.1' procedure 'p_delete_rows'() begin declare v_rows int; set v_rows int, while v_rows=1, while v_rows>0 do delete from test where id>=9000 and id<=19000 limit 5000; select row_count() into v_rows; select sleep(5); end while; end $$ delimiter;
如何修改大表的表结构
1.对表中的列的字段类型进行修改改变字段的宽度时还是会进行锁表
2.无法解决主从数据库延迟的问题
修改的方法：
pt-online-schema-change --alter="modify c varchar(150) not null default''" --user=root --password=PassWord D=testDataBaseName,t=tesTableName --charset=utf-8 --execute
如何优化not in和<>查询
#原始的SQL语句 SELECT customer_id, first_name, last_name, email FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM payment ) #优化后的SQL语句 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
使用汇总表的方法进行优化
#统计商品的评论数[优化前的SQL] select count(*) from product_comment where product_id=999; #汇总表就是提前以要统计的数据进行汇总并记录到数据库中以备后续的查询使用 create table product_comment_cnt(product_id int,cnt int); #统计商品的评论数[优化后的SQL] select sum(cnt) from( select cnt from product_comment_cnt where product_id=999 union all select count(*) from product_comment where product_id=999 and timestr>DATE(NOW()) ) a
