Mysql索引使用

x33g5p2x  于2021-03-14 发布在 Mysql  
字(5.0k)|赞(0)|评价(0)|浏览(245)

1.准备环境

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=utf8mb; 

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);
#创建联合索引name status address 

2.避免索引失效

1). 全值匹配 ,对索引中所有列都指定具体值。

mysql> explain select * from tb_seller where name='' and status='1' and address=''\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_seller
   partitions: NULL
         type: ref
possible_keys: idx_seller_name_sta_addr
          key: idx_seller_name_sta_addr
      key_len: 612
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

1). 全值匹配 ,对索引中所有列都指定具体值。

改情况下,索引生效,执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

2). 最左前缀法则

如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。

2.1匹配最左前缀法则,走索引:

索引全部覆盖时,索引长度比较长。

2.2违法最左前缀法则 , 索引失效:

explain select * from tb_seller where status='0' and address='北京市';

2.3如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效::

explain select * from tb_seller where name = '小米科技' and address='北京市';


2.4 范围查询右边的列,不能使用索引 。

explain select * from tb_seller where status = '0' and address='北京市';


2.5 不要在索引列上进行运算操作, 索引将失效。

explain select  * from tb_seller where substring(name,3,2) = '科技';

2.6 字符串不加单引号,造成索引失效。

mysql> explain select * from tb_seller where name = '小米科技' and status =0;

由于,在查询是,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效。


此时只命中name一个索引,索引长度为单独命中name时的长度、

**2.6 尽量使用覆盖索引,避免select **

mysql> explain select  * from tb_seller where name = '小米科技' and status='0' and address='北京市';

如果查询列,超出索引列,也会降低性能。

mysql> explain select name,nickname from tb_seller where name = '小米科技' and status='0' and address='北京市';

TIP : 
	
    using index :使用覆盖索引的时候就会出现,完全使用索引
    using where:在查找使用索引的情况下,需要回表去查询所需的数据
    using index condition:查找使用了索引,但是需要回表查询数据
    using index ; using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据

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

示例,name字段是索引列 , 而nikename不是索引列,中间是or进行连接是不走索引的 :

explain select * from tb_seller where name='黑马程序员' or nickname = '张三';

2.8 以%开头的Like模糊查询,索引失效。
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。

mysql> explain select * from tb_seller where name like '%黑马程序员';

2.9 如果MySQL评估使用索引比全表更慢,则不使用索引。
address 是有索引的,但是查询时并不会走索引
address 的内容单一且全部都是重复的,mysql 判定走索引比全表更慢,所以查询不能使用索引。


其实这个字段不适合加索引,我们这只做测试做。

2.10). is NULL , is NOT NULL 有时索引失效
将其中一个name 修改成NUll ,测试mysql索引优化选择器。

可以看到,并不是is NULL , is NOT NULL完全不走索引,Mysql 内部会自动判断是否会要使用索引,mysql 判定走索引比全表更慢,就不会使用索引。

2.11). in 走索引, not in 索引失效

尽量使用复合索引,而少使用单列索引 。

创建复合索引

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

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

相关文章