索引

1、为什么使用索引？

最主要原因：加速对表中数据行的检索。

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 帮助服务器避免排序和临时表
• 将随机IO变为顺序IO
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

  2、索引如何提高查询速度的

  将无序的数据变成相对有序的数据（就像查有目的一样）

  3、谈谈你对索引的理解

  索引的出现是为了提高数据的查询效率。对于数据库的表而言，索引其实就是它的“目录”。
  索引也会带来很多负面影响：创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。
  建立索引的原则：

1. 在最频繁使用的、用以缩小查询范围的字段上建立索引；
2. 在频繁使用的、需要排序的字段上建立索引。

不适合建立索引：

1. 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引；
2. 对于一些特殊的数据类型，不宜建立索引，比如：文本字段（text）等。

换一种说法：

• 在 查询中很少使用 或者参考的列不要创建索引。由于这些列很少使用到，增加索引反而会降低系统的维护速度和增大空间需求。
• 只有很少数据值的列 也不应该增加索引。由于这些列的取值很少，区分度太低，例如人事表中的性别，在查询时，需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 定义为 text、image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
• 当 修改性能远远大于检索性能 时，不应该创建索引。这时因为，二者是相互矛盾的，当增加索引时，会提高检索性能，但是会降低修改性能。

• 定义有 外键 的数据列一定要创建索引。

  4、索引底层使用的什么数据结构？MySQL索引主要使用的两种数据结构是什么？

  索引的数据结构和具体存储引擎的实现有关。
  在MySQL中使用较多的索引有 Hash 索引、B+树索引等。
  哈希索引，对于哈希索引来说，底层的数据结构肯定是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。
  BTree索引，Mysql的BTree索引使用的是B树中的B+Tree，BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。
  而我们经常使用的 InnoDB 存储引擎的默认索引实现为 B+ 树索引。

  5、为什么说B+tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？

  B+tree的磁盘读写代价更低，B+tree的查询效率更加稳定。
  数据库索引采用B+树而不是B树的主要原因：B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
  考虑的是 IO 对性能的影响，B 树的每个节点都存储数据，而 B+ 树只有叶子节点才存储数据，所以查找相同数据量的情况下，B 树的高度更高，IO 更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。
  B+树的特点

• 所有关键字都出现在叶子结点的链表中(稠密索引)，且链表中的关键字恰好是有序的;

• 不可能在非叶子结点命中;
• 非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相当于是存储(关键字)数据的数据层;

  6、谈谈你对 B+ 树的理解？

1. B+ 树是基于 B 树和叶子节点顺序访问指针进行实现，它具有 B 树的平衡性，并且通过顺序访问指针来提高区间查询的性能。
2. 在 B+ 树中，一个节点中的 key 从左到右非递减排列，如果某个指针的左右相邻 key 分别是 key i 和 key i+1，且不为 null，则该指针指向节点的所有 key 大于等于 key i 且小于等于 key i+1。
3. 进行查找操作时，首先在根节点进行二分查找，找到一个 key 所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出 key 所对应的 data。
4. 插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

   7、为什么MySQL索引要使用B+树，而不是B树或者红黑树或哈希表？

   磁盘读取花费时间。
   B类树可以进行优化。
   可以根据B类树的特点，构造一个多阶的B类树，然后在尽量多的在结点上存储相关的信息，保证层数（树的高度）尽量的少，以便后面我们可以更快的找到信息，磁盘的I/O操作也少一些，而且B类树是平衡树，每个结点到叶子结点的高度都是相同，这也保证了每个查询是稳定的。
   B+和B树
   B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。
   关于哈希树

• 利用Hash需要把数据全部加载到内存中，如果数据量大，是一件很消耗内存的事，而采用B+树，是

基于按照节点分段加载，由此减少内存消耗。

• 和业务场景有关，对于唯一查找（查找一个值），Hash确实更快，但数据库中经常查询多条数据，这

时候由于B+数据的有序性，与叶子节点又有链表相连，查询效率会比Hash快的多。

