当前位置:首页 > Java技术 > Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化

2022年11月09日 18:05:15Java技术22

本文提要

最近写的几篇文章都是关于数据层优化方面的,这几天也在想还有哪些地方可以优化改进,结合日志和项目代码发现,关于数据层的优化,还是有几个方面可以继续修改的,代码方面,整合了druid数据源也开启了sql监控等,修改和规范了变量的命名方式,建表时的命名方式也做了修改,不过做的这些还不够,优化这件事真是一个长期和自觉的过程,mapper文件中的sql语句,依然不是十分的符合规范,有继续优化的必要,数据库中表的结构也需要继续优化。

前一篇文章主要讲了慢sql的整理,以及explain关键字在优化查询语句中的作用,也对sql做了一点小改动,但是修改的力度不是特别大,这一篇会稍微补充一下前一篇中关于索引的知识点,以及使用索引对查询语句进行优化。本文依然是代码优化和sql优化,主要还是讲一下索引及案例,前一篇文章较为简略,为了弥补这个懒惰造成的结果,这篇文章就详细一些。

表结构优化

建表语句规范,对原来的表结构重新做了一下修改,主要有:

  • 字段非空修改
  • 添加字段注释
  • 添加索引(下面一个段落细讲)

这里只贴出部分修改后的代码,其余的自行通过github更新:

CREATE TABLE `ssm_article` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `article_title` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '文章标题',
  `article_create_date` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '创建时间',
  `article_content` text COLLATE utf8_bin NOT NULL COMMENT '文章内容',
  `is_top` int(4) NOT NULL DEFAULT '0' COMMENT '是否置顶,1为置顶,默认为0',
  `add_name` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '添加人',
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

NOT NULL:防止索引失效,对表中一个列添加了索引的话,如果有记录为NULL,则查询时不会使用该索引。

表注释:同代码注释一样,可以帮助开发人员快速理解的建表的含义,但是同项目中的代码又不一样,没有注释,代码看久了总会理解其中的逻辑和功能,但是表字段,如果不懂就真的只能靠猜了。添加表注释就是为了大家都能理解字段的意思及建表意图,不要太相信自己的记忆力,它是比不了白纸黑字的,时间久了总会淡忘一些,如果连自己都忘了真的是搞笑了,因此注释的添加也是极其必要的,有些标示类型字段也是需要详细解释的,不同的值分别代表什么意思,如果不在注释中约定好,只靠口头来传达是很容易出现差错的,注释就是为了减少沟通成本,也减少理解出错的几率,从而提升工作效率。

查询语句优化

mapper文件中sql语句规范,修改select *语句,这里只贴出部分修改后的代码,其余的自行通过github更新:

<select id="findArticles" parameterType="Map" resultMap="ArticleResult">
        select id,article_title,article_create_date,article_content,add_name from ssm_article
        <where>
            <if test="articleTitle!=null and articleTitle!='' ">
                and article_title like #{articleTitle}
            </if>
        </where>
        <if test="start!=null and size!=null">
            limit #{start},#{size}
        </if>
    </select>    

关于mapper文件中的sql语句,主要是修改了select语句,原本的select语句都是用select * from TABLE实现的,这种方式有不少缺点而且不是一个优雅的写法,因此做了一下规范,改为如下格式:

select COL(需要的列名) from TABLE

关于避免使用select *语句的个人想法:

  • 执行SELECT * 语句时,SQL Server首先要查找出表中有哪些列,然后才能开始执行SELECT * 语句,这在某些情况会产生性能问题。
  • SELECT * 语句取出表中的所有字段,所有数据都会返回到结果集中。
  • SELECT * 中返回的字段中有可能存在一个功能中不需要的字段。
  • 使用SELECT * 语句可能会影响到索引的使用,不利于查询的性能优化。

