Adakah anda benar-benar memahami pesanan MySQL oleh?

Artikel ini membawakan anda pengetahuan yang berkaitan tentang pesanan dengan menyusun dalam mysql saya harap ia akan membantu anda.

Tanggapan pertama saya tentang perkataan "isih" ialah hampir semua apl mempunyai tempat pengisihan produk Taobao diisih mengikut masa pembelian dan ulasan di Stesen B diisih mengikut populariti. Menyusun... Sudah tentu, apa yang kita bincangkan hari ini bukanlah cara menyusun secara elegan di bawah data besar atau cara meningkatkan prestasi pengisihan.

Untuk MySQL, apabila ia berkaitan dengan pengisihan, apakah perkara pertama yang terlintas di fikiran anda? Susunan kata kunci mengikut? Adakah yang terbaik untuk mempunyai indeks untuk pesanan mengikut medan? Adakah nod daun sudah berurutan? Atau patutkah kita cuba untuk tidak mengisih dalam MySQL?

Punca perkara itu

Sekarang andaikan terdapat jadual rakan pengguna:

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;

Pada masa ini terdapat dua dalam jadual Berikut adalah beberapa perkara yang perlu diberi perhatian:

Pengguna_id, nama rakan nama_kawan, alamat rakan rakan_addr

id_pengguna diindeks

Pada suatu hari, ada jurutera pembangunan junior Xiao Yuan, menerima permintaan daripada Xiao Wang, pengurus produk junior:

Xiao Wang: Rakan Xiao Yuan, sekarang kita perlu menambah fungsi di latar belakang nama semua rakannya berdasarkan ID pengguna dan alamat, dan meminta nama rakan disusun mengikut kamus.

Xiaoyuan: Okay, fungsi ini mudah, saya akan pergi ke dalam talian dengan segera.

Jadi Xiaoyuan menulis SQL ini:

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

Dalam sekelip mata, Xiaoyuan pergi dalam talian dengan bangganya sehingga suatu hari seorang rakan sekelas operasi membawa kepada pertanyaan seperti itu:

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

Walau bagaimanapun, pertanyaan ini adalah lebih perlahan daripada biasa. Pangkalan data melaporkan pertanyaan yang perlahan pada masa ini: Apa yang sedang berlaku. User_id jelas mempunyai indeks, dan dengan bijak saya hanya menggunakan pilih friend_name, friend_addr dan bukannya pilih *. Beruk kecil itu terus menghiburkan dirinya pada masa ini, mengatakan bahawa dia harus tenang, dan kemudian dia tiba-tiba memikirkan perintah explain Use explain to check the execution plan sql ialah perkataan yang kelihatan berbahaya: menggunakan filesort.

"Pertanyaan ini sebenarnya menggunakan pengisihan fail legenda, tetapi jika seseorang itu tidak mempunyai ramai rakan, walaupun dia menggunakan pengisihan fail, ia sepatutnya menjadi sangat pantas Melainkan user_id=10086 ini mempunyai ramai rakan, kemudian." Xiaoyuan pergi untuk menyemak dan mendapati bahawa pengguna ini mempunyai lebih daripada 100,000 rakan.

Beruk kecil, yang hilang akal, berfikir: Kesalahan nampaknya telah diperbaiki, 100,000 data agak besar, dan apakah prinsip pengisihan menggunakan filesort?

Isih fail anatomi

Sesetengah orang mungkin mengatakan bahawa masalah di atas ialah 100,000 data terlalu besar, dan ia perlahan walaupun tidak diisih Ini sebenarnya masuk akal boleh diketahui pada satu masa, sama ada penghunian penimbal memori MySQL dan penggunaan lebar jalur rangkaian adalah sangat besar Jadi bagaimana jika saya menambah had 1000? Masalah lebar jalur rangkaian pastinya telah diselesaikan, kerana saiz paket data keseluruhan telah menjadi lebih kecil, tetapi masalah menggunakan failsort masih tidak diselesaikan Melihat ini, anda mungkin mempunyai soalan, adakah menggunakan failsort menyusun fail? Bagaimanakah mereka diisih dalam fail? Atau saya bertanya ini: Jika saya mereka bentuk dan menyusun untuk anda, bagaimana anda akan menanganinya? Dengan soalan dan pemikiran ini, mari kita lihat apakah kesukaran teknikal yang terlibat dalam menggunakan failsort dan bagaimana untuk menyelesaikannya?

• Pertama sekali, user_id kami diindeks, jadi kami akan mendapatkan semula data sasaran kami pada pokok indeks user_id, iaitu data user_id=10086, tetapi apa yang kami mahu pertanyaan ialah medan friend_name dan friend_addr, malangnya, nilai kedua-dua medan ini tidak boleh didapati dengan bergantung pada indeks user_id sahaja

• Jadi kita perlu kembali ke jadual dan cari dalam pokok indeks kunci utama melalui kunci utama yang sepadan dengan user_id, ok , kami menemui medan friend_name dan friend_addr pertama

• user_id=10086 Apakah yang perlu kami lakukan sekarang? Ia pasti salah untuk kembali secara langsung, kerana saya perlu mengisih nama_teman. Data belum ditemui, jadi anda perlu meletakkan data yang ditemui di satu tempat dahulu , perlu diingatkan di sini bahawa setiap utas akan mempunyai sort_buffer yang berasingan Tujuannya adalah untuk mengelakkan masalah persaingan kunci yang disebabkan oleh berbilang utas yang beroperasi pada memori yang sama.

• Apabila friend_name dan friend_addr bagi sekeping data pertama telah dimasukkan ke dalam sort_buffer, ini sudah tentu langkah penyegerakan akan diulang sehingga semua friend_name dan friend_addr user_id=10086 telah dimasukkan. Ia tamat selepas memasukkan sort_buffer

• Data dalam sort_buffer telah dimasukkan, dan tiba masanya untuk mengisih Di sini MySQL akan melakukan pengisihan pantas pada friend_name. Selepas isihan pantas, data dalam sort_buffer friend_name adalah teratur

• Akhirnya kembalikan 1000 item pertama dalam sort_buffer dan tamat.

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

Atas ialah kandungan terperinci Adakah anda benar-benar memahami pesanan MySQL oleh?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!