面试之敌系列 1 MySQL

625 阅读27分钟

MySQL

简单介绍

mySQL是一种常用的关系型数据库。免费开源。 其默认的存储引擎为InnoDB,并且,和MYISAM相比,InnoDB是支持事务的(事务型存储引擎)。

MYISAM和InnoDB

在5.5版本之前,默认的存储引擎是 MyISAM,因为它提供的了很多的特性,主要有索引和全文压缩等技术。但是它不支持行锁以及事务。因此,在5.6版本之后,默认的存储引擎就变成了InnoDB。 下面罗列一些比较

  1. 是否支持行级锁:InooDB支持行级锁而MyISAM不支持。MyISAM只有表级锁。
  2. 是否支持事务和崩溃后的安全恢复:MyISAM主要强调的是性能方面。因此不提供事务。而且MyISAM不支持外键。其操作都是原子性的。相比之下,InnoDB不仅支持事务,还支持外键,支持事务失败之后的回滚,崩溃之后的安全恢复等操作。

事务

  1. 事务:事务是逻辑上的一组操作,这组操作在执行的时候,要么都执行,要么都不执行。简单就是要么同时成功,要么你同时失败。

  2. 事务的四个特性:

  3. 原子性(Atomicity):事务的原子性是其最显著的特性,就是一组操作组成一个原子操作。该组操作作为最小的单位,执行的时候不可被分割。要么同时执行,要么同时不执行。

  4. 一致性(Consistency):执行事务的前后,保持数据的一致性。这里的一致性主要是针对其他事务来说,在执行事务的前后,其他不同的事务对数据的读取是相同的。

  5. 隔离性(Isolation):隔离性主要是并发访问数据库的时候,各个事务是独立执行的,事务之间互不影响。(虽然事务是并发的,但是最终的结果感觉就是事务是串行执行的)。事务的隔离性是对并发的支持。面试的时候,一般会问并发方面的知识,我们还会详细的讲解事务的四中常见的隔离等级。

  6. 持久性(Durability):事务提交之后,会将改变持久化到数据库中。

  7. 事务的这四中机制ACID。

  8. 并发带来的问题

  9. InnoDB提供的事务支持中可以知道,在隔离性我们提到了事物的并发结果就像是串行的。这其实是需要看事务隔离的等级的。

  10. 由于事务的并发,导致的一些常见的问题有

  11. 脏读:当你的隔离等级为 读取未提交的时候。一个事务对数据的修改还没有提交的时候,另外的一个事务可以进行数据的读取,此时就发生脏读。这种最常见的就是发生在缓存中。数据修改后,发送到缓存中,但是没有写到数据库,此时如果有新的事务进行读取的话,读取的还是就是还没有持久化的数据。这可能会造成一些影响。

  12. 丢失修改:一个事务所做的修改由于另外一个并发的事务的修改二丢弃不见。一个事务对数据进行了修改之后,此时另外的一个事务对数据也进行修改。但是他们读取的是同一个数据,因此当两个事务都提交的时候,只修改了一次。这里可能需要使用 写 happen-before 读的规则。保证顺序。可以采用的右排他的写锁。

  13. 不可重复度:同一个事务中,对一个数据的多次读取结果不一样。这主要是该事务在进行的时候,有并打的另外的事务对数据做了修改。要保证可重复度的话,在读取的时候不允许对数据进行修改。这里可以使用读写锁实现。读的时候加共享的读锁,写的时候加排他的写锁。

  14. 幻读:一个事务中会执行多次读取,但是两次读取的记录数目不一致。这里和重复度的主要的区别是数据是增多了或者变少了。这个的主要原因也是并发的事务进行了插入或者删除操作。

  15. 事务隔离的四个等级

  16. 读取未提交:最低的隔离等级。会导致上面提到的所有的问题。

  17. 读取已提交。这个隔离等级可以保证不会脏读,读取的数据都是已经提交到数据库中的数据。

  18. 可重复度。要保证并发的时候事务的可重读读,需要实现的是在读的时候,写不能进行。这可以采用读写锁来实现。但是可重复度还是不能解决幻读。MVCC?

  19. 串行化:最高的隔离等级。将事务串行化。

  20. MCVV 多版本并发控制,实现对数据库的并发访问;在编程语言中实现事务内存。对并发的实现主要基于版本号进行。一个简答的例子就是当你对数据进行跟新的时候,并不会直接将原来的数据覆盖,而是采用将原来的数据标记过时,在追加一新的记录。但是它需要对这个模型进行维护,以便定期的正真的将过期的数据进行删除。

