什么是网络营销它与传统营销相比有何特征_ui设计网上培训课程_晚上看b站_济南网站建设公司选济南网络

背景：数据量越来越庞大，导致搜索越来越慢，虽然在时间条件上创建了索引，但是检索的时间范围过大时，会导致索引失效；
实际背景：通话记录表（tong_hua_ji_lu）达到了450w的数据量，查询超过了30s，根本查询不出来，客户骂声不断；
MySQL版本：8.0.32
MySQL 的分区（Partitioning）是一种将表数据水平分割成更小、更易管理的部分的技术。通过分区，你可以根据特定的规则将数据分布到多个物理存储单元中，而这些部分对于应用程序来说仍然是一个逻辑上的整体。分区可以提高查询性能、简化维护任务，并且有助于更高效地管理和组织大量数据。

1. 决定表的开始分区

# 了解好数据的开始时间，这样决定第一个分区是多少
# 总数据量：450w
# 最早的数据：id：91345 开始时间：2023-12-19 07:52:26
select id,kai_shi_shi_jian
from tong_hua_ji_lu
order by id asc
limit 1;# 统计你优化之前的sql耗时

2.将要分区的字段纳入主键

如果表有主键或唯一键，那么这些键必须包含所有分区键。这是因为 MySQL 需要确保每个分区内的唯一性。
本案例中id是一个自增int类型的列，以下操作将开始时间和id设置为复合主键：

# 先取消自增列
alter table tong_hua_ji_lu modify id int not null comment 'ID';# 再删除主键
alter table tong_hua_ji_lu drop primary key;# 给开始时间is null的数据赋值
select *
from tong_hua_ji_lu
where kai_shi_shi_jian is null;# 开始时间赋予默认值
ALTER TABLE tong_hua_ji_lu MODIFY COLUMN `kai_shi_shi_jian` DATETIME DEFAULT NOW();# 再创建复合主键
alter table tong_hua_ji_luadd primary key (id, kai_shi_shi_jian);# 恢复id的自增
alter table tong_hua_ji_lu modify id int auto_increment comment 'ID';

3.创建分区

自行决定分区到时什么时候，下面这个语句分区到了2030年：

