MySQL开发的三十六条军规

•来自一线的实战经验•每一军规背后都是血淋淋教训•丌要华丽，叧要实用•若有一条让你有所受益，慰矣•主要针对数据库开发人员

总是在灾难发生后，才想起容灾的重要性； 总是在吃过亏后，才记得曾经有人提醒过。

背景

•别让脚趾头想事情•那是脑瓜子的职责

让数据库多做她擅长的事

尽量别在数据库做运算复杂运算移到程序端CPU尽可能简单应用MySQL

实例: md5() / Order by Rand()

尽量别在数据库做运算

纯INT不超1000W含CHAR不超500W

一年内的单表数据量预估

USERIDDATEAREA

合理分表不超载

建议单库不超过300-400个表

控制单表数据量

√ IO高效 √全表遍历 √表修复快√提高并发√alter table快

表字段数少而精

单表多少字段合适？

顺序读1G文件需N秒单行不超过200Byte单表不超50个纯INT字段单表不超20个CHAR(10)字段

单表1G体积 500W行评估

单表字段数上限控制在20~50个

保持表身段苗条

平衡是门艺术严格遵循三大范式？效率优先、提升性能没有绝对的对与错适当时牺牲范式、加入冗余但会增加代码复杂度

平衡范式不冗余

数据库并发像城市交通非线性增长

拒绝3B大SQL (BIG SQL)大事务 (BIG Transaction)大批量 (BIG Batch)

拒绝3B

一.核心军规(5)

TINYINT(1Byte)SMALLINT(2B)MEDIUMINT(3B)INT(4B)、BIGINT(8B)

FLOAT(4B)、DOUBLE(8B)

三类数值类型：

INT(1) VS INT(11)

BIGINT AUTO_INCREMENT

BAD CASE：

用好数值字段类型

更高效查询更快占用空间更小

数字型VS字符串型索引

INT UNSIGNEDINET_ATON()INET_NTOA()

实例：用无符号INT存储IP，而非CHAR(15)

将字符转化为数字

优先使用ENUM或SET字符串可能值已知且有限

ENUM占用1字节，转为数值运算SET视节点定，最多占用8字节比较时需要加‘ 单引号(即使是数值)

存储

实例

优先使用ENUM或SET

很难进行查询优化NULL列加索引，需要额外空间含NULL复合索引无效

避免使用NULL字段

`a` char(32) DEFAULT NULL`b` int(10) NOT NULL`c` int(10) NOT NULL DEFAULT 0

TEXT类型处理性能远低于VARCHAR强制生成硬盘临时表浪费更多空间VARCHAR(65535)==>64K (注意UTF-8)

•尽量不用TEXT/BLOB数据类型

•若必须使用，则拆分到单独的表

实例：

少用并拆分TEXT/BLOB

子主题

不在数据库里存图片

二.字段类军规(6) 

 改善查询 减慢更新 索引不是越多越好

谨慎合理添加索引

综合评估数据密度和数据分布最好不超过字段数20%

•能不加的索引尽量不加

•结合核心SQL优先考虑覆盖索引

不要给“性别”列创建索引

•实例

字符字段必须建前缀索引

不在索引列进行数学运算或函数运算无法使用索引导致全表扫描

不在索引列做运算

• 对主键建立聚簇索引• 二级索引存储主键值• 主键不应更新修改• 按自增顺序插入值• 忌用字符串做主键• 聚簇索引分裂• 推荐用独立于业务的AUTO_INCREMENT列或全局ID生成器做代理主键• 若不指定主键，InnoDB会用唯一且非空值索引代替

自增列或全局ID做INNODB主键

外键可节省开发量有额外开销逐行操作可‘到达’其它表，意味着锁高并发时容易死锁

•线上OLTP系统（线下系统另论）

•由程序保证约束

尽量不用外键

三.索引类军规(5) 

传统设计思想BUT MySQL NOT一条SQL叧能在一个CPU运算5000+ QPS的高幵发中，1秒大SQL意味着？可能一条大SQL就把整个数据库堵死

大SQL VS 多个简单SQL

简单SQL缓存命中率更高减少锁表时间，特别是MyISAM用上多CPU

拒绝大SQL，拆解成多条简单SQL

SQL语句尽可能简单

保持事务/DB连接短小精悍

发贴时的图片上传等待大量的sleep连接

