SQL索引

 

1. 概念：

　　MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构；　　

　　SQL索引是一种数据结构，用于提高数据库系统（DBMS）对表中数据的查询速度与性能；索引相当于书籍的目录，可以让数据库系统快速定位和访问表中特定的数据行，而不必扫描整个表；

 

　　索引通常基于表中一个或多个列的值构建的；当在查询中使用索引列作为条件进行过滤或排序时，数据库系统可利用索引进行快速定位匹配的数据行，从而加速查询的执行过程；

　　通过索引，找到磁盘内数据的地址，通过地址取值。

 

2. 类型：

　　SQL索引的工作原理类似于数据的索引；

　　常见索引类型：

• 单列索引：基于单个列的值构建的索引，常见于对单一列进行查询和排序的场景。
• 复合索引：基于多个列的值构建的索引，适用于涉及多个列的查询条件，能够提升复合条件查询的效率。
• 唯一索引：索引列的值必须唯一，用于保证数据完整性和约束。唯一索引要求键值不能重复，而主键索引是一种特殊的唯一索引，要求键值不能为空。
  
  

　　索引类型：

• 普通索引（Normal）：也称为非唯一索引，最普通的索引，没有任何限制。
• 唯一索引（Unique）：要求键值不能重复。主键索引是一种特殊的唯一索引，要求键值不能为空。
• 全文索引（Fulltext）：适用于较大的文本数据，比如存放消息内容等。可解决LIKE查询效率低的问题，只有文本类型的字段才可以创建全文索引，如 char、varchar、text。

　　

　　优点：

• 加速数据检索速度：索引可以减少数据库系统需要扫描的数据量，从而提高查询效率。
• 改善查询性能：索引可以减少数据库系统执行查询所需的时间，从而提高整体系统性能。
• 提高数据完整性：唯一索引可以保证索引列的值唯一性，确保数据的完整性和一致性。

 

　　注意：

• 索引需要占用额外的存储空间，可能会增加数据库的存储成本。
• 索引的维护会增加插入、更新和删除操作的成本，因为数据库系统需要维护索引的结构和状态。
• 不恰当的索引设计可能会导致查询性能下降，因此需要根据实际的查询模式和业务需求进行合理的索引设计和优化。

---

 

3. 树

　　概念：
　　树是一种抽象数据类型，它由一组以边连接的节点组成。树的一个重要性质是没有循环路径。

　　1) 二叉树（Binary Tree）：是一种树形数据结构，除了叶子节点，其他节点最多有两个孩子的节点且有序；
　　2) 平衡二叉树（Balanced Binary Tree）：一种树结构，左右子树的高度差不超过1。定义比较宽泛，因此有很多种可能的平衡二叉树，包括AVL树、红黑树、Splay树等；
　　3) B树（B-tree）：是一种平衡树结构，所有叶节点具有相同的深度。树中的索引元素唯一且有序排列（不重复），节点中的数据索引从左到右递增。
　　4) B+树：是一种多路平衡查找树，是B树的一种改进版本。

• • 非叶子节点的特点： 非叶子节点不存储实际数据，只存储索引信息，这样可以容纳更多的索引，提高了树的分支因子，减少了树的高度。
  • 叶子节点的特点： 叶子节点包含了所有的索引字段以及对应的数据，这样可以实现在叶子节点上进行数据的直接查找。
  • 叶子节点的连接： 叶子节点通过指针连接起来，形成一个有序链表，这样可以提高区间访问的性能，支持范围查询和顺序访问。
  • 适用性： B+树适用于大规模数据集合的索引结构，特别是在需要频繁范围查询和顺序访问的场景下，因为它具有较高的查询性能和更适合大规模数据集合的特点。

　　　　总的来说，B+树是一种高效的索引结构，通过优化非叶子节点的存储方式和叶子节点的连接方式，实现了更高效的查找和查询操作。

---

 

4. InnoDB引擎

　　InnoDB引擎结构：是B+树：
　　InnoDB每张表的数据结构都存储在一个B+树的索引上——使用B+树是实现主键索引和辅助索引；
　　主键索引叶子节点存储正行数据；辅助索引叶子节点存储着主键值，因此需要通过主键值再去检索整行数据。
　　B+树在InnoDB中的应用使得查找、范围查询和排序等操作具有高效性，同时也能够处理大量数据，适合用于高并发的数据库系统。
　　
　　InnoDB引擎工作原理：
　　在InnoDB内，是以主键索引组织数据的存储，故索引文件和数据文件都是同一个文件，都在 .db 文件内；
　　在InnoDB的主键索引的叶子节点上，直接存储数据。

　　非叶子节点上存放主键和地址值（孩子节点的地址）；
　　叶子节点上，主键和主键对应的数据；

5. Hash索引

　　Hash索引是一种数据库索引结构，使用哈希表来加速数据的检索。与B+树索引不同，Hash索引将索引键值通过哈希函数映射到哈希表中，从而直接确定数据的存储位置，使得查找速度非常快。

　　Hash算法：根据主键进行hash，得到的Hash值就是数据存储的地址值；

• 时间复杂度： Hash索引的查询时间复杂度是O(1)，因为通过哈希函数直接计算出存储位置，查找速度很快。这使得Hash索引在等值查询（= IN）方面非常有效。

• 不支持排序和范围查询： 由于Hash索引中的数据不是按顺序存储的，所以不能用于排序操作。另外，由于Hash索引是通过哈希码来确定存储位置的，不支持范围查询操作（> < >= <= between and）。

• 哈希冲突： 当字段重复值很多时，可能会出现大量的哈希冲突。哈希冲突是指不同的键值经过哈希函数计算得到相同的哈希码，导致多个键值映射到同一个存储位置。这会降低Hash索引的效率，因为需要处理冲突的情况。

6. MylSAM　　

　　1) 一个是.MYD 文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录

　　2) 一个是.MYI 文件，I 代表 Index，是 MyISAM 的索引文件，存放索引，比如我们在id 字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。

　　3) MyISAM 的 B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI 中找到键值后，会到数据文件.MYD 中获取相应的数据记录。

　　地址值：

　　　　非叶子节点放主键和地址值（孩子节点的地址）；

　　　　叶子节点存放的是主键和.MYD文件中的数据的地址；