针对这四点分别讲一下个人理解:

  • 第一条,对于性能的影响可能不会太大,几毫秒或者几微妙的事情,似乎不是那么严重,而且SQL Server也有对应的缓存策略,不会每次都去查表中有哪些列。但是,我们现在是在做优化工作,既然做就要尽量做到最好,有优化的地方还是要去尝试一下,提升0.01秒也是提升,不要因为量小而忽略它。
  • 第二条,所有的字段都返回,结果集的数据量也会相应的增大,对应的,也就会增加数据传输的时间,即使mysql服务和客户端是在同一台机器上,二者间通信时使用的协议还是tcp,通信也是需要额外的时间,因此,还是取所需内容为好。
  • *第三条,举个例子,表中有16个字段,我们要实现一个功能,但是这个方法中需要其中6个字段就可以了,那么剩下的10个字段就是没用的了,如果用select 返回结果,一个sql返回列可能就要多创建10个String对象到jvm中,10000个呢?再多呢?这是浪费。而且,如果返回的列中有大字段,例如很长的varchar,blob,text,占用空间也更大,更浪费。
  • 第四条,举个例子,对于ssm_user表,创建了user_name和role_name的联合索引,对于以下两条sql语句:select role_name from ssm_user where user_name='admin' select * from ssm_user where user_name='admin',前者要比后者的速度快,因为表中存在user_name和role_name的联合索引,因此role_name可以在索引树上直接拿到,不再需要读取表中的这条记录,在下面会给出具体代码。

其实我也没想到一个select *会写这么多知识点。

既然优化select * from TABLE 有那么多好处,那么是不是select count(*) from TABLE也要优化呢?OK,我们接着来分析,select count(*) from TABLE在不同情况下,性能表现的不同:

  • innodb引擎:select count(*)select count(COL)的效率没有太大差距,都会扫描全表或者where条件语句后的结果,累加可能得到的结果不同。
  • myisam引擎且没有where子句:可以直接得出myisam会记录总行数,很快。
  • myisam有where子句:与innodb引擎相同。

注:
count(col) 是表示结果集中有多少个column字段不为空的记录。
count(*) 是表示整个结果集有多少条记录。

因此,通过上面的分析发现,select count(*)语句的性能提升,首先是引擎的选择(这个不重要,目前选择的是innodb引擎),重点是在where条件语句的优化,也就是索引的优化。

索引和优化案例

前面两个段落中基本都提到了一个知识点--索引,在sql语句优化中,合理和正确的索引真的是一个利器。

优化的意义:我们在前一篇讲到了慢sql给项目带来的危害,这里再补充一下,首先我们大多数用到的都是innodb引擎来建表,这个引擎已经做了挺大的优化,支持行级锁和表锁,并且默认锁为行级锁,对表锁做了一定的优化,因此对于行级锁的优化来说,就是尽量减少sql语句执行而带来的行级锁,尽量使得处于锁状态中的记录数减少。如果一条sql锁住过多的记录,那么对于其他sql语句的执行就是一个阻碍,需要等到锁释放并且竞争到锁才能正常的执行,因此我们优化慢sql不仅仅是提升执行速度和执行时间,同时也是优化查询过程,优化mysql的资源使用,减少锁资源的开销以及系统开销,同时,合理的查询语句也能降低各种错误的产生,比如事务死锁的产生。
从以上分析中我们可以得出一个结论,尽量使得sql语句执行时innodb使用行级锁而不是表锁,innodb行锁是通过给索引上的索引项加锁来实现的,innodb这种行锁实现特点意味着:只有通过索引条件检索数据,innodb才使用行级锁,否则,innodb将使用表锁!

索引的类型:

  • 普通索引:这是最基本的索引类型,没唯一性之类的限制。
  • 唯一性索引:和普通索引基本相同,但所有的索引列值保持唯一性。
  • 主键索引:主键是一种唯一索引,但必须指定为”PRIMARY KEY”。
  • 全文索引:不讲这个,想了解的自己去看一下

结合前文提到的ssm_user表来讲一个小例子:

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记

从结果来看,依然是全表扫描。

对user_name,role_name字段添加索引:

alter table ssm_user add index idx_un_rn(user_name,role_name);

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记
再次执行上面的分析语句:

explain select role_name from ssm_user where user_name='admin';

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记

通过结果来看,添加了索引之后,type已由原来的全表扫描ALL变成了ref级别,也如前文所说,role_name直接通过索引树返回,extra中的using index参数表示直接访问索引就足够获取到所需要的数据,不需要通过索引回表。

