‘壹’ sql执行顺序
查询语句中select from where group by having order by的执行顺序
1.查询中用到的关键词主要包含六个，并且他们的顺序依次为
select--from--where--group by--having--order by
其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序
与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行
from--where--group by--having--select--order by,
from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列，或列的计算结果
order by :按照什么样的顺序来查看返回的数据
2.from后面的表关联，是自右向左解析的
而where条件的解析顺序是自下而上的。
也就是说，在写SQL文的时候，尽量把数据量大的表放在最右边来进行关联，
而把能筛选出大量数据的条件放在where语句的最下面。
SQL Select语句完整的 执行顺序 【从DBMS使用者角度】：
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序 。
from 子句--执行顺序为从后往前、从右到左
表名(最后面的那个表名为驱动表，执行顺序为从后往前, 所以数据量较少的表尽量放后）
oracle 的解析器按照从右到左的顺序处理，FROM 子句中的表名，FROM 子句中写在最后的表(基础表 driving
table)将被最先处理，即最后的表为驱动表，在FROM 子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3
个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指被其他表所引用的表
多表连接时，使用表的别名并把别名前缀于每个Column上。可以减少解析的时间并减少那些由Column 歧义引起的语法错误.
where子句--执行顺序为自下而上、从右到左
ORACLE 采用自下而上从右到左的顺序解析Where 子句,根据这个原理,表之间的连接必须写在其他Where 条件之前, 可以过滤掉最大数量记录的条件必须写在Where 子句的末尾。
group by--执行顺序从左往右分组
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。即在GROUP BY前使用WHERE来过虑，而尽量避免GROUP BY后再HAVING过滤。
having 子句----很耗资源，尽量少用
避免使用HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作.
如果能通过Where 子句在GROUP BY前限制记录的数目,那就能减少这方面的开销.
(非oracle 中)on、where、having 这三个都可以加条件的子句中，on 是最先执行，where 次之，having 最后，因为on 是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，
where 也应该比having 快点的，因为它过滤数据后才进行sum，在两个表联接时才用on 的，所以在一个表的时候，就剩下where 跟having比较了。
在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where 可以使用rushmore 技术，而having 就不能，在速度上后者要慢。
如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，where 的作用时间是在计算之前就完成的，而having 就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。
在多表联接查询时，on 比where 更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where 进行过滤，然后再计算，计算完后再由having 进行过滤。
由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。
select子句--少用*号，尽量取字段名称 。
ORACLE 在解析的过程中, 会将依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 使用列名意味着将减少消耗时间。
sql 语句用大写的；因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行
order by子句--执行顺序为从左到右排序,很耗资源
‘贰’ Sql语句解析过程
为了将用户写的SQL文本转化为Oracle认识的且可执行的语句 这个过程就叫做解析过程 解析分为硬解析和软解析 一条SQL语句在第一次被执行时必须进行硬解析
当客户端发出一条SQL语句（也可以是一个存储过程或者一个匿名PL/SQL块）进入shared pool时（注意 我们从前面已经知道 Oracle对这些SQL不叫做SQL语句 而是称为游标 因为Oracle在处理SQL时 需要很多相关的辅助信息 这些辅助信息与SQL语句一起组成了游标） Oracle首先将SQL文本转化为ASCII值 然后根据hash函数计算其对应的hash值（hash_value） 根据计算出的hash值到library cache中找到对应的bucket 然后比较bucket里是否存在该SQL语句
如果不存在 则需要按照我们前面所描述的 获得shared pool latch 然后在shared pool中的可用chunk链表（也就是bucket）上找到一个可用的chunk 之后释放shared pool latch 在获得了chunk以后 这块chunk就可以认为是进入了library cache 接下来 进行硬解析过程 硬解析包括以下几个步骤
对SQL语句进行文法检查 看是否有文法错误 比如没有写from select拼写错误等 如果存在文法错误 则退出解析过程
到数据字典里校验SQL语句涉及的对象和列是否都存在 如果不存在 则退出解析过程 这个过程会加载dictionary cache
将对象进行名称转换 比如将同名词翻译成实际的对象等 比如select * from t中 t是一个同名词 指向hr t 于是Oracle将t转换为hr t 如果转换失败 则退出解析过程
检查发出SQL语句的用户是否具有访问SQL语句里所引用的对象的权限 如果没有权限 则退出解析过程
通过优化器创建一个最优的执行计划 这个过程会根据数据字典里记录的对象的统计信息 来计算最优的执行计划 这一步牵涉大量数学运算 是最消耗CPU资源的
将该游标所产生的执行计划 SQL文本等装载进library cache的heap中
在硬解析的过程中 进程会一直持有library cache latch 直到硬解析结束为止 硬解析结束以后 会为SQL语句产生两个游标 一个是父游标 另一个是子游标 父游标里主要包含两种信息 SQL文本以及优化目标（optimizer goal） 父游标在第一次打开时被锁定 直到其他所有的session都关闭该游标后才被解锁 当父游标被锁定的时候是不能被交换出library cache的 只有在解锁以后才能被交换出library cache 父游标被交换出内存时 父游标对应的所有子游标也被交换出library cache 子游标包括游标所有的信息 比如具体的执行计划 绑定变量等 子游标随时可以被交换出library cache 当子游标被交换出library cache时 Oracle可以利用父游标的信息重新构建出一个子游标来 这个过程叫reload 可以使用下面的方式来确定reload的比率
select *sum(reloads)/sum(pins) Reload_Ratio from v$librarycache;
一个父游标可以对应多个子游标 子游标具体的个数可以从视图v$sqlarea的version_count字段体现出来 而每个具体的子游标则全都在视图v$sql里体现 当具体绑定变量的值与上次绑定变量的值有较大差异（比如上次执行的绑定变量值的长度是 位 而这次执行绑定变量的值的长度是 位）时或者当SQL语句完全相同 但是所引用的表属于不同的用户时 都会创建一个新的子游标
如果在bucket中找到了该SQL语句 则说明该SQL语句以前运行过 于是进行软解析 软解析是相对于硬解析而言的 如果解析过程中 可以从硬解析的步骤中去掉一个或多个的话 这样的解析就是软解析 软解析分为以下三种类型
第一种是某个session发出的SQL语句与library? cache里其他session发出的SQL语句一致 这时 该解析过程中可以去掉硬解析中的 和 但是仍然要进行硬解析过程中的 也就是表名和列名检查 名称转换和权限检查
* 第二种是某个session发出的SQL语句是该session之前发出的曾经执行过的SQL语句 这时 该解析过程中可以去掉硬解析中的 和 这四步 但是仍然要进行权限检查 因为可能通过grant改变了该session用户的权限
* 第三种是当设置了初始化参数session_cached_cursors时 当某个session第三次执行相同的SQL时 则会把该SQL语句的游标信息转移到该session的PGA里 这样 该session以后再执行相同的SQL语句时 会直接从PGA里取出执行计划 从而跳过硬解析的所有步骤 这种情况下 是最高效的解析方式 但是会消耗很大的内存
我们举一个例子来说明解析SQL语句的过程 在该测试中 绑定变量名称相同 但是变量类型不同时 所出现的解析情况 如下所示
首先 执行下面的命令 清空shared pool里所有的SQL语句
SQL> alter system flush shared_pool;
然后 定义一个数值型绑定变量 并为该绑定变数赋一个数值型的值以后 执行具体的查询语句
SQL> variable v_obj_id number;
SQL> exec :v_obj_id := ;
SQL> select object_id object_name from sharedpool_test
where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
AGGXMLIMP
接下来 定义一个字符型的绑定变量 变量名与前面相同 为该绑定变数赋一个字符型的值以后 执行相同的查询
SQL> variable v_obj_id varchar ( );
SQL> exec :v_obj_id := ;
SQL> select object_id object_name from sharedpool_test
where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
AGGXMLIMP
然后我们到视图v$sqlarea里找到该SQL的父游标的信息 并到视图v$sql里找该SQL的所有子游标的信息
SQL> select sql_text version_count from v$sqlarea where
sql_text like %sharedpool_test% ;
SQL_TEXT
VERSION_COUNT
select object_id object_name from sharedpool_test where
object_id=:v_obj_id
SQL> select sql_text child_address address from v$sql
where sql_text like %sharedpool_test% ;
SQL_TEXT
CHILD_ADDRESS ADDRESS
select object_id object_name from sharedpool_test where
object_id=:v_obj_id F
B D
select object_id object_name from sharedpool_test where
object_id=:v_obj_id FC
B D
从记录父游标的视图v$sqlarea的version_count列可以看到 该SQL语句有 个子游标 而从记录子游标的视图v$sql里可以看到 该SQL文本确实有两条记录 而且它们的SQL文本所处的地址（ADDRESS列）也是一样的 但是子地址（CHILD_ADDRESS）却不一样 这里的子地址实际就是子游标所对应的heap 的句柄
lishixin/Article/program/Oracle/201311/18653
‘叁’ 使用SQL语句创建存储过程
使用SQL语句创建存储的具体过程如下：
1、首先，打开企业管理器，选择【工具】-【查询分析器】：
‘肆’ SQL执行与优化
SQL优化
执行计划,表关联查询顺序,优化策略与思路
下面再向前走一些，容我根据自己的认识说一下查询执行的流程是怎样的：
1.连接
1.1客户端发起一条Query请求，监听客户端的‘连接管理模块’接收请求
1.2将请求转发到‘连接进/线程模块’
1.3调用‘用户模块’来进行授权检查
1.4通过检查后，‘连接进/线程模块’从‘线程连接池’中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求
2.处理
2.1先查询缓存，检查Query语句是否完全匹配，接着再检查是否具有权限，都成功则直接取数据返回
2.2上一步有失败则转交给‘命令解析器’，经过词法分析，语法分析后生成解析树
2.3接下来是预处理阶段，处理解析器无法解决的语义，检查权限等，生成新的解析树
2.4再转交给对应的模块处理
2.5如果是SELECT查询还会经由‘查询优化器’做大量的优化，生成执行计划
2.6模块收到请求后，通过‘访问控制模块’检查所连接的用户是否有访问目标表和目标字段的权限
2.7有则调用‘表管理模块’，先是查看table cache中是否存在，有则直接对应的表和获取锁，否则重新打开表文件
2.8根据表的meta数据，获取表的存储引擎类型等信息，通过接口调用对应的存储引擎处理
2.9上述过程中产生数据变化的时候，若打开日志功能，则会记录到相应二进制日志文件中
3.结果
3.1Query请求完成后，将结果集返回给‘连接进/线程模块’
3.2返回的也可以是相应的状态标识，如成功或失败等
3.3‘连接进/线程模块’进行后续的清理工作，并继续等待请求或断开与客户端的连接
接下来再走一步，让我们看看一条SQL语句的前世今生。
首先看一下示例语句
示例语句
执行顺序
SQL解析
1. FROM
当涉及多个表的时候，左边表的输出会作为右边表的输入，之后会生成一个虚拟表VT1。
(1-J1)笛卡尔积
计算两个相关联表的笛卡尔积(CROSS JOIN) ，生成虚拟表VT1-J1。
两次全表扫描
哈希索引,查找复杂度都是 O(1) 。
2. WHERE
对VT1过程中生成的临时表进行过滤，满足WHERE子句的列被插入到VT2表中。
注意：
此时因为分组，不能使用聚合运算；也不能使用SELECT中创建的别名；
与ON的区别：
如果有外部列，ON针对过滤的是关联表，主表（保留表）会返回所有的列；
如果没有添加外部列，两者的效果是一样的；
应用：
对主表的过滤应该放在WHERE；
对于关联表，先条件查询后连接则用ON，先连接后条件查询则用WHERE；
hash join 哈希连接 驱动表和被驱动表都只会访问0次或1次
应用场景:一个大表一个小表/表上没有索引/返回结果集比较大
3. GROUP BY
这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。
注意：
其后处理过程的语句，如SELECT,HAVING，所用到的列必须包含在GROUP BY中，对于没有出现的，得用聚合函数；
原因：
GROUP BY改变了对表的引用，将其转换为新的引用方式，能够对其进行下一级逻辑操作的列会减少；
原作者的理解是：
根据分组字段，将具有相同分组字段的记录归并成一条记录，因为每一个分组只能返回一条记录，除非是被过滤掉了，而不在分组字段里面的字段可能会有多个值，多个值是无法放进一条记录的，所以必须通过聚合函数将这些具有多值的列转换成单值；
GROUP BY 重新聚合查询
4. HAVING
这个子句对VT3表中的不同的组进行过滤，只作用于分组后的数据，满足HAVING条件的子句被加入到VT4表中。
7.LIMIT
LIMIT子句从上一步得到的VT6虚拟表中选出从指定位置开始的指定行数据。
注意：
offset和rows的正负带来的影响；
当偏移量很大时效率是很低的，可以这么做：
采用子查询的方式优化，在子查询里先从索引获取到最大id，然后倒序排，再取N行结果集
采用INNER JOIN优化，JOIN子句里也优先从索引获取ID列表，然后直接关联查询获得最终结果
当前未用到索引,
三次full scan , table1 AS a / table2 AS b / GROUP BY
尽量少做重复的工作
控制同一语句的多次执/减少多次的数据转换/
杜绝不必要的子查询和连接表，子查询在执行计划一般解释成外连接，多余的连接表带来额外的开销
关于临时表和表变量的选择
临时表产生使用SELECT INTO和CREATE TABLE + INSERT INTO的选择，一般情况下，SELECT INTO会比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO会锁定TEMPDB的系统表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以建议，在并发系统中，尽量使用CREATE TABLE + INSERT INTO，而大数据量的单个语句使用中，使用SELECT INTO。
子查询的用法
相关子查询可以用IN、NOT IN、EXISTS、NOT EXISTS引入
NOT IN、NOT EXISTS的相关子查询可以改用LEFT JOIN代替写法
如果保证子查询没有重复 ，IN、EXISTS的相关子查询可以用INNER JOIN 代替
IN``的相关子查询用EXISTS代替
不要用 COUNT (*)的子查询判断是否存在记录，最好用 LEFT` `JOIN 或者EXISTS，比如有人写这样的语句：
建立索引后，并不是每个查询都会使用索引，在使用索引的情况下，索引的使用效率也会有很大的差别。只要我们在查询语句中没有强制指定索引，
不要对索引字段进行运算，而要想办法做变换
不要对索引字段进行格式转换
不要对索引字段使用函数
不要对索引字段进行多字段连接
join关联查询的计算是很复杂的，特别是数据量比较大的情况下，实际情况还是拆解较快的
Join拆解的核心就是利用In关键字
要么用空间换时间，要么用时间换空间
多表连接的连接条件对索引的选择有着重要的意义，所以我们在写连接条件条件的时候需要特别注意。
A、多表连接的时候，连接条件必须写全，宁可重复，不要缺漏。
B、连接条件尽量使用聚集索引
C、注意ON、WHERE和HAVING部分条件的区别
ON是最先执行， WHERE次之，HAVING最后，因为ON是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，WHERE也应该比 HAVING快点的，因为它过滤数据后才进行SUM，在两个表联接时才用ON的，所以在一个表的时候，就剩下WHERE跟HAVING比较了
考虑联接优先顺序：
(1)INNER JOIN
(2)LEFT JOIN (注：RIGHT JOIN 用 LEFT JOIN 替代)
(3)CROSS JOIN
索引并不适用于所有情况：a.少量数据；b.频繁进行改动的字段，不适合做索引；c.很少使用的字段，不需要加索引
索引会提高数据查询效率，但是会降低“增、删、改”的效率。当不使用索引的时候，我们进行数据的增删改，只需要操作源表即可，但是当我们添加索引后，不仅需要修改源表，也需要再次修改索引，很麻烦。
先执行顺序, 是否走索引, 有无类型转换
18000 字的SQL优化大全
步步深入：MySQL架构总览->查询执行流程->SQL解析顺序
MySQL索引总结（4）——btree与hash区别
‘伍’ mysql 核心内容-上
1、SQL语句执行流程
MySQL大体上可分为Server层和存储引擎层两部分。
Server层：
连接器：TCP握手后服务器来验证登陆用户身份，A用户创建连接后，管理员对A用户权限修改了也不会影响到已经创建的链接权限，必须重新登陆。
查询缓存：查询后的结果存储位置，MySQL8.0版本以后已经取消，因为查询缓存失效太频繁，得不偿失。
分析器：根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法。
优化器：多种执行策略可实现目标，系统自动选择最优进行执行。
执行器：判断是否有权限，将最终任务提交到存储引擎。
存储引擎层
负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎(经常用的也是这个)。
SQL执行顺序
2、BinLog、RedoLog、UndoLog
BinLog
BinLog是记录所有数据库表结构变更（例如create、alter table）以及表数据修改(insert、update、delete)的二进制日志，主从数据库同步用到的都是BinLog文件。BinLog日志文件有三种模式。
STATEMENT 模式
内容：binlog 记录可能引起数据变更的 sql 语句
优势：该模式下，因为没有记录实际的数据，所以日志量很少 IO 都消耗很低，性能是最优的
劣势：但有些操作并不是确定的，比如 uuid() 函数会随机产生唯一标识，当依赖 binlog 回放时，该操作生成的数据与原数据必然是不同的，此时可能造成无法预料的后果。
ROW 模式
内容：在该模式下，binlog 会记录每次操作的源数据与修改后的目标数据，StreamSets就要求该模式。
优势：可以绝对精准的还原，从而保证了数据的安全与可靠，并且复制和数据恢复过程可以是并发进行的
劣势：缺点在于 binlog 体积会非常大，同时，对于修改记录多、字段长度大的操作来说，记录时性能消耗会很严重。阅读的时候也需要特殊指令来进行读取数据。
MIXED 模式
内容：是对上述STATEMENT 跟 ROW 两种模式的混合使用。
细节：对于绝大部分操作，都是使用 STATEMENT 来进行 binlog 没有记录，只有以下操作使用 ROW 来实现：表的存储引擎为 NDB，使用了uuid() 等不确定函数，使用了 insert delay 语句，使用了临时表
主从同步流程：
1、主节点必须启用二进制日志，记录任何修改了数据库数据的事件。
2、从节点开启一个线程（I/O Thread)把自己扮演成 mysql 的客户端，通过 mysql 协议，请求主节点的二进制日志文件中的事件 。
3、主节点启动一个线程（mp Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
4、从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具体哪一个二进制日志文件内部的哪一个位置（主节点中的二进制文件会有多个）。
5、从节点启动另外一个线程（sql Thread ），把 Relay log 中的事件读取出来，并在本地再执行一次。
mysql默认的复制方式是异步的，并且复制的时候是有并行复制能力的。主库把日志发送给从库后不管了，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。
全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。
半同步复制
半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。
还可以延伸到由于主从配置不一样、主库大事务、从库压力过大、网络震荡等造成主备延迟，如何避免这个问题？主备切换的时候用可靠性优先原则还是可用性优先原则？如何判断主库Crash了？互为主备的情况下如何避免主备循环复制？被删库跑路了如何正确恢复？( o )… 感觉越来越扯到DBA的活儿上去了。
RedoLog
可以先通过下面demo理解：
饭点记账可以把账单写在账本上也可以写在粉板上。有人赊账或者还账的话，一般有两种做法：
1、直接把账本翻出来，把这次赊的账加上去或者扣除掉。
2、先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。
生意忙时选后者，因为前者太麻烦了。得在密密麻麻的记录中找到这个人的赊账总额信息，找到之后再拿出算盘计算，最后再将结果写回到账本上。
同样在MySQL中如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。而粉板和账本配合的整个过程就是MySQL用到的是Write-Ahead Logging 技术，它的关键点就是先写日志，再写磁盘。此时账本 = BinLog，粉板 = RedoLog。
1、 记录更新时，InnoDB引擎就会先把记录写到RedoLog（粉板）里面，并更新内存。同时，InnoDB引擎会在空闲时将这个操作记录更新到磁盘里面。
2、 如果更新太多RedoLog处理不了的时候，需先将RedoLog部分数据写到磁盘，然后擦除RedoLog部分数据。RedoLog类似转盘。
RedoLog有write pos 跟checkpoint
write pos ：是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。
check point：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write pos和check point之间的是粉板上还空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示粉板满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。
有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。 redolog两阶段提交：为了让binlog跟redolog两份日志之间的逻辑一致。提交流程大致如下：
1 prepare阶段 --> 2 写binlog --> 3 commit
当在2之前崩溃时，重启恢复后发现没有commit，回滚。备份恢复：没有binlog 。一致
当在3之前崩溃时，重启恢复发现虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致
binlog跟redolog区别：
redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
redo log是物理日志，记录的是在某个数据页上做了什么修改；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给ID=2这一行的c字段加1。
redo log是循环写的，空间固定会用完；binlog是可以追加写入的。追加写是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
UndoLog
UndoLog 一般是逻辑日志，主要分为两种：
insert undo log
代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃
update undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除
3、MySQL中的索引
索引的常见模型有哈希表、有序数组和搜索树。
哈希表：一种以KV存储数据的结构，只适合等值查询，不适合范围查询。
有序数组：只适用于静态存储引擎，涉及到插入的时候比较麻烦。可以参考Java中的ArrayList。
搜索树：按照数据结构中的二叉树来存储数据，不过此时是N叉树(B+树)。广泛应用在存储引擎层中。
B+树比B树优势在于：
B+ 树非叶子节点存储的只是索引，可以存储的更多。B+树比B树更加矮胖，IO次数更少。
B+ 树叶子节点前后管理，更加方便范围查询。同时结果都在叶子节点，查询效率稳定。
B+树中更有利于对数据扫描，可以避免B树的回溯扫描。
索引的优点：
1、唯一索引可以保证每一行数据的唯一性
2、提高查询速度
3、加速表与表的连接
4、显着的减少查询中分组和排序的时间
5、通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
索引的缺点：
1、创建跟维护都需要耗时
2、创建索引时，需要对表加锁，在锁表的同时，可能会影响到其他的数据操作
3、 索引需要磁盘的空间进行存储，磁盘占用也很快。
4、当对表中的数据进行CRUD的时，也会触发索引的维护，而维护索引需要时间，可能会降低数据操作性能
索引设计的原则不应该：
1、索引不是越多越好。索引太多，维护索引需要时间跟空间。
2、 频繁更新的数据，不宜建索引。
3、数据量小的表没必要建立索引。
应该：
1、重复率小的列建议生成索引。因为重复数据少，索引树查询更有效率，等价基数越大越好。
2、数据具有唯一性，建议生成唯一性索引。在数据库的层面，保证数据正确性
3、频繁group by、order by的列建议生成索引。可以大幅提高分组和排序效率
4、经常用于查询条件的字段建议生成索引。通过索引查询，速度更快
索引失效的场景
1、模糊搜索：左模糊或全模糊都会导致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。
2、隐式类型转换：比如select * from t where name = xxx , name是字符串类型，但是没有加引号，所以是由MySQL隐式转换的，所以会让索引失效 3、当语句中带有or的时候：比如select * from t where name=‘sw’ or age=14
4、不符合联合索引的最左前缀匹配：(A,B,C)的联合索引，你只where了C或B或只有B,C
关于索引的知识点：
主键索引：主键索引的叶子节点存的是整行数据信息。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。主键自增是无法保证完全自增的哦，遇到唯一键冲突、事务回滚等都可能导致不连续。
唯一索引：以唯一列生成的索引，该列不允许有重复值，但允许有空值(NULL)
普通索引跟唯一索引查询性能：InnoDB的数据是按数据页为单位来读写的，默认每页16KB，因此这两种索引查询数据性能差别微乎其微。
change buffer：普通索引用在更新过程的加速，更新的字段如果在缓存中，如果是普通索引则直接更新即可。如果是唯一索引需要将所有数据读入内存来确保不违背唯一性，所以尽量用普通索引。
非主键索引：非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）
回表：先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。
覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。
联合索引：相对单列索引，组合索引是用多个列组合构建的索引，一次性最多联合16个。
最左前缀原则：对多个字段同时建立的组合索引(有顺序，ABC，ACB是完全不同的两种联合索引) 以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引。另外组合索引实际还是一个索引，并非真的创建了多个索引，只是产生的效果等价于产生多个索引。
索引下推：MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。
索引维护：B+树为了维护索引有序性涉及到页分裂跟页合并。增删数据时需考虑页空间利用率。
自增主键：一般会建立与业务无关的自增主键，不会触发叶子节点分裂。
延迟关联：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据。
InnoDB存储: * .frm文件是一份定义文件，也就是定义数据库表是一张怎么样的表。*.ibd文件则是该表的索引，数据存储文件，既该表的所有索引树，所有行记录数据都存储在该文件中。
MyISAM存储：* .frm文件是一份定义文件，也就是定义数据库表是一张怎么样的表。* .MYD文件是MyISAM存储引擎表的所有行数据的文件。* .MYI文件存放的是MyISAM存储引擎表的索引相关数据的文件。MyISAM引擎下，表数据和表索引数据是分开存储的。
MyISAM查询：在MyISAM下，主键索引和辅助键索引都属于非聚簇索引。查询不管是走主键索引，还是非主键索引，在叶子结点得到的都是目的数据的地址，还需要通过该地址，才能在数据文件中找到目的数据。
PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引
4、SQL事务隔离级别
ACID的四个特性
原子性（Atomicity）：把多个操作放到一个事务中，保证这些操作要么都成功，要么都不成功
一致性（Consistency）：理解成一串对数据进行操作的程序执行下来，不会对数据产生不好的影响，比如凭空产生，或消失
隔离性（Isolation，又称独立性）：隔离性的意思就是多个事务之间互相不干扰，即使是并发事务的情况下，他们只是两个并发执行没有交集，互不影响的东西；当然实现中，也不一定需要这么完整隔离性，即不一定需要这么的互不干扰，有时候还是允许有部分干扰的。所以MySQL可以支持4种事务隔离性
持久性（Durability）：当某个操作操作完毕了，那么结果就是这样了，并且这个操作会持久化到日志记录中
PS：ACID中C与CAP定理中C的区别
ACID的C着重强调单数据库事务操作时，要保证数据的完整和正确性，数据不会凭空消失跟增加。CAP 理论中的C指的是对一个数据多个备份的读写一致性
事务操作可能会出现的数据问题
1、脏读(dirty read)：B事务更改数据还未提交，A事务已经看到并且用了。B事务如果回滚，则A事务做错了
2、 不可重复读(non-repeatable read)：不可重复读的重点是修改: 同样的条件, 你读取过的数据, 再次读取出来发现值不一样了，只需要锁住满足条件的记录
3、 幻读(phantom read)：事务A先修改了某个表的所有纪录的状态字段为已处理，未提交；事务B也在此时新增了一条未处理的记录，并提交了；事务A随后查询记录，却发现有一条记录是未处理的造成幻读现象，幻读仅专指新插入的行。幻读会造成语义上的问题跟数据一致性问题。
4、 在可重复读RR隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在当前读下才会出现。要用间隙锁解决此问题。
在说隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。SQL标准的事务隔离级别由低到高如下： 上图从上到下的模式会导致系统的并行性能依次降低，安全性依次提高。
读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
读已提交(Oracle默认)：别人改数据的事务已经提交，我在我的事务中才能读到。
可重复读(MySQL默认)：别人改数据的事务已经提交，我在我的事务中也不去读，以此保证重复读一致性。
串行：我的事务尚未提交，别人就别想改数据。
标准跟实现：上面都是关于事务的标准，但是每一种数据库都有不同的实现，比如MySQL InnDB 默认为RR级别，但是不会出现幻读。因为当事务A更新了所有记录的某个字段，此时事务A会获得对这个表的表锁，因为事务A还没有提交，所以事务A获得的锁没有释放，此时事务B在该表插入新记录，会因为无法获得该表的锁，则导致插入操作被阻塞。只有事务A提交了事务后，释放了锁，事务B才能进行接下去的操作。所以可以说 MySQL的RR级别的隔离是已经实现解决了脏读，不可重复读和幻读的。
5、MySQL中的锁
无论是Java的并发编程还是数据库的并发操作都会涉及到锁，研发人员引入了悲观锁跟乐观锁这样一种锁的设计思想。
悲观锁：
优点：适合在写多读少的并发环境中使用，虽然无法维持非常高的性能，但是在乐观锁无法提更好的性能前提下，可以做到数据的安全性
缺点：加锁会增加系统开销，虽然能保证数据的安全，但数据处理吞吐量低，不适合在读书写少的场合下使用
乐观锁：
优点：在读多写少的并发场景下，可以避免数据库加锁的开销，提高DAO层的响应性能，很多情况下ORM工具都有带有乐观锁的实现，所以这些方法不一定需要我们人为的去实现。
缺点：在写多读少的并发场景下，即在写操作竞争激烈的情况下，会导致CAS多次重试，冲突频率过高，导致开销比悲观锁更高。
实现：数据库层面的乐观锁其实跟CAS思想类似， 通数据版本号或者时间戳也可以实现。
数据库并发场景主要有三种：
读-读：不存在任何问题，也不需要并发控制
读-写：有隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：可能存更新丢失问题，比如第一类更新丢失，第二类更新丢失
两类更新丢失问题：
第一类更新丢失：事务A的事务回滚覆盖了事务B已提交的结果 第二类更新丢失：事务A的提交覆盖了事务B已提交的结果
为了合理贯彻落实锁的思想，MySQL中引入了杂七杂八的各种锁：
锁分类
MySQL支持三种层级的锁定，分别为
表级锁定
MySQL中锁定粒度最大的一种锁，最常使用的MYISAM与INNODB都支持表级锁定。
页级锁定
是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
行级锁定
Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大行级锁不一定比表级锁要好：锁的粒度越细，代价越高，相比表级锁在表的头部直接加锁，行级锁还要扫描找到对应的行对其上锁，这样的代价其实是比较高的，所以表锁和行锁各有所长。
MyISAM中的锁
虽然MySQL支持表，页，行三级锁定，但MyISAM存储引擎只支持表锁。所以MyISAM的加锁相对比较开销低，但数据操作的并发性能相对就不高。但如果写操作都是尾插入，那还是可以支持一定程度的读写并发
从MyISAM所支持的锁中也可以看出，MyISAM是一个支持读读并发，但不支持通用读写并发，写写并发的数据库引擎，所以它更适合用于读多写少的应用场合，一般工程中也用的较少。
InnoDB中的锁
该模式下支持的锁实在是太多了，具体如下：
共享锁和排他锁 (Shared and Exclusive Locks)
意向锁（Intention Locks）
记录锁（Record Locks）
间隙锁（Gap Locks）
临键锁 （Next-Key Locks）
插入意向锁（Insert Intention Locks）
主键自增锁 (AUTO-INC Locks)
空间索引断言锁（Predicate Locks for Spatial Indexes）
举个栗子，比如行锁里的共享锁跟排它锁：lock in share modle 共享读锁：
为了确保自己查到的数据没有被其他的事务正在修改，也就是说确保查到的数据是最新的数据，并且不允许其他人来修改数据。但是自己不一定能够修改数据，因为有可能其他的事务也对这些数据使用了 in share mode 的方式上了S 锁。如果不及时的commit 或者rollback 也可能会造成大量的事务等待。
for update排它写锁:
为了让自己查到的数据确保是最新数据，并且查到后的数据只允许自己来修改的时候，需要用到for update。相当于一个 update 语句。在业务繁忙的情况下，如果事务没有及时的commit或者rollback 可能会造成其他事务长时间的等待，从而影响数据库的并发使用效率。
Gap Lock间隙锁：
1、行锁只能锁住行，如果在记录之间的间隙插入数据就无法解决了，因此MySQL引入了间隙锁(Gap Lock)。间隙锁是左右开区间。间隙锁之间不会冲突。
2、间隙锁和行锁合称NextKeyLock，每个NextKeyLock是前开后闭区间。
间隙锁加锁原则(学完忘那种)：
1、加锁的基本单位是 NextKeyLock，是前开后闭区间。
2、查找过程中访问到的对象才会加锁。
3、索引上的等值查询，给唯一索引加锁的时候，NextKeyLock退化为行锁。
4、索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，NextKeyLock退化为间隙锁。
5、唯一索引上的范围查询会访问到不满足条件的第一个值为止。
‘陆’ MySql中Sql的执行过程
如果查询缓存没有命中，那么SQL请求会进入分析器，分析器是用来分辨SQL语句的执行目的，其执行过程大致分为两步：
表1 语法分析关键字然后再通过语法规则解析，判断输入的SQL 语句是否满足MySQL语法，并且生成图5的语法树。由SQL语句生成的四个单词中，识别出两个关键字，分别是select 和from。根据MySQL的语法Select 和 from之间对应的是fields 字段，下面应该挂接username；在from后面跟随的是Tables字段，其下挂接的是userinfo。
优化器的作用是对SQL进行优化，生成最有的执行方案。如图6所示，前面提到的SQL解析器通过语法分析和语法规则生成了SQL语法树。这个语法树作为优化器的输入，而优化器（黄色的部分）包含了逻辑变换和代价优化两部分的内容。在优化完成以后会生成SQL执行计划作为整个优化过程的输出，交给执行器在存储引擎上执行。
所处的位置如上图所示，这节的重点在优化器中的逻辑变换和代价优化上。
逻辑变换也就是在关系代数基础上进行变换，其目的是为了化简，同时保证SQL变化前后的结果一致，也就是逻辑变化并不会带来结果集的变化。其主要包括以下几个方面：
这样讲概念或许有些抽象，通过图7 来看看逻辑变化如何在SQL中执行的吧。
如图7所示，从上往下共有4个步骤：
1. 针对存在的SQL语句，首先通过“否定消除”，去掉条件判断中的“NOT”。语句由原来的“or”转换成“and”，并且大于小于符号进行变号。蓝色部分为修改前的SQL，红色是修改以后的SQL。2. 等值传递，这一步很好理解分别降”t2.a=9” 和”t2.b=5”分别替换掉SQL中对应的值。3. 接下来就是常量表达式计算，将“5+7”计算得到“12”。4. 最后是常量表达式计算后的化简，将”9<=10”化简为”true”带入到最终的SQL表达式中完成优化。
代价优化是用来确定每个表，根据条件是否应用索引，应用哪个索引和确定多表连接的顺序等问题。为了完成代价优化，需要找到一个代价最小的方案。因此，优化器是通过基于代价的计算方法来决定如何执行查询的（Cost-based Optimization）。简化的过程如下：
这里将配置操作的代价分为MySQL 服务层和MySQL 引擎层，MySQL 服务层主要是定义CPU的代价，而MySQL 引擎层主要定义IO代价。MySQL 5.7 引入了两个系统表mysql.server_cost和mysql.engine_cost来分别配置这两个层的代价。如下：MySQL 服务层代价保存在表server_cost中，其具体内容如下：
由上可以看出创建临时表的代价是很高的，尤其是内部的myisam或innodb临时表。MySQL 引擎层代价保存在表engine_cost中，其具体内容如下：
目前io_block_read_cost和memory_block_read_cost默认值均为1，实际生产中建议酌情调大memory_block_read_cost，特别是对普通硬盘的场景。MySQL会根据SQL查询生成的查询计划中对应的操作从上面两张代价表中查找对应的代价值，并且进行累加形成最终执行SQL计划的代价。再将多种可能的执行计划进行比较，选取最小代价的计划执行。
当分析器生成查询计划，并且经过优化器以后，就到了执行器。执行器会选择执行计划开始执行，但在执行之前会校验请求用户是否拥有查询的权限，如果没有权限，就会返回错误信息，否则将会去调用MySQL引擎层的接口，执行对应的SQL语句并且返回结果。例如SQL：“SELECT * FROM userinfo WHERE username = 'Tom';“假设 “username“ 字段没有设置索引，就会调用存储引擎从第一条开始查，如果碰到了用户名字是” Tom“， 就将结果集返回，没有查找到就查看下一行，重复上一步的操作，直到读完整个表或者找到对应的记录。需要注意SQL语句的执行顺序并不是按照书写顺序来的，顺序的定义会在分析器中做好，一般是按照如下顺序：
如果命中的记录比较多，应用会从MySql Server一批批获取数据
本文从MySQL中SQL语句的执行过程作为切入点，首先介绍了查询请求的执行流程，其中将MySQL的处理分为MySQL Server层和MySQL存储引擎层。通过介绍SQL语句的流转，引出了后面要介绍的5大组件，他们分别是：连接器、查询缓存、分析器、优化器、执行器。后面的内容中对每个组件进行了详细的介绍。连接器，负责身份认证和权限鉴别；查询缓存，将查询的结果集进行缓存，提高查询效率；分析器，对SQL语句执行语法分析和语法规则，生成语法树和执行计划；优化器，包括逻辑变换和代价优化；执行器，在检查用户权限以后对数据进行逐条查询，整个过程遵守SQL语句的执行顺序。