您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL使用规范手册,程序员必知必会

时间:2020-05-13 10:36:38  来源:  作者:

背景

一个平台或系统随着时间的推移和用户量的增多,数据库操作往往会变慢;而在JAVA应用开发中数据库更是尤为重要,绝大多数情况下数据库的性能决定了程序的性能,如若前期埋下的坑越多到后期数据库就会成为整个系统的瓶颈;因此,更规范化的使用MySQL在开发中是不可或缺的。

一、MySQL数据库命名规范

1、数据库所有表前缀均使用项目名称首字母缩写;

2、数据库所有对象名称均使用小写字母,并且单词之间通过下划线分开;

3、数据库所有对象名称禁止使用MySQL保留字及关键字,涉及到关键字的SQL查询需要将关键字用单引号括起来;

4、数据库所有对象名称不超过32个字符,并且命名要遵循见名知意原则;

5、数据库临时表必须以 pro_tmp_ 为前缀并且以日期 20190917 为后缀,备份表必须以 pro_bac 为前缀并以时间戳为后缀;(pro为项目名称首字母缩写)

6、数据库所有存储相同数据的列名和列类型必须保持一致。

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

1、若无特殊说明,建表时一律采用Innodb存储引擎。

选择合适的引擎可以提高数据库性能,如InnoDB和MyISAM,InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定;

基本的差别为:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持;MyISAM类型的表强调的是性能,其执行速度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能;

因此,其支持事务处理、支持外键、支持崩溃修复能力和并发控制是我们建表时首选的存储引擎。

2、数据库和表的字符集统一使用UTF8

数据库和表的字符集统一使用utf8,若是有字段需要存储emoji表情之类的,则将表或字段设置成utf8mb4;因为,utf8号称万国码,其无需转码、无乱码风险且节省空间,而utf8mb4又向下兼容utf8。

3、设计数据库时所有表和字段必须添加注释

使用Comment从句添加表和列的备注,或直接在数据库连接工具的注释栏添加注释,从项目开始就进行数据字典的维护。

使用Comment从句添加注释如:

-- 1、创建表:

CREATE TABLE t1(id varchar2(32) primary key,name VARCHAR2(8) NOT NULL,age number);

-- 2、添加表注释:

Comment on table t1 is '个人信息';

-- 3、添加字段注释:

comment on column t1.id is 'id';
comment on column t1.nameis '姓名';
comment on column t1.age is '年龄';

使用数据库连接工具添加注释:

4、单个表的数据量大小控制在500万以内

尽量控制单表数据量的大小,建议控制在500万以内;500万并不是MySQL数据库的极限,但数据量太多不利于对表结构进行修改、备份和恢复数据,适当采用分库分表等手段来控制单表数据量的大小。

5、使用MySQL分区表需谨慎

分区是将一个表的数据按照某种方式,比如按照时间上的月份,分成多个较小的,更容易管理的部分,但是逻辑上仍是一个表;分区表在物理上表现为多个文件,在逻辑上仍表现为同一个表,需要谨慎选择分区键;跨分区查询效率可能会更低,建议使用物理分区表等方式管理大数据。

6、尽量满足冷热数据分离,减小表等宽度

MySQL限制每个表最多存储4096列,并且每一行数据的大小不超过65535字节,为了减少磁盘IO线程的开销,就要适当控制表的宽度,因为表越宽,把表装载进内存缓冲池时所占用的内存也就越大,就会消耗更多的IO线程;除此之外,为了保证热数据的内存缓存命中率,更有效的利用缓存,避免读入无用的冷数据,尽量把经常使用到的列放到同一个表中,避免不必要的关联操作。

7、建立预留字段需谨慎

部分友人在设计数据库表时,不仅设计了当前所需要的字段,而且还在其中留出几个字段作为备用。比方说,我设计了一个人员表(Person),其中已经添加了各种必要的字段,包括姓名(Name)、性别(Sex)、出生年月日(birthday)等等;

为了以防万一,比如之后可能Person 表会涉及到毕业院校、工作单位、是否婚配和相片等信息,于是就加入5个varchar2 型的字段,分别叫做Text1、Text2……Text5;这一手操作看似防范于未然,其实也并不见得,因为大量预留字段会浪费空间、预留字段不能做到见名知意、预留字段无法确认存储的数据类型且修改其字段类型还可能会造成锁表等问题。

针对此等情况可以参考以下两点解决方案:

  1. 如果数量很少,而且信息的性质与原表密切相关,那么就可以直接在原表上增加字段,并将相关的数据更新进去;
  2. 如果数量较大,或者并非是原表对象至关重要的属性,那么就可以新增一个表,然后通过键值连接起来;

