1. 对于 MySQL 索引的理解 为什么mysql要选择B+树来存储索引
MySQL 选择 B+ 树作为索引的存储结构，主要是为了提高查询效率和减少 I/O 操作。以下是对这个主题的详细分析：
-
I/O 问题：在 MySQL 中，表的真实数据和索引数据都是存储在磁盘中的。在进行数据读写时，I/O 操作的效率直接影响数据库的性能。设计索引时，需要考虑如何提高 I/O 效率，主要有两个方面：减少 I/O 次数和减少 I/O 量。减少 I/O 次数可以通过优化数据访问路径，减少需要读取的块数来实现，而减少 I/O 量则可以通过提高每次读取的数据量来实现。
-
分块读取：当表的数据量非常大时，无法一次性将所有数据都读取到内存中，因此需要采用分治的思想，将数据进行分块读取。设计合理的块大小可以有效提高数据读取效率。例如，采用与页大小（通常为 16KB）相匹配的块大小，可以最大限度地利用内存和磁盘的读取能力。
-
页的概念：数据在磁盘存储时，具有时间局部性和空间局部性。内存与磁盘的数据交互通常以页为单位进行。MySQL 中，页的大小一般为 16KB，可以通过参数进行调整（如
innodb_page_size）。在进行数据读取时，通常会选择页的整数倍进行读取，这种设计可以有效减少 I/O 操作。 -
数据格式设计：在使用索引时，通常根据某个或多个索引列的值来检索整行数据。整体的数据格式可以设计为 K-V 形式，其中 K 为索引列的值，V 为行记录。通过这种方式，可以快速定位所需数据，提高检索效率。
-
减少 I/O 次数：为了定位某一行记录，通常需要知道文件名称、偏移量和数据长度。如果将这些信息作为索引信息，可能需要多次 I/O 操作。因此，最佳方案是在 V 中直接存储行记录，这样可以直接根据 K 值读取行记录。在 InnoDB 存储引擎中，数据文件和索引文件都存储在后缀名为
.ibd的文件中，这种设计可以显著提高数据读取的效率。 -
选择 B+ 树：与其他数据结构相比，B+ 树能够在保证树的高度不变的情况下存储更多数据，尤其在范围查询时表现优异。B+ 树的非叶子节点只存储键值和指针，所有数据存储在叶子节点中，这样可以大幅提高存储效率，降低查询的时间复杂度。
2. 索引的分类
索引可以从不同的角度进行分类，以下是详细的分类方式：
-
数据结构角度：
- B+ 树索引：最常用的索引类型，支持快速查找和范围查询，能够有效地减少 I/O 操作。
- 哈希索引：用于精确查找，速度极快，但不支持范围查询，因此适用场景有限。
- FULLTEXT 索引：用于对文本数据的全文检索，适合处理大文本字段的搜索。
- R-Tree 索引：主要用于 GIS 数据，支持空间数据的查询，适合存储和查询地理信息。
-
物理存储角度：
- 聚簇索引：数据与索引绑定存储，数据的物理顺序与索引的顺序一致。每个表只能有一个聚簇索引。
- 非聚簇索引：索引与数据分开存储，叶子节点存储聚簇索引的列值，一个表可以有多个非聚簇索引。
-
逻辑角度：
- 主键索引：唯一标识记录的索引，确保每条记录的唯一性。
- 普通索引：用于加速查询的索引，不要求唯一性。
- 唯一索引：确保索引列的值唯一，能够防止重复数据的插入。
- 组合索引：基于多个列的索引，适合多条件查询。
3. 聚簇索引与非聚簇索引
在 MySQL 的 InnoDB 存储引擎中，数据在插入时必须与某个索引列绑定存储：
-
聚簇索引：与数据绑定存储，数据的物理顺序与索引的顺序一致。每个表只能有一个聚簇索引，这种设计使得基于主键的查询速度非常快。
-
非聚簇索引：索引与数据分开存储，叶子节点存储的是聚簇索引的值（通常是主键值），这样可以通过非聚簇索引快速定位到聚簇索引，然后再获取完整数据。一个表可以有多个非聚簇索引，这样可以满足不同的查询需求。
4. 相关概念
（1）回表
回表是指使用非聚簇索引时，数据库引擎先根据普通索引找到匹配的行，然后根据叶子节点中存储的聚簇索引值去聚簇索引的索引树中查找整行记录。例如，查询：
SELECT * FROM table WHERE name = 'zhangsan';在上述 SQL 中，首先根据 name 的值在非聚簇索引中找到记录，获取其对应的主键 id，然后再通过 id 查找聚簇索引获取完整行数据。这个过程涉及两次 I/O 操作，因此在生产环境中应尽量避免回表。
（2）索引覆盖
索引覆盖是指一个索引包含查询所需的所有数据，从而无需回表。例如：
SELECT id, name FROM table WHERE name = 'zhangsan';在这个查询中，如果 name 字段有索引且包含了所需的所有字段（在此例中为 id 和 name），则可以直接通过 name 的索引获取结果，而不需要再访问原始数据表，从而提高查询性能。
（3）最左匹配原则
最左匹配原则适用于组合索引，多个列值进行匹配时需遵循从左到右的顺序，否则会导致索引失效。例如：
假设有一张表，(name, age)是组合索引。
1. SELECT * FROM table WHERE name = 'zhangsan' AND age = 10; -- 可以使用索引
2. SELECT * FROM table WHERE name = 'zhangsan'; -- 可以使用索引
3. SELECT * FROM table WHERE age = 10; -- 不能使用索引
4. SELECT * FROM table WHERE age = 10 AND name = 'zhangsan'; -- 可以使用索引在上述查询中，只有第 3 条语句不能使用组合索引，因为它没有遵循最左匹配原则。
（4）索引下推
索引下推（Index Condition Pushdown, ICP）是对 MySQL 使用索引检索行的优化。启用 ICP 后，部分 WHERE 条件可以通过索引直接进行筛选，从而减少 I/O 操作。例如：
SELECT * FROM table WHERE name = 'zhangsan' AND age = 10;如果没有启用 ICP，MySQL 会首先根据 name 查找匹配的行，然后在服务器端再筛选 age。而启用 ICP 后，MySQL 会在存储引擎层面就根据 name 和 age 进行筛选，从而减少了不必要的数据传输和处理。
5. 如何设计性能优良的索引？
设计高效的索引需要考虑多个因素：
- 空间占用小：索引列占用的空间越小越好，尤其是在大数据量的情况下，可以显著提高性能。
- 高离散度：选择离散度高的列作为索引列，离散度的计算公式为
count(distinct(column_name)) / count(*)，该值越大越适合做索引。 - 适用字段：在
WHERE和ORDER BY字段上创建索引，能够加速查询和排序操作。 - 适量索引：索引数量应适中，避免过多索引导致的维护成本增加。
- 避免频繁更新的字段：频繁更新的字段不应创建索引，以减少维护成本。
6. 造成索引失效的情况
索引失效可能由多种因素造成，以下是常见的情况：
- 使用函数或表达式：在索引列上使用函数（如
UPPER(),LOWER()）会导致索引失效。 - 数据类型不匹配：查询条件的数据类型与索引字段的类型不匹配会导致索引失效。
- LIKE 条件前面带
%：如果使用LIKE查询时前面带有%，则无法使用索引。 - 不满足最左匹配原则：在组合索引中，如果查询条件不符合最左匹配原则，索引将失效。
- 使用
OR关键字：在查询中使用OR可能导致索引失效，尤其是在涉及多个索引时。
7. 自增主键的优势
使用自增主键的主要优势在于：
- 避免页分裂：自增主键在插入数据时以追加的形式进行存储，避免了页分裂的问题。
- 高效的写入性能：在本页索引写满之后，仅需申请一个新页继续写入即可，这样可以降低写入成本。
相比之下，使用业务字段作为主键时，数据插入的顺序可能不一致，导致频繁的页分裂和数据移动，从而增加了写入成本。
8. 如何查看 SQL 语句是否使用索引
通过执行计划可以判断查询中是否用到了索引，以便进行 SQL 优化。EXPLAIN 语句提供了 MySQL 如何执行语句的信息，支持 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。
| 列名 | 含义 |
|---|---|
id | 查询的序列号 |
select_type | 查询类型 |
table | 访问的表名 |
type | 访问类型 |
possible_keys | 可能使用的索引 |
key | 实际使用的索引 |
key_len | 使用的索引长度 |
ref | 用于查找索引列的列或常量 |
rows | 估算需要读取的行数 |
filtered | 预估的过滤百分比 |
Extra | 查询的额外信息 |
id
id 列表示查询的序列号，包含一组数字，表示查询中执行 select 子句或者操作表的顺序。
- 情况 1：如果
id相同，则执行顺序从上到下。
EXPLAIN SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno JOIN salgrade sg ON e.sal BETWEEN sg.losal AND sg.hisal;- 情况 2：如果
id不同，且是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
EXPLAIN SELECT * FROM emp WHERE ename NOT IN (SELECT ename FROM emp WHERE ename LIKE '%S%');- 情况 3：
id相同和不同的同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行。
EXPLAIN SELECT dept.*, person_num, avg_sal FROM dept, (SELECT COUNT(*) person_num, AVG(sal) avg_sal, deptno FROM emp GROUP BY deptno) t WHERE dept.deptno = t.deptno;select_type
select_type 列主要用来分辨查询的类型，是普通查询、联合查询还是子查询。
select_type Value | JSON Name | Meaning |
|---|---|---|
SIMPLE | None | Simple SELECT (not using UNION or subqueries) |
PRIMARY | None | Outermost SELECT |
| UNION | None | Second or later SELECT statement in a UNION |
DEPENDENT UNION | dependent (true) | Second or later SELECT statement in a UNION, dependent on outer query |
UNION RESULT | union_result | Result of a UNION. |
| SUBQUERY | None | First SELECT in subquery |
DEPENDENT SUBQUERY | dependent (true) | First SELECT in subquery, dependent on outer query |
DERIVED | None | Derived table |
DEPENDENT DERIVED | dependent (true) | Derived table dependent on another table |
MATERIALIZED | materialized_from_subquery | Materialized subquery |
UNCACHEABLE SUBQUERY | cacheable (false) | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
UNCACHEABLE UNION | cacheable (false) | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
-- SIMPLE: 简单的查询，不包含子查询和 UNION
EXPLAIN SELECT * FROM emp;
-- PRIMARY: 查询中最外层的查询，如果查询中有子查询，则最外层的查询被标记为 PRIMARY
EXPLAIN SELECT * FROM emp WHERE ename NOT IN (SELECT ename FROM emp WHERE ename LIKE '%S%');
-- UNION: 若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION
EXPLAIN SELECT * FROM emp WHERE deptno = 10 UNION SELECT * FROM emp WHERE sal > 2000;
-- DEPENDENT UNION: 跟 UNION 类似，此处的 DEPENDENT 表示 UNION 或 UNION ALL 联合而成的结果会受外部表影响
EXPLAIN SELECT * FROM emp e WHERE e.empno IN (SELECT empno FROM emp WHERE deptno = 10 UNION SELECT empno FROM emp WHERE sal > 2000);
-- UNION RESULT: 表示一个 UNION 的结果集作为一个单独的表返回，这通常发生在 UNION 操作之后，并且可能跟其他表进行 JOIN 操作
EXPLAIN SELECT * FROM emp WHERE deptno = 10 UNION SELECT * FROM emp WHERE sal > 2000;
-- SUBQUERY: 在查询中作为另一个查询的子查询的查询，例如，在 `SELECT ... WHERE column IN (SELECT ...)` 结构中的子查询。
EXPLAIN SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);
-- DEPENDENT SUBQUERY: 与 SUBQUERY 类似，但是这个查询依赖于外部查询的某些部分。
EXPLAIN SELECT e.empno, e.ename, e.sal FROM emp e WHERE e.sal < (SELECT e2.sal FROM emp e2 WHERE e2.empno = e.mgr);
-- DERIVED: 出现在 FROM 子句中的子查询，MySQL 会为这个子查询生成一个临时表。这个值表示该查询是为派生表生成的。
EXPLAIN SELECT t.job FROM (SELECT MIN(sal) min_sal, job FROM emp GROUP BY job) t WHERE t.min_sal > 2500;
-- DEPENDENT DERIVED: 与 DERIVED 类似，但是这个查询依赖于外部查询的某些部分：未找到案例
-- MATERIALIZED: 表示该子查询的结果被物化（即存储在临时表中），以供稍后的 JOIN 使用，这种类型的子查询在执行时比常规子查询要慢。
EXPLAIN SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM (SELECT MIN(sal) min_sal, deptno FROM emp GROUP BY deptno) a WHERE min_sal < '2000');
-- UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，因此每次都会重新计算：未找到案例
-- UNCACHEABLE UNION: 一个 UNION 的结果不能被缓存，因此每次都会重新计算：未找到案例table
table 列对应行正在访问的表，表名或者别名，可能是临时表或者 UNION 合并结果集。
- 具体的表名：如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名。
- 衍生表：表名是
derivedN的形式，表示使用了id为N的查询产生的衍生表。 - UNION 结果：当有 UNION result 的时候，表名是
union n1,n2等的形式，n1,n2 表示参与 UNION 的 id。
type
type 显示的是访问类型，表示以何种方式去访问数据。访问类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般情况下，得保证查询至少达到 range 级别，最好能达到 ref。
-- ALL: 全表扫描，一般情况下出现这样的 SQL 语句而且数据量比较大的话那么就需要进行优化。
EXPLAIN SELECT * FROM emp;
-- INDEX: 全索引扫描，这比 ALL 的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序。
EXPLAIN SELECT empno FROM emp;
-- RANGE: 表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了 INDEX 的全索引扫描，适用的操作符： `=`, `<>`, `>`, `>=`, `<`, `<=`, `IS NULL`, `BETWEEN`, `LIKE`, or `IN()`.
EXPLAIN SELECT * FROM emp WHERE empno BETWEEN 7000 AND 7500;
-- INDEX_SUBQUERY: 跟 UNIQUE_SUBQUERY 类型，使用的是辅助索引。
SET optimizer_switch='materialization=off';
EXPLAIN SELECT * FROM emp WHERE ename NOT IN (SELECT dname FROM dept WHERE dname LIKE '%SALES');
SET optimizer_switch='materialization=on';
-- UNIQUE_SUBQUERY: 子查询的结果由聚簇索引或者唯一索引覆盖，dept 表的 deptno 字段有主键。
SET optimizer_switch='materialization=off';
EXPLAIN SELECT * FROM emp WHERE deptno NOT IN (SELECT deptno FROM dept WHERE deptno > 20);
SET optimizer_switch='materialization=on';
-- INDEX_MERGE: 索引合并，在 WHERE 条件中使用不同的索引字段，ename 和 deptno 都创建索引。
EXPLAIN SELECT * FROM emp WHERE ename = 'SMITH' OR deptno = 10;
-- REF_OR_NULL: 跟 REF 类似，在 REF 的查询基础上，加一个 NULL 值的条件查询。
EXPLAIN SELECT * FROM emp WHERE ename = 'SMITH' OR ename IS NULL;
-- REF: 使用了非聚集索引进行数据的查找。
ALTER TABLE emp ADD INDEX idx_name(ename);
EXPLAIN SELECT * FROM emp WHERE ename = 'SMITH';
-- EQ_REF: 使用唯一性索引进行数据查找。
EXPLAIN SELECT * FROM emp e, emp e2 WHERE e.empno = e2.empno;
-- CONST: 这个表至多有一个匹配行。
EXPLAIN SELECT * FROM emp WHERE empno = 7369;
-- SYSTEM: 表只有一行记录（等于系统表），这是 CONST 类型的特例，平时不会出现。possible_keys
possible_keys 显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
EXPLAIN SELECT * FROM emp WHERE ename = 'SIMTH' AND deptno = 10;key
key 列表示实际使用的索引，如果为 NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠。
EXPLAIN SELECT * FROM emp WHERE ename = 'SIMTH' AND deptno = 10;key_len
key_len 表示索引中使用的字节数，可以通过 key_len 计算查询中使用的索引长度。在不损失精度的情况下，索引长度越短越好。
EXPLAIN SELECT * FROM emp WHERE ename = 'SIMTH' AND deptno = 10;ref
ref 列显示了哪些列或常量被用于查找索引列，这对于非唯一索引查找有效。
EXPLAIN SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.deptno = 10;rows
rows 列根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数。此参数很重要，直接反映 SQL 找了多少数据，在完成目的的情况下越少越好。
EXPLAIN SELECT * FROM emp;filtered
filtered 表示返回行的预估百分比，它显示了哪些行被过滤掉了，最大的值为 100，这意味着没有对行进行筛选。从 100 开始递减的值表示过滤量在增加，rows 表示预估的行数，rows * filtered 表示与下表连接的行数。
extra
extra 列提供查询的额外信息，能够帮助开发者理解查询的执行过程。
-- using filesort: 说明 MySQL 无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置。
EXPLAIN SELECT * FROM emp ORDER BY sal;
-- using temporary: 建立临时表来保存中间结果，查询完成之后把临时表删除。
EXPLAIN SELECT ename, COUNT(*) FROM emp WHERE deptno = 10 GROUP BY ename;
-- using index: 这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现 using where，表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找。
EXPLAIN SELECT deptno, COUNT(*) FROM emp GROUP BY deptno LIMIT 10;
-- using where: 通常是进行全表或者全索引扫描后再用 where 子句完成结果过滤，需要添加索引。
EXPLAIN SELECT * FROM emp WHERE job = 'SMITH';
-- using join buffer: 使用连接缓存。
EXPLAIN SELECT * FROM t3 JOIN t2 ON t3.c1 = t2.c1;
-- impossible where: where 语句的结果总是 false。
EXPLAIN SELECT * FROM emp WHERE 1 = 0;
扫一扫
1910
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
