前面我们说了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，索引很容易采用统计数据计算单区间，当没有采用索引查询，可能是这个值太小导致的)