索引就是来帮助表来快速检索数据的。

创建索引

（普通索引的创建方式）：

  1. create

    create index indexName on tableName (columnName(length) [asc|desc]);

  2. alter

    alter table tableName add index indexName (columnName(length) [asc|desc]);

  3. ddl （建表时DDL语句）

    create table tableName(
    columnName1 varchar(10) not null,
  .....,
  index [indexName] (columnName(length))    
);

MySQL中没有修改索引的命令。

删除索引：

drop index indexName on tableName;

索引也是数据的一种，和数据库表一样，索引也会在磁盘生成本地文件。

索引在磁盘文件以何种方式存储由数据库的存储引擎决定，不同存储引擎下的索引文件，保存在本地的格式也不相同。

创建索引的时机在表数据越少时越好，如果在表数据有1000w再去创建索引，会在这个表数据上，重新在磁盘中创建新的本地索引文件（.ibd文件），如果表中有1000w条数据，在创建索引时就需要将索引字段上的1000w个值全部拷贝到本地索引文件中，同时做好排序与表数据产生映射关系。

索引的分类

先列一些大家都听说过的索引称呼：聚簇索引、非聚簇索引、唯一索引、主键索引、联合索引、全文索引、单列索引、多列索引、复合索引、普通索引、二级索引、辅助索引、次级索引、有序索引、B+Tree索引、R-Tree索引、T-Tree索引、Hash索引、空间索引、前缀索引......。

索引的数据结构由存储引擎决定的，Innodb引擎默认的索引数据结构是B+Tree。

MySQL索引支持的数据结构如下：

  • B+Tree类型：MySQL中最常用的索引结构，大部分引擎支持，有序。
  • Hash类型：大部分存储引擎都支持，字段值不重复的情况下查询最快，无序。
  • R-Tree类型：MyISAM引擎支持，也就是空间索引的默认结构类型。
  • T-Tree类型：NDB-Cluster引擎支持，主要用于MySQL-Cluster服务中。

更换索引数据结构：

CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]) USING HASH;

上面从索引数据结构上来看，可以分为不同的类型。

从表字段来看，索引又可以分为单列索引（由一个字段建立的索引），多列索引（由多个字段建立的索引）。

单列索引也会分为很多类型，比如：

  • 唯一索引：指索引中的索引节点值不允许重复，一般配合唯一约束使用。
  • 主键索引：主键索引是一种特殊的唯一索引，和普通唯一索引的区别在于不允许有空值。
  • 普通索引：通过KEY、INDEX关键字创建的索引就是这个类型，没啥限制，单纯的可以让查询快一点。
  • .....还有很多很多，只要是基于单个字段建立的索引都可以被称为单列索引。

多列索引也有很多叫法：

  • 联合索引、复合索引、组合索引、 多值索引

不过在使用多列索引时要注意：当建立多列索引后，一条SELECT语句，只有当查询条件中了包含了多列索引的第一个字段时，才能使用多列索引，下面举个栗子。

-- 无法使用多列索引的SQL语句
SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18";

-- 能命中多列索引的SQL语句
SELECT * FROM `zz_user` WHERE name = "竹子" AND id = 6;

全文索引：

InnoDB引擎在Mysql5.7之后才支持的。

全文索引和ES搜索中间件的分词器类似。它只能创建在char、varchar、text等文本字段上，而且使用全文索引，查询字符数必须要大于3才有效。

空间索引：

在MySQL中总共支持GEOMETRY、POINT、LINESTRING、POLYGON四种空间数据类型，而空间索引则是基于这些类型的字段建立的，也就是可以帮助我们快捷检索空间数据。

从存储方式的层面来看，MySQL的索引主要可分为两大类：

  • 聚簇索引：也叫聚集索引、簇类索引
  • 非聚簇索引：也叫二级索引、辅助索引、次级索引

在所这两类索引存储的区别之前，先回忆一下数组和链表的区别：

  • 数组：物理空间上的连续，存放的元素有序的存放在同一块内存区域中。
  • 链表：逻辑上的连续，存放的元素可能不在一块连续的内存中，元素之间以指针连接。

聚簇索引和非聚簇索引和上面的区别也大致相同：

  • 聚簇索引：逻辑上连续且物理空间上的连续
  • 非聚簇索引：逻辑上连续，物理空间上不连续。

因为索引大部分使用B+Tree结构存储,所以在磁盘中的数据结构是以树结构存放的，这里的连续不是指索引节点连续，而是指索引数据和表数据，也就是说聚簇索引中，索引数据和表数据在磁盘中的位置是一起的。而非聚簇索引则是分开的，索引数据和表数据之间用物理地址的方式维护两者关系。

一张表中只允许一个聚簇索引，一般都会选择主键作为聚簇索引，其他字段上建立的索引都属于非聚簇索引。

