MySQL高级(3) 索引入门

---

MySQL高级(3) 索引入门

索引介绍

为什么要使用索引?

查询快

索引的概念和分类

概念

是帮助MySQL搞笑获取数据的数据结构,可以简单的理解为"排好序的快速查找数据结构".

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据,
这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引

简单的理解,就是使用B+树构建的一个"目录"

为什么使用B+树而不是B树?

理论上B树的查找速度要比B+树快, 但是在有限的内存中, B+树可以存储更多的数据, 这样可以提高索引的命中率, 所以MySQL选择了B+树

举例

如图是一个B+树,非叶子节点存储的都是数据范围,只有叶子节点存储的是确切的值和对应的指针(指向物理地址),数据库执行查询时,不断的从根节点向下查找该值所在的范围,知道查找到叶子节点,然后读取相应的物理地址

一般来说索引占用内存也很大,不可能全都存储在内存中,所以索引一般以索引文件的形式存储在磁盘上

分类

聚簇索引

按照顺序排列, 如id自增的主键

非聚簇索引

不按照顺序排列, 一般在数据库中, 除了主键是聚簇索引, 其他都是非聚簇索引

单值索引

只有一个字段作为索引

唯一索引

该索引的值是唯一的(unique)

主键索引

默认主键自动创建索引

复合索引

多个字段作为索引

索引的优缺点

优点

1. 提高了数据检索的效率,降低数据的IO成本
2. 降低数据排序的成本,降低CPU的消耗

缺点

1. 实际上索引也是一张表,保存了主键与索引字段,并指向实体表的记录,要占用空间
2. 虽然大大提高了查询速度,但是会降低更新速度,如对表进行insert,update和delete操作时,MySQL不仅要保存数据,还要保存索引

索引入门

创建的创建和删除

创建索引

• 一般语法(最常用)

create [unique] index <索引名> on <表名>(<索引字段列表>);
# 或者
alter table <表名> add index <索引名> (<索引字段列表>);

举例

create index idx_age_dep_name on emp(age,dep,name);
# 或者
alter table emp add index idx_age_dep_name (age,dep,name);

• 创建一个主键索引(本质上是添加或更换一个表的主键)

alter table <表名> add primary key(<索引字段列表>);

• 创建一个唯一索引

alter table <表名> add unique <索引名>(<索引字段列表>);

• 添加普通索引

alter table <表名> add index <索引名>(<索引字段列表>);

• 添加全文索引

alter table <表名> add fulltext <索引名>(<索引字段列表>);

• 查看指定表中的索引

show index from <表名>;

默认情况下,如果一个表有主键,会自动创建一个主键索引

删除索引

• 删除索引

drop index <索引名称> on <表名>;

索引的命名规范

一般使用idx开头,后面跟表名和相应的字段名,如果有多个字段名,则采用驼峰命名法,即idx_表名_字段名

举例 1 在`user`表中使用`name`字段建立单值索引

create index idx_user_name on user(name);

举例 2 在`user`表中使用`name`和`email`字段建立复合索引

create index idx_user_nameEmail user(name,email);

创建索引的情景

需要建立索引的情况

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该建立索引
• 查询中与其他表关联的字段,外键关系建立索引
• 单键/组合索引的选择问题,组合索引性价比更高(默认情况下MySQL只会选择一个索引)
• 查询中排序的字段,排序字段若通过索引去访问可以大大提高排序速度
• 查询中统计或者分组的字段

不需要建立索引的情况

• 表记录太少
• 经常增删改查的表或者字段
• where条件里用不到的字段
• 过滤性不好的,即字段内容比较单一,例如性别

索引选择性公式

索引的选择性是指索引列中不同记录的数据与表中记录数的比,例如一个表中有1000条记录,表索引中有800个不同值值,那么这个索引的选择性就是800/1000=0.8,一个索引的选择性越接近于1,这个索引的效率就越高

Explain (执行计划)

执行计划介绍

概念

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。从而分析你的查询语句或是表结构的性能瓶颈

作用(Explain能查询什么)

对于一张表,我们是不是应该建立索引呢,一般来说我们需要了解整个SQL语句的执行过程,然后根据实际情况来判断是否要建立索引,Explain指令可以帮助我们来查看SQL语句的执行计划(就是SQL语句是怎么执行的),Explain指令可以:

1. 表的读取顺序
2. 可以使用的索引
3. 数据读取操作的操作类型
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被物理查询

Explain的使用

语法

explain
<SQL语句>

Explain结果各个字段的含义和说明

执行一个SQL语句,发现输出的内容有以下字段:

id	select查询的序列号,包含一组数字,表示查询中执行select自居或操作表的顺序
select_type	查询类型
table	说明这一行数据是关于哪张表的
type	访问类型
partitions	分区表中的命中情况, 不是分区表时, 该项为null
key	实际使用的索引, 如果为null, 则没有使用索引
key_len	表示索引中使用的字节数
rows	执行SQL语句实际扫描的行数, 越小越好
ref	显示索引的哪一行被使用了
filtered	表示存储引擎返回的数据在server层过滤后, 剩下多少满足查询的记录数量的比例(百分比)
==extra=	附加的其他信息

id (可以看做权重,权重越大的表,越先读取,权重一样就按顺序执行)

1. 如果id相同,从上到下执行

举例

