数据库专用词汇表及使用规范
2010-09-18 13:26:18 阿炯

数据库专用词汇表
命名、设计及使用规范(MySQL)


数据库专用词汇表
1、数据(Data):
反映客观世界的事实,并可以区分其特征的符号:字符、数字、文本、声音、图形、图像、图表、图片等,它们是现实世界中客观存在的,可以输入到计算机中进行存储和管理的。

2、信息(Information):
由原始数据经加工提炼而成的、用于决定行为、计划或具有一定语义的数据称为信息。

3、数据库DB(Data Base):
是现实世界中相互关联的大量数据及数据间关系的集合。

4、数据库管理系统DBMS(Data Base Management System):
是对数据库中的数据进行存储和管理的软件系统。包括存储、管理、检索和控制数据库中数据的各种语言和工具,是一套系统软件。

5、数据库系统DBS(Data Base System):
是对数据库和数据库管理系统的总称。是指相互关联的数据集合与操纵数据的软件工具集合。DBS = DB+DBMS

6、数据库应用程序接口API(Application Programming Interface):
是由DBMS为开发应用程序提供的操纵和访问数据库中数据的接口函数、过程或语言。

7、数据库应用程序AP(Application Program):
满足某类用户要求的操纵和访问数据库的程序。

8、数据仓库(Data Warehouse)
一个数据仓库就是一个自带数据库的商业应用系统。利用现有的数据资源,把数据转换为信息,从中挖掘出知识,提炼成智慧,创造出效益。

9、数据发掘(Data Mining)
数据发掘可帮助商业用户处理大量存在的数据,以期发现一些"意外的关系",以便增加市场份额和利润。

10、信息发掘(Information Mining):
信息发掘是指在文档、地图、照片、声音和影像之汪洋大海中发现相关信息,即面向内容的检索。

11、数据模式(Data Model):
在数据库中,对数据(实体)的描述称为数据库模式。用相应的模式定义语言来描述。

12、数据模型(Data Model):
是对现实世界特征的数字化的模拟和抽象。

13、字段( Field):
层次数据模型中数据的最小单位,描述实体的属性。

14、片段(Segment):
是IMS层次数据模型中描述实体的单位,由字段集合组成的记录。也是应用程序访问数据库数据的单位。

15、数据项(Data Item):
是DBTG命名的网状数据模型中的最小数据单位, 表示实体的属性。

16、记录(Record ):
是DBTG命名的网状数据模型中描述实体的单位,是数据项的有序集合。

17、域(Domain):
域是值的集合,即值的取值范围。

18、关系(Relation):
一个关系就是一张二维表, 每张表有一个表名。

19、元组(Tuple):
关系表中的一行称为一个元组。元组可表示一个实体或实体之间的联系。

20、属性( Attribute):
关系表中的一个列称为关系 的一个属性,即元组的一个数据项。属性有属性名、属性类型、属性值域和属性值之分。属性名在表中是唯一的。

21、关键字( Primary Key ):
表中的一个属性或几个属性的组合、其值能唯一地标识关系中的一个元组。关键字属性不能取空值。

22、外部关键字(Forgien Key):
在一个关系中含有的与另一个关系的关键字相对应的属性组称为该关系的外部关键字。外部关键字取空值或为外部表中对应的关键字值。

23、网络库(Net-Library):
一个通信软件包,对数据库请求和结果打包,由网络协议传送。也称Net-Library,在客户机和服务器上都要安装。

24、主数据文件:
SQL Server数据库管理系统中,主数据文件用于存储指向其他数据库文件的指针和启动信息。SQL Server的每个数据库只有一个主数据文件,扩展名为.mdb。

25、辅数据文件:
SQL Server数据库管理系统中,所有不适合存储在主数据文件中的数据都存储在辅数据文件中。如果主数据文件可以包含所有的数据,可不用辅数据文件。大规模的数据库可以有分布在多个磁盘驱动上的辅文件。文件的扩展名为.ndf。

26、日志文件(LOG File):
存储数据库的事务日志信息,必要时利用日志恢复数据库。每个数据库至少需要一个日志文件。SQL Server日志文件的扩展名为.ldf。

27、开放数据库互连ODBC(Open Database Connection)
这是一个调用级的数据库访问接口,而且是标准 的数据库通用接口,通过使用不同的数据库管理系统提供的ODBC驱动程序,应用程序可以访问不同的数据库系统,而不必随数据库的改变而改变。

28、OLE DB:
这是一个基于组件对象模型(COM)的数据访问接口。

