大数据MySQL分库后如何高效精确地合并数据库节点结果集进行分页查询的通杀方案

最新推荐文章于 2024-06-04 10:10:01 发布
最新推荐文章于 2024-06-04 10:10:01 发布
阅读量2.2k 收藏 3
点赞数
分类专栏： MySQL 文章标签： 大数据分库分页查询
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhengzizhi/article/details/83515832
版权 

USE testdb; -- 分3个表存储用户信息
CREATE TABLE `users0` (
  `user_id` BIGINT(20) NOT NULL,
  `user_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `last_modified` TIMESTAMP(6) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `users1` (
  `user_id` BIGINT(20) NOT NULL,
  `user_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `last_modified` TIMESTAMP(6) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `users2` (
  `user_id` BIGINT(20) NOT NULL,
  `user_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `last_modified` TIMESTAMP(6) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 初始化3个表的记录 
DELIMITER $$

USE `testdb`$$

DROP PROCEDURE IF EXISTS `generateInitData`$$

CREATE DEFINER=`root`@`%` PROCEDURE `generateInitData`(totalRows INT)
BEGIN
	SET @num = 0;
	WHILE @num < totalRows DO
		SET @user_id = @num;
		SET @user_name = CONCAT('test', @user_id);
		SET @email = CONCAT(@user_name, '@163.com');
		SET @password = FLOOR(RAND() * 1000000);
		SET @created_at = CURRENT_TIMESTAMP(6);
		SET @current = @num % 3;
		IF @current = 0 THEN
			INSERT INTO users0(`user_id`,`user_name`,`email`,`password`,`created_at`) VALUES(@user_id,@user_name,@email,@password,@created_at);
		ELSEIF @current = 1 THEN
			INSERT INTO users1(`user_id`,`user_name`,`email`,`password`,`created_at`) VALUES(@user_id,@user_name,@email,@password,@created_at);
		ELSEIF @current = 2 THEN
			INSERT INTO users2(`user_id`,`user_name`,`email`,`password`,`created_at`) VALUES(@user_id,@user_name,@email,@password,@created_at);
		END IF;
		SET @num = @num + 1;
	END WHILE;
END$$

DELIMITER ;
-- 调用存储过程初始化100万条记录 
CALL generateInitData(1000000)


1 queries executed, 1 success, 0 errors, 0 warnings

查询：call generateInitData(1000000)

共 1000000 行受到影响

执行耗时   : 5 min 36 sec
传送时间   : 0 sec
总耗时      : 5 min 36 sec
$ mysql -uroot -h192.168.10.10 -p123456
USE testdb0; -- 分3个数据库存储用户信息（分库不分表）
CREATE TABLE `users` (
  `user_id` BIGINT(20) NOT NULL,
  `user_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `last_modified` TIMESTAMP(6) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


$ mysql -uroot -h192.168.10.11 -p123456
USE testdb1; -- 分3个数据库存储用户信息（分库不分表）
CREATE TABLE `users` (
  `user_id` BIGINT(20) NOT NULL,
  `user_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `last_modified` TIMESTAMP(6) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;


$ mysql -uroot -h192.168.10.12 -p123456
USE testdb2; -- 分3个数据库存储用户信息（分库不分表）
CREATE TABLE `users` (
  `user_id` BIGINT(20) NOT NULL,
  `user_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(120) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `created_at` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `last_modified` TIMESTAMP(6) NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

使用MySQL 客户端可视化管理工具SQLyog Ultimate 将数据库 testdb 中3个表 users0,users1,users2分别导出对应保存

为users0.csv ,users1.csv ,users2.csv 这3个csv格式的文件（文件大小均为23.3Mb大小）

同样使用SQLyog，然后将文件users0.csv导入到数据库服务器节点192.168.10.10 数据库表名称为testdb0.users中；

同样使用SQLyog，然后将文件users1.csv导入到数据库服务器节点192.168.10.11 数据库表名称为testdb1.users中；

同样使用SQLyog，然后将文件users2.csv导入到数据库服务器节点192.168.10.12 数据库表名称为testdb2.users中；

第1个数据库节点

$ mysql -uroot -h192.168.10.10 -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 10.3.10-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use testdb0
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 0,30;  -- 第1页，每页30行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|       0 | test0     | test0@163.com  | 723968   | 2018-10-30 00:45:03.120618 | NULL          |
|       3 | test3     | test3@163.com  | 623938   | 2018-10-30 00:45:03.123731 | NULL          |
|       6 | test6     | test6@163.com  | 367932   | 2018-10-30 00:45:03.125267 | NULL          |
|       9 | test9     | test9@163.com  | 588289   | 2018-10-30 00:45:03.126534 | NULL          |
|      12 | test12    | test12@163.com | 129007   | 2018-10-30 00:45:03.127923 | NULL          |
|      15 | test15    | test15@163.com | 436787   | 2018-10-30 00:45:03.130163 | NULL          |
|      18 | test18    | test18@163.com | 459812   | 2018-10-30 00:45:03.131413 | NULL          |
|      21 | test21    | test21@163.com | 991766   | 2018-10-30 00:45:03.132423 | NULL          |
|      24 | test24    | test24@163.com | 926347   | 2018-10-30 00:45:03.133656 | NULL          |
|      27 | test27    | test27@163.com | 330094   | 2018-10-30 00:45:03.134957 | NULL          |
|      30 | test30    | test30@163.com | 396205   | 2018-10-30 00:45:03.135756 | NULL          |
|      33 | test33    | test33@163.com | 783877   | 2018-10-30 00:45:03.137019 | NULL          |
|      36 | test36    | test36@163.com | 12670    | 2018-10-30 00:45:03.137753 | NULL          |
|      39 | test39    | test39@163.com | 848919   | 2018-10-30 00:45:03.139071 | NULL          |
|      42 | test42    | test42@163.com | 571448   | 2018-10-30 00:45:03.139800 | NULL          |
|      45 | test45    | test45@163.com | 795033   | 2018-10-30 00:45:03.140792 | NULL          |
|      48 | test48    | test48@163.com | 173555   | 2018-10-30 00:45:03.142139 | NULL          |
|      51 | test51    | test51@163.com | 418536   | 2018-10-30 00:45:03.143215 | NULL          |
|      54 | test54    | test54@163.com | 796930   | 2018-10-30 00:45:03.144229 | NULL          |
|      57 | test57    | test57@163.com | 453970   | 2018-10-30 00:45:03.145270 | NULL          |
|      60 | test60    | test60@163.com | 800511   | 2018-10-30 00:45:03.146273 | NULL          |
|      63 | test63    | test63@163.com | 783696   | 2018-10-30 00:45:03.147218 | NULL          |
|      66 | test66    | test66@163.com | 81956    | 2018-10-30 00:45:03.148277 | NULL          |
|      69 | test69    | test69@163.com | 396697   | 2018-10-30 00:45:03.149708 | NULL          |
|      72 | test72    | test72@163.com | 522975   | 2018-10-30 00:45:03.151594 | NULL          |
|      75 | test75    | test75@163.com | 127242   | 2018-10-30 00:45:03.153122 | NULL          |
|      78 | test78    | test78@163.com | 59026    | 2018-10-30 00:45:03.154210 | NULL          |
|      81 | test81    | test81@163.com | 286569   | 2018-10-30 00:45:03.155240 | NULL          |
|      84 | test84    | test84@163.com | 331839   | 2018-10-30 00:45:03.156243 | NULL          |
|      87 | test87    | test87@163.com | 809766   | 2018-10-30 00:45:03.157165 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
30 rows in set (0.114 sec)

MariaDB [testdb0]> 
MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 0,5;  -- 第1页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|       0 | test0     | test0@163.com  | 723968   | 2018-10-30 00:45:03.120618 | NULL          |
|       3 | test3     | test3@163.com  | 623938   | 2018-10-30 00:45:03.123731 | NULL          |
|       6 | test6     | test6@163.com  | 367932   | 2018-10-30 00:45:03.125267 | NULL          |
|       9 | test9     | test9@163.com  | 588289   | 2018-10-30 00:45:03.126534 | NULL          |
|      12 | test12    | test12@163.com | 129007   | 2018-10-30 00:45:03.127923 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.115 sec)

MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 5,5;  -- 第2页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      15 | test15    | test15@163.com | 436787   | 2018-10-30 00:45:03.130163 | NULL          |
|      18 | test18    | test18@163.com | 459812   | 2018-10-30 00:45:03.131413 | NULL          |
|      21 | test21    | test21@163.com | 991766   | 2018-10-30 00:45:03.132423 | NULL          |
|      24 | test24    | test24@163.com | 926347   | 2018-10-30 00:45:03.133656 | NULL          |
|      27 | test27    | test27@163.com | 330094   | 2018-10-30 00:45:03.134957 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.120 sec)

MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 10,5;  -- 第3页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      30 | test30    | test30@163.com | 396205   | 2018-10-30 00:45:03.135756 | NULL          |
|      33 | test33    | test33@163.com | 783877   | 2018-10-30 00:45:03.137019 | NULL          |
|      36 | test36    | test36@163.com | 12670    | 2018-10-30 00:45:03.137753 | NULL          |
|      39 | test39    | test39@163.com | 848919   | 2018-10-30 00:45:03.139071 | NULL          |
|      42 | test42    | test42@163.com | 571448   | 2018-10-30 00:45:03.139800 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.121 sec)

