关于MySQL的关联查询

一. 关联查询执行流程

总的来说，mysql认为任何一个查询都是一次关联，并不仅仅是一个查询需要用到两个表匹配才叫关联。

所以，在mysql中，每一个查询，每一个片段（包括子查询，甚至单表select）都可能是关联。

所以，理解mysql如何执行关联查询至关重要，先来看一个union的例子，对于union，mysql先将一系列的单个查询结果放到一个临时表中，

然后再重新读出临时表数据来完成union，在mysql的概念中，每个查询都是一次关联，所以读取结果临时表也是一次关联。

首先MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：

哪个表的结果集小，就以哪个表为驱动表，当然MySQL优化器实际的处理方式会复杂许多

 

如：

mysql在from子句中遇到子查询，先执行子查询并将结果放到一个临时表中，然后将这个临时表当作一个普通表对待（正如其名：派生表），

mysql在执行union查询时也使用类似的临时表，在遇到右外连接是，mysql将其改写成等价的左外连接，换而言之，当前版本的mysql会将所有的查询类型都换换成类似的执行计划，

不过，不是所有的查询都可以转换成上面的形式，如：全外连接就无法通过嵌套循环和回溯的方式完成，这也是mysql并不支持全外连接的原因。

 

执行计划：

和很多其他关系数据库不同,mysql并不会生成查询字节码来执行查询，mysql生成查询的一棵指令树，

然后通过存储引擎执行完成这棵指令树并返回结果，最终的执行计划包含了重构查询的全部信息，如对某个查询执行explain extended后，

再执行show warnings，就可以查看到重构的查询。 mysql总是从一个表开始一直嵌套循环，回溯完成所有表的关联。

所以，mysql的执行计划是一棵左侧深度优先的树，然后回溯到上一层关联：

 

关联查询优化器：

mysql优化器最重要的一部分就是关联查询优化，它决定了多个表关联时的顺序，通常多表关联的时候，

可以有多种不同的关联顺序来获得相同的执行效果，关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

 

二.优化

关于straight_join：

straight_join实现强制多表的载入顺序，从左到右，如：

...A straight_join B on A.name = B.name

straight_join完全等同于inner join 只不过，join语法是根据“哪个表的结果集小，就以哪个表为驱动表”来决定谁先载入的，而straight_join 会强制选择其左边的表先载入。

 

注意：

总的来说STRAIGHT_JOIN只适用于内连接，因为left join、right join已经知道了哪个表作为驱动表，哪个表作为被驱动表，

比如left join就是以左表为驱动表，right join反之，而STRAIGHT_JOIN就是在内连接中使用，而强制使用左表来当驱动表，

所以这个特性可以用于一些调优，强制改变mysql的优化器选择的执行计划。

但是往往在分析mysql处理性能时，如(Explain),如果发现mysql在载入顺序不合理的情况下，可以使用这个语句，但往往mysql能够自动的分析并处理好。

 

 三.注意事项

（1）单条查询最后添加 LIMIT 1，停止全表扫描。

 

（2）对于char(4) 或者vachar(4)，无论是中文还是英文都是存储四个字符，注意是字符而不是字节。

 

（3）如果一个字段未int类型，此类型只有0、1两个状态，需要为此建立索引吗？过度索引，影响更新速度，必须在唯一性较高的字段上建立非聚集索引。

 

（4）在创建表的时候如果在业务中能保证非null的字段，建议明确标示not null 因为mysql中对null需要特殊的标示。使用not null 字段更节省空间。对接下来的索引构建也有好处。

 

（5）count(*) 和count(name) name 代表某个字段，可以为NULL。在mysql中count(*)会把null统计进去、而count(name) 不会。如果统计的字段中含有null，这个两个统计的结果是不同的。

 

（6）在sql语句等号左边用函数，会使该查询在该字段无法使用索引。如LENGTH(str) 函数。

 

（7）索引也是需要存储到物理空间的，经常增删的表不适合建太多的索引，因为索引的维护会很耗时间。一张表最多建立15个索引。索引的长度越小越好，索引是有序的。

         如果查询Max（）之类用索引的话，连表都不用查询了，快得飞起。

 

（8）mysql中null不参与比较运算，name <>'小米' 得出的结果中不包含 name=null的情况。在业务不能保证某字段是否为null的情况，写代码的时候需要注意null的坑。保证取得的数据是对而全，然后再考虑查询速率问题。

 

（9）MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

 

（10）对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。

 

（11）UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。

 

（12）通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

 

（13）TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

 

（14）schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

 

（15）大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。