Bagaimana untuk menyelesaikan masalah paging dalam pertanyaan SQL dalam penalaan MySQL

1. Pengenalan masalah

Sebagai contoh, pada masa ini terdapat table test_user, dan kemudian masukkan 3 juta data ke dalam jadual ini:

CREATE TABLE `test_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_id` varchar(36) NOT NULL COMMENT '用户id',
  `user_name` varchar(30) NOT NULL COMMENT '用户名称',
  `phone` varchar(20) NOT NULL COMMENT '手机号码',
  `lan_id` int(9) NOT NULL COMMENT '本地网',
  `region_id` int(9) NOT NULL COMMENT '区域',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT;

Dalam proses pembangunan pangkalan data, kami sering menggunakan paging , teknologi terasnya ialah menggunakan had permulaan, kira pernyataan paging untuk membaca data.

Mari lihat masa pelaksanaan paging bermula dari 0, 10000, 100000, 500000, 1000000, dan 1800000 (100 item setiap halaman).

SELECT * FROM test_user LIMIT 0,100;         # 0.031
SELECT * FROM test_user LIMIT 10000,100;     # 0.047
SELECT * FROM test_user LIMIT 100000,100;    # 0.109
SELECT * FROM test_user LIMIT 500000,100;    # 0.219
SELECT * FROM test_user LIMIT 1000000,100;   # 0.547s
SELECT * FROM test_user LIMIT 1800000,100;   # 1.625s

Kami telah melihat bahawa apabila rekod permulaan meningkat, masa juga meningkat. Selepas menukar rekod permulaan kepada 2.9 juta, kita dapat melihat bahawa terdapat korelasi yang besar antara had dalam penyata halaman dan nombor halaman permulaan

SELECT * FROM test_user LIMIT 2900000,100; # 3.062s

Kami terkejut apabila mendapati titik permulaan paging MySQL apabila jumlah daripada data adalah besar Semakin besar nilai, semakin perlahan kelajuan pertanyaan!

Jadi kenapa situasi di atas berlaku?

Jawapan: Kerana sintaks menghadkan 2900000,100 sebenarnya bermakna mysql mengimbas 2900100 keping data pertama dan kemudian membuang 3000000 baris pertama sebenarnya satu pembaziran.

Kami juga boleh menyimpulkan dua perkara berikut daripada ini:

Masa pertanyaan pernyataan had adalah berkadar dengan kedudukan rekod permulaan.

Penyataan had MySQL sangat mudah, tetapi ia tidak sesuai untuk kegunaan langsung pada jadual dengan banyak rekod.

2. Hadkan penggunaan dalam MySQL

Klausa had boleh digunakan untuk memaksa pernyataan pilih untuk mengembalikan bilangan rekod yang ditentukan Format sintaks adalah seperti berikut:

had

SELECT * FROM 表名 limit m,n;
SELECT * FROM table LIMIT [offset,] rows;

menerima satu atau dua parameter angka Parameter mestilah pemalar integer Jika dua parameter diberikan:

Parameter pertama menentukan offset baris rekod pertama
yang kedua. Parameter menentukan bilangan maksimum baris rekod yang dikembalikan

2.1 m mewakili perolehan bermula daripada baris rekod m+1 dan n mewakili pengambilan n keping data. (m boleh ditetapkan kepada 0)

SELECT * FROM 表名 limit 6,5;

SQL di atas menunjukkan bahawa bermula dari baris rekod ke-7, 5 keping data akan dikeluarkan

2.2 Ia adalah perlu diperhatikan bahawa n tin ditetapkan kepada -1 Apabila n ialah -1, ia bermakna untuk memulakan pengambilan dari baris m+1 sehingga data terakhir diambil

SELECT * FROM 表名 limit 6,-1;

SQL di atas bermaksud untuk mengambil semua data selepas baris rekod ke-6

2.3 Jika hanya m diberikan, bermakna m rekod akan dikeluarkan bermula dari baris rekod pertama

SELECT * FROM 表名 limit 6;

2.4 Keluarkan yang teratas dalam susunan terbalik umur 3 baris