MariaDB [testdb0]>

第2个数据库节点

$ mysql -uroot -h192.168.10.11 -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 10.3.10-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use testdb1
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 0,30;  -- 第1页，每页30行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|       1 | test1     | test1@163.com  | 749248   | 2018-10-30 00:45:03.122768 | NULL          |
|       4 | test4     | test4@163.com  | 396682   | 2018-10-30 00:45:03.124253 | NULL          |
|       7 | test7     | test7@163.com  | 504876   | 2018-10-30 00:45:03.125680 | NULL          |
|      10 | test10    | test10@163.com | 679698   | 2018-10-30 00:45:03.127149 | NULL          |
|      13 | test13    | test13@163.com | 744177   | 2018-10-30 00:45:03.128836 | NULL          |
|      16 | test16    | test16@163.com | 182346   | 2018-10-30 00:45:03.130450 | NULL          |
|      19 | test19    | test19@163.com | 494952   | 2018-10-30 00:45:03.131681 | NULL          |
|      22 | test22    | test22@163.com | 672856   | 2018-10-30 00:45:03.132717 | NULL          |
|      25 | test25    | test25@163.com | 464786   | 2018-10-30 00:45:03.133945 | NULL          |
|      28 | test28    | test28@163.com | 15801    | 2018-10-30 00:45:03.135256 | NULL          |
|      31 | test31    | test31@163.com | 714861   | 2018-10-30 00:45:03.136280 | NULL          |
|      34 | test34    | test34@163.com | 762310   | 2018-10-30 00:45:03.137254 | NULL          |
|      37 | test37    | test37@163.com | 683589   | 2018-10-30 00:45:03.138255 | NULL          |
|      40 | test40    | test40@163.com | 104783   | 2018-10-30 00:45:03.139306 | NULL          |
|      43 | test43    | test43@163.com | 925761   | 2018-10-30 00:45:03.140221 | NULL          |
|      46 | test46    | test46@163.com | 231775   | 2018-10-30 00:45:03.141379 | NULL          |
|      49 | test49    | test49@163.com | 546448   | 2018-10-30 00:45:03.142399 | NULL          |
|      52 | test52    | test52@163.com | 457953   | 2018-10-30 00:45:03.143479 | NULL          |
|      55 | test55    | test55@163.com | 882178   | 2018-10-30 00:45:03.144484 | NULL          |
|      58 | test58    | test58@163.com | 209548   | 2018-10-30 00:45:03.145568 | NULL          |
|      61 | test61    | test61@163.com | 945062   | 2018-10-30 00:45:03.146522 | NULL          |
|      64 | test64    | test64@163.com | 947155   | 2018-10-30 00:45:03.147487 | NULL          |
|      67 | test67    | test67@163.com | 255729   | 2018-10-30 00:45:03.148598 | NULL          |
|      70 | test70    | test70@163.com | 885156   | 2018-10-30 00:45:03.150226 | NULL          |
|      73 | test73    | test73@163.com | 907812   | 2018-10-30 00:45:03.152236 | NULL          |
|      76 | test76    | test76@163.com | 725802   | 2018-10-30 00:45:03.153404 | NULL          |
|      79 | test79    | test79@163.com | 553271   | 2018-10-30 00:45:03.154481 | NULL          |
|      82 | test82    | test82@163.com | 665017   | 2018-10-30 00:45:03.155528 | NULL          |
|      85 | test85    | test85@163.com | 263061   | 2018-10-30 00:45:03.156504 | NULL          |
|      88 | test88    | test88@163.com | 89461    | 2018-10-30 00:45:03.157420 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
30 rows in set (0.118 sec)

