mysql 千万级别分页查询实战与数据库优化

rainbowcheng

已于 2023-07-07 11:42:09 修改

阅读量1.9k

点赞数 1

分类专栏：数据库架构文章标签： mysql 大数据分布式数据库

于 2019-04-10 10:58:25 首次发布

本文链接：https://blog.csdn.net/t2bbs20930/article/details/89174302

版权

架构同时被 2 个专栏收录

11 篇文章 0 订阅

订阅专栏

数据库

10 篇文章 1 订阅

订阅专栏

更多文章：RAINBOW知识管理系统

数据库（版本5.7.x）----表 product 商品信息表（远程连接服务器数据库，可能存在网络延时误差）

SELECT count(*) from product ;                    #查询时间 12.681s~25.270s
SELECT count(id) from product ;                   #查询时间 14.642s
SELECT count(1) from product ;                    #查询时间 12.512s
//结论区别不大

//--默认走基数最小的索引（order by id 走主键索引）
#万
SELECT id from product LIMIT 10000, 10 ;                  #查询时间 0.026s
SELECT id from product LIMIT 100000, 10  ;                #查询时间 0.039s
SELECT *  from product force index(id) LIMIT 10000,10;    #强制选择索引

#百万
SELECT id from product LIMIT 1000000, 10  ;                             #查询时间 0.185s
SELECT id from product ORDER BY id LIMIT 1000000, 10;                   #查询时间 0.560s
SELECT id from product where mode=0 ORDER BY id LIMIT 1000000, 10;      #查询时间 0.258s
SELECT id from product where id>1000000 ORDER BY id  limit 10;          #查询时间 0.023s
#备注：
        * id>走的range【40290302行】  limit 1000000走的是index【1000010行】类型
        * mode=0走的ref【2632436行】

SELECT id from product LIMIT 5000000, 10  ;               #查询时间 0.829s

#千万
SELECT id from product LIMIT 10000000, 10  ;                            #查询时间 2.141s
SELECT id from product ORDER BY id LIMIT 10000000, 10;                  #查询时间 5.284s
SELECT id from product where id>10000000 ORDER BY id  limit 10;         #查询时间 0.026s
SELECT id from product where id>10000000 ORDER BY id  limit 1000;       #查询时间 0.046s
SELECT *  from product where id>10000000 ORDER BY id  limit 10;         #查询时间 0.051s
#备注：
        * id> 或 id in 走的range【40291705】  index【10000010】；
        * limit 1000 比10时间长【分页IO】;
        * select * 时间用于聚集索引；（切记index类型用*，那样索引会失效）

SELECT id from product LIMIT 20000000, 10  ;              #查询时间 3.322s
SELECT id from product LIMIT 80000000, 10  ;              #查询时间 13.071s

type性能俳优：const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

const：读常量，只需要读取一次；eq_ref：最多只会有一条匹配结果，一般通过主键或者唯一键索引访问；ref：join中被驱动表索引引用查询；index_merge：使用两个以上索引，然后结果合并，再读取表数据；

index_subquery：子查询中返回结果字段组合是一个索引，但不是主键或者唯一索引；range：索引范围扫描；index：全索引检索；all：全表扫描。

SELECT id from product where id>10000000 ORDER BY id  limit 10;
SELECT id,mode,name from product where id>10000000 ORDER BY id  limit 10;
id    select_type     table      type      key        key_len     rows      Extra
1        SIMPLE       product    range    PRIMARY       8       40291705    Using where; Using index
1        SIMPLE       product    range    PRIMARY       8       40291705    Using where

#备注：因为走了range（虽然第2条没有走索引）   两句执行的时间是一样的！！！！！！！【时间: 0.024s】

【Extra】using where：只是过滤元组，和是否读取数据文件或索引文件没有关系；using temporary：必须使用临时表，常用group by或者order by；using filesort：无法利用索引完成排序操作，使用外部的排序；

using index：所需数据在index可以全部获得，不需要回表取数据；distinct：当找到第一条匹配结果，将停止，转为后面其他值的查找；not exists：某些左连接中。

