MySQL DBA SQL开发最佳实践

所需积分/C币:10 2017-07-28 16:44:38 1.24MB PDF

某银行MySQL DBA SQL开发最佳实践
MySQL数据库开发规范 规范的范围和目的 1.1规范的范围 该规范主要包括 My SQL数据库的SQL语句的开发和性能优化、数据库用户和权限、 数据库日常管理的等内容 12规范的目的 通过该规范,统一各个项目工程SQL语句的设计编写、控制生产数据库的权限,以达 保证数据库相关的信息安全; 提高数据库运行的稳定性 提高数据库系统的数据处理效率; 减少出现低效SQL语句的可能性; 提高应用系统在不同数据库平台的可移植性; 提高SQL代码的可读性。 命名规范 命名规范是指数据库对象如数据库( DATABASE)、表( TABLE)、索引( INDEX) 约束( CONSTRAINTS)等的命名约定。 2.1数据库命名 命名尽量使用具有意义的英文词汇,词汇中间以下划线分隔; 命名只能使用英文字母、数字、下划线; 避免用 MYSQL的保留字如: call, group等; 所有数据库对象使用小写字母; 招联消费金融有限公司 MySQL数据库开发规范 22表命名 库名、表名、字段名必须见名知意,建议使用名词而不是动词,命名与业务、产品 线等相关联 库名、表名、字段名禁止使用 MySQL保留字。 库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传 输量,必须不超过32字符 >同一个模块的表尽可能使用相同的前缀,表名称尽可能表达含义。多个单词以下划 线分隔。 临时表、备份表保存在单独的备份库,命名规则:bak.表名_创建日期(如:bak 表名_1015),保留策略不超过1个月;备份表记录不超过100w,大小不超过10GB, 以先到为准。 2.3索引命名规范 >主键:pk表名; > UNIQUE索引:uk表名_字段名(或字段缩写); 索引:非唯一索idx表名_字段名(或字段缩写); 复合索引:idx表名_字段缩写,取尽可能代表意义的缩写,如 business contract 表 customerid和 customername上的索引: idx business contract cid cnames 24脚本文件命名规范 执行序号_数据库_脚本类型业务系统名称开发人员名简称sq1,如 01_loandb_ddl_-cls-core_ hjt. sql, 02_mysqllg_ddl_cls-channel_hzc sql 执行序号:DBA按照序号执行,尤其有先后顺序的,必须注意每批脚本序号唯 业务系统名称:业务系统名称,如:cls-core、cls- channel、cls-cs 数据库:数据库类型,如: oracle数据库写成 oracle的SID,比如微信用 credit mysq1数据库写成“ mysql”,如mysq119”,数据库的IP:10.77.64.19。 脚本类型:ddl-表结构、索引、视图、过程等变更,dml-新增、删除、修改等数 据更新脚本 开发人员简称:开发人员姓名首字母,脚本发布发生问题,方便DBA联系 25脚本书写规范 脚本带属主:脚本所有部分都应包含对象属主或者 schema,如 create table< owner 招联消费金融有限公司 MySQL数据库开发规范 schema>, table name XX 脚本分段提交:对于DML操作脚本超过10000行的,要分开多个脚本,每个脚本分 开提交; insert语句写成 insert into xxx0,0…形式; 脚本执行说明:对于有前后依赖的脚本应明确说明依赖关系; 脚本开始位置须使用 use db name子句指定操作数据库; 禁止在任何脚本中加入创建用户、増加授权之类语句,新建存储过程必须提申请给所需 用户增加权限; 对于对象删除或者数据大量修改的操作,建议操作前备份数据,备份方法 create table bak table name 151010 as select k from <database> table name 存储过程、函数、触发器等须使用 DELIMITER直接开始和结束分隔符 尽量避免在线对业务表做DDL操作,如果必须要做尽量选择非业务高峰时间段,并把 同一个表的多个DDL操作合并成一个命令。 错误写法: ALTER TABLE crm mng. ol call rec Add column attr2 VARCHAR(3000) COMMENT备用字段2 ALTER TABLE crm mng. ol call rec ADD COLUMN r_couty_ nm VARCHAR(50) COMMENT地区名称 正确写法: ALTER TABLE crm mng. oI call rec ADD column attr2 VARCHAR3000 COMMENT备用字段 2 adr couty nm VARCHAR(50) COMMENT地区名称; 数据库设计规范 4.1存储引擎的选择 My SQL支持数个存储引擎作为对不同表的类型的处理器, My SQL中的插件式存储引 擎架构是非常有特色的亮点。当前只能使用 innodb存储引擎,该引擎为5.6版本中的默认 存储引擎 MySQL引擎 说明 InnoDB 索引和数据都可以缓存到内存中; 支持事务; 支持行级锁,可实现更高的并发度; 支持故障恢复; 支持外键约束; 支持4种不同的事务隔离级别; 42字符集的选择 对于没有特殊要求的系统字符集一律选择utf8,排序集 collation: utf8_general_ci 招联消费金融有限公司 MySQL数据库开发规范 外购系统、和其它系统没有数据交叉且厂商明确不支持υtf8字符集的系统可按厂 商要求选择合适字符集; MYSQL配置参数 lower case table names=1,不可动态更改, linux系统默认 为O,即库表名以实际情况存储,大小写敏感。如果是1,以小写存储,大小写不敏 感。如果是2,以实际情况存储,但以小写比较。 4.3表设计规范 表必须要有PK,最好是以白增id为主键; 一个字段只表示一个含义,设计应至少满足第三范式,尽量减少数据冗余; 单表字段数不要太多,建议最多不要超过50个; 禁止使用复杂数据类型(数组,自定义等) MySQL在处理大表(char的表>1000W行,或int表>1000W)时,性能就开 始明显降低,需要定期删除(或者转移)过期数据的表:通过表分区、分库、分表解决。 INT:只需要关注数据类型的大小范围,选择合理的类型,不需要指定int(size), 不需要关注字节大小,必要时指定 unsigned 自增字段类型必须是整型,推荐类型为INT或 BIGINT,并且自增字段必须是主键 或者主键的一部分。 VARCHAR(N)甲的N,指的是字符数,而非字节数;无论存储数字、字母、中文 只跟数量挂钩,具体存储字节数要看编码。根据实际的数据长度来选择N,N尽可 能小,因为 mysql进行排序和创建临时表一类的内存操作时,会使用N的长度申 请内存。没有特殊需要统一使用 varchar类型。 口期:只需要精确到天的字段全部使用DATE类型,而不应该使用 TIMESTAMP 或者 DATETIME类型; 般米说所有需要精确到时间(时分秒)的字段使用 DATETIME,虽然索引性能 TIMESTAMP会优于 DATETIME,但是除非已经特别熟悉 TIMESTAMP类型的 默认值、时区跟随、自动更新、范围限制等特性,否则一般不建议使用; 对于精确浮点型数据存储,例如金额,需要使用 DECIMAL,禁止使用 FLOAT和 DOUBLE 字段建议使用 not nu属性,可用默认值代替nul; 仅仅当字符数量可能超过20000个的时候,才可以使用TEXT类型来存放字符类 数据,建议所有使用TEXT类型的字段和原表进行分拆,与原表主健单独组成另外 个表进行存放; 建议对部分可以归档的数据增加 update da净德 不建议使用ENUM、SET类型,使用 TINYINT来代 禁止在数据库中存储明文密码,把密码加密后存储, 招联消费金融有限公司 MySQL数据库开发规范 4.4索引设计规范 3.41 MYSOL几种索引 聚簇索引是一种数据存储方式,它实际上是在同一个结构中保存了B+树索引和数据行 Innode表是按照聚簇索引组织的(类似于 Oracle的索引组织表)。 InnodB通过主键 聚簇数据,如果没有定义主键,会选择一个唯一的非空索引代替,如果没有这样的索引, 会隐式定义个主键作为聚簇索引。 下图形象说明了聚簇索引表( InnodB)和非聚簇索引( MVISAM)的区别: Primary ke Primary key Secondary key \写、\ 中中 玄\\\互 InnoDB (clustered)table layout My ISAM (nonclustered table layout 对于非聚簇索引表来说(右图),表数据和索引是分成存储的,主键索引和二级索引存 储上没有任何区别 而对于聚簇索引表来说(左图),表数据是和主键一·起存储的,主键索引的叶结点存储 行数据,二级索引的叶结点存储行的主键值。 聚簇索引表最大限度地提高了I/O密集型应用的性能,但它也有以下几个限制 插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分 裂,严重影响性能。因此,对于 InnodB表,我们一般都会定义一个自增的ID列为主 键 更新主键的代价很高,因为将会导致被更新的行移动。因此,对于 InnodB表,我们 般定义主键为不可更新。 二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据 二级索引的叶节点存储的是主键值,而不是行指针(非聚簇索引存储的是指针或者说是 招联消费金融有限公司 MySQL数据库开发规范 地址),这是为了减少当出现行移动或数据页分裂时二级索引的维护工作,但会让二级 索引占用更多的空间。 非聚簇索引: secondary index,叶子节点保存了主键值,要定位记录还要再查一遍 聚簇索引。 攪盖索引:是指索引的叶子节点已包含所有要查询的列,因此不需要访问表数据,能极 大地提高性能。覆盖索引对 InnodB的聚簇索引表特别有用,因为可以避免 nnoDE二级 索引的二次查询。 342 MySQL索引规 PK应该是有序并且无意义的字段且尽可能短,可使用自增序列; 不使用更新频繁的列作为主键; 单张表的索引数量控制在5个以内; 索引中的字段数建议不超过5个; 惟一键由3个以下字段组成; 索引的首字段,必须在 where条件中; 禁止使用外键,太容易产生死锁,应由程序保证约束; 对于确定需要组成组合索引的多个字段,建议将选择性高的字段靠前放; 避免重复索引、多余索引; 合理使用覆盖索引减少IO 四、SQL开发规范 MySQL数据库中关键业务的SQL语句应尽可能简单;所有业务逻辑的SQL必须使用 索引访问数据,最好使用主键或唯一键;在所有 Query的 Where条件中必须使用和字段 完全一致的数据类型,杜绝任何隐式类型转换;业务逻辑中避免使用触发器、函数、自定义 函数、存储过程、视图。 SQL语句应遵循以下基本规范: 如设置隔离级别为RC(), select语句之后必须加上 commit 将多表关联的SQL语句拆分成多条简单的SQL语句,最多只允许2张表进行关 联查询。 避免使用不等值查询,例如:NOT、=、<>、!<、!>、 NOT EXISTS、 NOT IN NOT LIKE等。 事务/连接使用原则:即开即用,用完即关。 与事务无关操作放到事务外面,减少锁资源的占用。 在不破坏一致性前提下,建议使用多个短事务代替长事务 因为 GROUP BY有分组和自动排序的功能,在无需排序的业务逻辑场景下,使用 Order by null避免排序。 >进行大批量操作时必须分批提交,每次数据量操作不能超过10W条。 招联消费金融有限公司 MySQL数据库开发规范 last insert id0函数只能返回当前 session最近一次 insert操作之后所使用到的 auto_ increment类型字段的值,且使用" select last insert_id0",不要再跟一个 from table name WHERE条件中不在索引列上进行数学运算或函数运算; 禁止使用%前缀进行模糊前缀查询; 使用 prepared statement,可以提高性能并且避免SQL注入 为避免出现误删数据,禁止使用 replace语句,改成相应的 delete和 insert组合; 永远不在程序端使用 LOCK TABLE语句加锁;仅允许使用 SELECT*….FOR UPDATE Where…语句 同一字段,将or改写为in0,且注意控制IN的个数,建议n小于300 不同字段,将Or改为 union,前提是使用or时查询结果集不会有重复记录。 若无需对结果进行去重,则用 UNION ALL代替 UNION,因为 UNION有去重开 销 禁止使用 order by rand( SQL中避免出现now(、 sysdate()、 cuurrent user()等不确定结果的函 数 5.1 select*使用规范 4.L.1基本原则 禁止使用 select*进行查询,但是统计表中记录数时使用 COUNT约),而不是 CoUntiprimary key和 COUNT(1)。因为 count(),白动会优化指定到那一个字段。所以 没必要去 count(?),用 count(),sq1会帮你完成优化的, count()将返回表格中所有存在 的行的总数包括值为nul的行,然而 count(列名)将返回表格中除去null以外的所有行的 总数(有默认值的列也会被计入)。 4.1.2并细说明 Select x会增加很多不必要的消耗,尤其IO,而且由于查询的所有列都包含在索引中 时才会使用覆盖索引,因此 select*会降低覆盖索引的使用。 进行 Order By操作时 My SQL有两种算法,种是直接取出所有需要返回字段 elect 后面的字段),存入内存中,然后排序(仅有需要排序的字段需要参亐)。第二种是先取出需要排 序的字段,然后排序,再回表中取出其他的字段就相当于所有数据都有两次磁盘IO。如果 select后面的字段长度总和超过1024字节(即参数 max_length_for_sort_data的默认值) 或者字段中包括BLOB、TEⅩT字段,都将会使用第二种算法。所以 order by的查询不允 许使用 select*; oin语句使用 select*可能导致只需要访问索引即可完成的查询需要回表取数,所以禁 止使用。 mysql> explain SEL ECIt. 1. a, t.2. aFROM t 1 IFFT JoIN t 2 ON t 1.a=t2.a I idI select type table I type possible kays key I key len ref 招联消费金融有限公司 MySQL数据库开发规范 I1 SIMPLE tI index NULL 5 Using inde I 1 SIMPLE t2 ref idx t2 aI idxt2a3 sbtestl.tIE 1 Using index Mysql> explain SELECT *k FROM tI LEFT OIN t2 oN tl. a =t2.a I idI select type table I type possible kays key key len I ref rowsIExtra 1 SIMPLE I NULLNULLNULL 5 NULL I II SIMPLE NULL NULL NLLL 5 Usirg where; LIsing join buffer (Block Nes ted Lccp 52字段上使用函数规范 4.2.1基本原网 必要时可以在某些列上使用相关函数,但是在 Where子句中的谓词表达式左边严禁使 用任何函数,包括数据类型转换函数。 4.2.2并细说明 错误的写法: select createtime where date format. (createtime,%Y%m%d %H: %i: %s)-'20090101 00: 00: 0 正确的写法: select date format(createtinc,'%7%m%d %H: %i: %s) fron where creatLetime =sr tn daLe (20090101 00: 00: 00,' %Y%u%1 %H: %i:s') 5.3表连接规范 企业版 mysql会自动根据统计信息优化表关联中驱动表的选择,一般选择行源的表做 驱动表,在关联时 My SQL最主要的表关联方式是嵌套循环 招联消费金融有限公司

...展开详情
试读 18P MySQL DBA SQL开发最佳实践
img

关注 私信 TA的资源

上传资源赚积分,得勋章
    最新推荐
    MySQL DBA SQL开发最佳实践 10积分/C币 立即下载
    1/18
    MySQL DBA SQL开发最佳实践第1页
    MySQL DBA SQL开发最佳实践第2页
    MySQL DBA SQL开发最佳实践第3页
    MySQL DBA SQL开发最佳实践第4页
    MySQL DBA SQL开发最佳实践第5页
    MySQL DBA SQL开发最佳实践第6页

    试读已结束,剩余12页未读...

    10积分/C币 立即下载 >