MariaDB [testdb1]>
MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 0,5;  -- 第1页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|       1 | test1     | test1@163.com  | 749248   | 2018-10-30 00:45:03.122768 | NULL          |
|       4 | test4     | test4@163.com  | 396682   | 2018-10-30 00:45:03.124253 | NULL          |
|       7 | test7     | test7@163.com  | 504876   | 2018-10-30 00:45:03.125680 | NULL          |
|      10 | test10    | test10@163.com | 679698   | 2018-10-30 00:45:03.127149 | NULL          |
|      13 | test13    | test13@163.com | 744177   | 2018-10-30 00:45:03.128836 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.117 sec)

MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 5,5;  -- 第2页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      16 | test16    | test16@163.com | 182346   | 2018-10-30 00:45:03.130450 | NULL          |
|      19 | test19    | test19@163.com | 494952   | 2018-10-30 00:45:03.131681 | NULL          |
|      22 | test22    | test22@163.com | 672856   | 2018-10-30 00:45:03.132717 | NULL          |
|      25 | test25    | test25@163.com | 464786   | 2018-10-30 00:45:03.133945 | NULL          |
|      28 | test28    | test28@163.com | 15801    | 2018-10-30 00:45:03.135256 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.117 sec)

MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 10,5;  -- 第3页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      31 | test31    | test31@163.com | 714861   | 2018-10-30 00:45:03.136280 | NULL          |
|      34 | test34    | test34@163.com | 762310   | 2018-10-30 00:45:03.137254 | NULL          |
|      37 | test37    | test37@163.com | 683589   | 2018-10-30 00:45:03.138255 | NULL          |
|      40 | test40    | test40@163.com | 104783   | 2018-10-30 00:45:03.139306 | NULL          |
|      43 | test43    | test43@163.com | 925761   | 2018-10-30 00:45:03.140221 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.119 sec)

MariaDB [testdb1]>

第3个数据库节点

 

