本文共 3261 字，大约阅读时间需要 10 分钟。
深入 Mysql 查询
-
Mysql 客户端对于查询，有两种策略，一种是，查一行处理一行，另一种是无论处理逻辑，会将查询结果都缓存在本地，通常建议，非大查询的场景，都使用第二种，但大查询场景，要考虑内存是否能缓存太多；
-
大查询，如果使用第一种，查一行处理一行，查询结果首先写入 net_buffer，net_buffer写满，就会调用网络接口发送出去，所以大查询，不会占用 Mysql 服务端太多内存，如果客户端处理慢，会导致查询执行时间较长；通过 show processlist 命令，可以看到 State 的值一直处于 Sending to client 状态，就表示发送阻塞在客户端处理；
-
客户端连接 Mysql 时，可以添加 -A 参数，表示跳过缓存表名到本地的功能，这就无法使用表名自动补全，但会加速连接；如果添加 -quick 参数，不仅跳过缓存表名的操作，还使用查一行处理一行的策略，这种方式可以节省本地内存；
-
在 InnoDB 引擎层，因为 WAL 技术，数据大量都是从内存中查询返回的，并且内存满了，会涉及一个淘汰策略，LRU，即淘汰最久未使用的数据，并且还有一个改进，分成了 young 和 old，避免偶尔大查询的冷数据，占满了内存，降低了内存的命中率；
join 操作
-
select * from t1 straight_join t2 on (t1.a=t2.a)，其中 t1 称为 驱动表，表行数 M，t2 称为被驱动表，表行数 N，如果 t2 表的 a 字段有索引，Mysql 就会使用Simple Nested-Loop Join 算法来查找数据，如果 t2 的 a 字段没有索引，会使用 Block Nested-Loop Join 算法查找数据；
-
简单嵌套连接，首先 对表 t1 做全表扫描，对每一行数据取出 a 值，通过 t2 的索引树，找到匹配记录，这样组成结果集，总的扫描行数是 2M，时间复杂度是 M + M2*log2N，通常小表作为驱动表，因为 M 越小，时间复杂度越低；
-
如果是连接条件，无法用到被驱动表的索引，会使用阻塞嵌套连接查询，首先将驱动表数据，加载到内存中，然后将被驱动表数据每一行取到内存，判断连接条件是否成立，这种方式，总的扫描行数是 M + N，在内存需要判断 M * N 次，同样这种方法，也需要小表作为驱动表，因为内存大小有限；
-
建议，大表 join 操作，如果无法用到被驱动表的索引，尽量不使用 join，可以通过 explain 的结果 Extra 字段 判断是否出现 Block Nested Loop 来决定是否使用 join 操作；
-
join 操作，总是应该使用小表做驱动表，小表是指，连接的表按照各自的条件过滤，过滤完成之后，参与 join 的各个字段的总数据量小的表；
-
对于被驱动表的连接字段上没有索引，导致查询效率低，一种方式，可以直接建索引，另一种情况，是大表建索引浪费，且该 sql 比较低频使用，这种情况可以使用临时表；
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000; // 使用临时表，并且 engine 可以选择 memory create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb; insert into temp_t select * from t2 where b>=1 and b<=2000; select * from t1 join temp_t on (t1.b=temp_t.b);
- 还有一种方式，在业务应用中，构建哈希结构存放临时表，使用哈希查找，效率高；
left join
-
left join 左连接，表示在 join 的基础上，左表中的数据，即使右表没有满足匹配条件的记录，查询结果中也会返回一行，并将右表中的各个字段值填成 NULL；
-
通常 left join 是用左边作为驱动表，右边作为被驱动表，根据右边连接字段是否有索引，来选择匹配算法，但也不绝对，也可能经过优化器之后，left join 优化为 join；
// a 表作为驱动表 select * from a left join b on(a.f1=b.f1) and (a.f2=b.f2); // 使用的是 b 表作为驱动表，因为 Null 和 任何值（包括null）做等值或者不等值判断都是 null，所以 left join 优化为了 join； select * from a left join b on(a.f1=b.f1) where (a.f2=b.f2);
- join 语句中，匹配条件放在 where 子句和 on 子句中是没有区别的，如果想要 left join 的语意，匹配条件要放在 on 里面，不能放在 wherer 子句中，在示例中，第二个语句不会得到 a 表有，而 b 表没有的数据；
临时表
-
临时表是指，只能被创建它的 session 访问，在 session 结束时，会自动删除的表，建表语句是 create temporary table …，注意，临时表不一定是内存表，也可以选择不同的存储引擎，临时表可以和普通表重名，重名会优先操作临时表；
-
临时表，可以应用在 join 优化 和 分库分表的跨库查询的使用场景中；
-
join 优化，是通过将被驱动表按条件过滤后的数据，插入到一个临时表，在临时表关联字段上建立索引，这样就可以用到简单嵌套连接算法；
-
分库分表的跨库查询，因为无法在单库上使用 join，group by，order by 等操作，所以有两种解决方案，一种是，每个分库取到数据后，在内存中进行计算，另一种是，每个分库取到数据后，汇总到一个临时表中，在临时表中完成操作；
-
在 binlog_format='row’的时候，临时表的操作是不记录到 binlog 中，省去了主备同步的一些麻烦；
内部临时表
-
Mysql 在执行某些 Sql 语句，如果无法在读数据的过程中直接得到结果，需要使用额外内存来保存中间结果，就会建立内部临时表，如，union，group by，distinct 等语句；
-
union 操作，是取两个子查结果的并集，并且重复的行只保留一行，union all 就不需要去重，因为 union 是无法在读数据时直接去重，所以需要用到临时表，而 union all 就不需要临时表了；
-
group by 操作，如果分组字段有序，即建立了索引，就可以边读数据边得到结果，不需要使用临时表，如果分组字段无序，就需要用到临时表，通过一个字段来累加计数，且需要排序，因为 group by 操作，默认是按照分组结果有序返回的，如果不需要有序，添加 order by null，可以提高查询效率；
-
explain 命令，如果显示 using temporary，就表示使用了临时表，using filesort，就表示使用了排序；
Memory 引擎
-
Memory 引擎，数据和索引是单独存放，支持哈希索引，每个索引值保存数据的位置，InnoDB 引擎，数据是存放在主键索引的叶子节点，其他索引上保存的是主键的值；所以 Memory 引擎的数据是按照插入顺序存放的，而 InnoDB 引擎的数据是有序存放的；
-
Memory 引擎的表，称为内存表，数据保存在内存中，数据是数组存放，每个字段固定长度存放，哈希索引是无法用到范围查找的，需要全表扫描，不过内存表也支持创建 b-tree 索引；
-
内存表，不支持行锁，只支持表锁，所以其对并发访问不太友好；另外内存表，在数据库重启后，数据会丢失，不太适合生产上作为普通数据表来使用，所以一般用作临时表中，作为临时内存表；
转载地址：http://isfin.baihongyu.com/