29、ADO(ActiveX Data Object )
封装了OLE DB,降低了应用程序开发和维护的成本,可以在VC、VB以及ASP(Active Server Pages)和Microsoft Internet Explorer等脚本对象模型中使用。

30、RDO(Remote Data Object)
远程数据对象,它 封装了ODBC,可以在VC、VB等中使用。

31、SQL(Structured Query Language):
SQL是结构化查询语言(Structured Query Language)的缩写,是关系数据库的标准语言。

32、视图(View):
视图是查询结果的关系,是被存储的查询定义。因此,视图数据在物理上是不存在的,但是可以查看,也可以通过视图修改基本表中的数据,故称为视图。

33、游标(CURSOR)
游标是系统为用户的查询结果开辟的数据缓冲区,存放 SQL的查询结果集合。

34、完整性约束(Integrity Constraint):
对数据库数据的正确性和有效性的约束。假如在任何时刻,一个数据库满足显式指定的验证规则集合,就说该数据库是满足完整性约束的。

35、触发器(Trigger):
触发器是一种特殊类型的存储过程,它由一系列的命令组成。当关系表中数据被修改时,系统自动执行触发器定义的操作。

36、T-SQL是SQL Server的数据库语言,它是对标准SQL语言的扩充。T-SQL以函数、数据类型、逻辑运算符及流程控制语句等扩充了SQL语言的功能。

37、存储过程(Stored Procedure):
存储过程是存放在服务器上的预先定义与编译好的SQL语句。可以提供给有权限的用户共享调用,提高系统的执行效率。

38、数据模式(Data Schema):
对某一类数据的结构、属性、联系和约束的描述称为数据模式。

39、关系模式(Relation Schema):
对关系的描述称为关系模式。关系模式可以形式化地表示为:
R(U, F)或R(U),
其中R为关系名, U为组成该关系的属性名集合, F为属性间数据的依赖关系集合。

40、数据依赖(Data Dependency):
是说明在一个关系中属性值之间的相互关系,它是现实世界属性间相互联系的抽象,是数据内在的性质,是语义的体现。

41、函数依赖(Functional Dependency)
所谓函数依赖是指关系中一个或一组属性的值可以决定其它属性的值。函数依赖正象一个函数 y = f(x) 一样,x的值给定后,y的值也就唯一地确定了。

42、规范化(Normalization):
关系模式需要满足一定的条件,不同程度的条件称作不同程度的规范化。对关系的最基本的规范化要求是每一个分量必须是不可分的数据项。

43、原子聚合(Atom Aggregation):
原子聚合是用实体表示联系。即把实体之间的联系看作是更高层次的一个实体,对较低层次实体不予考虑。

44、数据流图(Data Flow Diagram):
是从"数据"和"对数据的加工"两方面表达数据处理系统工作过程的一种图形表示法, 具有直观、易于被用户和软件人员双方理解的特点。

45、数据字典(Data Dictionary)
是各类数据描述的集合,通常包括数据项、数据结构、数据流、数据存储和加工过程等五个部分。

46、Asp:
ASP是一套微软开发的服务器端脚本环境,ASP内含于IIS 3.0和 4.0 之中,通过ASP我们可以结合HTML网页、ASP指令和ActiveX元件建立动态、交互且高效的Web服务器应用程序。

47、数据库操纵语言DML:
数据操纵语言DML是终端用户、应用程序实现对数据库中的数据进行各种操纵的语言。DML包括的基本操作功能有:增加、删除、更新、检索等。

48、事务(transaction):
是用户定义的一个数据库操作序列,这些操作要么全做,要么全不做,是一个不可分割的工作单元。

49、共享锁(shared lock) :
共享锁只用于并发的只读数据操作。任何试图修改某数据的事务,如果该数据上有共享锁,那么该事务就会被阻塞,直到所有的共享锁被释放。

50、独占锁(exclusive lock) :
任何写操作均要使用独占锁。独占锁被授予已经准备好进行数据修改的事务,确保其它事务不能访问该独占锁锁定的数据。

51、角色(role):
角色是数据库访问许可的管理单位,其成员继承角色所拥有的访问许可。角色是为方便管理而设置的权限管理单位,类似于Windows NT中用户组的概念。

52、数据传输(Data Transformation):
数据传输就是把数据从一个数据源(数据库或文件)转到另外一个数据源。

53、复制(replication):
复制是将一组数据通过网络从一个数据源拷贝到多个数据源的技术。使数据分布式的存放多个数据备份。

54、快照复制 (Snapshot Replication):
快照复制是指出版服务器在指定的时刻把发表数据的变化更新到分发服务器上,改写原来存在的任何数据。

