Mysql sql执行流程

一. sql执行流程

1.当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名与密码认证，也可以通过SSL证书进行认证。登录认证后，服务器还会验证客户端是否有执行某个查询的操作权限。
2.在正式查询之前，服务器会检查查询缓存，如果能找到对应的查询，则不必进行查询解析，优化，执行等过程，直接返回缓存中的结果集。 3.MySQL的解析器会根据查询语句，构造出一个解析树，主要用于根据语法规则来验证语句是否正确，比如SQL的关键字是否正确，关键字的顺序是否正确。而预处理器主要是进一步校验，比如表名，字段名是否正确等
4.查询优化器将解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方式，最终返回相同的结果，优化器就是根据成本找到这其中最优的执行计划 5.执行计划调用查询执行引擎，而查询引擎通过一系列API接口查询到数据 6.得到数据之后，在返回给客户端的同时，会将数据存在查询缓存中

登陆认证 -> 权限认证 -> 查询缓存 -> 如果无缓存 -> 解析器构造解析树 -> 验证语法 -> 查询优化器将解析树转换为查询计划并选择最优的执行计划 -> 执行计划调用查询执行引擎 -> 通过API接口查询数据 -> 返回给客户端,并保存在缓存中 简化过程即 : 解析 -> 优化 -> 执行

二. 缓存

我们先通过show variables like '%query_cache%'来看一下默认的数据库配置，此为本地数据库的配置。

2.1 概览

have_query_cache:当前的MYSQL版本是否支持“查询缓存”功能。 query_cache_limit:MySQL能够缓存的最大查询结果，查询结果大于该值时不会被缓存。默认值是1048576(1MB) query_cache_min_res_unit:查询缓存分配的最小块（字节）。默认值是4096（4KB）。当查询进行时，MySQL把查询结果保存在query cache，但是如果保存的结果比较大，超过了query_cache_min_res_unit的值，这时候MySQL将一边检索结果，一边进行保存结果。他保存结果也是按默认大小先分配一块空间，如果不够，又要申请新的空间给他。如果查询结果比较小，默认的query_cache_min_res_unit可能造成大量的内存碎片，如果查询结果比较大，默认的query_cache_min_res_unit又不够，导致一直分配块空间，所以可以根据实际需求，调节query_cache_min_res_unit的大小。注：如果上面说的内容有点弯弯绕，那举个现实生活中的例子，比如咱现在要给运动员送水，默认的是500ml的瓶子，如果过来的是少年运动员，可能500ml太大了，他们喝不完，造成了浪费，那我们就可以选择300ml的瓶子，如果过来的是成年运动员，可能500ml不够，那他们一瓶喝完了，又开一瓶，直接不渴为止。那么那样开瓶子也要时间，我们就可以选择1000ml的瓶子。 query_cache_size:为缓存查询结果分配的总内存。 query_cache_type:默认为on，可以缓存除了以select sql_no_cache开头的所有查询结果。 query_cache_wlock_invalidate:如果该表被锁住，是否返回缓存中的数据，默认是关闭的

2.2 原理

MYSQL的查询缓存实质上是缓存SQL的hash值和该SQL的查询结果，如果运行相同的SQL,服务器直接从缓存中去掉结果，而不再去解析，优化，寻找最低成本的执行计划等一系列操作，大大提升了查询速度。 问题一 : 第一个弊端就是如果表的数据有一条发生变化，那么缓存好的结果将全部不再有效。这对于频繁更新的表，查询缓存是不适合的。 问题二 : 第二个弊端就是缓存机制是通过对SQL的hash，得出的值为key，查询结果为value来存放的，那么就意味着SQL必须完完全全一模一样，否则就命不中缓存。

三.索引

聚簇索引（主键索引）
他包含两个特点： 1.使用记录主键值的大小来进行记录和页的排序。 页内的记录是按照主键的大小顺序排成一个单项链表。 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。 2.叶子节点存储的是完整的用户记录。

非聚簇索引（二级索引）
1.叶子节点内部使用name字段排序，叶子节点之间也是使用name字段排序。 2.叶子节点不再是完整的数据记录，而是name和主键值。

为什么不再是完整信息？
MySQL只让聚簇索引的叶子节点存放完整的记录信息，因为如果有好几个非聚簇索引，他们的叶子节点也存放完整的记录绩效，那就不浪费空间啦。 如果我搜索条件是基于name，需要查询所有字段的信息，那查询过程是啥？
1.根据查询条件，采用name的非聚簇索引，先定位到该非聚簇索引某些记录行。
2.根据记录行找到相应的id，再根据id到聚簇索引中找到相关记录。这个过程叫做回表

