MySQL如何使用索引

﹏ヽ暗。殇╰゛Y 2023-06-08 04:45 29阅读 0赞

# 初始化测试数据 #

--------------------

### 创建一个测试用的表 ###

create table dept(  
id int primary key auto\_increment ,  
deptName varchar(32) not null unique,  
salary decimal(12,6) not null,  
remark varchar(256),  
createDate date);

--------------------

### 在表中插入一万条数据 ###

BEGIN

Declare i int default 0;  
while(i<=10000) do  
begin  
select i;  
set i=i+1;  
insert into dept(deptName,salary,createDate,remark) values(i,20000,now(),‘test’);  
end ;  
End while;  
END

--------------------

# 通过EXPLAIN分析SQL的执行计划 #

![在这里插入图片描述][format_png]  
type:表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如下(从左到右，性能由差到好)

ALL index range ref eq\_ref const,system NULL

ALL: 全表扫描

index: 索引全扫描

range:索引范围扫描

ref:使用非唯一索引扫描

eq\_ref:使用唯一索引扫描

const，system:单表中最多只有一个匹配行

*以上:通过explain分析type是ALL 是性能最差的一种 以下:开始优化。*

--------------------

# MySQL中BTREE索引的用法 #

### 普通的BTREE索引 ###

优化前 explain select remark from dept where deptName = ?; **type ALL**

增加索引 ALTER TABLE dept ADD INDEX index\_remark (`remark`);

优化测试 explain select remark from dept where deptName = ?; **type ref**

### 联合BTREE索引 ###

增加联合索引 ALTER TABLE dept ADD INDEX index\_all (`deptName`,`remark`,`createDate`);

explain select deptName,remark,createDate from dept where deptName=‘2’ and remark = 'test’and createDate =‘2018-07-22’;—— **type ref**

但是explain select deptName,remark,createDate from dept where remark = 'test’and createDate =‘2018-07-22’; ——**type index**

——type从ref(使用非唯一索引扫描)变成了index(索引全扫描)，

上面两个SQL 索引相同 Type不同是因为 BTREE索引中匹配最左前缀 比如给col1+col2+col3字段上加联合索引能够被包含col1+col2 、col1+col2+col3、col1+col3使用(顺序没有影响 比如 where col1=? and col2=? 和 where col2=? and col1=? 结果一样)

使用联合索引 相当于一下子创建了如上的三个索引，这就是联合索引的好处

--------------------

# 存在索引但不能使用索引的经典场景 #

例句：explain select deptName,remark,createDate from dept where deptName=‘2’ and remark = 'test’and createDate =‘2018-07-22’;  
——type ref

下面是反面教材:

**1.以%开头的LIKE查询不能使用BTREE索引**

explain select deptName,remark,createDate from dept where deptName like’%2’ and remark = 'test’and createDate =‘2018-07-22’; type index

**2.数据类型出现隐式转换时也不能使用索引**

explain select deptName,remark,createDate from dept where deptName =2 and remark = 'test’and createDate =‘2018-07-22’; type index

**3.复合索引时 不符合最左匹配原则(上面已经提到)**

explain select deptName,remark,createDate from dept where remark = 'test’and createDate =‘2018-07-22’; type index

**4.用or分隔开的条件，如果or前的条件中的列有索引，后面的列中没有索引，那么涉及到的索引都不会使用到**