55、事务复制 (Transaction Replication) :
事务复制发布的是修改发表数据的语句,如INSERT,UPDATE,DELETE,而实际的数据并不会传输。事务复制需要连续监视出版服务器事务日志的改变。

56、合并复制 (Merge Replication):
合并复制修改出版服务器中的出版数据库和订阅服务器中的订阅数据库,它能够自动监视这些数据库中的数据变化,并定期将这些修改操作进行合并,把合并后的结果提交给所有订阅者。

57、客户机/服务器系统(Client/Server System):
软件应用系统,其处理过程由两个有明显区别的结构分量共同提供,即客户机和服务器。

58、数据市场(Data Mart):
服务于一个单位的某个部门的数据仓库和对数据限制较少的数据仓库工具。一个单位通常有若干个数据市场。

59、决策支持系统DSS(Decision Support System):
能够帮助企业单位制定与商业有关的决策的一种自动化应用系统。

60、维度(Dimension):
数据仓库用于组织大量运作数据和历史数据的一种形式,例如时间是一个重要的维度。

61、粒度(Granularity):
数据仓库中用于表达信息详细程度的一个术语。粒度越高,详细的程度越低(高级抽象)。

62、索引:(Indexing):
用于提高查找与检索数据库记录的数据访问方法,是改善数据库查询性能的一种经常使用的技术。

63、多维数据分析(Multidimensional data analysis):
对多个数据维同时进行分析。

64、在线事务处理OLTP (On-Line Transaction Processing):
连机对事务数据库中数据进行增、删、改操作,及查询和报表等处理。

65、在线分析处理OLAP(On-Line Analytical Processing):
连机对多维数据进行分析、查询和报表等处理。

66、面向主题(Subject Oriented):
按照商业主题或者题目所进行的分类。

67、对称多处理器SMP(Symmetric MultiProcessors):
在一个CPU上按多个处理器来分解任务。

68、同义词(Synonyms):
内容相同而名字不同的两个数据元素。

69、胖客户(Fat client):
用于客户机/服务器结构中,其中客户工作站管理信息的处理(商业逻辑)以及图形用户界面。

70、瘦客户(Thin client):
用于客户机/服务器结构中,其中客户工作站主要管理用户图形界面。

71、瀑布开发方法(Waterfall Development Method):
一般用于软件开发工程中的传统开发生存期。该名称来源于以下事实:在瀑布中水流的方向总是单一的,因此这种方法的流程也是从需求收集和分析到系统开发和交付,每个阶段都要产生一箩文档,形似瀑布。

命名、设计及使用规范(MySQL)

基本命名原则

使用有意义的英文词汇,词汇中间以下划线分隔(不要用拼音)
只能使用英文字母,数字,下划线,并以英文字母开头
库、表、字段全部采用小写,不要使用驼峰式命名
避免用ORACLE、MySQL的保留字,如desc,关键字如index
命名禁止超过32个字符,须见名之意,建议使用名词不是动词
数据库,数据表一律使用前缀
临时库、表名必须以tmp为前缀,并以日期为后缀
备份库、表必须以bak为前缀,并以日期为后缀

为什么库、表、字段全部采用小写?

在 MySQL 中,数据库和表对就于那些目录下的目录和文件。因而,操作系统的敏感性决定数据库和表命名的大小写敏感。Windows下是不区分大小写的。Linux下大小写规则:
数据库名与表名是严格区分大小写的;
表的别名是严格区分大小写的;
列名与列的别名在所有的情况下均是忽略大小写的;
变量名也是严格区分大小写的;

如果已经设置了驼峰式的命名如何解决?需要在MySQL的配置文件my.ini中增加 lower_case_table_names = 1即可。

表命名

同一个模块的表尽可能使用相同的前缀,表名称尽可能表达含义。所有日志表均以 log_ 开头。

字段命名

表达其实际含义的英文单词或简写。布尔意义的字段以“is_”作为前缀,后接动词过去分词。
各表之间相同意义的字段应同名。各表之间相同意义的字段,以去掉模块前缀的表名_字段名命名。
外键字段用表名_字段名表示其关联关系。
表的主键一般都约定成为id,自增类型,是别的表的外键均使用xxx_id的方式来表明。

索引命名

非唯一索引必须按照“idx_字段名称_字段名称[_字段名]”进行命名
唯一索引必须按照“uniq_字段名称_字段名称[_字段名]”进行命名

约束命名

