数据库规范

前言：这绝对是宝典，互联网大厂必备，别问为什么，请珍藏！

一、运维准则

• （1）数据修改：研发、运营人员不能直接修改现网数据库，只能通过管理系统等业务系统进行修改；
• （2）脚本部署：版本上线包含数据库脚本，必须运维人员与开发人员进行部署，运营任玉环不能进行部署；
• （3）版本审核：所有上线版本包含数据库脚本，必须研发先内部进行邮件审批，然后发给运维人员进行审批；
• （4）版本测试：发布包必须经过测试人员测试，并经过压力测试，由测试人员提供，不接受开发人员发布包；
• （5）现网环境：运营人员不能接触现网环境，研发与运维需要查询数据库，需要使用只读查询账号，防止误操作；
• （6）慢查询语句：新出现的必须3天内整改，历史慢查询必须7天内完成整改；
• （7）故障处理：现网故障研发必须及时进行支撑；
• （8）操作前需要备份：现网操作前，必须先进行数据备份，使所有操作能回滚；
• （9）配置文件加密：应用代码连接数据库必须加密，使用阿里中间件druid方案；

• （10）DDL和大批量DML执行窗口：白天需要进行预发布的版本，涉及到数据库表DDL和大批量DML操作，必须提前一个晚上执行，防止白天进行变更阻塞正常业务运行，对于高并发的业务或者大表操作，必须在晚上22:30后进行，防止产生大量的表锁，导致数据库崩溃；

二、高效SQL编写规范

2.1、避免使用多表关联：

尽量使用单一表实现业务逻辑，避免使用多表关联，一个sql不能超过2个表进行关联

2.2、对join语句：

确保on或using使用的字段上存在索引；

 尽量使group by和order by语句只参照从一个表中取出的字段，以便使语句可使用索引；

2.3、使用join替代子查询（IN）：

下面是现网的具体例子，由原来35秒优化为毫秒级别，提高万倍以上。   

select id,
       。。。
  from AA
 where id IN (SELECT MAX(id)
                FROM t_second_call400
               WHERE agent_id = 264816
               GROUP BY house_id)
 order by id desc limit 40, 20 G

更改如下：

select c.id, 
。。。
from BBc 
inner join (SELECT MAX(id) as id 
FROM t_second_call400 
WHERE agent_id = 264816 
GROUP BY house_id) as t 
on c.id=t.id 
order by c.id desc

2.4、不要使用*进行查询；

2.5、不要遗漏表之间的连接条件:

遗漏了连接条件会导致数据库使用笛卡尔集的方式进行表连接，如果两张表的数据量都非常大，那么一个查询需要消耗大量的系统资源，同时执行时间相当长，例如（连接条件a.dept_no=b.dept_no）：

正确：
       select a.name,b.sal
from t_emp a,t_dept b
where a.dept_no=b.dept_no  and b.location=’广州’
错误：
       select a.name,b.dept_no
from t_emp a,t_dept b
where b.location=’广州’

 

2.6、根据应用特点创建表索引，减少全表扫描：

一般来说，90%以上的性能问题是由于索引导致的。但也要注意索引的数量，若是存在大量dml操作的表，索引过多，会增加对表的维护开销，影响数据库响应速度，降低业务体验；

2.7、运营，400，统计等支撑系统进行分库：

运营，400，统计等支撑系统不要使用生产主库，而是使用从库或者其他分离的数据库；

2.8、适当使用COMMIT：

当前我们使用的是innodb存储引擎，对于非自动提交事务的session和批量修改，适当使用commit可提高程序的性能、

求也会因为COMMIT所释放的资源而减少，COMMIT释放的资源：

回滚段上用于恢复数据的信息、
被程序语句获得的锁、
redo log buffer 中的空间、
为管理上述3种资源中的内部花费；

2.9、用TRUNCATE替代DELETE：

原因：

（1）当删除表全部的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息、

（2）如果你没有COMMIT事务,mysql会将数据恢复到删除之前的状态，而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复，因此很少的资源被调用,执行时间也会很短，同时，truncate操作可释放数据原先占用的空间，但delete不会释放。

2.10、对count()语句：

（1）对于某些复杂的count，可考虑转化为sum函数，例如：要通过一条简单的语句分别查看字段color为“blue”和“red”的数量，可考虑使用下面的sql：

SELECT SUM(IF(color = 'blue', 1, 0)) AS blue,
SUM(IF(color = 'red', 1, 0))  AS red
FROM items;

（2）考虑在select count(col) ... where col=...的col字段上添加索引，以便通过扫描col即可获得结果。

2.11、对于group by和distinct:

