一文搞懂mysql索引底层逻辑，干货满满！

在MySQL中，索引是一种特殊的数据库结构，通过它我们可以快速查询数据表中具有特定值的记录。
索引相当于一本书的目录。
有了它，我们只需查看目录就可以快速找到我们要查找的信息，而不必从头开始一页一页地搜索。
在执行查询语句时，如果没有索引，MySQL必须逐行读取数据，当数据量较大时，这会大大降低效率。
通过在特定列上创建索引，MySQL在查询时只需扫描索引即可找到所需的数据，而无需读取整个数据集。
使用索引的原因是为了提高查询效率。
假设有一条查询语句：`SELECT*FROMtableWHEREcol2=85`。
如果表中没有索引，MySQL将从第一行开始逐行搜索，直到找到符合条件的记录。
如果数据量很大，这种查询方式就会变得非常低效。
但如果为“Col2”列创建索引，查询效率将会显着提高。
使用二叉树接口，MySQL首先找到35。
由于85大于35，它会搜索正确的子节点，最终找到满足条件的记录。
使用索引可以高效、快速地检索和查询数据。
在讨论索引的数据结构时，我们首先了解了二叉树。
二叉树是一种特殊的树结构，其中每个节点最多有两个子节点。
二叉树不适合作为索引的主要原因是它们最坏情况的时间复杂度可能是O(N)，这种情况发生在输入序列已经排序时。
此时二叉树结构就会变成单链表，效率极低。
平衡二叉树（例如AVL树）是一种自平衡二叉树，它通过在插入和删除操作后旋转树来保持良好的查询性能。
然而，AVL树在删除操作时效率较低，因为它需要在被删除节点到根节点的路径上进行旋转操作，在某些场景下可能不如红黑树高效。
红黑树是一种自平衡二叉搜索树，通过节点的颜色标记来保持树的平衡。
虽然红黑树在插入和删除操作时效率更高，但在极端情况下，比如数据量特别大时，树的高度可能会很高，导致搜索效率下降。
B树是一种多向搜索树，广泛应用于数据库系统中。
B树的特点包括关键字有序分布、每个节点可以有多个子节点、叶节点存储实际数据、自动分层控制。
这些特性使得B树在数据搜索、插入和删除操作中高效，并避免了平衡B树的复杂性。
B+树是B树的变体，特别适合数据库和文件系统。
B+树中，节点不叶子不存储数据，只存储索引，这使得B+树在磁盘读写操作上更加高效，并且支持高效的范围查询。
B+树的叶子节点形成有序链表，进一步提高了区间查询的性能。
在MySQL中，不同的存储引擎以不同的方式实现索引。
MyISAM引擎基于B+树索引，将索引数据存储在内存中，将索引和数据文件分开存储，实现快速查询。
InnoDB引擎使用聚集索引，其表数据文件本身就是一个以B+树组织的索引结构文件。
数据和索引紧密相连，赋予了InnoDB在事务处理和数据安全方面的优势。
联合索引允许您在多个字段上创建索引。
MySQL会遵循最左前缀原则，即索引列中最左边的字段将首先用于查询。
这有助于优化查询性能，避免表查询，提高查询效率。
覆盖索引可以直接从索引中获取所有查询列的数据，从而避免额外的返表操作，进一步提高查询效率。

数据库索引简析

数据库索引简析MySQL执行计划中使用索引的目的是保证查询性能，特别是范围和引用查询级别。
执行级别从最好到最差是const>ref>range>index>all。
EXPLAIN命令可以帮助您了解查询中索引的使用。
在全值匹配查询中，索引可以极大地优化性能，减少磁盘IO操作。
匹配顺序以及范围值的处理方式直接影响查询效率，尤其是在涉及精确匹配和范围匹配的场景下。
索引覆盖率是优化查询、避免不必要的表值操作以及降低数据访问成本的关键。
如果希望组合索引从最左边的字段开始并避免跳过字段，请使用最左边的前缀策略。
联合索引包括单列索引和连接索引。
单列索引简单高效，而组合索引可以处理更多的查询场景，提供更灵活的查询方式。
最左前缀原则确保索引的有效使用。
聚集索引和非聚集索引是存储数据的两种方法。
聚集索引将数据与索引紧密联系起来，通过B+树实现高效查询，适合大数据存储和主键查询的快速访问。
非聚集索引将数据和索引分开存储，适用于多种查询场景。
InnoDB存储引擎实现了ACID特性来保证事务的原子性、一致性、隔离性和持久性。
行级锁定和多版本并发控制机制优化并发查询场景下的性能，主键聚集索引提高IO效率。
区分锁可以帮助您管理并发操作。
全局锁和表级锁对应不同的应用需求。
表共享读锁和排它写锁保证了事务之间的隔离，行级锁适合高并发的OLTP应用。
共享锁和排他锁提供多种场景下的数据访问控制。
B+树和B-树的索引结构存在显着差异。
B+树的叶子节点包含完整的数据和索引，适合外部存储和范围查询。
查询时间复杂度为O(log(n))。
B树的每个节点都存储数据和索引，搜索时间可以为O(1)，而且高度较低，适合快速搜索。
应用数据库索引包括全表查询、连接查询、多表查询等场景。
通过合理的索引设计和使用，可以显着提高查询性能并减少资源消耗。
为了获得最佳性能，您必须根据具体的查询需求和数据特征灵活调整索引优化策略。

MySQL之：为什么InnoDB索引有长度限制？

在MySQL的InnoDB存储引擎中，索引长度限制是优化查询性能和存储效率的重要设计考虑因素。
让我们找出为什么索引有长度限制。
B+树是InnoDB内部数据结构的基础。
其特点是所有数据都存储在叶子节点中，并且数据按照key-value顺序组织。
这确保了高效的搜索和插入操作。
InnoDB支持主键索引和非主键索引。
主键索引包含完整的数据，而非主键索引仅包含指向主键数据的指针。
系统参数“innodb_page_size”定义页面大小并影响数据库读写操作。
在当前版本（>=5.7.6）中，可用值为4096、8192、16384、32768、65536，默认值为16KB。
该值直接影响数据存储和行索引。
考虑行数据中的隐藏字段，例如“trx_id”、“roll_ptr”和“row_id”，它们总共使用6+7+6字节。
这意味着一个页面应该至少包含2行数据才能维持B+树的效率。
因此，每行的大小不能超过约8000字节。
将此限制除以2，您将得到每行索引大小的上限4000字节。
您必须在实际内存中预留空间，使得每个行索引的最大有效大小约为3072字节。
对于聚集索引，InnoDB将主键和数据存储在同一页上，并在搜索时直接访问数据。
这种设计提供了对数据的高效访问，但数据的物理顺序与索引的顺序一致。
如果主键不是自增ID，则数据布局会更加复杂，频繁更改可能会导致性能下降。
相比之下，使用自增ID作为主键，数据布局更紧凑，磁盘碎片更少，查询效率更高。
非聚集索引将数据和索引分开存储，允许通过主键索引进行快速访问。
但是，这需要额外的查询步骤才能获取完整的数据。
这种设计在复杂查询场景中提供了灵活性，但可能不如聚集索引高效。
简而言之，InnoDB索引长度限制是为了保证高效的数据访问和存储以及优化查询性能。
通过合理选择索引结构和参数，可以最大限度地发挥InnoDB的性能优势。