常见的MySQL 索引面试题(超有用)

您所在的位置：网站首页 › 大数据文章相关标题有哪些类型 › 常见的MySQL 索引面试题(超有用)

常见的MySQL 索引面试题(超有用)

2024-07-14 22:58| 来源: 网络整理| 查看: 265

在面试中，MySQL 索引是一个常见的话题，因为它们对数据库的性能优化至关重要。以下是几个常见的 MySQL 索引相关的面试题及其解答：

1. 什么是 MySQL 索引？有哪些类型？解答：

索引是一种用于提升数据库查询速度的数据结构。索引本质上是数据表中一列或多列的有序排列，可以加速数据的检索。

常见的 MySQL 索引类型有：

BTREE 索引：默认类型，适用于大多数场景HASH 索引：用于高效的等值查询FULLTEXT 索引：用于全文本搜索SPATIAL 索引：用于地理空间数据BLOB 和 TEXT 索引：适用于二进制大对象和文本字段 2. 创建索引有哪些方式？解答：

在创建表时创建索引：

CREATE TABLE tbl_name ( column1 datatype, column2 datatype, ... INDEX index_name (indexed_column) );

在表创建后添加索引：

CREATE INDEX index_name ON tbl_name (indexed_column);

通过 ALTER TABLE 语句添加索引：

ALTER TABLE tbl_name ADD INDEX index_name (indexed_column); 3. 什么是复合索引？如何使用？解答：

复合索引，也称联合索引，是一个由多个列组合而成的索引。复合索引中各列的顺序非常重要，因为查询优化器会按照复合索引的顺序来使用这些列。

例如，在一个用户表中，创建一个复合索引 (last_name, first_name)：

CREATE INDEX idx_name ON users (last_name, first_name);

只有查询条件符合索引的最左前缀匹配原则时，索引才会被有效利用：

-- 使用索引 SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John'; -- 使用索引，因为符合最左前缀原则 SELECT * FROM users WHERE last_name = 'Smith'; -- 不使用索引，因为不符合最左前缀原则 SELECT * FROM users WHERE first_name = 'John'; 4. 为什么在使用 LIKE ‘%abc%’ 时，索引失效？解答：

在使用 LIKE ‘%abc%’ 时，前面的百分号会导致前导匹配失败，无法使用 BTREE 索引。这是因为 BTREE 索引按照从前到后的顺序进行存储和搜索，查询中的前导百分号会使得索引的有序性无效。

如果需要部分匹配，可以考虑使用 FULLTEXT 索引，专门用于全文本搜索。

5. 如何查看表的索引？解答：

可以使用 SHOW INDEX FROM table_name 命令查看表的索引信息：

SHOW INDEX FROM tbl_name;

该命令会返回索引的详细信息，包括索引名称、列名称、索引类型等。

6. 什么时候不建议使用索引？解答：数据量较小的表：索引的开销可能超过查询带来的收益。频繁增删改操作的表：索引维护是有成本的，频繁的增删改会导致索引频繁重建。含有大量重复值的列：例如性别列（数量级较小），索引效用不大。使用函数或表达式的查询条件：如 WHERE UPPER(column) = 'VALUE'，索引将无效。 7. 什么是覆盖索引？什么时候会使用？解答：

覆盖索引是指一个索引包含了查询所需要的所有列数据，不需要回表查询。例如，有个索引 (columnA, columnB)，查询 SELECT columnA, columnB FROM tbl WHERE columnA = 'value' 不需要回到数据页，只需访问索引页即可返回结果，这种情况称为覆盖索引。

覆盖索引可以显著提高查询性能，因为减少了 I/O 操作。

