这周公司开发工作比较悠闲,工作几乎压在设计上游,于是整理了下公司开发的文档,包括项目架构、服务器运维、规范、api
对接、基本依赖信息等。如下是包含其中的MySQL
开发规范,根据社区很多的博文参考以及结合自身小团队开发情况总结。
tmp_
开头、以日期结尾,备份表必须以bak_
开头、以日期结尾InnoDB
作为表的存储引擎
在MySQL 5.6
以后,InnoDB
被设置成默认的存储引擎,支持事务和行级锁。
UTF8MB4
字符编码
UTF8MB4
字符编码支持中文储存以及表情存储,兼容性杠杠的。
comment
设定注释。
IO
过高。数据量膨大建议采用分表、合理分区等方案。
MySQL
中,数据表列数最大限制为4096
列 ,每条元祖数据总和大小不能超过65535
字节,常用的字段与基本不常用的字段、细分不同业务的数据分开表设计存储,减小表宽度,保证热数据的内存缓存命中率,降低CPU
使用率以及降低IO
流。
MySQL
虽然支持对文件对象的存储,但是开发人员是不允许、不推荐这样做的。文件通常是很大的,转成二进制数据将是一串很长的字符串,无疑占用数据库很大的存储空间,在数据库读写更是消耗内存和占用大量的IO
流,最终导致查询的效率低下。一般文件是存放于文件服务器,将文件服务器的路径存储于数据库中。
super
权限只能属于DBA
,不能赋予项目程序SQL
运行性能的习惯,可以借用性能分析工具
譬如:EXPLAIN
语句 | showprofile
| mySQLsla
等。
DBA
的审阅和同意InnoDB
表都必须含有一个主键
InnoDB 是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引,但是表的存储顺序只能有一种 InnoDB是按照主键索引的顺序来组织表的。不要使用可能会更新的列作为主键,同时尽量不要使用UUID
、MD5
、HASH
等无序的字符串作为主键。在没有特别的情况下,要使用自增的整型或发号器作为主键。
nice
。范式设计是数据结构的一种思想,但是我们应当灵活使用,一味追求三范式无疑会影响程序的性能,适当的冗余是可以提高查询的效率的,前提要保证是主键的冗余。
IO
流,导致操作的效率下降。将可能将字段按照业务细分、冷热的条件进行分表设计。
ext
、ext_1
、extend_n
,时间一长,好几个这样的字段,即使每一个都有comment
,也会造成SQL
的可读性,特别是在构建SQL
语句的时候。
MySQL
的表空间,是性能优化的姿势之一。同时,索引列定义空间越大也会导致建立索引的所需空间也越大。应当严禁定义字段,譬如
IP
应使用UNSUGNED
或者INT
结构类型,在PHP中可以使用long2ip
与ip2long
函数进行互转
性别应使用CHAR(1)
,即定长的字符串类型
... ...
TEXT
、BLOB
、ENUM
数据类型
MySQL 内存临时表不支持TEXT
、BLOB
这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行,毋庸置疑会降低查询的效率。MySQL
对索引字段长度是有限制的,TEXT
或BLOB
类型只能使用前缀索引。
ENUM
数据类型
在MySQL
中,存储枚举类型的数据在库中,字段列中保存的值实际为整数,特别容易导致开发者混乱,同时在查询使用排序是基于数值整型的,虽然可以使用ORDER BY FIELD()
,但是会导致索引失效,尽量避免这么做。
NOT NULL
类型
NULL
列比较特殊,需要额外的空间来保存,同时会造成索引失效。
TIMESTAMP
与INT
替换DATETIME
存储时间
很明显,TIMESTAMP
与INT
占4位字节,而DATETIME
占8位字节。那么存储时间应该如何选择TIMESTAMP
与INT
呢?TIMESTAMP
的可读性高而INT
的灵活性高,因而经常需要使用计算操作的应当使用INT
存储,否则使用TIMESTAMP
。
DECIMAL
数据类型
谈到钱这个东西呢,精确是非常重要的,即便要浪费存储空间、笑?~DECIMAL
类型为精准浮点数,在计算时不会丢失精度,可以自定义其长度,可用于存储比 bigint 更大的整型数据。
CHAR
节省存空间、降低内存使用率、提高读写性能。
UNSIGNEG
存储非负整数
节省存空间、降低内存使用率、提高读写性能。
SQL
语句必须带上索引作为条件id
设置成主键的同时再设置成唯一索引,那就是重复索引,如果创建了索引(a
,b
),再设置a
索引,则a为冗余索引,这两种错误的操作都会降低读写的性能。
MySQL
不擅长于运算,需要计算的应该移至代码业务层。总而言之,凡是计算都要移至代码业务层(MySQL
不擅长于运算)。
JOIN
连表查询,提高效率特别明显。
SQL
语句必须带上索引作为条件,谨记谨记
哪些是危险的SQL
语句呢,删、改皆为危险的语句,一定要记住带上WHERE
。
SQL
执行的流程,SQL
先解析、预编译处理再生成执行计划,最后调用引擎的api
方法返回执行的结果,使用预编译的操作姿势,在读写的时候可以省去预编译的时间,终而提高执行效率。
SELECT *
查询字段
要什么SELECT
什么,不能多,否则可能导致覆盖索引失效,消耗更多的 CPU
和 IO
以网络带宽资源。
MySQL
中,数据会存在隐式转换,当该字段发生转换时,索引会造成失效。
INSERT
操作
这是一种错误的做法,对于表的改动后会造成比较大的影响。
INSERT INTO user VALUES ('alicfeng',23); # 应该这样操作 INSERT INTO user (`username`,`age`) VALUES ('alicfeng',23);
JOIN
替代子查询操作
子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。 特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大。 由于子查询会产生大量的临时表也没有索引,所以会消耗过多的 CPU
和 IO
资源,产生大量的慢查询。
JOIN
关联过多的表
一般情况下,建议JOIN
的表不要超过5个,JOIN
多表查询比较耗时时间,关联的表越多越耗时间,防止执行超时或死锁。
SQL
操作,降低IO
消耗的同时也提高了执行效率,譬如
UPDATE user SET username='alicfeng' FROM id=1995; UPDATE user SET age=23 FROM id=1995; # 合并操作成一条SQL UPDATE user SET username='alicfeng',age=23 FROM id=1995;
IN
代替OR
语句ORDER BY RAND()
随机排序语句
会把表中所有符合条件的数据装载到内存中,然后在内存中对所有数据根据随机生成的值进行排序,并且可能会对每一行都生成一个随机值,如果满足条件的数据集非常大,就会消耗大量的 CPU
和 IO
及内存资源。
WHERE
语句中进行计算
对列进行函数转换或计算时会导致无法使用索引。
# 索引会失效 WHERE DATE(create_date)='20190308'; # 灵活使用[推荐] WHERE create_date>='20190308' AND create_date<'20190309';
UNION ALL
而不是使用UNION
在已知数据没有重复或无须删除重复行的前提下,因为UNION
需要重复值扫描,降低效率。
MySQL
不擅长数学运算和逻辑判断。
SQL
语句简单化IN
语句参数的个数尽量控制在1000以内LIMIT
分页查询效率,LIMIT
越大效率越低
在使用LIMIT
做分页时,更改巧妙地处理查询,譬如使用S1
替换成S2
,将有效地提高查询的效率。
# S1 SELECT `username` FROM `user` LIMIT 10000,20; # S2 SELECT `username` FROM `user` WHERE id>10000 LIMIT 20;
SQL
语句必须全部为大写,每个词必只允许只有一个空格符
编写规范,必须统一并遵循。
EXIST|NOT EXIST
替代IN | NOT IN
LIKE
添加%
前缀进行模糊查询
%
前置会导致索引失效