面试题010-数据库-MySQL(MySQL+索引)

面试题010-数据库-MySQL(MySQL+索引)

题目自测

• 1. MySQL是什么？有什么优点？
• 2. 什么是SQL注入？如何解决SQL注入？
• 3. MyISAM 和 InnoDB 有什么区别？
• 4. SQL在MySQL数据库中的执行过程？
• 5. 什么是索引？如何使用索引提高查询性能？
• 6. 聚簇索引和非聚簇索引的区别？
• 7. 索引底层的数据结构了解么？
• 8. B+树做索引比红黑树好在哪里？
• 9. 最左前缀匹配原则了解么？
• 10. 什么是覆盖索引？
• 11. 如何查看某条SQL语句是否用到了索引？

题目答案

1. MySQL是什么？有什么优点？

答：MySQL是一款开源的关系型数据库管理系统，它以结构化查询语言来管理和操作关系数据库。主要用于持久化存储我们系统中的数据。如用户信息、订单列表等信息。

• 开源免费。
• 成熟稳定、功能完善。能够高效的处理数据，支持高并发。
• 兼容性好，支持常见的操作系统和多种开发语言。
• 支持事务处理，可以确保数据的可靠性和完整性。
• 社区活跃，提供了丰富的文档、教程和资源支持。

2. 什么是SQL注入？如何解决SQL注入？

答：SQL注入是一种安全漏洞，攻击者可以利用这个漏洞，通过应用程序中的输入字段插入恶意的SQL代码，来控制或操作数据库。
SQL注入的例子：用户通过用户名和密码进行登录

# 用户名：' OR '1'='1
# 密码：' OR '1'='1
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1'

由于’1’='1’永远为真，这个查询将总是返回数据库中的所有用户，攻击者可以绕过身份验证成功登录。

解决SQL注入的办法：

• 对用户的输入进行验证和过滤：可以使用正则表达式检查输入数据的合法性。
• 使用预处理语句：预处理语句将SQL查询和用户输入分开处理。
• 使用ORM框架来自动生成SQL语句。
  • 在MyBatis中可以通过#{}进行参数绑定，避免使用${}进行字符串替换。

3. MyISAM 和 InnoDB 有什么区别？

答：MyISAM和InnoDB都是MySQL中常用的储存引擎。MySQL5.5之前MyISAM是默认的存储引擎、之后的版本中InnoDB是默认的存储引擎。两者主要在事物支持、锁机制、外键约束、索引类型、存储结构、崩溃恢复、适用场景等方面存在区别。

• 事务支持：
  • MyISAM：不支持事务处理。
  • InnoDB：支持事务处理，具有ACID特性，具有提交和回滚事务的能力。
• 锁机制：
  • MyISAM：只有表级锁，在执行写操作时，整张表都会被锁定。
  • InnoDB：支持行级锁和表级锁，默认使用行级锁。
• 外键约束：
  • MyISAM：不支持外键约束。
  • InnoDB：支持外键约束。外键可以用来维护数据的一致性，但不建议使用外键，外键概念应在应用层解决。
• 索引类型：
  • MyISAM：使用非聚簇索引，索引和数据分开存储，索引文件以.MYI为扩展名。
  • InnoDB：使用聚簇索引，数据和索引一起存储在表空间中，通常扩展名为.ibd。
• 存储结构：
  • MyISAM：为每个表在磁盘上存储三个文件，.frm存储表定义文件、.MYD存储表数据文件、.MYI存储索引文件。
  • InnoDB：存储数据和索引在一个或多个.idb文件中。
• 崩溃恢复：
  • MyISAM：不提供崩溃恢复机制。
  • InnoDB：提供崩溃恢复机制，通过重做日志(redo log)来恢复数据。
• 适用场景：
  • MyISAM：适用于读密集型应用，如只读或很少写入的数据仓库、报表和分析系统等。
  • InnoDB：适用于需要高并发写入和事务处理的应用。例如在线交易处理系统等。

4. SQL在MySQL数据库中的执行过程？

答：SQL的执行过程涉及了解析、优化、执行和结果返回等多个步骤，如下图所示

• 客户端请求：客户端通过MySQL客户端工具或应用程序向MySQL服务器发送SQL语句。
• 连接管理：MySQL的连接器处理客户的请求，进行身份和权限验证。
• 查询缓存(8.0后被移除)：执行查询语句的时候，会先查询缓存，如果命中缓存则直接返回结果。。
• 解析：对SQL语句进行词法分析和语法分析。
  • 词法分析：将SQL语句拆分成最小的语法单元，如关键词、表名、字段名等。
  • 语法分析：根据SQL的语法规则检查SQL语句是正确，并生成解析树。
