SQL语句性能差异分析

案例一、条件字段的函数操作

SQL查询语句中，如果在where条件限制字段时使用了函数，那么可能会导致该字段的索引失效，使用不上B+树的快速定位功能。

需求&场景：统计所有记录中七月的交易记录总数。其中t_modified为datetime类型，并且添加了索引。

查询方式①

select count(*) from tradelog where month(t_modified)=7;

查询方式②

select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

对于方式一，因为没有给出限定年份，搜索时不能走B+树的快速定位。但并不代表不走这个索引，对比发现索引t_modified叶子节点数据比主键索引叶子节点数据更小，所以最后还是走的全索引扫描。

对于方式二，将函数操作改为范围查询，此时可以使用到B+树的快速定位功能。

另外优化器在判断解析where字段时，对于表达式而言等号左边只含有索引字段才能使用到索引。具体来说 where id+1=10000和where id=10000-1只有后者会使用到索引。(字段出现等号左边与右边没有影响，a=b和b=a等价)

案例二、隐式类型转换

SQL数据类型隐式转换规则：在表达式中会自动将字符串类型转化成数字，再进行判断和比较。

需求&场景：tradeid字段为varchar(32)类型，存在索引。

select * from tradelog where tradeid=110717;

这条语句在查询时相当于给tradeid字段添加一个”转换函数“，从而使tradeid的索引B+树失效，不能快速定位。

案例三、隐式字符编码转换

SQL字符编码隐式转换规则：在联表查询时，不同表之间字段的表达式进行比较时，会将编码格式为utf8的字段值转换成utf8mb4编码格式。因为utf8mb4时utf8的超集。

需求&场景：d表中编码格式为utf8，而l表编码格式为utf8mb4。

select d.* from l, d where d.tradeid=l.tradeid and l.id=2;

拿到l表的字段值在d表匹配查询时，d表的tradeid字段索引树需要转化为utf8mb4格式，因此索引失效。

两种解决方案：

• 将d表的字段字符集也改成utf8mb4
• 在SQL语句直接修改d.tradeid的字符集编码格式

案例四、InnoDB引擎预处理与server层后处理

需求&场景：b字段在InnoDB引擎定义了长度为VARCHAR(10)，数据库中存在十万条b='1234567890'的数据。

select * from t where b='1234567890abcd';

这条查询语句虽然返回空，但是执行过程比较慢：

• innoDb引擎执行语句前，因为b字段只定义了十个字节，会进行字符截断，只会拿‘1234567890’到索引树中进行行记录匹配。
• b索引树拿到id后，总共会进行十万次回表。
• 每次回表查出整行记录后，会到==server层==进行一致性判断，发现b='1234567890'不等于'1234567890abcd'
• 返回结果都为空

案例五、select * from t where id=1执行慢

执行以下单行查询语句发现速度慢，其中id为主键字段。

select * from t where id=1;

可能的原因、排查过程、解决方案如下：

1.等待MDL表锁

在会话A中因为执行了DDL语句，拿到MDL写锁，只要会话A不提交事务那么MDL锁就不会释放，从而导致会话B中的SQL语句被阻塞。

💡解决方案：找到具体是哪一个线程持有MDL写锁，然后kill掉。具体来说可以执行以下SQL语句查到对应的线程，其中表sys.schema_table_lock_waits是MySQL开机自动配置维护的一张系统表。

select blocking_pid from sys.schema_table_lock_waits;

2.等待flush表锁

flush操作：首先需要等待表上的所有操作执行完成，然后“关闭”表，最后执行flush。显然flush操作可以被前面的线程阻塞，并且也会堵住阻塞其它后面的线程。

显然在下面场景中，sessionA因为一直对表t操作，导致sessionB要一直等待sessionA的查询结束。这样sessionC中的查询语句就会被flush操作阻塞住了。

💡排查方案：从系统的processlist表中查询某个线程状态，可以发现状态字段为waiting for table flush

select * from information_schema.processlist where id=x;

当前，可以直接执行如下语句查看所有的线程id的状态，找到对应flush语句的线程id后直接kill。

show processlist;

3.等待行锁

在RR隔离级别下，普通的查询语句因为是快照读，并不会被行锁阻塞。若select想要当前读，执行以下SQL语句：

select * from t where id=1 lock in share mode;

显然既然是当前读，那么就可以被其它更新语句的行锁阻塞，sessionB只有等待sessionA提交事务释放行锁后，才能执行select语句。

💡解决方案：直接show processlist查看每个线程的状态位并不能解决问题，因此关键在于如何找到哪个线程id阻塞了sessionB的当前读？MySQL5.7版本可以借助sys.innodb_lock_waits表查到：

select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

​ KILL QUERY 4表示暂停事务中的查询语句，并不会提交事务释放锁。只有执行KILL 4才能断开连接，回滚事务释放锁。

4.多更新版本号导致的查询慢

sessionA开启事务后，sessionB执行更新语句，id=1这行记录的版本号急剧增加。

• 对于普通select查询，因为事务当前读，只能读到update语句之前的版本号，因此行记录“当前版本号”需要根据undo log计算并向前回滚一万个版本号，而这个回滚计算的代价在当前情况下是比较大的。
• 而对于加了行锁的当前读的select查询，发现行记录的当前版本号就是当前读所需要的最新的数据，因此不需要任何回滚计算。故此时当前读的查询时间和代价更小。