$ mysql -uroot -h192.168.10.12 -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 27
Server version: 10.3.10-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use testdb2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 0,30;  -- 第1页，每页30行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|       2 | test2     | test2@163.com  | 574336   | 2018-10-30 00:45:03.123408 | NULL          |
|       5 | test5     | test5@163.com  | 111595   | 2018-10-30 00:45:03.124665 | NULL          |
|       8 | test8     | test8@163.com  | 420583   | 2018-10-30 00:45:03.126236 | NULL          |
|      11 | test11    | test11@163.com | 633620   | 2018-10-30 00:45:03.127411 | NULL          |
|      14 | test14    | test14@163.com | 333863   | 2018-10-30 00:45:03.129391 | NULL          |
|      17 | test17    | test17@163.com | 601370   | 2018-10-30 00:45:03.130819 | NULL          |
|      20 | test20    | test20@163.com | 95324    | 2018-10-30 00:45:03.132136 | NULL          |
|      23 | test23    | test23@163.com | 388983   | 2018-10-30 00:45:03.133231 | NULL          |
|      26 | test26    | test26@163.com | 544890   | 2018-10-30 00:45:03.134524 | NULL          |
|      29 | test29    | test29@163.com | 88721    | 2018-10-30 00:45:03.135494 | NULL          |
|      32 | test32    | test32@163.com | 385692   | 2018-10-30 00:45:03.136537 | NULL          |
|      35 | test35    | test35@163.com | 459920   | 2018-10-30 00:45:03.137486 | NULL          |
|      38 | test38    | test38@163.com | 379937   | 2018-10-30 00:45:03.138554 | NULL          |
|      41 | test41    | test41@163.com | 977159   | 2018-10-30 00:45:03.139536 | NULL          |
|      44 | test44    | test44@163.com | 914463   | 2018-10-30 00:45:03.140512 | NULL          |
|      47 | test47    | test47@163.com | 773776   | 2018-10-30 00:45:03.141648 | NULL          |
|      50 | test50    | test50@163.com | 211576   | 2018-10-30 00:45:03.142665 | NULL          |
|      53 | test53    | test53@163.com | 34158    | 2018-10-30 00:45:03.143774 | NULL          |
|      56 | test56    | test56@163.com | 20101    | 2018-10-30 00:45:03.144770 | NULL          |
|      59 | test59    | test59@163.com | 685831   | 2018-10-30 00:45:03.146028 | NULL          |
|      62 | test62    | test62@163.com | 323776   | 2018-10-30 00:45:03.146824 | NULL          |
|      65 | test65    | test65@163.com | 384684   | 2018-10-30 00:45:03.147819 | NULL          |
|      68 | test68    | test68@163.com | 32777    | 2018-10-30 00:45:03.149365 | NULL          |
|      71 | test71    | test71@163.com | 235688   | 2018-10-30 00:45:03.150772 | NULL          |
|      74 | test74    | test74@163.com | 970136   | 2018-10-30 00:45:03.152536 | NULL          |
|      77 | test77    | test77@163.com | 247286   | 2018-10-30 00:45:03.153726 | NULL          |
|      80 | test80    | test80@163.com | 589276   | 2018-10-30 00:45:03.154719 | NULL          |
|      83 | test83    | test83@163.com | 465378   | 2018-10-30 00:45:03.155809 | NULL          |
|      86 | test86    | test86@163.com | 319790   | 2018-10-30 00:45:03.156748 | NULL          |
|      89 | test89    | test89@163.com | 18006    | 2018-10-30 00:45:03.157663 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
30 rows in set (0.114 sec)

MariaDB [testdb2]>
MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 0,5;  -- 第1页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|       2 | test2     | test2@163.com  | 574336   | 2018-10-30 00:45:03.123408 | NULL          |
|       5 | test5     | test5@163.com  | 111595   | 2018-10-30 00:45:03.124665 | NULL          |
|       8 | test8     | test8@163.com  | 420583   | 2018-10-30 00:45:03.126236 | NULL          |
|      11 | test11    | test11@163.com | 633620   | 2018-10-30 00:45:03.127411 | NULL          |
|      14 | test14    | test14@163.com | 333863   | 2018-10-30 00:45:03.129391 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.115 sec)

MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 5,5;  -- 第2页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      17 | test17    | test17@163.com | 601370   | 2018-10-30 00:45:03.130819 | NULL          |
|      20 | test20    | test20@163.com | 95324    | 2018-10-30 00:45:03.132136 | NULL          |
|      23 | test23    | test23@163.com | 388983   | 2018-10-30 00:45:03.133231 | NULL          |
|      26 | test26    | test26@163.com | 544890   | 2018-10-30 00:45:03.134524 | NULL          |
|      29 | test29    | test29@163.com | 88721    | 2018-10-30 00:45:03.135494 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.115 sec)

MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 10,5;  -- 第3页，每页5行
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      32 | test32    | test32@163.com | 385692   | 2018-10-30 00:45:03.136537 | NULL          |
|      35 | test35    | test35@163.com | 459920   | 2018-10-30 00:45:03.137486 | NULL          |
|      38 | test38    | test38@163.com | 379937   | 2018-10-30 00:45:03.138554 | NULL          |
|      41 | test41    | test41@163.com | 977159   | 2018-10-30 00:45:03.139536 | NULL          |
|      44 | test44    | test44@163.com | 914463   | 2018-10-30 00:45:03.140512 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.116 sec)

MariaDB [testdb2]>

正式进入主题  采用高效精确的二次查询方案

假如网页上每翻页一次，只显示15条记录

例如显示第2页记录 来自3个数据库节点

SELECT * FROM users ORDER BY created_at LIMIT 页码值  * （显示15条/3个数据库节点数）,（显示15条/3个数据库节点数）

MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 5,5; -- 第2页显示网页中15条记录中的部分记录
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      15 | test15    | test15@163.com | 436787   | 2018-10-30 00:45:03.130163 | NULL          |
|      18 | test18    | test18@163.com | 459812   | 2018-10-30 00:45:03.131413 | NULL          |
|      21 | test21    | test21@163.com | 991766   | 2018-10-30 00:45:03.132423 | NULL          |
|      24 | test24    | test24@163.com | 926347   | 2018-10-30 00:45:03.133656 | NULL          |
|      27 | test27    | test27@163.com | 330094   | 2018-10-30 00:45:03.134957 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.125 sec)
MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 5,5;  -- 第2页显示网页中15条记录中的部分记录
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      16 | test16    | test16@163.com | 182346   | 2018-10-30 00:45:03.130450 | NULL          |
|      19 | test19    | test19@163.com | 494952   | 2018-10-30 00:45:03.131681 | NULL          |
|      22 | test22    | test22@163.com | 672856   | 2018-10-30 00:45:03.132717 | NULL          |
|      25 | test25    | test25@163.com | 464786   | 2018-10-30 00:45:03.133945 | NULL          |
|      28 | test28    | test28@163.com | 15801    | 2018-10-30 00:45:03.135256 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.129 sec)
MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 5,5;  -- 第2页显示网页中15条记录中的部分记录
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      17 | test17    | test17@163.com | 601370   | 2018-10-30 00:45:03.130819 | NULL          |
|      20 | test20    | test20@163.com | 95324    | 2018-10-30 00:45:03.132136 | NULL          |
|      23 | test23    | test23@163.com | 388983   | 2018-10-30 00:45:03.133231 | NULL          |
|      26 | test26    | test26@163.com | 544890   | 2018-10-30 00:45:03.134524 | NULL          |
|      29 | test29    | test29@163.com | 88721    | 2018-10-30 00:45:03.135494 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.127 sec)

MariaDB [testdb2]>

将上面3个结果合并在一起再次排序一次（我们可以用PHP Python Golang Java C# 等等语言实现此需求）

获得如下结果显示到页面上：

第1页 user_id 值范围从0开始到14结束，第2页 user_id 值范围从15开始到29结束

+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      15 | test15    | test15@163.com | 436787   | 2018-10-30 00:45:03.130163 | NULL          |
|      16 | test16    | test16@163.com | 182346   | 2018-10-30 00:45:03.130450 | NULL          |
|      17 | test17    | test17@163.com | 601370   | 2018-10-30 00:45:03.130819 | NULL          |
|      18 | test18    | test18@163.com | 459812   | 2018-10-30 00:45:03.131413 | NULL          |
|      19 | test19    | test19@163.com | 494952   | 2018-10-30 00:45:03.131681 | NULL          |
|      20 | test20    | test20@163.com | 95324    | 2018-10-30 00:45:03.132136 | NULL          |
|      21 | test21    | test21@163.com | 991766   | 2018-10-30 00:45:03.132423 | NULL          |
|      22 | test22    | test22@163.com | 672856   | 2018-10-30 00:45:03.132717 | NULL          |
|      23 | test23    | test23@163.com | 388983   | 2018-10-30 00:45:03.133231 | NULL          |
|      24 | test24    | test24@163.com | 926347   | 2018-10-30 00:45:03.133656 | NULL          |
|      25 | test25    | test25@163.com | 464786   | 2018-10-30 00:45:03.133945 | NULL          |
|      26 | test26    | test26@163.com | 544890   | 2018-10-30 00:45:03.134524 | NULL          |
|      27 | test27    | test27@163.com | 330094   | 2018-10-30 00:45:03.134957 | NULL          |
|      28 | test28    | test28@163.com | 15801    | 2018-10-30 00:45:03.135256 | NULL          |
|      29 | test29    | test29@163.com | 88721    | 2018-10-30 00:45:03.135494 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+

假如第3个数据库节点testdb2，user_id >= 26 的主键都不存在，模拟节点数据分布不均匀的情形

MariaDB [testdb2]> DELETE FROM users WHERE user_id >= 26;
Query OK, 333325 rows affected (1.343 sec)

MariaDB [testdb2]>

继续补充说明，把user_id >= 26 的主键都删掉，意思是第2页第3个数据库节点testdb2只会返回3条记录

我们一共需要15条记录，但是只返回13条记录从3个数据库节点，这显然是不对的，因为我们的数据库全部节点里面可有几十万行记录啊

MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 5,5;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      15 | test15    | test15@163.com | 436787   | 2018-10-30 00:45:03.130163 | NULL          |
|      18 | test18    | test18@163.com | 459812   | 2018-10-30 00:45:03.131413 | NULL          |
|      21 | test21    | test21@163.com | 991766   | 2018-10-30 00:45:03.132423 | NULL          |
|      24 | test24    | test24@163.com | 926347   | 2018-10-30 00:45:03.133656 | NULL          |
|      27 | test27    | test27@163.com | 330094   | 2018-10-30 00:45:03.134957 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.361 sec)

MariaDB [testdb0]> 
MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 5,5;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      16 | test16    | test16@163.com | 182346   | 2018-10-30 00:45:03.130450 | NULL          |
|      19 | test19    | test19@163.com | 494952   | 2018-10-30 00:45:03.131681 | NULL          |
|      22 | test22    | test22@163.com | 672856   | 2018-10-30 00:45:03.132717 | NULL          |
|      25 | test25    | test25@163.com | 464786   | 2018-10-30 00:45:03.133945 | NULL          |
|      28 | test28    | test28@163.com | 15801    | 2018-10-30 00:45:03.135256 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.557 sec)

MariaDB [testdb1]> 
MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 5,5;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      17 | test17    | test17@163.com | 601370   | 2018-10-30 00:45:03.130819 | NULL          |
|      20 | test20    | test20@163.com | 95324    | 2018-10-30 00:45:03.132136 | NULL          |
|      23 | test23    | test23@163.com | 388983   | 2018-10-30 00:45:03.133231 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
3 rows in set (0.001 sec)

MariaDB [testdb2]>

