1.mysql 索引你知道哪些？

• 按「数据结构」分类：B+tree 索引、Hash 索引、Full-text 索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

1.为什么使用 B+树索引?

Mysql 选⽤ B+树这种数据结构作为索引

• 提⾼查询索引时的磁盘 IO 效率
• 可以提⾼范围查询的效率
• B+树⾥的元素也是有序的

B+树索引是一种常见的数据库索引结构，它被广泛用于提高数据库查询性能和管理大量数据。以下是为什么使用 B+树索引的一些主要原因：

1. 高效的数据检索：B+树索引是一种平衡树结构，它具有固定高度，通常很矮。这意味着无论数据量多大，数据检索的时间复杂度仍然是 O(log N)，其中 N 是数据项的数量。这使得 B+树在数据检索方面非常高效，适用于大型数据库。

2. 排序和范围查询：B+树索引非常适合支持排序和范围查询。由于树的有序性质，可以很容易地执行小于、大于、小于等于、大于等于等范围查询操作。

3. 数据存储和维护效率：B+树索引的内部结构使得它适合于磁盘存储。数据在叶子节点上存储，而非叶子节点只包含索引信息，这降低了磁盘 I/O 的需求。此外，B+树的平衡性质使得插入和删除操作相对高效，因为它不会导致树的不平衡。

4. 支持多级索引：B+树索引支持多级索引，这意味着可以在多个列上创建索引，从而满足不同类型的查询需求。这种灵活性对于复杂的查询和数据模型非常重要。

5. 支持聚簇索引：B+树索引通常用于聚簇索引的实现。聚簇索引将表的数据存储与索引结合在一起，进一步提高了查询性能，因为不需要额外的查找步骤来获取实际数据。

6. 适用于高并发环境：B+树索引对于高并发数据库系统非常有效。由于 B+树的结构和锁定机制，多个事务可以并发地访问索引，而不会导致严重的性能问题或数据冲突。

总的来说，B+树索引是数据库管理系统中常用的索引类型，因为它在数据检索、范围查询、数据存储和维护等方面提供了高效性能和灵活性。然而，索引的选择应该根据具体的数据库应用和查询需求来进行，因为不同类型的索引可能更适合特定的用例。

2.什么是聚集索引?

InnoDB 存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵 B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每个数据页之间都通过一个双向链表来进行链接。 由于实际的数据页只能按照一棵 B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在 B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据 如:用户需要查询一张用户注册表，查询最后注册的 10 位用户，由于 B+树索引是双向链表的，用户可以快速找到最后一个数据页，并取出 10 条记录

SELECT * FROM Profile ORDER BY id LIMIT 10;

虽然使用 ORDER BY 对主键 id 记录进行排序，但是在实际过程中并没有进行所谓的 file sort 操作，而这就是因为聚集索引的特点。另一个是范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。如：

SELECT * FROM Profile where id>1 and id<100;

聚簇索引与非聚簇索引的区别:

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引。

在MySQL的InnoDB存储引擎中， 聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询。

• 一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个。
• 一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表。

3.什么是辅助索引?

对于辅助索引（Secondary Index，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由于 InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。 辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为 3 的辅助索引树中查找数据，那需要对这棵辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度同样为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此一共需要 6 次逻辑 IO 访问以得到最终的一个数据页。

4.什么是二级索引?

MySQL 的二级索引，也称为辅助索引或非聚集索引，是一种用于提高数据库查询性能的索引类型。与主键索引（聚集索引）不同，二级索引不是基于表的物理顺序排序的，而是独立于表数据的额外数据结构。

二级索引允许您在表中的一个或多个列上创建索引，以加快特定列或列组合的查询速度。当您在一个或多个列上创建二级索引后，MySQL 将会构建一个索引数据结构，该数据结构包含索引列的值和指向实际数据位置的指针。这样，当查询需要使用索引列进行筛选或排序时，MySQL 可以直接使用二级索引来定位相关的数据行，而无需扫描整个表。

与主键索引不同，二级索引不是表的物理排序顺序，因此在使用二级索引进行查询时，MySQL 可能需要额外的 I/O 操作来访问实际的数据行。这就是为什么使用二级索引可能会比使用主键索引稍慢的原因。然而，二级索引的存在仍然可以大大提高查询性能，特别是对于那些频繁使用特定列进行筛选或排序的查询。

