当前位置：首页 > article >正文

MySQL开发05-MySQL开发规范

article 2024/11/13 9:36:59

文章目录

1、命名规范
2、表设计规范
3、索引规范
4、SQL语句规范
5、SQL脚本规范
6、数据架构规范
7、配置文件建议
8、其他规范
9、总结

1、命名规范

命名应有意义，包括库名、表名、用户名等，以使用方便记忆、描述性强的可读性名称为第一准则，尽量避免使用缩写或代码来命名。传统使用缩写或代码的方法是出于一些历史原因，比如希望节省空间、尽快加载数据等，但随着硬件的快速发展，目前来说这么做的意义不大。
命名不要过长（应尽量少于25个字符）；
不要使用保留字；
数据库、表都用小写，尽量不要使用除下划线、小写英文字母之外的其他字符；
索引的命名以idx_为前缀；
如果同一个数据库下有不同的应用模块，则可以考虑对表名用不同的前缀标识；
注意字段类型的一致性、命名的一致性，同一个字段在不同的表中也应是相同的类型或长度；
备份表时加上时间标识；

2、表设计规范

表的存储引擎优先选择InnoDB引擎，字符集建议选择UTF-8。
每个表都应有主键。
尽量将字段设置成NOT NULL：NULL值存储需要额外空间，且会导致比较运算更复杂，使优化器更难以优化SQL。
尽量使用更短小的列。
尽量使用整型：整型列的执行速度往往更快。整型定义中不需要添加显示长度的值，比如使用INT，而不是INT(4)。
存储精确浮点数时必须使用DECIMAL替代FLOAT和DOUBLE。
建议使用UNSIGNED类型存储非负值。
建议使用INT UNSIGNED存储IPV4：可以使用INET_ATON()、INET_NTOA()函数进行转换，PHP里也有类似的函数如ip2long()、long2ip()。
不要使用ENUM类型。
尽量不要使用TEXT、BLOB类型。
在VARCHAR(N)中，N表示的是字符数而不是字节数，比如VARCHAR(255)，最大可存储255个汉字。需要根据实际的宽度来选择N。N应尽可能地小，因为在MySQL的一个表中，所有的VARCHAR字段的最大长度是65535个字节，进行排序和创建临时表一类的内存操作时，会使用N的长度申请内存。
不要在数据库中使用VARBINARY或BLOB存储图片及文件等：MySQL并不适合大量存储这种类型的文件。
JOIN（连接）字段在不同表中的类型和命名要一致。
. 可以把大字段或使用不频繁的字段分离到另外的表中，这样做可以减少表的大小，让表执行得更快。但如果将字段分离了到分离表后，又经常需要建立连接，那可能就会得不偿失了，所以，我们要确保分离的表不会经常进行连。

3、索引规范

建议单表索引不超过5个，单索引中字段数不超过5个；
建议索引字段中，选择性高的字段放在前面；
ORDER BY、GROUP BY、DISTINCT的字段需要放在复合索引的后面，也就是说，复合索引的前面部分用于等值查询，后面的部分用于排序。
UPDATE、DELETE语句需要根据WHERE条件添加索引。
建议不要使用“like %value”的形式，因为MySQL仅支持最左前缀索引。
对长度过长的VARCHAR字段（比如网页地址）建立索引时，可以增加一个对VARCHAR字段的散列字段，散列字段类型为整型，然后对该散列字段建立索引，这样比对长VARCHAR字段直接排序要好。
存储域名地址采用反向存储的方法：比如把news.sohu.com存储为com.sohu.news，方便在其上构建索引和进行统计。
8 . 合理使用复合索引：复合索引(a,b,c)可以用于“where a=?”、“where a=?and b=?”、“where a=?and b=?and c=?”等形式，但对于“where a=?”的查询，可能会比仅仅在a列上创建单列索引查询要慢，因此需要在空间和效率上达成平衡。
合理利用覆盖索引：由于覆盖索引一般常驻于内存中，因此可以大大提高查询速度。注意把范围条件放到复合索引的最后，WHERE条件中的范围条件（BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引。

4、SQL语句规范

将对大量数据的增删改操作分割为多批次小数据量的操作：特别是对于业务繁忙的系统，长时间的锁表，可能会导致线上查询被阻塞、应用宕机。解决方案是，尽可能早地释放资源。比如使用LIMIT子句限制每次操作的记录数，也可以利用一些日期字段，基于更小粒度的时间范围进行操作。
尽量避免使用SELECT * 语句：只获取需要的字段。
优先使用预编译语句（prepared statement）：可以提高性能并且防范SQL注入攻击。
IN列表包含的值不应过多：建议少于100。
尽量避免在UPDATE、DELETE语句中不要使用LIMIT。
尽量避免MySQL进行隐式类型转化。
INSERT语句必须显式地指明字段名称：不要使用INSERT INTO table()。
避免在SQL语句中进行数学运算或函数运算，避免将业务逻辑和数据存储耦合在一起。
避免使用存储过程、触发器、函数：这会将业务逻辑和数据库耦合，且存在Bug。
连接的表也不宜过多；
尽量减少和数据库的交互次数；

5、SQL脚本规范

一律优先使用InnoDB引擎和utf8字符集。
SQL脚本必须去除^{M符号。Windows系统中，每行的结尾是“<回车><换行>”，即“\r\n”；Mac系统里，每行的结尾是“<回车>”，即’\r’。Unix/Linux系统里，每行的结尾是换行CR，即“\n”。三个系统行的结尾各不相同，这会导致的一个直接后果是，Unix/Mac系统下的文件在Windows里打开时，所有的文字会变成一行；而Windows里的文件在Unix/Mac下打开，在每行的结尾可能会多出一个}M符号。而在SQL脚本中，必须要将此符号去除。
必须保证注释的有效性，建议统一使用“#”进行注释（注：MySQL注释可以使用“--”、“#”或“/**/”，其中“–”后面跟内容时一定要有空格，由于“–”这种注释方法经常导致出错）。
SQL文件必须是UTF-8无BOM格式的文件。对于存在非英文字符的升级文件，可以用file命令确认它是否为一个UTF-8编码的文件，如下图：

file  t3.txt

转储数据优先使用mysqldump，因为mysqldump保持了最佳兼容性；

6、数据架构规范

单表数据量控制在5000万以下。

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