Java互联网架构-Mysql数据库设计规范

序言

数据库设计（Database Design)是指对于一个给定的应用环境，构造最优的数据库模式，建立数据库及其应用系统，使之能够有效地存储数据，满足各种用户的应用需求（信息要求和处理要求）。

数据库设计规范

命名规则

表名命名

数据库表的名称应尽量采用和存贮内容相符的英文名词，如city, category, friends等。
表名小写
如果表名由几个单词组成，则单词间用下划线(“_”)分割，如：god_cities、user_money等。
表名尽量用英文全拼，少用缩写，如：system（非sys）、management(非mng) 等。
表名需要限制在30个字符内。当表的全名超过30字符时，可用缩写来减少表名的长度。

例如：

description==>desc、information==>info、address==>addr。
如果同一个库中分表，命名形式：<表名>_<分表序号>

字段命名

字段名为有意义的单词，或单词的缩写
字段名小写
如果字段由几个单词组成，则单词间用下划线(“_”)分割，如client_id、post_code等。
字段名限制在30个字符内。当字段名超过30字符时，可用缩写来减少字段名的长度。

例如：

description==>desc、information==>info、address==>addr
常用字段命名保持一致：

例如：常用名词典

索引命名规则

索引名限制在30个字索引须按照idx/uk_<表名><column1><column2>,其中<table>是建立索引的表名，<column>是建立索引的字段名，普通索引用“idx_”开头，唯一索引“uk_”开头。
当索引名超过30字符时，可用缩写来减少索引名的长度。

例如：

description==>desc、information==>info、address==>addr

主键、外键命名规则

主键有三种命名方式：<表名>_id、表名首字母缩写_id、id
外键按照<表名>id<nn>的规则命名，其中<nn>可选，当表中存在多个来自同一个表的外键时，需要做区分，可以用<nn>来描述。

对象名	前缀	范例
视图（view）	v_/v	v_user_info
触发器（trigger）	trg_	trg_user_info
存储过程（procedure）	p_	p_user_info
函数（function）	f_	f_user_info

设计约定

字段约定

数据库表中的字段都不允许为空（NOT NULL），需要有默认值。字符型的默认值为一个空字符值串''。
为了配合BI的需求，所有的表都需要有modifytime和createtime字段:

　　modifytime：timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',

　　createtime：timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

主键约定

所有数据库必须要有主键，优先使用自增主键。
数据库主键必须用 unsigned int，只有一些特殊情况如feed表才用big int。

分表约定

如果是分表，请在表名形式如：<表名>_<seq>，seq为分表序号

表引擎约定

表的引擎统一采用InnoDB

字符集约定

表的字符集统一采用utf8mb4

触发器约定

禁止在表上使用Trigger

外键约定

外键不需要显示的声明定义

其它约定

一个库尽量不要超过300张表，单表记录尽量控制在1000万以内，单表文件大的小控制在1.5G以内，一行（Row）尽量不要超过200个字节（约50个Int或20个Char(10)字段）。

数据库操作规范

项目中对数据库的操作没有事务的概念，数据正确性需要用程序保证。
查询数据库不允许关联查询。

数据库使用技巧

设计技巧

整型存储小，读取速度会快，将部分字段转成整型存贮是不错的选择，例如：将IP地址char(15)转成int unsigned存储、将部分长度>1的状态字段用tinyint存储。

tinyint： 1Byte 无符号：0-255 有符号：-128至127

smallint：2Byte 无符号：0-65535

mediumint:3Byte 无符号：0-16777215

int :4Byte 无符号：0-42亿\+

bigint :8Byte

Float :4Byte

Double :8Byte
尽量不要使用TEXT/BLOB，TEXT/BLOB类型处理能力较差，强制生产临时表，浪费更多空间，解决办法：

a.能否使用varchar替换
b.垂直拆分表，将text字段单独做一个表

索引可以改善查询，但会减慢更新，索引不是越多越好，最好不超过字段数的20%（在数据增、删、改比较频繁的表中，索引数量不应超过5个.）
离散程度越小，不适合加索引，例如：不要给性别建索引、不要给一些状态值建索引.
离避免在空值(Null)很多的字段上建立索引，大量空值会降低索引效率.
避免在数据值分布不均的字段上建立索引，个别数据值占总数据量的百分率明显比其它数据值占总数据量的百分率高，表明该字段数据值分布不均，容易引起数据库选择错误索引，生成错误的查询执行计划.
在数据量较少且访问频率不高的情况下，如只有一百行记录以下的表不需要建立索引。因为在数据量少的情况下，使用全表扫描效果比走索引更好
字符字段如果需要建索引，需建前缀索引

