Verstehen Sie die Reihenfolge von MySQL wirklich?

Dieser Artikel vermittelt Ihnen relevantes Wissen über die Sortierung in MySQL. Ich hoffe, er wird Ihnen hilfreich sein.

Wenn es um die Wortsortierung geht, ist mein erster Eindruck, dass fast alle Apps einen Sortierplatz für Taobao-Produkte haben, der nach Kaufzeitpunkt sortiert ist, und Kommentare auf Station B sind nach Beliebtheit sortiert ... Natürlich, wir Es geht heute nicht darum, wie man unter Big Data elegant sortiert oder wie man die Sortierleistung verbessert. Sprechen wir über das Sortieren in MySQL.

Was fällt Ihnen beim Sortieren bei MySQL als Erstes ein? Keyword-Reihenfolge nach? Ist es am besten, einen Index für die Reihenfolge nach Feldern zu haben? Sind die Blattknoten bereits sequentiell? Oder sollten wir versuchen, nicht innerhalb von MySQL zu sortieren?

Die Ursache der Angelegenheit

Angenommen, es gibt die Freundestabelle eines Benutzers:

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;

Es gibt derzeit zwei Punkte in der Tabelle, die Aufmerksamkeit erfordern:

Die Benutzer-ID des Benutzers, der Name des Freundes, der Name des Freundes, der Name des Freundes Adresse freund_addr

Benutzer_ID ist indiziert

Eines Tages erhielt Xiao Yuan, ein Junior-Entwicklungsingenieur, eine Anfrage von Xiao Wang, einem Junior-Produktmanager:

Xiao Wang: Genosse Xiao Yuan, jetzt müssen wir eine Funktion hinzufügen Hintergrund: Diese Funktion erfordert die Überprüfung der Namen und Adressen aller seiner Freunde anhand der Benutzer-ID und erfordert, dass die Namen der Freunde nach dem Wörterbuch sortiert werden.

Little Yuan: Okay, diese Funktion ist einfach, ich gehe sofort online.

Also schrieb Xiaoyuan diese SQL:

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

Im Nu ging Xiaoyuan stolz online, bis eines Tages ein Klassenkamerad aus dem Bereich Operations eine solche Abfrage verursachte:

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

Diese Abfrage erwies sich jedoch als viel langsamer Üblicherweise meldete die Datenbank eine langsame Abfrage. Zu diesem Zeitpunkt geriet der kleine Affe in Panik: Was ist los? User_id hat offensichtlich einen Index, und geschickterweise habe ich nur select friends_name, friends_addr anstelle von select * verwendet. Der kleine Affe tröstete sich zu diesem Zeitpunkt immer wieder und sagte, er müsse ruhig sein, und dann fiel ihm plötzlich der Erklärungsbefehl ein, mit dem er den Ausführungsplan von SQL überprüfen konnte war ein gefährlich aussehendes Wort: Dateisortierung verwenden.

„Diese Abfrage verwendet tatsächlich die legendäre Dateisortierung, aber wenn eine Person nicht viele Freunde hat, sollte sie sehr schnell sein, selbst wenn sie die Dateisortierung verwendet.“ Sofern diese user_id=10086 nicht viele Freunde hat, wird Xiaoyuan sie später überprüfen Nach Überprüfung hat dieser Benutzer tatsächlich mehr als 100.000 Freunde.

Der gedankenverlorene kleine Affe dachte sich: Es scheint, dass die Schuld geklärt ist. Die 100.000 Daten sind etwas groß, und was ist das Sortierprinzip bei der Verwendung von Filesort?

Anatomie der Dateisortierung

Manche Leute sagen vielleicht, dass das oben genannte Problem darin besteht, dass 100.000 Daten zu groß sind und es langsam ist, selbst wenn sie nicht sortiert sind. Dies macht tatsächlich Sinn, wenn 100.000 Daten gleichzeitig ermittelt werden. Ob es an der Belegung des MySQL-Speicherpuffers liegt oder ob der Verbrauch an Netzwerkbandbreite sehr groß ist. Was passiert also, wenn ich ein Limit von 1000 hinzufüge? Das Problem der Netzwerkbandbreite wurde definitiv gelöst, da die Gesamtgröße der Datenpakete kleiner geworden ist, aber das Problem der Verwendung von Filesort ist immer noch nicht gelöst. Wenn Sie dies sehen, haben Sie möglicherweise Fragen: Funktioniert die Verwendung von Filesort beim Sortieren von Dateien? Wie sind sie in der Datei sortiert? Oder ich frage: Wenn ich für Sie entwerfen und sortieren würde, wie würden Sie damit umgehen? Lassen Sie uns anhand dieser Fragen und Gedanken einen Blick darauf werfen, welche technischen Schwierigkeiten mit der Verwendung von Filesort verbunden sind und wie diese gelöst werden können.

• Zuerst wird unsere Benutzer-ID indiziert, daher rufen wir zunächst unsere Zieldaten im Benutzer-ID-Indexbaum ab, dh die Daten von Benutzer-ID = 10086. Wir möchten jedoch die Felder „Freundname“ und „Freund_Adresse“ abfragen. Leider kann der user_id-Index die Werte dieser beiden Felder nicht finden

• Wir müssen also zur Tabelle zurückkehren und im Primärschlüsselindexbaum nach dem Primärschlüssel suchen, der user_id entspricht. OK, wir haben es gefunden die ersten Felder „friend_name“ und „friend_addr“ von user_id=10086

• Was jetzt tun? Es ist definitiv falsch, direkt zurückzukehren, da ich „friend_name“ sortieren muss. Die Daten wurden noch nicht gefunden, daher müssen Sie die gefundenen Daten zuerst an einem Ort ablegen: sort_buffer. Ich denke, Sie sollten es erraten, wenn Sie den Namen sehen. Ja, sort_buffer wird in diesem Fall verwendet Hierbei ist zu beachten, dass jeder Thread über einen separaten sort_buffer verfügt. Der Hauptzweck besteht darin, Sperrkonkurrenzprobleme zu vermeiden, die dadurch verursacht werden, dass mehrere Threads im selben Speicher arbeiten.

• Wenn der Freundname und die Freundadresse des ersten Datenelements in den Sortierpuffer gelegt wurden, ist dies natürlich noch nicht abgeschlossen. Die Synchronisierungsschritte werden wiederholt, bis alle Freundnamen und Freundadressen von user_id=10086 in den Sortierpuffer gelegt wurden .

• Die Daten in sort_buffer wurden eingegeben und es ist Zeit zu sortieren. Nach der schnellen Sortierung wird „friend_name“ in sort_buffer in der richtigen Reihenfolge angezeigt in sort_buffer 1000 Elemente, Ende.

一切看起来很丝滑，但是 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视频教程

Das obige ist der detaillierte Inhalt vonVerstehen Sie die Reihenfolge von MySQL wirklich?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!