mysql - 分区

1. mysql示例表：dept_manager

SELECT * FROM dept_manager;

部分截图：

 

2. 子查询实现 - 数据量小：

SELECT  dept_no,emp_no,(
    SELECT COUNT(*) FROM dept_manager AS s2 WHERE s1.`dept_no`=s2.dept_no AND s2.emp_no <= s1.`emp_no`
) AS rownum 
FROM dept_manager AS s1
ORDER BY dept_no,emp_no;

 部分截图：

 

2. 子查询实现 - 数据1万条记录：

这里使用到了mysql官方示例数据库表：dept_emp

#dept_emp 查询10000条数据，耗时1分19秒
SELECT de1.`emp_no`, de1.`dept_no`,(
    SELECT COUNT(*) FROM dept_emp de2 WHERE de2.dept_no=de1.`dept_no` AND de2.`emp_no` <= de1.emp_no
) rownum
FROM dept_emp de1
ORDER BY de1.`dept_no`,de1.`emp_no`
LIMIT 0,10000;

   对于分区问题，假设表中有m个分区，每个分区有n行数据，则扫描的总行数是m*n*(n-1)/2，而m*n=N为表中的所有数据。因此，当n的值较小，即每个分区中含有的数据较少时，扫描成本为O(N)，而当n的值较大时，该子查询解决方案的扫描成本为O(N²)。

总结：对于表中数据较少的情况，这种方法非常快。 当表数据记录过大时，子查询不可取。

引用书上的：“当查询30万条数据时，电脑上运行了8个小时也没有得到结果”

 

3. 优化 - 游标解决方案

• 创建存储过程：

DROP PROCEDURE IF EXISTS pGetPartitionNumber;
DELIMITER //
CREATE PROCEDURE pGetPartitionNumber()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE deptno CHAR(4) DEFAULT NULL;
  DECLARE deptno_prev CHAR(4) DEFAULT NULL;
  DECLARE empno INT;
  DECLARE rn INT DEFAULT 0;
  DECLARE cur1 CURSOR FOR
    SELECT dept_no,emp_no FROM dept_emp ORDER BY dept_no,emp_no;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 若游标没有数据返回时，则将done设置等于1
  
  -- 创建临时表
  CREATE TEMPORARY TABLE $ret(
    dept_no CHAR(4),
    emp_no INT,
    rownum INT
  )ENGINE=INNODB;
 
  OPEN cur1;
  FETCH cur1 INTO deptno,empno; -- 游标向下走一步
  
  START TRANSACTION; -- 开启事物
  WHILE done = 0 DO
    IF deptno_prev IS NULL THEN
        SET rn = 1;
    ELSEIF deptno = deptno_prev THEN
        SET rn = rn + 1;
    ELSE
        SET rn = 1;
    END IF;
    SET deptno_prev = deptno;
    INSERT INTO $ret SELECT deptno,empno,rn;
    FETCH cur1 INTO deptno,empno;
  END WHILE;
  COMMIT; -- 提交事物
  
  CLOSE cur1;
  SELECT * FROM $ret;
  DROP TABLE $ret;
END;
//

• 调用存储过程

# 调用存储过程
CALL pGetPartitionNumber();

• 执行信息

 

总结：从上图可以看到查询30多万条记录只花了7秒，数据记录过大时可以采取游标的方法进行优化。

 

4. 优化 - 临时表

# 创建带有自增长列的临时表
DROP TABLE IF EXISTS $temp;
CREATE TABLE $temp(
    id INT UNSIGNED AUTO_INCREMENT,
    dept_no CHAR(4),
    emp_no INT,
    PRIMARY KEY(id)
);

# 根据分区要求插入数据
INSERT INTO  $temp SELECT NULL, dept_no, emp_no  FROM dept_manager ORDER BY dept_no, emp_no;


# join运算，通过嵌套子查询得到结果
SELECT m.dept_no, m.emp_no, id-minid+1 AS rownum
FROM $temp AS m
INNER JOIN (
    SELECT dept_no, MIN(id) AS minid
    FROM $temp
    GROUP BY dept_no
) AS n
ON m.dept_no = n.dept_no;

# 删除临时表
DROP TABLE $temp;

执行信息：

 

上图来看，执行效率也是非常高的。