Mysql重点思考(下)--SQL 优化/慢查询

前言

极端点说，SQL 优化就是对索引的优化。因此，我们要看下各种情况下，如何优化索引。

索引优化和sql优化区别

索引优化

• 索引优化侧重于优化数据库中的索引结构，以提高查询性能。
• 它涉及选择合适的索引类型、确定哪些列应该创建索引、以及如何组织这些索引以最大程度地提高查询速度。
• 索引优化的目标是通过减少数据检索的行数和提高数据检索速度来优化数据库的性能。

SQL 调优

• SQL 调优是通过改进 SQL 查询语句的结构和执行计划来提高查询性能。
• 它包括重构查询以减少资源消耗、使用更有效的连接方法、避免不必要的操作等。
• SQL 调优的目标是优化查询的执行计划，使查询更有效率地访问数据，并减少查询执行所需的时间和资源。

SQL优化的情况

可以走索引

• 应该走索引，但是没走
• 走索引了，但是没到最优(explain 分析，type 一般我们要求至少到达 range 这个级别)
• order by 和 group by 优化

往期指路：
Mysql重点思考(中)–mysql前缀和覆盖优化 http://t.csdnimg.cn/S1CKj

没法走索引

• 或者 type 是 index，而且数据量大
• 了解适用索引的情况，请不要只有面试时会说，工作就不知道了(数据量不大，直接查没事;大的话，考虑引进其他技术解决，如 :Redis, MongoDB, elasticsearch等)

小表驱动大表

• 假设我们有两个表，一个是小表 small_table，另一个是大表 large_table，它们之间通过某个字段进行关联，我们想要查询大表中满足某些条件的数据。

-- 创建一个小表
CREATE TABLE small_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 创建一个大表
CREATE TABLE large_table (
    id INT PRIMARY KEY,
    small_table_id INT,
    some_data VARCHAR(100),
    FOREIGN KEY (small_table_id) REFERENCES small_table(id)
);

-- 示例数据插入
INSERT INTO small_table (id, name) VALUES (1, 'Small1'), (2, 'Small2');

INSERT INTO large_table (id, small_table_id, some_data) VALUES 
(101, 1, 'Data1 related to Small1'),
(102, 2, 'Data2 related to Small2'),
(103, 1, 'Data3 related to Small1');

• 面是一个简单的查询，演示如何使用小表驱动大表：

SELECT lt.id, lt.some_data
FROM large_table lt
JOIN small_table st ON lt.small_table_id = st.id
WHERE st.name = 'Small1';

使用limit

• 分页查询

当用户需要查看大量数据结果时，一次性加载所有数据可能会导致性能问题。通过使用 LIMIT 来限制每次查询返回的结果数量，可以将大数据集分割成多个页面显示，提高用户体验和系统性能。

-- 分页查询，每页返回10条数据
SELECT * FROM table_name LIMIT 10 OFFSET 0; -- 第一页
SELECT * FROM table_name LIMIT 10 OFFSET 10; -- 第二页

• 快速验证数据

在调试或测试阶段，当你只需要查看数据的一小部分时，可以使用 LIMIT 来快速获取样本数据，而不必检索整个数据集。

-- 获取前10条数据进行验证
SELECT * FROM table_name LIMIT 10;

• 优先级排序

-- 获取最新的10个订单
SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

• 限制联合查询结果

-- 获取每个用户的最新5条订单
SELECT * FROM (
    SELECT * FROM orders WHERE user_id = 1 ORDER BY order_date DESC LIMIT 5
) AS user_orders
UNION ALL
SELECT * FROM (
    SELECT * FROM orders WHERE user_id = 2 ORDER BY order_date DESC LIMIT 5
) AS user_orders;

将包含大量值的子查询改写为连接操作

假设我们有两个表，一个是 orders 表，另一个是 customers 表。我们想要从 orders 表中选择某些客户的订单。

• IN 子查询的示例

-- 使用IN子查询
SELECT * 
FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE last_name = 'Smith');

• 连接操作来优化这个查询

-- 使用连接操作
SELECT o.* 
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.last_name = 'Smith';

增量查询代替limit分页查询

