关闭

高性能可扩展mysql(用户模块设计,分区表使用)

标签: mysql数据库函数高性能可扩展
299人阅读 评论(0) 收藏 举报
分类:

这里写图片描述
这里写图片描述

这里写图片描述

如何把用户的属性存到表中?

这里写图片描述

问题:
需求:单独保存会员级别信息(没有用户登录名)->sql无法执行

这里写图片描述

数据更新异常

这里写图片描述

当我们数据量比较大时,更新一次就需要很长的时间

数据删除异常

这里写图片描述

数据冗余问题
级别积分上限,级别积分下限

数据表过宽,会影响修改表结构的效率

问题所在:
数据库设计范式:http://blog.csdn.net/qq_33936481/article/details/72526156
https://www.zhihu.com/question/24696366

这里写图片描述

传递函数依赖关系:

这里写图片描述

解决思路:(拆分表)

这里写图片描述

冷热数据分离,减少表宽度

这里写图片描述

建表

用户登录表customer_login

这里写图片描述

  • 无符号整型来存储主键
  • timestamp每次其他列修改时,表自动更新时间值
  • 备注信息用于维护数字字典

用户信息表customer_inf

这里写图片描述

  • customer_inf.customer_id关联customer_login.customer_id ,一致
  • -

用户级别表customer_level_Inf

这里写图片描述

用户地址表customer_addr

这里写图片描述

用户积分日志表customer_point_log(操作失误,方便审计)

这里写图片描述

用户余额变动表customer_balance_log

精准浮点值decimal
这里写图片描述

用户登录日志表customer_login_log

这里写图片描述

MySQL分区表(延伸)

分区表是一种粗粒度,简易的索引策略,适用于大数据的过滤场景(比如日志表).最适合的场景是,没有合适的索引时,对其中几个分区表进行全表扫描.或者只有一个分区表和索引是热点,而且这个分区和索引能够全部存储在内存中.限制单表分区数不要超过150个,并且注意某些导致无法做分区过滤的细节,分区表对于单条记录的查询没有优势,需要注意这类查询的性能.

这里写图片描述

Hash分区表特点:

这里写图片描述

分区表分为RANGE,LIST,HASH,KEY四种类型,并且分区表的索引是可以局部针对分区表建立的

这里写图片描述

建立HASH分区表:

这里写图片描述

非整型Hash键值列转化成整型,函数
这里写图片描述

这里写图片描述

这里写图片描述

RANGE分区表特点:

range分区values less than,小于(也就是范围上限)

这里写图片描述

这里写图片描述

这里写图片描述

如果是分区键按照数量ID来分区,p1数据量(如果活跃用户比较多)就会比p0数据量大很多
按照时间来进行归档
查询包涵分区键,避免跨分区扫描

List分区

这里写图片描述

List分区特点

这里写图片描述

为customer_login_log分区

这里写图片描述

根据业务场景,可以得知使用Range分区最为合适,特别在数据量非常大的时候。分区键的选择login_time,避免产生跨分区查询。
这里写图片描述

查看是否分区成功
SELECT table_name,partition_name,partition_description,table_rows FROM information_schema.`PARTITIONS` WHERE table_name='customer_login_log';

增加分区
ALTER TABLE customer_login_log  ADD PARTITION (PARTITION p4 VALUES LESS THAN(2018));

分区删除
ALTER TABLE CUSTOMER_LOGIN_LOG DROP PARTITION p0;(DELETE删除语句非常耗时,而且会造成表的锁定)

这里写图片描述

归档:过期数据导入另一个归档表中
Mysql>=5.7,归档分区历史数据非常方便,建相同表(非分区表),分区交换
ALTER TABLE customer_login_log exchange PARTITION p1 WITH TABLE arch_customer_login_log;
删除p2分区(仍然存在于分区表中,交换的只是数据)
ALTER TABLE customer_login_log DROP PARTITION p2;
升级归档引擎,占用空间更小,不过只能进行查询操作,不能进行写操作
ALTER TABLE arch_customer_login_log ENGINE=ARCHIVE;

使用分区表注意事项

分区表更适合在MyISAM存储引擎。没有自增ID的原因也是为此。

这里写图片描述

创建MAXVALUE分区会让数据维护不方便,更好的办法是使用计划任务,在每年年底修改并增加分区(避免插入数据到RANGE分区失败)

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:184018次
    • 积分:3975
    • 等级:
    • 排名:第8915名
    • 原创:220篇
    • 转载:12篇
    • 译文:0篇
    • 评论:35条
    十年磨一剑
    自我革命者,妄想狂,物哀者。活着的目的是舍身求法,自我完善(平衡)。P.s. 道不同者勿扰
    CSDN体验太差,转简书了
      我的微信公众号
    博客专栏
    最新评论