主键约束:pk_表名称。
唯一约束:uk_表名称_字段名(应用中需要同时有唯一性检查逻辑)。

触发器命名
trg_表名_操作。

函数过程命名
采用动词+名词的形式表达其含义。

序列命名
seq_表名

表设计规范

1、表引擎取决于实际应用场景;日志及报表类表建议用myisam,与交易,审核,金额相关的表建议用innodb引擎。如无说明,建表时一律采用innodb引擎。myisam与innodb的区别

2、默认使用utf8mb4字符集,数据库排序规则使用utf8mb4_general_ci(由于数据库定义使用了默认,数据表可以不再定义,但为保险起见,建议都写上)。

为什么字符集不选择utf8,排序规则不使用utf8_general_ci?

采用utf8编码的MySQL无法保存占位是4个字节的Emoji表情。为了使后端的项目,全面支持客户端输入的Emoji表情,升级编码为utf8mb4是最佳解决方案。对于JDBC连接串设置了characterEncoding为utf8或者做了上述配置仍旧无法正常插入emoji数据的情况,需要在代码中指定连接的字符集为utf8mb4。

3、所有表、字段均应用 comment 列属性来描述此表、字段所代表的真正含义,如枚举值则建议将该字段中使用的内容都定义出来。

4、如无说明,表中的第一个id字段一定是主键且为自动增长,禁止在非事务内作为上下文作为条件进行数据传递。禁止使用varchar类型作为主键语句设计。

5、如无说明,表必须包含create_time和modify_time字段,即表必须包含记录创建时间和修改时间的字段

6、如无说明,表必须包含is_del,用来标示数据是否被删除,原则上数据库数据不允许物理删除。

7、用尽量少的存储空间来存数一个字段的数据

能用int的就不用char或者varchar
能用tinyint的就不用int
使用UNSIGNED存储非负数值。
不建议使用ENUM、SET类型,使用TINYINT来代替
使用短数据类型,比如取值范围为0-80时,使用TINYINT UNSIGNED
存储精确浮点数必须使用DECIMAL替代FLOAT和DOUBLE
时间字段,除特殊情况一律采用int来记录unix_timestamp
存储年使用YEAR类型。
存储日期使用DATE类型。
存储时间(精确到秒)建议使用TIMESTAMP类型,因为TIMESTAMP使用4字节,DATETIME使用8个字节。
建议使用INT UNSIGNED存储IPV4。
尽可能不使用TEXT、BLOB类型。
禁止在数据库中使用VARBINARY、BLOB存储图片、文件等。建议使用其他方式存储,MySQL只保存指针信息。
单条记录大小禁止超过8k(列长度(中文)*3(UTF8)+列长度(英文)*1)

datetime与timestamp有什么不同?

相同点:TIMESTAMP列的显示格式与DATETIME列相同。显示宽度固定在19字符,并且格式为YYYY-MM-DD HH:MM:SS。

不同点:
TIMESTAMP
4个字节储存,时间范围:1970-01-01 08:00:01 ~ 2038-01-19 11:14:07
值以UTC格式保存,涉及时区转化 ,存储时对当前的时区进行转换,检索时再转换回当前的时区。

datetime
8个字节储存,时间范围:1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
实际格式储存,与时区无关

如何使用TIMESTAMP的自动赋值属性?

将当前时间作为ts的默认值:ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP。
当行更新时,更新ts的值:ts TIMESTAMP DEFAULT  ON UPDATE CURRENT_TIMESTAMP。
可以将1和2结合起来:ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

如何使用INT UNSIGNED存储ip?

使用INT UNSIGNED而不是char(15)来存储ipv4地址,通过MySQL函数inet_ntoa和inet_aton来进行转化。Ipv6地址目前没有转化函数,需要使用DECIMAL或者两个bigINT来存储。

8、如无备注,所有字段都设置NOT NULL,并设置默认值;

9、禁止在数据库中存储明文密码

10、如无备注,所有的布尔值字段,如is_hot、is_deleted,都必须设置一个默认值,并设为0;

11、如无备注,排序字段order_id在程序中默认使用降序排列;

12、整形定义中不添加长度,比如使用INT,而不是INT[4]

INT[M],M值代表什么含义?

注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系。很多人他们认为INT(4)和INT(10)其取值范围分别是 (-9999到9999)和(-9999999999到9999999999),这种理解是错误的。其实对整型中的 M值与 ZEROFILL 属性结合使用时可以实现列值等宽。不管INT[M]中M值是多少,其取值范围还是 (-2147483648到2147483647 有符号时),(0到4294967295无符号时)。

