【MySQL】的相关面试题（二）

面试题

1. 唯一索引比普通索引快吗？

• 唯一索引和普通索引在性能上没有本质的区别，他们在查询和检索数据的时候速度是相同的
• 数据完整性：唯一索引确保索引列中每一个值都是唯一的，二普通索引则没有这种约束。意味着在插入新数据的时候，数据库系统会自动检查唯一索引，以确保新插入的值在索引列中不存在，如果重复值被插入，数据库会抛出唯一键冲突的错误。
• 查询优化：在某些情况下，唯一索引可能会带来查询性能上的微小优势。由于唯一索引的特性，数据库系统可能会对查询执行更有效的优化，例如跳过不必要的扫描或者避免额外的重复值检查
• 应用场景：唯一索引通常用于需要在索引列上强制唯一性约束的场景，例如数据库表的主键或者唯一标识符。普通索引则用于一般的查询需求，不需要强制唯一性约束的情况。
• 唯一索引和普通索引在性能上没有本质的区别，他们都可以加速查询操作，但是唯一索引在维护数据完整性方面有优势，而普通索引则更适合用于一般的查询需求。

2. 索引的优点

• 优点
  • 提高检索的速度：索引可以加速数据库查询的操作，通过在索引列上建立索引，数据库系统可以快速定位符合查询条件的数据，从而提高插曲性能性能
  • 加速排序和分组：索引不仅可以通过加速查询操作，还可以加速排序和分组操作。通过在排序列或者分组列上船舰索引，数据库可以更快的执行排序和分组操作
  • 减少IO操作：索引可以减少数据库系统的IO操作。通过使用索引，数据库可以更有效的利用内存缓存，减少磁盘IO次数，提高数据访问速度
  • 支持唯一性约束：索引可以用于时间唯一性约束，确保索引列中的每个值都i是唯一的，从而帮助维护数据的完整性
  • 缺点
    • 增加存储空间：索引需要额外的存储空间来存储索引数据结构，对于大型表或者复合索引，可能会占用更多的存储空间，增加数据库的存储成本。
    • 降低写入性能：对标进行插入，更新和删除的操作的时候，索引也需要进行相应的维护操作，这可能会导致写入性能下载。特别是在频繁的更新的表上，索引维护成本会很高
    • 增加查询优化成本；索引的存在会增加数据库系统的查询优化成本。 数据库系统需要根据索引统计信息和查询条件选择最优的执行计划，这可能会增加查询优化的复杂度和计算成本
    • 占用内存和cpu资源：索引 需要占用一定的内存和cpu资源来维护和更新。特别是在高并发的环境中，索引的维护操作可能会成为数据库的瓶颈

3. 什么情况下设置了索引但无法使用？（索引失效）
   • 列类型不匹配：如果索引列的数据类型与查询条件中的数据类型不匹配，索引可能就会失效。例如索引列是字符串类型，但是查询条件中使用了数值类型，那么索引就无法使用。
   • 函数包装：如果在查询条件中对索引列进行函数包装或者运算，索引列也可能会失效。数据库无法直接使用索引来加速对函数结果的计算
   • 模糊查询：在模糊查询中，如果通配符出现在索引的开头，索引可能会失效，因为索引通常是按照从左到右的顺序进行匹配的，无法利用通配符前缀来进行检索，避免方法尽量避免在模糊查询中使用通配符开头的模式，或者考虑使用全文索引来优化模糊查询
   • 数据量过小：如果表中的数据量下，数据库很可能会选择全表扫描而不是使用索引。因为使用索引可能会增加查询优化的成本，对于小表来说，全表扫描可能更为高效
   • 查询条件过于复杂：如果查询条件过于复杂，可能会导致数据库无法有效的使用索引来加快速度。
   • 模糊查询：以”%“开头的like模糊拆线呢可能会导致索引失效，因为无法利用索引的有序性。解决方法，尽量避免以%开头的模糊查询，或者考虑使用全文搜索索引等技术来优化，对于尾部的模糊匹配，索引一般不会失效
   • 违背了最左前缀法则：在使用多列索引时候，如果查询条件没有按照索引列的顺序使用，或者只是用了部分索引列，可能会导致索引失效。因此确保查询条件满足最左前缀法则是避免索引失效的重要方法之一。解决方法，如果需要在多列上建议索引，确保查询条件从左到右依次使用索引。
4. 在建立索引的时候，需要考虑那些因素

• 查询频率：索引的选择应该基于最常用的查询类型和频率。正对经常使用的查询条件建立索引可以提高查询性能
• 查询效率：索引的目的是提高查询效率，因为应该选择能够加速查询的列作为索引列。例如经常用于过滤，排序，分组，的列是建立索引的好选择
• 数据的唯一性：如果某一列的值在表中几乎唯一的，可以考虑为该列建一个唯一索引，确保数据的完整性和查询效率
• 表大小：对于大型表，需要更加谨慎的选择建立索引的列，以避免过多的索引造成额外的存储空间和维护成本
• 事务性能：在具有高并发的事务性环境中，过多的索引会影响插入更新和删除操作的性能，因此需要i权衡考虑索引的数量和类型
• 索引列的选择：选择所以会列时，应该考虑查询条件的最左前缀匹配规则，确保索引能够充分利用。此外，还需要考虑列的数据类型，长度和选择性等因素。
• 索引的维护成本:索引的建立和维护会增加数据库系统的负担，包括磁盘空间，内存占用，cpu计算等方便。在建议索引时需要权衡查询性能和维护成本。