带条件的分页（还可以优化--看看会不会举一反三）

SELECT id from product where mode=0 ORDER BY id LIMIT 1000000, 10;      #查询时间 0.258s
id    select_type     table      type      key        key_len   ref    rows      Extra
1        SIMPLE       product    ref     idx_mode       2      const  2632436    Using where; Using index

留一个问题给大家：

EXPLAIN SELECT id from product ORDER BY mode  LIMIT 1000000, 100;    #时间: 0.210s
EXPLAIN SELECT id from product LIMIT 1000000, 100;                   #时间: 0.188s


id    select_type     table      type      key        key_len     rows      Extra
1        SIMPLE       product    index    idx_mode       2       1000100    Using index
1        SIMPLE       product    index    idx_mode       2       80575956   Using index

请回答 为什么 第2条 查询 比第一条 快？

explain select * from product where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;     #耗时2.23秒
id    select_type   table   partitions   type   possible_keys   key  key_len   ref   rows   filtered   Extra
1       SIMPLE     product    null       range      a,b          b      5     null   50198 1.00     Using index condition;Using where


explain select * from product where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;     #耗时0.05秒
id    select_type   table   partitions   type   possible_keys   key  key_len   ref   rows   filtered   Extra
1       SIMPLE     product    null       range      a,b          a      5     null   1000 50.00     Using index condition;Using where;Using filesort


选择错误，避免排序。b本身是索引，已经有序了。
在排序里增加a后，扫描行数成了影响决策的主要条件。

还有可以删除b索引！！！！！！！！！！！1

区分度：索引上不同值越多，区分度就越好。基数cardinality：不同值的个数。采样统计：随机选择N【20】个数据页，统计不同值（当变更行数超过一定数量1/10，会自动触发重新做一次索引统计）。

自动选择索引考虑：1.采样统计；2.回表；3.排序。索引有时候不会用到查询条件里面索引，而用主键索引。【注意：有时候选择主键索引，反而慢，是因为采集统计不准确，过多预估扫描行数。analyze table t重新统计索引】

性能优化

Schema设计的性能优化

适度冗余-让Query尽量减少join：避免间隙锁排队阻塞时间 > 多次简单查询带来的网络开销将会是非常巨大的 > 冗余（耗费时间）。
大字段垂直拆分：如文章内容，帖子的内容，产品的介绍等，或访问频率低。读取其他字段（在索引中完成整个查询除外），大字段占空间，自然浪费IO资源。

基本表拆分到每个数据库中，表关联在应用系统端通过接口来处理。

大表水平分拆-基于类型的分拆优化：
统计表-准实时优化：定时统计数据替代实时统计查询。（准确性要求不高，对时间并不是太敏感，访问非常频繁，重复执行较多）如在线人数，帖数，top
选择更“小”的数据类型减少存储空间（页交换 IO降低）；合适数据类型加速比较。如浮点型可以乘以系数转换为整型，即提高精确度，又处理高效。

数据切分整合方案

在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个数据库，在模块内完成数据的整合；
通过中间代理层来统一管理所有的数据源，后端数据库集群对前端应用程序透明；

自行开发中间代理层

选择自行开发，享受让个性化定制最大化的乐趣的同时，自然也需要投入更多的成本来进行前期研发以及后期的持续升级改进工作。

利用Mysql proxy实现数据划分及整合

MySQL 官方提供的一个数据库代理层产品。目前具备的功能主要有连接路由， Query 分析，Query 过滤和修改，负载均衡，以及基本的 HA 机制等。要实现这些功能，还需要通过我们自行编写 LUA 脚本来实现。

利用 Amoeba 实现数据切分及整合(目前不支持事务)

基于java开发的，解决分布式数据库数据源整合proxy程序的开源框架，基于GPL3开源协议。

amoeba.xml；rule.xml；functionMap.xml；rullFunctionMap.xml

