进学阁

业精于勤荒于嬉，行成于思毁于随

0%

索引

发表于 更新于 分类于

MySQL官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

  • 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储。
  • 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。

索引涉及的理论知识：二分查找法、Hash和B+Tree。

理论知识补充

二分查找法

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。

  • 首先定位left和right两个指针
  • 计算(left+right)/2
  • 判断除2后索引位置值与目标值的大小比对
  • 索引位置值大于目标值就-1，right移动；如果小于目标值就+1，left移动

举个例子，下面的有序数组有17 个值，查找的目标值是7，过程如下：第一次查找：

第二次查找：

Hash结构

Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key查找value值，也就是单个key查询，或者说等值查询。其结构如下所示：

从上面结构可以看出，Hash索引可以方便的提供等值查询，但是对于范围查询就需要全表扫描了。Hash索引在MySQL 中Hash结构主要应用在Memory原生的Hash索引 、InnoDB 自适应哈希索引。InnoDB自适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+Tree索引再创建一个哈希索引，使得内存中的 B+Tree 索引具备哈希索引的功能，即能够快速定值访问频繁访问的索引页。InnoDB自适应哈希索引：在使用Hash索引访问时，一次性查找就能定位数据，等值查询效率要优于B+Tree。自适应哈希索引的建立使得InnoDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外InnoDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

show engine innodb status \G; show variables like ‘%innodb_adaptive%’;

B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。B-Tree结构：

  • 索引值和data数据分布在整棵树结构中
  • 每个节点可以存放多个索引值及对应的data数据
  • 树节点中的多个索引值从左到右升序排列

B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

B+Tree结构：

  • 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
  • 叶子节点包含了所有的索引值和data数据
  • 叶子节点用指针连接，提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

索引基础

索引优缺点

优点：

- **<font style="color:rgb(1, 1, 1);">索引减小了需要扫描的数据量，从而大大加快数据的检索速度（创建索引的最主要的原因）</font>**
- **<font style="color:rgb(1, 1, 1);">可以加速表与表的连接</font>**
- **<font style="color:rgb(1, 1, 1);">可以显著的减少查询中分组和排序的时间</font>**
- **<font style="color:rgb(1, 1, 1);">索引可以帮助服务器避免排序和创建临时表</font>**
- **<font style="color:rgb(1, 1, 1);">索引可以将随机IO变成顺序IO</font>**

索引既然有这么多优点，那为什么不对表中每个列都建一个索引呢，这样不是更加能提升性能吗，实际上这是不可取的，索引虽然有诸多优点，但是也有很多缺点

缺点：

  • 对表中的数据进行增、删、改的时候，索引也要动态的维护，降低了数据的写入速度
  • 随着数据量的增加，创建索引和维护索引要耗费时间也会越来越长，影响性能
  • 索引的存储需要占物理空间，每一个索引都要占用一定的物理空间

索引分类

MySQL索引的类型其实只有五种，但是我们经常会听到很多种不同的索引，那其实是在不同维度划分的类型：

  • 存储结构维度划分

B Tree索引、Hash索引、B + Tree索引

  • 应用层次维度划分

普通索引、唯一索引、主键索引、复合索引、全文索引，空间索引

  • 索引键值类型维度划分

主键索引、辅助索引（二级索引）

  • 数据存储和索引键值逻辑关系维度划分

聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

  • 索引组成维度划分

组合索引（复合索引）、单一索引

因为只是分类的维度不同那我们以普通索引、唯一索引、主键索引、复合索引，空间索引为例

普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。是辅助索引的一种。对应逻辑关系中的非聚集索引。索引列单独保存，查询的时候根据索引查到数据后再从主键索引中将完整的数据取出，这个过程称为回表查询。创建普通索引的方法如下：

1
2
3
CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

唯一索引

与”普通索引”类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。创建唯一索引的方法如下：

1
2
3
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。对应逻辑关系划分中的聚集索引。索引的数据和索引保存在一起

创建主键索引的方法如下：

1
2
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

复合索引

复合索引是普通索引的一种，单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。创建组合索引的方法如下：

1
2
3
CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：

  • 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
  • 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。
  • 复合索引在生效时OrderBy中有复合索引中的字段会增加查询速度
  • 额外介绍一下覆盖索引：当查询结果需要的字段都在复合索引中就不需要回表查询

全文索引

查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。创建全文索引的方法如下：

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from user where match(name) against(‘aaa’);

全文索引使用注意事项：

  • 全文索引必须在字符串、文本字段上建立。
  • 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）
  • 全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
  • 全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

select * from user where match(name) against(‘a*’ in boolean mode);

索引创建准则

基于以上索引的介绍，我们知道索引优缺点都很明显，我们不能在表数据中所有的列都添加索引，需要根据具体场景选择创建索引的列与类型。那么具体应该在那些列中添加索引，那些列中不能添加索引呢？

  • 能创建索引的列
    • 主键索引，在MySQL中，主键列会默认的当成唯一性索引
    • 在业务场景中被【当成条件查询的列】创建索引，可以提高查询效率
    • 外键索引，比如需要【用于JOIN的列】创建索引，可以提高连接的速度
    • 由于索引是已经排序的，所以在经常【用于范围查询的列】和需要【排序的列】创建索引，可以避免排序，提高查询效率
  • 不能创建索引的列
    • 经常用于计算的列
    • 数据值很少或者大量重复的列
    • 大字段的列
    • 经常修改的列
    • 很少使用的字段

:::tips
以上几种情况的列，一般不建议创建索引，非但不能提高查询速度，反而增加索引后提高了数据的维护时间成本和空间成本。

:::

索引创建与删除

创建索引

索引的创建方式有三种：建表时创建索引，已存在的表上直接创建索引，已存在的表上新增列并创建索引

建表时创建索引：
1
2
3
4
5
CREATE TABLE 表名 (
    字段名1  数据类型 [完整性约束条件…],
    字段名2  数据类型 [完整性约束条件…],
    [NORMAL | UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
    [索引名]  (字段名[(长度)]  [ASC | DE
已存在的表上直接创建索引
1
CREATE  [NORMAL | UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名  ON 表名 (字段名[(长度)]  [ASC | DESC])
已存在的表上新增列并创建索引（修改表结构）
1
ALTER TABLE 表名 ADD  [NORMAL | UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)]  [ASC | DESC]) ;

备注：

  • NORMAL | UNIQUE | FULLTEXT | SPATIAL

可选参数，Normal 普通索引，Unique 唯一索引，Full Text 全文索引，SPATIAL 空间索引

  • INDEX | KEY

同义词，作用相同，用来指定创建索引

  • ASC | DESC

指定升序或降序的索引值存储

删除索引

1
DROP INDEX 索引名 ON 表名字;

相关SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
//查看表结构
desc table_name;

//查看生成表的SQL
show create table table_name;

//查看索引结构信息
show index from  table_name;

//查看SQL执行时间
set profiling = 1;
select * from user where id=1; 
show profiles;