基本规范 必须使用InnoDB引擎 P0

支持事务、行级锁、并发性能更好，可利用缓存提高内存利用率，避免磁盘IO开销。

基本规范

禁止在数据库中做运算，禁止使用存储过程、函数、触发器、视图、event。

P0

如order by rand（），md5（）；在数据库中做复杂运算count（），sum（）等。海量、高并发场景，数据库资源宝贵，不易扩展，高cpu的业务逻辑可以上移到服务层。因为应用服务层更容易扩展，数据库做擅长的存储和索引。

基本规范

拒绝3B，Big Transaction，Big SQL，Big Batch。

P0

3B会造成锁定时间长，影响并发，同时导致备库和只读库延迟。应该尽量将大事务拆分成多个小事务；

将复杂SQL进行拆分分解，在应用中合并，达到最终结果一致。

负杂SQL可以拆分成多个小SQL，或在应用中实现，一个SQL可以使用一个CPU。

大的批处理操作，拆分成多个小批处理操作，每个批处理之间sleep（）。

部分UPDATE、SELECT语句 写得很复杂（如嵌套多级子查询）——可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作。

基本规范 InnoDB表尽量避免使用COUNT(*)操作 P1

因为内部没有计数器，需要一行一行累加计算，计数统计实时要求较强的，可以使用Redis或MySQL的表做计数器，Redis可能导致计数不精确，InnoDB引擎MySQL表可以保证计数精确。

基本规范 数据库编码使用utf8或utf8mb4，排序规则选择utf8mb4_general_ci。 P0

不会产生乱码风险，但要注意，不要部分表、部分列是utf8，部分列是utf8mb4，会导致隐式转换，索引失效。

基本规范

所有表和字段都需要添加中文注释。

P0

利己利人

基本规范

避免使用大字段，一定要使用，拆分到单独的表，用主键关联，提高缓存命中率。

P1

大字段可以存放在oss里，数据库只存url。大字段影响性能，如修改频繁，会产生巨量的binlog。

基本规范 避免使用外键，外键用来保护参照完整性，可在业务端实现。 P0

外键会导致父表和子表之间耦合，十分影响SQL性能，出现过多的锁等待，甚至会造成死锁。

基本规范

对事务一致性要求不高的业务，如日志表等，优先选择存入MongoDB或Hbase，统计类需求在单独只读库或分布式列存数据库ADB中进行。大表必须有历史表，应用要具备自动归档大表数据到历史表的能力。

P1 目的是控制表大小，性能考虑。

基本规范

控制单表数据量，1000万行或10Gb大小。

P1

目的是控制DDL时间和优化性能。

基本规范

控制单表的字段数量，50个以内。

P1

避免一个数据库块存不下，造成行链接和行迁移，影响性能，每行数据不超过8K。

基本规范

避免大量地使用短连接。

P0

基本规范 代码或框架必须支持自动重连机制。 P0

基本规范 每个月检查一次慢SQL，并优化，避免慢sql出现。 P1 暂定超过1S为慢SQL。

行为规范

避免未审核的SQL上线。

P0 规避上线风险

行为规范

禁止在主库上执行后台管理和统计类的功能查询。

P0

这种复杂类的SQL会造成CPU的升高，进而会影响业务，这类SQL在单独只读库查询。

行为规范

大批量操作数据，如归档数据、迁移数据、批量删除数据、大量加载数据应通过项目负责人和DBA审核，在空闲时段进行，避免性能问题。

P0

从主库拉取数据，也可以采取从binlog订阅的方式，不影响主库性能。

行为规范

禁止在业务高峰期对大表DDL操作。

P1

尽管MySQL5.6以上版本支持onlineDDL，但还是会有些操作会阻塞并发DML，如创建全文索引、修改列的数据类型、删除主键、修改字符集。可以利用PT-OSC或GH-OST工具做在线DDL或DMS系统的“结构设计”。

库表设计规范

适当反范式设计表，禁止3张表以上JOIN.

P0

目的是避免过多的join，join是很耗资源的操作,需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。即使双表join也要注意表索引、SQL性能。

