mysql运行原理笔记-成本优化

基于成本的优化

什么是成本

我们之前老说MySQL在执行一个查询时可以有不同的执行方案。它会选择其中成本最低，或者说代价最低的那种方案去真正地执行查询。不过我们之前对成本的描述是非常模糊的，其实一条查询语句在MySQL中的执行成本是由两个方面组成的。

IO成本，我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引存储到磁盘上。当查询表中的记录时，需要先把数据或者索引加载到内存中，然后再进行操作。这个从磁盘到内存的加载过程损耗的时间称为IO成本。
CPU成本，读取记录以及检测记录是否满足对应的搜索条件，对结果集进行排序等这些操作损耗的时间称为CPU成本。

代价模型将操作分为Server层和Engine（存储引擎）层两类，Server层主要是CPU代价，Engine层主要是IO代价，比如MySQL从磁盘读取一个数据页的代价io_block_read_cost为1，计算符合条件的行代价为row_evaluate_cost为0.2。除此之外还有：

memory_temptable_create_cost (default 2.0) 内存临时表的创建代价。
memory_temptable_row_cost (default 0.2) 内存临时表的行代价。
key_compare_cost (default 0.1) 键比较的代价，例如排序。
disk_temptable_create_cost (default 40.0) 内部myisam或innodb临时表的创建代价。
disk_temptable_row_cost (default 1.0) 内部myisam或innodb临时表的行代价。

对于InnoDB存储引擎来说，页是磁盘和内存之间进行交互的基本单位。读取一个页面花费的成本默认是1.0；读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称为成本常数，这两个成本常数最常用到，其余的成本常数会在后面说。

需要注意的是，在读取记录时，即使不需要检测记录是否符合搜索条件，其成本也算作0.2

单表查询的成本

基于成本的优化步骤

在真正执行一条单表查询语句之前，MySQL的优化器会找出所有可以用来执行该语句的方案，并在对比这些方案之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询。这个过程总结一下就是下面这样的。
1、根据搜索条件，找出所有可能使用的索引。
2、计算全表扫描的代价。
3、计算使用不同索引执行查询的代价。
4、对比各种执行方案的代价，找出成本最低的那个方案。
下面以一个实例来分析一下这些步骤。单表查询语句如下：

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';

1、根据搜索条件，找出所有可能使用的索引。
对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=或者LIKE操作符连接起来，就会产生一个扫描区间，也就是说，这些搜索条件都可能使用到索引。MySQL把一个查询中可能用到的索引称之为possible keys。
我们分析一下上面的查询语句中涉及的几个搜索条件。

key1 IN (‘a’,‘b’,‘c’)：这个搜索条件可以使用二级索引idx_key1。
key2 > 10 AND key2 < 1000：这个搜索条件可以使用二级索引uk_key2。
key3 > key2：这个搜索条件的索引列由于没有与常数进行比较，因此不能产生合适的扫描区间。
key_part1 LIKE ‘%hello%’：key_part1通过LIKE操作符与以通配符开头的字符串进行比较，不能产生合适的扫描区间。
common_field = ‘123’：由于压根没有在该列上建立索引，所以不会用到索引。

综上所述，上面的查询语句可能用到的索引（也就是possible keys）有idx_key1和uk_key2。

2、计算全表扫描的代价。
对InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次与给定的搜索条件进行比较，并把符合搜索条件的记录加入到结果集中。所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=IO成本+CPU成本，所以在计算全表扫描的代价时需要两个信息：

聚簇索引占用的页面数
该表中的记录数

show table status like 'single_table';

Rows：表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的；对于使用InnoDB存储引擎的表来说，该值是一个估计值。
Data_length：表示表占用的存储空间字节数。对于使用MyISAM存储引擎来说，该值就是数据文件的大小；对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以按照下面的公示来计算该值的大小：
Data_length = 聚簇索引的页面数量*每个页面大小

聚簇索引的页面数量 = 1589248 / 16 / 1024 = 97
现在已经得到了聚簇索引占用的页面数量以及该表记录值以及该表记录数的估计值，接下来就可以计算全表扫描成本了。但是MySQL在真正计算成本时会进行一些微调，这些微调的值是直接硬编码到代码中的。现在看一下全表扫描成本的计算过程。

