你了解MySQL？

开局一张图,内容全靠编

实例&&数据库傻傻分不清

• 数据库

  就是一堆数据文件的集合，相当于一个大文件夹其中包含了各种格式的数据文件，不同的数据格式代表使用了不同的存储引擎，如我们经常使用的innoDB在磁盘上的存储格式就是.ibd文件，还有frm MYD MYI格式的，代表不同的存储引擎。如果当前的数据表出现问题，可以从这个数据文件执行恢复数据。需要特别注意的是存储引擎的粒度是数据表而不是数据库，所以在建表的时候是可以指定存储引擎的。

• 实例

  MySQL实例其实就是一个守护进程mysqld再加上一块共享内存，这个实例就参操作那些数据文件子这块内存和硬盘上来回复制，

切记有人以后再问及数据库实例和数据库时候就可以清晰的解释了，实例就是一个进程，单机而言一个数据库对应一个实例，集群下就是一个数据库对应了多个实例。

MySQL数据库结构

上图是摘自MySQL技术内幕一书中的配图，我相信大多数的开发人员不是很清楚MySQL到底是怎么的结构，大多数使用时候只是在项目中添加连接的jar包，配置数据源，然后crud，其内部构造不是业务的关注点，故而了解的点在开发人员是较为有限，不是很深入，但是作为软件开发人员最好对数据库有一个较为深入的理解，虽然我们不是DB开发程序员，但是熟悉数据库的构造，存储引擎，有助于我们更好的建立数据表，数据恢复等等，题外话就此打住，切入正题，上图中的最上层为多语言的支持，为各个语言提供api接口，用于交互数据库实例进行业务数据的操作，紧接着为连接池，最左边是暂且理解为管理中心，接着右边依次是sql接口实现，查询分析器 ，优化器，缓存 ，下面一层就是MySQL的插件存储引擎，用户可以在建立数据表的时候指定任何一个存储引擎，最下面就是磁盘的文件，所以整个MySQL的结构分层有序，而且灵活性十足，你要足够牛X，你可以自己实现存储引擎的接口，满足你的特殊需求。并不是我们单纯的启动电脑 启动mysql 好了，启动应用服务器，run就完事了，整个应用和每一步都是上下层有序的进行着，只不过这个对于非数据库研发来说是个黑盒。
浅谈常用的MySQL几大存储引擎

• InnoDB

  作为MySQL支持事务的唯一一个存储引擎，在5.5.8 release版本的MySQL 中为默认存储引擎 该存储引擎存放的数据表为idb格式独立存储，通过乐观锁来实现并发访问，支持标准SQL的隔离级别默认可重复读，innoDB拥有类似Oracle的rowid，在无指定主键的情况下，默认使用rowid，在存储上是按照主键顺序存储的。

• MyISAM

  MyISAM呢不支持事务，不支持锁，但是支持全文检索，在5.5.8版本之前是MySQL的默认存储，MyISAM呢的缓存仅仅是缓存了索引文件这一点是和InnoDB不一样的地方，而且MyISAM的方式数据文件和索引文件是分开存放的，MYD存放数据，而MYI存放索引，5.0版本之前MyISAM默认存储的表的大小只有4G，如果要存放的数据超过该值，需要修改配置文件中的max_rows和avg_row_length，5.0之后呢默认支持256TB。

• NDB

  NDB呢是Oracle收购回来的，这特点就是内存型，所有的数据塞入内存中，所以其查询速度是异常的快，可以通过水平拓展增加数据节点来线性提高数据库性能，但是其JOIN操作是由MySQL自己完成的，所以复杂的join操作需要很大的网络开销。

至于Memory Archive Feddeated Maria 不是常见使用的类型，就不提了，有兴趣可以自行谷歌。

记得面试的上有问过 当数据量大于1000万之后，MySQL的性能如何保证，回答的答案也是众说纷纭，数据行数的增加性能肯定会有所下降这道理很简单，篮子里的鸡蛋多了要保证不碎肯定得小心翼翼的拿放，不说业务上的如果处理这大数据量，MySQL官方手册曾说，只要你选择合理的数据存储引擎和配置，MySQL可以满足你的要求。

我们怎么平时的增删改查是怎么和MySQL交互

