MySQL分区表

MySQL分区表

记得这是曾经面阿里的一个面试题，数据量亿级别的数据，提升查询效率怎么去处理？

这里不讲什么导ES，导Hbase之类的， 只通过mysql来处理，这时候会用到分区表；

分区表怎么个意思呢？    跟hive里面的partition类似，但是功能没有hive强大，有很多限制，mysql 5.01后开始支持，创建分区表后,使用show create table查看，可以看到限制，5.01后才开始支持；

在业务里面很少用到分区表，这里简单记录一下使用：

创建分区表：

插入数据，分布有各个时间段的，每个时间段2条记录；

这时通过explain去查询，查询的时候已经用到分区了，扫描的不是全表的数据：

查看分区的数据：

mysql> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'by_year' and TABLE_SCHEMA='db_name';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| P1 | 2 |
| P2 | 2 |
| P3 | 2 |
| P4 | 4 |
+----------------+------------+
4 rows in set (0.00 sec)

 

 

实践

刚好最近有个任务表需要进行分区，每天的任务量有几十万，进行查询的时候基本上都会带着时间字段，想把表修改成分区表，按天进行分区；

直接修改表为分区表（如果数据较大的话，需要等待很长时间，我这一千多万的数据，花了十几分钟，业务高峰慎重。。。，我的线上操作是历史数据可以删掉一些不用的，然后再进行修改分区表操作）

ALTER TABLE tasks_table PARTITION BY RANGE (TO_DAYS(start_time))
(
PARTITION p20180401 VALUES LESS THAN (TO_DAYS('2018-04-01')),
PARTITION p20180402 VALUES LESS THAN (TO_DAYS('2018-04-02')),
PARTITION p20180403 VALUES LESS THAN (TO_DAYS('2018-04-03')),
PARTITION p20180404 VALUES LESS THAN (TO_DAYS('2018-04-04')),
PARTITION p20180405 VALUES LESS THAN (TO_DAYS('2018-04-05')),
PARTITION p20180406 VALUES LESS THAN (TO_DAYS('2018-04-06')),
PARTITION p20180407 VALUES LESS THAN (TO_DAYS('2018-04-07')),
PARTITION p20180408 VALUES LESS THAN (TO_DAYS('2018-04-08')),
PARTITION p20180409 VALUES LESS THAN (TO_DAYS('2018-04-09')),
PARTITION p20180410 VALUES LESS THAN (TO_DAYS('2018-04-10'))

)
出现错误
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
---因为分区键不是主键的其中一个组成部分而报错

alter table tasks_table drop primary key,add primary key(task_id,start_time)；

再次执行分区修改，执行完后查看分区，如下：

MariaDB [test_db]> SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'tasks_table' and TABLE_SCHEMA='test_db';

+----------------+------------+

| PARTITION_NAME | TABLE_ROWS |

+----------------+------------+

| p20180401      |   16746594 |

| p20180402      |       2808 |

| p20180403      |       2808 |

| p20180404      |       2808 |

| p20180405      |       5001 |

| p20180406      |          0 |

| p20180407      |          0 |

| p20180408      |          0 |

| p20180409      |          0 |

| p20180410      |          0 |

+----------------+------------+

10 rows in set (0.00 sec)

 

接下来就是按天分区了，想让他自动的分区，但是mysql不支持自动分区，只能另想它招：

 

1.通过脚本生成一堆add partition的语句，这种操作比较憨厚，而且如果时间长后，有可能忘记添加分区了；

2.通过mysql的存储过程加events操作；

创建存储过程

DELIMITER $$

USE `root`$$

DROP PROCEDURE IF EXISTS `create_Partition_tasks`$$

CREATE DEFINER=`root`@`%` PROCEDURE `create_Partition_tasks`()
BEGIN
/* 事务回滚，其实放这里没什么作用，ALTER TABLE是隐式提交，回滚不了的。*/
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
START TRANSACTION;

/* 到系统表查出这个表的最大分区，得到最大分区的日期。在创建分区的时候，名称就以日期格式存放，方便后面维护 */
SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name='tasks_table' and TABLE_SCHEMA='test_db' ORDER BY partition_ordinal_position DESC LIMIT 1;
SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
/* 修改表，在最大分区的后面增加一个分区，时间范围加1天 */
SET @s1=CONCAT('ALTER TABLE tasks_table ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (TO_DAYS (''',DATE(@Max_date),''')))');
/* 输出查看增加分区语句*/
SELECT @s1;
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
/* 取出最小的分区的名称，并删除掉 。
注意：删除分区会同时删除分区内的数据，慎重 */
/*select partition_name into @P0_Name from INFORMATION_SCHEMA.PARTITIONS
where table_name='tasks_table' order by partition_ordinal_position limit 1;
SET @s=concat('ALTER TABLE tasks_table DROP PARTITION ',@P0_Name);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; */
/* 提交 */
COMMIT ;
END$$

DELIMITER ;

 

创建事件event

DELIMITER ||
CREATE EVENT Partition_tasks_event
ON SCHEDULE
EVERY 1 day STARTS '2018-04-10 12:25:59'
DO
BEGIN

CALL create_Partition_tasks;

END ||
DELIMITER ;

 

这时候有一点需要注意，就是数据库的event_scheduler需要打开

通过命令

show variables like 'event_scheduler%' 查看是否打开；

如果没有，通过下面命令打开

set global event_scheduler = ON;

 

大功告成，查询的效率会大大大的提升；

检验一下：

MariaDB [test_db]> explain partitions select * from task_table where start_time<'2018-04-05' and start_time>'2018-04-03';

+------+-------------+-------+---------------------+------+---------------+------+---------+------+------+-------------+

| id   | select_type | table      | partitions          | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+------+-------------+-------+---------------------+------+---------------+------+---------+------+------+-------------+

|    1 | SIMPLE      | task_table | p20180404,p20180405 | ALL  | NULL          | NULL | NULL    | NULL | 7809 | Using where |

+------+-------------+-------+---------------------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

posted on 2018-04-09 17:28  qiezijiajia 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/dpains/p/8761407.html