需要注意的是，当对表进行更新（插入、更新或删除）时，MySQL 还需要维护二级索引的一致性，以保证索引的准确性和完整性。因此，在创建二级索引时，需要权衡查询性能和更新性能之间的平衡。

5.什么是自适应 hash 索引?

自适应哈希索引采用之前讨论的哈希表的方式实现。不同的是，这仅是数据库自身创建并使用的，DBA 本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如

SELECT * FROM TABLE WHERE index_col='xxx';

但是对于范围查找就无能为力了。通过命令

SHOW ENGINE INNODB STATUS;

可以看到当前自适应哈希索引的使用状况。

6.什么是全文索引?

当前 InnoDB 存储引擎的全文检索还存在以下的限制:

• 每张表只能有一个全文检索的索引。

• 由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。

• 不支持没有单词界定符(delimiter)的语言，如中文、日语、韩语等。

#添加全文索引
alter  table  mysql_user  add  fulltext  (name);

SELECT * FROM blog WHERE content like '%xxx%';

根据 B+树索引的特性，上述 SQL 语句即便添加了 B+树索引也是需要进行索引的扫描来得到结果。类似这样的需求在互联网应用中还有很多。例如，搜索引擎需要根据用户输入的关键字进行全文查找，电子商务网站需要根据用户的查询条件，在可能需要在商品的详细介绍中进行查找，这些都不是 B+树索引所能很好地完成的工作。 全文检索（Full-TextSearch）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。 在之前的 MySQL 数据库中，InnoDB 存储引擎并不支持全文检索技术。大多数的用户转向 MyISAM 存储引擎，这可能需要进行表的拆分，并将需要进行全文检索的数据存储为 MyISAM 表。这样的确能够解决逻辑业务的需求，但是却丧失了 InnoDB 存储引擎的事务性，而这在生产环境应用中同样是非常关键的。 从 InnoDB1.2.x 版本开始，InnoDB 存储引擎开始支持全文检索，其支持 MyISAM 存储引擎的全部功能，并且还支持其他的一些特性。 InnoDB 存储引擎从 1.2.x 版本开始支持全文检索的技术，其采用 full inverted index 的方式。在 InnoDB 存储引擎中，将(DocumentId，Position)视为一个“ilist”。因此在全文检索的表中，有两个列，一个是 word 字段，另一个是 ilist 字段，并且在 word 字段上有设有索引。 全文检索通常使用倒排索引（inverted index)来实现。倒排索引同 B+树索引一样，也是一种索引结构。它在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：

• inverted file index，其表现形式为{单词，单词所在文档的 ID}
• full inverted index，其表现形式为{单词，(单词所在文档的 ID，在具体文档中的位置)}

从图 1 可以看出，可以看到单词 code 存在于文档 1 和 4 中，单词 days 存在与文档 3 和 6 中。 从图 2 可以看出，full inverted index 还存储了单词所在的位置信息，如 code 这个单词出现在（1∶6），即文档 1 的第 6 个单词为 code。相比之下，full inverted index 占用更多的空间，但是能更好地定位数据

7.全文索引的语法有了解吗?

MySQL 数据库支持全文检索（Full-TextSearch）的查询，其语法为：

MATCH(col1，col2，...)AGAINST(expr[search_modifier])
search_modifier:
{
  IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}

MySQL 数据库通过 MATCH()…AGAINST()语法支持全文检索的查询，MATCH 指定了需要被查询的列，AGAINST 指定了使用何种方法去进行查询。

NATURAL LANGUAGE MODE

全文检索通过 MATCH 函数进行查询，默认采用 Natural-Language 模式，其表示查询带有指定 word 的文档

SELECT*
FROM mysql_user
WHERE MATCH(name) AGAINST('张三' IN NATURAL LANGUAGE MODE);

BOOLEAN MODE

MySQL 数据库允许使用 IN BOOLEAN MODE 修饰符来进行全文检索。当使用该修饰符时，查询字符串的前后字符会有特殊的含义，例如下面的语句要求查询有字符串张三但没有 hot 的文档，其中+和-分别表示这个单词必须出现，或者一定不存在。

SELECT*
FROM mysql_user
WHERE MATCH(name) AGAINST('+张三-hot' IN BOOLEAN MODE);

WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

MySQL 数据库还支持全文检索的扩展查询。这种查询通常在查询的关键词太短，用户需要 implied knowledge（隐含知识）时进行。例如，对于单词 database 的查询，用户可能希望查询的不仅仅是包含 database 的文档，可能还指那些包含 MySQL、Oracle、DB2、RDBMS 的单词。而这时可以使用 QueryExpansion 模式来开启全文检索的 implied knowledge。

