(四)MySQL之索引初识篇：索引机制、索引分类、索引使用与管理综述

导言

本文为社区首发签约文章，14天内禁止转载，14天后未获授权禁止转载，侵权必究！

由于MySQL是作为存储层布置在事务系统的最终端，一切的事务数据最终都要入库落盘，但随着一个项目在线上运转的时刻越来越久，数据库中的数据量自然会越来越多，而数据体积出现增长后，当需求从表查询一些数据时，功率会越发低下。在正常情况下，表的查询功用和数据量是成反比的，也便是数据越多，查询越慢。

这是什么原因导致的呢？由于MySQL默许的查询办法导致的，举个比方~

SELECT * FROM `zz_student`;
+------------+--------+------+--------+
| student_id | name   | sex  | height |
+------------+--------+------+--------+
|          1 | 竹子   | 男   | 185cm  |
|          2 | 熊猫   | 女   | 170cm  |
|          3 | 子竹   | 男   | 182cm  |
|          4 | 棕熊   | 男   | 187cm  |
|          5 | 黑豹   | 男   | 177cm  |
|          6 | 脑斧   | 男   | 178cm  |
|          7 | 兔纸   | 女   | 165cm  |
+------------+--------+------+--------+
SELECT * FROM `zz_student`  WHERE name = "脑斧";

上面给出了一张学生表，其中有七位学生信息，而此刻要查询姓名为「脑斧」的学生信息时，MySQL底层是怎样检索数据的呢？会触发磁盘IO，对表中的数据进行逐条读取并判别，也便是说，在这儿想要查找到符合要求的数据，至少要经过六次磁盘IO才干检索到方针（暂且先不考虑局部性读取原理与随机IO）。

• 那假定这个表中有1000W条数据呢？要查的方针数据坐落表的900W行以后怎样办？岂不是要触发几百万次磁盘IO才干检索到数据啊，假如真的这样去干，其功率咱们可想而知。

在这种情况下，又该怎样去提高数据库的查询功用呢？由于查询往往都是一个事务系统中最频繁的操作，一般项目的写/读恳求份额都遵从三七规律，也便是30%的恳求会涉及到写库操作，别的70%则归于查库类型的操作。

在考虑怎样提高查询功用前，咱们无妨先回想一下小时分的场景，小时分由于刚接触汉字，许多字都不认识，所以一般每个人小时分都会具有一本「新华字典」，但一本字典那么厚，咱们是一页页去翻的吗？并不是，字典中有目录索引，咱们能够依据音节、偏旁等办法查找不认识的字。

在「新华字典」中一页页翻找某个汉字，就类似于咱们前面给出的全表扫描办法，功率特别特别低，而经过目录索引则能够在很短的时刻内找到方针汉字。

已然字典中都存在目录索引页，能协助小时分的咱们快速检索汉字，那这个思想能否运用到数据库中来呢？答案是当然能够，并且MySQL也供给了索引机制，索引是数据库中的中心组件之一，一张表中树立了适宜的索引后，往往在面对海量数据查询时，能够事半功倍，接下来一同聊一聊MySQL的索引。

索引机制会分为上、中、下三篇进行论述，大致内容如下：
《上篇：索引初识篇》首要解说索引的概述、分类、运用与办理等；
《中篇：索引运用篇》首要论述索引优劣分析、树立索引的准则、索引失效的场景、怎样正确的运用索引、索引优化机制等；
《下篇：索引原理篇》则首要叙述索引的底层完成、B+Tree、Hash数据结构、聚簇索引和非聚簇索引完成、索引查询原理、索引办理完成等；

一、MySQL索引机制概述

关于MySQL索引机制的效果，经过上述「新华字典」的事例后可得知：索引便是用来协助表快速检索方针数据的。此刻先来简略回忆一下MySQL中索引是怎样运用的呢？首要需求创立索引，MySQL能够经过CREATE、ALTER、DDL三种办法创立一个索引。

1.1、MySQL索引的创立办法

• ①运用CREATE句子创立

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

这种创立办法能够给一张已存在的表结构添加索引，其中需求指定几个值：

• indexName：当时创立的索引，创立成功后叫啥姓名。
• tableName：要在哪张表上创立一个索引，这儿指定表名。
• columnName：要为表中的哪个字段创立索引，这儿指定字段名。
• length：假如字段存储的值过长，选用值的前多少个字符创立索引。
• ASC|DESC：指定索引的排序办法，ASC是升序，DESC是降序，默许ASC。

