MySQL 分区实践

seth-shi

发布于 2023-12-18 15:02:53

1030

发布于 2023-12-18 15:02:53

文章被收录于专栏：seth-shi的专栏seth-shi的专栏

之前看过很多相关的文章, 纸上得来终觉浅，绝知此事要躬行。
最近数据组的同事把日志重新整了一下, 然后我出了一个格式的表, 让数据组的同事把客户端的日志, 经过清洗, 整理之后写到我的表中
因为涉及的是播放数据表, 所以就以日期进行了分区
库里的视频不是很多, 撑死不到10w级别, 所以不按日分区, 用月份进行分区

如果要 id 自增主键, 必须把`id`和分区键建立成联合主键

看实际情况, 我这边直接抛弃了主键

`id` BIGINT UNSIGNED NOT NULL auto_increment,
PRIMARY KEY(`id`, `date_key`)

按月分区

省略了其它播放数据的字段
会把比2021-09-01小的分到p_202108分区
大于等于2021-10-01小于20210901分到p_202109分区

CREATE TABLE `video_play_logs` (
	`video_id` INT UNSIGNED NOT NULL,
	`date_key`  date NOT NULL,
	index `video_play_logs_video_id_index` ( `video_id` )
) ENGINE=InnoDB   DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY RANGE(to_days(`date_key`)) (
    PARTITION p_202108  VALUES LESS THAN (to_days('20210901')),
	PARTITION p_202109  VALUES LESS THAN (to_days('20211001')),
	PARTITION p_202110  VALUES LESS THAN (to_days('20211101')),
	PARTITION p_202111  VALUES LESS THAN (to_days('20211201')),
	PARTITION p_202112  VALUES LESS THAN (to_days('20220101'))
)

查看分区建立情况

可以插入几条数据查看, 可以通过TABLE_ROWS查看分区里的数据条数

SELECT
	TABLE_NAME,
	PARTITION_NAME,
	TABLE_ROWS
FROM
	information_schema.`PARTITIONS` 
WHERE
	table_name='video_play_logs'
ORDER BY TABLE_ROWS desc
+-----------------+----------------+------------+
| TABLE_NAME      | PARTITION_NAME | TABLE_ROWS |
+-----------------+----------------+------------+
| video_play_logs | p_202108       |          0 |
| video_play_logs | p_202109       |          0 |
| video_play_logs | p_202110       |          0 |
| video_play_logs | p_202111       |          0 |
| video_play_logs | p_202112       |          0 |
| video_play_logs | p_202201       |          0 |
| video_play_logs | p_202202       |          0 |
| video_play_logs | p_202203       |          0 |
| video_play_logs | p_202204       |          0 |
......

查看查询是否命中分区

partitions包含数据,则代表查询已经命中分区

EXPLAIN PARTITIONS SELECT * FROM video_play_logs where date_key='2021-08-27';
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | video_play_logs | p_202109   | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.16 sec)

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2021-08-27 ，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql