MySQL之常见面试题分析

1：a表有100条记录，b表有10000条记录，两张表做关联查询时，是将a表放前面效率高，还是b表放前面效率高？

这个问题考察的其实就是应该将小表作为驱动表，还是将大表作为驱动表，可以从数据查找的时间复杂度来进行分析，如果是将小表作为驱动表，则查找的时间复杂度就是100*log(10000),如果是将大表作为驱动表则查找的时间复杂度就是10000*log(100)，比较这两个时间复杂度，就可以很容易的知道，将小表作为驱动表是更优的选择。

以上的分析是在假定从动表可以使用到索引的情况，驱动表毫无疑问是一个全表扫描的过程，而如果是从动表无法使用到索引的话，使用小表作为驱动表还是使用大表作为驱动表，理论上是没有差别的，因为此时查询的时间复杂度是100*10000以及10000*100，很明显二者是相同的。因此我们可以将该问题进一步抽象，假设两张表，数据的条数是m，n应该使用哪个表作为驱动表呢，可以总结以下的两种情况来分析。

1.1：不管谁作为从动表都可以使用到索引

两种情况，当m*logn > n*logm时,使用n条数据的表作为驱动表，反之使用m条数据的表作为驱动表。

1.2：不管谁作为从动表不可以使用到索引

此时使用哪张表作为驱动表差别不大。

关于使用谁作为驱动表，谁作为被驱动表的问题上，其实MySQL的优化器会帮我们做出最优的选择，因此，sql语句中谁写在前面，谁写在后面都是无所谓的，但是我们要领会面试官的意图，知道面试官到底是想要问什么，给出尽量好的答案。如果是不想要优化器优化的话，可以使用straight_join，如select * from t1 straight_join t2 on (t1.a=t2.a);

2：索引失效的场景都有哪些

不考虑优化器触发未知bug导致选错索引的情况。

基本上有两种情况，第一种是优化器认为不使用索引比使用索引查询速度快，第二种是虽然有索引但是却无法使用，下面分别看下这两种情况。

• 不使用索引比使用索引查询速度快

1：比如数据量较少，直接查询主键索引，可以避免回表，此时会选择不使用索引
2：查询的数据区分度低，会导致大量的回表，此时会选择不使用索引

• 虽然有索引但是却无法使用

1：对数据字段使用了函数，运算等破坏了B+数索引的有序性
    如where id+1=100,where month(birthday)=9等
2：使用了左like，无法使用B+树的有序性来搜索

3：select * from t where id=1阻塞可能什么原因？

准备数据如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure t_1704()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

begin;
call t_1704();
commit;

3.1：等MDL锁

当我们执行show processlist，如果是State的值是Waiting for table metadata lock的话，则说明是被MDL锁阻塞了,接下来我们具体看下。

• 启动会话A,B,C
• 在会话A开启事务A
  注意这步，让后续操作在一个事务里，这样获取的锁在commit或rollback之前就不会释放，便于我们试验。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

• 在事务A执行lock table

mysql> lock table t write;
Query OK, 0 rows affected (0.00 sec)

• 在会话B执行select * from t where id=1
  会被阻塞，如下图：

• 在会话C执行show processlist

可以看到其中一行中的State信息Waiting for table metadata lock，其中的进程ID=13就是持有MDL锁的进程(此时Command是Sleep)，我们将其kill掉就行了，如下：

我们这里进程不多，所以很容易发现是哪个进程占用了MDL锁，实际环境我们也可以根据Command=Sleep这个特征，以及Time时间来大概推断是哪个进程，然后kill，此时适当的误杀问题不大。

3.2：等flush

我们知道，MySQL的刷脏页操作，叫做flush，当执行flush时会阻塞所有的DML操作，包括读操作，除了满足特定条件时自动触发flush，也可以通过命令flush tables with read lock，主要有如下两种情况：

flush tables t with read lock;
flush tables with read lock;

第一个语句指定了表t，因此只会关闭表t，本例我们就是用该命令来模拟场景。第二个语句会关闭所有的表，影响比较大，要慎用！flush table语句执行特别快，为了让其等待我们可以通过让其等待select操作完成，具体如下：

• 启动会话A，B，C，D
• 在会话A执行一个长时间的查询

这句话的意思是，对满足where条件的每一行都睡眠1秒，因为我们的表中一种有10万行数据，所以一共会睡眠10万秒，足够我们来进行后续的操作了。

• 在会话B执行flush table

可以看到也阻塞等待了。

• 在会话C执行select * from t where id=1;

可以看到阻塞等待了。

