目录
4. InnoDB与MyISAM引擎最大的不同是什么？（面试题）
一、首先复习打印的课件
二、Explain中的列
id列：id列的编号是select的序列号，有几个select就有几个id，且id的顺序是按select出现的顺序增长的；id编号越大执行优先级越高，id相同则从上到下执行，id为null最后执行。
select_type列：表示对应行时简单还是复杂的查询。回忆有哪几个取值？
table列：当前表名。
partitions列：显示查询将访问的分区，如果你的查询是基于分区表。
type列：这一列表示关联类型或访问类型，即MySQL决定如何查询表中的行，查找数据记录大大概范围。依次从最优到最差分别为：____________________________________；一般来说，得保证查询达到range级别，最好达到ref。
possible_keys列：这一列显示查询可能使用哪些索引来查找。
key列：这一列显示MySQL实际采用哪个索引来优化对该表的访问。如果没有使用索引，则显示为null。
key_len列：这一列显示了MySQL在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。该值越小越好。
ref列：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const(常量)，字段名(例如：film.id)。
rows列：这一列是MySQL估计要读取并检测的行数。
filtered列：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。
extra列：这一列展示的是额外信息。
实现
三、解读extra
1、using index
表示实现了覆盖索引扫描；也就是需要访问的数据都在索引中，不需要回表。在一般情况下，减少不必要的数据访问能够提升效率。
2、using where：
一般有两层意思：表示通过索引访问时，需要再回表访问所需的数据；
过滤条件发生在server层而不是存储引擎层；
如果执行计划中显示走了索引，但是rows值很高，extra显示为using where，那么执行效果就不会很好。因为索引访问的成本主要在回表上，这时可以采用覆盖索引来优化。
通过覆盖索引也能将过滤条件下压，在存储引擎层执行过滤操作，这样效果是最好的。所以，覆盖索引是解决using where的最有效的手段。
3、using index condition
表示将过滤下压到存储层执行，防止server层过滤过多数据
如果extra中出现了using index condition，说明对访问表数据进行了优化。
4、using temporary
表示语句执行过程中使用到了临时表。以下子句的出现可能会使用到临时表：
order by
group by
distinct
union等
数据不能直接返回给用户，就需要缓存，数据就以临时表缓存在用户工作空间。注意，可能会出现磁盘临时表，需要关注需要缓存的数据的rows。
可以使用索引消除上面的四个操作对应的临时表。
5、using filesort
说明有排序行为，但是不一定是磁盘排序。将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘排序。这种情况下一般也是要考虑使用索引来优化的。
Using filesort通常出现在order by，当试图对一个不是索引的字段进行排序时，mysql就会自动对该字段进行排序，这个过程就称为“文件排序”。
6、using sort_union(indexs)
比如当执行下面语句：
Sname和sphone列上都有索引，这时执行计划的extra项就会显示using sort_union(i_sname,i_spone)，表示索引合并。常伴随着index_merge。
7、using MRR
一般通过二级索引访问表数据的过程是：先访问二级索引列，找到对应的二级索引数据后就得到对应的主键值，然后拿着这个主键值再去访问表，取出行数据。这样取出的数据是按照二级索引排序的。
MRR表示：通过二级索引得到对应的主键值后，不直接访问表而是先存储起来，在得到所有的主键值后，对主键值进行排序，然后再访问表。这样可以大幅减低对表的访问次数，至少实现了顺序访问表。
MRR的一个优点就是提升索引访问表的效率，也就是降低了回表的成本。但是有一个比较大的问题：取出来的数据就不按照二级索引排序了。
8、using join buffer(Block Nested Loop)
BNL主要发生在两个表关联时，被关联的表上没有索引。
BNL表示这样的意思：A关联B，A的关联列上有索引而B的没有。这时就会从A表中取10行数据拿出来放到用户的join buffer空间中，然后再取B上的数据和join buffer中A的关联列进行关联，这时只需要对B表访问一次，也就是B表发生一次全表扫描。
如果join buffer中的10行数据关联完后，就再取10行数据继续和B表关联，一直到A表的所有数据都关联完为止。
从上面可以看出来，这种方式大概效率会提高约90%。
9、using join buffer(Batched Key Access)
一般出现BKA的情况是：表关联时，被驱动表上有索引，但是驱动表返回的行数太多。
当出现上述情况时，就会将驱动表的返回结果集放到用户工作空间的join buffer中，然后取结果集的一条记录去关联被驱动表的索引关联列。得到相应的主键列后并不马上通过这个主键列去被被驱动表中取数据，而是先存放到工作空间中。等到结果集中的所有数据都关联完了，对工作空间中的所有通过关联得到主键列进行排序，然后统一访问被驱动表，从中取数据。这样的好处就是大大降低了访问的次数。
从上面可以看出：BKA用到了MRR技术；BKA适合驱动表返回行数较多、被驱动表访问时走的是索引的情况。
这个功能可以打开或者关闭：
Set optimizer_switch=’mrr=on,batched_key_access=on’;
10、using index for group by
表示通过复合索引完成group by,不用回表。
例如复合索引(a,b)，执行语句：select a from tb group by b;时就会出现using index for group by。
11、materialize scan
对物化表的全扫描，因为物化表就是一个临时表，表上没有索引。
四、索引最佳实践
1. 不在索引列上进行计算、函数、类型转换，否则会导致索引失效而全表扫描；
2. 存储索引不能使用索引中范围条件右边的列；
3. 尽量使用覆盖索引，减少回表；
4. MySQL在使用不等于(!=或者<>)，not in，not exist，not like的时候无法使用索引会导致全表扫描；
5. 小于<，大于>，小于等于≤，大于等于≥这些，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引；
6. is null，is not null一般无法使用索引；
7. 字符串不加单引号索引失效；
8. 用or或者in查询时，MySQL不一定使用索引；MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引；（or或者in在表数据量比较大的情况会走索引，在表记录不多时会全表扫描）；
9. like KK%一般情况都会走索引；
注意：
10. 为什么范围查找时MySQL并没有索引下推优化？
估计应该是MySQL认为范围查找过滤的结果集过大的缘故。like KK% 在绝大时候，过滤掉的结果集比较小，所以这里MySQL选择给like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。
五、MySQL的内部组结构
Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL大部分核心服务功能，以及所有的内置函数(如日期、时间、数字、加密函数)，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、试图等。
Store层(引擎层)：负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的是InnoDB引擎。
1. 查询缓存，在MySQL5.8版本正式移除了；
2. bin-log归档：
六、常见SQL深入优化
1. order by 与 group by 优化
Explain分析sql语句后，关注Extra列的值。优化总结：
2. 索引设计原则
- 代码先行，索引后上；
- 联合索引尽量覆盖条件，尽量少建单值索引，可以设计一个或两三个联合索引，让每一个联合索引都尽量去包含sql语句中的where、order by、group by的字段，还要确保这些字段的顺序尽量满足sql查询的最左特性原则；
- 长字符串可以采用前缀索引；
- where与order by冲突时优先where。
3. 分页查询优化（根据非主键字段排序的分页查询）
-- 优化前
EXPLAIN SELECT * FROM employees ORDER BY name LIMIT 90000,5;
-- 优化后
EXPLAIN
SELECT * FROM employees e1
inner join (SELECT id FROM employees ORDER BY name LIMIT 90000,5) e2
ON e1.ID = e2.ID;优化的关键是：让排序时返回的字段尽可能的少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。
4. join关联查询优化
5. in 和 exist优化
七、阿里巴巴MySQL规范手册
八、并发事务
为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。
1. 并发事务带来的4个问题（见讲义）；
2. 事务隔离级别；
3. 锁详解；
4. InnoDB与MyISAM引擎最大的不同是什么？（面试题）
- InnoDB支持事务；
- InnoDB支持行级锁，而MyISAM引擎只有表级锁；
- MyISAM引擎在执行查询语句select之前，会自动给涉及的所有表加读锁；但在执行select语句时(非串行化隔离级别)，则不会加锁。（详见讲义中的笔记）
5. 什么是MySQL的MVCC机制？
MySQL的默认隔离级别是可重复读，可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作不会更新版本号，是快照读(历史版本)，insert、update、delete会更新版本号，是当前读(当前版本)。