mysql基于成本的优化(1)---mysql进阶（四十一)

前面我们说了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)