索引

数据库的索引是一种数据结构,用于加快对数据的查询过程。 索引依托于存储引擎,因此存储引擎不一样的话,可能其支持的索引页不一样。一般来说,每个表支持的索引至少有16个。

索引的优势
  1. 加快数据的查询
  2. 唯一索引,可以保证伟一行的唯一性
  3. 建立索引之后,在一些排序和分组的sql性能得到提升。
索引的缺点
  1. 索引是物理文件,因此需要消耗存储空间。例如,主键索引的文件大小就比表还大。
  2. 由于建立了索引,需要进行维护,数据在增删改查的时候,会带来额外的开销。
索引的类型
  1. 主键索引:它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。注意:一个表只能有一个主键。
  2. 唯一索引:列的值是唯一的时候。但允许哟unull。
  3. 组合索引 INDEX:即一个索引包含多个列,多用于避免回表查询。可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引:
  4. 引一经创建不能修改,如果要修改索引,只能删除重建。
聚簇索引和非聚簇索引
  1. 聚簇索引的叶子节点存放了数据,非聚簇索引的叶子节点存放的是指针。InnoDB 主键使用的是聚簇索引,MyISAM 不管是主键索引,还是二级索引使用的都是非聚簇索引。
  2. 当你需要取出一定范围内的数据时,用聚簇索引也比用非聚簇索引好。
  3. 当通过聚簇索引查找目标数据时理论上比非聚簇索引要快,因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。
  4. 二级索引和非聚簇索引的区别:二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值,而不是行指针,非聚簇索引存储的是指针或者说是地址。
  5. 采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多,因为插入要保证主键不能重复,判断主键不能重复,采用的方式在不同的索引下面会有很大的性能差距,聚簇索引遍历所有的叶子节点,非聚簇索引也判断所有的叶子节点,但是聚簇索引的叶子节点除了带有主键还有记录值,记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。
覆盖索引

mysql 可以使用索引直接来获取列的数据,这样就可以不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据,那么还有什么必要再回表查询呢?如果一个索引包含(覆盖)所有要查询的字段的值,那么就称为“覆盖索引”。

  1. InnoDB 的聚簇索引,覆盖索引对InnoDB 表的特别有用。InnoDB 的二级索引在叶子节点保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。
  2. select id from order where user_id between 1 and 3 这时候只需要查ID 的值,而ID 已经在user_id 索引树上,因此可以直接提供查询结果,不需要回表。

