mysql选择投影连接_learning-note/MySQL查询优化.md at bad4485d71c380fa63d2addd581e60af564c41ed · kaysanshi/learn...

MySQL查询优化

重要的概念：

域：是一组具有相同数据类型的值得集合，例如：实数，整数，介于某个范围的数

关系：关系代数的对象是关系，单一的数据结构,D1XD2X...Dn的子集叫做在域D1,D2,Dn上的关系，可以表示为 R(D1,D2,...Dn) R是关系名，n:关系的目或度

关系的表示：关系是一个二维表，表的每行是对应的一个元组，表中的每一列对应的是一个域，

关系的性质：

列是同质的(homogeneous)

不同的列可出自同一个域

列的顺序无所谓，可以任意交换

任意两个元组的候选码不能相同

行的顺序无所谓，行的次序可以任意交换

分量必须取原子值

属性：关系中不同列可以对应相同的域，为了加以区分，必须给每列起一个名字，称为属性(attribute)n目关系必须有n个属性

码：

候选码：关系中的某一属性组的值能唯一的标识一额元组，简单的情况：候选码只包含一个属性

全码：关系模式中的所有的属性组是这个关系模式的候选码，

主码：如一个关系中有多个候选码则选定其中一个为主码，

主属性：候选码的属性称为主属性，

非主属性：不包含在任何候选码中的属性称为非主属性，

外码：设F是基本关系R的一个属性或一组属性，但不是关系R的码。如果F与基本关系S的主码Ks对应则F是R的外码 R是参照关系，S是被参照关系或者是目标关系

关系R和S不一定是不同的关系

目标关系S的主码Ks和参照关系的外码F必须定义在一个域上

外码不一定要与相应的主码同名

投影：可以选择性的显示关系的某些属性

笛卡尔积：给定一组域，允许某些域是相同的,

所有域的所有取值是一个组合，

不能重复

D1,D2,...Dn的笛卡尔积为： D1*D2*..Dn={(d1,d2,....dn)| di属于Di,i=1,2,...n}

元组：笛卡尔积中的每一个元素(d1,d2,d3...dn) 叫做一个元组，(A,B,C) 括号包括起来

分量：笛卡尔积中每一个元素的值如：d1:是一个值。 d2也是，则这些叫做分量，即：括号内每一个元素，

基数：若Di(i=1,2,3,..n)为有限集，其基数为Mi(i=1,2,...n)

笛卡尔积表示方法：可以表示为一张二维表，表中的行对应一个元组，表中的每一列对应一个域

例如：A={A1,A2} B={B1,B2} C={C1,C2,C3}

A1XB2XC3={(A1,B1,C1),(A1,B1,C2),(A1,B1,C3),(A1,B2,C1),(A1,B2,C2),(A1,B2,C3),(A2,B1,C1),(A2,B1,C2),(A2,B1,C3),(A2,B2,C1),(A2,B2,C2),(A2,B2,C3)}看似和排列组合一样

则基数为2X2X3=12

同样可以转换为一张二维表

选择：

并集，差集：

Select A.*,B.* // "投影"

From A,B // 笛卡尔积

Where A.c1=1 and A.c2=B.c2 or B.c1>100 // "选择"

UNION // 并集，差集

Select A.*,B.*

From A,B

Where A.c1=1 and A.c2=B.c2 or B.c1>100

常见的关系操作

查询的操作：选择，投影，连接，除，并，差，交，笛卡尔积，(选择，投影，并，差，笛卡尔积)基本操作

数据更新：插入，删除，修改

关系操作的特点：

集合操作方式：操作对象和结果都是集合，一次一集合的方式

完整性检查：

实体完整性：

实体完整性是针对基本关系而言，一个基本表通常对应现实世界的一个实体集，

实体可以区分具有唯一标识

关系模型以主码为唯一标识

主码中的属性不可以为空值

参照完整性

若属性(或属性组)F是基本关系R额外码他与基本关系S的主码Ks相对应(基本关系R和S不一定是不同的关系)则对应R中的每个元组在F上必须为：空值(F上的属性均为空值)，或者等于S中的某个元组的主码值