（1）尽量对仅存在索引的字段进行group by或者distinct;

（2）当group by 不能使用index 时mysql有两种处理方法：临时表和filesort。根据不同的情况，必定有一种方法优于另一种，对此可以使用sql_big_result和sql_small_result强制优化器使某种方法；

（3）在group by 语句中mysql会自动order如果，不需要可使用order by null来禁止自动的order。

2.12、对limit和offset：

（1）mysql对limit,offset子句的执行，如limit 10000,20，会先取出10020条记录，再丢弃前10000条记录。对此，可尽量考虑使用索引，或进行转化。实例如下：

低效：
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
高效：
SELECT film.film_id, film.description
FROM sakila.film
  INNER JOIN (
    SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5
  ) AS lim USING(film_id);

后者之所以高效，主要是由于它通过扫描索引而不必扫描所有的rows，当找到满足条件的记录后，再用join将其与全表的数据连接获得所需其他非索引的字段。

（2）此外，可考虑将limit,offset转化为索引的区间扫描，如通过between and转化，实例如下：

SELECT film_id, description
FROM sakila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position;

其中，position为索引键

2.13、合并查询结果使用UNION ALL替代UNION：

当SQL 语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并, 然后在输出最终结果前进行排序. 尽可能用UNION ALL替代UNION, 这样排序就不是必要了， 效率就会因此得到提高。

2.14、尽量用IN代替OR（IN也是不推荐常用的）：

在查询中的IN()列表比较：不同于其他数据库（IN()子句是OR的同义词）mysql将会对IN()中列表的值进行排序，使用较快的binary search进行比较，它要比OR速度快（o(log(n))和o(n)，n是list中的值的数量）；

2.15、尽量减少不必要的二次查询：

表中同一笔记录中获取记录的字段值，须使用同一 SQL语句得到，不允许分多条 SQL语句,减少查询次数。例如：

高效：
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER) = (
SELECT TAB_NAME, DB_VER
FROM TAB_COLUMNS WHERE VERSION = 604)

低效：
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = (
SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604)
AND DB_VER = (
SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

2.16、避免查询语句索引失效：

（1）避免对索引字段计算；

（2）避免对索引字段数据类型转换：

潜在的数据转换，查询条件中是指由于等式两端的数据类型不一致。例如索引字段使用的是数字类型，而条件等式的另一端数据类型是字符类型，数据库将会对其中一端进行数据类型转换，数据类型的转换会让索引的作用失效，令数据库选择其他的较为低效率的访问路径。

（3）避免对索引字段使用各种函数：

使用索引列作为条件进行查询时，如非已经建立了基于特定函数索引，应该避免对索引列条件进行任何的函数操作，诸如to_char(),to_date()等函数将会破坏索引使用法则，查询语句无法使用索引提高整个查询的效率。

（4）避免对索引字段使用like ‘%XX’操作：

用此类条件进行查询，将无法使用索引提高整个查询效率。

（5）避免对索引字段选取较大范围查询；

（6）使用复合索引前导列:

使用复合索引尽量选择索引的前导列。

（7）排序的索引问题:

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的;

因此数据库默认排序可以符合要求的情况下不要使用排序操作；

尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

（8）避免对索引字段不等于符号：
要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

（9）免对索引字段进行是否NULL值判断：

免使用索引列值是否可为空的索引，如果索引列值可以是空值，在SQL语句中那些要返回NULL值的操作，将不会用到索引。

（10）相同的索引列不能互相比较，这将会启用全表扫描：

如tab1上存在索引idx_col1_col2(col1,col2)，其中col1和col2都是int型。则查询语句SELECT * FROM tab1 WHERE col1>col2;是不会使用索引的。

2.17、用 >= 替代 >：

DEPT>3和DEPT >=4两者的区别在于， 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录；

2.18、不要使用SQL取数据库时间：

案例：
SELECT NOW();
SELECT CURRENT_TIME;
SELECT CURRENT_DATE;
SELECT CURRENT_TIMESTAMP;等

问题分析

从单条SQL来看，此SQL消耗的CPU资源有限，但如果调用的频率非常高，就会引起非常明显的CPU消耗。

优化建议

减少使用SELECT CURRENT_TIMESTAMP;之类的语句直接从数据库中取当前时间，可以改为从业务本地操作系统取得当前时间。

2.19、必须充分利用分区表的特性，以优化SQL效率：

问题案例：

select 'SERVQUAN_MID7_1' midtype,20081231 timekey,200812 month,
 a.v1field,sum(quantity)  quantity   from  
 haha.Sr_mid6_Rptrela  a,haha.stat_operator_workload   b 
 where  a.v1field=b.ownerorgid  and    substr(b.cycle,0,6) = 200812 
 and    b.region = 200 
 group  by 'SERVQUAN_MID7_1',20081231,200812,a.v1field

问题分析

检查分区表CS_CWL_WORKLOAD，cycle 为partition key。TO_NUMBER(SUBSTR(TO_CHAR("B"."CYCLE"),0,6))=200812) 这样写SQL条件不能有效利用partition特性。

