搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > JAVA早餐 > 深入理解java—mysql数据库性能优化全面总结

深入理解java—mysql数据库性能优化全面总结

JAVA早餐 2017-11-28

概述

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我 们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能。这里,我们不会讲过 多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。

优化,一直是面试最常问的一个问题。因为从优化的角度,优化的思路,完全可以看出一个人的技术积累。那么,关于系统优化,假设这么个场景,用户反映系统太卡(其实就是高并发),那么我们怎么优化?

如果请求过多,判定web服务器的压力过大,增加前端的web服务器,做负载均衡

如果请求静态界面不卡了,但是动态数据还是卡,说明MySQL处理的请求太多了,在应用层增加缓存.

数据库层其实是最脆弱的一层,一般在应用设计时在上游就需要把请求拦截掉,数据库层只承担“能力范围内”的访问请求,所以,我们通过在服务层引入队列和缓存,让最底层的数据库高枕无忧。但是如果请求激增,还是有大量的查询压力到MySQL,这个时候就要想办法解决MySQL的瓶颈了

总结起来就是,系统优化的第一步,是绝对轮不到MySQL优化我们之所以要做MySQL的集群,一般都是在做好了应用级别的缓存,请求还是太多的情况下考虑的问题.

一丶MySQL的执行流程

那么,要知道我们平时常说的优化sql到底是在优化些什么,就必须弄懂MySQL的执行流程。而这个专题将系统化的由浅到深讲解MySQL一些高级用法。打算先讲很多人关注的使用方式(增删改查以及其优化),然后就讲数据库和表的操作(很多我们学习忽略的地方),接着就是引擎还有更高级的查询等等。

先简单粗暴上一执行流程图感受下

深入理解java—mysql数据库性能优化全面总结

执行流程图

大致可以分为以下几个步骤:

1.当我们请求mysql服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断

2.通过权限之后,SQL就到MySQL内部,他会在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回,说明在MySQL内部,也有一个查询缓存.但是这个查询缓存,默认是不开启的,这个查询缓存,和我们的Hibernate,Mybatis的查询缓存是一样的,因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的(没什么卵用的意思)。

3.如果我们没有开启查询缓存,或者缓存中没有找到对应的结果,那么就到了解析器,解析器主要对SQL语法进行解析

4.解析结束后就变成一颗解析树,这个解析树其实在Hibernate里面也是有的,大家回忆一下,在以前做过Hibernate项目的时候,是不是有个一个antlr.jar。这个就是专门做语法解析的工具.因为在Hibernate里面有HQL,它就是通过这个工具转换成SQL的,我们编程语言之所以有很多规范、语法,其实就是为了便于这个解析器解析,这个学过编译原理的应该知道.

5.得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...

6.预处理完毕之后,此时得到一棵比较规范的树,这棵树就是要拿去马上做执行的树,比起之前的那棵树,这棵得到了一些优化

7.查询优化器,是MySQL里面最关键的东西,我们写任何一条SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它会怎么去执行?它是先执行username = toby还是password = 1?每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,MySQL都是有缓存起来的,在真正执行SQL之前,他会根据自己的这些数据,进行一个综合的判定,判断这一次在多种执行方式里面,到底选哪一种执行方式,可能运行的最快.这一步是MySQL性能中,最关键的核心点,也是我们的优化原则.我们平时所讲的优化SQL,其实说白了,就是想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.

8.这里的查询执行计划,也就是MySQL

查询中的执行计划,比如要先执行username = toby还是password = 1

9.这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询,这个时候重点来了,影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是我们平时熟悉的IO,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO又是什么来确定的?就是传过来的这一份执行计划.

二丶MySQL存储引擎概述

MyISAM存储引擎

MyISAM存储引擎的表在数据库中,每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm文件,另外还有.MYD和.MYI文件,分别存放了表的数据(.MYD)和索引数据(.MYI)。每个表都有且仅有这样三个文件做为MyISAM存储类型的表的存储,也就是说不管这个表有多少个索引,都是存放在同一个.MYI文件中。

MyISAM支持以下三种类型的索引:

B-Tree索引

B-Tree索引,顾名思义,就是所有的索引节点都按照balancetree的数据结构来存储,所有的索引数据节点都在叶节点。

R-Tree索引