5. 创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?
   • 执行计划：在执行查询语句的时候，可以使用explain关键字来查看查询的执行计划，执行计划会显示数据库 系统选择的查询执行方式，包括是否使用了索引示例：EXPLAIN SELECT * FROM table_name WHERE column_name = ‘value’
   • 慢查询日志，在数据库服务器上启动慢查询日志，记录执行时间超过阈值的查询语句。通过分析慢查询日志，可以确定那些查询语句执行较慢，并进一步检查是否使用了索引
   • 索引统计信息：数据库系统通常会维护索引的统计信息，包括索引的使用i情况和效率。可以通过查看索引统计信息来确定索引被频繁使用
   • 数据库服务器的负载情况
   • 查询语句的复杂度
   • 数据量的大小
   • 系统配置或者调优
6. 如何优化sql
   • sql语句中in包含的值不应过多：MySQL对于in做了相应的优化，即将in中的常量全部存储了一个数组里面，这个数组是排好序的，但是如果数值较多，产生的消耗也是比较大的
   • select查询语句必须指明字段名称
   • 如果排序没有用到索引，就尽量少排序
   • 避免写回进行全表扫描的sql
     • 没有使用索引的列进行查询，如果查询条件中没有被索引，或者查询的写法无法利用索引，MySQL可能会进行全表扫描进行匹配
     • 使用了不适当的比较操作夫：某些比较操作夫（如like 操作符号用前导通配符）可能回无法有效的使用索引，导致全表扫描
     • 强制类型转换或者函数应用于列：在列上使用函数或进行类型转换可以使得该列上的索引变得不可用，如“where year（column_Name） = 2021”
     • or条件链接的不同列且至少一个列未被索引
     • 使用不等式操作符号
     • 索引的选择性不高（即索引列中很多行具有相同的值）
7. 超大分页怎么处理？
   • 使用索引和条件限制：避免使用offset，通过使用条件语句如where和索引来限制拆线呢的范围可以有效减少扫描的数据量
   • 使用游标：对于需要逐行处理数据的情况，游标可以用来避免一次性加载大量数据到内存中。单使用游标可能回增加复杂度，并且在web应用中使用游标处理分页可能不适合
   • 查序优化
     • 覆盖索引查询，确保分页查询只是用到覆盖索引，这样可以减少数据的实际读取数量，提高查询效率
     • 减少数据量，只返回必要列，而不是使用select *
   • 使用更快的分页方法
     • 基于主键或者唯一索引分页
     • 与计算分页偏移
   • 分区
     • 表分区：对于非常大的表，可以考虑使用MySQL的分区功能，将数据分不到不同的物理部分，每个分区可以独立查询，这样可以提高查询的效率
   • 延迟加载和预加载
     • 懒加载：只有在用户需要时才加载数据，可以减轻服务区的负担
     • 预加载：根据用户的行为预测，提前加载可能会被访问的数据。
8. ACID是什么

• 原子性（Atomicity）原子性意味着事务中的所有操作都被视为一个单以的不可分割的单位，要么全部成功，要么全部失败。如果事务中的某个操作失败，整个事务回回滚到事务开始前的状态，就好像这个事务未被执行国过一样。这保证了事务的完整性，避免了部分更新的情况发生
• 一致性（Consistency）一致性确保了事务从一个一致的状态转移到了另一个一致的状态，换句话说，事务的执行不会违反数据库的任何完整性的约束。在事务开始和结束时，所有的数据规则都必须应用，保证数据的准确性和完整性。即使发生给肆，数据库也必须保持一致的状态
• 隔离性：隔离性意味着并发执行的事务时隔离的，一个事务的执行不应被其他事务干扰。隔离性防止多个事务同时执行可能导致发生数据不一致的问题。数据库管理系统通常提供不同级别的隔离，如读未提交，读提交，可重复度和串行化。这些隔离级别在保证隔离性和提高并发性之间提供了不同的平衡
• 持久性：持久性保证了一旦事务被提交，他对数据库的修改就是永久的，即使系统发生崩溃也不会数据丢失。数据库管理系统回使用何种技术来确保事务修改在系统崩溃之后也能恢复
• Insert操作和事务
  • 当执行一个INSERT语句的时候，即使没有显式的开始一个事务（使用begin transaction或等价的命令），这个insert的操作本身就是在一个隐式的事务中执行的。这意味着如果insert执行成功，那么插入的数据将回呗提交，使得操作永久保存在数据库中。如果在插入过程中遇到错误，那么任何已执行的更改都会被撤销。以保证数据的一致性
  • 在大多数数据库系统中，如果你没有显示的开始一个事务，每个单独的sql语句（包括insert）都会被十五一个完整的事务，这被称为自动提交模式，如果你想一次性执行多个操作为一个单一的事务，你需要显示的开始一个事务
• select操作和事务
  • 对于select语句，虽然他们不修改数据，但是他们也可以在十五的上下文中执行。在一个事务中执行select可以确保你在事务的整个过程中看到一致的数据试图。这尤其在合理级别的情况下变得很重要，其中你可能希望确保在事务执行期间读取的数据不会因为其他并发事务的执行二改变
  • 即使在自动提交模式下，select操作也可以视为在执行在他们自己的隐式事务中，尽管这张事务不涉及数据的修改
  • 显示和隐式事务
    • 显示事务你通过SQL语句（如BEGIN TRANSACTION COMMIT ROLLBACK）明确指定的事务。在这种模式下，你可以控制事务的开始和结束，以及在出现错误时候如何回滚
    • 隐式事务是指数据库系统为每个单独的sql语句包装的事务，尤其在自动提交模式下，即使单个的insert或者select语句，也被视为在他们自己的事务中执行