4.性能优化

1. 调优金字塔

架构调优：采用更适合业务场景的架构能最大程度地提升系统的扩展性和可用性。在设计中进行垂直拆分能尽量解耦应用的依赖，对读
压力比较大的业务进行读写分离能保证读性能线性扩展，而对于读写并发压力比较大的业务在 MySQL 上也有采用读写分离的大量案例。

MySQL 调优：需要确认业务表结构设计是否合理，SQL 语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引等等。

硬件和 OS 调优：使用什么样的磁盘阵列（RAID）级别、是否可以分散磁盘 IO、是否使用裸设备存放数据，使用哪种文件系统（目前比较推荐的是 XFS），操作系统的磁盘调度算法选择，是否需要调整操作系统文件管理方面比如 atime 属性等等。

2. 慢查询

慢查询是查询慢的日志，是指 mysql 记录所有执行超过 long_query_time 参数设定的时间阈值的 SQL 语句的日志，首先要开启慢查询日志功能。

2.1 慢查询优化考虑点

从两方面考虑：

1. 请求了不需要的数据？查询不需要的记录、总是取出全部列、重复查询相同的数据
2. 是否在扫描额外的记录？衡量查询开销的指标：响应时间、扫描的行数和返回的行数、扫描的行数和访问类型

2.2 重构查询的方式

1. 一个复杂查询还是多个简单查询
2. 切分查询：限制查询返回的数据量
3. 分解关联查询？更容易让缓存的效率更高、可以减少锁的竞争、更容易做到高性能和可扩展、减少冗余记录的查询

2.3 慢查询配置

show VARIABLES like ‘slow_query_log’;
set GLOBAL slow_query_log=1;
show VARIABLES like ‘%long_query_time%’
show VARIABLES like ‘%log_queries_not_using_indexes%’
set global log_output=‘FILE,TABLE’

2.4 慢查询分析

./mysqldumpslow -s t -t 10 /home/mysql/mysql57/data/iZwz9j203ithc4gu1uvb2wZ-slow.log 显示慢查询中前10条
./mysqldumpslow -s t -t 10 /home/mysql/mysql57/data/iZwz9j203ithc4gu1uvb2wZ-slow.log -g select 显示慢查询中前 10 条，只显示 select

2.5 Explain

执行计划的作用：
表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

EXPLAIN select * from order_exp;

1. table

该条记录的table列代表着该表的表名。

2. id

查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。

3. select_type

通过某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

SIMPLE：简单的select 查询，不使用 union 及子查询
PRIMARY：最外层的 select 查询
UNION：UNION 中的第二个或随后的 select 查询，不依赖于外部查询的结果集
UNION RESULT：UNION 结果集
SUBQUERY：子查询中的第一个 select 查询，不依赖于外部查询的结果集
DEPENDENT UNION：UNION 中的第二个或随后的 select 查询，依赖于外部查询的结果集
DEPENDENT SUBQUERY：子查询中的第一个 select 查询，依赖于外部查询的结果集
DERIVED：用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。
MATERIALIZED：物化子查询
UNCACHEABLE SUBQUERY：结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估，出现极少。
UNCACHEABLE UNION：UNION 中的第二个或随后的select 查询，属于不可缓存的子查询，出现极少。

4. partitions

和分区表有关，一般情况下我们的查询语句的执行计划的 partitions 列的值都是NULL。

5. type

这是重点关注的指标

执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法/访问类型，其中的 type 列就表明了这个访问方法/访问类型是什么，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

出现比较多的是：system > const > eq_ref > ref > range > index > ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

1. system
   表只有一行记录（等于系统表），这是const类型的特例。
   
2. const
   根据主键或者唯一二级索引列与常数进行等值匹配时。
   
3. eq_ref
   在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是eq_ref。

下面 MySQL 把 s2 作为驱动表，s1 作为被驱动表。

4. ref
   当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
   本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他属于查找和扫描的混合体
   

5. ref_or_null
   有时候我们不仅想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为 NULL 的记录也找出来，就像这个查询：SELECT * FROM order_exp WHERE idx_order_no= ‘abc’ OR idx_order_no IS NULL;

