MySQL索引

索引

索引(Index)是什么？为什么要使用索引？

索引是提高数据库性能的重要方式，用来快速找出数据表中的特定记录。如果在表中查询的列有一个索引，MySQL 能快速到达一个位置去搜寻数据，而不必查看所有数据。一个表可以创建多个索引，一个索引可以包含一个或者多个字段。

从表面上看，索引类似于书的目录，在没有目录的情况下，要从书中查找某项内容就必须阅读全文，而有了目录之后，通过页码就可以很快定位到相关内容。

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

从本质上看，索引是根据表的一个或者多个字段生成的子表，该子表中的数据已经进行了排序。子表除了包含指定字段中的数据，还包含一个 rowid 列，用于存储当前记录在原始表中的位置。用户无法看到索引，它只是用来加快查询速度。

为了提高查询效率，便于后期维护，索引都是基于某种数据结构而创建的，比如 B+ 树、B- 树、位图等。

• 为什么要使用索引

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据:

1. 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。

顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2. 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。

使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

• 索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

1. 优点

索引的优点如下：

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
• 可以给所有的 MySQL 列类型设置索引。
• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

2. 缺点

增加索引也有许多不利的方面，主要如下：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。增加 I/O 成本，过多的索引甚至会增加碎片。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。
• MySQL里同一个数据表里的索引总数限制为16个。

使用索引时，需要综合考虑索引的优点和缺点。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

索引的分类

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。

推荐阅读

存储方式

B+树索引

MySQL索引底层：B+树详解 - 知乎 (zhihu.com)

MySQL 存储引擎的索引为什么使用B+树？

为什么 MySQL 使用 B+树 - 知乎 (zhihu.com)

首先需要澄清的一点是，MySQL 跟 B+ 树没有直接的关系，真正与 B+ 树有关系的是 MySQL 的默认存储引擎 InnoDB，MySQL 中存储引擎的主要作用是负责数据的存储和提取(从我们的磁盘中)，除了 InnoDB 之外，MySQL 中也支持 MyISAM 作为表的底层存储引擎。

先思考一下，为什么不用平衡二叉搜索树或者红黑树，而选择B+树？

1. 二叉树相比于顺序查找的确减少了查找次数，但是在最坏情况下，二叉树有可能退化为顺序查找。而且就二叉树本身来说，当数据库的数据量特别大时，其层数也将特别大。二叉树的高度一般是 log_2^n，B 树的高度是 log_t^((n+1)/2) + 1，其高度约比 B 树大 lgt 倍。n 是节点总数，t 是树的最小度数。
2. B 树在提高 IO 性能的同时，并没与解决元素遍历时效率低下的问题，正是为了解决这个问题，B+树应运而生。B+树只需遍历叶子节点即可实现整棵树的遍历，而 B 树必须使用中序遍历按序扫库，B+树支持范围查询非常方便。这才是数据库选用 B+树的主要原因。另外，最后说一下，并不是说 B+树就比 B 树好，有很多基于频率的搜索是选用 B树，越频繁 query 的结点越往根上走，前提是需要对 query 做统计，而且要对 key做一些变化。无论是 B 树还是 B+树由于前边几层反复 query，因此早已被加载入内存，不会出现读磁盘 IO。一般启动的时候，就会主动换入内存。在内存中 B+树并没有优势，只有在磁盘中 B+树的威力才能显现。
3. B+树的高度一般为 2-4 层，所以查找记录时最多只需要 2-4 次 IO，相对二叉平衡树已经大大降低了。 范围查找时，能通过叶子节点的指针获取数据。例如查找大于等于 3 的数据，当在叶子节点中查到 3 时，通过 3 的尾指针便能获取所有数据，而不需要再像二叉树一样再获取到 3 的父节点。

总结一下：

• 二叉查找树(BST)：解决了排序的基本问题,但是由于无法保证平衡,可能退化为链表
• 平衡二叉树(AVⅥL)：通过旋转解决了平衡的问题,但是旋转操作效率太低
• 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了 AⅥ旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO 次数太多
• B 树：通过将二叉树改为多路平衡查找树，解决了树过高的问题
• B+树：在 B 树的基础上,将非叶节点改造为不存储数据的纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效。B+树叶节点之间只是逻辑相邻，而不是物理相邻，甚至在物理位置相邻很远的情况下，依然会产生很多的随机IO。B+树减少随机IO的关键在于，利用叶节点逻辑相邻的特性，尽可能地做到物理相邻(数据被分配到连续的页中)，使得在读取叶节点中的大量记录时可以使用顺序IO。这点很重要！

