MySQL 知识总结

发表于 更新于 分类于 阅读次数： 本文字数： 12k 阅读时长 ≈ 11 分钟
采用思维导图的形式与大纲的形式，一网打尽MySQL面试核心知识点。

MySQL

思维导图

MySQL

存储引擎

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。
主索引是聚集索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

  • 创建表后生成的文件

    • frm:创建表的语句
    • idb:表里面的数据+索引文件

  • 引擎底层实现（聚集索引方式）

    InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，如左下图所示，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name='Bob'。
这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY！拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。
提供了大量的特性，包括压缩表、空间数据索引等。
不支持事务。
不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。
可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。
如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

  • 创建表后生成的文件

    • frm:创建表的语句
    • MYD:表里面的数据文件（myisam data）
    • MYI:表里面的索引文件（myisam index）

  • 引擎底层实现（非聚集索引方式）

    MyISAM 用的是非聚集索引方式，即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

当我们为某个字段添加索引时，我们同样会生成对应字段的索引树，该字段的索引树的叶子节点同样是记录了对应数据的物理地址，然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

InnoDB 与 MyISAM 的对比

  • 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
  • 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
  • 外键：InnoDB 支持外键。
  • 备份：InnoDB 支持在线热备份。
  • 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
  • 其它特性：MyISAM 支持压缩表和空间数据索引。

应用场景

  • MyISAM 管理非事务表，它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。【偏读】
  • InnoDB 用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用 InnoDB ,这样可以提高多用户并发操作的性能。【偏写】

索引

优点

  • 1.大大加快数据的检索速度;
  • 2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
  • 3.加速表和表之间的连接;
  • 4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

  • 1.索引需要占物理空间。
  • 2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

MySQL 索引结构

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

  • B+ 树索引：这是MySQL里默认和最常用的索引类型。
  • HASH索引：由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
  • 全文索引：目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE "%word%"这类针对文本的模糊查询效率较低的问题。
  • 空间数据索引：R-TREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

索引底层数据结构选型

  • HASH

    使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

  • 二叉查找树（BST）

    二叉查找树的时间复杂度是 O(logn)，从检索效率上看来是能做到高速检索的。
    范围查找也算是比较容易实现。
    但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O(n)，检索性能急剧下降。
    在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。因此，简单的二叉查找树存在不平衡导致的检索性能降低的问题，是不能直接用于实现 Mysql 底层索引的。

  • 红黑树

    红黑树，这是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O(logn)），也就保证了查找效率不会明显减低。
    从根本上上看，红黑树并没有完全解决二叉查找树虽然这个“右倾”趋势，虽远没有二叉查找树退化为线性链表那么夸张，但是数据库中的基本主键自增操作，主键一般都是数百万数千万的，如果红黑树存在这种问题，对于查找性能而言也是巨大的消耗，我们数据库不可能忍受这种无意义的等待的。

  • 自平衡二叉查找树（AVL）

    不错的查找性能（O(logn)），不存在极端的低效查找的情况。
    可以实现范围查找、数据排序。

    数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，磁盘IO还是过多，所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

  • B-树

    优秀检索速度，时间复杂度：B 树的查找性能等于 O（h*logn），其中 h 为树高，n 为每个节点关键词的个数；
    尽可能少的磁盘 IO，加快了检索速度；
    可以支持范围查找。

  • B+树

    B 树和 B+树有什么不同呢？
    第一，B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。
    第二，B+树的叶子节点是数据阶段用了一个链表串联起来，便于范围查找。

    通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。因此 Mysql 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

单值与复合索引

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

  • 单值索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
  • 复合索引，即一个索引包含多个列。

主键索引与唯一索引

  • 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键

  • 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

    可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
    可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

  • 区别

    主键约束比唯一索引约束严格，当没有设定主键时，非空唯一索引自动称为主键。
    主键和唯一索引都要求值唯一，但是他们还是有区别的：

    主键是一种约束，唯一索引是一种索引
    一张表只能有一个主键，但是可以创建多个唯一索引
    主键创建后一定包含一个唯一索引，唯一索引并一定是主键
    主键不能为null，唯一索引可以为null
    主键可以作为外键，唯一索引不行
    主键产生唯一的聚集索引，唯一索引产生唯一的非聚集索引

