MySQL 开发规范

优质博文:IT-BLOG-***

一、设计原则

原则: SQL开发规范制定是基于良好的编码习惯和可读性;
目的: 消除冗余,数据简约,提高效率,提高安全;
【1】禁止在线上生产环境做数据库压力测试,全链路压测可以支持,但是一定不要在业务高峰期搞压力测试。
【2】禁止在数据库中明文存储密码和敏感信息,可能信安部门回来找你。
【3】禁止从开发、测试环境直连线上生产数据库,一般会做网络隔离,可能也连不上。
【4】禁止将业务日志实时保存到数据库。具体看情况,一些重要的也可以,但是程序日志就不要放进去,可以放到ClickHostES之类的产品。
【5】业务部门推广活动,需要提起通知DBA
【6】任何语句使用前通过EXPLAIN查看执行计划是否用到索引:链接
【7】不要从明细表查统计结果,定期统计插入到汇总表:这个做互联网的深有感触,一张表几千万的数据,统计一个数据,性能真心很低。

EXPLAIN
SELECT COUNT(user_no) AS u_no,
SUM(bid_no) AS b_no
FROM user
WHERE registered_date >= '2013-02-07 18:11:40';

Query_time: 18.229131 Lock_time: 0.000474 Rows_sent: 0 Rows_examined:100289

注:从明细表查询时检索记录数为100289,从汇总表查询时检索记录数为1,需要改为从汇总表查询。

id select_type table type possible_keys key key_len ref rows Extra
1 SIMEPLE user ALL 100289 Using 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...selectreplace...select这样的bulk insert形式。
【13】不允许再查询语句中使用hint类似的语法:sql_no_cacheforce indexignore keystraight join等。
【14】不要频繁在数据库中做运算符操作,如count()/sum()/md5()/order by rand()等,需要控制QPS,对CPU消耗是很高的,特别是countsum他有可能会扫描整个索引。
【15】不要使用大偏移量的limit,正例:where id > N limit M 反例:where ? limit N,MN越来越大的时候,相当于没用limit
【16】禁止使用SELECT *,必须指定字段名称,同样,INSERT后边需要指定字段;
【17】明细统计时,只统计编码,不要关联名称等冗余字段。注:名称显示可查询全局hashtable(写 300万/秒 读1200万/秒)。
其他KV工具:Memcached(读写 8万/秒,多线程更快,适合小数据)Redis(读写10万/秒,单线程,可多进程,适合大数据和复杂数据结构)注:一般phpMemcachedJavaRedis,小量数据时推荐static变量。
【18】联合查询时,每个表必须加别名,否则系统需要自己生成别名,影响性能。关联字段必须是索引(最好是主键),where条件用以过滤主表;注:提高SQL解析效率,便于代码阅读,注意字段前边也要加别名。

--问题语句:bid_no 就没有指定别名
SELECT a.*, b.user_name
FROM user a
LEFT JOIN class_name b
ON a.uid = b.uid AND b.student = 0
WHERE bid_no = '31001383713';

【19】每个查询结果集使用的内存量不要超过256M,可以通过时间范围控制,如RK BETWEEN A AND B,大表可按小时操作。注:可通过分批查询返回大量数据;

SELECT user_no, bid_no, evaluate_no, registered_date, updated_date, operator, time_stamp FROM user;
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需要创建临时结果集;

SELECT MIN (b.bid_no) FROM user b
GROUP BY b.bid_no

【23】禁止语句级并行;
【24】大表join用临时表代替create temporary table
【25】链接MySQL不要设置成auto***mit=0,否则就隐式的开启了一个事务;
【26】线上尽量少使用大量查询,将复杂SQL拆分成多条简单SQL,化繁为简;
【27】如果结果集不需要去重,尽量改写unionunion all
【28】ORDER BY语句对多个不同条件使用不同方向的排序无法使用索引,请尽量避免或在程序端进行排序;

二、建表规范