当然，上述句子中的INDEX也可更改为KEY，效果都是创立一个一般索引，而关于其他的索引类型，这点在后续的索引分类中再聊。

• ②运用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))  
);

这种办法就比较合适在库表规划时，已经确定了索引项的情况下树立。

1.2、查询、删去、指定索引

但不论经过哪种办法树立索引，本质上创立的索引都是相同的，当索引创立完成后，可经过SHOW INDEX FROM tableName;这条指令查询一个表中具有的索引，如下：

CREATE TABLE `zz_user`  (
  `user_id` int(8) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) NULL DEFAULT "",
  `user_sex` varchar(255) NULL DEFAULT "",
  `user_phone` varchar(255) NULL DEFAULT "",
	PRIMARY KEY (`user_id`) USING BTREE
)
    ENGINE = InnoDB 
    CHARACTER SET = utf8 
    COLLATE = utf8_general_ci 
    ROW_FORMAT = Compact;

在上述的建表SQL中，为user_id创立了一个主键索引，然后来查一下当时表的索引信息：

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

• ①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：创立索引时，是否对索引有备注信息。

这条指令在后续扫除问题、功用调优时，会有不小的效果，比方能够经过分析其中的Cardinality字段值，假如该值少于数据的实际行数，那现在索引有或许失效（关于这些后续排查篇和SQL优化篇再聊）。

OK~，到这儿了解了一下索引相关的创立、查询指令，接着再看看删去、强制运用指令。

在MySQL中并未供给修改索引的指令，也就说当你建错了索引，只能先删再重新树立一次，删去索引的句子如下：

DROP INDEX indexName ON tableName;

当然，当树立了一条索引后，也能够强制性的为SELECT句子指定索引，如下：

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

FORCE INDEX要害字能够为一条查询句子强制指定走哪个索引查询，但要紧记的是：假如当时的查询SQL压根不会走指定的索引字段，哪这种办法是行不通的，这个要害字的用法是：一条查询句子在有多个索引能够检索数据时，显式指定一个索引，减少优化器挑选索引的耗时。

但要留意：假如你关于你整个事务系统十分熟悉，那能够这样干。但假如不熟悉的话，仍是交给优化器来自行挑选，不然会适得其反！

1.3、数据库索引的本质

前面一直在聊创立、检查、删去、指定等一些索引的基本操作，但索引本质上在数据库中是什么呢？咱们都知道，数据库是依据磁盘作业的，一切的数据都会放到磁盘上存储，而索引也是数据的一种，因而与表数据相同，最终创立出的索引也会在磁盘生成本地文件。

不过索引文件在磁盘中终究以何种办法存储，这是由索引的数据结构来决议的。一同，由于索引机制最终是由存储引擎完成，因而不同存储引擎下的索引文件，其保存在本地的格式也并不相同。

在这儿有一个点需求留意：树立索引的作业在表数据越少时越好，假如你想要给一张百万、千万条数据等级的表新创立一个索引，那创立的耗时也不短，这是为什么呢？

由于刚刚聊过，索引本质上和表是相同的，都是磁盘中的文件，那也就代表着创立一个索引，并不像单纯的给一张表加个束缚那么简略，而是会依据原有的表数据，重新在磁盘中创立新的本地索引文件。假定表中有一千万条数据，那创立索引时，就需求将索引字段上的1000W个值悉数拷贝到本地索引文件中，一同做好排序并与表数据产生映射联络。

OK~，至此就对MySQL供给的索引机制做了简略回忆，下面再来说说数据库中“多样化”的索引类型。

二、MySQL的索引分类

在前面我为什么用多样化去形容数据库索引呢？由于确实如此，先列一些咱们都传闻过的索引称号：聚簇索引、非聚簇索引、仅有索引、主键索引、联合索引、全文索引、单列索引、多列索引、复合索引、一般索引、二级索引、辅助索引、次级索引、有序索引、B+Tree索引、R-Tree索引、T-Tree索引、Hash索引、空间索引、前缀索引……

是不是看的眼花缭乱，这些都是MySQL中索引的一些称号，一通看下来，估计咱们看“索引”两个字都有点不认识了^_^

