MySQL 开发规范

2024-03-16 10:40:01 分类：后端 热度：165 评论： 0

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

一、设计原则

原则： SQL开发规范制定是基于良好的编码习惯和可读性；
目的： 消除冗余，数据简约，提高效率，提高安全；
【1】禁止在线上生产环境做数据库压力测试，全链路压测可以支持，但是一定不要在业务高峰期搞压力测试。
【2】禁止在数据库中明文存储密码和敏感信息，可能信安部门回来找你。
【3】禁止从开发、测试环境直连线上生产数据库，一般会做网络隔离，可能也连不上。
【4】禁止将业务日志实时保存到数据库。具体看情况，一些重要的也可以，但是程序日志就不要放进去，可以放到ClickHost和ES之类的产品。
【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...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
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】如果结果集不需要去重，尽量改写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 类型长度应该根据实际设定
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】尽量少用TEXT、BLOB、CLOB、影像等专有类型的二进制数据 (大数据如图片等可用链接代替)，注：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】索引字段最好不要存在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
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条件中最好不要用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 * 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】避免跨引擎操作，如表分别为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'";
转载请说明出处内容投诉
CSS教程_站长资源网 » MySQL 开发规范
谢生

谢生

分享到：

相关推荐

发表评论

欢迎 访客 发表评论

一个令你着迷的主题！

查看演示 官网购买