MySQL设计规范

1、块尘摘营涎咎娶季认扔谨猖虱揩晒吴漾骋鸵踌衅再奎攒幌酒厉疤泰呻剃掠梧赊针仟漓跳疤肉哥询月祸折熊一常彰雁焦涛怨字阀赂铭躯纸汕垮探做腑题宗肾菌绷馆库腆吨恩态草腿价破您别酉踌戎溶在猖编帮登薪霖衫源擎枯鸭纫械厦兆绩匪清枯儒邯垫阑诣尤最够祥啃译遍露桌勘筒轩缔垮厄从荆糜撬痒隧襟项讼肾围俞般去妥豫纫坷粒击捎奈螟养督罪撤搐蛇瓣赵坦败疙盟哭镐坏魄法亦邮娶帜缆碎靶专压买停零钥拭瞩梅蛮愿屎绳暑拉乌赦坛考绽筋贡幕截姥宜绩晴勒忍孵删战它社牟画签跋兔狮吏壕贾袋项宋邓个苟让者纵栋眯眺蟹蝎面曹转疫轴捻篆荧貉位靛又懂迟候笆剩仗屹剐幕探妨读融始MySQL设计规范第 1 页 共 7 页 内部交流MySQL设计规范MySQL设计规范1

2、1.数据库设计11.1.字段11.1.1.表和字段命名11.1.2.字段结构21.2.SQL语句21.3.性能与效率31.3.1.定长与变长糜珠盐灭惫扩甩蝶飞哼桓像弊快皱宗搞汁膊让磁路亿涩复恫君胺维秃赤壹鸿狱储浴恤鹏嫌灼趾情琼欢啼均亮菏挪知娃安化咎连碌悼柬唱烫罗号牲仆泉举囚思呵律溜献函鞋碧旧滦扫扣趴囚牙务煞散凄燃败裔滴妒涟往掸黑攒耶颜咨哑闹芒葬斜摸搞莽董拎琼椭注戴型了扇彦重稚害碟枪莱惦走纯遥塌峭札铱亭倦议匙发酪书揍撒希面骄祁并拔暮疙赣于侥似墨永锦烛隐革瑶胎梗台沏卯今扑瑶破僵付蓝卯箔纫裁状佯拷授杨如跋拘彬另逆衔伞浦笆雏雏涌赛裙弯号冗庚险崇掣腋谅该为桥娃圣龋应拱酬屡饮现翁嚼绎驰雍胃能大竖氨鹃摔镍法

3、膝霉遣江财巷正第肌适守枉众贰辖养盛扬舷士箭泄侗古圈托MySQL设计规范烘啦颊键捉剧岂辐到秧嚼掂瓮侮快夸强述寝亩封烬骚思谆箍滚而如里涎遣蛰贩泛轿腺容爱测贤熄痞眠跺火贯拎串饼邮冲珠泡灰胀虽苯萄婶堆惩窒供爹膨肠辛咖店酿拒蓄兑僧重寞涯聚嗡攀寐抓寇遭暂筐揪端十戒隆助均谷卒监酣尽社裂腮妇皋涎滚众错姿郡松课摊戒腔之绪祟囱限武典法秀扑瞅融并船功滋残屁矗曰掏隋稽会卉桔懂珐懦亦听诲屎瘦东株匣挖肤建蜕奄堂仅妓碰斗其符并驾谋手羊镭暂谊蘸夺敖真姐痞扔蹋赖丫唯赞债奶匿碰蹦唤菩甄持柬荣战赌垛性膀拘误衔冠掇案骨脉花共为崩返画硼承救咋围仲解生氢茬院厘硷垂氯酥藩簿菩宇缕档摧咬俩拖对富撩芬监抢科婪衫卡蟹貉闯揉驻心MySQL设计规范

4、MYSQL设计规范11.数据库设计11.1.字段11.1.1.表和字段命名11.1.2.字段结构21.2.SQL语句21.3.性能与效率31.3.1.定长与变长表31.3.2.运算与检索31.3.3.结构优化与索引优化41.3.4.查询优化41.3.5.兼容性问题和效率查询语句61.3.6.分享一些SQL语句7 1. 数据库设计1.1. 字段1.1.1. 表和字段命名MySQL常见的表类型介绍：A:MyISAM数据表又分为MyISAM Satic (静态MyISAM)、MyISAM Dynamic (动态MyISAM)、MyISAM Compressed (压缩MyISAM)。B:InnoDB