• 在会话D执行show processlist;

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------------------------+----------------------------+
| Id | User | Host            | db   | Command | Time | State                   | Info                       |
+----+------+-----------------+------+---------+------+-------------------------+----------------------------+
| 18 | root | localhost:57178 | test | Query   |  145 | Waiting for table flush | flush tables t             |
| 19 | root | localhost:58776 | test | Query   |  307 | User sleep              | select sleep(1) from t     |
| 20 | root | localhost:58797 | test | Query   |   60 | Waiting for table flush | select * from t where id=1 |
| 21 | root | localhost:58801 | test | Query   |    0 | NULL                    | show processlist           |
+----+------+-----------------+------+---------+------+-------------------------+----------------------------+
4 rows in set (0.00 sec)

从结果看，我们只需要kill 19，就可以了，如下：

3.3：等行锁

当执行如update t set c=x where id=1之类的语句时，就会获取id=1这一行的行写锁，此时行写锁(update 语句, select xxx for update)，行读锁(select xxx lock in share mode)注意select xx from t where id=1不获取任何锁都无法被获取，下面我们来一起看下。

• 启动会话A,B,C
• 在会话A启动事务

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

• 在会话A执行语句更新id=1的行

mysql> update t set c=c where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

• 在会话B启动事务

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

• 在会话B执行语句更新id=1的行

可以看到阻塞等待了。

• 在会话C执行show processlist;

mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+-----------------------------+
| Id | User | Host            | db   | Command | Time | State    | Info                        |
+----+------+-----------------+------+---------+------+----------+-----------------------------+
| 18 | root | localhost:57178 | test | Query   |   18 | Updating | update t set c=c where id=1 |
| 20 | root | localhost:58797 | test | Query   |    0 | NULL     | show processlist            |
| 21 | root | localhost:58801 | test | Sleep   |   55 |          | NULL                        |
| 22 | root | localhost:64840 | test | Sleep   | 1155 |          | NULL                        |
+----+------+-----------------+------+---------+------+----------+-----------------------------+

可以看到第一行线程ID为18的就是阻塞等待的线程，那么我们如何找到是谁占用了id=1的行锁呢？没有特别好的办法，但是我们知道行锁使用的是两阶段锁协议，即在执行语句时才会获取锁，但是语句执行完毕不立即释放锁，而是在事务提交时才释放锁，因此可以断定既然锁一直没有被释放，肯定是因为其所在的事务没有提交，所以我们可以通过过滤长事务的方式找到罪魁祸首,当然只是可能是哪个，但是必要时误杀也影响不大。可通过如下语句过滤：

mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G
*************************** 1. row ***************************
                    trx_id: AAF56
                 trx_state: RUNNING
               trx_started: 2022-07-15 14:18:56
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 26
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 2
     trx_lock_memory_bytes: 320
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
1 row in set (0.03 sec)

这里我使用的时长是60秒，具体可以根据情况来设置时长，trx_mysql_thread_id: 26其中的25就是线程ID，kill 26即可，如下图：

4：只有十万条数据但select * from t where id=1执行特别慢？

准备数据如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure t_1704()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

begin;
call t_1704();
commit;

delimiter ;;
create procedure update_100wan()
begin
  declare i int;
  set i=1;
  while(i<=1000000) do
    update t set c=c+1 where id=1;
    set i=i+1;
  end while;
end;;
delimiter ;

为了方便查看执行时长，打开慢日志:

mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow_query_log%';
+---------------------+---------------------------------------------------------------+
| Variable_name       | Value                                                         |
+---------------------+---------------------------------------------------------------+
| slow_query_log      | ON                                                            |
| slow_query_log_file | D:\program_files\phpstudy\PHPTutorial\MySQL\data\jhp-slow.log |
+---------------------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> set long_query_time=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

如下测试：

• 启动会话A，B
• 在会话A启动事务

mysql> start transaction with consistent snapshot;
Query OK, 0 rows affected (0.00 sec)

• 在会话B执行”update t set c=c+1 where id=1“100万次

mysql> begin;
Query OK, 0 row affected (13.47 sec)
mysql> call update_100wan();
Query OK, 1 row affected (13.47 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

• 在会话A执行查询

mysql> select * from t where id=1/** session A query **/;
+----+------+
| id | c    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.63 sec)

慢日志如下：

# Time: 220715 15:39:32
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.629361  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1657870772;
select * from t where id=1;

可以看到执行时长是0.629361,单位是s，可以说对于当前数据量的基于主键的查询，是比较慢的了。原因是会话B的100万次的update，会生成一个长度为100万的undo log，而这里的查询必须遍历这100万个undo log才能获取想要的结果即MVCC。

