SQL——数据库调优

---

SQL数据库调优

SQL 中的数据库调优是为优化数据库并防止其成为瓶颈而执行的一组活动。

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

数据库调优技术

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

数据库规范化

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

适当的索引

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

避免不当查询

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

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

1.使用SELECT字段代替SELECT(*)

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

例子

假设我们使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 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岁或工资大于2000的客户的详细信息。优化后的查询如下所示 -

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 利用率，从而提高数据库的效率。