写点什么

技术站最全 MySQL 数据库实战规范,java 程序语言基础王锦盛

用户头像
极客good
关注
发布于: 刚刚

null 值的列,很难对索引优化;null 的列对占用更多的空间,因为需要额外的空间来标识。null 的查询操作,也过于麻烦,只能采用 is null 或 is not null,而不能采用=、in、<、<>、not in 、!=操作符,如:where name!='laogu',是不会查询出 name 为 null 的值的。


3、禁止使用 Text、BLOB 类型


Mysql 内存临时表不支持 Text、Blob 类型,如果查询中包含这些类型,就不能使用内存临时表,而会采用磁盘临时表,导致性能很差会浪费更多的磁盘和内存空间,导致数据库内存命中率低,影响数据库性能如果一定要使用,建立单独的扩展表。


4、禁止使用 ENUM、可用 Tinyint 代替


修改 Enum 值时,需要使用 alter 语句 order by 操作效率低。


5、禁止使用小数


直接使用整数,小数容易有精度差异,导致金额对不上。


6、使用 Timestamp 或 Datetime 类型存储时间


经常小伙伴们用 String 类型储存时间缺点

1:无法用日期函数进行计算比较缺点

2:用户字符串存储,占用更多的空间。

索引设计规范

1、每张表索引不要超过 5 个一般常识索引可以增加查询效率,但同样降低了插入和更新的效率。


但针对查询,索引也不是越多越好。因为 mysql 优化器在选择如何优化查询时,会根据查询信息,对每一个用到的索引进行评估,以生成一个最好的执行计划,如果有很多个索引,就会增加 mysql 优化器的执行时间,反而降低了查询性能。


2、区分度不高、更新频繁的列 不建议加索引


更新频繁会变更 B+树,大大降低数据库的性能。区分度(区分度=列中不同值的数量/列的总行数),区分度不高(如:性别,只有男、女、未知)建立索引没有意义,性能和全表扫描差不多。


3、联合索引时,把区分度高的放到最左侧因为 mysql 的索引结构原理,联合索引有一个原则,就是最左索引原则。


a、尽量把区分度高的


【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
复制代码


放在联合索引的最左侧


b、把查询频繁的列放在最左侧

c、把字段长度小的放到最左侧,这样内存页存储数据量越大,IO 性能越好。

SQL 开发规范

1、禁止使用 select 要用 select 列名 代替 select


原因:1、消耗更多的 CPU、IO 开销

2、无法使用覆盖索引

3、可减少表结构的改动,带来的代码影响。


2、禁止使用属性隐式转换


隐式转换会导致索引失效,如:select name from customer where id='1000';id 为整型,正确的写法 select name from customer where id=1000。


3、建议使用预编译语句进行数据库操作


预编译语句可以重复使用优化计划,减少 SQL 编译时间,避免 SQL 注入。


4、禁止使用不含字段的 insert 语句


如:insert into txxxx values(xxx,xxx,xxx) 应使用 insert into txxx(c1,c2,c3) values(xxx,xxx,xxxx)防止表结构变化。


5、禁止负向查询,以及 %开头的模糊查询


负向查询为:not、!=、<>、not in、not like 等,会导致全表扫描 %开头也会导致全表扫描。


6、一个 SQL 只能利用复合索引中的一列进行范围查询


如:有 c1、c2、c3 三个列建立联合索引,在查询条件中有 c1 列的范围查询,则在 c2、c3 列上的索引将不会被用到。如果一定要用 c1 做范围查询,那把 c1 列放到联合索引的最右侧。


7、禁止在 where 条件上对属性使用函数或表达式


如:select id from torder where fromunixtime(createtime) >= '20190101'应改为 select id from torder where createtime >= unixtimestamp('20190720')


8、禁止大表使用 join 查询,禁止大表使用子查询


会产生临时表,消耗较多的内存、cpu 资源,影响性能。


9、避免使用 JOIN 关联太多的表


对于 Mysql 来说,是有关联缓存的,缓存的大小是由 joinbuffersize 参数进行设置。对于同一个 SQL 多关联一个表,就会多分配一个关联缓存,越多的 join,就消耗越多的内存。如果 joinbuffersize 设置不合理,就会导致数据库内存溢出,影响性能和稳定性。


10、禁止使用 OR 条件,必须改为 IN 查询


绝大多数情况下,Mysql 的 OR 查询是不能命中索引的。


11、尽量减少与数据库的交互次数


能够一次性读取尽可能多的数据,减少和数据库的交互,可以极大提升数据库的吞吐量。


12、禁止使用 order by rand()进行排序


会把表中的所有数据都加到内存中,然后在对内存的数据进行随机排序,会消耗较多的 CPU、IO 以及内存资源推荐在程序中生成一个随机值,传给数据库的方式。

总结

上面有很多规范,也许小伙伴一时间记不住,慢慢练习就会越熟练。老顾这里给大家分享一个索引口诀,方便记忆。


索引优化口诀全值匹配我最爱,最左前缀要遵守;带头大哥不能死,中间兄弟不能断;索引列上少计算,范围之后全失效;Like 百分写最右,覆盖索引不写星;不等空值还有 or,索引失效要少用;VAR 引号不可丢,SQL 高级也不难!

用户头像

极客good

关注

还未添加个人签名 2021.03.18 加入

还未添加个人简介

评论

发布
暂无评论
技术站最全MySQL数据库实战规范,java程序语言基础王锦盛