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

最新推荐文章于 2021-09-25 22:27:24 发布
最新推荐文章于 2021-09-25 22:27:24 发布
阅读量398 收藏
点赞数
分类专栏： 工作笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48154829/article/details/115719668
版权

前言

最近两周读了《高性能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优化器会自动调整顺序

覆盖索引

索引下推

20 【建议】被索引的列在where条件中不要进行表达式计算或使用函数

会使索引失效

21【建议】长字符串，BLOB、TEXT使用前缀索引

节约空间，提高索引效率，但是也会降低索引的选择性。MySQL无法使用前缀索引做order by  和 group by 

22 【建议】在高选择性的列上创建索引

索引选择性：不重复的索引值/数据表的记录总数

23 【建议】利用覆盖索引优化高频/低性能查询查询

只需要扫描索引而无需回表，极大的提高性能。如果使用了覆盖索引 执行计划的Extra 可以看到”Using index“

24 【建议】使用索引扫描排序

MySQL有两种方式生成有序规则：通过排序操作，或者通过索引顺序扫描

利用索引排序时也要满足索引的最左前缀原则 

索引(a,b,c)

可以用到

where a = '2021-03-31' order by b;

where  a >  '2021-03-31' order by a,b;

可以用到

where a = '2021-03-31 order by a desc, c ASC;

where a >  '2021-03-31' order by b,d;

where a = '2021-03-31' order by c;

where  a >  '2021-03-31' order by b,c;

where  a >  '2021-03-31' and b in (1,2) order by c;

 

24 【建议】选择合适的索引列顺序

索引是有序的。索引先按第一列排序，再依次按第二列 第三列等排序，下图示例多列索引的排序，

索引最左前缀原则：

 

25 【建议】避免冗余或重复索引

会占用空间，索引维护需要成本，降低更新写入的性能。

重复索引：在相同的列上按相同顺序创建了索引

冗余索引：

已有索引(A,B),再创建索引(A)变为冗余索引。但是创建(B)，因为B不是索引(A,B)的最左前缀列，或者(B,A)就不是

使用pt-duplicate-key-checker检查

26 【建议】删除不使用的索引

会占用空间，索引维护需要成本，降低更新写入的性能。

可使用工具pt-index-usage分析

 

四 查询规范

 

27【建议】 禁止使用SELECT *，只获取必要的字段，需要显示说明列属性

解读：

a）读取不需要的列会增加CPU、IO、NET消耗

b）不能有效的利用覆盖索引

c）使用SELECT * 容易在增加或者删除字段后出现程序BUG

举例

SELECT * FROM tag WHERE id = 999184 

转换为

SELECT keyword FROM tag WHERE id = 999184

28 【建议】禁止使用INSERT INTO t_xxx VALUES(xxx)

解读：容易在增加或者删除字段后出现程序BUG

29【建议】避免负向查询和% 前缀模糊查询

NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、 NOT LIKE等

使用不了索引

 

举例

MySQL> select * from post WHERE title like ‘北京%' ;

298 rows in set (0.01 sec)

MySQL> select * from post WHERE title like '%北京%' ;

572 rows in set (3.27 sec)

30【建议】禁止在where条件列上使用函数或者表达式

解读：

  • 会导致索引失效，如lower(email)，column % 4。可放到右边的常量上计算
  • 返回小结果集不是很大的情况下，可以对返回列使用函数，简化程序开发

 

举例：

SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' -- 会导致全表扫描

正确的写法是：

SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')

31 【建议】禁止使用属性隐式转换

32 【建议】改写OR为IN()

33【建议】 尽量不用少用count(*)

34【建议】 高效分页

35 【建议】禁止大表使用JOIN查询，禁止大表使用子查询

五 行为规范

六 工具附件

SHOW FULL PROCESSLIST

EXPLAIN

SHOW PROFILE

SHOW STATUS

SLOW LOG 

 

