规范

库

  • 命名规范：驼峰
  • 库名要能体现业务范围

表

  • 命名规范：驼峰
  • 表名称要能反映出功能
  • 表必须要有注释

列

  • 命名规范：小写字母
  • 列名要能表达具体含义，比如shopid,userid
  • 列的数据类型尽量选择整型，并且范围够用即可
  • 列必须要有注释

索引

  • 命名规范：普通索引 IX_<column1_column2,,,>，唯一索引 UK_<column1_column2,,,>

索引介绍

什么是索引

索引是存储引擎用于快速找到记录的一种数据结构

常见索引类型

BTREE，HASH，全文索引等

image.png

image.png

数据组织方式

聚簇，非聚簇

聚簇索引：表记录的物理存储顺序与索引顺序一致，且索引的叶子节点就是数据节点

非聚簇索引：表记录的物理存储顺序与索引顺序无关，叶节点包含主键值或指向数据块的

指针（因存储引擎而异，InnoDB存放主键和索引键值，MyISAM存放数据库指针）

image.png

索引如何设计

哪些列建议创建索引

WHERE, JOIN , GROUP BY, ORDER BY 等语句使用的列

利用最左前缀

（A, B, C)：A, A&B, A&C, A&B&C, B, C, B&C

如何选择索引列的顺序

经常被使用到的列优先，利用最左前缀，尽量复用已有索引

区分度大的列优先，区分度 = distinct(col) / count(col) ， distinct(col) 即cardinality，至少大于0.1，尽快排除掉更多的记录

宽度小的列优先，列宽度 = 列的数据类型，宽度越小，单节点的key值越多，索引树的高度越低，查询复杂度越低

字符集和校对集

定义

  • 字符集 (Character sets)含义：
    • 由一些列字符符号及其编码组成的集合。常见字符集：Unicode, Asian，West European等；
    • 假设有A,B,a,b四个字母，有如下映射：A => 0, B=>1, a => 2, b =>3 其中A就是字符符号，0就是编码
      如果比较A和B两个字符的大小，最简单的方法是比较二者的编码，即比对 0 和 1，由于 0 < 1，因此得到A < B。其中“比较字符编码” 就是一种比对规则。
  • 校对 (Collations)含义：
    • 一组用于某个字符集的比对规则。命名规则：以字符集的名字开头，以_ci，_cs或_bin结尾_ci 表示大小写不敏感、_cs表示大小写敏感_bin 表示按二进制编码值比较
  • 常见字符集
    • Unicode
      • “点” ——> UTF8编码：0XE782B9 3字节
      • “😊" ——> UTF8mb4编码：F09F9880 4字节
    • Asian
      • “点” ——> GBK编码：0XB5E3 2字节
    • West European
      • “A” ——> Latin1编码：0X41 1字节

https://dev.mysql.com/doc/refman/5.7/en/charset-charsets.html

查看命令

show character set;

show collation;

image.png

两者之间关系

  • 字符集会有多个校对规则，并且有一个默认
  • 校对规则只属于一个字符集

如何设置

MySQL字符集的设置有两个场景

  • 创建对象（库、表、列）时，可显示指定所用字符集，也可通过继承关系获取
  • 服务器和客户端通信时，服务器和客户端可能使用不同的编码，可通过配置，实现必要的转换

创建对象时

  • 建库和建表时可以显示指定数据库、表或列的字符集
  • 未指定时通过如下继承关系得到默认的字符集
  • image.png

服务器和客户端通信时

  • 有如下3个变量控制通信时的字符集
  • | 字符集变量 | 功能 |
    | - | - |
    | character_set_client | MySQL Server假定客户端对发送SQL语句时设置的字符集 |
    | character_set_connection | MySQL Server接收客户端发布的查询请求后，将其统一转换成该字符集，之后交由服务器内部处理 |
    | character_set_results | MySQL Server将结果集和错误信息返回给客户端时设置的字符集 |
  • 连接时指定
    • 终端：mysql --default-character-set=字符集-u root -p
    • JDBC：URL=jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=字符集
  • 运行时指定
    • SET character_set_client = utf8 或SET names utf8，同时设置3个变量的值为utf8

运行图

image.png

一般我们常会遇到的问题就是emoji表情😊的字符集utf8mb4，大小写的校对集utf8mb4_bin

分库分表

什么是分库分表

  • 分库：把原本存储于一个库的数据分块存储到多个库上
  • 分表：把原本存储于一个表的数据分块存储到多张表上

为什么要分库分表

  • 单库资源有限，会影响应用性能，阻碍业务发展
    • 业务场景拓展，数据写入和查询频次增加，IO资源不够，导致性能下降
    • 业务数据增加，磁盘和内存资源遇到瓶颈，比如无法新增表，无法创建索引等
  • 单表数据过大，同样影响增删改查性能
    • 索引高度增大，IO增加，索引维护复杂

何时分库分表

指标阀值（版本<=5.7.21）阀值（版本>=5.7.22）
单库容量300GB300GB
单表数据量60GB60GB
单表行数最佳1000W内，不能超过2亿最佳1000W内，不能超过2亿
单库单表写QPS4K7K
单库单表读QPS40W60W

如何分库分表

  • 垂直拆分

    将表按照功能模块、关系密切程度进行划分，部署到不同的库上；比如将评价表和评价带图表划分到一个库，比如将门店信息表中字段拆分到不同表。

  • 水平拆分

    当一个表中的数据量过大时，可将该表的数据按照某种规则分散到多张表上，或者将这些表分散在多个库上

拆分策略

分表策略

  • 哈希：哈希取模路由

    举例：一般选择业务查询的主体标识作为key，比如userid

    优缺点：数据分布均匀，请求散列均匀，不易出现热点访问，扩容困难

  • range：根据业务查询的主体ID进行划分

    举例：userid范围1-10000在表1，10001-20000在表2...

    优缺点：分表数据量可控，最终会会平衡，扩容方便，但存在热点访问问题

  • 时间：根据数据的生产的时间进行划分

    举例：时间在1月份的在表1，2月份的在表2...

    优缺点：扩容方便，但分表数据量不可控，也容易造成热点访问问题

分库分表数量

假设有如下ABCD数值：

A：预估3到5年的总记录数

B：单条记录大小(Byte)

C：单表建议记录数（小于1KW，不要超出2亿）

D：单库建议容量（小于300GB)

则获得相应的计算结果为：

分库数：A * B / D /1024/1024/1024（取最接近2的N次方的数，建议向上取）

分表数：A / C（取最接近2的N次方的数，建议向上取）

单库分表数：分表数/分库数

全局主键生成策略

部分场景需要做数据的合并，要保证一条记录全局唯一，比如表进入hive时

生成策略优点缺点
UUID本地实现，低延迟ID过长（128位），浪费存储空间
数据库自增ID实现简单，成本低有单点，容易产生性能瓶颈，分布式集群模式下不适合
Snowflake分布式生成，无单点问题可能出现重复ID，数据间隙较大
LeafID为64位正整数，SLA有保障非递增关系，不能作为分布式索引

案例

库表历史数据日增量3个月数据量单条记录大小（byte）
待点评表9亿4千万1000万118亿9千万394B

拆分方法：水平拆分

分表策略：按userid哈希

分库分表数：

  • 分库数：118亿9千万 * 394/1024/1024/1024/300 ≈ 14.5，取16
  • 分表数：118亿9千万 / 1000万 = 1189
  • 单库分表数： 1189 / 16 ≈ 74，应取128，实际选取了64