mysql 千万级别分页查询实战 与 数据库优化_一千万条数据的表, 如何分页查询

更多文章：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中读取数据，并合并结果集返回给客户端应用程序。

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

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

日志设置优化

错误日志、二进制日志、查询日志、慢查询日志。默认只打开错误日志。正式环境需要打开二进制日志。> 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，但是消耗比较小（为了安全，这点牺牲还是可以的）。

注意：在solaris平台上ZFS文件系统会自己保证文件写入完整性、或Slave端，可以禁用double write。

• Adaptive Hash Index：并不是改善IO，而是给Buffer Pool中数据做了索引（B-Treee索引按一定长度前缀构造一个Hash Index）。没用持久化到磁盘。

事务优化

• READ UNCOMMITTED：脏读
• READ COMMITTED：幻读、不可重复读

数据存储优化

page（16KB）<extent（64个page）<segment(files)<tablespace

• 为了尽量减小secondary index的大小，提高访问效率，主键占用空间越小越好，最好是INT。
• 尽量指定主键，最好是不牵涉到业务。不要更新主键，减少数据索引移动。
• 尽量提供主键条件进行查询

Innodb性能监控

可扩展性原则

Scale（扩展）：提供更强的服务能力、处理能力；Scalable（可扩展）：通过升级--增加内存等单机能力或增加服务器；Scalablility（扩展性）：升级的难易程度。

事务相关性最小化原则

实例

1.查询用户列表，以及粉丝数

方案一：select用户表（eg.前10条），然后for循环10次统计粉丝。

方案二：select用户表（eg.前10条 ），然后用in  加  group by 一次获取粉丝数。

备注：1交互11次，2交互2次；IO 最少11次，小于等于11次；2多1应用程序拼装处理。

性能消耗分析：

1. SQL语句解析，消耗数据库主机CPU，方案一和方案二是11：2。解析消耗CPU比列蛮高。
2. 网络方面是11：2
3. 数据库IO操作接近1：1
4. 方案二比方案一多消耗内存资源进行排序分组操作。由于数据量不大，多出消耗占比比较小（<20%）。
5. 结果集处理11：2，但是方案二数量大（可以理解数据一样，两个方案性能区别不大）
6. 方案二应用程序数据处理可以忽略不计算。

汇总：方案二远远优于方案一的

2.当交互次数与SQL复杂度。不必要数据关联访问，造成IO访问的增加性能大于交互次数的性能。

关联表不是大部分数据需要展示，可以单独查询关联表数据。

3.返回完全相同结果集的不同SQL语句，执行性能方面存在差异

执行过程：分解解析sql语句，然后通过查询模块根据sql涉及的数据表的相关统计信息进行计算分析，得出一个最合理最优化的数据访问方式（执行计划），然后根据执行计划调用存储引擎接口来获取数据。最后处理数据返回，包装客户端所需要格式返回。

例如：通过关系表索引字段，缩写范围，然后查询主表取出需要所有列。【子查询select from (select  索引列)】

4.表设计

1. 通过冗余经常使用字段，提供查询性能（避免子查询）。
2. 大字段最好拆出单独表。避免读取大量没有任何意义的数据。
3. 基本表与扩展表使用：减少数据的索引量。

5.综合

不能“隐式转换”；不能嵌套子查询；limint；不能查询条件下推；提前缩小范围；中间结果集下推（就是有聚和等函数可以缩小范围聚合）

阿里云代金券