MySQL查询优化技术-01

1、数据库查询优化技术http:/ Query Optimization Technique Of MySQL(5.6.X)那海蓝蓝那海蓝蓝1数据库查询优化技术http:/ data technology learners, practitioners Book : 数据库查询优化器的艺术：原理解析与数据库查询优化器的艺术：原理解析与SQL性能优化性能优化23Blog : http:/ : database_XX5Direction : DB(PostgreSQL,MySQL.etc)63http:/ 数据库与关系代数2 数据库查询优化技术总揽3 查询优化技术理论与MySQL实践（一）-子查询的优

2、化（一）4 查询优化技术理论与MySQL实践（二）-子查询的优化（二）5 查询优化技术理论与MySQL实践（三）-视图重写与等价谓词重写6 查询优化技术理论与MySQL实践（四）-条件化简7 查询优化技术理论与MySQL实践（五）-外连接消除、嵌套连接消除与连接消除8 查询优化技术理论与MySQL实践（六）-数据库的约束规则与语义优化9 查询优化技术理论与MySQL实践（七）-非SPJ的优化10 MySQL物理查询优化技术概述11 MySQL索引的利用、优化12 表扫描与连接算法与MySQL多表连接优化实践13 查询优化的综合实例-TPCH实践（一）14 查询优化的综合实例-TPCH实践（二）

3、15 关系代数对于数据库的查询优化的指导意义-查询优化技术总结http:/ 1:Database and Relational Algebra 3What is the Query Optimization Of MySQL ？How to learn and master it？45What is the relational algebra ？5http:/ is the RDBMS ？2What is the technology Of Query Optimization ？What is database optimization？66http:/ What is the techn

4、ology Of RDBMS ？1.3Why do we need to master Query Optimization Technology ？1.4What is the Relation Database Management System ？1.1What is the Database Management System ？1.2Why do we need to learn MySQL/PostgreSQL ？7http:/ What is the Database Management System ？数据库管理系统数据库管理系统(Database Management Sy

5、stem)：1 是一种操纵和管理数据的大型软件，用于建立、使用和维护数据，简称DBMS。2 它对数据进行统一的管理和控制，以保证数据的安全性和完整性。3 用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。4 它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据。5 DBMS提供数据定义语言DDL（Data Definition Language）和数据操作语言DML（Data Manipulation Language），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。8http:/ What is the Datab

6、ase Management System ？数据库管理系统数据库管理系统(Database Management System)：只要做数据处理，软件规模达到一定程度，似乎都称为了数据库。如：HBase/MongoDB等等衡量是否是数据库的标准：衡量是否是数据库的标准：ACID，是指在数据库管理系统（DBMS）中事务所具有的四个特性：1）原子性（Atomicity）2）一致性（Consistency）3）隔离性（Isolation）4）持久性（Durability） + 技术的复杂性技术的复杂性9http:/ What is the Relation Database Management

7、System ？历史上的几种类型的数据库：1 层次型2 网状型3 关系型关系数据库关系数据库，是建立在关系数据库模型基础上的数据库，借助于关系代数等概念和方法来处理数据库中的数据，同时也是一个被组织成一组拥有正式描述性的表格，该形式的表格作用的实质是装载着数据项的特殊收集体，这些表格中的数据能以许多不同的方式被存取或重新召集而不需要重新组织数据库表格。10http:/ What is the Relation Database Management System ？http:/ Why do we need to learn MySQL/PostgreSQL ？趋势趋势1：云计算，淘汰大部分的

8、运维人员趋势趋势2：云数据库，淘汰大部分以商业数据库为职业的DBA趋势趋势3：电商等兴起，对开源数据库技术的人员需求增多趋势趋势4：去IOE化等运动背后的商业成本和安全问题，促进开源产业发展趋势趋势5：MySQL和PostgreSQL为代表的开源数据库自身正蓬勃发展中http:/ Why do we need to master Query Optimization Technology ？名师录： 数据库查询优化技术一直是数据库查询优化技术一直是DBMS实现技术中实现技术中的精华，也是难点和重点。的精华，也是难点和重点。 王珊中国人民大学信息学院教授/博士生导师13http:/ Why do

9、 we need to master Query Optimization Technology ？从数据处理的角度看：从数据处理的角度看： 数据库的OLAP型应用要多于OLTP型应用。 大数据技术目前处理了一部分非结构化的数据，但分布式数据库技术的发展，将使得数据库技术进一步发展甚至出现革命性的进步进而与现行的hadoop/spark等技术争夺数据分析的阵地。 而查询优化技术因此二者得益继续发展。从当下技术发展的趋势看：从当下技术发展的趋势看： 大数据背景下，盛行几十年的SQL查询技术焕发了新的生机，不仅在数据库中占有半壁江山，而且在大数据处理技术下，各种SQL查询接口/功能层异军突起。 而

