总结数据库建表/SQL/索引规范

这篇文章主要介绍“总结数据库建表/SQL/索引规范”,在日常操作中,相信很多人在总结数据库建表/SQL/索引规范问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”总结数据库建表/SQL/索引规范”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!

创新互联主要业务有网站营销策划、成都网站建设、成都网站设计、微信公众号开发、小程序开发H5建站、程序开发等业务。一次合作终身朋友,是我们奉行的宗旨;我们不仅仅把客户当客户,还把客户视为我们的合作伙伴,在开展业务的过程中,公司还积累了丰富的行业经验、网络营销推广资源和合作伙伴关系资源,并逐渐建立起规范的客户服务和保障体系。 

一、建表规约

【强制】(1) 存储引擎必须使用InnoDB

解读:InnoDB支持事物、行级锁、并发性能更好,CPU及内存缓存页优化使得资源利用率更高。

【强制】(2)每张表必须设置一个主键ID,且这个主键ID使用自增主键(在满足需要的情况下尽量短),除非在分库分表环境下

解读:由于InnoDB组织数据的方式决定了需要有一个主键,而且若是这个主键ID是单调递增的可以有效提高插入的性能,避免过多的页分裂、减少表碎片提高空间的使用率。 而在分库分表环境下,则需要统一来分配各个表中的主键值,从而避免整个逻辑表中主键重复。

【强制】(3)必须使用utf8mb4字符集

解读:在MySQL中的UTF-8并非“真正的UTF-8”,而utf8mb4”才是真正的“UTF-8”。

【强制】(4) 数据库表、表字段必须加入中文注释

解读:大家都别懒。

【强制】(5) 库名、表名、字段名均小写,下划线风格,不超过32个字符,必须见名知意,禁止拼音英文混用

解读:约定。

【强制】(6)单表列数目必须小于30,若超过则应该考虑将表拆分

解读:单表列数太多使得Mysql服务器处理InnoDB返回数据之间的映射成本太高。

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

解读:外键会导致表与表之间耦合,UPDATE与DELETE操作都会涉及相关联的表,十分影响SQL的性能,甚至会造成死锁。

【强制】(8)必须把字段定义为NOT NULL并且提供默认值

解读:

  •  NULL的列使索引/索引统计/值比较都更加复杂,对MySQL来说更难优化;

  •  NULL这种类型Msql内部需要进行特殊处理,增加数据库处理记录的复杂性;同等条件下,表中有较多空字段的时候,数据库的处理性能会降低很多;

  •  NULL值需要更多的存储空,无论是表还是索引中每行中的NULL的列都需要额外的空间来标识。

【强制】(9)禁用保留字,如DESC、RANGE、MARCH等,请参考Mysql官方保留字

【强制】(10)如果存储的字符串长度几乎相等,使用CHAR定长字符串类型。

解读:能够减少空间碎片,节省存储空间。

【建议】(11)在一些场景下,考虑使用TIMESTAMP代替DATETIME

解读:

  •  这两种类型的都能表达"yyyy-MM-dd HH:mm:ss"格式的时间,TIMESTAMP只需要占用4个字节的长度,可以存储的范围为(1970-2038)年,在各个时区,所展示的时间是不一样的;

  •  而DATETIME类型占用8个字节,对时区不敏感,可以存储的范围为(1001-9999)年。

* 【建议】(12)当心自动生成的Schema,建议所有的Schema手动编写

解读:对于一些数据库客户端不要太过信任。

二、SQL规约

【建议】 (1) 为了充分利用缓存,不允许使用自定义函数、存储函数、用户变量

解读:如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表,其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间,返回不同的查询结果。

【强制】(2)在查询中指定所需的列,而不是直接使用“ *”返回所有的列

解读:

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

  •  不能有效的利用覆盖索引。

【强制】(3)不允许使用属性隐式转换

解读:假设我们在手机号列上添加了索引,然后执行下面的SQL会发生什么?explain SELECT user_name FROM parent WHERE phone=13812345678; 很明显就是索引不生效,会全表扫描。

【建议】(4)在WHERE条件的属性上使用函数或者表达式

解读:Mysql无法自动解析这种表达式,无法使用到索引。

【强制】(5)禁止使用外键与级联,一切外键概念必须在应用层解决