8、数据库中禁止存储图片、文件等大的二进制数据

若往数据库表中存储文件,而文件通常很大,当数据库进行读取操作时,会进行大量的随机IO操作,大文件使得IO操作很耗时耗性能,造成短时间内数据量快速增长;所以,通常将图片、文件存储在文件服务器中,数据库只用于存储文件地址信息。

三、MySQL数据库字段设计规范

1、优先选择符合存储需要的最小的数据类型。

主要是考虑索引的性能,因为列的字段越大,建立索引时所需要的空间也越大,这样一页中能存储的索引节点的数量也就越少,在遍历时需要的IO次数也就越多,索引的性能也就越差。

2、避免使用TEXT、BLOB数据类型

避免使用TEXT和BLOB数据类型,其中最常见的TEXT类型可以存储64K数据,MySQL内存临时表不支持TEXT、BLOB这样的大数据类型,若查询中包含这样的数据,在执行排序等操作时就不能使用内存临时表,必须使用磁盘临时表执行操作;

TEXT和BLOB类型只能使用前缀索引(当索引是很长的字符序列时,这个索引将会很占内存,而且会很慢,这时候就会用到前缀索引了;所谓的前缀索引就是去索引的前面几个字母作为索引,但是要降低索引的重复率,所以我们还必须要判断前缀索引的重复率;),因为MySQL对索引字段长度是有限的,所以TEXT类型只能使用前缀索引,并且TEXT列上是不能有默认值的;

若需要使用,建议把BLOB或TEXT列分离到单独的的扩展表中,且查询时一定不要使用select *,只需取出必要的列即可。

3、避免使用ENUM枚举类型

修改ENUM 值需要使用ALTER 语句;

ENUM 类型的ORDER BY 操作效率低;

禁止使用数值作为ENUM 的枚举值。

4、所有列的默认值定义为NOT NULL

数据库所有为NULL 的列需要额外的空间来存储,因此会占用更多的空间;

数据库在进行比较和计算时需要对NULL 值做特别处理。

5、使用TIMESTAMP(4字节)或DATETIME(8字节)类型存储时间

TIMESTAMP 存储的时间范围为:1970-01-01 00:00:01 ~ 2038-01-19-03:14:07;

TIMESTAMP 占用4字节和INT相同,但可读性比INT 类型的高,若是超出TIMESTAMP 取值范围的则使用DATETIME 类型存储;

用字符串类型存储时间的缺点:无法使用日期函数进行比较计算、字符串存储占有更多的空间。

6、财务相关的金额类数据必须使用decimal 类型

精准浮点:decimal

非精准浮点:float、double

Decimal类型为精准浮点数,在计算时不会丢失精度;占有空间大小由定义的宽度决定,每4个字节可以存储9位数字,且小数点也要占有一个字节;另外,Decimal类型可用于存储比bigint更大的数据类型。

四、MySQL索引设计规范

1、每张表的索引数量不超过5个

索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下还会降低查询效率,因此并不是越多越好,要控制其数量。

2、每个Innodb 表必须有一个主键

Innodb 是一种索引组织表,其数据存储的逻辑顺序和索引的顺序是相同的;

每张表可以有多个索引,但表的存储顺序只能有一种,Innodb 是按照主键索引的顺序来组织表的,因此不要使用更新频繁的列、UUID、MD5、HASH和字符串列作为主键,这些列无法保证数据的顺序增长,主键建议使用自增ID 值。

3、尽量避免使用外键约束

不建议使用外键约束(foreign key),但一定要在表与表之间的关联键上建立索引;

外键虽然可以保证数据的参照完整性,但外键也会影响父表和子表的写操作从而降低性能,还会使得表更耦合,建议在业务端实现。

五、MySQL数据库SQL开发规范

1、建议使用预编译语句进行数据库操作

预编译语句可以重复使用,相同的SQL语句可以一次解析,多次使用,减少SQL编译所需要的时间,提高处理效率;此外,还可以有效解决动态SQL带来的SQL注入问题。

2、避免数据类型的隐式转换

隐式转换如:SELECT 1 + "1";数值型 + 字符型 的隐式转换有可能会导致索引失效,以及一些意想不到的结果等。

3、充分利用表中存在的索引

1)避免使用双%号的查询条件

如 WHERE first_name like '%James%',若无前置%,只有后置%,则执行SQL语句时会用到列上的索引,双%号则不会使用列上的索引。

