Mysql之面试题之索引

最新推荐文章于 2024-11-08 22:59:30 发布

苏汀star

最新推荐文章于 2024-11-08 22:59:30 发布

阅读量1.3k

点赞数 17

分类专栏： MySQL 数据库文章标签： mysql 数据库 java 面试索引 B+Tree

本文链接：https://blog.csdn.net/sutingstart/article/details/136339011

版权

数据库同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

MySQL

2 篇文章 0 订阅

订阅专栏

前言

提示：这里可以添加本文要记录的大概内容：
写这个MySQL的人也是牛逼，这就是一个Excel表格由之前的直接可视化界面操作被改成通过命令操作。

简而言之就是：在数据库系统中，我们考虑的主要构成包括库(数据库)、表、行(记录)、索引这四类对象。每类对象上都可以进行增加、删除、修改、查询等操作。然而，数据库系统设计和优化的重点通常集中在索引的管理、事务的处理以及锁的控制上。这些机制确保了数据库在执行操作时，不仅遵循ACID原则，即原子性、一致性、隔离性、持久性，也保证了数据处理的高效率。在日常应用中，这意味着要把页面中的数据变化高效且安全地同步到数据库

一、索引

简述：一个空间和时间博弈的结果：其实这里有句话：
哪有什么岁月静好，只不过是有人在替你负重前行：
1、想用索引，先得有索引，有索引就得建索引并时实更新索引。
增删改数据的这个过程中就在更新索，这样其实就降低了增删改数据的性能，进而提升了查询的效率：
所以当表的查询相对较少，增删改相对较多时候，索引不建议多建。更具业务情况来
一般公司建立索引为10个：建议使用联合索引

作用：主要作用（重要）

提升查询性能：这是索引最基本也是最重要的作用，通过将数据索引化来避免全表扫描，从而快速定位到所需的数据。
减少回表操作：在拥有覆盖索引的情况下，查询可以直接在索引中获取所需的所有列的值，无需访问数据表本身，这降低了磁盘I/O操作和查询成本。
提升排序效率：索引本质上是排序的数据结构，当查询包含ORDER BY子句时，索引可以极大加快排序的速度。
辅助锁机制：尤其是在行级锁定中，索引可以帮助数据库更精确地锁定需要修改的记录，避免不必要的锁定扩展，从而提高并发性能。

作用：更细分了没啥用，想看看看（不重要）

提高查询效率：索引最主要的功能是加速查询操作，类似于书籍的目录，可以快速找到数据的位置而不必全表扫描。
增强排序和分组速度：当对数据进行排序（ORDER BY）或分组（GROUP BY）操作时，索引可以显著减少数据排序和分组的时间，因为索引中的数据已经是有序的。
加速表连接：在使用JOIN语句连接多个表时，对参与连接的列创建索引可以提高连接的效率。
实现数据的唯一性约束：通过在表上创建唯一性索引（UNIQUE INDEX），可以保证列中的数据不出现重复值，从而维护数据的唯一性。
优化条件过滤：索引可以帮助数据库更快地筛选满足特定条件的记录，尤其当这些条件针对索引列时。
辅助计算：在一些数据库引擎中，如InnoDB，索引可以用来直接完成一些COUNT()、MIN()和MAX()等聚合函数的快速计算，因为索引存储的是有序数据。
减少I/O成本：读取索引通常比读取整个表消耗的I/O要少，特别是在索引能够被覆盖的查询中，数据库可以只读取索引而避免读取数据行，减少了磁盘I/O操作。
改善锁性能：对于支持行级锁的存储引擎（如 InnoDB），索引可以减少锁定需要的行数目，从而提高并发访问性能。

简言之，索引是MySQL提高数据库性能和维护数据完整性的重要工具。不过，也要注意索引不是万能的，不恰当的索引会额外增加更新、删除和插入操作的开销，占用更多存储空间，并且可能对数据库性能产生负面影响。因此，如何根据应用场景合理设计和使用索引是数据库优化的重要方面。

索引分类：有兴趣就看看没兴趣就直接跳过（不重要）

提示：索引可以按照不同的维度被分类，包括按照数据结构、按照功能等来进行分类

功能分类

