常见的SQL优化面试专题大全

目录

• 介绍：
• 问：比如，现在有个面试官说，现在线上有个SQL执行很慢，你怎么优化？
• 问：慢 SQL 语句的几种常见诱因？
• 问：平时写SQL时该注意什么？有什么经验可谈？
• 问：有哪些影响数据库性能的瓶颈？
• 问：改善SQL性能涉及哪些步骤？
• 问：您如何分析执行计划？
• 问：您如何使用执行计划调整查询？
• 问：什么是Summary advisor，它提供什么类型的信息？
• 问：是什么最有可能导致SQL查询运行慢至5分钟？
• 问：什么是Latch Free Event？何时发生？系统如何处理？
• 问：什么是主动调整和被动调整？
• 问： 什么是基于规则的优化器和基于成本的优化器？
• 问： Oracle中有哪些SQL性能调优增强功能？
• 总结

介绍：

无论您是创建Web应用程序的开发人员，还是参与Web测试的DBA或测试人员，SQL方面的技巧在数据库编程和数据库验证中都非常重要。因此，我们整理了QL性能优化方面的面试问题。

SQL性能优化是一项艰巨的任务，并且是处理Web应用程序日益增加的负载的关键。

因此，我们有选择地选择了SQL性能优化方面的面试问题，这些问题可以使您充分了解SQL性能优化概念。

MySQL 基本是每个后台面试者必问的问题，而SQL优化又是MySQL中重之又重的一块了。如果你的SQL优化回答的好，有条理，那你入取的概率就大大提升了。

问：比如，现在有个面试官说，现在线上有个SQL执行很慢，你怎么优化？

这种时候最好分几步回答，不要一上来就说，该怎么怎么写SQL，面试时要学会，跳出来，看全貌，装进去，看本质。

问：慢 SQL 语句的几种常见诱因？

在这个时候，就可以说，面试官，您好，我觉得SQL慢出现的原因可以分为以下几种，切记，不要回答自己模糊不清的，没法自圆其说的即使知道你也别回答，不然就是给自己挖坑。。。

• 无索引、索引失效导致慢查询
  如果表很大，而对where或者order by 后面字段没有建立索引，那这种情况查起来肯定很费力。但是有时候建立了索引，但在一些特定的场景下，索引还有可能会失效，所以索引失效也是导致慢查询的主要原因之一。失效场景很多，比如模糊查询白分号在前、查询的字段使用了函数或者做了计算操作都有可能导致索引失效。
• 锁等待
  InnoDB 存储引擎支持行锁和表锁，我们要注意行锁升级为表锁的可能。在批量更新操作时，行锁就很可能会升级为表锁。MySQL 认为如果对一张表使用大量行锁，会导致事务执行效率下降，从而可能造成其它事务长时间锁等待和更多的锁冲突问题发生，致使性能严重下降，所以 MySQL 会将行锁升级为表锁。还有，行锁是基于索引加的锁，如果我们在更新操作时，条件索引失效，那么行锁也会升级为表锁。除了锁升级之外，行锁相对表锁来说，虽然粒度更细，并发能力提升了，但也带来了新的问题，那就是死锁。
• 不恰当的 SQL 语句
  使用不恰当的 SQL 语句也是慢 SQL 最常见的诱因之一。例如，习惯使用 <SELECT >，<SELECT COUNT()> SQL 语句，在大数据表中使用 <LIMIT M,N> 分页查询，以及对非索引字段进行排序等等。对于一些慢的SQL，我该怎么分析？

上面我说了一些关于SQL执行慢的原因，下面就该看看怎么分析这些SQL了

• 找出慢SQL
  开启慢查询，慢查询是分析SQL执行慢的常见手段，主要有这几个参数，slow_query_log：慢查询开启状态，slow_query_log_file：慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录），long_query_time：查询超过多少秒才记录。
• 通过 EXPLAIN 分析 SQL 执行计划
• 通过 Show Profile 分析 SQL 执行性能

问：平时写SQL时该注意什么？有什么经验可谈？

到这里，如果面试官还在听你回答，这时候，你可以适当提高一点语气，说对于一些慢SQL，有很大一部分是由于我们开发者写SQL时不注意SQL优化导致的，那么我就说一下我知道的一些注意事项。。。（提高声音分贝，以防面试官睡着，并敲重点，告诉面试官，我准备好了，哈哈哈。。。）