举例

保持事务(连接)短小

尽可能少用存储过程尽可能少用触发器减用使用MySQL函数对结果进行处理

线上OLTP系统（线下库另论）

由客户端程序负责

尽可能避免使用SP存储过程/TRIG触发器/FUNC函数

•更多消耗CPU、内存、IO、网络带宽•先向数据库请求所有列，然后丢掉不需要列？

用SELECT * 时

•更安全的设计：减少表变化带来的影响•为使用covering index提供可能性•Select/JOIN减少硬盘临时表生成，特别是有TEXT/BLOB时

尽量不用SELECT * ，只取需要数据列

【错误】SELECT * FROM tag WHERE id = 999184 

【改正后】SELECT keyword FROM tag WHERE id = 999184

实例 

尽量不用 SELECT *

•OR效率：O(n)•IN 效率：O(Log n)•当n很大时，OR会慢很多

同一字段，将or改写为in()

注意控制IN的个数，建议n小于200

【错误】Select * from opp WHERE phone=‘12347856' or phone=‘42242233' 

改写OR为IN()

•减少对不同字段进行 \"or\" 查询•Merge index往往很弱智•如果有足够信心：set global optimizer_switch='index_merge=off';

不同字段，将or改为union

【错误】Select * from opp WHERE phone='010-88886666' or cellPhone='13800138000';

【优化后】 Select * from opp WHERE phone='010-88886666' union Select * from opp WHERE cellPhone='13800138000';

改写OR为UNION

改写OR

NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

避免负向查询

B+ Tree使用不了索引导致全表扫描

避免 % 前缀模糊查询

MySQL> select * from post WHERE title like ‘北京%' ; 298 rows in set (0.01 sec) MySQL> select * from post WHERE title like '%北京%' ; 572 rows in set (3.27 sec)

避免负向查询和% 前缀模糊查询

COUNT(*)的几个例子

COUNT(COL) VS COUNT(*)COUNT(*) VS COUNT(1)COUNT(1) VS COUNT(0) VS COUNT(100)

几个有趣的例子

示例

COUNT(*)=count(1)COUNT(0)=count(1)COUNT(1)=count(100)COUNT(*) !=count(col)WHY?

结论

不带 WHERE COUNT()带 WHERE COUNT()

COUNT(*)的资源开销大，尽量不用少用

MyISAM VS INNODB

实时统计：用memcache，双向更新，凌晨跑基准非实时统计：尽量用单独统计表，定期重算

计数统计

减少COUNT(*)

传统分页

LIMIT原理

select * from table WHERE id>=23423 limit 11; #10+1 (每页10条)select * from table WHERE id>=23434 limit 11;

推荐分页

分页方式二

分页方式三

分页方式四

•可能需按场景分析并重组索引

反页方式

LIMIT高效分页

若无需对结果进行去重，则用UNION ALLUNION有去重开销

MySQL>SELECT * FROM detail20091128 UNION ALL SELECT * FROM detail20110427 UNION ALL SELECT * FROM detail20110426 UNION ALL SELECT * FROM detail20110425 UNION ALL SELECT * FROM detail20110424 UNION ALL SELECT * FROM detail20110423;

用UNION ALL 而非 UNION

高并发DB不建议进行两个表以上的JOIN

可缓存大量早期数据使用了多个MyISAM表对大表的小ID IN()联接引用同一个表多次

适当分解联接保证高幵发

实例： 

分解联接保证高并发

GROUP BY 实现分组自劢排序•无需排序：Order by NULL•特定排序：Group by DESC/ASC

GROUP BY 去除排序

数字对数字，字符对字符

原则

同时转换为双精度进行比对

数值列不字符类型比较

字符列整列转数值不会使用索引查询

字符列不数值类型比较

同数据类型的列值比较

批量数据快导入

延迟同步出错

尽量不用 INSERT ... SELECT

Load data 导数据

大批量更新凌晨操作，避开高峰

凌晨丌限制白天上限默认为100条/秒（特殊再议）

打散大批量更新

MySQLslaEXPLAINSHOW PROFILEShow Slow LogShow ProcesslistSHOW QUERY_RESPONSE_TIME(Percona)MySQLdumpslow

Know Every SQL

四.SQL类军规(15)

•开发无线上库操作权限

开发不能上生产操作

