预备知识 B+树: https://www.jianshu.com/p/5e0818b7c1ea

索引基础

索引的作用是：

• 加速查找
• 约束

索引优化是对查询性能优化最有效的手段了。索引能轻易将查询性能提高几个数量级，最优的索引有时比一个好的索引性能要好两个数量级。

如果没有使用索引，相当于从前往后整个表进行查找。而使用索引，相当于拥有了一个目录，可以大大加速查找。

创建索引的过程可以理解为数据库额外创建了一个文件（某种格式存储），作为目录。在查找时，先在这个目录中查找。即存储引擎先再索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

举例：

# 一开始name列没有索引
select * from userinfo3 where name='alxe';    // 300w数据里 接近7s找到
# 为name列创建一个普通的索引 相当于创建了一个目录文件
create index ix_name on userinfo3(name);
# 再次执行同样的查找
select * from userinfo3 where name='alxe';   // 仅0.07秒就可以完成
# 删除索引 使用 drop idex 索引名 on 表名

索引的种类

• 普通索引
  • 加速查找
• 主键索引
  • 加速查找 + 不能为空 + 不能重复
• 唯一索引
  • 加速查找 + 不能重复
• 组合索引
  • 多列组成一个索引
    • 联合主键索引
    • 联合唯一索引
    • 联合普通索引

以及两种特殊索引名词

• （覆盖索引）能直接在索引文件获取到数据
  • select id from tb where id = 1;
• （索引合并）多个单列索引合并使用
  • select * from tb where id=1 and name='xixi';

组合索引效率 > 索引合并

索引类型

索引是在存储引擎层而不是服务层实现的。不同的存储引擎的索引的工作方式并不相同，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。这里只提两个常见的索引类型。

B-Tree索引

• B-Tree
  • 常用
  • 将索引放置于树中
  • 有利于范围查找

存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

B-Tree通常意味着所有的值都是按顺序存储的，并且每个叶子页到根的距离相同。

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子结点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子结点，这些指针实际上定义了子结点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。

image

B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。

hash索引

• hash索引
  • 创建了一个索引哈希表，记录了每个索引的哈希值已经数据存储地址。
  • 哈希索引表里和原表顺序不同 例如查找id<3的值，则效率慢。但是如果查找id=3，则效率很快。即范围查找慢，单值查找快。
  • 少用

哈希索引基于哈希表实现。只有精确匹配索引所有列的査询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code),哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

MySQL中，只有Memory引擎显式支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-Tree索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，这在数据库世界里面是比较与众不同的。如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。

因为索引只存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

但是哈希索引包含以下缺点：

1. 哈希索引只包含哈希值和行指针，而不存储数据值，所以无法通过索引来避免读取行。（当select只取索引值时）
2. 哈希表中的哈希值是有序的，但导致索引值是无序的，所以无法用于排序
3. 哈希索引不支持部分索引列匹配索引。因为哈希值是根据所有使用的索引列进行计算。所以当索引为(A,B)时，如果只使用A，索引无效。
4. 哈希索引只支持等值查询。即 = 、 IN()、 <=>。不支持范围查询
5. 当出现哈希冲突时，需要遍历对应链表的所有行指针，逐行比较。
6. 如果哈希冲突很多时，一些索引维护操作的代价也会很高。例如当做对应的删除操作时，需要遍历对应链表的所有行指针，找到并删除对应行的引用。

因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。

Innodb引擎有一个特殊的功能叫“自适应哈希索引”。 当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中，基于B-Tree之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。这是一个完全自动的、内部行为，用户无法控制或配置，但是可以关闭。

自定义哈希索引 **** 实用

如果存储引擎不支持哈希索引，可以模拟InnoDB创建哈希索引，这可以享受一些哈希索引的便利，例如只需要很小的索引就可以为超长的键创建索引。

当需要存储大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会很大，因为URL本身都很长。

常规查找：

SELECT id FROM url WHERE url="http://www.mysql.com";

若删除原来URL列上的索引，新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

SELECT id FROM url WHERE url="http://www.mysql.com"
    AND url_crc=CRC32("http://www.mysql.com");