IO成本：97*1.0+1.1 = 98.1 97指的是聚簇索引占用的页面数，1.0指的是加载一个页面的成本常数，后边1.1是一个微调值。
CPU成本：9693*0.2+1.0 = 1939.6 9693指的是统计数据中表的记录数，对于InnoDB存储引擎来说这是一个估算值；0.2指的是访问一条记录所需的成本常数，后边的1.0是一个微调值。

综上所述：全表扫描所需的总成本就是：98.1 + 1939.6 = 2037.7。

前文说过，完整的用户记录其实都存在聚簇索引对应的B+树的叶子节点中，所以我们只要通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描的过程中，其实有的B+树的内节点是不需要访问的，但是MySQL在计算全表扫描成本的时候，直接使用聚簇索引占用的页面操作作为计算IO成本的依据，并没有区分内节点和叶节点。

3、计算使用不同索引执行查询的代价。
在前面的根据搜索条件，找出所有可能使用的索引中知道，前述查询可能使用到idx_key1和uk_key2这两个索引，我们需要分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。这里需要注意的是MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本。所以我们也先分析uk_key2的成本，然后再看使用idx_key1的成本。
（1）、使用uk_key2执行查询的成本分析
uk_key2对应的搜索条件是key2 > 10 AND key2 < 1000，也就是说对应的扫描区间就是(10, 1000)。使用uk_key2执行查询的示意图如下所示：
mysql-cost-note-003.drawio
对于使用二级索引+回表方式执行的查询，在计算这种查询成本时，依赖两方面的数据：扫描区间数量和需要回表的记录数。

扫描区间数量
无论某个扫描区间的二级索引到底占用了多少页面，查询优化器粗暴地认为读取索引的一个扫描区间的IO成本与读取一个页面的IO成本是相同的。本例中使用uk_key2的扫描区间只有一个：(10,1000)，所以相当于访问这个扫描空间的二级索引所付出的IO成本就是1*1.0 = 1.0。
需要回表的记录数
查询优化器需要计算二级索引的某个扫描空间到底包含多少条记录，对于本例来说就是要计算uk_key2在（10,1000）扫描区间中包含多少二级索引记录。计算过程是这样的。
步骤1.先根据key2>10条件访问uk_key2对应的B+树索引，找到满足key2>10条件的第一条记录，在B+树中定位一条记录的过程是非常快的，是常数级别的，所以这个过程的性能消耗可以忽略不计。
步骤2.然后再根据key2<1000条件继续从uk_key2对应的B+树索引中找出最后一条满足这个条件的记录，这个过程也可以忽略不计。
步骤3.如果区间最左记录和区间最右记录相隔不太远（在MySQL5.7.22版本中，只要间隔不大于10个页面即可），就可以精确地统计出满足key2>10 AND key2<1000条件的二级索引记录的条数。
否则只沿着区间最左记录向右读10个页面，计算每个页面平均包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了：怎么估计区间最左记录和区间最右记录之间有多少个页面呢？要解决这个问题，还得回到B+树索引结构中来，如下图所示：

假设上图区间最左记录在页b中，区间最右记录在页c中，那么要计算出区间最左记录和区间最右记录直接的页面数量，就相当于计算页面b和页面c之间有多少页面。而每一条目录项记录都对应一个数据页，所以计算页b和页c之间有多少页面就相当于计算他们的父节点（也就是页a）中对应的目录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本就相当低了。
不过还有问题：如果页b和页c之间的页面实在太多，以至于页b和页c对应的目录项记录都不在一个页面中则需要继续递归，也就是再统计页b和页c对应的目录项记录所在页之间有多少个页面。
知道了如何统计二级索引某个扫描区间的记录数之后，就需要回到现实中来。根据上述统计二级索引扫描区间的记录数之后测得uk_key2在区间（10,1000）中大约有95条记录，读取这95条二级索引记录需要付出的CPU成本就是95*0.2+0.01 = 19.01。其中95是需要读取的二级索引的记录条数，0.2是读取一条记录的成本常数，0.01是微调值。
在通过二级索引获取到记录之后，还需要干两件事。
根据这些记录的主键值到聚簇索引中执行回表操作。
这里每次回表操作都相当于访问一个页面，也就是说二级索引扫描区间中有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面IO。在使用uk_key2二级索引执行查询时，预计有95条二级索引需要进行回表操作，所以回表操作带来的IO成本就是95*1.0 = 95.0.其中95是预计的二级索引记录数，1.0是读取一个页面的IO成本常数。
回表操作后得到完整的用户记录，然后再检测其他搜索条件是否成立。
回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除key2>10 AND key2<1000这个搜索条件以外的其他搜索条件是否成立。因为我们通过扫描区间获取到的二级索引记录共有95条，这也就对应着聚簇索引中95完整的用户记录。读取并检测这些完整的用户记录是否符合其余的搜索条件的CPU成本为95*0.2 = 19.0.其中95是待检测记录的条数，0.2是检测一条记录是否给定搜索条件的成本常数。
所以uk_key2执行查询的成本如下所示：
IO成本：1.0+95*1.0 = 96.0（扫描区间的数量+预估的二级索引记录的条数）。
CPU成本：95*0.2+0.01+95*0.2 = 38.01（读取二级索引记录的成本+读取并检测回表操作后聚簇索引记录的成本）