1、主键索引（PRIMARY KEY）：

用于唯一标识表中的每一行，并且不允许NULL值。
每个表只能有一个主键索引。
InnoDB存储引擎中的主键索引也是聚簇索引，数据实际存储在主键索引的叶子节点上。
使用场景：用于快速查找或排序基于主键的记录，外键关联等。

2、唯一索引（UNIQUE INDEX）：

确保某列（或多列组合）的值唯一。
允许有空值，但空值只能有一个。
使用场景：主要用于实施业务层面的数据唯一性，如用户邮箱、身份证号码等。

3、普通索引（INDEX）：

基础的索引类型，没有任何约束。
使用场景：加快对数据的检索速度，适用于常作为查询条件的列。

4、全文索引（FULLTEXT INDEX）：

专门用于全文搜索，支持非精确匹配上的文本搜索，如MATCH AGAINST查询。
在InnoDB和MyISAM存储引擎上可用。
使用场景：常用于文本数据的搜索引擎功能，如搜索文章、评论等。

5、空间索引（SPATIAL INDEX）：

用于地理数据存储，基于R-Tree数据结构。
仅MyISAM存储引擎支持。
使用场景：使用地理数据类型的列，如GIS地理位置查询。

7、索引前缀（PREFIX INDEX）：

对文本类型列只索引开始的部分字符。
使用场景：适用于长文本字段，能够节省索引空间，提高索引效率。

8、复合索引（COMPOSITE INDEX）：

基于多个列构建的索引，可以覆盖多个列。
MySQL在查询时可以利用组合索引的最左前缀规则。
使用场景：适合跨多个列进行查询的场景。

9、单一索引（SINGLE-COLUMN INDEX）：

指基于单一列构建的索引。
使用场景：适用于只涉及单一列的查询优化。

特殊说明：

Clustered与Nonclustered：这是索引的另一种分类方式。聚簇索引（如InnoDB的主键索引）中数据记录就存放在索引结构中，而非聚簇索引（如主键索引以外的其他索引）则不是。
锁定策略：某些索引如通过主键或唯一索引的搜索，可能对性能有更好的优化，尤其是在并发事务多的场景中，主键查找通常拥有更优的锁定性能。
选择合适的索引列：创建索引时需要考虑查询模式，选择性高的列（即列中不重复值多的列）作为索引通常更有效。

总而言之，正确使用索引可以大幅提升数据库性能。但是，索引不是免费的午餐，它们在写操作时会带来额外的开销，因为索引本身也需要更新。因此，设计索引应该根据应用的查询模式，避免过度索引，使得维护成本和性能损耗最小化。

结构分类（不重要）

1、B+Tree索引：（重要）

描述：

B+Tree索引是最常见的索引类型，它是MySQL中默认的索引类型，用于InnoDB和MyISAM等存储引擎。

使用场景：

等值查询：非常适合查找单个值。
范围查询：由于B+树的有序性，可以快速定位范围的起始位置。
排序与分组：对于ORDER BY和GROUP BY操作，B+树可以利用索引顺序快速完成。
联合索引的最左前缀优化：对于复合索引，可以根据索引列的左边起第一个字段进行查询优化。

特殊说明：

B+Tree索引更适用于需要频繁进行读操作的场景。
由于其排序特性，B+Tree索引在处理ORDER BY和GROUP BY子句时特别有效。

2、HASH索引：

描述：

HASH索引基于哈希表实现，只能满足“等值查询”的快速查找，不支持范围查询。

使用场景：

适用于快速查找的场景，尤其是对于精确匹配查询。
在内存数据库（如MEMORY表）中使用效果更佳。

特殊说明：

HASH索引用于精确查找，不适用于查找范围内的记录。
MYSQL的HASH索引是适应性的，当冲突增多时倾向于通过链表方式解决。

3、全文（FULLTEXT）索引：

描述：

用于全文检索的特殊类型的索引，在MyISAM和InnoDB存储引擎上可用。

使用场景：

适用于文本数据的搜索，可以找到包含某些词汇的所有记录。
常用于搜索引擎算法，如MATCH AGAINST。

特殊说明：

