浅析索引失效（一）

先在重复一下，什么是索引？

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

但是在生产中，我们有时候会发现，某一条sql速度慢的出奇，原因可能就是语句中的某些表示，导致了索引失效。

以下针对索引失效的几种看法！欢迎交流！！！

---

一、查询条件中包含or，可能导致索引失效。

新建一个普通表employees,

CREATE TABLE `emp` (`id` int(11) NOT NULL,`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`id`),KEY `idx_emp_no` (`emp_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入少量元素，意思意思 

INSERT INTO `emp` VALUES ('1','10001', '1953-09-02', 'Georgi', 'Facello', 'M', '1986-06-26'); INSERT INTO `emp` VALUES ('2','10002', '1964-06-02', 'Bezalel', 'Simmel', 'F', '1985-11-21'); INSERT INTO `emp` VALUES ('3','10003', '1959-12-03', 'Parto', 'Bamford', 'M', '1986-08-28'); INSERT INTO `emp` VALUES ('4','10004', '1954-05-01', 'Chirstian', 'Koblick', 'M', '1986-12-01'); INSERT INTO `emp` VALUES ('5','10005', '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1989-09-12'); INSERT INTO `emp` VALUES ('6','10006', '1953-04-20', 'Anneke', 'Preusig', 'F', '1989-06-02'); INSERT INTO `emp` VALUES ('7','10007', '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1989-02-10'); INSERT INTO `emp` VALUES ('8','10008', '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1994-09-15'); INSERT INTO `emp` VALUES ('9','10009', '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18'); INSERT INTO `emp` VALUES ('10','10010', '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1989-08-24'); INSERT INTO `emp` VALUES ('11','10011', '1953-11-07', 'Mary', 'Sluis', 'F', '1990-01-22');

1.执行一条sql语句，它是会走索引的，如下图所示：

如果你没有使用过explain这个命令来查看SQL语句的执行计划，这里走：https://georgedage.blog.csdn.net/article/details/103526199

或者，继续看：

乔治大哥

 

2.把or条件+没有索引的age加上，并不会走索引，如图所示

分析&结论：

• 对于or+没有索引的age这种情况，假设它走了emp_no的索引，但是走到gender查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并

• 如果它一开始就走全表扫描，直接一遍扫描就完事。

• mysql是有优化器的，处于效率与成本考虑，遇到or条件，让索引失效，看起来也合情合理嘛。

注意: 如果or条件的列都加了索引，索引可能会走的，大家可以自己试一试。

---

二、如何字段类型是字符串，where时一定用引号括起来，否则索引失效

再创个表： 

CREATE TABLE `emp2` (`id` int(11) NOT NULL,`emp_no` varchar(14) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`id`),KEY `idx_emp_no` (`emp_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后接下来看两种情况：

分析与结论：

为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较。

---

三、like通配符可能导致索引失效。

在emp2表中，当%在前面不走索引，%在后面的话走索引。

乔治大哥

 在emp表中，当%无论前后都是不走索引，这个我觉得还是想我们二中所说的在底层mysql进行了转化，因为emp表中的emp_no是int类型。

乔治大哥

然后给大家一个意想不到的：改为只查索引的字段（覆盖索引），发现还是走索引

结论：

like查询以%开头，会导致索引失效。可以有两种方式优化：

• 使用覆盖索引

• 把%放后面

附: 索引包含所有满足查询需要的数据的索引，称为覆盖索引(Covering Index)。

---

四、联合索引，查询时的条件列不是联合索引中的第一个列，索引失效

表结构：（有一个联合索引 idx_emp_no_age,emp_no在前， age在后）

CREATE TABLE `emp3` (`id` int(11) NOT NULL,`emp_no` int(11) NOT NULL,`age` int(11) NOT NULL,PRIMARY KEY (`id`),KEY `idx_emp_no_age` (`emp_no`,`age`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

在联合索引中，查询条件满足最左匹配原则时，索引才是正常生效的。请看demo：

乔治大哥 乔治大哥 乔治大哥
 

【友情补充】index这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据

分析与结论：

• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

• 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

---

五、在索引列上使用mysql的内置函数，索引失效。

创表：

CREATE TABLE `emp4` (`id` int(11) NOT NULL,`emp_no` int(11) NOT NULL,`logintime` datetime NOT NULL,PRIMARY KEY (`id`),KEY `idx_emp_no` (`emp_no`) USING BTREE,KEY `idx_login_time` (`loginTime`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Demo: 

乔治大哥

虽然loginTime加了索引，但是因为使用了mysql的内置函数Date_ADD()，索引直接GG，如图： 

乔治大哥
 

 

总结

以上是生活随笔为你收集整理的浅析索引失效（一）的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。