mysql有没有dba标识_7-MySQL DBA笔记-研发规范

第7章 研发规范

本章将为读者解读一份研发规范。

为了更好地协同工作和确保所开发的应用尽可能的稳定、高效，建立一套数据库相关的研发规范是很有必要的，

虽然研发规范的确立和推广是一项很耗时的工作，但所取得的收益也是长久的，

它可以让研发人员更高效地使用数据库，可以让新的研发人员尽快融入研发体系，还可以极大地减少DBA和研发团队、测试团队的沟通成本。

如果DBA需要建立研发规范，建议和研发团队一起沟通确定，因为标准的实行和落地，需要考虑到现有的一些框架、语言和习惯，而旧有的力量往往是强大的。

不同的人背景不一样，看待事务的标准也不一样，自然就会有理解上的不一致，我们应该尽可能地求得最大的共识。

以下将列举下一些研发规范，主要包括命名约定、索引、表设计、SQL语句、升级/部署脚本规范、数据架构建议这几个部 分，以供读者参考。

这些规范中有些并不是绝对要遵循的，需要依据现实情况进行权衡取舍。

7.1 命名约定

对于命名，并没有很严苛的规定，但在同一个应用中，建议风格统一。

以下是一些通用的法则，可能有互相冲突的地方，请读者自行衡量取舍。

命名应有意义，以使用方便记忆、描述性强的可读性名称为第一准则，应尽量避免使用缩写或代码来命名。

传统的使用缩写或代码的方法是出于一些历史原因，比如希望节省空间、尽快加载数据等，但随着硬件的快速发展，目前来说这么做的意义不大。

数据库、表都用小写(尽量不要使用除下划线、小写英文字母之外的其他字符，如果要用下划线，应该尽量保持一致的风格)。

索引的命名以idx_为前缀。

命名不要过长(应尽量少于25个字符)。

不要使用保留字。

注意字段类型的一致性、命名的一致性，同一个字段在不同的表中也应是相同的类型或长度。

如果同一个数据库下有不同的应用模块，则可以考虑对表名用不同的前缀标识。

备份表时加上时间标识。

新建库必须提供库名，库的命名规则必须契合所属业务的特点，新建库必须说明需要授权的用户，若要新建用户，则必 须提供用户名，用户命名规则要契合业务。

7.2 索引

建议索引中的字段数量不要超过5个。

单张表的索引数量建议控制在5个以内。

唯一键和主键不要重复。

索引字段的顺序需要考虑字段唯一值的个数，选择性高的字段一般放在前面。

ORDER BY、GROUP BY、DISTINCT的字段需要放在复合索引的后面，也就是说，复合索引的前面部分用于等值查询， 后面的部分用于排序。

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

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

建议不要使用“like %value”的形式，因为MySQL仅支持最左前缀索引。

对长度过长的VARCHAR字段(比如网页地址)建立索引时，需要增加散列字段，对VARCHAR使用散列算法时，散列后的字段最好是整型，然后对该字段建立索引。

存储域名地址时，可以考虑采用反向存储的方法，比如把news.sohu.com存储为com.sohu.news，方便在其上构建索引和进行统计。

合理地创建复合索引，复合索引(a,b,c)可以用于“where a=?”、“where a=?and b=?”、“where a=?and b=?and c=?”等形式，

但对于“where a=?”的查询，可能会比仅仅在a列上创建单列索引查询要慢，因此需要在空间和效率上达成平衡。

合理地利用覆盖索引。由于覆盖索引一般常驻于内存中，因此可以大大提高查询速度。

把范围条件放到复合索引的最后，WHERE条件中的范围条件(BETWEEN、、>=)会导致后面的条件使用不了索引。

7.3 表设计

如果没有特殊的情况，建议选择InnoDB引擎。

每个表都应该有主键，可选择自增字段，或者整型字段。使用UNSIGNED整型可以增加取值的范围。

例外的情况是，一些应用会频繁地基于某些字段进行检索，设计人员可能会认为这些字段/字段组合更适合做主键，因为它们更自然、更高效。

尽量将字段设置成NOT NULL。如果没有特殊的理由，建议将字段定义为NOT NULL。