覆盖索引

  • 索引包含所有需要查询的字段的值。

  • 优点

      • 索引通常远小于数据行的大小，只读取索引能大大减少数据访问量。
      • 一些存储引擎（例如 MyISAM）在内存中只缓存索引，而数据依赖于操作系统来缓存。因此，只访问索引可以不使用系统调用（通常比较费时）。
      • 对于 InnoDB 引擎，若辅助索引能够覆盖查询，则无需访问主索引。

全文索引

  • 是目前搜索引擎使用的一种关键技术。
  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

前缀索引

  • 对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。
  • 前缀长度的选取需要根据索引选择性来确定。

事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

ACID 特性

  • 原子性（Atomicity）：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
  • 一致性（Consistency）：执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
  • 隔离性（Isolation）：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
  • 持久性（Durability）：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

并发事务带来的问题？

  • 更新丢失：多个事务同时修改某一行数据，后一次更新覆盖了前一次的更新操作
  • 脏读：事务T1更新了一行记录内容，但并没有提交修改。事务T2读取更新后的行，然后T1执行回滚操作。读取了刚才所做的修改。现在T2读取的行就无效了。（一个事务读取了另一个事务未提交的数据）
  • 不可重复读： 事务T1读取了一行记录，紧接着T2修改了T1刚才读取的那一行记录，然后T1又再次读取这行记录，发现与刚才读取的结果不同。
  • 幻读：事务T1读取一个结果集，然后T2事务在T1结果集范围内插入一行记录。然后T1再次对表进行检索，发现多了T2插入的数据。

