mysql sql优化与调优机制详解_《高性能SQL调优精要与案例解析》一书谈主流关系库SQL调优(SQL TUNING或SQL优化)核心机制之——索引(index)...

继《高性能SQL调优精要与案例解析》一书谈SQL调优(SQL TUNING或SQL优化),我们今天就谈谈各主流关系库中,占据SQL调优技术和工作半壁江山的、最重要的核心机制之一——索引(index)。我们知道,《高性能SQL调优精要与案例解析》一书中也再三强调索引对SQL调优的重要性,可是上篇文章中也谈到,只看案例和解决问题的具体方法,而不掌握SQL调优的基础知识,是没有用的,我们必须做到知其然,更要知其所以然,才能做到融会贯通,活学活用,进而将SQL调优技术掌握到炉火纯青的地步。因《高性能SQL调优精要与案例解析》主要以Oracle数据库为基础,对SQL调优技术进行了讲解,上篇文章中,我也说到,各关系库就SQL调优的分析和解决问题的思路、方法和步骤来说,几乎完全一样,只是具体命令、方法和形式有所差别而已。那么据此,我们今天就对各主流关系库的索引机制进行简要阐述,以帮助读者能更加深入理解和掌握《高性能SQL调优精要与案例解析》一书中的内容和精髓,更寄希望能使其他同学多多受益。但关系库中,索引类型不止一种,而不同关系库,相同关系库不同版本之间,也有差别,那么,下面,我们仅就应用最广泛的B*tree索引加以介绍。

1、Oracle B*tree索引:Oracle中B*Tree索引的组织结构图在《高性能SQL调优精要与案例解析》一书中均有详细描述,这里不再赘述,需要强调的是,Oracle中B*Tree的非叶级块(non-leaf level block )中,存储的只有索引列的键值(单列索引的列值或多列索引的列值组合)最大值和指向下一级叶级块(leaf level block)或非叶级块的指针(pointer),这里的指针,也就是块的文件号+块号。而Oracle中B*Tree的叶级块中,则存储了索引列的键值+ROWID(数据行所在文件号+块号+槽号)这样,通过B*Tree中的键值和ROWID值,就能很容易的通过索引查找到表中的数据行。而Oracle中表对应的段中数据行,则是堆结构(heap),具体见《高性能SQL调优精要与案例解析》。而值得一提的是,Oracle中还有一种特殊的表组织结构,那就是索引组织表(IOT),该类表虽然在Oracle中应用不多,但在其他关系库中,确应用很广,只不过名称和细节不同而已,具体继续看下面的内容。

2、MYSQL B*Tree索引:大家知道,MYSQL数据库是一种插件数据库,也就是其中的数据存储引擎可以方便的进行插拔,因此,MYSQL中也有多种存储引擎同时存在。因为本文不是专门讲述MYSQL存储引擎的,因此,我们就拿应用最广的INNODB为例来进行说明。INNODB中的B*Tree索引(MYSQL中又称为key),和Oracle中不同的是,根据具体的组织结构,又可分为簇索引(clustered index或primary key index)和非簇索引(secondary index),在innodb中创建一个表时,系统会为表的主键创建一个簇索引,如果不指定一个主键,系统会选定一个唯一非空索引作为主键,如果不存在唯一非空索引,系统也会自动创建一个隐式主键,总之,表的主键非有不可。INNODB中表的数据,都存储在表的簇索引中,具体说,簇索引是一个B*Tree结构,只是叶级页(leaf

level page)内除了簇索引的键值外,还包含了表中所有的数据列值,因此,INNODB表的数据是有序的。而INNODB表中非簇索引,其叶级块中并不包含数据行的物理地址(类似Oracle中数据行的ROWID),而是包含了表上簇索引中的键值,因此,INNODB中,通过非簇索引查找数据,一般要经历两次键值查找,第一次在非簇索引上,找到簇索引的键值后,再到簇索引上再次查找,才能找到真正要查找的数据行。这里强调的是,INNODB中表的簇索引并不是个可选项。其组织结构和Oracle中的索引组织表类似。

