MySQL开发规范

发表于 | 分类于 |

简介

持续借鉴、收集并整理一些开发规范和技巧，期望能更充分利用MySQL的特性，得到更好的性能。
规范是死的，人是活的。
现在定义的规范，是为以后推翻准备的。

目的

提供给开发人员参考，方便写成更有效率的开发。

范围

文档涉及的范围：需要基于MySQL做应用开发的人员。

定义、首字母缩写词和缩略语

暂无

数据库设计

  • 目标三个：功能实现，可伸缩性，可用性。
  • 关键点：平衡业务技术各个方面，做好取舍。
  • 80%的性能优化来自架构设计的优化。

引擎及版本选择

  • 引擎建议使用InnoDB
  • 根据目前我们业务的特点，建议使用MySQL5.1社区版和InnoDB plugin或MySQL5.5，后续MySQL5.6比较稳定后再行考量和评估。

架构浅谈

  • 开发大牛都擅长，这里不多提，仅标注一下。
  • 非功能性需求
  • 读写分离
  • 分库分表
  • 热点数据
  • 多级缓存
  • 雪崩效应与过载保护
  • 读优化
  • 写优化
  • schema设计
  • 尽量不在数据库做运算
  • 复杂运算移到程序端CPU
  • 尽可能简单应用MySQL
    如：md5() 或Order by Rand()或计算字段等操作不在数据库表上进行。

适当的范式设计

  • 库和表预估
  • 常见的有100库100表，1000库10表等。
  • 建议单库不超过300-400个表。
  • 总空间容量不超过100G。

单表控制

  • IO高效；全表遍历；表修复快；提高并发；alter table快。

字段数量

  • 建议上限20~50个。

一年内的单表数据量预估

  • 建议纯INT不超1000W，含CHAR不超500W。

举例

  • 单表1G体积 500W行评估：
  • 顺序读1G文件需N秒
  • 单行不超过200Byte
  • 单表不超50个纯INT字段
  • 单表不超20个CHAR(10)字段

拒绝3B

  • 大SQL (BIG SQL)
  • 大事务 (BIG Transaction)
  • 大批量 (BIG Batch)

反范式设计

  • 无外键，少多表join查询。
  • 便于分布式设计，允许适度冗余，为了容量扩展允许适度开销。
  • 基于业务自由优化，基于i/o 或查询设计，无须遵循范式结构设计。

典型场景

  • 原有展现程序涉及多个表的查询，希望精简查询程序。
  • 数据表拆分往往基于主键，而原有数据表往往存在非基于主键的关键查询，无法在分表结构中完成。
  • 存在较多数据统计需求（count, sum等），效率低下。

  • 解决思路

    基于展现的冗余设计
    如：
    消息表message，存在字段 from_uid,to_uid,msg,send_time 四个字段，而展示程序需要显示发送者姓名和性别。
    通常在message表中增加冗余字段from_username和from_user_sex即可。

基于查询的冗余设计

如：
用户分表，将用户库分成若干数据表。基于用户名的查询和基于uid的查询都是高并发请求。用户分表基于uid分成数据表，同时基于用户名做对应冗余表。
如果允许多方式登陆，可以有如下设计方法：
uid,passwd,用户信息等等，主数据表，基于uid分表
ukey,ukeytype,uid基于ukey分表，便于用户登陆的查询。分解成如下两个SQL：
select uid from ulist_key_13 where ukey=’$username’ and ukeytype=’$login’;
select * from ulist_uid_23 where uid=$uid and passwd=’$passwd’;
ukeytype定义用户的登陆依据，比如用户名，手机号，邮件地址，网站昵称等。 Ukey+ukeytype必须唯一。
此种方式需要登陆密码统一，对于第三方接入模式，可以通过引申额外字段完成。

基于统计的冗余设计

如：
count(*)操作。
需要不精准结果，可以直接show table status like …获得。
需要精准结果，可以在缓存层增加key-value对，实时更新该key-value。同时异步更新到数据库中冗余字段，或冗余表中。

历史数据表

  • 历史数据表对应于热点数据表。
  • 将需求较少又不能丢弃的数据，仅在少数情况下被访问存入历史数据表。

全文检索设计

  • 最差的设计，直接使用sql语句where条件中使用like %fulltext%
  • 直接全表扫描或全索引扫描，性能最差，无任何扩展，基本不可接受。

MySQL相关引擎支持

  • MyISAM全文索引，使用match()函数搜索。InnoDB从MySQL5.6.4开始支持全文索引，对中文支持不好，使用MATCH()…AGAINST。
  • 并发不高，数据量不大，业务逻辑简单，可以考虑。

