mysql专题（一）：深入理解Mysql索引底层数据结构与算法

• Mysql索引

帮助MySQL高效获取数据的排好序的数据结构。

1、索引

一种为表的行提供快速查找功能的数据结构，通常通过形成表示特定列或列集 的所有值的 树结构（B 树）来实现。

InnoDB表总是有一个 代表主键的聚簇索引。它们还可以在一个或多个列上定义一个或多个二级索引。根据其结构，二级索引可以分为 部分索引、 列索引或 复合索引。

索引是查询性能的一个重要方面 。设计表、查询和索引，以允许快速查找应用程序所需的数据。理想的数据库设计在可行的情况下使用覆盖索引 ；查询结果完全根据索引计算，而不读取实际的表数据。每个 外键约束还需要一个索引，以有效地检查父表和 子表中是否存在值。

虽然 B 树索引是最常见的，但哈希索引 使用了不同类型的数据结构，如MEMORY存储引擎和InnoDB 自适应哈希索引。 R树索引用于多维信息的空间索引。

2、索引缓存

InnoDB 保存全文搜索 令牌数据的内存区域 。当在属于 FULLTEXT 索引的列中插入或更新数据时，它会缓冲数据以最大限度地减少磁盘 I/O 。当索引缓存变满时，令牌数据将写入磁盘。每个InnoDB FULLTEXT索引都有自己独立的索引缓存，其大小由配置选项控制 innodb_ft_cache_size。

3、索引条件下推

索引条件下推 (ICP) 是一种优化，WHERE如果条件的一部分可以使用索引中的字段进行评估，则将条件的一部分下推到存储引擎。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

4、索引提示

用于覆盖优化器推荐的索引的扩展SQL语法。例如，the FORCE INDEX、USE INDEX和IGNORE INDEX子句。通常在索引列的值分布不均匀时使用，从而导致基数估计不准确。

5、索引前缀

在应用于多列的索引（称为复合索引）中，索引的初始列或前导列。引用复合索引的前 1、2、3 等列的查询可以使用该索引，即使查询没有引用索引中的所有列。

6、主要概念