优化建议

应该将TO_NUMBER(SUBSTR(TO_CHAR("B"."CYCLE"),0,6))=200812)写为cycle between 20081201 and 20081231。

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

MySQL 自4.1版以后开始支持INSERT … ON DUPLICATE KEY UPDATE语法，使得原本需要执行3条SQL语句（SELECT,INSERT,UPDATE），缩减为1条语句即可完成。

例如ipstats表结构如下：
CREATE TABLE ipstats (
ip VARCHAR(15) NOT NULL UNIQUE,
clicks SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0'
);

原本需要执行3条SQL语句，如下：

IF (SELECT * FROM ipstats WHERE ip='192.168.0.1') {
UPDATE ipstats SET clicks=clicks+1 WHERE ip='192.168.0.1';
} else {
    INSERT INTO ipstats (ip, clicks) VALUES ('192.168.0.1', 1);
}

而现在只需下面1条SQL语句即可完成：
INSERT INTO ipstats VALUES('192.168.0.1', 1) ON DUPLICATE KEY UPDATE clicks=clicks+1;
注意，要使用这条语句，前提条件是这个表必须有一个唯一索引或主键。

 

2.21、共享SQL代码

由于mysql的query cache中存放了相同查询SQL的语句和结果，在第一次查询后，如果相应的记录没有发生update，则下次相同的查询语句就可直接在query cache中查找，不必物理读取磁盘数据文件。mysql从query cache直接获得查询结果的重要条件之一是SQL语句必须字符完全绝对匹配。

例如以下两个SQL字符不匹配，在数据库里面不是共享的：

• SELECT EMPNO FROM EMP WHERE DEPNO=1;
• SELECT empno  FROM EMP WHERE DEPNO=1;

三、InnoDB加锁机制对开发的影响

3.1、innodb行锁的实现方式

对于innodb的表而言，insert、update、delete等操作虽然都是加行级锁，但这些行锁都是通过给索引上的索引项加锁来实现的，这就意味着：只有通过索引条件检索数据，innodb才能使用行级锁，否则，innodb将使用表级锁。

（1） 在不通过索引条件检索的时候，innodb使用的是表锁，不是行锁：

例如：
create table tab_no_index(id int,name varchar(10)) engion=innodb;
create table tab_with_index(id int,name varchar(10),key id_idx(id)) engion=innodb;
语句select * from tab_no_index where id=1 for update;会加表锁，而select * from tab_with_index where id=1 for update;会加行锁。

（2）虽然访问不同行的记录，但是如果是使用相同的索引键，仍然会出现锁冲突：

例如，上例表tab_with_index中有两条记录为(1,’1’)和(1,’4’)，则select * from tab_with_index where id=1 and name=’1’for update;会对这两条记录都加锁。

（3）当表有多个索引时，不同的事务可以使用不同的索引锁定不同的行。此外，不论是使用主键索引、唯一索引或普通索引，innodb都会使用行锁对数据加锁。

3.2、Innodb的间隙锁（Next-key锁）

（1）当使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，innodb会给符合条件的已有数据记录的索引项加锁;

（2）对于键值在条件范围内但并不存在的记录，叫做“间隙”，innodb也会对这个“间隙”加锁

例如：emp表中只有101条记录，其中empid为索引键，值分别为1,2,……,100,101，语句select * from emp where empid>100 for update;不仅会对101记录加锁，还会对empid大于101（这些记录不存在）的“间隙”加锁。

四、数据库表及索引建立使用规范

4.1、表建立考虑的因素

（1）不同存储引擎的选择

• MyISAM：它虽然不支持事务、也不支持外键，但其优势是访问速度快；
• InnoDB：提供具有提交、回滚和崩溃恢复能力的事务安全。支持外键约束。采用的是行级锁，所以对于有大量insert、update操作的表能提供更好的并发性。相对于myisam，InnoDB更适用于大数据量和事务；
• MEMORY：该引擎的数据是存放在内存中的，默认使用是hash索引，所以访问速度快，一旦服务关闭，表中数据会丢失；
• MERGE：该引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身没有数据，对MERGE类型的表可进行查询、更新、删除操作，这些操作是对内部的实际的MyISAM表进行的。