8. 索引失效的情况有哪些？解答：不遵循最左前缀原则：如复合索引 (col1, col2)，但查询条件只用 col2。使用函数或表达式：如 WHERE UPPER(col1) = 'VALUE'。类型不一致：比如字符串字段没有加引号，与列类型不一致。使用 LIKE 查询时前导有百分号：如 LIKE '%value'。使用 IS NULL 或 !=：尤其是对 BTREE 索引，不支持这些操作。隐式类型转换：如字符串不带引号导致的类型转换。好的，这里是对于你补充的几个问题的详细解答： 9. count(*) 和 count(1) 有什么区别？哪个性能最好？解答： count(*)：count(*) 会计算结果集中的行数，不会忽略 NULL 值。* 代表所有列，这种方式不会解析具体的列。count(1)：count(1) 实际上也是计算结果集中的行数，其中的 1 是一个常数，代表每行都做一次计算，不会涉及任何列。

在 MySQL 中，count(*) 和 count(1) 都表示统计行数，只不过 count(*) 表示计算所有字段，而 count(1) 表示计算值为 1 的列。二者的性能是几乎相同的，因为在 MySQL 的优化器中会对 count(*) 进行优化，使其执行效率接近 count(1)。同时，count(列名)和count(1)在大多数情况下也具有相同性能，差异微乎其微。

出于习惯和标准 SQL 书写的缘故，一般推荐使用 count(*)。它明确表明统计的是行数，并且代码可读性更高。

10. MySQL 单表最好不要超过多少行？解答：

推荐行数：通常建议单表数据行数不要超过 2000 万行。但实际能接受的数值取决于多个因素，如硬件配置（CPU、内存、磁盘）、数据库版本、查询和写入方式等。

考虑因素：

性能：随着行数的增加，查询性能和数据维护的性能可能会降低。存储：单表中大量的数据可能需要大量的磁盘空间，同时索引也会占用大量的存储。管理与运维：大表的备份、恢复和复制等操作会更加复杂且耗时。

为了解决大表带来的性能和管理问题，可以考虑以下策略：

分表：按时间、范围或哈希值将数据拆分到多个表中。分库：将数据分散到多个数据库中。使用分区表：MySQL 支持表分区，可以将一个大表划分为多个较小的分区，提高查询性能。 11. MySQL 为什么采用 B+ 树作索引？解答：

MySQL 选择 B+ 树（B+ Tree）作为索引结构的主要原因包括高效的查找性能、顺序访问能力和磁盘I/O利用率等优点。下面详细说明：

平衡树结构：B+ 树是一种平衡树，所有叶子节点在同一层，保证了查询、插入、删除和更新操作的时间复杂度都是 O(log N)。这使得 B+ 树在面对大量数据时，依然能保持较高的性能。磁盘 I/O 性能：B+ 树的内部节点不存储实际的数据值，只存储索引（键）和子节点指针。实际数据存储在叶子节点上，这使得内部节点能更紧凑，从而减少了树的高度。这样可以减少访问深度，从而减少磁盘 I/O 次数。范围查询高效：B+ 树的所有叶子节点通过链表相连，便于区间范围查询。比方说，进行一些范围操作（如 BETWEEN 查询）时，直接通过链表的连接可以进行顺序扫描，效率较高。支持多种操作：B+ 树不仅支持单值查询，还能高效地支持范围查询、前缀匹配、排序查询等操作，这是由于其叶子节点的有序性和链表连接。

对比其他索引结构，B+ 树在数据库系统中有显著优势：

相比于 B 树：B+ 树的叶子节点形成链表，可以顺序访问和范围查询，而 B 树针对这类操作效率不高。相比于 Hash：Hash 索引更适合等值查询，但不支持范围查询和排序操作，B+ 树则能很好地支持这些操作。相比于平衡二叉树：平衡二叉树在数据量大时，树的高度会更高，导致更多的磁盘 I/O 操作，而 B+ 树能维持较低的高度，效率更高。综上所述，B+ 树的这些特性使得它在MySQL等数据库系统中得到了广泛应用，成为主要的索引结构。

【本文地址】

常见的MySQL 索引面试题(超有用)

常见的MySQL 索引面试题(超有用)

今日新闻

推荐新闻