数据库索引

背景

  1.     MySQL凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。
  3.     虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。
  5.     我们知道，**一般的应用系统，读写比例在10:1左右**，而且**插入操作和一般的更新操作很少出现性能问题**，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以**查询语句的优化显然是重中之重**。

一个慢查询引发的思考

select
   count(*) 
from
   task 
where
   status=2 
   and operator_id=20839 
   and operate_time>1371169729 
   and operate_time<1371174603 
   and type=2;

系统使用者反应有一个功能越来越慢，于是工程师找到了上面的SQL。

并且兴致冲冲的找到了我，“这个SQL需要优化，给我把每个字段都加上索引”。

我很惊讶，问道：“为什么需要每个字段都加上索引？”

“把查询的字段都加上索引会更快”，工程师信心满满。

“这种情况完全可以建一个联合索引，因为是最左前缀匹配，所以operate_time需要放到最后，而且还需要把其他相关的查询都拿来，需要做一个综合评估。”

“联合索引？最左前缀匹配？综合评估？”工程师不禁陷入了沉思。

多数情况下，我们知道索引能够提高查询效率，但应该如何建立索引？索引的顺序如何？许多人却只知道大概。其实理解这些概念并不难，而且索引的原理远没有想象的那么复杂。

一，索引的概念

1，什么是索引：

    在数据库中，索引的含义与日常意义上的“索引”一词并无多大区别，与书中的索引一样，数据库中的索引使您可以**快速**找到表中的特定信息。

    索引包含从表中一个或多个列生成的键，以及映射到指定数据的存储位置的指针，也就是说索引由 键 和 指针组成。

    索引是用于提高数据库表数据访问速度的数据库对象。

    MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

    拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

    创建索引时，你需要**确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件**)。

2，建立索引的好处：

  • 索引可以避免全表扫描。带索引的查询可以仅扫描少量索引页及数据页，而不是遍历所有数据页即全盘扫描，加快找到目标数据的速度。
  • 一些情况下，索引还可用于避免排序操作。索引就是排序。比如说读需要有顺序的数据，会直接读索引，再通过索引，找到数据。一个索引只能对每个索引列支持一种排序规则。尽量利用索引排序，即取出的数据就是有序的。

3，建立过多的索引的坏处：

索引需要付出的代价和带来的弊端：

  • 1，增加了数据库的存储空间 ；建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但是如果你在一个数据量比较大的表上创建了多种组合索引，那么索引文件的增长会很快，这就显著增加了数据库的存储压力。
  • 2，在插入和修改数据时要花费较多的时间(因为索引也要随之变动)；虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
  • 3，对某些数据量不是很大的表，对于某些添加索引的行为，不但不能优化查询速度，反而会减慢查询速度，当然，如果索引的建立不恰当，所选择建立索引的字段不合适，也可能会削弱查询速度。

4，索引的目的：

    索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

    如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

    除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

    数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？

    我们回想字典的例子，能不能把数据分成段，然后分段查询呢？

    最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。

    但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

二，索引的数据结构

    前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，。

    我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：**每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级**。

    那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

1，页

    数据库文件存储是以页为存储单元的，一个页是8K（8192Byte），一个页就可以存放N行数据。

    我们常用的页类型就是`数据页`和`索引页`。

    一个页中除了存放基本数据之外还需要存放一些其他的数据，如页的信息、偏移量等。

    虽然SQLServer是以页为单位存储数据，但是其分配空间是以一个盘区为单位的（8个页=64K），这样做的目的主要是为提高I/O的性能。

2，磁盘IO与预读

    考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。

    **每一次IO读取的数据我们称之为一页(page)，页是数据库文件存储的存储单元**。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

3，什么是B树与B+树

3.1 ，什么是B树：

    B树中要求所有的叶节点都在同一层；

    2-3树是B树中的一项特例。2-3树分为两种，一种是2节点，一种是3节点。有两个子节点的节点叫二节点，有三个子节点的节点叫三节点。

    比如说像这种节点，有三个子节点，就是三节点；

数据库索引 - 图1

    像6这种节点，只有两个子节点，就是二节点。

数据库索引 - 图2

