避免使用 SELECT *

Step 1

Q:: 有哪些常见的 SQL 优化手段？

A:: 常见的 SQL 优化手段包括： 1. 避免使用 SELECT *，应明确选择需要的字段。 2. 使用索引提高查询速度，尤其是在 WHERE 子句中使用索引列。 3. 使用 LIMIT 限制返回的记录数，减少数据传输。 4. 避免使用子查询，尽量使用 JOIN 替代。 5. 对频繁执行的查询进行查询缓存。 6. 使用覆盖索引（index only scan）来避免回表操作。 7. 对于大表，分区表可以显著提升查询性能。

Step 2

Q:: 为什么应该避免使用 SELECT *？

A:: 使用 SELECT * 会导致以下问题： 1. SELECT * 返回了所有列，可能包含大量无用数据，增加了网络带宽消耗和传输时间，尤其是在大字段存在时更为明显。 2. SELECT * 会阻止 MySQL 优化器使用覆盖索引，导致查询效率降低。 3. 在应用程序开发中，使用 SELECT * 难以应对表结构的变更，当表结构发生变化时，可能会导致应用程序崩溃。

Step 3

Q:: 什么是覆盖索引？

A:: 覆盖索引（Covering Index）是指查询所需的所有列都能从索引中获取，而无需访问表数据。通过这种方式，可以大大减少 I/O 操作，提高查询效率。覆盖索引通常通过在 SELECT 子句中只选择索引列实现。

Step 4

Q:: 如何判断 SQL 查询是否使用了索引？

A:: 可以通过以下方式判断 SQL 查询是否使用了索引： 1. 使用 EXPLAIN 命令查看查询执行计划，检查是否有索引被使用。 2. 查看查询的 type 字段，type 为 'index' 或 'range' 通常表示使用了索引。 3. 检查 key 和 key_len 字段，确认是否使用了期望的索引。

Step 1

Q:: 面试题：如何优化 MySQL 的深度分页查询？

A:: 答案：在 MySQL 的深度分页查询中，如果使用直接的 OFFSET 查询（如 LIMIT 1000000, 10），会导致扫描大量的数据行，这在大数据量的情况下效率极低。优化方法包括： 1. 使用子查询：通过子查询先查出目标数据的主键，再通过主键索引进行快速查询。例如：

 
SELECT `score`, `name` FROM `cus_order` WHERE id >= (SELECT id FROM `cus_order` ORDER BY `score` DESC LIMIT 1000000, 1) LIMIT 10;
 

2. 使用延迟关联：先获取需要分页的主键范围，再通过主键范围进行查询。示例：

 
SELECT `score`, `name` FROM `cus_order` a, (SELECT id from `cus_order` ORDER BY `score` DESC LIMIT 1000000, 10) b WHERE a.id = b.id;
 

这种方式减少了全表扫描和数据量的处理，提升了查询效率。

Step 2

Q:: 面试题：为什么子查询能够优化分页查询？

A:: 答案：子查询能够优化分页查询的原因在于它减少了全表扫描的开销。在直接使用 LIMIT 和 OFFSET 的分页查询时，数据库必须扫描和排序大量的数据行，即使这些行并不会出现在最终的结果中。而通过子查询先定位到某个具体的记录点（如通过主键），再从该点开始取数据，可以显著减少不必要的数据处理，优化查询性能。

Step 3

Q:: 面试题：子查询与延迟关联各有什么优缺点？

A:: 答案： 1. 子查询优点：实现简单，代码可读性高，适合于分页数据量较大时优化性能。 缺点：子查询在 MySQL 中会创建临时表，可能会带来额外的性能开销，尤其是在复杂的查询场景下。 2. 延迟关联优点：通过先获取所需的主键范围，然后基于主键进行查询，可以避免全表扫描，减少数据库的负载。 缺点：相对代码实现复杂度高于子查询，且在数据分布不均匀时，主键范围可能难以确定。

Step 4

Q:: 面试题：什么是 MySQL 中的覆盖索引，它如何帮助优化查询？

A:: 答案：覆盖索引是指查询中所需的数据全部可以从索引中获取，无需回表（即无需从数据表中再读取数据）。通过覆盖索引，可以大大减少 I/O 操作，提升查询性能。对于分页查询，如果能够创建一个覆盖索引，查询时仅扫描索引即可完成数据提取，效率会显著提高。

Step 1

Q:: 为什么阿里巴巴开发手册中建议避免超过三个表的 join？

