SQL 数据库调优

---

---

SQL 数据库调优

SQL 数据库调优是一组用于优化数据库并防止其成为瓶颈的活动。

有多种技术可以配置特定数据库的最佳性能。数据库调优与查询调优重叠；因此，良好的索引和避免不正确的查询有助于提高数据库效率。此外，增加存储空间、更新到最新的数据库版本以及投资更强大的 CPU（如果需要）也是一些常用的技术。

数据库调优技术

我们可以实现以下技术来优化数据库的性能：

数据库规范化

规范化是从数据库中删除重复数据的过程。我们可以通过将更大的表分解成更小的相关表来规范化数据库。这提高了数据库的性能，因为它需要更少的时间从小的表中检索数据，而不是从一个大的表中检索数据。

合适的索引

在 SQL 中，索引是指向数据库中特定数据位置的指针（内存地址）。我们在数据库中使用索引来减少查询时间，因为数据库引擎可以使用其索引跳转到特定记录的位置，而不是扫描整个数据库。

避免不正确的查询

选择正确的查询以高效地检索数据也可以提高数据库的性能。例如，当我们只需要单个列中的数据时，选择检索整个表会不必要地增加查询时间。因此，要明智地查询数据库。

让我们讨论一些常见的错误查询以及如何纠正它们以优化数据库性能。

1. 使用 SELECT *fields* 代替 SELECT (*)

在大型数据库中，我们应该始终只从数据库中检索所需的列，而不是检索所有列，即使不需要它们也是如此。我们可以通过在 SELECT 语句中指定列名而不是使用 SELECT (*) 语句轻松地做到这一点。

示例

假设我们在 MySQL 数据库中使用 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

以下查询使用 INSERT 语句将值插入此表：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

假设我们只需要 CUSTOMERS 表的 ID、NAME 和 SALARY 列中的数据。因此，我们应该只在 SELECT 语句中指定这三列，如下所示：

SELECT ID, NAME, SALARY FROM CUSTOMERS;

输出

获得的输出如下所示：

2. 使用通配符

通配符 (%) 是我们用来根据模式搜索数据的字符。这些与索引配对的通配符只会提高性能，因为数据库可以快速找到与模式匹配的数据。

示例

如果我们想从 CUSTOMERS 表中检索所有以 K 开头的客户的姓名，则以下查询将提供最快的结果：

SELECT ID, NAME FROM CUSTOMERS WHERE NAME LIKE 'K%';

输出

以下是上述查询的输出：

3. 使用显式连接

SQL JOIN 用于基于公共列组合两个表。创建 JOIN 有两种方法：隐式连接和显式连接。显式连接表示法使用 JOIN 关键字和 ON 子句连接两个表，而隐式连接表示法不使用 JOIN 关键字，并与 WHERE 子句一起使用。

从性能方面来看，它们都在同一水平上。但是，在更复杂的情况下，隐式连接表示法可能会产生与预期完全不同的结果。因此，首选显式连接。

4. 避免使用 SELECT DISTINCT

SQL 中的 DISTINCT 运算符用于从数据库中检索唯一记录。在具有唯一索引的正确设计的数据库表上，我们很少使用它。

但是，如果我们仍然必须在表上使用它，则使用 GROUP BY 子句代替 DISTINCT 关键字会显示更好的查询性能（至少在某些数据库中）。

5. 避免使用多个 OR

OR 运算符用于在筛选数据库时组合多个条件。每当我们在筛选条件中使用 OR 时，每个语句都会单独处理。这会降低数据库性能，因为必须多次扫描整个表才能检索与筛选条件匹配的数据。

相反，我们可以使用更优化的解决方案；通过将不同的 OR 条件分解成单独的查询，这些查询可以由数据库并行处理。然后，可以使用 UNION 组合这些查询的结果。

示例

例如，假设我们需要获取所有年龄大于 25 岁或工资大于 2,000 的客户的详细信息。优化的查询将如下所示：

SELECT ID, NAME FROM CUSTOMERS WHERE AGE > 25 
UNION
SELECT ID, NAME FROM CUSTOMERS WHERE SALARY > 2000;

输出

执行上述代码后，我们将得到以下输出：

6. 使用 WHERE 代替 HAVING

WHERE 和 HAVING 子句都用于筛选 SQL 中的数据。但是，WHERE 子句比 HAVING 子句更有效。使用 WHERE 子句，只检索与条件匹配的记录。但对于 HAVING 子句，它首先检索所有记录，然后根据条件筛选它们。因此，WHERE 子句更可取。

数据库碎片整理

当数据存储在数据库中时，它们被放置在连续的物理位置。在这种情况下，逻辑位置和物理位置顺序相同。

但是，当通过删除或更新记录来更改数据库表时，索引也会更改以适应所做的更改。这将导致索引分散在存储中。物理位置也失去了连续分配。从而降低数据库性能。

碎片整理是解决此问题的方案。它将重新组织/重建索引的逻辑顺序以匹配物理顺序。但是，此过程首先会分析索引并选择它们是否只需要重新组织或完全重建。

内置调优工具

某些数据库提供内置的调优工具来监控数据库性能。例如，Oracle数据库提供以下调优工具：

• EXPLAIN − 在SQL中，EXPLAIN命令显示查询执行的顺序以及每个步骤的估计成本。我们可以用它来找到成本最低的查询，从而优化数据库。

• tkprof − tkprof是一个命令，它提供各种统计信息，例如查询的CPU和I/O使用情况。通过使用这些统计信息，我们可以调整查询以减少CPU和I/O利用率，从而提高数据库效率。