10、查询优化技术因具有普适性得以继续使用。14http:/ What is the relational algebra ？数学数学中，关系代数是支持叫做逆反(converse)的对合一元运算的剩余布尔代数。激发关系代数的例子是在集合 X 上的所有二元关系的代数 ，带有 RS 被解释为平常的二元关系复合。关系代数的早期形式形成于十九世纪德摩根、皮尔士和 Ernst Schrder 的工作。它今日的纯等式形式是阿尔弗雷德塔斯基和他的学生在 1940 年代开发的。15http:/ What is the relational algebra ？数据库数据库中，关系代数是一阶逻辑的分支，是闭合于运算下闭

11、合于运算下的关系关系的集合。运算作用于一个或多个关系上来生成一个关系。关系代数是计算机科学的一部分。 在纯数学中的关系代数是有关于数理逻辑和集合论的代数结构。 SQL的查询语言松散的基于了关系代数，尽管SQL中的操作数（表）不完全是关系，很多有用的关于关系代数的理论在SQL对应者中不成立。16http:/ What is the relational algebra ？Codd的关系代数的六个原始运算是“选择选择”、“投影投影”、笛卡尔积笛卡尔积（也叫做“叉积”或“交叉连接”）、并集并集、差集差集和“重命名重命名”。（实际上，Codd忽略了重命名，而ISBL的发明者显著的包括了它）。这六个运算

12、在省略其中任何一个都要损失表达能力的意义上是基本的。已经依据这六个原始运算定义了很多其他运算。其中最重要的是交集、除法和自然连接。事实上ISBL显著的用自然连接替代了笛卡尔积，它是笛卡尔积的退化情况。第一个基于Codd的代数的查询语言是ISBL，许多作者都认同这个先驱的工作展示了一个使Codd的想法成为有用语言的方式1970年E.F. Codd发表了数据的关系模型论文。Codd提议这样一种代数作为数据库查询语言的基础，称为关系代数。17http:/ What is the relational algebra ？关系代数关系代数是一种抽象的查询语言，用对关系的运算来表达查询，作为研究关系数据语

13、言的数学工具。关系代数的运算对象是关系，运算结果亦为关系。关系代数用到的运算符包括四类：1）集合运算符2）专门的关系运算符3）算术比较符4）逻辑运算符比较运算符和逻辑运算符是用来辅助专门的关系运算符进行操作的，所以按照运算符的不同，主要将关系代数分为传统的集合运算和专门的关系运算两类。18http:/ What is the technology Of Query Optimization ？摘自：数据库查询优化器的艺术：原理解析与SQL性能的艺术19http:/ What is the technology Of Query Optimization ？Query reuseThe quer

14、y result reuseThe query result reuseThe query plan reuseThe query plan reuseThe Rule Of Query RewriteBased on Relational Algebra and Heuristic RuleBased on Relational Algebra and Heuristic RuleView RewriteView Rewrite、Sub-query OptimizationSub-query Optimization、Equivalent Equivalent Predicate Rewri

15、tePredicate Rewrite、Condition SimplificationCondition Simplification、Outer Join Outer Join EliminationElimination、Join EliminationJoin Elimination、Nest Join EliminationNest Join EliminationSematic Optimization Sematic Optimization The Algorithm Of Query OptimizationSingle Table Scan AlgorithmSingle

16、Table Scan AlgorithmTwo Table Join AlgorithmTwo Table Join AlgorithmMulti-table Join AlgorithmMulti-table Join AlgorithmParallel Query OptimizationDistribute Query Optimization1920http:/ What is the Query Optimization Of MySQL ？Query reuse -MySQL 5.6.10 Not SupportThe query result reuseThe query res

17、ult reuseThe query plan reuseThe query plan reuseThe Rule Of Query RewriteBased on Relational Algebra and Heuristic RuleBased on Relational Algebra and Heuristic RuleView RewriteView Rewrite、 Sub-query Optimization Sub-query Optimization、 Equivalent Equivalent Predicate RewritePredicate Rewrite、Cond

18、ition SimplificationCondition Simplification、Outer Join Outer Join EliminationElimination、Join Elimination、Nest Join EliminationNest Join EliminationSematic OptimizationSematic Optimization The Algorithm Of Query OptimizationSingle Table Scan AlgorithmSingle Table Scan AlgorithmTwo Table Join Algori

