赶集网MySQL36条军规
58到家MySQL军规升级版
阿里巴巴Java开发手册（终极版）.pdf
参考以上内容及工作中的一些总结
表结构设计规范
- 不在数据库做运算，复杂运算移动到程序端，节省数据库服务CPU等资源，同时运算的字段也无法使用索引
- 控制单表数据量，纯
INT不超过1000W行，含CHAR不超过500W行 - 合理分表，如按用户USERID，日期，地区等
- 建议单库不超过300-400个表
- 单表分表个数必须控制在1024个以内
- 表字段数少而精，有利于IO高效、全表遍历、表修复快、提高并发、alter table快
- 单表字段上限控制在20~50个
- 单表不超过50个纯
INT字段、不超过20个CHAR(10)字段 - 单行不超过200Byte
- 适当牺牲范式、加入适当的冗余
字段设计规范
- 选用合适的数值字段类型（根据业务区分使用
tinyint/int/bigint，分别会占用1/4/8字节） - 表达是与否概念的字段，使用
is_xxx方式命名，数据类型为UNSIGNED TINYINT（1表示是，0表示否） - 小数类型为
DECIMAL，禁止使用float 和 double，存在精度缺损 - 时间类型使用
BIGINT，统一存储时间戳，方便前端格式化 - 字段长度固定，或者长度近似的业务场景，适合使用
CHAR，能够减少碎片，查询性能高 - 字段长度相差较大，或者更新较少的业务场景，适合使用
VARCHAR，能够减少空间 - 将字符串转化为数字，因为数字型比字符串型索引更高效、查询更快、占用空间更小
- 避免使用
ENUM，因为增加新类型需要修改表结构 - 避免使用
NULL字段，因为很难进行查询优化、含NULL复合索引无效、NULL列加索引需要额外空间，NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑 - 少用并拆分
TEXT/BLOB，处理性能远低于VARCHAR，若必须使用则拆分单独的表（建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据） - 不在数据库里存图片，借助文件系统存储图片，数据库中仅存图片存储地址（文件类同）
- 使用
INT UNSIGNED存储IPv4，不要用CHAR(15) - 使用
VARCHAR(20)存储手机号，不要使用整数（牵扯到国家代号，可能出现+/-/()等字符，例如+86；VARCHAR可以模糊查询，例如like ‘138%’）
索引规范
- 自增列或全局ID做InnoDB主键，推荐用独立于业务的
AUTO_INCREMENT列或全局ID生成器做代理主键 - 非唯一索引命名默认为
idx_字段名来命名 - 唯一索引使用
uk_字段名来命名 - 主键索引
pk_来命名 - 单张表索引数量建议控制在5个以内
- 合理添加索引可以改善查询，但会减慢更新，并不是索引越多越好，索引字段数最好不超过表字段的20%
- 字符字段必须建前缀索引，
like '%name%'不会使用索引 - 不在索引列做运算，否则无法使用索引，导致全表扫描
- 尽量不用外键，高并发时容易死锁（外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈）
- 非必要不要进行
JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引 - 理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a), (a,b), (a,b,c)
- 防止因字段类型不同造成的隐式转换，导致索引失效
SQL规范
- SQL语句尽可能简单，一条SQL只能在一个CPU运算，5000+QPS的高并发中，可能一条大SQL就把整个数据库堵死
- 拆解成多条简单SQL，缓存命中率更高，用上更多的CPU；减少锁表时间，特别是MyISAM
- 事务/连接使用原则：即开即用，用完即关
- 与事务无关操作放到事务外面，减少销资源的占用
- 不破坏一致性前提下，使用多个短事务代替长事务
- 尽可能避免使用存储过程、触发器、Event（调试，排错，迁移都比较困难，扩展性较差），减少使用MySQL函数对结果进行处理，由程序端负责
- 尽量不用
SELECT *只取需要的数据列，不则会占用更多的CPU、内存、IO、网络带宽资源 - 同一字段，将
OR改写为IN，注意控制IN的个数，建议 N 小于200
select id from t where phone=’159′ or phone=’136′;
=>
select id from t where phone in (’159′, ’136′);
- 不同字段，将
OR改为UNION
select id from t where phone = ’159′ or name = ‘john’;
=>
select id from t where phone=’159′
union
select id from t where name=’jonh’
- 避免负向查询和%前缀模糊查询，使用不了索引，导致全表扫描
- 尽量不用或少用
COUNT(*) LIMIT高效分页，传统分布SELECT id FROM user LIMIT 10000, 10，偏移量越大则越慢；推荐分页SELECT id FROM user WHERE id >= 10000 LIMIT 10- 若无需对结果进行去重，使用
UNION ALL而非UNION，UNION有去重开销 - 高并发DB禁止进行两个表以上的
JOIN
互联网公司为什么不推荐使用join?
1.不利于写操作。执行读操作时，会锁住被读的数据，阻塞其他业务对该部分数据的更新操作。如果涉及多个聚合函数，相当于同时锁住多张表，不能进行读写操作，影响系统整体性能。
2.不利于维护。业务发生变动时，如join中的一张表修改了，可能导致系统中原有的SQL不可用。
3.数据库计算资源相对于service层的水平扩展要难很多。
- 同数据类型的列值进行比较（防止因字段类型不同造成的隐式转换，导致索引失效）
- 避免大SQL、大事务、大批量长时间占用系统资源而堵塞系统，一个SQL只能在一个CPU运算
- 尽量不用
INSERT ... SELECT Load data批量导入数据，尽量避开高峰期操作- 使用
EXPLANIN查看执行计划 - 观察慢查询日志
show processlist查看进程状态- MySQL子查询大部分情况下优化较差，特别是
WHERE中使用IN id的子查询，一般可用JOIN改写
通用约定
- 永远不在程序端显示加锁，外部锁对数据库不可控，高并发时是灾难
- 表存储引擎必须使用InnoDB
- 统一字符集为UTF8，乱码：
SET NAMES UTF8，必要时候使用utf8mb4（utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它） - 库表等名称统一用小写，MySQL库表大小写敏感，字段名的大小写不敏感（Linux VS win）
- 表名不使用复数名词
- 禁止使用关键字
- 必备三个字段
id, create_at, update_at - 表命名最好加上“业务名称_表的作用”
- 库名与服务名最好一致