使用外部开源全文检索引擎

  • 目前常用的有sphinx和lucene等。
  • 适合并发高，数据量大，业务逻辑复杂的场景。
  • 主要关注预热、增量更新及分片功能的实现。

分页设计

  • 传统分页

    Select * from table limit 10000,10;

  • LIMIT原理

    Limit 10000,10
    偏移量越大则越慢

  • LIMIT方式推荐分页

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

  • LIMIT的高效分页

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

    分页方式二
    Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;

    分页方式三
    ?SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ;

    分页方式四
    程序取ID：select id from table limit 10000,10;
    Select * from table WHERE id in (123,456…) ;

    LIMIT分页举例
    MySQL> select sql_no_cache from post limit 10,10;
    10 row in set (0.01 sec)
    MySQL> select sql_no_cache     from post limit 20000,10;
    10 row in set (0.13 sec)
    MySQL> select sql_no_cache from post limit 80000,10;
    10 rows in set (0.58 sec)
    MySQL> select sql_no_cache id from post limit 80000,10;
    10 rows in set (0.02 sec)
    MySQL> select sql_no_cache     from post WHERE id>=323423 limit 10;
    10 rows in set (0.01 sec)
    MySQL> select * from post WHERE id >= ( select sql_no_cache id from post limit 80000,1 ) limit 10 ;
    10 rows in set (0.02 sec)

  • LIMIT分页与缓存结合

    类似sina微博的首页，总共保留最新的500条微博，分10页。

表字段设计

  • 主键

    如果使用的是InnoDB并且不需要特殊的聚簇。定义一个代理键（surrogate key）是个好的主意。意思就是这个主键并不是来自于你的应用程序的数据（与业务逻辑无关，而应用程序的数据如果有唯一的候选列可以做成唯一键），最简单的方法就是使用AUTO_INCREMENT列。这能保证数据插入保持着连续的顺序并且对于使用主键连接会获得更好的性能。最好避免使用随机的聚簇键。 对每张表，最重要的就是一定要有主键。

  • 主键设计之UUID

    从性能的角度来说，使用UUID是个最不好的方法：它使聚簇索引的插入是随机的。这是最不好的场景了。并且对于数据的聚集也没有什么帮助。
    UUID_SHORT()
    UUID_SHORT()所占用的存储空间比UUID要小。（UUID_SHORT()可能要使用bigint占用8个字节，而UUID可能要使用字符串用char(32)）。另外uuid_short()是顺序的，这个也解决了随机导致的问题，但是uuid_short()也有一些限制和bug。

  • 主键设计之自增列

    没什么好说的，自增列简单实用。
    仅注意一下锁(gap lock)问题即可。

  • 主键设计之程序控制

    避免自增列引起的一些锁问题，统一管理，并发性更高。

  • 主键设计之使用中间件

    更高的并发性，可以考虑从数据库中剥离，使用自己开发或第三方中间件，如：
    https://github.com/twitter/snowflake

  • 类型溢出

    原因
    int占用4个字节，而int又分为无符号型和有符号性。对于无符号型的范围是0 到 4294967295；有符号型的范围是-2147483648 到 2147483647。

    举一反三，其他类型都可能有类似问题，均需要考量。

    控制方法
    可以通过sql_mode参数控制，但一般建议程序控制，比如：对表单项的值进行校验。

  • 类型转换

    溢出和隐含转换导致的运行缓慢。
    discuz论坛, uid是pk, mediumint类型, MyISAM表,总行数255万 #现象：更新比较慢，快7秒

    MySQL> UPDATE cdb_members SET email=’qin@zol.com.cn’ WHERE uid=’486851368’;Query OK, 0 rows affected (6.94 sec)Rows matched: 0 Changed: 0 Warnings: 0

    #通过观察语句发现有类型转换，因为uid为mediumint，而传过来的值是字符串’2982891440’

    这里的uid是mediumint， 这个条件的uid大小明显超过了范围。（参考 字段定义）

    去掉uid=后面值的’’，速度就正常了。但是对于uid没有溢出的,加不加引号速度都一样。

    原因：
    在non-strict mode下，MySQL会自动帮你把字符串转换成整形，但是如果数值超出了范围，转换就会失败，所以MySQL就按照字符串来处理，因此不能使用索引。而从explain的结果上，并没有表现出这样的差别。

    控制方法：
    可以通过sql_mode参数控制，一般建议程序控制，比如：对表单项的值进行校验。