这样做的性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。只需要根据哈希值做快速的整数比较就能找到索引条目。相比于对完整URL字符串做索引，效率高很多。

这样实现的缺陷是需要维护哈希值。可以手动通过触发器实现。

1. 创建表如下：

CREATE TABLE url_hash_test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL ,
  `url_crc` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 创建触发器

DELIMITER //
create trigger url_insert before Insert on url_hash_test for each row
begin
set new.url_crc = crc32(new.url);
end;
//
create trigger url_update before update on url_hash_test for each row
begin
set new.url_crc = crc32(new.url);
end;
//
DELIMITER ;

尽量不要使用SHA1()\MD5()作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢。这两个函数都是强加密函数，设计目标是最大限度消除重读，但这里并不需要这样搞的要求。

一旦出现哈希冲突

SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com");

是无法正确执行的（但是可以用于统计记录数）。所以我们采用哈希值加原数据进行匹配，保证准确性。

SELECT id FROM url WHERE url="http://www.mysql.com"
    AND url_crc=CRC32("http://www.mysql.com");

索引的优点

• 大大减少了服务器需要扫描的数据量
• 帮助服务器避免排序和临时表
• 索引可以将随机I/O变为顺序I/O

建立索引的缺点

• 额外的文件保存特殊的数据结构
• 插入和更新删除效率降低（需要更新索引文件）
• 需要命中索引才能发挥作用

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

• 对于非常小的表，大部分情况下简单的全表扫描更高效。
• 对于中到大型的表，索引就非常有效
• 对于特大型表，建立和使用索引的代价将随之增长。

对于特大型表的情况下，需要一种技术可以直接区分出查询需要的一组数据，而不是一条一条记录地匹配。例如 分区技术。如果表的数据特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别元数据技术来替代索引。

索引策略

正确地创建和使用索引是实现高性能查询的基础。

有效索引

可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效。

前提： key (last_name, first_name, dob)

• 全值匹配

全值匹配指的是和索引中的所有列进行匹配，
例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。

• 匹配最左前缀

前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列。
匹配列前缀也可以只匹配某一列的值的开头部分。
例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列。

• 匹配范围值

例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。
这里也只使用了索引的第一列。

• 精确匹配到某一列并范围匹配另外一列

前面提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头（比如Kim、Karl等）的人。
即第一列last_name全匹配，第二列first_name范围匹配。

• 只访问索引的查询

B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面例子中的索引在每用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名（first_name），则MySQL只能使用索引的第一列。
• 如果查询中有某个列的范围(like between > < 都算范围查询)查询，则其右边所有列都无法使用索引优化查找。例如有查询WHERE lastname='Smith’AND firstname like '%J%'AND dob＝’1976-12-23＇，这个查询只能使用索引的前两列，因为这里的like是一个范围条件（但是服务器可以把其余列用于其他目的）。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

所以前面提到的索引列的顺序是多么的重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

也有些限制并不是B-Tree本身导致的，而是MySQL优化器和存储引擎使用索引的方式导致的，这部分限制在未来的版本中可能就不再是限制了。

高效使用索引的策略

独立的列

索引不能是表达式的一部分，也不能是函数的参数。

SELECT actor_id FROM actor WHERE actor_id + 1 =5;

如以上查询，无法使用actor_id列索引。MySQL无法自动解析这个方程，这完全是用户行为。我们应该养成化简WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

前缀索引和索引选择性

有时候索引很长的字符列，这会让索引变得大且慢。通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性（可以理解为准确度，唯一索引的选择性为1）。

所以我们要再选择性足够高的前提下，减少索引值的长度。

SELECT COUNT(*)AS cnt, LEFT(city, 3) AS pref
    FROM city GROUP BY pref ORDER BY cnt DESC LIMIT 10;

通过left截取长度，分组排序。然后和原数据进行比较，查看选择性足够高时，合适的字符长度。

选择合适的索引列顺序（B-Tree）

由于哈希或者其他类型的索引不会像B-Tree索引一样按顺序存储数据，本节只适合B-Tree索引。