缺少2条记录，此时不知道这2条记录该来自哪些节点，那么一次性搞定的办法就是第2次查询时，每页每个节点返回7条记录

遵循以下动态规则

SELECT * FROM users ORDER BY created_at LIMIT 页码值  * （显示15条/3个数据库节点数）,（显示15条/3个数据库节点数+缺少的记录行数）

MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 5,7;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      15 | test15    | test15@163.com | 436787   | 2018-10-30 00:45:03.130163 | NULL          |
|      18 | test18    | test18@163.com | 459812   | 2018-10-30 00:45:03.131413 | NULL          |
|      21 | test21    | test21@163.com | 991766   | 2018-10-30 00:45:03.132423 | NULL          |
|      24 | test24    | test24@163.com | 926347   | 2018-10-30 00:45:03.133656 | NULL          |
|      27 | test27    | test27@163.com | 330094   | 2018-10-30 00:45:03.134957 | NULL          |
|      30 | test30    | test30@163.com | 396205   | 2018-10-30 00:45:03.135756 | NULL          |
|      33 | test33    | test33@163.com | 783877   | 2018-10-30 00:45:03.137019 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
7 rows in set (0.131 sec)

MariaDB [testdb0]> 
MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 5,7;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      16 | test16    | test16@163.com | 182346   | 2018-10-30 00:45:03.130450 | NULL          |
|      19 | test19    | test19@163.com | 494952   | 2018-10-30 00:45:03.131681 | NULL          |
|      22 | test22    | test22@163.com | 672856   | 2018-10-30 00:45:03.132717 | NULL          |
|      25 | test25    | test25@163.com | 464786   | 2018-10-30 00:45:03.133945 | NULL          |
|      28 | test28    | test28@163.com | 15801    | 2018-10-30 00:45:03.135256 | NULL          |
|      31 | test31    | test31@163.com | 714861   | 2018-10-30 00:45:03.136280 | NULL          |
|      34 | test34    | test34@163.com | 762310   | 2018-10-30 00:45:03.137254 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
7 rows in set (0.118 sec)

MariaDB [testdb1]> 
MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 5,7;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      17 | test17    | test17@163.com | 601370   | 2018-10-30 00:45:03.130819 | NULL          |
|      20 | test20    | test20@163.com | 95324    | 2018-10-30 00:45:03.132136 | NULL          |
|      23 | test23    | test23@163.com | 388983   | 2018-10-30 00:45:03.133231 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
3 rows in set (0.001 sec)

MariaDB [testdb2]>

将合并的结果排序后，只取前15条记录，丢弃最后2条，这样第2页显示15条记录就齐活啦

那么第3页翻页时显示时，该发送怎样SQL到每个数据库节点呢?

MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 10,5;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      30 | test30    | test30@163.com | 396205   | 2018-10-30 00:45:03.135756 | NULL          |
|      33 | test33    | test33@163.com | 783877   | 2018-10-30 00:45:03.137019 | NULL          |
|      36 | test36    | test36@163.com | 12670    | 2018-10-30 00:45:03.137753 | NULL          |
|      39 | test39    | test39@163.com | 848919   | 2018-10-30 00:45:03.139071 | NULL          |
|      42 | test42    | test42@163.com | 571448   | 2018-10-30 00:45:03.139800 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.120 sec)

MariaDB [testdb0]> 
MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 10,5;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      31 | test31    | test31@163.com | 714861   | 2018-10-30 00:45:03.136280 | NULL          |
|      34 | test34    | test34@163.com | 762310   | 2018-10-30 00:45:03.137254 | NULL          |
|      37 | test37    | test37@163.com | 683589   | 2018-10-30 00:45:03.138255 | NULL          |
|      40 | test40    | test40@163.com | 104783   | 2018-10-30 00:45:03.139306 | NULL          |
|      43 | test43    | test43@163.com | 925761   | 2018-10-30 00:45:03.140221 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
5 rows in set (0.118 sec)

MariaDB [testdb1]> 
MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 10,5;
Empty set (0.001 sec)

MariaDB [testdb2]>

后面所有的翻页规则依然遵循以下动态规则，此时缺少5行记录

SELECT * FROM users ORDER BY created_at LIMIT 页码值  * （显示15条/3个数据库节点数）,（显示15条/3个数据库节点数+缺少的记录行数）

MariaDB [testdb0]> SELECT * FROM users ORDER BY created_at LIMIT 10,10;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      30 | test30    | test30@163.com | 396205   | 2018-10-30 00:45:03.135756 | NULL          |
|      33 | test33    | test33@163.com | 783877   | 2018-10-30 00:45:03.137019 | NULL          |
|      36 | test36    | test36@163.com | 12670    | 2018-10-30 00:45:03.137753 | NULL          |
|      39 | test39    | test39@163.com | 848919   | 2018-10-30 00:45:03.139071 | NULL          |
|      42 | test42    | test42@163.com | 571448   | 2018-10-30 00:45:03.139800 | NULL          |
|      45 | test45    | test45@163.com | 795033   | 2018-10-30 00:45:03.140792 | NULL          |
|      48 | test48    | test48@163.com | 173555   | 2018-10-30 00:45:03.142139 | NULL          |
|      51 | test51    | test51@163.com | 418536   | 2018-10-30 00:45:03.143215 | NULL          |
|      54 | test54    | test54@163.com | 796930   | 2018-10-30 00:45:03.144229 | NULL          |
|      57 | test57    | test57@163.com | 453970   | 2018-10-30 00:45:03.145270 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
10 rows in set (0.117 sec)

