Bagaimana untuk menyelesaikan masalah paging dalam mysql

Artikel ini membawa anda pengetahuan yang berkaitan tentang mysql Ia terutamanya memperkenalkan penyelesaian elegan kepada masalah paging dalam mysql Artikel ini akan membincangkan cara mengoptimumkan paging dalam apabila jadual mysql mempunyai jumlah data yang besar . Masalah penomboran, dan dilampirkan ialah kod pseudo bagi kes mengoptimumkan masalah SQL yang perlahan, saya harap ia akan membantu semua orang.

Pembelajaran yang disyorkan: tutorial video mysql

Dalam proses pembangunan permintaan harian, saya percaya semua orang akan biasa dengan had, tetapi menggunakan had, apabila offset (offset) sangat besar, anda akan mendapati kecekapan pertanyaan semakin perlahan dan perlahan. Apabila had ialah 2000 pada permulaan, ia mungkin mengambil masa 200ms untuk menanyakan data yang diperlukan Walau bagaimanapun, apabila had adalah 4000 mengimbangi 100000, anda akan mendapati bahawa kecekapan pertanyaannya sudah memerlukan kira-kira 1S lebih teruk dan lambat.

Ringkasan

Artikel ini akan membincangkan cara mengoptimumkan masalah paging dalam apabila jadual mysql mempunyai jumlah data yang besar, dan melampirkan pseudokod bagi kes terkini untuk mengoptimumkan masalah sql perlahan.

1. Hadkan huraian masalah paging dalam

Mari kita lihat struktur jadual dahulu (sekadar beri contoh, struktur jadual tidak lengkap dan medan yang tidak berguna tidak akan dipaparkan)

