编 写：袁 亮
时 间：2014-10-30
说 明：Mysql使用过程中的一些规范，tech4内部试行版
一.说明
1.1 不管是新入职的实习生，还是工作多年的老油条，都需要遵守
1.2 里面有些可能不是那么合理，如果觉得有疑问，可以随时找我沟通讨论
1.3 所有的规定都是基于某些原因考虑的，只是这边没有说的太仔细
1.4 过早优化、过渡优化都是一种错误，不要在一个小项目里，过渡的使用各种优化
二.数据库设置选择
2.1 统一字符集：utf8
2.1.1 校对规则：utf8_general_ci
2.1.2 建库和建表的时候必须选，建表的时候选是因为有些老库没有设置该字符集
2.1.3 乱码：SET NAMES UTF8 (这个是因为有很多老坑，否则这个是应该去掉，很影响性能)，程序也要设置utf8
2.1.4 如果不想在程序段处理emoj等表情，可以使用UTF8MB4字符集做兼容(5.5.3版本以后)
2.2 mysql版本最好是5.1 或者5.5以后的版本
2.2.1 我们新的都已经是5之后了，只有几个非常老的项目，还是4.几的版本
2.3 引擎的选择
2.3.1 大大小小10几种引擎，但我们常用的就Myisam和InnoDB
2.3.2 mysql 5以后的版本，默认引擎用InnoDB
2.3.3 InnoDB的效率，根据我看网上各家的评测结果，应该是全面占优了，之前一直理解是Myisam读性能强于InnoDB
2.3.4 我们的老项目，基本上都是Myisam，新项目尽量使用InnoDB
2.4 程序连接数据库的账号权限
2.4.1 指定ip连接(内网或者指定ip)
2.4.2 主库给增、删、改、查权限
2.4.3 从库只需要查的权限即可，禁止给全部权限
三.字段介绍选择
3.1 常用字段：
3.1.1 tinyint:0-255，枚举类型，性别等等，均以tinyint来
3.1.2 int:一般的整型均用该类型，int不要设置长度，设不设置，实际存储都一样
3.1.3 decimal:存储确定精度的浮点数时，不要用float和double，M是所有数字的个数，D是小数点后面的个数
3.1.4 char:定长字符串，性能较高，存储md5后的值之类的非常适合
3.1.5 varchar:变长字符串，非常常用，设置适合长度的(过长不会影响使用空间，但会影响内存使用)
3.1.6 date:存储日期，生日之类的
3.1.7 datetime:具体时间，精确到秒，不推荐timestamp，查看太麻烦了，性能还可以
3.1.8 text:文本类型，最好分单独的表
3.1.9 longtext:长文本，存储富文本编辑器中的内容，单独分表存储
3.1 常用类型：
3.2.1 主键：id int 不要设置长度
3.2.2 用户id:user_id int
3.2.3 用户名：username varchar(30) 根据用户名规则长度
3.2.4 昵称：nickname varchar(30) 根据注册规则定
3.2.5 Ip地址：ip varchar(15)
3.2.6 记录时间：dated datetime
3.2.7 修改时间：update_dated datetime
3.2.8 生日：birthday date
3.2.9 手机号：mobile char(11) 如果要兼容国内外的和座机，可以用varchar
3.2.10 链接地址：url varchar(255)
3.2.11 标题：title varchar(255)
3.2.12 图片地址：pic varchar(255)
3.2.13 描述：describe varchar(255)
3.2.14 内容：content text 分到单独的表中
3.2.15 状态：state tinyint 每个值对应什么含义，在数据字典里标明
3.2.16 类型：types tinyint 同状态
3.2.17 是否显示：is_show tinyint 0:不线上，1:显示，不要反过来
3.1 字段设计的几点禁忌
3.3.1 每张表，必须加两个字段，一个id，自增长主键，一个dated，这条记录的生成时间，不管你有没有用到，必须加
3.3.2 不要使用枚举类型或者set，使用tinyint来代替，否则数据量上去之后，想改就是个悲剧
3.3.3 所有字段必须是not null，并且默认值不要给null
3.3.4 text或者longtext的拆分到单独的表中存储，不要跟主逻辑表混一起
3.3.5 图片等不允许用blob等存储图片内容，只存图片地址
3.3.6 密码不允许明文存储，MD5(密码+定义的字符串密钥)
3.3.7 单张表，字段数不要超过15个，超过的话，考虑拆分成多张表，例如：
主表：posts:id,user_id,title,....,dated
附属表，不常用字段 post_details:id,post_id,describe,...,dated
大文本内容： post_contents:id,post_id,content,dated
通过post_id来做关联，将最常用的字段放到主表里，不怎么用的字段放到detail表，长文本content单独一张表
四.命名规则
4.1 库、表、字段、索引名等：只允许小写字母、_ 、数字组成，命名不要超过20个字符
4.2 索引命名：idx_字段名_字段名 (太长的时候，对字段名采用简写)
4.3 避免保留字命名
4.4 表名最后加s，看表名，字段名，需要知道这张表是干什么的，不要起一些奇奇怪怪的名字
4.5 同一个功能模块，采用统一前缀，比如特卖购物中，订单会有多个表：
trade_totals:
trade_alis:
trade_details:
trade_express:
不要是下面这种命名
total_trades:
ali_trades:
express:
所有订单模块涉及的表，它的表前缀必然都是trade_
4.6 数据字典维护，以及表注释维护
4.7 常用表名
五.数据库规模控制
5.1 除非是一些不重要的库，否则一个库最好是一个数据库实例(同一台服务器同一个端口)
5.2 单个数据库，表的个数不超过300，总体大小，最好在100G以内
5.3 数据表规模：
5.3.1 log表等，只根据索引查询的，可以大点，超过4G，也不会有什么大的性能影响
5.3.2 核心表，比如博客的文章表等，会有很多复杂查询等，最好不要超过1G，否则性能下降会非常严重(垂直分表)
5.3.3 单张表，字段数不超过15个(log表等，不超过30个)
5.3.4 学会预估表的大小，预估5年时间的数据量，可以在本地插入1000条左右的正常记录，看表的大小，然后预估
5.4 表结构设计的时候，降低数据库规模
5.4.1 只在数据库在可预期的时间里，可能会超大的时候才优化
5.4.2 一个字段，如果只有表中的少量记录有值，拆分到一个新的表里，空间占用将会减少很多，比如tags
5.4.3 log表等，可以定时备份清零
六.禁止以下写法，性能考虑
6.1 【重要】order by rand
6.2 【重要】子查询
6.3 【重要】联表查询
6.4 【重要】不带limit的sql(除了insert)
6.5 【重要】like '%username%' 这种前缀%的查询
6.6 or的改写(尽量不要用or，实在没办法的时候，采用以下方法优化)：
6.6.1 同一字段的不同条件，请采用in来优化，in的个数不要超过200个，否则性能下降很严重
6.6.2 不同字段上的条件，使用union all，不要用union(union会去重，严重影响性能)
6.7 不要出现select *，在一些大表中，这个会浪费各种字段(内存、带宽、cpu等等)，会导致一堆问题
6.8 不要在sql中，使用mysql的内置函数，比如md5等等(绝对不能在索引列上使用)
6.9 避免not , != , <> , !< 、!> 、NOT EXISTS 、NOT IN 、NOT LIKE 等语法
6.10 少写大sql，或者让人看起来不易懂的sql
6.11 不要用存储过程、触发器等，mysql做的并不好
6.12 分页考虑大页码时候的优化(redis的有序集合非常擅长做这个)
6.13 group by的时候，可以采用order by null 来去除排序(group by 本身会进行排序)
七.索引
7.1 自增长id主键必须有
7.2 字符串上建索引定长，不要全部索引(一般前8或者前10即可)
7.3 不允许在索引列上，用mysql做运算(其他字段除非实在没办法，否则也不允许)
7.4 不要使用外键(高并发，锁竞争会死的很惨，而且调bug很不方便，程序约束)
7.5 explain必须要经常用，看懂基本的
7.6 字段上越分散，索引效果越好(比如性别上单独建索引，性能提升基本没有，甚至出现性能下降)
7.7 联合索引只能使用最左前缀
7.8 覆盖索引速度最快
7.9 不要出现重复索引(比如user_id上加了个索引，又在user_id上建个唯一索引)
八.sql规范
8.1 sql语句采用小写
8.2 字段名、表名必须加``包含起来，因为你不知道什么时候就会碰到mysql关键字
8.3 容易导致条件不明显的加()区分先后顺序，谁要考验我逻辑运算先后顺序，我就会去考验他加班的耐性...
8.4 尽量使用等值条件
8.5 不要对字段使用函数，比如where abs(`num`)>3这种的
8.6 union all代替union
8.7 数据库配置统一
8.8 A项目需要使用B项目中的数据库，不要直接连，采用B项目的接口形式
8.9 insert的时候，必须指明插入哪些字段，否则改下表结构，就废了
8.10 select的时候，取字段名数据，不要以数字下标，理由同insert
九.其他操作
9.1 导入大批量数据
9.1.1 使用load data比insert快20倍
9.1.2 先不要索引，导入完之后，再建立索引会很快很多
9.1.3 如果只用insert的话，一次insert多条，不要每次insert一条
9.1.4 大批量的话，尽量不要在高峰期间执行
9.2 insert select 可能导致复制异常
9.3 较大项目的时候，读写分离
9.4 不允许程序端对mysql加锁
9.5 平时上phpmyadmin的话，上从库的，不要在主库在做操作
9.6 对于不清楚性能的sql，在前面讲explain先看下查询计划，没问题之后再执行，要是不清楚的话，可以找其他人问下再确定是否执行
9.7 执行大规模统计等的时候，严禁在浏览器中访问统计脚本
9.7.1 见过最极端的是一个apache进程跑了差不多20000秒，6个小时
9.7.2 浏览器里，执行的时候，卡住，很多人直接关浏览器，导致进程一直堵塞
9.7.3 浏览器上如果有百度插件，你访问了一个连接，百度马上就会去爬下这个链接，也可能会死的很惨
9.7.4 在服务器上，直接php static.php这样来执行，发现卡住，ctrl+c可以终止进程
9.7.5 该统计脚本执行完之后，删除或者在最上面die掉，不允许一直留着
9.8 搜索
9.8.1 精确搜索，通过索引等解决
9.8.2 非模糊前缀的，可以同上，通过索引解决
9.8.3 模糊搜索，通过sphinx来建立索引，不要直接用mysql的
9.9 定时脚本,禁止通过http访问
if(isset($_SERVER['HTTP_HOST'])){
die();
}
9.10 log表过大，备份，清空
9.10.1 导出原表结构，并记录下来
9.10.2 对原表进行rename
9.10.3 根据原表结构，进行建表
附录：
1.不同类型数据比较
字符列与数值型
数值列与字符串
2.数值溢出，导致转换失败，从而不能使用索引，性能大降
$sql = "update `users` set `name`='hello' where id='2147483648' limit 1";
因为id是有符合整型，最大值是2147483647，而sql中的值超过上限，转换失败，update的时候，因为没有转成整型，索引使用失败，全表扫描
没有超过上限的话，加不加单引号，效率基本一致
3.emoji 表情符，MYSQL版本不支持的话，需要预先转换再存入数据库中，否则会被截断，可以参考文档
mysql内容截断原因.wps
手机聊天表情
4.php中的mysql_query
mysl_query执行之后，其实跟数据库打交道的事情就已经解决了，后面mysql_fetch_rows等的时候，只是把内存中的数据读出来
所以不要抱着侥幸心理，我只是query了一下，没取出来，所以对数据库没影响