• b+树的非叶子节点不保存数据，只保存子树的临界值（最大或者最小），所以同样大小的节点， b+树相对于b树能够有更多的分支，使得这棵树更加矮胖，查询时做的IO操作次数也更少。

  8、既然索引有那么多优点，为什么不对表总的每一列创建一个索引呢？

  当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
  索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立簇索引，那么需要的空间就会更大。
  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

  9、文件索引和数据库索引为什么使用B+树?

  文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上。而所谓索引，则为了数据的快速定位与查找，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，而红黑树这种结构，高度明显要深的多，并且由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性。
  最重要的是，B+树还有一个最大的好处：方便扫库。
  B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因。
  B+树查找效率更加稳定，B树有可能在中间节点找到数据，稳定性不够。
  B+tree的磁盘读写代价更低：B+tree的内部结点并没有指向关键字具体信息的指针(红色部分)，因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一块盘中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了；
  B+tree的查询效率更加稳定：由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

  10、增加B+树的路数可以降低树的高度，那么无限增加树的路数是不是可以有最优的查找效率？

  不可以。因为这样会形成一个有序数组，文件系统和数据库的索引都是存在硬盘上的，并且如果数据量大的话，不一定能一次性加载到内存中。有序数组没法一次性加载进内存，这时候B+树的多路存储威力就出来了，可以每次加载B+树的一个结点，然后一步步往下找。

  11、Mysql有四种索引类型，分别是什么？

  FULLTEXT ：即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
  HASH ：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
  BTREE ：BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
  RTREE ：RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。 相对于BTREE，RTREE的优势在于范围查找。

  12、索引的分类有哪些？有什么特点？

  从数据结构角度

1. 树索引 (O(log(n)))
2. Hash 索引

从物理存储角度

1. 聚集索引（clustered index）
2. 非聚集索引（non-clustered index）

从逻辑角度

1. 普通索引：：仅加速查询
2. 唯一索引：加速查询 + 列值唯一（可以有null）
3. 主键（聚集）索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
4. 联合（复合）索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
5. 全文索引：对文本的内容进行分词，进行搜索
6. 覆盖索引：：select的数据列只用从索引中就能够取得，不必读取数据行，
7. 索引合并：使用多个单列索引组合搜索

索引优点

• ⼤⼤加快数据的检索速度;
• 创建唯⼀性索引，保证数据库表中每⼀⾏数据的唯⼀性；
• 加速表和表之间的连接;
• 在使⽤分组和排序⼦句进⾏数据检索时，可以显著减少查询中分组和排序的时间

缺点

• 索引需要占⽤数据表以外的物理存储空间；
• 创建索引和维护索引要花费⼀定的时间；
• 当对表进⾏更新操作时，索引需要被重建，这样降低了数据的维护速度。

聚集索引
也叫聚簇索引。数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
好处是查询速度快。索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询。
聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。
非聚集索引
（unclustered）索引。该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
其实除了聚集索引以外的索引都是非聚集索引，细分下去为普通索引，唯一索引，全文索引。
非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。
非聚集索引其实叶子节点除了会存储索引覆盖列的数据，也会存放聚集索引所覆盖的列数据。
两者区别
聚簇索引（Innodb）的叶子节点就是数据节点，而非聚簇索引(MyisAM)的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值 ，再使用主键的值通过聚集索引查找到需要的数据。
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

• 聚集索引表示表中存储的数据按照索引的顺序存储，检索效率⽐⾮聚集索引⾼，但对数据更新影响较⼤。⾮聚集索引表示数据存储在⼀个地⽅，索引存储在另⼀个地⽅，索引带有指针指向数据的存储位置，⾮聚集索引检索效率⽐聚集索引低，但对数据更新影响较⼩。
• 聚集索引⼀个表只能有⼀个，⽽⾮聚集索引⼀个表可以存在多个。聚集索引存储记录是物理上连续存在，⽽⾮聚集索引是逻辑上的连续，物理存储并不连续

解决非聚集索引的二次查询问题—复合索引
建立两列以上的索引，即可查询复合索引里的列的数据而不需要进行回表二次查询。
覆盖索引
如果一个索引包含了满足查询语句中字段与条件的数据就叫做覆盖索引。具有以下优点：

1. 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
2. 一些存储引擎（例如：MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
3. 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

在InnoDB存储引 擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次,这样就 会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作.
覆盖索引大规模应用可以用联合索引。
联合索引
联合索引也即使用多个字段建立一个索引，联合索引的一个显著的好处就是可以形成覆盖索引，提高查询效率。
唯一索引
表明此索引的每⼀个索引值只对应唯⼀的数据记录，对于单列惟⼀性索引，这保证单列不包含᯿复的值。对于多列惟⼀性索引，保证多个值的组合不重复。
主键索引
数据库表经常有⼀列或列组合，其值唯⼀标识表中的每⼀⾏。该列称为表的主键。在数据库关系图中为表定义主键将⾃动创建主键索引，主键索引是唯⼀索引的特定类型。该索引要求主键中的每个值都唯⼀。当在查询中使⽤主键索引时。
唯⼀性索引和主键索引的区别
对于主健索引，oracle/sql server/mysql 等都会⾃动建⽴唯⼀索引;
主键不⼀定只包含⼀个字段，所以如果你在主键的其中⼀个字段建唯⼀索引还是必要的；
主健可作外健，唯⼀索引不可；
主健不可为空，唯⼀索引可以；
主健也可是多个字段的组合；
主键与唯⼀索引不同的是主键索引有 not null 属性；
主键索引每个表只能有⼀个。

