SQL优化核心思想 PDF 完整版

  • 更新时间:
  • 9889人关注
  • 点击下载

给大家带来的一篇关于SQL优化相关的电子书资源,介绍了关于SQL优化、核心思想方面的内容,本书是由人民邮电出版社出版,格式为PDF,资源大小3.9MB,罗炳森 黄超 钟侥编写,目前豆瓣、亚马逊、当当、京东等电子书综合评分为:7.4分

SQL优化核心思想

出版时间: 2018

结构化查询语言(Structured Query Language,SQL)是一种功能强大的数据库语言。它基于关系代数运算,功能丰富、语言简洁、使用方便灵活,已成为关系数据库的标准语言。本书旨在引导读者掌握SQL优化技能,以更好地提升数据库性能。本书共分10章,从SQL基础知识、统计信息、执行计划、访问路径、表连接方式、成本计算、查询变换、调优技巧、经典案例、全自动SQL审核等角度介绍了有关SQL优化的方方面面。本书基于Oracle进行编写,内容讲解由浅入深,适合各个层次的读者学习。本书面向一线工程师、运维工程师、数据库管理员以及系统设计与开发人员,无论是初学者还是有一定基础的读者,都将从中获益。

目录

  • 第 1章SQL优化必懂概念 1
  • 1.1基数(CARDINALITY) 1
  • 1.2选择性(SELECTIVITY) 3
  • 1.3直方图(HISTOGRAM) 7
  • 1.4回表(TABLE ACCESS BY INDEXROWID) 13
  • 1.5集群因子(CLUSTERING FACTOR) 15
  • 1.6表与表之间关系 19
  • 第 2章统计信息 21
  • 2.1什么是统计信息 21
  • 2.2统计信息重要参数设置 24
  • 2.3检查统计信息是否过期 32
  • 2.4扩展统计信息 37
  • 2.5动态采样 42
  • 2.6定制统计信息收集策略 47
  • 第3章执行计划 49
  • 3.1获取执行计划常用方法 49
  • 3.1.1使用AUTOTRACE查看执行计划 49
  • 3.1.2使用EXPLAIN PLAN FOR查看执行计划 52
  • 3.1.3查看带有A-TIME的执行计划 54
  • 3.1.4查看正在执行的SQL的执行计划 56
  • 3.2定制执行计划 57
  • 3.3怎么通过查看执行计划建立索引 59
  • 3.4运用光标移动大法阅读执行计划 63
  • 第4章访问路径(ACCESS PATH) 67
  • 4.1常见访问路径 67
  • 4.1.1TABLE ACCESS FULL 67
  • 4.1.2TABLE ACCESS BY USER ROWID 71
  • 4.1.3TABLE ACCESS BY ROWID RANGE 71
  • 4.1.4TABLE ACCESS BY INDEX ROWID 72
  • 4.1.5INDEX UNIQUE SCAN 72
  • 4.1.6INDEX RANGE SCAN 73
  • 4.1.7INDEX SKIP SCAN 74
  • 4.1.8INDEX FULL SCAN 75
  • 4.1.9INDEX FAST FULL SCAN 77
  • 4.1.10INDEX FULL SCAN (MIN/MAX) 80
  • 4.1.11MAT_VIEW REWRITE ACCESS FULL 83
  • 4.2单块读与多块读 83
  • 4.3为什么有时候索引扫描比全表扫描更慢 84
  • 4.4DML对于索引维护的影响 84
  • 第5章表连接方式 86
  • 5.1嵌套循环(NESTED LOOPS) 86
  • 5.2HASH连接(HASH JOIN) 90
  • 5.3排序合并连接(SORT MERGE JOIN) 93
  • 5.4笛卡儿连接(CARTESIAN JOIN) 95
  • 5.5标量子查询(SCALAR SUBQUERY) 98
  • 5.6半连接(SEMI JOIN) 100
  • 5.6.1半连接等价改写 100
  • 5.6.2控制半连接执行计划 101
  • 5.6.3读者思考 103
  • 5.7反连接(ANTI JOIN) 104
  • 5.7.1反连接等价改写 104
  • 5.7.2控制反连接执行计划 105
  • 5.7.3读者思考 108
  • 5.8FILTER 108
  • 5.9IN与EXISTS谁快谁慢 111
  • 5.10SQL语句的本质 111
  • 第6章成本计算 112
  • 6.1优化SQL需要看COST吗 112
  • 6.2全表扫描成本计算 112
  • 6.3索引范围扫描成本计算 116
  • 6.4SQL优化核心思想 119
  • 第7章必须掌握的查询变换 120
  • 7.1子查询非嵌套 120
  • 7.2视图合并 125
  • 7.3谓词推入 129
  • 第8章调优技巧 133
  • 8.1查看真实的基数(Rows) 133
  • 8.2使用UNION代替OR 134
  • 8.3分页语句优化思路 135
  • 8.3.1单表分页优化思路 135
  • 8.3.2多表关联分页优化思路 150
  • 8.4使用分析函数优化自连接 153
  • 8.5超大表与超小表关联优化方法 154
  • 8.6超大表与超大表关联优化方法 155
  • 8.7LIKE语句优化方法 159
  • 8.8DBLINK优化 161
  • 8.9对表进行ROWID切片 167
  • 8.10SQL三段分拆法 169
  • 第9章SQL优化案例赏析 170
  • 9.1组合索引优化案例 170
  • 9.2直方图优化案例 173
  • 9.3NL被驱动表不能走INDEX SKIP SCAN 177
  • 9.4优化SQL需要注意表与表之间关系 178
  • 9.5INDEX FAST FULL SCAN优化案例 179
  • 9.6分页语句优化案例 181
  • 9.7ORDER BY取别名列优化案例 183
  • 9.8半连接反向驱动主表案例一 185
  • 9.9半连接反向驱动主表案例二 187
  • 9.10连接列数据分布不均衡导致性能问题 192
  • 9.11Filter优化经典案例 198
  • 9.12树形查询优化案例 202
  • 9.13本地索引优化案例 204
  • 9.14标量子查询优化案例 206
  • 9.14.1案例一 206
  • 9.14.2案例二 207
  • 9.15关联更新优化案例 211
  • 9.16外连接有OR关联条件只能走NL 213
  • 9.17把你脑袋当CBO 217
  • 9.18扩展统计信息优化案例 221
  • 9.19使用LISGAGG分析函数优化WMSYS.WM_CONCAT 227
  • 9.20INSTR非等值关联优化案例 230
  • 9.21REGEXP_LIKE非等值关联优化案例 233
  • 9.22ROW LEVEL SECURITY优化案例 237
  • 9.23子查询非嵌套优化案例一 240
  • 9.24子查询非嵌套优化案例二 247
  • 9.25烂用外连接导致无法谓词推入 252
  • 9.26谓词推入优化案例 262
  • 9.27使用CARDINALITY优化SQL 268
  • 9.28利用等待事件优化SQL 272
  • 第 10章全自动SQL审核 281
  • 10.1抓出外键没创建索引的表 281
  • 10.2抓出需要收集直方图的列 282
  • 10.3抓出必须创建索引的列 283
  • 10.4抓出SELECT * 的SQL 284
  • 10.5抓出有标量子查询的SQL 285
  • 10.6抓出带有自定义函数的SQL 286
  • 10.7抓出表被多次反复调用SQL 287
  • 10.8抓出走了FILTER的SQL 288
  • 10.9抓出返回行数较多的嵌套循环SQL 290
  • 10.10抓出NL被驱动表走了全表扫描的SQL 292
  • 10.11抓出走了TABLE ACCESS FULL的SQL 293
  • 10.12抓出走了INDEX FULL SCAN的SQL 294
  • 10.13抓出走了INDEX SKIP SCAN的SQL 295
  • 10.14抓出索引被哪些SQL引用 297
  • 10.15抓出走了笛卡儿积的SQL 298
  • 10.16抓出走了错误的排序合并连接的SQL 299
  • 10.17抓出LOOP套LOOP的PSQL 301
  • 10.18抓出走了低选择性索引的SQL 302
  • 10.19抓出可以创建组合索引的SQL(回表再过滤选择性高的列) 304
  • 10.20抓出可以创建组合索引的SQL(回表只访问少数字段) 306
     
