文章目录

• • 千万级别MySQL分页查询优化
  • • mysql环境
    • MySQL查询语句中的limit
    • 剖析分页查询流程
    • 高性能分页查询
    • • 高性能分页查询----百万级别分页处理
      • 超高性能处理亿级数据---倒排序+分块查询
      • • 分块处理+倒排序查询
  • 问题不足
  • 参考文档

千万级别MySQL分页查询优化

mysql环境

1. id为自增主键
2. 条件查询字段添加了索引
3. 多个条件查询迎合了组合索引

MySQL查询语句中的limit

1. MySQL的limit是对查询结果集合的封装，主要分为2部分， limit m 和 limit m,n limit m 表示对查询的结果取m条，当查询m条后就不再查了。 limit m,n ：表示查询的数据中从m条记录开始，取n条。注意这个m是从0开始的，即：0是第一条，取第一条就是limit 0,1，limit的m并未包含，属于开区间

剖析分页查询流程

1. 获取某个条件下数据的总数量

SELECT count(*) FROM `geoip_online_landmark` WHERE source = 'APP' AND time >  '2015-06-09'

2. 通过获取某个条件下的数据总数量，计算总页数

int totalPage = allCount % pageSize == 0 ? allCount / pageSize : allCount / pageSize + 1;

3. 根据用户的请求页数计算出 limit m,n 中的m的值，n的值为页面大小

//为什么-1？因为：当取前10行时，是limit 0,10 ,当取第2页时，是limit 10,10  =(2-1)*10
m =(currPage - 1) * pageSize

4. 普通的分页查询，根据m值，直接limit m,n

ELECT
        id,
        INET_NTOA(numip) as ip, `source`, latitude, longitude, date_format(`time`, '%Y-%m-%d %h:%i:%s' ) as time
        FROM
        `geoip_online_landmark` limit 10,10

5. 这种普通的分页查询可以应付50w以下的数据量，如果数据量较大就变得非常卡顿。

高性能分页查询

高性能分页查询----百万级别分页处理

1. 主要思想：获取每页的启始记录的id，通过id快速定位到所在的id，然后limit 10取出10行记录(默认有排序情况)
2. 问题的关键点在于快速查找到每页启始记录的id

	 # currPage是前端传来的页数，通过limit m,n获取第1页内容，m是从0开始计算，且不包含第m条记录
   SELECT
        id
        FROM
        `biz_iplocation_db`.`geoip_online_landmark` where time > '2015-06-02' ORDER BY id DESC limit (currPage-1)*10,1

3. 通过获取的id，通过id跳跃limit出10条记录

# 其中recordId就是上面sql语句查询的id
SELECT
        id,
        INET_NTOA(numip) as ip, `source`, latitude, longitude, date_format(`time`, '%Y-%m-%d %h:%i:%s' ) as time
        FROM
        `biz_iplocation_db`.`geoip_online_landmark` where id>=recordId limit 10

超高性能处理亿级数据—倒排序+分块查询

1. 核心原理和上面的根据id进行查询分页相同
2. 当数据达到1亿的数据量，当用户点击最后一页的时候就会非常的耗时，超过1800s，无法忍受

分块处理+倒排序查询

1. 根据数据量分成2份，前半份用正排序查询，后半份用倒排序查询，理论上会提升2倍性能
2. 最终结果会出现两边页数查询快中间页数查询慢
3. 倒排序例子：

# 获取倒排序id
# 业务逻辑：计算出倒排序的偏移量
#int record = allCount-landmarkReqVO.getCurrPage()-1;
#landmarkReqVO.setRecord(record);
#landmarkReqVO.setAllCount(allCount);
SELECT
        id
        FROM
        `biz_iplocation_db`.`geoip_online_landmark` where source='App',time>'2021-05-06' ORDER BY id DESC limit #{record},1

• 根据id倒排序查询出数据

 SELECT
        id,
        INET_NTOA(numip) as ip, `source`, latitude, longitude, date_format(`time`, '%Y-%m-%d %h:%i:%s' ) as time
        FROM
        `biz_iplocation_db`.`geoip_online_landmark` where source='APP' and time>='2020-12-4' and  AND id>= #{recordId} ORDER BY `id` DESC limit #{pageSize}

• 结果：当用户点击中间的页数时，是最慢的情况，测试1亿条记录的结果为：3分钟响应

问题不足

1. 通过测试发现当添加多个条件查询时，耗时最长的sql语句为计算符合条件的总记录数

SELECT count(*) FROM `geoip_online_landmark` WHERE source = 'APP' AND time >  '2015-06-09'

• 建议添加复合索引

参考文档

1. MySQL千万级数据查询优化：https://www.jb51.net/article/127728.htm
2. MySQL查询语句中limit使用：https://www.cnblogs.com/cai170221/p/7122289.html
3. MySQL执行计划的使用：https://blog.csdn.net/u011431326/article/details/104119611
4. MySQL分页查询太慢优化：https://blog.csdn.net/liuxl57805678/article/details/91377203