但实际上MySQL中真的有这么多索引类型吗？其实并没有，上述列出的索引称号中，有几个称号对应的索引是同一个，有一部分仅仅逻辑上的索引，那索引终究该怎样分类呢？其实从不同的层面上来说，能够将索引区分为不同的类型，接下来要点聊一聊。

2.1、数据结构层次

前面聊索引本质的时分提到过，索引树立后也会在磁盘生成索引文件，那每个具体的索引节点该怎样在本地文件中寄存呢？这点是由索引的数据结构来决议的。比方索引的底层结构是数组，那一切的索引节点都会以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树等结构。

但为何后续的一些索引结构咱们没传闻过呢？这是由于索引到底支撑什么数据结构，这是由存储引擎决议的，不同的存储引擎支撑的索引结构也并不同，现在较为常用的引擎便是MyISAM、InnoDB，因而咱们未曾传闻后面列出的这些索引结构也是正常的。

当然，也正由于索引结构由存储引擎决议，而MySQL引擎层在《MySQL架构篇》中提到过，归于可拔插式引擎，所以假如你有才干自己完成一个引擎，那你甚至能够让引擎的索引机制支撑任何数据结构。

在MySQL中创立索引时，其默许的数据结构就为B+Tree，怎样更换索引的数据结构呢？如下：

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

也便是在创立索引时，经过USING要害字显现指定索引的数据结构（有必要要为当时引擎支撑的结构）。

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

2.2、字段数量层次

前面从索引的数据结构层次动身，能够将索引分为不同结构的类型，而从表字段的层次来看，索引又能够分为单列索引和多列索引，这两个称号也比较好理解，单列索引是指索引是依据一个字段树立的，多列索引则是指由多个字段组合树立的索引。

单列索引也会分为许多类型，比方：

• 仅有索引：指索引中的索引节点值不答应重复，一般合作仅有束缚运用。
• 主键索引：主键索引是一种特别的仅有索引，和一般仅有索引的差异在于不答应有空值。
• 一般索引：经过KEY、INDEX要害字创立的索引便是这个类型，没啥约束，单纯的能够让查询快一点。
• …..还有许多许多，只要是依据单个字段树立的索引都能够被称为单列索引。

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

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

但不论名称咋变，描绘的含义都是相同的，即由多个字段组合树立的索引。

不过在运用多列索引时要留意：当树立多列索引后，一条SELECT句子，只要当查询条件中了包括了多列索引的第一个字段时，才干运用多列索引，下面举个栗子。

比方在用户表中，经过id、name、age三个字段树立一个多列索引，什么情况下会运用索引，什么时分不会呢？如下：

-- 无法运用多列索引的SQL句子
SELECT * FROM `zz_user` WHERE name = "竹子" AND age = "18";
-- 能命中多列索引的SQL句子
SELECT * FROM `zz_user` WHERE name = "竹子" AND id = 6;

OK，到这儿就依据字段数量的层面动身，简略讲明晰单列和多列索引的概念，但无论是单列仍是多列，都能够存在一个前缀索引的概念，啥叫前缀索引呢？还记得创立索引时指定的length字段吗？

• length：假如字段存储的值过长，选用值的前多少个字符创立索引。

运用一个字段值中的前N个字符创立出的索引，就能够被称为前缀索引，前缀索引能够在很大程度上，节省索引文件的存储空间，也能很大程度上提高索引的功用，这是为什么呢？后面分析索引完成原理的时分细聊。

2.3、功用逻辑层次

信任咱们在面试时，假如问到了MySQL索引机制，信任一定会问如下这道面试题：

请答复一下你知道的MySQL索引类型。

这题的答案该怎样答复呢？其实首要便是指MySQL索引从逻辑上能够分为那些类型，以功用逻辑区分索引类型，这也是最常见的区分办法，从这个维度来看首要可区分为五种：

• 一般索引、仅有索引、主键索引、全文索引、空间索引

关于一般索引、仅有索引、主键索引都介绍过了，就不再过多论述，但略微提一嘴，在主键字段上树立的索引被称为主键索引，非主键字段上树立的索引一般被称为辅助索引或、二级索引或次级索引，接着要点聊一下全文索引和空间索引。

全文索引和空间索引都是MySQL5.7版别后开端支撑的索引类型，不过这两种索引都只要MyISAM引擎支撑，其他引擎要么我没用过，要么就由于本身完成的原因不支撑，例如InnoDB。关于全文索引而言，其实在MySQL5.6版别中就有了，但当时并不支撑汉字检索，到了5.7.6版别的时分才内嵌ngram全文解析器，才支撑亚洲语种的分词，一同InnoDB引擎也开端支撑全文索引，在5.7版别之前，只要MyISAM引擎支撑。