解读:外键与级联更新适用于单机低并发,不适合分布式、高并发集群;级联更新是强阻塞,存在数据库更新风暴的风险;外键影响数据库的插入速度。

【建议】(6)应尽量避免在WHERE子句中使用or作为连接条件

解读:根据情况可以选择使用UNION ALL来代替OR。

【强制】(7)不允许使用%开头的模糊查询

解读:根据索引的最左前缀原理,%开头的模糊查询无法使用索引,可以使用ES来做检索。

三、索引规约

【建议】(1)避免在更新比较频繁、区分度不高的列上单独建立索引

解读:区分度不高的列单独创建索引的优化效果很小,但是较为频繁的更新则会让索引的维护成本更高。

【强制】(2) JOIN的表不允许超过五个。需要JOIN的字段,数据类型必须绝对一致; 多表关联查询时,保证被关联的字段需要有索引

解读:太多表的JOIN会让Mysql的优化器更难权衡出一个“最佳”的执行计划(可能性为表数量的阶乘),同时要注意关联字段的类型、长度、字符编码等等是否一致。

【强制】(3)在一个联合索引中,若第一列索引区分度等于1,那么则不需要建立联合索引

解读:索引通过第一列就能够完全定位的数据,所以联合索引的后边部分是不需要的。

【强制】(4)建立联合索引时,必须将区分度更高的字段放在左边

解读:区分度更高的列放在左边,能够在一开始就有效的过滤掉无用数据。提高索引的效率,相应我们在Mapper中编写SQL的WHERE条件中有多个条件时,需要先看看当前表是否有现成的联合索引直接使用,注意各个条件的顺序尽量和索引的顺序一致。

【建议】(5)利用覆盖索引来进行查询操作,避免回表

解读:覆盖查询即是查询只需要通过索引即可拿到所需DATA,而不再需要再次回表查询,所以效率相对很高。我们在使用EXPLAIN的结果,extra列会出现:"using index"。这里也要强调一下不要使用“SELECT * ”,否则几乎不可能使用到覆盖索引。

【建议】(6)在较长VARCHAR字段,例如VARCHAR(100)上建立索引时,应指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可

解读:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,若长度为20的索引,区分度会高达90%以上,则可以考虑创建长度例为20的索引,而非全字段索引。例如可以使用SELECT COUNT(DISTINCT LEFT(lesson_code, 20)) / COUNT(*) FROM lesson;来确定lesson_code字段字符长度为20时文本区分度。

【建议】(7)如果有ORDER BY的场景,请注意利用索引的有序性。ORDER BY最后的字段是联合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort的情况,影响查询性能。

解读:

  •  假设有查询条件为WHERE a=? and b=? ORDER BY c;存在索引:a_b_c,则此时可以利用索引排序;

  •  反例:在查询条件中包含了范围查询,那么索引有序性无法利用,如:WHERE a>10 ORDER BY b; 索引a_b无法排序。

【建议】(8)在where中索引的列不能某个表达式的一部分,也不能是函数的参数

解读:即是某列上已经添加了索引,但是若此列成为表达式的一部分、或者是函数的参数,Mysql无法将此列单独解析出来,索引也不会生效。

【建议】 (9)我们在where条件中使用范围查询时,索引最多用于一个范围条件,超过一个则后边的不走索引

解读:Mysql能够使用多个范围条件里边的最左边的第一个范围查询,但是后边的范围查询则无法使用。

【建议】 (10)在多个表进行外连接时,表之间的关联字段类型必须完全一致

解读:当两个表进行Join时,字段类型若没有完全一致,则加索引也不会生效,这里的完全一致包括但不限于字段类型、字段长度、字符集、collection等等。

到此,关于“总结数据库建表/SQL/索引规范”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!


本文标题:总结数据库建表/SQL/索引规范
文章起源:http://bzwzjz.com/article/giicgo.html

其他资讯

Copyright © 2007-2020 广东宝晨空调科技有限公司 All Rights Reserved 粤ICP备2022107769号
友情链接: 网站设计制作 成都网站建设 成都网站建设 成都网站建设公司 企业网站设计 网站建设开发 定制网站设计 成都网站建设 重庆企业网站建设 手机网站建设 重庆网站建设 成都网站建设 网站建设费用 定制网站建设 成都企业网站建设公司 成都网站制作 成都商城网站建设 网站制作 营销型网站建设 网站设计 成都h5网站建设 成都商城网站制作