APP下载

深入浅出sql的效能优化(一)

消息来源:baojiabao.com 作者: 发布时间:2024-05-18

报价宝综合消息深入浅出sql的效能优化(一)

在谈SQL优化前,我么先分析下SQL的架构图

mysql客户端通过协议与mysql服务器建连线,传送查询语句,先检查查询快取,如果命中,直接返回结果,否则进行语句解析,也就是说,在解析查询之前,服务器会先访问查询快取(query cache)——它储存SELECT语句以及相应的查询结果集。如果某个查询结果已经位于快取中,服务器就不会再对查询进行解析、优化、以及执行。它仅仅将快取中的结果返回给使用者即可,这将大大提高系统的效能。

语法解析器和预处理:首先mysql通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询;前处理器则根据一些mysql规则进一步检查解析数是否合法。

查询优化器当解析树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。

最后会找到相应引擎,进行查询。

总结:MySQL连线池--快取--sql界面分析sql--解析器 复杂sql解析--优化器生成执行计划--储存引擎执行--存入快取

MySQL中的各个引擎

1、InnoDB储存引擎

InnoDB是MySQL的预设事务型引擎,它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的储存引擎,否则应该优先考虑InnoDB引擎。

2、MyISAM储存引擎

MyISAM提供了大量的特性,包括全文索引、压缩、空间函式(GIS)等,但MyISAM不支援事务和行级锁,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

3、Archive引擎

Archive档案储存引擎只支援INSERT和SELECT操作,在MySQL5.1之前不支援索引。

Archive表适合日志和资料采集类应用。

根据英文的测试结论来看,Archive表比MyISAM表要小大约75%,比支援事务处理的InnoDB表小大约83%。

4、Blackhole引擎

Blackhole引擎没有实现任何储存机制,它会丢弃所有插入的资料,不做任何储存。但服务器会记录Blackhole表的日志,所以可以用于复制资料到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此并不推荐。

5、CSV引擎

CSV引擎可以将普通的CSV档案作为MySQL的表来处理,但不支援索引。

CSV引擎可以作为一种资料交换的机制,非常有用。

CSV储存的资料直接可以在操作系统里,用文字编辑器,或者excel读取。

6、Memory引擎

如果需要快速地访问资料,并且这些资料不会被修改,重启以后丢失也没有关系,那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。

7、Federated引擎

Federated引擎是访问其他MySQL服务器的一个代理,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此预设是禁用的。

引擎中最重要的就是MyISAM和InnoDB,下面分析下这两个引擎

MyISAM

外来键不支援

事务不支援

行表锁表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作

快取只快取索引,不快取真实资料

关注点节省资源、消耗少、简单业务

预设安装Y

预设使用N

自带系统表使用Y

InnoDB

对比项

外来键 支援

事务 支援

行表锁行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作

快取 不仅快取索引还要快取真实资料,对内存要求较高,而且内存大小对效能有决定性的影响

关注点并发写、事务、更大资源

预设安装Y

预设使用Y

自带系统表使用N

MyISAM主要用在MySQL自带的表中,而InnoDB用在我们自己建的表中

了解了mysql架构之后,我们再来了解一下mysql的索引机制。

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取资料的资料结构。

可以得到索引的本质:索引是资料结构。你可以简单理解为“排好序的快速查询资料结构”。

一般来说索引本身也很大,不可能全部储存在内存中,因此索引往往以索引档案的形式储存的磁盘上

优势

类似大学图书馆建书目索引,提高资料检索的效率,降低数据库的IO成本

通过索引列对资料进行排序,降低资料排序的成本,降低了CPU的消耗

劣势

虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。 因为更新表时,MySQL不仅要储存资料,还要储存一下索引档案每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引资讯

实际上索引也是一张表,该表储存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的

mysql索引结构

首先我们要了解两种索引结构

BTree索引

一颗b树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个资料项(深蓝色所示)和指标(黄色所示),

如磁盘块1包含资料项17和35,包含指标P1、P2、P3,

P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。

真实的资料存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点只不储存真实的资料,只储存指引搜寻方向的资料项,如17、35并不真实存在于资料表中。

如果要查询资料项29,那么首先会把磁盘块1由磁盘载入到内存,此时发生一次IO,在内存中用二分查询确定29在17和35之间,锁定磁盘块1的P2指标,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指标的磁盘地址把磁盘块3由磁盘载入到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指标,通过指标载入磁盘块8到内存,发生第三次IO,同时内存中做二分查询找到29,结束查询,总计三次IO。

真实的情况是,3层的b+树可以表示上百万的资料,如果上百万的资料查询只需要三次IO,效能提高将是巨大的,如果没有索引,每个资料项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

B+Tree索引

B+Tree中非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。B+Tree中每个记录的查询时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度看B-树的效能好像要比B+树好,而在实际应用中却是B+树的效能要好些。因为B+树的非叶子节点不存放实际的资料,这样每个节点可容纳的元素个数比B-树多,树高比B-树小,这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的效能可能还会好些,而且B+树的叶子节点使用指标连线在一起,方便顺序遍历(例如检视一个目录下的所有档案,一个表中的所有记录等),这也是很多数据库和档案系统使用B+树的缘故。

思考:为什么说B+树比B-树更适合实际应用中操作系统的档案索引和数据库索引?

1) B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体资讯的指标。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查询的关键字也就越多。相对来说IO读写次数也就降低了。

2) B+树的查询效率更加稳定

由于非终结点并不是最终指向档案内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查询必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

MySQL用的就是B+Tree索引。

mysql索引分类

单值索引

即一个索引只包含单个列,一个表可以有多个单列索引

唯一索引

索引列的值必须唯一,但允许有空值

主键索引

设定为主键后数据库会自动建立索引,innodb为聚簇索引

复合索引

即一个索引包含多个列

基本语法

建立

CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))

删除

DROP INDEX [indexName] ON mytable;

检视

SHOW INDEX FROM table_nameG

哪些情况需要建立索引

主键自动建立唯一索引

频繁作为查询条件的字段应该建立索引

查询中与其它表关联的字段,外来键关系建立索引

单键/组合索引的选择问题, 组合索引价效比更高

查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

查询中统计或者分组字段

具体怎么建索引优化,我会在下一篇文章中详谈,之后还会陆续更新 jvm虚拟机器,juc多执行绪程式设计,gc垃圾回收,activeMQ讯息伫列等java高阶技术,对java感兴趣的朋友记得关注一下。

2019-09-16 16:57:00

相关文章