首先从应用JDBC触发，连接阶段是携带上我们的用户名 地址 密码 要链接的数据库的名字等这些信息，通过MySQL实例开放的端口进行通信，首先请求到达mysql实例，去建立tcp/ip连接，建立连接之后，要先去查询权限是否满足，如用户名 密码 以及请求的ip地址，这一系列校验通过之后，就可以从连接池中获取一个连接,之后的数据操作工作呢，就有这个连接池中的实例去通过访问sql接口来进行文件的读写。

我们来看这个查询SQL执行，在mysql Server端到底执行了那些内部操作。

select * from T where ID=10  

首先要执行就需要先去建立连接，不管是mysql Client还是jdbc去连接的时候均会和mysql Server的连接器交互，在交互的过程中，回去校验用户的权限，然后建立一个数据库链接，从连接池中拿到一个实例，这是一个长连接，所以有很多数据库连接的话，数据库会很占内存，极坏情况下会重启，拿到数据库实例之后会利用mysql的分析器去吧这条sql语句去解析，在分析的过程中会去判断查询的column是否存在，如果不存在就会报Unknown Column 之类的错误，最主要的是会把sql中的表名映射为 数据库中的表，列映射为数据库的Column 映射完毕之后会对这条sql进行sql语法校验，要是这时候你的sql关键字写错了 就会提示你 ...have an error in your SQL syntx,出现之后你只需关注use near处的内容即可，然后经过语法校验完毕之后会走到优化器，在优化的过程中回看你的查询条件是否使用了索引，如果有多个索引时决定使用哪一个，还有就是join的时候要去决定连接的顺序，这一系列的操作执行完毕之后就到了执行器阶段了，在执行器阶段还是需要做一步权限验证，此时会校验你这个用户是否对该表有权限，如果有就回去交互存储引擎去拿去数据，读取数据的过程，会逐行搜索，当遇到就加入结果集中，没有则会走到最后一行，然后将拿到的结果集返回。

我们来看这个更新SQL执行，在mysql Server端到底执行了那些内部操作。

update T set name ='李茂' where ID=10  

和之前分析的查询语句执行一样，同样也会走连接器，分析器，优化器，执行器这一套，但是不同的是更新操作呢涉及到binlog，而innodb数据库引擎时候还会设计到redolog。redo log呢是innodb数据引擎特有的，mysql5.5版本之前都是myisam引擎，这个存储引擎呢，就是所谓的无事物，大表存储，mysql收购回来innodb引擎之后呢，才增加了事物，什么事redolog呢，就类似与cpu和主存之间的高速缓存。所有的更新操作在更新了redo页之后就返回结果集，应为频繁的交互磁盘带来的IO代价特别的高，而IO密集型的web引用来说，这部分的代价就灰常高，所有innodb存储引擎就搞了个redo页，默认是4个redo页，更新只去更新内存，有一条守护进程去做刷磁盘的工作。应为redo页的存在，异常重启时候，不会丢失之前提交写操作，binlog呢则是归档日志，就是说你所有的操作均会在这里体现，一般用于主从复制时候，从库备份主库的数据，还有就是，这俩个日志的区别是，binlog类似redis的aof模式，追加写，但是没有aof的合并命令的操作，基本是逻辑备份，你执行了什么sql就会在里面push什么记录，而redolog则不是，内存中指定了这些区域，是循环写的，当redo页满了之后，会通知进程去刷磁盘，然后刷完之后更新操作会覆盖之前。而redolog最著名的就是二踢脚，(二阶段提交)，更新内存之后就回去此时redo也先提交一次进入prepare，然后向binlog写入日志，最后在提交一次redo页进入commit，这样保证了redolog和binlog的数据一致。和这俩个log相关的两个数据库参数为innodb_flush_log_at_trx_commit,sync_binlog,这俩个设置为1，代表每一次更新均会持久化。建议在线上开启。数据无价。。。

事物隔离级别

事物的隔离 分为如下几种 READ_UNCOMMITTED READ_COMMITED REPEATABLE_READ SERLALIZABLE

针对上图的查询情况下，如果是Oracle的读提交模式下。那么V1==1 V2 V3的值都是2，而如果是读未提交的情况下呢，那么V1 V2 V3得到的值都是2，可重复读时候V1 V2都是1 V3的值是2，串行化则是，对同一行来说，写会加写锁，读会加读锁，也就是说 1改为2的b事物需要在a事物完事之后才可以，所以对应的值为v1=1 v2=1 v3=2。
提到事物呢我们就要说下这个隔离级别的实现，其依赖于回滚段的设计，也就是undo页，一个数据记录在数据库存在了多条备份，也就数MVCC多版本控制，依托于这个就可以实现4中不通的隔离级别。当然如果持有的事物特别长的时候，这样回滚段的占用就会非常感，这些就可能拖垮库。所以我=我们开发的时候要避免使用长事物，在建立连接的时候有时候会增加一个参数autoCommit=0这样的时候就会在开始建立连接就打开了事物，如果该连接一直没有释放得话，就是一个长事物。我们要避免这个参数的使用，
可以使用如下语句去查询事物较长的