A:: 阿里巴巴开发手册中建议避免超过三个表的 join，主要是出于性能考虑。Join 操作的性能瓶颈主要在于其底层实现，尤其是当表数据量较大时，join 会导致大量的计算和数据传输。特别是当表没有适当的索引时，join 操作可能会导致全表扫描，极大地影响查询效率。而且，随着表的数量增加，查询的复杂度和维护难度也会显著增加。

Step 2

Q:: 在什么情况下 Simple Nested-Loop Join、Block Nested-Loop Join 和 Index Nested-Loop Join 会被使用？

A:: Simple Nested-Loop Join 在没有进行任何优化时使用，它通过遍历每一个记录来匹配，效率最低。Block Nested-Loop Join 则使用了 JOIN BUFFER 进行优化，但当数据量较大时，优化效果有限。Index Nested-Loop Join 则依赖于索引的存在，当字段有索引时，数据库可以利用索引加速查询，从而显著提升性能。

Step 3

Q:: 实际业务中如何避免多表 join 带来的性能问题？

A:: 实际业务中可以通过单表查询后在内存中自己做关联，或通过数据冗余的方式来避免多表 join 带来的性能问题。单表查询的代码复用性高，更易于维护；数据冗余则适用于表结构稳定的情况，虽然会增加存储空间的开销，但可以减少复杂的查询。

Step 4

Q:: 什么是 Index Nested-Loop Join？它是如何提高性能的？

A:: Index Nested-Loop Join 是一种利用索引来优化 join 操作的方式。通过在关联字段上建立索引，数据库在进行 join 操作时可以快速查找匹配的记录，避免全表扫描，从而提高查询性能。

Step 5

Q:: 什么时候可以考虑使用多表 join 而不是其他替代方案？

A:: 当系统并发量不高且需要从多个表中查询数据时，可以考虑使用多表 join。此外，如果业务需求明确，且使用 join 能够简化查询逻辑和代码复杂度，也可以使用多表 join。但是需要确保关联字段上有适当的索引，以避免性能问题。

Step 1

Q:: 为什么阿里巴巴《Java 开发手册》建议不要使用外键与级联？

A:: 阿里巴巴《Java 开发手册》建议不要使用外键与级联，主要原因在于外键与级联操作在分库分表的场景下不友好。当系统的数据量大到需要进行分库分表时，外键和级联可能导致跨库事务和复杂的分布式一致性问题，这会增加系统的复杂性并影响性能。另一方面，外键和级联容易隐藏业务逻辑，降低了代码的可维护性和可读性。因此，阿里巴巴建议将外键的关联逻辑在应用层手动实现。

Step 2

Q:: 外键和级联操作的缺点是什么？

A:: 外键和级联操作的主要缺点包括：1. 影响分库分表，难以处理跨库事务；2. 潜在的性能问题，尤其是在涉及大规模数据操作时；3. 增加数据库锁的争用，可能导致死锁问题；4. 隐藏了数据操作的业务逻辑，降低了代码的可读性和维护性。

Step 3

Q:: 在什么情况下可以考虑使用外键与级联？

A:: 外键与级联在单体应用或数据规模较小、数据库无需分库分表的场景下仍然是有价值的。它们可以自动保证数据的一致性，简化数据的插入和删除操作，尤其在表结构和业务关系相对简单的系统中可以考虑使用。但在涉及复杂系统和高并发场景时，建议在应用层手动管理这些关系。

Step 1

Q:: 选择合适的字段类型时，为什么要将某些字符串（如IP地址）转换成数字类型存储？

A:: 将IP地址等字符串转换成数字类型存储的原因在于数字类型占用的存储空间较小，并且在查询和排序等操作中性能更好。MySQL提供了INET_ATON()和INET_NTOA()方法来将IP地址转换为无符号整型，并在显示时再转换回IP地址，以节省空间和提高查询效率。

Step 2

Q:: 为什么非负型的数据（如自增ID、整型IP、年龄）要优先使用无符号整型来存储？

A:: 无符号整型（UNSIGNED INT）可以有效利用存储空间，因为它不需要保留负数的空间，从而提供更大的正数范围。相比有符号整型，无符号整型可以存储更多的数据，例如无符号INT可以存储0到4294967295，而有符号INT只能存储-2147483648到2147483647。

Step 3

Q:: 在选择整型数据类型时，为什么小数值类型（如年龄、状态表示）优先使用TINYINT类型？

A:: TINYINT类型占用的存储空间最小（仅1个字节），适合存储较小的整数值，如年龄或布尔值（0/1）。选择TINYINT可以有效节省存储空间，尤其是在大数据量的情况下，能够显著降低数据库的整体存储需求。

