MySQL的查询优化技术-01

数据库查询优化技术/li_hx/TheQueryOptimizationTechniqueOfMySQL(5.6.X)@那海蓝蓝1数据库查询优化技术/li_hx/1Adatatechnologylearners,practitionersBook:《数据库查询优化器的艺术：原理解析与SQL性能优化》23Blog:/li_hx4Twitter：@那海蓝蓝Email:database_XX@126.com5Direction:DB(PostgreSQL,MySQL.etc)63/li_hx/课程大纲：1数据库与关系代数2数据库查询优化技术总揽3查询优化技术理论与MySQL实践（一）------子查询的优化（一）4查询优化技术理论与MySQL实践（二）------子查询的优化（二）5查询优化技术理论与MySQL实践（三）------视图重写与等价谓词重写6查询优化技术理论与MySQL实践（四）------条件化简7查询优化技术理论与MySQL实践（五）------外连接消除、嵌套连接消除与连接消除8查询优化技术理论与MySQL实践（六）------数据库的约束规则与语义优化9查询优化技术理论与MySQL实践（七）------非SPJ的优化10MySQL物理查询优化技术概述11MySQL索引的利用、优化12表扫描与连接算法与MySQL多表连接优化实践13查询优化的综合实例------TPCH实践（一）14查询优化的综合实例------TPCH实践（二）15关系代数对于数据库的查询优化的指导意义------查询优化技术总结/li_hx/Lesson1:DatabaseandRelationalAlgebra

3WhatistheQueryOptimizationOfMySQL？Howtolearnandmasterit？45Whatistherelationalalgebra？5/li_hx/1WhatistheRDBMS？2WhatisthetechnologyOfQueryOptimization？Whatisdatabaseoptimization？66/li_hx/1WhatisthetechnologyOfRDBMS？1.3WhydoweneedtomasterQueryOptimizationTechnology？1.4WhatistheRelationDatabaseManagementSystem？1.1WhatistheDatabaseManagementSystem？1.2WhydoweneedtolearnMySQL/PostgreSQL？7/li_hx/1.1WhatistheDatabaseManagementSystem？数据库管理系统(DatabaseManagementSystem)：1是一种操纵和管理数据的大型软件，用于建立、使用和维护数据，简称DBMS。2它对数据进行统一的管理和控制，以保证数据的安全性和完整性。3用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。4它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据。5DBMS提供数据定义语言DDL（DataDefinitionLanguage）和数据操作语言DML（DataManipulationLanguage），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。8/li_hx/1.1WhatistheDatabaseManagementSystem？数据库管理系统(DatabaseManagementSystem)：只要做数据处理，软件规模达到一定程度，似乎都称为了数据库。如：HBase/MongoDB等等衡量是否是数据库的标准：ACID，是指在数据库管理系统（DBMS）中事务所具有的四个特性：1）原子性（Atomicity）2）一致性（Consistency）3）隔离性（Isolation）4）持久性（Durability）

+技术的复杂性9/li_hx/1.2WhatistheRelationDatabaseManagementSystem？历史上的几种类型的数据库：1层次型2网状型3关系型关系数据库，是建立在关系数据库模型基础上的数据库，借助于关系代数等概念和方法来处理数据库中的数据，同时也是一个被组织成一组拥有正式描述性的表格，该形式的表格作用的实质是装载着数据项的特殊收集体，这些表格中的数据能以许多不同的方式被存取或重新召集而不需要重新组织数据库表格。10/li_hx/1.2WhatistheRelationDatabaseManagementSystem？/li_hx/blog/static/18399141320144164585328/11/li_hx/1.3WhydoweneedtolearnMySQL/PostgreSQL？趋势1：云计算，淘汰大部分的运维人员趋势2：云数据库，淘汰大部分以商业数据库为职业的DBA趋势3：电商等兴起，对开源数据库技术的人员需求增多趋势4：去IOE化等运动背后的商业成本和安全问题，促进开源产业发展趋势5：MySQL和PostgreSQL为代表的开源数据库自身正蓬勃发展中/li_hx/blog/static/18399141320144164585328/12/li_hx/1.4WhydoweneedtomasterQueryOptimizationTechnology？名师录：

数据库查询优化技术一直是DBMS实现技术中的精华，也是难点和重点。

王珊中国人民大学信息学院教授/博士生导师13/li_hx/1.4WhydoweneedtomasterQueryOptimizationTechnology？从数据处理的角度看：

数据库的OLAP型应用要多于OLTP型应用。

大数据技术目前处理了一部分非结构化的数据，但分布式数据库技术的发展，将使得数据库技术进一步发展甚至出现革命性的进步进而与现行的hadoop/spark等技术争夺数据分析的阵地。

而查询优化技术因此二者得益继续发展。从当下技术发展的趋势看：

大数据背景下，盛行几十年的SQL查询技术焕发了新的生机，不仅在数据库中占有半壁江山，而且在大数据处理技术下，各种SQL查询接口/功能层异军突起。

而查询优化技术因具有普适性得以继续使用。14/li_hx/2Whatistherelationalalgebra？数学中，关系代数是支持叫做逆反(converse)的对合一元运算的剩余布尔代数。激发关系代数的例子是在集合X上的所有二元关系的代数，带有R·S被解释为平常的二元关系复合。关系代数的早期形式形成于十九世纪德·摩根、皮尔士和ErnstSchröder的工作。它今日的纯等式形式是阿尔弗雷德·塔斯基和他的学生在1940年代开发的。15/li_hx/2Whatistherelationalalgebra？数据库中，关系代数是一阶逻辑的分支，是闭合于运算下的关系的集合。运算作用于一个或多个关系上来生成一个关系。关系代数是计算机科学的一部分。