MariaDB [testdb0]> 
MariaDB [testdb1]> SELECT * FROM users ORDER BY created_at LIMIT 10,10;
+---------+-----------+----------------+----------+----------------------------+---------------+
| user_id | user_name | email          | password | created_at                 | last_modified |
+---------+-----------+----------------+----------+----------------------------+---------------+
|      31 | test31    | test31@163.com | 714861   | 2018-10-30 00:45:03.136280 | NULL          |
|      34 | test34    | test34@163.com | 762310   | 2018-10-30 00:45:03.137254 | NULL          |
|      37 | test37    | test37@163.com | 683589   | 2018-10-30 00:45:03.138255 | NULL          |
|      40 | test40    | test40@163.com | 104783   | 2018-10-30 00:45:03.139306 | NULL          |
|      43 | test43    | test43@163.com | 925761   | 2018-10-30 00:45:03.140221 | NULL          |
|      46 | test46    | test46@163.com | 231775   | 2018-10-30 00:45:03.141379 | NULL          |
|      49 | test49    | test49@163.com | 546448   | 2018-10-30 00:45:03.142399 | NULL          |
|      52 | test52    | test52@163.com | 457953   | 2018-10-30 00:45:03.143479 | NULL          |
|      55 | test55    | test55@163.com | 882178   | 2018-10-30 00:45:03.144484 | NULL          |
|      58 | test58    | test58@163.com | 209548   | 2018-10-30 00:45:03.145568 | NULL          |
+---------+-----------+----------------+----------+----------------------------+---------------+
10 rows in set (0.120 sec)

MariaDB [testdb1]> 
MariaDB [testdb2]> SELECT * FROM users ORDER BY created_at LIMIT 10,10;
Empty set (0.000 sec)

MariaDB [testdb2]>

合并排序后，只取前15条记录，丢弃最后5条记录，在第2页中丢弃的user_id=42,user_id=43这两行记录此时出现在第3页网页上

二次查询虽然查询了两次，但是返回的记录精准，因为每个节点获取的数据量很少，性能非常优秀

不管数据源是采用何种哈希算法来切分存储数据，此方案通杀通用！