主键	一组列——暗示，基于这组列的索引——可以唯一标识表中的每一行。因此，它必须是不包含任何NULL值的唯一索引。 InnoDB要求每张表都有这样的索引（也叫聚簇索引或聚簇索引），根据主键的列值来组织表存储。 选择主键值时，请考虑使用任意值（合成键），而不是依赖从其他来源派生的值（ 自然键）。
自然键	索引列，通常是主键，其中的值具有一定的现实意义。通常不建议这样做，因为： 如果该值应该发生变化，则可能需要进行大量索引维护以重新 排序聚集索引并更新在每个二级索引中重复的主键值副本。 即使看似稳定的值也可能以难以在数据库中正确表示的不可预知的方式发生变化。例如，一个国家可以变成两个或几个，使原来的国家代码作废。或者，关于唯一值的规则可能有例外。例如，即使纳税人 ID 对于一个人来说是唯一的，数据库也可能不得不处理违反该规则的记录，例如身份盗用的情况。纳税人 ID 和其他敏感的 ID 号也是不好的主键，因为它们可能需要保护、加密，并且在其他方面不同于其他列。 因此，通常最好使用任意数值来形成合成键，例如使用自动增量列。
合成键	索引列，通常是主键，其中的值是任意分配的。通常使用自动增量 列来完成。通过将值视为完全任意的，您可以避免过度限制的规则和错误的应用程序假设。例如，如果员工被批准雇用但从未真正加入，则代表员工编号的数字序列可能会有差距。或者 100 号员工的雇用日期可能比 500 号员工的雇用日期晚，前提是他们离开公司后又重新加入。数值也会产生可预测长度的较短值。例如，存储表示“道路”的数字代码, “ Boulevard ” , “ Expressway ”等等比一遍又一遍地重复这些字符串更节省空间。也称为代理键。与自然键对比。
聚集索引	主键InnoDB索引的术语。表存储是根据主键列的值来组织的，以加快涉及主键列的查询和排序。为获得最佳性能，请根据性能最关键的查询仔细选择主键列。因为修改聚集索引的列是一项昂贵的操作，所以选择很少或从不更新的主列。 InnoDB在 Oracle 数据库产品中，这种类型的表称为索引组织表。
二级索引	一种表示表列子集的索引InnoDB 。一个InnoDB表可以有零个、一个或多个二级索引。（对比聚簇索引，它是每个表都需要的InnoDB，存储所有表列的数据。） 二级索引可用于满足仅需要索引列中的值的查询。对于更复杂的查询，它可用于识别表中的相关行，然后使用聚簇索引通过查找检索这些行。 创建和删除二级索引传统上涉及复制表中所有数据的大量开销 InnoDB。快速索引创建功能使二级索引的CREATE INDEXandDROP INDEX语句更快InnoDB。
全文索引	MySQL全文搜索机制中保存搜索索引的 一种特殊索引 。表示列值中的单词，省略任何指定为 stopwords 的单词。最初，仅适用于表格。从 MySQL 5.6.4 开始，它也可用于 InnoDB表。
复合索引	包含多个列的索引。
覆盖索引	包含查询检索到的所有列的索引。查询不使用索引值作为指针来查找完整的表行，而是从索引结构返回值，从而节省磁盘 I/O。 InnoDB可以将此优化技术应用于比 MyISAM 更多的索引，因为 InnoDB 二级索引还包括 主键列。 InnoDB在该事务结束之前，不能将此技术应用于对事务修改的表的查询。 如果查询正确，任何列索引或 复合索引都可以充当覆盖索引。设计索引和查询以尽可能利用此优化技术。
哈希索引	一种索引，用于使用相等运算符而不是范围运算符（如大于或）的查询BETWEEN。它可用于MEMORY表格。尽管出于历史原因哈希索引是表的默认索引 MEMORY，但该存储引擎还支持 B 树索引，这通常是通用查询的更好选择。 MySQL 包含此索引类型的变体，即 自适应哈希索引，如果需要，它会 InnoDB根据运行时条件自动为表构建。
部分索引	仅表示列值的一部分的索引，通常是long值的前N个字符（ 前缀）VARCHAR。
统计数据	InnoDB 与每个表和索引相关的估计值，用于构建高效的查询执行计划。主要值是基数（不同值的数量）和表行或索引条目的总数。该表的统计信息表示其主键索引中的数据。二级索引的统计信息表示该索引涵盖的行。 这些值是估计的，而不是精确计算的，因为在任何时候，不同的 事务都可以从同一个表中插入和删除行。为了防止值被频繁重新计算，您可以启用 持久统计，其中值存储在InnoDB系统表中，并且仅在您发出语句时刷新 ANALYZE TABLE。 您可以通过配置选项控制在计算统计信息时如何处理 NULLinnodb_stats_method值。 通过INFORMATION_SCHEMA和 PERFORMANCE_SCHEMA表，其他类型的统计信息可用于数据库对象和数据库活动。

• Mysql索引类型

常用于数据库索引的树数据结构。该结构始终保持排序，支持快速查找精确匹配（等于运算符）和范围（例如，大于、小于和BETWEEN 运算符）。这种类型的索引适用于大多数存储引擎，例如InnoDB和 MyISAM。

因为 B 树节点可以有很多子节点，所以 B 树与二叉树不同，二叉树每个节点只能有 2 个子节点。

与仅在存储引擎中可用的哈希索引 相对MEMORY 。存储MEMORY引擎也可以使用B-tree索引，MEMORY如果某些查询使用范围操作符，你应该为表选择B-tree索引。

术语 B 树的使用旨在作为索引设计的一般类别的参考。由于经典 B 树设计中不存在的复杂性，MySQL 存储引擎使用的 B 树结构可能被视为变体（主要体现在子节点数据的双向链表设计）。相关信息参考MySQL Internals Manual的 InnoDBPage Structure Fil Header部分。

1、B-Tree 索引特性

B 树索引可用于在使用 =、 >、 >=、 <、 <=或BETWEEN运算符的表达式中进行列比较。LIKE 如果参数 LIKE是不以通配符开头的常量字符串，索引也可用于比较。例如，以下SELECT语句使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第一个语句中，只考虑带有的行。在第二条语句中，只考虑带有的行。

'Patrick' <= key_col < 'Patricl'  'Pat' <= key_col < 'Pau'