综上所述，使用uk_key2执行查询的总成本就是96.0+38.01 = 134.01.

（2）使用idx_key1执行查询的成本分析
idx_key1对应的搜索条件是key1 IN (‘a’,‘b’,‘c’)，也就是说相当于3个单点扫描区间：

[‘a’,‘a’]
[‘b’,‘b’]
[‘c’,‘c’]

使用idx_key1执行查询的示意图如下图所示：
mysql-cost-note-004.drawio
与使用uk_key2的情况类似，我们也需要计算使用idx_key1时需要访问的扫描区间的数量以及需要回表的记录数。

扫描区间的数量
在使用idx_key1执行查询时，很显然有3个单点扫描区间，所以访问这三个扫描区间的二级索引付出的IO成本就是3*1.0 = 3.0。
需要回表的记录数
由于在使用idx_key1时存在3个单点扫描区间，所以每个单点扫描区间都需要查找一遍对应的二级索引记录数。
查找单点扫描区间[‘a’,‘a’]对应的二级索引记录数：计算单点扫描区间对应的二级索引记录数与计算范围扫描区间对应的二级索引记录数是一样的，都是先找到区间最左记录和区间最右记录，然后再计算它们之间的记录数。最后计算得到的单点扫描区间[‘a’,‘a’]对应的二级索引记录数是35。
查找单点扫描区间[‘b’,‘b’]对应的二级索引记录数：与上同理，计算得到的本单点扫描区间对应的记录数是44。
查找单点扫描区间[‘c’,‘c’]对应的二级索引记录数：与上同理，计算得到的本单点扫描区间对应的记录数是39。

三个单点扫描区间总共需要回表的记录数就是35+44+39 = 118.读取这些二级索引记录的CPU成本就是118*0.2+0.01 = 23.61

根据这些记录中的主键值到聚簇索引中执行回表操作：所需的IO成本就是118*1.0 = 118.0.
针对回表操作后读取到的完整的用户记录，比较其他的条件是否成立。这一步骤对应的CPU成本就是118*0.2 = 23.6

所以本例中使用Idx_key1执行查询的成本如下所示。

IO成本：3.0 + 118*1.0 = 121.0（扫描区间的数量+预估的二级索引记录条数）。
CPU成本：118*0.2 + 0.01 + 118*0.2 = 47.21（读取二级索引记录的成本+读取并检测回表操作后聚簇索引记录的成本）
综上所述，使用idx_key1执行查询的总成本就是121.0 + 47.21 = 168.21
（3）、是否有可能使用索引合并（Index Merge）
4、对比各种执行方案的代价，找出成本最低的那个方案。
全表扫描的成本：2037.7。
使用uk_key2的成本：134.01。
使用idx_key1的成本：168.21。

很显然，使用uk_key2的成本最低，所以当然选择uk_key2来执行查询。

在使用range访问方法执行查询时，扫描区间中包含多少条记录，优化器就认为需要进行多少次回表操作，也就相当于需要进行多少次页面IO。不过对于ref访问方法来说，MySQL在计算因回表操作带来的IO成本时设置了上限，也就是ref访问方法因回表操作带来的IO成本最多不能超过相当于访问全表记录数的1/10个页面的IO成本或者全表扫描的IO成本的3倍。之所以设置了这个上限，是因为在使用ref访问方法时，需要扫描的二级索引记录的id值离得很近，一次回表操作可能将多条需要访问的聚簇索引记录都从磁盘中加载到了内存。也就是说，即使在range访问方法中与在ref访问方法中需要扫描的记录数相同，ref访问方法也更有优势。

