mysql索引失效场景

索引失效除了一些常规的，比如使用了某些函数，如：not null 、 or 等，还有一个是跟数据量有关系的。之前在网上看博客有的博主有提到，当数据量达到百分之多少的时候好像会导致索引失效。今天就来验证一下这个结论是否正确。

一.创建测试表

CREATE TABLE `order_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `orderId` varchar(36) DEFAULT NULL COMMENT '订单id',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `name` varchar(36) DEFAULT NULL COMMENT '订单名称',
  `createBy` varchar(36) DEFAULT NULL COMMENT '创建人',
  PRIMARY KEY (`id`),
  UNIQUE KEY `orderId` (`orderId`),
  KEY `createTime` (`createTime`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

给创建时间建立普通索引，给订单id建立唯一索引。

二.通过存储引擎批量插入数据

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(IN `add_pro` int)
BEGIN
 #Routine body goes here...
declare i int default 0;
set i=0;

while i<200 do
 
insert into order_test (orderId,`name`,createBy,createTime) VALUES ( replace(uuid(),'-',''),CONCAT('订单',i) ,'张三','2021-04-13 14:49:26');
 
set i=i+1;
end while;

END

三. 具体执行情况

数据量多少才会导致不走索引？

现在假设数据库里有850条数据，日期小于 ‘2021-04-06 14:49:26’ 的数据有50条，当执行

 EXPLAIN select * from order_test  where createTime <= '2021-04-06 14:49:26'

50/850 相当于只有5%的数据时候使用了索引并且是range级别的索引。

网上传言当数据量达到查询数据的20%就会导致放弃走索引而走全表扫描，这里修改数据，保证查询的数据是200，总数是1000，刚好达到20%的情况

此时的执行计划如图所示：

这时候查询数据达到了20% 但是已经是ALL，并没有走索引

经过细微调整当略低于20%的时候并不会造成影响，之后调整到数据量大约为16%的时候则走了range索引。具体是否使用索引应该是由innodb的优化器决定的.

什么是优化器？

在索引建立之后，一条语句可能会命中多个索引，这时，索引的选择，就会交由优化器来选择合适的索引。
优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。

这里应该是优化器底层进行判断走索引的效率已经不如全表扫描了而导致不走索引。

当然这里演示的是 select * 的情况，如果是只查询这个createTime字段的话，即使数据量比较大，比如说是70%，依旧会使用索引，这里比较清楚的知道因为没有带上其他字段所以肯定是使用索引效率更高：

这里可能有人会有疑问了，那能否带上其他字段呢？生产环境中一般很少只查询一个字段的。当带上主键id进行查询的时候并不会影响索引，这里我推测是主键不需要进行回表并不影响效率；当带上name字段进行查询则没办法走索引，所以具体生产环境还是要根据具体的sql语句进行分析，对字段或者索引进行适当调整，当无用字段过多的时候也会导致不走索引的情况。

当然优化器选择也不一定总是对的，当需要强制使用索引的时候可以加上force index强制走你需要的索引。具体使用如下图所示：

这样就依旧能让索引生效了。

mysql底层优化器具体的执行逻辑因为是c++编写的，并且作为后端人员也不需要了解这么深入，本文的一些推论也是个人附带猜测的观点，希望能起到“抛砖引玉”的作用，文中若有错误之处，还请大家不吝指正。主要还是扩展一下索引失效的场景，而不要仅仅局限于八股文里那些烂大街的知识点里。

转载：https://www.xdx97.com/article/834479030387343360

一.创建测试表

二.通过存储引擎批量插入数据

三. 具体执行情况

公告