Java Develop——基于 MySQL 的 SQL 规约/优化记录

女爷i 2022-06-02 04:00 81阅读 0赞

转载请注明出处：[http://blog.csdn.net/smartbetter/article/details/77568440][http_blog.csdn.net_smartbetter_article_details_77568440]

怎样才能写出高性能的 Web 应用程序，SQL 优化显得格外重要，下面是自己的一些总结，希望本文能给各位数据库管理员在性能优化方面一些启示。在现代业务开发中，建议还是尽可能的进行单表查询。  
1.SQL规约  
1.建表规约

必备三字段：

create table `demo` (  
`id` bigint(32) not null auto\_increment comment ‘主键’,  
`gmt_create` timestamp not null default current\_timestamp comment ‘主动创建时间’,  
`gmt_modified` timestamp not null default current\_timestamp on update current\_timestamp comment ‘被动更新时间’,  
primary key (`id`)  
)

1
    2
    3
    4
    5
    6

价格字段用 decimal(8, 2) 类型  
2.索引规约  
2.SQL优化  
1.合理的使用连接  
1.内连接与左连接

//查询学生信息+成绩，隐式的内连接，学生信息表student，学生成绩表studentcource，  
//只返回两张表里学号相同的记录  
select \* from student s, studentcource sc where s.id = sc.sid;

//左连接，以左表为主表，返回左表里所有的行，并以此和右表连接，即使右表没有和左表相对应的行数据，也需要返回null  
//右连接改成studentcource sc left student s即可  
//全连接等于左连接的所有行加上右连接的所有行  
select \* from student s left join studentcource sc on s.id = sc.sid;

//左连接添加where条件，指定查询的名字  
select \* from student s left join studentcource sc on s.id = sc.sid where name = ‘Tom’;

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12

2.范式和连接的代价

三范式的理解可以参考文章 [http://blog.csdn.net/wangqyoho/article/details/52900585][http_blog.csdn.net_wangqyoho_article_details_52900585]，一般说来，数据库只需满足第三范式(3NF）就行了：

三范式  
1NF:字段不可分;  
2NF:有主键，非主键字段依赖主键;  
3NF:非主键字段不能相互依赖;

解释:  
1NF:原子性 字段不可再分,否则就不是关系数据库;  
2NF:唯一性 一个表只说明一个事物;  
3NF:每列都与主键有直接关系，不存在传递依赖;

1
    2
    3
    4
    5
    6
    7
    8
    9
    10

既然按三范式的原则设计表，那么考虑表之间的连接代价就是必须要做的事情了，为此必须在三范式和连接代价之间权衡一下了。  
3.建表需要权衡数据冗余和连接代价

在设计的时候，如果知道系统的数据量不会太大（关联代价不会太高），那么用三范式的原则是必须的，毕竟三范式能避免数据冗余带来的更新插入上的需要同时多表里相同字段的麻烦。

如果数据量很大，那么就需要冗余数据，例如订单流水表中还放入了用户表的邮件地址和商品表的商品名。冗余数据也得付出相应的代价，比如用户一旦更新了邮件地址，那么我就需要同时在用户表和订单流水表里修改该字段。

我们不仅需要掌握诸如连接的范式之类的技术，更应该从业务角度权衡各种建表代价，具体业务具体分析。  
2.一些常用的SQL

//查询学生表中前10条数据，limit为分页查询关键字  
select \* from student limit 0,10

//用like进行模糊查询  
select \* from 表名 where name like ‘%java%’

//函数：查询name首字母是J的记录  
select \* from 表名 where substr(name)=’J’

insert into 表名

//以字母顺序显示公司名称  
select company, order\_number from orders order by company

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13

3.一些被忽视的SQL  
1.group by 和 having

//查询每个社团小组的总人数  
//社团小组记录表包括字段: 年级、人数、社团小组名  
//返回数据格式: 年级、总人数  
select 社团小组名, sum(人数) as 总人数 from 社团小组记录表 group by 社团小组名

//分组之后过滤数据: 哪个组的人数低于10人  
select 社团小组名, sum(人数) as 总人数 from 社团小组记录表 group by 社团小组名 having sum(人数)<10

//having查看重复记录，重复问题可以通过建立主键约束来避免  
//学生表字段：id、name  
select id from 学生表 group by having count(\*)>1

1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11

2.select定式

常用的 select 语句，无非包括 子查询、in、group by、having 的组合。

/\*建四张表:  
学生表student 字段: 学号、姓名、年龄  
课程表course 字段: 课程编号、课程名、老师编号  
成绩表sc 字段: 学号、课程号、成绩  
老师表teacher 字段: 老师编号、老师姓名\*/

//查询所有功课都及格的学生信息  
select sid, sname, sage from student where sid not in(select sid from sc where score<60)

//查询语文成绩比数学成绩差的学生学号和姓名  
select sid sname from  
(select student.’sid’, student.sname, score,  
(select score from sc sc\_2 where student.’sid’=sc\_2.’sid’ and sc\_2.’cname’=’语文’)  
score2 from student, sc where sc.’sid’=student.’sid’ and sc.’cname’=’数学’)  
s\_2 where score2

[http_blog.csdn.net_smartbetter_article_details_77568440]: http://blog.csdn.net/smartbetter/article/details/77568440
[http_blog.csdn.net_wangqyoho_article_details_52900585]: http://blog.csdn.net/wangqyoho/article/details/52900585