• 优化：对解析数进行优化，生成高效的执行计划。如重写SQL、选择索引、选择连接类型等。
• 执行：根据生成的执行计划，访问对应的存储引擎，执行SQL语句。
• 返回结果：将执行器生成的结果返回给客户端。

5. 什么是索引？如何使用索引提高查询性能？

答：索引是一种数据结构，用于帮助数据库管理系统快速查询和检索表中的记录。索引类似于书籍的目录，可以加快数据检索的过程。MySQL支持多种类型的索引，如B树索引、哈希索引、全文索引等。
使用索引提高查询性能的方法：

• 选择合适的列进行索引：在经常用于查询条件的列上创建索引，主要是WHERE子句和JOIN条件上的列。
• 选择合适的索引类型：根据查询的需求选择合适的索引类型，如查询涉及范围则B-Tree索引更为合适，如果总是固定的值则选择哈希索引更为合适。
• 避免对选择性的列创建索引：对低选择性的列（如性别、布尔值），列的差异性小，创建索引可能效果不佳。
• 定期维护索引：定期使用ANALYZE TABLE和OPTIMIZE TABLE命令保持统计信息的最新状态

  -- 创建名为idx_column_name的索引在table_name的column_name列上
  CREATE INDEX idx_column_name ON table_name (column_name);
  CREATE INDEX idx_salary ON employees(salary);
  
  
  -- 删除名为idx_column_name的索引
  DROP INDEX idx_column_name ON table_name;
  DROP INDEX idx_salary ON employees;
  

6. 聚簇索引和非聚簇索引的区别？

答：聚簇索引和非聚簇索引是数据库两种主要的索引类型。

• 聚簇索引(聚集索引)：索引结构和数据一起存放的索引。InnoDB中的主键索引就属于聚簇索引。
  • 聚簇索引将表中的数据存储在叶节点上，具有高效的范围查询。
  • 因为需要移动数据，所以更新的代价大。
  • 适用于经常需要范围查询、排序和分组等操作的列
• 非聚簇索引(非聚集索引)：索引结构和数据分开存放的索引。MyISAM 引擎，主键和非主键，都是使用非聚簇索引。
  • 叶节点存储的索引键值和数据指针。
  • 不需要移动数据，只需要更新索引项，所以更新代价小。
  • 适用于需要快速访问但不需要排序的列。

7. 索引底层的数据结构了解么？

答：索引的底层数据结构有B树、B+树、哈希表、倒排索引等。

• B树：也称B-树，全称为多路平衡查找树。
  • 所有节点都存放键和数据。
  • 是一种自平衡树，确保数据在插入和删除后任然保持平衡，所有叶节点在同一层上。
  • 树的高度较低，查找和插入操作效率较高。
• B+树：是B树多一个变体。在B树的基础上进行了优化，使其更适合数据库系统的需求，InnoDB默认采用的数据结构。
  • 只有叶节点存放键和数据，非叶子节点只存放键。
  • 叶节点通过链表相连，便于范围查询和顺序访问。
  • 非叶节点占用更少空间，提高了索引的缓存效率。
• 哈希表：哈希表是一种基于哈希函数的数据结构，通常用于Memory存储引擎中
  • 使用哈希函数将键值映射到存储位置。
  • 适用于等值查找，不支持范围查找。
• 倒排索引：主要用于全文检索，如文章内容和评论等。
  • 将文档中的每个词映射到包含该词的文档列表，便于快速查找包含特定词的文档。
  • 支持布尔模式和自然语言模式。

8. B+树做索引比红黑树好在哪里？

答：B+树和红黑树都是常用的平衡树数据结构。

• 范围查询效率更高：
  • B+树：所有的数据存储在叶节点，并且通过双向链表连接，查询时只需从叶节点顺序遍历链表即可。
  • 红黑树：范围查询需要在树中进行中序遍历，涉及多次树的遍历，效率较低。
• 更好的磁盘I/O效率：
  • B+树：B+树的节点可以有多个子节点，树的高度较低，读取路径短，需要的磁盘I/O操作更少。
  • 红黑树：它是二叉树，每个节点只有两个叶节点。因此树的高度较高，相比B+树，需要更多的磁盘I/O操作。
• 更高的存储空间利用率：
  • B+树：数据存储在叶子节点，非叶子节点只存储键，使得索引结构更加紧凑，非叶节点占用的空间更少，可以在内存中存储更多的叶节点，减少磁盘的访问次数。
  • 红黑树：每个节点都存储数据和键值，索引结构不如B+树紧凑，占用更多空间，导致内存利用率较低。
