MySQL优化：12种提升SQL执行效率的有效方法

在数据库管理和优化的世界里，MySQL作为一个流行的关系型数据库管理系统，其性能优化是任何数据密集型应用成功的关键。优化MySQL数据库不仅可以显著提高SQL查询的效率，还能确保数据的稳定性和可靠性。

在本文中，我将介绍12种提升SQL执行效率的有效方法，并通过实用的代码示例来具体展示如何实施这些优化策略。

本文，已收录于，我的技术网站 ddkk.com，有大厂完整面经，工作技术，架构师成长之路，等经验分享

1、使用索引优化查询

使用场景：当你的数据库表中有大量数据，而你需要频繁进行搜索查询时，索引是提高查询效率的关键。

代码示例：

-- 假设我们有一个员工表 employeesCREATETABLE employees (
    id INTAUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,PRIMARYKEY(id));-- 为department_id字段创建索引CREATEINDEX idx_department ON employees(department_id);-- 使用索引进行查询SELECT*FROM employees WHERE department_id =5;

代码解释：

第一步是创建一个包含id, name, department_id字段的employees表。

然后为department_id字段创建一个索引idx_department。这个操作会让基于department_id的查询更快。

最后，我们执行一个查询，利用创建的索引，从而提高查询效率。

最近无意间获得一份阿里大佬写的刷题笔记，一下子打通了我的任督二脉，进大厂原来没那么难。这是大佬写的， 七千页的BAT大佬写的刷题笔记，让我offer拿到手软

2、优化查询语句

使用场景：避免使用高成本的SQL操作，如**SELECT ***，尽量指定需要的列，减少数据传输和处理时间。

代码示例：

-- 不推荐的查询方式SELECT*FROM employees;-- 推荐的查询方式SELECT id, name FROM employees;

代码解释：

第一个查询语句使用了**SELECT ***，它会获取所有列，这在数据量大时非常低效。

第二个查询仅请求需要的id和name列，减少了数据处理的负担。

3、使用查询缓存

使用场景：当相同的查询被频繁执行时，使用查询缓存可以避免重复的数据库扫描。

代码示例：

-- 启用查询缓存SETglobal query_cache_size =1000000;SETglobal query_cache_type =1;-- 执行查询SELECT name FROM employees WHERE department_id =5;

代码解释：

通过设置query_cache_size和query_cache_type，我们启用了查询缓存。

当我们执行查询时，MySQL会检查缓存中是否已经有了该查询的结果，如果有，则直接返回结果，避免了重复的数据库扫描。

4、避免全表扫描

使用场景：当表中数据量巨大时，全表扫描会非常耗时。通过使用合适的查询条件来避免全表扫描，可以显著提高查询效率。

代码示例：

-- 假设我们需要查询员工表中特定部门的员工-- 不推荐的查询方式，会导致全表扫描SELECT*FROM employees WHERE name LIKE'%张%';-- 推荐的查询方式SELECT*FROM employees WHERE department_id =3AND name LIKE'%张%';

代码解释：

第一个查询使用了模糊匹配LIKE，但缺乏有效的过滤条件，可能导致全表扫描。

第二个查询在name字段的模糊匹配前，增加了对department_id的条件过滤，这样就可以先缩小查找范围，避免全表扫描。

5、使用JOIN代替子查询

使用场景：在需要关联多个表的复杂查询中，使用JOIN代替子查询可以提高查询效率。

代码示例：

-- 假设我们有一个部门表 departmentsCREATETABLE departments (
    id INTAUTO_INCREMENT,
    name VARCHAR(100),PRIMARYKEY(id));-- 不推荐的子查询方式SELECT*FROM employees WHERE department_id IN(SELECT id FROM departments WHERE name ='IT');-- 推荐的JOIN查询方式SELECT employees.*FROM employees JOIN departments ON employees.department_id = departments.id WHERE departments.name ='IT';

代码解释：

第一个查询使用了子查询，这在执行时可能效率较低，特别是当子查询或主查询的结果集较大时。

第二个查询使用了JOIN操作，这通常比子查询更有效，尤其是在处理大型数据集时。

6、合理分页

使用场景：在处理大量数据的列表展示时，合理的分页策略可以减少单次查询的负担，提高响应速度。

代码示例：

-- 假设我们需要分页显示员工信息-- 不推荐的分页方式，尤其是当offset值很大时SELECT*FROM employees LIMIT10000,20;-- 推荐的分页方式，使用更高效的条件查询SELECT*FROM employees WHERE id >10000LIMIT20;

代码解释：

第一个查询使用了LIMIT和较大的偏移量offset，在大数据集上执行时会逐行扫描跳过大量记录，效率低下。

第二个查询通过在WHERE子句中添加条件来避免不必要的扫描，从而提高分页效率。

最近无意间获得一份阿里大佬写的刷题笔记，一下子打通了我的任督二脉，进大厂原来没那么难。这是大佬写的， 七千页的BAT大佬写的刷题笔记，让我offer拿到手软

7、利用分区提高性能

使用场景：对于大型表，特别是那些行数以百万计的表，使用分区可以提高查询性能和数据管理效率。

代码示例：

