高性能可扩展mysql 笔记（一）数据库表、索引、SQL语句设计规范

Love The Way You Lie 2023-06-04 10:54 91阅读 0赞

个人博客网：[https://wushaopei.github.io/][https_wushaopei.github.io]    (你想要这里多有)

项目说明：该笔记的背景为电商平台项目，电商项目由于其高并发、多线程、高耗能等特性，在众多的项目类型中涉及的技术面最广，故以此为例作为案例：

项目说明：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70][]

主要功能模块：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1][]

对于一个项目的立项，从需求分析到技术栈的决定，其中的业务执行都离不开持久层中对数据库的操作，数据库的库、表设计等规范对项目的高效、可用性都具有很大的影响。

以下：对数据库的相关规范进行分析及说明:

### 数据库设计规范包括： ###

> 1.  数据库命名规范；
> 2.  数据库基本设计规范；
> 3.  数据库索引设计规范；
> 4.  数据库字段设计规范；
> 5.  数据库SQL开发规范；
> 6.  数据库操作行为规范。

### 一、数据库命名规范 ###

> 1、所有数据库对象名称必须使用小写字母并用下划线分割
> 
>          不同的数据库名      DbName  dbname
> 
>          不同的表名             Table     table  tabLe

> 2、 所有数据库对象名称禁止使用MySQL保留关键字
> 
>             select id , username , \`from\` ,age from tb\_user
> 
> 注意：如果一定要使用保留字，那么，就在保留字上添加 \`\` 符号，数据库在编译时会对其进行判定为普通字符，避免错误

> 3、数据库对象的命名要能做到见名知意，并且组好不要超过32个字符
> 
>           例如：  用户数据库 mc\_userdb
> 
>                       用户账号表  user\_account

> 4、临时库表必须以tmp为前缀并以日期为后缀
> 
>      备份库，备份表必须以bak为前缀并以日期为后缀

> 5、所有存储相同数据的列名和列类型必须一致
> 
>                  ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2][]

### 二、数据库基本设计规范 ###

> 1、所有表必须使用Innodb存储引擎
> 
>         5.6 以后的默认引擎
> 
>         支持事务，行级锁，更好的恢复性，高并发下性能更好

> 2、数据库和表的字符集统一使用UTF8
> 
>         统一字符集可以避免由于字符集转换产生的乱码
> 
>         MySQL中UTF8字符集汉字点3个字节，ASCII码占用1个字节

> 3、所有表和字段都需要添加注释
> 
>         使用comment 从句添加表和列的备注

> 4、尽量控制单表数据量的大小，建议控制在500万以内
> 
>           500万并不是MySQL数据库的限制；
> 
>            可以用历史数据归档，分库分表等手段来控制数据量大小

**MySQL最多可以存储多少万数据呢？**

>  这种限制取决于存储设置和文件系统。

> 5、谨慎使用MySQL分区表
> 
>         分区表在物理上表现为多个文件，在逻辑上表现为一个表
> 
>         谨慎选择分区键，跨分区查询效率可能更低
> 
>         建议采用物理分表的方式管理大数据

> 6、尽量做到冷热数据分离，减小表的宽度
> 
>         减少磁盘IO，保证热数据的内存缓存命中率
> 
>        更有效的利用缓存，避免读入无用的冷数据
> 
>        经常一起使用的列放到一个表中

> 7、禁止在表中建立预留字段
> 
>        预留字段的命名很难做到见名识义
> 
>       预留字段无法确认存储的数据类型，所以无法选择合适的类型
> 
>       对预留字段类型的修改，会对表进行锁定

> 8、禁止在数据库中存储图片，文件等二进制数据

> 9、禁止在线上做数据库压力测试

> 10、禁止从开发环境，测试环境直连生产环境数据库

### 三、数据库索引设计规范 ###

**索引对数据库的查询性能来说是非常重要的**

>  注意： 不要滥用索引

> 1、限制每张表上的索引数量，建议单张表索引不超过5个
> 
>      索引并不是越多越好！索引可以提高效率同样可以降低效率
> 
>      禁止个表中的每一列都建立单独的索引

> 2、每个Innodb表必须有一个主键
> 
>         不使用更新频繁的列作为主键，不使用多列主键
> 
>        不使用UUID,MD5,HASH,字符串列作为主键
> 
>        主键建议选择使用自增ID值

> 3、常用索引列建议
> 
>         SELECT 、UPDATE、DELETE语句的WHERE从句中的列
> 
>         包含在ORDER BY、GROUP BY、DISTINCT中的字段
> 
>        多表JOIN的关联列

> 4、如何选择索引列的顺序
> 
>         区分度最高的列放在联合索引的最左侧
> 
>         尽量把字段长度小的列放在联合索引的最左侧
> 
>         使用最频繁的列放到联合索引的左侧

> 5、避免简历冗余索引和重复索引
> 
>           primary key(id)、index(id)、unique index(id)
> 
>           index(a,b,c)、index(a,b)、index(a)

> 6、对于频繁的查询优先考虑使用覆盖索引
> 
>          避免Innodb表进行索引的二次查找
> 
>          可以把随机IO变为顺序IO加快查询效率

> 7、尽量避免使用外键
> 
>         外键可用于保证数据的参照完整性，但建议在业务端实现
> 
>         外键会影响父表和子表的写操作从而降低性能

### 四、数据库字段设计规范 ###

> **   1、优先选择符合存储需要的最小的数据类型**
> 
>              将字符串转化为数字类型存储
> 
>              INET\_ATON( \`255.255.255.255\`) = 4294967295
> 
>              INET\_NTOA( 4294967295) = \`255.255.255.255\`

> **2、优先选择符合存储 需要的最小的数据类型**
> 
>             对于非负数据采用无符号整形进行存储
> 
>             SIGNED INT -214748~2147483647
> 
>             UNSIGNED INT 0 ~ 4294967295

> **3、优先选择符合存储需要的最小的数据类型**
> 
>            VARCHAR(N) 中的N代表的是字符数，而不是字节数
> 
>           使用UTF8存储汉字Varchar(255) = 765个字节
> 
>           过大的长度会消耗更多的内存

> **4、避免使用TEXT、BLOB数据类型**
> 
>            建议把BLOB或是TEXT列分离到单独的扩展表中
> 
>            TEXT 或 BLOB类型只能使用前缀索引

> **5、尽可能把所有列定义为 NOT NULL**
> 
> **     **      索引NULL列需要额外的空间来保存，所以要占用更多的空间
> 
>           进行比较和计算时要对NULL值做特别的处理

> **6、避免使用ENUM数据类型**
> 
>           修改ENUM值需要使用ALTER语句
> 
>          ENUM类型的OREDR BY操作效率低，需要额外操作
> 
>          禁止使用数值作为ENUM的枚举值

> **7、避免在字符串存储日期型的数据（不正确的做法）**
> 
>         缺点1： 无法用日期函数进行计算和比较
> 
>         缺点2： 用字符串存储日期要占用更多的空间

> **8、使用TIMESTAMP或DATETIME类型存储时间**
> 
>          TIMESTAMP 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07
> 
>          TIMESTAMP占用4字节和INT相同，但比INT可读性高

### 五、SQL开发规范 ###

> 1、建议使用预编译语句进行数据库操作
> 
>                          ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3][]
> 
>      **使用预编译语句的好处**：
> 
>             只传参数，比传递SQL语句更高效；
> 
>            相同语句可以一次解析，多次使用，提高处理效率

> **2、避免数据类型的隐式转换**
> 
>   隐式转换会导致索引失效
> 
>   select name,phone from customer where id = \`111\`

> **3、充分利用表上已经存在的索引**
> 
>           避免使用双%号的查询条件。如 a like \`%123%\`
> 
>          一个SQL只能利用到符合索引中的一列进行范围查询

> **4、程序连接不同的数据库使用不同的账号，禁止跨库查询**
> 
>            为数据库迁移和分库分表流出余地
> 
>           降低业务耦合度
> 
>           避免权限过大而产生的安全风险

> 5**、禁止使用SELECT \* 必须使用 SELECT <字段列表>查询**
> 
>           消耗更多的CPU和IO以及网络带宽资源
> 
>           无法使用[覆盖索引][Link 1]
> 
>           可减少表结构变更带来的影响

> **6、避免使用子查询，可以把子查询优化为join操作**
> 
>              子查询的结果集无法使用索引
> 
>              子查询会产生临时表操作，如果子查询数据量大则严重影响效率
> 
>              消耗过多的CPU及IO资源

> **7、避免使用JOIN关联太多的表**
> 
>             每JOIN一个表会多占用一部分内存（join\_buffer\_size）
> 
>             会产生临时表操作，影响查询效率
> 
>             MySQL最多允许关联61个表，建议不超过5个

> **8、减少同数据库的交互次数**
> 
>          数据库更适合处理批量操作
> 
>          合并多个相同的操作到一起，可以提高处理效率
> 
>          alter table t1 add column c1 int,change column c2 c2 int ...

> **9、禁止使用 order by rand() 进行随机排序**
> 
>       a.随机排序会把表中所有符合条件的数据装载到内存中进行排序；
> 
>       b.随机排序会消耗大量的CPU和IO及内存资源
> 
>       推荐在程序中获取一个随机值，然后从数据库中获取数据的方式

> **10、WHERE 从句中禁止对列进行函数转换和计算**
> 
>           对列进行函数转换或计算会导致无法使用索引
> 
>          where date(createtime)=\`20160901\`
> 
>          where createtime >=\`20160901\` and createtime <\`20160902\`

> **11、在明显不会有重复值时使用UNION ALL 而不是UNION**
> 
>          UNION会把所有数据放到临时表中后再进行去重操作
> 
>          UNION ALL 不会再对结果及进行去重操作

### 六、数据库操作行为规范 ###

> **1、超100万行的批量写操作，要分批多次进行操作**
> 
>           大批量操作可能会造成严重的主从延迟
> 
>           binlog日志为row格式时会产生大量的日志
> 
>           避免产生大失误操作

> **2、对于大表使用 pt-online-schema-change修改表结构**
> 
>        避免达标修改产生的主从延迟
> 
>       避免在对表字段进行修改时进行锁表

> **3、禁止为程序使用的账号赋予super权限**
> 
>     因为当达到最大连接限制时，还允许1个有super权限的用户连接
> 
>     super权限只能留给DBA处理问题的账号使用

> 4、对于程序连接数据库账号，遵循权限最小原则
> 
>        程序使用数据库账号只能在一个DB下使用，不准跨库
> 
>        程序使用的账号原则上不准有drop权限

[https_wushaopei.github.io]: https://wushaopei.github.io/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70]: /images/20230601/b743766a891440059974696f5f3cee7a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1]: /images/20230601/a150be8a5f9f464ea3dc767303e32d20.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 2]: /images/20230601/13b2f91800fe4613a978fa4596960372.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 3]: /images/20230601/e6105e69f8fb4f988f4f0c99cf8ecf3b.png
[Link 1]: https://www.cnblogs.com/happyflyingpig/p/7662881.html