• 更高的插入和删除效率：
  • B+树：由于B+树的高扇出特性，树的高度较低，插入和删除操作涉及的节点较少。B+树的节点分裂和合并操作相对较少，从而减少了重新平衡的频率。
  • 红黑树：红黑树的插入和删除操作需要频繁地进行旋转和重新着色，以保持树的平衡，这些操作复杂且频繁，导致性能开销较大。
• 支持顺序访问：
  • B+树：叶节点的链表结构使得B+树天然支持顺序访问，可以高效地进行顺序扫描、范围查询和排序操作。
  • 红黑树：红黑树不支持顺序访问，进行顺序扫描需要中序遍历，效率较低。
• 适用于大规模数据存储：
  • B+树：由于B+树的高度较低、存储空间利用率高、查询性能稳定，它更适用于大规模数据的存储和查询。
  • 红黑树：由于其二叉树的结构限制，在大规模数据情况下可能性能不如B+树。

9. 最左前缀匹配原则了解么？

答：最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，直到不再满足查询条件或索引列结束为止，MySQL 就会使用索引来过滤数据。如果最左列没有匹配，则整个索引将不会被利用。

- 复合索引的示例： 假设有一个表 users，其复合索引包含以下三列： (first_name, last_name, age)。
CREATE TABLE users (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);
CREATE INDEX idx_name_age ON users(first_name, last_name, age);

• 使用最左前缀列：first_name

  -- 该查询会使用 idx_name_age 索引，因为 first_name 是索引的最左前缀。
  SELECT * FROM users WHERE first_name = 'John';
  

• 使用最左前缀列和第二列：first_name 和 last_name

  -- 该查询会使用 idx_name_age 索引，因为 first_name 和 last_name 是索引的前两列。
  SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';
  

• 使用最左前缀列和第二列：first_name 和 last_name

  -- 该查询会使用 idx_name_age 索引，因为查询条件包含了索引的所有列。
  SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe' AND age = 30;
  

• 跳过最左前缀列，直接使用第二列：last_name

  -- 该查询不会使用 idx_name_age 索引，因为没有使用索引的最左前缀列 first_name。
  SELECT * FROM users WHERE last_name = 'Doe';
  

• 跳过中间列，使用最左前缀和第三列：first_name 和 age

  -- 该查询会部分使用 idx_name_age 索引，只会利用 first_name 部分，而 age 部分不会被利用，因为跳过了中间列 last_name。
  SELECT * FROM users WHERE first_name = 'John' AND age = 30;
  

10. 什么是覆盖索引？

答：覆盖是指一个索引包含了查询中所有需要的列，因此查询可以完全通过索引来获取数据，而不需要访问表中的数据行。这种方式可以显著提高查询性能，因为只需要读取索引，而无需进行额外的磁盘I/O操作来访问数据行。
在 InnoDB 存储引擎中，非主键索引的叶子节点包含的是主键的值。这意味着，当使用非主键索引进行查询时，数据库会先找到对应的主键值，然后再通过主键索引来定位和检索完整的行数据。这个过程被称为“回表”。覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。

11. 如何查看某条SQL语句是否用到了索引？

答：在MySQL中，可以使用 EXPLAIN 关键字来查看某条SQL语句是否使用了索引。EXPLAIN 语句提供了关于查询执行计划的详细信息，包括是否使用了索引、使用了哪个索引、访问的数据行数等。通过这些信息，可以了解查询的执行过程，并进行优化。
在执行EXPLAIN 语句后，会输出一个表格，其中内容如下：

• id：查询的标识符，通常表示SELECT语句的执行顺序或嵌套层次
• select_type: 查询的类型，例如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
• table: 当前行涉及的表名。
• partitions: 匹配的分区，对于未分区的表，值为 NULL。
• type: 查询执行的类型，描述了查询是如何执行的。顺序从最优到最差排序为。system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
  • system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
  • const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
  • eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
  • ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
  • index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
  • range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
  • index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快
  • ALL：全表扫描。
• possible_keys: 查询时可能用到的索引列表。
• key: 实际使用到的索引。如果为空，表示没有使用索引。
• key_len: 实际使用的索引的最大长度
• ref: 显示索引的哪一列被用于查找行。
• rows: 大致估算出找到所需的记录或所需读取的行数
• filtered: 表示返回结果的行占开始查找行的百分比（即经过WHERE条件过滤后的行数的百分比）。
• Extra: 解析查询的额外信息。例如Using where、Using index、Using temporary等。
  • Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
  • Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
  • Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
  • Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
  • Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
  • Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

参考资料

• JavaGuide
• 牛客网-Java面试宝典
• ChatGPT
• MySQL官网