基于索引统计数据的成本计算

有时在使用索引执行查询时会有许多单点扫描区间，使用IN语句就很容易产生非常多的单点扫描区间。比如下面这个查询

SELECT * FROM single_table WHERE key1 IN ('aa1','aa2','aa3',...,'zzz');

很显然，这个查询可能使用到的索引就是idx_key1。由于这个索引并不是唯一二级索引，所以并不能确定一个单点扫描区间内对应的二级索引记录的条数有多少，需要我们去算一下。计算方式前文已经介绍过了，就是先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少条记录。MySQL把这种通过直接访问索引对应的B+树来计算某个扫描区间内对应的索引记录条数的方式称为index dive。

SHOW INDEX FROM single_table;

Table：该列所属索引所在的表的名称。
Non_unique：该列所属索引是否是唯一索引。对于聚簇索引和唯一二级索引来说，Non_unique的值为0；对于普通的二级索引来说，Non_unique的值为1
Key_name：该列所属索引的名称。如果是聚簇索引的话，key_name为PRIMARY。
Seq_in_index：该列在索引包含的列中的位置，从1开始计数。比如对于联合索引idx_key_part来说，key_part1、key_part2、key_part3对应的位置分别是1,2,3
Column_name：该列的名称。
Collation：该列中的值是按照哪种排序方式存放的。Collation为A时代表升序存放；Collation为NULL时代表不排序。
Cardinality：该列中不重复值的数量。对于联合索引来说，Cardinality表示从索引列的第一列开始，到本列为止的列组合不重复的数量。
Sub_part：对于存储字符串或者字符串列来说，有时只想对这些字符串前n个字符或字节建立索引，Sub_part表示的就是这个n。如果对完整的列建立索引，Sub_part的值就是NULL。
Packed：该列如何被压缩，NULL值表示未被压缩。
NULL：该列是否允许存储NULL值。
index_type：该列所属索引的类型。
Comment：该列索引的一些额外信息。
Index_comment：创建索引时，使用COMMENT语句为该索引添加的注释信息。

Cardinality在中文中是基数的意思，表示某个列中不重复的值的个数。如果Cardinality属性是1，就意味着该列的值全部都是重复的。需要注意的是，对于InnoDB存储引擎来说，使用SHOW INDEX语句显示出来的某个列的Cardinality属性是一个估计值，并不精确。
前文讲到，当IN语句中对应的单点区间数量大于等于系统变量eq_range_index_dive_limit的值时，就不会使用index dive来计算各个单点区间对应的索引记录条数，而是使用索引统计数据（index statistics）。这里的索引统计数据指的是下面这两个值。

使用SHOW TABLE STATUS语句显示出来的Rows值：表示一个表中有多少条记录。
使用SHOW INDEX语句显示出来的Cardinality属性。

结合Rows统计数据，我们可以计算出在某一列中一个值平均重复多少次。一个值的重复次数大约等于Rows除以Cardinality的值。
以single_table表的idx_key1索引为例，Rows值是9683，key1列的Cardinality值是968，所以可以计算key1列单个值的平均重复次数：9693/968≈10条。
此时再看本节最开始的那条查询语句：

SELECT * FROM single_table WHERE key1 IN ('aa1','aa2','aa3',...,'zzz');

假设IN语句对应着20000个单点扫描区间，就直接使用统计数据来估算这些单点扫描区间对应的记录条数了。每个单点扫描区间大约对应着10条记录，所以总共需要回表的记录数就是20000 * 10 = 200000。
使用统计数据来计算单点扫描区间对应的索引记录条数可比index dive方式简单多了，但是它的致命弱点就是不准确！使用统计数据算出来的查询成本与实际执行时的成本可能相差很大。

连接查询的成本

条件过滤（Condition Filtering）

在MySQL中连接查询采用的嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次。所以，对于两表连接查询来说，它的查询成本由两部分构成：

单次查询驱动表的成本
多次查询被驱动表的成本（具体查询多少次取决于针对驱动表查询后的结果集中有多少条记录）。

我们把查询驱动表后得到的记录条数称为驱动表的扇出（fanout）。显然，驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所需的成本时，就需要计算出驱动表的扇出值。有时扇出值的计算是很容易的，比如下面这两个查询。

