MySql索引

1.什么是索引

官方定义:一种帮助mysql提高查询效率的数据结构-索引数据结构

索引的优点:

1、大大加快数据查询速度

索引的缺点:

1、维护素引需要耗费数据库资源
2、索引需要占用磁盘空间
3、当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

2.索引分类

主键索引

建立表时，将某个字段设定为主键后数据库会自动建立索引，innodb为聚簇索引。主键索引所在的列值不能为空。

单值索引（普通索引）

即一个索引只包含单个列，一个表可以有多个单列索引。

-- 创建name为单值索引
select * from user where name='zhangsan';

唯一索引

索引列的值必须唯一，但允许有空值(只能有一个)

复合索引

即一个索引包含多个列

-- 创建(name,age)为复合索引
select * from user where name='zhangsan' and age=20;

全文索引(My5.7版本之前只能由MYISAM引擎建立。5.7之后INNODB引擎也能建立。)

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引。

3.索引基本操作

新建一个表

create table t_user (
  id varchar(20) PRIMARY key,
  name varchar(30)
);

3.1 主键索引

主键索引是在建表时自动为主键创建的索引。

-- 查看表中的所有索引
show index from t_user;

3.2 普通索引

3.2.1 建表时创建普通索引

drop table if EXISTS t_user;

-- 建表时指定哪一列为单值索引列
create table t_user (
	id varchar(20) PRIMARY key,
	name varchar(30),
	key(name)
);

普通索引所在的列值允许为空。
索引名默认为字段名。

-- 删除索引   drop index 索引名 on 表名;
drop index name on t_user;

3.2.2 建表后创建普通索引

-- create index 索引名 on 表名(字段名);
create index name_index on t_user(name);

3.3 唯一索引

3.3.1 建表时创建唯一索引

drop table if EXISTS t_user;
create table t_user (
	id varchar(20) PRIMARY key,
	name varchar(30),
	unique(name)
);

唯一索引所在的列的值必须保证唯一，能为null，且只能有一个null。

3.3.2 建表后创建唯一索引

-- create unique index 索引名 on 表名(字段名);
create unique index name_index on t_user(name);

3.4 复合索引

3.4.1 建表时创建复合索引

drop table if EXISTS t_user;
create table t_user (
	id varchar(20) PRIMARY key,
	name varchar(30),
	address varchar(50),
	key(name,address)
);

面试题

基于（name,age,address）三个字段建立的符合索引

name address 能否利用索引
name address age 能否利用索引
age address 能否利用索引
age name address 能否利用索引

1.最左前缀原则
- name age,name address,name age address可以利用索引。
2.mysql引擎在查询为了更了更好利用索引在查询过程中会动态调整查询字段顺序以便利用索引。

name address 能否利用索引              ---可以  
name address age 能否利用索引          ---可以
age address 能否利用索引               ---不能
age name address 能否利用索引          ---可以
age                                   ---不可以
name                                  ---可以

3.4.2 建表后创建复合索引

create index name_index on t_user(name,address);

4. 索引的底层原理

新建一个表,并插入数据。

drop table if EXISTS t_user;
-- 只存在一个主键索引
create table t_user (
	id bigint PRIMARY key,
	name varchar(30),
	address varchar(50)
);
--插入数据，主键无序插入
insert into t_user values (5 , 'zhangsan','北京');
insert into t_user values (1 , 'lisi','上海');
insert into t_user values (3 , 'wangwu','武汉');
insert into t_user values (6 , 'zhaoliu','天津');
insert into t_user values (2 , 'tianqi','重庆');
insert into t_user values (4 , 'hongba','成都');
insert into t_user values (7 , 'chenjiu','长沙');
-- 查看所有数据
select * from t_user;

主键自动进行了排序

为什么要对主键进行自动排序

B+树结构

叶子节点

数据存储类似于单向链表，对主键进行排序，时间复杂度是O(n),查找id为5的数据，需要查5次。
每个叶子节点存放的都是主键id，数据（占空间比较大）和指针。
每个叶子节点之间用指针连接，形成链表结构。

普通节点(非叶子节点)

普通节点存放的只是主键id和指针（占用空间少）
一个普通节点指向多个叶子节点，但只包含该页第一个叶子节点的指针。
第一层：假如一条数据加指针总共36B（一个叶子节点的大小），每页能存（16*1024/36=455）条数据。
第二层：普通节点只存放主键id和指针共16B（bigint 8个字节+指针8个字节），一个普通节点能存（16*1024/16=1024）个叶子节点。共465920条数据。
第三层：每个节点能存储（161024/16=1024）个第二层的节点，共（1024465920=4.8亿）条数据。

根节点

为了加快查询，Mysql将根节点常驻内存，减少I/O操作。
如果有3层结构，只需要进行两次I/O操作。

B树和B+树结构区别

B树非叶子节点也存储数据。
B+树只叶子节点存储数据，非叶子节点只存储键值信息（索引值和指针），不存储数据。
如果使用B树的话，每层存储的数据量就会减少很多，树的深度就会更深，存储相同数据量的层数要比B+数大。

主键索引又称为聚簇索引，普通索引又称为非聚簇索引。