如果将字段设置成一个空字符串或设置成0值并没有什么不同，都不会影响到应用逻辑，那么就可以将这个字段设置为NOT NULL。

NULL值的存储需要额外的空间，且会导致比较运算更为复杂，这会使优化器更难以优化SQL。

当然，是否设置为NULL更应取决于你的业务逻辑，如果你确实需要，那么就设置它允许NULL值，

NULL值虽然会导致比较运算更加复杂，但这比因为定义了NOT NULL默认值而导致应用逻辑出现异常要好。

使用更短小的列，比如短整型。整型列的执行速度往往更快。

考虑使用垂直分区。比如，我们可以把大字段或使用不频繁的字段分离到另外的表中，这样做可以减少表的大小，让表执行得更快。

我们还可以把一个频繁更新的字段放到另外的表中，因为频繁更新的字段会导致MySQL Query Cache里相关的结果集频繁失效，可能会影响性能。

需要留意的一点是，垂直分区的目的是为了优化性能，但如果将字段分离了到分离表后，又经常需要建立连接，那可能就会得不偿失了，

所以，我们要确保分离的表不会经常进行连接，这时，用程序进行连接是一个可以考虑的办法。

存储精确浮点数时必须使用DECIMAL替代FLOAT和DOUBLE。

建议使用UNSIGNED类型存储非负值。

建议使用INT UNSIGNED存储IPV4。可以使用INET_ATON()、INET_NTOA()函数进行转换，PHP里也有类似的函数如 ip2long()、long2ip()。

整形定义中不添加显示长度的值，比如使用INT，而不是INT(4)。

建议不要使用ENUM类型。

尽可能不要使用TEXT、BLOB类型。

在VARCHAR(N)中，N表示的是字符数而不是字节数，比如VARCHAR(255)，最大可存储255个汉字。需要根据实际的宽度来选择N。

此外，N应尽可能地小，因为在MySQL的一个表中，所有的VARCHAR字段的最大长度是65535个字节，

进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存(对于这一点，MySQL 5.7后有了改进)。

字符集建议选择UTF-8。

存储年时使用YEAR类型。

存储日期时使用DATE类型。

存储时间时(精确到秒)建议使用TIMESTAMP类型，因为TIMESTAMP使用的是4字节，DATETIME使用的是8个字节。

不要在数据库中使用VARBINARY或BLOB存储图片及文件等。MySQL并不适合大量存储这种类型的文件。

JOIN(连接)字段在不同表中的类型和命名要一致。

如果变更表结构可能会影响性能，则需要通知DBA审核。

7.4 SQL语句

执行一些大的DELETE、UPDATE、INSERT操作时要慎重，特别是对于业务繁忙的系统，要尽量避免对线上业务产生影响。

长时间的锁表，可能会导致线上部分查询被阻塞，甚至导致Web应用服务器宕机。

解决的方案是，尽可能早地释放资源， 尽可能把大操作切割为小的操作，

比如使用LIMIT子句限制每次操作的记录数，也可以利用一些日期字段，基于更小粒度的时间范围进行操作。

我们也可以基于自增字段ID分批分段删除数据，如下的例子是一个定时删除线上数据的脚本，interval变量用于设置每次循环删除的记录数，i变量用于控制循环的次数。

由于在删除记录的同时，可能也插入了记录，因此设置为最后一次删除的记录数小于500($delRow-le 500)时，退出循环。

interval=200000

i=1

while [ $i -lt 100 ]

do

delRow=`mysql db_name 2>>$logFile <

set @minMid=(select min(id) from table_name) ;

delete from table_name where id < @minMid + $interval + 500 and date_time < ‘ 2014-10-10 00:00:00’ ;

select ROW_COUNT();

EOF`

if [ $? -ne 0 ] ; then

echo “ delete table_name failed” | tee -a $logFile

exit 1

fi

echo “ $i round: delete $delRow rows”

if [ $delRow -le 500 ] ; then

break

fi

sleep 1

i=$(($i + 1 ))

done

说明：

select row_count(); 能够获取到上一条update语句或delete语句影响的行数。update前后的值一样，则影响的行数为0。

