位数字,小数点单独占一个字节;
decimal数据类型可以用来存储比bigint更大的整数数据,这比使用VARCHAR存储数据更高效
五.数据库SQL开发规范
1.建议使用预编译语句进行数据库操作
预编译的SQL语句使用相同的查询计划,节省了MySQL生成查询计划的时间,而且可以防止SQL注入
2.避免数据类型的隐式转化
当数据类型不一致的时候,MySQL会进行数据的隐式转换,这会导致建立在列上的索引失效。
3.充分使用表上已存在的索引
(1)避免使用双%进行查询,e.g.LIKE'%123%'或LIKE'%123'都会导致列上的索引失效,但是使用LIKE'123%'的查询方式是可以利用到索引的。
(2)一个SQL只能利用复合索引中的一列进行范围查找
如果A、B、C三列进行联合查询,A列进行范围查询,那么B、C上的索引就不会再被用到了,此时我们应该将A列放在联合索引的右侧
(3)使用leftjoin和notexists来替换notin,后者有可能造成索引失效
4.禁止使用Select*进行查询,应该使用Select<字段名>进行查询
5.禁止使用不含字段列名的Insert语句,不应该使用insertintotvalues(1,2,3)这样的语句进行数据插入,而应该使用insertintot(a,b,c)values(1,2,3)这样的语句进行插入操作,防止表结构变更造成插入错误。
6.避免使用子查询,尽量转化为Join查询方式
子查询无法使用索引;子查询会产生临时表表操作,而这些临时表是没有索引的,会消耗过多的CPU和IO资源。
7.避免使用JOIN关联过多的表
每JOIN一个表就会占用一部分内存,MySQL可以通过join_buffer_size设置这部分内存的大小;
Join操作也会产生临时表操作,影响查询效率;
MySQL最多允许关联61个表,建议不要超过5个
8.减少同数据库交互的次数
数据库更适合处理批量操作,e.g.分页查询的时候,可以一次拉去很多条数据,分批进行返回,而不要分多次每次从数据库中拉去一小部分数据
9.使用IN代替OR,IN的值不要超过500个
IN操作可以有效的使用到索引,而OR一般是用不到索引的。
10.禁止使用orderbyrand()进行随机排序
rand()操纵会将所有的符合条件的数据加载到内存中进行排序,这样会消耗大量的CPU和IO资源,我们可以在SQL查询之前生成好随机查找的数据,然后执行SQL进行数据拉取
11.WHERE从句中禁止对列进行函数转换和计算
对列进行函数转换和计算,会导致无法使用索引,e.g.当我们要查询日期的时候,可能使用下面的语句进行查找:
wheredate(createTime)='20180423'这样,我们便无法使用createTime上的索引,建议使用下面的语句:
wherecreateTime>='20180423'andcreateTime
12.UNION和UNIONALL的操作,可以合并两个查询的结果集
当两个结果集之间明显没有重复值的时候,尽量使用UNIONALL,这是因为UNION会将所有的数据放到一个临时表中,然后进行去重操作
13.拆分复杂的大SQL为多个小SQL进行查询,再合并
MySQL的一个SQL只能使用一个CPU进行计算,大SQL拆分之后可以通过并行的方式查询,再合并多个结果集,从而提高查询的效率。
六.数据库操作行为规范
1.超过100w行数据的写操作,要分批进行
大量的写操作会造成严重的主从延时,造成数据库操作的阻塞;分批进行可以有效的避免大事务操作
对于大表结构的修改一定要使用pt-online-schema-change这样的工具进行,使用这个工具进行大表结构修改的时候,会首先复制创建一个结构相同的新表,在这个新表的机构上进行修改,再将原表中的数据复制到新表中,同时在原表上添加一些触发器,保证原表中新添加的数据能够及时添加到新表中,在原表中数据全部复制完成之后,会在原表上添加一个很短的时间锁,将新表的名称命名为原表的名称,并将原表删除掉,这样做可以避免大表修改产生的主从延迟的问题,避免在对表字段修改时产生的较长时间的锁表问题。
2.禁止为程序使用账号授予super权限
MySQL在达到最大链接数之后,还允许1个具有super权限的用户进行连接,当为前端用户授予super权限之后,DBA处理账号就无法再以super权限进行登录排查问题了,在为程序连接账号授予数据库权限时,应该遵循最小权限的原则,并不能有类似droptable的权限。