SQL 调优之查询调优

最新推荐文章于 2024-06-27 21:02:16 发布

陈晨辰呀

最新推荐文章于 2024-06-27 21:02:16 发布

阅读量1k

点赞数

分类专栏： Java基础文章标签：数据库

本文链接：https://blog.csdn.net/weixin_38004638/article/details/103927219

版权

Java基础专栏收录该内容

132 篇文章 16 订阅

订阅专栏

sql 语句性能分析

1、看 sql 语句执行时间 file

2、看 sql 的执行计划 file

3、查看 sql 的执行中各个环节耗时时间 file

4、查看mysql的执行进程，处理锁表的情况，命令 show PROCESSLIST， state 为LOCKED，说明产生锁表，ID为进程id，直接执行kill ID，就可以停止这个进程； file

MySQL整个查询执行过程：

1、客户端同数据库服务层建立TCP连接。2、客户端向MySQL服务器发送一条查询请求。3、连接线程接收到SQL语句之后，将语句交给SQL语句解析模块进行语法分析和语义分析。4、先看查询缓存中是否有结果，如果有结果可以直接返回给客户端。5、如果查询缓存中没有结果，就需要真的查询数据库引擎层了，于是发给SQL优化器，进行查询的优化，生成相应的执行计划。6、MySQL根据执行计划，调用存储引擎的API来执行查询7、使用存储引擎查询时，先打开表，如果需要的话获取相应的锁。查询缓存页中有没有相应的数据，如果有则可以直接返回，如果没有就要从磁盘上去读取。8、当在磁盘中找到相应的数据之后，则会加载到缓存中来，从而使得后面的查询更加高效，由于内存有限，多采用变通的LRU表来管理缓存页，保证缓存的都是经常访问的数据。9、最后，获取数据后返回给客户端，关闭连接，释放连接线程。

Procedure Analyse优化表结构

PROCEDURE ANALYSE() ，在优化表结构时可以辅助参考分析语句。通过分析select查询结果对现有的表的每一列给出优化的建议。

利用此语句，MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。【只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。】

PROCEDURE ANALYSE的语法如下：

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])max_elements:指定每列非重复值的最大值，当超过这个值的时候，MySQL不会推荐enum类型。（默认值256）max_memory （默认值8192）analyse()为每列找出所有非重复值所采用的最大内存大小。执行返回中的Optimal_fieldtype列是mysql建议采用的列。

样例程序mysql> DESC user_account; ----------- ------------------ ------ ----- --------- ---------------- | Field | Type | Null | Key | Default | Extra | ----------- ------------------ ------ ----- --------- ---------------- | USERID | int(10) unsigned | NO | PRI | NULL | auto_increment || USERNAME | varchar(10) | NO | | NULL | || PASSSWORD | varchar(30) | NO | | NULL | || GROUPNAME | varchar(10) | YES | | NULL | | ----------- ------------------ ------ ----- --------- ---------------- rows in set (0.00 sec)

mysql> select * from user_account PROCEDURE ANALYSE(1)G;* 1. row *Fieldname: ibatis.useraccount.USERIDMin_value: 1Max_value: 103Min_length: 1Max_length: 3Emptiesorzeros: 0Nulls: 0Avgvalueoravglength: 51.7500Std: 50.2562Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL* 2. row *Fieldname: ibatis.useraccount.USERNAMEMin_value: dfsaMax_value: LMEADORS.........................................................

从第一行输出我们可以看到analyze分析ibatis.user_account.USERID列最小值1，最大值103，最小长度1，最大长度3，字符串或0的个数为0；值为空的字段数为0；平均每个字段的值为51.7500并给出了该字段的优化建议：建议将该字段的数据类型改成TINYINT(3) UNSIGNED NOT NULL。

查询语句出现sending data耗时解决

执行SHOW PROFILES 查出QueryID后在执行 show profile for query QueryID ，或者是show processlist，查看查询所耗时资源得知查询到语句耗时主要集中在 sending data上 file

