线上数据库使用规范

发表于 | 分类于
字数统计 2.6k | 阅读时长 10
本文包括数据库的一些命名规范，库、表约束规范，字段定义规范，索引规范，SQL涉及规范等。

命名规范

库名、表名、字段名、索引

库名、表名、字段名使用驼峰命名。（线上库为大小写敏感）

MySQL有配置参数lower_case_table_names=1，即库表名以小写存储，大小写不敏感。如果是0，则库表名以实际情况存储，大小写敏感；如果是2，以实际情况存储，但以小写比较。

查看MySQL是否大小写敏感：

1
2
3
4
5
6
7
8
mysql> show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.01 sec)

库名以【db】开头(暂未遵循)

1
create database db_xxx default character set=utf8;

表名以【tbl】开头,如：tblUserMsg

职责相近的表，命名规则应该相同，功能相关的表尽量采用相同的前缀。例如:

1
2
3
tblAtlasAtlas        
tblAtlasComment      
tblAtlasPic

字段名以【驼峰】风格命名

索引命名

  • 数据表必须有主键，且建议均使用auto_increment的id作为主键（与业务无关）,和业务相关的要做为唯一索引，原则上必须定义PRIMARY KEY (id)。
  • 普通索引以【idx_】开头后面紧跟相关列（idx_col1_col2）
    如：KEY idx_msgType_status (msgType,status)
  • 唯一索引以【uk_】开头（uk_col1_col2 ）
    如：UNIQUE KEY uk_adminUid_userUid (adminUid,userUid)

命名长度限制

库名、表名、字段名禁止超过32个字符，需见名知意。

临时库、表名须以tmp加日期为后缀（原则上不允许线上创建临时表）

比如tblCrmEcRecord201403。
不具有时间特性的，直接以 tblName01 这样的方式命名。

库、表约束规范

存储引擎

使用Innodb存储引擎

字符集

默认使用utf8字符集（由于数据库定义使用了默认，数据表可以不再定义，但为保险起见，建议都写上）

建表规范