显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示。当结合可选扩展属性ZEROFILL使用时默认补充的空格用零代替。例如:对于声明为INT(5) ZEROFILL的列,值4检索为00004。请注意如果在整数列保存超过显示宽度的一个值,当MySQL为复杂联接生成临时表时会遇到问题,因为在这些情况下MySQL相信数据适合原列宽度,如果为一个数值列指定ZEROFILL,MySQL自动为该列添加UNSIGNED属性。

13、使用VARBINARY存储大小写敏感的变长字符串

什么时候用CHAR,什么时候用VARCHAR?

CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如,CHAR(30)可以占用30个字符。

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时,在它们的右边填充空格以达到指定的长度。当检索到CHAR值时,尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节)。

同CHAR对比,VARCHAR值保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留,符合标准SQL。

char适合存储用户密码的MD5哈希值,它的长度总是一样的。对于经常改变的值,char也好于varchar,因为固定长度的行不容易产生碎片,对于很短的列,char的效率也高于varchar。char(1)字符串对于单字节字符集只会占用一个字节,但是varchar(1)则会占用2个字节,因为1个字节用来存储长度信息。

索引设计规范

MySQL的查询速度依赖良好的索引设计,因此索引对于高性能至关重要。合理的索引会加快查询速度(包括UPDATE和DELETE的速度,MySQL会将包含该行的page加载到内存中,然后进行UPDATE或者DELETE操作),不合理的索引会降低速度。MySQL索引查找类似于新华字典的拼音和部首查找,当拼音和部首索引不存在时,只能通过一页一页的翻页来查找。当MySQL查询不能使用索引时,MySQL会进行全表扫描,会消耗大量的IO。索引的用途:去重、加速定位、避免排序、覆盖索引。

什么是覆盖索引?

InnoDB存储引擎中,secondary index(非主键索引)中没有直接存储行地址,存储主键值。如果用户需要查询secondary index中所不包含的数据列时,需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引的概念就是查询可以通过在一个索引中完成,覆盖索引效率会比较高,主键查询是天然的覆盖索引。合理的创建索引以及合理的使用查询语句,当使用到覆盖索引时可以获得性能提升。比如SELECT email,uid FROM user_email WHERE uid=xx,如果uid不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。

索引的基本规范

1、索引数量控制,单张表中索引数量不超过5个,单个索引中的字段数不超过5个。
综合评估数据密度和分布
考虑查询和更新比例

为什么一张表中不能存在过多的索引?

InnoDB的secondary index使用b+tree来存储,因此在UPDATE、DELETE、INSERT的时候需要对b+tree进行调整,过多的索引会减慢更新的速度。

2、对字符串使用前缀索引,前缀索引长度不超过8个字符,建议优先考虑前缀索引,必要时可添加伪列并建立索引。

不要索引blob/text等字段,不要索引大型字段,这样做会让索引占用太多的存储空间

什么是前缀索引?

前缀索引说白了就是对文本的前几个字符(具体是几个字符在建立索引时指定)建立索引,这样建立起来的索引更小,所以查询更快。前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。

建立前缀索引的语法:ALTER TABLE table_name ADD KEY(column_name(prefix_length));

3、主键准则

表必须有主键
不使用更新频繁的列
尽量不选择字符串列
不使用UUID MD5 HASH
默认使用非空的唯一键
建议选择自增或发号器

4、 重要的SQL必须被索引,核心SQL优先考虑覆盖索索引

UPDATE、DELETE语句的WHERE条件列
ORDER BY、GROUP BY、DISTINCT的字段
多表JOIN的字段

5、区分度最大的字段放在前面

选择筛选性更优的字段放在最前面,比如单号、userid等,type,status等筛选性一般不建议放在最前面,索引根据左前缀原则,当建立一个联合索引(a,b,c),则查询条件里面只有包含(a)或(a,b)或(a,b,c)的时候才能走索引,(a,c)作为条件的时候只能使用到a列索引,所以这个时候要确定a的返回列一定不能太多,不然语句设计就不合理,(b,c)则不能走索引。

合理创建联合索引(避免冗余),(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)

6、索引禁忌

不在低基数列上建立索引,例如“性别”
不在索引列进行数学运算和函数运算
不要索引常用的小型表

7、 尽量不使用外键

外键用来保护参照完整性,可在业务端实现
对父表和子表的操作会相互影响,降低可用性
INNODB本身对online DDL的限制

MYSQL 中索引的限制

