Comprenez-vous vraiment l'ordre de MySQL ?

Cet article vous apporte des connaissances pertinentes sur la commande par tri dans MySQL. J'espère qu'il vous sera utile.

En ce qui concerne le tri des mots, ma première impression est que presque toutes les applications ont un lieu de tri. Les produits Taobao sont triés par heure d'achat, et les commentaires sur Station B sont triés par popularité... Bien sûr, nous. Nous en parlons aujourd'hui. Le problème n'est pas de savoir comment trier correctement les données volumineuses ou comment améliorer les performances de tri. Parlons du tri dans MySQL.

Pour MySQL, lorsqu'il s'agit de trier, quelle est la première chose qui vous vient à l'esprit ? Trier les mots clés par ? Est-il préférable d'avoir un index pour l'ordre par champ ? Les nœuds feuilles sont-ils déjà séquentiels ? Ou devrions-nous essayer de ne pas trier dans MySQL ?

La cause du problème

Supposons maintenant qu'il y ait une table d'amis d'un utilisateur :

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;

Il y a actuellement deux points dans la table qui nécessitent une attention particulière :

L'identifiant_utilisateur de l'utilisateur, le nom de l'ami nom_ami, le nom de l'ami l'adressefriend_addr

user_id est indexé

Un jour, Xiao Yuan, un ingénieur de développement junior, a reçu une demande de Xiao Wang, un chef de produit junior :

Xiao Wang : Camarade Xiao Yuan, maintenant nous devons ajouter une fonction dans le arrière-plan.Cette fonction nécessite qu'elle prenne en charge la vérification des noms et adresses de tous ses amis en fonction de l'ID utilisateur et nécessite que les noms des amis soient triés selon le dictionnaire.

Petit Yuan : D'accord, cette fonction est simple, je vais me connecter tout de suite.

Alors Xiaoyuan a écrit ce SQL :

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

Dans un éclair de lumière, Xiaoyuan s'est connecté fièrement. Tout s'est bien passé jusqu'au jour où un camarade de classe en opérations a provoqué une telle requête :

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

Cependant, cette requête s'est avérée beaucoup plus lente que celle-ci. habituel. La base de données a signalé une requête lente. À ce moment-là, le petit singe a paniqué : que se passe-t-il ? User_id a évidemment un index, et intelligemment, je n'ai utilisé que select friend_name,friend_addr au lieu de select *. Le petit singe n'arrêtait pas de se réconforter à ce moment-là, en disant qu'il devait être calme, puis il a soudainement pensé à la commande expliquer. Utilisez expliquer pour vérifier le plan d'exécution du SQL. Lorsque le petit singe a utilisé expliquer, il a trouvé cela là. était un mot dangereux : utiliser le tri de fichiers.

"Cette requête utilise en fait le tri de fichiers légendaire, mais si une personne n'a pas beaucoup d'amis, même si elle utilise le tri de fichiers, elle devrait être très rapide." À moins que cet user_id=10086 n'ait beaucoup d'amis, Xiaoyuan la vérifiera plus tard. . Après vérification, cet utilisateur a en réalité plus de 100 000 amis~.

Le petit singe, perdu dans ses pensées, pensa en lui-même : le blâme semble avoir été réglé, 100 000 données, c'est un peu gros, et quel est le principe de tri de l'utilisation du tri de fichiers ?

Anatomie du tri des fichiers

Certaines personnes peuvent dire que le problème ci-dessus est que 100 000 données sont trop volumineuses et qu'elles sont lentes même si elles ne sont pas triées. Cela a du sens. 100 000 données peuvent être trouvées en même temps. qu'il s'agisse de l'occupation de la mémoire tampon MySQL ou de la consommation de bande passante du réseau est très importante, et si j'ajoute une limite de 1000 ? Le problème de la bande passante du réseau a définitivement été résolu, car la taille globale des paquets de données est devenue plus petite, mais le problème de l'utilisation du tri de fichiers n'est toujours pas résolu. En voyant cela, vous vous demandez peut-être si l'utilisation du tri de fichiers trie les fichiers ? Comment sont-ils triés dans le dossier ? Ou je pose la question suivante : si on vous demandait de concevoir et de trier, comment vous y prendriez-vous ? Avec ces questions et réflexions, examinons quelles difficultés techniques sont impliquées dans l'utilisation du tri de fichiers et comment les résoudre ?

• Tout d'abord, notre user_id est indexé, nous allons donc d'abord récupérer nos données cibles sur l'arborescence d'index user_id, c'est-à-dire les données de user_id=10086, mais ce que nous voulons interroger, ce sont les champs friend_name etfriend_addr. Malheureusement, en se basant uniquement sur l'index user_id, nous ne pouvons pas trouver les valeurs de ces deux champs

• Nous devons donc revenir à la table et rechercher dans l'arborescence de l'index de clé primaire via la clé primaire correspondant à user_id. OK, nous avons trouvé. les premiers champsfriend_name etfriend_addr de user_id=10086

• Que faire maintenant ? C'est définitivement une erreur de revenir directement, car je dois trier mon_ami. Comment trier ? Les données n'ont pas encore été trouvées, vous devez donc d'abord placer les données trouvées au même endroit. Cet endroit est sort_buffer. Je pense que vous devriez le deviner lorsque vous voyez le nom. Oui, sort_buffer est utilisé pour le tri dans ce cas. , il convient de noter ici que chaque thread aura un sort_buffer distinct. L'objectif principal est d'éviter les problèmes de concurrence de verrouillage causés par plusieurs threads fonctionnant sur la même mémoire.

• Lorsque le nom_ami et l'adresse_ami de la première donnée auront été placés dans le tampon de tri, ce n'est bien sûr pas terminé. Les étapes de synchronisation seront répétées jusqu'à ce que tous les nom_ami et adresse_ami avec user_id=10086 soient placés dans le tampon de tri.

• Les données dans sort_buffer ont été insérées, et il est temps de trier. Ici, MySQL effectuera un tri rapide sur name_name. Après un tri rapide,friend_name dans sort_buffer sera en ordre

• Enfin, renvoyez le premier élément. dans sort_buffer 1000 éléments, fin.

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

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!