13、MyISAM和InnoDB实现B树索引方式的区别是什么？

MyISAM是非聚簇索引，B+Tree叶节点的data域存放的是数据记录的地址，在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。
InnoDB是聚簇索引，其数据文件本身就是索引文件，相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。
在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。因此，在设计表的时候，不建议使用过长的字段为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

13、创建索引是要注意什么？使用索引注意什么？

创建时注意：
唯一、不为空、经常被查询的字段 的字段适合建索引。

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在 MySQL 中，含有空值的列很难进行查 询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者 一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数 查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率 越高。

使用时注意：
(1) 不要在列上使用函数，这将导致索引失效而进行全表扫描。
或者为使用索引，防止执行全表扫描。
(2）尽量避免使用 != 或 not in或 <> 等否定操作符.
因为这几个操作符都会导致索引失效而进行全表扫描。尽量避免使用 or 来连接条件.
(3)多个单列索引并不是最佳选择
MySQL 只能使用一个索引，会从多个索引中选择一个限制最为严格的索引，因此，为多个列创建单列索引，并不能提高 MySQL 的查询性能。为 了 提 高 性 能 ， 可 以 使 用 复 合 索 引.
(4）复合索引的最左前缀原则
复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。
(5)覆盖索引的好处
如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够
极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。
(6)范围查询对多列查询的影响
查询中的某个列有范围查询，则其右边所有列都无法使用索引优化查找。
(7）索引不会包含有NULL值的列
只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。
(8)隐式转换的影响
当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换带来的影响就是可能导致索引失效而进行全表扫描。
(9)like 语句的索引失效问题
like 的方式进行查询，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情。所以，根据业务需求，考虑使用 ElasticSearch 或 Solr 是个不错的方案.
其他：

• 在经常需要搜索的列上，可以加快搜索的速度；
• 在经常使用在where子句中的列上面创建索引，加快条件的判断速度。
• 将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描
• 在经常需要排序的列上创建索引，因为索引已排序，这样查询可以利用索引的排序，加快排序查询时间
• 避免where子句中对字段施加函数，这会造成无法命中索引
• 在中到大型表索引都是非常有效，但是特大型表的维护开销会很大，不适合建索引，建立用逻辑索引
• 在经常用到连续的列上，这些列主要是由一些外键，可以加快连接的速度
• 与业务无关时多使用逻辑主键，也就是自增主键，在使用InnoDB时，使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗

• 在使用limit offset查询缓存时，可以借助索引来提高性能。

  14、谈谈你对哈希索引的理解？

  哈希索引能以 O(1) 时间进行查找，但是失去了有序性。无法用于排序与分组、只支持精确查找，无法用于部分查找和范围查找。
  InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+ 树索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如：快速的哈希查找。

  15、谈谈你对最左前缀原则的理解？

  MySQL 使用联合索引时，需要满足最左前缀原则。
  建立一个索引，对于索引中的字段，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的顺序可以任意调整。
  =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

• B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。

• 在建立联合索引的时候，如何安排索引内的字段顺序？评估标准是，索引的复用能力。
  • 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往是优先考虑采用的。
  • 考虑的原则就是空间

• 最左前缀可以用于在索引中定位记录。

  16、怎么知道创建的索引有没有被使用到？或者说怎么才可以知道这条语句运行很慢的原因?

  使用 Explain 命令来查看语句的执行计划，MySQL 在执行某个语句之前，会将该语句过一遍查询优化器，之后会拿到对语句的分析，也就是执行计划，其中包含了许多信息。可以通过其中和索引有关的信息来分析是否命中了索引，例如：possilbe_key、key、key_len 等字段，分别说明了此语句可能会使用的索引、实际使用的索引以及使用的索引长度。

  17、什么情况下索引会失效？即查询不走索引？

  1、索引列参与表达式计算
  2、 函数运算
  3、%词语%–模糊查询
  4、 字符串与数字比较不走索引
  5、 查询条件中有 or ，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引
  6、正则表达式不使用索引。
  7、 MySQL 内部优化器会对 SQL 语句进行优化，如果优化器估计使用全表扫描要比使用索引快，则不使用索引。

  18、Innodb为什么要用自增id作为主键？

• 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页 写满，就会自动开辟一个新的页。

• 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE（optimize table）来重建表并优化填充页面。

04 讲深入浅出索引(上).html)
05 讲深入浅出索引(下).html)

• 哈希表
  • 键-值（key-value），哈希冲突-链表
  • 适用于只有等值查询的场景，比如Memcached及其他一些NoSQL引擎。
• 有序数组
  • 在等值查询和范围查询场景中的性能就都非常优秀
  • 有序数组索引只适用于静态存储引擎
• 二叉搜索树
  • 保持这棵树是平衡二叉树
  • N叉树，为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。“N”取决于数据块的大小。
  • 即B+树，B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
  • 以InnoDB的一个整数字段索引为例，这个N差不多是1200。

在MySQL中，索引是在存储引擎层实现的，不同存储引擎的索引的工作方式并不一样。

InnoDB 的索引模型

• 表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
• 使用了B+树索引模型，所以数据都是存储在B+树中的。
• 根据叶子节点的内容，索引类型分为主键索引和非主键索引。
  • 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。
  • 非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

基于主键索引和普通索引的查询有什么区别？

• 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
• 如果语句是select from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。*回到主键索引树搜索的过程，称为回表。

基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

• 页分裂。影响性能和页的利用率。
• 页合并

自增主键
自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。
插入新记录的时候可以不指定ID的值，系统会获取当前ID最大值加1作为下一条记录的ID值。
自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
考虑性能和存储空间。

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
从性能和存储空间方面考量，自增主键往往是更合理的选择。
没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：

1. 只有一个索引；
2. 该索引必须是唯一索引。

你一定看出来了，这就是典型的KV场景。（key-value）
由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

重建索引

通过两个alter 语句重建索引k，以及通过两个alter语句重建主键索引是否合理。

alter table T drop index k;
alter table T add index(k);
或者
alter table T drop primary key;
alter table T add primary key(id);

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。
重建索引k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

如何避免回表，进行索引优化。

覆盖索引

• ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。
• 在这个查询里面，索引k已经“覆盖了”我们的查询需求，称为覆盖索引。
• 如果一个索=引包含（或者说覆盖）所有需要查询的字段的值，我们就称 之为“覆盖索引”。在InnoDB存储引 擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回 表”，也就是要通过主键再查找一次,这样就 会比较慢。覆盖索引就是把要查询出的列和索引是对应的， 不做回表操作！
• 可以减少树的搜索次数，显著提升查询性能。

• 联合索引如（身份证号、姓名），高频请求，建立冗余索引来支持覆盖索引时就需要权衡考虑了。

  索引下推

  判断条件时，

• 在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。

• MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

  如何选择唯一索引和普通索引

  09 讲普通索引和唯一索引，应该怎么选择
  由于唯一索引用不上change buffer的优化机制，因此如果业务可以接受，从性能角度出发建议优先考虑非唯一索引。

• 查询时，两种索引性能差别很小。

InnoDB的数据是按数据页为单位来读写的。当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。

• 更新时，change buffer

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

change buffer

change buffer在内存中有拷贝，也会被写入到磁盘上。
将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。
好处：

• 减少读磁盘，明显的提升语句的执行速度。
• 避免占用内存，提高内存利用率。（数据读入内存是需要占用buffer pool的）

什么条件下可以使用change buffer呢？
唯一索引没必要，只有普通索引可以使用。
change buffer用的是buffer pool里的内存，因此不能无限增大。change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

如果要在这张表中插入一个新记录(4,400)的话，InnoDB的处理流程是怎样的。
第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB的处理流程如下：

• 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。
第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB的处理流程如下：

• 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
• 对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

普通索引的所有场景，使用change buffer都可以起到加速作用吗？

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

change buffer 和 redo log

redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

“业务可能无法确保”

• 首先，业务正确性优先。咱们这篇文章的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。这种情况下，本篇文章的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，可以给你多提供一个排查思路。
• 然后，在一些“归档库”的场景，你是可以考虑使用唯一索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

问题：change buffer一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致change buffer丢失呢？
1.change buffer有一部分在内存有一部分在ibdata.
做purge操作,应该就会把change buffer里相应的数据持久化到ibdata
2.redo log里记录了数据页的修改以及change buffer新写入的信息
如果掉电,持久化的change buffer数据已经purge,不用恢复。主要分析没有持久化的数据
情况又分为以下几种:
(1)change buffer写入,redo log虽然做了fsync但未commit,binlog未fsync到磁盘,这部分数据丢失
(2)change buffer写入,redo log写入但没有commit,binlog以及fsync到磁盘,先从binlog恢复redo log,再从redo log恢复change buffer
(3)change buffer写入,redo log和binlog都已经fsync.那么直接从redo log里恢复。