CREATE TABLE `p2p_detail_record` (
  `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主键',
  `batch_num` int NOT NULL DEFAULT '0' COMMENT '上报数量',
  `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上报时间',
  `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '会议id',
  `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '开始时间',
  `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '应答时间',
  `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '结束时间',
  `duration` int NOT NULL DEFAULT '0' COMMENT '持续时间',
  PRIMARY KEY (`id`),
  KEY `idx_uuid` (`uuid`),
  KEY `idx_start_time_stamp` (`start_time_stamp`) //索引,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='p2p通话记录详情表';

Andaikan SQL paging dalam yang ingin kami tanyakan kelihatan seperti ini

select * 
from p2p_detail_record ppdr 
where ppdr .start_time_stamp >1656666798000 
limit 0,2000

Kecekapan pertanyaan ialah 94ms. Bukankah ia pantas? Jadi jika kita mengehadkan 100000 atau 2000, kecekapan pertanyaan ialah 1.5S, yang sudah sangat perlahan Bagaimana jika ada lagi?

2 Analisis punca slow sql

Mari kita lihat pelan pelaksanaan ini sql

juga telah mencapai indeks, jadi mengapa ia masih perlahan? Mari kita semak semula mata pengetahuan mysql yang berkaitan.

Indeks berkelompok dan indeks bukan berkelompok

Indeks berkelompok: Nod daun menyimpan seluruh baris data.

Indeks bukan berkelompok: Nod daun menyimpan nilai kunci utama yang sepadan dengan keseluruhan baris data.

Proses menggunakan pertanyaan indeks bukan berkelompok

• Cari nod daun yang sepadan melalui indeks bukan berkelompok tree , dapatkan nilai kunci utama.
• kemudian mendapatkan semula nilai kunci utama dan kembali ke pokok indeks berkelompok untuk mencari keseluruhan baris data yang sepadan. (Keseluruhan proses dipanggil pulangan jadual)

Berbalik kepada persoalan mengapa SQL ini lambat, sebabnya adalah seperti berikut

1 pernyataan akan mengimbas n baris pertama offset, kemudian membuang baris offset pertama dan mengembalikan n baris data seterusnya. Dalam erti kata lain, limit 100000,10 akan mengimbas 100010 baris, manakala limit 0,10 hanya akan mengimbas 10 baris. Di sini kita perlu kembali ke jadual 100010 kali, dan banyak masa dihabiskan untuk mengembalikan jadual.

Idea teras ​​penyelesaian: Bolehkah anda mengetahui terlebih dahulu ID kunci utama yang mana untuk dimulakan dan mengurangkan bilangan pemulangan jadual

Penyelesaian biasa

Melalui subqueries Optimize

select * 
from p2p_detail_record ppdr 
where id >= (select id from p2p_detail_record ppdr2 where ppdr2 .start_time_stamp >1656666798000 limit 100000,1) 
limit 2000

Hasil pertanyaan yang sama juga merupakan item ke-2000 bermula daripada 100,000 item Kecekapan pertanyaan ialah 200ms, yang jauh lebih pantas.

Kaedah rakaman tag

Kaedah rakaman tag: Sebenarnya, tandakan item mana yang anda semak kali terakhir, dan semak lagi lain kali Apabila tiba masanya, mulakan imbasan dari bar ini ke bawah. Serupa dengan penanda halaman

select * from p2p_detail_record ppdr
where ppdr.id > 'bb9d67ee6eac4cab9909bad7c98f54d4'
order by id 
limit 2000

备注：bb9d67ee6eac4cab9909bad7c98f54d4是上次查询结果的最后一条ID

Menggunakan kaedah rakaman tag, prestasi akan menjadi baik kerana indeks id dipukul. Tetapi pendekatan ini mempunyai beberapa kelemahan.

• 1. Anda hanya boleh membuat pertanyaan pada halaman berturut-turut, bukan merentasi halaman.
• 2. Medan serupa dengan yang secara berterusan meningkat diperlukan (orber mengikut id boleh digunakan).

Perbandingan penyelesaian

• Menggunakan untuk mengoptimumkan melalui subqueries

Kelebihan: Anda boleh membuat pertanyaan merentasi halaman dan anda boleh menyemak data pada mana-mana halaman yang anda ingin semak.

Kelemahan: tidak secekap kaedah rakaman tag. Sebab: Contohnya, selepas anda perlu menyemak 100,000 keping data, anda juga perlu menanyakan sekeping data ke-1000 yang sepadan dengan indeks bukan berkelompok dahulu, dan kemudian dapatkan ID bermula dari ke-100,000 sekeping untuk pertanyaan.

• Menggunakan kaedah rakaman tag

Kelebihan: Kecekapan pertanyaan adalah sangat stabil dan sangat pantas.

Kelemahan:

• 不跨页查询，
• 需要一种类似连续自增的字段

关于第二点的说明： 该点一般都好解决，可使用任意不重复的字段进行排序即可。若使用可能重复的字段进行排序的字段，由于mysql对于相同值的字段排序是无序，导致如果正好在分页时，上下页中可能存在相同的数据。

实战案例

需求： 需要查询查询某一时间段的数据量，假设有几十万的数据量需要查询出来，进行某些操作。

需求分析 1、分批查询（分页查询），设计深分页问题，导致效率较慢。

CREATE TABLE `p2p_detail_record` (
  `id` varchar(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '主键',
  `batch_num` int NOT NULL DEFAULT '0' COMMENT '上报数量',
  `uptime` bigint NOT NULL DEFAULT '0' COMMENT '上报时间',
  `uuid` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '会议id',
  `start_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '开始时间',
  `answer_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '应答时间',
  `end_time_stamp` bigint NOT NULL DEFAULT '0' COMMENT '结束时间',
  `duration` int NOT NULL DEFAULT '0' COMMENT '持续时间',
  PRIMARY KEY (`id`),
  KEY `idx_uuid` (`uuid`),
  KEY `idx_start_time_stamp` (`start_time_stamp`) //索引,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='p2p通话记录详情表';

伪代码实现：

//最小ID 
String  lastId = null; 
//一页的条数 
Integer pageSize = 2000; 
List<P2pRecordVo> list ;
do{   
   list = listP2pRecordByPage(lastId,pageSize);    //标签记录法，记录上次查询过的Id 
   lastId = list.get(list.size()-1).getId();       //获取上一次查询数据最后的ID，用于记录
   //对数据的操作逻辑
   XXXXX();
 }while(isNotEmpty(list));
   
<select id ="listP2pRecordByPage">  
   select * 
   from p2p_detail_record ppdr where 1=1
   <if test = "lastId != null">
   and ppdr.id > #{lastId}
   </if>
   order by id asc
   limit #{pageSize}
</select>

这里有个小优化点: 可能有的人会先对所有数据排序一遍，拿到最小ID，但是这样对所有数据排序，然后去min（id),耗时也蛮长的，其实第一次查询，可不带lastId进行查询，查询结果也是一样。速度更快。

总结

1、当业务需要从表中查出大数据量时，而又项目架构没上ES时，可考虑使用标签记录法的方式，对查询效率进行优化。

2、从需求上也应该尽可能避免，在大数据量的情况下，分页查询最后一页的功能。或者限制成只能一页一页往后划的场景。

推荐学习：mysql视频教程

Atas ialah kandungan terperinci Bagaimana untuk menyelesaikan masalah paging dalam mysql. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!