以下SELECT语句不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一条语句中，LIKE 值以通配符开头。在第二个语句中，LIKE值不是常量。

如果使用and 超过三个字符，MySQL 使用Turbo Boyer-Moore 算法初始化字符串的模式，然后使用该模式更快地执行搜索。 ... LIKE '%string%'string

col_name IS NULL如果已编入索引，则使用使用索引的 搜索col_name。

任何未跨越 子句AND中所有级别的 索引WHERE都不会用于优化查询。换句话说，为了能够使用索引，必须在每个 AND组中使用索引的前缀。

以下WHERE子句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

     /* index = 1 OR index = 2 */

... WHERE index=1 OR A=10 AND index=2

     /* optimized like "index_part1='hello'" */

... WHERE index_part1='hello' AND index_part3=5

     /* Can use index on index1 but not on index2 or index3 */

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

这些WHERE子句不 使用索引：

     /* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

     /*  Index is not used in both parts of the WHERE clause  */

... WHERE index=1 OR A=10

     /* No index spans all rows  */

... WHERE index_part1=1 OR index_part2=10

有时 MySQL 不使用索引，即使索引可用。发生这种情况的一种情况是优化器估计使用索引将需要 MySQL 访问表中很大一部分行。（在这种情况下，表扫描可能会快得多，因为它需要更少的查找。）但是，如果这样的查询仅用于LIMIT检索某些行，则 MySQL 无论如何都会使用索引，因为它可以更快地找到结果中返回几行。

2、哈希索引特征

散列索引与刚刚讨论的那些有一些不同的特征：

它们仅用于使用 =or in运算符（但速度非常快）的相等比较。它们不用于比较运算符，例如 <查找值范围的运算符。依赖这种类型的单值查找的系统被称为“键值存储”；要将 MySQL 用于此类应用程序，请尽可能使用哈希索引。

优化器不能使用散列索引来加速 ORDER BY操作。（这种类型的索引不能用于按顺序搜索下一个条目。）

MySQL 无法确定两个值之间大约有多少行（范围优化器使用它来决定使用哪个索引）。如果将MyISAM或 InnoDB表更改为散列索引 MEMORY表，这可能会影响某些查询。

只能使用整个键来搜索行。（对于 B 树索引，键的任何最左边的前缀都可用于查找行。）

• Mysql索引数据结构

InnoDB索引实现(聚集)

表数据文件本身就是按B+Tree组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

 

• Mysql索引为什么选用B+树？

1、二叉树                       

2、平衡二叉树

3、红黑树       

          

4、B树

5、B+树  

      

6、hash

7、B*树（B+树变种INNODB）   

 

8、B*树（B+树变种MYISAM）

• 联合索引的底层存储结构长什么样？

• mysql限制

InnoDB 限制（最大表空间大小也是表的最大大小）：

InnoDB 页面大小	最大表空间大小
4KB	16TB
8KB	32TB
16KB	64TB
32KB	128TB
64KB	256TB

一个表最多可以包含 1017 列（在 MySQL 5.6.9 中提高了之前的 1000 列限制）。虚拟生成的列包含在此限制中。

一个表最多可以包含 64 个 二级索引。

如果innodb_large_prefix启用（默认），索引键前缀限制为 3072 字节，用于使用 or 行格式InnoDB的表。如果禁用，对于任何行格式的表，索引键前缀限制为 767 字节。  innodb_large_prefix在 MySQL 5.5 中引入以禁用大索引键前缀，以与 InnoDB不支持大索引键前缀的早期版本兼容。

InnoDB对于使用 REDUNDANT or 行格式的表 ，索引键前缀长度限制为 767 字节 COMPACT。例如，您可能会 在或 列上使用超过 255 个字符的列前缀索引达到此限制，假设有一个 字符集并且每个字符最多 3 个字节。 TEXT VARCHAR utf8mb3尝试使用超过限制的索引键前缀长度会返回错误。innodb_large_prefix为避免复制配置中的此类错误，如果不能在副本上启用，请避免在源上启用。

如果您在创建 MySQL 实例时通过指定选项将页面大小减小到InnoDB 8KB或 4KB ，则索引键的最大长度将按比例降低，基于 16KB 页面大小的 3072 字节限制。innodb_page_size即页大小为8KB时索引键最大长度为1536字节，页大小为4KB时为768字节。适用于索引键前缀的限制也适用于全列索引键。

