优质博文：IT-BLOG-CN
一、设计原则
原则：
SQL
开发规范制定是基于良好的编码习惯和可读性；
目的： 消除冗余，数据简约，提高效率，提高安全；
【1】禁止在线上生产环境做数据库压力测试，全链路压测可以支持，但是一定不要在业务高峰期搞压力测试。
【2】禁止在数据库中明文存储密码和敏感信息，可能信安部门回来找你。
【3】禁止从开发、测试环境直连线上生产数据库，一般会做网络隔离，可能也连不上。
【4】禁止将业务日志实时保存到数据库。具体看情况，一些重要的也可以，但是程序日志就不要放进去，可以放到
ClickHost
和
ES
之类的产品。
【5】业务部门推广活动，需要提起通知
DBA
。
【6】任何语句使用前通过
EXPLAIN
查看执行计划是否用到索引：链接
【7】不要从明细表查统计结果，定期统计插入到汇总表：这个做互联网的深有感触，一张表几千万的数据，统计一个数据，性能真心很低。
EXPLAINSELECTCOUNT(user_no)AS u_no,SUM(bid_no)AS b_no
FROMuserWHERE registered_date >='2013-02-07 18:11:40';
Query_time
:
18.229131 Lock_time
:
0.000474 Rows_sent
:
0 Rows_examined:100289
注：从明细表查询时检索记录数为
100289
，从汇总表查询时检索记录数为
1
，需要改为从汇总表查询。
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMEPLEuserALL100289Using where
【8】禁止使用子查询，
MySQL
不像
SqlServer
等其他数据库，它对子查询的力度支持比较低。
【9】
update/delete/replace
只能单表操作，必须基于主键操作，删除表操作，建议使用
truncate
。假设写一个大事务，
MySQL
先写
replay_log
在写
bin_log
，如果特别大，bin_log是顺序写，会阻塞别的事务提交。
【10】不允许出现多于一次的
join
，需要
join
的字段，数据类型保持绝对一致，多表关联时，保证被关联的字段有索引。与子查询一致，因为
join
和子查询是可以相互转换的。
【11】数据订正
update/delete
时，要先
select
避免出现误删除，确认无误再执行更新语句。
【12】
insert
语句要显示指定插入的列明，且不允许使用
insert...select
，
replace...select
这样的
bulk insert
形式。
【13】不允许再查询语句中使用
hint
类似的语法：
sql_no_cache
，
force index
，
ignore key
，
straight join
等。
【14】不要频繁在数据库中做运算符操作，如
count()
/
sum()
/
md5()
/
order by rand()
等，需要控制
QPS
，对
CPU
消耗是很高的，特别是
count
和
sum
他有可能会扫描整个索引。
【15】不要使用大偏移量的
limit
，正例：
where id > N limit M
反例：
where ? limit N,M
当
N
越来越大的时候，相当于没用
limit
。
【16】禁止使用
SELECT *
，必须指定字段名称，同样，
INSERT
后边需要指定字段；
【17】明细统计时，只统计编码，不要关联名称等冗余字段。注：名称显示可查询全局
hashtable
（写 300万/秒 读1200万/秒）。
其他
KV
工具：
Memcached
（读写 8万/秒，多线程更快，适合小数据）
Redis
（读写10万/秒，单线程，可多进程，适合大数据和复杂数据结构）注：一般
php
用
Memcached
，
Java
用
Redis
，小量数据时推荐
static
变量。
【18】联合查询时，每个表必须加别名，否则系统需要自己生成别名，影响性能。关联字段必须是索引（最好是主键），
where
条件用以过滤主表；注：提高
SQL
解析效率，便于代码阅读，注意字段前边也要加别名。
--问题语句：bid_no 就没有指定别名SELECT a.*, b.user_name
FROMuser a
LEFTJOIN class_name b
ON a.uid = b.uid AND b.student =0WHERE bid_no ='31001383713';
【19】每个查询结果集使用的内存量不要超过
256M
，可以通过时间范围控制，如
RK BETWEEN A AND B
，大表可按小时操作。注：可通过分批查询返回大量数据；
SELECT user_no, bid_no, evaluate_no, registered_date, updated_date, operator, time_stamp FROMuser;
Query_time: 239.269626 Lock_time: 0.000151 Rows_sent: 2271775 Rows_examined: 2271775
【20】页面查询在
10
秒内要返回结果，服务器超时限制默认为
65
秒。定期查看慢查询日志
xxx-slow.log
。注：
show variables like '%cache%'
查看
query cache
是否足够大和命中率；
Query_time: 67.432783 Lock_time: 0.000373 Rows_sent: 1 Rows_examined: 168671
【21】语句中避免子查询，子查询无法使用索引（少数子查询使用索引，参考执行计划）。注：子查询结果集记录极少时可以使用，否则效率很差；
【22】语句中避免使用
GROUP BY
，可通过批量程序定期汇总。注：频繁
GROUP BY
需要创建临时结果集；
SELECTMIN(b.bid_no)FROMuser b
GROUPBY b.bid_no
【23】禁止语句级并行；
【24】大表
join
用临时表代替
create temporary table
；
【25】链接
MySQL
不要设置成
autocommit=0
，否则就隐式的开启了一个事务；
【26】线上尽量少使用大量查询，将复杂
SQL
拆分成多条简单
SQL
，化繁为简；
【27】如果结果集不需要去重，尽量改写
union
为
union all
；
【28】
ORDER BY
语句对多个不同条件使用不同方向的排序无法使用索引，请尽量避免或在程序端进行排序；
二、建表规范
【1】存储引擎必须是
InnoDB
，因为只有这一个引擎支持事务，还有
InnoDB
有许多好的特性，比如
ChannlBuffer
对写入和修改特别友好。
double write
保护机制对数据一致性要求比较高，读上面有一些
Handler_read
来保证读写能力；
【2】必须有显示主键，如果不设置主键，
MySQL
会给一个隐藏主键。会影响查询效率。如果进行修改的时候，数据库是根据唯一主键进行处理的，如果没有主键，可能会扫描全表，造成数据库延迟；
【3】推荐使用独立业务的
AUTO_INCREMENT
列做主键，禁止使用多字段联合主键；好处就是主键是连续的，再
InnoDB
是
B+
树那么存储的空间就会更加紧密。不要有业务意义主要是为了方便业务的操作。如果使用联合索引特别长的话，会影响
Table get
的点查；
【4】不要使用
UUID/MD5/HASH
等函数生成的无序值做主键，会导致页的分裂；
【5】字符集必须是
utf8mb4
，
MySQL
对字符集的匹配机制做的不是特别好，比如
utf8
和
utf8mb4
做一些
join
运算，会进行一些隐式类型转换，可能找不到索引;
【6】字段声明
NOT NULL
,且必须显示指定默认值。
NULL
比较空，如果进行
COUNT(name)
的时候，
Null
是不进行统计的，
Sum(age)
如果有
NULL
的话，计算出来的值也是
NULL
;
【7】库名、表名、字段名禁用保留字，如
desc
、
rang
、
match
、
delayed
等;
【8】对表和字段都添加备注说明;
【9】主键列如果为整型，统一使用
bigint
，因为
int
可能会溢出;
【10】库名、表名字段都是小写;
【11】必须包含时间戳字段
datachange_lasttime
，定义默认值为
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
，并添加索引，
CD
发布平台会自动创建；
【12】
datachange_createtime
作为创建时间，定义默认值时不要添加
on update CURRENT_TIMESTAMP
，直接设置为
CURRENT_TIMESTAMP
即可；
三、字段设计
【1】尽可能使用更小的数据类型，如
TINYINT
、
SMALLINT
、
MEDIUMINT
、
INT
、
BIGINT
。注：
int(11)
的11代表客户端显示宽度，并不是取值范围，精度：
tinyint -2^8-2^8-1
smallint - 2^15-2^15-1
int -2^31-2^31-1
bigint -2^63-2^63-1
；
--问题语句：int 类型长度应该根据实际设定DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_no`int(32)NOTNULLDEFAULT'',`bid_no`int(32)NULLDEFAULTNULL,`evaluate_no`int(32)NULLDEFAULTNULL,`registered_date`datetimeNULLDEFAULTNULL,`updated_date`datetimeNULLDEFAULTNULL,`operator`varchar(255)CHARACTERSET latin1 COLLATE latin1_swedish_ci NULLDEFAULTNULL,`time_stamp`datetimeNULLDEFAULTNULL,PRIMARYKEYUSINGBTREE(`user_no`))ENGINE=InnoDBCHARACTERSET= latin1 COLLATE= latin1_swedish_ci ROW_FORMAT = Compact;
【2】尽量少用
TEXT
、
BLOB
、
CLOB
、影像等专有类型的二进制数据 (大数据如图片等可用链接代替)，注：
CLOB
等类型在性能和兼容性表现不好，会有带宽问题，将
IO
或者网络带宽打爆，
Text
字段正常是不能使用索引的。再就是
MySQL
不适合存放这些东西，还需要自己去解压，建议使用分布式文件系统等存储；
--问题语句：after_value_text 字段使用了 CLOBDROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_no`int(11)NOTNULLDEFAULT'',`bid_no`int(11)NULLDEFAULTNULL,`evaluate_no`int(11)NULLDEFAULTNULL,`after_value_text` CLOB,PRIMARYKEYUSINGBTREE(`user_no`))ENGINE=InnoDBCHARACTERSET= latin1 COLLATE= latin1_swedish_ci ROW_FORMAT = Compact;
【3】相同属性对应的数据类型，如字符型，数值型不能混合使用，依赖后期转换；
--问题语句：默认bid_no 应该是 int，否则需要隐式转化DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_no`int(11)NOTNULLDEFAULT'',`bid_no`char(11)NULLDEFAULTNULL,PRIMARYKEYUSINGBTREE(`user_no`))ENGINE=InnoDBCHARACTERSET= latin1 COLLATE= latin1_swedish_ci ROW_FORMAT = Compact;
【4】相同字段不同表中的类型和长度要一致；
【5】字段名称不能使用关键字；
【6】不要指定字段级编码，建议全库统一。可以设置编码的有数据源、表，语句。如果语句中没有设置编码就会遵循表的编码，表的编码不存在时，则遵循数据源的编码，注：字段级编码在导入导出时可能乱码；
【7】默认值要规范，例如日期不要使用
0000-00-00
；
--问题语句：特有默认值在 ETL 时会导致异常DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_no`int(11)NOTNULLDEFAULT'',`bid_no`int(11)NULLDEFAULTNULL,`time_stamp`datetimeNOTNULLDEFAULT'0000-00-00 00:00:00',PRIMARYKEYUSINGBTREE(`user_no`))ENGINE=InnoDBCHARACTERSET= latin1 COLLATE= latin1_swedish_ci ROW_FORMAT = Compact;
【8】不要用自增
ID
做主键，无法同步，无约束意义。自增在分表分库的业务场景下也不实用，容易导致主键
ID
冲突。注：字段设计需要精雕细琢，尽量符合三范式，确定没有索引的表可以使用自增主键；
--问题语句：user_no 使用了自增DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_no`int(11)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'ID号',`bid_no`int(11)NULLDEFAULTNULL,PRIMARYKEYUSINGBTREE(`user_no`))ENGINE=InnoDBCHARACTERSET= latin1 COLLATE= latin1_swedish_ci ROW_FORMAT = Compact;
【9】不要使用外键和触发器，注：外键和触发器不适合大数据；
【10】事务相关记录保留时间戳，建议只增不改；在必须对记录进行修改的时候，保留更改时间戳；
-- 正确示例：可用于同步和跟踪
ctime datetimeNOTNULLCOMMENT'创建时间',
utime timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间'
【11】精度要求高的字段使用
decimal(number, numeric)
代替
double
,
float(real)
。需要选对场景：精度要求非常高的计算中，要保证有效数字的总个数足够多，需要使用
decimal
。注：用在与金额有关的地方；
【12】禁止非英文字段名称，使用中文字段名会创造很多困难点；
四、索引使用
【1】对较长的字符类型，如果需要索引，则建立前缀索引，前缀索引长度控制在20个字符以内，索引不能超过
767
个字节，这个也可以通过设计加长，但是太长也会影响效率；
【2】组合索引字段的顺序，去重之后数量越多代表区分度越高，区分度越高的字段排在前面；
【3】每个查询必须用到索引 (小表可能全表更好,视数据量决定)。注：如果没有索引，即使加了
rownum=1
，也会全表扫描；
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMEPLEuserrangPRIMARYPRIMARY412Using where【4】建立组合索引时，
WHERE
条件中用到等于的字段放前边，用到范围的字段放后边。注：如
DD=100000 AND SJ BETWEEN A AND B
。idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra----------------------------------------1SIMEPLEuserrangp_nop_no1012Using where【5】删除重复字段的索引，减少
DML IO
。注：索引过多影响操作效率，重复索引可能导致执行计划异常；
--问题语句：下面的 key 属于重复key 第一个可以删除掉DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_no`int(11)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'ID号',`bid_no`int(11)NULLDEFAULTNULL,PRIMARYKEYUSINGBTREE(`user_no`),KEY RoadLineID (`bid_no`),KEY RoadLineID2 (`bid_no`,`evaluate_no`))ENGINE=InnoDBCHARACTERSET= latin1 COLLATE= latin1_swedish_ci ROW_FORMAT = Compact;
【6】除了主键外，避免建立其他唯一性索引。注：业务逻辑通过业务应用控制，数据库设计时要选择正确的主键；
--问题语句：bid_no 数据量大的话不应该定义为唯一索引DROPTABLEIFEXISTS`user`;CREATETABLE`user`(`user_no`int(11)UNSIGNEDNOTNULLAUTO_INCREMENTCOMMENT'ID号',`bid_no`int(11)NULLDEFAULTNULL,PRIMARYKEYUSINGBTREE(`user_no`),UNIQUEKEY index_bid_no (bid_no))ENGINE=InnoDBCHARACTERSET= latin1 COLLATE= latin1_swedish_ci ROW_FORMAT = Compact;
【7】索引中重复的记录数越少，效率越高，效率最高的是主键。注：如果同一记录超过50%，全表扫描定期
analyze table
收集统计信息和直方图，如果可以加
not null
的最好加上。例如性别这个字段就不适合体检索引；
【8】索引字段最好不要存在
NULL
，
NULL
可用0替代，建议把默认值设置为0。注：在
null
远多于非
null
的情况下，建议表设计
default 0
；
【9】组合索引可以只使用第一个，或者前两个，或者前几个，不能从第二个开始用，也不能跳着使用。注：索引使用从前缀开始，多字段索引到
between
或者
<,>
等以后字段不会使用索引，排序最好在索引中实现；
【10】关联查询用到的索引的编码必须一致，建议都采用
UTF-8
。注：如果编码不一致，索引无效；
【11】一般情况下，一次查询只会用到一个索引。特定情况出现
merge index
的情况，如下可能出现 ( a=1 or b=2 ) 会合并 a 和 b 的索引，或者使用
union all
。因为
or
会破坏索引的最左原则；
【12】每个表索引越少越好，建议1-3个，最多5个 (oltp 1-5,olap 5以上)。注：表索引过多影响写和更新操作效率,
GreenPlum
数据库可合理利用分区和分布键，线上就碰到过索引比表还大；
【13】
where a = '1'
如果
a
为
int
，查询字段值为字符类型，这样会有隐式转换，无法使用索引，但是
a
如果是字符类型，
a = 1
却能用到索引；
【14】
in
后面的集合元素的数量，尽量控制在200个以内，
MySQL
主要根据查询字段占比决定是否走索引；
五、查询条件
【1】
SQL
语句的
WHERE
条件避免使用无效条件、无效括号、无效排序，如
(1=1)
、
order by
。注：示例语句中使用了无效条件、无效括号，对性能有极大影响；
--问题语句：SELECT user_no, bid_no, evaluate_no
FROMuserWHERE(1=1)AND((user_no LIKE'%')AND(bid_no LIKE'%'))AND( evaluate_no <>0)AND( operator <>0)ORDERBY user_no ASC;
Query_time: 10.688586 Lock_time: 0.000070 Rows_sent: 55561 Rows_examined: 59075
【2】
SQL
语句中不要加用不到的排序。注：类似
order by null
等无效排序会影响执行效率；
【3】控制临时结果集，包括中间结果和中间排序。注：合理横向和纵向拆分，一次处理数据量不要太大；
select*from v$sort_usage,showstatuslike%temp%;、
【4】
WHERE
条件中最好不要用
IN
和
LIKE
。注：可使用
exists
代替
in
, 使用
=
代替
like
；
【5】
WHERE
条件中不要使用
NOW()
等进行判断，避免影响执行计划。注：使用
now()
的存储过程无法重复运行，且影响执行计划；
【6】禁止使用未经认证的
hint
，如
SELECT SQL_NO_CACHE f FROM t
。注：使用标准
SQL
，便于版本间兼容；
【7】索引相关字段不要使用函数或者进行运算，如
field1 + 1 = field2
、
ADDDATE
(
field1
,
…CAST
。注：大多数字段使用函数不会使用索引，只有确定性
function
使用函数索引；
--问题语句：会导致索引失效SELECT*FROMuserWHERE CAST(CONCAT(updated_date,' ', time_stamp)ASdatetime)BETWEEN'2014-06-10 10:30:00'
【8】禁止字段格式转换，如
SELECT x FROM GS WHERE BM=200000
，数值两边不要加引号。注：要区分数值、日期和字符串，科学计数法更要慎重使用；
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMEPLEuserrefp_nop_no5const50144Using where
六、存储过程
MySQL
尽量不要使用外键、存储过程、视图、触发器。在互联网公司都不希望用外键，因为外键对性能的损耗比较严重。在
SQL Server
时期存储过程是一个好设计，但在
MySQL
不是一个好设计，主要是因为两者的实现逻辑不同，在
SqlServer
中存储过程能够应用程二级制码的，效率非常高，而且可以进行提前做一个
plan cash
的解析，但在
MySQL
没有上述的逻辑，所以实现不是很好。
【1】存储过程中操作的记录数超过1000条时不能使用游标 (可用临时表代替，禁止使用触发器、自定义函数)。注：也不能使用
while
，大批量数据操作对应脚本必须进行压力测试，以保证可用性；
【2】在存储过程的关键步骤开始和结束都要记录信息到日志表，用于监控和调试。注：提交要及时，记录要详细；
【3】 使用标准操作符，不要使用双引号、!= 等过渡性操作符。注：非标准操作符可能导致其他无法预期问题；
【4】存储过程要能够重复执行，执行时需要清空历史冲突记录。注：非常重要，多种情况下需要存储过程重复运行；
【5】使用自制事务(autonomous transaction)控制写日志，独立日志操作存储过程。注：用以避免回滚时无法记录日志；
【6】所有过程必须定义异常处理，并自定义错误代码，从
-20001
开始；
CREATEORREPLACEPROCEDURE ap ASBEGINNULL;--body
EXCEPTION
WHEN OTHERS
THENNULL;-- exception handlerEND;
【7】过程避免每条语句提交。注：控制提交频率大于1秒；
【8】禁止使用递归。注：使用递归时，性能消耗无法控制；
七、远程表
【1】远程表结构要与原始表一致，尤其是索引。注：同时编码要一致；
【2】远程表数据不要大于256M，远程表的 WHERE 无效。注：远程表先下载到本地，再进行其他操作，所以不宜过大；
【3】远程表一般用来全表小数据全量同步。注：远程表大数据操作时很慢；
【4】远程表操作完毕提交操作。注：默认远程表只用来读操作，如果进行写操作，则锁全表；
八、性能优化
【1】 文件格式改为
XFS
可以提升
5%
，增加
1/6
磁盘可以提升
1/6
，优化索引和结构，一般可以提升
100-1000
倍。注：了解SQL原理，让数据经过一次读操作即能返回结果时最快；
【2】使用
TYPE=HEAP
的临时表。注：适合频繁删除和更新；
九、引擎使用
【1】使用
INNODB
引擎操作大批量数据时，在过程结尾提交，避免过度
COMMIT
。注：通过事务提交可以提高大数据操作效率，同时有序插入、合并插入也可以大幅提高数据库效率。
--正确语句：STARTTRANSACTION;INSERTINTO t(datetime, UID, content,TYPE)VALUES('0','userid_0','content_0',0),('1','userid_1','content_1',1);INSERTINTO t(datetime, UID, content,TYPE)VALUES('2','userid_2','content_2',2),('3','userid_3','content_3',3);COMMIT;
【2】避免跨引擎操作，如表分别为
InnodB
和
MyISAM
。问题语句：（不能用函数、需要有索引、先汇总再关联、要查汇总表、表引擎要一致、表编码要一致、字段类型要一致）。注：跨引擎操作时，事务失效，推荐都使用
InnodB
；
十 、权限控制
【1】
PHP
连接
MYSQL
的用户只分配对应库
SIUD
。注：权限越大，被攻击时受到的破坏越大。注：权限越大，被攻击时受到的破坏越大；
【2】所有客户端提交的变量都要进行转义操作，防止非法注入。注：可通过工具
AppScan
统一检测；
-- 问题脚本：
$username = isset($_REQUEST["username"]) ? $_REQUEST["username"] : "";
$sql_gh="select EMPID,USERPASS,EMPSTATUS from ydserver.yd_cas_emp where EMPID='$username'";
版权归原作者 程序猿进阶 所有， 如有侵权，请联系我们删除。