3.2，2-3树的要求：

    二节点要么有两个子节点，要么没有子节点；

    三节点要么有三个子节点，要么没有子节点。

    （同理，四节点就是要么有四个子节点，要么没有子节点。）

举例：

比如说这种情况，如果将10这个节点追加到6这个节点的下边，就不符合2-3树的要求了。

数据库索引 - 图3

所以正确的做法应该是将6这个二节点与10这个节点合并为一个三节点：

数据库索引 - 图4

    2-3树、2-3-4树这些其实都是B树，只不过都是B树的一种特例。

    什么是B树呢，其实就是2-3树、2-3-4树、2-3-4-5树...等等等等，将这些统称为B树。

    **在B树中，最大的节点的数字，称之为该B树的阶**。比如说2-3树是3阶的B树、2-3-4树就是4阶的B树；

3.3，什么是B+树呢？

    B+树是B树的一种变形。它相比于B树主要有两个变化。

1，非叶子结点只存储索引信息，不存储数据；

比如说这颗B+树，真正的数据只存储于它的叶子结点中，它的所有的非叶子结点只存储索引信息、只是用来帮助找到相应的数据用的。

数据库索引 - 图5

2， 每一个叶子节点最右边的指针，指向下一个相邻的叶子结点。
    比如说0001这个节点指向它右边的那个节点——0002、0003、0003；

    0002、0003、0003这个节点指向它右边的那个节点——0004、0005；

    ......

也就是说， 所有的叶子结点组成了一个有序链表 。

数据库索引 - 图6

3.4，B树与B+树的区别：

数据库索引 - 图7

3.5，B+树的优点：

    当真正的数据存储在硬盘中的时候，当将B+树中所有的**非叶子结点的信息也就是索引的信息都存储到内存中**以后，当我们想要找某一条数据的时候，**只需要通过一次硬盘IO就可以读取到相应的数据了**。

    就是说我们将更多的索引信息存储到内存中以后，我们就可以通过索引去快速的找到相应的数据。

    而如果是普通的B树的话，可能就需要去一次一次的读取数据和对比数据了。

    而在实际情境下很可能的情况是数据量非常大、索引量很小，所以我们就可以采用B+树的数据结构，将索引信息都存储在非叶子结点中，将数据都存储在叶子节点中，这样就可以快速的去找到相应的数据了。

    当然B+树和B树各有优缺点，各自适用于不同的实际场景中。

4，详解b+树：

数据库索引 - 图8

    如上图，是一颗b+树，关于b+树的定义可以参见[B+树](http://zh.wikipedia.org/wiki/B%2B%E6%A0%91)，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

    真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

    非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

4.1，b+树的查找过程

    如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。<br />        **真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的**，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

4.2，b+树性质

    通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；

    而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。

    这就是**为什么每个数据项，即索引字段要尽量的小**，比如int占4字节，要比bigint8字节少一半。

    这也是为什么**b+树要求把真实的数据放到叶子节点而不是内层节点**，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

    2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；

    但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

    比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

三， MySQL有哪几种索引以及SQL操作：

MySQL目前主要有以下几种索引类型：

  1. 普通索引
  2. 唯一索引
  3. 主键索引
  4. 组合索引
  5. 全文索引

1.普通索引

    普通索引是最基本的索引，它没有任何限制。功能就是加速查找。

    普通索引有以下几种创建方式：

1）直接创建索引
CREATE INDEX index_name ON table_name (column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

比如：

CREATE INDEX index_name ON table_name(column(length))

举例：

创建一个简单的索引，名为 “PersonIndex”，在 Person 表的 LastName 列：

CREATE INDEX PersonIndex ON Person (LastName)

如果希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：

CREATE INDEX PersonIndex ON Person (LastName DESC)

假如希望索引不止一个列，可以在括号中列出这些列的名称，用逗号隔开：（组合索引）

CREATE INDEX PersonIndex ON Person (LastName, FirstName)

2）在已有表的基础上追加索引：
ALTER table table_name ADD INDEX index_name(column_name)

ALTER table table_name ADD INDEX index_name(column_name(length))

3）创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

4）删除索引
DROP INDEX index_name ON table_name

2.唯一索引

    与前面的普通索引类似，唯一索引不同的就是：索引列的值必须唯一，但允许有空值。

    即，相比于普通索引，唯一索引，还多了一个**唯一性约束**的功能。

    如果是组合索引，则列值的组合必须唯一。

    它有以下几种创建方式：

1）创建唯一索引
CREATE UNIQUE INDEX index_name on table_name(column_name);

CREATE UNIQUE INDEX index_name ON table_name(column_name(length))

（2）修改表结构
ALTER TABLE table_name ADD UNIQUE index_name (column_name)

ALTER TABLE table_name ADD UNIQUE index_name (column_name(length))

（3）创建表的时候直接指定
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
)

3.主键索引

    主键索引是一种特殊的唯一索引，一般情况下也是表中默认的聚簇索引；

    一个表只能有一个主键；

    建立主键索引的列，**首先也具备唯一索引的性质，具有唯一性约束；除此之外还不允许有空值**。

    一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
     PRIMARY KEY (`id`)
)