（2）建立分区表

MySQL的分区表基本类型可分为：范围分区(range)，哈希分区(hash)，列值分区(list)、键值(key)分区和子分区五类。

注意：在互联网行业中，请不要使用分区表，而是使用分库分表的模式

l  建立范围分区表

范围分区是应用范围比较广的表分区方式，它是以表中单个列的值的范围来作为分区的划分条件。不同的记录将按照分区列的值的不同，存放到列值所在的范围分区中。比较常用如按照时间字段划分分区，2008年1月的数据放到200801分区，2008年2月的数据放到200802分区以此类推。
范围分区方式适用于应用中频繁对分区键值进行范围查询的场合。

l  建立Hash分区表

Hash分区将表平均划分为2的整数幂个分区，表中的数据，按照分区列的值进行特定的hash计算后，由hash运算的结果确定该索引数据存放于那一个表分区中。
此种分区方式最适用于查询条件中，对分区字段进行单值查询的情况(如，ColA=1)。但是hash分区，并不适用于对索引字段使用范围查询，如对字段使用大于>，小于<，范围between等操作的查询语句中。

l  建立列值分区索引

列值分区与范围分区有类似之处，该分区与范围分区类似的是需要指定列的值，但是其分区值必须明确指定。它的分区列只能有一个，单个分区对应值可以是多个。
列值分区使用范围较上两种分区方式较小。

l  建立key分区

按照KEY进行分区类似于按照HASH分区，除了HASH分区使用的用户定义的表达式，而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇（Cluster）使用函数MD5()来实现KEY分区；对于使用其他存储引擎的表，服务器使用其自己内部的 哈希函数，这些函数是基于与PASSWORD()一样的运算法则。

l   子分区

索引按照某列分区之后，仍然较大，或者是一些性能查询上需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式
组合分区适用于表特别庞大，或者有特别性能需要的场合。
但在mysql中，分区表存在一定的局限性
①所有的唯一索引（包括primary key）都需要保护分区关键字。文档中是这样说的：
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
②对存储引擎的限制：MERGE引擎不支持分区，分区表也不支持merge；目前， FEDERATED引擎不支持分区；CSV引擎不支持分区；BLACKHOLE引擎不支持分区；在NDBCLUSTER引擎上使用分区表，分区类型只能是KEY(or LINEAR KEY) 分区；在数据库进行升级的情况下，使用key方法进行分区的表，不管使用何种存储引擎，都需要把这个表dump后再reload；分区表的所有分区或者子分区的存储引擎必须相同，这个限制也许会在以后的版本取消。
③对于分区关键字，可以是用加减乘除等运算符和部分函数计算的表达式，但结果必须是int或是null。 |, &, ^, <<, >>, , ~ 等不允许出现在分区表达式。
④官方建议在创建分区表后，永远别改变mysql的sql_mode。因为在不同的模式下，某些函数或者运算返回的结果可能会不一样。可能会导致corruption或是数据丢失。
⑤对于分区操作过程中，会给表添加写锁。从该表进行的读操作将不会受到影响，当分区操作执行完后，挂起的insert和update操作才会被执行。
⑥分区表是不支持外键的：既不能包含其他表需要引用的外键，也不能也引用其他表作为外键。
⑦不支持FULLTEXT indexes（全文索引），包括MYISAM引擎。不支持spatial column types。临时表不能被分区。分区表不支持INSERT DELAYED。分区表的分区键创建索引，那么这个索引也将被分区。在mysql中分区表是没有全局索引一说。

4.2、表使用建议

（1）选择合适的存储引擎

• ①    如果对事务的完整性有较高要求，在并发条件下要求数据的一致性，数据操作除了插入和查询外，还有很多更新删除操作，则InnoDB更适合，可有效降低锁定，提高并发性。建议除了mysql系统数据库外，业务库都使用Innodb引擎。
• ②    如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并对事务的完整性、并发性要求不高，则MyISAM是非常适合的。

（2）历史、日志表建立时间分表

目前生产系统中的历史表和日志表，一般数据量都非常庞大。大量的历史数据积压到当前生产用户中，一方面会降低应用程序的效率，另一方面亦浪费大量的存储空间。因此请考虑对历史表和日志表采用分表的模式建立，并考虑是否需要建立一定的历史数据清理策略，以基本保持当前生产用户下的总数据量。

基于这个原则，建议在建立历史表和日志表做如下考虑：

• l  按时间键值进行时间范围分表，时间范围可根据数据增长的速度按月或按季度等进行
• l  表的命名采用以下的规范：<表名>_YYYYMMDD，其中YYYY为分表数据的年份，MM为分表数据的月份或季度，DD代表表的日期。

