ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL 查询优化：提速查询服从的13大秘籍（避免利用SELECT *、分页查询的优 [打印本页]

---

作者: 刘俊凯    时间: 2024-8-25 19:06
标题: MySQL 查询优化：提速查询服从的13大秘籍（避免利用SELECT *、分页查询的优
在MySQL优化中，查询分析语句是不可或缺的工具。在数据库开发中，遇到查询速度迟钝的问题是司空见惯的挑战。MySQL作为一款广泛利用的关系型数据库，其查询性能优化显得尤为重要。除了常见的添加索引、优化LIKE和OR查询之外，还有一系列其他关键方面需要留意。本文将深入探讨MySQL查询优化的细节，包括避免利用SELECT *、分页查询的优化、公道利用毗连、子查询的优化等多个关键策略。
奇妙的利用索引

当利用MySQL进行查询时，索引可以大大提高查询的速度。索引是一种数据布局，它允许数据库体系快速定位和访问特定的数据行。通过利用索引，数据库可以避免全表扫描，而是直接跳转到包含所需数据的位置。
下面是一些关于如何利用索引来增长查询速度的详细表明：

• 索引的创建：在MySQL中，可以通过在表的列上创建索引来提高查询性能。常见的索引类型包括B树索引和哈希索引。创建索引的语法如下：
  
  1. CREATE INDEX index_name ON table_name (column_name);

  复制代码
  其中，index_name是索引的名称，table_name是表的名称，column_name是要创建索引的列名。
  
• 索引的选择：选择合适的列来创建索引非常重要。一样平常来说，那些经常在查询中利用的列是最好选择的索引列。例如，如果经常利用WHERE子句来过滤某个列的值，那么在该列上创建索引将大大提高查询性能。
  
• 联合索引：在某些环境下，利用多个列来创建联合索引可以更好地支持查询。当查询涉及多个列的组合条件时，联合索引可以更有用地过滤数据。创建联合索引的语法如下：
  
  1. CREATE INDEX index_name ON table_name (column1, column2, ...);

  复制代码
  这将在列column1、column2等上创建一个联合索引。
  
• 索引的更新和优化：当表中的数据发生变革时，索引也需要进行更新以保持其正确性和服从。插入、更新和删除操纵可能会导致索引失效或低沉性能。因此，在对表进行大量的数据修改操纵之前，最好先思量删除或禁用索引，然后再重新创建或启用它们。利用EXPLAIN语句可以分析查询语句的执行计划，以确定是否利用了合适的索引。
  

总结起来，通过创建适当的索引、选择合适的列、利用联合索引、实时更新索引和进行索引优化，可以显著提高MySQL查询的速度。然而，索引的利用也需要权衡，由于过多或不须要的索引可能会带来负面影响。因此，在计划数据库架构和查询语句时，需要综合思量索引的利用和管理。根据具体的数据和查询需求，可以针对性地创建合适的索引来优化查询性能。
下面是一个案例，展示了如何利用查询分析语句来优化查询服从：
案例配景

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

1. SELECT *
2. FROM orders
3. WHERE order_status = '已发货'
4.   AND order_amount > 100;

复制代码

优化前的查询分析

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

1. EXPLAIN SELECT *
2. FROM orders
3. WHERE order_status = '已发货'
4.   AND order_amount > 100;

复制代码

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersNULLALLNULLNULLNULLNULL100000010.00Using where 优化后的查询分析

通过为 order_status 和 order_amount 字段创建索引，再次运行查询，时间降至0.12秒。

1. CREATE INDEX idx_order_status ON orders(order_status);CREATE INDEX idx_order_amount ON orders(order_amount);EXPLAIN SELECT *
2. FROM orders
3. WHERE order_status = '已发货'
4.   AND order_amount > 100;

复制代码

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersNULLrefidx_order_status,idx_order_amountidx_order_status13const50000050.00Using where 通过创建适当的索引，我们成功地将查询时间从5.32秒低沉到了0.12秒。这个案例展示了如何通过查询分析语句来辨认缺少索引的问题，并通过创建合适的索引来优化查询性能。
请留意，索引的创建需要根据具体的查询需求和数据环境进行权衡和优化。不适当的索引可能会导致额外的存储开销和性能下降。因此，在实际应用中，需要综合思量索引的创建和管理，以得到最佳的查询性能。
两大查询优化本领

