MySQL （七）索引

一、概述

1.索引结构

B+tree\Hash（类似于算法的搜索）

指路：10. 进阶-索引-结构-介绍_哔哩哔哩_bilibili

2.索引分类

按存储形式分类：

索引选取规则：

1.存在主键，主键索引就是聚集索引

2.不存在主键，第一个唯一索引就是聚集索引

3.以上都没有，InnoDB引擎自动生成rowid作为隐藏聚集索引

聚集索引：下方挂的是各行的数据

二级索引：下方挂的是与其对应的聚集索引序号

回表查询：在下面的图中根据name = 'Amy'确定聚集索引为2，再回到上面的图中查找索引为2的整行数据

二、基础语法

1.创建索引

为了完整，这部分的代码与之前的有所重复

create database test;

#创建时设置索引
use test;
create table total  
(`id` int(10) primary key, 
`name` varchar(20),
`dept` int(10),
`height` float ,
index h(height) #设置索引,index 索引名(列名)
#unique index(height) #设置唯一索引
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

create database test;

#创建后设置索引
use test;
create table total  
(`id` int(10) primary key, 
`name` varchar(20),
`dept` int(10),
`height` float
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

create index h on test.total(height); #create index 索引名 on 库名.表名(列名)
create index n_h on test.total(name,height); #联合索引

2.查看索引

#查看索引
show indexes from total in test; #from 表名 in 库名

3.删除索引

#删除索引
drop index height on total;  #索引名 on 表名

alter table total
drop index height;

三、性能分析

1.查看执行频次

#查看执行频次
show global status like 'Com_______' #后面_符不同，会出现不同的指令

运行结果：产看insert\delete\select等的执行频次

2.profile

#查看是否支持profile操作
select @@profiling #0不支持，1支持

#开启支持
set profiling = 1;

#查看每条SQL的耗时
show profiles;

#查看指定query_id的SQL语句各个阶段的耗时
show profile for query 4; #上图（运行结果）的第4条，画红线部分

#查看指定query_id的SQL语句各个阶段的CPU使用
show profile cpu for query 4;

3.explain

#数据准备
create database test;
use test;
create table total  ( #总表：学生信息
`id` int primary key, #索引1
`dept` int,
`age` int,
`height` varchar(20), #注意：此处身高是字符串类型
`university` varchar(20),
index total_dept(dept), #索引2
index ahu(age,height,university) #索引3：联合索引
)engine = InnoDB charset = utf8mb4 collate = utf8mb4_bin;

insert into `total` values(1,3500,18,'162',"CUMT");
insert into `total` values(2,3522,19,'165',"JNU");
insert into `total` values(3,3545,20,'178',"ABC");
insert into `total` values(4,3576,20,'173',"ICBC");
insert into `total` values(5,3587,25,'173',"CCB");
insert into `total` values(6,3523,22,'178',"CUMT");

#查看执行计划
#explain + 语句
explain select age,height,university from test.total 
where dept in (select dept from test.section where math > 80)

运行结果：

其中：以上运行结果各参数解释

id	操作顺序：id相同，从上到下；id不同，值越大，越先执行
select_type	simple(不适用表连接或子查询)、primary(外层的查询)等
type	性能由好到差：null、system、const、eq_ref、ref、range、index、all
possible_key	可能用到的索引
key	实际用到的索引
key_len	索引中使用的字节数（非实际使用长度，而是最大可能长度） 不损失精确性的前提下，长度越短越好
rows	必须执行的查询行数（估计值）
filtered	返回结果行数占读取行数的百分比 filtered值越大越好

四、运行规则

1.（联合索引）最左前缀法则：

联合索引ahu(age,height,university):

a.全部存在，走ahu

explain select * from test.total 
where (age > 20) and (height > '170') and (university = 'CUMT')

b.有age、height，缺university，走ahu

explain select * from test.total 
where (age > 20) and (height > '170')

c.有age，缺height，有university，走ahu，但university部分失效(与上图相比，filter降低)

explain select * from test.total 
where (age > 20) and (university = 'CUMT')

d.缺age，有height、unversity，走全表搜寻all

explain select * from test.total 
where (height > '170') and (university = 'CUMT')

e.缺age，仅有university，走全表搜寻all

explain select * from test.total 
where (university = 'CUMT')

2.SQL提示

create index u on total(university); #创建新索引
create index h on total(height)

explain select * from test.total 
where (age > 20) and (height > '170') and (university = 'CUMT')

a. use：指定使用，但不一定使用

explain select * from test.total 
use index(ahu) #指定使用ahu，但不一定使用
where (age > 20) and (height > '170') and (university = 'CUMT')

explain select * from test.total 
use index(h) #指定使用h，但不一定使用
where (age > 20) and (height > '170') and (university = 'CUMT')

b. force：强制使用，一定使用

explain select * from test.total 
force index(h) #强制使用h
where (age > 20) and (height > '170') and (university = 'CUMT')

c. ignore：不许使用

explain select * from test.total 
ignore index(u) #不许使用u
where (age > 20) and (height > '170') and (university = 'CUMT')

3.索引失效

a.筛选条件中对索引进行函数操作

create index u on total(university)  #该索引在四、2.中已经创建过，勿重复
explain select * from test.total 
where university = 'CUMT'

explain select * from test.total 
where substring(university,1,1) = 'C'

第一个运行结果使用索引u，第二个运行结果未使用索引

由于对列university进行substring操作，导致索引u失效

b.字符串类型不带引号

create index h on total(height) #该索引在四、2.中已经创建过，勿重复
explain select * from test.total 
where height = '162'

explain select * from test.total 
where height = 162

第一个运行结果使用索引h，第二个运行结果未使用索引

由于第二个162没有加引号，导致索引h失效

但是如果使用>，无论是否加引号，索引h都失效。（同e,与数据集有关）

explain select * from test.total 
use index(ahu) #指定使用联合索引，此处mysql会自己选择索引h，但为了演示，我们指定ahu
where  (age < 20) and (height = '162')

explain select * from test.total 
where (age < 20) and (height = 162)

上面两个运行结果均使用了索引ahu,

但是由于第二个height的162没有加引号，所以导致联合索引中height部分失效，

filtered值明显下降

c.尾部模糊匹配，索引不失效；头部模糊匹配，索引失效

explain select * from test.total 
where university like 'CU%'

explain select * from test.total 
where university like '%MT'

第一个用到了索引u，第二个没有；

但当用‘%C’和‘C%’时，你会发现没有区别，都没用索引u（同e，与数据集有关）

d. or一侧没有索引（联合索引无效）

列id有索引primary，且使用了primary

explain select * from test.total 
where (id = 1)

列id有索引primary，列age有联合索引ahu(没有独立索引)，所以两个索引均不能用

explain select * from test.total 
where (id = 1) or (age > 20)

当or两侧均有索引时，也不意味着一定会用索引,如下：

e.当全表扫描比索引更快时

explain select * from test.total 
where (id = 1) or (university = 'CUMT')

or两侧均有索引，应该走索引，

但是id = 1的行就在全表扫描的第一个，所以走全表扫描

4.覆盖索引

explain select * from test.total 
where (age > 20) and (height > '170') and (university = 'CUMT')

explain select age,height,university from test.total 
where (age > 20) and (height > '170') and (university = 'CUMT')

参见一、2.索引分类部分的回表查询：

联合索引中每个叶子节点都包含（age,height,university）三个信息，例如(18,'162','CUMT')

当结果仅需要返回id,age,height,university时，就不用回表查询，

当结果需要除以上数据之外的数据时，需要回表查询。

以上两个运行结果，明显第二个的key_len更小，查询效率更快

5.前缀索引

字符串较大时，可截取部分字符串，作为前缀索引。

#使用最初的数据，将索引u和h全部删除
create index u on total(university); 
explain select * from test.total 
where (age > 20) and (height > '170') and (university = 'CUMT')

create index u on total(university(2)); #前缀索引：截取字符串前两个字符
explain select * from test.total 
where (age > 20) and (height > '170') and (university = 'CUMT')

第二个运行结果的key_len明显小于第一个运行结果的