总览
我上家公司做的项目也是互联网项目,经常会遇到性能问题,在提升性能方面除了使用缓存,使用多线程,拆分服务等等 还有一个最常用的方法就是排除掉项目中慢sql。 慢sql对项目运行真是非常大。我们项目就因为有慢sql阻塞了数据库服务器,导致了后面执行的sql无法运行,导致服务器响应超时。 一个页面要刷20秒才能出来。 这对一个互联网项目是无法忍受的。下面就介绍一下常见的几个慢sql
条件字段函数操作
假设有一个交易系统,其中有一个表叫trade表包含了流水号(tradeid)、交易员id(operator)、交易时间(t_modified)等字段。
create table tradelog(
id int(11) NOt NULL,
tradeid varchar(32) default null,
operator int(11) Default NULL,
t_modified datetime default null,
primary key (id),
key tradeid (tradeid),
key t_modified (t_modified)
) engine=innodb default charset=utf8mb4;
假如,表中假了2016年到2018年中的数据,现在要统计所有年份中7月份的交易记录总数。你sql也许会是这样的
select count(1) from tradelog where month(t_modified)=7;
虽然t_modified字段上有索引,但这仍然是一个慢sql,因为如果字段上做了函数计算就不会使用索引了。
下面这个图就是t_modified字段的索引示意图, 方框上面的数字是month()函数对应的值 。方块中的值是索引值 ,B+树数会用所有值,构造一个树结构,是一个二叉数,所有的值会放在最后一层。
因为对字段进行了计算,计算出来的结果,和索引的值是不一致的,而这种不一致会造成,树关索引的有序性被破坏,因为一开始就不能走树状索引。 这就是为什么字段进行函数计算了就无法使用索引的原因了。
在这个例子中,优化器虽然放弃了树状索引,但是优化器可以使用主键索引,和t_modified索引 。优化器会选择一个小的索引,最终使用的是t_modified索引.
使用explain 对sql进行分析
key = t_modified是代表使用这个索引,rows是代表扫描了多少行。extra字段的using index表示的是使用了索引覆盖。(没有回表操作)这里的rows也是Mysql优化器做出的选择,如果行数非常短的话,优化器就不会使用索引了。 也同样会进行全表扫描。
现在我们对sql进行优化
这样就会使用树关索引了。rows数就会大符减少。
隐式类型转换
explain select * from tradelog where tradeid = 100;
大家可以先看看这条sql,tradeid上面是有索引的,但是经过分析这个sql也是走的全表扫描。这是为什么呢?
因为我们在定义字段类型的时候tradeid是varchar类型的,而他的条件是非varchar类型的,里面包含了类型转换,因为不能走索引。
类型转换的规则是什么?
字符串和数字做比较的话,会将字符串转成数字。
为什么有数据类型转换,就需要走全表扫描 ?
下面我们来看sql
select * from tradelog where tradeid= 110717;
对优化器来说这个语句相当于
select * from tradelog where CAST(tradeid As signed int )= 110717
我们之前已经说过了,对字段进行函数操作,会改变树状索引的顺序,优化器会放弃走树搜索功能。
两个表之间的编程不一致
两个表之间的编码不一致同样会导致不能走树索引。这里就不在给予例子,这种情况出现的概率比较少,因为我们在创建表的时候,一般是通过工具,表的编码都是一样的。
总结
对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就放器走树搜索功能。