Step 4

Q:: 为什么建议使用Timestamp而非DateTime来存储日期？

A:: Timestamp类型占用的存储空间较小，并且它会根据服务器的时区自动进行转换，便于跨时区的数据存储和处理。相比之下，DateTime类型不包含时区信息，且占用更多的存储空间，因此在涉及到跨时区处理时，Timestamp是更优的选择。

Step 5

Q:: 金额字段为什么要用Decimal类型来存储？

A:: Decimal类型可以精确存储小数点后的数值，避免了浮点数存储可能带来的精度丢失问题。对于涉及到财务计算的场景，使用Decimal类型可以确保计算的准确性，避免因精度问题导致的财务数据错误。

Step 6

Q:: 为什么建议使用自增ID作为主键？

A:: 使用自增ID作为主键有助于优化B+树索引结构的插入性能。自增ID保证了插入数据的顺序性，使得新数据始终插入到B+树的叶子节点末尾，从而减少了节点分裂的情况，提升了写入性能。然而，在分布式系统或分库分表的场景下，可能更适合使用分布式ID（如UUID）来保证主键的唯一性。

Step 1

Q:: 为什么在SQL查询中尽量使用UNION ALL而不是UNION?

A:: 在SQL查询中，UNION会对两个结果集进行合并并去重，去重操作需要消耗额外的CPU资源和时间。而UNION ALL则直接将两个结果集合并，不进行去重操作，因此效率更高。在实际业务中，如果不需要去重操作，推荐使用UNION ALL以提高查询性能。

Step 2

Q:: UNION和UNION ALL的区别是什么?

A:: UNION会对合并后的结果集进行去重操作，因此如果结果集中有重复的行，UNION会返回去重后的数据集。而UNION ALL不会去重，会返回所有行，包括重复的行。UNION操作的代价更高，主要体现在去重过程中的CPU和内存消耗。

Step 3

Q:: 在什么情况下应该使用UNION而不是UNION ALL?

A:: 在业务场景中，如果两个结果集有可能产生重复数据，而你需要确保最终的结果集不包含重复的记录，这时应该使用UNION而不是UNION ALL。举例来说，当你合并两个来源不同但内容相似的数据集，且业务逻辑要求结果集中的数据唯一时，应选择使用UNION。

Step 1

Q:: 为什么在数据库操作中建议使用批量操作而不是单条插入？

A:: 在数据库操作中，使用批量操作可以减少与数据库的交互次数，降低网络开销和数据库负载，从而提升整体性能。如果每次都使用单条插入操作，数据库必须为每次插入处理单独的事务和索引更新，这会导致较大的性能损耗。通过批量插入，多个数据操作可以合并成一个事务，减少数据库锁定和索引更新的开销。

Step 2

Q:: 批量插入操作有哪些潜在的风险和注意事项？

A:: 虽然批量插入可以提升性能，但也存在一些风险。例如，批量操作可能会导致事务时间过长，从而增加发生死锁的概率。此外，插入大量数据可能会导致数据库写入压力过大，影响其他操作的性能。为了降低风险，可以考虑将批量插入操作分批次进行，并确保数据库的事务日志和内存配置足够支持大规模的批量操作。

Step 3

Q:: 在MySQL中，如何提高批量插入操作的性能？

A:: 在MySQL中，提高批量插入性能的方法包括：关闭或暂时禁用外键约束和索引（可以在插入后重新开启并重建索引），使用InnoDB存储引擎的事务特性来批量处理操作，调整MySQL的innodb_buffer_pool_size参数以优化内存使用，尽量减少每次批量插入的数据量以避免内存溢出问题，并使用LOAD DATA INFILE语句来代替常规的INSERT操作。

Step 1

Q:: 如何启用 MySQL Profiling 并确认其状态？

A:: MySQL Profiling 默认是关闭的，可以通过执行 SET @@profiling=1 命令启用。启用后，可以通过 SELECT @@profiling 查看其状态。Profiling 启用后，将记录当前 Session 下执行的所有 SQL 语句的资源消耗情况。

Step 2

Q:: 如何查看当前 Session 下所有 SQL 语句的 Profiling 信息？

A:: 启用 Profiling 后，可以通过执行 SHOW PROFILES 命令查看当前 Session 下所有 SQL 语句的简要 Profiling 信息，包括 Query_ID 和 Duration（耗时）。这些信息可以帮助识别执行时间较长的查询。

Step 3

Q:: 如何查看某条 SQL 语句的详细执行资源消耗？