构建数据库的生态环境

实时数据用real库模拟环境用sim库测试用qa库开发用dev库

原则：线上连线上，线下连线下

案例：

隔离线上线下

MySQL子查询大部分情况优化较差特别WHERE中使用IN id的子查询一般可用JOIN改写

MySQL> select * from table1 where id in (select id from table2); MySQL> insert into table1 (select * from table2); //可能导致复制异常

实例: 

禁止未经DBA确认的子查询

•外部锁对数据库不可控•高并发时是灾难•极难调试和排查

永远不在程序端对数据库显式加锁

•采用事务•相对值修改•Commit前二次较验冲突

并发扣款等一致性问题

永远不在程序端显式加锁

MySQL 4.1 以前叧有latin1为多语言支持增加多字符集也带来了N多问题保持简单

字符集：

统一字符集：UTF8

校对规则：utf8_general_ci

乱码：SET NAMES UTF8

统一字符集为UTF8

•库表等名称统一用小写Linux VS WindowsMySQL库表大小写敏感字段名的大小写丌敏感

索引命名默认为“idx_字段名”

DataSharing ==> ds

库名用缩写，尽量在2~7个字母

举例: Select * from return; Select * from `return`;

ADDALLALTERGOTOGRANTGROUPPURGERAID0RANGEANALYZEANDASHAVINGHIGH_PRIORITYHOUR_MICROSECONDREADREADSREALASCASENSITIVEBEFOREHOUR_MINUTEHOUR_SECONDIFREFERENCESREGEXPRELEASEBETWEENBIGINTBINARYIGNOREININDEXRENAMEREPEATREPLACEBLOBBOTHBYINFILEINNERINOUTREQUIRERESTRICTRETURNCALLCASCADECASEINSENSITIVEINSERTINTREVOKERIGHTRLIKECHANGECHARCHARACTERINT1INT2INT3SCHEMASCHEMASSECOND_MICROSECONDCHECKCOLLATECOLUMNINT4INT8INTEGERSELECTSENSITIVESEPARATORCONDITIONCONNECTIONCONSTRAINTINTERVALINTOISSETSHOWSMALLINTCONTINUECONVERTCREATEITERATEJOINKEYSPATIALSPECIFICSQLCROSSCURRENT_DATECURRENT_TIMEKEYSKILLLABELSQLEXCEPTIONSQLSTATESQLWARNINGCURRENT_TIMESTAMPCURRENT_USERCURSORLEADINGLEAVELEFTSQL_BIG_RESULTSQL_CALC_FOUND_ROWSSQL_SMALL_RESULTDATABASEDATABASESDAY_HOURLIKELIMITLINEARSSLSTARTINGSTRAIGHT_JOINDAY_MICROSECONDDAY_MINUTEDAY_SECONDLINESLOADLOCALTIMETABLETERMINATEDTHENDECDECIMALDECLARELOCALTIMESTAMPLOCKLONGTINYBLOBTINYINTTINYTEXTDEFAULTDELAYEDDELETELONGBLOBLONGTEXTLOOPTOTRAILINGTRIGGERDESCDESCRIBEDETERMINISTICLOW_PRIORITYMATCHMEDIUMBLOBTRUEUNDOUNIONDISTINCTDISTINCTROWDIVMEDIUMINTMEDIUMTEXTMIDDLEINTUNIQUEUNLOCKUNSIGNEDDOUBLEDROPDUALMINUTE_MICROSECONDMINUTE_SECONDMODUPDATEUSAGEUSEEACHELSEELSEIFMODIFIESNATURALNOTUSINGUTC_DATEUTC_TIMEENCLOSEDESCAPEDEXISTSNO_WRITE_TO_BINLOGNULLNUMERICUTC_TIMESTAMPVALUESVARBINARYEXITEXPLAINFALSEONOPTIMIZEOPTIONVARCHARVARCHARACTERVARYINGFETCHFLOATFLOAT4OPTIONALLYORORDERWHENWHEREWHILEFLOAT8FORFORCEOUTOUTEROUTFILEWITHWRITEX509FOREIGNFROMFULLTEXTPRECISIONPRIMARYPROCEDUREXORYEAR_MONTHZEROFILL

保留字一览表

注意避免用保留字命名

统一命名规范

五.约定类军规(5)

MySQL开发的三十六条军规