mysql定位和优化慢查询的方案

21 篇文章 0 订阅
17 篇文章 0 订阅

源于蚂蚁课堂的学习,点击这里查看(老余很给力)   

是否遇到过这样的场景:因为一条sql查询耗时太长,降低用户体验;或者面对sql结合业务,不知道怎么写才能更高效地输出结果?今天帝都的雁为大家分享一下如何在千万级的数据量下输出高效的sql语句。

(PS:需要有mysql执行流程原理的理论基础,可参考我的另一篇博文《mysql查询和修改的底层原理》)

一、数据结构

Mysql之所以可以快速地吞吐,很大程度上依赖于其底层的数据结构--B+树。

首先我们先了解一下几种索引可选择的数据结构。

1、Hash

数据按照hash算法进行散列分布,通过hash算法,可以快速定位到具体的数据存放地址。但由于其散列分布带来的致命缺陷(范围查询不友好),实际开发中,很少有表使用这种索引。

 

 

2、二叉树

二叉树存放数据时,需要先指定根节点,然后进行比值,将索引值存放在左子树(比父节点小)或右子树(比父节点大)上。二叉树缺点很明显,如果根节点固定的话,那么数据很有可能失去平衡。比如自增ID如果按照二叉树去存放索引,那么本质上就是一条链表,时间复杂度也变为o(n)。

 

3、平衡二叉树

在二叉树的基础上,增加了旋转功能来使整棵树达到平衡(左右子树高度差的绝对值不超过2)。HashMap8的数据结构红黑树也是一种特殊的平衡二叉树。这种数据结构看似可以维护平衡,但也仅仅是比链表好一点。由于其每个节点只能存放一个索引值,在数据量庞大的情况下,树的高度将会是一个无法直视的数字。想要找到树底层的数据,可能要进行的时间复杂度为o(log(n)),而且每次查找都是一次磁盘的IO,这种频繁IO必然牺牲性能。

 

4、B树

为了解决降低树的高度,B树开始支持单个节点存放多个索引值(16KB)。这样一来,树的高度就会几何式降低。B树的缺点在于,节点上不仅存放索引值,也同时存放这索引对应的数据(或者数据的地址,这要依照于索引是否是聚簇索引)。试想,如果索引对应的数据比较大,那么单个节点存放的索引个数就会变小,使得树的高度,不在那么理想化。而且InnoDB存储引擎以页的方式对数据IO,也就是说,一次IO会把这一个节点上的数据都加载出来,可是实际需要的可能只是其中几个索引值,那么其他的索引值对应的数据也加载到内存,是不是增加了内存的消耗?

 

5、B+树

B+树在B树的基础上进行优化。单个节点上可以存放多个索引值,但是非叶子节点,是不存放数据的。只要叶子节点存放索引值和数据,并且叶子节点从左至右排序。这么一来,我们可以进行数值的预估。

一个节点(页)存放的数值大小为16KB,一行数据大小为1KB,即叶子节点可以存放16行数据。假设我们的主键为bigint,占用8个字节;每个索引值需要有个指针指向下级地址,占用6个字节。那么一个节点最多可以存放的索引值大约为16*1024/(8+6) = 1170,那么三层树可以存放的索引个数为1170*1170*16 大约2000W左右。

可想而知,树的高度为3时,已经支持千万级别查询,即查询第三层,只需要3次IO。

而且B+树对范围查询特别友好,只要找到索引在叶子节点位置,按照数轴的方式取值即可。所以mysql采用B+树作为索引的数据结构来存放数据。

 

二、索引名词

主键索引:以主键排序,叶子节点存放行数据(INNODB)或行数据的地址(MYISAM)。

二级索引(非主键索引):以索引的列排序,叶子节点存放主键的id。

联合索引:按照索引创建时多个列的顺序进行排序,叶子节点存放主键的id。

聚簇索引:叶子节点存放具体的行数据。

非聚簇索引(辅助索引):以聚簇索引为基础建立的索引,叶子节点存放主键id。