【1】存储引擎必须是InnoDB,因为只有这一个引擎支持事务,还有InnoDB有许多好的特性,比如ChannlBuffer对写入和修改特别友好。double write保护机制对数据一致性要求比较高,读上面有一些Handler_read来保证读写能力;
【2】必须有显示主键,如果不设置主键,MySQL会给一个隐藏主键。会影响查询效率。如果进行修改的时候,数据库是根据唯一主键进行处理的,如果没有主键,可能会扫描全表,造成数据库延迟;
【3】推荐使用独立业务的AUTO_INCREMENT列做主键,禁止使用多字段联合主键;好处就是主键是连续的,再InnoDBB+树那么存储的空间就会更加紧密。不要有业务意义主要是为了方便业务的操作。如果使用联合索引特别长的话,会影响Table get的点查;
【4】不要使用UUID/MD5/HASH等函数生成的无序值做主键,会导致页的分裂;
【5】字符集必须是utf8mb4MySQL对字符集的匹配机制做的不是特别好,比如utf8utf8mb4做一些join运算,会进行一些隐式类型转换,可能找不到索引;
【6】字段声明NOT NULL,且必须显示指定默认值。NULL比较空,如果进行COUNT(name)的时候,Null是不进行统计的,Sum(age)如果有NULL的话,计算出来的值也是NULL;
【7】库名、表名、字段名禁用保留字,如descrangmatchdelayed等;
【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】尽可能使用更小的数据类型,如TINYINTSMALLINTMEDIUMINTINTBIGINT。注: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 类型长度应该根据实际设定
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_no` int(32) NOT NULL DEFAULT '',
  `bid_no` int(32) NULL DEFAULT NULL,
  `evaluate_no` int(32) NULL DEFAULT NULL,
  `registered_date` datetime NULL DEFAULT NULL,
  `updated_date` datetime NULL DEFAULT NULL,
  `operator` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  `time_stamp` datetime NULL DEFAULT NULL,
  PRIMARY KEY USING BTREE (`user_no`)
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = ***pact;

【2】尽量少用TEXTBLOBCLOB、影像等专有类型的二进制数据 (大数据如图片等可用链接代替),注:CLOB等类型在性能和兼容性表现不好,会有带宽问题,将IO或者网络带宽打爆,Text字段正常是不能使用索引的。再就是MySQL不适合存放这些东西,还需要自己去解压,建议使用分布式文件系统等存储;

--问题语句:after_value_text 字段使用了 CLOB
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_no` int(11) NOT NULL DEFAULT '',
  `bid_no` int(11) NULL DEFAULT NULL,
  `evaluate_no` int(11) NULL DEFAULT NULL,
  `after_value_text` CLOB,
  PRIMARY KEY USING BTREE (`user_no`)
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = ***pact;

【3】相同属性对应的数据类型,如字符型,数值型不能混合使用,依赖后期转换;

--问题语句:默认bid_no 应该是 int,否则需要隐式转化
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_no` int(11) NOT NULL DEFAULT '',
  `bid_no`  char(11) NULL DEFAULT NULL,
  PRIMARY KEY USING BTREE (`user_no`)
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = ***pact;

【4】相同字段不同表中的类型和长度要一致;
【5】字段名称不能使用关键字;
【6】不要指定字段级编码,建议全库统一。可以设置编码的有数据源、表,语句。如果语句中没有设置编码就会遵循表的编码,表的编码不存在时,则遵循数据源的编码,注:字段级编码在导入导出时可能乱码;
【7】默认值要规范,例如日期不要使用0000-00-00

--问题语句:特有默认值在 ETL 时会导致异常
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_no` int(11) NOT NULL DEFAULT '',
  `bid_no` int(11) NULL DEFAULT NULL,
  `time_stamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY USING BTREE (`user_no`)
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = ***pact;

【8】不要用自增ID做主键,无法同步,无约束意义。自增在分表分库的业务场景下也不实用,容易导致主键ID冲突。注:字段设计需要精雕细琢,尽量符合三范式,确定没有索引的表可以使用自增主键;

--问题语句:user_no 使用了自增
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_no` int(11) UNSIGNED NOT NULL AUTO_INCREMENT ***MENT 'ID号',
  `bid_no` int(11) NULL DEFAULT NULL,
  PRIMARY KEY USING BTREE (`user_no`)
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = ***pact;

