尽量全值匹配

建表语句：

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类型，那么当它作为条件的时候必须加上单引号，否则会导致这一列的索引失效。就不举例子了，也很简单。

SQL优化之索引失效