最常用，最有效的数据库优化方式
查询语句层面
避免全表扫描
- 使用索引：确保查询条件中的字段有索引。例如，查询语句
SELECT * FROM users WHERE age > 20，若age字段有索引，数据库会利用索引快速定位符合条件的记录，而不是全表扫描。 - 优化查询条件：避免在
WHERE子句中使用函数，因为这可能导致索引失效。如SELECT * FROM users WHERE YEAR(created_at) = 2024，可改写为SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'。
优化查询逻辑
- 减少子查询：尽量用连接查询替代子查询。例如，原查询
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE country = 'China')可改写为SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.country = 'China'。 - 避免
SELECT *：只选择需要的列，减少数据传输量和数据库处理时间。
事务层面
减少事务持有时间
- 优化事务逻辑：事务中包含的操作应尽量少，避免在事务中进行耗时的操作，如大量的数据计算或文件读写。
合理设置事务隔离级别
- 选择合适级别：根据业务需求选择合适的事务隔离级别，避免使用过高的隔离级别导致并发性能下降。例如，对于一些对数据一致性要求不是特别高的场景，可使用
READ COMMITTED隔离级别。
设计层面
表结构优化
- 合理选择字段类型
选择与数据匹配的最小数据类型，以节省存储空间并提高查询速度。例如，对于仅存储 0 - 255 范围内整数的字段，使用TINYINT而不是INT。对于存储少量字符的字段，使用VARCHAR而非TEXT。 - 主键和索引设计
-
- 主键：使用自增整数作为主键，在 MySQL 中，自增主键能让数据在磁盘上顺序存储，提高插入和查询效率。
- 索引：在经常用于查询条件（如
WHERE子句）、排序（ORDER BY）和连接（JOIN）的字段上创建索引。例如，在用户表的username字段上创建索引，可加快根据用户名查找用户的查询速度。当经常需要多个字段组合查询时，创建复合索引。例如，对于查询SELECT * FROM products WHERE category = 'Electronics' AND price < 1000，可创建复合索引(category, price)。如果查询所需的数据都可以从索引中获取，数据库就无需再回表查询数据行，提高查询效率。 - 重建或重新组织索引：随着数据的插入、更新和删除，索引可能会变得碎片化，影响查询性能。定期对索引进行重建或重新组织，可提高索引的效率。
数据库架构优化
- 垂直拆分：将一个包含很多列的大表拆分成多个小表，每个小表包含原表的一部分列。例如，用户表中若包含基本信息（如姓名、性别）和扩展信息（如兴趣爱好、简介），可拆分成两个表，减少每次查询时需要读取的数据量。
- 水平拆分：当单表数据量过大时，将数据按一定规则（如日期、地域等）分散到多个表中。如将订单表按年份拆分为多个表，每年一个表，能提高查询和维护效率。
服务器配置层面
内存配置
- 调整缓存大小：对于关系型数据库如 MySQL，可增大
innodb_buffer_pool_size参数，让数据库有更多内存用于缓存数据和索引，减少磁盘 I/O。一般可将其设置为服务器物理内存的 70% - 80%。
磁盘 I/O 优化
- 使用 SSD 硬盘：相比传统的机械硬盘，SSD 硬盘具有更快的读写速度，能显著提高数据库的性能。
- 合理规划磁盘布局：将数据文件、日志文件和临时文件分别存放在不同的磁盘分区，避免 I/O 竞争。
疑问解答
为什么尽量用连接查询替代子查询？
在 SQL Server 中，尽量使用连接查询替代子查询主要基于以下几个方面的原因：
性能方面
- 执行计划与优化
-
- 连接查询的执行计划通常更容易被 SQL Server 查询优化器理解和优化。优化器可以更有效地分析连接条件和表之间的关系，从而选择更合适的索引和执行策略。例如，对于两个表的连接查询，优化器可以根据表的大小、索引情况等因素选择嵌套循环连接、哈希连接或合并连接等方式。
- 子查询在某些情况下可能会导致优化器难以生成最优的执行计划。尤其是相关子查询，它会针对外部查询的每一行都执行一次子查询，这可能会导致大量的重复计算，性能较差。例如下面的相关子查询：
SELECT column1, column2
FROM TableA
WHERE column1 > (SELECT AVG(column1) FROM TableB WHERE TableB.key = TableA.key);这个查询会对 `TableA` 中的每一行都执行一次子查询来计算 `TableB` 中对应行的平均值，效率较低。
资源利用方面
- 内存使用效率
-
- 连接查询在内存使用上更加高效。它可以直接在内存中对多个表的数据进行关联处理，而不需要额外的临时表存储中间结果。
- 子查询可能会占用更多的内存资源。特别是当子查询的结果集较大时，需要在内存中创建临时表来存储这些结果，这会增加内存的压力。
但在某些特定情况下，子查询可能更合适，例如需要获取满足特定条件的单个值或进行复杂的过滤操作时。因此，在实际应用中，需要根据具体的业务需求和数据情况来选择合适的查询方式。
索引碎片化产生的原因
数据频繁插入与删除：当数据库中频繁进行数据的插入和删除操作时，就容易导致索引碎片化。例如，在一个用户信息表中，不断有新用户注册（插入数据）和老用户注销（删除数据），这会使得索引页中的空闲空间变得零散，新的数据可能会被插入到这些分散的空闲空间中，从而造成索引数据的不连续。
数据更新：对索引列的数据进行更新操作也可能引发碎片化。如果更新操作导致索引键值的大小发生变化，就可能需要重新安排该索引项在索引页中的位置，进而破坏了索引的连续性。