（3） 大表进行分表

分表表空间设计原则

l  表的大小：

• 对于大表进行分表，将有益于大表操作的性能和大表的数据维护。通常当表的大小超过1.5GB－2GB，或对于OLTP系统，表的记录超过1000万，都应考虑对表进行分表。

l  数据访问特性：

• 基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分表，可充分利用分表排除无关数据查询的特性。

l  数据维护：

• 某些表的数据维护，经常按时间段删除成批的数据，例如按月删除历史数据。针对这种删除（Delete）大量数据的操作，对系统开销很大，有时甚至是不可接受的。对于这样的表需要考虑进行分表，以满足维护的需要。代之以分表的drop功能。其效果是清理效率将显著提高，而且不产生大量日志文件。

4.3、 索引建立规范

（1）据表数据量评估索引

数据量达到GB级别、记录数达到百万级别、访问频繁的表，需要建立合适的索引；

相反，在数据量较少且访问频率不高的情况下，如只有一百行记录以下的表不需要建立索引。因为在数据量少的情况下，使用全表扫描效果比走索引更好。

（2）选择适当的索引字段

选择建立索引的字段，应该遵循以下的原则：

l  高选择性：

• 选择性是指通过索引字段查询返回结果集占表总数据量的百分比，结果集占表总数据量的百分比越小选择性越高，反之越低。选择性越高，通过索引查询返回的结果集越少，索引更为高效。

l  空值少：

• 避免在空值(Null)很多的字段上建立索引，大量空值会降低索引效率。

l  数据分布均匀：

• 索引字段中，个别数据值占总数据量的百分率明显比其它数据值占总数据量的百分率高，表明该字段数据值分布不均，容易引起数据库选择错误索引，生成错误的查询执行计划。应该避免在数据值分布不均的字段上建立索引。

（3）组合索引字段

建立合适的组合索引可以提高查询效率，选择正确的组合索引字段，应该遵循以下的原则：

l  合适组合索引字段顺序

• 选择正确的组合索引字段顺序，最常用的查询字段和选择性较高的字段，应该作为索引的前导字段使用。

l  合适的字段数

• 组合索引的字段数不适宜较多，较多的组合索引字段数会降低索引查询效率，组合索引字段数应不多于3个，如业务特点需要建立多字段的组合主键例外。

（4）适当的索引个数

建立索引会有效提高数据访问速度，但也会降低增、删、修改等DML语句的执行速度。

在数据增删比较频繁的表中，索引数量不应超过5个。

（5） 建立唯一索引

在建立索引的字段所有数值都具有唯一性特点的情况下，建立唯一索引(unique index)代替普通索引，唯一索引(unique index)查询效率比普通索引查询效率更高，可以大幅提升查询速度

（6）使用频率低字段不适合建立索引

（7）不建立功能相同的索引

和多数数据库一样，mysql对索引也采取左前缀原则，所以在同一个表中，避免建立两个或以上功能相同索引。例如已经建立字段A、B两个字段的索引，应该避免再建立字段A的单独索引。两个索引之间，对相同的查询都会起到相同的作用。建立两个功能相同的索引，反而会容易引起数据库产生错误的查询计划，降低查询效率。

（8） 使用有效索引

分析应用访问表的所有索引，估算比较选择哪些索引更高效。在环境比较复杂的应用中，存在多个索引可能导致应用执行低效索引时，可通过优化提示器的方式(hints)指定使用特定索引，强制应用选择高效的访问路径。

 

五、命名规范

5.1、数据库用户命名

• 对于程序账号：应用名称+@+数据库名
• 对于维护账号：维护人员姓名拼音全称+@+数据库名
• 容易理解，看到数据库名称，基本上理解数据库属于那个应用。
• 库名必须加上公司的名称,后面是应用的名称，中间以下划线分隔，例如fdd用户库:fdd_user。
• 容易理解，能够表达表的功能。
• 表名不能取得太长（一般不超过三个英文单词）。
• 在命名表时，用单数形式表示名称。例如，使用 user，而不是 users.
• 如果在一个库中存在多个应用，表名尽量加上应用的名称，并且应该加表示模块标识，格式如:databaseName_modelName_tableName比如用户属性表表：user_oper_profile(属于业务模块),用户城市表：user_base_city(属于基础模块),用户统计:user_state(属于统计)等等。关于模块命名，视具体应用进行归类，但必须前后一致，比如业务模块的如果统一使用oper，那么不能用o,或者operation。基础模块的统一base,那么不能使用b
• 采用有意义的列名。列的名称必须是易于理解，能表达列功能。
• 列名不能取得太长，（一般不超过三个英文单词）。
• 不要在列的名称中包含数据类型。比如用户名称,不用这样写.int_user_name,应用是user_name.
• 主键：表中的主键命名同列命名相同(仅用于标示唯一性的列)。(后缀名称建议家上pk,如:user_pk)