经验法则：将选择性最高的列放到索引最前列。（在某些场景有效，需要更全面考虑）

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。因为此时索引的作用只是用于优化WHERE条件查找，确实能够最快地过滤出需要的行。

然而，性能不只依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

可以通过类似代码，计算出不同列的选择性，然后将选择性大的放于前面。

当某些特殊值作为查询条件导致性能很差时，也可以查询某个值在该列的占比，当占比很高，代表选择性很低，不适合作为索引条件查询。可以禁止这些特殊值应用于某个查询。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

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

索引是由存储引擎实现的，所以并不是所有的存储引擎都支持聚簇索引。此处主要关注InnoDB

[图片上传失败...(image-3c31d-1565862000648)]
如图，叶子页包含了行的全部数据，但是节点页只包含了索引列。

InnoDB将通过主键聚集数据。（一些数据库服务允许选择哪个索引作为聚簇索引。）

如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。
如果也没有，则会隐式定义一个主键来作为聚簇索引。
InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

聚簇索引的优缺点

优点：

• 可以把相关数据保存在一起
• 数据访问更快。由于索引和数据保存在一起，因此从聚簇索引中获取数据通常比非聚簇索引中查找要快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

缺点：

• 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据都放在内存中，则访问顺序就没那么重要了，聚簇索引就没有什么优势了。
• 插入速度严重依赖于插入顺序。（内部是按主键排序的。如果乱序插入后，建议使用OPTIMIZE TABLE命令重新组织一下表）
• 更新聚簇索引列的代价很高。会强制InnoDB将每个被更新的行移动到新的位置。
• 在插入新行或者主键更新时，可能某个页已满，需要进行页分裂。影响效率，并且占用更多的磁盘空间。
• 全表扫描更慢，尤其是行比较稀疏的情况，或者由于页分裂导致数据存储不连续。
• 二级索引（非聚簇索引）更大，因为需要存储主键列。
• 二级索引访问需要两次查找。（在二级索引中，先找到主键值，再去聚簇索引找对应的行）

InnoDB和MyISAM的数据分布对比

MyISAM中主键索引和其他所有在结构上没有什么不同。主键索引就是一个名为PRIMART的唯一非空索引。

在InnoDB中，聚簇索引“就是”表，每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。而在二级索引（非聚簇索引）中，叶子节点中存储的不是“行指针”，而是主键值，通过主键值在聚簇索引中找到对应行。虽然占用更多空间，但在移动行时无需更新二级索引中的这个“指针”。

image

在InnoDB表中按主键顺序插入行

推荐使用自增AUTO_INCREMENT列作为主键，这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

最好避免不连续且值分布范围非常大的聚簇索引，特别是对于I/O密集型的应用。从性能的角度考虑，使用UUID来作为聚簇索引则会很糟糕：它使得聚簇索引的插入变得完全随机，这是最坏的情况，使得数据没有任何聚集特性。

如果主键是顺序的，InnoDB只需要把插入是记录放在上一条记录的后面。当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这样顺序加载，那么主键页就会近似于被顺序的记录填满（利用率高，而不是一个页只保存稀疏的数据），这页正是所期待的结果。

如果采用类似uuid的随机主键值，每插入一条数据InnoDB都需要寻找合适的位置并分配空间（频繁地做页分裂操作）。这会增加很多额外工作，并导致数据分布不够优化（页变得稀疏），并且导致最终数据会有碎片。

顺序的主键什么时候会造成更坏的结果？

对于高并发工作负载，如果按照顺序插入，会造成明显的争用。主键的上界会成为“热点”，因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制。如果遇到这个问题，可能需要考虑重新设计表或应用，或者更改innodb_autoinc_lock_mode配置。

覆盖索引

如果可以使用索引来直接获取列的数据，不需要回表查询，则称为覆盖索引。

好处：

• 索引条目通常小于数据行大小，会极大减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应实际大部分花费在数据拷贝上。对于I/O密集型的应用，因为所有比数据更小，更容易全部放入内存中。
• 如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

