Java 后端实战——基于 MySQL 的 SQL 规约/优化记录

原创 2017年08月25日 18:31:20

转载请注明出处: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`)
) 

价格字段用 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';

2.范式和连接的代价

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

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

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

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

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

3.一些被忽视的SQL

1.group by 和 having

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

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

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

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<score

//查询都学过2号同学学习过的课程的学生的学号
//用count(*)来保证这些同学学过的课程数量和2号同学课程数量一样多
select 'sid' from sc where 'cid' in
    (select 'cid' from sc where 'sid'=2)
    group by 'sid' having count(*)=(select count(*) from sc where 'sid'=2)

4.解决SQL慢查询问题

1.排序慢SQL之order by解决

项目中 order by 排序出现耗费大量时间的现象,加和不加 order by 差距在80倍左右。从三张表里面查数据(left join),然后根据其中一个字段排序(排序的字段有索引)。这种情况可以考虑数据查出来,然后再用 Java 进行排序:

//Object排序一
Collections.sort(returnList, new Comparator<CostStatisticsD0>() {
    @Override
    public int compare(CostStatisticsD0 o1, CostStatisticsD0 o2) {
        return o1.getConsume() < o2.getConsume() ? 1 : -1;
    }
});

//Object排序二
Collections.sort(returnList, new Comparator<CostStatisticsD0>() {
    @Override
    public int compare(CostStatisticsD0 o1, CostStatisticsD0 o2) {
        return o1.getAdvertEntName().compareTo(o2.getAdvertEntName());
    }
});

5.索引的用途与代价

索引是数据库优化所必备的工具。索引相关内容参考文章 http://blog.csdn.net/xluren/article/details/32746183
索引的用途:查询效率高。
付出的代价:空间代价(索引需要占硬盘空间);时间代价(一旦插入新的数据,就需要重新建索引)。

建立索引需要权衡利弊(为了提高查询效率可以建索引)。一定要有了业务需求才会建索引,比如在一个商品表里,我们经常要根据name做查询,如果没有索引,查询会很慢,这时就需要建索引。但在项目开发中,如果不经常根据商品编号查询,那么就没必要对编号建索引。

注意:建索引是要付出代价的,没事别乱建,同时在一个表上也不能建太多的索引。

6.JDBC操作优化

1.try-catch-finally优化

1.不能直接用 Exception 来接收所有异常,应当先用专业的异常处理类,比如 SQLException 来接收,最后再用 Exception 来做最后的防守。
2.在 catch 从句里,应该返回一些可操作性的语句,提示用户遇到异常后该怎么办。
3.应当尽量缩小 try-catch 的范围,只包括必要的代码即可。

2.预处理和批处理

使用占位符(?)预处理可以提升效率,避免SQL注入,从而保证系统的安全。
批量操作能提效率,一般没批操作 500 - 1000 条语句,但也切记,别一次性把所有的 insert 语句都放入,因为如果 SQL 过多,会撑爆缓存,从而出错。

MyBatis 批量操作MySQL参考文章 http://blog.csdn.net/hardworking0323/article/details/51105218

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

MySQL设计之三范式的理解

网上查找了一些资料,记录如下并加入自己的理解。 设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。...

Java 开发Log4j 详细使用教程

日志是应用软件中不可缺少的部分,Apache的开源项目Log4j是一个功能强大的日志组件,提供方便的日志记录。在apache网站:jakarta.apache.org/log4j 可以免费下载到Log...

Linux 下 Solr 分布式集群安装

数据分为结构化数据(有固定类型或固定长度,如MySQL数据库中的数据、元数据等)和非结构化数据(没有固定类型或固定长度,如word中的数据、邮件中的数据等)。非结构化数据查询方法一般使用全文检索。从非...

Spring 架构篇——Spring Boot 整合 Solr

Solr 是基于 Lucene 的全文检索服务器,可配置、可扩展,并对索引和搜索性能进行了优化。Solr 多用于电子商务网站、门户、论坛这类网站的站内搜索。Solr 可以独立运行在 Jetty、Tom...

Spring 架构篇——Spring Boot 整合 Freemarker

Freemarker 是一个用 Java 语言编写的开源模版引擎 (不是 web 框架),它基于模版来生成文本输出。Freemarker 与 Web 容器无关,不依赖于 Web 容器,在 Web 运行...

Linux 下 Kafka 分布式集群安装

Kafka 是由 LinkedIn 使用 Scala 编写的一个分布式消息系统,用作 LinkedIn 的活动流(Activity Stream)和运营数据处理管道(Pipeline)的基础,具有高水...

Java 后端实战——常见数据加密算法/编码方式

本文主要介绍了几种常见的数据加密算法(对称密码算法/非对称密码算法)的 Java 实现和常见编码方式的使用。对称加密指加密和解密使用相同密钥的加密算法。常见的对称加密算法包括:AES算法,Java实现...

服务器后端开发系列——《实战Nginx高性能Web服务器》

1、高性能Web服务器Nginx的配置与部署研究(1)Nginx简介及入门示例内容:概述Nginx的背景知识和简单的入门实例。2、高性能Web服务器Nginx的配置与部署研究(2)Nginx入门级配置...
  • Poechant
  • Poechant
  • 2012年01月21日 18:51
  • 13518

【Java开发手册之MySql规约(三)】SQL规约

【强制】不要使用 count( 列名 ) 或 count( 常量 ) 来替代 count( * ) , count( * ) 就是 SQL 92 定义的标准统计行数的语法,跟数据库无关,跟 NULL ...

《阿里巴巴Java开发手册(正式版)》--MySQL规约

(一)建表规约1.【强制】表达是与否概念的字段,必须使用 is_xxx的方式命名,数据类型是 unsigned tinyint( 1表示是,0表示否),此规则同样适用于 odps建表。 说明:任何字...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Java 后端实战——基于 MySQL 的 SQL 规约/优化记录
举报原因:
原因补充:

(最多只允许输入30个字)