or导致索引失效的解决方法_SQL优化之索引失效

尽量全值匹配

建表语句：

CREATE TABLE `t_logs` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`action` varchar(100) DEFAULT NULL,

`data` varchar(2000) DEFAULT NULL,

`author_id` int(10) DEFAULT NULL,

`ip` varchar(20) DEFAULT NULL,

`created` int(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `actionipcreated` (`action`,`created`,`ip`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=938 DEFAULT CHARSET=utf8;

其中有三个字段是联合索引，在查询的时候尽量把where条件跟索引全量匹配，例如：

EXPLAIN select * from t_logs where action = '访客访问' and created = '1528366547' and ip = '58.135.82.57';

执行计划：

+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+

| 1 | SIMPLE | t_logs | NULL | ref | actionipcreated | actionipcreated | 371 | const,const,const | 1 | 100.00 | NULL |

+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------------------+------+----------+-------+

如果条件不允许使用索引列的全局匹配，那么最好使用覆盖索引，比如：

EXPLAIN select action,created,ip from t_logs where action = '访客访问';

执行计划：

+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+

| 1 | SIMPLE | t_logs | NULL | ref | actionipcreated | actionipcreated | 303 | const | 195 | 100.00 | Using index |

+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

建表语句：

CREATE TABLE `t_logs` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`action` varchar(100) DEFAULT NULL,

`data` varchar(2000) DEFAULT NULL,

`author_id` int(10) DEFAULT NULL,

`ip` varchar(20) DEFAULT NULL,

`created` int(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `actionipcreated` (`action`,`created`,`ip`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=938 DEFAULT CHARSET=utf8;

可见，一个复合索引，顺序是action，created，ip，当在使用这三个索引作为条件查询的时候可以打乱顺序，但不能跳过。

举例：

正确的利用了索引的查询语句：

EXPLAIN select * from t_logs where action = '访客访问' and created = '1528366547' and ip = '58.135.82.57';

未全部利用索引进行的查询：

EXPLAIN select * from t_logs where action = '访客访问' and ip = '58.135.82.57';

上面的sql语句仅有action列上的索引起作用了，ip列上的索引是没有作用的，因为中间的created列没有作为查询条件。

如果既想改变顺序，又能利用索引，那还是使用覆盖索引的方法。最佳左前缀法则就是：带头大哥不能死、中间兄弟不能断。

补充：例如有一个联合索引 idx_nameAgeGender 假如在查询的时候三个字段打乱顺序，是可以用到索引的，因为mysql有自己的优化器。

不在索引列上做任何操作

不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描。

举例，其中data列是索引列：

EXPLAIN select * from t_logs where left(DATA,4) = 'bcwn'；

执行计划：

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | t_logs | NULL | ALL | NULL | NULL | NULL | NULL | 197 | 100.00 | Using where |

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

范围条件放最后

存储引擎不能使用索引中范围条件右边的列。会导致最后的索引失效。

例子：

EXPLAIN select * from t_logs where action = '访客访问' and created < '1528366547' and ip = '58.135.82.57';

导致了ip这列的索引失效了。

覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *的出现。

不等于要甚用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。解决办法就是使用覆盖索引。例子就不举了，很简单。

Null/Not Null有影响

建表语句：

CREATE TABLE `t_logs` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`action` varchar(100) DEFAULT NULL,

`data` varchar(100) DEFAULT NULL,

`author_id` int(10) DEFAULT NULL,

`ip` varchar(20) DEFAULT NULL,

`created` int(10) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `actionipcreated` (`action`,`created`,`ip`) USING BTREE,

KEY `data` (`data`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=938 DEFAULT CHARSET=utf8;

这个情况有点多，具体取决于该字段是否默认为NULL，可以去试试，遇到这种SQL需要根据实际情况去优化，索引并不一定会失效。如果说碰到IS NULL或IS NOT NULL一定会导致索引失效肯定是错了。

Like查询

like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作，但不以'%'开头不会导致索引失效。

字符类型加引号

假如一个列是char或者varchar类型，那么当它作为条件的时候必须加上单引号，否则会导致这一列的索引失效。就不举例子了，也很简单。