字段选择

  1. 字段定义为NOT NULL。
  2. 表字符集选择UTF8，对恶魔字符集emoji用utf8mb4，如果MySQL版本不支持时，应用收到这种字符集的时候先转码成定义的串再传入数据库，例如传来\x0F，应用先替换成[i_smile]这样的特定字符串再存入数据库。
  3. 存储年使用YEAR类型。存储日期使用DATE类型。 存储时间（精确到秒）使用TIMESTAMP类型或INT。使用时间字段作为查询条件，尤其是以时间段查询时，应使用INT保存。
  4. 整形定义中不添加长度，比如使用INT，而不是INT[4]。
  5. 使用VARBINARY存储变长字符串。
  6. 自增序列类型的字段只能使用INT或者BIGINT，且明确标识出为无符号型(UNSIGNED)，除非确实会出现负数，仅当该字段数字取值会超过42亿，才使用BIGINT类型；
  7. int(10)和int(1)没有什么区别，10和1仅是宽度而已，在zerofill等扩展属性的时候有用或者特殊的命令行交互工具
  8. 少量枚举或状态定义类需求不建议使用ENUM、SET、VARCHAR类型，使用TINYINT来代替。需在COMMENT信息中标明被枚举的含义。例如：
    is_disable tinyint(1) DEFAULT ‘0’ COMMENT ‘0:启用 1:禁用 2:异常’;
  9. 存储精确浮点数使用DECIMAL替代FLOAT和DOUBLE。
  10. 使用UNSIGNED存储非负数值。
  11. 使用INT UNSIGNED存储IPV4。
  12. 使用短数据类型，比如取值范围为0-80时，使用TINYINT UNSIGNED。
  13. 尽可能不使用TEXT、BLOB类型，如果确实需要将过大字段拆分到其他表中。
  14. VARCHAR(N)，N表示的是字符数不是字节数，比如VARCHAR(255)，可以最大可存储255个汉字，需要根据实际的宽度来选择N，VARCHAR(N)，N>5000时，使用BLOB类型。如果N<256时会使用一个字节来存储长度，如果n>=256则使用两个字节来存储长度。
  15. 不在数据库中使用VARBINARY、BLOB存储图片、文件等。
  16. 禁止在数据库中存储明文密码
  17. 数据库中存放IP时，按功能确定字段类型。仅作展示功能的使用CHAR，作为查询功能的应使用INT类型存放。参见”将字符转化为数字”
  18. 严格禁止在库名、表名中使用大写字母。

技巧

  • 将字符转化为数字更高效，查询更快，占用空间更小

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

    INT UNSIGNED

    INET_ATON()

    INET_NTOA()

  • 将日期转化为数字

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

    举例：用无符号INT存储日期和时间

    INT UNSIGNED

    FROM_UNIXTIME()

    UNIX_TIMESTAMP()

  • 避免使用NULL字段

    原因：
    很难进行查询优化；

    NULL列加索引，需要额外空间；

    含NULL复合索引无效。

    举例：

    不使用：`a` char(32) DEFAULT NULL

    不使用：`b` int(10) NOT NULL

    使用：`c` int(10) NOT NULL DEFAULT 0