SELECT*
FROM mysql_user
WHERE MATCH(name) AGAINST('张三' WITH QUERY EXPANSION);

8.什么是覆盖索引?

InnoDB 存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是覆盖索引不包含整行记录的所有信息，故其大小要远小于聚集索引， 因此可以减少大量的 IO 操作。

覆盖索引就是查询的结果中全部包含了索引字段

如果要查询辅助索引中不含有的字段，得先遍历辅助索引，再遍历聚集索引，而如果要查询的字段值在辅助索引上就有，就不用再查聚集索引了，这显然会减少 IO 操作。

覆盖索引的好处:

• 不会回表，在查询结果中有列信息和主键信息
• 统计操作 count 时，优化器会进行优化，选择覆盖索引

执行 count(*)的执行计划，possible_keys 列为 NULL，但是实际执行时优化器却选择了 userid 索引，而列 Extra 列的 Using index 就是代表了优化器进行了覆盖索引操作。

此外，在通常情况下，诸如(a，b)的联合索引，一般是不可以选择列 b 中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择.所以在使用联合索引时，带头的兄弟断了，也有可能使用到索引.

9.优化器选择覆盖索引情况?

InnoDB 存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录（此时不能够使用 select * 操作，只能对特定的索引字段进行 select），而不需要查询聚簇索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚簇索引，因此可以减少大量的 IO 操作。 对于 InnoDB 存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primarykey1，primarykey2，…，key1，key2，…）。例如，下列语句都可仅使用一次辅助联合索引来完成查询：

• select count(*) from table;
• select 覆盖索引 from table;

SELECT key2 FROM table WHERE key1=xxx；

SELECT primary key2，key2 FROM table WHERE key1=xxx；

SELECT primary key1，key2 FROM table WHERE key1=xxx；

覆盖索引的另一个好处是对某些统计问题而言的。还是对于上题创建的表 buy_log，要进行举例说明。

SELECT COUNT(1) FROM buy_log;

InnoDB 存储引擎并不会选择通过查询聚集索引来进行统计。由于 buy_log 表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少 IO 操作。 在通常情况下，诸如（a，b）的联合索引，一般是不可以选择列 b 中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择，如下述语句：

explain SELECT COUNT(1) FROM buy_log WHERE buy_date >=  '2011-01-01' AND buy_date <='2011-02-01'

表 buy_log 有（userid，buy_date）的联合索引，这里只根据列 b 进行条件查询，一般情况下是不能进行该联合索引的，但是这句 SQL 查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引：

从图中可以发现列 possible_keys 为 userid_2，列 key 为 userid_2，即表示（userid，buy_date）的联合索引。在列 Extra 同样可以发现 Using index 提示，表示为覆盖索引。

不符合最左前缀时也可以走索引:全扫描联合索引树的方式查询到数据

执行计划里的 type 是 index，这代表着是通过全扫描联合索引树的方式查询到数据的，这是因为 where buy_date 并不符合联合索引最左匹配原则。

那么，如果写了个符合最左原则的 select 语句，那么 type 就是 ref，这个效率就比 index 全扫描要高一些。

因为联合索引树的记录比要小的多，而且这个 select * 不用执行回表操作，所以直接遍历联合索引树要比遍历聚集索引树要小的多，因此 MySQL 选择了全扫描联合索引树。

10.什么是联合索引?

联合索引是有多个索引列组成的索引.

# 创建联合索引
CREATE TABLE tmp_kwan_muti_fileld
(
    a INT，
    b INT，
    PRIMARY KEY (a)，
    KEY idx_a_b (a， b)
) ENGINE = INNODB;

SHOW INDEX FROM tmp_kwan_muti_fileld;

联合索引是指对表上的多个列进行索引

CREATE TABLE buy_log(
	userid INT UNSIGNED NOT NULL，
	buy_date DATE
)ENGINE=InnoDB;
ALTER TABLE buy_log ADD KEY(userid);
ALTER TABLE buy_log ADD KEY(userid，buy_date);

以上代码建立了两个索引来进行比较。两个索引都包含了 userid 字段。 情况 1：如果只对于 userid 进行查询，如：

SELECT * FROM buy_log WHERE userid=2;

索引选择：优化器最终的选择是索引 userid，因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多。 情况 2：

SELECT * FROM buy_log WHERE userid=1 ORDER BY buy_date DESC LIMIT 3；