2)一条SQL语句只能使用复合索引中的一列进行范围查询

例如有weight、age、sex三列的联合索引,在查询条件中有weight列的范围查询,则在age和sex列上的索引将不会被使用;因此,在定义联合索引时,若某列需要用到范围查询,则将该列放到联合索引的右侧。

3)使用not exists 代替not in

因为not in 在SQL语句中执行时会导致索引失效。

4、杜绝使用SELECT * ,必须使用SELECT <字段列表> 查询

因为使用SELECT * 查询会消耗更多的CPU、IO和网络宽带资源,并且查询时无法使用覆盖索引。

5、禁止使用不含字段列表的INSERT 语句

如:INSERT into table_name values ('1','2','3'); 改为带字段列表的INSERT 语句:INSERT into table_name('c1','c2','c3') values ('1','2','3');

6、避免使用子查询,可以把子查询优化为join 关联操作

但是,通常子查询在in 子句中,且子查询中为简单SQL(即不包含union、group by、order by、limit从句)时,才可以把子查询转化为join关联查询进行优化;

子查询性能差的原因:

  • 子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响;
  • 由于子查询会产生大量的临时表也没有索引,所以会消耗过多的CPU和IO资源,产生大量的慢查询。

7、避免使用JOIN 关联太多表

1)在Mysql中,对于同一个SQL关联(join)多个表,每个join 就会多分配一个关联缓存,如果在一个SQL中关联的表越多,所占用的内存也就越大;

2)如果程序中大量的使用了多表关联的操作,同时join_buffer_size(MySQL允许关联缓存的个数)设置的也不合理的情况下,就容易造成服务器内存溢出的情况,就会影响服务器数据库性能的稳定性;

3)此外,对于关联操作来说,会产生临时表影响查询效率,而Mysql最多允许关联61个表,建议不超过5个;

8、对同一列对象进行or 判断时,使用in 替代or

in 的值只要涉及不超过500个,则in 操作可以更有效的利用索引,or 大多数情况下很少能利用到索引。

9、禁止使用order by rand() 进行随机排序

10、禁止在WHERE 从句中对列进行函数转换和计算

因为在WHERE 从句中对列进行函数转换或计算时会导致索引无法使用。

No推荐:

where date(end_time)='20190101'

推荐:

where end_time >= '20190101' and end_time < '20190102'

11、在明显不会有重复值时使用UNION ALL 而不是UNION

1)UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作;

2)UNION ALL 不会再对结果集进行去重操作;

12、把复杂、较长的SQL 拆分为为多个小SQL 执行

1)大SQL在逻辑上比较复杂,是需要占用大量CPU 进行计算一条SQL语句;

2)在MySQL中,一条SQL 语句只能使用一个CPU 进行计算;

3)SQL拆分后可以通过并行执行来提高处理效率。

六、MySQL数据库行为规范

1、超过100万行数据的批量操作(update delete insert),分多次进行

大批量操作可能会造成严重的主从延迟;

binlog日志为row格式时会产生大量的日志;

避免产生大事物操作。

2、对于大表使用pt-online-schema-change 修改表结构

1)避免大表修改产生的主从延迟、避免在对表字段进行修改时进行锁表;

2)pt-online-schema-change 它首先会建立一个与原表结构相同的新表,并且在新表上进行表结构的修改,然后再把原表中的数据复制到新表中,并在原表中增加一些触发器;然后,把原表中新增的数据也复制到新表中,在行所有数据复制完成之后,把新表命名成原表,并把原来的表删除掉,其是把原来一个DDL操作,分解成多个小的批次执行。

3、禁止给程序使用的账号授予super 权限

当达到最大连接数限制时,还运行1个有super权限的用户连接super权限只能留给DBA处理问题的账号使用。

4、对于程序连接数据库账号,遵循权限最小原则

程序使用数据库账号只能在一个数据库下使用,且程序使用的账号原则上不授予drop 权限。



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  Tags: MySQL  点击:(7)  评论:(0)  加入收藏
一、为什么要搭建主从架构呢1.数据安全,可以进行数据的备份。2.读写分离,大部分的业务系统来说都是读数据多,写数据少,当访问压力过大时,可以把读请求给到从服务器。从而缓解数据...【详细内容】
2021-12-15  Tags: MySQL  点击:(12)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  Tags: MySQL  点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  Tags: MySQL  点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  Tags: MySQL  点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  Tags: MySQL  点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  Tags: MySQL  点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  Tags: MySQL  点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  Tags: MySQL  点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Tags: MySQL  点击:(31)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条