用户定义完整性

针对某一具体关系数据库的约束条件，反应某一具体应用所涉及的数据必须满足的语义要求

关系模型提供定义检验这类的完整性的机制，以便于用户统一系统的方法处理他们，而不需由应用程序承担，

数据库调优

数据库调优可以使得数据库应用运行的更快，目标是：使得数据库有更高的吞吐量，更短的相应时间。被调优的对象是整个数据库管理系统总体。查询语句调优：调优的对象是一条语句。

调优有以下方式：

人工调优

主要依赖于人，效率低下，要求操作者完全理解常识所依赖的原理，还需对应用，数据库管理系统，操作系统，以及硬件有广泛的理解，最体现维护人员的价值。

基于案例的调优

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

自调优

为数据库系统建立一个模型，根据“影响数据库系统性能效率的因素”，数据库系统自动进行参数的配置。一些商业数据库，实现了部分自调优技术

数据库调优五个阶段的主要技术

1.应用情况的估算：

应用的使用情况(把业务逻辑转换为数据库的读写分布逻辑，以是读多写少还是读写均衡来换分OLTP和OLAP，应用数据库的并发情况，并发是否可以池化)，数据量，对数据库的压力，峰值压力等做一个预估。

2.系统选型策略：

确定什么样的数据库可以适用应用场景，并确定数据库是使用的开源数据库还是商业数据库，是使用集群还是单机系统，同时对操作系统，中间件，硬件，网络等选型。

3.数据模型设计：主要根据业务逻辑，从几个角度考虑表的逻辑结构：

E-R模型设计：遵循E-R模型设计原理，偶尔的适当程度的非规范化可以改善系统查询性能。

数据逻辑分布策略:目的减少数据请求的不必要的数据量，把用户需要的数据返回，可用的技术如分区，用E-R模型分表等(如互联网企业典型的用法，根据业务的不同，进行分库，分表操作)

数据物理存储策略:目的减少IO，如启用压缩技术，把索引和表数据的存储分开，不同的表的数据分布不同的表空间，不同表空间分布在不同的物理存储上(尤其是读写量大的表空间分布在不同的物理存储上)，日志，索引和数据分布在不同的物理存储上

索引：在查询频繁的对象上建立索引，使用索引的正效应大于负效应(索引的维护存在消耗)

4.SQL设计

编写正确的，查询效率高的SQL语句，这依据的主要是“查询重写规则”,编写语句的过程要注意保障SQL能利用到索引

5.数据库功能的启用:数据库为提高性能的一些功能，可合理使用，具体如下：

查询重用：根据实际情况，进行配置，可缓存查询执行计划，查询结果等，

数据库参数的设置：可设置合适的参数如数据缓冲区等，模型系统预运行，在备用系统上模拟实际运行环境，加大压力进行系统测试，提前发现问题。

6.系统监控与分析，在工业环境中加强对系统的运行监控和日志的分析工作

应用系统表现：收集用户的信息，系统存在的问题，这都是用户第一时间发现的。

OS环境监控：实现监控CPU,内存，IO等，并对比实时情况与历史正常情况。

数据库内部状况监控：一些数据库提供系统表，视图，工具等手段，向用户提供数据库运行过程中的内部信息，如锁的情况，这些都是需要实时的监控，并对比实时情况与历史正常情况。

日志分析：在数据库的日志，操作系统的日志中找出异常事件，定位问题。

数据库查询优化概述

数据库查询优化技术主要包括查询重用技术，查询重写技术，查询算法优化技术，并行查询优化技术，分布式查询优化技术，这6项构成“广义数据库查询优化”

查询重写技术，查询算法优化技术可称为“狭义数据库查询优化”

可分为代数优化(逻辑优化)或非代数优化(物理优化)

逻辑优化：主要依据关系代数的等价变化做一些逻辑变换 如：“查询重写规则”

物理优化：主要根据数据读取，表连接，表连接顺序，等技术。如