当利用LIKE关键字进行含糊查询时，索引的利用需要特别留意。通常环境下，将通配符%放在后面的查询条件可以更好地利用索引。这是由于MySQL的索引是按照索引列的次序进行存储的，而通配符%在前面会导致索引无法按次序匹配。
例如，假设有一个名为products的表，其中有一个列product_name需要进行含糊查询。我们希望查询所有以"apple"开头的产品名称。

1. CREATE INDEX idx_product_name ON products(product_name);
3. -- 不能使用索引
4. EXPLAIN SELECT *
5. FROM products
6. WHERE product_name LIKE '%apple%';
8. -- 可以使用索引
9. EXPLAIN SELECT *
10. FROM products
11. WHERE product_name LIKE 'apple%';

复制代码

在第一个查询中，将通配符%放在前面，导致无法利用索引进行匹配，而需要进行全表扫描。而在第二个查询中，将通配符%放在后面，可以利用索引按次序匹配，而且只返回满足条件的行，大大提高了查询服从。
类似地，当利用OR关键字进行查询时，为了可以或许利用索引，必须保证OR前后的表达式中的字段都建有索引。否则，MySQL将无法利用索引来加速查询，而是进行全表扫描。
例如，假设有一个名为orders的表，其中有两个列customer_id和order_number需要进行查询。我们希望查询所有满足customer_id = 1或order_number = 'ORD123'条件的订单。

1. CREATE INDEX idx_customer_id ON orders(customer_id);
2. CREATE INDEX idx_order_number ON orders(order_number);
4. -- 可以使用索引
5. EXPLAIN SELECT *
6. FROM orders
7. WHERE customer_id = 1 OR order_number = 'ORD123';
9. -- 不能使用索引
10. EXPLAIN SELECT *
11. FROM orders
12. WHERE customer_id = 1 OR order_date = '2022-01-01';

复制代码

在第一个查询中，customer_id和order_number两个条件都有对应的索引，MySQL可以利用索引进行查询优化。而在第二个查询中，customer_id有索引，但order_date没有索引，导致无法利用索引进行加速，而是进行全表扫描。
综上所述，通过公道创建索引和留意LIKE、OR等关键字的利用，可以优化MySQL查询性能。将通配符%放在后面的LIKE查询可以更好地利用索引，而为了可以或许利用索引，OR前后的表达式中的字段都需要建立索引。这些优化本领可以显著提高查询服从，减少查询时间。
利用合适的字段数据类型

利用合适的字段数据类型： 确保表中的字段利用了最适合的数据类型。选择适当的数据类型可以减小存储空间，提高查询速度。
选择合适的字段数据类型对数据库性能和存储服从至关重要。以下是一些常见的数据类型及其利用场景的详细表明：

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

正确选择数据类型有助于减小存储空间、提高查询服从，同时保证数据的正确性。在计划数据库表时，过细思量字段的实际需求和数据特性，公道选择数据类型，避免不须要的浪费。
避免利用SELECT *

避免利用SELECT *是数据库查询性能优化的一项根本原则。下面详细表明为什么不保举利用SELECT *，以及应该如何替代它：
为什么不保举利用SELECT *：

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

应该如何替代SELECT *：

• 明白指定需要的列：
  
  
  
  • 明白列出查询语句中需要的字段，只选择实际需要的数据，减少网络传输和数据库工作的开销。
    
  1. -- 不推荐
  2. SELECT * FROM users;
  4. -- 推荐
  5. SELECT user_id, username, email FROM users;

  复制代码
  
  
• 利用表别名：
  
  
  
  • 在多表关联查询时，利用表别名可以或许更清晰地表达查询意图，避免歧义。
    
  1. -- 不推荐
  2. SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
  4. -- 推荐
  5. SELECT o.order_id, o.order_date, c.customer_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

  复制代码
  
  
• 减小数据量：
  
  
  
  • 只选择实际需要的行，通过利用WHERE子句进行条件过滤，减小效果集的巨细。
    
  1. -- 不推荐
  2. SELECT * FROM products WHERE price > 100;
  4. -- 推荐
  5. SELECT product_id, product_name, price FROM products WHERE price > 100;

  复制代码
  
  
• 利用视图（View）：
  
  
  
  • 将经常性利用的复杂查询或者需要隐藏部门信息的查询封装成视图，然后在应用程序中利用视图，而不是直接利用SELECT *。
    
  1. -- 创建视图
  2. CREATE VIEW vw_customer_info AS
  3. SELECT customer_id, customer_name, email FROM customers;
  5. -- 在应用程序中使用
  6. SELECT * FROM vw_customer_info;

  复制代码
  
  

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4