我的mysql版本是mysql5.6,今天纠结于一个问题,结果后来发现是mysql5.6版本这个ICP特性导致的,这里把问题分享一下:
1首先看下我的表结构,有5个字段,其中ID为主键
2然后看下我的索引结构,ID为主键,所以是聚簇索引,AddDate为二级索引
这里简单说下我理解的聚簇索引,聚簇索引放到我上面的表就是主键ID在索引里面按照顺序排列,并且存储了每个ID对应的表中所有字段的数据,在聚簇索引下,二级索引Adddate则是Adddate在索引里面按照时间顺序排列,并且存储了每个AddDate对应的主键ID,所以查询会根据二级索引找到对应的主键ID,再从主键ID的聚簇索引里面查找行数据,这样理论上当然比较快,因为都用到了上面两个索引
3下面展示我的第一个查询语句
explain SELECT * FROM bairong_ErrorLog WHERE ID IN
( SELECT ID FROM bairong_ErrorLog WHERE ADDDATE<=DATE_SUB(NOW(),INTERVAL 2 DAY))
上面的语句大概意思是先查找两天前的主键ID,然后再根据ID去获取对应的数据,我在后面加上了explain看下执行计划
可以看到,它正是按照我上面所说的,首先用到了二级索引adddate查看主键ID,然后通过这个主键ID去查找主键ID聚簇索引里面的所有字段的数据
4下面展示我的第二个查询语句
EXPLAIN SELECT * FROM bairong_ErrorLog WHERE ADDDATE<=DATE_SUB(NOW(),INTERVAL 2 DAY)
这个查询和上面查询的区别是只用到了二级索引adddate,按理来说二级索引只保存了Addate和主键ID的信息,如果要select *这样查询所有字段的信息,只有回表进行全表扫描了,这样explain里面的rows是不是就有很多行了?下面直接上图
可以看到,rows只有18行,而且也走了Adddate索引,并没有出现全表扫描的信息,后面的extra是using index condition,那么为什么会这样呢?
5后来,我在网上查了using index condition,就引出了mysql5.6 ICP的这个特性,谈下我对ICP的理解,mysql是分成server层和存储层,然后server层的作用一般是执行命令,并且通过where进行条件筛选,存储层就是收到server层的指令后,找到表中符合索引的数据。但是ICP的作用是将where的处理放到了存储层,这样在存储层就可以对表进行where筛选,再返回到server层,就是满足条件的数据了,这样server层就不需要再花时间在where上面了,因此减少了回表查询的时间和磁盘I/O。
那么放到我上面的查询过程,就是在存储层那里通过数据表找到了二级索引Adddate所有字段的数据(注意,我认为这里这里应该是要回表进行全表扫描的),然后使用了WHERE ADDDATE<=DATE_SUB(NOW(),INTERVAL 2 DAY)这个条件进行了数据的筛选,剩下18条数据,最后把这些符合条件的数据给server,所以explain显示出来的是server得到的数据。