MySQL设计和开发规范
0.2
2013.06.27
文档版本历史
Ver. No. Ver. Date Revised By Description Reviewer Status
0.1 2013.05.09 DBA组 文档创建
0.2 2013.06.27 DBA组 文档修改uniq的索引命名
[编辑]Schema设计规范
[编辑]1.命名规则
// 代码规范方面的问题,不解释,自己理解。
数据库对象:库名,表名,字段等都严格必须小写命名;
// 同样规范问题不解释,话说如何避免这个问题引起的语法错误呢?
// 用反单引号引起变量名即可:
// 试着比较 create table sum(id int);
// create table `sum`(id int); 的区别,如果某人真不小心这样干了,你后续在
// 查询的时候也需要反单引,否则会遭遇 SQL Syntax Error
数据库对象名不能为MySQL的保留字;
// 小小的吐槽下,dbwww58com 是不是 too long 了?
数据库名以dbwww58com_+业务英语单词, 表名:t_ +业务单词;请尽量名字易懂简短;
[编辑]2.字段类型设计
// 能用数字型/专有类型就不要用字符串型,如 ipv4我们一般转换成 int 保存,
// 性别等非是即非的逻辑,建议采用 TINYINT 存储,而不是 CHAR(1)
// 这样省空间而且索引起来更高效,mysql 也内置了 ip 到十进制的 互映射函数
表示状态字段(0-255)的使用TINYINT UNSINGED ;
非负的数字类型字段,都添加上UNSINGED, 如可以使用INT UNSINGED字段存IPV4 ;
// 尽量使用TIMESTAMP类型,因为其存储空间只需要 DATETIME 类型的一半。
// 对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间
// 只需要3个字节,比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp
// 的值,因为这太不直观,会给维护带来不必要的麻烦,同时还不会带来任何好处。
时间字段使用时间日期类型,不要使用字符串类型存储,日期使用DATE类型,年使用YEAR类型,日期时间可使用DATETIME和TIMESTAMP;
// 定长字段,建议使用 CHAR 类型,不定长字段尽量使用 VARCHAR,且仅仅设定适当的最
//大长度,而不是非常随意的给一个很大的最大长度限定,因为不同的长度范围,MySQL也
//会有不一样的存储处理。注意如果你设置的 VARCHAR(N) 如果超过了 N,mysql 不会抛异
//常,只会给截断你的输入然后给一个隐式的 warning,当你在设计密码字段时一定要留意
//这个问题,否则你会感到莫名其妙~ 还有 VARCHAR(N) 如果超过了 64k,会被强制转换为
// Text,而且单行记录会有 64k的限制(只算varchar 等常见类型),超过会报错。
字符串VARCHAR(N), 其中N表示字符个数,请尽量减少N的大小 ;
小于64KiB的文本,请使用VARCHAR类型,不要使用TEXT类型;
6)字段尽量设置为NOT NULL, 为字段提供默认值,如’’和’0’ ;
NULL 类型比较特殊,SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异,会进入索引中,但如果是一个组合索引,那么这个NULL 类型的字段会极大影响整个索引的效率。此外,NULL 在索引中的处理也是特殊的,也会占用额外的存放空间。
很多人觉得 NULL 会节省一些空间,所以尽量让NULL来达到节省IO的目的,但是大部分时候这会适得其反,虽然空间上可能确实有一定节省,倒是带来了很多其他的优化问题,不但没有将IO量省下来,反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL,也是一个很好的表结构设计优化习惯。
7) 每个表必须有主键,且保持增长趋势的, 小型系统可以依赖于MySQL的自增主键,大型系统使用内置的ID生成器;
// 注意:innoDB 如果你不指定主键,主键默认就是一个内在的聚集key。所以最好就定义一个并且使用它。
[编辑]3.其他设计
// 虽然 utf-8 最耗空间,但为了数据交互的统一和后期编、解码的维护方便,最好统一使用u utf-8 编码,注意 mysql 的编码种类繁多:服务端编码、客户端编码、库、表、列编码等等
数据库创建时,指定字符集为utf8
CREATE DATABASE dbwww58com_xxx DEFAULT CHARACTER SET utf8;
表创建语句中,指定字符集为utf8
// 简单列下 MyISAM 和 innoDB 优劣:
InnoDB特点:
a) 支持ACID,简单地说就是支持事务完整性、一致性;
b) 支持行锁,以及类似ORACLE的一致性读,多用户并发;注意: InnoDB只有通过索引条件检索数据,才会用到行锁,否则将退化为表锁。
c) 独有的聚集索引主键设计方式,可大幅提升并发读写性能;
d) 支持外键;
e) 支持崩溃数据自修复;
从5.5.8版本开始,它已经成为了默认引擎,so 个人建议后续优先选择 InnoDB 引擎。
MyISAM适用场景及特点:
a) 不需要事务支持(不支持)
b) 并发相对较低(锁定机制问题)
c) 数据修改相对较少(阻塞问题)
d) 以读为主,主要面向一些OLAP/ETL 数据库应用
e) 数据一致性要求不是非常高
f) 占用资源少,支持并发插入,提升插入效率,单语句执行速度快,表级锁,降低死锁概率
g) 缓存区只会缓存索引,而不会缓存数据,这点与其它大多数引擎却别很大
so,如果确定只有insert/select, 应该选用MyISAM, 比如log表。
存储引擎使用INNODB;
表和每个字段都添加简短的comments
// 外键是最高效的一致性维护方法,由数据库维护级联更新和删除。
// 但外键只用于InnoDB, 而且不方便分表,所以不要太依赖这个特性。
禁用外键约束,由应用程序实现参照完整性。
[编辑]4.索引设计
// 龟腚,没啥好说的 。。。
索引名称以idx_列名命名,如果多列考虑列名缩写
唯一索引以uk_列名命名
索引占磁盘空间,不要重复的索引,尽量短
只给常用的查询条件加索引
//索引选择性是不重复的索引值也叫基数(cardinality)表中数据行数的比值,索引选择性=基数/数据行,基数可以通过“show index from 表名”查看。
高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行,唯一索引的选择性最佳,值为1。低cardinality 会导致 mysql 引擎执行计划后全表扫描,而不走索引,这是因为二叉树索引本来最适合的就是点查询,和小范围的range查询,当预估返回的数据量超过一定比例的时候,再根据索引一条一条去查就慢了,反而不如全表扫描快了。Mysql有自己内部自动优化机制,但有些自动优化机制可能不是最优的。这时候就需要人工去干预。
比如长期不优化表,Mysql判断出索引不优,就会不使用索引。
有时候就要人工强制使用真正高效的索引(FORCE INDEX)。
过滤性高的列建索引,取值范围固定的列不建索引
唯一的记录添加唯一索引
// 知道怎么快速 load 大量数据到 mysql 么?优化点之一先干掉索引,导入后再重建,道理一样一样的不解释,可以说 索引带来了查询效率的提升,他的劣势就是在每次 isnert/update 都需要重新平衡索引Tree带来效率的下降。
频繁更新的列不要建索引
// 恩,计算了就走不了索引了,把计算移到右边去
不要对索引列运算
// 索引占空间和影响效率的,so,有长度限制的,对于过长的字符型字段,可以只对其进行前缀索引。
同样过滤效果下,保持索引长度最小
//最左前缀前缀原则,这是由BTree这种数据结构决定的
合理利用组合索引,注意索引字段先后顺序
// 注意组合索引和多列索引的区别
多列组合索引,过滤性高的字段最前
// explain 看执行计划,对于 OLTP 应用来说 出现了 filesort 是不可接受的。
order by 字段建立索引,避免filesort
//最左前缀前缀原则,简单的说就是联合索引当中不能断了
例如:
索引idx(c1,c2,c3),相当于建立了idx(c1),idx(c1,c2)和idx(c1,c2,c3)三个索引。其它组合是没法走索引的,例如 (c1,c3)、(c2,c3),可以思考、实践下 (c2,c1,c3) 会走索引嘛?
组合索引,不同的排序顺序 ,不能使用索引
// 其实这本质上还是索引选择性的问题
<> != 无法使用索引
//覆盖索引(covering index),MySQL只需要通过索引就可以返回查询所需要的数据,而不必在查到索引之后再去查询数据,所以那是相当的快!但是同时也要求所查询的字 段必须被索引所覆盖到,在Explain的时候,输出的Extra信息中如果有“Using Index”,就表示这条查询使用了覆盖索引。
覆盖索引的示例 :
Create index index_name1 on table1(col2,col1,col3).
Select col1,col3 from table1 where col2 = 'value'.
so,建议大家别随便 select * from xxx
覆盖索引
// like‘%xx%’, 不符合前缀匹配的规则,因此用不上索引字段,只能作全表扫描。
但这也不是绝对的,select id from tb where title like ‘%abcd%’; 如果你这里是用覆盖索引那么是可以走索引的。
注意模糊匹配
说明:
1、索引用的好坏直接决定了数据库的性能,更多内容可以参考:
http://my.oschina.net/leejun2005/blog/73912
http://my.oschina.net/leejun2005/blog/134932
http://my.oschina.net/leejun2005/blog/133791
2、最后提下设计数据库时,应当根据当前数据量和增长趋势,结合业务来进行水平/垂直拆分,必要时可以空间换时间。
3、可以了解下常用的 MS 架构,要保证高可用的话可以考虑 MMM 等架构。
相关推荐
阿里巴巴MySQL开发规范是一套针对MySQL数据库设计、实现及优化的最佳实践指南。这套规范旨在提高数据库应用的稳定性、性能和可维护性,同时降低潜在的风险。规范主要分为三个部分:建库建表、创建索引、编写SQL语句...
《数据库设计开发规范-阿里.pdf》是一份由阿里巴巴云数据库服务部门编制的技术文档,旨在为数据库设计和开发提供一套全面且规范化的指导原则。该文档涵盖了Oracle和MySQL两大主流数据库系统的具体规范,并针对每种...
MySQL 设计规范是指在 MySQL 数据库设计和开发过程中需要遵守的一系列规则和标准,以确保数据库的稳定性、可靠性和高效性。该规范涵盖了数据库设计、表和字段命名、字段结构、SQL 语句、性能与效率、索引优化、查询...
MySQL作为一款广泛使用的开源关系型数据库管理系统,其开发规范包括了命名规则、SQL编写规范、性能优化、架构设计优化以及数据类型的选择等多个方面。 首先,在架构设计方面,80%以上的性能优化应该来源于对整体...
数据库设计与开发规范是软件开发过程中的重要环节,特别是在大型企业如阿里巴巴这样的环境中,严谨的数据库规范能够确保数据的一致性、稳定性和高效性。本规范主要针对MySQL,但也适用于Oracle等其他数据库系统。 1...
### 某行MySQL数据库开发规范 #### 一、规范的范围和目的 **1.1 规范的范围** 本规范主要涵盖MySQL数据库的SQL语句开发与性能优化、数据库用户及权限管理、数据库日常维护等内容。适用于招联消费金融有限公司内部...
MySQL开发规范是数据库管理和开发中的重要指南,尤其对于去哪网这样的在线旅游服务平台,数据的高效、稳定和安全至关重要。本规范旨在确保开发人员和运维人员在使用MySQL时遵循最佳实践,以提升系统的整体性能、可...
总的来说,阿里巴巴的数据库设计开发规范旨在提供一套系统化、标准化的方法,确保数据库系统的高效、稳定和安全,同时兼顾扩展性和维护性。对于任何数据库工程师来说,理解和遵循这些规范都是提升项目质量和效率的...
本文将详细介绍一份针对MySQL数据库设计的规范文档,该文档根据重要程度分为【高危】、【强制】和【建议】三个级别,旨在帮助技术人员做出最适合线上业务需求的设计。 #### 二、基础规范 **1. 使用InnoDB存储引擎*...
### MySQL Oracle 数据库开发设计及使用规范 #### 设计规范 **1. 数据设计原则** - **遵循3NF规范:** - 表中的每个值只能出现一次。 - 每一行都应通过唯一的键来标识。 - 表内不应包含依赖于其他键的非键信息...
Mysql数据库开发规范是一份针对数据库开发过程中的各项实践标准进行规范和指导的文档。该文档主要包括以下几个方面的知识点: 1. 引言部分:引言部分主要介绍了该规范的背景及目的、适用范围以及术语和缩略语。这些...
MySQL开发规范总结是针对数据库设计和管理的一套标准,旨在提升效率、标准化开发流程并方便数据库的统一管理。本规范适用于平安科技所有涉及MySQL的开发人员、DBA和运营人员。 1. 引言 - 背景与目的:随着业务的...
### MySQL开发与操作规范知识点详解 #### 一、目的与适用范围 ...通过遵循以上MySQL开发与操作规范,可以有效提升数据库系统的安全性、稳定性和性能,同时也有助于维护良好的团队协作环境和技术文档标准。
MySQL 规范是数据库设计和开发中非常重要的一部分,需要遵守相关的规范和标准,避免一些不良的设计和编程习惯,提高数据库的性能和可维护性。 本 MySQL 规范详解为读者提供了详细的 MySQL 规范知识,帮助读者更好...
PHP开发规范指南包含了项目开发规范、PHP编码规范、Mysql数据库设计规范、单元测试规范、异常日_standard
MySQL设计文档旨在提供一套规范,以确保在使用MySQL数据库时能充分发挥其优势,避免潜在问题。以下是基于给定内容的详细知识点: 一、数据库命令规范: 1. 数据库对象名称应使用小写字母和下划线,以符合lower_case...