多版本控制器 MVCC

  解决读-写冲突问题。不用加锁,通过一定机制生成一个数据请求时间点时的一致性数据快照, 并用这个快照来提供一定级别 (语句级或事务级) 的一致性读取。这样在读操作的时候不需要阻塞写操作,写操作时不需要阻塞读操作。Mysql的大多数事务型存储引擎实现都不是简单的行级锁,基于并发性能考虑,一般都实现了MVCC多版本并发控制。MVCC是通过保存数据在某个时间点的快照来实现的。不管事务执行多长时间,事务看到的数据都是一致的。 Mysql的大多数事务型存储引擎实现都不是简单的行级锁,基于并发性能考虑,一般都实现了MVCC多版本并发控制。MVCC是通过保存数据在某个时间点的快照来实现的。不管事务执行多长时间,事务看到的数据都是一致的。

  1. innodb存储引擎中,每行数据都包含了一些隐藏字段:DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR和DELETE_BIT。

  2. 读操作分成两类:快照读和当前读。快照读:简单的select操作属于快照读,不加锁。当前读:特殊的读操作,插入/更新/删除操作,属于当前读,需要加锁。

  3. BD_TRX_ID:用于标志最有一次操作这个行的事务。delete对于数据库的操作来说就是一次简单的update操作,就是将delete标志位置为1。

  4. BD_ROLL_PTR:行指针,指向undo日志,用于进行事务失败的时候回滚。或者用于寻找之前的版本的信息。

  5. DB_ROW_ID: 自增的主键值。用于生成聚簇索引

  6. DELETD_BIT:删除标志位,删除某个行的时候其实就是简单的对这个标志的更新。同时,事务标志位位本次事物的id即可。

  7. insert的时候,简单的添加一条记录,填写事务的id,回滚指针指向null

  8. delete的时候,加单的修改事务id为当前的事务,同时删除标志置为1.

  9. update的时候,先将未修改的时候的记录写到undo日志中,之后进行插入一行新的数据,回滚指针指向刚记录的undo日志。这样就会形成一条版本链。我们可以通过版本链来找到合适的数据。

  10. read view 版本链中哪些版本对当前事务可见?InnoDB 为了解决这个问题,设计了 ReadView(可读视图)的概念。在 RR 隔离级别下,每个事务 touch first read 时(本质上就是执行第一个 SELECT 语句时,后续所有的 SELECT 都是复用这个 ReadView,其它 update, delete, insert 语句和一致性读 snapshot 的建立没有关系),会将当前系统中的所有的活跃事务拷贝到一个列表生成ReadView。注意,是此时的,瞬间的一个快照,表示的是当前世界中所有的活跃的事务。这里就会出现活跃事务的上界和下届。

  11. 如果当前的行中事务的id 小于活跃事务的下届,表示的是这个事务是由之前的事务已经完成了的。因此,这个行对本次的事务是可见的。

  12. 如果行记录中的事务是大于活跃事务的上界的,表示这个记录是由之后的事务产生的。显然,此时这个记录对本事务是不可见的。(不然会出现不可重复读或者幻读等)。此时,根据该事物的undo指针,进行版本的回溯,直到找打本事务可见的记录。

  13. 如果该行记录的事务id是在活跃事务中间的话,表明改行是由本次活跃的事务修改的,但是未提交。此时,遍历活跃事务表,如果找到对应的事务id,表明此时的行事务还未提交,对本次事务是不可见的,根据undo指针回溯上个版本,继续进行判断。如果找不到对应的事务id,表明此时的该行的事务是已经提交了的,对本次事务是可见的。

  14. RC、RR 两种隔离级别的事务在执行普通的读操作时,通过访问版本链的方法,使得事务间的读写操作得以并发执行,从而提升系统性能。RC、RR 这两个隔离级别的一个很大不同就是生成 ReadView 的时间点不同,RC 在每一次 SELECT 语句前都会生成一个 ReadView,事务期间会更新,因此在其他事务提交前后所得到的 m_ids 列表可能发生变化,使得先前不可见的版本后续又突然可见了。而 RR 只在事务的第一个 SELECT 语句时生成一个 ReadView,事务操作期间不更新。

RC 下的 MVCC 判断流程

我们现在回看刚刚的查询过程,为什么事务 B 在 RC 隔离级别下,两次查询的 x 值不同。RC 下 ReadView 是在语句粒度上生成的。

当事务 A 未提交时,事务 B 进行查询,假设事务 B 的事务 ID 为 300,此时生成 ReadView 的 m_ids 为 [200,300],而最新版本的 trx_id 为 200,处于 m_ids 中,则该版本记录不可被访问,查询版本链得到上一条记录的 trx_id 为 100,小于 m_ids 的最小值 200,因此可以被访问,此时事务 B 就查询到值 10 而非 20。

待事务 A 提交之后,事务 B 进行查询,此时生成的 ReadView 的 m_ids 为 [300],而最新的版本记录中 trx_id 为 200,小于 m_ids 的最小值 300,因此可以被访问到,此时事务 B 就查询到 20