全文索引

全文索引类似于ES、Solr查找中间件中的分词器，或许说和之前常用的like+%含糊查询很类似，它只能创立在CHAR、VARCHAR、TEXT等这些文本类型字段上，而且运用全文索引查询时，条件字符数量有必要大于3才收效。当然，仍是举个栗子才有感觉：

+------------+--------------------------------------------+------------------+
| article_id | article_name                               | special_column   |
+------------+--------------------------------------------+------------------+
|          1 | MySQL架构篇：自顶向下深入分析MySQL全体架构 | 《全解MySQL》    |
|          2 | MySQL履行篇：一条SQL句子从诞生至完毕的进程 | 《全解MySQL》    |
|          3 | MySQL规划篇：数据库六范式与反范式规划准则！| 《全解MySQL》    |
|          4 | MySQL索引篇：索引概述、分类及树立索引的准则| 《全解MySQL》    |
+------------+--------------------------------------------+------------------+

比方现在用户想要查找一篇文章，可是忘记文章全称了，只记得「诞生至完毕」这个词汇，此刻用户查找这个词汇，走全文索引的情况下，照样能够定位到上表中的第二条记载。

当然，全文索引怎样创立与运用，待会儿后面一同列出来。

空间索引

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

地舆空间数据首要包括矢量数据、3D模型、印象文件、坐标数据等，说简略点，空间数据也便是能够将地舆信息以模型的办法，在地图上标注出来。在MySQL中总共支撑GEOMETRY、POINT、LINESTRING、POLYGON四种空间数据类型，而空间索引则是依据这些类型的字段树立的，也便是能够协助咱们快捷检索空间数据。

不过关于空间索引，一般用的较少，咱们了解即可。

2.4、存储办法层次

上面聊完了三种不同层次的索引区分后，接着从存储办法的层面再聊聊，从存储办法来看，MySQL的索引首要可分为两大类：

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

要点说一说这两类索引存储办法的差异，在说之前先回忆一下数组和链表的差异：

• 数组是物理空间上的接连，存储的一切元素都会按序寄存在同一块内存区域中。
• 链表是逻辑上的接连，存储的一切元素或许不在同一块内存，元素之间以指针连接。

为啥要说这个呢？由于聚簇索引和非聚簇索引的差异也大致是相同的：

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

当然，这儿的接连和数组不同，由于索引大部分都是运用B+Tree结构存储，所以在磁盘中数据是以树结构寄存的，所以接连并不是指索引节点，而是指索引数据和表数据，也便是说聚簇索引中，索引数据和表数据在磁盘中的方位是一同的，而非聚簇索引则是分隔的，索引节点和表数据之间，用物理地址的办法维护两者的联络。

不过一张表中只能存在一个聚簇索引，一般都会选用主键作为聚簇索引，其他字段上树立的索引都归于非聚簇索引，或许称之为辅助索引、次级索引。但也不要走进一个误区，虽然MySQL默许会运用主键上树立的索引作为聚簇索引，但也能够指定其他字段上的索引为聚簇索引，一般聚簇索引要求索引有必要是非空仅有索引才行。

其实就算表中没有界说主键，InnoDB中会挑选一个仅有的非空索引作为聚簇索引，但假如非空仅有索引也不存在，InnoDB隐式界说一个主键来作为聚簇索引。

当然，主键或许说聚簇索引，一般合适采用带有自增性的次序值。

关于聚簇、非聚簇索引的差异、两者的查找进程、隐式主键、为何主键合适自增值等这些问题，在后续的《索引原理篇》中会具体解说。

2.5、索引分类小结

至此，关于MySQL“多样化”的索引机制，一大堆索引名词，就已经梳理清楚啦！信任到这儿为止，咱们也对MySQL的索引机制有了系统化的认知，其实最开端给出的一大堆索引名词，仅仅从不同视点区分出来的，在上述中分别从数据结构、字段数量、功用逻辑以及存储办法多个层面进行了描绘。当然，要紧记的是，以功用逻辑的层次来区分索引，这也是最常用的办法。

三、MySQL其他索引的创立运用办法