查询1：

SELECT * FROM s1 INNER JOIN s2;

假设使用s1表作为驱动表，很显然就只能使用全表扫描的方式对驱动表执行单表查询。驱动表的扇出值也很明确，那就是驱动表中有多少条记录，扇出值就是多少。前面说过，统计数据中s1的记录行数是9693，也就是说优化器直接会把9693当作s1表的扇出值。

查询2：

SELECT * FROM s1 INNER JOIN s2 WHERE s1.hey2 > 10 AND s1.key2 < 1000;

仍然假设s1表是驱动表，很显然可以使用uk_key2索引对驱动表执行单表查询。此时uk_key2的扫描区间(10,1000)中有多少条记录，那么扇出值就是多少。我们前面计算过，满足uk_key2的扫描区间(10, 1000)的记录数是95条，也就是说在本查询中优化器会把95当作驱动表s1的扇出值。

查询3：

SELECT * FROM s1 INNER JOIN s2 WHERE s1.common_field > 'xyz';

查询3和查询1类似，只不过在查询驱动表s1时多了一个common_field > 'xyz’的搜索条件。优化器又不会真正地执行查询，所以它只能猜这9693条记录中有多少条记录满足common_field > 'xyz’条件。

查询4：

SELECT * FROM s1 INNER JOIN s2 WHERE s1.key2 > 10 AND s1.key2 < 1000 AND s1.common_field > 'xyz';

查询4和查询2类似，只不过在查询驱动表s1时也多了一个common_field > 'xyz’的搜索条件。不过因为查询4可以使用uk_key2索引，所以只需要在二级索引扫描区间的记录中猜测有多少条记录符合common_field > 'xyz’条件，也就是只需要在95条记录中猜测有多少条符合common_field > 'xyz’条件即可。

查询5：

SELECT * FROM s1 INNER JOIN s2 WHERE s1.key2 > 10 
               AND s1.key2 < 1000 
               AND s1.key1 IN ('a','b','c') 
               AND s1.common_field > 'xyz';

查询5和查询2类似，不过在对驱动表s1选取uk_key2索引执行查询后，查询优化器需要在二级索引扫描区间的记录中猜测有多少条记录符合下面两个条件：
key1 IN (‘a’,‘b’,‘c’)
common_field > ‘xyz’
也就是优化器需要在95条记录中猜测有多少条记录符合上述两个条件。

计算驱动表扇出值的两种情况，需要靠猜测。

如果使用全表扫描的方式执行单表查询，那么计算驱动表扇出值需要猜测满足全部搜索条件的记录到底有多少条。
如果使用索引来执行单表查询，那么计算驱动表扇出值时需要猜测除了满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条。

两表连接的成本分析

连接查询的成本计算公式是这样的：
连接查询总成本=单次访问驱动表的成本+驱动表扇出值*单次访问被驱动表的成本
对于左连接和右连接查询来说，它们的驱动表是固定的，所以只需要分别为驱动表和被驱动表选择成本最低的访问方法，就可以得到最优的查询方案。
可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，因此需要考虑两个方面的问题：

当不同的表作为驱动表时，最终的查询成本可能不同，也就是需要考虑最优的表连接顺序
然后分别为驱动表和被驱动表选择成本最低的访问方法

很显然，计算内连接查询成本的方式更麻烦一些。下面就以内连接为例来看看如何计算出最优的连接查询方案。
比如，对于下面这个查询来说：

SELECT * FROM s1 INNOR JOIN s2 ON s1.key1 = s2.common_field WHERE s1.key2 > 10 AND s1.key2 <1000 AND s2.key2 >1000 AND s2.key2 < 2000;

可以选择的连接顺序有两种：

s1连接s2，即s1作为驱动表，s2作为被驱动表。
s2连接s1，即s2作为驱动表，s1作为被驱动表。

优化器需要分别考虑这两种情况下的查询成本，然后选取成本更低的那个连接顺序，以及该连接顺序下各个表的最优访问方法作为最终的执行计划。
1、使用s1作为驱动表
分别针对驱动表的成本最低的执行方案，看一下涉及s1这一单表的搜索条件有哪些。

