如何优化Mysql千万级快速分页

很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。

我们先从一个常用但性能很差的查询来看一看。

SELECT

FROM city

ORDER BY id DESC

LIMIT 0, 15

这个查询耗时000sec。So，这个查询有什么问题呢？实际上，这个查询语句和参数都没有问题，因为它用到了下面表的主键，而且只读取15条记录。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

真正的问题在于offset(分页偏移量)很大的时候，像下面这样：

SELECT

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

上面的查询在有2M行记录时需要022sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。Facebook意识到了这一点，但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。

对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。

SELECT COUNT()

FROM city;

然而，上面的SQL在采用InnoDB为存储引擎时需要耗费928sec。一个不正确的优化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句 select FOUND_ROWS(); 就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。不幸的是，这种分页查询方式在许多主流框架中都有用到，下面看看这个语句的查询性能。

SELECT SQL_CALC_FOUND_ROWS

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

这个语句耗时2002sec，是上一个的两倍。事实证明使用 SQL_CALC_FOUND_ROWS 做分页是很糟糕的想法。

下面来看看到底如何优化。文章分为两部分，第一部分是如何获取记录的总数目，第二部分是获取真正的记录。

高效的计算行数

如果采用的引擎是MyISAM，可以直接执行COUNT()去获取行数即可。相似的，在堆表中也会将行数存储到表的元信息中。但如果引擎是InnoDB情况就会复杂一些，因为InnoDB不保存表的具体行数。

我们可以将行数缓存起来，然后可以通过一个守护进程定期更新或者用户的某些 *** 作导致缓存失效时，执行下面的语句：

SELECT COUNT()

FROM city

USE INDEX(PRIMARY);

获取记录

下面进入这篇文章最重要的部分，获取分页要展示的记录。上面已经说过了，大的偏移量会影响性能，所以我们要重写查询语句。为了演示，我们创建一个新的表“news”，按照时事性排序(最新发布的在最前面)，实现一个高性能的分页。为了简单，我们就假设最新发布的新闻的Id也是最大的。

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

一个比较高效的方式是基于用户展示的最后一个新闻Id。查询下一页的语句如下，需要传入当前页面展示的最后一个Id。

SELECT

FROM news WHERE id < $last_id

ORDER BY id DESC

LIMIT $perpage

查询上一页的语句类似，只不过需要传入当前页的第一个Id，并且要逆序。

SELECT

FROM news WHERE id > $last_id

ORDER BY id ASC

LIMIT $perpage

上面的查询方式适合实现简易的分页，即不显示具体的页数导航，只显示“上一页”和“下一页”，例如博客中页脚显示“上一页”，“下一页”的按钮。但如果要实现真正的页面导航还是很难的,下面看看另一种方式。

SELECT id

FROM (

SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt

FROM news

JOIN (SELECT @cnt:= 0)T

WHERE id < $last_id

ORDER BY id DESC

LIMIT $perpage $buttons

)C

WHERE cnt = 0;

通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset Id，这个问题就迎刃而解了。Mark Callaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。

如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。

SET p:= 0;

UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。

UPDATE pagination T

JOIN (

SELECT id, CEIL((p:= p + 1) / $perpage) page

FROM news

ORDER BY id

)C

ON Cid = Tid

SET Tpage = Cpage;

现在想获取任意一页的元素就很简单了：

SELECT

FROM news A

JOIN pagination B ON Aid=BID

WHERE page=$offset;

还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))

SELECT id, FLOOR(RAND() 0x8000000) random

FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;

接下来就可以向下面一样执行分页查询了。

SELECT

FROM _tmp

WHERE OFFSET >= $offset

ORDER BY OFFSET

LIMIT $perpage;

简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。

电子商务网站的系统设计

1．电子商务的基本概念

电子商务是利用计算机及互联网开展的各种商务活动。其中电子是手段，商务是目的。是通过网站的商务运作和会员制收费，达到盈利的目的。

电子商务包括以下三部分内容：

电子：指信息基础设施及相关应用系统，其中信息基础设施包括internet网络基础和信息技术，应用系统应包括支持电子商务活动的网站。

商务：指业务内容、流程及规则，这是电子商务网站系统设计的基础和依据。

信息：指业务活动中的数据，应完整、全面、实时、动态。业务活动所使用的数据也是网站系统数据库设计的依据。

Internet技术、信息技术系统和商务过程的有机集成形成了一个新的商务模型，即电子商务模型。

2．电子商务网站的基本架构设计

电子商务网站是以商务活动为中心进行的，而网站的盈利一般通过网站的会员制收费进行，网站的盈利点是网站根据网站的商务活动内容确定的，所以网站的基本架构设计既要以商务活动的业务内容、流程、相关规则为基础，又要兼顾电子商务网站的收费体系。