• 统计时尽量使用count(*)，count(*)≈count(1)，大于count(主键)。
• 如果明确查找一条语句，请使用limit 1;，因为找到一条符合条件的记录后就不会继续查找了。
• 优化分页查询
• 避免 Select *
  用到什么字段就具体写什么字段，原因除了 select * 查询所有字段会多出网络传输开销之外，还有更重要的一点是，select * 无法使用覆盖索引。
• 尽量使用 MySQL 5.6以后的版本
• 对于使用索引方面对索引字段做函数操作或者做运算操作，都不能使用上索引。
  所以针对这一点，除了我们索引的字段不要加函数之外。还要注意一些隐式转换，比如，交易日志表（tradelog），tradeid 的字段类型是 varchar(32)，字段有索引，但是当你执行 select * from tradelog where tradeid=110717;语句，你发现走的还是全索引扫描。这是因为它其实做了类型转换，相当于这么执行mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;。隐式字符编码转换
  如果两张表使用的编码集不一致，比如一个是utf8mb4，一个是utf8，那么这两个字段连接后，相当于做了一次CONVERT(traideid USING utf8mb4),这样也用不了索引。其实字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

问：1. 什么是SQL查询优化？

答: 查询优化是一种以某种方式编写查询以便可以快速执行的过程。对于任何标准应用而言，这都是重要的一步。

问：有哪些技巧可以提高SQL查询的性能？

答: 优化SQL查询可以对性​能带来实质性的积极影响。它还取决于您所拥有的RDBMS知识水平。现在让我们看一下调整SQL查询的一些技巧。

1.尽量不要编写冗长的查询，使用视图和存储过程，它有助于最大程度地减少网络负载。

2.最好引入约束而不是触发器。它们比触发器更有效，并且可以提高性能。

3.使用表级变量而不是临时表。

4. UNION ALL子句的响应速度比UNION快。它不会查找重复的行，而UNION语句会查找重复的行，无论它们是否存在。

5.防止使用DISTINCT和HAVING子句。

6.避免过多使用SQL游标。

7.构建存储过程时，请使用SET NOCOUNT ON子句。它表示受T-SQL语句影响的行。这将导致网络流量减少。

8.优良作法是返回所需的列而不是表的所有列。

9.最好不要使用复杂的Join，并避免不当地使用触发器。

10.创建表索引并遵守标准

问：有哪些影响数据库性能的瓶颈？

答: 在Web应用程序中，数据库层可能被证明是实现最后一英里可扩展性的关键瓶颈。如果数据库存在性能泄漏，则可能成为瓶颈，并可能导致问题。一些常见的性能问题如下。

1. CPU使用率异常是最明显的性能瓶颈。但是，您可以通过扩容CPU或替换为高级CPU来修复它。它可能看起来像是一个简单的问题，但异常的CPU使用率可能导致其他问题。

2.内存不足是下一个最常见的瓶颈。如果服务器无法管理峰值负载，那么它将对性能造成很大的问号。对于任何应用程序来说，执行内存都是至关重要的，因为它比持久性内存要快。同样，当RAM下降到特定阈值时，操作系统将转向使用交换内存。但这会使应用程序运行非常缓慢。

您可以通过扩容RAM来解决它，但是如果有内存泄漏，它将无法解决。在这种情况下，您需要分析应用程序，以识别其代码中的潜在泄漏。

3.对外部存储设备（如SATA磁盘）的过度依赖也可能成为瓶颈。在将大量数据写入磁盘时，其影响显而易见。如果输出操作非常慢，则很明显表明问题已成为瓶颈。

在这种情况下，您需要进行扩容，用更快的驱动器替换现有驱动器。尝试升级到SSD硬盘驱动器或类似产品。

问：改善SQL性能涉及哪些步骤？

答：

• 发现–首先，找出需要改进的地方。探索诸如探查器，查询执行计划，SQL调优顾问，动态视图和自定义存储过程之类的工具。

• 审核–集中讨论可用数据以隔离主要问题。

• 提议–这是一种可以提高性能的标准方法。但是，您可以对其进行进一步自定义以最大程度地受益。

1.识别字段并创建索引。

2.修改大型查询以使用创建的索引。

3.刷新表和视图并更新统计信息。

4.重置现有索引并删除未使用的索引。

5.查找并清除死块。

验证–测试SQL性能调整方法。定期监视进度。另外，跟踪是否对应用程序的其他部分有不利影响。

发布–现在，是时候与团队中的每个人共享工作解决方案了。让他们知道所有最佳做法，以便他们可以轻松使用它。问：什么是执行计划（explain plan）？