-- 大于60秒的长事物
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

MVCC

这里会用到一致性视图，这个视图和查询语句定义的虚拟表不一，这个是innodb用来实现mvcc的关键。这个视图用来定义事务执行前进能感知到那些数据。
在可重复读的隔离级下，我们知道同一个事务内部对于看到的结果均一样，如同快照一样。这个快照是基于全库的。如果是另外拷贝出来一份，那以事务的实现岂不是太臃肿，这个快照不是在开始事务的时候采取创建，而数据表中的记录在存放时候就是从存放了多个版本，每一数据记录的版本有一个事务ID，这个id对应的是innodb的事务处理系统中的transaaction id，这就为undo log的实现提供了温床，注意这个使用undo log去回滚数据的时通过倒计算得到的并不是物理落盘，每一个已经提交了事务id会被在缓存在内存中，新开事务会获取已提交事务的值加1，然后作为新事务的id，这一段距离的包含的数据记录构成的就是一致性视图。

索引

索引是数据检索的一个重要的使用策略，是为了提高数据查询的速率，索引的设计模型一般统筹有序数组索引 hash表 二叉搜索树这几种模式，hash的模式适配于等值搜索，有序数据适用于静态数据的搜索，树的查询时间复杂度O(LogN)。

• hash表

  hash表其结构(key–value)。我们更具要查找的数据的hash值去定位key，然后在得到key之后去获取value，hashMap就是基于该模式的的数组加链表的情况来实现，拉链的方式来取解决hash冲突，采取hash表的模式检索不适用于范围搜索场景。

• 有序数组

  有序数组呢则是基于在等值查询和范围查询的场景中性能还是可以满足的，参考数组的随机访问即可以找到，但是结合数组的插入，如果插入的同事还要保证数组的有序，那么一定会有数据的移位，所以有序数组的检索场景在于静态数据的查询。

• 二叉搜索树

二叉搜索树的特点就是左子树都比根节点小，右子树都比根节点大。所以查询性能就是O(LogN)，为了保证这个性质，那么其必须是要做到插入或者删除的时候，要保证这个树的平衡。实际查询中我们可以使用的N叉树了。

我简述了上述的三种索引模型，innodb存储引擎下支持三种索引，全文索引，B+tree，哈希索引。

innodb的哈希索引比较特殊的是自适应模式无法人工干预在数据表使用hash索引。使用与等值搜索，如select * from table where name='xxxx';并且访问该数据页的模式必须一致持续访问超过100次，还有要保证该页被访问的N次=页中的记录的1/16.此时innodb存储引擎会建立自适应hash索引，可以通过SHOW ENGING INNODB STATUS来查看当前的AHI的使用情况。

B+tree索引呢就是使用的二叉搜索树索引模型来实现的。B+tree的数据结构已经不可以认为是一颗树了，因为b+tree的叶子节点之间用指针关联着，使用B+tree来做查询时比Btree(平衡多路查找树)减少了磁盘访问的次数，而且其节点的的大小也比Btree的占用空间小。

要理解B+tree数据结构，首先要知道二分查询，也叫做折半查找，针对有序数组进行的跳跃式的查找,通过与中间值的比较逐步划分查找区间。

public static int midFind(int[] array,int n,int start,int end){
        if (start>end) return -1;
        int mid=start+((end-start)>>1);
        if (n==array[mid]){
            return mid;
        }else if (array[mid]>n){
            return midFind(array,n,start,mid-1);
        }else {
            return midFind(array, n, mid+1, end);
        }
}
     6    
    /  \
   /    \
  3      7
 /  \      \  
2   5      8

[2,3,4,5,6,7,8]