网站基本架构的设计主要根据以下步骤进行：

21确定电子商务网站功能定位

确定网站所涉及的商务活动的内容、商务活动的流程。比如我们在进行房产信息网的设计中，首先考虑确定网站发布房产信息的种类，确定了房源信息包括中介所的房源信息和个人的出售、出租信息，网站负责信息的发布和信息的管理。同时在确定了信息发布种类后，确定了信息处理的流程为房源信息输入、会员资格审核、信息审核，信息发布。

22确定网站的收费对象和收费规则

在网站所涉及的商务内容确定了的情况下，确定收费的对象和如何进行收费，以此为依据确定网站的栏目。网站栏目的划分实际上就是系统的功能模块划分。在房产网站的系统设计中，确定了网站只对房产中介所进行收费，个人用户免费，所以网站的主要栏目分为个人专区和中介所专区两个主要栏目，同时根据功能的逐步扩大，这样也就基本确定了网站的信息服务内容和方式。

23确定网站的栏目的功能

在确定了网站的收费项目后，要确定网站的主要栏目和功能，包括网站的管理功能模块、网站的信息发布方式、网站商务活动的发布以及网站导航栏等。

网站的功能栏目的设置和系统的主要功能模块的划分是相一致的。

网站业务介绍性栏目，应包括内容应包括会员申请流程，收费标准，网站运行规程等，使用户对网站的服务有一个明确的了解，是扩大网站的会员用户数量和提高网站的使用率都是必不可少的栏目。

网站的导航栏是网站的整体功能的全面介绍，使用户对网站的功能有一个清晰的了解，也是网站不可缺少的栏目。

同时也应有网站运行的相关提示信息，比如在房产网站的设计中，我们在确定了收费对象和主要功能后，确定了网站首页的主要栏目为中介所专区、个人专区、写字间专区、新房楼市等栏目，同时加入了上网导航栏目对网站的主要功能进行介绍。

24确定网站的信息流和控制流

在确定了网站的主要功能和商务活动的主要规则后，应该确定网站的信息流图和控制流图，作为数据库设计的基础。

在房产网的设计中，我们根据房产信息发布的功能和所确定的信息审核和控制流程，确定房产网的基本数据流图为：

实例：一个网站的数据流图

在确定了一个网站的数据流图和控制流后，系统的运行控制流程也就确定下来了。

3．网站的后台管理

在网站的基本功能和数据流确定后，为了保证网站信息的准确性和有效性，应有完善的后台管理和维护系统，进行相关数据的审核，定期进行数据库的维护和备份，进行缴费会员资格的管理，有效的保证网站的商务运作。

我们在房产信息发布网站的后台管理系统的系统设计中，设计了一套完整的网站后台管理系统，主要功能包括房源信息管理如：房产信息审核、房产信息删除、房产信息删除确认；网站运行提示信息的管理，主要是对网站与商务运营有关的信息进行管理，使网站的用户对网站的运行情况进行管理；网站会员资格的审核，对逾期未缴费的用户取消会员资格；网站系统管理员权限管理，对不同的网站系统管理人员进行授权使用不同的后台维护功能。

4．网站的数据库设计

在确定了网站的主要商务的业务对象和业务流程后，可以确定了网站的数据流，也就可以进行数据库设计。在进行数据库设计时，同样和一般的应用系统开发一样，应该注意信息的完整性和数据的独立性。

我们在房产网站的开发过程中，在数据库的设计阶段，对系统的数据库按房源的基本信息、中介所信息、个人信息分别进行库表的设计，同时对系统的维护信息、权限管理等控制信息设计独立的库表，主要的数据库表为房源信息表、中介所信息表、会员信息表等，这样可以方便网站的信息输入、数据库查询同时也方便网站后台的数据库管理和数据库维护。

数据库表数据的独立性和数据冗余直接影响数据的存取效率，影响网站的运行速度，所以在数据库设计时一定要避免数据的冗余性，同时要避免长数据库表的设计。

总结：

在电子商务网站开发过程中，网站的商业运作模式决定了网站系统设计，一个功能清晰的网站的设计，一定要从网站的系统设计入手。

        今天使用中继器来实现一个简单的二级导航栏。这是一款折叠形可变导航，一般应用在系统页面左侧。它的最大优势是：1、菜单根据中继器配置的列表动态加载整个导航菜单（后期系统维护升级可快速增减菜单）。2、二级导航菜单数量不等也可以正常工作（突破局限）。

        下面以制作一个简单的后台管理系统导航为例。

一、准备元件

1、将工作界面划分为三块：头部放一个动态面板，里面放系统logo和系统名称；左侧导航区域放一个动态面板，里面放一个中继器；右侧放一个内联框架(动态交互区域)。

