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万左右，以次时间效率推断，多查询条件的复杂分页查询，可以支持单表几亿没有问题。