mysql count函数与分页功能极限优化

场景：#

某后台的功能列表，页面底部为通用分页：
总条数: 16209321 页码：1 2 3 4 5 .... 9819
页面默认展示 10 条数据，默认展示条数可选。
页面上部分搜索区域部分有多达 20-30 的筛选条件，筛选条件分别来自于不下 10 张数据表。拿订单列表查询举例，可以使用用户表里的某个特殊字段进行筛选，如性别等，这些字段肯定不会在订单表存储，所以必然会进行联表。
使用者常常有疑问：为何页面只有 10 条数据，查询却如此之慢？
老板会质疑你，做的是什么玩意？查询 10 条数据都要 1 分钟以上的时间？（优化前页面需要转 1 分钟才可显示出数据，页面转圈圈～）

优化：#

第一次优化：#

排查性能问题，首先想到的是优化数据表索引以及 sql 语句，一般考虑的点有下
1、索引是否有问题？是否按照该表的实际使用场景建立了合适的复合索引？
2、编写的 sql 是否符合索引规范？是否使用了索引？
3、是否使用了合适的索引？这点也比较关键。

通过分析可知，依旧拿订单举例，后台查询条件会默认时间字段，查询近一个月的，按照字段查询常用的顺序，分别建立复合索引。
1、以我们的业务举例，最长使用的字段查询依次为：按时间查、按销售地域查、按合同查、按客户查、按订单号查，所以建立对应复合索引。
2、优化查询 sql，将 where 语句中的查询条件按顺序排列，一定要按照最左匹配原则。
3、当使用 Explain 关键字进行仔细调试后，最终此次调优效果成功将接口时间提升一倍以上，查询某月数据（一个月），索引 type 为 range 级别，最终优化优化后，时间来到了：50 秒

第二次优化：#

在第一次优化后艰难使用一段时间后，依然遭到投诉，想了想，遭到投诉也是必然的，50 秒开一个页面。决定再次优化，本次的考虑点如下：
1、由于时间慢是因为 count 语句造成，count 语句大约需要 40 多秒的时间，实际数据查询经过了第一次优化只需要不到十秒。而 count 函数又是必不可少的，因为需要查询总数，以供分页显示总条数及最后一页，所以必须从 count 优化入手
2、如果技术上优化遇到了瓶颈，或者说 mysql 已经优化到极致，那么能否从业务上解决？

优化分析主要从两个角度进行。

1、从技术角度来看，查询必有筛选条件，由于几十个筛选条件的取值不确定性，通过缓存 count 的总条数是无法满足的。继续观察 mysql 索引情况，由于现有索引的 key_len 过大，可以通过建立较小的索引 (使用小字段) 来为排序使用，由于我们的业务查询必有时间段条件，固为时间段字段单独建立索引，由此带来了几秒的性能提升 (并不明显)。

2、从业务角度看，我们观察了百度、google、微博等网站，分页都不会显示结果集的总条数以及也不会有最后一页的链接，都是通过点击下一页的方式不断检索后面的数据，也就没有使用 count 函数的必要了，经过与需求方沟通，发现我们的想法并不复合需求，由于是后台管理系统，是需要通过筛选条件实时查询总条数，固此优化想法 pass。
3、通过沟通，得到另一个优化方向，可以将前端分页组件异步加载。首先将数据列表展示出来，方便其它操作。固修改前端，分别掉 2 次接口，获取数据接口、获取分页结果接口。分页结果接口返回数据前，分页组件显示 “正在努力加载…” 字样。
此种优化最终实现：列表数据加载 <5 秒，分页组件加载 >40 秒

第三次优化：#

通过学习研究发现，mysql innodb 引擎在有索引、有 where 条件的情况下，count 速度并不慢，所以问题一样还出在 sql 上。

1, 通过分析 sql 发现，由于查询条件众多，只是对 where 语句后面的 sql 进行了动态 sql 处理，而 left join 语句没有进行动态 sql 处理，导致不管有几个查询条件，都需要链接 10 张左右其它数据表。是链接查询限制了 sql 的性能！而不是 count 限制的 sql 的性能！固做以下优化，将 left join 一并使用动态 sql 链接：

 SELECT
    count(*)
 FROM
    xxx t
    <choose>
        <when test="查询条件1存在">
            LEFT JOIN aaa uoc ON (t.user_id = uoc.user_id)
        </when>
        <when test="查询条件2存在">
            LEFT JOIN ccc uoc ON (t.user_id = uoc.user_id)
        </when>
         <otherwise>
        </otherwise>
    </choose>
    <if test="查询条件3存在">
        LEFT JOIN ddd pcc ON t.center_id = pcc.id
    </if>
    ....等其它N张链接表
    WHERE
        t.cycle_start_time BETWEEN #{x1} AND #{x2}
    <if test="查询条件1存在">
        AND uoc.user_id = #{x3}
    </if>
    <if test="查询条件2存在">
        AND uoc.alias = #{accountInstanceDTO.userName}
    </if>
    <if test="查询条件3存在">
        AND t.product_line = #{x4}
    </if>
    ... 等其余N个查询条件

与需求方沟通后，查询列表可去掉排序规则，使用默认排序即可。此点优化也十分重要，order by 会导致 file sort，而极大的影响查询性能，所以我们去掉了 order by 语句，而使用默认的 id 排序。
最终优化：列表数据查询 130ms，分页插件查询 (count 查询)150ms，目前数据单表数据量级在 2000 万左右，以次时间效率推断，多查询条件的复杂分页查询，可以支持单表几亿没有问题。