这个例子是对上一段落select *select COL间性能比较的补充,下面再讲解一个ssm_picture表的例子,对前一篇文章《Spring+SpringMVC+MyBatis+easyUI整合优化篇(十二)数据层优化-explain关键字及慢sql优化》做一个补充。

首先在数据库中插入20000条数据:
Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记

分析查询语句explain select id,path,type,time,url,grade from ssm_picture where type=1 and grade=1 limit 0,10;
Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记
多次执行,时间基本都是0.02s:

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记

在没有添加索引前,依然是全表扫描,记录总数为20134,前文做的一个小改动:

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记

是通过order by id使得查询语句使用主键索引,type由ALL变为index,但是依然为全表扫描。因为where条件中是通过type和grade对结果集进行过滤,因此添加一个type字段和grade字段组成的多列索引。
在ssm_picture表中添加一个名称为idx_type_grade的索引:

alter table ssm_picture add index idx_type_grade (type,grade);

此时再次分析查询语句得到结果如下:

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记

多次执行后的执行时间如下:
Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记
比原来应该是有一些提升的,不仅仅是时间上的提升,关键是结构上的整理,不仅优化了查询过程和结果,也避免全表扫描节省了mysql的开销,降低可预见的错误发生几率。

最左前缀原则:

多列索引有一个特点,即最左前缀(Leftmost Prefixing)。假如有一个多列索引为key(firstname lastname age),当搜索条件是以下各种列的组合和顺序时,MySQL将使用该多列索引:
firstname,lastname,age
firstname,lastname
firstname
也就是说,相当于还建立了key(firstname lastname)和key(firstname)。
按照最左前缀原则,本例中的idx_type_grade(type,grade)即创建了两个索引:
type,grade
type
show index from ssm_picture查看表的索引信息:

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记
图中Key_name 为索引的名称,Seq_in_index为索引中的列序列号(从1开始),因此结果集表示的是,有一个索引名为idx_type_grade,第一列为type,第二列是grade,而不是像id主键索引表示单独的两个索引。分别以type和grade为where语句中的条件来执行sql验证一下:

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记

Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化 _ JavaClub全栈架构师技术笔记

type用到了索引,而grade依然是全表扫描,即无grade索引,也符合最左前缀原则的分析。

总结

使用索引的优点

  • 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
  • 建立索引可以大大提高检索的数据,以及减少表的检索行数
  • 在表连接的连接条件可以加速表与表直接的相连
  • 在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
  • 建立索引,在查询中使用索引可以提高性能

使用索引的缺点:

  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要更新数据,还要更新一下索引文件,创建索引和维护索引 会耗费时间,随着数据量的增加而增加
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
  • 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句

使用索引需要注意的地方:

  • 在经常需要搜索的列上,可以加快索引的速度
  • 在表与表的而连接条件上加上索引,可以加快连接查询的速度
  • 在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
  • 如果你对nickname字段建立了一个索引,当查询的时候的语句是 nickname lick '%ABC%'那么这个索引讲不会起到作用,而nickname lick 'ABC%'那么将可以用到索引
  • 索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者' '字符串
  • 使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.
  • 不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
  • 选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

什么情况下不创建索引:

  • 查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
  • 很少数据的列也不应该建立索引,比如一个性别字段0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
  • 定义为text和image和bit数据类型的列不应该增加索引
  • 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

索引不是越多越好,也不是所有的表都要创建索引,根据需求和实际运行的sql语句进行合理的索引创建。本文中的案例都是围绕ssm-demo这个项目来进行的,都是单表的简单查询sql语句,并没有复杂的连接查询或者复杂的where语句,join查询等复杂查询,以后有时间专门写几篇文章来详细讲解一下,本项目实在没有太好的切入点,只能重新建表来单独讲解,OK,本文结束。

这周并没有更新博客,是因为完成这两篇博客花费了不少的时间,从构思到整理资料和理出文章结构再到完成,前前后后大概有6个晚上,白天要上班或者有其他事情,基本都是抽出晚上的一段时间来写,也慢慢感觉随着项目的修改以及知识点的深入,后面会越来越难吧,也不知道自己坚不坚持得下来,加油啦。

作者:程序员十三
来源链接:https://www.cnblogs.com/han-1034683568/p/6768807.html

