mysql索引失效问题

小咪咪 2022-04-03 07:22 367阅读 0赞

### 1、单个主键 ###

CREATE TABLE `user_details` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(50) DEFAULT NULL,
      `user_phone` varchar(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

\#创建索引  
alter table user\_details add index user\_name\_index(\`user\_name\`);

desc user\_details;

insert into user\_details values(1,'yushengjun1','15921009245');  
insert into user\_details values(2,'yushengjun2','15921009245');  
insert into user\_details values(3,'yushengjun3','15921009245');  
insert into user\_details values(4,'yushengjun4','15921009245');  
insert into user\_details values(5,'yushengjun5','15921009245');  
insert into user\_details values(6,'yushengjun6','15921009245');

\#主键索引  
EXPLAIN select \* from user\_details WHERE id=1;  
\#主键索引  
EXPLAIN select \* from user\_details WHERE id='1';

\#普通索引  
EXPLAIN select \* from user\_details WHERE user\_name='15921009245';

\#不走索引  
EXPLAIN select \* from user\_details WHERE user\_name =15921009245;

\#不走索引  
EXPLAIN select \* from user\_details WHERE user\_phone='15921009245';

\#主键索引模糊查询百分号开头不走索引  
EXPLAIN select \* from user\_details WHERE id like '%sss';

\#主键索引模糊查询百分号结束不走索引  
EXPLAIN select \* from user\_details WHERE id like 'sss%';

\#普通索引模糊查询百分号开头不走索引  
EXPLAIN select \* from user\_details WHERE user\_name like '%sss';

\#走索引user\_name\_index,普通索引模糊查询百分号结束走索引  
EXPLAIN select \* from user\_details WHERE user\_name like 'sss%';

\#走索引PRIMARY  
EXPLAIN select \* from user\_details WHERE id=1 and user\_name='yushengjun1';

\#走索引PRIMARY,user\_name\_index  
EXPLAIN select \* from user\_details WHERE id=1 or user\_name='yushengjun1';

\#走索引PRIMARY,user\_name\_index  
EXPLAIN select \* from user\_details WHERE user\_name='yushengjun1' and user\_phone='aaaa';

\#不走索引  
EXPLAIN select \* from user\_details WHERE user\_name='yushengjun1' or user\_phone='aaaa';

### 2、联合主键 ###

drop table if exists `user_details`;
    CREATE TABLE `user_details` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(50) DEFAULT NULL,
      `user_phone` varchar(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

desc user\_details;

alter table user\_details add index user\_name\_phone\_index(\`user\_name\`, \`user\_phone\`);

\#走索引user\_name\_phone\_index  
explain select \* from user\_details where user\_name = 'aaa';

\#不走索引  
explain select \* from user\_details where user\_phone = 'aaa';

\#走索引user\_name\_phone\_index  
explain select \* from user\_details where user\_name = 'aaa' and user\_phone = 'aaa';

\#走索引user\_name\_phone\_index  
explain select \* from user\_details where user\_phone = 'aaa' and user\_name = 'aaa';

explain select \* from user\_details where user\_name like 'aaa&' and user\_phone = 'aaa';

\#不走索引  
explain select \* from user\_details where user\_name = 'aaa' or user\_phone = 'aaa';

###  3、索引失效的几种情况： ###

索引无法存储null值

前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)