MySQL 查询优化:提速查询效率的13大秘籍(避免使用SELECT *、分页查询的优化、合理使用连接、子查询的优化)(上)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 查询优化:提速查询效率的13大秘籍(避免使用SELECT *、分页查询的优化、合理使用连接、子查询的优化)(上)

MySQL优化中,查询分析语句是不可或缺的工具。在数据库开发中,遇到查询速度缓慢的问题是司空见惯的挑战。MySQL作为一款广泛使用的关系型数据库,其查询性能优化显得尤为重要。除了常见的添加索引、优化LIKE和OR查询之外,还有一系列其他关键方面需要注意。本文将深入探讨MySQL查询优化的细节,包括避免使用SELECT *、分页查询的优化、合理使用连接、子查询的优化等多个关键策略。

巧妙的使用索引

当使用MySQL进行查询时,索引可以大大提高查询的速度。索引是一种数据结构,它允许数据库系统快速定位和访问特定的数据行。通过使用索引,数据库可以避免全表扫描,而是直接跳转到包含所需数据的位置。

下面是一些关于如何使用索引来增加查询速度的详细解释:

  1. 索引的创建:在MySQL中,可以通过在表的列上创建索引来提高查询性能。常见的索引类型包括B树索引和哈希索引。创建索引的语法如下:
CREATE INDEX index_name ON table_name (column_name);
  1. 其中,index_name是索引的名称,table_name是表的名称,column_name是要创建索引的列名。
  2. 索引的选择:选择合适的列来创建索引非常重要。一般来说,那些经常在查询中使用的列是最好选择的索引列。例如,如果经常使用WHERE子句来过滤某个列的值,那么在该列上创建索引将大大提高查询性能。
  3. 联合索引:在某些情况下,使用多个列来创建联合索引可以更好地支持查询。当查询涉及多个列的组合条件时,联合索引可以更有效地过滤数据。创建联合索引的语法如下:
CREATE INDEX index_name ON table_name (column1, column2, ...);
  1. 这将在列column1、column2等上创建一个联合索引。
  2. 索引的更新和优化:当表中的数据发生变化时,索引也需要进行更新以保持其准确性和效率。插入、更新和删除操作可能会导致索引失效或降低性能。因此,在对表进行大量的数据修改操作之前,最好先考虑删除或禁用索引,然后再重新创建或启用它们。使用EXPLAIN语句可以分析查询语句的执行计划,以确定是否使用了合适的索引。

总结起来,通过创建适当的索引、选择合适的列、使用联合索引、及时更新索引和进行索引优化,可以显著提高MySQL查询的速度。然而,索引的使用也需要权衡,因为过多或不必要的索引可能会带来负面影响。因此,在设计数据库架构和查询语句时,需要综合考虑索引的使用和管理。根据具体的数据和查询需求,可以针对性地创建合适的索引来优化查询性能。

下面是一个案例,展示了如何使用查询分析语句来优化查询效率:

案例背景

假设有一个名为 orders 的表,包含了100万条订单记录。我们的目标是查询订单状态为"已发货"且订单金额大于100的订单列表。

SELECT *
FROM orders
WHERE order_status = '已发货'
  AND order_amount > 100;
优化前的查询分析

查询执行较慢,运行时间为5.32秒。通过查询分析语句,我们了解到是因为缺少索引,导致全表扫描。

EXPLAIN SELECT *
FROM orders
WHERE order_status = '已发货'
  AND order_amount > 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE orders NULL ALL NULL NULL NULL NULL 1000000 10.00 Using where
优化后的查询分析

通过为 order_statusorder_amount 字段创建索引,再次运行查询,时间降至0.12秒。

CREATE INDEX idx_order_status ON orders(order_status);
CREATE INDEX idx_order_amount ON orders(order_amount);
EXPLAIN SELECT *
FROM orders
WHERE order_status = '已发货'
  AND order_amount > 100;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE orders NULL ref idx_order_status,idx_order_amount idx_order_status 13 const 500000 50.00 Using where

通过创建适当的索引,我们成功地将查询时间从5.32秒降低到了0.12秒。这个案例展示了如何通过查询分析语句来识别缺少索引的问题,并通过创建合适的索引来优化查询性能。

请注意,索引的创建需要根据具体的查询需求和数据情况进行权衡和优化。不适当的索引可能会导致额外的存储开销和性能下降。因此,在实际应用中,需要综合考虑索引的创建和管理,以获得最佳的查询性能。

两大查询优化技巧

