三、索引

1.什么是索引

索引(Index)是帮助MySQL高效获取数据的数据结构

2.索引的底层数据结构

MySQL中索引是B+树实现的,每个索引都对应一棵B+树。一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录

• 非叶子节点不存储data，只存储key（索引值），可增大度
• 叶子节点不存储指针
• 顺序访问指针，每个叶子节点都指向下一个叶子节点，提高区间访问的性能

3.索引的优缺点

1. 优点：
   • 创建唯一性索引可以保证每一行数据的唯一性
   • 可以加快检索效率
   • 加速表与表之间的连接
   • 在使用分组和排序子句进行数据检索时，可以显著减少查询分组和排序的时间
2. 缺点：
   • 索引的创建和维护需要消耗时间，数据量越大，消耗越大
   • 索引要占用物理空间，有额外的开销
   • 对表中数据进行增删改时，索引也要进行动态维护，频繁的此类操作会导致很大的开销
   • 由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率

4.创建索引的准则

• 经常搜索的列
• 主键列
• 用于表连接的列
• 按范围查找的列，因为索引已经排好序了
• 要排序的列
• 经常在where子句中使用的列

5.索引的原理

B+树是一种平衡查找树，所有的记录存在叶子节点之中，并且是顺序存放。

6.索引的分类

1. 聚集索引：按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，叶子节点成为数据页。每张表只能有一个聚集索引。数据页存放的是完整的每行记录，索引页仅仅存放的是键值及指向数据页的偏移量，而不是一行完整的记录。聚集索引在逻辑上是连续的，物理上不是连续的。
2. 辅助索引：叶子节点并不包含行记录的全部数据，包含键值以及行数据的聚集索引键。每张表可以有多个辅助索引，通过辅助索引搜索数据时，InnoDB存储引擎会遍历辅助索引并通过页级别的指针获得指向主键索引的主键，再通过主键索引获得行记录。
3. 所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低

7.创建索引的三种方式

1. 在执行create table 时创建索引

create table user_index(
     id int auto_increment primary key,
     first_name varchar(16),
     last_name varchar(16),
     id_card varchar(18),
     information text(225),
     key name(first_name,last_name),
    fulltext key(information),
    unique key(id_card)
);

2. alter table

alter table table_name add index index_name(column_list);

3. create index

create index index_name on table_name(column_list);

8.创建索引时的注意事项

1. 表中要包含聚集索引且唯一
2. 保证聚集索引最小，聚集索引越小，非聚集索引页包含的有效数据越多
3. 覆盖索引，索引中的列包含了数据处理中涉及到的列
4. 索引要适量，维护索引也有一定的开销

9.百万级别或以上的数据如何删除

1. 删除索引
2. 删除无用数据
3. 重建索引

10.前缀索引

对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快

MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

11.最左前缀匹配原则

MySQL 建立联合索引时，首先根据联合索引中最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。若某列索引没有精准命中，则后面的索引都会失效。

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式