多列索引最多允许 16 列。超出限制会返回错误。

ERROR 1070 (42000): Too many key parts specified; max 16 parts allowed

对于 4KB、8KB、16KB 和 32KB 页面大小，最大行大小（不包括页外存储的任何可变长度列）略小于页面的一半。例如，默认 innodb_page_size16KB 的最大行大小约为 8000 字节。但是，对于InnoDB 64KB 的页面大小，最大行大小约为 16000 字节。LONGBLOB和 LONGTEXT 列必须小于 4GB，并且包括列在内的总行大小BLOB必须 TEXT小于 4GB。

如果一行的长度小于半页，则所有行都存储在页面的本地。如果它超过半页，则选择可变长度列用于外部页外存储，直到该行适合半页，如第 14.12.2 节，“ 文件空间管理”中所述。

尽管InnoDB在内部支持大于 65,535 字节的行大小，但 MySQL 本身对所有列的组合大小施加了 65,535 的行大小限制。请参阅 第 8.4.7 节，“表列数和行大小的限制”。

在一些较旧的操作系统上，文件必须小于 2GB。这不是InnoDB限制。如果您需要一个大型系统表空间，请使用多个较小的数据文件而不是一个大型数据文件对其进行配置，或者将表数据分布在 file-per-table 和通用表空间数据文件中。

日志文件的最大组合大小InnoDB为 512GB。

最小表空间大小略大于 10MB。最大表空间大小取决于 InnoDB页面大小。

在 Windows 32 位系统上，表空间文件不能超过 4GB（缺陷号 80149）。

一个InnoDB实例最多支持 2^32 (4294967296) 个表空间，其中少量表空间保留用于撤消表和临时表。

共享表空间最多支持 2^32 (4294967296) 个表。

表空间文件的路径，包括文件名，不能超过MAX_PATHWindows 的限制。在 Windows 10 之前，MAX_PATH限制为 260 个字符。从 Windows 10 版本 1607 开始， MAX_PATH常见 Win32 文件和目录功能的限制已被删除，但您必须启用新行为。

ROW_FORMAT=COMPRESSED在 Barracuda文件格式中假定页面大小最大为 16KB 并使用 14 位指针。

列数限制	MySQL 对每个表有 4096 列的硬限制，但对于给定的表，有效最大值可能会更少。确切的列限制取决于几个因素： 表的最大行大小限制了列的数量（可能还有大小），因为所有列的总长度不能超过此大小。 各个列的存储要求限制了适合给定最大行大小的列数。某些数据类型的存储要求取决于存储引擎、存储格式和字符集等因素。 存储引擎可能会施加额外的限制来限制表的列数。例如， InnoDB每个表有 1017（16*1024%（8+2+6））列的限制。 每个表都有一个.frm包含表定义的文件。该定义以可能影响表中允许的列数的方式影响此文件的内容。请参阅 .frm 文件结构施加的限制。
行大小限制	给定表的最大行大小由几个因素决定： MySQL 表的内部表示具有 65,535 字节的最大行大小限制，即使存储引擎能够支持更大的行也是如此。 BLOB和 TEXT列仅对行大小限制贡献 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。 适用于本地存储在数据库页面中的数据的表的最大行大小InnoDB 略小于 4KB、8KB、16KB 和 32KB 设置的半页 innodb_page_size 。例如，对于默认的 16KB 页面大小，最大行大小略小于 8KB InnoDB。对于 64KB 页面，最大行大小略小于 16KB。 如果包含可变长度列的 行 超过InnoDB 最大行大小，InnoDB则选择可变长度列用于外部页外存储，直到该行符合InnoDB 行大小限制。对于页外存储的可变长度列，本地存储的数据量因行格式而异。 不同的存储格式使用不同数量的页眉和页尾数据，这会影响可用于行的存储量。
表大小限制	如果遇到全表错误，可能有以下几种原因： 磁盘可能已满。 您正在使用InnoDB表并且表空间文件中的空间已用完InnoDB。最大表空间大小也是表的最大大小。 通常，对于大小超过 1TB 的表，建议将表分区为多个表空间文件。 您已达到操作系统文件大小限制。例如，您MyISAM在仅支持最大 2GB 文件大小的操作系统上使用表，并且您已达到数据文件或索引文件的此限制。 您正在使用一个MyISAM表，并且该表所需的空间超出了内部指针大小所允许的范围。MyISAM默认情况下允许数据和索引文件增长到 256TB，但可以将此限制更改为最大允许大小 65,536TB（256 7 − 1 字节）。
数据库数量限制	MySQL 对数据库的数量没有限制。底层文件系统可能对目录数量有限制。
表数量限制	MySQL 对表的数量没有限制。底层文件系统可能对表示表的文件数量有限制。各个存储引擎可能会施加特定于引擎的约束。InnoDB最多允许 40 亿张表。

