平时在使用学习MySQL以及阅读其他技术文章时，遇到了不少小知识点，特总结如下，希望也能对看到本文的朋友有一定帮助。

1. SQL执行顺序

1、from子句组装来自不同数据源的数据；

2、where 子句基于指定的条件对记录行进行筛选；

3、group by 子句将数据划分为多个分组；

4、使用聚集函数进行计算；

5、使用 having子句筛选分组；

6、计算所有的表达式；

7、select 的字段；

8、使用 order by 对结果集进行排序。

2.执行计划解释

SQL执行的顺序的标识,SQL从大到小的执行

id相同时，执行顺序由上至下
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

表示查询中每个select子句的类型

SIMPLE(简单SELECT，不使用UNION或子查询等)
PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
UNION(UNION中的第二个或后面的SELECT语句)
DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
DERIVED(派生表的SELECT, FROM子句的子查询)
UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。一般来说，得保证查询至少达到range级别，最好能达到ref

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，一般出现在where语句的条件中，如使用between、>、<、in等查询。
ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值
eq_ref: 类似ref，唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 表示通过一次索引就找到了结果，常出现于primary key或unique索引。因为只匹配一行数据，所以查询非常快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，key_len显示的值为索引字段的最大可能长度，并非实际使用长度

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。如果使用常数等值查询，则显示const，如果是连接查询，则会显示关联的字段。

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息，有以下几种情况：

Distinct ：在select部分使用了distinc关键字 一旦mysql找到了与行相联合匹配的行，就不再搜索了。 

no tables used：不带from字句的查询或者From dual查询

Not exists ?：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。
使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表 Range checked for each Record（index map:#）：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来查询如何对返回row(行)排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中(这是可能是ordery by，group by语句的结果，这可能是一个CPU密集型的过程，可以通过选择合适的索引来改进性能，用索引来为查询结果排序)

Using index ：查询时不需要回表查询，直接通过索引就可以获取查询的数据。，这发生在对表的全部的请求列都是同一个索引的部分的时候。   说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。

Using temporary ：看到这个的时候，查询需要优化了。表示使用了临时表存储中间结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上(常用于GROUP BY 和 ORDER BY操作中??)。 临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来 
Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

3.存储引擎

3.1 MyISAM

MyISAM存储文件格式为*.MYD(数据),*.MYI（索引）。

使用表级别的锁，即对表数据修改时对该表整个加锁，

支持数据压缩（myisampack命令压缩文件），支持全文索引

适用于非事务性应用，只读类应用（支持压缩）

3.2 InnoDB

InnoDB表空间分为独立表空间与系统表空间，默认使用独立表空间

事务性引擎，为了实现事务特性，使用了两种特殊的日志类型重做日志Redo Log和回滚日志Undo Log, Redo Log存储已提交的事务，Undo Log存储未提交的事务

InnoDB支持行级锁

可以使用 show engine innodb status 输出部分监控信息

3.3 CSV 引擎

数据以文本方式存储在文件中，可以直接查看，而以上两种都是用二进制形式存储的

所有列都是不能为null的，不支持索引

适合作为数据交换的中间表

3.4 Archive

会利用zlib对表数据压缩，磁盘io更少，数据存储在以arz为后缀的文件中

只支持insert和select，只支持在自增ID列上添加索引

可以应用于日志和数据采集类应用

3.5 Memory

也称HEAP，数据都保存在内存中，一旦数据库重启，数据丢失

默认使用hash索引

所有字段都是固定长度

使用表级锁

由于使用hash索引，等值查找效率优秀

4.MVCC

MVCC(Multi-Version Concurrency Control)多版本并发控制。对数据库的任何修改的提交都不会直接覆盖之前的数据，而是产生一个新的版本与老版本共存，使得读取时可以完全不加锁。

实现原理：

每个数据记录携带两个额外的数据created_by_txn_id和deleted_by_txn_id

· 当一个数据被insert时，created_by_txn_id记录下插入该数据的事务ID，deleted_by_txn_id留空。

· 当一个数据被delete时，该数据的deleted_by_txn_id记录执行该删除的事务ID。

· 当一个数据被update时，原有数据的deleted_by_txn_id记录执行该更新的事务ID，并且新增一条新的数据记录，其created_by_txn_id记录下更新该数据的事务ID

对于隔离级别控制如下：

· 对于Read Committed，每次读取时，总是取最新的，被提交的那个版本的数据记录。

· 对于Repeatable Read，每次读取时，总是取created_by_txn_id小于等于当前事务ID的那些数据记录。在这个范围内，如果某一数据多个版本都存在，则取最新的。

5.仍然建议选择读已提交(Read Commited)作为默认的隔离级别

该小节内容选自：www.cnblogs.com/rjzheng/p/1…

首先，为了便于表述，将

可重复读(Repeatable Read)，简称为RR；
读已提交(Read Commited)，简称为RC；

我们知道，在Oracle，SqlServer中都是选择RC作为默认的隔离级别，为什么Mysql不选择RC作为默认隔离级别，而选择RR作为默认的隔离级别呢？

这就要从Mysql的主从复制开始讲起，主从复制基于binlog复制，而binlog有三种格式，分别是

statement:记录的是修改SQL语句
row：记录的是每行实际数据的变更
mixed：statement和row模式的混合

而Mysql在5.0这个版本以前，binlog只支持STATEMENT这种格式！而这种格式在RC这个隔离级别下主从复制是有bug的。如下所示，在主(master)上执行如下事务：

SQL在master的执行顺序先删除后新增，而binlog为STATEMENT格式，它记录的顺序为先新增后删除，会出现从机执行的顺序和主机不一致，就会有主从不一致问题。

