数据库规范

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

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

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

2.1、避免使用多表关联：

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

2.2、对join语句：

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

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

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

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

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

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

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

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

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

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

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

2.8、适当使用COMMIT：

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

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

2.9、用TRUNCATE替代DELETe：

原因：

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

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

2.10、对count()语句：

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

（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条记录。对此，可尽量考虑使用索引，或进行转化。实例如下：

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

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

其中，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语句,减少查询次数。例如：

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取数据库时间：

问题分析

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

优化建议

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

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

问题案例：

问题分析

检查分区表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引起的插入错误：

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;

3.1、innodb行锁的实现方式

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

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

（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)分区和子分区五类。

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

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、 部分参数赋值规范