• mysql各种文件

.ARM文件	表的元数据ARCHIVE。与.ARZ 文件对比。具有此扩展名的文件始终包含在由 MySQL Enterprise Backupmysqlbackup产品的命令生成的备份中。
.ARZ文件	ARCHIVE 表的数据。对比.ARM 文件。具有此扩展名的文件始终包含在由 MySQL Enterprise Backupmysqlbackup产品的命令生成的备份中 。
.cfg 文件	InnoDB 与可传输表空间功能 一起使用的元数据文件 。它由命令生成FLUSH TABLES ... FOR EXPORT，将一个或多个表置于可以复制到另一台服务器的一致状态。该 .cfg文件与相应的.ibd 文件一起复制，并用于 在 步骤中调整.ibd文件的内部值，例如 空间ID ALTER TABLE ... IMPORT TABLESPACE。
.ibd 文件	file-per-table表空间和通用表空间 的数据文件 。File-per-table 表空间 .ibd文件包含单个表和关联的索引数据。一般表空间 .ibd文件可能包含多个表的表和索引数据。MySQL 5.7.6 中引入了通用表空间。文件.ibd扩展名不适用于 由一个或多个ibdata 文件组成的系统表空间。如果使用该子句创建 file-per-table 表空间或通用表空间DATA DIRECTORY =，则 文件位于指定路径，正常数据目录之外，并由.isl 文件.ibd指向。当一个.ibd文件包含在MySQL Enterprise Backup产品的压缩备份中时，压缩的等价物是一个 .ibz文件。
.ibz 文件	当MySQL Enterprise Backup产品执行 压缩备份时，它会将使用file-per-table设置 创建的 每个表空间文件从扩展名转换为 扩展名。 .ibd.ibz备份期间应用的压缩不同于 在正常操作期间保持表数据压缩的压缩行格式。压缩备份操作会跳过已经采用压缩行格式的表空间的压缩步骤，因为第二次压缩会减慢备份速度，但产生的空间节省很少或没有。
.isl文件	一个文件，指定.ibd 文件 的位置，该文件用于使用MySQL 5.6 及更高版本中的子句或MySQL 5.7 及更高版本中的子句InnoDB创建的表。 它的功能类似于符号链接，不受实际符号链接机制的平台限制。您可以将表空间存储在 数据库目录之外，例如，根据表的用途，存储在特别大或快速的存储设备上。
ib_log文件	一组文件，通常命名为ib_logfile0 和ib_logfile1，它们构成了 重做日志。有时也称为日志组。这些文件记录了试图改变 InnoDB表中数据的语句。在崩溃后启动时，这些语句会自动重放以更正由不完整事务写入的数据。 此数据不能用于手动恢复；对于这种类型的操作，请使用二进制日志。
.frm 文件	MySQL 将表的数据字典信息存储在数据库目录中的.frm文件中。与其他 MySQL 存储引擎不同， InnoDB还在系统表空间内的自己的内部数据字典中对有关表的信息进行编码。当 MySQL 删除表或数据库时，它会删除一个或多个.frm文件以及InnoDB数据字典中的相应条目。您不能InnoDB仅通过移动文件来在数据库之间移动表.frm。
.MRG文件	包含对其他表的引用的文件，由 MERGE存储引擎使用。具有此扩展名的文件始终包含在由 MySQL Enterprise Backup产品的mysqlbackup命令生成的备份中 。
.MYD文件	MySQL 用来存储表数据的文件 MyISAM。
.MYI文件	MySQL 用来存储 MyISAM表索引的文件。