首页 > 数据库 >mysql千万条据下的分页

mysql千万条据下的分页

时间:2023-08-01 15:32:15浏览次数:40  
标签:goods 20 分页 千万条 -- user mysql id SELECT

1.1. 背景

对于开发来说,分页功能碰到的频率还是算蛮高的,基本上在每个模块中都需要都会遇到列表分页的功能。他们实现的都很快,因为基本上只要把之前的代码改改就OK了。他们的实现基本是是如下语句:



SELECT * FROM goods WHERE user_id = 4 LIMIT 1000 , 20 ;
. . . omit . . .
20 rows in set ( 0.11 sec )

像这样的语句对数据量小或偏移量小的时候是十分快的。但是当数据量大并且偏移量大的时候就会有问题了。如下:



SELECT * FROM goods WHERE user_id = 4 LIMIT 500000 , 20 ;
. . . omit . . .
20 rows in set ( 7.84 sec )

为什么会这样就不说了。下面给出优化的过程。


1.2. 构建数据


--创建商品表

DROP TABLE IF EXISTS goods ;
CREATE TABLE goods (
   id bigint ( 20 ) unsigned NOT NULL AUTO_INCREMENT ,
   good_name VARCHAR ( 50 ) NOT NULL ,
   user_id TINYINT unsigned NOT NULL ,
   PRIMARY KEY ( ` id ` )
) ;

--创建批量添加数据存储过程

--下面创建数据可能需要一点时间

DROP PROCEDURE IF EXISTS insert_batch ;
DELIMITER //
CREATE PROCEDURE insert_batch ( )
BEGIN
   DECLARE num INT ;
   DECLARE user_id TINYINT ;
   SET num = 1 ;
   WHILE num <= 100000 DO
     SELECT FLOOR ( RAND ( ) * 10 + 1 ) INTO user_id ;
     INSERT INTO goods VALUES ( NULL , REPEAT ( 'X' , 50 ) , user_id ) ;
     SET num = num + 1 ;
   END WHILE ;
   SET num = 1 ;
   WHILE num <= 7 DO
     INSERT INTO goods SELECT NULL , good_name , user_id FROM goods ;
     SET num = num + 1 ;
   END WHILE ;
 
END //

DELIMITER ;

--调用存储过程

CALL insert_batch ( ) ;

--添加索引

ALTER TABLE goods
ADD INDEX idx $ goods $ user_id ( user_id ) ;
 
SELECT user_id , COUNT ( * ) FROM goods GROUP BY user_id ;
+ -- -- -- -- - + -- -- -- -- -- +
| user_id | COUNT ( * ) |
+ -- -- -- -- - + -- -- -- -- -- +
|        1 |      10089 |
|        2 |      10077 |
|        3 |      9944 |
|        4 | 12710074 |
|        5 |      10011 |
|        6 |      9925 |
|        7 |      9950 |
|        8 |      10149 |
|        9 |      9949 |
|        10 |      9832 |
+ -- -- -- -- - + -- -- -- -- -- +

这边我们以数据最多的user_id=4的记录来模拟


1.3. 优化规则

让所有结果集数据最小化。如果是临时表,还是行数据还是列数据都让结果最小化,还有就是临时结果集尽量不走主键索引,走二级索引。


1.4. 模拟

现在我们需要查询用户4在10000000后20条数据


1、通过user_id找到主键ID(让列结果最小化)


SELECT id FROM goods WHERE user_id = 4 LIMIT 10000000 , 20 ;
10343427
. . . omit . . .
10343446
20 rows in set ( 1.83 sec )

2、通过获得的主键ID寻找需要的数据,这边我就不使用python来演示了。在程序里面就需要拼出IN里面的条件。


SELECT *
FROM goods
WHERE id IN (
   10343427 , 10343428 , 10343429 , 10343430 , 10343431 ,
   10343432 , 10343433 , 10343434 , 10343435 , 10343436 ,
   10343437 , 10343438 , 10343439 , 10343440 , 10343441 ,
   10343442 , 10343443 , 10343444 , 10343445 , 10343446
) ;
+ -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- - +
| id        | good_name                                            | user_id |
+ -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- - +
| 10343427 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |        4 |
. . . omit . . .
20 rows in set ( 0.01 sec )


1.5. 进一步优化

其实上面我们还能让结构级变少。来看下面列表简图:




在网页的分页按钮基本省都是一个连接,或者通过jquery时间分页。我们可以在按钮上添加两个属性参数为max_id和min_id。分别记录的是当前页数据的最小ID和最大ID(如:min_max=10343427、max_id=10343446)。


查找数据如下:


1、通过user_id找到主键ID(让列结果最小化)


如果是点击下一页



SELECT id FROM goods WHERE id > 10343446 AND user_id = 4 LIMIT 0 , 20 ;
+ -- -- -- -- -- +
| id        |
+ -- -- -- -- -- +
| 10343447 |
. . . omit . . .
20 rows in set ( 0.02 sec )

如果是点击上一页(上一页会比下一页性能来的差一点,因为有用到排序)



SELECT id FROM goods WHERE id < 10343427 AND user_id = 4
ORDER BY id DESC
LIMIT 0 , 20 ;

2、通过获得的主键ID寻找需要的数据


SELECT *
FROM goods
WHERE id IN (
   10343447 , 10343448 , 10343449 , 10343450 , 10343451 ,
   10343452 , 10343453 , 10343454 , 10343455 , 10343456 ,
   10343457 , 10343458 , 10343459 , 10343460 , 10343461 ,
   10343462 , 10343463 , 10343464 , 10343465 , 10343466
) ;
+ -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- - +
| id        | good_name                                            | user_id |
+ -- -- -- -- -- + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + -- -- -- -- - +
| 10343447 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX |        4 |
. . . omit . . .
20 rows in set ( 0.01 sec )


1.6. 总结

这种优化可能在一些使用到聚合函数的排序的情况下没法使用。


在这边鼓励使用MySQL的尽量使用比较简单的语句,不使用JOIN。因为优化器对简单的语句解析的很快,而且在维护的角度来说越白痴的语句越让人容易明白。


当然,强烈反对在程序中 for 循环取数据库。


标签:goods,20,分页,千万条,--,user,mysql,id,SELECT
From: https://blog.51cto.com/u_6186189/6922900

相关文章

  • SQL总结-MySQL索引使用和优化技巧
    本文将全面介绍MySQL索引的使用技巧,并提供多种优化索引的方法,帮助读者提高数据库查询性能。MySQL索引基础知识索引的工作原理索引就像书的目录,可以帮助MySQL快速定位数据,从而加速查询。索引类型MySQL支持多种索引类型:B树索引(B-Tree):最常见的索引类型,可以对值进行排......
  • 理解MySQL——索引与优化
    写在前面:索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录。如果没有索引,查询将对整个表进行扫描,最坏的情况下,如果所有数据页都不在内存,需要读取10^4个页面,如果这10^4个页......
  • 智慧校园源码:vue2+Java+springboot+MySQL+elmentui+jpa+jwt
    智慧校园综合管理云平台源码系统主要以校园安全、智慧校园综合管理云平台为核心,以智慧班牌为学生智慧之窗,以移动管理平台、家校沟通为辅。教师—家长一学校—学生循环的无纸化管理模式及教学服务,实现多领域的信息互联互通以及校园管理一体化、信息数据化、数据自动化。智慧班牌融合......
  • 分页批量请求数据封装
    关于淘宝接口每次最多取100个,需要分页获取的封装总体思路采取迭代器的方式来多次发送请求. TOPCollection<TOPArticleOrder>orders=session.getArticleOrdersByNick(articleCode,start,end,nick);size=saveOrders(orders.toList()); 最终调用TOPCollection中的toList()......
  • 导入mysql 乱码问题及 Linux 中的文件格式转换
    问题下载了一个单词sql文件,导入mysql报错,查看文件类型:%fileenglish_word.sqlenglish_word.sql:Non-ISOextended-ASCIItext与另一个导入成功的文件对比,发现格式不一样:%fileenglish-root.sqlenglish-root.sql:UTF-8Unicodetext,withverylonglines,withnoli......
  • MySQL8压缩包安装教程
    解压缩包配置环境变量初始化mysqld--initialize-insecure安装服务mysqldinstallMySQL8移除服务mysqldremoveMySQL8启动服务netstartMySQL8修改密码切换数据库usemysql;修改root用户的密码alteruser'root'@localhostidentifiedby'mysql';刷新权限,一般......
  • liunx 环境 mysql5.6安装
    1安装包下载mysql5.6下载地址:http://dev.mysql.com/downloads/mysql/ 这里选择Linux版本:使用Navicat管理远程Linux服务器上的MySQL数据库 http://www.linuxidc.com/Linux/2011-09/42285.htm ftp://mirror.switch.ch/mirror/mysql/Downloads/MySQL-5.6/2mysql的安装从官网下载......
  • MySQL中动态SQL的解决方法:预处理语句
    动态SQL是一种很好的特性,允许开发人员在运行时动态构建和执行SQL语句。虽然MySQL缺乏对动态SQL的内置支持,但本文介绍了使用预处理语句(preparedstatements)的变通方法。将探讨如何利用预处理语句实现动态查询执行、参数化查询以及动态表和列查询。 了解预处理语句(preparedst......
  • docker 不适合MySQL
    近几年Docker非常的火热,各位开发者恨不得把所有的应用、软件都部署在Docker容器中,但是您确定也要把数据库也部署的容器中吗?这个问题不是子虚乌有,因为在网上能够找到很多各种操作手册和视频教程,这里整理了一些数据库不适合容器化的原因供大家参考,同时也希望大家在使用时能够谨慎一......
  • MySQL加锁逻辑与死锁
    RC级别:create table t1(id int primary key, name varchar(30));insert into t1 values(1, 'a'),(4, 'c'),(7, 'b'),(10, 'a'),(20, 'd'),(30, 'b');commit;案例一--sess1           --sess2begin;              ......