索引选择：优化器使用了（userid，buy_date）的联合索引 userid_2，因为在这个联合索引中 buy_date 已经排序好了。根据该联合索引取出数据，无须再对 buy_date 做一次额外的排序操作。 情况 3：假如三个字段的联合索引。如：对于联合索引（a，b，c）来说，下列语句同样可以直接通过联合索引得到结果，不需要 filesort 的排序操作：

SELECT * FROM TABLE WHERE a=xxx ORDER BY b;
SELECT * FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c;

但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次 filesort 排序操作，因为索引（a，c）并未排序：

CREATE TABLE buy_log_01(
	a INT UNSIGNED NOT NULL，
  b INT  default NULL，
	c DATE
)ENGINE=InnoDB;
ALTER TABLE buy_log ADD KEY(a，b，c);

explain SELECT * FROM buy_log_01 WHERE a='xxx' ORDER BY c;

• Using index:使用到了 a 索引
• Using filesort:需要对 c 进行排序

explain SELECT b from buy_log_01 where b=1

• Using index:使用到了 a 索引

• Using where:使用了筛选条件

• 联合索引中只用到了 b 字段进行查询，也用到了索引

11.如何创建联合索引

联合索引（也称为复合索引或组合索引）是数据库中的一种重要优化工具，用于提高数据库查询性能。创建联合索引时需要考虑一些原则，以确保索引的有效性和效率。以下是创建联合索引的一些原则：

1. 选择合适的列组合： 联合索引涉及多个列，因此需要仔细选择哪些列应该包含在索引中。选择常用于查询条件、连接条件或排序操作的列。避免在联合索引中包含过多的列，因为这可能会增加索引维护的开销，并且不一定会带来更好的性能提升。

2. 列的顺序： 列的顺序对联合索引的性能影响很大。应该将最常用于过滤数据的列放在索引的前面，这有助于数据库更快地定位到所需的数据。如果经常使用的列在索引的后面，数据库可能需要更多的操作才能达到所需的数据行。

3. 避免重复列： 不要在联合索引中重复包含相同的列。如果某列已经在索引的前面出现，就不需要再将其放在后面的位置。

4. 平衡性能需求： 联合索引需要权衡查询性能和索引维护的成本。过多的索引可能会导致写操作（如插入、更新和删除）变慢，因为数据库需要维护多个索引。因此，需要根据实际场景综合考虑查询性能和写操作性能。

5. 考虑查询选择性： 索引的选择性是指索引列中不同值的数量与总行数的比例。选择性越高，索引在过滤数据时的效率越高。因此，选择选择性较高的列作为索引列通常会带来更好的性能。

6. 避免过度索引： 不要为每个可能的查询都创建一个新的联合索引。过多的索引可能会导致索引失效，降低性能。根据实际使用情况选择最有价值的查询来创建索引。

7. 定期维护索引： 随着数据的增加和变化，索引的性能可能会下降。定期重新组织或重建索引，以保持其效率。数据库管理系统通常提供了工具来进行索引维护。

8. 考虑内存和存储限制： 索引需要内存和存储空间来维护。过多的索引可能会占用大量的资源。在创建索引时要考虑数据库服务器的内存和存储容量。

创建联合索引需要权衡多个因素，包括查询模式、写操作频率、数据量和数据库系统的特性。根据实际情况和性能测试，选择合适的列组合和顺序，以提高数据库的查询性能和整体效率。

举例:

有个笔试一道题：一张表，sql 语句是这样,select * from XXwhere sex=x and shengfen=x order by idCard limit 10,最好的建立索引的方式是什么?sex 两个类别，idCare-身份证，shengfeng-省份，如何建立联合索引呢?

首先排序字段不能放在联合索引的左边，因为排序字段后面的字段就没办法走索引了。所以建立联合索引 idCare 不能放在最左边。

然后 shengfen 和 sex，shengfen 的区分度会更高，所以可以把 shengfen 作为最左边的字段，因此 (shengfen,sex,idCare)会比较好,这个联合索引索引可以保证三个字段都能走索引。

如果是联合索引，那么 key 也由多个列组成，同时，索引只能⽤于查找 key 是否存在（相等），遇到范围查询 (>、<、between、like 左匹配)等就不能进⼀步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。

12.什么是最左前缀原则?

CREATE TABLE mysql_user
(
    id   INT UNSIGNED NOT NULL，
    name VARCHAR(64) DEFAULT NULL，
    age  int         DEFAULT NULL
)ENGINE=InnoDB;