展开阅读
精选笔记:MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

4小时36分钟前回答

在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子:

MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦。

在优化这个语句之前,我们先了解下SQL查询的基本执行过程:

1.应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析

2.检查权限、MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(query plan),并可以被缓存

3.如果存在索引,那么先扫描索引,如果数据被索引覆盖,那么不需要额外的查找,如果不是,根据索引查找和读取对应的记录

4.如果有关联查询,查询次序是扫描第一张表找到满足条件的记录,按照第一张表和第二张表的关联键值,扫描第二张表查找满足条件的记录,按此顺序循环

5.输出查询结果,并记录binary logs

显然合适的索引将大大简化和加速查找。再看一下上面那条查询语句,除了条件查询外,还有关联查询以及ORDER BY即排序操作,

那么让我们进一步了解下关联查询(JOIN)和ORDER BY是怎么工作的,MySQL有三种方式来处理关联查询和数据排序:

MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

第一种方法是基于索引,第二种是对第一个非常量表进行filesort(quicksort),还有一种是把联合查询的结果放入临时表,然后进行filesort。

注1:关于什么是非常量表,请参考阅读MySQL开发手册:Consts and Constant Tables,
注2:什么是filesort呢,这不是字面意思的文件排序,filesort有两种模式:
1、模式1:排序后的元素涵盖了要输出的数据。排序结果是一串有序序列元素组,不再需要额外的记录读取;
2、模式2:排序结果是<sort_key,row_id>键值对序列,通过这些row_ids再去读取记录(随机读取,效率低下);
注3:关于什么是临时表,请参考阅读MySQL开发手册:How MySQL Uses Internal Temporary Tables