构造一有序的二叉查找树，左子树都比根节点小，右子树都比根节点大，那么对于上面这个颗树来说，每次从根出发，找5为例，先找左子树，再找左子树的右子树，找了3次，以此类推找2,5,8的趟数均是3，如果是有序数组的顺序查找，那么找到8就得多进行3次的访问。二叉查找的速度平均也要比顺序访问快的多。二叉查找树呢只要符合右节点始终大于根，就可以任意构建，但是其性能也就不一样了，所以为了补充二叉查找树的稳定性，引入了平衡的概念，任意节点的俩个子树的高度最大差为1，为了保证插入和删除节点之后树的平衡，引入了左旋，右旋的操作。B+tree呢则是由精简的Btree+叶子节点顺序访问来构造。除了构造了一颗平衡查找N叉树，其所有的节点都是按照Key的大小顺序存放的。而且叶子节点之间通过指针关联着。

B+Tree的插入删除

当节点中搜索节点的个数达到5的时候进行页分裂。每页存放4个节点,叶子节点的个数必须是有序的，插入删除也需要保证这个顺序

                {[25]               |[50]                       |[75]    |[]}
            /            \               \                         \
          /                \               \                         \
   {[5]|[10]|[15]|[20]}—>{[25]|[30]|[]|[]}—>{[50]|[55]|[60]|[]}—> {[75]|[80]|[89]|[99]}

插入

当叶子节点没有满或者索引节点没有满的时候，按照顺序直接插入对应节点即可，如果说叶子节点满了达到了5个了而索引节点未满，就要分裂该叶子节点成左右节点，把中间节点提到索引节点中，小于中间节点的放在左叶子节点中，大于中间节点的放在右节点中。如果说索引节点满了叶子节点也满了，那么叶子节点分裂成左右节点，把中间节点提到索引节点中，小于中间节点的放在左叶子节点中，大于中间节点的放在右节点中，然后索引节点也一样。每次的平衡的代价就是节点的拆分，这就意味着大量的磁盘交互，也会发生旋转来吧记录移动到左右兄弟节点。

删除

B+tree的删除方式页的填充因子有关，填充因子在innodb中相关的参数为innodb_fill_factor默认100.表示使用时页是会剩余极小的空间，会分配1/16的用于未来的增长，只针对于索引节点和叶子节点，text和blob的溢出行是无效的。当叶子节点大于填充因子时，直接删除，如果该节点还是索引节点，那么用该节点的右节点来替换；如果叶子节点小于填充因子中间节点大于填充因子，那么就合并这个叶子节点和他的兄弟节点，同时会更新索引节点，如果说叶子节点和索引节点都小于填充节点时，合并这个叶子节点和他的兄弟节点，同时更新索引节点，合并索引节点和他的兄弟节点。

聚集索引和普通索引

数据库的索引分为了聚集索引和普通索引，聚集索引和普通索引的关键在于起叶子节点的存放内容，聚集索引存放的是整行的数据，而聚集索引存放的则是主键的值。

聚集索引就是根据主键构造了一张B+tree，叶子节点的存放的是整行记录，由于聚集索引构造按照主键的顺序来构造，所以检索的速度非常快，而且sql执行在优化器阶段更加侧重于聚集索引，由于是有顺序的，所以针对主键的范围查询和排序是很快的。应为对于主键的order By 操作实际上在查询收集结果集就处理了，并不会做filesort的操作。

普通索引则是在索引节点上就是被索引的key的值，而叶子节点呢则是主键的值，当使用普通索引的来查询数据的过程呢，就是需要从普通索引的B+tree上搜索到主键的信息，然后在做一次“回表”操作，再从主键查询得到整行的数据。

索引语法

ALTER TABLE ADD INDEX|KEY indexName (name,age);
ALTER TABLE DROP INDEX|KEY indexName;

CREATE INDEX indexName ON tableName(name,age);
DROP INDEX indexName ON tableName

Innodb的主键索引的创建和删除代价是极大的，会先创建一个临时表，然后通过ALTER TABLE 填充表结构，把原表的数据导入临时表，删除原表，然后重命名临时表。但是普通的索引则由于FIC机制的存在，会给原表加锁，在原表的基础上建立，完毕之后释放锁，删除普通索引的情况则是直接表示内部视图中辅助索引的空间标记为不可用即可。

查询某个表的索引使用情况

SHOW INDEX FROM TABLENAME;

着重关注结果中的Cardinality表示索引中唯一值的预估值，如果该值非常小，可能该索引的列就是不是高频变换的，增加索引的效果不明显，这个Cardinality的值会被查询优化器使用，可以使用 ANALYZE TABLE tableName去更新该值。当Cardinality等于NULL的时候，可能创建了索引却从未使用过。在做sql执行计划分析的时候，如果说俩次的分析结果不一致，一次使用表扫描 一次使用了索引，那么此时可以更新一下Cardinality的值，让优化器更高效的工作。