解决步骤：1、查询资料需要开启查询缓存，执行命令查看开启情况 show variables like '%querycache%'，No则是未开启，改配置文件设置 querycache_type=YES后重启 file

2、查看buffer 执行 show variables like 'innodbbufferpool%'，可以看出innodbbufferpool_size只有8M大小这里设置成3G，SET GLOBAL innodbbufferpool_size = 3221225472，然后重启mysql（参考：https://www.cnblogs.com/wanbin/p/9530833.html） file

3、查看表索引，show index from table_name发现cardinality的值为1，表的cardinality（可以翻译为“散列程度”），它表示某个索引对应的列包含多少个不同的值。如果cardinality大大少于数据的实际散列程度，那么索引就基本失效了。

使用命令：analyze table table_name修复索引，如果修复失败，查看是否因为新添加的字段设置了相同的默认值 file

总结从上面这个例子我们可以看出analyze能根据目前表中的数据情况给出优化建议。当数据库在生产环境运行一定时间以后，开发或是DBA能参考analyze的分析结果来对表结构做出一定的优化。

where条件排序字段以及limit使用索引

背景

我们先来看2条sql

第一条：select * from acct_trans_log WHERE acct_id = 1000000000009000757 order by create_time desc limit 0,10

第二条：select * from acct_trans_log WHERE acct_id = 1000000000009003061 order by create_time desc limit 0,10

表的索引及数据总情况：索引：acctid，createtime分别是单列索引，数据库总数据为500w

通过acct_id过滤出来的结果集在1w条左右查询结果：第一条要5.018s，第二条0.016s为什么会是这样的结果呢？第一，acctid和createtime都有索引，不应该出现5s查询时间这么慢啊

那么先来看执行计划第一条sql执行计划： file 第二条执行计划： file

仔细观察会发现，索引只使用了idxcreatetime，没有用到idxacctid

这能解释第一条sql很慢，因为where查询未用到索引，那么第二条为什么这么快？看起来匪夷所思，其实搞清楚mysql查询的原理之后，其实很简单

我们来看这2条sql查询，都用到了where order by limit当有limit存在时，查询的顺序就有可能发生变化，这时并不是从数据库中先通过where过滤再排序再limit因为如果这样的话，从500万数据中通过where过滤就不会是5s了。

此时的执行顺序是，先根据idxcreatetime索引树，从最右侧叶子节点，反序取出n条，然后逐条去跟where条件匹配若匹配上，则得出一条数据，直至取满10条为止，为什么第二条sql要快，因为运气好，刚好时间倒序的前几条就全部满足了。

搞清楚原理之后，我们了解了为什么第一条慢，第二条快的原因，但是问题又来了为什么mysql不用idxacctid索引，这是一个问题，因为这样的话，我们的建立的索引基本失效了，在此类sql下查询效率将会是相当低

因为通过acctid过滤出来的结果集比较大，有上万条，mysql认为按时间排序如果不用索引，将会是filesort,这样会很慢，而又不能2个索引都用上，所以选择了idxcreate_time。

为什么mysql只用一个索引

这里为什么不能2个索引都用上，可能很多人也不知道为什么，其实道理很简单，每个索引在数据库中都是一个索引树，其数据节点存储了指向实际

数据的指针，如果用一个索引来查询，其原理就是从索引树上去检索，并获得这些指针，然后去取出数据，试想，如果你通过一个索引，得到过滤后的指针，这时，你的另一个条件索引如果再过滤一遍，将得到2组指针的集合，如果这时候取交集，未必就很快，因为如果每个集合都很大的话，取交集的时候，等于扫描2个集合，效率会很低，所以没法用2个索引。当然有时候mysql会考虑临时建立一个联合索引，将2个索引联合起来用，但是并不是每种情况都能奏效，同样的道理，用一个索引检索出结果集之后，排序时，也无法用上另一个索引了。

