索引结构详解
B树
- 结构：B树的每个节点都存储键值和数据。
B+树
- 结构：B+树的非叶子节点仅存储键值信息，不存储数据，叶子节点存储所有数据，并且叶子节点之间通过指针相连。
- 性能：B+树的非叶子节点不存储数据，仅用于索引，容纳更多的键值，这使得树的高度更低，减少了磁盘I/O操作。叶子节点的链表结构使得范围查询更加高效。这使得B+树在数据库索引中更为常用，InnoDB数据引擎默认使用B+树作为索引结构。
哈希索引
结构：哈希索引使用哈希表实现，通过计算行数据的哈希值来确定数据的存储位置。
性能：哈希索引在执行等值查询时非常快，因为它可以直接定位到数据。但是，它不支持范围查询和顺序检索。
索引分类详解
聚簇索引（Clustered Index）
- 定义：聚簇索引确定了数据行的物理存储顺序，通常在主键上建立；如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引；若没有的唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引。
- 特点：每个表有且仅有一个聚簇索引，因为数据行不能同时按照两种不同的顺序进行物理存储。
- 访问方式：聚簇索引直接访问数据行，无需回表查询。
二级索引（Secondary Index）
- 定义：除了聚簇索引之外的其他索引都是二级索引。
- 特点：二级索引包含指向数据行的指针，这些指针指向数据行在聚簇索引中的位置。
- 访问方式：当执行一个基于二级索引的查询时，MySQL首先会在二级索引的B+树中搜索对应的主键值。由于二级索引的叶子节点仅存储主键值和索引列的数据，要查询的字段可能不在二级索引的叶子节点中存储，那么此时需要回表操作，通过主键值回到聚簇索引中查找完整的数据行。
索引使用规则详解
最左前缀法则
- 规则：在复合索引中，查询条件必须从最左边的列开始匹配，索引才能被有效利用。
- 正确示例：对于索引
(a, b, c)，以下查询可以利用索引：WHERE a = 'value' AND b = 'value'
- 反例：以下查询将不会使用索引，因为条件没有从最左边的列开始：
WHERE c = 'value'（没有先匹配 a 和 b）
索引失效情况
- 使用
%通配符的LIKE查询：例如LIKE '%value'，索引失效。%通配符会导致无法从索引的最左列开始匹配，因为索引无法预判字符串的开头，’value%’则可以。 - 在索引列上进行函数操作：例如
WHERE YEAR(date_column) = 2024，索引失效。 - 使用
OR条件：用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 - 隐式类型转换：
WHERE column_name = 123（123没有加’ ‘,而column_name是字符串类型）MySQL 会对索引列进行隐式类型转换，导致索引失效。 - 使用不等于 (
!=或< >)：SELECT * FROM table_name WHERE column_name != 'value';不等于操作会导致全表扫描，因为无法通过索引快速定位不等于的值（<= 或 >= 索引不会失效）。
SQL提示（HINT）
SQL提示是数据库提供的优化器指令，用于指导查询优化器在执行查询时的行为。通过SQL提示，可以显式地指示优化器使用或忽略特定的索引，以此来提高查询效率或避免不必要的索引扫描。
强制使用指定索引：
1
SELECT * FROM table_name USE INDEX (index_name) WHERE column_name = 'value';
这条语句指示优化器在查询时强制使用
index_name索引。忽略指定索引：
1
SELECT * FROM table_name IGNORE INDEX (index_name) WHERE column_name = 'value';
这条语句指示优化器在查询时忽略
index_name索引。强制优化器选择最优索引：
1
SELECT * FROM table_name FORCE INDEX (index_name) WHERE column_name = 'value';
这条语句指示优化器在查询时强制使用
index_name索引，即使它可能不是查询优化器自动选择的最优索引。
查询优化
覆盖索引&回表查询
- 覆盖索引：如果索引包含所有查询所需的列，则不需要回表。
- 回表查询：如果索引不包含所有所需列，则需要回表查询完整数据行。
为了优化查询以使用覆盖索引，可以采取以下措施：
- **避免使用
SELECT ***：明确指定需要查询的列，这样可以更容易地利用覆盖索引。 - 使用联合索引：根据查询条件设计联合索引，遵循最左前缀法则，这样可以在查询时减少回表。
示例：
1 | CREATE INDEX index_name ON table_name (column1, column2); |
通过这样的索引设计，对于查询WHERE column1 = 'value' AND column2 = 'value2'，可以利用联合索引，减少回表。
前缀索引
- 定义：对于字符串列，如果查询通常只匹配前几个字符，可以使用前缀索引来提高查询效率，同时减少索引占用的存储空间。
- 示例：
假设有一个用户表tb_user，其中存储了用户的电话号码phone_number，如果查询通常只匹配电话号码的前几位，可以创建前缀索引：上述语句表示只对电话号码的前7位字符建立索引。1
CREATE INDEX idx_phone_prefix ON tb_user (phone_number(7));
如何选择前缀长度
- 原则：前缀长度的选择应在保证索引选择性较高的前提下，尽量减少索引的存储空间。
- 索引选择性：索引选择性是指不重复的索引值（基数）与数据表记录总数的比值，计算公式如下：
1
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;
- 索引选择性越接近1，查询效率越高。
- 唯一索引的选择性为1，是最理想的索引选择性。
计算选择性
通过以下步骤选择合适的前缀长度：
计算完整列的选择性：
1
SELECT COUNT(DISTINCT phone_number) / COUNT(*) AS selectivity FROM tb_user;
如果选择性已经很高，可以直接使用完整列作为索引。
尝试不同的前缀长度：
通过截取电话号码的前几位，计算不同前缀长度的选择性：1
2
3
4
5-- 前3位的选择性
SELECT COUNT(DISTINCT SUBSTRING(phone_number, 1, 3)) / COUNT(*) AS selectivity FROM tb_user;
-- 前7位的选择性
SELECT COUNT(DISTINCT SUBSTRING(phone_number, 1, 7)) / COUNT(*) AS selectivity FROM tb_user;比较不同前缀长度的选择性，选择一个既能保证较高选择性，又能节省存储空间的前缀长度。
创建前缀索引：
根据选择的前缀长度创建索引：1
CREATE INDEX idx_phone_prefix ON tb_user (phone_number(7));
注意事项
前缀索引的局限性：
- 前缀索引无法用于
ORDER BY和GROUP BY操作，因为前缀索引只存储部分列值，无法保证完整的排序或分组。 - 前缀索引也无法用于覆盖索引，因为它不包含完整的列值。
- 前缀索引无法用于
前缀长度的权衡：
- 前缀长度过短可能导致索引选择性较低，从而影响查询效率。
- 前缀长度过长会增加索引的存储空间，失去前缀索引的优势。
实际应用：
- 对于电话号码列，如果查询通常只匹配前几位（如区号或前缀），前缀索引是一个很好的选择。
- 如果查询需要精确匹配整个电话号码，则应使用完整列索引。
示例
假设有一张用户表tb_user，其中存储了用户的电话号码phone_number，我们可以通过以下步骤选择前缀长度：
- 计算完整列的选择性：
1
SELECT COUNT(DISTINCT phone_number) / COUNT(*) AS selectivity FROM tb_user;
- 计算前7位的选择性：
1
SELECT COUNT(DISTINCT SUBSTRING(phone_number, 1, 7)) / COUNT(*) AS selectivity FROM tb_user;
- 如果前7位的选择性接近完整列，可以选择前7位作为前缀索引：通过这种方式，可以在保证查询效率的同时，减少索引的存储空间。
1
CREATE INDEX idx_phone_prefix ON tb_user (phone_number(7));