INSERT INTO kwan.mysql_user (id， name， age) VALUES(100， '张一'， 10);
INSERT INTO kwan.mysql_user (id， name， age) VALUES(300， '张二'， 20);
INSERT INTO kwan.mysql_user (id， name， age) VALUES(400， '张三'， 40);
INSERT INTO kwan.mysql_user (id， name， age) VALUES(600， '李四'， 10);

如果我们按照 name 字段来建立索引的话，采用 B+树的结构，大概的索引结构如右图：

如果我们要进行模糊查找，查找 name 以“张"开头的所有人的 ID，即 sql 语句为

select ID from mysql_user where name like '张%';

由于在 B+树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的，索引在查找的时候，可以快速定位到 ID 为 100 的张一，然后直接向右遍历所有张开头的人，直到条件不满足为止。 也就是说，我们找到第一个满足条件的人之后，直接向右遍历就可以了，由于索引是有序的，所有满足条件的人都会聚集在一起。 而这种定位到最左边，然后向右遍历寻找，就是我们所说的最左前缀原则。

13.联合索引习题

#联合索引
index(a,b,c)

#查询语句
1. select * from T where a=x and b=y and c=z;
2. select * from T where a=x and b>y and c=z;
3. select * from T where c=z and a=x and b=y;
4. select (a，b) from T where a=x and b>y;
5. select count(*) from T where a=x;
6. select count(*) from T where b=y;
7. select count(*) form T;

1. a、b、c 三个字段都可以走联合索引。
2. a 和 b 都会走联合索引，但是由于最左匹配原则， 范围查找后面的字段是无法走联合索引的，但是在 mysql 5.6 版本后，c 字段虽然无法走联合索引，但是因为有索引下推的特性，c 字段在 inndob 层过滤完满足查询条件的记录后，才返回给 server 层进行回表，相比没有索引下推，减少了回表的次数。
3. 查询条件的顺序不影响，优化器会优化，所以 a、b、c 三个字段都可以走联合索引。
4. a 和 b 都会走联合索引，查询是覆盖索引，不需要回表。
5. a 可以走联合索引。
6. 只有 b，无法使用联合索引，由于表存在联合索引，所以 count(*) 选择的扫描方式是扫描联合索引来统计个数，扫描的方式是 type=index。
7. 由于表存在联合索引，所以 count(*) 选择的扫描方式是扫描联合索引来统计个数，扫描的方式是 type=index。

关于 count(*) 为什么选择扫描联合索引（二级索引），而不扫描聚簇索引的原因：这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。

14.索引设计原则

设计原则：

1. 针对于数据量较大，且查询比较频繁的表建立索引

2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高

4. 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率

7. 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询

15.什么列作为索引更加有效?

并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加 B+树索引，一般的经验是，在访问表中数据量很大，且重复数据较少时， B+树索引才有意义。对于性别字段、地区字段、类型字段，它们可取值的范围很小，称为低选择性不建议添加索引，当然也要根据自身项目和场景的需求。如：

SELECT * FROM student WHERE sex='M';

按性别进行查询时，可取值的范围一般只有'M'、'F'。因此上述 SQL 语句得到的结果可能是该表 50%的数据（假设男女比例 1∶1），这时添加 B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用 B+树索引是最适合的。

怎样查看索引是否是高选择性的呢？可以通过 SHOW INDEX 结果中的列 Cardinality 来观察。Cardinality 值非常关键，表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality 是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值。在实际应用中，Cardinality/n_rows_in_table 应尽可能地接近 1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。简单来说就是区分度高的字段且经常当做 where 条件查询的字段适合做索引。

举例：

ALTER TABLE mysql_user
    ADD UNIQUE (id);

EXPLAIN
SELECT *
FROM mysql_user
WHERE id = '500';

表 mysql_user 大约有 500 万行数据。id 字段上有一个唯一的索引。这时如果查找 id 为 500 的用户，将会得到如下的执行计划：

SHOW INDEX FROM mysql_user;

可以看到使用了 id 这个索引，这也符合之前提到的高选择性，即 SQL 语句选取表中较少行的原则。

选择合适的字段创建索引:

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0，1，true，false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

16.索引关键属性

SHOW INDEX FROM chatbot;