实际上用索引idxacctid大多数情况还是要比用索引idxcreatetime要快，我们举个例子：select * from acct_trans_log force index(idx_acct_id) WHERE acct_id = 1000000000009000757 order by create_time desc limit 0,10耗时：0.057s可以看出改情况用idxacctid索引是比较快的，那么是不是这样就可以了呢，排序未用上索引，始终是有隐患的。

联合索引，让两个字段都用上索引

我们来看下一条sql:select * from acct_trans_log force index(idx_acct_id) WHERE acct_id = 3095 order by create_time desc limit 0,10耗时: 1.999s执行计划： file

该sql通过acct_id过滤出来的结果集有100万条，因此排序将会耗时较高，所幸这里只是取出前10条最大的然后排序查询概况，我们发现时间基本消耗在排序上，其实这是内存排序，对内存消耗是很高的。 file

解决办法就是建立联合索引：alter table accttranslog add index idxacctidcreatetime(acctid,createtime)然后执行sql:select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10耗时: 0.016s file

联合索引使用的原理

但是为什么能解决这个问题呢，下面我们再看一个sql：select * from acct_trans_log force index(idx_acct_id_create_time) WHERE acct_id in(3095,1000000000009000757) order by create_time desc limit 0,10耗时：1.391s索引还是用idxacctidcreatetime，时间居然慢下来了。看执行计划，排序用到了filesort，也就是说，排序未用到索引。

那么我们还是来看看，索引排序的原理，我们先来看一个sql:select * from acct_trans_log ORDER BY create_time limit 0,100耗时：0.029s执行计划为： file 这里执行的步骤是，先从索引树中，按时间升序取出前100条，因为索引是排好序的，直接左序遍历即可了因此，这里mysql并没有做排序动作，如果想降序，则右序遍历索引树，取出100条即可，查询固然快，

那么联合索引的时候，是怎样的呢？select * from acct_trans_log WHERE acct_id = 3095 order by create_time desc limit 0,10使用组合索引：idxacctidcreatetime这个时候，因为acct_id是联合索引的前缀，因此可以很快实行检索，

如果sql是select * from acct_trans_log WHERE acct_id = 3095出来的数据是按如下逻辑排序的3095 time13095 time23095 time3

默认是升序的，也就是说，次sql相当于select * from acct_trans_log WHERE acct_id = 3095 order by create_time他们是等效的。

如果我们把条件换成order by create_time desc limit 0,10呢这时候，应该从idxacctidcreatetime树右边叶子节点倒序遍历，取出前10条即可因为数据的前缀都是3095，后缀是时间升序。那么我们倒序遍历出的数据，刚好满足order by create_time desc因此也无需排序。

那么语句：select * from acct_trans_log force index(idx_acct_id_create_time) WHERE acct_id in(3095,1000000000009000757) order by create_time desc limit 0,10为什么排序无法用索引呢？我们先分析下索引的排序规则已知：id1

索引出来的默认排序是这样的，id是有序的，时间是无序的，因为有2个id，优先按id排序，时间就是乱的了，这样排序将会用filesort，这就是慢的原因，也是排序没有用到索引的原因。

查询计划使用以及使用说明table：显示这一行数据是关于哪张表的type：显示使用了何种类型，从最好到最差的连接类型为const,eq_ref,ref,range,index,allpossible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引key：实际使用的索引，如果为null，则没有使用索引。key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好ref：显示索引的哪一列被使用了，如果可能的话，是一个常数rows：mysql认为必须检查的用来返回请求数据的行数

参考：1、Navicat常用功能：https://blog.csdn.net/littlexiaoshuishui/article/details/828840942、MySQL查询过程剖析：https://blog.csdn.net/z_ryan/article/details/822627613、分析诊断工具之Procedure Analyse：https://www.cnblogs.com/duanxz/p/3968639.html4、sending data耗时解决：https://www.cnblogs.com/yaoxing92/p/11058420.html、https://www.cnblogs.com/rwxwsblog/p/5684213.html5、where条件排序字段以及limit使用索引：https://www.cnblogs.com/tangyanbo/p/6378741.html