【MySQL系列】-索引知多少

标签：index name INDEX 知多少索引 MySQL id 前缀

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到网站。

索引及其作用

索引（Index）是帮助 MySQL 高效获取数据的数据结构。索引的本质是数据结构。索引作用是帮助 MySQL 高效获取数据。通俗的说，索引就像一本书的目录，通过目录去找想看的章节就很快，索引也是一样的。

【MySQL系列】-索引知多少_二级索引

如果没有索引，MySQL在查询数据的时候就需要从第一行数据开始一行一行数据对比，只能扫描完整个表找到要查询的数据，表的数据越多需要花费的时间越多。如果表中有相关列的索引，MySQL可以快速确定在数据文件中间查找的位置，而无需查看所有数据，这比按顺序读取每一行要快得多。

索引常用的数据结构有BTREE、HASH、RTREE等等，其中数BTREE最为常见。

索引的分类

索引分为聚集索引和二级索引。

聚集索引

InnoDB引擎中使用了聚集索引(Clustered index)，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该 B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。一般来说，在MySQL中聚集索引和主键是一个意思。
聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行
记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。
如果没有设置主键的话，MySQL默认会创建一个隐含列row_id作为主键。

二级索引

二级索引(Secondary Index，也称辅助索引、非聚集索引)是InnoDB引擎中的一类索引，聚集索引以外的索引统称为二级索引，包括唯一索引、联合索引、全文索引等等。二级索引并不包含行记录的全部数据，二级索引上除了当前列以外还包含一个主键，通过这个主键来查询聚集索引上对应的数据。当查询除索引以外的其他数据时，由于数据不在索引上就需要通过主键来找到完整的行记录，这就是回表。

针对二级索引MySQL提供了一个优化技术，索引覆盖(covering index)。即从辅助索引中就可以得到查询的记录，就不需要回表再根据聚集索引查询一次完整记录。使用索引覆盖的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作，但是前提是要查询的所有列必须都加了索引。

唯一索引

唯一索引(Unique Index)要求列的数据必须是唯一的，唯一索引具有唯一性约束，在插入数据时，如果有列中有相同的数据就会报错。唯一索引可以允许多个列的值为NULL，如果列是字符串类型的话，空字符串值只能有一个。

全文索引

全文索引(Full-Text Index)只有在MyISAM和InnoDB存储引擎中支持，全文索引只能创建在基于文本的列上，例如CHAR、VARCHAR、TEXT类型。全文索引不支持索引前缀，即使设置了索引前缀也不会起作用。

全文索引采用的是倒排索引(inverted index)设计，倒排索引就是将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字时，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。为了支持邻近搜索，还存储每个单词的位置信息，作为字节偏移量。

MySQL 从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符(delimiter)的语言，如中文、日语、韩语等。

全文索引辅助表

创建一个db_test数据库，并创建一个users表，users表结构如下

【MySQL系列】-索引知多少_MySQL 索引_02

在name字段上创建全文索引：

ALTER TABLE `users` ADD FULLTEXT INDEX `idx_name` (`name`);

然后查看INNODB_SYS_TABLES中db_test数据库信息

SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'db_test/%';

【MySQL系列】-索引知多少_全文索引_03

当全文索引创建时就会创建一组辅助索引表，前六个表就是辅助索引表。辅助索引表以 FTS_ 开头，以index_# 结尾，每个辅助索引表的表名都和全文索引所在表的table_id的十六进制值关联。比如db_test/users的table_id是170，170对应的十六进制是0xaa，辅助索引表的表名就用aa作为其表名的一部分，以便和db_test/users表关联。
全文索引的index_id也可以通过辅助索引表的表名获取，拿第一个辅助索引表db_test/FTS_00000000000000aa_00000000000000fb_INDEX_1举例，fb就是index_id的十六进制表示，换算成十进制是251，所以index_id=251.可通过以下SQL语句验证index_id：

SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE index_id = 251;

【MySQL系列】-索引知多少_MySQL 索引_04

db_test/users表的table_id=170，通过index_id = 251查询到的table_id也是170，并且索引名称也是我们创建的idx_name，所以这个index_id就是我们创建的全文索引的id。

多列索引

多列索引(Multiple-Column Index，又称联合索引、复合索引)顾名思义就是几个列共同组成一个索引。多列索引最多由16个列组成。多列索引遵守最左前缀原则。