覆盖索引必须要存储索引列的值，而哈希索引、空间索引等都不存储索引列的值，所以mysql只能使用B-Tree索引做覆盖索引。

索引扫描来做排序

mysql由两种方式生成有序的结果

• 通过排序
• 按索引顺序扫描。

扫描索引本身是很快的，因为只需要从一条索引记录移动扫紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不扫描一条索引记录就回表查询一次对于的行。这基本上都是随机I/O。因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能使用索引来对结果做排序。

不满足最左前缀也能利用索引排序：即前缀列的条件为常量时

# key(date, id)
where date='2005-05-25' order by id

压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数也可以对整数做压缩。

压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余不同的后缀部分，把这部分存储起来。

索引块第一个值是“perform”
第二个值是 “performance”
则第二个值存储的类似 7，ance

压缩块使用更少的空间，代价是某些操作可能更慢。因为每个值的压缩前缀都依赖于前面的值，导致的缺点是：

• 无法在索引块使用二分查找
• 正序扫描速度还行，倒叙很差
• 随即查找速度差

冗余和重复、未使用索引

重复、未使用的索引无疑要删除。（例如对主键列又设置了唯一索引）

冗余索引则需要分情况讨论。

如果创建索引(A, B)再创建索引(A)就冗余了，因为这只是前一个索引的前缀索引（只对B-Tree索引来说）。但如果创建(B, A)、(B)则不是冗余索引，因为它们都不是(A, B)的最左前缀列。

冗余索引通常发生在为表添加新索引的时候。例如在已有索引(A)，不考虑扩展成(A, B)而直接添加新索引(A, B)

大多数时候都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引。

但是如果额外需要添加索引是类似一个很长的VARCHAR列，如果采用扩展的策略，对于前一个索引来说性能可能会急剧下降。特别是有查询把这个索引当作覆盖索引，或者是MyISAM表并且有很多防卫查询的时候。

总结

• 单行访问是很慢的。如果从存储中读取一个数据块只是为了获取其中一行，则浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提高效率
• 按顺序访问访问数据是很快的。两个原因：顺序I/O不需要多次磁盘寻道，比随机I/O快很多。二，如果能按需要顺序取数据，则不需要额外的排序操作
• 索引覆盖查询很快。

命中索引

1. like
   • 模糊查询以%开头，会导致索引失效
   • 即使不以%开头，使用like也会降低查询效率
   • 如果用户量很大的话，不使用like 而会导入第三方工具处理文字
2. 避免使用函数
   • 例如使用reverse(email) 会导致索引失效
   • 尽量将类似的功能在代码中完成
3. or
   • 当or条件中有未建立的索引列会失效
   • 例： SELECT * FROM TB WHERE 索引列 or 非索引列 则在此 索引失效
   • 但是 SELECT * FROM TB WHERE 索引列 or 非索引列 and 索引列 则会只用首尾的索引列
4. 类型不一致
   • 即传入的数据类型要与列类型相符 不然索引失效
   • 例如列类型为字符 而传入数
5. ！= >
   • 普通索引使用 ！= 索引失效 但主键有效
   • 普通索引字符型 > 索引失效 数字或者主键有效
6. order by
   • 当根据索引排序时，选择的映射如果不是索引，则失效
   • 例如： select name from tb where email='123@' email是索引，但映射name 索引失效
   • 但是对主键排序，索引有效
7. 最左前缀
   • 如果组合索引为（name，email）
   • name and email 有效
   • name 有效
   • email 失效
8. 一个表建的索引尽量不要超过5个。
9. 尽量使用覆盖索引。
10. 尽量不要在重复数据多的列上建索引。
11. ...

Mysql

在Inodb存储引擎中，也有页的概念，默认每个页的大小为16K，也就是每次读取数据时都是读取4 * 4K的大小。

image

在某个页内插入新行时，为了减少数据的移动，通常是插入到当前行的后面或者是已删除行留下来的空间，所以在某一个页内的数据并不是完全有序的，但是为了数据访问顺序性，在每个记录中都有一个指向下一条记录的指针，以此构成了一条单向有序链表，不过在这里为了方便演示我是按顺序排列的！

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

