mysql 分组排序前n + 长表转宽表

MySQL数据库优化的八种方式(经典必看)

 

建表

CREATE TABLE if not EXISTS `bb` (
  `id` int  not null  primary key auto_increment,
  `aab001` varchar(20) DEFAULT NULL COMMENT '参保组织编号',
  `aab003` varchar(20) DEFAULT NULL COMMENT '组织机构代码',
  `aae044` varchar(100) DEFAULT NULL COMMENT '单位名称',
  `pre_rs` decimal(8,0) DEFAULT NULL COMMENT '应缴人数',
  `cur_rs` decimal(8,0) DEFAULT NULL COMMENT '应缴人数',
  `rs` decimal(9,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table if not EXISTS aa as SELECT * from bb;
create table cc like aa;

　　

表操作

# 改表名
rename table `aa` to `aaa`;
ALTER TABLE cc RENAME TO orbit
# 删表
drop table if exists tmp;
# 更新字段
UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = 'M'
# 删除行
DELETE FROM EMPLOYEE WHERE AGE > 20
# 插入数据 
INSERT INTO cc SELECT * FROM orbit WHERE xttime > '2018-04-16 00:00:00' AND xttime <= '2018-04-17 00:00:00'; #分批插入

　　

　 

A、B两表，找出ID字段存在A表，但是不存在B表的数据。A表总共13w数据，去重后约3W条，B表有2W条，且B表的ID字段有索引

方法一：1.395秒
select distinct A.ID from  A where A.ID not in (select ID from B)
方法二：0.739秒
select A.ID from A left join B on A.ID=B.ID where B.ID is null
方法三：0.570秒
select * from  B where (select count(1)  from A where A.ID = B.ID) = 0

　　

 长表转宽表

SELECT 
    userkey,
    MAX(CASE tagid
        WHEN 1 THEN valueID
        ELSE NULL
    END) AS tag1,
    MAX(CASE tagid
        WHEN 2 THEN valueID
        ELSE NULL
    END) AS tag2
FROM
    userdata
WHERE
    tagid IN (1 , 2)
GROUP BY userkey

　　

分组排序

--方法一: 该方法目前会报错，暂不知如何解决
select
    x,y,z
from
    (
    select
        x,y,z
    from
        a
    order by
        y desc
    limit
        1000(大于总行数)
    ) t 
group by
    x


--方法二：子查询
SELECT 
    machine, deny_flow, total_flow, time 
FROM 
    total_freq_ctrl A 
WHERE 
    1 > (SELECT COUNT(machine) FROM total_freq_ctrl WHERE machine = A.machine AND time > A.time) 
AND A.module = 'all' 
ORDER BY 
    A.time desc;


--方法三
set @row=0;set @mid='';
select 
    a.*,b.rownum 
from 
    total_freq_ctrl a 
    inner join 
    (
    SELECT 
        module, machine, time, 
        case when @mid = machine then @row:=@row+1 else @row:=1 end rownum, 
        @mid:=machine mid 
    FROM
        total_freq_ctrl 
    order by 
        module,machine,time desc
    ) b on b.module=a.module and b.machine=a.machine and b.time=a.time 
where 
　　b.rownum<5;

--改为下面的也可以
SET @R = 0;
SET @mid = '';
SELECT
    * 
FROM
	(
	SELECT
		test1.*,
		( CASE WHEN @mid = a THEN @R := @R + 1 ELSE @R := 1 END ) AS rownum,
		@mid := a midnum 
	FROM
		test1 
	ORDER BY
		a,b DESC 
	) t 
WHERE
	rownum < 2