MySQL学习总结(1)——MySQL命名、设计及使用规范

2018-01-13 11:02:30来源:网络收集作者:管理员人点击

分享

阿里云爆款
命名规范


基本命名原则


使用有意义的英文词汇,词汇中间以下划线分隔。(不要用拼音)只能使用英文字母,数字,下划线,并以英文字母开头。库、表、字段全部采用小写,不要使用驼峰式命名。避免用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存储图片、文件等。建议使用其他方式存储(TFS/SFS),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 UPDATEREPLACE 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方式来解决。


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台