2、双击中继器，进入中继器编辑模式。复制矩形元件并粘贴多三个，共四个按右对齐排列。如下图所示，一级导航（w:180px，h：30px），二级导航（w:160px，h:25px）。注意，各矩形件元件间的行高是10px，各矩形元件的填入文字及命名见图。

将所有二级导航选中，将它们设置为一个组合，命名为：组合_二级导航。这个组合用于点击一级导航进行交互时，实现组合整体展开/收缩（隐藏/显示）。

再将它们设置为一个选项组，命名为：选项组_二级导航。这个选项组用于点击二级导航进行交互时，实现只选中一个二级导航。 注意 ：要将所有二级导航选中，右键选中：选项组，命名为：选项组_二级导航。 不是将它们的组合设置为选项组 。

同样，将一级导航也设置为一个选项组，命名为：选项组_一级导航。

二、设置元件样式

1、设置中继器的行距。即间距为10px。

2、设置一级导航和二级导航的鼠标悬停、选中的样式。一级导航和二级导航鼠标悬停样式设置， 填充 颜色为：FFCC99；一级导航选中样式设置， 字色 为：FFCCCC；二级导航选中样式设置， 填充 颜色为：FFCCCC;

三、设置导航数据。以后系统升级，增减导航就是通过设置导航数据来完成。

1、设置中继器样式数据。选中：中继器，选中：样式。

2、设置样式数据列名。双击第一列：Column0，重新命名为：FirstLevel。这一列对应的是一级导航。双击第二列，命名为：SecLevel1。重复 *** 作，得到 SecLevel2、SecLevel3。SecLevel1、SecLevel2、SecLevel3分别对应的是二级导航1、二级导航2、二级导航3。

3、设置样式数据内容。这是导航显示的结构数据，或者说，导航将根据设置的样式数据内容来显示。以后系统升级，只要修改这个数据内容即可。根据需求，这里设置的数据如下：

四、设置交互

1、设置载入时事件。在载入元件时，根据显示样式数据设置的值作为导航文本。

选中：中继器，选中：交互。选择事件：载入时，选择：设置文本，选择：添加目标。选中：一级导航。点击 fx，d出编辑框。如下图：

删除中间输入框中的：一级导航。点击：插入变量或函数，选中：ItemFirstLeve，得到结果。如下图：

同样，设置二级导航1、二级导航2、二级导航3 的 fx 值分别为 ItemSecLeve1、ItemSecLeve2、ItemSecLeve3。得到结果，如下图：

2、设置每项加载事件。在每项加载时，根据显示样式数据设置的值显示或隐藏二级导航。从而实现二级导航菜单数量不等也可以工作。

选中：中继器，选中：交互。选择事件：每项加载，选择：启用情形。添加条件，第一个框选中：值，点击第二个框：fx，选中：ItemSecLevel1。如下图：

在此情形下，添加动作，选择：显示/隐藏，添加目标：二级导航1，选中：隐藏动作。这个设置是实现当二级导航1对应的数据值为空时，隐藏该二级导航菜单选择项。同样，设置二级导航2、二级导航3。得到结果，如下图：

3、设置二级导航的组合整体展开/收缩。即当点击一下一级导航时，或展开，或隐藏其下面全部二级导航。

选中：一级导航，点击：新建交互，选择：单击时，选择动作：显示或隐藏。选择目标：组合_二级导航，选择：切换。设置参数，如下图：

如果想实现初次加载导航时，二级导航是折叠的。即只看到一级导航。则可以将二级导航的组合设置为隐藏。

双击：中继器，选中：组合_二级导航，右键，选择：设为隐藏。

4、设置二级导航的单击事件。二级导航选中时，父级也选中。同时，在内联框架中打开链接页面。

选中：二级导航1，选择事件：单击时，点击：启用情形，点击：添加条件，选择：值，点 fx，点击：插入变量或函数，选择：ItemSecLevel1，设置值：角色列表。添加动作：设置选中，选择目标：二级导航1，值为真。添加目标，选中：一级导航，值为真。添加动作：框架中打开链接，选择元件：内联框架，选择页面：角色列表。

同样，添加情形2，设置二级导航1对应的第二行数据：系统日志。如果有第3行，也同样设置。得到结果，如下图：

同样，设置：二级导航2、二级导航3 的单击事件。

至此，使用中继器来实现二级导航栏制作完毕。按F5预览下效果。

以上就是关于如何优化Mysql千万级快速分页全部的内容，包括:如何优化Mysql千万级快速分页、一个完整的网站包含几层结构,每层的作用是什么啊、Axure中继器实现二级导航栏等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！