网站建设培训 店,移动网站建设解决方案,企业网站案例欣赏,定制开发app方案优质博文#xff1a;IT-BLOG-CN 一、设计原则
原则#xff1a; SQL开发规范制定是基于良好的编码习惯和可读性#xff1b; 目的#xff1a; 消除冗余#xff0c;数据简约#xff0c;提高效率#xff0c;提高安全#xff1b; 【1】禁止在线上生产环境做数据库压力测试IT-BLOG-CN 一、设计原则
原则 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需要改为从汇总表查询。
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...selectreplace...select这样的bulk insert形式。 【13】不允许再查询语句中使用hint类似的语法sql_no_cacheforce indexignore keystraight 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 用MemcachedJava用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不要设置成autocommit0否则就隐式的开启了一个事务 【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】字符集必须是utf8mb4MySQL对字符集的匹配机制做的不是特别好比如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 Compact;【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 Compact;【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 Compact;【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 Compact;【8】不要用自增ID做主键无法同步无约束意义。自增在分表分库的业务场景下也不实用容易导致主键ID冲突。注字段设计需要精雕细琢尽量符合三范式确定没有索引的表可以使用自增主键
--问题语句user_no 使用了自增
DROP TABLE IF EXISTS user;
CREATE TABLE user (user_no int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 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 Compact;【9】不要使用外键和触发器注外键和触发器不适合大数据 【10】事务相关记录保留时间戳建议只增不改在必须对记录进行修改的时候保留更改时间戳
-- 正确示例可用于同步和跟踪
ctime datetime NOT NULL COMMENT 创建时间,
utime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间【11】精度要求高的字段使用decimal(number, numeric)代替double, float(real)。需要选对场景精度要求非常高的计算中要保证有效数字的总个数足够多需要使用decimal。注用在与金额有关的地方 【12】禁止非英文字段名称使用中文字段名会创造很多困难点
四、索引使用
【1】对较长的字符类型如果需要索引则建立前缀索引前缀索引长度控制在20个字符以内索引不能超过767个字节这个也可以通过设计加长但是太长也会影响效率 【2】组合索引字段的顺序去重之后数量越多代表区分度越高区分度越高的字段排在前面 【3】每个查询必须用到索引 (小表可能全表更好,视数据量决定)。注如果没有索引即使加了rownum1也会全表扫描
idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMEPLEuserrangPRIMARYPRIMARY412Using where【4】建立组合索引时WHERE条件中用到等于的字段放前边用到范围的字段放后边。注如 DD100000 AND SJ BETWEEN A AND B 。idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra----------------------------------------1SIMEPLEuserrangp_nop_no1012Using where【5】删除重复字段的索引减少 DML IO。注索引过多影响操作效率重复索引可能导致执行计划异常
--问题语句下面的 key 属于重复key 第一个可以删除掉
DROP TABLE IF EXISTS user;
CREATE TABLE user (user_no int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 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 Compact;【6】除了主键外避免建立其他唯一性索引。注业务逻辑通过业务应用控制数据库设计时要选择正确的主键
--问题语句bid_no 数据量大的话不应该定义为唯一索引
DROP TABLE IF EXISTS user;
CREATE TABLE user (user_no int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 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 Compact;【7】索引中重复的记录数越少效率越高效率最高的是主键。注如果同一记录超过50%全表扫描定期analyze table收集统计信息和直方图如果可以加not null的最好加上。例如性别这个字段就不适合体检索引 【8】索引字段最好不要存在NULLNULL可用0替代建议把默认值设置为0。注在null远多于非null的情况下建议表设计 default 0 【9】组合索引可以只使用第一个或者前两个或者前几个不能从第二个开始用也不能跳着使用。注索引使用从前缀开始多字段索引到between或者,等以后字段不会使用索引排序最好在索引中实现 【10】关联查询用到的索引的编码必须一致建议都采用UTF-8。注如果编码不一致索引无效 【11】一般情况下一次查询只会用到一个索引。特定情况出现merge index的情况如下可能出现 ( a1 or b2 ) 会合并 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条件避免使用无效条件、无效括号、无效排序如 (11)、order by。注示例语句中使用了无效条件、无效括号对性能有极大影响
--问题语句
SELECT user_no, bid_no, evaluate_no
FROM user
WHERE (11)
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 BM200000数值两边不要加引号。注要区分数值、日期和字符串科学计数法更要慎重使用
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开始
CREATE OR REPLACE PROCEDURE ap AS
BEGINNULL;--body
EXCEPTIONWHEN OTHERSTHENNULL;-- exception handler
END;【7】过程避免每条语句提交。注控制提交频率大于1秒 【8】禁止使用递归。注使用递归时性能消耗无法控制
七、远程表
【1】远程表结构要与原始表一致尤其是索引。注同时编码要一致 【2】远程表数据不要大于256M远程表的 WHERE 无效。注远程表先下载到本地再进行其他操作所以不宜过大 【3】远程表一般用来全表小数据全量同步。注远程表大数据操作时很慢 【4】远程表操作完毕提交操作。注默认远程表只用来读操作如果进行写操作则锁全表
八、性能优化
【1】 文件格式改为XFS可以提升5%增加1/6磁盘可以提升1/6优化索引和结构一般可以提升100-1000倍。注了解SQL原理让数据经过一次读操作即能返回结果时最快 【2】使用TYPEHEAP的临时表。注适合频繁删除和更新
九、引擎使用
【1】使用INNODB引擎操作大批量数据时在过程结尾提交避免过度COMMIT。注通过事务提交可以提高大数据操作效率同时有序插入、合并插入也可以大幅提高数据库效率。
--正确语句
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);
COMMIT;【2】避免跨引擎操作如表分别为InnodB和MyISAM。问题语句不能用函数、需要有索引、先汇总再关联、要查汇总表、表引擎要一致、表编码要一致、字段类型要一致。注跨引擎操作时事务失效推荐都使用InnodB
十 、权限控制
【1】PHP连接MYSQL的用户只分配对应库SIUD。注权限越大被攻击时受到的破坏越大。注权限越大被攻击时受到的破坏越大 【2】所有客户端提交的变量都要进行转义操作防止非法注入。注可通过工具AppScan统一检测
-- 问题脚本
$username isset($_REQUEST[username]) ? $_REQUEST[username] : ;
$sql_ghselect EMPID,USERPASS,EMPSTATUS from ydserver.yd_cas_emp where EMPID$username;