第一种方法用于第一个非常量表中存在ORDER BY所依赖的列的索引,那就可直接使用已经有序的索引来查找关联表的数据,这种方式是性能最优的,因为不需要额外的排序动作:

MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

第二种方式用于ORDER BY所依赖的列全部属于第一张查询表且没有索引,那么我们可以先对第一张表的记录进行filesort(模式可能是模式1也可能是模式2),得到有序行索引,然后再做关联查询,filesort的结果可能是在内存中,也可能在硬盘上,这取决于系统变量sort_buffer_size(一般为2M左右):

MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

第三种方法用于当ORDER BY的元素不属于第一张表时,需要把关联查询的结果放入临时表,最后对临时表进行filesort:

MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

第三种方法中的临时表,可能是在内存中(in-memory table),也可能是在硬盘上,一般是下面两种情况会使用硬盘(on-disk table):

(1)使用了BLOB,TEXT类型的数据

(2)内存表占用超过了系统变量tmp_table_size/max_heap_table_size的限定(一般为16M左右),只能放在硬盘上

从上面的查询执行过程和方式,我们应该可以清楚的知道为什么Using filesort,Using temporary会严重的影响查询性能,因为如果数据类型或者字段设计有问题,

在需要查询的表以及结果中存在大数据的字段,而没有合适的索引可用时,都可能会导致产生大量的IO操作,这就是查询性能缓慢的根源所在。

回到文章开头所举的查询实例,它显然是使用了效率最低的第三种方法,我们需要做和尝试的优化手段有:

1、为users.fl_no添加索引,为select和where所使用的字段建立索引

2、把users.fl_no转移到或者作为冗余字段添加到表user_profile中

3、去除TEXT类型的字段,TEXT可以替换为VARCHAR(65535)或对于中文而言VARCHAR(20000)

4、如果实在无法消除Using filesort,那么提高sort_buffer_size,以减少IO操作负担

5、尽量使用第一张表所覆盖的索引进行排序,实在不行,可以把排序逻辑从MySQL中移到PHP/Java程序中执行

实施1、2、3的优化方法后,EXPLAIN结果如下:

MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

备注:编写简单的PHP应用,用siege测试,查询效率提高>3倍。

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持码农之家。

展开阅读

资源下载

