大数量的mysql 查询优化 mysql优化 百万级 mysql 查询优化

最新推荐文章于 2025-10-10 10:10:29 发布
转载 最新推荐文章于 2025-10-10 10:10:29 发布 · 1.2k 阅读 · 1
收录于
php mysql

当前文章被收录于：

php mysql
39 篇文章 0 人学习
订阅专栏 查看详情
架构 性能

当前文章被收录于：

架构 性能
4 篇文章 0 人学习
订阅专栏 查看详情
算法

当前文章被收录于：

算法
3 篇文章 0 人学习
订阅专栏 查看详情
本文介绍了一种通过创建分页表来优化MySQL中大数据量分页查询的方法。通过对比使用LIMIT和分页表两种方式获取相同数据的速度，展示了分页表对于提高查询效率的优势。

 
 
关于分页的优化。
我们知道，在MySQL中分页很简单，直接LIMIT page_no,page_total 就可以了。
可是当记录数慢慢增大时，她就不那么好使了。
这里我们创建摘要表来记录页码和原表之间的关联。
下面为测试数据。

原表：
CREATE TABLE `t_group` (
  `id` int(11) NOT NULL auto_increment,
  `money` decimal(10,2) NOT NULL,
  `user_name` varchar(20) NOT NULL,
  `create_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  KEY `idx_combination1` (`user_name`,`money`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

原表总记录数：
mysql> select count(*) from t_group;
+----------+
| count(*) |
+----------+
| 10485760 |
+----------+
1 row in set (0.00 sec)
分页表：


CREATE TABLE `t_group_ids` (
  `id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`,`group_id`),
  KEY `idx_id` (`id`),
  KEY `idx_group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


插入分页表数据。当然这里如果你的表主键不是ID,那你得自己想办法搞这个分页表的数据了。这个好实现，就不说了。
mysql> insert into t_group_ids select ceil(id/20),id from t_group;
Query OK, 10485760 rows affected (2 min 56.19 sec)
Records: 10485760  Duplicates: 0  Warnings: 0

现在来看看对比数据。

用普通LIMIT来实现分页。
mysql> select * from t_group where 1 limit 20;
+----+--------+-----------+---------------------+
| id | money  | user_name | create_time         |
+----+--------+-----------+---------------------+
|  1 |  50.23 | david     | 2008-10-23 12:55:49 |
|  2 |  55.23 | livia     | 2008-10-23 10:02:09 |
|  3 | 100.83 | leo       | 2008-10-23 10:02:22 |
|  4 |  99.99 | lucy      | 2008-10-23 10:02:39 |
|  5 | 299.99 | simon     | 2008-10-23 10:02:52 |
|  6 | 599.99 | sony      | 2008-10-23 10:03:03 |
|  7 | 599.99 | rick      | 2008-10-23 10:03:12 |
|  8 |   9.99 | anne      | 2008-10-23 10:03:47 |
|  9 |   9.99 | sarah     | 2008-10-23 10:04:31 |
| 10 | 900.99 | john      | 2008-10-23 10:04:50 |
| 11 |   0.23 | david     | 2008-10-23 10:05:31 |
| 12 |   5.23 | livia     | 2008-10-23 10:05:31 |
| 13 |  50.83 | leo       | 2008-10-23 10:05:31 |
| 14 |  49.99 | lucy      | 2008-10-23 10:05:31 |
| 15 | 249.99 | simon     | 2008-10-23 10:05:31 |
| 16 | 549.99 | sony      | 2008-10-23 10:05:31 |
| 17 | 549.99 | rick      | 2008-10-23 10:05:31 |
| 18 | -40.01 | anne      | 2008-10-23 10:05:31 |
| 19 | -40.01 | sarah     | 2008-10-23 10:05:31 |
| 20 | 850.99 | john      | 2008-10-23 10:05:31 |
+----+--------+-----------+---------------------+
20 rows in set (0.01 sec)


用分页表来实现分页。

mysql> select a.* from t_group as a inner join t_group_ids as b where a.id = b.g
roup_id and b.id = 1;
+----+--------+-----------+---------------------+
| id | money  | user_name | create_time         |
+----+--------+-----------+---------------------+
|  1 |  50.23 | david     | 2008-10-23 12:55:49 |
|  2 |  55.23 | livia     | 2008-10-23 10:02:09 |
|  3 | 100.83 | leo       | 2008-10-23 10:02:22 |
|  4 |  99.99 | lucy      | 2008-10-23 10:02:39 |
|  5 | 299.99 | simon     | 2008-10-23 10:02:52 |
|  6 | 599.99 | sony      | 2008-10-23 10:03:03 |
|  7 | 599.99 | rick      | 2008-10-23 10:03:12 |
|  8 |   9.99 | anne      | 2008-10-23 10:03:47 |
|  9 |   9.99 | sarah     | 2008-10-23 10:04:31 |
| 10 | 900.99 | john      | 2008-10-23 10:04:50 |
| 11 |   0.23 | david     | 2008-10-23 10:05:31 |
| 12 |   5.23 | livia     | 2008-10-23 10:05:31 |
| 13 |  50.83 | leo       | 2008-10-23 10:05:31 |
| 14 |  49.99 | lucy      | 2008-10-23 10:05:31 |
| 15 | 249.99 | simon     | 2008-10-23 10:05:31 |
| 16 | 549.99 | sony      | 2008-10-23 10:05:31 |
| 17 | 549.99 | rick      | 2008-10-23 10:05:31 |
| 18 | -40.01 | anne      | 2008-10-23 10:05:31 |
| 19 | -40.01 | sarah     | 2008-10-23 10:05:31 |
| 20 | 850.99 | john      | 2008-10-23 10:05:31 |
+----+--------+-----------+---------------------+
20 rows in set (0.00 sec)



取第50W页的数据。
原来表：
mysql> select * from t_group where 1 limit 9999980,20;
+----------+---------+-----------+---------------------+
| id       | money   | user_name | create_time         |
+----------+---------+-----------+---------------------+
|  9999981 |  810.13 | david     | 2008-10-23 10:09:24 |
|  9999982 |  815.13 | livia     | 2008-10-23 10:09:24 |
|  9999983 |  860.73 | leo       | 2008-10-23 10:09:24 |
|  9999984 |  859.89 | lucy      | 2008-10-23 10:09:24 |
|  9999985 | 1059.89 | simon     | 2008-10-23 10:09:24 |
|  9999986 | 1359.89 | sony      | 2008-10-23 10:09:24 |
|  9999987 | 1359.89 | rick      | 2008-10-23 10:09:24 |
|  9999988 |  769.89 | anne      | 2008-10-23 10:09:24 |
|  9999989 |  769.89 | sarah     | 2008-10-23 10:09:24 |
|  9999990 | 1660.89 | john      | 2008-10-23 10:09:24 |
|  9999991 |  760.13 | david     | 2008-10-23 10:09:24 |
|  9999992 |  765.13 | livia     | 2008-10-23 10:09:24 |
|  9999993 |  810.73 | leo       | 2008-10-23 10:09:24 |
|  9999994 |  809.89 | lucy      | 2008-10-23 10:09:24 |
|  9999995 | 1009.89 | simon     | 2008-10-23 10:09:24 |
|  9999996 | 1309.89 | sony      | 2008-10-23 10:09:24 |
|  9999997 | 1309.89 | rick      | 2008-10-23 10:09:24 |
|  9999998 |  719.89 | anne      | 2008-10-23 10:09:24 |
|  9999999 |  719.89 | sarah     | 2008-10-23 10:09:24 |
| 10000000 | 1610.89 | john      | 2008-10-23 10:09:24 |
+----------+---------+-----------+---------------------+
20 rows in set (4.21 sec)

分页表：

mysql> select a.* from t_group as a inner join t_group_ids as b where a.id = b.g
roup_id and b.id = 500000;
+----------+---------+-----------+---------------------+
| id       | money   | user_name | create_time         |
+----------+---------+-----------+---------------------+
|  9999981 |  810.13 | david     | 2008-10-23 10:09:24 |
|  9999982 |  815.13 | livia     | 2008-10-23 10:09:24 |
|  9999983 |  860.73 | leo       | 2008-10-23 10:09:24 |
|  9999984 |  859.89 | lucy      | 2008-10-23 10:09:24 |
|  9999985 | 1059.89 | simon     | 2008-10-23 10:09:24 |
|  9999986 | 1359.89 | sony      | 2008-10-23 10:09:24 |
|  9999987 | 1359.89 | rick      | 2008-10-23 10:09:24 |
|  9999988 |  769.89 | anne      | 2008-10-23 10:09:24 |
|  9999989 |  769.89 | sarah     | 2008-10-23 10:09:24 |
|  9999990 | 1660.89 | john      | 2008-10-23 10:09:24 |
|  9999991 |  760.13 | david     | 2008-10-23 10:09:24 |
|  9999992 |  765.13 | livia     | 2008-10-23 10:09:24 |
|  9999993 |  810.73 | leo       | 2008-10-23 10:09:24 |
|  9999994 |  809.89 | lucy      | 2008-10-23 10:09:24 |
|  9999995 | 1009.89 | simon     | 2008-10-23 10:09:24 |
|  9999996 | 1309.89 | sony      | 2008-10-23 10:09:24 |
|  9999997 | 1309.89 | rick      | 2008-10-23 10:09:24 |
|  9999998 |  719.89 | anne      | 2008-10-23 10:09:24 |
|  9999999 |  719.89 | sarah     | 2008-10-23 10:09:24 |
| 10000000 | 1610.89 | john      | 2008-10-23 10:09:24 |
+----------+---------+-----------+---------------------+
20 rows in set (0.03 sec)


我们来取最后一页的数据。
原表：

mysql> select * from t_group where 1 limit 10485740,20;
+----------+---------+-----------+---------------------+
| id       | money   | user_name | create_time         |
+----------+---------+-----------+---------------------+
| 10485741 | 1935.42 | david     | 2008-10-23 10:09:24 |
| 10485742 | 1955.42 | livia     | 2008-10-23 10:09:24 |
| 10485743 | 2137.82 | leo       | 2008-10-23 10:09:24 |
| 10485744 | 2134.46 | lucy      | 2008-10-23 10:09:24 |
| 10485745 | 2934.46 | simon     | 2008-10-23 10:09:24 |
| 10485746 | 4134.46 | sony      | 2008-10-23 10:09:24 |
| 10485747 | 4134.46 | rick      | 2008-10-23 10:09:24 |
| 10485748 | 1774.46 | anne      | 2008-10-23 10:09:24 |
| 10485749 | 1774.46 | sarah     | 2008-10-23 10:09:24 |
| 10485750 | 5338.46 | john      | 2008-10-23 10:09:24 |
| 10485751 | 1735.42 | david     | 2008-10-23 10:09:24 |
| 10485752 | 1755.42 | livia     | 2008-10-23 10:09:24 |
| 10485753 | 1937.82 | leo       | 2008-10-23 10:09:24 |
| 10485754 | 1934.46 | lucy      | 2008-10-23 10:09:24 |
| 10485755 | 2734.46 | simon     | 2008-10-23 10:09:24 |
| 10485756 | 3934.46 | sony      | 2008-10-23 10:09:24 |
| 10485757 | 3934.46 | rick      | 2008-10-23 10:09:24 |
| 10485758 | 1574.46 | anne      | 2008-10-23 10:09:24 |
| 10485759 | 1574.46 | sarah     | 2008-10-23 10:09:24 |
| 10485760 | 5138.46 | john      | 2008-10-23 10:09:24 |
+----------+---------+-----------+---------------------+
20 rows in set (4.88 sec)

分页表：

mysql> select a.* from t_group as a inner join t_group_ids as b where a.id = b.g
roup_id and b.id = 524288;
+----------+---------+-----------+---------------------+
| id       | money   | user_name | create_time         |
+----------+---------+-----------+---------------------+
| 10485741 | 1935.42 | david     | 2008-10-23 10:09:24 |
| 10485742 | 1955.42 | livia     | 2008-10-23 10:09:24 |
| 10485743 | 2137.82 | leo       | 2008-10-23 10:09:24 |
| 10485744 | 2134.46 | lucy      | 2008-10-23 10:09:24 |
| 10485745 | 2934.46 | simon     | 2008-10-23 10:09:24 |
| 10485746 | 4134.46 | sony      | 2008-10-23 10:09:24 |
| 10485747 | 4134.46 | rick      | 2008-10-23 10:09:24 |
| 10485748 | 1774.46 | anne      | 2008-10-23 10:09:24 |
| 10485749 | 1774.46 | sarah     | 2008-10-23 10:09:24 |
| 10485750 | 5338.46 | john      | 2008-10-23 10:09:24 |
| 10485751 | 1735.42 | david     | 2008-10-23 10:09:24 |
| 10485752 | 1755.42 | livia     | 2008-10-23 10:09:24 |
| 10485753 | 1937.82 | leo       | 2008-10-23 10:09:24 |
| 10485754 | 1934.46 | lucy      | 2008-10-23 10:09:24 |
| 10485755 | 2734.46 | simon     | 2008-10-23 10:09:24 |
| 10485756 | 3934.46 | sony      | 2008-10-23 10:09:24 |
| 10485757 | 3934.46 | rick      | 2008-10-23 10:09:24 |
| 10485758 | 1574.46 | anne      | 2008-10-23 10:09:24 |
| 10485759 | 1574.46 | sarah     | 2008-10-23 10:09:24 |
| 10485760 | 5138.46 | john      | 2008-10-23 10:09:24 |
+----------+---------+-----------+---------------------+
20 rows in set (0.01 sec)

http://blogold.chinaunix.net/u/29134/showart_1333566.html

上面转来的
但是这样如果是静态的数据没问题，但是插入的时候成本有点高！还有如果需要删除1条数据的话也是代价比较大的！
如果是没有多条件查询的话会相对简单一些！可以给id大于或者小于 》1000 limit 10 也会提高一些性能！
还在了解中
标签
#mysql #优化 #user #duplicates #null
#warnings
百万级数据采集优化：C# 上位机的数据存储与查询性能提升
这里只有干货：从 Modbus 通信到 MES 对接，从 YOLO 训练到工控机部署，带你搞定工业软件开发全流程。
09-06 1599
摘要： 本文探讨了C#上位机在百万级工业数据场景下的存储与查询性能优化方案。针对高频率、高并发、海量数据的特点，提出采用关系型数据库（如SQL Server）结合时序数据库的混合架构，通过分区表、批量插入（SqlBulkCopy）、复合索引等技术提升存储效率。查询优化包括分页查询（OFFSET-FETCH）、预聚合、Redis缓存及异步处理（async/await）。此外，建议采用数据压缩和定期归档策略降低存储压力。这些方法可显著提升工业自动化系统中实时监控与历史数据分析的性能，满足物联网时代的数据处理需求
MySQL大数据量的分页查询优化
桐花思雨
08-16 2169
目录背景数据准备 背景 随着业务发展越来越快，原来的数据库数据从几万突破到几百万，分页的查询策略是否需要调整一下？ 数据准备 1、表名：order_history 2、描述：某个业务的订单历史表 3、主要字段：unsigned int id，tinyint(4) int type 4、字段情况：该表一共 37 个字段，不包含 text 等大型数据，最大为 varchar(500)，id 字段为索引，且为递增 5、数据量：5709294 6、MySQL 版本：5.7.16 ...
MySQL大表优化方案分享
AI天才研究院
09-13 3418
作者：禅与计算机程序设计艺术 1.简介 随着互联网应用的快速发展、数据量的激增和用户需求的不断变化，网站的数据库容量已经成为一种难题。如何让网站的性能更高、体验更流畅？如何降低数据库的空间占用率、提升查询速度？本文将分享MySQL大表优化的思路，主要包括： 1）建表优化：减少字段类型、字段
mysql查询优化count(*)-查询记录总条数（一）
热门推荐
bigwood99的博客
01-15 3万+
计算表记录总量最直接的方法就是使用count() 比如：select count(*) from 表名; 也有人喜欢这么写： select count(字段名) from 表名; 这两种写法都可以得到结果，不过效果略有不同。 count(*)一定返回的是表记录总量 count(字段名)返回的是该字段不为null值的记录总量。它有可能跟count(*)相等，也可能不等。 所以要慎用co...
使MySQL引擎使用索引避免全表扫描的sql查询优化
lzf的博客
12-09 2310
本文主要内容： 1：查询语句where 子句使用时候优化或者需要注意的 2：like语句使用时候需要注意 3：in语句代替语句 4：索引使用或是创建需要注意 解决办法就是 中的这一句 HXZGSB00265Request.setQcs(new JSONObject()); 把期初数设为0
Mysql查询优化之 触发器加中间表 方法优化count()统计大数据量总数问题
a631278993的博客
12-06 961
转载请注明原文地址：http://www.cnblogs.com/ygj0930/p/6138288.html 在上一篇博文我们提到，分页有三种方法。其中，第三种是我们最常用的。然而，在实际应用过程中我们会发现，select count(*) from tname 语句在统计某表内记录总数时，如果表内数据量达到一定规模（比如100W条），这个语句就会执行得非常慢。有什么...
史上最全MySQL 大表优化方案（长文）
04-23 5263
当MySQL单表记录数过大时，增删改查性能都会急剧下降，可以参考以下步骤来优化： 单表优化 除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间，甚至能正常支撑千万级以上的数据量： 字段 1、尽量使用TINY...
MySql 百万级数量查询优化总结
piaolaoshi的博客
09-25 3647
MySql 百万级数量查询优化总结
MySQL百万级数据分页查询及优化
qq_34874784的博客
05-23 270
MySQL百万级数据分页查询及优化 java知路java知路今天 方法1: 直接使用数据库提供的SQL语句 语句样式:MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N 适应场景:适用于数据量较少的情况(元组百/千级) 原因/缺点:全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,...
MySQL 数据优化技巧：提升百万级数据聚合统计速度
沉淀所学，分享所思，让热爱与成长同行。商务记录AI实战经验，助力开发者快速成长。 热爱AI，希望做出有影响力的技术成果。 技术点亮生活，分享连接价值与机会。 专注AI落地实战，陪你走好技术每一步。
02-26 3538
作为一名技术博主，我们经常面对诸如 MySQL 数据库中百万级别数据的聚合统计速度问题。本文将深入探讨如何优化 MySQL 数据库以提升聚合统计速度，涵盖了索引优化、查询优化以及适当的数据库设计，旨在帮助您充分了解并解决这一挑战。通过对 MySQL 数据库的索引优化、查询优化和数据库设计优化，可以有效提升百万级别数据聚合统计的速度。合理的索引设计、优化的查询语句和良好的数据库结构是提高性能的关键。希望本文提供的优化技巧能够帮助读者解决实际应用中的性能问题。
MySQL 百万级分页优化(Mysql千万级快速分页)
liDE博客
10-17 1万+
一般刚开始学SQL的时候，会这样写 SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候，这样写会慢死 SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 SELECT * FROM table WHERE id >= (SE
MySQL百万级/千万级数据存储解决方案
最近学习目标：MySQL，Redis
12-06 5269
MySQL 百万级/千万级数据存储解决方案 百万级、千万级数据处理，个人认为核心关键在于数据存储方案设计，存储方案设计的是否合理，直接影响到数据CRUD操作。总体设计可以考虑一下三个方面进行设计考虑： 数据存储结构设计 索引设计 数据主键设计 查询方案设计 百万级数据处理方案： 数据存储结构设计 表字段设计 表字段 not null，因为 null 值很难查询优化且占用额外的索...
MySQL查询优化实战巧用索引提升百万级数据性能
最新发布
eric.zhang的博客
10-10 368
在处理百万级数据时，需要根据查询模式选择最合适的索引类型，例如，对于频繁进行等值查询的列可以考虑哈希索引，而对于需要范围查询或排序的列，B-Tree索引是更好的选择。例如，在(col1, col2, col3)上创建了复合索引，那么查询条件包含(col1)、(col1, col2)或(col1, col2, col3)时都可以使用该索引，但条件只包含(col2)或(col2, col3)则无法使用。合理设计复合索引的列顺序，可以将多个查询的过滤条件整合到一个索引中，减少索引数量，提升写入性能。
mysql数据库查询优化
Wildpen的成长之路
07-31 6178
1.硬件方面 硬件方面优化是成本最高却效果最差的一种优化。 服务器性能对数据库的读写能力还是有一定的影响的，尤其体现在磁盘I/O上。mysql数据库频繁的CURD操作是十分吃磁盘I/O的，一般可以认为磁盘I/O是硬件方面制约mysql数据库性能的最大因素。 解决方法：目前一般使用RAID0-1磁盘阵列来解决。 2.系统配置 下面是已经配置好的my.cnf [mysqld] po...
Mysql查询性能优化
鸽子窝下蛋
02-28 7100
      Mysql查询性能优化要从三个方面考虑，库表结构优化、索引优化和查询优化。通常在实际应用中，我们要面对这三种搅和一起的情况。一、 库表结构优化      良好的逻辑设计和物理设计是高性能的基石。库表结构的设计既要关注全局，又要专注细节。要设计优秀的库表结构，可从以下几个方面着手：1.  选择优化的数据类型         选择优化的数据类型可以遵循以下几个原则：         更小的...
30多条mysql数据库优化方法,千万级数据库记录查询轻松解决
chiqiaoci452622的博客
08-07 1073
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描， Sql 代码 : select id from t where...
mysql-sql优化之-查询总条数
02-29 3万+
mysql之查询总条数
mysql查询条数优化_MySQL优化总结-查询总条数
weixin_31262911的博客
02-04 283
1、COUNT(*)和COUNT(COL)COUNT(*)通常是对主键进行索引扫描，而COUNT(COL)就不一定了，另外前者是统计表中的所有符合的纪录总数，而后者是计算表中所有符合的COL的纪录数。还有有区别的。优化总结，对于MyISAM表来说：1.任何情况下SELECT COUNT(*) FROM tablename是最优选择；2.尽量减少SELECT COUNT(*) FROMtablena...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值