12、MySQL 提升 – 性能优化2-索引优化-MySQL 提升-itgogogo.cn

一、创建测试数据

 mysql版本5.7.30
创建表结构
create table consumer_info
(
    id           int auto_increment
        primary key,
    user_name    varchar(32) null,
    age          int         null,
    account_name varchar(32) null,
    email        varchar(64) null,
    details_id   int         null
);
插入数据
insert into consumer_info(id, user_name, age, account_name, email, details_id) values (null,'lily',20,'lily_admin','lily@163.com',1);
insert into consumer_info(id, user_name, age, account_name, email, details_id) values (null,'zhangsan',59,'zhangsan_admin','zhangsan@163.com',2);
insert into consumer_info(id, user_name, age, account_name, email, details_id) values (null,'xiaobai',17,'xiaobai_admin','xiaobai@qq.com',1);
insert into consumer_info(id, user_name, age, account_name, email, details_id) values (null,'huahua',21,'huahua_admin','huahua@163.com',1);

创建复合索引（user_name,age,account_name）：索引名叫index_uaa

 ALTER TABLE consumer_info ADD INDEX index_uaa(user_name,age,account_name);

查看创建好的索引：

 show index from consumer_info;

二、测试索引

1.复合索引跳过首字段导致索引失效

测试一：复合索引的三个字段（user_name,age,account_name）都使用

 explain select * from consumer_info where user_name='xiaobai' and age=17 and account_name='xiaobai_admin';

测试二：仅使用age和account_name字段做检索

 explain select * from consumer_info where  age=17 and account_name='xiaobai_admin';

测试三：跳过复合索引的中间字段，使用（user_name,和account_name）做检索

 explain select * from consumer_info where user_name='xiaobai'  and account_name='xiaobai_admin';

测试四：在复合索引中使用范围查找(age字段使用范围条件)

 explain select * from consumer_info where user_name='xiaobai' and age>10  and account_name='xiaobai_admin';

结论：
对于复合索引，要遵循最左前缀法则，即查询从索引的最左列开始，并且不要跳过索引中的列。
在复合索引中，范围条件后边的列索引会失效。

2.在索引字段上函数、计算或者类型转换导致索引失效

测试一：使用user_name正常检索

 explain select * from consumer_info where user_name = 'xiaobai';

测试二：在user_name列使用了函数

 explain select * from consumer_info where left(user_name,7) = 'xiaobai';

测试三：使用了类型转换

 explain select * from consumer_info where user_name = 100;

结论
在索引字段上函数、计算或者类型转换会导致索引失效。

3.使用 select * 与select 索引列对比

测试一：使用select *

 explain select * from consumer_info where user_name='xiaobai' and age=17  and account_name='xiaobai_admin';

测试二：使用select 索引列

 explain select user_name,age,account_name from consumer_info where user_name='xiaobai' and age=17  and account_name='xiaobai_admin';

结论：尽量使用覆盖索引，查询列和索引列保持一致。

4.使用不等于(!= 或 <>)

测试一：使用=

 explain select * from consumer_info where user_name='xiaobai'；

测试二：使用!=

 explain select * from consumer_info where user_name!='xiaobai';

结论
尽量不要使用!=或者<>，因为这样会使索引失效。

5.使用is null 或 is not null

测试一：使用is null

 explain select * from consumer_info where user_name is null;

测试二：使用is not null

 explain select * from consumer_info where user_name is not null;

结论
使用is not null 的时候会导致索引失效。

6.使用like以通配符开头的检索

测试一：使用like：xxx%

 explain select * from consumer_info where user_name like 'xiaobai%';

测试二：使用like：%xxx

 explain select * from consumer_info where user_name like 'xiaobai%';

测试三：使用like %xxx，并且覆盖索引查询

 explain select user_name,account_name from consumer_info where user_name like '%xiaobai%';

结论：
like以通配符%开头的条件会让索引失效。
like以通配符%开头的条件，使用覆盖索引可以解决索引失效的问题。

7.使用or来查询

测试：

 explain select * from consumer_info where user_name='xiaobai' or age=17 ;

结论
在where条件中使用了or，会导致索引失效，从而全表扫描。

三、总结

对于复合索引，要遵循最左前缀法则，即查询从索引的最左列开始，并且不要跳过索引中的列。

1、在复合索引中，范围条件后边的列索引会失效；
2、在索引字段上函数、计算或者类型转换会导致索引失效；
3、尽量使用覆盖索引，查询列和索引列保持一致；
4、使用!=或者<>会使索引失效；
5、使用isnotnull会导致索引失效；
6、 like以通配符%开头的条件会让索引失效；
7、 like以通配符%开头的条件，使用覆盖索引可以解决索引失效的问题；
8、在where条件中使用了or，会导致索引失效；

12、MySQL 提升 - 性能优化2-索引优化