我们今天最终将要分析的问题其实还是，为什么 MySQL 默认的存储引擎 InnoDB 会使用 B+ 树来存储数据，相信对 MySQL 稍微有些了解的人都知道，无论是表中的数据(主键索引)还是辅助索引最终都会使用 B+ 树来存储数据，其中前者在表中会以 <id, row> 的方式存储，而后者会以 <index, id> 的方式进行存储，这其实也比较好理解：

• 在主键索引中，id 是主键，我们能够通过 id 找到该行的全部列；
• 在辅助索引中，索引中的几个列构成了键，我们能够通过索引中的列找到 id，如果有需要的话，可以再通过 id 找到当前数据行的全部内容；

对于 InnoDB 来说，所有的数据都是以键值对的方式存储的，主键索引和辅助索引在存储数据时会将 id 和 index 作为键，将所有列和 id 作为键对应的值。

到了这里我们已经明确了今天待讨论的问题，也就是为什么 MySQL 的 InnoDB 存储引擎会选择 B+ 树作为底层的数据结构，而不选择 B 树或者哈希？在这一节中，我们将通过以下的两个方面介绍 InnoDB 这样选择的原因。

• InnoDB 需要支持的场景和功能需要在特定查询上拥有较强的性能；
• CPU 将磁盘上的数据加载到内存中需要花费大量的时间，这使得 B+ 树成为了非常好的选择；

数据的持久化以及持久化数据的查询其实是一个常见的需求，而数据的持久化就需要我们与磁盘、内存和 CPU 打交道；MySQL 作为 OLTP 的数据库不仅需要具备事务的处理能力，而且要保证数据的持久化并且能够有一定的实时数据查询能力，这些需求共同决定了 B+ 树的选择，接下来我们会详细分析上述两个原因背后的逻辑。

• 读写性能

很多人对 OLTP 这个词可能不是特别了解，我们帮助各位读者快速理解一下，与 OLTP 相比的还有 OLAP，它们分别是 Online Transaction Processing 和 Online Analytical Processing，从这两个名字中我们就可以看出，前者指的就是传统的关系型数据库，主要用于处理基本的、日常的事务处理，而后者主要在数据仓库中使用，用于支持一些复杂的分析和决策。

作为支撑 OLTP 数据库的存储引擎，我们经常会使用 InnoDB 完成以下的一些工作：

• 通过 INSERT、UPDATE 和 DELETE 语句对表中的数据进行增加、修改和删除；
• 通过 UPDATE 和 DELETE 语句对符合条件的数据进行批量的删除；
• 通过 SELECT 语句和主键查询某条记录的全部列；
• 通过 SELECT 语句在表中查询符合某些条件的记录并根据某些字段排序；
• 通过 SELECT 语句查询表中数据的行数；
• 通过唯一索引保证表中某个字段或者某几个字段的唯一性；

如果我们使用 B+ 树作为底层的数据结构，那么所有只会访问或者修改一条数据的 SQL 的时间复杂度都是 O(log n)，也就是树的高度，但是使用哈希却有可能达到 O(1) 的时间复杂度，看起来是不是特别的美好。但是当我们使用如下所示的 SQL 时，哈希的表现就不会这么好了：

select * from posts where author = 'draven' order by created_at desc;
select * from posts where comments_count > 10;
update posts set github = 'github.com/BearBrick0' where author = 'BearBrick0';
delete from posts where author = 'BearBrick0';

如果我们使用哈希作为底层的数据结构，遇到上述的场景时，使用哈希构成的主键索引或者辅助索引可能就没有办法快速处理了，它对于处理范围查询或者排序性能会非常差，只能进行全表扫描并依次判断是否满足条件。全表扫描对于数据库来说是一个非常糟糕的结果，这其实也就意味着我们使用的数据结构对于这些查询没有其他任何效果，最终的性能可能都不如从日志中顺序进行匹配。

使用 B+ 树其实能够保证数据按照键的顺序进行存储，也就是相邻的所有数据其实都是按照自然顺序排列的，使用哈希却无法达到这样的效果，因为哈希函数的目的就是让数据尽可能被分散到不同的桶中进行存储，所以在遇到可能存在相同键 author = 'BearBrick0 或者排序以及范围查询 comments_count > 10 时，由哈希作为底层数据结构的表可能就会面对数据库查询的噩梦 —— 全表扫描。

