业务-（导出速度优化）的实现与原理分析

这篇文章分享一下一个sql查询优化方案:where+limit避免对无用数据的访问（避免全表扫描），这样可以减少访问表的次数，提高查询效率。

limit的两种用法：

1.limit m //从结果集中取出前m条返回

2.limit m,n //从结果集的第m条数据开始，返回n条数据

其中第2种在结果集数据量非常大的时候效率就显得不那么高了，因为无论最后返回多少条数据，数据库还是要查询m+n次，只是截取了前m条结果丢弃了而已。

既然前m条数据都是无用的，那么如何使数据库可以避免查询这m条数据呢？

这个可以根据业务场景的不同采取对应的措施，比如下面这个导出速度过慢的问题：

 业务场景：

一个界面的货品货位数据导出14万条数据10分钟导出了2%，速度太慢。

技术背景：

导出数据的逻辑操作是底层封装的，导出的数据是通过调用存储过程中的查询语句得到的，由于界面是分页显示的，一页100条数据，所以查询语句也是分页查的，导出第一页时查询主表的前100条，导出第二页时查询主表的第100到200条......从第二次查询开始，使用到了limit m,n语句。

这时候我们会注意到，每一次查询都会从主表的第一条数据开始查询，总共查询(page_size+1)*page_no条数据，随着page_no的增加，查询的无用数据也越来越多，导致了不必要的浪费。

基本这样的背景，我们可以创建临时表tmp_table，在第一次查询数据时保存主表rec_id到临时表里,这样从第二次开始，每次查询数据时使用

where tmp_table.rec_id > page_no*page_size limit page_size

可以避免数据库查询前page_no*page_size条数据，大大提高了查询效率，这种提高在导出数据越大的情况下提升的越明显，优化效率越高。

下面是创建临时表和第二页及以上的sql代码：

CREATE TEMPORARY TABLE `tmp_export_id`(
			rec_id INT NOT NULL AUTO_INCREMENT,
			goods_position_rec_id INT NOT NULL,
			PRIMARY KEY (`rec_id`)
		) ENGINE=MYISAM;
		
INSERT INTO tmp_export_id (goods_position_rec_id) 
SELECT 
  cgp.position_id 
FROM
  cfg_goods_position cgp 
  LEFT JOIN cfg_warehouse_position cwp 
    ON cgp.position_id = cwp.rec_id 
  LEFT JOIN goods_spec gs 
    ON cgp.spec_id = gs.spec_id 
  LEFT JOIN goods_goods gg 
    ON gs.goods_id = gg.goods_id 
  LEFT JOIN cfg_warehouse_zone cwz 
    ON cwz.zone_id = cgp.zone_id 
WHERE cwp.warehouse_id IN (1, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19) 
  AND gs.owner_id IN (1, 3, 4, 5, 6, 7, 9, 10, 13, 14, 15, 16, 17) 
  AND cgp.type = 2  

创建并在临时表中插入信息，以便导出第二页及以后的信息时速度变快。

    SELECT 
    cgp.rec_id,
    gs.owner_id,
    gs.spec_no,
    gg.goods_no,
    gg.goods_name,
    gs.spec_code,
    gs.spec_name,
    cwp.warehouse_id,
    cwp.position_no,
    cwp.priority,
    cgp.type,
    cgp.zone_id,
    cwz.is_pop,
    cgp.created,
    cgp.modified 
  FROM
    tmp_export_id tei 
    INNER JOIN cfg_goods_position cgp 
      ON tei.goods_position_rec_id = cgp.rec_id 
    LEFT JOIN cfg_warehouse_position cwp 
      ON cgp.position_id = cwp.rec_id 
    LEFT JOIN goods_spec gs 
      ON cgp.spec_id = gs.spec_id 
    LEFT JOIN goods_goods gg 
      ON gs.goods_id = gg.goods_id 
    LEFT JOIN cfg_warehouse_zone cwz 
      ON cwz.zone_id = cgp.zone_id 
  WHERE tei.rec_id > 100 
    AND cwp.warehouse_id IN (1, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19) 
    AND gs.owner_id IN (1, 3, 4, 5, 6, 7, 9, 10, 13, 14, 15, 16, 17) 
    AND cgp.type = 2 
  ORDER BY tei.rec_id ASC 
  LIMIT 100