优化sql语句步骤:
1.发现问题
2.分析执行计划
3.优化索引
4.改写sql
(再达不到优化效果的话 进行数据库分库分表)
1.发现问题途径
1.用户上报性能问题
2.慢查询日志发现问题SQL
3.数据库实时监控长时间运行的SQL
2.设置MYSQL
set global slow_query_log = on/off （慢查询开关）
set global slow_query_log_file = /地址 （慢查询地址）
set global long_query_time = xx.xx秒 （超过XX秒会被记录）
set global log_queries_not_using_indexes = on/off （记录没有使用索引的慢查询）
3.分析慢查询日志
1.mysqldumpslow (mysql自带)
2.pt-query-digest
4.安装percona-toolkit-3.0.13
下载软件包:
wget https://www.percona.com/downloads/percona-toolkit/3.0.13/binary/redhat/7/x86_64/percona-toolkit-3.0.13-1.el7.x86_64.rpm
安装依赖包:
yum install -y perl-DBD-MySQL.x86_64 perl-DBI.x86 perl-Time-HiRes.x86_64 perl-IO-Socket-SSL.noarch perl-TermReadKey.x86_64 perl-Digest-MD5
安装
rpm -ivh percona-tookot-3.0.13-1.el7.x86_64.rpm
输入pt 按 tab 获取所有软件
5.慢查询日志设置 与 查询日志
查看各参数的值
show variables like 'slow_query_log ';
show variables like 'slow_query_log_file ';
show variables like 'long_query_time ';
show variables like 'log_queries_not_using_indexes ';
设置各参数的值
set global slow_query_log = on/off （慢查询开关）
set global slow_query_log_file = /地址 （慢查询地址）
set global long_query_time = xx.xx秒 （超过XX秒会被记录）
set global log_queries_not_using_indexes = on/off （记录没有使用索引的慢查询）
查看日志
mysql> show variables like 'slow_query_log_file';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
1 row in set (0.01 sec)
more /var/lib/mysql/localhost-slow.log
# Time: 2019-06-07T08:48:26.661099Z
# User@Host: root[root] @ localhost [] Id: 12
# Query_time: 0.001453 Lock_time: 0.000257 Rows_sent: 1 Rows_examined: 0
SET timestamp=1559897306;
EXPLAIN select * from student;
其中
query_time(执行sql时间)
lock_time(锁的时间)
rows_sent(查询返回的行数)
Rows_examined(扫描的行数)
6.使用工具分析日志
1.使用mysqldumpslow 分析
mysqldumpslow /var/lib/mysql/localhost-slow.log
Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
show variables like 'S'
其中
count(执行次数)
time(执行时间)
lock(锁的时间)
rows(行数)
2.使用pt-query-digest 分析
pt-query-digest /var/lib/mysql/localhost-slow.log
Query 1: 0.00 QPS, 0.00x concurrency, ID 0x751417D45B8E80EE5CBA2034458B5BC9 at byte 1475
Scores: V/M = 0.00
Time range: 2019-06-07T08:23:55 to 2019-06-07T08:48:11
Attribute pct total min max avg 95% stddev median
============ === ======= ======= ======= ======= ======= ======= =======
Count 15 4
Exec time 29 24ms 992us 11ms 6ms 11ms 4ms 9ms
Lock time 41 15ms 423us 8ms 4ms 8ms 3ms 7ms
Rows sent 52 20 5 5 5 5 0 5
Rows examine 4 104 26 26 26 26 0 26
Query size 7 56 14 14 14 14 0 14
String:
Databases data
Hosts localhost
Users root
#Query_time distribution
1us
10us
100us
1ms ################################################################
10ms #####################
100ms
1s
10s+
show databases\G
6.实时监控长时间运行的SQL
select id,user,host,db,command,time,state,info
from information_schema.PROCESSLIST
WHERE TIME>=60
(sql执行时间大于60秒的SQL)
7.分析执行计划
为什么分析执行计划
1.SQL如何访问表中的数据
2.了解SQL如何使用表中的索引
3.了解SQL所使用的查询类型
获取执行计划 EXPLAIN select * from XXX
8.分析执行计划
id列
为数字或者为no
id列相同时由上到下执行
id不同时,由大到小
select_type列
子查询
simple 不包含子查询或者 UNION查询
primary 查询中如果包含任何子查询,最外层的标记为primary
subquery 子查询
dependent subquery （依赖关系 子查询）
UNION查询
union union查询的第二条或者之后的
dependent union （依赖关系 子查询 ,union 语句作为子查询,union查询的第二条或者之后的）
union result 产生的结果集
derived 出现在from子句中的子查询
table列
输出表的名称
<union M,N> 由ID为M，N union后产生的结果集
<derived N>/<subquery N> 由ID为N的查询产生的结果集
partitions列
查分区表
type 列 （性能从高到低）
system (性能高)
const连接类型,当查询表只有一行时
const
表中值有且只有一行匹配,或者利用where 查询某个常量的值,主键或唯一索引查询是效率最高的方式
eq_ref(常出现在join查询 )
唯一索引或主键索引查找,对每个索引键,表中只有一条记录与之匹配
ref
非唯一索引查找,返回匹配某个单独值的所有行
ref_or_null
类似于ref 增加了null值的查询
index_merge
索引合并
range
索引范围素描(between , > , <)
index
full index scan 全索引扫描
all (性能最低)
全表扫描
key列
(possible_keys)可能会被使用到的索引
(keys)会被使用到的索引
(key_len) 实际使用索引的最大长度 (字节)
ref列
列出哪些列被用于索引查找
rows列
预估扫描行数
filtered列
返回行数与扫描行数的百分比(越高 就 性能越高)
extra列
distinct 找到第一个值后立刻停止找同值的动作
not exists 使用not exists优化 使用不存在于某个条件的查询
using filesort 常见使用order by 或者 group by 查找
using index 使用了覆盖索引(直接通过索引获取数据,不访问表)
using temporary使用了临时表
using where 使用了where
select tables optimized away 操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）。
网友评论