mysql 表查询遍满_MySQL的查询优化

1.对于一个SQL语句，查询优化器先看是不是能转换成JOIN，再将JOIN进行优化

优化分为：1. 条件优化，2.计算全表扫描成本，3. 找出所有能用到的索引，4. 针对每个索引计算不同的访问方式的成本，5. 选出成本最小的索引以及访问方式。

2.查询优化实例

1)常量传递

a = 1 AND b > a

上面这个sql可以转换为：

a = 1 AND b > 1

2)等值传递

a = b and b = c and c = 5

上面这个sql可以转换为：

a = 5 and b = 5 and c = 5

3)基于成本

一个查询可以有不同的执行方案，可以选择某个索引进行查询，也可以选择全表扫描，查询优化器会选择其中成本最低的方案去执行查询。

4)I/O成本

InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

5)CPU成本

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。

3.计算全表扫描的代价

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

1. 聚簇索引占用的页面数

2. 该表中的记录数

MySQL为每个表维护了一系列的统计信息， SHOW TABLE STATUS 语句来查看表的统计信息。

SHOW TABLE STATUS like 'menu';

Rows：表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。

Data_length:表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：Data_length = 聚簇索引的页面数量 x 每个页面的大小

4.使用in关键字的查询：

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

select * from employees.titles where to_date in ('a','b','c','d', ..., 'e');

很显然，这个查询可能使用到的索引就是idx_titles_to_date，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。

MySQL提供了一个系统变量eq_range_index_dive_limit，我们看一下这个系统变量的默认值：SHOW VARIABLES LIKE '%dive%'; 为200。

如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算。像会为每个表维护一份统计数据一样，MySQL也会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用 SHOW INDEX FROM 表名 的语法。

IN子查询的优化：

mysql对IN子查询进行了优化。

比如：

mysql> select * from t1 where a in (select a from t2);

在mysql中，不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：

该临时表的列就是子查询结果集中的列。

写入临时表的记录会被去重。

一般情况下子查询结果集不会特别大，所以会为它建立基于内存的使用Memory存储引擎的临时表，而且会为该表建立哈希索引。IN语句的本质就是判断某个操作数在不在某个集合里，如果集合中的数据建立了哈希索引，那么这个匹配的过程就是很快的。

如果子查询的结果集非常大，超过了系统变量tmp_table_size或者max_heap_table_size，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为B+树索引。

这个将子查询结果集中的记录保存到临时表的过程称之为物化(Materialize)。那个存储子查询结果集的临时表称之为物化表。正因为物化表中的记录都建立了索引(基于内存的物化表有哈希索引，基于磁盘的有B+树索引)，通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

5.关于JOIIN的优化：

内连接：以下三种写法都是内连接：

mysql> select * from t1 join t2 on t1.a =t2.a;

mysql> select * from t1 inner join t2 on t1.a =t2.a;

mysql> select * from t1 cross join t2 on t1.a = t2.a;

左连接：

mysql> select * from t1 left join t2 on t1.a = t2.a;

右连接：

mysql> select * from t1 right join t2 on t1.a = t2.a;

连接的原理：

不管是内连接还是左右连接，都需要一个驱动表和一个被驱动表，对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左连接的驱动表就是左边的那个表，右连接的驱动表就是右边的那个表。

连接的大致原理是：

1. 选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的访问形式来执行对驱动表的单表查询。

2. 对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

对应的伪代码就是：

for each row in t1 { //此处表示遍历满足对t1单表查询结果集的每一条记录for each row in t2 { //此处表示对于某条t1表的记录来说，遍历满足对t2表查询结果集中的每一条记录//判读是否符合join条件

}

}

嵌套循环连接：

上面的过程就像是一个嵌套的循环,所以这种驱动表只访问一次,但被驱动表却可能被多次访问,访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接(Nested-Loop Join),这是最简单,也是最笨拙的一种连接查询算法。

比如对于下面这个sql:

mysql> select * from t1 join t2 on t1.a = t2.a where t1.b in (1,2);

先会执行：

mysql> select * from t1 where t1.b in (1,2);

+---+------+------+------+------+

| a | b | c | d | e |

+---+------+------+------+------+

| 1 | 1 | 1 | 1 | a |

| 2 | 2 | 2 | 2 | b |

| 5 | 2 | 3 | 5 | e |

+---+------+------+------+------+

3 rows in set (0.00 sec)

得到三条记录。

然后分别执行：

mysql> select * from t2 where t2.a = 1;

mysql> select * from t2 where t2.a = 2;

mysql> select * from t2 where t2.a = 5;

所以实际上对于上面的步骤，实际上都是针对单表的查询，所以都可以使用索引来帮助查询。