mysql如何筛选第三行到第十行的数据 mysql取前三

1分组取时间最大的一条：

（1）：基于外连接去时间最大然后关联取最大的头像，昵称等

select a.id,a.mobile,b.name,b.head_img,a.salesCount,a.salesPrice,b.recommend_user_id,b.create_time from(SELECT id,REPLACE(mobile,SUBSTR(mobile,4,4), '****') as mobile,NAME,head_img,COUNT(recommend_user_id) AS salesCount , SUM(sales_price) AS salesPrice,recommend_user_id ,max(create_time) as create_time  FROM  hwxc_activity_guide_bind WHERE activation_status=1  and status=0  GROUP BY recommend_user_id ORDER BY salesCount DESC,salesPrice DESC) a left join  hwxc_activity_guide_bind b on a.recommend_user_id=b.recommend_user_id and a.create_time=b.create_time

（2）：基于子查询先排序，然后分组取最大；注意 因为在mysql5.7的时候，子查询的排序已经变为无效了，可能是因为子查询大多数是作为一个结果给主查询使用，所以子查询不需要排序的原因。

SELECT
	id,
	REPLACE ( mobile, SUBSTR( mobile, 4, 4 ), '****' ) AS mobile,
	NAME,
	head_img,
	COUNT( recommend_user_id ) AS salesCount,
	SUM( sales_price ) AS salesPrice,recommend_user_id 
FROM
 (select *from  hwxc_activity_guide_bind order by create_time desc LIMIT 10000000)
 a
WHERE
activation_status = 1 
	AND STATUS = 0 
GROUP BY
	recommend_user_id 
ORDER BY
	salesCount DESC,
	salesPrice DESC

对子查询的排序进行limit限制，此时子查询就不光是排序，所以此时排序会生效，但是限制条数却只能尽可能的设置大些

 

2.分组取每组前三条记录：

select recommend_user_id ,sales_price  from hwxc_activity_guide_bind a where 3>(select count(*) from hwxc_activity_guide_bind b where b.recommend_user_id=a.recommend_user_id and b.sales_price>a.sales_price) order by recommend_user_id desc ,sales_price DESC 结果如下：

注意这不是真正的分组，不能进行聚合统计操作。只是查了排名前三的  注意如果数据一样你指定1条也会查出多条

 

加聚合没有分组会默认只显示一条数据：

select recommend_user_id ,sales_price , SUM( sales_price ) AS salesPrice from hwxc_activity_guide_bind a where 3>(select count(*) from hwxc_activity_guide_bind b where b.recommend_user_id=a.recommend_user_id and b.sales_price>a.sales_price) order by recommend_user_id desc ,sales_price DESC  结果如下：

3.update selet 批量更新

UPDATE hwxc_activity_guide_bind a INNER JOIN hwxc_user u ON a.user_id=u.id        SET a.order_name=CONVERT(u.name USING utf8) COLLATE utf8_unicode_ci,a.order_head_img=u.head_img;
UPDATE hwxc_activity_guide_bind a INNER JOIN (SELECT o.barcode,o.order_no,p.product_name FROM hwxc_order_detail o LEFT JOIN hwxc_product p ON o.barcode=p.barcode ) AS u ON a.order_no=u.order_no SET a.barcode=u.barcode,a.product_name=u.product_name;
UPDATE hwxc_activity_guide_bind b INNER JOIN ( SELECT u.*,p.reward_icon, (p.reward_icon)*(u.sales_count) AS total_reward FROM ( SELECT SUBSTRING_INDEX(o.associated_type_id,'-',-1) AS spell_id,a.barcode AS barcode ,a.sales_count AS sales_count ,a.order_no FROM hwxc_activity_guide_bind a LEFT JOIN hwxc_order o ON a.order_no=o.order_no ) AS u LEFT JOIN app_spell_product p ON u.barcode=p.barcode AND u.spell_id=p.spell_id) t
ON b.order_no=t.order_no SET b.reward_icon=t.total_reward;