最左前缀原则就是在查询数据时，以最左边的列为基准进行索引匹配。例如，有个索引mul_index(col1, col2, col3)，在进行索引查询的时候，只有(col1)、(col1, col2)和(col1, col2, col3)这三种组合才能使多列索引mul_index(col1, col2, col3)生效。就是说col1只能在查询时被用到，这个索引就能被用到，索引在创建多列索引时一定要将查询最频繁的列放到最左边。

空间索引

MyISAM、InnoDB、NDB和ARCHIVE存储引擎都支持空间索引(Spatial Indexe)，但是要求列必须是POINT和GEOMETRY相关类型。但是，对空间列索引的支持因引擎而异，可根据以下规则使用空间列上的空间和非空间索引。

空间索引在空间列上有以下特性：

只有MyISAM和InnoDB可以使用，如果在创建时指定其他存储引擎会报错
索引列必须是NOT NULL
不允许使用索引前缀

非空间索引在空间列上有以下特性：

允许用于除ARCHIVE存储引擎之外的任何支持空间列的存储引擎。
列值可以为NULL，除非是唯一索引。
非空间索引在空间列时，除了列的类型是POINT之外，在创建索引的时候都要指定索引前缀并且索引前缀长度是以字节为单位的。
非空间索引在空间列的数据结构取决于存储引擎，目前使用的是BTREE。
在InnoDB、MyISAM和 MEMORY存储引擎中允许空间列的值为NULL。

空间索引主要用于列类型是地理位置或者坐标之类的列上的，空间索引主要使用的是R-Tree。

自适应哈希索引

自适应哈希索引(Adaptive Hash Index)是InnoDB表的优化，可以通过在内存中构造哈希索引来加速使用 = 和 IN 运算符的查找。InnoDB 存储引擎内部自己去监控索引表，如果监控到某个索引频繁使用，那么就认为是热数据，然后内部就会自动创建一个 hash 索引。从某种意义上说，自适应哈希索引在运行时对MySQL进行配置，以利用充足的主存，这样更接近主存数据库的架构。这个特性是由innodb_adaptive_hash_index参数控制的，默认是开启的。
可以通过以下命令查看自适应hash索引的使用状况

show engine innodb status

【MySQL系列】-索引知多少_全文索引_05

status字段内容很长，有兴趣的可以自己试试看下，里面有这么一段：

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 195, seg size 197, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 2 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
Hash table size 2267, node heap has 0 buffer(s)
Hash table size 2267, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

通过 hash searches: nonhash searches 可以大概了解使用哈希索引后的效率。

索引的增删改查

新增索引

新增索引有三种方式：

使用create index 语句
使用alter 语句
在CREATE TABLE的时候创建索引

前两种方式都是在创建好表以后再给表新增索引的，第三种是在创建表的同时创建索引

CREATE TABLE 创建索引

CREATE TABLE tableName(  
  id bigint(10) NOT NULL AUTO_INCREMENT ,   
  col_name  columnType,
  {INDEX} [index_name] [index_type] (key_part,...)
);

例如：

CREATE TABLE `test` (
`id`  int NOT NULL AUTO_INCREMENT ,
`name`  varchar(255) NULL ,
PRIMARY KEY (`id`),
INDEX `idx` (`name`) 
)

CREATE INDEX 创建索引

语法：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
}

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

带中括号[]的都是可选项，可写可不写，[=]表示等于号可要可不要

key_part:col_name [(length)] [ASC | DESC] ：表示列名、索引前缀长度、升序或降序
index_type：表示使用BTREE或HASH作为索引的数据结构
index_option:索引的可选项，包括索引类型、备注、PARSER、KEY_BLOCK_SIZE 等
algorithm_option：算法的选择，可选值为DEFAULT、INPLACE、COPY
lock_option：锁的选择，可选值为DEFAULT、NONE、SHARED(共享锁)、EXCLUSIVE(排它锁)

示例：

CREATE  INDEX index_name USING BTREE  ON account(amount DESC) COMMENT 'string' ALGORITHM  INPLACE LOCK  SHARED;

ALTER TABLE 创建索引

语法：

ALTER TABLE tbl_name ADD {UNIQUE  | FULLTEXT | SPATIAL} INDEX index_name(key_part,...) [index_option]