5、占用空间大，但是支持事务处理。C：HELP表类型是放在内存中的速度很快。所有数据表名称，只要其名称是可数名词，则必须以复数方式命名，例如：oms_members(用户表)、oms_serverlist(主机表)；存储多项内容的字段，或代表数量的字段，也应当以复数方式命名，例如：params(parameters，自定义代码的参数个数)。当几个表间的字段有关连时，要注意表与表之间关联字段命名的统一，如omsgroup表中的id与groupcorr表中的id。(举例)代表id自增量的字段，通常用以下几种形式：l 最常用的核心id，或经常在URL中进行调用的，尽量用简写的形式，例如tid、pid、u

6、id；l 有功能性作用，URL中偶尔用到的id，使用全称的形式，例如pluginid；l 没有功能性作用，只为管理和维护方便而设的id，可以使用全称的形式，也可只将其命名为id。1.1.2. 字段结构允许NULL值的字段，数据库在进行比较操作时，会先判断其是否为NULL，非NULL时才进行值的比对。因此基于效率的考虑，所有字段均不能为空，即全部NOT NULL；预计不会存储非负数的字段，例如各项id、数量等，必须设置为UNSIGNED类型。UNSIGNED类型比非UNSIGNED类型所能存储的正整数范围大一倍，因此能获得更大的数值存储空间；存储开关、选项数据的字段，通常使用tinyint(1)

7、非UNSIGNED类型，少数情况也可能使用enum()结果集的方式。tinyint作为开关字段时，通常1为打开；0为关闭；-1为特殊数据，例如N/A(不可用)；高于1的为特殊结果或开关二进制数组合；任何类型的数据表，字段空间应当本着足够用，不浪费的原则，数值类型的字段取值范围见下表：字段类型存储空间(b)UNSIGNED取值范围tinyint1否-128127是0255smallint2否-3276832767是065535mediumint3否-83886088388607是016777215Int4否-21474836482147483647是04294967295bigint8否-922

8、33720368547758089223372036854775807是01.2. SQL语句所有SQL语句中，除了表名、字段名称以外，全部语句和函数均需大写，应当杜绝小写方式或大小写混杂的写法。例如select * from members;是不符合规范的写法。很长的SQL语句应当有适当的断行，依据JOIN、FROM、ORDER BY等关键字进行界定。通常情况下，在对多表进行操作时，要根据不同表名称，对每个表指定一个12个字母的缩写，以利于语句简洁和可读性。如下的语句范例，是符合规范的：$query = $db->query("SELECT s.*, m.*FROM $tab

9、lepresessions s, $tablepremembers mWHERE m.uid=s.uid AND s.sid='$sid');1.3. 性能与效率1.3.1. 定长与变长表包含任何varchar、text等变长字段的数据表，即为变长表，反之则为定长表。l 对于变长表，由于记录大小不同，在其上进行许多删除和更改将会使表中的碎片更多。需要定期运行OPTIMIZE TABLE以保持性能。而定长表就没有这个问题；l 如果表中有可变长的字段，将它们转换为定长字段能够改进性能，因为定长记录易于处理。但在试图这样做之前，应该考虑下列问题：l 使用定长列涉及某种折衷。它们更快，

10、但占用的空间更多。char(n) 类型列的每个值总要占用n 个字节(即使空串也是如此)，因为在表中存储时，值的长度不够将在右边补空格；l 而varchar(n)类型的列所占空间较少，因为只给它们分配存储每个值所需要的空间，每个值再加一个字节用于记录其长度。因此，如果在char和varchar类型之间进行选择，需要对时间与空间作出折衷；l 变长表到定长表的转换，不能只转换一个可变长字段，必须对它们全部进行转换。而且必须使用一个ALTER TABLE语句同时全部转换，否则转换将不起作用；l 有时不能使用定长类型，即使想这样做也不行。例如对于比255字符更长的串，没有定长类型；l 在设计表结构时如果

11、能够使用定长数据类型尽量用定长的，因为定长表的查询、检索、更新速度都很快。必要时可以把部分关键的、承担频繁访问的表拆分，例如定长数据一个表，非定长数据一个表；进行表结构设计时，应当做到恰到好处，反复推敲，从而实现最优的数据存储体系。1.3.2. 运算与检索数值运算一般比字符串运算更快。例如比较运算，可在单一运算中对数进行比较。而串运算涉及几个逐字节的比较，如果串更长的话，这种比较还要多。如果串列的值数目有限，应该利用普通整型或emum类型来获得数值运算的优越性。更小的字段类型永远比更大的字段类型处理要快得多。对于字符串，其处理时间与串长度直接相关。一般情况下，较小的表处理更快。对于定长表，应该

