MySQL数据库开发规范《高性能MySQL》读书分享

最新推荐文章于 2021-09-25 22:27:24 发布

DBA之路

最新推荐文章于 2021-09-25 22:27:24 发布

阅读量426

点赞数

分类专栏：工作笔记文章标签： mysql

本文链接：https://blog.csdn.net/weixin_48154829/article/details/115719668

版权

工作笔记专栏收录该内容

3 篇文章 0 订阅

订阅专栏

前言

最近两周读了《高性能MySQL》的第四五六章节，读完之后收获颇丰，解答了我原来的一些疑惑，也系统学习了关于MySQL字段索引和查询方面的知识。在这里整理记录一下自己的学习收获和大家一起分享讨论。

这几章的内容非常契合或者解释了我们经常遇到的”数据库开发规范“，比如”为什么表设计中一定要有自增主键“，或者”索引可以提高查询速度，我们可以把每个字段都加上字段岂不是很好？“等等，所以把分享主题定为了《数据库开发规范解读》。

一表设计和命名规范

1 【强制】必须使用Innodb存储引擎

原因解读

(a) 5.5版本开始mysql默认存储引擎就是InnoDB，5.7版本开始，系统表都放弃MyISAM了
(b) 支持事务,行级锁,并发性能更好
(c) CPU及内存缓存页优化使得资源利用率更高

点击此处展开...

2【强制】字符集必须使用utf8mb4

utf8mb4是utf8的超集，emoji表情，火星文以及部分不常见汉字在utf8下会表现为乱码，故需要升级至utf8mb4
UTF8mb4字符集存储汉字占用3个字节，存储英文字符占用1个字节，存储emoji表情占用4个字节
标准，万国码，无需转码，无乱码风险
校对排序字符集规则使用默认的 utf8_general_ci
选择UTF8mb4是因为所谓的“通用性”

3 【强制】数据表、数据字段必须加入中文注释

方便开发，DBA工作的交接以及系统维护
状态类型要明确含义如‘卡类型（100为默认卡，200为新增卡）’
如果不添加注释，多年后你能知道a表，字段c1,d2的含义吗？

4 【强制】表必须有主键

a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率
c）无主键的表删除，在row模式的主从架构，会导致备库夯住

5 【强制】表必须有时间戳字段

dbctime DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) comment '创建时间',

dbutime DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) comment '更新时间'

创建时间和修改时间字段在异常数据分析、数据清理、数据增量抽取等方面有显著作用

6 【强制】禁止使用存储过程、视图、触发器、Event

高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。
数据库擅长存储数据与索引，CPU计算建议上移应用层。应用层无状态，横向扩展更为容易
存储过程（procedure）虽然可以简化业务端代码，在传统企业写复杂逻辑时可能会用到，而在互联网企业变更是很频繁的，在分库分表的情况下要升级一个存储过程相当麻烦。又因为它是不记录log的，所以也不方便debug性能问题。如果使用过程，一定考虑如果执行失败的情况。
触发器（trigger）也是同样，但也不应该通过它去约束数据的强一致性，mysql只支持“基于行的触发”，也就是说，触发器始终是针对一条记录的，而不是针对整个sql语句的，如果变更的数据集非常大的话，效率会很低。掩盖一条sql背后的工作，一旦出现问题将是灾难性的，但又很难快速分析和定位。再者需要ddl时无法使用pt-osc工具。放在transaction执行。
使用视图(view)一定程度上也是为了降低代码里SQL的复杂度，但有时候为了视图的通用性会损失性能（比如返回不必要的字段）。
事件（event）也是一种偷懒的表现，目前已经遇到数次由于定时任务执行失败影响业务的情况，而且mysql无法对它做失败预警。建立专门的 job scheduler 平台。

7 【强制】禁止存储大文件或者大照片

数据库存储指针信息，例如图片视频使用OSS对象存储

8 【强制】禁止使用外键，如果有外键完整性约束，需要应用程序控制

解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先

9 【强制】命名规范

库命名规范：小写下划线分割部门_业务线_功能例如 conan_user_device

表命名规范小写下划线分割

字段命名规范驼峰

索引命名规范普通 idx_被索引的字段唯一索引 uniq_被索引的字段

中间表备份表命名规范 bak_日期_表名 temp_日期_表名

10 【建议】数量规范

单实例流量 1W QPS

单实例库数量

单库表数量

单表字段数量

单表索引数量

组合索引字段个数规范

10 【示例】建表参考Demo

