MySQL数据库编码开发规范

目的

为了规范MySQL的应用开发，减少开发过程中对MySQL的错误使用，提高数据存取效率，特制订本规范。

适用范围

所有mysql数据库。原则上，数据库设开发遵循本规范说明，特殊情况可例外，但需跟DBA说明原因。

规范

 

不允许不同业务模块的表连接查询

表分模块很可能在后续进行业务的垂直拆分到不同的业务库里面去，如果做连接查询后面拆分就比较麻烦，系统的模块化结构也不清晰

 

尽量不在数据库做运算，尽可能简单使用MySQL

md5() 或Order by Rand()或计算字段等操作不在数据库表上进行

 

SQL中避免出现now()、rand()、sysdate()、current_user()等不确定结果的函数

语句级复制场景下,引起主从数据不一致;不确定值的函数,产生的SQL语句无法利用QUERY CACHE。

 

合理使用覆盖索引减少IO,避免排序

覆盖索引能从索引中获取需要的所有字段,从而避免回表进行二次查找,节省IO。INNODB存储引擎中, secondary index(非主键索引,又称为辅助索引、二级索引)没有直接存储行地址,而是存储主键值。如果用户需要查询secondary index中所不包含的数据列,则需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次。覆盖索引则可以在一个索引中获取所有需要的数据,因此效率较高。主键查询是天然的覆盖索引。例如SELECT email,uid FROM user_email WHERE uid=xx,如果uid 不是主键,适当时候可以将索引添加为index(uid,email),以获得性能提升。

 

禁止在数据库中存储明文密码

密码存储应该使用不可逆加密算法将密码、用户名及盐值组合加密后存储

 

禁止将数据字典表进行连接查询

数据自动表可以查询到cache或者在内存中对相关列进行替换

 

禁止频繁使用的SQL语句中出现三个以上表的连接查询

MySQL在连接查询方面的处理能力远没有Oracle强劲，在连接的过程中只有Nest Loop Join方式，Oracle则可以有Hash Join 和 Memory Index Joini。在常用的SQL中禁止出现三个以上表的连接查询，如果是定时执行的次数不多的SQL需要另外考虑

 

用IN代替OR。SQL语句中IN包含的值不应过多,应少于1000个。

IN是范围查找,MySQL内部会对IN的列表值进行排序后查找,比OR效率更高。

 

用UNION ALL代替UNION

UNION ALL不需要对结果集再进行排序。

 

禁止使用order by rand()

order by rand()会为表增加一个伪列,然后用rand()函数为每一行数据计算出rand()值,然后基于该行排序, 这通常都会生成磁盘上的临时表,因此效率非常低。建议先使用rand()函数获得随机的主键值,然后通过主键获取数据。

 

建议使用合理的分页方式以提高分页效率

第一种分页写法：

select * from t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15;

原理：一次性根据过滤条件取出所有字段进行排序返回。

数据访问开销 = 索引IO + 索引全部记录结果对应的表数据IO

缺点：该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

 

第二种分页写法：

select t.* from ( select id from t where thread_id = 771025 and deleted = 0 order by gmt_create asc limit 0, 15) a, t where a.id = t.id;

前提：假设t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create)

原理：先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。

数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO。

优点：每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

 

SELECT只获取必要的字段,禁止使用SELECT *

减少网络带宽消耗;

能有效利用覆盖索引;

表结构变更对程序基本无影响。

 

减少与数据库交互次数,尽量采用批量SQL语句

使用下面的语句来减少和db的交互次数:

1. INSERT ... ON DUPLICATE KEY UPDATE
2. REPLACE INTO
3. INSERT IGNORE
4. INSERT INTO VALUES()

 

拆分复杂SQL为多个小SQL,避免大事务

简单的SQL容易使用到MySQL的QUERY CACHE;减少锁表时间特别是MyISAM;可以使用多核CPU。

 

对同一个表的多次alter操作必须合并为一次操作

mysql对表的修改绝大部分操作都需要锁表并重建表,而锁表则会对线上业务造成影响。为减少这种影响,必须把对表的多次alter操作合并为一次操作。例如,要给表t增加一个字段b,同时给已有的字段aa建立索引,

通常的做法分为两步: alter table t add column b varchar(10);

然后增加索引: alter table t add index idx_aa(aa);

正确的做法是: alter table t add column b varchar(10),add index idx_aa(aa);

 

避免使用存储过程、触发器、视图、自定义函数等

这些高级特性有性能问题,以及未知BUG较多。业务逻辑放到数据库会造成数据库的DDL、SCALE OUT、 SHARDING等变得更加困难。

 

禁止有super权限的应用程序账号存在

安全第一。super权限会导致read only失效,导致较多诡异问题而且很难追踪。

 

提交线上建表改表需求,必须详细注明涉及到的所有SQL语句(包括INSERT、DELETE、UPDATE),便于DBA进行审核和优化

不只是SELECT语句需要用到索引。UPDATE、DELETE都需要先定位到数据才能执行变更。因此需要业务提供所有的SQL语句便于DBA审核。

 

禁止使用HINT

HINT给DBA维护带来很多的不便，MySQL使用CBO优化器，会综合考虑如果执行SQL。