MySQL中的索引的使用

​一、索引是什么？

索引就是一个数据结构，我们把表中的记录用一个适合高效查找的数据结构来表示，目的就是让查询变得更高效。

二、哪些情况需要创建索引?

1.主键自动建立唯一索引。

2.频繁作为查询条件的字段应该创建索引。

3.多表关联查询中，关联字段应该创建索引 ，on两边都要创建索引。

4.查询中排序的字段，应该创建索引 B + tree 有顺序

5.统计或者分组字段，应该创建索引

三、哪些情况不需要创建索引?

1.表记录太少，索引是要有存储的开销。

2.频繁更新的，索引需要维护。

3.查询字段使用频率不高。

四、为什么使用组合索引

由多个字段组成的索引就是组合索引。使用顺序就是创建的顺序。

优势：效率高、省空间、容易形成覆盖索引

使用：遵循最左前缀原则

1.前缀索引

like 常量%使用索引     like %常量 不使用索引

2.最左前缀

从左向右匹配直到遇到范围查询> < between 索引失效

五、索引失效分析

-- 首先我们新建以下表和索引。
-- 用户表 
create table tuser( 
id int primary key,
loginname varchar(100),
name varchar(100), age int, 
sex char(1),
dep int,
address varchar(100)
);
-- 部门表 
create table tdep(
id int primary key, 
name varchar(100) 
);
-- 地址表 
create table taddr( 
id int primary key, 
addr varchar(100) );
-- 创建普通索引 
 alter table tuser add index idx_dep(dep); -- 创建唯一索引  
 alter table tuser add unique index idx_loginname(loginname); -- 创建组合索引  
 alter table tuser add index idx_name_age_sex(name,age,sex); -- 创建全文索引   
 alter table taddr add fulltext ft_addr(addr);
 

1.全局匹配我最爱

 explain select * from tuser where name='lx' and age=1 and sex='1';

条件与索引一一对应。

2.最佳左前缀法则

组合索引

带头索引不能死，中间索引不能断。如果索引了多个列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。正确的例子如1中的图所示。

错误的：

带头索引死：

 explain select * from tuser where age=23;

中间索引断：

explain select * from tuser where name='abc' and sex='1';

3.不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描。

 explain select * from tuser where loginname='zyy';

4.存储引擎不能使用索引中范围条件右边的列

explain select * from tuser where name='abc' and age>20 and sex='1';

5.尽量使用覆盖索引，减少select *

 explain select * from tuser ;

explain select name,loginname from tuser ;

explain select name,age,sex from tuser ;

explain select loginname from tuser ;

6.mysql 在使用不等于（!= 或<>）的时候无法使用索引会导致全表扫描

注意：主键索引会使用范围索引，辅助索引会失效

explain select * from tuser where loginname='lx';

explain select * from tuser where loginname!='lx';

7.主键索引字段上不可以判断null

主键索引字段上不可以判断null，索引字段上使用is null判断时，可使用索引。

explain select * from tuser where name is null;

explain select * from tuser where loginname is null;

explain select * from tuser where id is not null;

8.like 以通配符开头，mysql 索引失效会变成全表扫描的操作。

explain select * from tuser where name like 'a%';

 explain select * from tuser where name like '%a';

由此可见，like 以通配符结尾相当于范围查找，索引不会失效。与范围条件不同的是：不会导致右边的索引失效。

解决like ‘%字符串%’时，索引失效的解决办法是使用覆盖索引。

explain select name , age,sex from tuser where name like '%a%';

9.字符串不加单引号索引失效。

索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描。

explain select * from tuser where name=123;

10.少用or，用它来连接时会索引失效。

explain select * from tuser where name='abc' or age=23;