MYISAM 存储引擎索引长度的总和不能超过 1000 字节
BLOB 和 TEXT 类型的列只能创建前缀索引
MYSQL 老版本不支持函数索引
使用不等于 (!= 或者 <>) 的时候, MYSQL 无法使用索引。
过滤字段使用函数运算 (如 abs (column)) 后, MYSQL无法使用索引。
join语句中join条件字段类型不一致的时候MYSQL无法使用索引
使用 LIKE 操作的时候如果条件以通配符开始 (如 ‘%abc…’)时, MYSQL无法使用索引。
使用非等值查询的时候, MYSQL 无法使用 Hash 索引。

语句设计规范

1、使用预编译语句
只传参数,比传递SQL语句更高效
一次解析,多次使用
降低SQL注入概率

2、避免隐式转换
会导致索引失效

3、充分利用前缀索引
必须是最左前缀
不可能同时用到两个范围条件
不使用%前导的查询,如like “%ab”

4、不使用负向查询,如not in/like
无法使用索引,导致全表扫描
全表扫描导致buffer pool利用率降低

5、避免使用存储过程、触发器、UDF、events等
让数据库做最擅长的事
降低业务耦合度,为sacle out、sharding留有余地
避开BUG

6、避免使用大表的JOIN
MySQL最擅长的是单表的主键/二级索引查询
JOIN消耗较多内存,产生临时表

7、避免在数据库中进行数学运算
MySQL不擅长数学运算和逻辑判断
无法使用索引

7、减少与数据库的交互次数
INSERT … ON DUPLICATE KEY UPDATE
REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
UPDATE … WHERE ID IN(10,20,50,…)

8、合理的使用分页
限制分页展示的页数
只能点击上一页、下一页
采用延迟关联

如何正确的使用分页?

假如有类似下面分页语句:SELECT * FROM table  ORDER BY id LIMIT 10000, 10

由于MySQL里对LIMIT OFFSET的处理方式是取出OFFSET+LIMIT的所有数据,然后去掉OFFSET,返回底部的LIMIT。所以,在OFFSET数值较大时,MySQL的查询性能会非常低。可以使用id > n 的方式进行解决:

使用id > n 的方式有局限性,对于id不连续的问题,可以通过翻页的时候同时传入最后一个id方式来解决。
//输出时,找出当前结果集中的最大最小id
//下一页
http://example.com/page.php?last=100
select * from table where id<100 order by id desc limit 10
//上一页
http://example.com/page.php?first=110
select * from table where id>110 order by id desc limit 10

这种方式比较大的缺点是,如果在浏览中有插入/删除操作,翻页不会更新,而总页数可能仍然是根据新的count(*) 来计算,最终可能会产生某些记录访问不到。为了修补这个问题,可以继续引入当前页码以及在上次翻页以后是否有插入/删除等影响总记录数的操作并进行缓存。