增量查询是一种优化 SQL 查询性能的方法，特别适用于大数据集合的分页查询。与使用 LIMIT 进行分页查询相比，增量查询更适合于需要按照某个特定顺序获取大量数据的场景，例如按照时间戳或者自然顺序排序的数据。以下是一个示例，演示了如何使用增量查询替代 LIMIT 分页查询：

• 分页查询

-- 使用LIMIT进行分页查询
SELECT * FROM orders ORDER BY created_at LIMIT 10 OFFSET 0; -- 第一页
SELECT * FROM orders ORDER BY created_at LIMIT 10 OFFSET 10; -- 第二页

• 增量查询

-- 使用增量查询获取分页数据
-- 假设上一页最后一行数据的 created_at 时间为 'last_timestamp'
SELECT * FROM orders WHERE created_at > 'last_timestamp' ORDER BY created_at LIMIT 10;

在这个示例中，我们假设已经获取了上一页数据，并且最后一行数据的 created_at 时间为 last_timestamp。接着，我们使用 WHERE 子句过滤出比 last_timestamp 大的数据，然后再按照 created_at 排序，并限制返回结果的数量为 10 条，以实现分页效果。需要注意的是，增量查询通常需要在应用程序层面保存上一页数据的某些状态信息，以便于构造下一页查询。

提升group by的效率

• 先过滤数据，再分组

SELECT 
    customer_id,
    SUM(order_amount) AS total_amount
FROM 
    orders
WHERE 
    order_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
    customer_id;

• 先分组，再过滤数据

SELECT 
    customer_id,
    SUM(order_amount) AS total_amount
FROM (
    SELECT 
        customer_id,
        order_amount
    FROM 
        orders
    GROUP BY 
        customer_id, order_amount
) AS subquery
WHERE 
    order_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
    customer_id;

慢查询

慢查询的定义

一般情况下，我们把查询时间超过1s的查询称为慢查询。

什么情况慢查询

索引问题

索引优化

优化方法：优化方法:建立索引，并使用索引，还可以对索引进行优化，让其更加高效

数据库结构设计

• 假设我们有一个简单的订单管理系统，其中包含 orders 表和 order_items 表。orders 表存储订单信息，order_items 表存储订单中的商品条目信息。这两个表的结构如下：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    -- 其他订单相关字段
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),
    -- 其他订单商品相关字段
);

• 现在，我们需要查询某个客户最近一段时间内的订单总金额。我们可以使用以下 SQL 查询：

SELECT 
    o.customer_id,
    SUM(oi.quantity * oi.price) AS total_amount
FROM 
    orders o
JOIN 
    order_items oi ON o.order_id = oi.order_id
WHERE 
    o.customer_id = 123
    AND o.order_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY 
    o.customer_id;

在这个查询中，我们需要通过订单表和订单商品表进行连接，并且使用 GROUP BY 对客户进行分组以计算订单总金额。但是，如果数据量非常大，这个查询可能会变得很慢，特别是在订单商品表 order_items 中存在大量数据时。

表结构优化

• 创建合适的索引
  

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

数据库参数配置问题

某公司的订单数据库中包含了几十亿条订单记录，由于查询频繁且数据量庞大，导致查询速度较慢，无法满足业务需求如何优化呢?

• 查看数据库的当前的配置

-- 最大连接数
SHOW VARIABLES LIKE 'max_connections';
-- 缓冲区大小
SELECT * FROM sys.dm_os_sys_memory;
-- 日志文件大小
SELECT * FROM sys.master_files WHERE type_desc = 'LOG';

• 优化最大连接数的参数sql

-- 设置最大连接数为1000
SET GLOBAL max_connections = 1000;

• 调整缓冲区大小

-- 设置 InnoDB 缓冲池大小为 4GB
SET GLOBAL innodb_buffer_pool_size = 4G;

• 调整日志文件大小sql

-- 修改 redo log 文件大小为 1GB
SET GLOBAL innodb_log_file_size = 1073741824;

• 分区表

分区表:考虑将订单表进行分区，根据时间范围或其他字段进行分区，将大表分割成多个较小的子表，以减少查询范围，提高查询性能。

• 数据库垂直拆分和水平

数据库垂直拆分和水平拆分:如果业务需要，可以考虑对数据库进行垂直拆分或水平拆分，将不同的业务模块或数据拆分到不同的数据库或表中，以减少单个表的数据量，提高查询效率。