版权声明:
1、JavaClub(https://www.javaclub.cn)以学习交流为目的,由作者投稿、网友推荐和小编整理收藏优秀的IT技术及相关内容,包括但不限于文字、图片、音频、视频、软件、程序等,其均来自互联网,本站不享有版权,版权归原作者所有。

2、本站提供的内容仅用于个人学习、研究或欣赏,以及其他非商业性或非盈利性用途,但同时应遵守著作权法及其他相关法律的规定,不得侵犯相关权利人及本网站的合法权利。
3、本网站内容原作者如不愿意在本网站刊登内容,请及时通知本站(javaclubcn@163.com),我们将第一时间核实后及时予以删除。


本文链接:https://www.javaclub.cn/java/68918.html

分享给朋友:

“Spring+SpringMVC+MyBatis+easyUI整合优化篇(十三)数据层优化-表规范、索引优化” 的相关文章

Mybatis分页插件PageHelper的使用

Mybatis分页插件PageHelper的使用

开头语 上回书说到:将SpringBoot和持久层框架Mybtais进行简单整合,整合时,使用了Mybatis的分页插件PageHelper,但并未介绍如何使用该分页插件,本次讲解PageHelper的常见的使用方法,请大家提意见 什么是PageHelper 在使用Java语言开发项目...

SpringBoot 如何进行参数校验

SpringBoot 如何进行参数校验

为什么需要参数校验在日常的接口开发中,为了防止非法参数对业务造成影响,经常需要对接口的参数进行校验,例如登录的时候需要校验用户名和密码是否为空,添加用户的时候校验用户邮箱地址、手机号码格式是否正确。 靠代码对接口参数一个个校验的话就太繁琐了,代码可读性极差。 Validator框架就是为了解决开发人...

专门为小白准备的入门级mybatis

专门为小白准备的入门级mybatis

引入依赖        <dependency>             <gr...

Spring Cloud Alibaba与Spring Boot、Spring Cloud版本对应关系

Spring Cloud Alibaba与Spring Boot、Spring Cloud版本对应关系

一、前言 在搭建SpringCloud项目环境架构的时候,需要选择SpringBoot和SpringCloud进行兼容的版本号,因此对于选择SpringBoot版本与SpringCloud版本的对应关系很重要,如果版本关系不对应,常见的会遇见项目启...

Spring Boot 自动配置的原理、核心注解以及利用自动配置实现了自定义 Starter 组件

Spring Boot 自动配置的原理、核心注解以及利用自动配置实现了自定义 Starter 组件

本章内容 自定义属性快速入门 外化配置 自动配置 自定义创建 Starter 组件 摘录:读书是读完这些文字还要好好用心去想想,写书也一样,做任何事也一样 图 2 第二章目录结构图 第 2...

Spring Boot 整合 Redis 实现缓存操作

Spring Boot 整合 Redis 实现缓存操作

摘要: 原创出处 www.bysocket.com 「泥瓦匠BYSocket 」欢迎转载,保留摘要,谢谢! 『 产品没有价值,开发团队再优秀也无济于事 – 《启示录》 』...

Spring5:@Autowired注解、@Resource注解和@Service注解

什么是注解 传统的Spring做法是使用.xml文件来对bean进行注入或者是配置aop、事物,这么做有两个缺点: 1、如果所有的内容都配置在.xml文件中,那么.xml文件将会十分庞大;如果按需求分开.xml文件,那么.xml文件又会非常多。总之这将导致配置文件的可读性与...

Spring Boot学习记录(四、日志文件)

市面上的日志框架: JUL、JCL、Jboss-logging、logback、log4j,log4j2、slf4j……   日志门面(日志抽象层) 日志实现 JCL(Ja...

使用Mybatis整合spring时报错Injection of autowired dependencies failed;

这是无法自动注入,通过查找,我出错的原因在于配置文件的路径没有写对,在applicationContext.xml中是这样写的。 <bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessi...

Spring Cloud Ribbon 客户端负载均衡

Spring Cloud Ribbon 客户端负载均衡

Ribbon客户端组件提供一系列完善的配置选项,比如连接超时、重试、重试算法等,内置可插拔、可定制的负载均衡组件。下面是用到的一些负载均衡策略: 简单轮询负载均衡 加权轮询负载均衡 区域感知轮询负载均衡 随机负载均...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。