在纯数学中的关系代数是有关于数理逻辑和集合论的代数结构。

SQL的查询语言松散的基于了关系代数，尽管SQL中的操作数（表）不完全是关系，很多有用的关于关系代数的理论在SQL对应者中不成立。16/li_hx/2Whatistherelationalalgebra？Codd的关系代数的六个原始运算是“选择”、“投影”、笛卡尔积（也叫做“叉积”或“交叉连接”）、并集、差集和“重命名”。（实际上，Codd忽略了重命名，而ISBL的发明者显著的包括了它）。这六个运算在省略其中任何一个都要损失表达能力的意义上是基本的。已经依据这六个原始运算定义了很多其他运算。其中最重要的是交集、除法和自然连接。事实上ISBL显著的用自然连接替代了笛卡尔积，它是笛卡尔积的退化情况。第一个基于Codd的代数的查询语言是ISBL，许多作者都认同这个先驱的工作展示了一个使Codd的想法成为有用语言的方式1970年E.F.Codd发表了数据的关系模型论文。Codd提议这样一种代数作为数据库查询语言的基础，称为关系代数。17/li_hx/2Whatistherelationalalgebra？关系代数是一种抽象的查询语言，用对关系的运算来表达查询，作为研究关系数据语言的数学工具。关系代数的运算对象是关系，运算结果亦为关系。关系代数用到的运算符包括四类：1）集合运算符2）专门的关系运算符3）算术比较符4）逻辑运算符比较运算符和逻辑运算符是用来辅助专门的关系运算符进行操作的，所以按照运算符的不同，主要将关系代数分为传统的集合运算和专门的关系运算两类。18/li_hx/3What

isthetechnologyOfQueryOptimization？摘自：《数据库查询优化器的艺术：原理解析与SQL性能的艺术》19/li_hx/3What

isthetechnologyOfQueryOptimization？QueryreuseThequeryresultreuseThequeryplanreuseTheRuleOfQueryRewriteBasedonRelationalAlgebraandHeuristicRuleViewRewrite、Sub-queryOptimization、EquivalentPredicateRewrite、ConditionSimplification、OuterJoinElimination、JoinElimination、NestJoinEliminationSematicOptimizationTheAlgorithmOfQueryOptimizationSingleTableScanAlgorithmTwoTableJoinAlgorithmMulti-tableJoinAlgorithmParallelQueryOptimizationDistributeQueryOptimization1920/li_hx/4WhatistheQueryOptimizationOfMySQL？Queryreuse------MySQL5.6.10NotSupportThequeryresultreuseThequeryplanreuseTheRuleOfQueryRewriteBasedonRelationalAlgebraandHeuristicRuleViewRewrite、

Sub-queryOptimization、EquivalentPredicateRewrite、ConditionSimplification、OuterJoinElimination、JoinElimination、NestJoinEliminationSematicOptimization

TheAlgorithmOfQueryOptimizationSingleTableScanAlgorithmTwoTableJoinAlgorithmMulti-tableJoinAlgorithmParallelQueryOptimizationDistributeQueryOptimization2021/li_hx/5Howtolearnandmasterit？22/li_hx/5Howtolearnandmasterit？九阳真经主要内容：1查询优化技术的原理2查询优化领域的主要技术3PostgreSQL查询优化器分析4MySQL查询优化器分析5PostgreSQLpkMySQL---必读内外兼修23/li_hx/5Howtolearnandmasterit？修习心法1读书而明理2听讲而释疑3练习而掌握4实践中巧遇大造化5再：读书/听讲/交流/思考/实践......24/li_hx/6Whatisdatabaseoptimization？数据库调优/DatabaseTuning数据库调优可以使数据库应用运行得更快，其目标是：使数据库

有更高的吞吐量

更短的响应时间。被调优的对象是整个数据库管理系统总体。25/li_hx/6Whatisdatabaseoptimization？数据库调优的方式通常有如下几种：1）人工调优。

主要依赖于人，效率低下；要求操作者完全理解常识所依赖的原理，还需要对应用、数据库管理系统、操作系统以及硬件有广泛而深刻的理解。2）基于案例的调优。

总结典型应用案例情况中数据库参数的推荐配置值、数据逻辑层设计等情况，从而为用户的调优工作提供一定的参考和借鉴。但这种方式忽略了系统的动态性和不同系统间存在的差异。3）自调优。

为数据库系统建立一个模型，根据“影响数据库系统性能效率的因素”，数据库系统自动进行参数的配置。一些商业数据库，实现了部分自调优技术。26/li_hx/6Whatisdatabaseoptimization？数据库调优主要分为五个阶段27/li_hx/6Whatisdatabaseoptimization？数据库调优五个阶段的主要技术1应用情况的估算。

应用的使用方式（把业务逻辑转换为数据库的读写分布逻辑，以是读多写少还是读写均衡等来区分OLTP和OLAP；应用对数据库的并发情况、并发是否可以池化等）、数据量、对数据库的压力、峰值压力等做一个预估。2系统选型策略。

确定什么样的数据库可以适用应用需求，并确定数据库是使用开源的还是商业的，是集使用群还是单机的系统，同时对操作系统、中间件、硬件、网络等进行选型。28/li_hx/6Whatisdatabaseoptimization？数据库调优五个阶段的主要技术1应用情况的估算。

应用的使用方式（把业务逻辑转换为数据库的读写分布逻辑，以是读多写少还是读写均衡等来区分OLTP和OLAP；应用对数据库的并发情况、并发是否可以池化等）、数据量、对数据库的压力、峰值压力等做一个预估。2系统选型策略。

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