全文索引适合对大量文本进行模糊搜索。
它可以使用自然语言处理进行检索。

4、R-Tree（空间）索引：

描述：

基于R-Tree数据结构的索引，用于索引空间数据类型（如GIS数据）。

使用场景：

用于地理空间数据的存储和查询，如查询地图上某个区域内的所有POI（兴趣点）

特殊说明：

R-Tree索引不像B+Tree那样广泛使用。
在MySQL中，只有MyISAM存储引擎支持空间索引。

5、组合索引（复合索引）：

描述：

按照一个或多个字段进行组合的索引，遵守最左前缀原则。

使用场景：

当查询涉及到多个列时使用。
对经常一起使用的列创建单一组合索引，而不是为每一列单独创建索引。

特殊说明：

需要细心设计，以避免因为组合索引中列的顺序而导致索引无效化。

这些索引类型的适用场景和性能特征凸显了如何根据应用的工作负载和查询模式来选择和优化索引。
正确理解和使用这些不同类型的索引对于数据库性能调优和管理至关重要。
在绝大多数情况下，B+Tree索引是默认选择，但对于特定的场景，比如全文搜索或空间数据，其他类型的索引可能更适合。

其中： B-Tree索引中 B+Tree索引是重点：（重要）

B-Tree索引：

B-Tree是一种平衡树数据结构，它允许数据在多个节点之间被存储。在B-Tree中：

所有叶子节点都在同一层。
每个节点包含数据，以及指向子节点的指针。
节点中的数据从左到右是有序的。

在B-Tree结构中，数据可以在内部节点和叶子节点中被找到。

B+Tree索引：

B+Tree是B-Tree的扩展，也是MySQL中InnoDB存储引擎使用的索引数据结构。它具有以下特性：

所有的叶子节点彼此链接，通常是双向链接，这对范围查询非常有用。
所有的叶子节点都在同一层，并且包括数据记录的指针。
内部节点（非叶子节点）不存储数据，只存储其子节点中的最大（或最小）值。

在B+Tree结构中，数据仅在叶子节点中被找到。这意味着对于范围查询，数据库引擎只需要遍历叶子节点即可，因此更加高效。

相比之下，B+Tree有以下优点：

由于所有数据入口都在叶子节点并且彼此链接，这对于全表扫描更有效，而且扫描的IO效率更高。
B+Tree索引结构更加紧凑，内部节点不保存数据，因此可以有更多的键值。
由于查询不会在非叶子节点结束，所以查询性能稳定。

在MySQL中，当我们提到B-Tree索引的时候，实际上指的是B+Tree索引。InnoDB和MyISAM这两种常用的存储引擎都使用B+Tree作为索引结构，而MyISAM也支持R-Tree索引，用于空间数据索引。需要注意的是Hash索引和Full-text索引也有自己的特定数据结构。

在更一般的数据库理论和实现上，B树和B+树都有广泛的应用，但在讨论MySQL时通常指的是B+树索引。

二、实际使用：增删改查索引（重要）

增 - 创建索引：

-- 创建主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY (`column_name`);