R-Tree索引的存储方式和b-tree索引有一些区别,主要设计用于为存储空间和多维数据的字段做索引,所以目前的MySQL版本来说,也仅支持geometry类型的字段作索引。

Full-text索引

Full-text索引就是我们长说的全文索引,他的存储结构也是b-tree。主要是为了解决在我们需要用like查询的低效问题。

Innodb 存储引擎

支持事务安装

数据多版本读取

锁定机制的改进

实现外键

NDBCluster存储引擎

NDB存储引擎也叫NDBCluster存储引擎,主要用于MySQLCluster分布式集群环境,Cluster是MySQL从5.0版本才开始提供的新功能。

Merge存储引擎

MERGE存储引擎,在MySQL用户手册中也提到了,也被大家认识为MRG_MyISAM引擎。Why?因为MERGE存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表,通过一些特殊的包装对外提供一个单一的访问入口,以达到减小应用的复杂度的目的。要创建MERGE表,不仅仅基表的结构要完全一致,包括字段的顺序,基表的索引也必须完全一致。

Memory存储引擎

Memory存储引擎,通过名字就很容易让人知道,他是一个将数据存储在内存中的存储引擎。Memory存储引擎不会将任何数据存放到磁盘上,仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。所以一旦MySQLCrash或者主机Crash之后,Memory的表就只剩下一个结构了。Memory表支持索引,并且同时支持Hash和B-Tree两种格式的索引。由于是存放在内存中,所以Memory都是按照定长的空间来存储数据的,而且不支持BLOB和TEXT类型的字段。Memory存储引擎实现页级锁定。

BDB存储引擎

BDB存储引擎全称为BerkeleyDB存储引擎,和Innodb一样,也不是MySQL自己开发实现的一个存储引擎,而是由SleepycatSoftware所提供,当然,也是开源存储引擎,同样支持事务安全。

FEDERATED存储引擎

FEDERATED存储引擎所实现的功能,和Oracle的DBLINK基本相似,主要用来提供对远程MySQL服务器上面的数据的访问接口。如果我们使用源码编译来安装MySQL,那么必须手工指定启用FEDERATED存储引擎才行,因为MySQL默认是不起用该存储引擎的。

ARCHIVE存储引擎

ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引,通过一个.frm的结构定义文件,一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性,ARCHIVE表不支持删除,修改操

作,仅支持插入和查询操作。锁定机制为行级锁定。

BLACKHOLE存储引擎

BLACKHOLE存储引擎是一个非常有意思的存储引擎,功能恰如其名,就是一个“黑洞”。就像我们unix系统下面的“/dev/null”设备一样,不管我们写入任何信息,都是有去无回。

CSV存储引擎

CSV存储引擎实际上操作的就是一个标准的CSV文件,他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件,而CSV文件是很多软件都支持的一种较为标准的格式,所以我们可以通过先在数据库中建立一张CVS表,然后将生成的报表信息插入到该表,即可得到一份CSV报表文件了。

三丶MySQL 锁定机制简介

行级锁定(row-level)

表级锁定(table-level)

页级锁定(page-level)

在MySQL数据库中,使用表级锁定的主要是MyISAM,Memory,CSV等一些非事务性存储引擎,而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎,页级锁定主要是BerkeleyDB存储引擎的锁定方式。

四丶MySQL Query的优化

Query语句的优化思路和原则主要提现在以下几个方面:

1. 优化更需要优化的Query;

2. 定位优化对象的性能瓶颈;

3. 明确的优化目标;

4. 从Explain入手;

5. 多使用profile

6. 永远用小结果集驱动大的结果集;

7. 尽可能在索引中完成排序;

8. 只取出自己需要的Columns;

9. 仅仅使用最有效的过滤条件;

10.尽可能避免复杂的Join和子查询;

合理设计并利用索引

B-Tree索引

一般来说,MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的结构来存储的,也就是所有实际需要的数据都存放于Tree的LeafNode,而且到任何一个LeafNode的最短路径的长度都是完全相同的,所以我们大家都称之为B-Tree索引当然,可能各种数据库(或MySQL的各种存储引擎)在存放自己的B-Tree索引的时候会对存储结构稍作改造。如Innodb存储引擎的B-Tree索引实际使用的存储结构实际上是B+Tree,也就是在B-Tree数据结构的基础上做了很小的改造,在每一个LeafNode上面出了存放索引键的相关信息之外,还存储了指向与该LeafNode相邻的后一个LeafNode的指针信息,这主要是为了加快检索多个相邻LeafNode的效率考虑。

