胖虎的工具箱-编程导航 胖虎的工具箱-编程导航

超全的数据库建表、SQL、索引规范

2年前 (2022) 程序员胖胖胖虎阿
228 0 0
点击上方 
Java后端
，选择 设为星标

优质文章，及时送达

来源：
https://juejin.im/post/6871969929365553165

背景

因为工作岗位的原因，负责制定了关于后端组数据库的规约规范，作为所有产品线的规范，历经几版的修改，最终形成下边的文本，规范在整个后端执行也有大半年的时间，对于整个团队在开发阶段就减少不恰当的建表语句、错误SQL、错误的索引有积极的意义，故分享出来给大家参考。

下边分为建表规约、SQL规约、索引规约三个部分，每部分的每一条都有强制、建议两个级别，大家在参考时，根据自己公司的情况来权衡。
一、建表规约

【强制】（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并且提供默认值
解读：a、NULL的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 b、NULL这种类型Msql内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多 c、NULL值需要更多的存储空，无论是表还是索引中每行中的NULL的列都需要额外的空间来标识

【强制】（9）禁用保留字，如DESC、RANGE、MARCH等，请参考Mysql官方保留字。

【强制】（10）如果存储的字符串长度几乎相等，使用CHAR定长字符串类型。
解读：能够减少空间碎片，节省存储空间。

【建议】（11）在一些场景下，考虑使用TIMESTAMP代替DATETIME。
解读：a、这两种类型的都能表达
"yyyy-MM-dd HH:mm:ss"
格式的时间，TIMESTAMP只需要占用4个字节的长度，可以存储的范围为
(1970-2038)
年，在各个时区，所展示的时间是不一样的；b、而
DATETIME
类型占用8个字节，对时区不敏感，可以存储的范围为
(1001-9999)
年。

* 【建议】（12）当心自动生成的Schema，建议所有的Schema手动编写。
解读：对于一些数据库客户端不要太过信任。

二、SQL规约

【建议】 (1) 为了充分利用缓存，不允许使用自定义函数、存储函数、用户变量。
解读：如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果都不会被缓存。比如函数
NOW()
或者
CURRENT_DATE()
会因为不同的查询时间，返回不同的查询结果。

【强制】（2）在查询中指定所需的列，而不是直接使用“ *”返回所有的列
解读：a）读取不需要的列会增加
CPU、IO、NET
消耗 b）不能有效的利用覆盖索引

【强制】（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的情况，影响查询性能。
解读：1、假设有查询条件为
WHERE a=? and b=? ORDER BY c；
存在索引：
a_b_c
，则此时可以利用索引排序。2、反例：在查询条件中包含了范围查询，那么索引有序性无法利用，如:
WHERE a>10 ORDER BY b;
索引
a_b
无法排序。

【建议】（8）在where中索引的列不能某个表达式的一部分，也不能是函数的参数。
解读：即是某列上已经添加了索引，但是若此列成为表达式的一部分、或者是函数的参数，Mysql无法将此列单独解析出来，索引也不会生效。

【建议】 （9）我们在where条件中使用范围查询时，索引最多用于一个范围条件，超过一个则后边的不走索引。
解读：Mysql能够使用多个范围条件里边的最左边的第一个范围查询，但是后边的范围查询则无法使用。

【建议】 （10）在多个表进行外连接时，表之间的关联字段类型必须完全一致
解读：当两个表进行Join时，字段类型若没有完全一致，则加索引也不会生效，这里的完全一致包括但不限于字段类型、字段长度、字符集、collection等等
参考

  • 《High.Performance.MySQL.3rd.Edition》

  • 《阿里巴巴java开发手册》

- END -

    
    
    

 
     
     
     

  
      
      
      

   
       
       
       

    
        
        
        
     
         
         
         

      
          
          
          最近整理一份面试资料《Java技术栈学习手册》，覆盖了Java技术、面试题精选、Spring全家桶、Nginx、SSM、微服务、数据库、数据结构、架构等等。
     
         
         
         

     
         
         
         

      
          
          
          获取方式：点“ 在看，关注公众号 Java后端 并回复 777 领取，更多内容陆续奉上。
     
         
         
         

     
         
         
         

      
          
          
          推
      
          
          
          荐
      
          
          
          阅
      
          
          
          读 
     
         
         
         

     
         
         
         

      
          
          
          1. 看看人家接口写得，那叫一个优雅！
     
         
         
         

     
         
         
         

      
          
          
          2. 推荐一款 Java 对象映射神器
     
         
         
         

     
         
         
         

      
          
          
          3. 5W 字详解分库分表之 Sharding-JDBC 中间件
     
         
         
         

     
         
         
         

      
          
          
          4. TCP/IP 基础知识总结
     
         
         
         
5. 面试：如何决定使用 HashMap 还是 TreeMap？
超全的数据库建表、SQL、索引规范


     
         
         
         

      
          
          
          喜欢文章，点个
      
          
          
          在看 
      
          
          
          超全的数据库建表、SQL、索引规范

本文分享自微信公众号 - Java后端（web_resource）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

版权声明：程序员胖胖胖虎阿 发表于 2022年11月13日 下午6:32。
转载请注明：超全的数据库建表、SQL、索引规范 | 胖虎的工具箱-编程导航

相关文章

【JVM】JVM08(java内存模型解析[JMM])
程序员胖胖胖虎阿
314
idea的2022.1激活码(IDEA2021激活码激活教程,全年更新)
程序员胖胖胖虎阿
182
IntelliJ IDEA正版破解码(idea的2022.1激活码激活教程,永久有效)
程序员胖胖胖虎阿
309
IntelliJ IDEA永久永久激活码(Intellij IDEA永久 破解教程,激活至2099年)
程序员胖胖胖虎阿
332
windows/mac idea2022.1破解插件(IDEA2022.1激活破解教程,全年更新)
程序员胖胖胖虎阿
239
windows/mac idea2021注册码(windows/mac idea正版激活工具成功激活)
程序员胖胖胖虎阿
271

暂无评论

暂无评论...
致力于称为最好的程序员导航网站

友链申请 免责声明 关于我们

Copyright © 2022  胖虎的工具箱-编程导航   陇ICP备2022001249号