MySQL优化加原理

最新推荐文章于 2025-06-11 18:23:02 发布
最新推荐文章于 2025-06-11 18:23:02 发布
阅读量307 收藏
点赞数 1
CC 4.0 BY-SA版权
分类专栏： MySql 文章标签： Mysql SQL优化 索引 InnoDB
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/jiangxiulilinux/article/details/89929223
本文围绕MySQL展开，先提出几个关于慢查询和索引使用的问题。接着介绍MySQL逻辑架构、执行过程，重点讲解InnoDB存储引擎及索引结构，如B+树等。实战部分阐述慢查询的捕获参数和解读，执行计划的各类型及性能排序，还给出优化案例和口诀，助力提升查询性能。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

文章目录

几个问题

  1. 如何发现慢查询？
  2. 为什么不建议使用订单号作为主键？
  3. 为什么要在需要排序的字段上加索引？
  4. 为什么字段加上函数或者计算就不走索引了？
  5. 为什么不建议字段允许为NULL？

理论

MySQL逻辑架构

MySQL逻辑架构整体分为三层，如下图：

在这里插入图片描述

  • 客户端：
    • 如，连接处理、授权认证、安全等功能
  • 核心服务：
    • MySQL大多数核心服务均在这一层
    • 包括查询解析、分析、优化、缓存、内置函数（如，时间、数学、加密等）
    • 所有的跨存储引擎的功能也在这一层，如，存储过程、触发器、视图等
  • 存储引擎：
    • 负责MySQL中的数据存储和读取
    • 中间的服务层通过API与存储引擎通信，这些API屏蔽了不同存储引擎间的差异
  • 重点解释下查询缓存：
    • 对于select语句，在解析查询之前，服务器会先检查查询缓存（Query Cache）。
    • 如果命中，服务器便不再执行查询解析、优化和执行的过程，而是直接返回缓存中的结果集。

MySQL执行过程

下图是MySQL执行一个查询的过程。实际上每一步都比想象中的复杂，尤其优化器，更复杂也更难理解。
在这里插入图片描述

MySQL查询过程如下：

  1. 客户端将查询发送到MySQL服务器；
  2. 服务器先检查查询缓存，如果命中，立即返回缓存中的结果，否则进入下一阶段；
  3. 服务器对SQL进行解析、预处理，再由优化器生成对象的执行计划；
  4. MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询；
  5. 服务器将结果返回给客户端，同时缓存查询结果。

InnoDB存储引擎

在这里插入图片描述

InnoDB索引结构

在MySQL中索引是在存储引擎层实现的，而不是在服务器层实现的。不同存储引擎实现索引的方式也各有不同。

Index_Type：

主键

普通索引

唯一索引

Index_Method：

HASH：

在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引。

例如这种SQL：
SELECT … FROM t WHERE C1 = ?; — 仅等值查询

BTREE（B+树）：

在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。B+树索引结构适用于绝大多数场景。

为什么使用B+树？

一、从内存角度上说，数据库中的索引一般时在磁盘上，数据量大的情况可能无法一次性装入内存，B+树的设计可以允许数据分批加载。

二、从业务场景上说，如果只选择一个数据那确实是hash更快，但是数据库中经常会选中多条这时候由于B+树索引有序，并且又有链表相连，它的查询效率比hash就快很多了。

B-树

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

  • d为大于1的一个正整数，称为B-Tree的度。
  • h为一个正整数，称为B-Tree的高度。
  • 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
  • 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
  • 所有叶节点具有相同的深度，等于树高h。
  • key和指针互相间隔，节点两端是指针。
  • 一个节点中的key从左到右非递减排列。
  • 所有节点组成树结构。
  • 每个指针要么为null，要么指向另外一个节点。
  • 如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key_1)，其中v(key_1)为node的第一个key的值。
  • 如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(key_m)，其中v(key_m)为node的最后一个key的值。
  • 如果某个指针在节点node的左右相邻key分别是key_i和key_{i+1}且不为null，则其指向节点的所有key小于v(key_{i+1})且大于v(key_i)。