B 树和 B+ 树在数据结构上其实有一些类似，它们都可以按照某些顺序对索引中的内容进行遍历，对于排序和范围查询等操作，B 树和 B+ 树相比于哈希会带来更好的性能，当然如果索引建立不够好或者 SQL 查询非常复杂，依然会导致全表扫描。

B+树的非叶子结点，存储的是索引信息，叶子结点存储的是行数据。叶子结点都在同一层，是一个高度平衡的搜索树。叶子之间相互连接，可以支持范围查询，可以减少磁盘的IO。是一个有序的。

不管是什么类型的索引，都对应着一个B+树。

与 B 树和 B+ 树相比，哈希作为底层的数据结构的表能够以 O(1) 的速度处理单个数据行的增删改查，但是面对范围查询或者排序时就会导致全表扫描的结果，而 B 树和 B+ 树虽然在单数据行的增删查改上需要 O(log n) 的时间，但是它会将索引列相近的数据按顺序存储，所以能够避免全表扫描。

• 数据加载

既然使用哈希无法应对我们常见的 SQL 中排序和范围查询等操作，而 B 树和 B 树和 B+ 树都可以相对高效地执行这些查询，那么为什么我们不选择 B 树呢？这个原因其实非常简单 —— 计算机在读写文件时会以页(page)为单位将数据加载到内存中。页的大小可能会根据操作系统的不同而发生变化，不过在大多数的操作系统中，页的大小都是 4KB，你可以通过如下的命令获取操作系统上的页大小:

插入个问题：如果我们的在B+树中去查找4-9之间的数，需要几次磁盘I/O呢？

如果4-9在同一页中,除了从根节点到对应页的过程,就一次,不在同一页中,多次看页数。访问一个page，就意味着需要读一次磁盘，然后在内存里缓存成一个page，下一次再访问这个page，就不需要读磁盘了。B+树特点就是以page为单位读写磁盘，来减少磁盘IO次数。MySQL把一个page定义为16KB，是根据经验主义，发现16KB是一个通用的最优配置。

getconf PAGE_SIZE
4096

当我们需要在数据库中查询数据时，CPU 会发现当前数据位于磁盘而不是内存中，这时就会触发 I/O 操作将数据加载到内存中进行访问，数据的加载都是以页的维度进行加载的，然而将数据从磁盘读取到内存中所需要的成本是非常大的，普通磁盘(非 SSD)加载数据需要经过队列、寻道、旋转以及传输的这些过程，大概要花费 10ms 左右的时间。

我们在估算 MySQL 的查询时就可以使用 10ms 这个数量级对随机 I/O 占用的时间进行估算，这里想要说的是随机 I/O 对于 MySQL 的查询性能影响会非常大，而顺序(IO)读取磁盘中的数据时速度可以达到 40MB/s，这两者的性能差距有几个数量级，由此我们也应该尽量减少随机 I/O 的次数，这样才能提高性能。

B 树与 B+ 树的最大区别就是，B 树可以在非叶结点中存储数据，但是 B+ 树的所有数据其实都存储在叶子节点中，当一个表底层的数据结构是 B 树时，假设我们需要访问所有『大于 4，并且小于 9 的数据』：

如果不考虑任何优化，在上面的简单 B 树中我们需要进行 4 次磁盘的随机 I/O 才能找到所有满足条件的数据行：

1. 加载根节点所在的页，发现根节点的第一个元素是 6，大于 4；
2. 通过根节点的指针加载左子节点所在的页，遍历页面中的数据，找到 5；
3. 重新加载根节点所在的页，发现根节点不包含第二个元素；
4. 通过根节点的指针加载右子节点所在的页，遍历页面中的数据，找到 7 和 8；

当然我们可以通过各种方式来对上述的过程进行优化，不过 B 树能做的优化 B+ 树基本都可以，所以我们不需要考虑优化 B 树而带来的收益，直接来看看什么样的优化 B+ 树可以做，而 B 树不行。

由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量的随机 I/O，也是 B 树最大的性能问题。

B+ 树中就不存在这个问题了，因为所有的数据行都存储在叶节点中，而这些叶节点可以通过『指针』依次按顺序连接，当我们在如下所示的 B+ 树遍历数据时可以直接在多个子节点之间进行跳转，这样能够节省大量的磁盘 I/O 时间，也不需要在不同层级的节点之间对数据进行拼接和排序；通过一个 B+ 树最左侧的叶子节点，我们可以像链表一样遍历整个树中的全部数据，我们也可以引入双向链表保证倒序遍历时的性能。

