下面我们将讲述如何对mysql进行优化分析
一、mysql执行流程
mysql分为客户端、服务端、存储引擎
客户端
客户端一般是java应用，或者其他可视化连接工具
服务端
连接器：负责管理客户端的连接以及验证权限
分析器：做语法和此法的分析，查询哪张表，什么条件
优化器：生成执行计划
执行器：真正执行sql调用引擎接口返回数据. MYISAM：不支持事务，也不支持行级锁，但它会存储表的行数，所以查行数较快。
查询缓存：8.0之后废弃了，由于数据一旦变动，缓存都要失效，收益并不明显
mysql在做更新操作时，会写日志，它采用write-ahead-log，写数据之前先写日志。日志分为两个部分：redo log和bin log
左边是redolog的格式，环形的，只有4G空间，write point是写的点位，check point是擦的点位。整个流程是顺时针循环的，当写的点位达到擦的点位时，就不能再写入了，需要等待擦的点位顺时针移动移。
binlog是磁盘上的全量日志，只要磁盘足够大，binlog只管追加写。
mysql崩溃是如何恢复的？
由上图，假设要把1变成2，它涉及到一个两阶段提交，首先会记录一个redolog，这个阶段redolog是一个就绪阶段，并没有提交，接着记录一个binlog，binlog记录完成后，再把redolog变成commit的状态，数据就变更完成了。
这个流程中如果redo repare过中断了，是无法恢复的，因为binlog没有任何日志。如果是binlog完成了，后面崩溃了都可以恢复。
二、mysql优化
explain分析
例如：EXPLAIN select * from order_info where period = 12 order by modified desc limit 0,10
将会查出如下结果：
各字段解释
id: 要执行的id，id越大越先执行。
select_type: 查询类型，分为四种
SIMPLE：查询语句只有一个 select，没有其余子查询 PRIMARY：复杂查询最前面的 select 查询，是最外层的查询 SUBQUERY：from 关键字前的子查询 DERIVED：from 关键字后的子查询，也叫派生查询 UNION：在 union 查询中，第二个及其后面的 select 语句，都是 UNION类型
table: 这一列表示 explain 的一行正在访问哪个表
当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查 询。 当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
type: 表示join的类型
依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引 A：system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index B：const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描 C：eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref D：ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。 E：fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引 F：ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。 G：unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值 H：index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。 I：range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。 J：index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range K：index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。 L：all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
possible_keys: 查询可能使用到的索引都会在这里列出来
key: 查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
key_len: 用于处理查询的索引长度
如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。 留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度， 而排序和分组就算用到了索引，也不会计算到key_len中。
ref: 如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func
rows: 这里是执行计划中估算的扫描行数，不是精确值
filtered: 表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数
Extra: 额外信息，这个列可以显示的信息非常多，有几十种，常用的有
A：distinct：在select部分使用了distinc关键字 B：no tables used：不带from字句的查询或者From dual查询 C：使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接。即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表。 D：using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中 E：using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。 F：using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。 G：using sort_union，using_union，using intersect，using sort_intersection： using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集 using union：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集 using sort_union和using sort_intersection：与前面两个对应的类似，只是他们是出现在用and和or查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。 H：using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。 I：using where：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition J：firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于where字句含有in()类型的子查询。如果内表的数据量比较大，就可能出现这个 K：loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在in()类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个 除了这些之外，还有很多查询数据字典库，执行计划过程中就发现不可能存在结果的一些提示信息
优化方法一：强制使用索引进行查询（一般不使用，不同数据库数据分布不一样，不通用）
可能用到的索引是idx_period，实际使用的索引是idx_modified，由于数据库选错索引，导致了慢查询，我们加上force index(idx_period) 强制使用索引
select * from order_info force index(idx_period) where period = 12 order by modified desc limit 0,10;
但是Extra中可能出现：Using fileSort，这样会非常消耗硬盘资源，应该尽量避免
主键索引是存储整行数据的，其它的二级索引都是只存储索引和主键ID。
优化方法二：创建联合索引进行优化
针对语句 select * from order_info where period = 12 order by modified desc limit 0,1000;
当where条件和order by条件不是同一个字段的时候，建立一个联合索引idx_period_modified(period, modified)一般情况下会大大减少执行时间
alter table order_info add index idx_period_modified(period, modified)
优化方法三：尽量减少回表查询次数，比如深翻页场景
让我们查询的字段都在索引上，比如如下sql耗时为1.879秒（其中满足条件的数据量为25万左右）
select * from order_info where period = 12 order by modified desc limit 240000,1000;
我们使用join方式尽量减少回表查询次数，如下优化的sql耗时仅为0.11秒，提升了百倍性能。
SELECT * from (select id from order_info where period=12 ORDER BY modified desc limit 240000,1000) as temp join order_info where temp.id=order_info.id;
通过上面这种覆盖索引子查询然后回原表join的方式，优化了一个回表次数，回表是一个很耗费io的操作，数据库的优化其实都是io的优化。数据库是按页存储的，如果我们把数据都集中在一页，一次磁盘io把它全读进来，比多次io节省大量时间。
优化方法四：避免索引失效导致全表扫描
只有两种情况索引失效，一是它没有办法使用索引，二是使用索引不如不使用。
explain分析出来的sql里面的type如果是ALL则表示全表扫描了，它通常出现在以下几种情况：
1.该表非常小，执行表扫描比费心查找key要快。 这对于行数少于 10 且行长度较短的表很常见。
2.索引列 不存在 ON 或 WHERE 子句中
3.您正在将索引列与常量值进行比较，并且 MySQL 已经计算出（基于索引树）常量覆盖表的太大部分并且全表扫描会更快。
4.您正在通过另一列使用基数较低的key（许多行与key值匹配）。 在这种情况下，MySQL 假设通过使用key，它可能会执行许多key查找，并且表扫描会更快。
索引合并场景：
索引合并检索具有多个范围扫描的行并将它们的结果合并为一个。 此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。 合并可以产生其底层扫描的并集、交集或交并集。
下面是可以使用索引合并的场景：
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30; SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col; SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);
下图的执行计划type列，表示合并索引
请注意，所以合并算发具有如下两个限制：
如果您的查询有一个复杂的 WHERE 子句和深度 AND/OR 嵌套并且 MySQL 没有选择最佳计划，请尝试使用以下恒等转换分布术语
(x AND y) OR z => (x OR z) AND (y OR z) (x OR y) AND z => (x AND z) OR (y AND z)
2.索引合并不支持全文索引
优化方法五：order by 排序优化
本节描述 MySQL 何时可以使用索引来满足 ORDER BY 子句，无法使用索引时将使用fileSort文件排序操作，以及优化器提供的有关 ORDER BY 的执行计划信息，带和不带 LIMIT 的 ORDER BY 可能会以不同的顺序返回行数据。
使用索引满足排序
如果查询包含 GROUP BY 但您希望避免对结果进行排序，则可以通过指定 ORDER BY NULL 来抑制排序
如下所示：
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
优化器可能仍然选择使用排序来实现分组操作。 ORDER BY NULL 禁止对结果进行排序，而不是通过分组操作确定结果之前进行排序。
