MySQL

MySQL中的索引学习

0

什么是索引？

在没有学数据库之前，大多数人对索引还是比较陌生（可能学完之后还是比较陌生：），今天我想告诉大家索引很有用，而且面试的时候都会问到。那么索引是什么呢？

其实索引这个东西我们很早就接触过了，想象一下当初学习《现代汉语词典》的时候，老师是如何教我们使用的，就可以理解什么是索引了。《现代汉语词典》有将近1800页，里面的汉字大概有1.3万多个，我们是如何在这么多的汉字中找到某个字呢？老师大概是这么教我们的：词典提供了“音节表”，“音节表”将所有汉子的汉语拼音编入其中，并且“音节表”按’a’到’z’的顺序排序，故而我们可以轻松的在音节表中找到某个字所对应的页数。其实不光是词典，我们看的书大部分都有目录，而这个目录就相当于索引的作用，如下是三本书的目录：

422

508

590

通过上面三本书的目录的对比，很明显第一本书的目录写的很简单，第二本书稍详细一点，而第三本书的目录最详细，目录写得越详细，读者在阅读的时候就越方便。

不管是词典中的音节表还是书的目录，它们实际都是索引的一种存在形式，都是为了能够提高查找的效率。

数据库中索引的本质

本质上，索引其实是数据库表中字段值的复制，该字段称为索引的关键字。

索引在计算机中的存储

在学习数据结构时，曾讲到文件的概念，而数据库中所有的数据都是以文件的形式保存的计算机中的，所以文件系统是数据库系统的基础。而数据库在操作这些文件时，都是先将文件中的数据加载到内存，然后再以某种数据结构来操作这些数据，通常的数据结构可以是AVL树、B树、B+树等。数据库中的往往是要存大量的数据的，而所有的数据不可能一次性全部加载到内存中，这时候就需要索引与分块加载来提高查询的速度，不过一般初学者不会体验到索引带来的高效，因为他们的电脑内存足够大完全可以一次性加载完所有数据，这也导致初学者忽略了对索引的仔细学习。如下是MyISAM存储引擎的索引示意图：

1311

更多关于数据库索引背后的数据结构和算法原理，请查看：https://www.cnblogs.com/tgycoder/p/5410057.html

MySQL中的索引

在MySQL中，共有4类索引：主键索引、唯一索引、常规索引和全文索引。

主键索引(Primary key)

主键索引是关系数据库中最常见的索引类型。它根据主键自身的唯一性来唯一标识每条记录。因此该键必须是表中的唯一值。创建主键索引很简单，如下：

1627

每个表只能有一个自增字段，该字段必须指定为主键。此外，任何指定为主键的字段不能是NULL，即使没有声明为NOT NULL，MySQL会自己设置。

唯一索引(Unique)

与主键索引一样，唯一索引可以防止创建重复的值。不同之处在于每个表只能有一个主键索引，但可以有多个唯一索引。修改上面的sql语句来增加唯一索引：

1863

如前面所讲，表中可以指定多个唯一字段，我们现在将name字段也设置为唯一：

1976

还可以指定多列唯一索引，比如上面的表结构中，允许用户插入重复的url，甚至可以插入重复的name值，但不希望出现重复的name和url的组合。可以创建多列唯一索引来强制这种约束，如下：

2142

常规索引(Index)

可能经常需要对数据库的搜索效率进行优化，以便能够根据并非主键甚至并非唯一的列获取数据行。为此，最有效的方法是采用某种方式索引列，是的数据库尽可能快的查找想要的值。这就称为常规索引。

单列常规索引

如果表中的某个列经常需要被查询到，就应该使用单列常规索引。假如，一张员工表you 4列：主键id，名字，姓氏和电子邮箱，若果大多数的查询操作都会针对员工的姓氏或者电子邮箱，因为员工的姓氏可以有重复的，而电子邮箱必须唯一，我们就可以给姓氏创建一个常规索引，电子邮箱创建一个唯一索引，如下：

2479

同时，MySQL还提供了创建局部字段索引的特性，方式是：将某一字段的前N的字符作为索引，相比于单列常规索引，局部索引需要更少的磁盘空间，同时由于索引的数据短，因此效率会高一点。修改前面的示例，因为通常lastname的前5个字符就足以确保获取到唯一的数据，修改如下：

2686

多列常规索引

一般查询时，通常需要包含多列，我们可以指定一些经常被查询的列为多列常规索引。MySQL的多列常规索引方法基于一种最左前缀(leftmost prefixing)的策略。最左前缀指出包含列A、B和C的任何多列索引都可以提高涉及如下列组合的查询的性能：

  • A、B、C
  • A、B
  • A

创建多列索引：

2967

上图中创建了三个索引，第一个是主键索引，第二个是电子邮箱的唯一索引，第三个就是多列索引，包括两列：lastname和firstname。

全文索引

当字段类型为char、varchar或text时，我们可以使用全文索引。在MySQL中，对大量自然语言做全文搜索时，它提供了一种数据获取的机制，并生成最符合用户需要的结果。比如在对这样“Nginx is the world’s most popular web server”的字符串进行搜索时，单词is和the对结果几乎起不了任何作用，MySQL在搜索时，将文本分解为单词，默认忽略少于4个字符的单词。创建全文索引：

3330

创建全文索引的方式与创建其他索引没什么区别，但基于全文索引的查询却有一点不同。在查询时，select语句需要使用两个特殊的MySQL函数match()和againet()。查询方式如下：

3501

Boolean全文搜索

Boolean全文搜索对查询提供了更加细的控制允许显示地标识候选结果中应当或不应当出现哪些词（MySQL默认会忽略少于4个字符的关键词）。以下是关于Boolean操作符的描述：

+ 前导加号确保后面的单词出现在每个结果记录中
- 前导减号确保后面的单词不出现在任何结果记录中
* 结尾处的星号允许接受关键字变体，只要该变体以星号前面的单词所制定的字符串开头
" " 外围的双引号确保结果记录包含所包围的字符串，要严格按照输入时的形式出现
< > 前导的大于号和小于号分别用于增加和减少后面单词的搜索级别相关度
( ) 小括号用于将单词分组为子表达式

如下是几个简单的例子：

  1. 返回包含Nginx，但不包含manual的记录：

3913

  1. 返回包含单词Nginx，但不包含Apache或Tomcat的记录：

4023

  1. 返回包含web和scripting或者php和scripting的记录，但web scripting的搜索级别低于php scripting：

4171

注意，由于MySQL默认会忽略少于4个字符的单词，所以执行上面的SQL命令时，应该先修改ft_min_word_len参数。

索引选取的注意事项

  1. 只对WHERE和ORDER BY子句中需要的列添加索引，过多的索引会占用很多硬盘空间，在修改数据时还会降低性能，因为每次修改数据时都需要更新索引。
  2. 如果创建如INDEX(firstname, lastname)的索引时，不要再创建INDEX(firstname)，因为MySQL的索引采用最左前缀策略。
  3. 需要索引的列要不为空（NOT NULL），保证构建索引时不存储NULL值。
  4. 可以开启慢查询日志，通过对慢查询日志的分析来优化索引。

参考：
《PHP与MySQL程序设计（第四版）》

欢迎阅读本篇文章，如有兴趣可以关注博主公众号哦：