MySQL索引优化与使用策略-优快云博客

本文链接：https://blog.youkuaiyun.com/pingping_wang/article/details/110054322

本文介绍了如何创建和使用MySQL索引，包括全值匹配、最左前缀法则、范围查询、运算操作对索引的影响，以及如何避免索引失效。强调了覆盖索引的重要性，并提供了针对OR条件、LIKE模糊查询、NULL和IN操作符的优化建议。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

准备环境

create table tb_seller (
sellerid varchar (100),
name varchar (100),
nickname varchar (50),
password varchar (60),
status varchar (1),
address varchar (100),
createtime datetime,
primary key(sellerid)
)engine=innodb default charset=utf8mb4;

insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘alibaba’,‘阿里巴巴’,‘阿里小店’,‘e10adc3949ba59abbe56e057f20f883e’,‘1’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘baidu’,‘百度科技有限公司’,‘百度小店’,‘e10adc3949ba59abbe56e057f20f883e’,‘1’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘huawei’,‘华为科技有限公司’,‘华为小店’,‘e10adc3949ba59abbe56e057f20f883e’,‘0’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘itcast’,‘传智播客教育科技有限公司’,‘传智播客’,‘e10adc3949ba59abbe56e057f20f883e’,‘1’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘itheima’,‘黑马程序员’,‘黑马程序员’,‘e10adc3949ba59abbe56e057f20f883e’,‘0’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘luoji’,‘罗技科技有限公司’,‘罗技小店’,‘e10adc3949ba59abbe56e057f20f883e’,‘1’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘oppo’,‘OPPO科技有限公司’,‘OPPO官方旗舰店’,‘e10adc3949ba59abbe56e057f20f883e’,‘0’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘ourpalm’,‘掌趣科技股份有限公司’,‘掌趣小店’,‘e10adc3949ba59abbe56e057f20f883e’,‘1’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘qiandu’,‘千度科技’,‘千度小店’,‘e10adc3949ba59abbe56e057f20f883e’,‘2’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘sina’,‘新浪科技有限公司’,‘新浪官方旗舰店’,‘e10adc3949ba59abbe56e057f20f883e’,‘1’,‘北京市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘xiaomi’,‘小米科技’,‘小米官方旗舰店’,‘e10adc3949ba59abbe56e057f20f883e’,‘1’,‘西安市’,‘2088-01-01 12:00:00’);
insert into tb_seller (sellerid, name, nickname, password, status, address, createtime) values(‘yijia’,‘宜家家居’,‘宜家家居旗舰店’,‘e10adc3949ba59abbe56e057f20f883e’,‘1’,‘北京市’,‘2088-01-01 12:00:00’);

创建索引：
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

避免索引失效

全值匹配，对索引列中所有的列都指定具体指。该情况下，索引生效，执行效率高
eg:
select * from tb_seller where name=‘小米科技’ and status=‘1’ and address=‘北京市’\G;
最左前缀法则
如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始，并且不跳过索引中的列。
eg:
select * from tb_seller where name=‘小米科技’ ;
select * from tb_seller where name=‘小米科技’ and status=‘1’;
select * from tb_seller where name=‘小米科技’ and status=‘1’ and address=‘北京市’;
匹配最左前缀法则，走索引：

select * from tb_seller where status=‘1’ ;
select * from tb_seller where and status=‘1’ and address=‘北京市’;
违反最左前缀法则，索引失效
在这里插入图片描述
select * from tb_seller where name=‘小米科技’ and address=‘北京市’;
符合最左法则，但是出现跳跃都一列，只有最左列索引生效：

3. 范围查询右边的列，不能使用索引
select * from tb_seller where name=‘小米科技’ and status>‘1’ and address=‘北京市’;

在这里插入图片描述
根据前面的两个字段name，status查询是走索引的，但是最后一个条件adress没有用到索引

不要在索引列上进行运算操作，索引将失效
select * from tb_seller where substring(name,3,2) =‘科技’ ;
字符串不加单引号，造成索引失效
select * from tb_seller where name=‘小米科技’ and status = 0 ;

由于，在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。
尽量使用覆盖索引，避免 select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少 select *
在这里插入图片描述
如果查询列，超出索引列，也会降低性能。
eg:
select status,address,password from tb_seller where name=‘小米科技’ and status=‘1’ and address=‘北京市’;
(password不在索引列)

TIP :

using index ：使用覆盖索引的时候就会出现

using where：在查找使用索引的情况下，需要回表去查询所需的数据

using index condition：查找使用了索引，但是需要回表查询数据

using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
eg： name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的
select * from tb_seller where name=‘黑马程序员’ or createtime = ‘2088-01-01 12:00:00’\G;
以%开头的like模糊查询，索引失效
如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效。

解决方案：
通过覆盖索引来解决
select sellerid from tb_seller where name like ‘%科技%’ ;
select sellerid,name from tb_seller where name like ‘%科技%’ ;
select sellerid ,name ,status ,adress from tb_seller where name like ‘%科技%’ ;
如果MySQL评估使用索引比全表扫描更慢，则不适用索引
is NULL ， is NOT NULL 有时索引失效。
in 走索引， not in 索引失效。

12.单列索引和复合索引。
尽量使用复合索引，而少使用单列索引。
创建复合索引
create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引：
name
name + status
name + status + address

创建单列索引
create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。