有t1,t2,t3三张表,执行某次次关联查询的explain结果如下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cNKESrE9-1585311663026)(MySQL%E9%AB%98%E7%BA%A7(2)].assets/image-20200214192140752.png)

那么表的读取顺序就是 t1->t3->t2

2. 如果id不同,先执行id大的,再执行小的,一般来说,有子查询的SQL语句会先执行子查询,所以子查询的id值会大

举例

同上,某次执行结果如下:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rUfp7xlq-1585311663026)(MySQL%E9%AB%98%E7%BA%A7(2)].assets/image-20200214192547243.png)

那么表的读取顺序是 t3->t1->t2

3. 如果既有id一样的又有不一样的,先按照id从大到小执行,然后再按照顺序从上到下执行

举例

同上,某次执行结果如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vRvZFMSn-1585311663028)(MySQL%E9%AB%98%E7%BA%A7(2)].assets/image-20200214193158599.png)

表的读取顺序是: t3->derived2->t2,其中derived2是衍生表,下面select_type中会介绍到

注意

1. 相同id值的表,读取顺序不一定按照表的书写顺序来读取,就比如第一个举例中,表的书写顺序是select * from t1 innerjoin t2 inner join t3,但是实际读取的顺序是 t1->t3->t2
2. id号每个号码表示一次独立的查询,一个SQL的查询次数越少越好

select_type

• SIMPLE 简单查询

简单的select查询,查询中不包含子查询或者UNION

• PRIMARY 主要查询

查询中若包含任何子查询部分，最外层查询则被标记为PRIMARY ,一般来说PRIMARY 是最后加载的那个

• DERIVED 衍生查询

在FROM后面包含的子查询被标记为DEPENDENT (衍生表),说白了就是一个临时表

• SUBQUERY 子查询

在SELECT或WHERE列表中包含了子查询, 但是WHERE条件是等于

explain select t1.id from t1
where t1.id=
(
	select t2.id from t2 where t2.name="小明"
)

• DEPENDENT SUBQUERY 依赖子查询

在SELECT或WHERE列表中包含了子查询, 但是WHERE条件是

explain select t1.id from t1
where t1.id in
(
	select t2.id from t2 where t2.name="小明"
)

• UNCACHEABLE SUBQUERY 不可用缓存的子查询

表示当前子查询不使用缓存, 比如当前SQL语句中有sql变量, 就不使用缓存

举例

explain select * 
from t1 
where id = (
    select id from t2 where t2.id=@@sort_buffer_size
);

• UNION

若第二个SELECT出现在UNION之后，则被标记为UNION;

若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED

• UNION RESULT

从UNION表获取结果的SELECT,通俗的讲就是使用了union联合查询之后的结果

type *

system>const>eq_ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>reange>index>all

• system

表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,可以忽略不计

• const

表示通过索引一次就找到了,const用于比较primary key或者unque索引,因为只匹配一行数据,所以很快,例如将主键作为where的查询条件,MySQL将会将该查询转换为一个常量

举例 此时t1表中只有一个id为1的行

select * from t1 where id=1;

• eq_ref

唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描

举例 在匹配时,t2表中只有一条记录,且这条记录刚好与t1.id匹配

explain select * from t1,t2 where t1.id=t2.id;

• ref

非唯一性扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回的是所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该数据查找和扫描的混合体

• index_merge

合并索引, 例如当查询条件使用了or, 且or的两边都是索引, 这个时候就会出现index_merge

• ref_or_null

对于某个字段即需要关联条件, 也需要null值的情况下

where t.id=null or t.id=1;

• index_subquery

利用索引来关联子查询, 不在全表扫描, 即子查询用到了索引

• unique_subquery

子查询中的唯一索引

explain select * 
from t
where t.id in (
	select t.id from t  # id是主键, 全局唯一
)

• range 可能需要优化

一般是使用了between,<,>,in等查询

• index 可能需要优化

表示SQL语句使用了索引,但是没有用过索引进行过滤(即where或having后面没有使用索引), 一般是覆盖索引或者是利用索引进行了排序分组

• all 可能需要优化

表示遍历了整张表, 当出现all时, 表示可能需要建立索引了

possible keys

可以使用的索引

key

实际使用的索引, 如果为null, 则没有使用索引

key_len *

表示where 条件中使用到索引的字节数, 可以通过该列计算查询中使用的索引的长度, 可以帮我们检查是否充分利用了索引

理论上key_len的数值越大越好, 比如使用了相同的复合索引, key_len的值越大, 说明命中的索引越多

rows *

执行SQL语句实际扫描的行数, 越小越好

ref

显示索引的哪一行被使用了, 如果可能的话, 是一个常数, 通常作用不大

filtered

表示存储引擎返回的数据在server层过滤后, 剩下多少满足查询的记录数量的比例(百分比), 作用不大

Extra *

包含不适合在其他列中显示但是十分重要的额外信息

• using filesort

没有使用索引排序, 这种情况下效率较慢, 即order by 后面没有使用索引

• using temporary

group by 没有使用索引, 因为group by 的机制是先进行排序, 后进行分组, 所以这里如果不使用索引, 会极大的消耗性能

• using join buffer

关联查询时, 关联字段(on)没有用到索引

• impossible where

sql 逻辑出现错误, 比如: 大于201且小于10

• using index

表示相应的select操作中使用了覆盖索引(covering Index), 避免了访问了表的数据行, 效率高!

• using where

表名使用了where过滤

• select tables optimized away

用到了优化器