mysql子查询sysdate_《MySQL技术内幕-SQL编程》笔记

一. SQL编程

1.1 OLTP & OLAP

OLTP Online Transaction Processing

面向应用的、基本的、日常的事务处理，如支付交易

实时系统

OLAP Online Analysis Processing

支持复杂的分析操作，决策支持等，如数据分析

二. 数据类型

2.1 类型属性

UNSIGNED 数字类型无符号化

例如INT的类型的范围是:(-2^31) ~ (2^31-1),INT的UNSIGNED的范围就是0 ~ 2^32

因为是无符号类型,当结果是负数时,数据结果变成第一位不是符号如:1-2=-1,二进制表示为0xFFFFFFF,因为是无符号,所以他解析成数字就是4294967295

尽量不适用UNSIGNED,因为底层容易将负数结果的二进制第一位不解析成符号

2.2 日期和时间类型

类型

所占空间

样例

说明

DATETIME

8字节

2018-04-13 09:00:00

1000-01-01 00:00:00 到 9999-12-31 23:59:59

DATE

3字节

2018-04-13

TIMESTAMP

4字节

2018-04-13 09:00:00

1970-01-01 00:00:00 到 2038-01-19 03:14:07

YEAR

1字节

2018

TIME

3字节

09:00:00

TIME可以表示范围为:-838:59:59 ~ 838:59:59 可以表示一天的时间也可以表示时间间隔

日期函数:NOW, CURRENT_TIMESTAMP和SYSDATE

mysql> select NOW(), current_timestamp(),sysdate(),sleep(2),now(),current_timestamp(),sysdate()

-> \G

*************************** 1. row ***************************

NOW(): 2018-04-13 01:37:01

current_timestamp(): 2018-04-13 01:37:01

sysdate(): 2018-04-13 01:37:01

sleep(2): 0

now(): 2018-04-13 01:37:01

current_timestamp(): 2018-04-13 01:37:01

sysdate(): 2018-04-13 01:37:03

1 row in set (2.00 sec)

NOW和CURRENT_TIMESTAMP是同义词,NOW获取的sql执行的时间,SYSDATE获取的函数执行的时间

三. 查询处理

3.1 逻辑查询处理

Mysql的sql执行顺序:

(7) SELECT

(8) DISTINCT

(1) FROM

(3) JOIN

(2) ON

(4) WHERE

(5) GROUP BY

(6) HAVING

(9) ORDER BY

(10) LIMIT

3.2 物理查询处理

物理查询会根据索引来进行优化

四. 子查询

4.1 子查询

子查询是指一个SELECT语句中嵌套另一个SELECT语句

++子查询的好处是++：

子查询允许结构化查询，这样可以把一个查询语句的每个部分隔开

子查询提供了另一种方法来执行有些需要复杂的JOIN和UNION来实现操作

子查询可读性较高

4.2 独立子查询

按照期望值的数量，可以分为标量子查询(就是返回单个值或者NULL值)和多值子查询

MYSQL优化器对于IN语句的优化是“LAZY”,对于IN子句，如果不是显示的列表定义，如IN('a','b','c')，那么IN子句都会转换为EXISTS的相关子查询，如

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2)

优化器会重写为如下的相关子查询：

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a)

4.3 相关子查询

相关子查询是指引用了外部查询列的子查询，即子查询会对外部查询的每行进行一次计算。可以通过派生表的方式来实现相关子查询(join)，减少逻辑查询的次数

4.4 派生表

派生表又被称为表子查询，与其他表一样出现在FROM子句中，但是是从子查询派生出来的虚拟表中产生的

五. 联接与集合操作

5.1 连接查询

1) CROSS JOIN

CROSS JOIN对两个表进行笛卡尔积，返回两个表所有列的组合，如左表有m行数据，右表有n行数据，那么CROSS JOIN后将返回m*n

2) INNER JOIN

INNER JOIN首先产生笛卡尔儿积的虚拟表，再按照ON过滤条件进行数据的匹配操作。