ALTER TABLE tong_hua_ji_lu
PARTITION BY RANGE columns (kai_shi_shi_jian) (PARTITION p202312 VALUES LESS THAN ('2024-01-01 00:00:00'),PARTITION p202401 VALUES LESS THAN ('2024-02-01 00:00:00'),PARTITION p202402 VALUES LESS THAN ('2024-03-01 00:00:00'),PARTITION p202403 VALUES LESS THAN ('2024-04-01 00:00:00'),PARTITION p202404 VALUES LESS THAN ('2024-05-01 00:00:00'),PARTITION p202405 VALUES LESS THAN ('2024-06-01 00:00:00'),PARTITION p202406 VALUES LESS THAN ('2024-07-01 00:00:00'),PARTITION p202407 VALUES LESS THAN ('2024-08-01 00:00:00'),PARTITION p202408 VALUES LESS THAN ('2024-09-01 00:00:00'),PARTITION p202409 VALUES LESS THAN ('2024-10-01 00:00:00'),PARTITION p202410 VALUES LESS THAN ('2024-11-01 00:00:00'),PARTITION p202411 VALUES LESS THAN ('2024-12-01 00:00:00'),PARTITION p202412 VALUES LESS THAN ('2025-01-01 00:00:00'),PARTITION p202501 VALUES LESS THAN ('2025-02-01 00:00:00'),PARTITION p202502 VALUES LESS THAN ('2025-03-01 00:00:00'),PARTITION p202503 VALUES LESS THAN ('2025-04-01 00:00:00'),PARTITION p202504 VALUES LESS THAN ('2025-05-01 00:00:00'),PARTITION p202505 VALUES LESS THAN ('2025-06-01 00:00:00'),PARTITION p202506 VALUES LESS THAN ('2025-07-01 00:00:00'),PARTITION p202507 VALUES LESS THAN ('2025-08-01 00:00:00'),PARTITION p202508 VALUES LESS THAN ('2025-09-01 00:00:00'),PARTITION p202509 VALUES LESS THAN ('2025-10-01 00:00:00'),PARTITION p202510 VALUES LESS THAN ('2025-11-01 00:00:00'),PARTITION p202511 VALUES LESS THAN ('2025-12-01 00:00:00'),PARTITION p202512 VALUES LESS THAN ('2026-01-01 00:00:00'),PARTITION p202601 VALUES LESS THAN ('2026-02-01 00:00:00'),PARTITION p202602 VALUES LESS THAN ('2026-03-01 00:00:00'),PARTITION p202603 VALUES LESS THAN ('2026-04-01 00:00:00'),PARTITION p202604 VALUES LESS THAN ('2026-05-01 00:00:00'),PARTITION p202605 VALUES LESS THAN ('2026-06-01 00:00:00'),PARTITION p202606 VALUES LESS THAN ('2026-07-01 00:00:00'),PARTITION p202607 VALUES LESS THAN ('2026-08-01 00:00:00'),PARTITION p202608 VALUES LESS THAN ('2026-09-01 00:00:00'),PARTITION p202609 VALUES LESS THAN ('2026-10-01 00:00:00'),PARTITION p202610 VALUES LESS THAN ('2026-11-01 00:00:00'),PARTITION p202611 VALUES LESS THAN ('2026-12-01 00:00:00'),PARTITION p202612 VALUES LESS THAN ('2027-01-01 00:00:00'),PARTITION p202701 VALUES LESS THAN ('2027-02-01 00:00:00'),PARTITION p202702 VALUES LESS THAN ('2027-03-01 00:00:00'),PARTITION p202703 VALUES LESS THAN ('2027-04-01 00:00:00'),PARTITION p202704 VALUES LESS THAN ('2027-05-01 00:00:00'),PARTITION p202705 VALUES LESS THAN ('2027-06-01 00:00:00'),PARTITION p202706 VALUES LESS THAN ('2027-07-01 00:00:00'),PARTITION p202707 VALUES LESS THAN ('2027-08-01 00:00:00'),PARTITION p202708 VALUES LESS THAN ('2027-09-01 00:00:00'),PARTITION p202709 VALUES LESS THAN ('2027-10-01 00:00:00'),PARTITION p202710 VALUES LESS THAN ('2027-11-01 00:00:00'),PARTITION p202711 VALUES LESS THAN ('2027-12-01 00:00:00'),PARTITION p202712 VALUES LESS THAN ('2028-01-01 00:00:00'),PARTITION p202801 VALUES LESS THAN ('2028-02-01 00:00:00'),PARTITION p202802 VALUES LESS THAN ('2028-03-01 00:00:00'),PARTITION p202803 VALUES LESS THAN ('2028-04-01 00:00:00'),PARTITION p202804 VALUES LESS THAN ('2028-05-01 00:00:00'),PARTITION p202805 VALUES LESS THAN ('2028-06-01 00:00:00'),PARTITION p202806 VALUES LESS THAN ('2028-07-01 00:00:00'),PARTITION p202807 VALUES LESS THAN ('2028-08-01 00:00:00'),PARTITION p202808 VALUES LESS THAN ('2028-09-01 00:00:00'),PARTITION p202809 VALUES LESS THAN ('2028-10-01 00:00:00'),PARTITION p202810 VALUES LESS THAN ('2028-11-01 00:00:00'),PARTITION p202811 VALUES LESS THAN ('2028-12-01 00:00:00'),PARTITION p202812 VALUES LESS THAN ('2029-01-01 00:00:00'),PARTITION p202901 VALUES LESS THAN ('2029-02-01 00:00:00'),PARTITION p202902 VALUES LESS THAN ('2029-03-01 00:00:00'),PARTITION p202903 VALUES LESS THAN ('2029-04-01 00:00:00'),PARTITION p202904 VALUES LESS THAN ('2029-05-01 00:00:00'),PARTITION p202905 VALUES LESS THAN ('2029-06-01 00:00:00'),PARTITION p202906 VALUES LESS THAN ('2029-07-01 00:00:00'),PARTITION p202907 VALUES LESS THAN ('2029-08-01 00:00:00'),PARTITION p202908 VALUES LESS THAN ('2029-09-01 00:00:00'),PARTITION p202909 VALUES LESS THAN ('2029-10-01 00:00:00'),PARTITION p202910 VALUES LESS THAN ('2029-11-01 00:00:00'),PARTITION p202911 VALUES LESS THAN ('2029-12-01 00:00:00'),PARTITION p202912 VALUES LESS THAN ('2030-01-01 00:00:00'),PARTITION p203001 VALUES LESS THAN ('2030-02-01 00:00:00'),PARTITION p203002 VALUES LESS THAN ('2030-03-01 00:00:00'),PARTITION p203003 VALUES LESS THAN ('2030-04-01 00:00:00'),PARTITION p203004 VALUES LESS THAN ('2030-05-01 00:00:00'),PARTITION p203005 VALUES LESS THAN ('2030-06-01 00:00:00'),PARTITION p203006 VALUES LESS THAN ('2030-07-01 00:00:00'),PARTITION p203007 VALUES LESS THAN ('2030-08-01 00:00:00'),PARTITION p203008 VALUES LESS THAN ('2030-09-01 00:00:00'),PARTITION p203009 VALUES LESS THAN ('2030-10-01 00:00:00'),PARTITION p203010 VALUES LESS THAN ('2030-11-01 00:00:00'),PARTITION p203011 VALUES LESS THAN ('2030-12-01 00:00:00'),PARTITION p203012 VALUES LESS THAN ('2031-01-01 00:00:00'),PARTITION pmax VALUES LESS THAN MAXVALUE
);

语法释义和注意事项：在这里插入图片描述
这个p202408就是分区名称，后面这个条件就是分区条件，小于这个条件的数据就会放到该分区。
分区的条件必须严格递增。
分区之后，表中的旧数据和新数据都会根据分区条件移动到指定的分区中存储。

4.再次查询测试优化效果

查看分区之后的效果：

# 查看已有的分区
SELECTPARTITION_NAME,PARTITION_ORDINAL_POSITION,PARTITION_DESCRIPTION,TABLE_ROWS
FROMINFORMATION_SCHEMA.PARTITIONS
WHERETABLE_SCHEMA = 'dospaas' -- 替换为你的数据库名AND TABLE_NAME = 'tong_hua_ji_lu';

如下图，就可以看到每个分区下的数据量有多少行了：
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/1ad125b3cc0441c499e92523f1cb1584.png