• Table:索引所在的表名。
• Non_unique:非唯一的索引，可以看到 primary key 对应的 id 是 0，因为必须是唯一的。
• Key_name:索引的名字，用户可以通过这个名字来执行 DROP INDEX。
• Seg_in_index:索引中该列的位置，如果看联合索引 idxac 就比较直观了。
• Column_name:索引列的名称。
• Collation:列以什么方式存储在索引中。可以是 A 或 NULL。B+树索引总是 A，即排序的。如果使用了 Heap 存储引擎，并且建立了 Hash 索引，这里就会显示 NULL 了。因为 Hash 根据 Hash 桶存放索引数据，而不是对数据进行排序。
• Cardinality:非常关键的值，表示索引中唯一值的数目的估计值。Cardinality 的值应尽可能接近表的最大行数，如果非常小，那么用户需要考虑是否可以删除此索引。
• Sub_part:是否是列的部分被索引。如果看 idx_b 这个索引，这里显示 100，表示只对 b 列的前 100 字符进行索引。如果索引整个列则该字段为 NULL。
• Packed:关键字如何被压缩。如果没有被压缩，则为 NULL。
• Null:是否索引的列含有 NULL 值。可以看到 idxb 这里为 Yes，因为定义的列 b 允许 NULL 值。
• Index_type:索引的类型。InnoDB 存储引擎只支持 B+树索引，所以这里显示的都是 BTREE。
• Comment:注释。

Cardinality 值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。因此这个值是不太准确的，只是一个大概的值。上面显示的结果主键的 Cardinality 为 2，但是很显然我们的表中有 4 条记录，这个值应该是 4。Cardinality 为 NULL，在某些情况下可能会发生索引建立了却没有用到的情况。或者对两条基本一样的语句执行 EXPLAIN，但是最终出来的结果不一样:一个使用索引，另外一个使用全表扫描。这时最好的解决办法就是做一次 ANALYZE TABLE 的操作，因此在一个非高峰时间，对应用程序下的几张核心表做 ANALYZE TABLE 操作，这能使优化器和索引更好的工作。

#刷新Cardinality的值
ANALYZE TABLE kwan_t1;

在实际应用中，Cardinality/n_rows_in_table 应尽可能地接近 1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加 B+树索引是非常有必要的。

InnoDB 存储引擎内部对更新 Cardinality 信息的策略为:

• 表中 1/16 的数据已发生过变化。
• stat_modifed_counter>2000 000 000

第一种策略为自从一次统计 Cardinality 信息后，表中 1/16 的数据已经发生过变化，这时需要更新 Cardinality 信息。第二种情况考虑的是，如果对表中某一行数据频繁地进行更新操作，这时表中的数据实际并没有增加，实际发生变化的还是这一行数据，则第一种更新策略就无法适用这这种情况。故在 InnoDB 存储引擎内部有一个计数器 stat_modifed_counter，用来表示发生变化的次数，当 stat_modifed_counter 大于 2000 000 000 时，则同样需要更新 Cardinality 信息。

17.不走索引的情况?

尽量用覆盖索引，注意查询的数据量，也就是查询条件的选取

• 不要在索引列上进行运算操作， 索引将失效。

• 字符串类型字段使用时，不加引号，索引将失效。数字类型加不加引号都走索引。

• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

• 用 or 分割开的条件

  • 如果 or 前后的查询字段有一个没有索引，则不走索引

  • 如果 or 前后都有索引，在 8.0 中走索引，在 5.7 中不走索引

• not in ，not exist 不走索引

• <> 不等于的情况

  • 在 8.0 中走索引

  • 在 5.7 中跟数据量有关，如果不等于的结果集大于 20%不走索引，小于 20%走索引

• 在包含有 null 值的 table 列上建立索引，当时使用 select count(*) from table 时不会使用索引

• 如果 MySQL 评估使用索引比全表更慢，则不使用索引

18.优化器不使用单列索引？

在某些情况下，当执行 EXPLAIN 命令进行 select 语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这利中情况多发生于范围查找、JOIN 链接操作等情况下。

SELECT *
FROM tmp_kwan_muti_fileld
WHERE a > 10000
  AND a < 102000;

比如，表 tmp_kwan_muti_fileld 有(a，b)的联合主键，此外还有对于列 a 的单个索引。上述这句 SQL 显然是可以通过扫描 a 上的索引进行数据的查找。然而通过 EXPLAIN 命令。用户会发现优化器并没有按照 OrderlD 上的索引来查找数据，使用了 a 的联合主键索引，也就是表扫描.而非辅助索引扫描.