3.1 一些原则

1.最左前缀原则。一个联合索引（a,b,c）,如果有一个查询条件有a，有b，那么他则走索引，如果有一个查询条件没有a，那么他则不走索引。
2.使用唯一索引。具有多个重复值的列，其索引效果最差。例如，存放姓名的列具有不同值，很容易区分每行。而用来记录性别的列，只含有“男”，“女”，不管搜索哪个值，都会得出大约一半的行，这样的索引对性能的提升不够高。
3.不要过度索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
4、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);5.一定要设置一个主键。前面聚簇索引说到如果不指定主键，InnoDB会自动为其指定主键，这个我们是看不见的。反正都要生成一个主键的，还不如我们设置，以后在某些搜索条件时还能用到主键的聚簇索引。
6.主键推荐用自增id，而不是uuid。上面的聚簇索引说到每页数据都是排序的，并且页之间也是排序的，如果是uuid，那么其肯定是随机的，其可能从中间插入，导致页的分裂，产生很多表碎片。如果是自增的，那么其有从小到大自增的，有顺序，那么在插入的时候就添加到当前索引的后续位置。当一页写满，就会自动开辟一个新的页。

注：如果自增id用完了，那将字段类型改为bigint，就算每秒1万条数据，跑100年，也没达到bigint的最大值

为什么索引用B+树）
1、B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。 2、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

B树 每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。 B+树 只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。

四. Explain

一般来说一个select一个唯一id，如果是子查询，就有两个select，id是不一样的，但是凡事有例外，有些子查询的，他们id是一样的。

4.1 概念

select_type

• simple：不包括union和子查询的查询都算simple类型。
• primary：包括union，union all，其中最左边的查询即为primary。
• union：包括union，union all，除了最左边的查询，其他的查询类型都为union。

table

• 显示这一行是关于哪张表的。

type：访问方法

• all — 扫描全表数据
• index — 遍历索引
• range — 索引范围查找
• index_subquery — 在子查询中使用 ref
• unique_subquery — 在子查询中使用 eq_ref
• ref_or_null — 对 null 进行索引的优化的 ref
• fulltext — 使用全文索引
• ref — 使用非唯一索引查找数据
• eq_ref — 在 join 查询中使用主键或唯一索引关联
• const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点

possible_keys

• 对某表进行单表查询时可能用到的索引

key

• 经过查询优化器计算不同索引的成本，最终选择成本最低的索引

rows

• 如果使用全表扫描，那么rows就代表需要扫描的行数
• 如果使用索引，那么rows就代表预计扫描的行数

filtered

• 如果全表扫描，那么filtered就代表满足搜索条件的记录的满分比
• 如果是索引，那么filtered就代表除去索引对应的搜索，其他搜索条件的百分比

五. 事务

事务中有一个隔离性特征，理论上在某个事务对某个数据进行访问时，其他事务应该排序，当该事务提交之后，其他事务才能继续访问这个数据。但是这样子对性能影响太大，我们既想保持事务的隔离性，又想让服务器在出来多个事务时性能尽量高些，所以只能舍弃一部分隔离性而去性能。
事务并发执行的问题

• 脏写（这个太严重了，任何隔离级别都不允许发生）
  sessionA：修改了一条数据，回滚掉
  sessionB：修改了同一条数据，提交掉对于 sessionB来说，明明数据更新了也提交了事务，不能说自己啥都没干
• 脏读：一个事务读到另一个未提交事务修改的数据
  session A：查询，得到某条数据 session B：修改某条数据，但是最后回滚掉啦
  session A：在sessionB修改某条数据之后，在回滚之前，读取了该条记录
  对于session A来说，读到了session回滚之前的脏数据
• 不可重复读：前后多次读取，同一个数据内容不一样
  session A：查询某条记录
  session B : 修改该条记录，并提交事务
  session A : 再次查询该条记录，发现前后查询不一致
• 幻读：前后多次读取，数据总量不一致 session A：查询表内所有记录
  session B : 新增一条记录，并查询表内所有记录
  session A : 再次查询该条记录，发现前后查询不一致

四种隔离级别
数据库都有的四种隔离级别，MySQL事务默认的隔离级别是可重复读，而且MySQL可以解决了幻读的问题。

• 未提交读：脏读，不可重复读，幻读都有可能发生
• 已提交读：不可重复读，幻读可能发生
• 可重复读：幻读可能发生
• 可串行化：都不可能发生