当使用LIKE关键字进行模糊查询时,索引的使用需要特别注意。通常情况下,将通配符%放在后面的查询条件可以更好地利用索引。这是因为MySQL的索引是按照索引列的顺序进行存储的,而通配符%在前面会导致索引无法按顺序匹配。

例如,假设有一个名为products的表,其中有一个列product_name需要进行模糊查询。我们希望查询所有以"apple"开头的产品名称。

CREATE INDEX idx_product_name ON products(product_name);
-- 不能使用索引
EXPLAIN SELECT *
FROM products
WHERE product_name LIKE '%apple%';
-- 可以使用索引
EXPLAIN SELECT *
FROM products
WHERE product_name LIKE 'apple%';

在第一个查询中,将通配符%放在前面,导致无法使用索引进行匹配,而需要进行全表扫描。而在第二个查询中,将通配符%放在后面,可以利用索引按顺序匹配,并且只返回满足条件的行,大大提高了查询效率。

类似地,当使用OR关键字进行查询时,为了能够使用索引,必须保证OR前后的表达式中的字段都建有索引。否则,MySQL将无法使用索引来加速查询,而是进行全表扫描。

例如,假设有一个名为orders的表,其中有两个列customer_idorder_number需要进行查询。我们希望查询所有满足customer_id = 1order_number = 'ORD123'条件的订单。

CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_order_number ON orders(order_number);
-- 可以使用索引
EXPLAIN SELECT *
FROM orders
WHERE customer_id = 1 OR order_number = 'ORD123';
-- 不能使用索引
EXPLAIN SELECT *
FROM orders
WHERE customer_id = 1 OR order_date = '2022-01-01';

在第一个查询中,customer_idorder_number两个条件都有对应的索引,MySQL可以利用索引进行查询优化。而在第二个查询中,customer_id有索引,但order_date没有索引,导致无法使用索引进行加速,而是进行全表扫描。

综上所述,通过合理创建索引和注意LIKEOR等关键字的使用,可以优化MySQL查询性能。将通配符%放在后面的LIKE查询可以更好地利用索引,而为了能够使用索引,OR前后的表达式中的字段都需要建立索引。这些优化技巧可以显著提高查询效率,减少查询时间。

使用合适的字段数据类型

使用合适的字段数据类型: 确保表中的字段使用了最适合的数据类型。选择适当的数据类型可以减小存储空间,提高查询速度。

选择合适的字段数据类型对数据库性能和存储效率至关重要。以下是一些常见的数据类型及其使用场景的详细解释:

  1. 整数类型(INT, BIGINT, SMALLINT):
  • INT: 通常用于存储普通整数,占用4个字节。范围为-231到231-1。
  • BIGINT: 用于存储大整数,占用8个字节。范围为-263到263-1。
  • SMALLINT: 适合存储较小的整数,占用2个字节。范围为-215到215-1。
    选择合适的整数类型可减小存储需求,同时提高查询效率。
  1. 浮点数类型(FLOAT, DOUBLE):
  • FLOAT: 用于存储单精度浮点数,占用4个字节。
  • DOUBLE: 用于存储双精度浮点数,占用8个字节。
    浮点数适合存储小数,但要注意浮点数精度问题。对于财务等敏感应用,建议使用DECIMAL类型。
  1. 字符串类型(VARCHAR, CHAR, TEXT):
  • VARCHAR: 变长字符串,适用于长度可变的文本数据,但会占用额外存储以记录字符串长度。
  • CHAR: 定长字符串,适用于长度固定的文本数据,效率较高。
  • TEXT: 用于存储较长文本,比VARCHAR更灵活,但可能会影响性能。
  1. 根据数据的特点选择合适的字符串类型,避免过度使用TEXT。
  2. 日期和时间类型(DATE, TIME, DATETIME, TIMESTAMP):
  • DATE: 用于存储日期。
  • TIME: 用于存储时间。
  • DATETIME: 用于存储日期和时间。
  • TIMESTAMP: 存储日期和时间,并自动更新。
  1. 根据实际需求选择合适的日期和时间类型,TIMESTAMP通常用于记录数据的创建和更新时间。
  2. 枚举类型(ENUM):
  • 用于存储字符串对象的一个预定义集合,每个字段只能是其中之一。
  • 适用于有限且固定的取值范围的情况,可以提高查询效率。
  1. 布尔类型(BOOLEAN):
  • 存储True或False,通常占用1个字节。
  • 用于存储逻辑值,可提高存储和查询效率。
  1. 二进制类型(BLOB, VARBINARY):
  • BLOB: 用于存储二进制大对象,如图像或文件。
  • VARBINARY: 变长的二进制数据类型。
  1. 适用于存储二进制数据,但要注意对查询性能的影响。