注意字符的转换，当表做外关联的时候，如果字符串字段编码集不一样，需要转换，字符串不一样还有可能导致索引失效

4.索引失效：

索引失效
1.字符串类型 隐式转换成数字索引失效， 若是数字转字符串则索引不失效
2.字符类型字段字符集不一样做表关联查询的时候，索引也会失效

 

5.sql语句执行先后顺序：

这一条语句包含我们经常用到的一些关键字，select，from，where，group by，order by，它的执行顺序如下：

先执行from关键字后面的语句，明确数据的来源，它是从哪张表取来的。

接着执行where关键字后面的语句，对数据进行筛选。

再接着执行group by后面的语句，对数据进行分组分类。

然后执行select后面的语句，也就是对处理好的数据，具体要取哪一部分。

最后执行order by后面的语句，对最终的结果进行排序

记录一次2千万条数据迁移过程：

项目背景：公司对现有系统做重构，新系统上线前需要将老系统的全量数据迁移到新的系统，其中有10几张表的数据有1千多万的数据量需要全量迁移过来，但是新老系统的表结构不是一样的，所以我们需要将老系统的数据全部转化为新系统的数据结构

过程：

项目开始时是从简单的版本开始做，并没有考虑速度问题，按照简单的单线程开发，数据分页读取然后做数据转化后通过mybatis批量写入数据库。第一版只是完成业务逻辑上的开发，保证数据不会有误。完成之后开始第一次测试，
测试过程中发现数据迁移很慢，读取一次需要几十秒，批量写入时每次写入1000条，批量插入，发现写入1000条居然要等上半个小时，这个肯定是不行的。于是对写入做了第一次整改，将写入由一次写入1000条改为每次写入100条，
测试后发现写入速度快了，由开始的半个小时优化了20-30秒就能完成1000条数据的写入。接着就开始了多线程的操作，将系统有开始的单个线程读取转化写入过程改成了多线程模式，用一个线程来负责每次从数据库中读取1000条，
然后把数据分给多线程去处理这1000天数据的转化并写入。第一次尝试我使用了10个线程，结果测试一跑起来傻眼了，发现数据写库由之前的单线程每1000条20-30秒变成100秒以上了，线程越多写入的性能还越来越慢了。这样肯定是不行的，
于是经过高手的请教，使用单条插入，批量commit。这里的多线程写入变慢是因为在批量写入的时候需要大量的数据库临时空间去装载大sql，并发越多，临时空间就越不够用，这也就出现了线程越多，写入越慢的情况了。经过指点后通过单条提交，
分批commit之后数据写入性能有了质的飞越，1000条写入耗时只要7左右秒就能完成，而且多线程也不会影响速度。于是开启了第二版的飞越，测试一次之后在写入到了200万之后速度满了下来，而且是越来越慢，通过日期观察写的数据没有问题，
那就只有读的问题了。通过日志分析发现，当数据读取到了100万以后，后面的数据读取越来越慢了，想起了数据库分页读取后面的页数需要翻过前面一堆数据之后才能读到，于是我们有队数据读取表做了修改，加入了一个自增顺序的id，
这样我们就不用分页读取了，每次读取完以后取最后一个数据id，后面的数据从这个之后读取就不需要分页了。每次读取的速度都是在一个稳定的值。这样之后我们的性能提升到了只需要几个小时就能完成了。但是老板对这个速度还不是很满意，
于是我们引入了多机器，其中一个机器负责读取id，然后发送到mq中，其他机器从mq中读取id，根据id来获取数据并转化落库，整个迁移过程就在1小时左右完成了。

 

总结： 这次全量数据的迁移，学到了2个点，第一点：数据插入并不是批量比单个快，批量sql需要消耗临时空间，sql越长需要的空间越大。单条插入通过手动控制事物提交到达的性能效果比批量的好；第二点数据读取分页到后面会越来越慢，当数据量大的时候，数据查询和插入结果往往是达不到自己所猜想的，你会有一个惊喜的结果。