MySql – 索引优化实例

MySql – 索引优化实例

1. 索引优化实例

---------------------------------------------------------------------------------------------------------------------------

1. 索引优化实例

1.1 数据表结构

1.2 前导模糊查询不能命中索引

name列存在普通索引，当执行

EXPLAIN SELECT * FROM test WHERE name LIKE '%s%';

返回的结果为：

如果将SQL改为

EXPLAIN SELECT * FROM test WHERE name LIKE 's%';

执行计划结果为

1.3 数据类型出现隐式转换的时候不会命中索引

name列的类型为varchar类型，执行以下SQL

EXPLAIN SELECT * FROM test WHERE name=1;

如果将SQL改为以下SQL

EXPLAIN SELECT * FROM test WHERE name='1';

1.4 复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左匹配原则），不会命中符合索引

为gender、age、status三列创建复合索引：ALTER TABLE test ADD INDEX ind_gender_age_status (gender,age,status);

EXPLAIN SELECT * FROM test WHERE gender = 0 AND age = 23;

可以看到是走了索引的，如果改成如下SQL

EXPLAIN SELECT * FROM test WHERE age = 23 AND gender = 0;

 

结果和上图一致，说明最左匹配原则和查询条件的顺序无关，但如果换成了如下SQL

EXPLAIN SELECT * FROM test WHERE status = 4;

因为查询条件缺失gender字段数据，就和最左匹配原则矛盾，造成索引失效

1.5 用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到

EXPLAIN SELECT * FROM test WHERE status = 4 OR remark = 'DSA'

因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。

1.6 负向条件查询不能使用索引，可以优化为in查询

负向条件有：!=、<>、not in、not exists、not like等。

EXPLAIN SELECT * FROM test WHERE status !=1 AND status != 2;

负向条件不能命中缓存，可以优化为in查询，但是前提是返回的数据量占比要少，否则有可能走全表扫描

1.7 利用覆盖索引进行查询，避免回表

被查询的列，数据能从索引中取得，即“被查询列要被所建的索引覆盖”，这样能加速查询速度。

EXPLAIN SELECT status FROM test where status=1;

但如果查询结果集变成select  *

EXPLAIN SELECT * FROM test where status=1;

当查询其他列时，就需要回表查询，这也是为什么要避免select  * 的原因