库表设计规范

表必须有主键，最好用int类型，不用varchar类型。

P0

无符号自增主键保证insert是顺序写，UUID是随机写，对于SAS传统机械式硬盘写入性能更好，根据主键做关联查询的性能也会更好，二级索引更小，并且还方便了数据仓库抽取数据，无主键导致主备延迟。VARCHAR等字符型列作为主键会对数据量、BP命中率、查询效率、插入效率等方面对数据库产生负面影响。

库表设计规范 新增表必须有ID,CREATE_TIME,UPDATE_TIME 3个字段。 P0

示例：

CREATE TABLE `category` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '分类ID',

`pid` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '父ID',

`name` varchar(50) NOT NULL DEFAULT '' COMMENT '类目名称',

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改日期',

PRIMARY KEY (`id`),

KEY `idx_pid_name` (`pid`,`name`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='类别表'

库表设计规范

所有字段定义为NOT NULL，并且要提供默认值。

P0 示例同上。

从应用层角度，可以减少程序判断代码。从数据库角度， NULL值很难进行查询优化，它会使索引统计更加复杂，还需要MySQL内部进行特殊处理 。

库表设计规范 慎用分区表 P1

分区表的好处是对于开发来说，不用修改代码，通过后端DB的设置，比如对于时间字段做拆分，就可以轻松实现表的拆分。但涉及一个问题，查询的字段必须是分区键，否则会遍历所有的分区表，并不会带来性能上的提升。此外，分区表在物理结构上仍旧是一张表，此时我们更改表结构，一样不会带来性能上的提升。所以应采用切表的形式做拆分。

库表设计规范

用DECIMAL代替FLOAT和DOUBLE，存储精确浮点数。

P0

对货币等对精度敏感的数据，应该用定点数表示或存储。FLOAT和DOUBLE有四舍五入的问题。

库表设计规范 禁止使用ENUM类型，用TINYINT来代替ENUM类型 P0

enum枚举类型，会存在扩展的问题，增加值，相当于做表结构DDL。

库表设计规范

时间类型选择datatime类型，timestamp类型有2038年限制。

P0

存储方式：

TIMESTAMP：把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时将其又转化为客户端当前时区进行返回。

DATETIME：不做任何改变，基本上是原样输入和输出。

库表设计规范 字段长度尽量按实际需要进行分配，不要随意分配一个很大的容量。 P1

能用占用空间小的类型，就不用占用空间大的类型。如主键能用无符号整型（4字节），就不用UUID（16字节）。为什么？省空间、省内存、占用更小的带宽，join更快，二级索引更小。能用tinyint（1字节），就不用int（4字节），如status，只用0-5几种状态。

库表设计规范

对于写热点表，TPS大于1000，要进行分表处理。

P1

库表设计规范

多表中的相同列，必须保证定义一致，尤其是数据类型和字符集、校验规则。

P0

SQL规范

不要轻易使用子查询in，要改为join。

P1

因为使用in，驱动表是外层的表。如果一定要使用in，要保证外层表（驱动表）的数据量要小，保证嵌套循环的次数少，io少。

SQL规范

禁止使用select *，替换为具体的列。

P0

不需要的列占用额外资源开销，不利于使用覆盖索引，修改表结构对应用有影响。当有大字段时，如用到filesort，导致filesort使用双路排序算法，从而增加IO和SQL执行时间。

SQL规范

sql语句中的or连接多个不同的字段的情况，不能利用索引，建议改为union或union all.

P1

merge index比较弱，MySQL5.6以前版本union会产生临时表合并结果，5.7不产生临时表，减少了io。

SQL规范

查询行数，用count（*），而不是用count（1）或count（列名）。

P0 后两者会统计不准确

SQL规范 分页查询建议使用延迟关联的优化方法。 P0

普通写法：

select * from t where sellerid=100 limit 73000000，5000

普通limit M，N的翻页写法，往往在越往后翻页的过程中速度越慢，原因mysql会读取表中的前M+N条数据，M越大，性能就越差：

优化写法：

select t1.* from t t1,

(select id from t sellerid=100 order by id ascwhere id >7300000 limit 5000) t2

where t1.id=t2.id;

优化后的翻页写法，先查询翻页中需要的N条数据的主键id，在根据主键id回表查询所需要的N条数据，此过程中查询N条数据的主键ID在索引中完成两者写法区别在于回表次数不同，优化写法回表5000次，普通写法回表73005000次！

注意：需要在t表的sellerid字段上创建索引

create index ind_sellerid on t(sellerid);

SQL规范

join时注意用小表做驱动表，大表关联列创建索引。

P1 查看执行计划验证

索引规范

避免where条件中字段使用计算函数和隐士转换，会导致索引失效。

P0

select id,s_ip,s_port,d_ip,d_port,db_type,ipfrom aurora_switch_log

where custins_id= 4665613 and switch_from= 1 and switch_type= 11 and TO_DAYS(gmt_created) > TO_DAYS(NOW())-30 and swtich_status= 1 order by gmt_createddesclimit 1；

select *from tab where id=110；（id是varchar类型）

优化：该SQL走索引 gmtc(`gmt_created`),但是由于TO_DAYS(gmt_created) > TO_DAYS(NOW())-30会导致

表中的索引无法使用，只能走全索引扫描，改为gmt_created>date_sub(now(),INTERVAL 30 day)

索引规范

变更比较频繁的表，索引数量建议不超过5个。

P1

InnoDB的secondary index使用b+tree来存储，因此在UPDATE、DELETE、INSERT的时候需要对b+tree进行调整，过多的索引会减慢更新的速度。

索引规范 禁止使用%前缀模糊查询，例如LIKE “%内容%”，会导致索引失效。 P0 此类需求可以用全文索引或迁移到ES中。

索引规范

索引字段的顺序：区分度高的、等值条件的字段放在前面，将range(范围)限制条件字段调整到where子句的最后(最右)部分。

P0

selectaccount_idfrom consume where expenditure>1.00 and account_payee=72478814;

如上语句，expenditure>1.00是一个范围限制条件，可以调整其至account_payee字段之后。同时设计索引index_accountpayee_expenditure时考虑到这个问题，索引中列顺序为(account_payee,expenditure)。

索引规范

需要考虑添加索引的字段

1）sql语句中频繁使用的字段，等值或范围查询，区分度高的字段。

2）表连接的列（特别是被驱动表的连接字段）

3）group by和order by的字段

P1

索引规范

不用低基数的列创建单列索引，可以创建包含低基数列的组合索引。

P1

如性别列，只有男、女两个值，但可以创建组合索引如（用户id,性别）

索引规范 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary P1

EXPLAIN语句可以获得MySQL如何执行SELECT语句的信息。通过对SELECT语句执行EXPLAIN，可以知晓MySQL执行该SELECT语句时是否使用了索引、全表扫描、临时表、排序等信息。尽量避免MySQL进行全表扫描、使用临时表、排序等。

索引规范

UPDATE、DELETE语句也需要根据WHERE条件添加索引，避免全表扫描。

P0

索引规范

对长度过长的VARCHAR字段建立索引时，使用前缀索引。

P1 如字段order_description（1000） 索引：index idx_pid2(`parent_id`,`order_description`(20))

索引规范 合理创建复合索引（避免冗余），(a,b,c)相当于(a) 、(a,b)、(a,b,c) P1

但不等于（b），（b，c），（c）

索引规范

当where子句中有2个以上的范围限制条件时，只保留一个范围限制条件，并且必须放在最后，其它范围限制条件需要改写为等值条件或in（）。

P1

由于第一个范围限制条件后的范围条件对应的索引列无法在索引中被使用，因此将语句：

select account_idfrom consume where account_payeebetween 51906734 and 51907000

and expenditure>0.00;

改写为：

select account_idfrom consume where account_payeein (51907000, 51906734, 51906740)

and expenditure>0.00;

就可以使用索引index_accountpayee_expenditure中包含的全部列信息

(index index_accountpayee_expenditure(account_payee,expenditure))