INNER JOIN不添加外部行，这是和OUTER JOIN最大区别之一，所以过滤条件在ON和WHERE中是没有区别的

MYSQL INNER JOIN后不跟ON子句，就等于CROSS JOIN产生笛卡尔积(MYSQL中，INNER JOIN和CROSS JOIN是同义词)

3) OUTER JOIN

CROSS JOIN对两个表产生笛卡尔积，应用ON过滤条件，添加外部行

4) NATURAL JOIN

等同于INNER JOIN使用USING组合

5.2 多表查询

对于INNER JOIN的多表联接查询，可以随意安排表的顺序而不会影响查询结果，所以，优化器会自动根据成本评估出访问表的顺序。

理由OUTER JOIN的多表联接之间的顺序关系可能会影响最后产生的结果。

5.3 联接算法

一些前置概念：

驱动表/外部表

指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]

未指定联接条件时，行数少的表为[驱动表](Important!)

忠告：如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断

Join Type:

all: 这便是所谓的“全表扫描”

index: 这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。

range: 指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。

ref: 查找条件列使用了索引而且不为主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。

这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描

但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描

const: 通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量

1) Simple Nested-Loops Join算法

从外部表中每次读取一条记录，然后将记录与内部表中的记录进行比较(all/index/range/ref),伪代码如下

for each row r in R do

For each row s in S do

If r and s satisfy the join condition

Then output the tuple

内部表的选择：

优化器一般情况下总是选择将联接列含有索引的表作为内部报

如果两张表的连接列都有索引，并且索引高度相同，那么优化器会选择将记录少的表作为外部表。(因为内部表的扫描次数总是索引高度，与记录数无关)

pushed-down conditions优化

对于内部表的过滤条件，SQL优化器可能会将判断放在外部表中去，能过将外部表过滤掉相当多的一部分数据。

在INNER JOIN中可以使用pushed-down conditions的优化方式，但是不能直接在OUTER JOIN中使用该方式，因为有写不满足联接条件的记录会通过外部表行的方式再次添加到结果中。

2) Block Nested-Loops Join算法

Block Nested-Loops Join算法就是针对没有索引的联接情况设计的，器使用Join Buffer(联接缓存)来减少内部表的循环读取次数。

如，Block Nested-Loops Join算法先对外部表每次读取10行记录(需要进行联接的)放入到Join Buffer中，然后在内部表中直接匹配这10行数据。

MySQL使用Join Buffer的原则如下：

系统变量join_buffer_size决定了Join Buffer的大小

Join Buffer可被用于联接是ALL、index和range的类型

每次联接使用一个Join Buffer，因此多表的联接可以使用多个Join Buffer

Join Buffer在联接发生之前进行分配，在SQL语句执行完后进行释放

Join Buffer只存储需要进行查询操作的相关列的数据，而不是整行的记录

加快JOIN的执行速度，有如下两种方式：

加快每次search-for-match操作的速度(索引)

search-for-match根据group来进行，减少内部表的访问次数(Block Nested-Loops Join)

3) Batched Key Access Join算法

可以理解为group-index-lookup(组索引查询)

5.4 集合操作

联接操作是表之间的水平操作，因为该操作生成的虚拟表包含两个表的列；集合操作，一般视为垂直操作：

UNION ALL

UNION DISTINCT

六. 聚合和旋转操作

6.1 聚合函数

MySQL支持的聚合操作：

AVG()

BIT_AND() 位运算，与

BIT_OR() 位运算，或

BIT_XOR() 位运算，异或

COUNT()

GROUP_CONCAT() 返回由属于一组的列值连接组合而成的结果

MAX()

MIN()

STD() 返回给定表达式中所有值的标准差

STDDEV_POP()

STDDEV_SAMP()

STDDEV() 返回给定表达式中所有值的标准差

SUM()

VAR_POP()

VAR_SAMP()

VARIANCE() 返回给定表达式中所有值的方差