原因在于用户要选取的数据是整行信息，而 a 索引不能覆盖到我们要查询的信息，因此在对 a 索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然 a 索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时(一般是 20%左右)，优化器会选择通过聚集索引来查找数据。因为顺序读要远远快于离散读。 因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字 FORCE INDEX 来强制使用某个索引.

19.desc 的索引实现?

在 mysql5.7 版本中添加降序索引后，并不会实际的添加降序索引，在 mysql8.0 中添加降序索引会添加降序索引.

降序的索引的执行计划中多了个 Backward index scan 反向索引扫描。

升序查询的时候，由于指定列是降序排列的，所以执行计划中多了个 using filesort 的结果，用到了文件排序，而在一些大型表的排序过程中，使用文件排序是非常消耗性能的。使用降序索引可以避免文件排序，这一点，就是降序索引能够带来的直观收益。

• 降序索引只能在 innodb 存储引擎中使用，其他存储引擎不支持。
• 使用聚合函数如果没有使用 group by 子句，不能使用降序索引进行优化。
• 降序索引只支持 BTREE 索引，不支持 HASH 索引。
• 升序索引支持的数据类型，降顺索引都支持。

20.表的最大索引个数

在 MySQL 中，每个表的索引数量是有限制的，但具体的限制取决于 MySQL 的版本和存储引擎。在某些情况下，一个表可以支持多于 64 个索引。

在 MySQL 5.7 及之前的版本中，每个表最多支持 64 个索引。这是因为 MySQL 5.7 及之前的版本使用了类似于file_per_table的存储模式，导致每个表的索引数量有限制。

然而，在 MySQL 8.0 及之后的版本中，情况有所改变。MySQL 8.0 引入了InnoDB存储引擎的innodb_large_prefix特性，默认情况下为开启状态。该特性允许索引的最大长度扩展到 3072 字节（之前为 767 字节），这使得每个表可以支持更多的索引数量。

总体而言，具体支持的最大索引数量取决于 MySQL 的版本和存储引擎，并且可能受到配置选项的影响。对于大多数应用场景而言，不太可能需要超过 64 个索引，因为合理使用较少数量的索引通常就能够满足查询优化的需求。

21.什么是 FIC?

说说 FIC(Fast index creation)原理，与普通 index 有什么不同?

MySQL5.5 版本之前（不包括 5.5）存在的一个普遍被人诟病的问题是：MySQL 数据库对于索引的添加或者删除的这类 DDL 操作，MySQL 数据库的操作过程为：

• 首先创建一张新的临时表，表结构为通过命令 ALTERTABLE 新定义的结构。
• 然后把原表中数据导入到临时表。
• 接着删除原表。
• 最后把临时表重命名为原来的表名。

可以发现，若用户对于一张大表进行索引的添加和删除操作，那么这会需要很长的时间。更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用。MySQL 数据库的索引维护始终让使用者感觉非常痛苦。

InnoDB 存储引擎从 InnoDB1.0.x 版本开始支持一种称为 Fast Index Creation（快速索引创建）的索引创建方式——简称 FIC。

对于辅助索引的创建，InnoDB 存储引擎会对创建索引的表加上一个 S 锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB 存储引擎只需更新内部视图，并将辅助索引的空间标记为可用（不影响辅助索引的使用，因为可读，后边的同时删除四个字非常传神），同时删除 MySQL 数据库内部视图上对该表的索引定义即可。

由于 FIC 在索引的创建的过程中对表加上了 S 锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用。此外，FIC 方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表.

大表如何添加索引:

如果一张表数据量级是千万级别以上的，那么，如何给这张表添加索引？

我们需要知道一点，给表添加索引的时候，是会对表加锁的。如果不谨慎操作，有可能出现生产事故的。可以参考以下方法：

1. 先创建一张跟原表A数据结构相同的新表B。
2. 在新表B添加需要加上的新索引。
3. 把原表A数据导到新表B
4. rename新表B为原表的表名A，原表A换别的表名；

22.有没有比 FIC 更好的方式?

虽然 FIC 可以让 InnoDB 存储引擎避免创建临时表，从而提高索引创建的效率。但索引创建时会阻塞表上的 DML 操作（除读操作）。OSC（一个 FaceBook 的 PHP 脚本）虽然解决了上述的部分问题，但是还是有很大的局限性。MySQL5.6 版本开始支持 Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如 INSERT、UPDATE、DELETE 这类 DML 操作，这极大地提高了 MySQL 数据库在生产环境中的可用性。