5.2、索引命名

建立索引的名称的原则是简洁和易于理解，索引命名采用如下的规范：

• 普通索引：IDX_<简要表名>_<索引首字段名称>

例如：在CS_REC_RECPTION表的INT_SERVERVER 字段上建立普通索引命名如下IDX_RECEPTION_SERVNUMBER

• 唯一索引：UK_<简要表名>_<索引首字段名称>

例如：在CS_REC_RECPTION表的INT_SERVERVER 字段上建立唯一索引命名如下UK_RECEPTION_SERVNUMBER

• 主键：PK_<表名>

例如：在CS_REC_RECPTION表的INT_SERVERVER 字段上建立主键索引命名如下PK_CS_REC_RECEPTION

5.3、 其他命名规范

• 视图的命名请遵循以下命名规范：v _ + 系统模块+_ + 表名 + _+功能+_动作。比如用户分页查询的视图：v_user_select_by_page
• 存储过程的命名请遵循以下命名规范：up _ + 系统模块+_ + 表名 + _+功能+_动作。比如增加好友: up_user_friend_insert；
• 函数的命名请遵循以下命名规范：f_+系统模块+_+函数标识。比如在用户统计函数为:f_user_stat

 

六、MySQL 数据库my.cnf 参数文件使用规范

6.1、 部分参数赋值规范

server-id：
采用IP地址小数点最后一个地址段+端口号后两位的方式。如192.168.2.214上3306端口数据库，server-id值为21406
datadir:
目前系统组单独划分了一个/data0 分区做为数据存放目录，所以mysql 目录只能放在/home目录下。参数值可以采用mysql+端口号的方式。如：3306端口的数据库可以存放在如下目录下，datadir=/data0/mysql/dbdata
pid-file:
采用mysql+端口号的方式。
如：3306端口的数据库pid-file，pid-file=/data0/mysql/dbdata/mysql.pid
socket：
采用mysql+端口号的方式。
如：3306端口的数据库socket，socket=/data0/mysql/dbdata/mysql.sock
binlog-ignore-db:
取值如下
binlog-ignore-db=mysql              
binlog-ignore-db=test               
binlog-ignore-db=information_schema 
不使用binlog-do-db参数
relay-log:
采用mysql+端口号+ ‘-relay-bin’的方式。
如：3306端口的数据库，relay-log=mysql3306-relay-bin

 

七、数据库规范总结

一.基础规范

(1) 使用INNODB存储引擎
(2) 库、表、列字符集使用utf8mb4,utf8mb4兼容utf8且可以存储表情字符。
(3) 建议所有表、所有列都需要添加注释
(4) 不在数据库中存储图，文件等大数据，可以将大对象放到磁盘上，数据库中存储它的路径
(5) 禁止在线上做数据库压力测试 (可在预发布环境)
(6) 禁止线下开发环境直连线上数据库主库

二.命名规范

(1) 库名.表名.字段名必须使用小写字母或数字，禁止出现数字开头，禁止连个下划线中间只出现数字，使用下划线“_”分割，分表使用后缀为 “_xx"，例如"order_01,order_99"
(2) 库名.表名.字段名禁止超过32个字符。须见名知意
(3) 库名.表名.字段名禁止使用MySQL保留字
(4) 临时库.表名必须以tmp为前缀，并以日期为后缀
(5) 备份库.表必须以bak为前缀，并以日期为后缀
三.库.表.字段开发设计规范 
(1) 按日期时间分表需符合YYYY[MM][DD][HH]格式
(2) 对日志型表选择分区表策略
(3) 建议不使用TEXT.BLOB类型
(4) 建议所有字段均定义为NOT NULL,默认值为空的写法为NOT NULL DEFAULT '' 而不是 NOT NULL DEFAULT NULL，禁止使用NULL字段 NULL字段很难查询优化，NULL字段的索引需要额外空间，NULL字段的复合索引无效，详情请查看：https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html 
(5) 使用UNSIGNED存储非负整数（存储的范围更大了） 
(6) 关于datetime、timestamp类型的异同点。
相同点：
     两者的最小精度都为小数点后6位
不同点：
     1.存储范围的问题：timestamp类型的存储范围较小，为'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC；datetime类型的存储范围较大，为'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
     2.时区的问题：timestamp会随server端时区的变化而变化，datetime不会