正确选择数据类型有助于减小存储空间、提高查询效率,同时保证数据的准确性。在设计数据库表时,仔细考虑字段的实际需求和数据特性,合理选择数据类型,避免不必要的浪费。

避免使用SELECT *

避免使用SELECT *是数据库查询性能优化的一项基本原则。下面详细解释为什么不推荐使用SELECT *,以及应该如何替代它:

为什么不推荐使用SELECT *

  1. 性能开销:
  • SELECT *会检索表中的所有列,包括不需要的字段。这样做会增加网络传输的开销,尤其是在表有大量列或者字段中包含大量数据时。
  1. 数据冗余:
  • 如果表结构发生变化,如添加、删除或调整列的顺序,SELECT *语句的输出也会相应变化,导致代码的脆弱性增加。
  1. 查询结果不可控:
  • 使用SELECT *可能导致查询结果包含不需要的字段,使得开发人员无法明确知道查询返回的具体列。
  1. 缓存失效:
  • 如果查询的是一个较大的表,SELECT *可能导致数据库缓存无法充分利用,降低查询性能。
  1. 可读性差:
  • SELECT *使得查询语句缺乏清晰的表达意图,可读性差,降低了代码的维护性和可理解性。

应该如何替代SELECT *

  1. 明确指定需要的列:
  • 明确列出查询语句中需要的字段,只选择实际需要的数据,减少网络传输和数据库工作的开销。
-- 不推荐
SELECT * FROM users;
-- 推荐
SELECT user_id, username, email FROM users;
  1. 使用表别名:
  • 在多表关联查询时,使用表别名能够更清晰地表达查询意图,避免歧义。
-- 不推荐
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
-- 推荐
SELECT o.order_id, o.order_date, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
  1. 减小数据量:
  • 只选择实际需要的行,通过使用WHERE子句进行条件过滤,减小结果集的大小。
-- 不推荐
SELECT * FROM products WHERE price > 100;
-- 推荐
SELECT product_id, product_name, price FROM products WHERE price > 100;
  1. 使用视图(View):
  • 将经常性使用的复杂查询或者需要隐藏部分信息的查询封装成视图,然后在应用程序中使用视图,而不是直接使用SELECT *
-- 创建视图
CREATE VIEW vw_customer_info AS
SELECT customer_id, customer_name, email FROM customers;
-- 在应用程序中使用
SELECT * FROM vw_customer_info;

总体来说,避免使用SELECT *可以提高查询性能、代码的可维护性和可读性。通过明确指定需要的列,开发者可以更好地控制查询的输出,减少不必要的开销。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
SQL 关系型数据库 MySQL
MYSQL数据库查询优化
MySQL 中的 `EXPLAIN` 语句用于查看 SQL 查询的执行计划,帮助分析查询如何使用索引以及优化查询性能。`EXPLAIN` 输出包括多个列,例如 `id`、`select_type`、`table`、`type`、`possible_keys`、`key`、`key_len`、`ref`、`rows` 和 `Extra`,这些列提供了关于如何处理查询的详细信息。
32 1
|
4天前
|
SQL 缓存 关系型数据库
下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
【5月更文挑战第20天】下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
26 3
|
7天前
|
关系型数据库 MySQL
实现MySQL分页查询的三种方式~
实现MySQL分页查询的三种方式~
|
7天前
|
缓存 关系型数据库 MySQL
如何优化MySQL性能?
【5月更文挑战第23天】如何优化MySQL性能?
17 1
|
8天前
|
存储 SQL 关系型数据库
【MySQL进阶之路 | 基础篇】基本的SELECT语句及DESC显示表结构
【MySQL进阶之路 | 基础篇】基本的SELECT语句及DESC显示表结构
|
9天前
|
SQL 存储 关系型数据库
MySQL怎样优化千万级数据
MySQL在处理千万级数据时可能存在性能挑战。本文介绍了几个优化策略来改善查询效率
123 2
|
13天前
|
缓存 关系型数据库 MySQL
如何优化MySQL数据库查询性能
MySQL是一款常用的关系型数据库,但在实际使用过程中,由于数据量增加和查询操作复杂度增加,会导致查询性能下降。本文将介绍一些优化MySQL数据库查询性能的方法。
|
14天前
|
SQL 存储 关系型数据库
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
|
15天前
|
SQL 存储 关系型数据库
【MySQL】SQL 优化
【MySQL】SQL 优化
40 0
|
15天前
|
存储 算法 关系型数据库
MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀
MySQL连接的原理⭐️4种优化连接的手段性能提升240%🚀