DBA之路
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
SQL语法整理（五）-视图
veggietaler的专栏
09-15 5932
视图 含义：从数据库一个或多个表中导出的虚拟表 作用： 方便用户操作: 要求所见即所需，无需添加额外的查询条件，直接查看 增加数据的安全性：通过视图，用户只能查看或修改指定的数据 提高表的独立逻辑性：原有数据表结构的变化，不会影响视图，如果修改原有列，则只需修改视图即可。 创建视图 语法： MYSQL create [algorithm = {undefined | merge | temptable}] view 视图名[{属性清单}] as select 语句 [with [cascaded|loc
mysql数据库开发规范【推荐】
09-09
MySQL数据库开发规范是确保数据库设计和管理的一致性、可靠性和高效性的关键准则。以下是对规范的详细解读： 1. **命名规范**： - 库名、表名、字段名均应使用小写字母，并用下划线分隔。这是因为MySQL在`lower_...
军规解读 不要对where条件中的字段使用函数和计算
DBA之路的博客
09-25 883
为什么不要对where条件中的字段使用函数和字段？ 案例一：条件字段函数操作 mysql> select count(*) from tradelog where month(t_modified)=7; 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。 select * from tradelog where id + 1 = 10000 案例二 隐式类型转换 字符串会转换为int tradeid varchar mysql> select *.
避免在WHERE条件中，在索引列上进行计算或使用函数，因为这将导致索引不被使用...
weixin_33842328的博客
05-13 1780
点击(此处)折叠或打开 --在sal列上创建非唯一索引 scott@TESTDB11>createindexidx_emp1_salonemp1(sal); Indexcreated. --查询年薪 > 20,000的员工的编号、姓名、薪水、年薪 --不走索引 selectempno,ename,sal,sal*12from...
尽量避免在SQL语句的WHERE子句中使用函数
千里之行始于足下
09-24 1万+
----start     在SQL语句的 WHERE 子句中应该尽量避免在字段上使用函数，因为这样做会使该字段上的索引失效，影响SQL语句的性能。即使该字段上没有索引，也应该避免在字段上使用函数。考虑下面的情况： CREATE TABLE USER ( NAME VARCHAR(20) NOT NULL,---姓名 REGISTERDATE TIMESTAMP---注册时间 );
MySQL数据库开发规范1.3.doc
06-03
《MySQL数据库开发规范1.3》是平安金融科技针对MySQL数据库设计和使用制定的一套详细规则，旨在确保数据库的稳定性和高效性。规范涵盖了命名规范、对象设计规范以及数据类型选择等多个方面，以下是对这些规范的详细...
mysql数据库开发的36条原则
最新发布
01-21
【MySQL数据库开发的36条原则】是一套实践总结出的数据库设计规范，旨在提高数据库性能、可维护性和稳定性。这些原则主要针对数据库开发者，强调了数据库设计的关键要素。 1. **尽量不在数据库做运算**：数据库的...
MySQL高性能优化规范建议
09-24
MySQL高性能优化规范旨在提高数据库的运行效率，减少资源消耗，保证数据安全性，以下是一些关键的规范要点： **数据库基本设计规范：** 1. **使用InnoDB存储引擎**：InnoDB提供事务处理、行级锁定以及更好的恢复性...
MySQL数据库设计规范(修正)1
08-08
MySQL数据库设计规范旨在确保数据库的高效、稳定和可维护性，尤其在团队协作开发中更为重要。本规范针对库名、表结构、列数据类型、索引设计、分库分表、字符集、DAO层设计以及SQL编写等多个方面提出了详细的要求。 ...
MySQL 规范手册【转载】
timchen525的专栏
04-15 225
原文：58同城沈剑的MySQL军规一、基础规范第一条：表存储引擎必须使用InnoDB解读：支持事务、行级锁第二条：使用utf8（utf8mb4）字符集解读：（1）通用，无乱码风险，汉字3字节，英文1字节（2）utf8mb4是utf8的超集，存储4字节（支持表情符号）推荐第三条：表、字段必须加入中文注释解读：对于表和字段创建的时候加入comment '中文注释'第四条：禁止使用存储过程、视图、触发器...
Oracle 尽量避免在 SQL语句的WHERE子句中使用函数
千里之行始于足下
06-19 6918
-- Start 在 WHERE 子句中应该尽量避免在列上使用函数，因为这样做会使该列上的索引失效，影响SQL 语句的性能。即使该列上没有索引，也应该避免在列上使用函数。考虑下面的情况： CREATE TABLE EMPLOYEE ( NAME VARCHAR2(20) NOT NULL,---姓名 REGISTERDATE TIMESTAMP---注册时间 ); 现在要
数据库的规范一览
weixin_33768481的博客
04-01 164
基础规范 必须使用InnoDB存储引擎 支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高 必须使用utf8字符集，必要时候使用utf8mb4 通用，无乱码风险，汉字3字节，英文1字节 utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它 禁止使用存储过程、视图、触发器、Event 高并发大数据的互联网业务，架构设计思...
MYSQL ‘ON UPDATE CURRENT_TIMESTAMP(3)‘ 版本之间不兼容问题
ttloveyou8的专栏
03-11 1万+
从mariaDB导出数据到普通mysql中，加粗的地方会报错，修改了一下换成蓝色字体的触发器形式实现，就好了。DROP TABLE IF EXISTS `act_evt_log`;CREATE TABLE `act_evt_log` (  `LOG_NR_` bigint(20) NOT NULL AUTO_INCREMENT,  `TYPE_` varchar(64) COLLATE utf8_...
MySQL建表执行datetime NOT NULL DEFAULT CURRENT_TIMESTAMP失败
weixin_43758633的博客
02-11 4344
mysql5.5升级mysql到5.7就可以了
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
weixin_30877493的博客
09-21 2879
timestamp属性的字段带有ON UPDATE CURRENT_TIMESTAMP时，别的任何字段变更都会导致该字段更新，这种属性可以作为数据更新的凭证，但是一般的业务数据不会有这种需求，所以要慎用ON UPDATE CURRENT_TIMESTAMP属性 转载于:https://www.cnblogs.com/geek-ace/p/7568971.html...
mysql视图之创建视图（CREATE VIEW）和使用限制
热门推荐
luyaran的博客
07-12 3万+
mysql5.x 版本之后支持数据库视图，在mysql中，视图的几乎特征符合SQL：2003标准。 mysql以两种方式处理对视图的查询：第一种方式，MySQL会根据视图定义语句创建一个临时表，并在此临时表上执行传入查询。第二种方式，MySQL将传入查询与查询定义为一个查询并执行组合查询。mysql支持版本系统的视图，当每次视图被更改或替换时，视图的副本将在驻留在特定数据库文件夹的arc(arch...
视图，存储过程和触发器
Mr__fang的专栏
10-26 2478
视图是将一些数据有机的结合起来放到一起，方便展示给有浏览权限的用户查阅。 存储过程是执行一组sql语句，将一个复杂的操作过程放到一个sp里面，由数据库服务器处理，提高运行的效率和保证数据的完整性。 触发器，是一个表数据的变更后通过触发器来修改与之相关联的其他表的数据，保证数据的一致性。 视图是一张虚拟表，通过它可以浏览你感兴趣的部分或全部内容。 存储过程是由一组SQL语句和可选控制流
MySQL数据库开发规范详解
MySQL数据库开发规范是数据库设计者和开发者遵循的一系列规则和指导原则，旨在确保数据库的可维护性、稳定性和高性能。以下是对规范部分内容的详细解释： 1. **对象命名规范**：这部分规定了如何为数据库对象（如...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

DBA之路

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值