3.SQL SERVER B*Tree索引:SQL SERVER数据库,作为微软的拳头产品之一,有时也被人们俗称为MSSQL,目前国内外市场有着相当的占有率。MSSQL中的B*Tree索引,和MYSQL中的类似,也分为簇索引(clustered index)和非簇索引(nonclustered index),但和MYSQL中B*Tree索引不同的是,MSSQL中表上的簇索引并非是强制的,也就是,你创建一张表,该表是一张堆表,也就是其对应的段是以堆的形式组织和存储的,堆的概念,《高性能SQL调优精要与案例解析》一书中有详细论述,这里不再赘述。MSSQL中的堆表上,如果你创建了一个簇索引,那么,该表中的数据都被移到该簇索引的叶级页(leaf

level page)中,并且以该簇索引的键值顺序排序组织和存储,原来的堆不再存在,如果你选择在该表上不创建簇索引,那么,该表就会一直以堆的形式存在。而MSSQL中的非簇索引,因为簇索引的存在与否,其内部组织和机制也分两种情况,当表上存在簇索引时,非簇索引的叶级页中存储的是簇索引的键值,也就是不存在指向相关数据行物理地址的指针;而如果表上不存在簇索引时,非簇索引叶级页中就会存储指向表中相关数据行物理地址的指针(这个指针MSSQL中叫做rid)。而不管表上是否存在簇索引,MSSQL将通过非簇索引查找数据行的行为称为书签查找(bookmark

lookup),虽然,MSSQL中,一般将主键作为表上的簇索引来进行使用和创建。我们可以看到,MSSQL中的簇索引,在组织结构上和Oracle中的索引组织表及MYSQL中的簇索引相似。

4.Postgresql B*Tree索引:Postgresql数据库,作为最强大的开源关系库之一,号称免费版的Oracle,但就其应用特点、行锁及MVCC等方面具体内部实现来讲,确实与Oracle有很多相似之处,也可以说是关系库大家族中,和Oracle最相近的一款关系库。上面我们也说到,Postgresql数据库在很多方面和Oracle非常相似,那么,B*Tree索引的组织结构和应用也不例外,Postgresql数据库中的B*Tree索引和Oracle中很相似,也并没有MYSQL和MSSQL数据库中簇索引一说。值得一提的是,Postgresql中的表和索引等数据库对象,都是以单独的文件形式组织和存储,8k大小的数据页也与Oracle中的块不同,Postgresql中的mvcc与Oracle中的实现机制也有很大区别,因为,Postgresql表中数据的前影像数据和当前版本存储在一起,需要定期通过vaccum进行清除。更值得一提的是,因为Postgresql中B*Tree索引中并不存在数据的版本信息,因此,SQL语句的索引覆盖技术并不存在,无论索引列是否能覆盖SQL语句的所有列,都需要回表操作来确认具体数据行的版本信息,虽然,在postgresql9.2版本中引进了scan-only

scans操作,这虽然在有些时候可以避开回表操作,但需要访问表的VM文件,更关键的是,如果VM文件中的相应位(bit)为unset状态,还是需要回表,因此,鉴于其机制上的局限,该技术实际中价值并没那么大,也许对写少读多的业务,会有一定的价值。

以上,仅就常用主流关系库索引机制做简要陈述,以帮助各位读者理解《高性能SQL调优精要及案例解析》一书内容,同时,也希望其他同学多多受益。

