mysql如何使用“分析函数”

今天同学遇到个sql需求“一张表里有一千行数据,分为四种类型,我想每种类型取20条数据”。
听到这个问题,随即想到了oracle的分析函数,不过mysql没有分析函数,那该如何实现呢,经过查阅资料,找到了解决办法,下面是对应的sql:

select * from (
select b.sex, b.id,if(@sex = b.sex, @rank := @rank + 1, @rank := 1) as rank,@sex:=b.sex 
     from (select sex, id from applicants order by sex desc,id asc) b, 
     (select @rank := 0, @sex := null) a
) c 
where c.rank < 20;```
下面是对应的执行计划

```bash
+----+-------------+------------+--------+---------------+------+---------+------+--------+----------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows   | Extra          |
+----+-------------+------------+--------+---------------+------+---------+------+--------+----------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL | NULL    | NULL | 499362 | Using where    |
|  2 | DERIVED     | <derived4> | system | NULL          | NULL | NULL    | NULL |      1 |                |
|  2 | DERIVED     | <derived3> | ALL    | NULL          | NULL | NULL    | NULL | 499362 |                |
|  4 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL |   NULL | No tables used |
|  3 | DERIVED     | applicants | ALL    | NULL          | NULL | NULL    | NULL | 485266 | Using filesort |
+----+-------------+------------+--------+---------------+------+---------+------+--------+----------------+

通过执行计划可以看出,这个语句用了三次全表扫描,一次主键查询,一次不需要访问表查询。我的测试数据量大概五十万,第一次执行时间大概十秒,可见性能不够好,再次进行相同的查询时加上不使用查询缓存用时大概1.5秒,按理应该还是10秒左右,具体原因还不清楚,有待进一步研究。

这个查询主要使用了mysql变量的方法,用法后续会继续学习。同时需要了解mysql多表关联查询是如何进行的(未完待续)。

推荐阅读更多精彩内容