当使用普通索引进行查数据时，不是拿这个列全表扫描，而是先根据这个列去非聚簇索引树上找到对应的主键id。在根据这个id去聚簇索引树上去查。
如果树深度一共三层
- 基于主键查找，共需2次I/O操作。根节点常驻内存。
- 基于非主键查找，需3次I/O操作，第一次I/O去非聚簇索引树上查找出主键id。

Mysql主键id不建议使用UUID，建议使用int或bigint类型

int类型能够方便进行排序。
UUID比较离散。不利于排序。

4.1 B+树

B+Tree是在B-Tree(B树)基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构.

B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘/O次数，进而影响查询效率。
B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同:

1.非叶子节点只存储键值信息。
2.所有叶子节点之间都有一个链指针。
3.数据记录都存放在叶子节点中。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节）或BIGINT(占用8个字节)，指针类型也一般为4或8个字节
也就是说一个页(B+Tree中的一个节点)中大概存储16KB(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为『10』 ^{3)。也就是说一个深度为3的B+Tree索引可以维护10}310^3 10^3=10亿条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。
mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

聚簇索引和非聚簇索引

– 聚簇索引:将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。

聚簇索引不一定是主键索引，但主键索引一定是聚簇索引。
非聚簇索引:将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。

注意:在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、普通索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

为什么非聚簇索引存储的是主键值，而不是主键值得地址

增删改时，链表结构下主键id地址会变化。

InnoDB中

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用'where id= 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
若对Name列进行条件搜索，则需要两个步骤∶第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。(重点在于通过其他键需要建立辅助索引)
聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，InoDB会隐式定义一个主键(类似
oracle中的Rowld)来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢

聚簇索引的优势

问题:每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗?聚簇索引的优势在哪?

1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中(缓存器)，再次访问时会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键id来组织数据，获得数据更快。
2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化。或者是我们需要查找的数据，在上一次I/O读写的缓存中没有，需要发生一次新的I/O操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

使用聚索引需要注意什么?

当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
建议使用int类型或bigint的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结杓影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间,也会影响到Io操作读取到的数据量。

为什么主键通常建议使用自增id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

什么情况下无法利用索引呢?

1．查询语句中使用LIKE关键字
- 在查询语句中使用LLIKE关键字进行查询时，如果匹配字符串的第一个字符为"“%"”，索引不会被使用。如果""%""不是在第一个位置，索引就会被使用。
2.查询语句中使用多列索引
- 多列索引是在表的多个字段上创建一个索引(name,age,address)，只有查询条件中使用了这些字段中的第一个字段(name)，索引才会被使用。
3.查询语句中使用OR关键字
- 查询语句只有or关键字时，如果oR前后的两个条件的列都是索引，那么查询中将使用索引。如果oR前后有一个条件的列不是索引，那么查询中将不使用索引。

4.2 聚簇索引

聚簇索引的主键索引的叶子结点存储的是键值对应的数据本身；辅助索引的叶子结点存储的是键值对应的数据的主键键值。

主键索引存储的键值就是主键。那么也就是说，聚簇索引的主键索引，在叶子节点中存储的是主键和主键对应的数据。数据和主键索引是存储在一起的，一起作为叶子节点的一部分。
辅助索引存储的键值是非主键的字段。那就也就是说，通过辅助索引，可以找到非主键字段对应的数据行中的主键。
当直接采用主键进行检索时，可通过主键索引直接获得数据；而当采用非主键进行检索时，先需要通过辅助索引来获得主键，然后再通过这个主键在主键索引中找到对应的数据行。

举个例子吧。假设有这么一个数据表。

那么采用聚簇索引的存储方式，对应的主键索引为：（主键为ID）

对应的辅助索引为：（键值为Name，大概的意思）：

所以当使用where ID = 7这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主键索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

最后把以上过程整理总结一下，聚簇索引实际上的过程就分为以下两个过程。现在这个图应该能够看懂了吧。

4.3 非聚簇索引

非聚簇索引的主键索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点都存储指向键值对应的数据的物理地址。

与聚簇索引来对比着看，上面的定义能够说明什么呢。首先，主键索引和辅助索引的叶子结点都存储着键值对应的数据的物理地址，这说明无论是主键索引还是辅助索引都能够通过直接获得数据，而不需要像聚簇索引那样在检索辅助索引时还得多绕一圈。

同时还说明一个点，叶子结点存储的是物理地址，那么表示数据实际上是存在另一个地方的，并不是存储在B+树的结点中。这说明非聚簇索引的数据表和索引表是分开存储的。

同样，对非聚簇索引的检索过程来个总结。

无论是主键索引还是辅助索引的检索过程，都只需要通过相应的 B+Tree 进行搜索即可获得数据对应的物理地址，然后经过依次磁盘 I/O 就可访问数据。

对比聚簇索引和非聚簇索引，可以发现二者最主要的区别就是在于是否在 B+Tree 的节点中存储数据，也就是数据和索引是否存储在一起。这个区别导致最大的问题就是聚簇索引的索引的顺序和数据本身的顺序是相同的，而非聚簇索引的顺序跟数据的顺序没有啥关系。

posted @ 2022-03-04 16:45 初夏那片海阅读(62) 评论(0) 收藏举报

刷新页面返回顶部

初夏那片海