RR 下的 MVCC 判断流程

如果在 RR 隔离级别下,为什么事务 B 前后两次均查询到 10 呢?RR 下生成 ReadView 是在事务开始时,m_ids 为 [200,300],后面不发生变化,因此即使事务 A 提交了,trx_id 为 200 的记录依旧处于 m_ids 中,不能被访问,只能访问版本链中的记录 10。

详细的博客介绍:MVCC MVCC 版本链

常见的问题
  1. Innodb 支持双向链表 – 每一个叶子都包含指向前驱和后继节点的指针. 请注意使用“ORDER BY DESC”这个优化选项并不是真正的需求 – 即使没有叶子指向其他的Page,你仍然可以向任意的方向遍历BTREE. 如若如此,这仅仅只是让搜索变得开销相对昂贵罢了.
  2. like有索引吗?
  3. like %keyword 索引失效,使用全表扫描。
  4. like keyword% 索引有效。
  5. like %keyword% 索引失效,也无法使用反向索引。
连接
  1. 先说什么是交叉连接: 交叉连接又叫笛卡尔积,它是指不使用任何条件,直接将一个表的所有记录和另一个表中的所有记录一一匹配。

  2. 内连接 则是只有条件的交叉连接,根据某个条件筛选出符合条件的记录,不符合条件的记录不会出现在结果集中,即内连接只连接匹配的行。外连接 其结果集中不仅包含符合连接条件的行,而且还会包括左表、右表或两个表中的所有数据行,这三种情况依次称之为左外连接,右外连接,和全外连接。

  3. 左外连接,也称左连接,左表为主表,左表中的所有记录都会出现在结果集中,对于那些在右表中并没有匹配的记录,仍然要显示,右边对应的那些字段值以NULL来填充。

  4. 右外连接,也称右连接,右表为主表,右表中的所有记录都会出现在结果集中。左连接和右连接可以互换,MySQL目前还不支持全外连接。

explain慢查询优化

  当出现慢查询的时候,可能需要对查询的过程进行跟踪,此时可以在对应的查询语句上加一个 explain 来查询SQL的执行情况。常见的字段以及意义有:

  1. select_type:简单的查询语句,日过出现复杂的子查询的话,会有PRIMARY,UNION UNION_RESULT等。
  2. table: 对应的查询表
  3. type: 查询的类型,是全表扫描 all,还是索引 index, range, ref等。
  4. possible_key:可能带来高效查询的索引推荐等。
  5. key:mysql 决定采用的索引

日志

  1. binlog: 二进制日志主要记录MySQL数据库的变化,二进制日志以一种有效的格式,并且是事务安全的方式包含更新日志中可用的信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据。还包含关于每个更新数据库的语句的执行时间,它不包含没有修改任何数据的语句。使用二进制日志的主要目的是最大可能地恢复数据库。
  2. 慢查询日志:顾名思义,慢查询日志中记录的是执行时间较长的query,也就是我们常说的slow query。 慢查询日志采用的是简单的文本格式,可以通过各种文本编辑器查看其中的内容。其中 记录了语句执行的时刻,执行所消耗的时间,执行用户,连接主机等相关信息。 慢查询日志的作用: 慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志,可以查找出哪些查询语句的执行效率很低,以便进行优化。
  3. 错误日志:默认情况下错误日志大概记录以下几个方面的信息:服务器启动和关闭过程中的信息(未必是错误信息,如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等)、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息,MySQL有很多系统变量可以设置,系统变量设置不同,会导致系统运行状态的不同。因此mysql提供两组命令,分别查看系统设置和运行状态。

