表字段加了索引但是查询依然很慢

比眉伴天荒 2022-04-23 01:44 223阅读 0赞

之前遇到一个问题（因为最近又遇到了，所以记录下来），表字段加了索引但是查询依然很慢，大概的情况如下

## **问题描述：** ##

有个表T\_ORDER（匿名），字段若干，其中有个TX\_TIME交易时间，MERCHANT\_NO商户号都已经添加了索引，表数据量较大5000W+

SQL大致如下（实际SQL比下面的复杂一点点，会做 LEFT JOIN 其他表，但是不多）：

SELECT * FROM T_ORDER 
    WHERE 
    CUSTOMER_NO = #{customerNo} 
    AND TX_TIME BETWEEN #{startTxTime} AND #{endTxTime}

查询一天某商户的数据经常超时（好几秒，具体的忘了）。

## 问题排查： ##

1、首先通过DEBUG日志将打印出来的SQL与参数拼写成完整SQL在数据库执行，发现很快（几十毫秒），并不是SQL本身问题（其实SQL本身也有一定的问题，比如表数据太大，又进行LEFT JOIN ，但因其不是这次分析重点，就此忽略）。

2、mybatis的\#会将SQL参数化，而在步骤1中的SQL是具体参数的，非参数化的， 所以怀疑参数化的SQL与非参数化的SQL执行计划不一样，于是找了DBA去帮忙查两SQL的执行计划（因为本人不擅长数据库方面的东西，并且身边有资深DBA，所以就直接请教他们了），果然不一样。

3、于是将mybatis中该SQL的\#全部替换为了$，部署发现问题解决了，之前部分SQL慢的问题未出现了。

## 问题分析： ##

1、首先说明一下，mybatis中的\#与$差别，\#\{\}的参数是参数化的，也就是在SQL中是?替代的；$\{\}的参数是拼接的，也就是在SQL中直接拼接上了参数的值，所以在替换成$后，SQL就与直接在数据库执行的一致了。

2、为啥会变快呢？在DBA的协助下，发现参数化的SQL在数据库的编译会存在一个问题，就是编译SQL的时候，会依据当前全表的量基于一定的算法生成执行计划，然后将执行计划缓存起来；而拼接好的SQL则会基于当前拼接的多个参数实际筛选的量基于算法生成执行计划，然后缓存起来。也就是说第二种的执行计划是动态的，每次有可能不一样，都是最优的。（具体的执行计划生成算法不是很清楚，也不是这里的重点）

举个例子：
    
    T_ORDER中，A商户每天可产生2500W数据，而剩下2500W条交易分别数据2500W个商户的，每个商户一条。
    
    若参数化，那么执行计划可能是，执行计划1，优先走时间索引，再走商户索引（或者是执行计划2优先走商户索引，再走时间索引，只能是两者的其中一个，然后缓存下来，以后都走这个缓存的）。
    
    若执行计划1，那么查询A商户一天的交易就比较快，查询剩余某个商户就比较慢
    
    若执行计划2，那么查询A商户就比较慢，查询剩余某个商户的就比较快
    
    总会有不好的地方
    
    若拼接的话，那么情况就会变了
    
    查询A商户一天交易的时候，执行计划就会是 优先走时间索引，再走商户索引
    
    查询剩余某个商户一天交易的时候，执行计划就会是 优先走商户索引，再走时间索引

## 结论： ##

1、mybatis中，高频简单的SQL，参数化\#会更好（因为编译耗时占用SQL执行耗时比例很大，几十倍吧），大表复杂SQL可以尝试使用$拼接，会快很多（编译耗时占用SQL执行耗时的比例很小，每次编译也就无所谓了，但可以使执行计划更准确，但是小心SQL注入）

2、数据库上有个绑定变量窥视的设置，打开后用\#就跟用$的执行计划一致了，这块参考了别人的文章（[https://blog.csdn.net/enmotech/article/details/79987663\#commentBox][https_blog.csdn.net_enmotech_article_details_79987663_commentBox]）

3、参数隐式转化也影响索引生效，具体可以看这篇，执行计划的东西可以学习一下（[https://blog.csdn.net/enmotech/article/details/79987663\#commentBox][https_blog.csdn.net_enmotech_article_details_79987663_commentBox]）

[https_blog.csdn.net_enmotech_article_details_79987663_commentBox]: https://blog.csdn.net/enmotech/article/details/79987663#commentBox