A:: 可以通过 SHOW PROFILE 命令查看某条 SQL 语句的详细执行资源消耗。使用 SHOW PROFILE FOR QUERY n 可以查看指定 Query_ID 的 SQL 的详细 Profiling 信息，包括 CPU 使用、IO 等待、内存使用等。

Step 4

Q:: MySQL Profiling 的默认设置有哪些，如何调整？

A:: 默认情况下，Profiling 功能是关闭的，并且最多记录 15 条 SQL 语句的 Profiling 信息。可以通过设置 profiling_history_size 参数调整记录的 SQL 数量，最大可设置为 100。

Step 5

Q:: MySQL Profiling 在哪些情况下不建议使用？

A:: MySQL Profiling 功能虽然有助于诊断 SQL 性能问题，但在高并发的生产环境中不建议开启，因为 Profiling 会带来额外的系统开销，可能导致性能下降。

Step 1

Q:: 什么是 MySQL 慢查询日志？如何开启慢查询日志？

A:: MySQL 慢查询日志是 MySQL 中用来记录响应时间超过预设阈值的 SQL 语句的日志。默认情况下，慢查询日志功能是关闭的。可以通过以下命令开启慢查询日志：

 
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/lib/mysql/ranking-list-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 100;
 

其中，slow_query_log 用于开启日志记录，slow_query_log_file 用于指定日志存放路径，log_queries_not_using_indexes 会将未使用索引的查询记录到日志中，long_query_time 设置慢查询阈值，min_examined_row_limit 设置查询记录的最低行数。

Step 2

Q:: 如何分析 MySQL 慢查询日志？

A:: 可以通过 MySQL 内置的 mysqldumpslow 工具分析慢查询日志，该工具可以将相似的 SQL 归为一类，并统计执行次数、平均耗时等信息。执行 mysqldumpslow -s t -t 10 /var/lib/mysql/ranking-list-slow.log 可以按时间统计前 10 条最慢的查询。对于更复杂的分析，可以借助其他工具如 pt-query-digest 来进一步分析慢查询日志。

Step 3

Q:: Explain 命令在 MySQL 中的作用是什么？

A:: Explain 命令用于分析 SQL 语句的执行计划，帮助了解查询的执行方式、可能使用的索引、扫描的行数等信息。通过分析 Explain 的输出，可以判断查询的效率并进行优化。Explain 的输出包含多个字段，其中 type 表示查询执行方式，是判断查询效率的重要指标。rows 表示执行计划中预计需要扫描的行数，Extra 则提供了额外的执行信息，如 Using filesort 表示需要对结果进行文件排序。

Step 4

Q:: 如何优化 MySQL 慢查询？

A:: 优化 MySQL 慢查询的步骤包括：1) 开启慢查询日志，找出执行慢的 SQL 语句；2) 使用 Explain 分析查询的执行计划；3) 根据分析结果，优化查询，如添加适当的索引、优化 SQL 语句结构、调整数据库表结构等；4) 使用优化后的查询进行测试，确保其性能有所提升。

Step 5

Q:: 什么是 MySQL 中的 Using filesort，如何优化？

A:: Using filesort 是指 MySQL 在执行查询时需要对结果进行文件排序，这通常是由于查询中有 ORDER BY 或 GROUP BY 操作。出现 Using filesort 时，查询可能会变慢。优化的方法包括：确保 ORDER BY 字段有适当的索引、避免在 WHERE 条件中过滤过多数据、减少返回的结果集、避免在查询中使用函数或表达式等。

Step 1

Q:: 什么是索引？为什么需要在数据库中使用索引？

A:: 索引是一种数据结构，用于快速查询数据库表中的数据。通过索引，可以减少查询的 I/O 操作，从而大幅提升数据检索速度。没有索引，数据库需要逐行扫描表中的数据，这对于大表来说非常低效。索引的主要功能是提高查询性能，但需要注意的是，索引也会增加写操作的开销和磁盘空间的消耗。

Step 2

Q:: 如何选择合适的字段来创建索引？

A:: 在选择字段创建索引时，应考虑以下几点：1）不为 NULL 的字段，因为数据库较难优化带有 NULL 的字段。2）被频繁查询的字段。3）作为 WHERE 条件查询的字段。4）需要频繁排序的字段。5）被经常用于表连接的字段。这些字段适合作为索引，以提高查询效率。

Step 3

Q:: 为什么频繁更新的字段不适合建立索引？

A:: 频繁更新的字段不适合建立索引，因为索引需要占用额外的空间和维护成本。每次更新数据时，索引也需要同步更新，这会增加数据库的写入操作开销。如果一个字段被频繁查询但更新较少，可以考虑为其建立索引；但如果频繁更新而不常用于查询，则不建议建立索引。