单字母区分度：26
6个字母区分度：26*26*26*26*26*26=308915776
对于26个英文字母组成的字段，只需要建char(6)的前缀索引就足够了

唯一索引：在建立索引的字段所有数值都具有唯一性特点的情况下，建立唯一索引(unique index)代替普通索引，唯一索引(unique index)查询效率比普通索引查询效率更高，可以大幅提升查询速度.
关于联合索引，避免建立两个或以上功能相同索引。

例如:已经建立字段A、B两个字段的索引，应该避免再建立字段A的单独索引。两个索引之间，对相同的查询都会起到相同的作用。建立两个功能相同的索引，反而会容易引起数据库产生错误的查询计划，降低查询效率

关于联合索引，选择正确的组合索引字段顺序，最常用的查询字段和选择性、区分度较高的字段，应该作为索引的前导字段使用，并遵循从左到右的顺序。
关于联合索引，合适的字段数，组合索引的字段数不适宜较多，较多的组合索引字段数会降低索引查询效率，组合索引字段数应不多于3个，如业务特点需要建立多字段的组合主键例外.
如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。对于核心sql，要想尽一切办法使用覆盖索引。

SQL语句

使用prepared statement，可以提高性能并且避免SQL注入。
UPDATE、DELETE语句不使用LIMIT。

a) 可能导致主从数据不一致
b) 会记录到错误日志，导致日志占用大量空间

WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化，原则：数值对数值，字符对字符。
拒绝Big sql，改为多个简单sql

a)OLTP中，绝对不允许使用多表关联查询，改为单表查询，将Mysql当做Nosql使用。

Mysql vs传统数据库设计思想，传统思想：尽量减少sql命令次数，一次获取所有数据，但Mysql可以快速打开和关闭连接。

b)不要使用生产数据库查询、统计大数据
openSession和closeSession之间，尽量减少业务操作，减少链接占用时间。恶习：用户发帖过程，先打开链接，等待图片上传后做，插入操作后关闭连接。
尽量不用select *，只取用到的列

a)减少表变化带来的影响
b)为使用覆盖索引提供了可能性
c)减少硬盘临时表的生产，尤其是含TEXT/BLOB字段

OR语句的改写

a)同一字段改成in，注意控制in的个数，小于200
b)不同字段，改成union all

用INSERT … ON DUPLICATE KEY UPDATE避免unique key引起的插入错误

CREATE TABLE ipstats (

ip VARCHAR(15) NOT NULL UNIQUE,

clicks SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0'

);

INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1;
MySQL中，有两种方式生成有序结果集，尽量用Index排序

filesort 糟糕
Index排序好

尽量不要让数据库做运算，复杂的运算移到程序端。从单条SQL来看，此SQL消耗的CPU资源有限，但如果调用的频率非常高，就会引起非常明显的CPU消耗。

例如：

MD5()/Order by Rand()

SELECT NOW();

SELECT CURRENT_TIME;

SELECT CURRENT_DATE;

SELECT CURRENT_TIMESTAMP;等
关于Count，myisam引擎会比较快，但Innodb不会存储总行数，因此速度较慢，请谨慎使用，必要时采用程序内存计数，定期校准的方式。

Explain语句

程序猿养成良好习惯，explain每一个写出的sql。
explain中一些重要的列，至少要rang以上，在Extra列尽量避免extra列出现：Using File Sort，Using Temporary

a) type列：all > index > range > ref > eq_ref > const,system

　　all：全表扫描

　　index：全索引扫描

　　rang：以范围的形式扫描索引，> < in查询

　　ref:非唯一性索引访问

　　eq_ref：最多只会有一条匹配结果，一般使用有唯一性索引查找（主键或唯一性索引） const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次 system：系统表，表中只有一行数据
b) possible_keys：该查询可以利用的索引。如果没有任何索引可以使用，就会显示成null，这项内容对优化索引时的调整非常重要.
c) key：MySQL Query Optimizer 从 possible_keys 中所选择使用的索引
d) rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。
e) Extra：辅助信息

　　Using index：所需数据只需在 Index 即可全部获得，不须要再到表中取数据。OK

　　Using filesort：当查询中含ORDER BY操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。Bad

　　Using temporary：使用临时表时，主要常见于 GROUP BY 和 ORDER BY 等操作中 Bad

　　Using where：如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息。可理解为“后过滤”（Post-filter），所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”。

　　SELECT tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或MAX()的时候。OK

　　Using index for group-by：数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。