sql语句开发教程（写了这么多年的SQL语句）

欢迎关注头条号：老顾聊技术，接下来我们就来聊聊关于sql语句开发教程?以下内容大家不妨参考一二希望能帮到您!

sql语句开发教程

欢迎关注头条号：老顾聊技术

精品原创技术分享，知识的组装工

1. 前言
2. 命名规范
3. 基础设计规范
4. 字段设计规范
5. 索引设计规范
6. SQL开发规范
7. 总结

我们小伙伴们经常使用到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该如何管理？

,