不仅是辅助索引，以下这几类 DDL 操作都可以通过“在线”的方式进行操作：

• 辅助索引的创建与删除
• 改变自增长值
• 添加或删除外键约束
• 列的重命名

使用语法：

alter table tba_name
|ADD{INDEX|KEY}[index_name]
[index_type](index_col_name，...)[index_option]...
ALGORITHM[=]{DEFAULT|INPLACE|COPY}
LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}

ALGORITHM 指定了创建或删除索引的算法，COPY 表示按照 MySQL5.1 版本之前的工作模式，即创建临时表的方式。INPLACE 表示索引创建或删除操作不需要创建临时表。DEFAULT 表示根据参数 old_alter_table 来判断是通过 INPLACE 还是 COPY 的算法，该参数的默认值为 OFF，表示采用 INPLACE 的方式。

LOCK 部分为索引创建或删除时对表添加锁的情况：

NONE:执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会收到阻塞。因此这种模式可以获得最大的并发度。 SHARE:这和之前的 FIC 类似，执行索引创建或删除操作时，对目标表加上一个 S 锁。对于并发地读事务，依然可以执行，但是遇到写事务，就会发生等待操作。如果存储引擎不支持 SHARE 模式，会返回一个错误信息。 EXCLUSIVE:在 EXCLUSIVE 模式下，执行索引创建或删除操作时，对目标表加上一个 X 锁。读写事务都不能进行，因此会阻塞所有的线程，这和 COPY 方式运行得到的状态类似，但是不需要像 COPY 方式那样创建一张临时表。 DEFAULT:DEFAULT 模式首先会判断当前操作是否可以使用 NONE 模式，若不能，则判断是否可以使用 SHARE 模式，最后判断是否可以使用 EXCLUSIVE 模式。也就是说 DEFAULT 会通过判断事务的最大并发性来判断执行 DDL 的模式。

InnoDB 存储引擎实现 Online DDL 的原理是在执行创建或者删除操作的同时，将 INSERT、UPDATE、DELETE 这类 DML 操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。这个缓存的大小由参数 innodb_online_alter_log_max_size 控制，默认的大小为 128MB。

需要特别注意的是，由于 Online DDL 在创建索引完成后再通过重做日志达到数据库的最终一致性，这意味着在索引创建过程中，SQL 优化器不会选择正在创建中的索引。

23.什么是离散读?

在某些情况下，当执行 EXPLAIN 命令进行 SQL 语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN 链接操作等情况下。

假设表：t_index。其中 id 为主键；c1 与 c2 组成了联合索引（c1，c2）；此外，c1 还是一个单独索引。进行如下查询操作：

SELECT * FROM t_index WHERE c1＞1 and c1＜100000;

可以看到表 t_index 有（c1，c2）的联合主键，此外还有对于列 c1 的单个索引。上述这句 SQL 显然是可以通过扫描 OrderID 上的索引进行数据的查找。然而通过 EXPLAIN 命令，用户会发现优化器并没有按照 OrderID 上的索引来查找数据。

在最后的索引使用中，优化器选择了 PRIMARY id 聚集索引，也就是表扫描（tablescan），而非 c1 辅助索引扫描（index scan）。

这是为什么呢？因为如果强制使用 c1 索引，就会造成离散读。具体原因在于用户要选取的数据是整行信息，而 c1 作为辅助索引不能覆盖到我们要查询的信息，因此在对 c1 索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然 c1 索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是 20%左右），优化器会选择通过聚集索引来查找数据。

24.索引下推

select * from employee where name like '小%' and age=28 and sex='0';

其中，name和age为联合索引（idx_name_age）。

如果是Mysql5.6 之前，在idx_name_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去对比年龄和性别等其他字段。如图：

idx_name_age（name,age)不是联合索引嘛？为什么选出包含“小”字后，不再顺便看下年龄age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

因此，MySQL5.6 版本之后，选出包含“小”字后，顺表过滤age=28

25.唯⼀索引普通索引选择难题

核心是需要回答到change buffer 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer,实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown) 的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。

要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer用的是 buffer pool 里的内存,因此不能无限增大,change buffer的大小,可以通过参数 innodb_change_buffer_max_size 来动态设置，这个参数设置为 50 的时候,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一，change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer的使用场景 因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多(也就是这个页面上要更新的次数越多），收益就越大。 因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价，所以，对于这种业务模式来说，change buffer 反而起到了副作用。