由于数据还比较少，一个页就能容下，所以只有一个根结点，主键和数据也都是保存在根结点（左边的数字代表主键，右边名字、性别代表具体的数据）。假设我们写入10条数据之后，Page1满了，再写入新的数据会怎么存放呢？

image

这时候需要进行页分裂，产生一个新的Page。在innodb中的流程是：

1. 产生新的Page2，然后将Page1的内容复制到Page2
2. 产生新的Page3，将新插入的数据（“秦寿生”）放入Page3
3. 原来的Page1依然作为根结点，但是变成了一个不存放数据只存索引的页，并且有两个子结点Page2、Page3

这里有两个问题：

1. 为什么要复制Page1为Page2而不是创建一个新的页作为根结点，这样就少了一步复制的开销了？

如果是重新创建根结点，那根结点存储的物理地址可能经常会变，不利于查找。并且在innodb中根结点是会预读到内存中的，所以结点的物理地址固定会比较好！

2. 原来Page1有10条数据，在插入第11条数据的时候进行裂变，根据前面对B-Tree、B+Tree特性的了解，那这至少是一颗11阶的树，裂变之后每个结点的元素至少为11/2=5个，那是不是应该页裂变之后主键1-5的数据还是在原来的页，主键6-11的数据会放到新的页，根结点存放主键6？

如果是这样的话新的页空间利用率只有50%，并且会导致更为频繁的页分裂。所以innodb对这一点做了优化，新的数据放入新创建的页，不移动原有页面的任何记录。

每次新增数据，都是将一个页写满，然后新创建一个页继续写，这里其实是有个隐含条件的，那就是主键自增！主键自增写入时新插入的数据不会影响到原有页，插入效率高！且页的利用率高！但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率！降低页的利用率！这也是为什么在innodb中建议设置主键自增的原因！

这棵树的非叶子结点上存的都是主键，那如果一个表没有主键会怎么样？在innodb中，如果一个表没有主键，那默认会找建了唯一索引的列，如果也没有，则会生成一个隐形的字段作为主键！

有数据插入那就有删除，如果这个用户表频繁的插入和删除，那会导致数据页产生碎片，页的空间利用率低，还会导致树变的“虚高”，降低查询效率！这可以通过索引重建来消除碎片提高查询效率！

innodb引擎数据查找

1. 找到数据所在页。这个查找过程和B+树的搜索过程意义，从根结点开始查找一直到叶子结点。
2. 在页内找具体的数据。读取第一步找到的叶子结点数据到内存中，然后通过分块查找的方法找到具体的数据。

这跟我们在新华字典中找某个汉字是一样的，先通过字典的索引定位到该汉字拼音所在的页，然后到指定的页找到具体的汉字。innodb中定位到页后用了哪种策略快速查找某个主键呢？这我们就需要从页结构开始了解。

image

• 左边蓝色区域称为Page Directory，这块区域由多个slot组成，是一个稀疏索引结构，即一个槽中可能属于多个记录，最少属于4条记录，最多属于8条记录。槽内的数据是有序存放的，所以当我们寻找一条数据的时候可以先在槽中通过二分法查找到一个大致的位置。
• 右边区域为数据区域，每一个数据页中都包含多条行数据。注意看图中最上面和最下面的两条特殊的行记录Infimum和Supremum，这是两个虚拟的行记录。在没有其他用户数据的时候Infimum的下一条记录的指针指向Supremum，当有用户数据的时候，Infimum的下一条记录的指针指向当前页中最小的用户记录，当前页中最大的用户记录的下一条记录的指针指向Supremum，至此整个页内的所有行记录形成一个单向链表。
• 行记录被Page Directory逻辑的分成了多个块，块与块之间是有序的，也就是说“4”这个槽指向的数据块内最大的行记录的主键都要比“8”这个槽指向的数据块内最小的行记录的主键要小。但是块内部的行记录不一定有序。
• 每个行记录的都有一个nowned的区域（图中粉红色区域），nowned标识这个这个块有多少条数据，伪记录Infimum的nowned值总是1，记录Supremum的nowned的取值范围为[1,8]，其他用户记录nowned的取值范围[4,8]，并且只有每个块中最大的那条记录的nowned才会有值，其他的用户记录的n_owned为0。
• 所以当我们要找主键为6的记录时，先通过二分法在稀疏索引中找到对应的槽，也就是Page Directory中“8”这个槽，“8”这个槽指向的是该数据块中最大的记录，而数据是单向链表结构所以无法逆向查找，所以需要找到上一个槽即“4”这个槽，然后通过“4”这个槽中最大的用户记录的指针沿着链表顺序查找到目标记录。

