MySQL の order by を本当に理解していますか?

この記事では、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;

現在、ポイントは 2 つあります。表内の注意点:

ユーザーの user_id、友達の名前、友達の名前、友達のアドレス、friend_addr

user_id はインデックスに登録されています

ある日、後輩がいました。開発エンジニアのシャオ ユアンは、ジュニア プロダクト マネージャーのシャオ ワンからリクエストを受け取りました:

シャオ ワン: シャオ ユアン同志、今度はバックグラウンドで関数を追加する必要があります。この関数は、ユーザー ID に基づいて友人全員の名前と住所を取得し、友人の名前を辞書に従って並べ替える必要があります。

シャオユアン: わかりました、この機能は簡単です。すぐにオンラインにします。

それで、Xiaoyuan は次の SQL を書きました:

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

一瞬の光の中で、Xiaoyuan は誇らしげにオンラインになりました。ある日手術が行われるまではすべてが順調でした。クラスメートはこのようなクエリを引き起こしました:

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

ただし、このクエリは通常よりもはるかに遅かったです。データベースはクエリが遅いと報告しました。このとき、子猿はパニックになりました。「何が起こっているの?」 User_id には明らかにインデックスがあり、賢明にも、select * の代わりに select friends_name、friend_addr のみを使用しました。このとき、猿は「落ち着かなければいけない」と自分を慰め続けていましたが、突然、Explain コマンドを思い出しました。SQL の実行計画を確認するには、Explain を使用します。Explain を使用すると、次のことがわかりました。ファイルソートを使用するという危険な単語でした。

「このクエリは実際に伝説的なファイル並べ替えを使用していますが、友達があまりいない人であれば、ファイル並べ替えを使用したとしても、非常に高速になるはずです。」この user_id=10086 に友達がたくさんいない限り、後で説明します。 Xiaoyuan が確認したところ、このユーザーには 100,000 人以上の友達がいることがわかりました。

物思いに耽っていた小さな猿は、次のように考えました。責任は決まっているようです。100,000 件のデータは少し大きいです。ファイルソートを使用する並べ替えの原理は何ですか?

解剖ファイルのソート

上記の問題は、100,000件のデータは大きすぎる、ソートしなくても遅いという人もいるかもしれませんが、これは実は理にかなっています。 MySQL のメモリ バッファ占有量とネットワーク帯域幅の消費量は両方とも非常に大きいため、1000 の制限を追加するとどうなるでしょうか?全体的なデータ パケット サイズが小さくなったため、ネットワーク帯域幅の問題は確実に解決されましたが、filesort を使用する問題はまだ解決されていません。これを見て、filesort を使用するとファイルがソートされるのか? という疑問が生じるかもしれません。ファイル内でどのようにソートされているのでしょうか?または、次のように尋ねます。デザインと分類を依頼されたら、どのように対処しますか?これらの質問や考えを踏まえて、filesort の使用に伴う技術的な問題とその解決方法を見てみましょう。

• まず第一に、user_id にはインデックスが付けられているため、まず user_id インデックス ツリー上のターゲット データ、つまり user_id=10086 のデータを取得します。クエリは、friend_name フィールドと Friend_addr フィールドです。残念ながら、これら 2 つのフィールドの値は、user_id インデックスだけでは見つかりません。

• そのため、テーブルに戻って検索する必要があります。 user_id に対応する主キーを介して主キー インデックス ツリー内で、 user_id=10086

• の最初の friends_name フィールドと friends_addr フィールドが見つかりました。次に何をすべきでしょうか? friends_name をソートする必要があるため、直接返すのは明らかに間違っています。ソート方法は?データはまだ見つかっていないので、まず見つかったデータを一か所に置く必要があります。ここがsort_bufferです。名前を見たら推測できると思います。はい、この場合ソートにはsort_bufferを使用します。バッファ, ここでは、各スレッドが個別の sort_buffer を持つことに注意してください。これの主な目的は、同じメモリ上で動作する複数のスレッドによって引き起こされるロック競合の問題を回避することです。

• データの最初の部分の friends_name と friends_addr が sort_buffer に入れられた時点で、これはもちろん終了ではありません。同期ステップは、最初のデータの friends_name と friends_addr がすべて格納されるまで繰り返されます。 user_id=10086 が入力されます。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]，那么 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 の order by を本当に理解していますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。