虽然普通索引的创建有FIC机制不用去创建临时表，但是加的锁会阻塞DML操作。至于为什么，我会在表锁MDL(Meta Data Lock)时候细致分析。所以再MySQL5.6之后增加了一个Online DDL，就是允许在执行普通索引创建的时候允许 insert update delete 操作，还有添加外键，column的重命名等。

ALTER TABLE ADD INDEX|KEY indexName (name,age) ALGORITHM = {DEFAULT|INPLACE|COPY} LOCK = {NONE|SHARE|EXCLUSIVE|DEFAULT};

ALGORITHM用于指定索引创建删除的算法，INPLACE则是原地操作不创建临时表，COPY则是回去创建，LOCK部分
NONE表示不使用锁，这是并发自由度最高，但是有并发访问问题，SHARE则是会加一个写锁，阻塞写，对于读事物并不会阻塞，EXCLUSIVE则是，读写均阻塞，但是不会创建临时表，DEFAULT是默认判断当前是否可以使用这三种模式，NONE—>SHARE—>EXCLUSIVE

online DDL的实现原理则是在把DML的操作日志放到缓存中，先去更新DDL然后在重新把DML的操作从缓存拿出来，然后在对表数据进行操作。

回到Cardinality这个值上来看，我们知道索引的建立并不是越多越好，应为更新操作会对索引的进行维护，当然建立索引的字段应该是高选择性的，长范围的，不能是性别这种低频字段。这个值代表了数据库不重复记录的数。是个预估值。那么他和总行数的比值越接近1，证明该索引建立的越合理。

联合索引

联合索引这一块就有一个最左适配原则，比如我们在(a,b)俩列上建立 那么查询select * from t where a='xx' h和 select * from t where b='yy'和select * from t where a='xx' and b='yy'均会使用到索引， 如果是 select * from t where b='xx' and x='yy' 则不会使用到索引应为顺序不一致，，还有就是使用联合索引的时候，如果被查询的字段在联合索引中，则不会去再进行一次回表操作，所以我们要避免使用*去查询，而是查询指定字段的形式去。
还有就是在MySQL5.6之后，支持了索引下推，就是说在命中a索引的时候会在联合索引内部进行一次过滤。减少了回表查询次数。

PS:
在范围查询的时候 又是可能会导致索引失效(基于上面的联合索引)
select * from t wehre a>1 and b<1000

如果说SQL优化器不能自动指定索引的话，可以使用FORCE index来强制使用索引，只针对普通索引。

