PHP面试指南2020-MySQL索引- 详解

什么是索引

索引是存储引擎用于快速找到记录的一种数据结构.

理解索引

一个常见的例子就是书的目录。我们都已经养成了看目录的习惯，拿到一本书时，我们首先会先去查看他的目录，并且当我们要查找某个内容时，我们会在目录中查找,然后找到该片段对应的页码，再根据相应的页码去书中查找。如果没有索引(目录)的话,我们就只能一页一页的去查找了。

在MySQL中,假设我们有一张如下记录的表:

如果我们希望查找到年龄为15的人的名字,在没有索引的情况下我们只能遍历所有的数据去做逐一的对比,那么时间复杂度是O(n).

而如果我们在插入数据的过程中, 额外维护一个数组,将age字段有序的存储.得到如下数组.

[10,15,18,20,21]
 |  |  |  |  |[x1,x4,x2,x3,x5]

下面的x是模拟数据再磁盘上的存储位置，这个时候如果我们需要查找15岁的人的名字，我们可以对盖数组进行二分查找，众所周知，二分查找的时间复杂度为O(logn)，查找到之后再根据具体的位置去获取真正的数据。

PS:MySQL中的索引不是使用的数组,而是使用的B+树(后面讲),这里用数组举例只是因为比较好理解.

索引分类

索引创建

ALTER TABLE `table_name` ADD INDEX index_name (`column`); #普通索引

ALTER TABLE `table_name` ADD UNIQUE (`column`); #唯一索引

ALTER TABLE `table_name` ADD PRIMARY KEY (`column`); #主键索引