select * from student order by age desc limit 3;

2.5 Langkau 3 baris pertama dan kemudian ambil 2 baris lagi

select * from student order by age desc limit 3,2;

3. Strategi pengoptimuman halaman dalam

Kaedah 1 : Gunakan id kunci utama atau pengoptimuman indeks unik

iaitu, mula-mula cari id maksimum halaman terakhir, dan kemudian gunakan indeks pada id untuk bertanya:

SELECT * FROM test_user WHERE id>1000000 LIMIT 100; # 0.047秒

Gunakan SQL yang dioptimumkan ini berbanding dengan pertanyaan sebelumnya Ia sudah 11 kali lebih pantas. Selain menggunakan ID kunci utama, anda juga boleh menggunakan indeks unik untuk mencari data tertentu dengan cepat, dengan itu mengelakkan imbasan jadual penuh. Berikut ialah kod pengoptimuman SQL yang sepadan untuk membaca data dengan kunci unik (pk) dalam julat 1000 hingga 1019:

SELECT * FROM 表名称 WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

Sebab: Imbasan indeks akan menjadi sangat pantas.

Senario yang berkenaan: Jika pertanyaan data diisih mengikut pk atau id, dan semua data tidak hilang, anda boleh mengoptimumkan dengan cara ini, jika tidak, operasi halaman akan membocorkan data.

Kaedah 2: Gunakan pengoptimuman liputan indeks

Kita semua tahu bahawa jika pernyataan yang menggunakan pertanyaan indeks hanya mengandungi lajur indeks itu (iaitu, liputan indeks ), Kemudian keadaan ini akan dipersoalkan dengan cepat.

Mengapa pertanyaan liputan indeks begitu pantas?

Jawapan: Oleh kerana terdapat algoritma pengoptimuman untuk carian indeks dan data berada pada indeks pertanyaan, tidak perlu mencari alamat data yang berkaitan, yang menjimatkan banyak masa. Apabila jumlah concurrency tinggi, Mysql juga menyediakan cache yang dikaitkan dengan indeks Penggunaan sepenuhnya cache ini boleh mencapai hasil yang lebih baik.

Memandangkan medan id ialah kunci utama dalam test_user jadual ujian kami, indeks kunci utama disertakan secara lalai. Sekarang mari kita lihat prestasi pertanyaan yang menggunakan indeks penutup.

Kali ini kami menanyakan data daripada baris 1000001 hingga 1000100 (menggunakan indeks penutup, termasuk hanya lajur id):

SELECT id FROM test_user LIMIT 1000000,100; # 0.843秒