事务的隔离级别

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。

  • 目的是解决多个事务操作同一数据库对象时出现的冲突问题。
    • Serializable (可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰。
    • Repeatable read (可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改。【MySQL默认隔离级别】
    • Read committed (读已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。【Oracle默认隔离级别】
    • Read uncommitted (读未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
  • 对比图示

数据库锁

共享锁与排它锁

  • 共享锁（读锁/S锁）：若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁为止。
  • 排他锁（写锁/X锁）：若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。

事务隔离与锁的关系

  • 事务隔离主要就是对事务的读写之间进行隔离，通过锁来实现隔离。

事务的隔离级别与锁的关系

  • 通过对事务的读写操作加锁情况的不同，划分出不同的事务隔离级别

三级封锁协议

  • 1 级封锁协议：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。 1级封锁协议可防止丢失修改，并保证事务T是可恢复的。在1级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，所以它不能保证可重复读和不 读"脏"数据。 【对应读未提交】
  • 2 级封锁协议：1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，读完后即可释放S锁。2级封锁协议除防止了丢失修改，还可进一步防止读"脏"数据。 【对应读已提交】
  • 3 级封锁协议：1级封锁协议加上事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。3级封锁协议除防止了丢失修改和不读’脏’数据外，还进一步防止了不可重复读。【对应可重复读】

行级锁（InnoDB）、页级锁（BDB）、表级锁（MyISAM）

  • 表级锁定（table-level）：表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。（避免死锁）最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发度大打折扣。表级锁分为读锁和写锁。

    • MyISAM锁优化建议

      • 1、缩短锁定时间
      • 2、分离能并行的操作
      • 3、合理利用读写优先级

  • 行级锁定（row-level）：行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。（容易发生死锁）InnoDB实际上有四种锁，即共享锁（S）、排他锁（X）、意向共享锁（IS）、意向排他锁（IX）。

    • 如何锁定一行？

      • select ... from ... where ... for update;
      • 索引失效，会导致行锁变表锁!!!

    • 如何分析行锁定？

      • 通过检查InnoDB_row_lock状态变量来分析系统上的行锁争夺情况：show status like 'innodb_row_lock%'

        Innodb_row_lock_current_waits: 当前正在等待锁定的数量
        Innodb_row_lock_time: 从系统启动到现在锁定总时间长度【重要】
        Innodb_row_lock_time_avg: 每次等待所花平均时间【重要】
        Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花的时间
        Innodb_row_lock_waits: 系统启动后到现在总共等待的次数【重要】

  • 页级锁定（page-level）：页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。（和行级锁一样，会发生死锁）。

InnoDB 锁的算法

  • Record lock：单个行记录上的锁

  • Gap lock：间隙锁，锁定一个范围，不包括记录本身

    • 定义：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
    • 危害：当锁定一个范围键值之后，某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

  • Next-key lock：record+gap 锁定一个范围，包含记录本身

乐观锁与悲观锁

  • 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

  • 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

  • 应用场景分析

    • 乐观锁->多读场景，有助于提高并发度
    • 悲观锁->多写场景

主从复制

定义：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

  • 作用

    • 主数据库出现问题，可以切换到从数据库。
    • 可以进行数据库层面的读写分离。
    • 可以在从数据库上进行日常备份。

  • 解决的问题

    • 数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
    • 负载均衡：降低单个服务器的压力
    • 高可用和故障切换：帮助应用程序避免单点失败
    • 升级测试：可以用更高版本的MySQL作为从库

原理与流程

    • binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
    • I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的中继日志（Relay log）。
    • SQL 线程 ：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

提高性能的原因

    • 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
    • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
    • 增加冗余，提高可用性。

实现

日志

日志分类

  • 1.错误日志(error log)：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。

  • 2.查询日志(general log)：记录建立的客户端连接和执行的语句。

  • 3.二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制。包含三种模式：

    • statement：基于SQL语句的模式，某些语句中含有一些函数，例如 UUID NOW 等在复制过程可能导致数据不一致甚至出错。
    • row：基于行的模式，记录的是行的变化，很安全。但是 binlog 的磁盘占用会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。
    • mixed：混合模式，根据语句来选用是 statement 还是 row 模式。

  • 4.慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。

  • 5.中继日志(relay log)：主从复制时使用的日志。

事务日志

  • undolog

    undo log 是回退日志，提供 回滚 操作。

    undo log 用来回滚行记录到某个版本。undo log 一般是逻辑日志，根据每行记录进行记录。

  • redolog

    redo log 是重做日志，提供 前滚 操作。

    redo log 通常是 物理 日志，记录的是 数据页 的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

    在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是 Redo Log 已经持久化。系统可以根据 Redo Log 的内容，将所有数据恢复到最新的状态。

  • 只使用undolog来保障原子性和持久性的缺陷

    • 事务提交前需要将 Undo Log 写磁盘（提供可回滚功能，保证原子性），这会造成多次磁盘 IO（不考虑各种优化例如 SQL 解析优化等），这些 IO 算是顺序 IO；

    • 事务提交后需要将数据立即更新到数据库中，这又会造成至少一次磁盘 IO，这是一次随机 IO。

    • 解决方案？

      如何优化？事务提交后如果能够将数据缓存一段时间，而不是立即更新到数据库，就能将一次次的随机 IO 打包变成一次 IO，可以提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即 redo log。redo 解决的问题之一就是事务执行过程中的强制刷脏。

      在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是 Redo Log 已经持久化。系统可以根据 Redo Log 的内容，将所有数据恢复到最新的状态。

  • undolog 与 redolog 的结合

    • Undo 记录某 数据 被修改 前 的值，可以用来在事务失败时进行 rollback；
    • Redo 记录某 数据块 被修改 后 的值，可以用来恢复未写入 data file 的已成功事务更新的数据。
    • Redo Log 保证事务的持久性
    • Undo Log 保证事务的原子性（在 InnoDB 引擎中，还用 Undo Log 来实现 MVCC）

  • 两者为何缺一不可？

    • 假设只有 undo-log：那么就必须保证提交前刷脏完成，否则宕机时有些修改就在内存中丢失了，破坏了持久性。（这样带来了一个问题，那就是前面提到的性能差）
    • 假设只有 redo-log：那么就不能随心所欲地在事务提交前刷脏，即无法支持大事务。（假如、某张表有 100 亿的 8 字节整数数据，就算不考虑其他东西带来的损耗，光 update 整张表至少要消耗 80G 的内存。如前所述，有了 undo-log，就可以随便刷脏。）

  • undolog/redolog 与 binlog 的区别

    • 层次不同。redo/undo 是 innodb 引擎层维护的，而 binlog 是 mysql server 层维护的，跟采用何种引擎没有关系，记录的是所有引擎的更新操作的日志记录。
    • 记录内容不同。redo/undo 记录的是 每个页/每个数据 的修改情况，属于物理日志+逻辑日志结合的方式（redo log 是物理日志，undo log 是逻辑日志）。binlog 记录的都是事务操作内容，binlog 有三种模式：Statement（基于 SQL 语句的复制）、Row（基于行的复制） 以及 Mixed（混合模式）。不管采用的是什么模式，当然格式是二进制的，
    • 记录时机不同。redo/undo 在 事务执行过程中 会不断的写入，而 binlog 是在 事务最终提交前 写入的。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

SQL语句相关

SQL语句分类

  • 数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER

    • 主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。

  • 数据查询语言DQL（Data Query Language）SELECT

    • 这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。

  • 数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE

    • 主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。

  • 数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

    • 主要为以上操作 即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

键

  • 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
  • 候选键：是最小超键，即没有冗余元素的超键。
  • 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
  • 外键：在一个表中存在的另一个表的主键称此表的外键。

SQL约束

  • NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
  • UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
  • PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
  • FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
  • CHECK: 用于控制字段的值范围。

关联查询

  • 交叉连接（CROSS JOIN）

    • 没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义

  • 内连接（INNER JOIN）

    • INNER JOIN可以缩写为JOIN

      • 等值连接：ON A.id=B.id
      • 不等值连接：ON A.id > B.id
      • 自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

  • 外连接（LEFT JOIN/RIGHT JOIN）

    • 左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
    • 右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

  • 联合查询（UNION与UNION ALL）

    • 就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
    • 如果使用UNION ALL，不会合并重复的记录行
    • 效率 UNION 高于 UNION ALL

  • 全连接（FULL JOIN）

    • MySQL不支持全连接
    • 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用

in 与 exists 的区别

  • mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。

  • 使用选择

    • 如果查询的两个表大小相当，那么用in和exists差别不大。
    • 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
    • not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

char 与 varchar 的区别

  • CHAR的长度是固定的，VARCHAR的长度是可以变化的
  • VARCHAR比CHAR要节省空间，但VARCHAR在效率上比CHAR差一些，所以如果想获得效率，就必须牺牲一定的空间，这就是在数据库设计上常说的‘以空间换效率。

Float 与 Double 的区别

  • FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
  • DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

drop delete truncate

  • 在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

SQL 优化

    1. 用查询缓存优化查询（mysql默认开启了查询缓存）
    1. 用explain关键字来处理select查询
    1. 当只有一行数据时使用 limit 1
    1. 为搜索字段建索引
    1. 避免select *
    1. 永远为每一张表设置一个Id
    1. 拆分大的delete或insert语句
    1. 选择正确的存储引擎
    1. 读写分离
    1. 使用order by null禁用排序
    1. 使用join 代理子查询（子查询会默认建临时表）

基于索引的 SQL 优化

    1. 全值匹配最佳；
    1. 最佳左前缀法则：查询从索引的最左前列开始，并且不跳过索引的中间列；（带头大哥不能死，中间兄弟不能断）
    1. 不要在索引上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效转而全表扫描；
    1. 存储引擎不能使用索引中范围条件右边的列（范围之后全失效）；
    1. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *；
    1. mysql 在使用不等于（!= 或<>）的时候，无法使用索引会导致全表扫描；
    1. is null、is not null也无法使用索引；
    1. like以通配符开头（'%abc...'），mysql索引失效会变成全表扫描的操作；
    • 解决两边都带%的查询且索引不失效的方法：覆盖索引
    1. varchar类型，字符串不加单引号，索引会失效；
    1. 少用or，用它来连接时索引会失效；

查询截取优化

  • 永远小表驱动大表，即小的数据集驱动大的数据集；

  • order by优化：

      • order by 能使用索引最左前缀
      • 如果 where 使用索引的最左前缀定义为常量，则能使用索引
      • 不能使用索引进行排序：i. 排序不一致、ii. 丢失大哥、iii. 丢失中间兄弟、iv. 不为索引的一部分、v. 大哥值不确定

  • group by优化：