答: 这是SQL中使用的术语，它显示其优化程序计划用于执行SELECT / UPDATE / INSERT / DELETE语句的执行计划。

问：您如何分析执行计划？

答: 在分析说明计划时，请检查以下区域。

1.驱动表

2.联接顺序

3.联接方法

4.意外的笛卡尔积

5.嵌套循环，合并排序和哈希联接

6.全表扫描

7.未使用的索引

8.访问路径

问：您如何使用执行计划调整查询？

答: 说明计划显示查询成本的完整输出，包括每个子查询。成本与查询执行时间成正比。该计划还在从查询中获取数据的同时在查询或子查询中描述了问题。

问：什么是Summary advisor，它提供什么类型的信息？

答: Summary advisor是用于过滤和实例化视图的工具。通过为给定的工作负载选择适当的实例化视图集，它可以帮助提高SQL性能。它还提供有关实例化视图建议的数据。

问：是什么最有可能导致SQL查询运行慢至5分钟？

答: 很有可能，特定表中数据量的突然增加可能会减慢SQL查询的输出。因此，收集目标表的必要统计信息。另外，监视数据库级别或基础对象级别的任何更改。

问：什么是Latch Free Event？何时发生？系统如何处理？

答: 在Oracle中，当会话需要锁，试图获取它但由于其他人拥有它而失败时，就会发生“Latch Free Event”等待事件。

因此，它等待着等待，等待锁释放，然后醒来并再次尝，试锁上没有等待服务员的有序队列，因此最先到达的人可以得到它。

问：什么是主动调整和被动调整？

答:

主动调整–架构师或DBA确定在设计和开发过程中哪些系统资源和可用的Oracle功能组合满足标准。

反应式调整–这是发现和消除瓶颈的自下而上的方法。目的是使Oracle响应更快。

问： 什么是基于规则的优化器和基于成本的优化器？

答: Oracle确定如何获取处理有效SQL语句所需的数据。它使用以下两种方法之一来做出此决定。

基于规则的优化器–如果服务器没有内部统计信息支持该语句引用的对象，则RBO方法将获得优先级。但是，Oracle将在以后的版本中弃用此方法。

基于成本的优化器–内部统计数据很多时，CBO将获得优先权。它验证几种可能的执行计划，并根据系统资源选择成本最低的计划。

问： Oracle中有哪些SQL性能调优增强功能？

答: Oracle提供了许多性能增强功能，其中包括：

1.自动性能诊断和调优功能

2.自动共享内存管理–它使Oracle可以控制SGA中的内存分配。

3.等待模型的改进–已经有了许多视图来增强等待模型。

4.自动优化器统计信息收集–使用称为GATHER_STATS_JOB的计划作业收集优化器统计信息。

5.动态采样–使服务器能够增强性能。

6. CPU Costing –这是优化器（CPU + I / O）的基本成本模型，其成本单位为时间优化器通知的时间。

7.基于规则的优化器过时-不再使用。

8.跟踪增强功能–端到端跟踪，该跟踪允许通过客户端标识符而不是使用典型的会话ID来识别客户端进程。

1、Mysql 查询是否区分大小写？

• 不区分
• SELECT VERSION(), CURRENT_DATE;
• SeLect version(), current_date;
• seleCt vErSiOn(), current_DATE;
• 所有这些例子都是一样的，Mysql 不区分大小写。

2、Mysql 的技术特点是什么？

• Mysql 数据库软件是一个客户端或服务器系统，其中包括：支持各种客户端程序和库的多
• 线程 SQL 服务器、不同的后端、广泛的应用程序编程接口和管理工具。

3、Heap 表是什么？

• HEAP 表存在于内存中，用于临时高速存储。
• BLOB 或 TEXT 字段是不允许的
• 只能使用比较运算符=，<，>，=>，= <
• HEAP 表不支持 AUTO_INCREMENT
• 索引不可为 NULL

4、Mysql 服务器默认端口是什么？

• Mysql 服务器的默认端口是 3306。

5、与 Oracle 相比，Mysql 有什么优势？

• Mysql 是开源软件，随时可用，无需付费。
• Mysql 是便携式的
• 带有命令提示符的 GUI。
• 使用 Mysql 查询浏览器支持管理

6、如何区分 FLOAT 和 DOUBLE？

• 以下是 FLOAT 和 DOUBLE 的区别：
• 浮点数以 8 位精度存储在 FLOAT 中，并且有四个字节。
• 浮点数存储在 DOUBLE 中，精度为 18 位，有八个字节。

7、区分 CHAR_LENGTH 和 LENGTH？