Step 4

Q:: 什么是联合索引，为什么建议使用联合索引而不是单列索引？

A:: 联合索引是在多个字段上创建的索引。相比于单列索引，联合索引可以减少磁盘空间的占用，并且在查询多个字段时可以提高效率。此外，联合索引在查询中可以覆盖多个列，避免冗余索引的出现，从而减少索引维护的开销。

Step 5

Q:: 什么是冗余索引，如何避免冗余索引？

A:: 冗余索引是指功能相同或覆盖关系的索引，比如 (a, b) 和 (a) 的索引，前者可以命中后者的查询，因此后者就是冗余索引。为了避免冗余索引，应该优先扩展已有的索引而不是创建新的索引。

Step 6

Q:: 为什么在字符串字段上考虑使用前缀索引？

A:: 在字符串类型的字段上使用前缀索引可以减少索引占用的磁盘空间。前缀索引只索引字段值的一部分（前缀），这在处理长字符串字段时尤其有用，因为它可以在减少存储空间的同时提供合理的查询性能。

Step 7

Q:: 哪些情况下索引会失效？

A:: 索引失效的情况包括：1）使用 SELECT * 进行查询。2）组合索引未遵守最左匹配原则。3）在索引列上进行计算、函数、类型转换等操作。4）以 % 开头的 LIKE 查询。5）使用 OR 查询条件时，其中一个列没有索引。6）隐式类型转换导致的索引失效。这些操作会使数据库无法利用索引，从而导致查询性能下降。

Step 8

Q:: 为什么要删除长期未使用的索引？

A:: 长期未使用的索引会占用磁盘空间，并增加数据库的维护成本，同时影响写操作的性能。删除这些索引可以减少不必要的性能损耗。可以通过查询数据库的视图（如 MySQL 5.7 的 schema_unused_indexes）来确定哪些索引从未被使用，然后删除这些索引以优化性能。

Step 1

Q:: 为什么阿里巴巴禁止在数据库中做多表 join？

A:: 阿里巴巴禁止在数据库中做多表 join 的原因是多表 join 的效率较低，尤其是在涉及多个表时，join 的代价会迅速增加。join 操作通常使用嵌套循环（Nested Loop）来实现，效率不高。特别是当没有适当的索引时，join 操作可能会导致全表扫描，增加查询的时间复杂度。此外，数据库的锁定和事务管理会变得更加复杂，影响系统的整体性能。在实际生产环境中，可能会考虑使用单表查询后在应用层关联数据，或者使用数据冗余来避免多表 join。

Step 2

Q:: 如何优化 MySQL 的深分页查询？

A:: 深分页查询通常会带来较大的性能开销，因为它需要跳过大量数据才能获取所需的数据。为了优化深分页查询，可以使用子查询或延迟关联来减少数据的扫描量。例如，可以首先通过子查询获取需要的数据行的主键值，然后再根据主键值过滤并获取最终的结果集。这样可以避免扫描和排序大部分不必要的数据，提升查询效率。

Step 3

Q:: 为什么要避免使用外键与级联操作？

A:: 外键与级联操作可能会影响数据库的性能，尤其是在高并发场景下。它们在保证数据一致性方面确实有帮助，但对分库分表等场景不友好，可能会增加数据库的负载和复杂性。阿里巴巴的《Java 开发手册》建议避免使用外键与级联操作，而是将数据一致性逻辑放在应用层处理，以便在分库分表场景下更好地扩展系统。

Step 4

Q:: 在 MySQL 中如何选择合适的字段类型以提高性能？

A:: 选择合适的字段类型可以显著提高数据库的性能。例如，对于 IP 地址，可以将其存储为无符号整型以减少存储空间和提升查询性能。对于非负数值，优先选择无符号整型，可以有效利用存储空间。对于日期字段，建议使用 Timestamp 而不是 DateTime 以减少存储空间和避免时区问题。对于小数值类型，例如状态标志或年龄，建议使用 TINYINT 类型。此外，对于金额字段，应使用 decimal 类型以避免精度丢失。

Step 5

Q:: 为什么要尽量使用 UNION ALL 而不是 UNION？

A:: UNION 会将两个结果集的所有数据合并到一个临时表中，并执行去重操作，这会耗费更多的 CPU 资源和时间。相比之下，UNION ALL 不会对结果集进行去重操作，直接返回合并后的结果集，因此性能更高。在业务逻辑允许重复数据的场景中，应尽量使用 UNION ALL 以提高查询效率。