利用 HiveDB 实现数据切分及整合
基于java开源框架，目前仅支持数据的水平切分。基于Hibernate Shards来实现数据切分工作。通过自定义切人规则，将数据分散到多个mysql server中。访问会自动分析过滤条件，并行从多个mysql server中读取数据，并合并结果集返回给客户端应用程序。

数据切分与整合中可能存在问题

分布式事务的问题：
各个数据库解决自己身上的事务，然后通过应用程序来控制多个数据库上面的事务。
跨节点Join的问题：
为了整体性能的考虑，偶尔牺牲那么一点点，其实是值得的，毕竟系统优化本身就是存在很多取舍和平衡的过程。（多次查询）
跨节点合并排序分页问题：
在从多个数据源取数据的过程是完全可以并行的

日志设置优化

错误日志、二进制日志、查询日志、慢查询日志。默认只打开错误日志。正式环境需要打开二进制日志。> show variables like '%binlog%';

binlog_cache_size:事务过程中容纳二进制日志SQl语句的缓存大小。
max_binlog_size:最好不超过1G。不能严格控制大小，因为mysql是数据库逻辑变化信息，是DML之类的query语句（注意“间隙锁”）。不像oracle是数据文件物理位置的变化。
sync_binlog:重要参数，0 是当事务提交后，由filesystem自行决定什么时间来同步。n如1是每一次事务提交都会写入磁盘。0与1的写入性能差5倍以上。

binlog复制到Slave端量的优化

Binlog_Do_DB：那些数据库需要记录（binlog_ignore_db不要记录）主库
Replicate_Do_DB:设定需要复制的数据库，多个用逗号（replicate_ignore_db忽略）从库
Replicate_Do_Table：表 Replicate_wild_Do_DB功能一样，但可以带通配符进行。（两个都有对应反义设置）从库

慢查询 show variables like 'log_slow%'; show variables like 'slow_query%' ； show variables like 'long_query%'

Query Cache优化

select语句通过hash算法进行计算得到hash值，存放在hash桶中，并且存放结果集的内存地址。如果数据改变，会通知缓存失效。通过sql_cache参数来告知那些语句需要强制缓存。

负面影响：高并发下，进行hash运算与查找带来的cpu消耗；更新频繁表，缓存命中低；内存问题。用sql_no_cache或设置结果集大小来不走缓存。

show status like 'Qcache%';

限制：5.1.17以后可以支持绑定变量的查询语句；子查询中的外部查询SQL不能被Cache；函数过程不能；带函数的查询语句

其他方面优化

网络连接与连接线程：max_connections最大连接数；max_user_connections:每个用户允许最大连接数；net_buffer_length:传输消息缓冲区大小（默认16k）；max_allowed_packet:一次传递消息量最大值（默认1M，最大1G）；back_log：等待队列允许最大连接数（默认50，最大65535）。

线程池：thread_cache_size:存放线程数（不是初始化就创建那么多线程，是实际逐渐增加），短连接应用建议设置参考并发量，长连接建议50~100；thread_stack：每个线程创建，mysql分配的内存大小（默认256k）。

show status like 'connections';中 Connections总连接次数与 show status like '%thread%';中Threads_created 共创建线程数来计算线程池命中率【(Connections-Threads_created)/Connections*100% > 90%】

Innodb存储引擎优化

缓存相关优化

Innodb_buffer_pool_size: innodb不仅仅缓存索引，同时还会缓存数据。大小(简单分析)=8GB-800M（系统）-2GB【500*(1M[sort_buffer_size]+1M[join]+1M[read]+512K[read_rnd]+512K[thread_statck])】-1.5GB（其他程序）。实际情况可以用show status like ‘Innodb_buffer_pool_%’;根据 buffer_pool_pages_data/buffer_pool_pages_total。
Innodb_additional_mem_pool_size:不是太重要，如果几百个表，每个表字段小于100的话，20M就够了。
Double Write Buffer：为了减少文件同步次数提高IO性能，提高系统Crash或者断电数据安全（就是需要同步文件的备份）。因为buffer是一块连续的磁盘空间，虽然增加一次IO，但是消耗比较小（为了安全，这点牺牲还是可以的）。