select found_row(); 能够获取到上一条select语句查询的行数。

下面列举一些相应的法则：

不要使用ORDER BY RAND()。

避免使用SELECT *语句，SELECT语句只用于获取需要的字段。

使用预编译语句(prepared statement)，可以提高性能并且防范SQL注入攻击。

分割大操作。

SQL语句中IN包含的值不应过多，建议少于100。

一般情况下在UPDATE、DELETE语句中不要使用LIMIT。

WHERE条件语句中必须使用合适的类型，避免MySQL进行隐式类型转化。

INSERT语句必须显式地指明字段名称，不要使用INSERT INTO table()。

避免在SQL语句中进行数学运算或函数运算，避免将业务逻辑和数据存储耦合在一起。

INSERT语句如果使用批量提交(如INSERT INTO table VALUES(),(),()……)，那么VALUES的个数不应过多。一次性提交过多的记录，会导致线上I/O紧张，出现慢查询。

避免使用存储过程、触发器、函数等，这些特性会将业务逻辑和数据库耦合在一起，并且MySQL的存储过程、触发器和函数中可能会存在一些Bug。

应尽量避免使用连接(JOIN)，连接的表也不宜过多。

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

建议使用合理的分页技术以提高操作的效率。

如果性能没有问题，则只在主库上执行后台查询或统计功能。如果必须在从库上执行大的查询，那么应该先通知DBA增 加专门用于生产查询的从库。

7.5 SQL脚本

SQL脚本必须去除^M符号。

Windows系统中，每行的结尾是“”，即“\r\n”；Mac系统里，每行的结尾是“”，即'\r'。Unix/Linux系统里，每行的结尾是换行CR，即“\n”。

三个系统行的结尾各不相同，这会导致的一个直接后果是，Unix/Mac系统下的文件在Windows里打开时，所有的文字会变成一行；

而Windows里的文件在Unix/Mac下打开，在每行的结 尾可能会多出一个^M符号。而在SQL脚本中，必须要将此符号去除。

对于存储过程或触发器，升级脚本里应该正确设置分隔符(DELIMITER)。

对于函数，需要确认DETERMINISTIC。

如果没有特殊需要，应该一律使用InnoDB引擎和utf8字符集。

升级脚本应尽量做到方便回滚、可重复执行。