所有表都必须要显式指定主键，每个字段都要有注释，，写明白这个表中存放的数据内容。
例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
tblUserMsg |CREATE TABLE `tblUserMsg` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增私信id',
  `msgType` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '0:test,1:单个、多个用户，2：全部用户',
  `status` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '0 : not start , 1: dealing,3:finished',
  `senderUid` bigint(20) unsigned NOT NULL COMMENT '私信发送者uid',
  `startUid` bigint(20) unsigned NOT NULL COMMENT '发送起始用户id',
  `curUid` bigint(20) unsigned NOT NULL COMMENT '当前发送用户id',
  `endUid` bigint(20) unsigned NOT NULL COMMENT '最后发送用户id',
  `percent` decimal(5,4) unsigned NOT NULL DEFAULT '0.0000' COMMENT 'produce percent',
  `createTime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'create time',
  `updateTime` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'update time',
  `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '0 : not , 1: deleted',
  `ext` varchar(5000) NOT NULL DEFAULT '[]' COMMENT 'extension json',
  PRIMARY KEY (`id`),
  KEY `idx_msgType_status` (`msgType`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8 COMMENT='用户私信列表'

关于分表

预估表数据量，如果数据量较大（超过1000w）则需要考虑分表策略。要分表的数据表必须与DBA商量分表策略。

字段定义规范

char、varchar、text等字符串类型

  • 对于长度基本固定的列，如果该列恰好更新又特别频繁，适合char
  • varchar虽然存储变长字符串，但不可太小也不可太大。UTF8最多能存21844个汉字，或65532个英文
  • varbinary(M)保存的是二进制字符串，它保存的是字节而不是字符，所以没有字符集的概念，M长度0-255（字节）。只用于排序或比较时大小写敏感的类型，不包括密码存储
  • TEXT类型与VARCHAR都类似，存储可变长度，最大限制也是2^16，但是它20bytes以后的内容是在数据页以外的空间存储（row_format=dynamic），对它的使用需要多一次寻址，没有默认值。
    一般用于存放容量平均都很大、操作没有其它字段那样频繁的值。
    网上部分文章说要避免使用text和blob，要知道如果纯用varchar可能会导致行溢出，效果差不多，但因为每行占用字节数过多，会导致buffer_pool能缓存的数据行、页下降。另外text和blob上面一般不会去建索引，而是利用sphinx之类的第三方全文搜索引擎，如果确实要创建（前缀）索引，那就会影响性能。凡事看具体场景。
    另外尽可能把text/blob拆到另一个表中
  • BLOB可以看出varbinary的扩展版本，内容以二进制字符串存储，无字符集，区分大小写，有一种经常提但不用的场景：不要在数据库里存储图片。

int、tinyint、decimal等数字类型

  • 使用tinyint来代替 enum和boolean，ENUM类型在需要修改或增加枚举值时，需要在线DDL，成本较高；ENUM列值如果含有数字类型，可能会引起默认值混淆，tinyint使用1个字节，一般用于status,type,flag的列
  • 建议使用 UNSIGNED 存储非负数值，相比不使用 unsigned，可以扩大一倍使用数值范围
  • int使用固定4个字节存储，int(11)与int(4)只是显示宽度的区别
  • 使用Decimal 代替float/double存储精确浮点数，对于货币、金额这样的类型，使用decimal，如 decimal(9,2)。float默认只能能精确到6位有效数字

timestamp与datetime选择

建议字段都定义为NOT NULL

索引定义规范

sql上线，先explain，查看索引使用情况

尽量避免extra列出现：Using File Sort，Using Temporary，rows超过1000的要谨慎上线。
explain解读:

  • type：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
  • possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
  • key：表示MySQL实际决定使用的键（索引）,如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
  • ref：表示选择 key 列上的索引，哪些列或常量被用于查找索引列上的值
  • rows：根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
  • Extra
    • Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
    • Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”

其他索引规范

  • 单个索引字段数不超过5，单表索引数量不超过5，索引设计遵循B+ Tree索引最左前缀匹配原则
  • 选择区分度高的列作为索引，区分度高的放在前面
  • 对字符串使用前缀索引，前缀索引长度不超过8个字符
  • 建议优先考虑前缀索引，必要时可添加伪列并建立索引
  • 建立的索引能覆盖80%主要的查询，不求全，解决问题的主要矛盾
  • DML和order by和group by字段要建立合适的索引
  • 避免索引的隐式转换
    隐式转换例子，字段定义为varchar，但传入的值是个int，就会导致全表扫描，要求程序端要做好类型检查
    字段：remark varchar(50) NOT Null
1
2
3
4
mysql>SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark=115127;
1 row in set (0.14 sec)
mysql>SELECT id, gift_code FROM pool_gift WHEREdeal_id = 640 AND remark=‘115127’;
1 row in set (0.005 sec)
  • 避免冗余索引
  • 关于主键：表必须有主键 ；不使用更新频繁的列 ；不选择字符串列 ；不使用UUID MD5 HASH ；默认使用非空的唯一键 ，建议选择自增或发号器
  • 重要的SQL必须被索引：UPDATE、DELETE语句的WHERE条件列；ORDER BY、GROUP BY、DISTINCT的字段；多表JOIN的字段
  • 核心SQL优先考虑覆盖索引
  • 不在低基数列上建立索引，例如“性别”
  • 不在索引列进行数学运算和函数运算
  • 尽量不使⽤外键 ，外键用来保护参照完整性，可在业务端实现；对父亲和子表的操作会相互影响，降低可用性 ；INNODB本身对online DDL的限制
  • 不使⽤%前导的查询，如like “%ab”
  • 不使用负向查询，如not in/like “无法使用索引，导致全表扫描

最左前缀原则

  • mysql使用联合索引时，从左向右匹配，遇到断开或者范围查询时，无法用到后续的索引列
    比如索引idx_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)、(c1,c2)、(c1,c2,c3)三个索引，where条件包含上面三种情况的字段比较则可以用到索引，但像 where c1=a and c3=c 只能用到c1列的索引，像 c2=b and c3=c等情况就完全用不到这个索引

尽量不要在频繁更新的列上创建索引

如不在定义了 ON UPDATE CURRENT_STAMP 的列上创建索引，维护成本太高（好在mysql有insert buffer，会合并索引的插入）

SQL设计规范

  • 杜绝直接 SELECT * 读取全部字段
  • 能确定返回结果只有一条时，使用 limit 1
  • 使用like模糊匹配，%不要放首位
    会导致索引失效，有这种搜索需求是，考虑其它方案，如sphinx全文搜索
  • 涉及到复杂sql时，务必先参考已有索引设计，先explain
  • IN的内容尽量不超过200个
  • 拒绝大事务
    比如在一个事务里进行多个select，多个update，如果是高频事务，会严重影响MySQL并发能力，因为事务持有的锁等资源只在事务rollback/commit时才能释放。但同时也要权衡数据写入的一致性。
  • 减少与数据库交互的次数，尽量采用批量SQL语句
  • INSERT … ON DUPLICATE KEY UPDATE …，插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE，如果不重复则直接插入，影响1行。
  • REPLACE INTO类似，但它是冲突时删除旧行。INSERT IGNORE相反，保留旧行，丢弃要插入的新行。
  • INSERT INTO VALUES(),(),()，合并插入。
  • 去掉where 1=1 这样无意义或恒真的条件，如果遇到update/delete或遭到sql注入就恐怖了

事务使用规范

使用场景、原则等。

行为规范

  • 杜绝危险SQL
  • 大批量更新，如修复数据，避开高峰期，并通知DBA。直接执行sql的由运维或DBA同事操作
  • 及时处理已下线业务的SQL
  • 复杂sql如多表join,count,group by，主动上报DBA评估。

引用

http://seanlook.com/2016/05/11/mysql-dev-principle-ec/
http://www.cnblogs.com/chenpingzhao/p/5059985.html

来源：http://leunggeorge.github.io/

0%