6. index_merge
   一般情况下对于某个表的查询只能使用到一个索引，在某些场景下可以使用索引合并的方式来执行查询
   

7. unique_subquery
   针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配。
   

8. index_subquery
   index_subquery 与 unique_subquery 类似，只不过访问⼦查询中的表时使⽤的是普通的索引。

9. range
   如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法，一般就是在你的where语句中出现了between、<、>、in等的查询。
   

10. index
    当可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。
    

11. all
    最熟悉的全表扫描，将遍历全表以找到匹配的行。
    

6. possible_keys与key

possible_keys 表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key 表示实际用到的索引有哪些，如果为NULL，则没有使用索引。

下面这种情况：没有可用的索引，但是用了索引，是因为 MySQL 分析发现查询的列和 where 是同一列，使用索引覆盖扫描更快。

7. key_len

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度。

8. ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const、eg_ref、ref、ref_or_null、unique_sutbquery、index_subopery其中之一时，ref 列展示的就是与索引列作等值匹配的是谁。

9. rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。

10. filtered

查询优化器预测有多少条记录满⾜其余的搜索条件。

11. Extra

Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。MySQL提供的额外信息很多，无法一一介绍，介绍一些比较重要的额外信息。

No tables used：当查询语句的没有 FROM 子句时将会提示该额外信息。
Impossible WHERE：查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息。
No matching min/max row：当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时，将会提示该额外信息。
Using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在 Extra 列将会提示该额外信息。
Using index condition：有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：
SELECT * FROM s1 WHERE order_no > ‘z’ AND order_no LIKE ‘%a’；MySQL 进行了改进，在查询语句的执行过程中使用了索引条件下推（Index Condition Pushdown）这个特性，在 Extra 列中将会显示 Using index condition。
Using where：
当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。

当使用索引访问来执行对某个表的查询，并且该语句的 WHERE 子句中有除了该索引包含的列之外的其他搜索条件时，在 Extra 列中也会提示上述信息。
Using filesort：
有一些情况下对结果集中的记录进行排序是可以使用到索引的，比如下面：

有些情况下排序不能使用索引，就需要用文件排序，可能使用内存、磁盘。

Using temporary：在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的。

Zero limit：当我们的 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息。
Using intersect(…)、Using union(…)和Using sort_union(…)：如果执行计划的 Extra 列出现了 Using intersect(…) 提示，说明准备使用Intersect 索引合并的方式执行查询，括号中的 … 表示需要进行索引合并的索引名称；如果出现了 Using union(…) 提示，说明准备使用Union 索引合并的方式执行查询；出现了 Using sort_union(…) 提示，说明准备使用 Sort-Union 索引合并的方式执行查询。
Start temporary, End temporary：有子查询时，查询优化器会优先尝试将IN子查询转换成semi-join，而semi-join又有好多种执行策略，当执行策略为DuplicateWeedout时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra列将显示End temporary提示。
LooseScan：在将In子查询转为semi-join时，如果采用的是LooseScan执行策略，则在驱动表执行计划的Extra列就是显示LooseScan提示。
FirstMatch(tbl_name)：在将In子查询转为semi-join时，如果采用的是FirstMatch执行策略，则在被驱动表执行计划的Extra列就是显示FirstMatch(tbl_name)提示。

3. 优化实例

3.1 count

从架构方面优化，使用估算、汇总表、Redis 缓存等优化。

3.2 limit

select * from order_exp limit 10000,10; MySQL 需要查询 10010 条记录然后只返回最后 10 条，前面 10 000 条记录都将被抛弃，这样的代价非常高。
select * from (select id from order_exp limit 10000,10) b,order_exp a where a.id = b.id; 效率会高一些。
select * from order_exp where id > 67 order by id limit 10; 推荐使用这种方式优化

4. 分区表

不建议使用。

CREATE TABLE test (
order_date DATETIME NOT NULL, ）ENGINE=InnoDB
PARTITION BY RANGE(YEAR(order_date))(
PARTITION p_0 VALUES LESS THAN (2010) ,
PARTITION p_1 VALUES LESS THAN (2011),
PARTITION p_2 VALUES LESS THAN (2012),
PARTITION p_other VALUES LESS THAN MAXVALUE );