事务日志详解

  1. redo :redo log通常是物理日志,记录的是数据页的物理修改,而不是某一行或某几行修改成怎样怎样,它用来恢复提交后的物理数据页(恢复数据页,且只能恢复到最后一次提交的位置)。
  2. undo undo用来回滚行记录到某个版本。undo log一般是逻辑日志,根据每行记录进行记录。
  3. redo log不是二进制日志。虽然二进制日志中也记录了innodb表的很多操作,也能实现重做的功能,但是它们之间有很大区别。
  4. 二进制日志是在存储引擎的上层产生的,不管是什么存储引擎,对数据库进行了修改都会产生二进制日志。而redo log是innodb层产生的,只记录该存储引擎中表的修改。并且二进制日志先于redo log被记录。
  5. 二进制日志记录操作的方法是逻辑性的语句。即便它是基于行格式的记录方式,其本质也还是逻辑的SQL设置,如该行记录的每列的值是多少。而redo log是在物理格式上的日志,它记录的是数据库中每个页的修改。
  6. 二进制日志只在每次事务提交的时候一次性写入缓存中的日志"文件"(对于非事务表的操作,则是每次执行语句成功后就直接写入)。而redo log在数据准备修改前写入缓存中的redo log中,然后才对缓存中的数据执行修改操作;而且保证在发出事务提交指令时,先向缓存中的redo log写入日志,写入完成后才执行提交动作。
MyISAM和InonDB

这两个存储引擎只支持BTree索引。

索引设计的原则
  1. 经常出现在where中的字段,或者在连接字句中指定的列
  2. 尽量使用较短的索引。
  3. 不要过度的建立和使用索引,因为对索引的维护可能会带来巨大的开销和性能问题。

最佳实践

一般我们是先优化,优化一些慢查询,优化业务逻辑的调用或者加入缓存等,如果真的优化到没东西优化了然后才上集群,先读写分离,读写分离之后顶不住就再分库分表。

慢查询

大表优化

当单个表的规格很大的时候CRUD速度会受到影响。此时有一些常见的手段。

限制数据范围

不允许全表扫描的语句。或者自动的限制的条件等。

分表
水平分

将表水平切割,这样表的数据结构不会该表,但是有了弹性拓展性,可以支持分布式的存储。而且不会带来业务逻辑的增加。

垂直分

根据业务特性,将表垂直切割,就是按列分割成多个表。垂直分割之后,一个得到的数据block会变小,减少了IO。但是会出现主键冗余和会让事务变得很复杂。

分库分表之后,最佳实践就是维护一个全局的主键。可以使用一些开源的主键生产系统。

读写分离

这是最常见的手段,特别是很多度多写少的业务的时候。主库写,从库读。主从数据库之间需要做好同步。在单机的情况下,一般我们做数据库优化都会加索引,但是加了索引对查询有优化,但是会影响写入,因为写入数据会更新索引。所以做了主从之后,我们可以单独的针对从库(读库)做索引上的优化,而主库(写库)可以减少索引而提高写的效率。

  1. 主从同步延迟

主库有数据写入之后,同时也写入在binlog(二进制日志文件)中,从库是通过binlog文件来同步数据的,这期间会有一定时间的延迟,可能是1秒,如果同时有大量数据写入的话,时间可能更长。这会导致什么问题呢?比如有一个付款操作,你付款了,主库是已经写入数据,但是查询是到从库查,从库里还没有你的付款记录,所以页面上查询的时候你还没付款。那可不急眼了啊,吞钱了这还了得!打电话给客服投诉!

读写分类的时候,或者说有主从库的时候,关键业务其实还是在主数据库上进行。例如一些付款等操作。或者写完立刻读等操作。主要的解决方法有:

  1. 二次读取:当从库读取不到的时候,去主库读取一次。
  2. 关键业务读写都在主库
  3. 写之后马上读的操作也直接放在主库。

B+ Tree数据结构详解

