MySQL索引简述

MySQL索引

概述

一种帮助MySQL提高查询效率的数据结构

• 优点：大大加快数据查询速度
• 缺点：1、维护索引需要耗费数据库资源 2、索引需要占用磁盘空间 3、当对表的数据进行增删改时，速度会收到影响

索引数据建构

• 二叉树
• 红黑树
• Hash表
• B-Tree

索引分类

1. 主键索引
   • 设定为主键后数据库会自动建立索引，innodb为聚簇索引
2. 普通索引
   • 即一个索引只包含单列，一个表可以有多个单列索引
3. 唯一索引
   • 索引列的值必须唯一，但允许有空值
4. 复合索引
   • 即一个索引包含多个列
5. Full Text 全文索引(5.7之前，只可以用于MYISAM引擎)
   • 全文素朴类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引中插入重复值和空值，全文索引可以在CHAR、VARCHAR、TEXT类型列上创建

实际操作

# 对表中索引进行查询
show index from user;

# 这建表的时候设置主键，自动建立主键索引
create table user(id varchar(20) primary key,name varchar(20));

# 普通索引 创建表之后创建  create index 索引名称 on 表名(列名)
create index name_index on user(name)

# 普通索引 创建表时 除了主键id外设置name为普通索引
create table user(id varchar(20) primary key,name varchar(20),key(name));

# 唯一索引 创建表时创建
create table user(id varchar(20) primary key,name varchar(20),unique(name));

# 唯一索引 创建表之后
create unique index name_index on user(name);

#复合索引 建表时
create table user(id varchar(20) primary key,name varchar(20),age int(10),key(name,age));

#复合索引 创建表之后
create index name_age_index on user(name,age)

复合索引有两个原则

1. 最左前缀原则
2. MySQL引擎在查询时为了更好的利用索引，查询过程中会动态调整查询字段顺序以便利用索引

所以在使用复合索引时，若查询字段全部包括在索引中，且查询字段有索引的第一位，即可使用索引

Hash

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引都要比B+数索引更加高效
• 仅能满足“=”。“IN”，不支持范围查询
• hash冲突问题

B Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

B+Tree

• 非叶子节点不存储data，只存储索引，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

聚簇索引与非聚簇索引

• 聚簇索引：将数据存储与索引放到了一起，索引结构的叶子节点保存了行数据
• 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

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

聚簇索引的优势

1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多行数据，访问同一页不同行记录时，已经把叶加载到Buffer中，再次访问时会在内存中完成访问
2. 辅助索引的叶子节点，存储主键值，而不是数据存放的地址。好处是当行数据发生变化时，索引树的叶子节点也需要分裂变化或者再需要进行io操作时，可避免对辅助索引的维护

MyLSAM存储引擎（非聚集 索引文件和数据文件是分离的）

通过MYI文件，快速查找索引，然后找到地址，通过MYD文件的地址查找到数据

InnoDB索引实现

• 表数据文件本身就是按B+Tree组织的一个索引结构文件
• 聚集索引-叶节点包含了完整的数据记录

将数据和主键存储到同一个文件中

非主键索引

非主键索引叶子节点中有主键值

在InnoDB中推荐自建整形自增主键，因为若无主键，mysql则会自己寻找没有重复的列

B Tree 与 B+Tree的区别

• B Tree叶子节点之间没有针，就不能更好的支持范围查找
• B+Tree将所有的data都放在叶子节点中，非叶子节点有冗余的数据，但是相比于B Tree在相同的大小，B+Tree可以存放更多的索引 k