对于联合索引或者索引列为字符的索引,其排序规则可以参考我们常用的新华字典。试想,如果我们根据拼音查找一个字,一定是按字母从左到右去依次查询,否则只能全表查询。

Mysql推荐表中一定要有一个主键,而且是自动增长。否则维护数据的顺序需要花费额外的时间。如果表中没有主键,会指定表中数据唯一的列作为聚簇索引,要是不存在此列,则默认生成rowid,以rowid去进行聚簇索引的存放。

 

三、定位慢查询

想要优化sql,首先得得到它。我们可以通过执行设置mysql开启慢查询日志。

#查询慢日志相关信息

SHOW VARIABLES LIKE '%query%'

# 默认是off关闭的,使用时,需要改为on 打开

slow_query_log

# 记录的是慢日志的记录文件     

slow_query_log_file

# 默认是10S,每次执行的sql达到这个时长,就会被记录

long_query_time 

# 查看慢查询状态

SHOW STATUS LIKE '%slow_queries%'  

# 修改慢查询时间1s

SET GLOBAL long_query_time = 2 

# 开启慢查询

SET GLOBAL slow_query_log ='ON'

注意:修改慢查询时间后,记得需要重新连接才可以生效

如何定位sql慢查询

SHOW VARIABLES LIKE '%query%'     

SHOW VARIABLES LIKE '%dir%';

可能每个企业对慢查询的定义不同,默认为10s。我们以1s为例,sql执行时长超过1s就会被记录在慢查询日志文件中。我们可以通过日志得到这些sql,进行下面的调优工作。

 

四、使用工具分析sql

mysql自带多种sql解析器。

1、explain

我们可以通过explain对慢查询输出执行计划。

比如:EXPLAIN SELECT a.* FROM USER a,USER b WHERE a.id=b.id;

 

下面对这些列作解释:

a、id

查询的序列号。值越大,越先执行;值相同,则自上而下执行;值为null,最后执行。

b、select_type

查询的类型。

SIMPLE:简单的查询,不使用UNION或子查询等;

PRIMARY:子查询中最外层的查询,查询中若包含任何复杂的字部分,最外层的SELECT被标记为PRIMARY;

UNION:UNION中第二个或后面的SELECT语句;

DEPENDENT UNION:UNION中第二个或后面的SELECT语句,取决于外面的查询;

UNION RESULT:UNION的结果,union语句中第二个select开始后面所有select

SUBQUERY:子查询中的第一个SELECT,结果不依赖于外部查询;

DEPENDENT SUBQUERY:子查询中的第一个SELECT,依赖于外部查询;

DERIVED:派生表的SELECT, FROM子句的子查询;

UNCACHEABLE SUBQUERY:一个子查询的结果不能被缓存,必须重新评估外链接的第一行。

c、table

输出结果集的表。

d、partitions

匹配的分区。

e、type

表的查询或连接类型。

NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。

例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表。
ALL:遍历全表匹配目标。

INDEX:遍历索引树匹配目标。

RANGE:给检索指定范围的行,使用一个索引进行查询。

REF:哪些列或常量被用于查找索引列上的值。
EQ_REF:对比REF,使用的索引是唯一索引。

CONST:当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。
SYSTEM:system是const类型的特例,当查询的表只有一行的情况下,使用system。

效率为SYSTEM>CONST>EQ_REF>REF>RANGE>INDEX>ALL,按照阿里开发手册来说,sql最低要保证在range级别,ref更好一些。

f、possible_keys

查询过程中可能用到的索引。

g、key

查询过程中实际用到的索引。

h、key_len

查询过程中发挥作用的索引的实际长度。

字符串

char(n):n字节长度

varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2

数值类型

tinyint:1字节

smallint:2字节

int:4字节

bigint:8字节  

时间类型 

date:3字节

timestamp:4字节

datetime:8字节

如果字段允许为 NULL,需要1字节记录是否为 NULL。

索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。

i、ref

列与索引的比较。

j、rows

扫描出的行数(估算结果)。

k、filtered

按表条件过滤的百分比。

l、Extra

执行情况的描述与说明。