-- 创建唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column_name`);

-- 创建普通索引
ALTER TABLE `table_name` ADD INDEX `index_name` (`column_name`);

-- 创建复合索引
ALTER TABLE `table_name` ADD INDEX `index_name` (`column1`, `column2`);

-- 创建全文索引（只适用于支持全文搜索的存储引擎，如MyISAM和InnoDB）
ALTER TABLE `table_name` ADD FULLTEXT (`column_name`);

-- 在创建表的同时指定索引
CREATE TABLE `table_name` (
  `column1` INT NOT NULL,
  `column2` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`column1`),
  INDEX `index_name` (`column2`)
);

删 - 删除索引：

-- 删除主键索引
ALTER TABLE `table_name` DROP PRIMARY KEY;

-- 删除唯一索引
ALTER TABLE `table_name` DROP INDEX `index_name`;

-- 删除普通索引
ALTER TABLE `table_name` DROP INDEX `index_name`;

修改索引：

通常，修改索引会涉及到删除原有的索引然后重新创建，因为MySQL不支持直接“修改”一个索引。如果需要修改索引，就是以下这种重建流程：

-- 删除后重新创建索引
ALTER TABLE `table_name` DROP INDEX `index_name`, ADD INDEX `index_name` (`column_name`);

查询索引：

-- 查看表的所有索引
SHOW INDEX FROM `table_name`;

-- 查看索引的CARDINALITY等统计信息
ANALYZE TABLE `table_name`;

这些都是非常基础的索引操作语句。
实际应用中可能还会涉及更多复杂的操作，比如考虑对索引创建的性能影响，可能会选择在非高峰时段进行索引的创建、删除或重建等。
此外，还有在线操作（ONLINE/OFFLINE）的问题，在一些场景中，创建和删除索引可能需要在不中断服务的情况下在线完成。
这些高级应用通常涉及到MySQL特定版本的特殊语法，并可能需要结合使用其他管理工具来实现。

三、分析索引合理性-explain:（很重要）

在MySQL中，EXPLAIN或EXPLAIN EXTENDED语句可以用来分析SELECT、DELETE、INSERT、REPLACE和UPDATE语句。
这是一个非常有力的工具，用于优化查询性能，因为它可以展示MySQL如何执行这些操作，包括使用哪些索引、连接类型等。
但是重点使用的是查询：

使用 EXPLAIN 使用：

EXPLAIN SELECT * FROM table_name WHERE column_name='value';

执行上述语句后，你会得到一系列字段，其含义如下：

EXPLAIN属性描述

1、id:
查询的标识符，如果是单个SELECT查询，则是1。对于复杂查询，如子查询、JOIN等，MySQL会为每个SELECT分配一个唯一的ID。

2、select_type: 查询的类型：

SIMPLE: 简单的select查询，不包含子查询或UNION。
PRIMARY: 查询中最外层的select。
SUBQUERY: 子查询中的第一个select。
DERIVED: 来自于子查询的表的结果，存储在临时表中。
UNION: UNION中第二个或之后的SELECT语句。
UNION RESULT: UNION的结果。

3、able: 显示这一行的数据是关于哪个表的。

4、partitions: 匹配的分区信息。

5、type: 连接类型（从最好到最坏）：

system: 表只有一行记录（等同于系统表）。
const: 表示通过索引一次就找到了。
eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。
ref: 非唯一性索引扫描。
range: 只检索给定范围的行，使用一个索引来选择行。
index: 全索引扫描。
ALL: 全表扫描。

6、possible_keys: 显示可能应用在这张表上的索引。

7、key: 实际使用的索引。

8、key_len: 使用的索引的长度。

9、ref: 显示索引的哪一列被使用了，如果可能的话，是一个常数。

10、rows: 根据表统计信息和索引选用，估计要查找的行数。

11、filtered: 表示返回结果的行数占需读行数的百分比的估计值。

12、Extra:（重点）
包含不适合在其他列中显示但对执行计划非常重要的额外信息。如：

需要注意的属性（可能需要优化）:

Using filesort: MySQL在返回结果前需要对结果集进行排序，这个过程并不使用索引，而是在内存或磁盘上完成。这能够显著降低查询的性能，特别是在处理大数据量时。

Using temporary: 指示MySQL会创建一个临时表来处理查询，常见于排序和选择不同的列进行GROUP BY操作。这种情况同样会影响性能。

Using where: 这本身并不是问题，但如果与"Using filesort"或"Using temporary"结合出现，可能表明WHERE子句在索引之后应用，而不是作为索引的一部分来过滤行。

Using join buffer: 这表示JOIN操作没有使用索引，而是使用了缓冲区。这通常会降低JOIN操作的性能。

Using index condition: MySQL在索引上应用了WHERE子句中的条件，这是"索引条件推送"（Index Condition Pushdown，ICP）的一个优化。

Impossible WHERE noticed after WHERE: WHERE子句的条件永远不会为真，导致不会有任何行返回。出现这种情况时，查询需要重新审查。

Range checked for each record (index map: N): 对于JOIN的每一行，MySQL都做了索引范围检查。这可能有效，但也可能是一个没有正确使用索引的提示。

一般不需优化的属性:

Using index: 这通常是个好消息，表示MySQL只需通过索引就能获取查询所需的信息，无需读取数据行，这是最高效的访问方式之一。

Using where with pushed condition: 表示WHERE条件已经被推送到了存储引擎级别，这通常是存储引擎优化的结果，例如NDBCLUSTER存储引擎。

Select tables optimized away: 在对某些类型的查询进行了优化，例如聚合函数（MIN()、MAX()）可以直接使用索引解决时，没有必要访问表中的任何行。

Using index for group-by: 与"Using index"类似，表示MySQL可以通过索引来完成GROUP BY操作，而不需要额外的排序或临时表。

Distinct: 一旦MySQL找到了第一个DISTINCT值，它就会停止查找同样的值，这常常提高查询的效率。

请注意，即使某些Extra属性看起来指示查询已经很优化了，但性能还是可能由于数据分布、表结构、索引选择或其他因素而不理想。性能优化是一个详细的过程，需要对具体情况进行全面分析。

同上：忽略就好

Using index: 指明MySQL将使用覆盖索引，以避免访问表的数据行，索引条目包含了查询中需要的全部数据。
Using where: 使用了WHERE条件来过滤结果。
Using temporary: MySQL在查询中使用了临时表，常见于排序和GROUP BY操作。 - 需要优化
Using filesort: MySQL将进行一个外部文件排序来优化查询，这通常发生在需要排序的记录超出内存限制时。- 需要优化：临时索引
Using join buffer: JOIN操作使用了缓冲区。
Impossible WHERE noticed after WHERE: WHERE子句的条件永远不会为true。
Select tables optimized away: 在无需访问表的情况下完成了优化（通常与索引合并或最小化聚合函数相关）。
Distinct: MySQL发现了DISTINCT值，停止搜索。
Using index for group-by: GROUP BY操作利用索引进行优化。

EXPLAIN优化（很重要）

在使用 EXPLAIN 分析查询并优化它时，以下是你需要特别关注的几个关键字段，我会特别着重描述 Extra 字段：

type: 这是最重要的字段之一，它描述了表的访问类型（如何查找行）。最佳的是 system 和 const 类型，其他较好的类型包括 eq_ref 和 ref。range, index 和 ALL 类型通常表明查询需要优化，尤其是 ALL 类型（即全表扫描），通常是性能问题的标志。
possible_keys 和 key: 这两个字段提供了可能与实施查询中使用的索引有关的信息。possible_keys 显示哪些索引可能被查询使用，而 key 则是实际使用的索引。如果 key 为空，或者选用了非最优索引，那么可以考虑添加或修改索引以优化查询。
rows: 这个字段提供了一种估计，指出为了满足查询条件，MySQL认为必须检查多少行。一个很高的数值通常意味着查询效率低下。
filtered: 这个百分比值表示行被条件过滤的估计比例。如果这个值相对较低，可能意味着MySQL必须检查很多行才能找到结果集，这可能是个效率低的信号。
Extra: 此字段包含MySQL解释执行计划的补充信息，对于优化来说非常重要。下面是一些可能需要优化的 Extra 属性及其含义：

Using filesort: MySQL需要进行一个额外的排序步骤来找出怎样来获取记录。尽管叫filesort，却不一定会使用到文件系统。这个通常发生在 ORDER BY
和 GROUP BY 子句中。如果出现这个情况，则可能需要优化这些子句或相应的索引。

Using temporary: 查询执行过程中创建了临时表，通常在执行 GROUP BY 或 ORDER BY 操作时发生。如果频繁出现临时表，可能需要调整查询结构或索引策略。

Using index: 这个属性实际是一个好消息，意味着查询能够通过使用索引来检索数据而无需读取数据行本身。这是因为索引已经包含了查询所需要的数据，被称为“覆盖索引”。

Using where: 显示MySQL在检索行后使用了WHERE子句来过滤结果。这并不一定坏，但如果可以通过索引直接滤除行，可能会更好。

Using join buffer: JOIN操作使用了缓冲区。可能在某些情况下，可以通过调整数据库的join_buffer_size参数或改进查询来减少其使用。

这些一般是EXPLAIN中的关键点，对于优化来说非常重要。当然，优化操作通常需要结合这些信息和实际查询内容来综合判断和处理。