MySQL索引优化：分页和join连接优化-天涯论坛

j8typz 发表于 2024-8-4 15:36:38

MySQL索引优化：分页和join连接优化

这篇文案是MySQL索引优化的第三部分。前两篇文案能够看：
<a style="color: black;">怎样优化mysql索引-最左前缀原则案例详解</a>
<a style="color: black;">怎样优化 MySQL 索引：加强排序性能</a>
这篇文案的重点主题是关于优化 MySQL 中的分页和连接。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">分页优化</h1>
分页查找指的是从一个大的结果集中只返回一部分数据，常用于网站的数据展示、浏览等场景。在MySQL中，LIMIT关键字用于实现分页查找，其语法如下：SELECT * FROM table_name LIMIT offset , row_count;其中offset指定查找结果的初始位置，并row_count指定查找结果的条数。
要查找表的前10条记录，能够运用以下SQL语句：SELECT * FROMtable_nameLIMIT 0 , 10 ;要查找接下来的10条记录，能够运用以下SQL语句：SELECT * FROM table_name LIMIT 10 , 10 ;然则，在处理大型数据集时，分页可能会作为性能瓶颈，尤其是在运用 OFFSET 子句时，它必须在返回指定的记录子集之前扫描全部数据集。
看下面这个例子explain select * from Students order by name limit 100000,10;该SQL语句看似从表中检索10条记录，但实质上它从表中检索100010条记录，并丢弃前100000条记录以得到所需的10条记录。
因此呢，当进一步深入结果集时，该查找的性能会下降。
SQL能够优化如下：explain select * from Students t1 join
(select id from Students order by `name` limit 100000, 10) t2
on t1.id = t2.id;<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-axegupay5k/157f07dd008a47b2bac5bead0d9b46ba~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723347266&x-signature=XvqBtN%2FSHSKXa05dAAcis5RbkV4%3D" style="width: 50%; margin-bottom: 20px;"></div>
优化思路：首要运用覆盖索引的办法查找10条数据，而后用这10条数据进行连接查找。
这般查找的字段就完全被索引覆盖了
覆盖索引说明：基于MySQL数据结构，主键索引（InnoDB引擎）存储完整的记录，而辅助索引仅存储主键。MySQL 节点一般为 16KB，因此呢二级索引叶节点能够容纳更加多记录。扫描辅助索引比扫描主键索引必须更少的 I/O 操作。
优化前查找时间：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/785a3b274b65454db736634f1057d698~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723347266&x-signature=ETZnzQFSBgVr2WP%2BSxGwpzUqlV8%3D" style="width: 50%; margin-bottom: 20px;"></div>
优化后查找时间：
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/47e5cae78abb4524b7ba013c47306fcd~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723347266&x-signature=RsHoQ9LcNnjCT8%2BoilfeWzUIGZM%3D" style="width: 50%; margin-bottom: 20px;"></div>
其他优化办法包含：尽可能避免运用子查找，由于子查找会增多查找繁杂性和执行时间。相反，运用联接或表联接查找来替换子查找。对必须分页的字段添加索引，加强查找速度。分页时，运用limit和order by语句，并按照排序字段创建索引，加强排序效率。您能够运用缓存机制将分页结果缓存到缓存服务器中，这般能够减少许据库查找的次数。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">Join优化</h1>
Join操作是平常的数据库操作之一，但亦很容易引起性能问题。
Join查找是平常的数据库操作，但连接操作亦会遇到性能问题。连接查找可分为内连接、左连接和右连接。
执行操作时有两种状况Join：运用索引字段连接和不运用索引字段连接。
以下是两个表 t1 和 t2 的示例，其中a列有索引，而b列无索引：CREATE TABLE t1 (
id INT(11) NOT NULL AUTO_INCREMENT,
a INT(11) DEFAULT NULL,
b INT(11) DEFAULT NULL,
PRIMARY KEY (id),
INDEX idx_a (a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE t2 (
id INT(11) NOT NULL AUTO_INCREMENT,
a INT(11) DEFAULT NULL,
b INT(11) DEFAULT NULL,
PRIMARY KEY (id),
INDEX `idx_a` (`a`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO t1 (a, b) VALUES (1,1), (2,2), (3,3), (4,4), (5,5);
INSERT INTO t2 (a, b) VALUES (1,1), (2,2), (3,3), (4,4), (5,5);表t2的结构与t1完全相同，t1有16743行数据，t2有540行数据。explain select * from t1 inner join t2 on t1.a = t2.a;<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-6w9my0ksvp/86e477efe2e149d4b0f23f245ce6e900~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723347266&x-signature=eYpPq5v8E%2B0po7DqrTFWlmKmt%2Bk%3D" style="width: 50%; margin-bottom: 20px;"></div>
执行计划分析：t2对（540 条记录）执行全表扫描。运用idx_a连接t1和t2检索 t1中满足连接要求的行并将它们与t2 中的数据合并。将结果返回给客户端。
耗时估计：扫描t2表：540次扫描t1表：N次。此过程的时间繁杂度大约是O(1)能够运用索引定位的数据的时间繁杂度。内存中的比较次数：540 * 16743 = 9041220。
这个过程被叫作为基于块的块嵌套循环连接（BNL）算法。
该算法的原理能够理解为：将驱动表中的数据加载到连接缓冲区中，而后算法扫描驱动表并将驱动表的每一行与连接缓冲区中的数据进行比较。
运用BNL算法时，倘若连接缓冲区不足大，没法容纳驱动表中的所有数据，能够将表分成多个块，并将每一个块加载到连接缓冲区中进行比较。
这般能够避免join buffer不足的问题。这种办法叫作为 Block In-Memory Join 算法，或 Sort-Merge Join 算法的变体。
当t2表有海量行，例如1000行，但连接缓冲区一次只能容纳800行时，将运用一种叫作为“分段”办法的策略。前 800 行将被加载到连接缓冲区中，而后算法将扫描 t1 表并执行比较。之后，连接缓冲区将被清除，剩余的 200 行将被加载到缓冲区中。
而后算法将再次扫描 t1 表并执行另一次比较。
换句话说，倘若连接缓冲区在两次迭代中没法容纳所有必须的数据，算法将再次扫描 t1 表，并重复该过程，直到处理完所有必须的数据。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">JOIN的其他知识点</h1>
在JOIN查找中，通常有两种算法：NLJ 算法：运用索引字段进行连接。BNL 算法：运用非索引字段进行连接。
NLJ算法的性能优于BNL算法。
连接查找的优化办法：在连接列上添加索引能够帮忙MySQL选取NLJ算法，该算法一般比BNL算法更快、更有效。在连接查找中运用较小的表来驱动很强的表。运用BNL算法时，如有必要，能够增多内存中的连接缓冲区体积，以避免驱动表的多次扫描。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">为何非索引字段不适合嵌套循环连接 (NLJ) 算法？</h1>
这是由于NLJ算法采用磁盘扫描的方式：首要扫描驱动表并检索一行数据。而后，它运用该数据的相关字段在驱动表中查询相应的数据。此过程运用索引字段并且非常快。
然则，倘若运用此办法运用非索引字段，则必须全表扫描来查询驱动表中的相关数据，由于索引拜访办法不可用。
例如，倘若驱动表有100行数据，则驱动表必须扫描100次。倘若驱动表包括10,000行数据，那样就会有100*10,000=1,000,000次磁盘I/O操作，速度会非常慢。
<h1 style="color: black; text-align: left; margin-bottom: 10px;">MySQL 中 In 和 Exists 关键字的优化。</h1>
IN和EXISTS的优化仅有一个原则：用数据集较小的表来驱动数据集很强的表。
当B表的数据集少于A表的数据集时，IN 优先于EXISTS。select * from A where id in (select id from B)当A表的数据集少于B表的数据集时，EXISTS优先于IN，即数据集较小的表为外表。select * from A where exists (select 1 from B whereB.id = A.id)<h1 style="color: black; text-align: left; margin-bottom: 10px;">索引设计原则</h1>先写代码，而后按照状况创建索引。通常来讲，仅有代码写好之后，咱们才可清楚地判断那些字段会运用索引。尽可能让复合索引尽可能覆盖大部分业务。
 通常不意见在表上创建太多索引，由于守护索引亦必须性能开销。因此呢，意见尝试创建1 到3个覆盖业务中SQL查找要求的复合索引。不要在低基数列上创建索引。能够运用部分前缀创建长字符串的索引
 对长字段的部分字符串（例如 varchar(255)）创立索引能够减少索引运用的磁盘空间。例如，您只能索引前 20 个字符。但必须重视的是，这可能会引起排序失败，由于只使用了前 20 个字符，而索引只能保准粗略的次序。基于慢查找日志优化索引是一项连续的任务。
 因为业务需求的迭代，查找要求可能会出现变化。此时就能够按照慢SQL日志对索引进行连续优化。意见尽可能创建独一索引。当where要求与ORDER BY查找中存在冲突时，应优先为WHERE要求创建索引。
由于过滤掉数据后，剩下的数据集一般比较小，排序的成本并不高。因此呢，最好首要使用where子句上的索引来更快地过滤数据。
倘若爱好这篇文案，点赞支持一下，关注我第1时间查看更加多内容！

quintin 发表于 2024-8-31 07:40:29

seo常来的论坛，希望我的网站快点收录。

ejn2tr4200 发表于 2024-9-2 11:05:26

我们有着相似的经历，你的感受我深有体会。

nykek5i 发表于 2024-10-25 07:49:48

论坛是一个舞台，让我们在这里尽情的释放自己。

1fy07h 发表于 2024-10-29 00:06:13

这夸赞甜到心里，让我感觉温暖无比。

页: [1]

天涯论坛's Archiver

MySQL索引优化：分页和join连接优化