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

07-16 974阅读

面试题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是什么?有什么优点?
        • 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的执行过程涉及了解析、优化、执行和结果返回等多个步骤,如下图所示

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

                                • 客户端请求:客户端通过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官网
VPS购买请点击我

文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。

目录[+]