下图是一个d=2的B-Tree示意图。
在这里插入图片描述

B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

  • 每个节点的指针上限为2d而不是2d+1；
  • 内节点不存储data，只存储key；
  • 叶子节点不存储指针。

下图是一个简单的B+Tree示意。

在这里插入图片描述

一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关。

带有顺序访问指针的B+Tree

一般在数据库系统或文件系统中使用的B+Tree结构都在经典B+Tree的基础上进行了优化，增加了顺序访问指针。

在这里插入图片描述

如图所示，在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

InnoDB索引实现

聚簇索引

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

在这里插入图片描述

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。

定义：数据行的物理顺序与列值（一般是主键那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

辅助索引

InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

在这里插入图片描述

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

实战

慢查询

我们总是希望MySQL能够获得更高的查询性能，最好的办法是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点，就会发现：很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。

1、slow_query_log

这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句。

2、long_query_time

当SQL语句执行时间超过此数值时，就会被记录到日志中，建议设置为1或者更短。

3、slow_query_log_file

记录日志的文件路径以及文件名。

4、log_queries_not_using_indexes

这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。

show VARIABLES like '%slow_query_log%';
show VARIABLES like '%long_query_time%';
show VARIABLES like '%log_queries_not_using_indexes%';

set global long_query_time=1.0; 
set global log_queries_not_using_indexes=ON;

慢查询解读

User@Host: test_yinni[test_yinni] @  [172.30.39.166]  Id:    85

Query_time: 3.299343  

Lock_time: 0.001484 

Rows_sent: 10  

Rows_examined: 4844057

SET timestamp=1557113299;

SELECT
       *
	FROM
		salaries
	ORDER BY
		from_date,
		salary
	LIMIT 2000000, 10;

从慢查询日志里面摘选一条慢查询日志，数据组成如下：

  1. 用户名 、用户的IP信息、线程ID号
  2. 执行花费的时间【单位：毫秒】
  3. 执行获得锁的时间
  4. 获得的结果行数
  5. 扫描的数据行数
  6. 这SQL执行的具体时间
  7. 具体的SQL语句

执行计划

1、简要说明