• CHAR_LENGTH 是字符数，而 LENGTH 是字节数。Latin 字符的这两个数据是相同的，但是对
• 于 Unicode 和其他编码，它们是不同的。

8、请简洁描述 Mysql 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

• SQL 标准定义的四个隔离级别为：
• read uncommited ：读到未提交数据
• read committed：脏读，不可重复读
• repeatable read：可重读
• serializable ：串行事物

9、在 Mysql 中 ENUM 的用法是什么？

• ENUM 是一个字符串对象，用于指定一组预定义的值，并可在创建表时使用。
• Create table size(name ENUM('Smail,‘Medium’,‘Large’);

10、如何定义 REGEXP？

• REGEXP 是模式匹配，其中匹配模式在搜索值的任何位置。

11、CHAR 和 VARCHAR 的区别？

• 以下是 CHAR 和 VARCHAR 的区别：
• CHAR 和 VARCHAR 类型在存储和检索方面有所不同
• CHAR 列长度固定为创建表时声明的长度，长度值范围是 1 到 255
• 当 CHAR 值被存储时，它们被用空格填充到特定长度，检索 CHAR 值时需删除尾随空格。

12、列的字符串类型可以是什么？

字符串类型是：

• SET
• BLOB
• ENUM
• CHAR
• TEXT
• VARCHAR

13、如何获取当前的 Mysql 版本？

• SELECT VERSION();用于获取当前 Mysql 的版本。

14、Mysql 中使用什么存储引擎？

存储引擎称为表类型，数据使用各种技术存储在文件中。

技术涉及：

• Storage mechanism
• Locking levels
• Indexing
• Capabilities and functions.

15、Mysql 驱动程序是什么？

以下是 Mysql 中可用的驱动程序：

• PHP 驱动程序
• JDBC 驱动程序
• ODBC 驱动程序
• CWRAPPER
• PYTHON 驱动程序
• PERL 驱动程序
• RUBY 驱动程序
• CAP11PHP 驱动程序
• Ado.net5.mxj

16、TIMESTAMP 在 UPDATE CURRENT_TIMESTAMP 数据类型上做什么？

• 创建表时 TIMESTAMP 列用 Zero 更新。只要表中的其他字段发生更改，UPDATE
• CURRENT_TIMESTAMP 修饰符就将时间戳字段更新为当前时间。

17、主键和候选键有什么区别？

• 表格的每一行都由主键唯一标识,一个表只有一个主键。
• 主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

18、如何使用 Unix shell 登录 Mysql？

我们可以通过以下命令登录：

[mysql dir]/bin/mysql -h hostname -u

19、 myisamchk 是用来做什么的？

它用来压缩 MyISAM 表，这减少了磁盘或内存使用。

20、ISAM 是什么？

ISAM 简称为索引顺序访问方法。它是由 IBM 开发的，用于在磁带等辅助存储系统上存储和

检索数据。

21、如何控制 HEAP 表的最大尺寸？

Heal 表的大小可通过称为 max_heap_table_size 的 Mysql 配置变量来控制。

22、MyISAM Static 和 MyISAM Dynamic 有什么区别？

在 MyISAM Static 上的所有字段有固定宽度。动态 MyISAM 表将具有像 TEXT，BLOB 等字

段，以适应不同长度的数据类型。点击这里有一套最全阿里面试题总结。

MyISAM Static 在受损情况下更容易恢复。

23、federated 表是什么？

federated 表，允许访问位于其他服务器数据库上的表。

24、如果一个表有一列定义为 TIMESTAMP，将发生什么？

每当行被更改时，时间戳字段将获取当前时间戳。

25、列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

26、怎样才能找出最后一次插入时分配了哪个自动增量？

LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。

27、你怎么看到为表格定义的所有索引？

索引是通过以下方式为表格定义的：

SHOW INDEX FROM

28、MYSQL 数据表在什么情况下容易损坏？

服务器突然断电导致数据文件损坏。

强制关机，没有先关闭 mysql 服务等。

29、mysql 有关权限的表都有哪几个？

Mysql 服务器通过权限表来控制用户对数据库的访问，权限表存放在 mysql 数据库里，由

mysql_install_db 脚本初始化。这些权限表分别 user，db，table_priv，columns_priv 和

host。

30、Mysql 中有哪几种锁？

MyISAM 支持表锁，InnoDB 支持表锁和行锁，默认为行锁

表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量

最低

行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

总结

到此这篇关于常见的SQL优化面试专题的文章就介绍到这了,更多相关SQL优化面试内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！