(7) 使用INT UNSIGNED存储IPV4,通过MySQL函数inet_ntoa和inet_aton来进行转化。Ipv6地址目前没有转化函数，需要使用DECIMAL或者两个bigINT来存储。
(8) 建议使用VARCHAR存储大小写敏感的变长字符串
(9) 禁止在数据库中存储明文密码，把密码加密后存储
(10) 不允许使用ENUM （插入非法值的时候，默认会插入一个空值）
(11) 所有的表(特殊情况除外,如:日志表)必须要有创建时间、更新时间两列，前者表示主动创建，后者表示被动更新【强制】
create_time datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
update_time datetime(0) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
(12)可以适当的添加冗余列,减少表关联，提高查询效率,通过注释表明哪张是主表
(13) 多表关联导致查询语句性能低下，进行join不超过3个表。超过三个表禁止 join。需要 join 的字段，数据类型必须绝对一致;多表关联查询时， 保证被关联的字段需要有索引。【强制】
说明:即使双表 join 也要注意表索引、SQL 性能。
(14) 数据库查询语句where条件范围要非常少，不要超过10条记录返回。（建议）
(15) 业务数据表不允许包含数据物理硬删除操作，若业务存在数据删除操作需求，必须采用逻辑删除，数据表中增加数据逻辑删除标记is_deleted,确实需要进行物理删除的时候需要经过部门leader同意后DBA方可操作。
（备注：其中is_deleted=’0’为否，is_deleted=’1’为是）
(16) varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长 度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索 引效率。【强制】
(17)  合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检 索速度。【参考】
（18）对于内容重复的大字段如text、varchar(500)等列，可以新建冗余表，通过表关联的方式进行查询，提升查询效率，减少磁盘空间使用。

四.索引规范

1.索引的数量要控制：

(1) 单个索引中的字段数不超过5个（建议3个以内）
(2) 单张表中索引数量不超过5个
2.主键准则
(1) 表必须有主键
(2) 不使用更新频繁的列作为主键
(3) 尽量不选择字符串列作为主键
(4) 不允许UUID MD5 HASH这些作为主键
(5) 默认使用非空的唯一键作为主键
(6) 建议选择自增列作为主键，定义为 int/bigint unsigned auto_increment comment '主键'

3.重要的SQL必须被索引，比如：

(1) UPDATE.DELETE语句的WHERE条件列
(2) ORDER BY.GROUP BY.DISTINCT的字段

4.多表JOIN的字段注意以下(涉及到多表JOIN的需求,提前提交到DBA处审核)：

(1) 区分度最大的字段放在前面
(2) 核心SQL优先考虑覆盖索引
(3) 避免冗余和重复索引

(4) 索引要综合评估数据密度和分布以及考虑查询和更新比例

5.索引禁忌

(1) 不在低基数列上建立索引，例如“性别”
(2) 不在索引列进行数学运算和函数运算
(3) 创建索引前先查看表结构，避免创建冗余索引
(4) 在 varchar 字段上建立索引时，指定索引长度，没必要对全字段建立索引，根据 实际文本区分度决定索引长度即可。【建议】
说明:索引的长度与区分度是一对矛盾体，一般对字符串类型数据，区分度达到 90%及以上即可，可以使用 count(distinct left(列名, 索引长度))/count(distinct 列名)的区分度 来确定。
(5) 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。【强制】
说明:索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。
(6) 建组合索引的时候，区分度最高的在最左边。【强制】
正例:如果 where a=? and b=? ，如果 a 列的几乎接近于唯一值，那么只需要单建 idx_a索引即可。
说明:存在非等号和等号混合时，在建索引时，请把等号条件的列前置。如:where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即索引 idx_d_c。

6.尽量不使用外键

(1) 不得使用外键与级联，一切外键概念必须在应用层解决 【强制】
说明:以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为 级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群;级联更新是强阻 塞，存在数据库更新风暴的风险;外键影响数据库的插入速度。
(2) 对父表和子表的操作会相互影响，降低可用性

7.索引命名：非唯一索引以 idx_字段1_字段2命名，唯一索引以uk_字段1_字段2命名，索引名称必须全部小写 【建议】
8.新建的唯一索引必须不能和主键重复
9.索引字段的默认值不能为NULL，要改为其他的default或者空。NULL非常影响索引的查询效率
10.反复查看与表相关的SQL，符合最左前缀的特点建立索引，例如：一个索引idx_test(n1,n2,n3)，where n1= ;where n1= and n2=;where n1= and n3=;where n1= and n2= and n3=,这四种情况会使用到该索引。多条字段重复的语句，要修改语句条件字段的顺序，为其建立一条联合索引，减少索引数量。【强制】
11.能使用唯一索引就要使用唯一索引，提高查询效率
12.使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary全表扫描
13.SQL变更需要确认索引是否需要变更并通知DBA

