MySQL 数据库铁律(小结)

好的数据库规范有助于减少软件实现的复杂度，降低沟通成本，本铁律主要涵盖了建库建表、建索引、写 SQL、ORM 映射等方面的处理约定。

1.建库铁律

- 铁律 Level 备注
字符集 使用 utf-8。如果存储的是表情则选用 utf8mb4 进行存储。 强制
排序规则 使用 utf8_general_ci 强制

2.建表铁律

- 铁律 Level 备注
注释 一定要有字段注释。 强制
编码 使用 utf-8。如果存储的是表情则选用 utf8mb4 进行存储。 强制
是否概念的字段 必须用 is_xx 命名，数据类型是 unsigned tinyint（1是0否）例如 is_deleted（1删除0未删除）。 强制 任何字段如果非负数必须unsigned
表名、字段名 只能使用小写字母、下划线或者数字；禁止以下划线或者数字开头；禁止两个下划线之间只出现数字；禁用保留字；表名禁止使用复数名词。 强制
库名、表名的命名 库名尽量与应用名称一致，表名最好用 业务名称_表的作用 命名。 强制
索引命名 主键索引用 pk_字段名；唯一索引用 uk_字段名；普通索引用 idx_字段名。 强制 pk_ 即 primary key；uk_即 unique key；idx_即 index
小数类型 数据类型是 decimal，禁止使用 float 和 double，float 和 double 存在精度损失，如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。 强制
varchar类型 varchar是可变长字符串，不预先分配存储空间，长度不要超过5000个字符，如果长度大于5000应用text（独立出一张表来，用主键来对应，避免影响其他字段的索引效率）。 强制
表名必备三字段 id（数据类型是 unsigned bigint，单表递增，步长为1），gmt_create、gmt_modified（主动创建时间、被动更新时间，数据类型都是 datetime）。 强制
字段冗余 字段允许适当冗余，但必须考虑数据一致，冗余字段应具备1）不频繁修改；2）不是varchar超长字段，更不能是text字段。 推荐
分库分表 单表行数超过500万行或者单表容量超过2GB时，才推荐分库分表。 推荐

设置合适的字符存储长度，不但可以节约数据库表空间和索引存储，更重要的是能够提升检索速度。

3.建索引铁律

- 铁律 Level 备注
唯一索引 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引影响了 insert 速度，这个损耗可以忽略，但是明显提高了查询速度；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。 强制
join 超过三个表禁止 join，需要 join 的字段，数据类型必须一致；当多表关联查询时，保证被关联的字段需要有索引；即使双表 join 也要注意表索引、SQL 性能。 强制
varchar字段上建立索引 必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。索引长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。 强制
页面搜索禁止模糊 页面搜索禁止左模糊或者全模糊，如果有需要请走搜索引擎来解决。禁止原因：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。 强制
order by 如果有 order by 的场景，请注意索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。正例：where a=? and b=? order by c; 索引应建为 a_b_c；反例：索引中有范围查找，那么索引有序性无法利用，如 where a>10 order by b; 索引 a_b 无法排序。 推荐

4.写SQL铁律

- 铁律 Level 备注
count(*) 不要使用 count(列名) 或 count(常量) 来替代 count(*)，count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 的行。 强制
count(distinct col) 计算该列除 NULL 外的不重复行数。注意，count(distinct col1, col2)，如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。 强制
sum(col) 当一列的值全为 NULL 时，count(col) 的返回结果为 0，但 sum(col) 的返回结果为 NULL，因此使用 sum() 时需要注意 NPE 问题。可用如下方式避免 NPE 问题：select if(isnull(sum(g)), 0, sum(g)) from table; 强制
isnull 使用 isnull() 来判断是否为 NULL 值。NULL 与任何值的比较都为 NULL。 强制
分页查询逻辑 若 count 为 0 应直接返回，避免执行后面的分页语句。 强制
外键与级联 禁止使用外键与级联，一切外键概念必须在应用层解决。原因：外键与级联不适合分布式、高并发集群，级联更新是强阻塞，存在数据库更新风暴的风险，外键影响数据库的插入速度。 强制
存储过程 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。 强制
数据订正 数据订正（特别是删除、修改记录操作）时要先 select，避免出现误删除，确认无误后才能执行更新语句。 强制
in in 操作能避免就避免，如果实在避免不了，in 后面的集合元素数量要控制在 1000 个以内。 推荐
truncate table 禁止使用 truncate table，truncate table 比 delete 速度快，且使用的系统和日志资源少，但是 truncate 无事务且不触发 trigger，有可能造成事故，故不要在开发代码中使用此语句。 参考