zhengzizhi
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
专栏目录
分库分表合并迁移
weixin_42241611的博客
09-08 372
DM 支持对上游多个分表的数据合并迁移到 TiDB 的一个表中，在迁移过程中需要协调各个分表的 DDL，以及该 DDL 前后的 DML。针对用户的使用场景，DM 支持悲观协调和乐观协调两种不同的模式。注意要执行分库分表合并迁移任务，必须在任务配置文件中设置shard-mode。DM 对分库分表的合并默认使用悲观协调模式，在文档中如果没特殊说明则默认为悲观协调模式。在没有深入了解乐观模式的原理和使用限制的情况下不建议使用该模式，否则可能造成迁移中断甚至数据不一致的严重后果。
模拟分表查询：多数据集合并
08-23 593
https://blog.csdn.net/github_26672553/article/details/100047058 之前我们把select * from users硬生生的替换成了select * from (select * from user1 union select * from user2) as t 很low，但如果你的分表需求和业务是在过于简单，也是可以的。。。 func...
分库分表后怎么分页查询
最新发布
qq_36176028的博客
06-04 1364
全局查询法：这种方案最简单，但是随着页码的增加，性能越来越低禁止跳页查询法：这种方案是在业务上更改，不能跳页查询，由于只返回一页数据，性能较高二次查询法：数据精确，在数据分布均衡的情况下适用，查询的数据较少，不会随着翻页增加数据的返回量，性能较高。
【Mysql】数据库分库后跨库join解决方案
热门推荐
哈士奇
01-04 2万+
一、前言      近几个月项目中数据库开始分库了，把一个原本60多张表的数据库分成了6个部分，每个部分有自己业务之内的表，整个数据库只是物理分开，逻辑依然是链接的，通过mycat这个数据库中间件对整个数据库进行路由转发。但是分库后随之而来的问题之一就是跨库join，可以说网上的解决方案已经烂大街了。但是小编还是觉得有必要总结分析一下。二、数据库结构示例      为了公司数据保密，小编建立两个新的
Mysql分库分表方案
weixin_34226706的博客
02-13 1366
2019独角兽企业重金招聘Python工程师标准>>> ...
MySQL高效分页解决方案集分享
12-15
MySQL高效分页解决方案是数据库管理中的重要议题，特别是在大数据量的场景下，传统的LIMIT与OFFSET方法在处理分页时性能会显著下降。本文主要探讨了三种优化MySQL分页查询的方法，旨在提高查询效率。 首先，最基本...
mysql大数据分库和分表 php解决方案
07-08
本文讲的是mysql大数据分库和分表 php解决方案。 mysql分库分表方案、mysql 分库方案、php实现mysql分库分表、mysql高并发解决方案。
易语言MYSQL数据库分页查询
07-20
本文将详细讲解如何在易语言中进行MySQL数据库的分页查询，并探讨相关技术点。 首先，我们要了解分页查询的基本原理。分页查询是指在浏览数据时，不一次性加载所有记录，而是按照一定的页面大小（如每页10条或20条...
MySQL百万级数据分页查询优化方案
12-16
当需要从数据库查询的表有上万条记录的时候，一次性查询所有结果会变得很慢，特别是随着数据量的增加特别明显，这时需要使用分页查询。对于数据库分页查询，也有很多种方法和优化的点。下面简单说一下我知道的一些...
mysql分库一个库和多个库_数据库分库后不同库之间的关联
weixin_35379520的博客
02-18 779
数据库分库后不同库之间的关联一.所有库在同一节点上当业务垂直拆分出很多业务库的时候，如果都部署在同一个源上(同一个机器节点上)，那么这种情况是最好办的，直接库名+表名 join就可以。二.不在一个节点当不同的库来自不同的节点，那么就没办法直接join查询了，有以下方式思路：1.全局表所谓全局表，就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。为了避免跨库join查询...
mysql跨库分页_分表分页/跨库分页为什么这么难？
weixin_34410975的博客
01-19 523
当业务数据达到一定量级(比如：mysql单表记录量>1千万)后，通常会考虑“分库分表”将数据分散到不同的库或表中，这样可以大大提高读/写性能。但是问题来了，对于select * from table limit offset , pagesize这种分页方式，原来一条语句就可以简单搞定的事情会变得很复杂，本文将与大家一起探讨分库分表后"分页"面临的新问题。一、分表对分页的影响比如有一张表...
mysql分片库分页查询_准备开发一个数据库分片的中间件，请问下分页查询用什么样的算法效率较高？...
weixin_35626308的博客
01-19 75
假设你说的用户，不是开发人员，是终端用户，比如saas之类的系统用户。如果对于用户是透明的，意味着每个用户只需要看到自己的数据，那么比较经济的处理方式是，把用户id的哈希值作为分配的条件，这样能够保证每个用户的数据都在同一个分配上。当用户的请求过来，只需要查询这个分配，就能像正常的单库单表一样的处理分页方式了。but，分页也可以说两句，简单的情况就是top，rownum，limit这些常规方式。但...
shardingjdbc (七)-结果合并
bohu83的博客
06-10 4857
一 序          单分片的SQL查询不需要合并，多分片的情况在各分片排序完后，Sharding-JDBC 获取到结果后，仍然需要再进一步排序。目前有 分页、分组、排序、聚合列、迭代 五种场景需要做进一步处理。 public ResultSet executeQuery() throws SQLException { ResultSet result; ...
我的架构梦：（五十二） 分库分表实战及中间件之实战背景
riemann_的博客
10-12 888
一、实战背景 1、背景描述 刚开始我们的系统只用了单机数据库 随着用户的不断增多，考虑到系统的高可用和越来越多的用户请求，我们开始使用数据库主从架构 当用户量级和业务进一步提升后，写请求越来越多，这时我们开始使用了分库分表 2、遇到的问题 用户请求量太大 单服务器TPS、内存、IO都是有上限的，需要将请求打散分布到多个服务器 单库数据量太大 单个数据库处理能力有限;单库所在服务器的磁盘空间有限;单库上的操作IO有瓶颈 单表数据量太大 查询、插入、更新操作都会变慢，在加字段、加索引、机器迁移都会产生高负
MySQL Sharding详解
weixin_30673715的博客
05-04 330
转自(http://blog.sina.com.cn/s/blog_821512b50101hyc1.html) MySQLSharding详解 一 背景 我们知道，当数据库中的数据量越来越大时，不论是读还是写，压力都会变得越来越大。采用MySQL Replication多master多slave方案，在上层做负载均衡，虽然能够一定程...
mysql数据库做关联查询_mysql中的跨库关联查询方法
weixin_29496633的博客
01-28 1145
业务场景：关联不同数据库中的表的查询比如说，要关联的表是：机器A上的数据库A中的表A && 机器B上的数据库B中的表B。这种情况下，想执行“select A.id,B.id from A left join B on ~~~;“那是不可能的，但业务需求不可变，数据库设计不可变，这就蛋疼了。。解决方案：在机器A上的数据库A中建一个表B。。。这当然不是跟你开玩笑啦，我们采用的是基于My...
MySQL跨库多表联查（不同库之间进行表关联）
Xx__WangQi的博客
03-11 1万+
MySQL跨库联查（不同库之间进行表关联） mysql支持多个库中不同表的关联查询，你可以随便链接一个数据库 然后，sql语句为： select * from db1.table1 left join db2.table2on db1.table1.id = db2.table2.id 只要用数据库名加上"."就能调用相应数据库的数据表了. 数据库名.表名 自己遇到的一个跨库聚合求差集的场景 SELECT COUNT(1) FROM analytics_graph_data.HORGSh.
MySQL分库分页_MySQL分库分表的分页查询解决方案
weixin_30658827的博客
02-09 2874
问题的提出我们知道，当我们的数据量达到一定数量时，需要将数据表进行水平拆分，从而满足大量数据的存储和查询，保证系统的可用性，但同时会出现另外一个问题就是，如果业务要查询“最近注册的第3页用户”，该如何实现呢？单库上，可以通过简单的sql实现分页查询。select * from t_user order by time limit 200,1001select *fromt_userorderbyt...
"深入探讨Mysql分库分表技术方案及架构原理
总之，Mysql分库分表技术方案可以有效地提高数据库的并发处理能力和查询效率，适用于数据量较大和并发访问量较高的应用场景。通过合理的分库分表方案，可以为企业的数据库管理带来更高的性能和可靠性。
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值