前面的事例中，聊到了咱们有三种办法创立索引，在创立时可经过INDEX、KEY两个要害字创立，但这种办法树立的索引仅是一般索引，接着再来聊一聊MySQL数据库其他类型的索引该怎样创立以及运用。

但不论是何种类型的索引，都能够经过前面聊到的三种办法创立。

3.1、仅有索引的创立与运用

仅有索引在创立时，需求经过UNIQUE要害字创立：如下：

-- 办法①
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))  
);

在已有的表基础上创立仅有索引时要留意，假如选用的字段，表中字段的值存在相同值时，这时仅有索引是无法创立的，比方：

SELECT * FROM `zz_article`;
+------------+--------------------------+-------------------+
| article_id | article_name             | special_column    |
+------------+--------------------------+-------------------+
|          1 | MySQL架构篇：.......     | 《全解MySQL》     |
|          2 | MySQL履行篇：.......     | 《全解MySQL》     |
|          3 | MySQL规划篇：.......     | 《全解MySQL》     |
|          4 | MySQL索引篇：.......     | 《全解MySQL》     |
|          5 | MySQL索引篇：.......     | 《全解MySQL》     |
+------------+--------------------------+-------------------+
CREATE UNIQUE INDEX i_article_name ON zz_article (article_name);

比方上述文章表中，第4、5条数据是重复的，此刻创立利用SQL句子创立仅有索引，就会抛出1062错误码：

ERROR 1062 (23000): Duplicate entry 'MySQL索引篇：.......' for key 'i_article_name'

在这种情况下，就只能先删去重复数据，然后才干创立仅有索引成功。

一同，当仅有索引创立成功后，它一同会对表具有仅有束缚的效果，当再运用INSERT句子刺进相同值时，会相同会抛出1062错误码：

INSERT INTO `zz_article` VALUES(6,"MySQL索引篇：.......","《全解MySQL》");
1062 - Duplicate entry 'MySQL索引篇：.......' for key 'i_article_name'

这儿会提示你刺进的哪个值，已经在表中存在，因而无法刺进当时这条数据。

3.2、主键索引的创立与运用

前面聊到过，主键索引其实是一种特别的仅有索引，但主键索引却并不是经过UNIQUE要害字创立的，而是经过PRIMARY要害字创立：

-- 办法①
ALTER TABLE tableName ADD PRIMARY KEY indexName(columnName);
-- 办法②
CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  PRIMARY KEY [indexName] (columnName(length))  
);

在这儿要留意：

• 创立主键索引时，有必要要将索引字段先设为主键，不然会抛1068错误码。
• 这儿也不能运用CREATE句子创立索引，不然会提示1064语法错误。
• 一同创立索引时，要害字要换成KEY，并非INDEX，不然也会提示语法错误。

仍是以之前的文章表为例，如下：

-- 对非主键字段创立主键索引
ALTER TABLE zz_article ADD PRIMARY KEY i_special_column(special_column);
-- 报错信息如下：
1068 - Multiple primary key defined
-- 运用CREATE要害字创立主键索引
CREATE PRIMARY KEY i_article_id ON zz_article (article_id);
-- 报错信息如下：
1064 - You have an error in your SQL syntax; check....
-- 运用INDEX要害字创立索引
ALTER TABLE zz_article ADD PRIMARY INDEX i_article_id(article_id);
-- 报错信息如下：
1064 - You have an error in your SQL syntax; check....
-- 创立主键索引正确的办法
ALTER TABLE zz_article ADD PRIMARY KEY i_article_id(article_id);

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

但好像无论在讲一般索引，仍是仅有索引、主键索引的时分，咱们都没有讲怎样运用这些创立好的索引查询数据，其实这一点无需咱们考虑，参阅之前《SQL履行篇》中查询句子的履行流程，在一条SELECT句子来到MySQL时，会阅历优化器优化的进程，而优化器则会自动帮咱们挑选一个最适宜的索引查询数据。当然，条件是查询条件中涉及到了索引字段才行。

前面也说过，你不想让优化器自动挑选，也能够手动经过FORCE INDEX要害字强制指定。

3.3、全文索引的创立与运用

全文索引和其他索引不同，首要假如你想要创立全文索引，那么MySQL版别有必要要在5.7及以上，一同运用时也需求手动指定，一同来先看看怎样创立全文索引，此刻需求运用FULLTEXT要害字：

