欢迎关注头条号:老顾聊技术,接下来我们就来聊聊关于sql语句开发教程?以下内容大家不妨参考一二希望能帮到您!
sql语句开发教程
欢迎关注头条号:老顾聊技术
精品原创技术分享,知识的组装工
目录
- 前言
- 命名规范
- 基础设计规范
- 字段设计规范
- 索引设计规范
- SQL开发规范
- 总结
我们小伙伴们经常使用到Mysql数据库,一般就这么一用,很少会考虑mysql里面的细节问题,如sql语句的规范,或索引有没有起到相应的效果,今天老顾就给大家介绍一下mysql实战
命名规范1、所有数据库对象都要小写字母、并用下划线分割
2、所有数据库对象不要用mysql关键字命名
3、库表的命名要达到看到此名称,就大概知道是干嘛的
4、临时库表要以tmp_为前缀,日期为后缀
5、备份库表要以bak_为前缀,日期为后缀
6、相同的数据,在所有表中的列名和类型要一致
基础设计规范1、在新建表时,要使用InnoDB引擎
因为InnoDB支持事务、行锁、性能更好
2、新库使用utf8mb4字符集
兼容更好,可以避免产生乱码,防止索引创建失败
3、表和字段必须加入中文注释
方便以后的系统维护
4、禁止使用存储过程、视图、触发器、Event
能够不占用数据库的资源,就不要占用;让这些计算上移到服务层。
将来的进行数据拆分方便,存储过程等是针对单实例的,无法适用分库分表的架构
5、单表数据量,控制在500万以内
当然mysql可以存储1000万数据,但过大后会影响mysql 的性能以及维护工作。
想要存储更多的数据,可以对数据进行拆分,分库分表设计来控制单表数据量
6、谨慎利用Mysql分区功能
在分区表中物理上面是多个文件,但逻辑上是一个文件,灵活度不够,而且跨分区查询效率低;还是建议使用物理分区,市面上也有一些中间件mycat、sharding-jdbc等
7、减少表的宽度、冷热数据分离、必须有主键
a、mysql表的列数限制可以为4096列,每一行的数据大小不能超过65535字节;
宽度越大,加载在内存中占用内存就越大,IO消耗越大。表的宽度建议在30左右,
b、要把经常用的数据列放在一起,这样可以一次性读取出来;把经常用不到的数据分离出去,这样极大提高效率
c、主键的好处,就是更好的利用索引,提高查询效率。不明白原理,可以看老顾之前的文章
8、禁止使用外键,交给程序控制
这个是不是和我们理解的不一样,为什么不要外键?
外键会导致表与表之间耦合,这样更新操作都会涉及到相关联的表,十分影响sql的性能,且容易造成死锁。
9、禁止使用预留字段
很多小伙伴为了以后的业务扩展,都喜欢在表中建立类似DEMO_1、DEMO_2字段,列名没有任何业务含义,而且类型都是用String代替。
预留字段另一个好处就是业务改变后,利用预留字段,SQL语句不需要改变,其实这个问题用一些ORM工具就能够很好的解决
字段设计规范1、优先选择符合业务的最小存储类型
可以有效节省数据库的空间,查询的时候也能够减少IO消耗
2、字段定义为Not Null,且提供默认值
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、尽量把区分度高的放在联合索引的最左侧
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 t_xxxx values(xxx,xxx,xxx)
应使用insert into t_xxx(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 t_order where from_unixtime(create_time) >= '20190101'
应改为
select id from t_order where create_time >= unix_timestamp('20190720')
8、禁止大表使用join查询,禁止大表使用子查询
会产生临时表,消耗较多的内存、cpu资源,影响性能
9、避免使用JOIN关联太多的表
对于Mysql来说,是有关联缓存的,缓存的大小是由join_buffer_size参数进行设置。
对于同一个SQL多关联一个表,就会多分配一个关联缓存,越多的join,就消耗越多的内存。
如果join_buffer_size设置不合理,就会导致数据库内存溢出,影响性能和稳定性
10、禁止使用OR条件,必须改为IN查询
绝大多数情况下,Mysql的OR查询是不能命中索引的
11、尽量减少与数据库的交互次数
能够一次性读取尽可能多的数据,减少和数据库的交互,可以极大提升数据库的吞吐量
12、禁止使用order by rand()进行排序
会把表中的所有数据都加到内存中,然后在对内存的数据进行随机排序,会消耗较多的CPU、IO以及内存资源
推荐在程序中生成一个随机值,传给数据库的方式
总结上面有很多规范,也许小伙伴一时间记不住,慢慢练习就会越熟练。老顾这里给大家分享一个转载网上的索引口诀,方便记忆
索引优化口诀
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
Like百分写最右,覆盖索引不写星;
不等空值还有or,索引失效要少用;
VAR引号不可丢,SQL高级也不难!
如果不能够理解,可以私信老顾哦!谢谢!!!
-End-
推荐阅读
1、不说“分布式事务”理论,直接上大厂阿里的解决方案,绝对实用
2、女程序员问到这个问题,让我思考了半天,Mysql的“三高”架构
3、大厂二面:CAP原则为什么只能满足其中两项?而不能同时满足
4、阿里P7二面:聊聊零拷贝的原理
5、秒杀系统的核心点都在这里,快来取
6、你了解如何利用token方式实现分布式Session吗?
7、Mysql索引结构演变,为什么最终会是那个结构呢?让你一看就懂
8、一场比赛涉及到的知识,用通俗易通的方式介绍并发协调
9、企业实战Redis全方面思考,你思考了吗?
10、面试题:Thread的start和run的区别
11、面试题:什么是CAS?CAS的作用以及缺点
12、如何访问redis中的海量数据?避免事故产生
13、如何解决Redis热点问题?以及如何发现热点?
14、如何设计API接口,实现统一格式返回?
15、你真的知道在生产环境下如何部署tomcat吗?
16、分享一线互联网大厂分布式唯一ID设计 之 snowflake方案
17、分享大厂分布式唯一ID设计方案,快来围观
18、你想了解一线大厂的分布式唯一ID生成方案吗?
19、你知道如何处理大数据量吗?(数据拆分篇)
20、如何永不迁移数据和避免热点? 根据服务器指标分配数据量(揭秘篇)
21、你知道怎么分库分表吗?如何做到永不迁移数据和避免热点吗?
22、你了解大型网站的页面静态化吗?
23、你知道如何更新缓存吗?如何保证缓存和数据库双写一致性?
24、你知道怎么解决DB读写分离,导致数据不一致问题吗?
25、DB读写分离情况下,如何解决缓存和数据库不一致性问题?
26、你真的知道怎么使用缓存吗?
27、如何利用锁,防止缓存击穿?重构思想的重要性
28、海量订单产生的业务高峰期,如何避免消息的重复消费?
29、你知道如何保障生产端100%消息投递成功吗?
30、微服务下的分布式session该如何管理?
,