【9】不要使用外键和触发器,注:外键和触发器不适合大数据;
【10】事务相关记录保留时间戳,建议只增不改;在必须对记录进行修改的时候,保留更改时间戳;

-- 正确示例:可用于同步和跟踪
ctime datetime NOT NULL ***MENT '创建时间',
utime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ***MENT '更新时间'

【11】精度要求高的字段使用decimal(number, numeric)代替double, float(real)。需要选对场景:精度要求非常高的计算中,要保证有效数字的总个数足够多,需要使用decimal。注:用在与金额有关的地方;
【12】禁止非英文字段名称,使用中文字段名会创造很多困难点;

四、索引使用

【1】对较长的字符类型,如果需要索引,则建立前缀索引,前缀索引长度控制在20个字符以内,索引不能超过767个字节,这个也可以通过设计加长,但是太长也会影响效率;
【2】组合索引字段的顺序,去重之后数量越多代表区分度越高,区分度越高的字段排在前面;
【3】每个查询必须用到索引 (小表可能全表更好,视数据量决定)。注:如果没有索引,即使加了rownum=1,也会全表扫描;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMEPLE user rang PRIMARY PRIMARY 4 12 Using where
【4】建立组合索引时,WHERE条件中用到等于的字段放前边,用到范围的字段放后边。注:如 DD=100000 AND SJ BETWEEN A AND B
id select_type table type possible_keys key key_len ref rows Extra
---- ---- ---- ---- ---- ---- ---- ---- ---- ----
1 SIMEPLE user rang p_no p_no 10 12 Using where
【5】删除重复字段的索引,减少 DML IO。注:索引过多影响操作效率,重复索引可能导致执行计划异常;
--问题语句:下面的 key 属于重复key 第一个可以删除掉
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_no` int(11) UNSIGNED NOT NULL AUTO_INCREMENT ***MENT 'ID号',
  `bid_no` int(11) NULL DEFAULT NULL,
  PRIMARY KEY USING BTREE (`user_no`),
  KEY RoadLineID (`bid_no`),
  KEY RoadLineID2 (`bid_no`,`evaluate_no`)
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = ***pact;

【6】除了主键外,避免建立其他唯一性索引。注:业务逻辑通过业务应用控制,数据库设计时要选择正确的主键;

--问题语句:bid_no 数据量大的话不应该定义为唯一索引
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_no` int(11) UNSIGNED NOT NULL AUTO_INCREMENT ***MENT 'ID号',
  `bid_no` int(11) NULL DEFAULT NULL,
  PRIMARY KEY USING BTREE (`user_no`),
  UNIQUE KEY index_bid_no (bid_no)
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = ***pact;

【7】索引中重复的记录数越少,效率越高,效率最高的是主键。注:如果同一记录超过50%,全表扫描定期analyze table收集统计信息和直方图,如果可以加not null的最好加上。例如性别这个字段就不适合体检索引;
【8】索引字段最好不要存在NULLNULL可用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'如果aint,查询字段值为字符类型,这样会有隐式转换,无法使用索引,但是a如果是字符类型,a = 1却能用到索引;
【14】in后面的集合元素的数量,尽量控制在200个以内,MySQL主要根据查询字段占比决定是否走索引;

五、查询条件

【1】SQL语句的WHERE条件避免使用无效条件、无效括号、无效排序,如 (1=1)order by。注:示例语句中使用了无效条件、无效括号,对性能有极大影响;

--问题语句:
SELECT user_no, bid_no, evaluate_no
FROM user
WHERE (1=1)
AND ( (user_no LIKE '%') AND (bid_no LIKE '%') )
AND ( evaluate_no <> 0 )
AND ( operator <> 0 )
ORDER BY 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,show status like %temp%;