5.ORM映射铁律

- 铁律 Level 备注
表查询 禁止使用 * 作为查询的字段列表，需要哪些字段必须明确。 强制
POJO POJO 类的布尔属性不能加 is，而数据库字段必须加 is，要求在 resultMap 中进行字段与属性之间的映射。 强制
返回参数 禁止用 resultClass 作为返回参数，即使所有类属性名与数据库字段一一对应，也需要定义；反过来，每一个表也必然有一个属性与之对应。原因：配置映射关系，使字段与 DO 类结耦，方便维护。 强制
返回参数 禁止直接使用 HashMap、HashTable 作为查询结果集的输出。原因：属性值的类型不可控。 强制
sql.xml 配置参数 sql.xml 配置参数使用 #{}, #param#，不要使用 ${}，${} 容易出现SQL注入。 强制
queryForList 禁止使用 Mybatis 自带的 queryForList(String statementName, int start, int size)。原因：其实现方式是在数据库取到 statementName 对应的 SQL 语句的所有记录，再通过 subList 取 start, size 的子集合。 强制
更新时间 更新数据库表记录时，必须同时更新记录对应的修改时间。 强制
更新数据库表记录 不要写一个大而全的数据更新接口（传入为 POJO 类）。执行 SQL 时，不要更新无改动的字段，原因：容易出错、效率低、增加 binlog 存储。 推荐
@Transactional @Transactional 事务不要滥用。事务会影响数据库的 QPS。另外，使用事务的地方需要考虑各方面的回滚方案，包括缓存回滚、搜索引擎回滚、消息补偿、统计修正等。 参考
Mybatis 动态sql标签 < isEqual> 中的 compareValue 是与属性值对比的常量，一般是数字，表示相等时执行相应的 SQL 语句；< isNotEmpty> 表示不为空且不为 null 时执行；< isNotNull> 表示不为 null 时执行。 参考

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • MySQL数据库导出与导入及常见错误解决

    MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:\Program Files\MySQL\MySQL Server 4.1\bin (或者直接将windows的环境变量path中添加该目录) 2,导出数据库:mysqldump -u 用户名 -p 数据库名 > 导出的文件名 如我输入的命令行:mysqldump -u root -p jluibmclub > d:\ jluibmclub .sql (输入

  • MySQL数据库引擎介绍、区别、创建和性能测试的深入分析

    数据库引擎介绍 MySQL数据库引擎取决于MySQL在安装的时候是如何被编译的.要添加一个新的引擎,就必须重新编译MYSQL.在缺省情况下,MYSQL支持三个引擎:ISAM.MYISAM和HEAP.另外两种类型INNODB和BERKLEY(BDB),也常常可以使用.如果技术高超,还可以使用MySQL+API自己做一个引擎.下面介绍几种数据库引擎:  ISAM:ISAM是一个定义明确且历经时间考验的数据表格管理方法,它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数.因此,ISAM执行读取

  • MySQL数据库远程连接开启方法

    MySQL数据库远程连接开启方法

    第一中方法:比较详细以下的文章主要介绍的是MySQL 数据库开启远程连接的时机操作流程,其实开启MySQL 数据库远程连接的实际操作步骤并不难,知识方法对错而已,今天我们要向大家描述的是MySQL 数据库开启远程连接的时机操作流程. 1.d:\MySQL\bin\>MySQL -h localhost -u root 这样应该可以进入MySQL服务器 复制代码 代码如下: MySQL>update user set host = '%' where user = 'root'; MySQL&g

  • MYSQL数据库导入数据时出现乱码的解决办法

    首先在新建数据库时一定要注意生成原数据库相同的编码形式,如果已经生成可以用phpmyadmin等工具再整理一次,防止数据库编码和表的编码不统一造成乱码. 方法一: 通过增加参数 –default-character-set = utf8 解决乱码问题 mysql -u root -p password < path_to_import_file –default-character-set = utf8 方法二: 在命令行导入乱码解决 1. use database_name; 2. set n

  • MYSQL 数据库导入导出命令

    MySQL命令行导出数据库 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:\Program Files\MySQL\MySQL Server 4.1\bin (或者直接将windows的环境变量path中添加该目录) 2,导出数据库:mysqldump -u 用户名 -p 数据库名 > 导出的文件名 如我输入的命令行:mysqldump -u root -p news > news.sql   (输入后会让你输入进入MySQL的密码

  • MySQL数据库远程访问权限如何打开(两种方法)

    下载GPL版本安装 MySQL Community Edition(GPL) Community (GPL) Downloads » 在我们使用mysql数据库时,有时我们的程序与数据库不在同一机器上,这时我们需要远程访问数据库.缺省状态下,mysql的用户没有远程访问的权限. 下面介绍两种方法,解决这一问题. 1.改表法 可能是你的帐号不允许从远程登陆,只能在localhost.这个时候只要在localhost的那台电脑,登入mysql后,更改 "mysql" 数据库里的 "

  • MySQL数据库备份与恢复方法

    MySQL数据库备份与恢复方法

    常有新手问我该怎么备份数据库,下面介绍3种备份数据库的方法: (1)备份数据库文件 MySQL中的每一个数据库和数据表分别对应文件系统中的目录和其下的文件.在Linux下数据库文件的存放目录一般为/var/lib/mysql.在Windows下这个目录视MySQL的安装路径而定,DiaHosting的技术员一般为客户安装在D:serversoftmysql下.如,有一个名为bbs的数据库,那么bbs的数据库文件会存放在/var/lib/mysql/bbs(linux)或者D:serversoft

  • MySQL数据库命名规范及约定

    一.[操作规范]1. 如无备注,则表中的第一个id字段一定是主键且为自动增长:2. 如无备注,则数值类型的字段请使用UNSIGNED属性:3. 如无备注,排序字段order_id在程序中默认使用降序排列:4. 如无备注,所有字段都设置NOT NULL,并设置默认值:5. 如无备注,所有的布尔值字段,如is_hot.is_deleted,都必须设置一个默认值,并设为0:6. 所有的数字类型字段,都必须设置一个默认值,并设为0:7. 针对varchar类型字段的程序处理,请验证用户输入,不要超出其预

  • MySQL数据库恢复(使用mysqlbinlog命令)

    1:开启binlog日志记录 修改mysql配置文件mysql.ini,在[mysqld]节点下添加 复制代码 代码如下: # log-bin log-bin = E:/log/logbin.log 路径中不要包含中文和空格.重启mysql服务.通过命令行停止和启动mysql服务 复制代码 代码如下: c:\>net stop mysql; c:\>net start mysql; 进入命令行进入mysql并查看二进制日志是否已经启动 Sql代码 复制代码 代码如下: mysql>sho

  • MySQL 数据库铁律(小结)

    好的数据库规范有助于减少软件实现的复杂度,降低沟通成本,本铁律主要涵盖了建库建表.建索引.写 SQL.ORM 映射等方面的处理约定. 1.建库铁律 - 铁律 Level 备注 字符集 使用 utf-8.如果存储的是表情则选用 utf8mb4 进行存储. 强制 排序规则 使用 utf8_general_ci 强制 2.建表铁律 - 铁律 Level 备注 注释 一定要有字段注释. 强制 编码 使用 utf-8.如果存储的是表情则选用 utf8mb4 进行存储. 强制 是否概念的字段 必须用 is_

  • MySQL 数据库的监控方式小结

    目录 1.连接数(Connects) 2.缓存(bufferCache) 3.锁(lock) 4.慢SQL 5.statement 6.吞吐(Database throughputs) 7.数据库参数(serverconfig) 8.慢 SQL排查步骤 9.总结 对于当前数据库的监控方式有很多,分为数据库自带.商用.开源三大类,每一种都有各自的特色: 而对于 mysql 数据库由于其有很高的社区活跃度,监控方式更是多种多样,不管哪种监控方式最核心的就是监控数据,获取得到全面的监控数据后就是灵活的

  • 命令行模式下备份、还原 MySQL 数据库的语句小结

    为了安全起见,需要经常对数据库作备份,或者还原.对于 MySQL 而言,最方便的方法可能就是用 phpMyAdmin 的导出.导入功能了,但如果你的数据库体积比较大,作为 Web 应用的 phpMyAdmin 可能会遭遇"超时"而操作失败.所以,学会在命令行模式下备份.还原数据库,还是很有必要的. 1.备份数据库 在 Linux 命令行模式下备份 MySQL 数据库,用的是 mysqldump 命令: 复制代码 代码如下: mysqldump -u mysqluser -p test_

  • 远程访问MySQL数据库的方法小结

    三种解决方法: 1.改表法.可能是你的帐号不允许从远程登陆,只能在localhost.这个时候只要在localhost的那台电脑,登入mysql后,更改 "mysql" 数据库里的 "user" 表里的 "host" 项,从"localhost"改称"%" mysql -u root -pvmware mysql>use mysql; mysql>update user set host = '

  • MySQL数据库常用命令小结

    目录 1.对数据库常用命令 2.数据库中对表的命令 插入数据 数据查询 常用函数 模糊查询 多表连接查询 1.对数据库常用命令 1.连接数据库mysql -u用户名 -p密码2.显示已有数据库show databases;3.创建数据库create database sqlname;4.选择数据库use database sqlname;5.显示数据库中的表(先选择数据库)show tables;6.显示当前数据库的版本信息以及连接用户名select version(),user();7.删除数

  • MySql数据库中Select用法小结

    一.条件筛选 1.数字筛选:sql = "Select * from [sheet1$] Where 销售单价 > 100" 2.字符条件:sql = "Select * from [sheet1$] Where 物品名称 ='挡泥板'" 3.日期条件:sql = "Select * from [sheet1$] Where 物品名称 ='挡泥板'" 4.区间条件:sql = "Select * from [sheet1$] Wh

  • C++连接mysql数据库的两种方法小结

    现在正做一个接口,通过不同的连接字符串操作不同的数据库.要用到mysql数据库,以前没用过这个数据库,用access和sql server比较多.通过网上的一些资料和自己的摸索,大致清楚了C++连接mysql的方法.可以通过2种方法实现. 第一种方法是利用ADO连接, 第二种方法是利用mysql自己的api函数进行连接. 第一种方法可以实现我当前的需求,通过连接不同的字符串来连接不同的数据库.暂时只连接了mysql,sqlserver,oracle,access.对于access,因为它创建表的

  • MySQL 数据库 like 语句通配符模糊查询小结

    MySQL 报错:Parameter index out of range (1 > number of parameters, which is 0)--MySQL 数据库 like 语句通配符模糊查询小结 前言 今天在使用MySQL语句执行增删改查操作时,控制台报出了以下错误:Parameter index out of range (1 > number of parameters, which is 0).翻译过来意思就是:查到结果数据为1,真实值应为0,参数越界,产生错误.如此也就明

  • 六条比较有用的MySQL数据库操作的SQL语句小结

    在MySQL数据库操作中,我们常常编写一些SQL语句来实现自己想要的功能.但是对于初学MySQL数据库的人来说这似乎又有一定的难度.本文我们总结了六条比较有用的SQL语句,初学者可以套用下面的格式,接下来就让我们一起来了解一下这部分内容. 1.计算年数 如果您想通过生日来计算这个人的年龄,可以用以下的语句来实现: SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0; 2.两个时间的差 取得

  • MySQL数据库备份和还原的常用命令小结

    备份MySQL数据库的命令 mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 备份MySQL数据库为带删除表的格式备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库. mysqldump ---add-drop-table -uusername -ppassword databasename > backupfile.sql 直接将MySQL数据库压缩备份 m

随机推荐