其他变种方式:
SELECT * FROM table WHERE id >= (SELECT id FROM table ORDER BY id LIMIT #offset#, 1)

9、拒绝大SQL,拆分成小SQL
充分利用QUERY CACHE
充分利用多核CPU

10、使用in代替or,in的值不超过1000个

11、禁止使用order by rand()

12、使用EXPLAIN诊断,避免生成临时表
EXPLAIN语句(在MySQL客户端中执行)可以获得MySQL如何执行SELECT语句的信息。通过对SELECT语句执行EXPLAIN,可以知晓MySQL执行该SELECT语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免MySQL进行全表扫描、使用临时表、排序等。详见官方文档。

13、用union all而不是union
union all与 union有什么区别?

union和union all关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。union在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。如:
select * from test_union1
union
select * from test_union2

这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。而union all只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。

从效率上说,union all要比union快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用union all,如下:
select * from test_union1
union all
select * from test_union2

14、程序应有捕获SQL异常的处理机制

15、禁止单条SQL语句同时更新多个表

16、不使用select * ,SELECT语句只获取需要的字段
消耗CPU和IO、消耗网络带宽
无法使用覆盖索引
减少表结构变更带来的影响
因为大,select/join 可能生成临时表

17、UPDATE、DELETE语句不使用LIMIT

18、INSERT语句必须显式的指明字段名称,不使用INSERT INTO table()

19、INSERT语句使用batch提交(INSERT INTO table VALUES(),(),()……),values的个数不超过500

20、统计表中记录数时使用COUNT(*),而不是COUNT(primary_key)和COUNT(1) 备注:仅针对Myisam

21、数据更新建议使用二级索引先查询出主键,再根据主键进行数据更新

22、禁止使用跨库查询

23、禁止使用子查询,建议将子查询转换成关联查询

24、针对varchar类型字段的程序处理,请验证用户输入,不要超出其预设的长度

分表规范

单表一到两年内数据量超过500w或数据容量超过10G考虑分表,需提前考虑历史数据迁移或应用自行删除历史数据,采用等量均衡分表或根据业务规则分表均可。要分表的数据表必须与DBA商量分表策略

用HASH进行散表,表名后缀使用十进制数,下标从0开始
按日期时间分表需符合YYYY[MM][DD][HH]格式
采用合适的分库分表策略。例如千库十表、十库百表等
禁止使用分区表,分区表对分区键有严格要,分区表在表变大后执行DDL、SHARDING、单表恢复等都变得更加困难。
拆分大字段和访问频率低的字段,分离冷热数据

行为规范

批量导入、导出数据必须提前通知DBA协助观察
禁止在线上从库执行后台管理和统计类查询
禁止有super权限的应用程序账号存在
产品出现非数据库导致的故障时及时通知DBA协助排查
推广活动或上线新功能必须提前通知DBA进行流量评估
数据库数据丢失,及时联系DBA进行恢复
对单表的多次alter操作必须合并为一次操作
不在MySQL数据库中存放业务逻辑
重大项目的数据库方案选型和设计必须提前通知DBA参与
对特别重要的库表,提前与DBA沟通确定维护和备份优先级
不在业务高峰期批量更新、查询数据库其他规范
提交线上建表改表需求,必须详细注明所有相关SQL语句

其他规范

日志类数据不建议存储在MySQL上,优先考虑Hbase或OceanBase,如需要存储请找DBA评估使用压缩表存储。


DBA与开发

DBA,指的是数据库管理员(Database Administrator)。DBA 是负责管理和维护数据库系统的专业人员,他们的工作涵盖了数据库系统的各个方面。传统上,DBA 主要专注于运维领域,DBA 是否需要懂开发呢?这是一个值得深思的问题。其职责包括安全加固、生产值班、工单处理、账号审计、例行巡检、容量管理、监控告警、部署交付、数据迁移、切换演练、上线变更、系统优化、备份恢复、系统集成、故障排除、指定标准规范、编写技术文档、数据库资产管理等等。除此之外,还会开展一些技术交流和培训工作。高级一些的 DBA 还要研究新技术,容灾架构设计,参与平台工具开发等等。很明显,高级一些的 DBA 是需要参与到开发里的,需要掌握一些开发知识。

总体而言,DBA 的目标是确保数据库系统的稳定性、高可用性、高性能和安全性。需要对数据库引擎、SQL 语言和数据库架构有深入的了解,以便更好地管理和维护数据库系统,满足组织对数据的需求。

在数据库领域有一些误区需要澄清。如果有人专门在开发部门里编写 SQL 并优化 SQL,他是否属于 DBA?其在传统上并不算是 DBA。在 Oracle 领域,这种情况很常见。这些人通常负责编写存储过程,有些存储过程甚至长达 3000 行,让人完全看不懂。区别于传统意义上的 DBA,他们被称为 "数据库开发",和编写业务代码的程序员没有区别,也要开发业务代码,只是这些业务代码用 SQL 实现,封装到了存储过程里,所以有些人也喜欢称其为 "开发 DBA",需要注意这种区别。有时候,"数据库开发" 还容易与 "数据库内核开发" 混淆。"数据库内核研发"是指负责研发数据库管理系统(DBMS)核心代码的专业人员。其深入了解数据库引擎的底层实现,并负责开发和优化数据库引擎的功能。

在使用 MySQL 的场景中,很多中小型公司会选择使用云服务,这些公司需要进行的运维工作较少,大多数工作都由云平台自动化完成,只有少量需要人工操作的运维任务由云计算服务商的技术人员负责。这些公司的 DBA 有可能也会参与到业务的表结构设计、SQL 开发和优化等工作,甚至开发运维平台。为了区分他们和纯粹从事运维工作的 DBA,我将称呼他们为 "业务 DBA"。

因此,DBA 可以分为三种形态,分别是运维 DBA、开发 DBA 和业务 DBA。

“懂开发”?

懂是一个模糊词。每个人对懂的理解和要求不一样。甚至 "开发" 也是个模糊词,“开发 "到底是指开发人员,还是" 开发流程”,亦或是 "开发技能" 呢?

开发指"开发人员" 或 "开发流程"。 有人问软件领域的产品经理是否需要懂开发?这里的懂,明显指产品经理懂开发流程和技术术语,能听懂 "开发人员" 说的东西,能很好地沟通。所以产品经理是应该要懂开发的。但如果你理解 "懂开发" 为 "懂编程",那大多数产品经理是做不到的,也是不需要的。所以这里的 "懂开发" 指的是他能理解开发的方方面面,包括人和事,这里的 "开发" 指程序员,也指开发流程。

开发指"开发流程"。假设你现在要卖软件,想忽悠客户,说开发这个软件多么的难,要报很高的人天。客户是行家,他知道这个项目很简单,拿开源框架,怎么怎么改,只是他不会写代码而已。工作量方面,设计、开发、测试全链路的人天耗时和你计算得清清楚楚,你这时会惊叹遇到一个懂开发的客户,生意真难做!这里的 "懂开发" 是什么意思?指的是客户懂开发流程。

开发指 "开发技能",并且对掌握的程度要求不一致。 当你学会了 Python 语言,并且使用该语言写一些自动化脚本,甚至能读懂 ansible 的一些模块,修改为自己想要的样子,你出去面试一个 Python 运维开发的岗位,完全受到打击了,面试官觉得你不懂开发,甚至不懂 Python,因为你不懂 Django 框架的任何东西。这里的懂开发指的是能不能专职做开发人员。

当我们说 DBA 需要 "懂开发" 时,指的是 DBA 需要具备一定的软件开发技能,能够编写和理解代码,并参与到应用程序的开发过程中。这些开发技能可以涵盖多个方面,例如编程语言、应用程序架构、Web 开发、数据库开发和自动化脚本等。所以这里的开发就是指 "开发技能" 了。接下来,就是定义 DBA 对开发有多 "懂" 才算 "懂"。

作为对技术有点追求的人,我认为能称为 "懂开发" 需要达到一些门槛,需要至少掌握以下 6 点,并不是说会写一些 shell 或 python 脚本就称之为 "懂开发" 了。

1.掌握主流的开发语言:DBA 需要熟练掌握至少一门主流的开发语言,例如 JavaScript、Java、Python、Go 等。这使得他们能够编写自动化脚本和工具,提高工作效率。许多任务仅凭 shell 语言无法完成,可以借助 Python 来实现。我认为,对于那些希望在技术道路上更进一步的 DBA,掌握 Python 代码编写是必不可少的。另外,Go 语言也是一个很好的选择,因为它在部署时无需过多的依赖包,且性能优秀,非常适合用于编写运维工具。最后,如果你需要开发运维管理平台,还需要掌握前端的 JavaScript 编程。

2.熟悉编程框架:熟悉一款主流的编程框架,如 JavaScript 的 Vue、Java 的 Spring Boot、Python 的 Django 等,有助于 DBA 更好地参与到应用程序的开发中,与开发团队合作。业务 DBA、开发 DBA 可以考虑掌握,运维 DBA 则不要求。

3.数据库开发知识:掌握数据库开发的基本知识,包括表设计、触发器设计、存储过程编写等,能够优化查询、设计数据库结构。DBA 都应当掌握。

4.熟悉数据结构与算法:了解常用的数据结构与算法,如堆栈、列表、队列、哈希等,有助于在处理复杂问题时更高效地解决。这个对于 DBA 来说不是必须的。

5.理解面向对象编程:掌握面向对象编程的原则和方法,有助于设计和维护复杂的应用程序。这个对于 DBA 来说不是必须的。

6.熟悉开发流程和工具:了解开发流程,如 DevOps,以及版本控制工具如 Git 等,能够更好地与开发团队协作,保障项目的顺利进行。这个对于 DBA 来说不是必须的。

上述列举了笔者认为 "懂开发" 需要达到的门槛,但实际上有很多条都不是 DBA 必须掌握的技能。所以可以得出结论,DBA 需要懂开发吗?答案是不需要。不需要完全懂开发,掌握了部分开发技能,例如 Python、shell 编程的 DBA 就可以参与到一些日常运维开发任务上来。而对于懂开发的 DBA,则可以实现更高的价值,技术道路走得更远,工作岗位也不局限于 DBA 了。在实际工作中,DBA 需要开发和参与开发的内容可能包括:

1.自动化脚本和工具:编写脚本和工具来监控数据库性能、高可用管理、执行备份恢复、自动化数据库部署和配置、巡检等。

2.数据库存储过程和触发器:设计和实现数据库存储过程和触发器,用于日常运维,例如 MySQL 自动创建分区表。

3.数据库优化:与开发团队一起优化 SQL 查询,改进数据库性能,提高应用程序的响应速度。

4.自动化运维平台的开发:这个就需要 "懂开发" 了。