高性能可扩展mysql(电商数据库设计构思)

时间:2023-01-12 19:51:40

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

数据库设计规范(统一)

  1. 数据库命名规范
  2. 数据库基本设计规范(存储引擎的选择,字符类型的选择)
  3. 数据库索引设计规范(索引列的选择,索引的优化技巧)
  4. 数据库字段设计规范(列的字段类型)
  5. SQL开发规范(开发人员使用,指导编写优质sql)
  6. 数据库操作行为规范(运维人员使用,减少数据库故障)

数据库命名规范

高性能可扩展mysql(电商数据库设计构思)
高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)
- 关键字查询:https://dev.mysql.com/doc/refman/5.7/en/keywords.html
- 对于一些小的字典表,为了恢复方便,直接在数据库中复制一份(不必导出成sql文件)
- 可以使用关键字创建,但是查询时就会报错,所以还需要使用“括起来。
- 如果两个表上的关联列数据类型不同,关联时就会进行隐式数据类型转换,造成列上的数据类型失效,查询效率降低

数据库基本设计规范

Mysql5.5使用之前MyIsam是默认存储引擎

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)
- Innodb无法满足的需求:列存储,空间数据
- UTF-8兼容性更好
- 对不同字符集的比较操作,比较前进行字符集的转换,所以使得列上的索引失效,带来性能问题
- 单表数据量较大,修改表结构,备份,恢复都会有很大的问题
- mysql并没有对单表最多存储设置限制。限制取决于存储设置和文件系统(32位系统单个文件最大不能超过2G)
- 历史数据归档主要用于日志文档(增长迅速),分库分表用于业务上
- 分区表大家误认为存储在不同的物理文件中,可以更好的利用磁盘IO。实际上分区表的不同分区文件最好存储在不同的磁盘阵列上。而我们把所有分区文件放在一起,IO的性能优化的效果并不能那么好
- mysql每个表限制最多存储4096列,并且每一行的数据大小不能超过65535个字节
- 表越宽,把表装入内存缓冲池占用内存也就越大,消耗更多IO
- select * ->读入无用的冷数据
- 垂直拆分
- 对于目前的mysql,修改一个字段的成本远远大于增加一个字段

数据库索引设计规范

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

高性能可扩展mysql(电商数据库设计构思)

  • mysql优化器在选择如何优化查询时,会根据统一信息对可以用到的每个索引进行评估,生成一个最好的执行计划。如果索引过多,增加sql优化器执行的时间,降低SQL查询的性能
  • 索引可以增加查询效率,同样也会降低插入和更新的效率
  • mysql5.6之前,一个查询只能使用一个索引,之后,合并索引(远远没有使用一个联合索引查询性能好)
  • Innodb是一种索引组织表,数据存储的逻辑顺序和索引顺序相同
  • Innodb是按照主键索引顺序来组织表的
  • 如果没有主键,mysql会选择第一非空唯一索引来选择主键,如果没有非空唯一索引,mysql会自动生成一个占6个字节的主键(性能不好)
  • 覆盖索引:包含了所有查询字段的索引
  • Innodb是使用聚集索引顺序来存储的。二级索引(叶子节点所保存的是行的主键信息),在查找到相应的键值之后,还要通过主键进行二次查询,获取数据。而在覆盖索引中,二级索引的键值就可以获取所有的数据了,避免了二次查询,减少了相关的IO操作
  • mysql在建立外键时,会自动的在外键上建立索引。
  • 不建议使用外键约束,但一定在表与表之间的关联键上建立索引
  • 使用其他方法来保证父表与子表的唯一性

索引建立经验
建立索引的目的:减少磁盘IO,过滤出更少的数据
索引建立在哪?

高性能可扩展mysql(电商数据库设计构思)

  • 推荐联合索引(从左到右的顺序使用)
  • mysql对关联操作的唯一方式只有一种,嵌套循环的关联方式(性能对关联列索引依赖很大)