ALTER TABLE `table_name` ADD FULLTEXT (`column`); #全文索引

ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`); #组合索引

索引能为我们带来什么

• 减少查询需要扫描的数据量(加快了查询速度)

• 减少服务器的排序操作和创建临时表的操作(加快了groupby和orderby等操作)

• 将服务器的随机IO变为顺序IO(加快查询速度).

索引有哪些缺点呢

• 索引占用磁盘或者内存空间

• 减慢了插入更新操作的速度

实际上,在一定数据范围内(索引没有超级多的情况下),建立索引带来的开销是远远小于它带来的好处的,但是我们仍然要防止索引的滥用.

聚族索引与非聚族索引的区别

• 按物理存储分类：聚簇索引(clustered index)、非聚簇索引(non-clustered index)

• 聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针

前缀索引和索引选择性

如果希望给一个很长的字符串上添加索引，那么可以考虑使用前缀索引。

在正式介绍前缀索引之前,我们先大概考虑一下索引的工作步骤，数据库使用索引进行查找的时候,一般是如下几步:

1. 在索引的B+树上找到对应的值,比如找到学校名称为卡塞尔学院的一条记录,并且拿到这条数据在磁盘上的地址。

2. 根据地址去磁盘上查找,拿到该条数据所有的值。

那么假如在所有的学校名称的值中，卡塞尔就可以唯一的标识这条数据，那么用卡塞尔来做索引是否可以达到和卡塞尔学院做索引相同的效果？

答案是肯定的，而使用卡塞尔的话，是可以减少索引的大小到原来的60%的。这就是前缀索引的作用。

前缀索引: 在对一个比较长的字符串进行索引时,可以仅索引开始的一部分字符,这样可以大大的节约索引空间,从而提高索引效率.但是这样也会降低索引的选择性.

索引的选择性: 不重复的值/所有的值. 可以看出索引的选择性为0-1,最高的就是该列唯一,没有重复值.所以唯一索引的效率是比较好的.

但是在一般情况下,较长的字符串的一些前缀的选择性也是比较好的,这个我们可以算出来.使用下面的语句:

select 
    count(distinct left(school_name,3))/count(*) as sch3, 
    count(distinct left(school_name,4))/count(*) as sch4,
    count(distinct left(school_name,5))/count(*) as sch5,
    count(distinct school_name)/count(*) as original
from 
    user;

其中查找到的original就是原本的选择性,sch3,sch4,sch5分别是取该列的前3,4,5个字符作为索引的时候的选择性.逐步增加这个数值,当选择性与原来相差不大的时候,就是一个比较合适的前缀索引的长度.(一般情况下是这样,但是也有例外,当数据极其不均匀时,这样的前缀索引会在某个特殊的case上表现很差劲).

找到合适的长度之后,就可以创建一个前缀索引了:alter table user add index sch_pre3(school(3))

注意:前缀索引和覆盖索引是很难一起使用的,我今天早上刚试过,对索引的优化进行到这一步之后无功而返,具体的原因在下面介绍完覆盖索引之后解释.

联合索引

一般我们都是有对多个列进行索引的需求的,因为查询的需求多种多样.这个时候我们可以选择建立多个独立的索引或者建立一个联合索引.大多数时候都是联合索引更加合适一些.

假设我们要执行这个语句:select * from user where school_name = '卡塞尔' and age > 20,我们在school和age上分别建立两个独立的索引,那么我们预期这条查询语句会命中两个索引,但是使用explain命令查看会发现不一定.这是一个玄学的过程.个人没有研究清楚.

从理论上来讲,MySQL在5.0之后的版本里面对支持合并索引,也就是同时使用两个索引,但是MySQL的优化器不一定这样认为,他可能会认为,查询两次B+树的代价高于查询一次索引之后去数据表进行过滤,因此会选择只用一个索引.(我在自己的5张表上做了类似此case的测试,结果都是只使用了一个索引.)

创建联合索引的语法:alter table user add index school_age(school,age).

使用联合索引的时候,有一个非常重要的因素就是所有的索引列只可以进行最左前缀匹配,例如上面的school_age联合索引,当仅使用age作为查询条件的时候是不能使用的,也就是说select * from user where age =20是不能命中上面的联合索引的.

在不考虑任何查询的情况下,我们应该讲选择性高的列放在联合索引的前面,但是实际上我们更多的是通过查询来反推索引,以使某个固定的查询可以尽可能的命中索引以提高查询速度.毕竟我们建立索引的目的也是为了加快查询的速度.

因此联合索引的优化更多的是根据某个或者某些语句来优化的,不具备一个通用的法则.

最左前缀索引的原理

当数据列有序的时候,mysql可以使用索引,那么假设我们建立了school_age索引,示例数据如下:

在这份数据中,school字段是完全有序的,索引school可以使用索引.

而从全表来看,age字段不是有序的,因此无法直接使用索引,那么观察一下数据表,在什么时候age有序呢?在school进行定值匹配的时候,例如当school=b的时候,对于这三条数据而言,age是有序的,因此可以使用age索引.这就是最左前缀的原理.

此外,最左前缀索引只能使用一个范围查询,例如select * from user where school > a,select * from user where school = a and age > 12,都是可以命中索引的,但是select * from user where school > a and age > 12中,仅school可以命中索引,这也可以从上面得出结论.因为当school是范围匹配的时候,mysql无法确认age字段是否严格有序,比如 school的范围匹配命中了b,c的四条数据,那么age就不是有序的.无法使用后续的索引.

聚簇索引

聚簇索引不是一种索引类型,而是一种存储数据的方式.Innodb的聚簇索引是在同一个数据结构中保存了索引和数据.

因为数据真正的数据只能有一种排序方式,所以一个表上只能有一个聚簇索引.Innodb使用主键来进行聚簇索引,没有主键的话就会选择一个唯一的非空索引,如果还还没有,innodb会选择生成一个隐式的主键来进行聚簇索引.为什么innodb这么执着的需要搞一个聚簇索引呢,因为一个数据表中的数据总得有且只有一种排序方式来存储在磁盘上,因此这是必须的.

这也是innodb推荐我们使用自增主键的原因,因为自增主键自增且连续,在插入的时候只需要不断的在数据后面追加即可.设想一下使用UUID来作为主键,那么每一次的插入操作,都需要找到当前主键在已排序的主键中的位置,然后插入,并且要移动该主键后的数据,以使得数据和主键保持相同的顺序,这无疑是代价非常高的.

也是因为这个原因,在其他索引的叶子节点中,存储的”数据”其实不是该数据的真实物理地址,而是该数据的主键,查找到主键之后,再根据主键进行一次索引,拿到数据.

聚簇索引和非聚簇索引的区别可以用一个简单的例子来说明:

当我们拿到一本书的时候,目录就是主键,是一个聚簇索引,因为在目录中连续的内容,在正文中也是连续的,当我们想要查看迎着阳光盛大逃亡章节,只需要在目录中找到它对应的页面,比如459,然后去对应的页码查看正文即可.

而非聚簇索引呢,则类似于书后面的附录专有名词索引一样(二级普通索引),当你查找邦达列夫的时候,附录会告诉你,这个名词出现在了迎着阳光盛大逃亡一节,然后你需要去目录(主键索引)中再次查找到对应的页码.

覆盖索引

当一个索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引.

设想有如下的查询语句:

select 
  school_name,age
from  
  user
where 
  school_name = '金色莺尾花学院'

这个语句根据学校名称来查询数据行的学校名称和年龄,从上面的数据查询的步骤我们可以知道,当在索引中找到要求的值的时候,还需要根据主键去进行一次索引,以拿到全部的数据,然后从其中挑选出需要的列,返回.但是现在索引中已经包含了所有的需要返回的列,那么就不用进行回数据表查询的操作了,此外索引的大小一般是远远小于真正的数据大小的,覆盖索引可以极大的减少从磁盘加载数据的数量.

为什么前缀索引和覆盖索引无法一起使用?

因为前缀索引的目的是用前缀来代表真正的值,他们在选择性上几乎没有区别,但是MySQL仍然无法判断真正的数据是什么,比如阿里巴巴和阿里妈妈在前缀为2的时候是一样的,但是为了确保你查询阿里巴巴的时候不会出现阿里妈妈的内容,是需要回到数据表拿到数据再次进行一个精准匹配来进行过滤的.

因此,覆盖索引无法和列前缀索引一起使用。

删除掉冗余和重复的索引

有一些索引是从未在查询中使用过,却白白增加数据插入时开销的,对于这种索引我们应该及时的进行删除.

比如在主键上再建立一个普通索引,无疑是毫无作用的.

还比如在有联合索引school_age的情况下,再建立一个school的独立索引,因为索引的最左前缀匹配原则,school_age是完全可以命中对school的单独查询的,因此后者可以删掉.

如何查看索引的一些相关信息

索引信息

在mysql中可以使用show index from table_name来查看某个表上的索引,它将会有如下的输出:

或者使用show create table table_name来查看建表语句,其中包含创建索引的语句.

索引大小

在5.0以后的版本中,我们可以通过查看information_schema.TABLES表中的数据来获取更加详细的数据.

该表各字段的含义如下表:

我们可以通过一些查询语句来获取详细的信息,比如:

// 查看当前MySQL服务器所有索引的大小(以MB为单位,默认是字节)SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES// 查看某一个库的所有大小SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'XXX';// 查看某一个表的索引大小SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'yyyy' and table_name = "xxxxx";  // 汇总查看一个库中的数据大小及索引大小SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'xxxxx';

对tables表的数据的所有查看方式都是可以的,其中还包含了一些表格本身的数据信息,但是因为和本文的主题不符合,这里就不举例子了.

注意:上面的表格是有缓存的,当更新数据库索引之后,最好执行analyze table xxxx,然后再进行查看.MySQL会在表格数据发生较大的变化时才更新此表(大小变化超过1/16或者插入20亿行).

索引碎片

在索引的创建删除过程中,不可避免的会产品索引碎片,当然还有数据碎片,我们可以通过执行optimize table xxx来重新整理索引及数据,对于不支持此命令的存储引擎来说,可以通过一条无意义的alter语句来触发整理,比如:将表的存储引擎更换为当前的引擎,alter table xxxx engine=innodb.

btree索引和hash索引的区别

Innodb和MyISAM默认的索引是Btree索引。

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

可能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，

Hash 索引本身由于其特殊性，也带来了很多限制和弊端

（1）Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下

mysql索引类型normal，unique，full text的区别

normal：表示普通索引

unique：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique。

full textl: 表示 全文搜索的索引。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

索引本身的实现（数据结构）

我们在线上遇到慢查询的情况，一般第一个想到的优化方式就是给where语句后的字段加索引，虽然效果是立竿见影的，但这通常是懒人做法。一方面是因为索引并不是都会生效，可能出现加了索引，查询依旧慢的问题，另一方面，索引会占用磁盘空间。

但是，这并不妨碍我们在遇到慢查询的时候，第一个想到的解决方案就是加索引，那么，为什么加了索引之后，就能优化慢查询，提升查询速度？

其实，索引就是一种优化查询的数据结构，MySQL中的索引就是用B+树实现的。那么为什么MySQL会选择B+树作为索引的实现数据结构呢？它和哈希表、完全平衡二叉树、B树有什么不同？

假设，我们现在有下面的user表：

① 哈希表

我们知道，hashMap(1.7)底层就是通过哈希表来实现的，即，数组+链表的方式。
哈希表的缺点有两个: 一、hash冲突，二、只支持精确查询，不支持范围查询，如果我们要某个年龄大于18的用户，如下：

select * from user where name = '关羽'; // 精确查找select * from user where name > '关羽'; // 范围查找

这种情况哈希表并不能实现，所以，哈希表不适合做MySQL的索引数据结构。

②完全平衡二叉树

平衡二叉树的每个节点都包含下面四部分信息：

1. 左指针，指向左子树

2. 键值

3. 键值所对应的数据存储地址

4. 右指针，指向右子树

另外，二叉树是有序的，简而言之，就是左节点小于右节点，所以，平衡二叉树是支持范围查找的，但是，在精确查找的时候，会涉及到多次，比如，查刘备，需要查询三次才能找到，比哈希表的精确查找要慢。

③ B树

可以看到，B树在层级上比平衡二叉树要少一层，即少一次磁盘IO，原因在于，B树中的一个节点可以存储多个元素。

④ B+树

B+树的叶子节点和B树是一样的，只不过冗余了一分非叶子节点的数据

B+树比B树要胖一些，原因在于B+树中的非叶子节点会冗余一分在叶子节点中，并且叶子节点之间用指针相连。

综上，我们可以看出，有三种数据结构是适合做MySQL索引的数据结构的，平衡二叉树、B树、B+树。这三种数据结构都支持精确查找和范围查找，那么为什么MySQL却选中了B+树作为索引的数据结构呢？

其实，索引也是存储元素的，当我们的一个表中的数据越来越多时，对应的索引文件也会越来越大，这样就不能把全部的索引文件放在内存，不得不将索引文件存储在磁盘上，那么选用哪种数据结构，能够提高磁盘的IO效率，就成了参考项。

如果使用完全平衡二叉树来查询“张飞”，则需要四次IO，而使用B树的话，只要三次就可以了，提升了磁盘ＩＯ效率，而Ｂ+树和B树的非叶子节点是一样的，只不过是叶子节点冗余了一份非叶子节点的数据。所以，在精确查找上，B树和B+树是一样的，而B+树在范围查找上优于B树。

MySQL为什么要选择B+树来作为索引的数据结构

重点：B+树能提高查询效率，降低磁盘IO

1、B+数是由B-数演变而来，所以B+数拥有B-数的所有特性
2、B+树的非叶子节点只保存关键字和子节点的地址，而叶子节点保留了当前路节点的所有节点的关键字、数据区和地址，所以要得到节点的数据就要到叶子节点上去获取，所以我们每次对数据的检索的时间都差不多，不像其他树，非叶子节点也有保留数据区，这样子当数据量庞大，当检索第一个跟最后一个的索引时间就相差比较大
2、B+树是一颗多路平衡查找树，由于它是多路的，所以它的高度比其他二叉树都矮，树的高度决定了检索数据的时间复杂度
计算机默认检索的一页是4k，而mysql对这个4k做了调整增加到16k，这个一页是16k，假如这里保存的是一个id的索引树，那id设置为int类型，一个int类型为4个字节，那这一页可以保存的id的个数就可以这样算（（16* 1024）/4），所以索引的类型和字节数都决定了数据库检索数据的效率，所以该id树的一个节点可以设置的路数就为（（16*1024）/4）路，所以这一页就可以保存这么多数据，一次加载到内存中就可以加载那么多，充分利用了计算机的IO读取性能和空间局部性原理，极大降低了计算机IO的次数
3、B+树的叶子节点上保存一个指针，这个指针指向的是下一个叶子节点的指针，譬如第一路的叶子节点上数据有567这三个树，而第二路有8910，则第一路的7有个指针会指向第二路的8，这样做的好处是使数据自带有顺序性的特性，这个顺序性在我们做一个范围查询时，性能就得到充分的发挥，这个指针也是B-树跟B+树的区别之一

总而言之：

B+树是B-树的变种（PLUS版）多路绝对平衡查找树，他拥有B-树的特点（优势 ） 
B+树扫库、表能力更强  
B+树的磁盘读写能力更强  
B+树的排序能力更强  
B+树的查询效率更加稳定

创建索引需要注意的点（原则）

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据更新的表，不要建立太多的索引，会早晨很多索引碎片；
9、删除无用的索引，避免对执行计划造成负面影响；

一般选择在这样的列上创建索引

1. 在经常需要搜索查询的列上创建索引，可以加快搜索的速度；

2. 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构；

3. 在经常用在连接的列上创建索引，这些列主要是一些外键，可以加快连接的速度；

4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询 时间；

6. 在经常使用在Where子句中的列上面创建索引，加快条件的判断速度；

7. 为经常出现在关键字order by、group by、distinct后面的字段，建立索引。

一般不选择具有这些特点的列上创建索引

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查 询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求；

2. 不要在有大量相同取值的字段上，建立索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加 快检索速度；

3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少；

4. 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

创建索引需要注意的地方

1. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

2. 避免在取值朝一个方向增长的字段（例如：日期类型的字段）上，建立索引；对复合索引，避免将这种类型的字段放置在最前面。由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。

3. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

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