前面我们说了join查询原理，最基本的是嵌套查询，这种不推荐，如果数据量庞大，因为内存是有限的，不能放下所有的数据，可能查询到后面的时候，前面的数据就从内存从释放，为了减少磁盘的查询次数，有了join buffer这个缓存区，专门放被驱动表的数据，用来匹配查询出来的驱动表数据是否符合，当然还是建议用索引来查询。
基于成本的优化
前面我们都说mysql优化器，每次查询数据库都会选择最低成本的方式访问，那么成本是什么呢？这里主要分为两类
I/O成本：我们查询表存储引擎是mySIMA和InnoDB都是从磁盘上查询数据的，然后把磁盘上的数据加载到内存里在获取。磁盘到内存这个过程就是I/O成本。
CPU成本：读取及检测数据是否满足过滤条件，如果没有用到索引排序，这时候对结果集进行排序都是会耗费CPU成本。
对于mysql中，规定读取一个页的成本是1.0，读取或者检测一条记录是否复合搜索条件的成本是0.2。这两个数称为成本常量，后面会经常用到。(注意：这里不管需不需要检测搜索条件，成本都是0.2)
单表查询成本
我们还是用之前建的single_table来讲解，对于一个查询sql语句，mysql先会计算出最小成本的查询方式，最后执行所谓的【执行计划】，之后调用存储引擎提供的真正接口访问。
根据过滤条件，找到所有可以使用的索引。
计算全表扫描大家。
计算不同索引扫描代价。
找出最低成本的进行执行计划。
下面我们来分析一下：
SELECT * FROM single_table WHERE key1 IN ('a', 'b', 'c') AND key2 > 10 AND key2 < 1000 AND key3 > key2 AND key_part1 LIKE '%hello%' AND common_field = '123';
根据搜索条件，找出所有可能使用的索引
Key1 in a,b,c可以使用到key1索引，
Key2 >10 and key2 < 1000 可以使用到key2二级索引，
Key3>key2，没有比较常量，无索引，
Key_part1 因为%在前面，无索引，
Common_field本身就无索引。
这种查询中可能会用到的索引称为possible keys，为什么说可能用到因为最后执行方法未必会执行这个索引，会采用性能最好的。
计算全表查询
对于innoDB表来说，全表查询就是直接查询聚簇索引中的记录，依次和给定的过滤条件做一下比较，然后把符合的条件放入结果集。所以全表扫描的成本=磁盘I/O+CPU成本，为了计算这两个信息，我们需要什么呢，我们前面说了一个页的成本查询是1.0，一条记录的查询成本是0.2，所以我们现在需要知道：
当前表存了多少数据页。
当前表有多少行数据。
那这两个数据从哪来？总不能计算出来吧，那样太耗费性能，mysql维护了一系列的统计数据来提高性能，我们可以用命令查看，主要注意data_length和rows：
mysql> SHOW TABLE STATUS LIKE 'single_table'\G *************************** 1. row *************************** Name: single_table Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 9693 Avg_row_length: 163 Data_length: 1589248 Max_data_length: 0 Index_length: 2752512 Data_free: 4194304 Auto_increment: 10001 Create_time: 2018-12-10 13:37:23 Update_time: 2018-12-10 13:38:03 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec)
rows:innodb表的rows是取大概值，而mySIAM表取的是真实的值，所以我们虽然有10000条数据，但大约是9693条。
data_length:如果在mySIAM该值表示数据文件大小，而innoDB表示聚簇索引存储空间大小。(因为innoDB是数据即是索引)
一个页大概16kb，那我们可以计算出多少页呢，1589248 / 16 / 1024 = 97个聚簇索引页。
所以I/O成本:97 * 1.0 +1.1 = 98.1
CPU成本为:9693 * 0.2 + 1.0 = 1939.6
后面加的1.1和0.2忽略不计。所以这里的全表成本为 98.1+1939.6 = 2037.7
3、计算不同索引查询代价
我们在前面的时候，有两个possible key，一个是key1一个是key2，mysql优化器是先查看唯一二级索引成本，再看普通索引成本，所以我们先分析key2唯一索引成本，再看key1普通索引成本，还要看看会不会使用索引合并。
Idx_key2执行成本：
Key2>10 and key2<1000，所以他们的范围在(10,1000)，使用二级索引+回表的方式。
范围区间数量：不论二级索引在某范围内占用多少页面，都认为与I/O读取一个页面是相同的，所以这里一个区间的成本是：1*1.0 =1.0
需要回表数量：
先看key2>10，这时候找到区间最左记录，我们这时候找的是常数级别，可以损耗忽略不计。
再看key2<1000，找到区间最右记录，损耗忽略不计。
如果区间最左记录数和区间最右记录数不是相差太远，那就很容易算出满足的>10 and <1000的数，否则只能沿着最右区间记录数读10个页面，计算平均每个页面包含多少记录，然后用这个平均值乘以区间最左记录 和区间最右记录之间的页面数就好。那么如何知道这之间包含多少页面数呢?只要看他们的根级的非叶子几点就好，非叶子节点一个数据就代表一个数据页，根据算法上面大概95 个数据页
所以是95 x 0.2 + 0.01 = 19.01
在通过二级索引获取到数据后，还需要干两件事
根据这些数据到聚簇索引做回表操作：mysql设定回表一次和I/O刷新数据到页的消耗是一样的，所以是95*1.0=95
回表获取到完整数量，再检测其他搜索条件是否成立：因为我们查询的是95条数据，而查询这95条数据是否成立则需要 95*0.2 = 19.0的CPU成本。
所以综上所述:成本 = i/o成本 + cpu成本 = 95 + 19 +19 = 133。
这里为什么加两个19呢，因为除了检测聚簇索引记录成本外还有读取二级索引记录成本也是 95 * 0.2 = 19
若使用idx_key1查询：
Key1 in a.b.c,对应三个区间，三个区间访问所以就是 3 * 1.0 = 3.0
需要回表的记录：根据区间最左记录和区间最右记录，分别求出三个区间的值[a,a] [b,b] [c,c] 分别为35，44，39。所以加起来等于118。所以这些CPU成本就是118*0.2+0.01 = 23.6
所以这些的I/O成本就是118*0.1= 118
所以成本 = 121.0 + 47.21 = 168.2
是否有可能使用索引合并(index merge)
这个sql使用and查询，所以可能用到intersection合并，但因为范围查询，不满足主键排序，所以不可以使用范围索引。
4、比较找出成本最低的
所以把上面三个比较之后，得出133的成本最低，所以选key2唯一索引。
基于索引的计算成本
如果用in语句来表示，会有很多区间，这时候计算区间有多少二级索引记录，先获取b+树区间最左记录和b+树区间最右记录，mysql吧这个过程叫做index dive。(索引俯冲？我感觉就是为了表示这个过程很快，不计入成本，反正是查sql之前需要操作的事务，不可能影响性能)
如果in语句里面零星放几个参数，每次都是一次index drive计算，这没什么，如果是大量的参数呢，那计算的成本可能超过了sql的成本，这时候怎么解决，这时候提供了系统变量eq_range_index_drive_limit：
mysql> SHOW VARIABLES LIKE '%dive%'; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | eq_range_index_dive_limit | 200 | +---------------------------+-------+ 1 row in set (0.05 sec)
从上面参数可以看到，当区间小于200的时候，用index drive，如果大于200，则要用索引统计数据，怎么统计呢？我们可以查看某个表的索引
mysql> SHOW INDEX FROM single_table; +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | single_table | 0 | PRIMARY | 1 | id | A | 9693 | NULL | NULL | | BTREE | | | | single_table | 0 | idx_key2 | 1 | key2 | A | 9693 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key1 | 1 | key1 | A | 968 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key3 | 1 | key3 | A | 799 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key_part | 1 | key_part1 | A | 9673 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key_part | 2 | key_part2 | A | 9999 | NULL | NULL | YES | BTREE | | | | single_table | 1 | idx_key_part | 3 | key_part3 | A | 10000 | NULL | NULL | YES | BTREE | | | +--------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 7 rows in set (0.01 sec)
可以看到很多属性：
Table：表名。
Non_unique:0代表唯一索引，1代表普通索引。
Key_name：索引名称。
Seg_in_index：从1开始，索引的位子。
Columns_name:列名称。
Collation:A代表升序，null代表降序。
Cardinality：代表当前索引存储的不重复的值。
Sub_part：对于列里前几个字符串创建索引，则该列显示那几个字符串，如果为全列创建，则为null。
Packed:索引如何被压缩，未被压缩为null。
Null:是否允许null。
Index_type:索引类型，这里就是我们常见的b+树，BTREE。
Comment:注解。
Index_commment:索引注解。
上面的大家除了packer看不懂外，其他应该都能理解，否则说明前面的文章没有看。而这里说cardinality直接翻译就是基数，表示索引值是否重复，对于10000万的表单数据，如果该值是10000，代表没有重复，如果是1，代表全部都是重复数据，不过需要注意的是，innoDB是一个估算值。
当in语句里面的区间大于eq_range_index_drive_limit值的话，就不会用index drive，索引统计这里主要有两个值：
看表的总rows。(show table status)
看cardinality值。(show index from table)
如果以idx_key1为例子，所以重复值 可以用 总行数/cardinality = 9693 / 968 =10；
如果in里面有20000个参数，每个参数有10条重复记录，所以回表记录是：20000 * 10 = 200000。
使用统计数据来计算单点区间比index drive方便多了，但缺点是不精确，算出来的查询成本可能和实际查询成本差距很大。(注意：mysql5.7.3之前eq_rang_index_drive_limit默认是10，之后版本改为默认200，索引很容易采用统计数据计算单区间，当没有采用索引查询，可能是这个值太小导致的)