19、thmTwo Table Join AlgorithmMulti-table Join AlgorithmMulti-table Join AlgorithmParallel Query OptimizationDistribute Query Optimization 2021http:/ How to learn and master it？22http:/ How to learn and master it？九阳真经主要内容：1 查询优化技术的原理2 查询优化领域的主要技术3 PostgreSQL查询优化器分析4 MySQL查询优化器分析5 PostgreSQL pk MySQL-必读

20、内外兼修23http:/ How to learn and master it？修习心法1 读书而明理2 听讲而释疑3 练习而掌握4 实践中巧遇大造化5 再：读书/听讲/交流/思考/实践.24http:/ What is database optimization？数据库调优/Database Tuning数据库调优可以使数据库应用运行得更快，其目标是：使数据库 有更高的吞吐量 更短的响应时间。被调优的对象是整个数据库管理系统总体。25http:/ What is database optimization？数据库调优的方式通常有如下几种：1）人工调优。）人工调优。 主要依赖于人，效率低下；要

21、求操作者完全理解常识所依赖的原理，还需要对应用、数据库管理系统、操作系统以及硬件有广泛而深刻的理解。2）基于案例的调优。）基于案例的调优。 总结典型应用案例情况中数据库参数的推荐配置值、数据逻辑层设计等情况，从而为用户的调优工作提供一定的参考和借鉴。但这种方式忽略了系统的动态性和不同系统间存在的差异。3）自调优。）自调优。 为数据库系统建立一个模型，根据“影响数据库系统性能效率的因素”，数据库系统自动进行参数的配置。一些商业数据库，实现了部分自调优技术。26http:/ What is database optimization？数据库调优主要分为五个阶段数据库调优主要分为五个阶段27http

22、:/ What is database optimization？数据库调优五个阶段的主要技术数据库调优五个阶段的主要技术1 应用情况的估算。应用情况的估算。 应用的使用方式（把业务逻辑转换为数据库的读写分布逻辑，以是读多写少还是读写均衡等来区分OLTP和OLAP；应用对数据库的并发情况、并发是否可以池化等）、数据量、对数据库的压力、峰值压力等做一个预估。2系统选型策略。系统选型策略。 确定什么样的数据库可以适用应用需求，并确定数据库是使用开源的还是商业的，是集使用群还是单机的系统，同时对操作系统、中间件、硬件、网络等进行选型。28http:/ What is database optimiz

23、ation？数据库调优五个阶段的主要技术数据库调优五个阶段的主要技术1 应用情况的估算。应用情况的估算。 应用的使用方式（把业务逻辑转换为数据库的读写分布逻辑，以是读多写少还是读写均衡等来区分OLTP和OLAP；应用对数据库的并发情况、并发是否可以池化等）、数据量、对数据库的压力、峰值压力等做一个预估。2系统选型策略。系统选型策略。 确定什么样的数据库可以适用应用需求，并确定数据库是使用开源的还是商业的，是集使用群还是单机的系统，同时对操作系统、中间件、硬件、网络等进行选型。29http:/ What is database optimization？数据库调优五个阶段的主要技术数据库调优五个

24、阶段的主要技术3 数据模型的设计。数据模型的设计。主要是根据业务逻辑，从几个角度考虑表的逻辑结构，内容如下：3.1 E-R模型设计：模型设计：遵循E-R模型设计原理。偶尔的适当程度的非规范化可以改善系统查询性能。3.2 数据逻辑分布策略：数据逻辑分布策略：目的是减少数据请求的不必要的数据量，把用户需要的数据返回；可用的技术如分区、用E-R模型分表等（如互联网企业典型的用法，根据业务的不同，进行分库、分表等操作）。3.3 数据物理存储策略：数据物理存储策略：目的是减少IO，如启用压缩技术、把索引和表数据的存储分开，不同的表数据分布于不同的表空间，不同表空间分布在不同的物理存储上（尤其是读写量大的表空间分布在不同的物理存储上）、日志、索引和数据分布在不同的物理存储上等。3.4索引：索引：在查询频繁的对象上建立合适的索引，使索引的正效应大于负效应（索引的维护存在消耗）。30http:/ What is database optimization？数据库调优五个阶段的主要技术数据库调优五个阶段的主要技术4 SQL设计。设计。编写正确的、查询效率高的SQL语句。这依据的主要是“查询重写规则”，编写语句的过程中要注意，要有意识地保障SQL能利用到索引。5 数据库功能的启用。数据库功能的启用。数据库为提高性能提供了一些功能，可合理使用，具体如下