4.组合索引

    组合索引是指在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

    使用组合索引时遵循最左前缀集合：

    添加组合索引：

CREATE INDEX index_name on table_name (column1, column2);

ALTER TABLE table_name ADD INDEX index_name (column1,column2,column3);

5.全文索引

    全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

    fulltext全文索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

    fulltext索引配合match against操作使用，而不是一般的where语句加like。

    它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 类型的列上可以创建全文索引。

（1）创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

（2）修改表结构添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_name(content)

（3）直接创建索引

CREATE FULLTEXT INDEX index_name ON table_name(content)

四，建立和使用索引的几个原则以及技巧

    使用索引时，有以下一些技巧和注意事项：

1、满足最左前缀匹配原则:

    WHERE可后接多个条件。一般地，执行引擎可以优化WHERE后多个条件的查询顺序。

    非常重要的原则，mysql会一直向右匹配，直到**遇到范围查询(>、<、between、like)就停止匹配**，比如：

    `a = 1 and b = 2 and c > 3 and d = 4`

    如果建立(a,b,c,d)顺序的索引，**d是用不到索引的**，

    如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

    WHERE的意图就是**在全量的数据中迅速缩小数据规模**。

2、=和in可以乱序:

    比如：建立(a,b,c)索引，where条件是： `a = 1 and b = 2 and c = 3` ，

    a = 1 、b = 2 、 c = 3 可以是任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3、尽量选择区分度高的列作为索引:

    区分度的公式是count(distinct col)/count(*)，表示字段的数据不重复的比例；

    字段内的数据不重复的比例越大，我们查询数据的效率越高；

4、尽量使用数据量小的列作为索引

    假定新的班级表结构：

    `学号（2020S010132）、班级内编号（56）、性别（男/女）、姓名（sugar）`

    新设置的**“班级内编号”列**在班级范围内**提供了不重复编号**。此时选择**“班级内编号”列**和选择**“学号”列**的比较速度，**显然是前者快咯**。

5、索引列不能参与计算：

    索引列不能参与计算，保持列“干净”，不要在列上进行运算，**否则将导致索引失效而进行全表扫描**。

    比如`from_unixtime(create_time) = ’2014-05-29’`就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，**需要先把所有元素都应用到函数中之后才能比较，显然成本太大**。

    所以语句应该写成`create_time = unix_timestamp(’2014-05-29’)`。

    例如：

--  这将导致数据库放弃索引，进行全表扫描：
SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6、 尽量的扩展索引，不要新建索引。

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

7、为常被用作查询条件的字段建立索引

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

索引的使用方法：