-- 假设我们需要对一个大型的订单表 orders 进行分区CREATETABLE orders (
    order_id INTAUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2),PRIMARYKEY(order_id))PARTITIONBY RANGE (YEAR(order_date))(PARTITION p2020 VALUES LESS THAN (2021),PARTITION p2021 VALUES LESS THAN (2022),PARTITION p2022 VALUES LESS THAN (2023));-- 查询特定年份的订单SELECT*FROM orders WHERE order_date BETWEEN'2021-01-01'AND'2021-12-31';

代码解释：

我们为orders表创建了基于order_date字段的年份范围分区。

查询特定年份的数据时，MySQL只会在相关分区中搜索，提高了查询效率。

8、利用批处理减少I/O操作

使用场景：在进行大量数据插入或更新时，批处理可以减少数据库的I/O操作次数，从而提高性能。

代码示例：

-- 批量插入数据INSERTINTO employees (name, department_id)VALUES('张三',1),('李四',2),('王五',3),-- 更多记录;-- 批量更新数据UPDATE employees
SET department_id =CASE name
    WHEN'张三'THEN3WHEN'李四'THEN2-- 更多条件ENDWHERE name IN('张三','李四',-- 更多名称);

代码解释：

在批量插入示例中，我们一次性插入多条记录，而不是对每条记录进行单独的插入操作。

在批量更新示例中，我们使用CASE语句一次性更新多条记录，这比单独更新每条记录更有效率。

9、使用临时表优化复杂查询

使用场景：对于复杂的多步骤查询，使用临时表可以存储中间结果，从而简化查询并提高性能。

代码示例：

-- 创建一个临时表来存储中间结果CREATETEMPORARYTABLE temp_employees
SELECT department_id,COUNT(*)as emp_count
FROM employees
GROUPBY department_id;-- 使用临时表进行查询SELECT departments.name, temp_employees.emp_count
FROM departments
JOIN temp_employees ON departments.id = temp_employees.department_id;

代码解释：

首先，我们通过聚合查询创建了一个临时表temp_employees，用于存储每个部门的员工计数。

然后，我们将这个临时表与部门表departments进行连接查询，这样的查询通常比直接在原始表上执行复杂的聚合查询要高效。

10、优化数据类型

使用场景：在设计数据库表时，选择合适的数据类型对性能有显著影响。优化数据类型可以减少存储空间，提高查询效率。

代码示例：

-- 原始表结构CREATETABLE example (
    id INTAUTO_INCREMENT,
    description TEXT,
    created_at DATETIME,
    is_active BOOLEAN,PRIMARYKEY(id));-- 优化后的表结构CREATETABLE optimized_example (
    id MEDIUMINTAUTO_INCREMENT,
    description VARCHAR(255),
    created_at DATE,
    is_active TINYINT(1),PRIMARYKEY(id));

代码解释：

在原始表中，使用了INT和TEXT这样的宽泛类型，这可能会占用更多的存储空间。

在优化后的表中，id字段改为MEDIUMINT，description改为长度有限的VARCHAR(255)，created_at只存储日期，而is_active使用**TINYINT(1)**来表示布尔值。这样的优化减少了每行数据的大小，提高了存储效率。

11、避免使用函数和操作符

使用场景：在WHERE子句中避免对列使用函数或操作符，可以让MySQL更有效地使用索引。

代码示例：

-- 不推荐的查询方式，使用了函数SELECT*FROM employees WHEREYEAR(birth_date)=1980;-- 推荐的查询方式SELECT*FROM employees WHERE birth_date BETWEEN'1980-01-01'AND'1980-12-31';

代码解释：

在第一个查询中，使用 YEAR() 函数会导致MySQL无法利用索引，因为它必须对每行数据应用函数。

第二个查询直接使用日期范围，这样MySQL可以有效利用birth_date字段的索引。

12、合理使用正规化和反正规化

使用场景：数据库设计中的正规化可以减少数据冗余，而反正规化可以提高查询效率。合理平衡这两者，可以获得最佳性能。

代码示例：

-- 正规化设计CREATETABLE departments (
    department_id INTAUTO_INCREMENT,
    name VARCHAR(100),PRIMARYKEY(department_id));CREATETABLE employees (
    id INTAUTO_INCREMENT,
    name VARCHAR(100),
    department_id INT,PRIMARYKEY(id),FOREIGNKEY(department_id)REFERENCES departments(department_id));-- 反正规化设计CREATETABLE employees_denormalized (
    id INTAUTO_INCREMENT,
    name VARCHAR(100),
    department_name VARCHAR(100),PRIMARYKEY(id));

代码解释：

在正规化设计中，departments和employees表被分开，减少了数据冗余，但可能需要JOIN操作来获取完整信息。

在反正规化设计中，employees_denormalized表通过直接包含部门信息来简化查询，提高读取性能，但可能会增加数据冗余和更新成本。

项目文档&视频：

开源：项目文档 & 视频 Github-Doc

总结

以上提到的优化方法只是众多MySQL优化技术中的一小部分。在实际应用中，应根据具体的数据模式和查询需求灵活选择最合适的优化策略。数据库优化是一个持续的过程，定期的性能评估和调优是保持数据库高效运行的关键。通过实践这些优化技巧，你可以显著提升数据库的性能和响应速度。

本文，已收录于，我的技术网站 ddkk.com，有大厂完整面经，工作技术，架构师成长之路，等经验分享