MySQL默认使用主键作为聚簇索引，但是也可以选择其他字段上的索引作为聚簇索引，一般聚簇索引要求索引必须是非空唯一索引才行。

其实就算表中没有定义主键，InnoDB中会选择一个唯一的非空索引作为聚簇索引，但如果非空唯一索引也不存在，InnoDB隐式定义一个主键来作为聚簇索引。

具体索引的创建

开头三种创建的是普通索引，但不管何种类型的索引都可以通过上面三种方式创建。

创建唯一索引：

需要通过 unique 关键字创建

-- 方式1
create unique index on tableName (columnName(length));

-- 方式2
alter table tableName add unique index indexName(ColumnName);

-- 方式3
create table tableName(
    columnName int(10) not null,
  ...,
    unique index [indexName] (columnName(length))  
)

如果在已有表的基础上创建唯一索引时，如果要添加的索引字段存在相同值是，这是唯一索引是无法创建的。

创建主键索引：

主键索引其实是一种特殊的唯一索引，但主键索引却不是同构unique关键字创建的，而是通过primary 关键字创建的。

-- 方式1
alter table tableName add primary key indexName(columnName);

-- 方式2
create table tableName(
    columnName tinyint not null,
  ...,
  primary key [indexName] (columnName(length))
)

一般主键索引都会在建表的DDL语句中创建，不会在表已经建立后再创建。

使用我们考虑如何去使用索引，在一条SELECT语句来到MySQL时，会经历优化器优化的过程，而优化器则会自动帮咱们选择一个最合适的索引查询数据。当然，前提是查询条件中涉及到了索引字段才行。

你不想让优化器自动选择，也可以手动通过FORCE INDEX关键字强制指定。

创建全文索引：

创建全文索引必须在mysql5.7及以上的版本。

需要使用 fulltext 关键字创建：

-- 方式1
alter table tableName add fulltext index indexName(columnName);

-- 方式1
create fulltext index indexName on tableName(columnName);

注意：

  1. 创建全文索引的字段类型必须为char、varchar、text。
  2. 如果想要创建出的全文索引支持中文，需要在最后指定解析器：with parser ngram。

....

创建联合索引：

之前都是在单个字段上添加索引，而联合索引的意思是可以在多个字段上建立索引。

-- 方式1
create index indexName on tableName (columnName1,columnName2,columnName3,...);

-- 方式2 
alter table tableName add index indexName (columnName1,columnName2,columnName3,...);
  • 你可以使用INDEX关键字，让多个列组成一个普通联合索引
  • 也可以使用UNIQUE INDEX关键字，让多个列组成一个唯一联合索引
  • 甚至还可以使用FULLTEXT INDEX关键字，让多个列组成一个全文联合索引
  • .......

但是前面也提过，SELECT语句的查询条件中，必须包含组成联合索引的第一个字段，此时才会触发联合索引，否则是无法使用联合索引的。

索引带来的利害关系

  • 建立索引会生成本地磁盘文件，需要额外的存储空间来存储这个文件，，磁盘占用会变高。
  • 写入数据时，需要额外维护索引结果，、增删改时需要额外操作索引。
  • 写入数据时，维护索引需要额外的时间开销，执行写效率会降低，性能会下降。

整体来说，索引带来的优势大于劣势。也正因为索引存在弊端，因此索引不是越多约好，合理建立索引才是最佳选择。

主键索引存在的陷阱：

在大多数数据库中，主键都使用自增ID，自增id不会重复，保证了唯一性没有错，但是使用随机的uuid同样不会出现重复，为什么不适用uuid呢？

前面说到：一张表默认情况下会，会将主键索引以聚簇的形式存在磁盘中，聚簇索引在存数据时，会将索引数据和表数据一起存放，同时MySQL默认的索引结构是B+Tree ，也代表着索引节点的数据是有序的。

如果插入一个元素，这个元素是主键是无序的，经过计算这个数据应该应该排在第一个位置，如果前面有两个节点，就代表这两个节点需要向后移动，腾出第一个位置给它，从而保证索引的有序性。

由于主键是聚簇索引，当后续节点需要移动时，也代表着还需要移动表数据，如果主键是无序的，那代表着几乎每次插入都可能导致树结构要调整。

使用自增id就没有这个问题， 因为每次新插入的数据都会放到最后。

联合索引存在的矛盾：

为了在多条件查询是的效率更好，一般都会对多个字段建立联合索引，但是，如果在查询条件时没有包含联合索引的第一个字段。那联合索引时无法生效的。例如给（name,id,age） 这三个字段建立了联合索引，在查询时：

-- 这条索引是会被命中的。
select * from zzz where id = '' and age = 20;

因为上面要查询的条件并没有包含联合索引的第一个字段。

所以在使用联合索引时，确保建立的联合索引命中率能更高。

前缀索引存在的弊端：

