MySQL的查询优化技术

数据库查询优化技术(5.6.X)@那海蓝蓝1数据库查询优化技术:《数据库查询优化器的艺术：原理解析与SQL性能优化》23Blog:：@那海蓝蓝Email:[e-mail]5Direction:DB(PostgreSQL,MySQL.etc)63课程大纲：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关系代数对于数据库的查询优化的指导意义------查询优化技术总结:DatabaseandRelationalAlgebra3WhatistheQueryOptimizationOfMySQL？Howtolearnandmasterit？45Whatistherelationalalgebra？5？2WhatisthetechnologyOfQueryOptimization？Whatisdatabaseoptimization？66？1.3WhydoweneedtomasterQueryOptimizationTechnology？1.4WhatistheRelationDatabaseManagementSystem？1.1WhatistheDatabaseManagementSystem？1.2WhydoweneedtolearnMySQL/PostgreSQL？7？数据库管理系统(DatabaseManagementSystem)：1是一种操纵和管理数据的大型软件，用于建立、使用和维护数据，简称DBMS。2它对数据进行统一的管理和控制，以保证数据的安全性和完整性。3用户通过DBMS访问数据库中的数据，数据库管理员也通过dbms进行数据库的维护工作。4它可使多个应用程序和用户用不同的方法在同时或不同时刻去建立，修改和询问数据。5DBMS提供数据定义语言DDL（DataDefinitionLanguage）和数据操作语言DML（DataManipulationLanguage），供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。8？数据库管理系统(DatabaseManagementSystem)：只要做数据处理，软件规模达到一定程度，似乎都称为了数据库。如：HBase/MongoDB等等衡量是否是数据库的标准：ACID，是指在数据库管理系统（DBMS）中事务所具有的四个特性：1）原子性（Atomicity）2）一致性（Consistency）3）隔离性（Isolation）4）持久性（Durability）+技术的复杂性9？历史上的几种类型的数据库：1层次型2网状型3关系型关系数据库，是建立在关系数据库模型基础上的数据库，借助于关系代数等概念和方法来处理数据库中的数据，同时也是一个被组织成一组拥有正式描述性的表格，该形式的表格作用的实质是装载着数据项的特殊收集体，这些表格中的数据能以许多不同的方式被存取或重新召集而不需要重新组织数据库表格。10？://blog.163.com/li_hx/1.3WhydoweneedtolearnMySQL/PostgreSQL？趋势1：云计算，淘汰大部分的运维人员趋势2：云数据库，淘汰大部分以商业数据库为职业的DBA趋势3：电商等兴起，对开源数据库技术的人员需求增多趋势4：去IOE化等运动背后的商业成本和安全问题，促进开源产业发展趋势5：MySQL和PostgreSQL为代表的开源数据库自身正蓬勃发展中://blog.163.com/li_hx/1.4WhydoweneedtomasterQueryOptimizationTechnology？名师录：数据库查询优化技术一直是DBMS实现技术中的精华，也是难点和重点。王珊中国人民大学信息学院教授/博士生导师13？从数据处理的角度看：数据库的OLAP型应用要多于OLTP型应用。大数据技术目前处理了一部分非结构化的数据，但分布式数据库技术的发展，将使得数据库技术进一步发展甚至出现革命性的进步进而与现行的hadoop/spark等技术争夺数据分析的阵地。而查询优化技术因此二者得益继续发展。从当下技术发展的趋势看：大数据背景下，盛行几十年的SQL查询技术焕发了新的生机，不仅在数据库中占有半壁江山，而且在大数据处理技术下，各种SQL查询接口/功能层异军突起。而查询优化技术因具有普适性得以继续使用。14？数学中，关系代数是支持叫做逆反(converse)的对合一元运算的剩余布尔代数。激发关系代数的例子是在集合X上的所有二元关系的代数，带有R·S被解释为平常的二元关系复合。关系代数的早期形式形成于十九世纪德·摩根、皮尔士和ErnstSchröder的工作。它今日的纯等式形式是阿尔弗雷德·塔斯基和他的学生在1940年代开发的。15？数据库中，关系代数是一阶逻辑的分支，是闭合于运算下的关系的集合。运算作用于一个或多个关系上来生成一个关系。关系代数是计算机科学的一部分。在纯数学中的关系代数是有关于数理逻辑和集合论的代数结构。SQL的查询语言松散的基于了关系代数，尽管SQL中的操作数（表）不完全是关系，很多有用的关于关系代数的理论在SQL对应者中不成立。16？Codd的关系代数的六个原始运算是“选择”、“投影”、笛卡尔积（也叫做“叉积”或“交叉连接”）、并集、差集和“重命名”。（实际上，Codd忽略了重命名，而ISBL的发明者显著的包括了它）。这六个运算在省略其中任何一个都要损失表达能力的意义上是基本的。已经依据这六个原始运算定义了很多其他运算。其中最重要的是交集、除法和自然连接。事实上ISBL显著的用自然连接替代了笛卡尔积，它是笛卡尔积的退化情况。第一个基于Codd的代数的查询语言是ISBL，许多作者都认同这个先驱的工作展示了一个使Codd的想法成为有用语言的方式1970年E.F.Codd发表了数据的关系模型论文。Codd提议这样一种代数作为数据库查询语言的基础，称为关系代数。17？关系代数是一种抽象的查询语言，用对关系的运算来表达查询，作为研究关系数据语言的数学工具。关系代数的运算对象是关系，运算结果亦为关系。关系代数用到的运算符包括四类：1）集合运算符2）专门的关系运算符3）算术比较符4）逻辑运算符比较运算符和逻辑运算符是用来辅助专门的关系运算符进行操作的，所以按照运算符的不同，主要将关系代数分为传统的集合运算和专门的关系运算两类。18？摘自：《数据库查询优化器的艺术：原理解析与SQL性能的艺术》19？1.Queryreuse①Thequeryresultreuse②Thequeryplanreuse2.TheRuleOfQueryRewrite①BasedonRelationalAlgebraandHeuristicRule②ViewRewrite、Sub-queryOptimization、EquivalentPredicateRewrite、ConditionSimplification、OuterJoinElimination、JoinElimination、NestJoinElimination③SematicOptimization3.TheAlgorithmOfQueryOptimization①SingleTableScanAlgorithm②TwoTableJoinAlgorithm③Multi-tableJoinAlgorithm4.ParallelQueryOptimization5.DistributeQueryOptimization1920？1.Queryreuse------MySQL5.6.10NotSupport①Thequeryresultreuse②Thequeryplanreuse2.TheRuleOfQueryRewrite①BasedonRelationalAlgebraandHeuristicRule②ViewRewrite、Sub-queryOptimization、EquivalentPredicateRewrite、ConditionSimplification、OuterJoinElimination、JoinElimination、NestJoinElimination③SematicOptimization3.TheAlgorithmOfQueryOptimization①SingleTableSc