点赞 (0)赏分享 (0)

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
DirectX修复工具(DirectX Repair)是一款系统级工具软件,简便易用。本程序为绿色版,无需安装,可直接运行。 本程序的主要功能是检测当前系统的DirectX状态,如果发现异常则进行修复。程序主要针对0xc000007b问题设计,可以完美修复该问题。本程序中包含了最新版的DirectX redist(Jun2010),并且全部DX文件都有Microsoft的数字签名,安全放心。 本程序为了应对一般电脑用户的使用,采用了易用的一键式设计,只要点击主界面上的“检测并修复”按钮,程序就会自动完成校验、检测、下载、修复以及注册的全部功能,无需用户的介入,大大降低了使用难度。在常规修复过程中,程序还会自动检测DirectX加速状态,在异常时给予用户相应提示。 本程序适用于多个操作系统,如Windows XP(需先安装.NET 2.0,详情请参阅“致Windows XP用户.txt”文件)、Windows Vista、Windows 7、Windows 8、Windows 8.1、Windows 8.1 Update、Windows 10,同时兼容32位操作系统和64位操作系统。本程序会根据系统的不同,自动整任务模式,无需用户进行设置。 本程序的V4.0版分为标准版、增强版以及在线修复版。所有版本都支持修复DirectX的功能,而增强版则额外支持修复c++的功能。在线修复版功能与标准版相同,但其所需的数据包需要在修复时自动下载。各个版本之间,主程序完全相同,只是其配套使用的数据包不同。因此,标准版和在线修复版可以通过补全扩展包的形式成为增强版。本程序自V3.5版起,自带扩展功能。只要在主界面的“工具”菜单下打开“选项”对话框,找到“扩展”标签,点击其中的“开始扩展”按钮即可。扩展过程需要Internet连接,扩展成功后新的数据包可自动生效。扩展用时根据网络速度不同而不同,最快仅需数秒,最慢需要数分钟,烦请耐心等待。如扩展失败,可点击“扩展”界面左上角小锁图标切换为加密连接,即可很大程度上避免因防火墙或其他原因导致的连接失败。 本程序自V2.0版起采用全新的底层程序架构,使用了异步多线程编程技术,使得检测、下载、修复单独进行,互不干扰,快速如飞。新程序更改了自我校验方式,因此使用新版本的程序时不会再出现自我校验失败的错误;但并非取消自我校验,因此程序安全性与之前版本相同,并未降低。 程序有更新系统c++功能。由于绝大多数软件运行时需要c++的支持,并且c++的异常也会导致0xc000007b错误,因此程序在检测修复的同时,也会根据需要更新系统中的c++组件。自V3.2版本开始使用了全新的c++扩展包,可以大幅提高工业软件修复成功的概率。修复c++的功能仅限于增强版,标准版及在线修复版在系统c++异常时(非丢失时)会提示用户使用增强版进行修复。除常规修复外,新版程序还支持C++强力修复功能。当常规修复无效时,可以到本程序的选项界面内开启强力修复功能,可大幅提高修复成功率。请注意,请仅在常规修复无效时再使用此功能。 程序有两种窗口样式。正常模式即默认样式,适合绝大多数用户使用。另有一种简约模式,此时窗口将只显示最基本的内容,修复会自动进行,修复完成10秒钟后会自动退出。该窗口样式可以使修复工作变得更加简单快速,同时方便其他软件、游戏将本程序内嵌,即可进行无需人工参与的快速修复。开启简约模式的方法是:打开程序所在目录下的“Settings.ini”文件(如果没有可以自己创建),将其中的“FormStyle”一项的值改为“Simple”并保存即可。 新版程序支持命令行运行模式。在命令行中用本程序,可以在路径后直接添加命令进行相应的设置。常见的命令有7类,分别是设置语言的命令、设置窗口模式的命令,设置安全级别的命令、开启强力修复的命令、设置c++修复模式的命令、控制Direct加速的命令、显示版权信息的命令。具体命令名称可以通过“/help”或“/?”进行查询。 程序有高级筛选功能,开启该功能后用户可以自主选择要修复的文件,避免了其他不必要的修复工作。同时,也支持通过文件进行辅助筛选,只要在程序目录下建立“Filter.dat”文件,其中的每一行写一个需要修复文件的序号即可。该功能仅针对高级用户使用,并且必须在正常窗口模式下才有效(简约模式时无效)。 本程序有自动记录日志功能,可以记录每一次检测修复结果,方便在出现问题时,及时分析和查找原因,以便找到解决办法。 程序的“选项”对话框中包含了7项高级功能。点击"常规”选项卡可以整程序的基本运行情况,包括日志记录、安全级别控制、试模式开启等。只有开启试模式后才能在C
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页