Featured image of post MySQL 分区实践
编程

MySQL 分区实践

纸上得来终觉浅，绝知此事要躬行。

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