-- 办法①
ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);
-- 办法②
CREATE FULLTEXT INDEX indexName ON tableName(columnName);

不过在创立全文索引时，有三个留意点：

• 5.6版别的MySQL中，存储引擎有必要为MyISAM才干创立。
• 创立全文索引的字段，其类型有必要要为CHAR、VARCHAR、TEXT等文本类型。
• 假如想要创立出的全文索引支撑中文，需求在最终指定解析器：with parser ngram。

此刻还依旧是以文章表为例，为文章名称字段创立一个全文索引，指令如下：

ALTER TABLE
    zz_article ADD 
FULLTEXT INDEX 
    ft_article_name(article_name) 
WITH PARSER NGRAM;

创立好全文索引后，当你想要运用全文索引时，优化器这时不能自动挑选，由于全文索引有自己的语法，但在了解怎样运用之前，得先清楚两个概念：最小查找长度和最大查找长度，先来看看全文索引的一些参数，可经过show variables like '%ft%';指令查询，如下：

多余的参数就不介绍了，要点讲一下其中的几个重要参数：

• ft_min_word_len：运用MyISAM引擎的表中，全文索引最小查找长度。
• ft_max_word_len：运用MyISAM引擎的表中，全文索引最大查找长度。
• ft_query_expansion_limit：MyISAM中运用with query expansion查找的最大匹配数。
• innodb_ft_min_token_size：InnoDB引擎的表中，全文索引最小查找长度。
• innodb_ft_max_token_size：InnoDB引擎的表中，全文索引最大查找长度。

那么终究做最小查找长度、最大查找长度的效果是什么呢？其实这个是一个约束，关于长度小于最小查找长度和大于最大查找长度的词语，都无法触发全文索引。也便是说，假如想要运用全文索引对一个词语进行查找，那这个词语的长度有必要在这两个值之间。

其实这两个值自己能够手动调整的，最小值能够手动调整为1，MyISAM引擎的最大值能够调整为3600，但InnoDB引擎最大好像便是84。

OK~，了解全文索引中的一些概念后，接下来看看怎样运用全文索引，全文索引中有两个专门用于检索的要害字，即MATCH(column)、AGAINST(要害字)，一同这两个检索函数也支撑三种查找形式：

• 自然语言形式(默许查找形式)
• 布尔查找形式
• 查询拓宽查找

MATCH()首要是担任指定要查找的列，这儿要指定创立全文索引的字段，AGAINST()则指定要查找的要害字，也便是要查找的词语，接下来简略的讲一下三种查找形式。

自然语言形式

这种形式也是在运用全文索引时，默许的查找形式，运用办法如下：

+------------+--------------------------+-------------------+
| article_id | article_name             | special_column    |
+------------+--------------------------+-------------------+
|          1 | MySQL架构篇：.......     | 《全解MySQL》     |
|          2 | MySQL履行篇：.......     | 《全解MySQL》     |
|          3 | MySQL规划篇：.......     | 《全解MySQL》     |
|          4 | MySQL索引篇：.......     | 《全解MySQL》     |
+------------+--------------------------+-------------------+
SELECT 
    COUNT(article_id) AS '查找成果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('MySQL');
-- 运转成果如下：
+--------------+
| 查找成果数量 |
+--------------+
|           4 |
+--------------+

一眼看过去，SQL就能看懂，毕竟都能够排版了一下SQL，不过多介绍了。仅有要留意的是，假如给定的要害词长度小于默许的最小查找长度，那是无法运用全文索引的，比方下述这条SQL就不会触发：

SELECT
    COUNT(article_id) AS '查找成果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('M');

布尔查找形式

布尔查找形式有些特别，由于在这种查找形式中，还需求把握特定的查找语法：

• +：表明有必要匹配的行数据有必要要包括相应要害字。
• -：和上面的+相反，表明匹配的数据不能包括相应的要害字。
• >：提高指定要害字的相关性，在查询成果中靠前显现。
• <：下降指定要害字的相关性，在查询成果中靠后显现。
• ~：表明答应出现指定要害字，但出现时相关性为负。
• *：表明以该要害字开头的词语，如A*，能够匹配A、AB、ABC....
• ""：双引号中的要害字作为全体，检索时不答应再分词。
• "X Y"@n：""包括的多个词语之间的间隔有必要要在n之间，单位-字节，如：
  • 竹子 熊猫@10：表明竹子和熊猫两个词语之间的间隔要在10字节内。
