MySql查询优化 百万级记录查询优化 limit分页查询

效率分析关键词：explain + SQL语句


一，最常见MYSQL最基本的分页方式limit：

select * from `table` order by id desc limit 0, 20

在中小数据量的情况下，这样的SQL足够用了，唯一需要注意的问题就是确保使用了索引。随着数据量的增加，页数会越来越多,在数据慢慢增长的过程中，可能就会出现limit 10000,20这样的情况，limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里，如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。

这种方式有几个不足：较大的偏移(OFFSET)会增加结果集，小比例的低效分页足够产生磁盘I/O瓶颈，需要扫描的行多。

简单的解决方法:不显示记录总数，没用户在乎这个数字;不让用户访问页数比较大的记录，重定向他们;避免count(*) ,不显示总数，让用户通过“下一页”来翻页 ,缓存总数;单独统计总数，在插入和删除时递增/递减

二，第二种就是分表，计算HASH值。

Mysql分表准则
在大量使用mysql时，数据量大、高访问时，为了提高性能需要分表处理，简介下mysql分表的标准，后续会继续补充

环境：
业务类型：OLTP
硬件：
cpu：8cpu 2.4GHZ
mem：48G
磁盘：raid5 6×sas

什么样的表需要拆分：根据表的体积、表的行数、访问特点来衡量表是否需要拆分

一.拆分标准是：
  1.表的体积大于2G或行数大于1000w,以单表主键等简单形式访问数据，这个时候需要分表
  2.表的体积大于2G或行数大于500W,以两表jion，小范围查询（结果集小100行）等形式访问数据，这个时候需要分表
  3.表的体积大于2G或行数大于200w,以多表join，范围查询，order by，group by，高频率等复杂形式访问数据，尤其DML，这个时候需要分表
  4.表的字段中含有text等大字段的、varchar(500)以上的、很少使用的字符型字段拆分成父子表,这种分表可以和以上联合使用
  5.数据有时间过期特性的，需要做数据分表归档处理

只要达到上面任何一个标准，都需要做分表处理

二.分表方法：
  1.冷热数据分表：适用小访问量，冷数据很少使用
     1.1 单表字段很多，把频繁使用整型字段的和非频繁使用的字符型字段或大字段拆到两个表中
     1.2 表数据具有时间过期性，把过期数据拆分到历史表里或者按时间梯度分表
  2.横向分表：适用大访问量
     2.1 如哈希等分切表或其他基于对某数字取余的切表，优点是方便数据分布，缺点是无法再扩展
     2.2 按主键id递增分表，比如每100w个id一个分表，优点是方便扩展，缺点是压力不均
     2.3 按日期分表，比如每天、每月、每年一个分表，优点是方便扩展，缺点是压力不均

说明

1.表的体积如何预估

CREATE TABLE `td_skate` (
      `valid` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '值id',
      `propertyid` BIGINT(20) NULL DEFAULT NULL COMMENT '属性id',
      `text` VARCHAR(400) NULL DEFAULT NULL,
      `entext` VARCHAR(400) NULL DEFAULT NULL,
      `picurl` VARCHAR(200) NULL DEFAULT NULL COMMENT '属性值说明图片，保存图片相对地址',
      `isother` BIGINT(20) NULL DEFAULT NULL COMMENT '是否是other值， 0  否  1  是',
      `createtime` DATETIME NULL DEFAULT NULL COMMENT '创建时间',
      `createuser` BIGINT(20) NULL DEFAULT NULL COMMENT '创建用户',
      `lastmodify` DATETIME NULL DEFAULT NULL COMMENT '最后修改时间',
      `updatetimeuser` BIGINT(20) NULL DEFAULT NULL COMMENT '最后修改人',
      `deletetime` DATETIME NULL DEFAULT NULL COMMENT '删除时间',
      `deleteuser` BIGINT(20) NULL DEFAULT NULL COMMENT '删除人',
      `description` VARCHAR(4000) NULL DEFAULT NULL COMMENT '产品描述',
      `isdelete` INT(11) NULL DEFAULT '0',
      PRIMARY KEY (`valid`),
      INDEX `fk_td_prodline_attrval_td_prodline_attr` (`propertyid`),
      CONSTRAINT `fk_td_prodline_attrval_td_prodline_attr` FOREIGN KEY (`propertyid`) REFERENCES `td_prodline_attr` (`propertyid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2491650;

把表的所有字段占用字节数相加，再乘以预估行数就是表的体积，比如上面的表，预估有1000W，那他的体积是
（8+8+400+400+200+8+8+8+8+8+8+8+4000+8）×10000000=50.8G，可以看到这个表设计非常不合理，可以修改如下：
int替代bigint
timestamp替代datetime
状态位isdelete用tinyint替代
根据业务特点看能否把varchar(4000)放到一个字表中
优化后表大小：（4+4+400+400+200+4+4+4+4+4+4+4+1）×10000000=10.37G，如果要进一步提升性能，需要删除外键，分表，保证单表在2G以下。
如果需要查看description信息，通过主键关联查看子表，只会扫描有效的子表信息， 性能将会提升非常大。  


2.表的行数预估就很简单，根据业务特点，访问量等预估.


三，第三种是偏移：

SELECT * FROM `table` WHERE id <= (SELECT id FROM `table` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) ORDER BY id desc LIMIT $pagesize

或者

select * FROM `table` AS t1 JOIN (SELECT id FROM `table` ORDER BY id desc LIMIT 900,1) AS t2 WHERE t1.id<=t2.id order by t1.id desc limit 5

原理就是记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大减少扫 描的行数。其实传统的limit m,n，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。
比如还是SELECT * FROM `table` ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，如果我们只提供”上一页”、”下一页”这样 的跳转（不提供到第N页的跳转），那么在处理”上一页”的时候SQL语句可以是：

SELECT * FROM `table` WHERE id > 9527 ORDER BY id  ASC LIMIT 20;

处理”下一页”的时候SQL语句可以是：

SELECT * FROM `table` WHERE id < 9500 ORDER BY id  DESC LIMIT 20;

不管翻多少页，每次查询只扫描20行。
缺点是只能提供”上一页”、”下一页”的链接形式，但是我一般来说非常喜欢”<上一页 1 2 3 4 5 6 7 8 9 下一页>”这样的链接方式，怎么办呢？
如果LIMIT m,n不可避免的话，要优化效率，只有尽可能的让m小一下，我们扩展前面做法，还是SELECT * FROM `table` ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，比如要跳到第8页，我看的SQL语句可以这 样写：

SELECT * FROM `table` WHERE id > 9527 ORDER BY id ASC LIMIT 20,20;

跳转到第13页：

SELECT * FROM `table` WHERE id < 9500 ORDER BY id DESC LIMIT 40,20;

注意SQL语句里面的ASC和DESC，如果是ASC取出来的结果，显示的时候记得倒置一下。
整体来说在面对百万级数据的时候如果使用上面第三种方法来优化，系统性能上是能够得到很好的提升，在遇到复杂的查询时也尽量简化，减少运算量。 同时也尽量多的使用内存缓存，有条件的可以考虑分表、分库、阵列之类的大型解决方案了。