MySQL索引

一.索引是什么

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。大家可以自己试一下有索引和没索引的区别，两者的速度都不是在一个量级上。索引是极大的加快查询数据库的速度。

当然，索引这么快也是有代价的，创建索引后会生成索引树，它是占磁盘空间的。磁盘IO是很耗时间的，因此我们在创建索引的时候也要注意，在某系特定情况下索引不是最优解。

二.索引的底层

1.索引底层的数据结构

索引底层选择的是B+树。B+树的B树的一个变种，在B树的基础上在叶子节点增加了一个链指针，并且非叶子节点也不存储值了，只起到指向的作用，所有的值都存储在叶子节点上。

1.1 为什么要选择B+树？

因为其可有效的的控制树高，并且在相同树高的情况下，B+树查找任意元素的时间复杂度都是一样的，性能均衡。

1.2 为什么不选其他的数据结构

其他数据结构比如说：哈希和二叉搜索树。哈希的时间复杂度是很优秀的，可以达到 O(1) 的级别，但是不支持范围查找。二叉搜索树支持范围查找，但是无法控制树高，在最坏的情况下就成了一个单链表了，时间复杂度是 O(N) 的。由于数据库的数据都是存在硬盘上的，每访问一次节点都会发生磁盘IO，这导致数据库的性能下降，也不合适。

综上，B+比较符合我们的需求。

2.页

页（Pages）是 InnoDB 中管理数据的最小单元，是内存与磁盘交互的最小单元，默认大小是16KB。InnoDB是MySQL中的存储引擎。不管有没有数据，都会开一个页。

MySQL中的页有很多种类型，这里主要说索引页（数据页）。

3.B+树的应用

非叶子节点全部都是索引页，索引页保存主键的值和子节点的引用。叶子节点是数据页，存储的是具体的数据，页与页之间通过页号连接。

三.索引分类

1.主键索引

当表中定义一个主键时，会自动创建一个主键索引，索引的值是主键列的值。如果一个表没有定义主键，我们可以添加一个自增列，因为索引可以大幅提高查询速度，诱惑很大。

下面时创建方法：

# 方法一：
create table t1(
  id int primary key auto_increment,
  name varchar(32)
);

# 方法二：
create table t1(
  id int,
  name varchar(32),
  primary key(id)
);

# 方法三：
create table t1(
  id,int,
  name varchar(32)
);
alter table t1 add primary key(id);
alter table t1 modify id int auto_increment;

2.普通索引

最基本的索引类型，没有唯一性限制。可为多列创建组合索引，称为复合索引。

下面时创建方法：

# 方法一：
create table t1(
  id int,
  name varchar(32),
  index(id)
);

# 方法二：
create table t1(
  id int,
  name varchar(32)
);
alter table t1 add index(id);

# 方法三：可对索引命名，替换index_name
create table t1(
  id,int,
  name varchar(32)
);
create index index_name on t1(id);

复合索引的创建与普通索引相似，只是多了几列而已：
 

# 方法一：
create table t1(
  id int,
  name varchar(32),
  class_id int,
  index(id,class_id)
);

# 方法二：
create table t1(
  id int,
  name varchar(32),
  class_id int
);
alter table t1 add index(id,class_id);

# 方法三：可对索引命名，替换index_name
create table t1(
  id,int,
  name varchar(32)
);
create index index_name on t1(id,class_id);

3.唯一索引

当表上定义一个unique时，自动创建唯一索引。这个与唯一约束相似，比较好理解。

唯一索引的创建与主键索引相似：

# 方法一：
create table t1(
  id int primary key auto_increment,
  name varchar(32)
);

# 方法二：
create table t1(
  id int,
  name varchar(32),
  unique(id)
);

# 方法三：
create table t1(
  id,int,
  name varchar(32)
);
alter table t1 add unique(id);

4.全文索引

基于文本列上创建，以加快对这些列的查询操作。用于全文搜索，仅MyISAM和InnoDB引擎支持。

5.聚集索引

如果没有为表定义 primary key, InnoDB使用第⼀个 unique 和 not null 的列作为聚集索引。如果表中没有 primary key 或合适的unique 索引，InnoDB会为新插入的行生成⼀个行号并用6字节的 ROW_ID 字段记录， ROW_ID 单调递增，并使用 ROW_ID 做为索引。

6.非聚集索引

InnoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询。

7.数据覆盖

当⼀个select语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时
就可以直接返回数据，而不用回表查询，这样的现象称为索引覆盖。

四.查看索引

一共有三种方法。

方法一：

show keys from t1;

查看的结果是这样的：

方法二：

desc t1;

查看的结果是这样的：

方法三：

show index from t1;

查看的结果是这样的：

如果我们给name添加一个普通索引，那么就会查出下列的结果：

五.删除索引

1.主键索引的删除

直接使用alter table t1 drop primary key;会报错，显示让我们先把自增给删掉。

那我们就先把自增删掉：

alter table t1 modify id int;

然后再删除就可以了：

alter table t1 drop primary key;

2.其他索引的删除

其他索引的删除比较简单，直接删除即可。

alter table 表名 drop index 索引名;