sql语句

  • 多使用等值操作，少使用非等值操作

    WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引，因为不能同时用到两个范围条件。

  • 常数表优先，字典表或小表其次，大表最后

    常数表指：空表或只有1行的表。与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表。如：
    SELECT FROM t WHERE primary_key=1;
    SELECT     FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

    字典表指：小数量的行。如：自定义的自增字段表，而不使用MySQL的AUTO_INCREMENT。

  • 减少或避免临时表

    如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。

  • where子句中的数据扫描别跨越表的30%

  • where子句中同一个表的不同的字段组合建议小于等于5组，否则考虑业务逻辑或分表

  • 不使用is null或is not null，字段设计时建议not null，若麻烦可折中考虑给一默认值，原因参见”避免使用NULL字段”。

  • 使用like时，%不要放在首字符位置。

    如果%必须放在首字符位置，参见”全文检索设计”

  • 值域比较多的表字段放在前面

    比如：id，date字段放在前面，而status这样的字段放在后面，具体的可以通过执行计划来把握。

  • 表字段组合中出现比较多的表字段放在前面

    方便综合评估索引，缓解因为索引过多导致的增删改的一些性能问题。

  • 表字段不能有表达式或是函数

    如：where abs(列)>3或where 列*10>100

  • 注意表字段的类型，避免表字段的隐示转换

    参见”表字段设计”

    比如：列为int，如果where 列=’1’，则会出现转换。

  • 考虑使用union all，少使用union，注意考虑去重

    union all不去重，而少了排序操作，速度相对比union要快，如果没有去重的需求，优先使用union all。

  • 不同字段的值or或in大于等于3次，考虑用union all替换；同一字段的值or用in替换

    Select * from opp WHERE phone=’12347856’ or phone=’42242233’;

    考虑用

    Select * from opp WHERE phone in (‘12347856’ , ‘42242233’);

    Select * from opp WHERE phone=’010-88886666’ or cellPhone=’13800138000’;

    考虑用
    Select from opp WHERE phone=’010-88886666’
    union all
    Select     from opp WHERE cellPhone=’13800138000’;

  • 用Where子句替换HAVING子句

    select id,count(star) from table group by id having age>=30 order by null;

    考虑用

    select id,count(star) from table where age>=30 group by id order by null;

  • 对同一表的order by和group by操作分别小于3组，否则考虑业务逻辑或分表
    尽量使用主键进行update和delete

    小心text/blobs等大字段，如果确实不需要这样的大字段，则不用放入sql语句中

    Text/blob大字段的用法参见”字段选择”

  • 使用INSERT … ON DUPLICATE KEY update (INSERT IGNORE)来避免不必要的查询

  • 考虑使用limit N，少用limit M,N，特别是大表，或M比较大的时候

    参考”分页设计”

  • 减少或避免排序，如：group by语句中如果不需要排序，可以增加order by null

  • 增删改语句中不使用不确定值函数和随机函数，如：RAND()和NOW()等。
    INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()„„），values的个数不超过500。

  • 避免使用存储过程、触发器、函数、UDF、events等，容易将业务逻辑和DB耦合在一起，并且MySQL的存储过程、触发器、函数、UDF、events中存在一定的bug。

  • 避免使用JOIN。

  • 使用合理的SQL语句减少与数据库的交互次数。

    INSERT … ON DUPLICATE KEY UPDATE
    REPLACE INTO、INSERT IGNORE 、INSERT INTO VALUES(),(),()
    UPDATE … WHERE ID IN(10,20,50,…)

  • 减少使用视图，避免复杂的语句。

  • SQL语句中IN包含的值不超过500。

  • UPDATE、DELETE语句不使用LIMIT。有主键id的表WHERE条件应结合主键。

  • 使用prepared statement，可以提供性能并且避免SQL注入。

  • InnoDB表避免使用COUNT(*)操作，计数统计实时要求较强可以使用memcache或者redis，非实时统计可以使用单独统计表，定时更新。

  • 禁止在Update语句，将”,”写成”and”，非常危险。

    正确示例：update Table set uid=uid+1000,gid=gid+1000 where id <=2 ;

    错误示例：update Table set uid=uid+1000 and gid=gid+1000 where id <=2 ;

    此时”uid=uid+1000 and gid=gid+1000”将作为值赋给uid，并且无Warning！！！

索引

  • 索引名称使用小写。

  • 索引中的字段数不超过5个。

    唯一键由3个以下字段组成，并且字段都是整形时，使用唯一键作为主键。

    没有唯一键或者唯一键不符合5中的条件时，使用自增（或者通过发号器获取）id作为主键。

  • 唯一键不和主键重复。

  • 索引字段的顺序需要考虑字段值去重之后的个数，个数多的放在前面。

  • ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面。

  • 单张表的索引数量控制在5个以内，若单张表多个字段在查询需求上都要单独用到索引，需要经过DBA评估。查询性能问题无法解决的，应从产品设计上进行重构。

  • 使用EXPLAIN判断SQL语句是否合理使用索引，尽量避免extra列出现：Using File Sort，Using Temporary。

  • UPDATE、DELETE语句需要根据WHERE条件添加索引。

  • 对长度大于50的VARCHAR字段建立索引时，按需求恰当的使用前缀索引，或使用其他方法。

  • 合理创建联合索引（避免冗余），(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

  • 合理利用覆盖索引。

大表

  • 表预计行数（比如：100万条以上），表预计大小（比如：100M以上），历史数据维护策略（分表策略，迁移策略，清理策略等）；

  • 频繁增删改的表

    方便考虑定期optimize优化减少碎片，减少相关索引提供更好的写性能。

  • 频繁查的表

    方便适当优化语句，合理评估索引。

  • 特殊字段的表

    大字段（text,blob），值域小的字段（比如字段status字段0表示失败，值分布少，并且用该字段查询时以该值居多，1表示成功）。

  • 按表或按功能页面来分类的常用sql语句

    方便dba整体评估索引，为SQL审核用(生成SQL审核报告)。

  • 缓存机制与规划及预热、降级限流方案

    防雪崩、防刷

配置需求

如主从关系，用户权限等等(可以提供在运维部署说明文档中)