mysql数据库sql优化---索引失效问题

总览

我上家公司做的项目也是互联网项目，经常会遇到性能问题，在提升性能方面除了使用缓存，使用多线程，拆分服务等等 还有一个最常用的方法就是排除掉项目中慢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

我们之前已经说过了，对字段进行函数操作，会改变树状索引的顺序，优化器会放弃走树搜索功能。

两个表之间的编程不一致

两个表之间的编码不一致同样会导致不能走树索引。这里就不在给予例子，这种情况出现的概率比较少，因为我们在创建表的时候，一般是通过工具，表的编码都是一样的。

总结

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就放器走树搜索功能。