• …….

举个几个比方运用一下，如下：

-- 查询文章名中包括 [MySQL] 但不包括 [规划] 的数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL -规划' IN BOOLEAN MODE);
-- 查询文章名中包括 [MySQL] 和 [篇] 的数据，但两者间的间隔不能超过10字节
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('"MySQL 篇"@10' IN BOOLEAN MODE);
-- 查询文章名中包括[MySQL] 的数据，
--    但包括 [履行] 要害字的行相关性要高于包括 [索引] 要害字的行数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL +(>履行 <索引)' IN BOOLEAN MODE);
-- 查询文章名中包括 [MySQL] 的数据，但包括 [规划] 时则将相关性降为负
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('+MySQL ~规划' IN BOOLEAN MODE);
-- 查询文章名中包括 [履行] 要害字的行数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('履行*' IN BOOLEAN MODE);
-- 查询文章名中有必要要包括 [MySQL架构篇] 要害字的数据
SELECT 
    *
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('"MySQL架构篇"' IN BOOLEAN MODE);

相同的，上述的SQL句子应该都能看理解，最终的IN BOOLEAN MODE表明运用布尔查找形式，除此外，咱们仅有疑惑的就在于：相关性这个词，其实这个词也不难理解，便是检索数据后，数据的优先级次序，当相关性越高，对应数据在成果中越靠前，当相关性为负，则相应的数据排到最终。

查询拓宽查找

查询拓宽查找其实是对自然语言查找形式的拓宽，比方举个比方：

SELECT
    COUNT(article_id) AS '查找成果数量' 
FROM 
    `zz_article` 
WHERE 
    MATCH(article_name) AGAINST('MySQL' WITH QUERY EXPANSION);

在自然语言形式的查询句子基础上，最终面多加一个WITH QUERY EXPANSION表明运用查询拓宽查找，这种形式下会比自然语言形式多一次检索进程，比方上述的比方中：

• 首要会依据指定的要害字MySQL进行一次全文检索。
• 然后第二阶段还会对指定的要害进行分词，然后再进行一次全文检索。

之前介绍全文索引参数时，也列出来了一个名为ft_query_expansion_limit的参数，这个参数便是操控拓宽查找时的拓宽行数的，最大能够调整到1000。但由于Query Expansion的全文检索或许带来许多非相关性的查询成果，因而在实际情况中要慎用！！！

实际上，全文索引引入MySQL后，能够用它代替之前的like%含糊查询，功率会更高。

3.4、空间索引的创立与运用

空间索引这玩意儿实际上许多项目不会用到，我用的次数也不多，但假如你要用到这个索引，那能够经过SPATIAL要害字创立，如下：

ALTER TABLE tableName ADD SPATIAL KEY indexName(columnName);

但在创立空间索引的时分，有几个留意点需求紧记：

• 现在MySQL常用引擎中，仅有MyISAM支撑空间索引，所以表引擎有必要要为它。
• 空间索引有必要要树立在类型为GEOMETRY、POINT、LINESTRING、POLYGON的字段上。

这个用的较少，就不展开细聊了~

3.5、联合索引的创立与运用

联合索引呢，实际上并不是一种逻辑索引分类，它是索引的一种特别结构，前面给出的一切事例中，都仅仅是在单个字段的基础上树立索引，而联合索引的意思是能够运用多个字段树立索引。那该怎样创立联合索引呢，不需求特别的要害字，办法如下：

CREATE INDEX indexName ON tableName (column1(length),column2...);
ALTER TABLE tableName ADD INDEX indexName(column1(length),column2...);

• 你能够运用INDEX要害字，让多个列组成一个一般联合索引
• 也能够运用UNIQUE INDEX要害字，让多个列组成一个仅有联合索引
• 甚至还能够运用FULLTEXT INDEX要害字，让多个列组成一个全文联合索引
• …….

可是前面也提过，SELECT句子的查询条件中，有必要包括组成联合索引的第一个字段，此刻才会触发联合索引，不然是无法运用联合索引的。

四、索引初识篇总结

OK~，在本篇中就对MySQL的索引机制有了全面认知，从索引的由来，到索引概述、索引办理、索引分类、仅有/全文/联合/空间索引的创立与运用等内容，进行了全面概述，信任本章看下来，满足让你对MySQL索引机制有一个系统化的系统，那么咱们下篇再会。