相关资源

  • SQL Server 2008中文版标准教程

    SQL Server 2008中文版标准教程

    SQLServer2008中文版标准教程 作者:康会光等编著 清华大学 出版时间:2009-3-1 本书全面介绍了SQLServer2008中文版数据库管理和应用的知识。本书共分12章,介绍数据库与SQLServer2008基础知识,数据查询和管理,SQLServer2008数据库的创建、配置和管理,数据类型、列属性、表数据完整性,Transact-SQL语言基础、常用函数和SQLServer2008中Transact-SQL语言增强功能等,使用XML查询技术,存储过程与触发器,操作架构、索引和视图,SQ

    大小:117.18MBSQL Server

    立即下载
  • PostgreSQL实战

    PostgreSQL实战

    本书基于PostgreSQL 10编写,重点介绍PostgreSQL的丰富特性以及生产实践运维技巧。这是一本值得存放于身旁的PostgreSQL参考书,特别是性能分析、集群、分片、地理信息等高技术含量的章节,可以作为日常工作的有效参考

    大小:221 MBPostgreSQL

    立即下载
  • PostgreSQL 9.0性能调校

    PostgreSQL 9.0性能调校

    本书是针对PostgreSQL 9.0的性能调校指南,能够帮助读者加速PostgreSQL系统,并且避免一些可能导致系统缓慢的常见缺陷,感兴趣的可以下载学习

    大小:47.5 MBPostgreSQL

    立即下载
  • PHP&MySQL跨设备网站开发实例精粹

    PHP&MySQL跨设备网站开发实例精粹

    这本书从易学实用的角度详细讲解了PHP、HTML5语法,MySQL数据库存取,网页之间的信息传递、表单的后端处理等,提供了丰富的网站开发范例,欢迎下载

    大小:80.9 MBPHP编程

    立即下载
  • 深入浅出MySQL数据库开发、优化与管理维护

    深入浅出MySQL数据库开发、优化与管理维护

    大小:157 MBMySQL

    立即下载
  • SQL Server从入门到精通

    SQL Server从入门到精通

    大小:1.62 GBSQL Server

    立即下载
  • ORACLE PLSQL性能调优诀窍与方法

    ORACLE PLSQL性能调优诀窍与方法

    在 《Oracle PL/SQL性能调优诀窍与方法》 这本书中,作为Oracle ACE的两位作者给出了一种方便的能够从PL/SQL获取最佳结果的方法,他们有着数十年为政府、企业和教育机构创建复杂生产系统的经验

    大小:58.2 MBORACLE

    立即下载

学习笔记

1小时42分钟前回答

MySQL优化之连接优化

上文MySQL优化之缓存优化 这篇文章中提到了一个很重要的概念,就是show variables是用来表示系统编译或者配置在my.cnf中的变量值。而show status则称之为状态值,显示的是当前服务实例运行所具有的状态信息,是一个动态改变的值。因此常用来观测当前MySQl的运行是否正常,如果不正常那么依靠调整静态参数来提高MySQL的性能。所以明白这两个概念的不同,是后面调优的基础。 MySQL 连接优化 记得有一次在公司内部连接MySQL的时候,总是连接不上。找到DBA后,经过排查原因,是当前MySQL连接数满了,经过调整后,解决了问题。引发连接数过多的错误原因一般有两个,第一的确是有很多人在连接MySQL,造成连接……

24小时6分钟前回答

MySQL单表分页性能优化技巧

测试环境: 先让我们熟悉下基本的sql语句,来查看下我们将要测试表的基本信息 use infomation_schemaSELECT * FROM TABLES WHERE TABLE_SCHEMA = dbname AND TABLE_NAME = product 查询结果: 从上图中我们可以看到表的基本信息: 表行数:866633 平均每行的数据长度:5133字节 单表大小:4448700632字节 关于行和表大小的单位都是字节,我们经过计算可以知道 平均行长度:大约5k 单表总大小:4.1g 表中字段各种类型都有varchar、datetime、text等,id字段为主键 测试实验 1. 直接用limit start, count分页语句, 也是我程序中用的方法: select * from product limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开……