Daripada keputusan ini, kami mendapati kelajuan pertanyaan lebih perlahan daripada kelajuan imbasan jadual penuh (Sudah tentu, selepas berulang kali melaksanakan SQL ini, kelajuan menjadi lebih pantas selepas berbilang pertanyaan, menjimatkan hampir separuh masa. Ini disebabkan oleh caching, kemudian gunakan perintah explain untuk melihat pelan pelaksanaan SQL dan mendapati bahawa pelaksanaan SQL Menggunakan indeks biasa idx_user_id :

EXPLAIN SELECT id FROM test_user LIMIT 1000000,100;

Jika kita memadamkan indeks biasa, indeks kunci utama akan digunakan apabila melaksanakan atas SQL. Jika kita tidak memadam indeks biasa, dalam kes ini, jika kita mahu SQL di atas menggunakan indeks kunci utama, kita boleh menggunakan perintah mengikut pernyataan:

SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,100; # 0.250秒

Kemudian jika kita juga ingin menanyakan semua lajur , terdapat dua kaedah , satu adalah dalam bentuk id>=, dan satu lagi adalah menggunakan join.

Cara penulisan pertama:

SELECT * FROM test_user WHERE ID >= (SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,1) LIMIT 100;

Masa pertanyaan SQL di atas ialah 0.281 saat

Cara penulisan kedua:

SELECT * FROM (SELECT id FROM test_user ORDER BY id ASC LIMIT 1000000,100) a LEFT JOIN test_user b ON a.id = b.id;

Pertanyaan SQL di atas masa ialah 0.252 saat

Kaedah 3: Susun semula berdasarkan indeks

Di mana pageNum mewakili nombor halaman, dan nilainya bermula dari 0 pageSize mewakili bilangan keping data bagi setiap halaman.

SELECT * FROM 表名称 WHERE id_pk > (pageNum*pageSize) ORDER BY id_pk ASC LIMIT pageSize;

适应场景：

• 适用于数据量多的情况

• 最好ORDER BY后的列对象是主键或唯一索引

• id数据没有缺失，可以作为序号使用

• 使用ORDER BY操作能利用索引被消除，但结果集是稳定的

原因：

• 索引扫描，速度会很快

• 但MySQL的排序操作，只有ASC没有DESC。在MySQL中，索引的存储顺序是升序ASC，没有降序DESC的索引。这就是为什么默认情况下，order by 是按照升序排序的原因

方法四：基于索引使用prepare

PREPARE预编译一个SQL语句，并为其分配一个名称 stmt_name，以便以后引用该语句，预编译好的语句用EXECUTE执行。 

PREPARE stmt_name FROM 'SELECT * FROM test_user WHERE id > ? ORDER BY id ASC LIMIT ?';
SET @a = 1000000;
SET @b = 100;
EXECUTE stmt_name USING @a, @b;;

上述SQL查询时间为0.047秒。 

对于定义好的PREPARE预编译语句，我们可以使用下述命令来释放该预编译语句：

DEALLOCATE PREPARE stmt_name;

原因：

• 索引扫描,速度会很快.

• prepare语句又比一般的查询语句快一点。

方法五：利用"子查询+索引"快速定位数据 

其中page表示页码，其取值从0开始；pagesize表示指的是每页多少条数据。 

SELECT * FROM your_table WHERE id <= (SELECT id FROM your_table ORDER BY id DESC LIMIT ($page-1)*$pagesize ORDER BY id DESC LIMIT $pagesize);

方法六：利用复合索引进行优化

假设数据表 collect ( id, title ,info ,vtype) 就这4个字段，其中id是主键自增，title用定长，info用text, vtype是tinyint，vtype是一个普通索引。

现在往里面填充数据，填充10万条记录，数据库表占用硬1.6G。

select id,title from collect limit 1000,10;

执行上述SQL速度很快，基本上0.01秒就OK。

select id,title from collect limit 90000,10;

然后再执行上述SQL，就发现非常慢，基本上平均8~9秒完成。

这个时候如果我们执行下述，我们会发现速度又变的很快，0.04秒就OK。

select id from collect order by id limit 90000,10;

那么这个现象的原因是什么？

答案：因为用了id主键做索引,  这里实现了索引覆盖，当然快。

所以如果想一起查询其它列的话，可以按照索引覆盖进行优化，具体如下：

select id,title from collect where id >= (select id from collect order by id limit 90000,1) limit 10;

再看下面的语句，带上where 条件：

select id from collect where vtype=1 order by id limit 90000,10;

可以发现这个速度上也是很慢的，用了8~9秒！

这里有一个疑惑：vtype 做了索引了啊？怎么会慢呢？

vtype做了索引是不错，如果直接对vtype进行过滤：

select id from collect where vtype=1 limit 1000,10;

可以看到速度还是很快的，基本上0.05秒，如果从9万开始，那就是0.05*90=4.5秒的速度了。

其实加了 order by id 就不走索引，这样做还是全表扫描，解决的办法是：复合索引！

因此针对下述SQL深度分页优化时可以加一个search_index(vtype,id)复合索引：

select id from collect where vtype=1 order by id limit 90000,10;

综上： 

• 在进行SQL查询深度分页优化时，如果对于有where条件，又想走索引用limit的，必须设计一个索引，将where放第一位，limit用到的主键放第二位，而且只能select 主键。

• 最后根据查询出的主键走一级索引找到对应的数据。

• 按这样的逻辑，百万级的limit 在0.0x秒就可以分完，完美解决了分页问题。

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