MySQL的索引

目录

索引

索引的数据结构

B树

B+树

索引的类型

聚集索引

二级索引

索引的使用原则

索引优化

---

        索引是一种有序的数据结构。

        如果把磁盘中的表做据比作是一本书，那么索引就像是书的目录。我们可以根据索引快速的定位一条数据在磁盘中的位置。

如何创建索引

//给表user_innodb 的 name创建一个索引

alter table user_innodb add index index_name(name);

索引类型

• NORMAL(普通索引)
• UNIQUE(唯一索引) 值不能重复。
• 主键索引:是特殊的唯一索引(主键索引不允许任何一个空值)
• FULLTEXT(全文索引)
• SPATIAL()

索引

索引的数据结构

B树

多路平衡查找树

通过分裂与合并来达到和平

B树的问题：

        查询连续数据时(范围查找时)可能会发生多次的磁盘IO.比如我们想查找5~15的数据，我们就需要在磁盘块2查找完5数据后在返回磁盘块1然后再判断6应该再哪个磁盘块里，5~15需要多次的遍历多次的进行磁盘IO。这显然会降低我们的查找速度

        而且存放data也会占用空间  树还是不够矮

B+树

B+数的优势

• B Tree能解决的问题B+Tree都能解决

• 只有叶子存放数据，会让我们的树的层高变少，这样磁盘IO的次数也变少

• 性能稳定 每次查询的磁盘IO次数一样

• 叶子节点是双向链表，范围查找更快

非叶子不存放data只存放索引的值，这样可以使每个节点存放更多的主键id(因为每个page16k)，这样可以让数的分支更多，就可以让树的层高更小。

叶子节点记录数据，而且所有的叶子节点都有双向的指针，这样就可以避免范围查找时发生多次的磁盘IO.

page= 16KB
1、假设一个叶子节点可以存10条记录(每条记录1600bytes)
2、假设内节点可以存1000个键值+子节点指针的单元(一个单元16 bytes)

 所以只需要两层就可以存储百万级的数据。

索引的类型

聚集索引

主键索引可以作为聚集索引，除了聚集索引其他的都称为二级索引。

但是如果没有主键索引，那么谁会是聚集索引呢?

会把不包含空值的唯一索引作为聚类索引。

那要是既没有主键索引又没有不包含空值的唯一索引呢？

就会把隐藏的row ID，当作聚集索引

Innodb中，数据就是索引，索引就是数据

 主键索引的叶子节点存放的是具体的数据，而不是数据的映射地址。

只有聚集索引的叶子上放的是完整的数据行，因为如果多个索引上都存放完整的数据行的话不仅浪费空间，也浪费时间。

二级索引

除了聚集索引，其他的都称为二级索引。

红色的线我们称之为回表，回表的意思就是我们查了二级索引后但是拿不到一条数据的所有信息，所以还要根据主键查询一遍聚集索引，这个过程我们称之为回表。

索引的使用原则

为什么不建议使用身份证作为主键?
模糊匹配like abc%，like %2673%，like %888都用不到索引,对吗?

不要使用select*,写明具体查询字段,为什么?

索引越多越好，对么？

既然索引那么nb，那么我们把一个表的所有字段都建上索引吧！这显然是不对的，因为索引会大量的占用我们磁盘的空间，而且新增或者删除一条数据，设计到全部索引的修改，这会大大降低我们的性能，会适得其反。

为什么不要在性别字段上建索引?

因为性别一般会用0或1来代表男或女，所以离散度非常的低(重复度非常高)，这样建出来的索引查询效率低的离谱，所以不要在离散度低的字段上建索引。

什么是联合索引的最左匹配原则?

联合索引是以第一个字段为准进行排序的，比如我们以name和phone创建一个联合索引，这个索引就是以name进行排序的

alter table user_innodb add index index_name_phone(name,phone);

下面谁能用到索引？

1. select * from user_innodb where name='zs' and phone='1888888888'; 
2. select * from user_innodb where phone='1888888888' and name='zs'; 
3. select * from user_innodb where phone='1888888888'; 
4. select * from user_innodb where name='zs'; 

答案:1,2,4

1和4应该没有疑问，2之所以能走索引是因为我们的优化器，优化器会把他优化成和1一样，所以他也可以走索引

联合索引桥

 什么时候不会发生回表?

当我们select 的字段全部包含在一个索引中，就不会进行回表。

alter table user_innodb add index index_name(name);

explain select name from user_innodb where name='杨兵';  

Extra Using index代表没有回表 

Using where; 代表存储引擎返回到server不全是需要的，需要再进行过滤

alter table user_innodb add index index_name_phone(name,phone);

下面那个不会回表？

1. select name,phone from user_innodb where name='zs' and phone='1888888888'; 
2. select name,phone from user_innodb where phone='1888888888' and name='zs'; 
3. select namefrom user_innodb where phone='1888888888'; 
4. select name from user_innodb where name='zs'; 

答案:1 2 3 4都不会回表

索引条件下推

这个功能可以关闭

应该在什么字段上创建索引？

• 在用于where判断、order排序、join的(on). group by字段上创建索引。
• 索引的个数不要过多。
• 离散度低的字段，例如性别,不要建索引。
• 频繁更新的值，不要作为主键或者索引。（因为频繁的更新值会让b+树不断地分散合并）
• 不建议用无序的值（例如身份证、UUID)作为索引。（无序也会频繁的改变B+树的结构）
• 复合索引把散列性高(区分度高)的值放在前面。
• 创建复合索引,而不是修改单列索引。
• 过长的字段，建立前缀索引。（太长会占用空间，太短离散度会低）

索引失效的场景？

• 用离散度低的字段作为索引（例如性别），这样当走索引时相同的值太多，B+树不知道左找还是又找，这时优化器就会优化，不让走索引
• 模糊查询时，把%放在前面.
• 范围查询，会让后续索引失效
• 索引列上使用函数，或者表达式
• 联合索引要满足最左匹配原则，就像桥一样不能断，断了后面就走不到

是否走索引还是由我们的优化器来决定的，优化器默认是基于成本的，

还有一种是基于规则的优化器

什么是索引覆盖和索引下推？

覆盖索引就是走了二级索引，但是没发生回表，也就是我们要查询的字段，全都包含在二级索引中，所以就不用回表了。

索引下推就是把Server层的过滤，下推到Innodb存储引擎层来进行过滤，可以减少回表次数。

索引优化

select * from test where c=1 and a =1 and b>1 现在我test建立了abc3

个字段的联合索引，能不能走索引？

我们要学会 explain

type

访问类型：

const：主键

ref： 使用了二级索引

range :范围索引查询 一般能够达到这个就OK （开发线） 会走索引

index:全索引扫描 但是不会走索引 通过链表的形式查数据

all：全表扫描

性能 从上往下 越来越慢 开发要求必须在range

一般不允许3个表关联查询

ROWS

预估的扫描的数量 数据越大，扫描的越多

Extra

Using where; 使用where过滤

Using index 用到了覆盖索引 其实不是索引 一般会在二级索引里面

using filesort 排序没有用到索引

Using index condition 索引下推 5.6之后新出的一个概念： 5.6之后优化

查出来以后之前是给服务器过滤的，但是现在我在索引树里面就去过滤掉了 减少回表次数

select * from test where c=1 and a =1 and b>1

5.6之前 a =1 and b>1 查出来3条，需要回表，回3条 给到server层 过滤

5.6之后 a =1 and b>1 查出来3条 现在索引树里面过掉c=1 剩1条 回表1条数据 给到server层