本节介绍索引优化。索引是数据库优化最常用也是最重要的手段之一,较为重要。

数据准备

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`)
); 
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);

避免索引失效

全值匹配

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

前面已经创建了索引idx_seller_name_sta_addr

现在使用索引。

全值匹配情况下,索引的每个参数都对得上,执行效率高。

-- 全值匹配,和字段匹配成功即可,和字段顺序无关,and情况下是并列的
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';

结果

最左前缀法则

-- 最左前缀法则
 -- 如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。
explain select * from tb_seller where name='小米科技'; -- 403
explain select * from tb_seller where name='小米科技' and status='1'; -- 410
explain select * from tb_seller where  status='1' and name='小米科技'; -- 410

-- 违法最左前缀法则 , 索引失效:
explain select * from tb_seller where status='1'; -- nulll
 
-- 如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:
explain select * from tb_seller where name='小米科技'  and address='北京市'; -- 403

其他匹配原则

1、下面的这个句子name和status用上了索引,address不能用上索引:

-- 范围查询右边的列,不能使用索引 。 
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市’;

 

2、索引列不要带有计算在里面,有运算索引将失效:

-- 不要在索引列上进行运算操作, 索引将失效。 
explain select * from tb_seller where substring(name,3,2)='科技’ 

 

3、字符串必须要加单引号,不然会失效。可以查看一下变量的类型,是整型、字符型或是其他的。

-- 字符串不加单引号,造成索引失效。 
explain select * from tb_seller where name='小米科技' and status = 1 ;

 

4、尽量使用覆盖索引。就是尽量查索引有的,对应起来。从索引树中就可以查询到所有数据。

-- 4、尽量使用覆盖索引,避免select *
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技'  and address='北京市';  -- 效率低
 
-- 从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
explain select name,status,address from tb_seller where name='小米科技'  and address='北京市';  -- 效率高
-- 如果查询列,超出索引列,也会降低性能。
explain select name,status,address,password from tb_seller where name='小米科技'  and address='北京市';  -- 效率低

 

5、 用or分割开的条件, 那么涉及的索引都不会被用到。

下面的几个句子都没用上索引。

-- 用or分割开的条件, 那么涉及的索引都不会被用到。
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'; 
explain select * from tb_seller where name='黑马程序员' or address = '西安市';  
explain select * from tb_seller where name='黑马程序员' or status = '1';   

 

6、以%开头 的Like模糊查询,索引失效。

-- 以%开头的Like模糊查询,索引失效。
explain select * from tb_seller where name like '科技%'; -- 用索引
explain select * from tb_seller where name like '%科技'; -- 不用索引
explain select * from tb_seller where name like '%科技%';-- 不用索引
-- 弥补不足,不用*,使用索引列
explain select name from tb_seller where name like '%科技%';

 

7、如果MySQL评估使用索引比全表更慢,则不使用索引。

-- 如果MySQL评估使用索引比全表更慢,则不使用索引。
  -- 这种情况是由数据本身的特点来决定的,优化器自动优化
create index index_address on tb_seller(address);
 
explain select * from tb_seller where address = '北京市'; -- 没有使用索引
explain select * from tb_seller where address = '西安市'; -- 使用索引,西安市只有一个,索引快

 

8、 is NULL , is NOT NULL 有时有效,有时索引失效。

create index index_address on tb_seller(nickname);
explain select * from tb_seller where nickname is NULL;  -- 索引有效
explain select * from tb_seller where nickname is not NULL; -- 无效

-- 主要看空的多还是空的少,和第七条类似

 

9、in走索引,not in索引失效。但是需要注意主键索引,in和not in都有效

-- in索引,not没索引
-- 普通索引
create index index_nickname on tb_seller(nickname);
explain select * from tb_seller where nickname in ('阿里小店','百度小店');   -- key是203
explain select * from tb_seller where nickname not in ('阿里小店','百度小店');   -- key是Null

-- 主键索引(变量sellerid是主键)
explain select * from tb_seller where sellerid in ('alibaba','baidu');   -- key是402
explain select * from tb_seller where sellerid not in ('alibaba','baidu');   -- key是402

 

10、单列索引和复合索引,尽量使用符合索引

同时定义了多个单列索引,查询时一起使用,也是单个有效。

CREATE index idx_san on tb_seller(name,status,address);
/*
    等价于:
    NAME
    NAME + STATUS
    NAME + STATUS + address
*/
-- 如果一张表中有多个索引,即使where中都是用了,也只有一个生效,具体哪个生效由优化器决定

声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



博主个人公众号
版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/mysql36.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!

最后修改:2022 年 06 月 15 日
如果觉得我的文章对你有用,请随意赞赏