五.SQL规范

(1) sql语句尽可能简单,尽力避免使用JOIN
(2) 事务要简单，整个事务的时间长度不要太长(多事务，小事务原则) ，单事务数据更改粒度为2000行，单事务提交时间应小于1S
(3) 避免使用触发器.函数.存储过程,如果临时使用存储过程进行批量操作，每个commit之后应该sleep 1s(根据情况设置)，避免由于主库大量写入导致的主从延迟问题。
(4) 避免在数据库中进行数学运算
(5) 在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明:1)增加查询分析器解析成本。2)增减字段容易与 resultMap 配置不一致。3)无用字 段增加网络消耗，尤其是 text 类型的字段。
(6) limit分页注意效率。Limit越大，效率越低。可以改写limit，比如例子改写： select id from tlimit 10000, 10; => select id from t where id > 10000 limit10; SELECT * FROM table ORDER BY TIME DESC LIMIT 10000,10;=》 SELECT * FROM table WHERE TIME<last_TIME ORDER BY TIME DESC LIMIT 10. =》 SELECT * FROM table inner JOIN(SELECT id FROM table ORDER BY TIME LIMIT 10000,10) as t USING(id)
(7) 使用union all替代union
(8) 避免使用大表的JOIN 
(9) 对数据的更新要打散后批量更新，不要一次更新太多数据，一次每次更新不超过2000条记录
(10) 减少与数据库的交互次数
(11) 注意使用性能分析工具 Sql explain / show profile 
(12) 防止因字段类型不同造成的隐式转换，导致索引失效。如id int，使用where id='1';
(13) IN条件里面的数据数量要少,尽量不用,IN条件里边的数量应不超过20
(14) 能不用NOT IN就不用NOT IN，不会把NULL给查出来 
(15) 在SQL语句中，禁止使用前缀是%的like 
(16) 不使用负向查询，如not in/like
(17) 关于分页查询：程序里建议合理使用分页来提高效率limit，offset较大要配合子查询使用
例如:先快速定位需要获取的 id 段，然后再关联:
SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
(18) 禁止在数据库中跑大查询
(19) 使用预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率
(20) 禁止使用order by rand()
(21) 禁止单条SQL语句同时更新多个表
(22) 禁止子查询中用group by，order by，DISTINCT。例如 (select xx,xxx from a where a.id in (select id from b group by xx)
(23) 对分区表查询 条件中必须带上分区字段
(24) 不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的 标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
说明:count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

六.流程规范 

(1) 发布包必须经过测试人员测试，若是高并发的sql必须同时经过压力测试；
(2) 所有的建表操作需要提前告知该表涉及的查询sql；(重点关注) ；
(3) 所有的建表需要确定建立哪些索引后才可以建表上线；
(4) 所有的改表结构.加索引操作都需要将涉及到所改表的查询sql发出来告知DBA等相关人员； 
(5) 在建新表加字段之前，建议研发至少要提前一天邮件，给DBA们评估.优化和审核的时间
(6) 批量导入，导出数据必须提前通知DBA协助观察,对批量操作进行拆分，原则每条SQL更改行数不得超过2000行，如果总数据量过多，可以和DBA商议增大更改行数。
(7) 禁止有super权限的应用程序账号存在 
(8) 推广活动或上线新功能必须提前通知DBA进行流量评估
(9) 不在业务高峰期批量更新.查询数据库 
(10) 业务高峰期DDL 变更,需要DBA 进行评估，由DBA 决定什么时候进行变更。
(11) 如版本迭代需要数据库的更新 提前通知DBA （上班时间告知，如果已下班 一定电话通知）
(12) 数据库慢查询语句，新出现的必须3天内完成整改，历史慢查询必须7天内完成整改
(13) 应用代码连接数据库配置必须使用阿里开源中间件druid加密。
(14) 新房和二手房第二个从库提供在线读业务，第三个从库提供离线读业务，,除了新房和二手房外，其他业务主进行读写操作，从库只是作为离线读从库。
(15)所有操作默认全部走SQL审核系统,如不能走SQL审核系统完成的,需拉群讨论需求,确认后由需求方发邮件到 yunwei-admin@fangdd.com 由DBA或指定的人员操作.
       需求邮件内容必需包括但不限于需求背景,操作的库,表,操作SQL脚本/命令