【4】WHERE条件中最好不要用INLIKE。注:可使用exists代替in, 使用=代替like
【5】WHERE条件中不要使用NOW()等进行判断,避免影响执行计划。注:使用now()的存储过程无法重复运行,且影响执行计划;
【6】禁止使用未经认证的hint,如SELECT SQL_NO_CACHE f FROM t。注:使用标准SQL,便于版本间兼容;
【7】索引相关字段不要使用函数或者进行运算,如field1 + 1 = field2ADDDATE(field1,…CAST。注:大多数字段使用函数不会使用索引,只有确定性function使用函数索引;

--问题语句:会导致索引失效
SELECT * FROM user WHERE CAST(CONCAT(updated_date, ' ', time_stamp) AS datetime) BETWEEN '2014-06-10 10:30:00'

【8】禁止字段格式转换,如SELECT x FROM GS WHERE BM=200000,数值两边不要加引号。注:要区分数值、日期和字符串,科学计数法更要慎重使用;

id select_type table type possible_keys key key_len ref rows Extra
1 SIMEPLE user ref p_no p_no 5 const 50144 Using where

六、存储过程

MySQL尽量不要使用外键、存储过程、视图、触发器。在互联网公司都不希望用外键,因为外键对性能的损耗比较严重。在SQL Server时期存储过程是一个好设计,但在MySQL不是一个好设计,主要是因为两者的实现逻辑不同,在SqlServer中存储过程能够应用程二级制码的,效率非常高,而且可以进行提前做一个plan cash的解析,但在MySQL没有上述的逻辑,所以实现不是很好。
【1】存储过程中操作的记录数超过1000条时不能使用游标 (可用临时表代替,禁止使用触发器、自定义函数)。注:也不能使用 while,大批量数据操作对应脚本必须进行压力测试,以保证可用性;
【2】在存储过程的关键步骤开始和结束都要记录信息到日志表,用于监控和调试。注:提交要及时,记录要详细;
【3】 使用标准操作符,不要使用双引号、!= 等过渡性操作符。注:非标准操作符可能导致其他无法预期问题;
【4】存储过程要能够重复执行,执行时需要清空历史冲突记录。注:非常重要,多种情况下需要存储过程重复运行;
【5】使用自制事务(autonomous transaction)控制写日志,独立日志操作存储过程。注:用以避免回滚时无法记录日志;
【6】所有过程必须定义异常处理,并自定义错误代码,从-20001开始;

CREATE OR REPLACE PROCEDURE ap AS
BEGIN
    NULL;
    --body
EXCEPTION
    WHEN OTHERS
    THEN
        NULL;
    -- exception handler
END;

【7】过程避免每条语句提交。注:控制提交频率大于1秒;
【8】禁止使用递归。注:使用递归时,性能消耗无法控制;

七、远程表

【1】远程表结构要与原始表一致,尤其是索引。注:同时编码要一致;
【2】远程表数据不要大于256M,远程表的 WHERE 无效。注:远程表先下载到本地,再进行其他操作,所以不宜过大;
【3】远程表一般用来全表小数据全量同步。注:远程表大数据操作时很慢;
【4】远程表操作完毕提交操作。注:默认远程表只用来读操作,如果进行写操作,则锁全表;

八、性能优化

【1】 文件格式改为XFS可以提升5%,增加1/6磁盘可以提升1/6,优化索引和结构,一般可以提升100-1000倍。注:了解SQL原理,让数据经过一次读操作即能返回结果时最快;
【2】使用TYPE=HEAP的临时表。注:适合频繁删除和更新;

九、引擎使用

【1】使用INNODB引擎操作大批量数据时,在过程结尾提交,避免过度***MIT。注:通过事务提交可以提高大数据操作效率,同时有序插入、合并插入也可以大幅提高数据库效率。

--正确语句:
START TRANSACTION;
INSERT INTO t(datetime, UID, content, TYPE) VALUES ('0', 'userid_0', 'content_0', 0),('1',
    'userid_1', 'content_1', 1);
INSERT INTO t(datetime, UID, content, TYPE) VALUES ('2', 'userid_2', 'content_2', 2),('3',
    'userid_3', 'content_3', 3);
***MIT;

【2】避免跨引擎操作,如表分别为InnodBMyISAM。问题语句:(不能用函数、需要有索引、先汇总再关联、要查汇总表、表引擎要一致、表编码要一致、字段类型要一致)。注:跨引擎操作时,事务失效,推荐都使用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'";
转载请说明出处内容投诉
CSS教程_站长资源网 » MySQL 开发规范

发表评论

欢迎 访客 发表评论

一个令你着迷的主题!

查看演示 官网购买