有些读者可能会认为使用 B+ 树这种数据结构会增加树的高度从而增加整体的耗时，然而高度为 3 的 B+ 树就能够存储千万级别的数据，实践中 B+ 树的高度最多也就 4 或者 5，所以这并不是影响性能的根本问题。

• 总结

我们在这里重新回顾一下 MySQL 默认的存储引擎选择 B+ 树而不是哈希或者 B 树的原因：

• 哈希虽然能够提供 O(1) 的单数据行操作性能，但是对于范围查询和排序却无法很好地支持，最终导致全表扫描；
• B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O，而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O，是一个顺序 I/O；

如果想要追求各方面的极致性能也不是没有可能，只是会带来更高的复杂度，我们可以为一张表同时建 B+ 树和哈希构成的存储结构，这样不同类型的查询就可以选择相对更快的数据结构，但是会导致更新和删除时需要操作多份数据。

从今天的角度来看，B+ 树可能不是 InnoDB 的最优选择，但是它一定是能够满足当时设计场景的需要，从 B+ 树作为数据库底层的存储结构到今天已经过了几十年的时间，我们不得不说优秀的工程设计确实有足够的生命力。而我们作为工程师，在选择数据库时也应该非常清楚地知道不同数据库适合的场景，因为软件工程中没有银弹。

哈希索引

哈希(Hash)一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入(又叫作预映射，pre-image)通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：

• MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
• 不能使用 HASH 索引排序。
• HASH 索引只支持等值比较，如“=” “IN()”或“<=>”。
• HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

逻辑角度

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。

普通索引允许在定义索引的列中插入重复值和空值。允许出现相同的索引内容。

创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

# 在 tb_student 表中的 id 字段上建立名为 index_id 的索引
create index index_id on tb_student(id);

唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。

唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。不可以出现相同的值，可以有null值。

创建唯一索引通常使用 UNIQUE 关键字。

# tb_student 表中的 id 字段上建立名为 index_id 的索引
create unique index index_id on tb_student(id);

主键索引

顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。非空唯一索引，一个表只能有一个主键索引。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

PRIMARY KEY(key);

空间索引

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。

创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。

空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

# tb_student 表中的 line 字段上建立名为 index_line 的索引
create spatial index index_line on tb_student(line);

其中，tb_student 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

全文索引

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

全文索引允许在索引列中插入重复值和空值。基于倒排索引。

不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

创建全文索引使用 FULLTEXT 关键字。

# tb_student 表中的 info 字段上建立名为 index_info 的全文索引，
create fulltext index index_info on tb_student(info);

其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

实际使用的角度

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

# 在 tb_student 表中的 address 字段上建立名为 index_addr 的单列索引，address 字段的数据类型为 VARCHAR(20)，索引的数据类型为 CHAR(4)。
create index index_addr ON tb_student(address(4));

这样，查询时可以只查询 address 字段的前 4 个字符，而不需要全部查询。

多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

# 在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引
create index index_na on tb_student(name,address);

该索引创建好了以后，查询条件中必须有 name 字段才能使用索引。就是要满足最左前缀法则

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。没有(c1,c3)的原因是索引插叙要满足最左前缀法则，意思就是从左到右，不能跳索引，否则索引就会失效。

索引的创建和删除

1. 使用 create index 语句

可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。

create index <索引名> on <表名> (<列名> [<长度>] [ asc | desc])

• <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
• <表名>：指定要创建索引的表名。也就是那个表。
• <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
• <长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节(MyISAM 和 InnoDB 表的最大上限为 1000 个字节)，如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
• ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

2. 使用 create table 语句

索引也可以在创建表(create table)的同时创建。

constraint primary key [索引类型] (<列名>,…)

在 create table 语句中添加此语句，表示在创建新表的同时创建该表的主键。

key | index [<索引名>] [<索引类型>] (<列名>,…)

# 在创建新表的同时创建该表的唯一性索引。
unique [ index | key] [<索引名>] [<索引类型>] (<列名>,…)

# 在创建新表的同时创建该表的外键。
foreign key <索引名> <列名>

在使用 create table 语句定义列选项的时候，可以通过直接在某个列定义后面添加 primary key 的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 primary key(<列名>，…) 子句的方式来实现。

3. alter table 语句