所以，考虑解决方案主要有以下两种：

隔离级别设为RR,在该隔离级别下引入间隙锁。当Session 1执行delete语句时，会锁住间隙。那么，Ssession 2执行插入语句就会阻塞住
将binglog的格式修改为row格式，此时是基于行的复制，自然就不会出现sql执行顺序不一样的问题！奈何这个格式在mysql5.1版本开始才引入

所以，基于这样的历史原因，Mysql将RC作为默认的隔离级别！

了解了以上问题，就要知道为何建议将Mysql的隔离级别设为RC，可以从以下缘由中分析：

缘由一：在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多！
缘由二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行
缘由三：在RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性！

其中，所谓半一致性读（semi-consistent），是Mysql5.1.15的时候，innodb引入的一个概念，即减少了更新同一行记录时的冲突，减少锁等待。就是一个update语句，如果读到一行已经加锁的记录，此时InnoDB返回记录最近提交的版本，由MySQL上层判断此版本是否满足update的where条件。若满足(需要更新)，则MySQL会重新发起一次读操作，此时会读取行的最新版本(并加锁)！

而在RR隔离级别下，若读到已经加锁的记录，则只能等待。

6.数据库与缓存双写一致性

该小节内容选自：www.cnblogs.com/rjzheng/p/9…

6.1 先更新数据库，再更新缓存

这套方案，大家是普遍反对的

原因一（线程安全角度）

同时有请求A和请求B进行更新操作，那么会出现（1）线程A更新了数据库（2）线程B更新了数据库（3）线程B更新了缓存（4）线程A更新了缓存

这就出现请求A更新缓存应该比请求B更新缓存早才对，但是因为网络等原因，B却比A更早更新了缓存。这就导致了脏数据，因此不考虑。

原因二（业务场景角度）

有如下两点：（1）如果你是一个写数据库场景比较多，而读数据场景比较少的业务需求，采用这种方案就会导致，数据压根还没读到，缓存就被频繁的更新，浪费性能。（2）如果你写入数据库的值，并不是直接写入缓存的，而是要经过一系列复杂的计算再写入缓存。那么，每次写入数据库后，都再次计算写入缓存的值，无疑是浪费性能的。显然，删除缓存更为适合。

6.2 先删缓存，再更新数据库

该方案会导致不一致的原因是。同时有一个请求A进行更新操作，另一个请求B进行查询操作。那么会出现如下情形: （1）请求A进行写操作，删除缓存（2）请求B查询发现缓存不存在（3）请求B去数据库查询得到旧值（4）请求B将旧值写入缓存（5）请求A将新值写入数据库

如何解决呢？采用延时双删策略 伪代码如下

public void write(String key,Object data){
        redis.delKey(key);
        db.updateData(data);
        Thread.sleep(1000);
        redis.delKey(key);
    }

转化为中文描述就是（1）先淘汰缓存（2）再写数据库（这两步和原来一样）（3）休眠1秒，再次淘汰缓存

6.3 先更新数据库，再删缓存（最优解）

这种情况不存在并发问题么？

不是的。假设这会有两个请求，一个请求A做查询操作，一个请求B做更新操作，那么会有如下情形产生（1）缓存刚好失效（2）请求A查询数据库，得一个旧值（3）请求B将新值写入数据库（4）请求B删除缓存（5）请求A将查到的旧值写入缓存

ok，如果发生上述情况，确实是会发生脏数据。

然而，发生这种情况的概率又有多少呢？

发生上述情况有一个先天性条件，就是步骤（3）的写数据库操作比步骤（2）的读数据库操作耗时更短，才有可能使得步骤（4）先于步骤（5）。可是，大家想想，数据库的读操作的速度远快于写操作的（不然做读写分离干嘛，做读写分离的意义就是因为读操作比较快，耗资源少），因此步骤（3）耗时比步骤（2）更短，这一情形很难出现。

7.问题合集

7.1 Mysql如何保持ACID

Mysql怎么保证一致性的？

从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段

Mysql怎么保证原子性的？

是利用Innodb的undo log。undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

Mysql怎么保证持久性的？

是利用Innodb的redo log。当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

Mysql怎么保证隔离性的？

利用的是锁和MVCC机制

7.2 可重复读(REPEATABLE_READ)并不能解决幻读

我们在该隔离级别下执行语句

select *  from tx_tb where pId >= 1;

是快照读，是不加任何锁的，根本不能解决幻读问题，除非你用

select *  from tx_tb where pId >= 1 lock in share mode;

这样，你就用上了next-key locks，解决了幻读问题！

Record Locks
锁定一个记录上的索引，而不是记录本身。 如果表没有设置索引，InnoDB 会自动在主键上创建隐藏的聚簇索引，因此 Record Locks 依然可以使用。 

Gap Locks
锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。 SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE; 

Next-Key Locks
它是 Record Locks 和 Gap Locks 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。例如一个索引包含 以下值：10, 11, 13, and 20，那么就需要锁定以下区间：
(-∞, 10] (10, 11] (11, 13] (13, 20] (20, +∞)

在串行读(SERIALIZABLE_READ)隔离级别下，所有的select语句后都自动加上lock in share mode，因此，在该隔离级别下，无论你如何进行查询，都会使用next-key locks

PS：

select lock in share mode
select for update

简单说，lock in share mode加了共享锁而另外一个是加了排它锁。共享锁允许其他事务加共享锁读取，但是，不允许其他事务去做修改，或者加排它锁。而排它锁显得更加严格，不允许其他事务加共享锁或者排它锁，更加不允许其他事务修改加锁的行。

MySQL杂货铺