Mysql子查询使用limit

关联查询是日常开发中常见的场景。但两张大表join操作消耗的资源是很大的，而且在部分场景下，可能仅仅只需要这张表的关联字段。因此在很多情况下，为了减小join带来的开销，一般会采用先查出符合要求的关联字段（通常是另一张表的id），再用查出的数据去查询另一张表的数据。 

当然这个方法不一定速度更快，毕竟撇开具体场景谈优化都是扯淡。



SELECT id,name FROM pic WHERE id IN (
    SELECT pic_id FROM download GROUP BY pic_id ORDER BY create_time DESC LIMIT 10
)

报错如下： 
[Err] 1235 - This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ 

在子查询语句外层再加一层select就可以了

SELECT id,name FROM pic WHERE id IN (
    select pic_id from (
        SELECT pic_id FROM download GROUP BY pic_id ORDER BY create_time DESC LIMIT 10
    ) a
)

其实常用的关联查询的优化方式还是关联字段加索引 :)

要改变写博客的习惯才行，把博客当成笔记好了

在mysql中进行分页查询时，一般会使用limit查询，而且通常查询中都会使用orderby排 序。但是在表数据量比较大的时候，例如查询语句片段limit 10000， 20，数据库会读取10020条数据，然后把前10000条丢弃，把最后的20条返回给你，这种消耗是可以避免的，也是没必要的。下边介绍几种优化方法：
　　优化方法1（让分页操作在索引中进行）：
　　一般表中经常作为条件查询的列都会建立索引，例如如下查询
　　Sql代码
　　SELECT msg_id, msg_content FROM message ORDER BY gmt_create desc LIMIT 100, 20;
　　可以写成如下方式
　　Sql代码
　　SELECT msg_id, msg_content FROM message
　　INNER JOIN (
　　SELECT msg_id FROM message
　　ORDER BY gmt_create LIMIT 100, 20
　　) AS page USING(msg_id);
　　这样当前查询页的内容就只会在索引中进行，当得到当前页的msg_id再统一通过一个INNER JOIN得到最终要得到的数据详情，避免了对大量数据详情进行操作的消耗。当然JOIN操作也可以通过子查询实现，不过书中介绍5.6之前版本的 mysql相比子查询还是优先使用JOIN。
　　优化方法2（显式指定要查询的索引列范围）
　　例如方法一中的gmt_create是建立索引的列，而且你也知道要查询的时间范围，这样你就可以通过如下查询语句：
　　Sql代码
　　SELECT msg_id, msg_content FROM message
　　WHERE gmt_create BETWEEN #startTime# AND #endTime#
　　ORDER BY gmt_create desc
　　这样数据库通过一个范围查询就可以得到想要的数据。
　　优化方法3（OFFSET作为查询条件显式指定）
　　例子还是如上，我们可以在查询参数中显式指定一个查询时间，叫做lastVisitTime吧。我们查询第一页可以用如下语句：
　　Sql代码
　　SELECT msg_id, msg_content FROM message
　　ORDER BY gmt_create desc
　　LIMIT 20
　　我们把读出来的数据的最后一条数据的gmt_create字段记录在lastVisitTime字段中，那么后边页的查询就可以用如下语句实现：
　　Sql代码
　　SELECT msg_id, msg_content FROM message
　　WHERE gmt_create < #lastVisitTime#
　　ORDER BY gmt_create desc
　　LIMIT 20;
　　这种查询方式，无论你查询多少页，分页都不会是影响效率的因素。

效率分析关键词：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取出来的结果，显示的时候记得倒置一下。

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

MySql:分页查询limit的正确用法
简述
测试说明
原始SQL（4500ms）
优化一（推荐）：用具体字段代替*（1600ms）
优化二：先查寻索引（450ms）
优化三：between ... and（5ms）
优化四（推荐）：保留上一页记录所在id（5ms）

简述

通常在MySQL中通过limit #{limit},#{offset}来进行分页查询。
当表中记录较多且页数（#{limit}）较大时，分页查询效率变慢。
变慢的原因时，分页查询时，会先查询出limit + offset条记录，然后截取后面的offset记录。
本文以实际1.4亿的表big_table为例，记录如何优化分页查询。

测试说明

测试表：big_table，数据量：1.4亿。

原始SQL（4500ms）
-- 原始分页查询，耗时：4500ms
select * 
from big_table
limit 2000000,10;

慢的原因：

1.查询条件为*
2.limit = 2000000太大

优化一（推荐）：用具体字段代替*（1600ms）
-- 用明确字段代替*，耗时：1600ms
select id,uid,media_id,media_name,news_id,comment 
from big_table
limit 2000000,10;

优化二：先查寻索引（450ms）
-- 方法1：先对索引进行分页查询，耗时：450ms
select * from big_table AS h inner join
 (select id from big_table
  limit 2000000,10) AS ss 
 on h.id = ss.id;

-- 方法2：先查询出起始位置的索引，耗时：450ms
select * from big_table
where id > (
	select id from big_table limit 2000000,1
)
limit 10;

优化三：between … and（5ms）
限制：id必须是连续递增的。
-- 上一页保留最后一条记录所在id，耗时：5ms
select * from big_table
where id between 4882489 and 4882489 + 10 ;

优化四（推荐）：保留上一页记录所在id（5ms）
限制：需要保留上一页的最后一条记录所在id
-- 上一页需要保留最后一条记录所在id，耗时：5ms
select * from big_table
where id > 4882488
limit 10;