当前位置: 首页> 财经> 股票 > 什么是网络营销它与传统营销相比有何特征_ui设计网上培训课程_晚上看b站_济南网站建设公司选济南网络

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

时间:2025/7/11 3:41:03来源:https://blog.csdn.net/weixin_46562226/article/details/146362268 浏览次数:0次
什么是网络营销它与传统营销相比有何特征_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在这里插入图片描述

相关耗时:

删除id自动列和删除主键用时:半小时
创建联合索引用时:7m30s
恢复id自增用时:5m44s
创建分区用时:5m30s1.1到1.2的白名单数据用时
优化前:40s
优化后:2.6s
关键字:什么是网络营销它与传统营销相比有何特征_ui设计网上培训课程_晚上看b站_济南网站建设公司选济南网络

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

责任编辑: