本系列文章是在学习慕课网《性能优化之MySQL优化》视频时及参考别人做的记录整理的笔记。
一、SQL语句优化
使用mysql慢查询日志对有效率问题的SQL进行监控：
//查看慢查询日志是否开启
show variables like 'slow_query_log';
//查看慢查询日志存储位置
show variables like 'slow_query_log_file';
//开启慢查询日志
set global slow_query_log=on;
//指定慢查询日志存储位置
set global show_query_log_file='/var/lib/mysql/homestead-slow.log';
//记录没有使用索引的sql
set global log_queries_not_using_indexes=on;
//记录查询超过1s的sql
set global long_query_time=1;
慢查询日志所包含的内容：
#User@Host:root[root] @localhost[]//执行sql的主机信息
#Query_time:0.0000024 Lock_time:0.00 Rows_sent:0 Rows_esamined:0//sql的执行信息
SET timestamp=1402389324//sql执行时间
select * from store; //sql的内容
MySQL官方慢查询日志分析工具之mysqldumpslow：
用法：
//查看参数列表
mysqldumpslow -h
//分析慢查询日志中前三条比较慢的sql
mysqldumpslow -t 3 /var/lib/mysql/homestead-slow.log | more
//输出样式效果
Count:1 Time:0.00s Lock=0.00s Rows=10.0
root[rppt]@localhost
select * from store
MySQL慢查询日志分析工具之pt-query-digest(结果比mysqldumpslow更详细全面)：
//输出到文件
pt-query-digest slow-log > slow_log.report
//输出到数据表
pt-query-digest slow.log -review
h=127.0.0.1,D=test,p=root,P=3306,u=root,t=query_review
--create-reviewtable
--review-history t=hostname_slow
用法：
//查看参数列表
pt-query-digest --help
//分析慢查询日志中前三条比较慢的sql
pt-query-digest /var/lib/mysql/homestead-slow.log | more
//输出分为三部分
1.显示除了日志的时间范围，以及总的sql数量和不同的sql数量
2.Response Time:响应时间占比 Calls:sql执行次数
3.sql的具体日志
通过慢查询日志发现有问题的SQL,问题如下：
1.查询次数多且每次查询占用时间长的SQL
通常为pt-query-digest分析的前几个查询
2.IO大的SQL（数据库主要瓶颈出现在IO层次）
注意pt-query-digest分析中的Rows examine项
3.未命中索引的SQL
注意pt-query-digest分析中的Rows examine和Rows Send的对比。
通过explain查询和分析SQL的执行计划
explain select customer_id,,first_name,last_name from customer;
max()和count()优化：
//查询最后支付时间--优化max()函数
explain select max(payment_date) from payment;
create index idx_paydate on payment(payment_data);//给payment_date建立索引(覆盖索引)
//在一条SQL中同时查出2006年和2007年电影的数量--优化Count()函数
select count(release_year='2006' or null) as '2006年电影数量'，count(release_year='2007' or null) as '2007年电影数量' from film;
count()说明：
子查询优化：
通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。(distinct去重)
//查询sandra出演的所有影片
explain select title,release_year,LENGTH from film
where film_id in (
select film_id from film_actor where actor_id in (
select actor_id from actor where first_name='sandra'));
group by的优化
//改前 临时表
explain select actor.first_name,actor_last_name,count(*) from sakila.film_actor
inner join sakila.actor USING(actor_id)
group by film_actor.actor_id;
//改后 结合子查询 索引
explain select actor.first_name,actor.last_name,c.cnt from sakila.film_actor
inner join (
select actor_id,count(*) as cnt from sakila.film_actor group by actor_id) as c USING(actor_id);
limit优化
limit常用于分页处理，时常会伴随order by 从句使用，因此大多时候会使用Filesorts这样会造成大量的IO问题。
//文件排序，IO大
explain select film_id,description from sakila.film order by title limit 50,5;
1.优化：使用有索引的列或主键进行order by操作（order by film_id）
2.记录上次返回的主键，在下次查询的时候用主键过滤，避免了数据量大时扫描过多的记录
select film_id,description from sakila.film where film_if>55 and film_id<=60 order by film_id limit 1,5;
页数越大，IO越大
//查询最后支付时间--优化max()函数explainselectmax(payment_date) from payment; createindex idx_paydate on payment(payment_data);//给payment_date建立索引(覆盖索引) //在一条SQL中同时查出2006年和2007年电影的数量--优化Count()函数selectcount(release_year='2006'ornull) as'2006年电影数量'，count(release_year='2007'ornull) as'2007年电影数量'from film; //有关count()函数 https://blog.csdn.net/wendychiang1991/article/details/70909958/