SQL优化之索引失效

/ SQL优化 / 0 条评论 / 420人围观

尽量全值匹配

建表语句:

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) = '博茨瓦纳';

执行计划:

+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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;

未完,,,

Like查询

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

字符类型加引号

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