9大MySQL性能优化详解(图文全面总结)

大家好，我是mikechen。

MySQL性能优化是确保数据库查询操作高效执行的关键，以下我详解9大常见的MySQL性能优化@mikechen

本篇已收于mikechen原创超30万字《阿里架构师进阶专题合集》里面。

MySQL查询优化方式一：使用索引

创建适当的索引可以大大提高查询性能，确保在经常用于过滤、连接和排序的列上创建索引。

比如：姓名，身份证号等都是高区分度的列，这类字段建立索引可以极大的提升查询效率。

示例：

-- 在 order 表的 customer_id 和 order_date 列上创建组合索引
CREATE INDEX idx_customer_order_date ON order (customer_id, order_date);

MySQL查询优化方式二：使用查询计划

使用 EXPLAIN 关键字分析查询执行计划，以便了解查询是如何使用索引、连接和排序等操作的。

EXPLAIN SELECT * FROM your_table WHERE your_condition;

执行这个查询时，MySQL 将返回一张描述查询执行计划的结果集，其中包含了查询优化器的决策和执行步骤。

id | select_type | table   | type   | possible_keys | key     | key_len | ref   | rows | Extra
----------------------------------------------------------------------------------------------
1  | SIMPLE      | orders  | ref    | idx_customer | idx_customer | 4

   | const | 10   | Using index condition; Using where

EXPLAIN 结果中的主要列有：

• id：查询的标识符，用于标识不同的查询。
• select_type：查询类型，可能是 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
• table：正在访问的表名。
• type：访问类型，描述了 MySQL 如何访问表。常见值有 ALL（全表扫描）、index（通过索引）、range（范围查询）等。
• possible_keys：可能使用的索引列表。
• key：实际使用的索引。
• key_len：使用的索引长度。
• ref：表示哪个列或常数与索引一起使用，或者与哪个常数进行比较。
• rows：估计要检查的行数。
• Extra：附加信息，例如使用了临时表、排序操作等。

MySQL查询优化方式三：优化 WHERE 子句

在 WHERE 子句中使用索引列进行过滤，避免使用函数或表达式，因为这可能导致索引失效。

如下所示：

-- 全表扫描
select * from article where year(publish_time) < 2019

-- 走索引
select * from article where publish_time < '2019-01-01'

-- 全表扫描
select * from article where id + 1 = 5

-- 走索引
select * from article where id = 4

在列上进行运算或使用函数会使索引失效，从而进行全表扫描。

MySQL查询优化方式四：避免 SELECT * 查询

避免使用 SELECT *，而是明确列出所需的列，这可以减少不必要的数据传输和查询时间。

示例：

SELECT customer_id, first_name, last_name FROM customers;

MySQL查询优化方式五：使用合适的连接操作

在 MySQL 中，合适的连接操作是优化查询性能的关键之一。

在 SQL 查询中，常见的连接操作有 INNER JOIN、LEFT JOIN、RIGHT JOIN和 FULL JOIN。

INNER JOIN 用于获取两个表中匹配的数据：

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

LEFT JOIN 和 RIGHT JOIN 用于获取一个表中的所有行，以及与之相关的匹配行：

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

在适当的情况下，使用以上连接（JOIN）操作来合并数据，连接通常可以更有效地优化。

MySQL查询优化方式六：使用缓存

使用 MySQL 的查询缓存来缓存常见查询结果，避免重复执行相同的查询。

示例：

-- 启用查询缓存
SET GLOBAL query_cache_size = 1000000;

当然，也可以使用Redis来缓存数据。

MySQL查询优化方式七：分页查询优化

使用 LIMIT 子句来分页查询，限制返回的行数，以减少数据传输。

示例：

-- 查询前 10 条记录

SELECT * FROM products LIMIT 10;

MySQL查询优化方式八：使用合适的数据类型

使用适当的数据类型可以减少存储空间和提高查询性能。

-- 使用整数类型存储标识符
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    -- ...

);

MySQL查询优化方式九：分库分表

如果数据量过大，也可以使用分库分表。

垂直拆分表：

如果某个表包含多个不同的实体或属性，可以考虑将其拆分为多个表，以减少查询时的冗余数据扫描。

水平分区表：

如果表的数据量过大，可以考虑将表水平分区为多个子表，以减少单个表的数据量，提高查询性能。

以上

本篇已收于mikechen原创超30万字《阿里架构师进阶专题合集》里面。