SQL优化（一）：MySQL多表查询FROM和JOIN的用法与性能优化

喜欢ヅ旅行 2022-02-21 13:20 1290阅读 0赞

### 一、概述 ###

*  from和join均是用于指定需要从哪些表查询数据，from可以是一个表或多个表，如果是多个表则是生成一个笛卡尔集，会涉及到大量数据。所以通常在涉及到多个表的查询时，通常通过join来拼接多个表。
 *  join主要是通过多个表之间的外键关联来进行拼接，注意用于拼接的列需要加上索引，如果没有则MySQL也会默认加上，不过前提是外键列和引用的主键列需要是相同的数据类型，如数字类型需要是相同的长度和均是有符号或无符号数，字符串类型长度可以不一样。以下分析涉及的表结构如下：用户表t\_user和用户订单表t\_order，在t\_order表的user\_id列是引用t\_user的id列的外键。
    
        mysql> show create table t_user;
         +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
         | Table  | Create Table                                                                                                                                                                                                                                                                                                                                   |
         +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
         | t_user | CREATE TABLE `t_user` (
           `id` int(11) NOT NULL AUTO_INCREMENT,
           `name` varchar(64) NOT NULL,
           `password` varchar(64) NOT NULL,
           `email` varchar(64) NOT NULL,
           `phone` varchar(32) DEFAULT NULL,
           PRIMARY KEY (`id`),
           UNIQUE KEY `name` (`name`),
           UNIQUE KEY `email` (`email`)
         ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
         +--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
         1 row in set (0.00 sec)
         
         mysql> show create table t_order;
         +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
         | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
         +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
         | t_order | CREATE TABLE `t_order` (
           `order_id` int(11) NOT NULL AUTO_INCREMENT,
           `user_id` int(11) NOT NULL,
           `cost` double DEFAULT NULL,
           `buy_date` date NOT NULL,
           PRIMARY KEY (`order_id`),
           KEY `idx_order_buy_date` (`order_id`,`buy_date`),
           KEY `idx_user_id` (`user_id`),
           KEY `idx_user_id_buy_date` (`user_id`,`buy_date`),
           CONSTRAINT `user_refrence` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`)
         ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
         +---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
         1 row in set (0.00 sec)

### 二、用法分析 ###

##### a. FROM #####

*  将多个表的所有数据行拼成笛卡尔集，故如果表的数据行多时，则数据量很多，造成巨大的磁盘、内存开销，当然查询速度也会很慢。
 *  如下：xyz2这个用户其实是没有订单的，故from后面跟着这两个表只是简单的进行了：2 \* 4=8行，其中用户表有2行数据，订单表有4行数据。
    
        mysql> select * from t_user, t_order;
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        | id | name | password                         | email      | phone    | order_id | user_id | cost  | buy_date   |
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        1 |       1 |   100 | 2019-04-10 |
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789   |        1 |       1 |   100 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        2 |       1 |   102 | 2019-04-10 |
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789   |        2 |       1 |   102 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        3 |       1 |   103 | 2019-04-10 |
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789   |        3 |       1 |   103 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        4 |       1 |   104 | 2019-04-10 |
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789   |        4 |       1 |   104 | 2019-04-10 |
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        8 rows in set (0.00 sec)

##### b. LEFT JOIN #####

*  包含左边表的所有行和右边的匹配行，如果左边表在右边表不存在关联数据，则只包含该行数据，而右边表相关的数据则是NULL，如下，相对于from，数据行变成了5行，即1\*4+1=5
    
        mysql> select * from t_user left join t_order on t_user.id=t_order.user_id;
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        | id | name | password                         | email      | phone    | order_id | user_id | cost  | buy_date   |
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        1 |       1 |   100 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        2 |       1 |   102 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        3 |       1 |   103 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        4 |       1 |   104 | 2019-04-10 |
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789   |     NULL |    NULL |  NULL | NULL       |
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        5 rows in set (0.00 sec)

##### c. JOIN / INNER JOIN #####

*  join是inner join的简写，只返回匹配表中匹配的数据行，如下：只返回4行数据。
    
        mysql> select * from t_user join t_order on t_user.id=t_order.user_id;
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        | id | name | password                         | email      | phone    | order_id | user_id | cost  | buy_date   |
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        1 |       1 |   100 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        2 |       1 |   102 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        3 |       1 |   103 | 2019-04-10 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        4 |       1 |   104 | 2019-04-10 |
        +----+------+----------------------------------+------------+----------+----------+---------+-------+------------+
        4 rows in set (0.00 sec)

##### d. RIGHT JOIN #####

*  righ join与left join刚好相反，包含右边表的所有行，如果在左边表没有匹配，则相关字段为NULL，由于只包含4行数据且均与用户1关联，故返回4行。
    
        mysql> select * from t_user right join t_order on t_user.id=t_order.user_id;
        +------+------+----------------------------------+------------+----------+----------+---------+------+------------+
        | id   | name | password                         | email      | phone    | order_id | user_id | cost | buy_date   |
        +------+------+----------------------------------+------------+----------+----------+---------+------+------------+
        |    1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        1 |       1 |  100 | 2019-04-10 |
        |    1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        2 |       1 |  102 | 2019-04-10 |
        |    1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        3 |       1 |  103 | 2019-04-10 |
        |    1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        4 |       1 |  104 | 2019-04-10 |
        +------+------+----------------------------------+------------+----------+----------+---------+------+------------+
        4 rows in set (0.00 sec)

##### e. JOIN多次 #####

*  如果包含多个left join（或者多个right join，join等，顺序没有关系），则是前面的left join的结果作为左边，继续与后面的left join的表进行拼接，如下创建一个订单清单条目表t\_order\_item，并为订单1创建一个条目：
    
        mysql> create table t_order_item(id int not null primary key auto_increment, product_id int not null, price double  not null, num double  not null, order_id int not null) default charset utf8;
        Query OK, 0 rows affected (0.04 sec)
        
        mysql> alter table t_order_item add constraint order_reference foreign key t_order_item(order_id) references t_order(order_id);
        Query OK, 0 rows affected (0.02 sec)
        
        mysql> insert into t_order_item(order_id, product_id, price, num) values(1, 1, 10.0, 10);
        Query OK, 1 row affected (0.00 sec)
        
        # 修改t_order的price列为cost
        mysql> alter table t_order change price cost double not null;
        Query OK, 0 rows affected (0.07 sec)
        Records: 0  Duplicates: 0  Warnings: 0
 *  多个left join：返回5行数据，其中第一个left join返回5行数据，然后这5行数据在第二个left join与t\_order\_item拼表，由于只插入了一行t\_order\_item数据，故其他行对应的字段均为null。
    
        mysql> select * from t_user left join t_order on t_user.id=t_order.user_id
        -> left join t_order_item on t_order.order_id = t_order_item.order_id;
        +----+------+----------------------------------+------------+----------+----------+---------+------+------------+------+------------+-------+------+----------+
        | id | name | password                         | email      | phone    | order_id | user_id | cost | buy_date   | id   | product_id | price | num  | order_id |
        +----+------+----------------------------------+------------+----------+----------+---------+------+------------+------+------------+-------+------+----------+
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        1 |       1 |  100 | 2019-04-10 |    1 |          1 |    10 |   10 |        1 |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        2 |       1 |  102 | 2019-04-10 | NULL |       NULL |  NULL | NULL |     NULL |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        3 |       1 |  103 | 2019-04-10 | NULL |       NULL |  NULL | NULL |     NULL |
        |  1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        4 |       1 |  104 | 2019-04-10 | NULL |       NULL |  NULL | NULL |     NULL |
        |  2 | xyz2 | 5838eec5e44b83f35c2763382b45e469 | 456@qq.com | 456789   |     NULL |    NULL | NULL | NULL       | NULL |       NULL |  NULL | NULL |     NULL |
        +----+------+----------------------------------+------------+----------+----------+---------+------+------------+------+------------+-------+------+----------+
        5 rows in set (0.00 sec)
 *  先left join再right join：第一个left join返回5行数据，由于第二个right join的t\_order\_item表只包含一行数据，故最终只返回一行数据：
    
        mysql> select * from t_user left join t_order on t_user.id=t_order.user_id
            -> right join t_order_item on t_order.order_id = t_order_item.order_id;
        +------+------+----------------------------------+------------+----------+----------+---------+------+------------+----+------------+-------+-----+----------+
        | id   | name | password                         | email      | phone    | order_id | user_id | cost | buy_date   | id | product_id | price | num | order_id |
        +------+------+----------------------------------+------------+----------+----------+---------+------+------------+----+------------+-------+-----+----------+
        |    1 | xyz  | 5838eec5e44b83f35c2763382b45e469 | 123@qq.com | 12345678 |        1 |       1 |  100 | 2019-04-10 |  1 |          1 |    10 |  10 |        1 |
        +------+------+----------------------------------+------------+----------+----------+---------+------+------------+----+------------+-------+-----+----------+
        1 row in set (0.00 sec)

### 三、性能优化 ###

优化方面主要是针对join的优化，因为join本身就是对from的一种优化了。而join的优化主要是从join的列的优化和join的表的左右顺序两个方面来分析。除此之外就是表的反范式设计。

##### a. join的列：外键索引 #####

*  外键约束能够保证主表和关联表的数据完整性，但是更新时需要同步更新，所以操作会变慢，即会存在级联操作。
 *  在关联表创建外键约束的语法如下：
    
        CONSTRAINT idx_column_name FOREIGN KEY  (index_col_name,...) REFERENCES main_tbl_name(index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option]
        
        其中外键约束条件如下：
        
        RESTRICT : 禁止操作， 如果关联表存在匹配的行，则禁止对主表的主键值进行UPDATE或DELETE。
        CASCADE : 级联操作，主表UPDATE或DELETE自动UPDATE或DELETE关联表的匹配行，其中UPDATE为更新外键列的值。
        SET NULL : 主表UPDATE或DELETE，会设置关联表的外键列为NULL，所以需要保证外键列可以为NULL。
        NO ACTION: 与RESTRICT相同。
 *  在主表和关联表之间，关联表包含一个映射主表的主键（或者其他列，但是必须是包含索引的列，主表用于进行外键约束的列必须显式加上索引）的外键，主表的主键和关联表的外键需要是相同的数据类型，如数字类型，如果是字符串类型，字符串长度可以不同。该外键需要加上索引，对于关联表的外键列，如果没有显式加上索引，则MySQL会自动隐式加上索引。如果主表的列与关联表的外键列数据类型不一样，则无法添加外键约束，如下：t\_order\_item表的order\_id为bigint(20)，t\_order表的order\_id为int(11)，则无法添加外键约束，改成int之后则可以正常添加，并且MySQL添加了一个索引：KEY `order_reference` (`order_id`)
    
        mysql> alter table t_order_item change order_id order_id bigint(20) not null;
        Query OK, 1 row affected (0.03 sec)
        Records: 1  Duplicates: 0  Warnings: 0
        
        mysql> alter table t_order_item add constraint order_reference foreign key t_order_item(order_id) references t_order(order_id);
        ERROR 1215 (HY000): Cannot add foreign key constraint
        mysql> 
        
        mysql> alter table t_order_item change order_id order_id int(20) not null;
        Query OK, 1 row affected (0.04 sec)
        Records: 1  Duplicates: 0  Warnings: 0
        
        mysql> alter table t_order_item add constraint order_reference foreign key t_order_item(order_id) references t_order(order_id);
        Query OK, 1 row affected (0.03 sec)
        Records: 1  Duplicates: 0  Warnings: 0
        
        mysql> show create table t_order_item;
        +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        | Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
        +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        | t_order_item | CREATE TABLE `t_order_item` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `product_id` int(11) NOT NULL,
          `price` double NOT NULL,
          `num` double NOT NULL,
          `order_id` int(20) NOT NULL,
          `remark` varchar(64) DEFAULT NULL,
          PRIMARY KEY (`id`),
          KEY `idx_remark` (`remark`),
          KEY `idx_num` (`num`),
          KEY `order_reference` (`order_id`),
          CONSTRAINT `order_reference` FOREIGN KEY (`order_id`) REFERENCES `t_order` (`order_id`)
        ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
        +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
        1 row in set (0.01 sec)

##### b. inner join的表的左右顺序：小表驱动大表 #####

*  在关联操作中，主表和关联表的顺序对性能影响很重要，特别对于JOIN/INNER JOIN这种匹配关联来说，因为LEFT/RIGHT JOIN一般是固定的不能调整顺序，而INNER JOIN由于是完全匹配，故主表和关联表的顺序可以调换。
 *  一般的优化规则是：小表驱动大表，即数据行较少的表在左边，数据行较多的表在右边。
 *  小表驱动大表的原理：在join当中，是使用左边表的每一个数据行去扫描右边的整个表的所有数据行，所以虽然总的匹配次数是相同的，但是如果左边表数据行很多，则需要加载右边的整个表很多次，使用小表驱动大表主要是要减少这个次数，即内循环次数，来提高性能。
 *  针对以上案例，如果订单表t\_order包含10000行，用户表t\_user包含100行，则用户表t\_user需要在左边，订单表t\_order需要在右边，即：
    
        select * from t_user join t_order on t_user.id = t_order.user_id;
 *  小表驱动大表的设计只是我们编写SQL需要注意的，但是MySQL优化器不一定就完全按照这个顺序，MySQL是使用“小结果集”驱动“大结果集”的，即如果SQL语句还包含其他WHERE查询条件，排序ORDER BY等，则以上顺序可能还是反过来的，如果要强制该顺序，则可以使用STRAIGHT JOIN来替代INNER JOIN。具体可以参考这位博主的文章[：MySQL优化的奇技淫巧之STRAIGHT\_JOIN  
    ][MySQL_STRAIGHT_JOIN]

##### c. 反范式设计：单表冗余，不使用JOIN #####

*  MySQL的三范式主要是从减少数据冗余的角度来规范表的设计，但是这个的历史背景是以前磁盘资源昂贵的角度出发的，在现代磁盘空间廉价的情况下，进行适当的数据冗余存储来避免拼表存在是一种以空间换时间的优化方法，所以进行适当的反范式设计也是一种优化思路。
 *  不过反范式设计也要考虑数据的更新问题，因为同一个数据在两个或多个表中存储了，故在更新时也要同步更新。

[MySQL_STRAIGHT_JOIN]: https://huoding.com/2013/06/04/261