如何优化mysql 的查询语句

最新推荐文章于 2025-12-18 19:34:09 发布

原创最新推荐文章于 2025-12-18 19:34:09 发布 · 918 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #软件测试 #自动化测试

优化 MySQL 查询对于提高应用程序性能至关重要。本文将介绍一些优化 MySQL 查询的最佳实践，并提供实际示例。

索引

索引是 MySQL 用于快速查找数据的结构。创建适当的索引可以显著提高查询性能。

使用唯一索引：对于唯一值列（如主键），使用唯一索引可以防止重复数据，并加快查找速度。
使用复合索引:对于经常一起使用的列，创建复合索引可以提高多列查询的性能。
避免使用通配符:在索引列中使用通配符（如 % 或 _）会降低索引的效率。

创建唯一索引示例：

CREATE UNIQUE INDEX idx_user_id ON users (user_id);

创建复合索引的示例：

CREATE INDEX idx_name_email ON users (name, email);

查询优化

使用 EXPLAIN： EXPLAIN 语句可以提供有关查询执行计划的信息，帮助识别查询瓶颈，找到使用索引扫描的数据行数，以此判断索引的问题和使用效率。
避免子查询：子查询会降低性能，应尽可能使用连接或派生表。
使用 LIMIT：限制返回的行数可以提高查询速度，尤其是在处理大型数据集时。
优化 ORDER BY：使用索引列进行排序可以提高性能。

使用 explain 示例：

EXPLAIN SELECT * FROM users WHERE name LIKE '%John%';

执行结果：

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

|---|---|---|---|---|---|---|---|---|---|---|---|

| 1 | SIMPLE | users | NULL | index_subquery | NULL | name | 255 | NULL | 1000 | 10 | Using where |

执行结果说明：

id：查询的标识符。

select_type：查询类型，这里是 SIMPLE，表示这是一个简单的查询，没有子查询或联合。

table：参与查询的表，这里是 users 表。

partitions：如果表被分区，则显示分区信息。这里没有分区。

type：访问类型，这里是 index_subquery，表示 MySQL 使用索引来查找匹配的行。

possible_keys：查询可能使用的索引列表。这里没有可能的索引。

key：实际使用的索引，这里是 name 索引。

key_len：索引使用的字节数，这里是 255 字节。

ref：索引列中使用的常量值，这里没有常量值。

rows： MySQL 估计要扫描的行数，这里是 1000 行。

filtered：估计过滤掉的行的百分比，这里是 10%。

Extra：其他信息，这里是 Using where，表示 MySQL 正在使用 WHERE 子句来过滤行。

分析：

MySQL 正在使用 name 索引来查找匹配的行。
MySQL 估计需要扫描 1000 行，并过滤掉其中 10%。
查询使用 LIKE 操作符，这通常比使用 = 操作符慢。
由于 name 列没有前缀索引，MySQL 必须扫描整个索引来查找匹配的行。

优化建议：

在 name 列上创建一个前缀索引，例如 CREATE INDEX idx_name_prefix ON users (name(10))。
如果可能，使用 = 操作符代替 LIKE 操作符。

表结构

规范化数据：将数据存储在多个表中，以避免冗余和提高性能。
使用适当的数据类型：选择正确的列数据类型可以优化存储和查询性能。
避免空值：空值会降低查询性能，应尽可能使用默认值或 NOT NULL。

创建表的一个比较好的示例：

CREATE TABLE orders (

order_id INT NOT NULL AUTO_INCREMENT,

customer_id INT NOT NULL,

product_id INT NOT NULL,

quantity INT NOT NULL,

PRIMARY KEY (order_id),

FOREIGN KEY (customer_id) REFERENCES customers (customer_id),

FOREIGN KEY (product_id) REFERENCES products (product_id)

);

其他技巧