作为对比我们可以使用当前读看下：

mysql> select * from t where id=1 lock in share mode;
+----+---------+
| id | c       |
+----+---------+
|  1 | 1000001 |
+----+---------+
1 row in set (0.00 sec)

可以看到执行的特别快，这也就是证明了我们的猜想。二者通过undo log的查询过程如下图：

5：如何处理主从架构中从读数据延迟问题？

5.1：强制主库读

这里的强制主库读并非是所有读请求都主库读，对于实时性要求高的读请求才主库读，其他请求还是从库读，这种方案在必须实时读的场景比较少时还是有一定的使用价值的，但是考虑像金融类的产品，可能几乎所有的请求都是对实时性要求很高的，此时主从架构其实就从根本意义上退化成了非主从，此时就需要考虑其他方案，可以继续看下面的方案，来从中选择一个合适的应用在自己的系统中。

5.2：强制sleep等待

既然是是数据延迟，那么就可以人为的制造等待，sleep一段时间，比如sleep 1秒钟后再执行查询，但是这种方式有个问题，就是，这个sleep时长应该定多少，数据库系统在不同的压力下延迟时长肯定是有长有短，过长的sleep会严重影响响应用户请求的速度，而过短的时间依然会出现过期读数据的问题，因此我认为这种方式在实际的应用中可操作性实际上是不高的。其实，我们仔细看这里sleep是为了什么，是为了让从库执行完对应事务的binlog，即sql thread将写到relay log中对应事务的binlog执行完毕，如果有一种方式能够完成这个判断，无疑就有了和sleep同等目标，但是效果更好的解决方案，我们接着往下看。

5.3：判断主备无延迟

最直接的方式就是判断sbm(seconds_behind_master)，但是单位是秒，精度是不够的，所以不予采纳。这里我们可以考虑的方案是判断同步位点和GTID。

5.3.1：同步位点

当我们执行show slave status，是会看到如下的输出信息：

其中的Master_Log_File，Read_Master_Log_Pos代表当前从master拉取的最新的binlog文件名称，以及binlog最新的位点。Relay_Master_Log_File代表中继日志中当前最新的binlog文件名称，Exec_Master_Log_Pos代表中继日志当前执行的最新位置，当Master_Log_File==Relay_Master_Log_File && Read_Master_Log_Pos==Exec_Master_Log_Pos时就代表数据同步完成了。

5.3.2：GTID

当我们执行show slave status，是会看到如下的输出信息：

其中的Retrieved_Gtid_Set代表的是从库从主库收到的所有的binlog日志的gtid集合，Executed_Gtid_Set代表的是sql thead已经从relay log中执行的gtid集合，当Retrieved_Gtid_Set==Executed_Gtid_Set时就代表数据同步完成了。

5.3.3：问题

同步位点和GTID方案其实都是用收到的binlog和已经执行的binlog作对比，如果二者相同就认为同步完成，但是其实这样是有问题的，考虑一种场景就是某次事务对应的binlog压根就没有同步到从库，如下图的trx3就是这种情况：

出现这种现象的原因是binlog的同步过程是异步的，即在返回给客户端提交成功的消息时，对应事务的binlog并不保证已经传输到了从库，想要保证这点，就需要配合半同步复制 机制了。引入了半同步机制后，在只有一个从节点时是没问题的，但当从节点的个数>1时，并不能保证所有的从节点都同步到事务对应的binlog，所以此时依然是有问题的。到这里，可以回顾下，所做的各种操作，无非就是想要保证事务对应的binlog在从节点执行完成了而已，那么我们能不能只判断事务对应的binlog执行完毕就可以而不需要从和主完全同步完成呢？接下来我们就看下这种方案如何实施。

5.4：判断事务binlog执行完毕

这种方案的执行方式是，事务执行后在主库执行show master status：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |      313 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

然后在从库判断是否执行到文件mysql-bin.000009的位点313即可，判断的具体方式是通过函数select master_pos_wait(file, pos[, timeout]);，这里执行的话就是select master_pos_wait('mysql-bin.000009', 313, 2);，最多等待2秒，从库执行到mysql-bin.000009的位点313后函数返回，函数的返回值可能如下：

1：正整数，代表执行到指定binlog文件的指定位点，返回正整数代表共执行了多少个事务
2：NULL，代表执行期间同步线程发生了异常
3：-1，超过了等待时长，也没有执行到指定binlog文件的指定位点
4：0，函数执行时指定binlog文件指定位点已经执行过了