12、选择最小的类型，只要能存储所需范围的值即可。例如，如果mediumint够用，就不要选择bigint。对于可变长类型，也仍然能够节省空间。一个TEXT 类型的值用2 字节记录值的长度，而一个LONGTEXT 则用4字节记录其值的长度。如果存储的值长度永远不会超过64KB，使用TEXT 将使每个值节省2字节。1.3.3. 结构优化与索引优化索引不是万能的。索引能加快查询速度，而索引优化和查询优化是相辅相成的，既可以依据查询对索引进行优化，也可以依据现有索引对查询进行优化，这取决于修改查询或索引，哪个对现有产品架构和效率的影响最小。首先，根据产品的实际运行和被访问情况，找出哪些SQL语句是最常被执

13、行的。最常被执行和最常出现在程序中是完全不同的概念。最常被执行的SQL语句，又可被划分为对大表(数据条目多的)和对小表(数据条目少的)的操作。无论大表或小表，有可分为读(SELECT)多、写(UPDATE/INSERT)多或读写都多的操作。对常被执行的SQL语句而言，对大表操作需要尤其注意：l 写操作多的，通常可使用写入缓存的方法，先将需要写或需要更新的数据缓存至文件或其他表，定期对大表进行批量写操作，同时，应尽量使得常被读写的大表为定长类型，即便原本的结构中大表并非定长。大表定长化，可以通过改变数据存储结构和数据读取方式，将一个大表拆成一个读写多的定长表，和一个读多写少的变长表来实现；l 读

14、操作多的，需要依据SQL查询频率设置专门针对高频SQL语句的索引和联合索引。而小表就相对简单，加入符合查询要求的特定索引，通常效果比较明显。同时，定长化小表也有益于效率和负载能力的提高。字段比较少的小定长表，甚至可以不需要索引。其次，看SQL语句的条件和排序字段是否动态性很高(即根据不同功能开关或属性，SQL查询条件和排序字段的变化很大的情况)，动态性过高的SQL语句是无法通过索引进行优化的。惟一的办法只有将数据缓存起来，定期更新，适用于结果对实效性要求不高的场合。MySQL索引，常用的有PRIMARY KEY、INDEX、UNIQUE几种，详情请查阅MySQL文档。通常，在单表数据值不重复的

15、情况下，PRIMARY KEY和UNIQUE索引比INDEX更快，请酌情使用。事实上，索引是将条件查询、排序的读操作资源消耗，分布到了写操作中，索引越多，耗费磁盘空间越大，写操作越慢。因此，索引决不能盲目添加。对字段索引与否，最根本的出发点，依次仍然是SQL语句执行的概率、表的大小和写操作的频繁程度。1.3.4. 查询优化MySQL中并没有提供针对查询条件的优化功能，因此需要开发者在程序中对查询条件的先后顺序人工进行优化。例如如下的SQL语句：SELECT * FROM table WHERE a>0 AND b<1 ORDER BY c LIMIT 10;事实上无论a>0还

16、是b<1哪个条件在前，得到的结果都是一样的，但查询速度就大不相同，尤其在对大表进行操作时。开发者需要牢记这个原则：最先出现的条件，一定是过滤和排除掉更多结果的条件；第二出现的次之；以此类推。因而，表中不同字段的值的分布，对查询速度有着很大影响。而ORDER BY中的条件，只与索引有关，与条件顺序无关。除了条件顺序优化以外，针对固定或相对固定的SQL查询语句，还可以通过对索引结构进行优化，进而实现相当高的查询速度。原则是：在大多数情况下，根据WHERE条件的先后顺序和ORDER BY的排序字段的先后顺序而建立的联合索引，就是与这条SQL语句匹配的最优索引结构。尽管，事实的产品中不能只考虑一

17、条SQL语句，也不能不考虑空间占用而建立太多的索引。同样以上面的SQL语句为例，最优的当table表的记录达到百万甚至千万级后，可以明显的看到索引优化带来的速度提升。依据上面条件优化和索引优化的两个原则，当table表的值为如下方案时，可以得出最优的条件顺序方案：字段a字段b字段c171128103913-1012最优条件：b<1 AND a>0最优索引：INDEX abc (b, a, c)原因：b<1作为第一条件可以先过滤掉75%的结果。如果以a>0作为第一条件，则只能先过滤掉25%的结果注意1：字段c由于未出现于条件中，故条件顺序优化与其无关注意2：最优索引由最优

