MySQL性能优化

image

MySQL慢查询日志的开启方式

查询是否开启了慢查询日志

show variables like 'slow_query_log';

设置慢查询日志文件位置

set global slow_query_log_file='c:/mysql-slow.log';

设置是否需要把没有使用到索引的sql 记录到日志中

set global log_queries_not_using_indexes=on;

设置日志的查询时间

set global long_query_time=1;

开启慢查询日志

set global slow_query_log=on;   

存储格式

# Time: 2017-02-08T02:59:28.039421Z       
# User@Host: root[root] @ localhost [::1]  Id:    10     
# Query_time: 0.001997  Lock_time: 0.001997 Rows_sent: 2  Rows_examined: 2
SET timestamp=1486522768;
select * from store limit 10;

(1) Time: 执行时间
(2) User@Host: 执行sql的主机信息
(3) Query_time: sql的执行信息,Lock_time: 锁定时间, Rows_sent: 发送(结果)行数, Rows_examined:扫描的行数
(4) timestamp: 执行时间
(5) select * from aaa; : 查询语句内容

MySQL慢查日志分析工具之mysqldumpslow

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

--verbose verbose
--debug debug
--help write this text to standard output

-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for -slow.log filename (can be wildcard),
default is '', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time

1. -s,排序，c,t,l,r以及ac,at,al,ar分别是按照query次数，时间，lock时间，返回记录排序。加a就是倒序。
2. -t,top n，跟上数字就是算出top多少条
3. -g，跟正则表达式。

返回数据的格式：
Count: 23 Time=505.55s (11627s) Lock=0.00s (0s) Rows=30740.8 (707039), username[password]@[10.194.172.41]
SELECT DISTINCT u.name,o.full_name FROM pub_user u,pub_user_org uo,pub_org o WHERE u.user_id=uo.user_id
AND uo.org_id=o.org_id and u.del_flag=N and uo.del_flag=N
and u.account not like 'S' and u.account not like 'S' group by u.user_id

Count: 4（执行了多少次）
Time=375.01s（每次执行的时间） (1500s)（一共执行了多少时间）
Lock=0.00s (0s)（等待锁的时间）
Rows=10200.3（每次返回的记录数） (40801)（总共返回的记录数）username[password]@[10.194.172.41]

MySQL慢查日志分析工具之pt-query-digest

SQL语句优化

如何通过慢查日志发现有问题

• 查询次数多且每次查询占用时间长的SQL
• IO大的SQL（主要看分析出的 Rows examine）
• 未命中索引的SQL（通过对比Row examine 和Rows Send）

通过explain查询和分析SQL执行计划

explain返回各列的含义

• table : 显示这一行的数据是关于哪张表的
• type : 重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const（主键或唯一索引）、eq_reg（主键或唯一索引的范围）、ref（基于索引的连接查找）、range（基于索引的范围查找）、index（对索引的扫描） 和 ALL（表扫描）
• possible_keys : 显示可能应用在这张表中的索引，如果为空，没有可能的索引。
• key : 实际使用的索引。如果为NULL，则没有使用索引
• key_len : 使用索引的长度。在不损失精确度的情况下，长度越短越好
• ref : 显示索引的哪一列被使用了，如果可能的话，是个常数
• rows : MySQL认为必须检查的用来返回请求数据的行数 （表扫描的行数）
• extra : 主要有两个：using filesort（看到这个就需要优化，MySQL需要进行额外的步骤来发现如何对返回的行排序） using temporary（看到这个也需要优化，Mysql需要创建一个临时表来存储结果）

Count()和Max()的优化

image

在没有优化前，这个SQL语句采用的方式是全表扫描，效率比较低，IO操作大
优化： 创建索引
mysql> create index idx_paydate on payment(payment_date);
结果：

image

表示不用查询任何表，就可以得到需要的数据。因为索引是顺序排列的，通过索引就可以找到最大的日期，建立的这种索引叫做覆盖索引

子查询的优化
通常情况下，需要把子查询优化为join的查询，但在优化的时候需要注意关联建是否有一对多的关系，要注意重复数据（使用 DISTINCT 去重）。

explain
select
t1.title,t1.release_year,t1.length
from film t1 WHERE t1.film_id in(
SELECT film_id from film_actor where actor_id in (
select actor_id from actor where first_name='sandra'
)
)

explain
select
t1.title,t1.release_year,t1.length,t3.first_name
from film t1
INNER JOIN film_actor t2 on t1.film_id = t2.film_id
INNER JOIN actor t3 on t2.actor_id = t3.actor_id and t3.first_name='sandra'

group by的优化

EXPLAIN
SELECT
actor.first_name, actor.last_name, COUNT(*)
FROM film_actor
INNER JOIN actor USING(actor_id)
GROUP BY film_actor.actor_id;

image

从执行计划中可以看出使用到了 临时表和文件排序，需要优化

explain
select
actor.first_name, actor.last_name, c.cnt
from actor
INNER JOIN (
select actor_id,count(*) as cnt from film_actor group by actor_id
) as c using(actor_id);

Limit查询的优化

常用于分页处理，时常会伴随order by 从句使用，因此大多时候会使用filesorts 这样造成大量的IO问题
eg:

EXPLAIN SELECT film_id, description FROM film ORDER BY title LIMIT 50,5;

https://dn-shimo-image.qbox.me/uWJkQC9fKe8kkxmy/image.png!thumbnail

采用了表扫描以及文件排序，需要优化

优化步骤1：使用有索引的列或主键进行Order by操作

EXPLAIN SELECT film_id, description FROM film ORDER BY film_id LIMIT 50,5;

image

EXPLAIN SELECT film_id, description FROM film ORDER BY film_id LIMIT 1000,5;

image

越往后翻页，扫描的行数就越多（rows）,会越来越慢，需要优化
优化步骤2：记录上次返回的主键，在下次查询的时候使用主键过滤（这种方式通常要求主键是连续的，如果不连续可以能出现不足 5 行的情况）

EXPLAIN SELECT film_id, description FROM film where film_id > 55 and film_id <=60 ORDER BY film_id LIMIT 1,5;

索引优化

如何选择合适的列建立索引

image

如何判断离散程度？

image

索引优化SQL的方法

image
image

使用工具来检查重复及冗余索引

image image