MySQL仅支持流聚合，流聚依赖于获得的存储再GROUP BY列中的数据，流聚合会先根据GROUP BY对行进行排序

七. 游标

游标是一种面向过程的SQL编程方法，与前面面向集合的方法处理关系数据库不同。

游标可以在存储过程和函数以及触发器和时间中使用

游标有三个属性：

Asensitive，数据库也可以选择不复制结果集

Read Only，不可更新

Nonscrollable，游标只能向一个方向前进，不可以跳过任何一行

游标的开销主要是因为需要对每行进行处理，处理过程越复杂，游标的效率越低

八. 事务编程

事务能保证数据库从一种一致状态转换到另一种一致状态.

8.1 事务概述

事务的严谨标准(ACID)：

A(atomicity)，原子性，原子性是整个数据库事务不可分割的工作单位。

C(consistency)，一致性，数据库从一种一致状态转换到另一种一致状态

I(isolation),隔离性，或并发控制(concurrency control)、可串行化(serializability)、锁(locking)

D(durability)，持久化，事务一旦提交，其结果是永久的。

8.2 事务分类

1) 扁平事务

实际生产环境中最频繁的一种事务，所有操作都处于同一层次，要么都执行，要么都回滚

2) 带有保存点的扁平事务

除了支持扁平事务外，允许在事务执行的过程中回滚到同一事务中的焦躁一个状态(保存点)

当系统发生奔溃时，所有的保存点都将消失，因为其保存点是易失的，而非持久的。

3) 链事务

提交一个事务时，释放不需要的数据对象，将必要的处理上下文隐式的传给下一个要开始的事务。

链式事务只能回滚仅限于当前事务，即只能恢复到最近一个保存点

4) 嵌套事务

嵌套事务时一个层次结构框架，有一个顶层事务控制着个个层次的事务

5) 分布式事务

通常是一个在分布式环境下运行的扁平事务

8.3 事务的隔离级别

ANSI SQL四个隔离级别：

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

事务的并发问题：

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

事务隔离级别

脏读

不可重复读

幻读

读未提交(read-uncommitted)

是

是

是

不可重复读(read-committed)

否

是

是

可重复读(repeatable-read)

否

否

是

串行化(serializable)

否

否

否

1、SQL规范所规定的标准，不同的数据库具体的实现可能会有些差异

2、mysql中默认事务隔离级别是可重复读时并不会锁住读取到的行

3、事务隔离级别为读提交时，写数据只会锁住相应的行

4、事务隔离级别为可重复读时，如果有索引(包括主键索引)的时候，以索引列为条件更新数据，会存在间隙锁间隙锁、行锁、下一键锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表。

5、事务隔离级别为串行化时，读写数据都会锁住整张表

6、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，鱼和熊掌不可兼得啊。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

九. 索引

9.1 B+树

9.2 B+树索引

9.3 Cardinality

如果某个字段的取值范围很广，几乎没有重复，即是高选择性的，那么此时使用B+树索引是最合适的。

数据库对于Cardinality统计都是通过采样的方法来完成的

9.4 B+树索引的使用

9.5 Index Condition Pushdown 索引条件下推

ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的where条件(pushed index condition，推送的索引条件)，如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录

ICP(优化器)尽可能的把index condition的处理从server层下推到storage engine层

优化器没有使用ICP时，数据访问和提取的过程如下：

1) 当storage engine读取下一行时，首先读取索引元组(index tuple)，然后使用索引元组在基表中(base table)定位和读取整行数据。

2) sever层评估where条件，如果该行数据满足where条件则使用，否则丢弃。

3) 执行1 ，直到最后一行数据。

index-access-2phases.png

优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层

1) storage engine从索引中读取下一条索引元组。

2) storage engine使用索引元组评估下推的索引条件。如果没有满足wehere条件，storage engine将会处理下一条索引元组(回到上一步)。只有当索引元组满足下推的索引条件的时候，才会继续去基表中读取数据

3) 如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。

4) server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃。

index-access-with-icp.png