综上几种结果，当返回值!=null && 返回值>=0时就可以在当前的从库执行查询，否则退化到主库查询(注意主库做好限流，防止请求都打到主库，主库挂掉！！！)。

6：自增主键一定是连续的吗？

结论是。不一定

在MySQL中有一个参数auto_increment_increment是用来控制自增主键的步长的，如下：

mysql> show variables like 'auto_increment_increment';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

如果是该值设置为大于1的值，自增ID肯定是不连续的，但是如果是假定不修改步长，即步长就是1的话，还有什么情况导致ID不连续吗？还是有的，接下来我们一起看下。

6.1：出现唯一键冲突导致

当出现了唯一键冲突，数据是不能插入成功的，如果是再插入数据就会出现主键不连续的情况，如下测试：

• 建表

mysql> create table t1122(
    -> `id` int(11) not null auto_increment,
    -> `c` int(11) default null,
    -> `d` int(11) default null,
    -> primary key (`id`),
    -> unique key `c` (`c`)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1122 values(null, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1122;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  1 |    1 |    1 |
+----+------+------+
1 row in set (0.00 sec)

mysql> insert into t1122 values(null, 1, 1); /*will happen duplicate key error*/
ERROR 1062 (23000): Duplicate entry '1' for key 'c'
mysql> insert into t1122 values(null, 2, 2); /*insert success*/
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1122;
+----+------+------+
| id | c    | d    |
+----+------+------+
|  1 |    1 |    1 |
|  3 |    2 |    2 |
+----+------+------+
2 rows in set (0.00 sec)

可以看到insert into t1122 values(null, 1, 1); /*will happen duplicate key error*/虽然是出现了唯一键冲突，但是insert into t1122 values(null, 2, 2); /*insert success*/获取到的自增ID值是3，这是为什么呢？要想说清楚这个问题，就必须了解下自增ID值申请的流程，下面我们一起看下。

1：执行器调用innodb引擎的写入一行数据的接口，传入的数据是(0,2,2)
2：innodb引擎发现用户没有指定id的值，则获取当前表t1122的自增值2
3：innodb存储引擎获取的自增值应用到用户传入的行中，(0,2,2)变为(2,2,2)
4：innodb引擎将表的引擎改为3
5：发生duplicate key error

也可以参考下图：

说明：

关于1：我们传入的(null,2,2)会在server层直接重写为(0,2,2),ID值指定为null和0都代表不设置ID值。

关于2：自增ID值的存储，在8之前，该值是存储在内存中的，重启后会重新获取当前表的最大ID+1作为下一个可分配的自增ID，8之后，该值是存储在文件中，因此具有了持久化的能力，重启不会丢失。

关于5：发生了duplicate key error，但是并没有将自增ID值修改为2，这么做的原因是在多事务并发的场景下，如果是想要修改该值，需要增大自增锁的范围，降低服务器的TPS，对性能影响较大，所以放弃这个功能。

上述就是造成ID不连续的一种情况。同样的，事务的回滚也会导致这种情况的发生，接下来也看下。

6.2: 事务回滚

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1122 values(0, 4, 4); /*insert suc*/
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select max(id) from t1122;
+---------+
| max(id) |
+---------+
|       5 |
+---------+
1 row in set (0.00 sec)

mysql> insert into t1122 values(0, 4, 4); /*insert suc*/
Query OK, 1 row affected (0.00 sec)

mysql> select max(id) from t1122;
+---------+
| max(id) |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

可以看到在事务提交之前已经申请的自增ID值6，在事务回滚后并没有修改回去。

6.3: insert…select…

我们首先需要先来分析下insert…select…的内部逻辑，该语句在执行完毕之前不会释放自增锁，也就是在这期间，目标表其他申请自增ID值的操作都会被阻塞，那么能不能一次申请所有的ID呢，答案是不行的，为什么呢？因为不知道要申请多少个，那么MySQL为了最大程度的减少自增锁的锁定时间，对于该语句申请自增ID策略是第一次申请1个自增ID，后面每次申请上次的2倍的自增ID，如

1：第一次申请1个自增ID
2：第二次申请2个自增ID
3：第三次申请4个自增ID
...

直到申请的自增ID足够使用就不会再进行申请，而从前面的分析我们知道，申请但最终没有被使用的自增ID是不会归还的,那么这种申请自增ID的方式，就肯定会导致出现自增ID不连续的情况了，比如最终插入的数据行数是5，则需要进行3次自增ID的申请，假设第一次申请的自增ID是x,则第二次申请的就是x+1,x+2,第三次申请的是x+3,x+4,x+5,x+6，最终x+4,x+5,x+6都是不会被使用的，如下测试：

mysql> create table t1122(
    -> `id` int(11) not null auto_increment,
    -> `c` int(11) default null,
    -> `d` int(11) default null,
    -> primary key (`id`),
    -> unique key `c` (`c`)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1122 values(null, 1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1122 values(null, 2,2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1122 values(null, 3,3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1122 values(null, 4,4);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1122 values(null, 5,5);
Query OK, 1 row affected (0.00 sec)

mysql> create table t1122_1 like t1122;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1122_1(c,d) select c,d from t1122;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select max(id) from t1122_1;
+---------+
| max(id) |
+---------+
|       5 |
+---------+
1 row in set (0.00 sec)

mysql> insert into t1122_1 values(null, 6,6);
Query OK, 1 row affected (0.00 sec)

mysql> select max(id) from t1122_1;
+---------+
| max(id) |
+---------+
|       8 |
+---------+
1 row in set (0.00 sec)

执行mysql> insert into t1122_1(c,d) select c,d from t1122;,申请的ID是1,2-3,4-7,最终使用的是1-5，所以第一次执行select max(id) from t1122_1;返回的结果是5，而插入一条数据后，执行select max(id) from t1122_1;的结果是8。

7：都有哪些join

join分为inner join和outer join，其中inner join返回的结果是左侧的表和右侧的表匹配成功的行，如果是没有匹配成功的行则不会返回。outer join会返回没有匹配成功的行，其又会根据full，left，right来返回不同结果，接下来我们按照是否会返回不匹配（即填充null）的行来进行分析。在开始之前我们先来准备测试数据：

• 商品表：存放商品信息
• 图片表：存放图片
• 建表语句

CREATE TABLE product (
    id int,
    name varchar(100) COMMENT '名称',
    img_id int COMMENT '关联的图片id'
);

CREATE TABLE image (
    id int,
    url varchar(500) COMMENT '图片的url'
);

• 插入测试数据

INSERT INTO image (id, url) VALUES 
(1, 'f7c27424b538281fee5f00db5f83c2de.jpg'),
(2, '522748524ad010358705b6852b81be4c.png'),
(3, '97adf9fdd4c3e8423f37a208beb47a7a.gif'),
(4, '7b1847d909f17365bec38b04b9da6e57.webp');

INSERT INTO product (id, name, img_id) VALUES 
(1, '杯子', 1),
(2, '电脑', null),
(3, '大米饭', 3),
(4, 'PS4', null);

7.1：不会返回不匹配的行（补null）

此时要使用inner join如下：

inner join 也可以简写为 join 如下：

还可以写作from a,b where a.xx=b.yy的形式，如下：

因此join，inner join，from a,b where a.xx=b.yy这三种写法都是等价的，效果可参考下图：

因为inner join返回的都是匹配的行，因此并没有left/right inner join的用法，如下：

7.2：会返回不匹配的行（补null）

此时要使用outer join，outer join又分为left outer join和right outer join，分别用来设置返回左侧没有匹配行的数据和右侧没有匹配行的数据，先看下left outer join返回左侧没有匹配行的情况，如下：

可以看到右侧列补null了，其中outer也可以省略，直接写作left join，如下：

执行的效果如下图：

right outer join同left outer join 只不过保留右侧没有匹配的行，左侧的列补null，如下图们：

另外还有一种是full outer join，是保留两侧不匹配行，然后分别补null，但是我本地MySQL执行提示不支持这种语法，如下：

不过也没有关系，真的需要的话，我们只要left join 来 union right join就行了，如下：

8：如何让左like匹配也走索引

我们知道MySQL的最左匹配原则，但是如果是像like "%xy"这种就没有办法使用索引，但我们可以使用迂回战术来达到目的。一起看下，比如有如下的表和索引：

这里我们要查询以杰结尾的，如EXPLAIN SELECT * from USER_INFO where USER_NAME like '%杰'，此时无法使用索引，如下explain：

为了能够使用索引，我们执行如下语句创建一个存储user_name的反顺序的数据行，执行如下语句：

ALTER TABLE product ADD COLUMN user_name_reverse VARCHAR(64) generated always AS(REVERSE(`user_name`)), ADD INDEX(user_name_reverse);

此时我们就可以执行如下语句，基于最左匹配原则来查询数据了，如下：

explain SELECT * from USER_INFO where REVERSE_USER_NAME like '杰%'

将会使用到新建的索引，如下图：

参考文章列表

什么时候用 INNER JOIN，什么时候用 LEFT JOIN 。

【mySQL】left join、right join和join的区别 。

mysql怎么让左模糊查询也能走索引？ 。