s1.key2>10 AND s1.key2<1000
这个查询可能使用uk_key2索引。从全表扫描与使用uk_key2这两个方案中选出成本最低的那个。很显然使用uk_key2执行查询成本更低。
然后分析针对被驱动表的成本最低的执行方案，此时涉及被驱动表s2的搜索条件如下。
s2.common_field=常数
s2.key2>1000 AND s2.key2<2000

很显然，在第一个条件中，由于common_field没有用到索引，所以并没有什么用。此时用来访问s2表的可用方案也是使用全表扫描和使用uk_key2这两种，很显然使用uk_key2的成本更低。
所以，此时使用s1作为驱动表的成本如下：
使用uk_key2访问s1的成本 + s1的扇出值 * 使用uk_key2访问的s2的成本
2、使用s2作为驱动表
分析针对驱动表的成本最低的执行方案，看一下涉及s2这一单表的搜索条件有哪些。

s2.key2>1000 AND s2.key2<2000
这个查询可能使用uk_key2索引。从全表扫描与使用uk_key2这两个方案中选出成本最低的那个。很显然使用uk_key2执行查询的成本更低。
然后分析针对被驱动表的成本最低的执行方案，此时涉及被驱动表s1的搜索条件如下。
s1.key1 = 常数
s1.key2 > 10 AND s1.key2 < 1000

这就很有趣了，使用idx_key1时可以使用ref访问方法，使用uk_key2时可以使用range访问方法。这时优化器需要从全表扫描，使用idx_key1、使用uk_key2这几个方案中选出一个成本最低的方案。这里假设使用idx_key1来访问s1。
此时使用s2作为驱动表时的总成本如下：
使用uk_key2访问s2的成本 + s2的扇出值 * 使用idx_key1访问s1的成本
最后，优化器会从这两种连接顺序中选出成本最低的那种真正的执行查询。从上面的计算过程中也可以看出来，在连接查询的成本中占大头的其实是驱动表扇出树 * 单次访问被驱动表的成本，所以我们的优化重点就是下面这两点。

尽量减少驱动表的扇出
访问被驱动表的成本要尽量低

在实际书写连接查询语句时，第二点十分有用。我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低被驱动表的访问成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降至更低了。

多表连接的成本分析

在分析多表连接的成本之前，首先要考虑多表连接可能会生成多少种连接顺序：

对于两表连接，比如表A和表B连接，只有AB、BA这两种连接顺序。
对于三表连接，比如表A、表B、表C进行连接，有ABC、ACB、BAC、BCA、CAB、CBA这6种连接顺序。
对于四表连接，则会有4*\3*\2*\1 = 24种连接顺序
对于n表连接，则有n*(n-1)*(n-2)* …*1种连接顺序，就是n的阶乘。

在有n个表进行连接时，MySQL查询优化器需要计算每一种连接顺序的成本么？MySQL有一些方法来减少因计算不同连接顺序下的查询成本而带来的性能损耗。

提前结束某种连接顺序的成本评估
MySQL在计算各种连接顺序的成本之前，会维护一个全局变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过了当前最小的连接查询成本，那么压根就不对该连接顺序继续往下分析了。
系统变量optimizer_search_depth
为了防止无穷无尽地分析各种连接顺序的成本，MySQL提供了optimizer_search_depth系统变量。如果链接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对数量与optimizer_search_depth值相同的表进行穷举分析，很显然，该值越大成本分析越准确，也就越容易得到好的执行计划，但是消耗的时间也就越长；否则得到的就不是很好的执行计划，但是节省了连接成本的分析时间。

总结

在MySQL中，一个查询的执行成本是由IO成本和CPU成本组成的。对于InnoDB存储引擎来说，读取一个页面的IO成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2
在单表查询中，优化器生成执行计划的步骤一般如下。
步骤1、根据搜索条件，找出所有可能使用的索引。
步骤2、计算全表扫描的代价。
步骤3、计算使用不同索引执行查询的代价。
步骤4、对比各种执行方案的代价，找出成本最低的那个方案。

在优化器生成执行计划的过程中，需要依赖一些数据。这些数据可能是使用下面两种方式得到的。

index dive：通过直接访问索引对应的B+树来获取数据
索引统计数据：直接依赖对表或者索引的统计数据

为了更精确地计算连接查询的成本，对于内连接来说，为了生成成本最低的执行计划，需要考虑两方面的事情：

选择最优的表连接顺序
为驱动表和被驱动表选择成本最低的访问方法