聚集索引 & 非聚集索引

• 聚集索引：数据与索引存放在一起，找到索引就找到了数据。
• 非聚集索引：将数据和索引分开存储，索引结构的叶子节点指向数据的对于行

InnoDB中，在聚簇索引之上创建的索引称之为辅助索引。辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，例如：复合索引、前缀索引、唯一索引，辅助索引叶子结点存储的不再是行的物理地址，而是主键值。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

聚簇索引具有唯一性。由于将数据和索引结构存放在一起，因此一个表仅有一个聚簇索引。

聚簇索引默认是主键，如果表中没有主键，InnoDB会选择一个唯一的非空索引代替。如果也没有，InnoDB会隐式的定义一个主键来作为聚簇索引。

聚簇索引性能最好而且具有唯一性，所以非常珍贵，必须慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。

我们最终目的就是在相同结果集情况下，尽可能减少逻辑IO。

image

image

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！
3. MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引的优势、劣势

看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

1. 由于行数据和叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2. 辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
3. 聚簇索引适合用在排序的场合，非聚簇索引不适合
4. 取出一定范围数据的时候，使用用聚簇索引
5. 二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据
6. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

存在的劣势：

1. 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
2. 表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所有建议使用主键自增。
   
   image
   
   
   image
   
   主键的值是顺序的，所以 InnoDB 把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改），下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载，主键页就会近似于被顺序的记录填满（二级索引页可能是不一样的）
3. 如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值；过长的主键值，会导致非叶子节点占用占用更多的物理空间

为什么都建议使用自增主键？

因为使用自增主键可以避免页分裂。

在InnoDB中，底层的数据结构是B+树。所谓的索引其实就是一颗B+树，一个表有多少个索引就会有多少棵B+树，mysql中的数据都是按顺序保存在B+树上的（所以说索引本身是有序的。）

mysql在底层又是以数据页为单位来存储数据的，一个数据页大小默认为16K，当然也可以自定义大小。如果一个数据页存满了，mysql就会去申请一个新的数据页来存储数据。

如果主键为自增id的划，mysql在写满一个数据页的时候，直接申请另一个数据页接着写就可以了。聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，为了确保索引有序，mysql就需要将每次插入的数据都放到合适的位置上。不断地调整数据的物理地址、分页（需要把上个数据页的部分数据挪到新的数据页上。这就造成了页分裂，这个大量移动数据的过程是会严重影响插入效率的。），当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

另外在满足业务需求的情况下，尽量使用占空间更小的主键id，因为普通索引的叶子结点上保存的是主键id的值，如果主键id占空间较大的划，会成倍增加mysql空间占用大小。

因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）
不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

innodb与MyISAM对比

image

MyISAM主键索引的存储结构与innodb不同的是：

1. 主键索引树的叶子结点的数据区域没有存放实际的数据，存放的是数据记录的地址
2. 数据的存储不是按主键顺序存放的，按写入的顺序存放的

也就是说innodb引擎数据在物理上是按主键顺序存放，而MyISAM引擎数据在物理上按插入的顺序存放。并且MyISAM的叶子结点不存放数据，所以与 非聚集索引的存储结构与聚集索引类似，在使用非聚集索引查找数据的时候通过非聚集索引树就能直接找到数据的地址了，不需要回表，这比innodb的搜索效率会更高呢！

引用自推文1

引用自推文2

文章

《高性能mysql》