MySQL 查询优化

Thresh

慢查询定位

开启慢查询日志
查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：

SHOW VARIABLES LIKE 'slow_query_log%'

通过如下命令开启慢查询日志：

SET global slow_query_log = ON; 
SET global slow_query_log_file = 'OAK-slow.log'; 
SET global log_queries_not_using_indexes = ON; 
SET long_query_time = 10;
(long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中。)
(log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效)

查看慢查询日志

文本方式查看
直接使用文本编辑器打开slow.log日志即可。

time：日志记录的时间
User@Host：执行的用户及主机
Query_time：执行的时间
Lock_time：锁表时间
Rows_sent：发送给请求方的记录数，结果数量
Rows_examined：语句扫描的记录条数
SET timestamp：语句执行的时间点
select....：执行的具体的SQL语句

使用mysqldumpslow查看

MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志内容。

在 MySQL bin目录下执行下面命令可以查看该使用格式。

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]  -- 后跟参数以及log文件的绝对地址;

  -s            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

例子:

mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log # 取出使用最多的10条慢查询

mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查询时间最慢的3条慢查询

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log # 得到按照时间排序的前10条里面含有左连接的查询语句

 mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log # 按照扫描行数最多的

除了使用mysqldumpslow工具，也可以使用第三方分析工具，比如pt-query-digest、
mysqlsla等。https://zhuanlan.zhihu.com/p/...

慢查询优化

索引和慢查询

• 如何判断是否为慢查询？

MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。

• 如何判断是否应用了索引？

SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain 命令分析查看，检查结果中的 key 值，是否为NULL。

• 应用了索引是否一定快？

select * from user where id>0;
虽然使用了索引，但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树，进行了全表扫描，此时索引就失去了意义。
而像 select * from user where id = 2; 这样的语句，才是我们平时说的使用了索引。它表示的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。

查询是否使用索引，只是表示一个SQL语句的执行过程；而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。

我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过滤性好，执行速度才会快。

提高索引过滤性
假如有一个5000万记录的用户表，通过sex='男'索引过滤后，还需要定位3000万，SQL执行速度也不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。

慢查询原因总结

全表扫描：explain分析type属性all
全索引扫描：explain分析type属性index
索引过滤性不好：靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销：尽量少用select *，使用覆盖索引

分页查询优化

一般性分页
一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：

SELECT * FROM 表名 LIMIT rows OFFSET size

如果偏移量固定，size对执行时间有什么影响?

在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。

如果查询偏移量变化，返回记录数固定对执行时间有什么影响？

在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）

分页优化方案
第一步：利用覆盖索引优化

select * from user limit 10000,100; 
select id from user limit 10000,100;

第二步：利用子查询优化

select * from user limit 10000,100; 
select * from user where id>= (select id from user limit 10000,1) limit 100;

原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化

MySQL 优化方案:

a.不要用*号(数据库内部会进行一次将*号转化为字段)
b.where多条件查询时,结果集条件少的放前面
c.in(先查内表),内表小时用;exists(先查外表),内表大时用
d.进行少用like,可以用全文检索,如果要用的话避免打两边通配符,要有效的使用检索
e.增加冗余字段,减少联表查询
f.合理利用检索(不适合写,适合读)
h.读写分离
i.更换硬件,用硬盘阵列,ssd硬盘
g.数据量大时,分库分表
.........等等