索引

yiran5
91 阅读8分钟

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

相关指令

创建

# create语句
CREATE INDEX indexName ON tableName (columnName(length) [ASC|DESC]);
# alter语句
ALTER TABLE tableName ADD INDEX indexName(columnName(length) [ASC|DESC]);
# 建表ddl语句
CREATE TABLE tableName( columnName1 INT(8) NOT NULL, columnName2 ...., ....., INDEX [indexName] (columnName(length)) );

查询

SHOW INDEX FROM tableName;

image.png

简单的概述一下查询后，每个字段的含义：

  • ①Table：当前索引属于那张表。
  • ②Non_unique：目前索引是否属于唯一索引，0代表是的，1代表不是。
  • ③Key_name：当前索引的名字。
  • ④Seq_in_index：如果当前是联合索引，目前字段在联合索引中排第几个。
  • ⑤Column_name：当前索引是位于哪个字段上建立的。
  • ⑥Collation：字段值以什么方式存储在索引中，A表示有序存储，NULL表无序。
  • ⑦Cardinality：当前索引的散列程度，也就是索引中存储了多少个不同的值。
  • ⑧Sub_part：当前索引使用了字段值的多少个字符建立，NULL表示全部。
  • ⑨Packed：表示索引在存储字段值时，以什么方式压缩，NULL表示未压缩，
  • ⑩Null：当前作为索引字段的值中，是否存在NULL值，YES表示存在。
  • ⑪Index_type：当前索引的结构（BTREE, FULLTEXT, HASH, RTREE）。
  • ⑫Comment：创建索引时，是否对索引有备注信息。

删除

DROP INDEX indexName ON tableName;

在MySQL中并未提供修改索引的命令，也就说当你建错了索引，只能先删再重新建立一次

指定索引

SELECT * FROM table_name FORCE INDEX(index_name) WHERE .....;

FORCE INDEX关键字可以为一条查询语句强制指定走哪个索引查询，但要牢记的是：如果当前的查询SQL压根不会走指定的索引字段，哪这种方式是行不通的，这个关键字的用法是：一条查询语句在有多个索引可以检索数据时，显式指定一个索引，减少优化器选择索引的耗时。

但要注意：如果你对于你整个业务系统十分熟悉，那可以这样干。但如果不熟悉的话，还是交给优化器来自行选择，否则会适得其反！

索引类型

按数据结构

索引建立后也会在磁盘生成索引文件，那每个具体的索引节点该如何在本地文件中存放呢？这点是由索引的数据结构来决定的。比如索引的底层结构是数组，那所有的索引节点都会以Node1→Node2→Node3→Node4....这样的形式，存储在磁盘同一块物理空间中，不过MySQL的索引不支持数组结构，或者说数组结构不适合作为索引结构，MySQL索引支持的数据结构如下：

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

在上述的几种索引结构中，B+树和哈希索引是最常见的索引结构，几乎大部分存储引擎都实现了，对于后续两种索引结构在某些情况下也较为常见，但除开列出的几种索引结构外，MySQL索引支持的数据结构还有R+、R*、QR、SS、X树等结构。

在MySQL中创建索引时，其默认的数据结构就为B+Tree，如何更换索引的数据结构呢？如下：

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

也就是在创建索引时，通过USING关键字显示指定索引的数据结构（必须要为当前引擎支持的结构）。

同时索引会被分为有序索引和无序索引，这是指索引文件中存储索引节点时，会不会按照字段值去排序。那一个索引到底是有序还是无序，就是依据数据结构决定的，例如B+Tree、R-Tree等树结构都是有序，而哈希结构则是无序的。

按表字段

前面从索引的数据结构层次出发，可以将索引分为不同结构的类型，而从表字段的层次来看，索引又可以分为单列索引和多列索引，这两个称呼也比较好理解，单列索引是指索引是基于一个字段建立的，多列索引则是指由多个字段组合建立的索引。

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

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

多列索引的概念前面解释过了，不过它也有很多种叫法，例如：

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

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

按功能逻辑

  • 普通索引、唯一索引、主键索引、全文索引、空间索引

在主键字段上建立的索引被称为主键索引，非主键字段上建立的索引一般被称为辅助索引或、二级索引或次级索引，

全文索引类似于ES、Solr搜索中间件中的分词器，或者说和之前常用的like+%模糊查询很类似，它只能创建在CHAR、VARCHAR、TEXT等这些文本类型字段上，而且使用全文索引查询时，条件字符数量必须大于3才生效。

空间索引这玩意儿其实用的不多，至少大部分项目的业务中不会用到，想要弄清楚空间索引，那么首先得知道一个概念：GIS空间数据，GIS是什么意思呢？是地理信息系统，这是一门新的学科，基于了计算机、信息学、地理学等多科构建的，主要就是用于管理地理信息的数据结构，在国土、规划、出行、配送、地图等和地理有关的项目中，应用较为频繁。

按存储方式

  • 聚簇索引：也被称为聚集索引、簇类索引(逻辑上连续且物理空间上的连续)
  • 非聚簇索引：也叫非聚集索引、非簇类索引、二级索引、辅助索引、次级索引(逻辑上连续，物理空间上不连续)

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

不过一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引，其他字段上建立的索引都属于非聚簇索引，或者称之为辅助索引、次级索引。但也不要走进一个误区，虽然MySQL默认会使用主键上建立的索引作为聚簇索引，但也可以指定其他字段上的索引为聚簇索引，一般聚簇索引要求索引必须是非空唯一索引才行。

MySQL其他索引的创建使用方式

唯一索引

唯一索引在创建时，需要通过UNIQUE关键字创建：如下：

sql
 代码解读
复制代码
-- 方式①
CREATE UNIQUE INDEX indexName ON tableName (columnName(length));

-- 方式②
ALTER TABLE tableName ADD UNIQUE INDEX indexName(columnName);

-- 方式③
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  UNIQUE INDEX [indexName] (columnName(length))  
);

主键索引

前面聊到过，主键索引其实是一种特殊的唯一索引，但主键索引却并不是通过UNIQUE关键字创建的，而是通过PRIMARY关键字创建：

sql
 代码解读
复制代码
-- 方式①
ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);

-- 方式②
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  PRIMARY KEY [indexName] (columnName(length))  
);

全文索引

   全文索引和其他索引不同，首先如果你想要创建全文索引，那么MySQL版本必须要在5.7及以上，同时使用时也需要手动指定，一起来先看看如何创建全文索引，此时需要使用FULLTEXT关键字：

sql
 代码解读
复制代码
-- 方式①
ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);

-- 方式②
CREATE FULLTEXT INDEX indexName ON tableName(columnName);

引用

juejin.cn/post/714760…

avatar
文章
阅读
粉丝