Hash索引

Hash索引在MySQL中使用的并不是很多,目前主要是Memory存储引擎使用,而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引,实际上就是通过一定的Hash算法,将需要索引的键值进行Hash运算,然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候,都会将检索条件进行相同算法的Hash运算,然后再和Hash表中的Hash值进行比较并得出相应的信息。

Hash索引仅仅只能满足“=”,“IN”和“<=>”查询,不能使用范围查询;

Hash索引无法被利用来避免数据的排序操作;

Hash索引不能利用部分索引键查询;

Hash索引在任何时候都不能避免表扫面;

Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高;

3)Full-text索引

Full-text索引也就是我们常说的全文索引,目前在MySQL中仅有MyISAM存储引擎支持,而且也并不是所有的数据类型都支持全文索引。目前来说,仅有CHAR,VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

索引能够极大的提高数据检索效率,也能够改善排序分组操作的性能,但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据,更新数据会带来的IO量和调整索引所致的计算量的资源消耗。

是否需要创建索引,几点原则:较频繁的作为查询条件的字段应该创建索引;唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件;更新非常频繁的字段不适合创建索引;

不会出现在WHERE子句中的字段不该创建索引;

Join语句的优化

尽可能减少Join语句中的NestedLoop的循环总次数;“永远用小结果集驱动大的结果集”。

优先优化NestedLoop的内层循环;

保证Join语句中被驱动表上Join条件字段已经被索引;

当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝惜JoinBuffer的设置;

ORDER BYGROUP BYDISTINCT优化

1)ORDER BY的实现与优化

优化Query语句中的ORDER BY的时候,尽可能利用已有的索引来避免实际的排序计算,可以很大幅度的提升ORDER BY操作的性能。

优化排序:

1.加大max_length_for_sort_data参数的设置;

2.去掉不必要的返回字段;

3.增大sort_buffer_size参数设置;

GROUP BY的实现与优化

由于GROUP BY实际上也同样需要进行排序操作,而且与ORDER BY相比,GROUP BY主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算。所以,在GROUP BY的实现过程中,与ORDER BY一样也可以利用到索引。

DISTINCT的实现与优化

DISTINCT实际上和GROUP BY的操作非常相似,只不过是在GROUP BY之后的每组中只取出一条记录而已。所以,DISTINCT的实现和GROUP BY的实现也基本差不多,没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现,当然,在无法仅仅使用索引即能完成DISTINCT的时候,MySQL只能通过临时表来完成。但是,和GROUP BY有一点差别的是,DISTINCT并不需要进行排序。也就是说,在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候,MySQL会利用临时表来做一次数据的“缓存”,但是不会对临时表中的数据进行filesort操作。

总结

到这里深入理解java—mysql数据库性能优化全面总结结束了,不足之处还望大家多多包涵!!觉得收获的话可以点个关注收藏转发一波喔,谢谢大佬们支持。(吹一波,233~~)

下面和大家交流几点编程的经验:

1、多写多敲代码,好的代码与扎实的基础知识一定是实践出来的

2丶 测试、测试再测试,如果你不彻底测试自己的代码,那恐怕你开发的就不只是代码,可能还会声名狼藉。

3丶 简化算法,代码如恶魔,在你完成编码后,应回头并且优化它。从长远来看,这里或那里一些的改进,会让后来的支持人员更加轻松。

最后,每一位读到这里的网友,感谢你们能耐心地看完。希望在成为一名更优秀的Java程序员的道路上,我们可以一起学习、一起进步。

想了解学习以上内容可加群469717771 验证:(009)


java海量视频资源免费获取、java最新资讯、新闻每日更新。

请长按图片,关注下方二维码

亲们,所有资源全部免费,视频均来源于网络,若发现个别资源失效,请及时联系早餐君,早餐君发现后会立即修改连接。你们的支持使我们坚持的动力!



版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《深入理解java—mysql数据库性能优化全面总结》的版权归原作者「JAVA早餐」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读