index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

示例：

-- 添加唯一索引
ALTER TABLE `account` ADD UNIQUE INDEX `uk` (`amount`) USING BTREE;

ALTER TABLE 和 CREATE INDEX 创建索引的区别：

ALTER 本身有修改的意思，所以可以对索引进行增删改，而CREATE只能创建索引
CREATE不能创建主键，ALTER可以
CREATE INDEX 可以指定索引算法ALGORITHM和LOCK，ALTER在添加索引的时候不能指定。

修改索引

修改索引是先删除之前的索引，然后重新添加

ALTER TABLE tableName DROP INDEX oldIndexName,ADD INDEX indexName(columns ...) USING BTREE;

删除索引

删除索引有两种方式：

ALTER TABLE tableName DROP INDEX indexName;

或

DROP INDEX indexName ON tableName;

索引查询

SHOW INDEX FROM tableName;

索引前缀

对于字符串类型的索引，在创建索引的时候可以指定以索引的前多少个字符作为索引，可以通过使用col_name(length)语法来指定索引前缀长度，这样可以节省空间和查询效率。

索引前缀使用范围及注意事项：

可以给类型为CHAR、VARCHAR、BINARY和VARBINARY的列指定前缀。
如果给类型是BLOB或者TEXT的列创建索引，必须为其指定前缀。此外，BLOB和TEXT类型的列只能在存储引擎是InnoDB、MyISAM和BLACKHOLE的表上建立索引。
索引前缀的长度是以字节为单位的。对于非二进制的(CHAR,VARCHAR,TEXT)的字符类型来说，长度指的是字符的长度。对于二进制的(BINARY, VARBINARY, BLOB)字符类型来说，长度指的是字节的长度。使用多字节的字符编码时，在给二进制的字符类型的列设置长度时要考虑这些。

索引前缀长度是否支持或者如何支持取决于存储引擎。对于InnoDB引擎来说，索引前缀长度可以最多可以支持767 bits，如果innodb_large_prefix参数开启，最多能支持3072 bits。对于MyISAM引擎来说，索引前缀的长度被限制在1000 bits以内。对于NDB引擎来说，压根就不支持索引前缀。

从 MySQL 5.7.17 开始，如果指定的索引前缀超过最大列数据类型大小，CREATE INDEX会按如下方式处理索引：

对于非唯一索引，要么发生错误（如果启用了SQL严格模式），要么索引长度减少到列数据类型大小内并产生警告（如果未启用严格SQL模式）。
对于唯一索引，无论SQL模式如何都会发生错误，因为减少索引长度可能会导致插入不满足指定唯一性要求的非唯一条目。

如果列的前n(n < 列的数据类型长度)个字符不同，使用索引前缀可能不会比使用整个列做索引慢，而且使用索引前缀索引文件更小，可以省更多磁盘空间并且可能会提高插入时的效率。

索引的选择性

索引的选择性是指不重复的索引值(也称为基数，cardinality)和数据表的记录总数(N)的比值，取值范围是1/N到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL 在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。创建索引时要选择索引选择性高的值创建索引。

比如有一百条数据，重复的行数有10条，那么索引的选择性就是10/100，也就是0.1。索引的选择性可以通过下列计算方式带入计算：

SELECT COUNT(DISTINCT col...) / COUNT(*) FROM table_name;

索引的代价

索引是把双刃剑，有利有弊。利：方面就是能提高查询效率。弊端主要是两个方面：

空间代价：每创建一个就会产生一个索引数据文件占用磁盘空间，索引越多占用的磁盘空间也就越大。
时间代价：虽然索引是提高了查询效率，但同时也降低了插入、删除和更新的效率。每次对数据进行增删改操作的同时也是在对索引的更改，索引越多更改时需要花费的时间越多。

总结

本文只是对索引进行一个简单的介绍。索引是把双刃剑，用的好可以提升系统查询效率，用的不好效率不升反降得不偿失。

参考资料：
https://dev.mysql.com/doc/refman/5.7/en/create-index.html

能力一般，水平有限，如有错误，请多指出。
如果文章对你有用就点个关注给个赞呗

标签：index,name,INDEX,知多少,索引,MySQL,id,前缀
From： https://blog.51cto.com/codersun/5745967