数据库设计开发规范--基于性能的考虑部分

最新推荐文章于 2022-08-08 23:13:59 发布

cudi7618

最新推荐文章于 2022-08-08 23:13:59 发布

阅读量161

点赞数

文章标签：数据库 java

发布了新一版的数据库开发设计规范，增加了基于性能考虑的部分内容：

贴在这里跟大家共享

基于性能考虑的原则：

所有的表需设置主键字段，mysql innodb 引擎必须设置主键字段，主键字段不能太长，建议以独立于业务数据的自增长数值字段为主键字段。
mysql 数据库单行的长度应尽可能的小，主要是基于mysql 的数据块的结构的考虑。
PG 数据库频繁更新的字段与长文本字段建议分离存储，主要基于平pg数据库的mvcc机制的考虑。
单表的索引数目应该控制在5个以内，同一个字段上索引控制在2个以内，复合索引的字段数控制在3个以内。针对mysql的covering index 优化原则，可以适当控制索引字段数
where 条件中，限制数据量最严格的字段一般建议作为复合索引的前导字段，如果各字段的选择性相近，应把访问最频繁的字段作为前导字段。
索引应该具有高选择性,原则count(distinct)/count(*) >0.6不应该给“性别列"添加索引,针对PG 数据库，可以考虑针对某些字段的高选择性，建立条件索引。
Pg数据库在同一个表上可以使用到多个索引，mysql 与oracle 在这方面有些欠缺，pg数据库可以支撑较复杂的sql查询，pg更适合于一个sql搞定所有的事情，而不是多个sql 搞定一个事情。
Mysql 数据库不适合嵌套sql的执行，高并发下有性能问题，建议把嵌套sql 拆解为多个单表查询的sql进行
group by 或者order by 的字段应该设置为前导字段（需根据业务调整）。
视图中不允许order by 排序。
Mysql中order by 字句的字段名如果落在不同的表上，会生成filesort，高并发下，严重影响性能。
Mysql 数据库如果group by 确定不需要排序可以使用order by null 避免排序操作执行，mysql 测试性能可以提高15%左右，PG 不支持该操作，oracle 10g 默认是不排序的了。
针对mysql的字符类型字段的查询可以考虑添加前缀索引，一般情况下前6个字符的索引已经可以达到3000w的区分度。（26^6=308915776）。
索引列不建议参与运算，索引列上的函数计算将导致索引失效，除非建立函数索引。
针对高并发的WEB应用，尽量避免使用外键，一定要使用外键约束，则外键字段上应建立索引。数据一致性要求高的应用，可谨慎使用外键。
注意where条件中数据类型的一致性，特别是索引字段，避免条件隐性转换而造成不走索引，例如：cms系统中channelid为字符型如果条件 where chennalid='123'则能走索引，而where channelid=123 是不会走索引的，原因是 oracle 解析的时候做了下隐性转换 where to_number(channel_id)=123.
PG 数据库针对java jdbc 的游标使用，必须设置autocommit=off 示例： con.setAutoCommit(false);

st =con.createStatement(ResultSet.TYPE_FO

RWARD_ONLY,

ResultSet.CONCUR_READ_ONLY);

st.setFetchSize(100000);

ResultSet rs = st.executeQuery(sql)；

mysql的query_cache 的缓存原则是sql语句的hash存储，sql语句中应该禁止使用运行时求值的函数，例如 where date 》 now（）应该修改为where date 》‘2012-01-01’这样格式的静态语句。
涉及到实时入库的问题，针对计数数据入库，建议先写内存，然后批量入库的策略，避免陷入性能问题。
针对数据库某个字段的修改，建议单独设置函数/方法处理，而不应该将整行数据，一起回写数据库，在近期的几个项目，出现过几次，因为更新单个字段而回写整个对象，造成数据库的大量的垃圾日志信息。
Sql语句的编写，原则上应该使用绑定变量，pg 与oracle 数据库必须使用绑定变量，优先选用jdbc 的prepareStatement()方法，mysql在这方面比较弱，没有强制要求。
警惕基于limit m,n 方式的分页操作，offset 越大，性能越差，应该考虑基于主键分页方式。
Mysql数据库的二级索引，在字段的列表的最后，加上primarykey 字段，在大多数情况下，可以改善优化器的优化策略，对一部分的排序操作有帮助，不会带来额外的空间损耗，这个功能从源代码级别上已经核实过了。
对表字段进行distinct 操作最好能转换成group by 操作，虽然同样是对表执行全表扫描，但是group by 的优势在于，扫描到相同的值时不再读取，而distinct 是每笔数据都要抓到内存去排序的。用group by减少了I/O操作。