Using index:查询的列在索引中全部存在,则不需要回表查询,可以直接返回结果。

Using where:查询条件中不包含索引,mysql对存储引擎层返回的数据所做的过滤。当然该过滤可能会回表,也可能不会回表,取决于查询字段是否被索引覆盖。

Using index Condition:查询条件使用了索引,但是查询的列不全在索引中,需要回表查询。

Using where;Using index:查询使用了索引,且查询的列都在索引中,无需回表查询。

Using filesort:查询使用了内存排序;即排序方式没有按照索引的规则排序,需要将结果集放入内存中再次排序,效率低。

 

Filesort分为单路排序算法和双路排序算法。

Mysql有一个参数max_length_for_sort_data,默认值为1024kb。当排序的列的总大小<max_length_for_sort_data时,采用单路排序,否则采用双路排序。

单路排序:将结果集放入内存中进行排序,然后将结果输出。可以避免回表。

双路排序:只将结果集的排序列以及主键放入内存进行排序,然后将结果根据主键去主键树上进行回表查询,再将查询后的数据输出。

 

 

2、OPTIMIZER_TRACE

Sql执行追踪器。

需要手动开启:

SET optimizer_trace = 'enabled=on', end_markers_in_json = ON ;

当我们执行查询时,需要同时将这条sql的日志追踪一起执行输出。

例如:
SELECT * FROM USER;
SELECT * FROM information_schema.`OPTIMIZER_TRACE`;

然后找到结果集2,将其中的trace内容读取即可。其中包含着详细的sql运行过程,以及索引的选择。

 

五、优化准则

优化原则很简单,围绕这索引的存储方式去进行优化,尽量使用索引,避免全表扫描。

select

查询的列最好使用联合索引中的列,这样可以避免回表查询的低效率。

where

最好使用精确查询,即使用常量或者固定值去访问索引列;

使用模糊查询时,禁止使用前模糊,避免索引失效导致的全表扫描。

针对联合索引,采用最左前缀原则,使索引尽可能地细粒度扫描。

对于is null或者is not null这种全表扫描来说,可以使用具体的值去替代。比如指定’’为默认值,我们直接按照索引查询 = ’’的数据即可。

分页

limit offset,n分页时,mysql底层是查询offset条记录后,再向后查询n条记录进行返回。所以分页的页码越大,分页性能衰减越明细。

我们可以换一种写法。

比如:select * from user limit 10000000,10;

调整:select * from user where id > 10000000 limit 10;

或select a.* from user a,(select id from user limit 10000000,10) b where a.id = b.id;

第一种方案是通过索引找到对应符合条件的记录位置,然后在索引树的叶子节点向后取10位即可,但需要主键严格安装自增长的方式去排序。

第二种方案先是通过索引查询出这些数据的主键(不包含具体数据,可以减少IO次数),然后通过主键再去聚簇索引上找到对应的10条数据返回。

对于单表数据量超过500W,则需要考虑使用mycat或shardingJDBC进行分库分表。

排序

严格按照索引分布方式进行排序。即如果存在联合索引,需要按照联合索引的字段顺序以最左前缀的方式排序。避免出现filesort情况。

count

Count(1)/count(id)/count(*)/count(name)这些查询本质上没有区别(name为索引列)。

在mysql5.7前,都是使用非主键索引去进行统计;5.7开始,除count(name)外,其它3个都是以主键索引进行统计,count(name)按照name的索引进行统计。

主键索引统计是弱于非主键索引的。因为非主键索引的叶子节点存放的是主键的id,而主键索引的叶子节点存放的是具体的行数据。而mysql以页(16kb)的方式取磁盘IO数据,如果行数据特别大时,主键索引的统计要进行的IO次数将远大于普通索引。

联表

无论是left join还是right join,都需要遵循驱动表的原理。即先查询小表,用小表去关联大表。

使用in子查询时,要保证in中为小表;使用exist子查询时,要保证exist外是小表。

 

 

欢迎大家和帝都的雁积极互动,头脑交流会比个人埋头苦学更有效!共勉!

公众号:帝都的雁

 

  • 2
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值