常见的索引的类型

  1. Hash: hash索引是通过比较hash值从而进行等值查找的过程,它不适合于那些范围查询的场景。hash无法机进行排序操作。而且,不支持最左匹配原则。而且,hash索引还会发生碰撞,大量的碰撞势必会带来巨大的效率问题。
  2. B树索引:B树索引会将数据和索引一起存储在节点中,这时候回使得美国节点可以存放的关键字数量减少,或者说是指针减少,这样进行索引的时候,可能需要更多次的IO.而B+树结构中,非叶子节点存储的是指针而已,这样每个节点可以存放的只指针的数量就很大了,可以较少IO的次数。这里的IO主要是索引也是一种存放在硬盘的文件,查询的时候需要通过IO读取到内存中。
  3. 平衡树:平衡树和二叉搜索树相比,可以使得查询时间为logN./但是每个节点中的数据只有两路的话,会使得树的高度很高,这回导致IO效率很低。

其他问题

N+1 问题

N+1是由于使用orm而带来的一个问题,本质上是由于查询的时候由于外键的存在,每次对外键关联的属性取值的时候,都会发生一条select语句而带来的问题。本来一句select就可以得到N条数据,但是这N条数据会每次都去select 从而得到外键的属性。因此会执行N+1 个语句。

  1. 二级缓存
  2. fetch=jion 也就是传说中的迫切的左连接,多的是左连接一的一边。

Sql 最佳实践

1. 放弃索引的情况

  • 在where子句中进行null值判断的话会导致引擎放弃索引而产生全表扫描
  • 避免在where子句中使用!= ,< >这样的符号,否则会导致引擎放弃索引而产生全表扫描
  • 避免在where子句中使用or来连接条件,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
  • 避免在where子句中=的左边使用表达式操作或者函数操作
  • 避免在where子句中使用like模糊查询 %在前面的时候不会使用索引
  • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引(少见)
  • 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

2. 建立索引的情况

主键自动建立唯一索引 频繁作为查询条件的字段应该创建索引(where 后面的语句或者ORDER BY 语句中出现的列) 查询中与其他表关联的字段,外键关系建立索引 单键、组合索引的选择问题(在高并发下推荐创建组合索引) 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度 查询中统计或者分组的字段

3. 不建立索引的情况

表记录太少 经常增删改的表 数据重复且分布平均的表字段,因此只为最经常查询和最经常排序的数据列创建索引。注意,如果某个数据列包含许多重复的内容,为他建立索引就没有太大的实际效果。(比如14亿中国人的国籍都是中国,这种类型字段就可以不建立索引,或者性别) Where条件里用不到的字段不创建索引

4. 表的连接

  • 内连接:返回的是符合条件的两个表的交集
  • 左连接,以左边的表为主表,将右边的表全部信息纳入进来,右边的记录没用的时候回采用null进行填充
  • 右连接:以右边的表为主表,将左边的表格的信息全部纳入进来,其中不符合的采用null进行填充。

5. 为什么要使用联合索引

减少开销。建一个联合索引(col1,col2,col3),实际相当于建 (col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销! 覆盖索引。对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。 效率高。索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w10% 10% *10%=1w,效率提升可想而知! 最左匹配原则:最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

6. Jion的三种算法的详解

  • Hash Join: 是做大数据集连接的时候使用的,用于等号操作的表连接。一般是将较小的表的join on 的字段建立哈希表存放到内存中(一般能全部存放到内存中性能最好,如果表太大会导致分段存放到内存,导致多次IO,性能下降)。将小表建立hash散列的过程就是一个build过程。之后大表直接根据join on 的字段进行查表即可。这次的过程就是嗅探。整个流程实现强烈依赖CPU,但是可以实现O(n1+n2)的时间复杂度。
  • Nested Loop Join:循环嵌套连接。顾名思义,就是对两个表的字段直接采用两层的for循环进行匹配来join表。内表被外表驱动,因此,选择较小的表作为外表,同时,内表join on 的字段建立索引,可以起到很好地性能优化。注意,外表不用建立索引,即使已经建立索引,在做nested loop join 的时候,也不会使用,但是内表就会使用建立的索引。
    • i. 内连接:查询分析器会自动的优化,将小表作为驱动表
    • ii. 左连接:左边的表为驱动表
    • iii. 右连接:右边的表为驱动表
  • c) Merge Sorted Join:如果两个表已经排序完成了,那么此时的merge join的性能会比较好。