关于mysql:MySQL-查询优化

48次阅读

共计 3244 个字符，预计需要花费 9 分钟才能阅读完成。

开启慢查问日志
查看 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. 数据量大时, 分库分表
......... 等等

正文完