利用一个字段的前N个字符建立的索引，以这种方式创建的索引被称为前缀索引，相比较与使用一个完整字段创建索引，前缀索引能够更加节省存储空间。当数据越多时，带来的优势更明显。

不过前缀索引虽然带来了节省空间的好处，但也正由于其索引节点中，未存储一个字段的完整值，所以MySQL也无法通过前缀索引来完成ORDER BY、GROUP BY等分组排序工作，同时也无法完成覆盖扫描等操作。

全文索引存在的硬伤：

.....

唯一索引存在的快慢问题：

唯一索引有个很大的好处就是，在查询数据时效率会比普通索引快。比如普通索引在查询到一条符合条件的数据后，此时会继续走完整个索引树，因为可能会存在多条字段值相同的 值。

但如果要查询的字段是唯一索引，当查到数据后就会立马停下检索。

因此唯一索引查询数据时会比普通索引块上一截。但插入数据就不同了，唯一索引在插入数据时会先检查一遍是否有相同值的数据。但普通索引就不需要考虑这个问题。所以普通索引在插入数据时会更快一些。

哈希索引的致命问题：

哈希索引说的是使用哈希数据类型的索引。哈希索引查询非常块。

但是哈希索引的致命问题在于无序，也就是哈希索引的字段无法做排序、分组等工作。

正确使用索引

在创建索引时，应该遵循哪些原理原则，才能正确合理创建索引呢？

建立索引遵守的原则：

  • 频繁用作查询条件的字段应酌情考虑为其建立索引
  • 表的主外键、必须建立索引、因为能很大程序提升表查询的性能
  • 建立索引的字段，值数据不应该太长，如果较长的字段需要建立索引，可以选择前缀索引
  • 建立联合索引时，应该遵循最左前缀原则，将字段之间按照优先级顺序组合
  • 经常根据范围取值、排序、分组、的字段应建立索引，因为索引有序，能加快排序时间
  • 尽量使用联合索引替代单值索引，联合索引查询效率比单值索引效率高

出了需要遵循的点还有要注意的点：

  • 值经常会增删改的字段，不适合建立索引，因为每次改变之后需要维护索引结构
  • 一个字段存在大量的重复值时，不适合建立索引，例如性别
  • 索引不能参与计算，经常用作函数查询的字段不适合建立索引
  • 一张表的索引不是越多越好，一般在3，最多不能超过5
  • 当表数据量小的时候不适合添加索引，因为数据量不大，维护索引反而开销更大
  • 索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

索引失效的情况

  1. 使用了OR
  2. 使用了反方向的操作符
  3. 联合索引的情况下违反了最左前缀原则
  4. 使用索引字段参与计算
  5. 使用索引字段被用于函数计算
  6. 字符类型的索引查询时不带引号
  7. 模糊查询时以%开头

在MySQL中还有一种特殊情况会使索引失效，如果走索引查询的行数占了全表的30%时，MySQL会默认放弃使用索引查询，转而使用全表扫描的方式检索数据，因此这种情况下走索引的顺序磁盘IO，反而不一定有全表的随机磁盘IO快。

回表问题

索引查询时的回表问题：一条SQL在内部查询了两次才能数据。

例如以id字段建立了主键索引、又以name字段建立了普通索引。

当执行一个查询语句：select * from xxx where name = 'aaa'

此时mysql会默认使用id主键为聚簇索引；将表数据和主键索引存放在同一个文件中，也就是主键索引的每个索引节点，都直接对应着行数据，而name字段建立的索引，其索引节点存放的则是指向聚簇索引的id值。

首先会走name字段的索引，然后找到对应的id值。然后拿着id值，再走id字段的主键索引，最终得到一条完整的数据。

使用select * from会造成回表。

索引覆盖

name和sex都建立了索引。

比如 select * from xxx where name = 'aaa' and sex = 'nan' 这种情况下会查询回表

再改为select name,sex from xxx where name = 'aaa' and sex = 'nan' 这种不会查询回表。

就是要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，这中情况称为索引覆盖。

explain执行分析工具

下面只说了部分信息：

id：当执行分析的id，数值越大执行的优先级就越高

key：用到的索引名字

type：目前执行计划查询的类型：

  • all：表示目前走了全表查询、未命中索引、或索引失效
  • system：表示当前要查询的表中只有一条数据
  • const:表示当个SQL语句的查询条件中，可以命中索引
  • ref：表示使用了普通索引查询
  • index：表示使用了辅助索引查询

extra：这里记录了额外的一些索引使用信息：

  • using index：表示目前使用了覆盖索引查询
  • using where :表示目前使用了where子句查询，通常表示没有没有使用索引

select_type：当前sql的查询类型：

  • simple：表示是一个简单查询
  • primary：复杂查询的外层查询
  • ....

....

参考文章：https://juejin.cn/post/7147609139974242317

文章目录