MySQL性能优化（一）基础优化

本系列文章是在学习慕课网《性能优化之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/