MySQL의 순서를 정말로 이해하고 있습니까?

이 기사는 mysql의 정렬에 대한 관련 지식을 제공합니다. 도움이 되기를 바랍니다.

정렬이라는 단어를 보면 거의 모든 앱에 정렬 위치가 있는 게 첫인상인데, 타오바오 상품은 구매 시간순으로, 스테이션B 댓글은 인기순으로 정렬되어 있는 것 같은데... 물론 저희는 그렇습니다. 오늘 이야기할 문제는 빅 데이터를 우아하게 정렬하는 방법이나 MySQL의 정렬 성능을 향상시키는 방법이 아닙니다.

MySQL의 경우 정렬하면 가장 먼저 떠오르는 것이 무엇인가요? 키워드 순서는? 필드별 정렬에 대한 색인을 갖는 것이 가장 좋습니까? 리프 노드는 이미 순차적입니까? 아니면 MySQL 내에서 정렬하지 않으려고 노력해야 합니까?

문제의 원인

이제 사용자의 친구 테이블이 있다고 가정해 보겠습니다.

CREATE TABLE `user` (
  `id` int(10) AUTO_INCREMENT,
  `user_id` int(10),
  `friend_addr` varchar(1000),
  `friend_name` varchar(100),  
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB;

현재 테이블에는 주의가 필요한 두 지점이 있습니다.

사용자의 user_id, 친구 이름 friend_name, 친구의 주소 friend_addr

user_id가 색인화되었습니다

어느 날 주니어 개발 엔지니어인 Xiao Yuan은 주니어 제품 관리자인 Xiao Wang으로부터 요청을 받았습니다.

Xiao Wang: Xiao Yuan 동지, 이제 이 기능은 사용자 ID를 기반으로 모든 친구의 이름과 주소를 확인하는 것을 지원하고 사전에 따라 친구의 이름을 정렬해야 합니다.

소위안: 좋습니다. 이 기능은 간단합니다. 바로 온라인에 접속하겠습니다.

그래서 Xiaoyuan은 다음 SQL을 작성했습니다.

select friend_name，friend_addr from user where user_id=? order by name

샤오위안은 순식간에 자랑스럽게 온라인에 접속했습니다. 어느 날 운영 동급생이 다음과 같은 쿼리를 발생시켰습니다.

select friend_name，friend_addr from user where user_id=10086 order by name

그러나 이 쿼리는 다음보다 훨씬 느린 것으로 나타났습니다. 데이터베이스는 느린 쿼리를 보고했습니다. 이때 작은 원숭이는 당황했습니다. 무슨 일이 일어나고 있는 걸까요? User_id에는 분명히 인덱스가 있으며 영리하게도 select * 대신에 select friend_name, friend_addr만 사용했습니다. 이때 꼬마 유인원은 침착해야 한다며 계속 위로를 하던 중 갑자기 explain 명령을 사용하여 SQL의 실행 계획을 확인하게 되었는데, 그 내용을 발견하게 되었습니다. 위험해 보이는 단어는 파일 정렬을 사용하는 것입니다.

"이 쿼리는 실제로 전설적인 파일 정렬을 사용하지만 친구가 많지 않은 사람이라면 파일 정렬을 사용해도 매우 빠를 것입니다. 이 user_id=10086에 친구가 많지 않으면 Xiaoyuan이 나중에 확인할 것입니다. .. 확인해 보니 이 사용자의 친구는 실제로 10만명이 넘었습니다~.

생각에 잠긴 꼬마 유인원은 '죄인이 정해져 있는 것 같은데, 10만 개의 데이터가 좀 큰데, 파일 정렬을 사용하는 정렬 원리는 무엇인가?'라고 생각했습니다.

파일 정렬의 해부학

어떤 사람들은 위의 문제가 10만 개의 데이터가 너무 크고, 정렬하지 않아도 느리다고 말할 수 있습니다. 이는 실제로 한 번에 10만 개의 데이터를 찾을 수 있다는 의미입니다. MySQL 메모리 버퍼 점유인지, 네트워크 대역폭 소모가 매우 큰데 1000이라는 제한을 추가하면 어떻게 될까요? 전체 데이터 패킷 크기가 작아졌기 때문에 네트워크 대역폭 문제는 확실히 해결되었지만 파일 정렬을 사용하는 문제는 여전히 해결되지 않았습니다. 이것을 보면 파일 정렬을 사용하면 파일이 정렬됩니까? 파일에서는 어떻게 정렬되어 있나요? 아니면 이렇게 묻습니다. 디자인하고 분류하라는 요청을 받은 경우 어떻게 처리하시겠습니까? 이러한 질문과 생각을 통해 파일정렬을 사용할 때 어떤 기술적인 어려움이 있고, 어떻게 해결해야 하는지 살펴보겠습니다.

• 우선 user_id가 인덱스되어 있으므로 먼저 user_id 인덱스 트리에서 대상 데이터, 즉 user_id=10086의 데이터를 검색하지만 쿼리하려는 것은 friend_name 및 friend_addr 필드입니다. 안타깝게도 user_id 인덱스만으로는 이 두 필드의 값을 찾을 수 없습니다

• 그래서 테이블로 돌아가서 user_id에 해당하는 기본 키를 통해 기본 키 인덱스 트리에서 검색해야 합니다. user_id=10086

  의 첫 번째 friend_name 및 friend_addr 필드

• 이제 무엇을 해야 할까요? friend_name을 정렬해야 하기 때문에 직접 반환하는 것은 확실히 잘못된 것입니다. 어떻게 정렬해야 합니까? 아직 데이터가 발견되지 않았기 때문에 먼저 찾은 데이터를 한 곳에 넣어두어야 합니다. 이 곳은 sort_buffer 입니다. 이름을 보면 짐작할 수 있을 것 같습니다. 네, 이 경우에는 sort_buffer 를 사용합니다. , 여기서는 각 스레드가 별도의 sort_buffer를 갖는다는 점에 유의해야 합니다. 이것의 주요 목적은 동일한 메모리에서 작동하는 여러 스레드로 인해 발생하는 잠금 경쟁 문제를 방지하는 것입니다.

• 첫 번째 데이터 조각의 friend_name과 friend_addr이 sort_buffer에 입력되면 물론 이것은 끝나지 않습니다. user_id=10086인 모든 friend_name과 friend_addr이 sort_buffer에 입력될 때까지 동기화 단계가 반복됩니다.

• sort_buffer에 데이터가 입력되었으며 이제 정렬할 차례입니다. 여기서 MySQL은 빠른 정렬 후 sort_buffer에 있는 friend_name이 순서대로 정렬됩니다.

• 마지막으로 첫 번째 항목을 반환합니다. sort_buffer 1000개 항목에서 끝납니다.

一切看起来很丝滑，但是 sort_buffer 占用的是内存空间，这就尴尬了，内存本身就不是无限大的，它肯定是有上限的，当然 sort_buffer 也不能太小，太小的话，意义不大。在 InnoDB 存储引擎中，这个值是默认是256K。

mysql> show variables  like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+

也就是说，如果要放进 sort_buffer 中的数据是大于256K的话，那么采用在 sort_buffer 中快排的方式肯定是行不通的，这时候，你可能会问：MySQL难道不能根据数据大小自动扩充吗？额，MySQL是多线程模型，如果每个线程都扩充，那么分给其他功能buffer就小了（比如change buffer等），就会影响其他功能的质量。

这时就得换种方式来排序了，没错，此时就是真正的文件排序了，也就是磁盘的临时文件，MySQL会采用归并排序的思想，把要排序的数据分成若干份，每一份数据在内存中排序后会放入临时文件中，最终对这些已经排序好的临时文件的数据再做一次合并排序就ok了，典型的分而治之原理，它的具体步骤如下：

• 先将要排序的数据分割，分割成每块数据都可以放到 sort_buffer 中

• 对每块数据在 sort_buffer 中进行排序，排序好后，写入某个临时文件中

• 当所有的数据都写入临时文件后，这时对于每个临时文件而言，内部都是有序的，但是它们并不是一个整体，整体还不是有序的，所以接下来就得合并数据了

• 假设现在存在 tmpX 和 tmpY 两个临时文件，这时会从 tmpX 读取一部分数据进入内存，然后从 tmpY 中读取一部分数据进入内存，这里你可能会好奇为什么是一部分而不是整个或者单个？因为首先磁盘是缓慢的，所以尽量每次多读点数据进入内存，但是不能读太多，因为还有 buffer 空间的限制。

• 对于 tmpX 假设读进来了的是 tmpX[0-5] ,对于 tmpY 假设读进来了的是 tmpY[0-5]，于是只需要这样比较：

如果 tmpX[0] < tmpY[0]，那么 tmpX[0] 肯定是最小的，然后 tmpX[1] 和 tmpY[0] 比较，如果 tmpX[1] > tmpY[0]，那么 tmpY[0] 肯定是第二小的...，就这样两两比较最终就可以把 tmpX 和 tmpY 合并成一个有序的文件tmpZ，多个这样的tmpZ再次合并...，最终就可以把所有的数据合并成一个有序的大文件。

文件排序很慢，还有其他办法吗

通过上面的排序流程我们知道，如果要排序的数据很大，超过 sort_buffer 的大小，那么就需要文件排序，文件排序涉及到分批排序与合并，很耗时，造成这个问题的根本原因是 sort_buffer 不够用，不知道你发现没有我们的 friend_name 需要排序，但是却把 friend_addr 也塞进了 sort_buffer 中，这样单行数据的大小就等于 friend_name 的长度 + friend_addr 的长度，能否让 sort_buffer 中只存 friend_name 字段，这样的话，整体的利用空间就大了，不一定用得到到临时文件。没错，这就是接下来要说的另一种排序优化rowid排序。

rowid 排序的思想就是把不需要的数据不要放到 sort_buffer 中，让 sort_buffer 中只保留必要的数据，那么你认为什么是必要的数据呢？只放 friend_name？这肯定不行，排序完了之后，friend_addr 怎么办？因此还要把主键id放进去，这样排完之后，通过 id 再回次表，拿到 friend_addr 即可，因此它的大致流程如下：

• 根据 user_id 索引，查到目标数据，然后回表，只把 id 和 friend_name 放进 sort_buffer 中

• 重复1步骤，直至全部的目标数据都在 sort_buffer 中

• 对 sort_buffer 中的数据按照 friend_name 字段进行排序

• 排序后根据 id 再次回表查到 friend_addr 返回，直至返回1000条数据，结束。

这里面其实有几点需要注意的：

• 这种方式需要两次回表的

• sort_buffer 虽然小了，但是如果数据量本身还是很大，应该还是要临时文件排序的

那么问题来了，两种方式，MySQL 该如何选择？得根据某个条件来判断走哪种方式吧，这个条件就是进 sort_buffer 单行的长度，如果长度太大（friend_name + friend_addr的长度），就会采用 rowid 这种方式，否则第一种，长度的标准是根据 max_length_for_sort_data 来的，这个值默认是1024字节：

mysql> show variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name          | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+

不想回表，不想再次排序

其实不管是上面哪种方法，他们都需要回表+排序，回表是因为二级索引上没有目标字段，排序是因为数据不是有序的，那如果二级索引上有目标字段并且已经是排序好的了，那不就两全其美了嘛。

没错，就是联合索引，我们只需要建立一个 （user_id，friend_name，friend_addr）的联合索引即可，这样我就可以通过这个索引拿到目标数据，并且friend_name已经是排序好的，同时还有friend_addr字段，一招搞定，不需要回表，不需要再次排序。因此对于上述的sql，它的大致流程如下：

• 通过联合索引找到user_id=10086的数据，然后读取对应的 friend_name 和 friend_addr 字段直接返回，因为 friend_name 已经是排序好的了，不需要额外处理

• 重复第一步骤，顺着叶子节点接着向后找，直至找到第一个不是10086的数据，结束。

联合索引虽然可以解决这种问题，但是在实际应用中切不可盲目建立，要根据实际的业务逻辑来判断是否需要建立，如果不是经常有类似的查询，可以不用建立，因为联合索引会占用更多的存储空间和维护开销。

总结

• 对于 order by 没有用到索引的时候，这时 explain 中 Extra 字段大概是会出现 using filesort 字眼

• 出现 using filesort 的时候也不用太慌张，如果本身数据量不大，比如也就几十条数据，那么在 sort buffer 中使用快排也是很快的

• 如果数据量很大，超过了 sort buffer 的大小，那么是要进行临时文件排序的，也就是归并排序，这部分是由 MySQL 优化器决定的

• 如果查询的字段很多，想要尽量避免使用临时文件排序，可以尝试设置下 max_length_for_sort_data 字段的大小，让其小于所有查询字段长度的总和，这样放入或许可以避免，但是会多一次回表操作

• 实际业务中，我们也可以给经常要查询的字段组合建立个联合索引，这样既不用回表也不需要单独排序，但是联合索引会占用更多的存储和开销

• 大量数据查询的时候，尽量分批次，提前 explain 来观察 sql 的执行计划是个不错的选择。

推荐学习：mysql视频教程

위 내용은 MySQL의 순서를 정말로 이해하고 있습니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!