如何进行sql优化？

本文介绍: 最近无意间获得一份阿里大佬写的刷题笔记和面经，一下子打通了我的任督二脉，进大厂原来没那么难。这是大佬写的，

在日常工作中都避免不了要和各种SQL语句打交道，无论是开发还是后期维护，一条执行效率高的SQL语句都会对系统性能产生巨大影响。那么，如何进行有效的SQL优化呢？下面将为大家深入浅出地讲解SQL优化的各个方面：

1、了解数据表和查询的基础

在进行SQL优化之前，你得先了解你的数据表以及你的查询。掌握表中数据的类型、分布和大小。这个过程包括：

分析表结构：查看表的索引、字段类型等，确保其符合最佳实践。
理解数据特性：了解数据的分布特性，哪些字段经常被查询，哪些字段数据量较大等。

假设我现在的表结构如下

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at DATETIME,
    last_login DATETIME
);

2、使用EXPLAIN分析执行计划

MySQL的EXPLAIN命令是优化查询的重要工具。它可以显示MySQL如何执行SQL查询，包括它是如何使用索引的，以及它是如何join表的。

explain  select * from users where username = 'mntalk';

通过分析EXPLAIN的结果，可以了解SQL执行的瓶颈在哪里，是否选择了合适的索引。

&nbsp

3、优化查询语句

避免SELECT *

使用SELECT *会返回表中的所有列，这通常比需要的要多。你应始终只查询需要的列。

-- 不推荐
SELECT * FROM users WHERE username = 'mntalk';

-- 推荐
SELECT username, email FROM users WHERE username = 'mntalk';

使用索引

确保对经常查询的列创建索引。但请注意，不是每个列都应该建立索引，因为索引本身也有维护成本。

使用主键索引与不使用索引查询对比
&nbsp

-- 创建索引
CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_email ON users(email);

-- 使用索引的查询
SELECT username, email FROM users WHERE username = 'mntalk';

在username和email上创建索引后，查询
使用索引前：
&nbsp

使用索索引之后
&nbsp

避免在WHERE子句中使用函数或者计算

当你在WHERE子句中使用函数或者其他计算时，MySQL通常不能使用索引。

-- 不推荐
SELECT username, email FROM users WHERE MONTH(last_login) = MONTH(CURRENT_DATE()) AND YEAR(last_login) = YEAR(CURRENT_DATE());

-- 推荐
SELECT username, email FROM users WHERE last_login >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH);

4、优化表结构

使用合适的数据类型

选择最合理的数据类型可以节省空间，并提高查询效率。

-- 不推荐
ALTER TABLE users MODIFY username VARCHAR(255);

-- 推荐
ALTER TABLE users MODIFY username VARCHAR(50);

规范化与反规范化

规范化可以减少数据冗余，但是可能会增加查询的复杂度。反规范化可能会增加数据冗余，但是可以简化查询。

5、查询缓存

确保合理使用查询缓存，虽然在MySQL 8.0中已经移除了查询缓存的功能，但在之前版本中，它可以通过缓存查询结果来减少服务器的负载。

6、使用批量操作

当你需要插入大量数据时，尽量使用批量操作而不是单条插入。

-- 不推荐
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
INSERT INTO users (username, email) VALUES ('jane_doe', 'jane@example.com');

-- 推荐
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com'), ('jane_doe', 'jane@example.com');