最后引用别人[姜敏(http://www.cnblogs.com/aspnet2008/)]曾经总结过的几句话来描述一下索引的使用原则：

8、不要设置过多的索引，合理应用组合索引

    不要设置过多的索引，过多的索引首先会带来更大的磁盘空间，而且在数据发生修改时，对索引的维护是特别消耗性能的。

    尽量不要包含多个列的排序，如果需要，**最好给这些列建立为一个组合索引**。

9、 避免对长字段列创建索引、尽量使用短索引。

    **索引字段要尽量的小**。

    对串列建立索引，应该尽量指定一个前缀长度。

    如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定前缀长度length。

    例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引，**改为char(10)或者char(20)就行了**。

    **短索引不仅可以提高查询速度，而且可以节省磁盘空间和I/O操作**。

10、TEXT类型的字段建立索引必须指定长度length:

    TEXT类型，必须指定前缀长度length。

    例如：

    create INDEX index_name on table_name(column_name(19)) --该字段是text类型，必须指定长度

11、索引不能包含有null值的列

    只要列中包含有null值，都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。

    所以我们在数据库设计时**不要让字段的默认值为null**。

12、尽量使用索引排序

    为了优化SQL语句的排序性能，最好的情况是避免数据库自己实现排序功能(比如 order by )，合理的SQL可以**利用索引避免和减少排序**，是一个不错的方法。

    因为**索引本身也是有序的**，**如果在需要排序的字段上面建立了合适的索引，那么就可以跳过排序的过程，提高SQL的查询速度**。

    对于不能利用索引避免排序的SQL，再考虑使用数据库自己实现排序功能以满足用户需求。

MySQL查询支持filesort(文件排序)和index(索引排序)两种方式的排序：

  • filesort是先把结果查出，然后在内存中或磁盘中进行排序操作，效率较低。
  • index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

详情见：https://blog.csdn.net/qq_44750696/article/details/123638577

13、like语句操作

    一般情况下不推荐使用like操作模糊查询，如果非使用不可，如何使用也是一个技巧：
  • like “%aaa%” 不会使用索引；
  • 而like “aaa%”可以使用索引。

14、不使用not in操作，谨慎使用in操作

    NOT IN 此操作是强列推荐不使用的，因为它不能使用表的索引，会导致数据库放弃索引、进行全表扫描。推荐用NOT EXISTS 或(外连接+判断为空)方案代替。

    关于in操作的使用也要谨慎，**如果是确定且有限的集合时，可以使用，如 IN (0，1，2)**。否则的话，查询效率会很低，而且查询结果容易有误。

15、隐藏索引

    MySQL 8开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。

    隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。

    **隐藏索引可以用来测试索引的性能**。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

alter table 表名 ALTER INDEX 索引别名 INVISIBLE;   --隐藏索引 
alter table 表名 ALTER INDEX 索引别名 VISIBLE;   --取消隐藏

五，索引无法命中的情况

索引无法命中的情况需要注意：

1）like查询是以%开头，索引不会命中

    **like '%xx'：**

    select * from tb1 where email like '%cn';

2）索引列不能参与函数计算：

    select * from tb1 where reverse(email) = 'wupeiqi';

3）使用了 or 条件的字段不是索引：

    使用了 or 条件的字段，至少有一个不是索引，就会导致索引失效，数据库进行全表扫描。

    select * from tb1 where nid = 1 or name = 'seven@live.com';  -- nid 和 name 字段起码有一个不是索引，就会导致索引失效。

注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引；

    **如果出现or，一个条件有索引、一个条件没有索引时，建议使用 union ，拼接多个查询语句。**

4）普通索引中，使用!=不等于不会走索引

    select * from tb1 where email != 'alex'

注意：如果是主键，则还是会走索引，比如：select * from tb1 where nid != 123

5）排序条件为索引，则select字段必须也是索引字段，否则无法命中

order by

当根据索引排序时候，select查询的字段如果不是索引，则不走索引。

比如：

select name from s1 order by email desc; -- 不走索引

走索引：

select email from s1 order by email desc;

特别：如果对主键排序，则还是走索引：

    select * from tb1 order by nid desc;

6）组合索引的最左前缀原则：

例如：

如果组合索引为：`(name,email)`
  • name and email — 使用索引
  • name — 使用索引
  • email — (不符合最左前缀原则)，不使用索引

六，什么是聚簇索引

1，聚簇索引的概念：

    **MySQL 里只有 INNODB 表支持聚集索引**，INNODB 表数据本身就是聚集索引。

    节点页只包含了索引列，叶子页包含了行的全部数据。聚簇索引“就是表”，因此可以不需要独立的行存储。

    聚簇索引不是一种索引类型，而是一种数据存储方式。innoDB的**聚簇索引实际上在同一个结构中保存了索引和数据行**。

    当表有聚簇索引时，它的行数据实际上存放在索引的叶子页中，因为无法同时把数据行存放在两个不同的地方，**所以一个表只能有一个聚簇索引**。

    换一种说法，聚簇索引是指关系表记录的物理顺序与索引的逻辑顺序相同。由于一张表只能按照一种物理顺序存放，一张表最多也只能存在一个聚簇索引。

    当创建表时创建了主键，MySQL会自动创建名为**PRIMARY**的特殊索引--主键索引， **该索引称为聚簇索引**。

    **PRIMARY主键索引是比较特殊的，**这个**索引本身与数据一起存储在同一个表中**。

    只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。集索引决定了表数据的存储顺序。

    如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

    除**PRIMARY**索引之外的其他索引，**称为二级索引或非聚簇索引**。

InnoDB的聚簇索引：

  • InnoDB对主键建立聚簇索引。
  • 如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。
  • 如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。
  • 聚簇索引（primary索引）：主键索引
  • 非聚簇索引（second索引）：二级索引
    什么是二级索引：叶子节点保存的不是指行的物理位置的指针，而是行的主键值。

聚簇索引的优点：

  • 可以把相关数据保存在一起，数据访问就更快。
  • 聚簇索引将索引和数据保存在同一个B-Tree中，因此获取数据比非聚簇索引要更快。

聚簇索引的缺点：

  • 更新聚簇索引列的代价很高，因为会强制每个被更新的行移动到新位置。
  • 当行数据比较稀疏或由于页分裂等原因，导致数据存储不连续时，全表扫描可能很慢。

2，非聚集索引是什么：

    除**聚簇索引之外的其他索引**，**称为二级索引或非聚簇索引**。

    非聚簇索引指的是非叶子节点按照索引的键值顺序存放，叶子节点存放索引键值以及对应的主键键值。

    非聚集索引并不是在物理上排列数据，即索引中的逻辑顺序并不等同于表中行的物理顺序，索引是指向表中行的位置的指针，这些指针本身是有序的，通过这些指针可以在表中快速定位数据。

    由于非聚集索引数据存储时无序的，所以在非聚集索引中指针包含数据行在数据页中的偏移量。即**指针由 数据页 + 数据行偏移量 组成。**

    非聚簇索引又称为二级索引，指的是：叶子节点保存的不是指行的物理位置的指针，而是行的主键值。

    这意味着通过二级索引查找行，存储引擎需要：
  • 1、找到二级索引的叶子节点获取对应的主键值；

  • 2、根据这个主键值去聚簇索引中查找到对应的行。

    这里需要两次B-Tree查找，而不是一次。

3，聚簇索引与非聚簇索引的区别

    在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：

    聚簇索引的叶子节点就是数据节点，而**非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针**。所以非聚簇索引需要两次B-Tree查找。

    因为非聚簇索引需要两次B-Tree查找，所以**聚簇索引有着更快的检索速度**。

    一般情况下**主键会默认是聚簇索引**，且一张表只允许存在一个聚簇索引。

    聚集索引和非聚集索引的根本区别是：**数据记录的排列顺序和索引的排列顺序是否一致**；

    聚集索引表记录的排列顺序与索引的排列顺序一致，**优点是查询速度快**，因为一旦具有第一个索引值的纪录被找到，**具有连续索引值的记录也一定在物理位置上紧跟其后**，从而缩小了搜索范围，对于返回某一范围的数据效果最好。

    聚集索引的缺点是**对表进行修改速度较慢**，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。

    非聚集索引的叶子层顺序并不与实际的数据页相同，而采用指向表中的记录在数据页中位置的方式。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。

七，其他

    本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论; 并针对遇到的典型案例做了详细的分析。

    **其实做了这么长时间的语句优化后才发现，任何数据库层面的优化都抵不上应用系统的优化**，同样是MySQL，可以用来支撑Google/FaceBook/Taobao应用，但可能连你的个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”

参考文献：

1.《高性能MySQL》 2.《数据结构与算法分析》

MySQL慢查询优化：

详情请见：

https://blog.csdn.net/qq_44750696/article/details/123621587

explain命令使用：

详情请见：

https://blog.csdn.net/qq_44750696/article/details/123626153