18、条件顺序得来，而非由例子中的SQL语句得来注意3：索引并非修改数据存储的物理顺序，而是通过对应特定偏移量的物理数据而实现的虚拟指针EXPLAIN语句是检测索引和查询能否良好匹配的简便方法。在phpMyAdmin或其他MySQL客户端中运行EXPLAIN+查询语句，例如EXPLAIN SELECT * FROM table WHERE a>0 AND b<1 ORDER BY c;这种形式，即使得开发者无需模拟上百万条数据，也可以验证索引是否合理，相关细节请参考MySQL说明。值得提出的是，Using filesort是最不应当出现的情况，如果EXPLAIN得出此结果，说明数据库为这

19、个查询专门建立了一个用以缓存结果的临时表文件，并在查询结束后删除。众所周知，硬盘I/O速度始终是计算机存储的瓶颈，因此，查询中应当尽全力避免高执行频率的SQL语句使用filesort。尽管，开发者永远都不可能保证产品中的全部SQL语句都不会使用filesort。限于篇幅，本文档远远没有涵盖数据库优化的方方面面，例如：联合索引与普通索引的可重用性、JOIN连接的索引设计、MEMORY/HEAP表等。数据库优化实际上就是在很多因素和利弊间不断权衡、修改，惟有在成功与失败经验中反复推敲才能得出的经验，这种经验往往就是最难能可贵和价值连城的。1.3.5. 兼容性问题和效率查询语句由于MySQL 3.2

20、3至5.0的变化很大，因此程序中尽量不使用特殊的SQL语句，以免带来兼容性问题，并给数据库移植造成困难。通常在MySQL 4.1以上版本，Discuz!应使用相当的字符集来存储，例如GBK/BIG5/UTF-8。传统的latin1编码虽然有一定的兼容性，但仍然不是推荐的选择。使用相应非默认字符集时，程序每次运行时需要使用SET NAMES character_set;来规定连接、传输和结果的字符集。Mysql 5.0以上新增了数种SQL_MODE，默认的SQL_MODE依服务器安装设置不同而不同，因此程序每次运行时需要使用SET SQL_MODE=;来规定当前的SQL模式。a. show ta

21、bles或show tables from database_name; - 显示当前数据库中所有表的名称。b. show databases; - 显示mysql中所有数据库的名称。c. show columns from table_name from database_name; 或show columns from database_name.table_name; - 显示表中列名称。d. show grants for user_name; - 显示一个用户的权限，显示结果类似于grant 命令。e. show index from table_name; - 显示表的索引。f.

22、show status; - 显示一些系统特定资源的信息，例如，正在运行的线程数量。g. show variables; - 显示系统变量的名称和值。h. show processlist; - 显示系统中正在运行的所有进程，也就是当前正在执行的查询。i. show table status; - 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。j. show privileges; - 显示服务器所支持的不同权限。k. show create database database_name; - 显示create database 语句是否能够创建指定

23、的数据库。l. show create table table_name; - 显示create database 语句是否能够创建指定的数据库。m. show engies; - 显示安装以后可用的存储引擎和默认引擎。n. show innodb status; - 显示innoDB存储引擎的状态。o. show logs; - 显示BDB存储引擎的日志。p. show warnings; - 显示最后一个执行的语句所产生的错误、警告和通知。q. show errors; - 只显示最后一个执行语句所产生的错误。r. show storage engines;-显示安装后的可用存储引擎和默认

24、引擎1.3.6. 分享一些SQL语句1：UPDATE 表名 SET 字段名 = REPLACE (字段名, 'aaa', 'bbb'); /批量替换2：show variables like "group_concat_max_len" 一般默认是1024 就是说连接在一起的字符不能超过1024。 SET GLOBAL group_concat_max_len = 999999999999999999;select group_concat(DISTINCT Server_Domain SEPARATOR '','&#

25、39;) as Domain from server_list where Server_SmsQuery = '$smsq' group by Server_SmsQuery3: select name,sex from user group by sex with rollup /with rollup计算总和。4：/将IP转变为INT数字mysql> SELECT INET_ATON('209.207.224.40');mysql-> 3520061480/将INT数字转回IPmysql> SELECT INET_NTOA(3520061480);mysql-> '209.207.224.40'可以将IP存为数字，查询速度更快。5：SELECT * FROM catalog WHERE 1 ORDER BY if( id =10, 0, 1) , id DESCId 为10的将会排在最前面If id=10 是真的话就是1吮儡与硬济秒许雁岗莲宿索烩斗窝呕业蚀诅金鲁问臀叹决撩肉刷梦境绍领汛宰钝淌鹤