create index 语句可以在一个已有的表上创建索引，alter table 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 alter table 语句中添加以下语法成分的某一项或几项。

# 在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。
add index [<索引名>] [<索引类型>] (<列名>,…)

# 在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。
add primary key [<索引类型>] (<列名>,…);

# 在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。
add unique [ index | key] [<索引名>] [<索引类型>] (<列名>,…)

# 在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键。
add foreign key [<索引名>] (<列名>,…);

4. 创建普通索引

创建普通索引时，通常使用 index 关键字。

create table tb_stu_info (
   id INT NOT NULL,
   name CHAR(45) DEFAULT NULL,
   dept_id INT DEFAULT NULL,
   age INT DEFAULT NULL,
   height INT DEFAULT NULL,
   index(height)
);

# 查看
show create table tb_stu_info;

5. 创建唯一索引

创建唯一索引，通常使用 unique 参数。

# 创建一个表 tb_stu_info2，在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引。
create table tb_stu_info2 (
   id INT NOT NULL,
   name CHAR(45) DEFAULT NULL,
   dept_id INT DEFAULT NULL,
   age INT DEFAULT NULL,
   height INT DEFAULT NULL,
   unique index(height)
);

show create table tb_stu_info2;

6. 删除索引

drop index <索引名> on <表名>;

查看索引

索引创建完成后，可以利用 SQL 语句查看已经存在的索引。在 MySQL 中，可以使用 show index 语句查看表中创建的索引。

show index from <表名> [ from <数据库名>]

show index from tb_stu_info2;

什么情况下会索引失效？

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用。

1. 查询语句中右like关键字

在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

先查询 tb_student 表中的数据。

select * from tb_student;

使用 LIKE 关键字，且匹配的字符串中含有“%”符号，使用 explain 分析查询情况。

explain select * from tb_student where name like '%四'

# 给数据表 name 字段 创建索引
create index index_name on tb_student(name);

# 查询
explain select * from tb_student where name like '李%';

第一个查询语句执行后，rows 参数的值为 6，表示这次查询过程中查询了 6 条记录；第二个查询语句执行后，rows 参数的值为 1，表示这次查询过程只查询 1 条记录。同样是使用 name 字段进行查询，因为第一个查询语句的 LIKE 关键字后的字符串是以“%”开头的，所以第一个查询语句没有使用索引，而第二个查询语句使用了索引 index_name。

2. 查询语句中使用多列索引

多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。

# 在 name 和 age 两个字段上创建多列索引，并验证多列索引的使用情况
cretae index index_name_age on tb_student(name,age);

# 测试 走了索引
explain select * from tb_student where name like '李%';

# 测试 没走索引
explain select * from tb_student where age like '12';

第一条查询语句的查询条件使用了 name 字段，分析结果显示 rows 参数的值为 1，且查询过程中使用了 index_name_age 索引。第二条查询语句的查询条件使用了 age 字段，结果显示 rows 参数的值为 6，且 key 参数的值为 NULL，这说明第二个查询语句没有使用索引。

因为 name 字段是多列索引的第一个字段，所以只有查询条件中使用了 name 字段才会使 index_name_age 索引起作用。

3. 查询语句中使用OR关键字

查询语句只有 OR 关键字时，如果 OR 前后的两个条件的列都是索引，那么查询中将使用索引。如果 OR 前后有一个条件的列不是索引，那么查询中将不使用索引。

# 演示 OR 关键字的使用
# 没走索引
explain select * from tb_student where name='张三' or sex='男';

# 走索引
explain select * from tb_student where name='张三' or id='12';

由于 sex 字段没有索引，所以第一条查询语句没有使用索引；name 字段和 id 字段都有索引，所以第二条查询语句使用了 index_name 和 PRIMARY 索引 。

总结：要走索引，要满足最左前缀法则。

怎么提升索引的使用效率，设计出更高效的索引

索引的设计可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

1. 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2. 为经常需要排序、分组和联合操作的字段建立索引

经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3. 为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

注意：常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

4. 限制索引的数目

索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。

如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。

5. 尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。

6. 数据量小的表最好不要使用索引

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

7. 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

8. 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

MySQL索引设计的原则

查询更快、占用空间更小。

1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列

2. 基数较小的表，索引效果较差，没有必要在此列建立索引

3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。

4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

5. 定义有外键的数据列一定要建立索引。

6. 更新频繁字段不适合创建索引

7. 若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

8. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

9. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

10. 对于定义为text、image和bit的数据类型的列不要建立索引。