针对索引的MRR优化，使用set @@optimizer_switch='mrr=on,mrr_cost_based=off 来开启。在explain分析是可以看到 use Condition MRR 使用了MRR之后性能提升较为明显，而且针对联合索引的情况来说
select * from t where a>1 and a<1000 and b='xx', 会被优化为等值查询（1,‘xx’）…(1000,‘xx’）.

---

锁

全局锁

顾名思义就是给整个数据库实例增加一个读锁，FTWRL Flush tables with read lock当为数据库实例添加全局锁之后 增，删，改，事务操作，修改表结构等都会被挂起。这个锁的使用场景在于对于全库进行逻辑备份。适用于无事务的存储引擎如MyISAM之流。而InnoDB 由于有MVCC的存在执行备份时候会开启一个事物，而其余的写是可以进行的。

---

全局锁和golab readonly =true都可以把数据库实例设置为只读，区别在readonly可能被用来区别主从库，如果用来去作备份的话都可以的，但是如果client执行过程中出现了异常，数据库会把全局自动锁释放，但是golab readonl就无法释放了，会导致这个实例
长时间无法写。还有就是如果有超级用户权限的话，readonly是无效的。

表锁

语法：加锁lock tables tableName read/write；解锁unlock tables

表锁是独占，除了自己释放，就只有客户端断开的时候自动释放。如A线程执行了表1读锁，表2写锁，那么A线程执行unlock tables之前别的线程是操作的，而A也应为由其加锁规则，只能对表1读，表2读写，无法对表1进写。

MDL（ metadata lock）

在MySQL 5.5引入MDL，不需要显示引用，会访问表的时候自动加上，当对于一个表进行增删改查会增加MDL 读锁，在对数据表结构进行修改时候加上MDL写锁。该锁的读锁不互斥，读写锁和写锁之间是互斥的，需要一个执行完毕才能执行下一个。释放的情况是在事务提交之后。所以遇到手动begin事物一直不提交，如果期间有ddl语句执行就会导致整个表锁住，运维执行可以去查询长事物kill，程序最好是在业务做作一下超时取消事物操作。
需要注意加字段的问题，因为会扫描全表，
执行增加字段操作时候尽量选择业务地峰执行。还有在MySQL 5.6支持了online ddl对表的ddl和dml进行了优化一般不会阻塞读写，但是如果在有长事物这种情况存在话 依旧是有锁表的风险的。

    online ddl

执行过程当拿到MDl写锁时候对锁执行降级，为MDL读锁，然后去执行ddl操作，这样读取是不会被阻塞的，执行完毕之后锁再去膨胀，让后升级成写，然后释放。也不是有了这个机就万无一失了，如第一就没获取到，之后的也都是白费功夫。

行锁

行锁是每个存储引擎自己实现，粒度比表锁更加细。对A行的update两条sql需要一个执行完另一个才可以执行。 而且行锁是在需要的时候加上，并不是不需要就释放了，也是在事务结束之后才会释放，所以在一个事物下如果有多个更新操作的话，你可以尽量把可能造成锁冲突的语句往后面。

JOIN

INNER JOIN

语法：A INNER JOIN B||A,B||A CROSS JOIN B

内连接主要用于获取数据表A,B中均可以匹配上的行

LEFT JOIN || RIGHT JOIN

语法：A LEFT JOIN B ||A RIGHT JOIN B 

有一个前提column不允许为NULL .因为左连接右的时候又数据表不存在的行会被补NULL,如果字段被设置为可以为NULL,就无法区分是未匹配到还是真是数据就是如此。左连接主要用于获取数据表A中匹配上的行 在数据表B中没有， 用于解决获取缺失值问题。右连接则是和左连接正好相反。

JOIN的优化算法NLJ和BNL

我们知道不合理的使用JOIN会导致全表扫描，严重了会拖垮业务查询。那我们都知道表去驱动大表进行JOIN查询，但是细节之处还是了解的甚微。

NLJ(Index Nested Loop Join)命中索引

如果说被驱动的表可以命中索引的话，完全没有问题可以使用JOIN查询，这也就是JOIN的NLJ优化算法，因为可以命中索引的话，查询的效率相当于是对N叉树的查询时间复杂度为Log2^{N由于索引回表的存在，那么查询被驱动表的一行时间就需要花费2}Log2^{N的时间，总共的笛卡尔集为M+M*2}Log2^N(M为驱动表的数据记录数目，N为被驱动表的数据记录数目）。

BNL(Block Nested Loop Join)分块join使用join bufffer

BNL算法和(Simle Nested Loop Join)一样都是没有办法命中索引，这样导致的问题呢就是俩表会作笛卡尔集，都进行全表扫描，不同的是BNL算法使用内存缓存，把表数据分段存放，然后然后从join buffer中获取数据和被驱动表进行笛卡尔集，一定程度上避免了直接俩个全表扫描。如果非要头铁使用join么有索引的俩个表，建议增大join_bufffer_size的设置。

ps：我们知道优化器会对查询进行优化，实际上还会优化join查询，所有当你A left join B的时候 不一定A就是驱动表，如果被驱动表可以命中索引可能就被优化成了join，

表f1，f2中字段一样，f1中的1字段有索引。
Q1：select * from f1 left join f2 on f1.a=f2.a and f1.2=f2.2 ;
Q2：select * from f1 left join f2 on f1.a=f2.a where f1.2=f2.2 ;

explain这俩条查询，第一个可以看到在Extra中有显示使用了BNL算法，这没可说的。关键在第二条中，可以看到在对f2表的扫描中有用到索引，是不是有点好奇，这个要和sql的执行顺序有关，join之后，俩表数据会去校验on后面的条件，如果有where的话还会去校验这部分的条件进行返回，第一个就没有过滤直接是笛卡尔集，第二个对on之后的有进行了过滤，我们知道left join 中查询a中的记录在b中不存在会补充NULL，由于MySQL中NULL和任何值的比较都是NULL，被where条件过滤的第二个查询就说明了这个查询语义是我的返回结果中都是f1，f2中都有的记录， 然后就被优化成了join查询。
这个优化如果针对获取都有的数据，那没有关系，属于MySQL帮助你做了一把优化。那要是你需要left join这个语义去获取被驱动表中不存在驱动表的信息，那你就要注意sql的执行顺序，也就是on和where不要乱用。