列名表格查询的顺序编号。降序查看，id相同的从上到下查查看。id可以为null ，当table为( union ,m,n )类型的时候，id为null，这个时候，id的顺序为 m跟n的后面。
id表查询的顺序编号。id相同，执行顺序由上至下；id不同，id值越大优先级越高，越先被执行
select_type查询的方式下文详细说明。
table表格名称表名，别名，( union m,n ）。
partitions分区名称查询使用到表分区的分区名。
type表连接的类型下文详细说明。
possible_keys可能使用到的索引这里的索引只是可能会有到，实际不一定会用到。
key使用到的索引实际使用的索引。
key_len使用到索引的长度比如多列索引，只用到最左的一列，那么使用到索引的长度则为该列的长度，故该值不一定等于 key 列索引的长度。
ref谓词的关联信息当 join type 为 const、eq_ref 或者 ref 时，谓词的关联信息。可能为 ：null（非 const \ eq_ref \ ref join type 时）、const（常量）、关联的谓词列名。显示该表的索引字段关联了哪张表的哪个字段
rows扫描的行数该表格扫描到的行数。这里注意在mysql里边是嵌套链接，所以，需要把所有rows相乘就会得到查询数据行关联的次数
filtered实际显示行数占扫描rows的比例实际显示的行数 = rows * filtered / 100
extra特性使用

2、select_type

  • SIMPLE，简单查询方式，不使用UNION跟子查询；
  • PRIMARY，该表格位于最外层开始查询，通常会跟其他查询方式组合；
  • SUBQUERY，子查询内层查询的第一个SELECT，结果不依赖于外部查询结果集（不会被数据库引擎改写的情况）；
  • UNION，UNION 第一个SELECT 为PRIMARY，第二个及之后的所有SELECT 为 UNION SELECT TYPE；
  • UNION RESULT，每个结果集的取出来后，会做合并操作，这个操作就是 UNION RESULT；
  • DEPENDENT UNION，子查询中的UNION操作，从UNION 第二个及之后的所有SELECT语句的SELECT TYPE为 DEPENDENT UNION，这个一般跟DEPENDENT SUBQUERY一起结合应用，子查询中UNION 的第一个为DEPENDENT SUBQUERY；
  • DEPENDENT SUBQUERY，子查询中内层的第一个SELECT，依赖于外部查询的结果集；
  • DERIVED，查询使用内联视图；
  • MATERIALIZED，子查询物化，表出现在非相关子查询中，并且需要进行物化时会出现MATERIALIZED关键词；
  • UNCACHEABLE SUBQUERY，结果集无法缓存的子查询，需要逐次查询；
  • UNCACHEABLE UNION，表示子查询不可被物化，需要逐次运行。

3、type

性能排序：system->const->eq-ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。

  • system：表只有一行记录，相当于系统表。
  • const：通过索引一次就找到，只匹配一行数据。主键或者唯一索引的常量查询。
  • eq_ref：join查询中，关联条件为主键或者唯一索引。
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，用于=、<或>操作符带索引的列。
  • range：只检索给定范围的行，使用一个索引来选择行。一般使用between、<、>。
  • index：只遍历索引树。使用到索引，但不是索引查找
  • all：全表扫描，性能最差。

4、extra

重点关注以下几种情况：

  • Using filesort：MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。若出现有该值，应该优化SQL语句。
  • Using temporary：使用临时表保存中间结果，比如MySQL在对查询结果排序时使用临时表，常见于group by和order by。若出现有该值，应该优化SQL语句。
  • Using index：索引覆盖，不止用到索引，而且没有回表。避免了访问表的数据行，效率不错。

优化案例

SHOW INDEX FROM departments;
-- const
EXPLAIN SELECT * FROM departments WHERE dept_name = 'Development';

SHOW INDEX FROM employees;
-- eq_ref
EXPLAIN SELECT * FROM employees, dept_manager WHERE dept_manager.emp_no = employees.emp_no;

SHOW INDEX FROM dept_manager;
-- ref
EXPLAIN SELECT * FROM dept_manager WHERE dept_no = 'd007';

-- 最左前缀
SHOW INDEX FROM titles;
DESC titles;
EXPLAIN SELECT * FROM titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

EXPLAIN SELECT * FROM titles WHERE emp_no='10001';

EXPLAIN SELECT * FROM titles WHERE emp_no='10001' AND from_date='1986-06-26';

EXPLAIN SELECT * FROM titles WHERE from_date='1986-06-26';

EXPLAIN SELECT * FROM titles WHERE emp_no='10001' AND title LIKE 'Senior%';

EXPLAIN SELECT * FROM titles WHERE title LIKE 'Senior%' AND emp_no='10001';

-- range
DESC titles;
EXPLAIN SELECT * FROM titles WHERE emp_no < '10010' and title='Senior Engineer';

EXPLAIN SELECT * FROM titles WHERE emp_no < '10010' AND title='Senior Engineer' AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

-- 函数索引
EXPLAIN SELECT * FROM titles WHERE emp_no='10001' AND left(title, 6)='Senior';

EXPLAIN SELECT * FROM titles WHERE emp_no-1='10000';

-- order by
ALTER TABLE salaries ADD INDEX idx_from_date(from_date);
ALTER TABLE salaries DROP INDEX idx_from_date;

ALTER TABLE salaries ADD INDEX idx_from_salary(salary);
ALTER TABLE salaries DROP INDEX idx_from_salary;

ALTER TABLE salaries ADD INDEX idx_from_date_salary(from_date,salary);
ALTER TABLE salaries DROP INDEX idx_from_date_salary;

SHOW INDEX FROM salaries;
EXPLAIN SELECT * FROM salaries WHERE from_date <= '1990-10-18' ORDER BY salary;

-- transfer
ALTER TABLE employees ADD INDEX idx_last_name(last_name);

EXPLAIN SELECT * FROM employees WHERE last_name = 666;

-- 前缀索引
ALTER TABLE employees ADD INDEX `first_name_last_name4` (first_name, last_name(4));

SHOW INDEX FROM employees;

DESC employees;

EXPLAIN SELECT * FROM employees WHERE first_name='Eric' AND last_name='Anido';

-- NULL
ALTER TABLE salaries MODIFY to_date date NULL;

UPDATE salaries set to_date = NULL WHERE emp_no = '10012';

EXPLAIN SELECT to_date FROM titles WHERE to_date NOT IN ('9999-01-01');

SELECT COUNT(to_date) FROM salaries;

SELECT COUNT(*) FROM salaries;

SELECT * FROM salaries WHERE to_date is null;

-- int(11)
UPDATE salaries SET salary = 1234567890 WHERE salary = 1234567890;

优化口诀

全职匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用。
MySQL/Oracle数据库优化总结（非常全面）
Java仗剑走天涯
08-21 6万+
MySQL/Oracle数据库优化总结（非常全面）
深入剖析MySQL索引优化：提升数据库性能的核心技巧
热门推荐
张彦峰的博客
04-07 177万+
本文深入探讨了MySQL数据库中的索引机制，重点分析了B+树索引的原理及其在查询优化中的应用。通过讲解常见的索引类型及其优缺点，本文帮助读者理解如何根据不同的业务需求和查询场景选择合适的索引策略。文章还介绍了常见的索引优化技巧，如避免全表扫描、使用覆盖索引和定期维护索引，以提高数据库性能。最终，本文旨在帮助开发者掌握MySQL索引优化的关键方法，提升系统的响应速度和稳定性。
最全 MySQL 优化方法，从此优化不再难
taoing的博客
08-18 3031
作者：Wayne 链接：https://zhuanlan.zhihu.com/p/59818056 来源：知乎 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。 说起MySQL的查询优化，相信大家收藏了一堆奇技淫巧：不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型..... 你是否真的理解这些优化技巧？是否理解其背后的工作原理？在实际场景下性能真有提升吗？我想未必。因而理解这些优化建议背后的原理就尤为重要，希望本文能让你重新审视这些优化建议，并在.
MySQL优化的几种方法
疯一样的女子
05-29 2131
在sql查询中为了提高查询效率，我们常常会采取一些措施对查询语句进行sql优化，下面总结的一些方法，有需要的可以参考参考。 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null...
MySQL Join 的原理与优化实践
tatasix的博客
11-23 2173
Join 是数据库中最为重要且复杂的操作之一。在数据量较大的场景下，Join 的效率常常决定了整个查询的性能。本文旨在通过深入剖析 MySQL Join 的实现与优化，帮助读者理解其工作原理与实际应用中的性能优化策略。
MySQL事务机制深度解析：从原理到应用
张彦峰的博客
01-17 85万+
本文深入探讨了MySQL事务的基本概念和实现原理，重点分析了ACID原则、事务的隔离级别、以及InnoDB存储引擎如何通过MVCC、多版本并发控制、锁机制和日志文件保障事务的原子性、一致性、隔离性和持久性。通过对MySQL事务底层实现的详细解析，本文帮助读者理解事务管理在数据库设计和优化中的重要性，并为开发者提供了在高并发环境中保证数据一致性和可靠性的实用知识。
深入解析MySQL Join算法原理与性能优化实战指南
最新发布
意疏的博客
06-11 2749
希望你在阅读完本文后，能不仅“会写Join”，更“写得高效”，让数据库真正“跑得快”。
MySQL之优化SELECT语句
沉淀所学，分享所思，让热爱与成长同行。商务记录AI实战经验，助力开发者快速成长。 热爱AI，希望做出有影响力的技术成果。 技术点亮生活，分享连接价值与机会。 专注AI落地实战，陪你走好技术每一步。
07-31 2万+
本文主题为MySQL优化SELECT语句，涵盖了数据库性能提升概述，WHERE子句优化，范围优化和哈希联接优化。在数据库性能方面，需要考虑软件结构、CPU和I/O操作的最小化和高效执行。WHERE子句优化涉及改进查询的算法和可读性。范围优化介绍了优化器使用范围访问方法的条件和方法。哈希联接优化方面，MySQL在较新版本中使用哈希连接取代了块嵌套循环联接算法，从而提高查询速度。
mysql的limit原理_mysql limit 性能优化
weixin_30974667的博客
02-08 1462
起因需求：获取某用户的所有操作记录日志日志数量虽然不多，但不可能一股脑的塞给用户，难看不说，还拖累服务器性能，因而分页必不可少limit基础用法limit 的用法是 limit [offset], [rows]，其中 offset 表示偏移值， rows 表示需要返回的数据行。问题mysql 的 limit 给分页带来了极大的方便，但数据偏移量一大，limit 的性能就急剧下降。以下是两条查询语句...
MySQL 的执行原理
lssffy的博客
08-09 1227
MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），以其高性能、灵活性和易用性在Web开发和企业应用中广泛应用。理解MySQL的执行原理对于数据库优化、查询调优、性能提升以及问题排查至关重要。本文将深入探讨MySQL的执行原理，包括其架构、查询执行流程、存储引擎、索引机制、日志机制、事务管理、执行计划分析以及性能优化等内容。MySQL 的架构是一个典型的客户端/服务器结构，核心组件分为连接层、服务层和存储引擎层。
Mysql底层原理与优化技巧
06-14
总的来说，掌握MySQL的底层原理，理解存储引擎的工作方式，善用索引，优化SQL，关注数据库设计，熟悉JDBC连接池的管理，并结合性能监控工具，是实现企业级MySQL优化的关键。通过不断学习和实践，你将能更好地驾驭这...
MySQL架构及优化原理详解
02-24
MySQL逻辑架构整体分为三层:1>客户端:并非MySQL所独有，诸如:连接处理、授权认证、安全等功能均在这一层处理2>核心服务:包括查询解析、分析、优化、缓存、内置函数(比如:时间、数学、加密等函数)，所有的跨存储引擎...
MySQL原理和优化实战
02-03
### MySQL原理和优化实战 #### 一、课程概述与必备基础知识 本课程旨在深入解析MySQL数据库的内部原理，并提供一系列实战技巧，帮助学员理解和掌握如何有效地优化MySQL性能。课程不仅覆盖了MySQL的基础知识，还...
MySQL优化—Explain中Type和Extra的含义
04-30 1032
一、Explain前言 如之前的文章中所说，我们的SQL在交给执行引擎之前，是要经过查询优化器的。我们如何知道查询优化器是如何优化我们的SQL的，那就要用到explain这个关键字了。 常规的用explain查询出的字段意义，本文不会详说，可以参考之前的文章。本文重点讲述容易被我们忽略和混淆的一些重点。 二、show warnings 当我们在执行了explain之后，紧跟着执行show warn...
只需两步，教你使用Mybatis-Plus代码生成器
12-05 806
MybatisPlus代码生成 一、添加maven依赖 <!--Mybatis plus--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <vers...
MYSQL中的触发器
04-03 761
只能用于insert，delete与update语句。 只能用于实体表。 每个表最多支持6个触发器。 触发器失败，它后面的逻辑也无法执行。 在触发器执行的时候，存在一个名为NEW的虚拟表，和一个名为OLD的虚拟表。 NEW中的数据可以更新，OLD中的数据不可改变。 触发器有before和after，在语句执行前和后做一些附带的操作，before一般用作数据有效性的检查，after一般用作审计跟踪的场
Myisam中的锁实战—如何验证读写锁
04-28 394
一、Myisam中的锁 Myisam中有读锁与写锁。只有表锁，无行锁。 那么也就是其中只有表级的行锁与表级的写锁。 我们可以通过命令来模拟上锁与解锁的过程。 二、上锁 读锁： lock table a read; 写锁： lock table a write; 三、解锁 unlock tables; 四、查询锁 show open tables; show open tables where...
MySQL索引优化：原理与实战
本文将深入探讨MySQL数据库的优化方法，特别是关于索引的使用和原理，以提高查询效率。 首先，我们需要理解索引的作用。索引是数据库管理系统中用于加速数据检索的数据结构，它以B-树的形式存储，使得MySQL可以在不...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值