必须保证注释的有效性(注：MySQL注释可以使用“--”、“#”或“/**/”，其中“--”后面跟内容时一定要有空格，由于“--”这种注释方法经常导致出错，建议统一使用“#”进行注释)。

对一个表的表结构的变更，应合并为一条SQL实现。

SQL文件必须是UTF-8无BOM格式的文件。对于存在非英文字符的升级文件，可以用file命令确认它是否为一个UTF-8编码的文件。

例如： [linux1]$ file upgrade.sql

upgrade.sql: UTF-8 Unicode text, with very long lines

需要留意的是，英语字母的utf8编码和ASCII编码是一样的。对于一个全英文字母的文件，file命令不会指明这是一个UTF-8编码的文件。

file命令对于GBK等字符集可能也会识别不佳。

对于开发和测试环境，建议制订严格的规范，让大家都使用UTF-8编码的文件。

可以使用enca、iconv等命令批量转换文件。

iconv的命令格式为：iconv -f encoding -t encoding inputfile > outputfile

iconv-l可列出所支持的字符集。

如下命令将转换GBK字符集的aaa.txt文件为utf8字符集的bbb.txt。 iconv -f gbk -t utf-8 aaa.txt > bbb.txt

一些编辑工具可以轻易地转换文件格式，图7-1展示了notepad++转换编码的菜单项。

一些初始化数据的操作，也可以用mysqldump导出测试/开发环境数据，然后提交给DBA升级生产环境数据库。

mysqldump可以保持最佳的兼容性，而其他的客户端工具导出的文件则可能存在一些异常或不兼容的情况。

导出导入数据时需要注意MySQL Server和客户端工具的版本。

由于一般软件都是向后兼容的，因此在高低版本间导出导入数据时，如果大版本是一致的，比如，都是MySQL 5.1，一般是不会有什么问题的。

但如果大版本不一致，则可能存在兼容性的问题，如从MySQL 5.0导入到5.1，或者从MySQL 5.1导入到5.0，请尽量遵循以下原则。

从MySQL Server低版本导入数据到MySQL Server高版本时，应该直接以高版本的mysqldump导出，然后导入高版本的 MySQL Server中，当然，以低版本的mysqldump导出可能也行。

从MySQL Server高版本导入数据到MySQL Server低版本，应该以低版本的mysqldump导出，然后再导入低版本的MySQL Server。

7.6 数据架构的建议

每张表的数据量控制在5000万以下。

推荐使用CRC32求余(或者类似的算术算法)进行分表，表名后缀使用数字，数字必须从0开始并等宽，比如散100张表，后缀则是从00-99。

使用时间分表，表名后缀必须使用固定的格式，比如按日分表为user_20110101。

7.7 开发环境、测试环境的配置参数建议

假设我们统一字符集为utf8，统一默认引擎为InnoDB，那么建议默认的配置文件my.cnf如下，

这份配置文件没有进行关注性能方面的调整，大家可以对照自己的环境修改或增加适当的参数。

[client]

port = 3306

socket = / tmp/mysql.sock

default-character-set = utf8

[mysqld]

character-set-server = utf8

port = 3306

socket = /tmp/mysql.sock

user = mysql

skip-external-locking

max_connections=3000

max_connect_errors=3000

thread_cache_size = 300

skip-name-resolve

server-id = 1

binlog_format=mixed

expire-logs-days = 8

sync_binlog=60

innodb_log_file_size = 256M

default-storage-engine=innodb

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

default-character-set = utf8

7.8 数据规划表

数据库是一项比较紧缺的资源，往往需要进行数据规划和资源申请。

表7-1是一个申请资源的范本表，可以作为研发团队提交给DBA进行申请资源之用。

由于互联网业务的变化可能会很快，往往难以准确地估计数据量和业务量的增长速度，

所以，对于这两项可以要求不必非常准确，但最好不要有数量级的估算错误，你规划得越准确，后续的运维成本就越低，调整的代价就越小。

表7-1 申请资源范本表：

insert事务/天

update和delete事务/天

select次数/天

峰值事务增比幅度 其中，峰值事务增比幅度=最高峰值事务/平均事务。

长查询事务 对于长查询事务，因为数据库不是很擅长同时处理批量大事务和实时短事务，因此对于线上的繁忙生产系统，一般是不允许有很多长查询存在的，以免影响线上业务。

如果有统计类的分析业务，则建议尽早规划，将统计数据分离到其他的数据库实例。

前端并发连接数

数据重要程度

数据敏感程度

数据保留时长

预计三个月后的数据文件大小 关于数据文件的大小，建议使用真实的数据进行估算。

如输入30万条数据，然后使用如下查询验证数据大小。

select sum(data_length+index_length) from information_schema.tables where table_schema='db_name' and table_name='table_name';

由以上的结果可以估算出100万数据的大小。

预计三年以后的数据文件大小

7.9 其他规范

批量导入、导出数据时DBA需要进行审查，并在执行过程中观察服务。

批量更新数据时，如执行UPDATE、DELETE操作，DBA也要进行审查，并在执行过程中观察服务。

产品出现非数据库平台运维导致的问题和故障时，请及时通知DBA，以便于维护服务的稳定性。

业务部门推广活动，请提前通知DBA进行服务和访问评估。

如果业务部门出现人为误操作而导致数据丢失，则需要恢复数据，请在第一时间通知DBA，并提供数据丢失的准确时间，误操作语句等重要线索。

小结：

规范的根本目的是为了帮助开发、释放人的潜力，提高生产力，而不是约束人，让人失去发挥的空间。

标准的建设任重而道远，在制定的过程中，前期宜宽不宜紧，逐渐收集信息，提高规范的适应性，最终是可以达到一个平衡的。

友好的规范既能保证运维的安全、便捷，也能让研发、测试团队的工作更加高效。

它还应该是一个知识的集聚地，让接触规范的人尽快变得训练有素。