如何选择索引列的顺序?
注意合理选择符合索引键值的顺序

高性能可扩展mysql(电商数据库设计构思)

区分度:列中唯一值的数量,用列中不同数据的数量和表的总行数的比例(接近于1)来计算,区分度最大的就是主键(1)和唯一索引的列

数据库字段设计规范

高性能可扩展mysql(电商数据库设计构思)

  • 优先选择符合存储需要的最小的数据类型
  • 对于非负型的数据来说,要优先使用无符号整型来存储(自增ID,主键……)
    • 无符号对于有符号来说,可以多出一倍存储空间
    • 高性能可扩展mysql(电商数据库设计构思)
  • 高性能可扩展mysql(电商数据库设计构思)
  • 避免使用TEXT,BLOB数据类型(性能很差)
    • TinyText,Text(64k),MidumText,LongText
    • 内存临时表不支持TEXT,BLOB,只能使用磁盘临时表(排序)
    • 还要进行二次查询
    • 只能使用前缀索引(索引类型是有长度限制的)
    • TEXT的列上不能有默认值
    • 建议把这种表分离到单独的扩展表中
  • 避免使用ENUM(枚举)类型
    • 字符串类型,内部以整数类性进行存储(MAX 65535)
    • 字符串转化整数很方便
    • 修改ENUM值需要使用ALTER语句(原数据锁,增加误操作几率)
    • ENUM类型的ORDER BY操作效率低,整型需要转化成字符串,需要额外操作(隐式转换,无法使用索引)
    • 禁止使用数值做为ENUM的枚举值
  • 尽可能把所有列定义为NOT NULL
    • 高性能可扩展mysql(电商数据库设计构思)
  • 使用TIMESTAMP(4字节)或DATETIME(8字节)类型存储时间
    • 高性能可扩展mysql(电商数据库设计构思)
    • 高性能可扩展mysql(电商数据库设计构思)
  • 财务相关的金额类数据,必须使用decimal类型
    • 精准浮点:decimal。非精准浮点:float,double
    • 四个字节存储9个数字(小数点占用一个字节)
    • 高性能可扩展mysql(电商数据库设计构思)

SQL开发规范

  • 预编译(一次解析,多次使用)
    • 重复使用执行计划,减少sql编译所需要的时间(相同语句可以一次解析,多次使用,提高处理效率)
    • 避免动态sql带来sql注入的问题
    • 只传参数,比传递SQL语句更高效
    • 高性能可扩展mysql(电商数据库设计构思)
  • 避免数据类型的隐式转换(where中列类型和参数类型不一致时)
    • 导致索引失效
    • 高性能可扩展mysql(电商数据库设计构思)
  • 充分利用表上已经存在的索引
    • 高性能可扩展mysql(电商数据库设计构思)
  • 对数据库扩展考虑
    • 高性能可扩展mysql(电商数据库设计构思)
    • 高性能可扩展mysql(电商数据库设计构思)
      • 修改表结构,select * 会把修改的列也读取到,这会影响到mysql的稳定运营
    • 高性能可扩展mysql(电商数据库设计构思)
    • 高性能可扩展mysql(电商数据库设计构思)
      • 产生慢查询
    • 高性能可扩展mysql(电商数据库设计构思)
  • 高性能可扩展mysql(电商数据库设计构思)
  • 高性能可扩展mysql(电商数据库设计构思)
  • 高性能可扩展mysql(电商数据库设计构思)
  • 高性能可扩展mysql(电商数据库设计构思)
  • 高性能可扩展mysql(电商数据库设计构思)
  • 高性能可扩展mysql(电商数据库设计构思)

数据库操作行为规范

高性能可扩展mysql(电商数据库设计构思)
高性能可扩展mysql(电商数据库设计构思)
高性能可扩展mysql(电商数据库设计构思)
高性能可扩展mysql(电商数据库设计构思)
高性能可扩展mysql(电商数据库设计构思)