CREATE TABLE `draw_complete_info` (

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',

`userId` int(11) NOT NULL DEFAULT '0' COMMENT '用户Id',

...

`dbctime` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',

`dbutime` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',

PRIMARY KEY (`id`),

UNIQUE KEY `uniq_userId_lessonId_date` (`userId`,`lessonId`,`date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='清明节抽奖活动奖励记录'

二字段类规范

11 【建议】尽量把字段定义为NOT NULL并且提供默认值

可为NULL的列使用更多的存储空间，在MySQL里也需要特殊处理。

可为NULL的列使得索引/索引统计/值比较更复杂。

可为NULL的列被索引是，每个索引记录需要一个额外的字节

对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录

建议

所有字段定义中，默认都加上NOT NULL约束，除非必须为NULL,如下：

NOT NULL DEFAULT 0

NOT NULL DEFAULT ‘ ’

NOT NULL DEFAULT ’特殊意义字符’

12 【建议】选择更小更简单的数据类型

更小的数据类型通常更快，占用更少的磁盘，内存和CPU缓存，但要保证不会小于被存储值的范围。

例如：

使用MySQL内建类型而不是自渡船来存储日期和时间

使用整型存储IP

13 【建议】使用DECIMAL或者BIGINT乘相应的倍数存储财务数据/金额等需要小数精确计算的数据类型

decimal用户存储精确的小数，在MySQL5.0以后支持精确计算；float和double类型支持使用标准的浮点运算进行近似计算。

服务器CPU不支持decimal小数的精确计算，是通过MySQL服务层实现。

TIPS：使用BIGINT代替DECIMAL，将存储的小数根据小数位数精度乘以相应的倍数即可。但是需要在程序端达成约定，不然容易出错

数据类型	Decimal	FLOAT/DOUBLE
是否支持小数精确计算	小数精确计算	近似计算，不精确
存储空间	4个字节存储9个数字，小数点占1字节	4字节/9字节
位数上限	65个数字
计算性能	计算需要额外开销	计算的性能更好

14 【建议】用CHAR存储很短的字符串，或者长度接近的字符串

例如用CHAR存储MD5值，因为这是一个定长的值

用CHAR(1)存储只有Y或N的值。

15 【建议】禁止使用BLOB和TEXT类型

Memory不支持BLOB和TEXT类型，在使用BLOB和TEXT列并且需要使用临时表时，将使用MyISAM磁盘临时表

会浪费更多的磁盘和内存空间，select * 时非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能

tips:可以将SUBSTRING(column,length)转换为为字符串，这样就可以使用内存临时表了，但是截取的字符串也要足够短，不会使临时表超过max_heap_table_size或tmp_table_size

16 【建议】使用tinyint来代替 enum

ENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较高；ENUM列值如果含有数字类型，可能会引起默认值混淆
tinyint使用1个字节，一般用于status,type,flag的列

17【对比】timestamp与datetime选择

	timestamp	datetime
存储空间	4字节	8字节
范围	1970年1月1日~2038年	1001年~9999年
时区依赖	是	内部转化为整数，与时区无关
对timestamp列属性支持	支持	MySQ5.7之后支持

18【建议】使用BIGINT存储为比秒更小的粒度的日期和时间

mysql5.5中，没有小数片段，精确到秒MySQL没有提供合适的数据类型存储比秒更小的时间类型，可以使用BIGINT春初微妙级别的时间戳，或者使用double存储秒之后的小数部分，也可以使用MariaDB替代MySQL。

从mysql 5.6.4中，可以存储小数片段，最多到小数点后6位，显示时格式为 yyyy-MM-dd HH:mm:ss[.222222]

19 【建议】将字符串转化为数字

数字型与字符串型相比更高效，查询更快，占用空间更小

举例：用无符号INT(10) UNSIGNED 存储IP地址，而非CHAR(15)

整数与IP转换函数

INET_ATON()

INET_NTOA()

Int（1） Int（4） Int（11）无区别，使用zerofill后两者才会有显示区别,不足的部分会用0补全，当没有加zerofill时候两者是没有任何区别的.

三索引规范

索引知识

MySQL最常用的为BTREE索引，本节规范针对BTREE索引

索引目的

可以让服务器快速定位到表的指定位置或是说更快的查询到数据

索引优点

索引大大减少了服务器需要扫描的数据量

索引可以帮助服务器服务器避免排序和临时表

索引可以将随机I/O变为顺序I/O

索引缺点

索引是双刃剑，会增加维护负担，增大IO压力，索引占用空间是成倍增加的

单张表的索引数量控制在5个以内，或不超过表字段个数的20%。若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。

索引评级

一星索引：可以将相关数据放到一起

二星索引：如果索引中数据顺序和查找中的排列顺序一致

三星索引：如果索引中的列包含了查询中需要的全部列。

B-Tree索引分类

聚簇索引（主键索引）（clustered index）叶子节点存的是整行数据

二级索引（非主键索引）（secondary index）叶子节点的内容是主键的值，例如二级索引(A,B),其实是(A,B,ID)

最左前缀原则

可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

mysql使用联合索引时，从左向右匹配，遇到断开或者范围查询时，无法用到后续的索引列
比如索引idx_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引，where条件包含上面三种情况的字段比较则可以用到索引，但像 where c1=a and c3=c 只能用到c1列的索引，像 c2=b and c3=c等情况就完全用不到这个索引

遇到范围查询(>、<、between、like)也会停止索引匹配，比如 c1=a and c2 > 2 and c3=c，只有c1列上的比较能用到索引

where条件里面字段的顺序与索引顺序无关，mysql优化器会自动调整顺序

覆盖索引

索引下推