MySQL数据库：求一条sql语句

点击联系发帖人 时间：2022-09-27 09:28
sql取第一条和最后一条

可以从两个维度回答这个问题：索引哪些情况会失效，索引不适合哪些场景

  • 查询条件包含 or，会导致索引失效。

  • 隐式类型转换，会导致索引失效，例如 age 字段类型是 int，我们 where age = “1”，这样就会触发隐式类型转换。

  • like 通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效

  • 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。

  • 对索引字段进行函数运算。

  • 对索引列运算（如，+、-、*、/），索引失效。

  • 索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。

  • 相 join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算

  • mysql 估计使用全表扫描要比使用索引快，则不使用索引。

  • 数据量少的不适合加索引

  • 更新比较频繁的也不适合加索引

  • 离散性低的字段不适合加索引（如性别）

  • 分析 sql 加锁情况

可以从这几个维度回答这个问题：

分库分表方案，分库分表中间件，分库分表可能遇到的问题

  • 水平分库：以字段为依据，按照一定策略（hash、range 等），将一个库中的数据拆分到多个库中。

  • 水平分表：以字段为依据，按照一定策略（hash、range 等），将一个表中的数据拆分到多个表中。

  • 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

  • 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

分库分表可能遇到的问题

  • 事务问题：需要用分布式事务啦

  • 跨节点 Join 的问题：解决这一问题可以分两次查询实现

  • 跨节点的 count,order by,group by 以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。

  • 数据迁移，容量规划，扩容等问题

  • ID 问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑 UUID

  • 跨分片的排序分页问题（后台加大 pagesize 处理？）

  • Innodb 表需要更多的内存和存储，而 MyISAM 可被压缩，存储空间较小，。

  • Innodb 按主键大小有序插入，MyISAM 记录插入顺序是，按记录插入顺序保存。

  • InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全，与 MyISAM 比 InnoDB 写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引

  • InnoDB 属于索引组织表，使用共享表空间和多表空间储存数据。MyISAM 用.frm、.MYD、.MTI 来储存表定义，数据和索引。

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是 B 树，而偏偏是 B + 树呢？

为什么不是一般二叉树？

1）当数据量大时，树的高度会比较高（树的高度决定着它的 IO 操作次数，IO 操作耗时大），查询会比较慢。 2）每个磁盘块（节点 / 页）保存的数据太小（IO 本来是耗时操作，每次 IO 只能读取到一个关键字，显然不合适），没有很好的利用操作磁盘 IO 的数据交换特性，也没有利用好磁盘 IO 的预读能力（空间局部性原理），从而带来频繁的 IO 操作。

为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是 B 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

那为什么不是 B 树而是 B + 树呢？

1）B+Tree 范围查找，定位 min 与 max 之后，中间叶子节点，就是结果集，不用中序回溯 2）B+Tree 磁盘读写能力更强（叶子节点不保存真实数据，因此一个磁盘块能保存的关键字更多，因此每次加载的关键字越多） 3）B+Tree 扫表和扫库能力更强（B-Tree 树需要扫描整颗树，B+Tree 树只需要扫描叶子节点）

  • 一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。

  • 聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

  • 索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

  • 聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

何时使用聚集索引或非聚集索引？

方案一：如果 id 是连续的，可以这样，返回上次查询的最大记录 (偏移量)，再往下 limit

方案二：在业务允许的情况下限制页数： 建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

方案四：利用延迟关联或者子查询优化超多分页场景。（先快速定位需要获取的 id 段，然后再关联）

  • 数据库自增长序列或字段。

隔离性是指，多个用户的并发事务访问同一个数据库时，一个用户的事务不应该被其他用户的事务干扰，多个并发事务之间要相互隔离。

事务 A，先执行，处于未提交的状态：

事务 B，后执行，也未提交：

如果事务 B 能够读取到 (4, wangwu) 这条记录，事务 A 就对事务 B 产生了影响，这个影响叫做 “读脏”，读到了未提交事务操作的记录。

事务 B，后执行，并且提交：

事务 A，再次执行相同的查询：

这次是已提交事务 B 对事务 A 产生的影响，这个影响叫做 “不可重复读”，一个事务内相同的查询，得到了不同的结果。

事务 B，后执行，并且提交：

事务 A，首次查询了 id>3 的结果为 NULL，于是想插入一条为 4 的记录：

这次是已提交事务 B 对事务 A 产生的影响，这个影响叫做 “幻读”。

可以看到，并发的事务可能导致其他事务：

InnoDB 实现了四种不同事务的隔离级别：

不同事务的隔离级别，实际上是一致性与并发性的一个权衡与折衷。

InnoDB 的四种事务的隔离级别，分别是怎么实现的？

这种事务隔离级别下，select 语句不加锁。

此时，可能读取到不一致的数据，即 “读脏”。这是并发最高，一致性最差的隔离级别。

这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的 select 都会被阻塞住。

这是一致性最好的，但并发性最差的隔离级别。 在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

  • 在唯一索引上使用唯一的查询条件，会使用记录锁 (record lock)，而不会封锁记录之间的间隔，即不会使用间隙锁 (gap lock) 与临键锁 (next-key lock)

  • 范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生幻影行记录，以及避免不可重复的读

此时，其他事务的插入依然可以执行，就可能导致，读取到幻影记录。

要安全的修改同一行数据，就要保证一个线程在修改时其它线程无法更新这行记录。一般有悲观锁和乐观锁两种方案

悲观锁思想就是，当前线程要进来修改数据时，别的线程都得拒之门外～比如，可以使用 select…for update

以上这条 sql 语句会锁定了 User 表中所有符合检索条件（name='jay’）的记录。本次事务提交之前，别的线程都无法修改这些记录。

乐观锁思想就是，有线程过来，先放过去修改，如果看到别的线程没修改过，就可以修改成功，如果别的线程修改过，就修改失败或者重试。实现方式：乐观锁一般会使用版本号机制或 CAS 算法实现。

悲观锁她专一且缺乏安全感了，她的心只属于当前事务，每时每刻都担心着它心爱的数据可能被别的事务修改，所以一个事务拥有（获得）悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放才可以执行。

乐观锁的 “乐观情绪” 体现在，它认为数据的变动不会太频繁。因此，它允许多个事务同时对数据进行变动。实现方式：乐观锁一般会使用版本号机制或 CAS 算法实现。

  • 通过慢查询日志定位那些执行效率较低的 sql 语句

  • explain 分析低效 sql 的执行计划（这点非常重要，日常开发中用它分析 Sql，会大大降低 Sql 导致的线上事故）

select 查询语句是不会加锁的，但是 select for update 除了有查询的作用外，还会加锁呢，而且它是悲观锁哦。至于加了是行锁还是表锁，这就要看是不是用了索引 / 主键啦。 没用索引 / 主键的话就是表锁，否则就是是行锁。

  • 原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

  • 一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如 A 账户给 B 账户转 10 块钱，不管成功与否，A 和 B 的总金额是不变的。

  • 隔离性： 多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。简言之，就是事务之间是进水不犯河水的。

  • 持久性： 表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

事务 ACID 特性的实现思想

  • 原子性：是使用 undo log 来实现的，如果事务执行过程中出错或者用户执行了 rollback，系统通过 undo log 日志返回事务开始的状态。

  • 持久性：使用 redo log 来实现，只要 redo log 日志持久化了，当系统崩溃，即可通过 redo log 把数据恢复。

  • 隔离性：通过锁以及 MVCC, 使事务相互隔离开。

  • 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

某个表有近千万数据，可以考虑优化表结构，分表（水平分表，垂直分表），当然，你这样回答，需要准备好面试官问你的分库分表相关问题呀，如

  • 分表方案（水平分表，垂直分表，切分规则 hash 等）

  • 分库分表一些问题（事务问题？跨节点 Join 的问题）

  • 解决方案（分布式事务等）

除了分库分表，优化表结构，当然还有所以索引优化等方案～

复合索引，也叫组合索引，用户可以在多个列上建立索引，这种索引叫做复合索引。

当我们创建一个组合索引的时候，如 (k1,k2,k3)，相当于创建了（k1）、(k1,k2) 和 (k1,k2,k3) 三个索引，这就是最左匹配原则。

有关于复合索引，我们需要关注查询 Sql 条件的顺序，确保最左匹配原则有效，同时可以删除不必要的冗余索引。

假设表 A 表示某企业的员工表，表 B 表示部门表，查询所有部门的所有员工，很容易有以下 SQL:

可以抽象成这样的一个循环：

显然，除了使用 in，我们也可以用 exists 实现一样的查询功能，如下：

因为 exists 查询的理解就是，先执行主查询，获得数据后，再放到子查询中做条件验证，根据验证结果（true 或者 false），来决定主查询的数据结果是否得意保留。

那么，这样写就等价于：

同理，可以抽象成这样一个循环：

数据库最费劲的就是跟程序链接释放。假设链接了两次，每次做上百万次的数据集查询，查完就走，这样就只做了两次；相反建立了上百万次链接，申请链接释放反复重复，这样系统就受不了了。即 mysql 优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优。 因此，我们要选择最外层循环小的，也就是，如果 B 的数据量小于 A，适合使用 in，如果 B 的数据量大于

使用自增主键对数据库做分库分表，可能出现诸如主键重复等的问题。解决方案的话，简单点的话可以考虑使用 UUID 哈 自增主键会产生表锁，从而引发问题 自增主键可能用完问题。

innodb 存储引擎，会在每行数据的最后加两个隐藏列，一个保存行的创建时间，一个保存行的删除时间，但是这儿存放的不是时间，而是事务 id，事务 id 是 mysql 自己维护的自增的，全局唯一。

事务 ID=122 的事务，将 ID=1 的这一行删除了，此时就会将 ID=1 的行的删除事务 ID 设置成 122

Innodb 存储引擎，对于同一个 ID，不同的事务创建或修改，每个事务都有自己的快照（会插入一条记录）

在一个事务内查询的时候，mysql 只会查询创建事务 id <= 当前事务 id 的行，这样可以确保这个行是在当前事务中创建，或者是之前创建的；同时一个行的删除事务 id 要么没有定义（就是没删除），要么是比当前事务 id 大（在事务开启之后才被删除）；满足这两个条件的数据都会被查出来。

那么如果某个事务执行期间，别的事务更新了一条数据呢？这个很关键的一个实现，其实就是在 innodb 中，是插入了一行记录，然后将新插入的记录的创建时间设置为新的事务的 id，同时将这条记录之前的那个版本的删除时间设置为新的事务的 id。

现在 get 到这个点了吧？这样的话，你的这个事务其实对某行记录的查询，始终都是查找的之前的那个快照，因为之前的那个快照的创建时间小于等于自己事务 id，然后删除时间的事务 id 比自己事务 id 大，所以这个事务运行期间，会一直读取到这条数据的同一个版本。

主从复制分了五个步骤进行：

  • 步骤二：从库发起连接，连接到主库。

  • 步骤四：从库启动之后，创建一个 I/O 线程，读取主库传过来的 binlog 内容并写入到 relay log

一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作，但是从服务器的里面读取 binlog 的线程仅有一个，当某个 SQL 在从服务器上执行的时间稍长 或者由于某个 SQL 要进行锁表就会导致，主服务器的 SQL 大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

主从同步延迟的解决办法

可以参考沈剑老师的文章：

数据库连接池原理：在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。

应用程序和数据库建立连接的过程

  • 通过 TCP 协议的三次握手和数据库服务器建立连接

  • 发送数据库用户账号密码，等待数据库验证用户身份

  • 完成身份验证后，系统可以提交 SQL 语句到数据库执行

  • 把连接关闭，TCP 四次挥手告别。

  • 资源重用 (连接复用)

  • 新的资源分配手段 统一的连接管理，避免数据库连接泄漏

MySQL 分为 Server 层和存储引擎层两个部分，不同的存储引擎共用一个 Server 层。

Server 层：大多数 MySQL 的核心服务功能都在这一层，包括连接处理、授权认证、查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层：存储引擎负责 MySQL 中数据的存储和提取。服务器通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

MySQL 客户端与服务端的通信方式是 “半双工”，客户端一旦开始发送消息另一端要接收完整这个消息才能响应，客户端一旦开始接收数据就没法停下来发送指令，一请求一响应。

第一步，先连接到数据库上，当客户端（应用）连接到 MySQL 服务器时，服务器需要对其进行认证，认证基于用户名、原始主机信息和密码，一旦客户端连接成功，服务器会继续验证客户端是否具有执行某个特定查询的权限（例如，是否允许客户端对某一数据库的某一表执行 SELECT 语句）

输完命令之后，需要在交互对话里面输入密码，密码不建议在 - p 后面直接输入，这样会导致密码泄露。

第二步，查询缓存，每次 MySQL 执行过的语句及其结果会以 key-value 形式缓存在内存中，key 是查询语句，value 是查询结果。如果查询能够在缓存中找到 key，那么这个 value 就会被直接返回客户端。

但是大多数情况下我会建议不要使用缓存，因为查询缓存的失效非常频繁，只要对一个表的更新，即便是更新一些与缓存无关的字段，这个表所有的缓存都会被清空，因此很可能会费劲地把结果存起来，还没使用就被一个更新全部清空，对于更新压力的数据库来说，查询缓存的命中率会非常低，除非业务就一张静态表，很长时间才会更新一次。（例如系统配置表）

通过查询语句做哈希算法得到一个哈希值，因此这里要想命中缓存，查询 SQL 和缓存 SQL 必须完全一致，每次检查缓存是否命中时都会对缓存加锁，对于一个读写频繁的系统使用查询缓存很有可能降低查询

注意：MySQL8.0 版本直接将缓存的整个功能模块删掉了

第三步，分析器，如果没有命中缓存，就会执行 SQL 语句，首先让 MySQL 知道我们需要做什么，因此需要对 SQL 语句解析，MySQL 从输入的 “select” 关键字识别出来，这是一条查询语句，把字符串 “TAB” 识别成表名 TAB，检查查询中涉及的表和数据列是否存在或别名是否有歧义

解析器的工作：语法分析（生成句子），语义分析（确保这些句子讲得通），以及代码生成（为编译准备）

注意：分析器和解析器是一个东西，有些书叫分析器，有些书叫解析器，就是不同的叫法而已

第四步，优化器，经过分析器 MySQL 知道我们需要什么了，在开始执行前，还要经过优化器进行处理，优化器是在表里面有多个索引时，决定使用哪个索引，或者在一个语句有多表关联（join）时，决定各个表的连接顺序。

第五步，执行器，MySQL 通过分析器知道要做什么，通过优化器知道怎么做，开始执行前，要先判断一下是否有表 TABLE 查询权限，如果有打开表，根据表的引擎定义，去使用这个引擎提供的接口。

根据执行计划，调用存储引擎 API 来查询数据

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效，对于中到大型的表，索引就非常有效。

正确地创建和使用索引是实现高性能查询的基础。

如果查询中的列不是独立的，则 MySQL 就不会使用索引，’独立的列’是指索引列不能是表达式的一部分，也不是函数的参数。

有时候需要索引很长的字符列，这会让索引变得大且慢，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性，索引的选择性是指，不重复的索引值和数据表的记录总数（#T）的比值，范围从 1/#T 到 1 之间，索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行，唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能，对于 BLOB、TEXT 或者很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 不允许索引这些列的完整程度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。

①我们可以通过 left 函数

前缀索引是一种能使索引更小、更快的有效方法，但另一方面也有其缺点：MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

有时候后缀索引也有用途，MySQL 原声不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。

一个常见的错误就是，为每个列创建一个独立的索引或者按照错误的顺序创建多列索引。

当出现服务器对多个索引做相交操作时（通常有多个 AND 条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

当服务器需要对多个索引做联合操作时（通常有多个 OR 条件），通常需要耗费大量 CPU 和内存资源在算法的缓存、排序合并操作上，特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候，导致该执行计划还不如直接走全表扫描，这样做不但会消耗更多的 CPU 和内存资源，还可能会影响查询的并发性。

单列索引：节点中关键字【name】

联合索引：节点中关键字【name，phoneNum】

①经常用的列优先【最左匹配原则】

②选择性（离散性）高的优先【离散度高原则】

③宽度小的列优先【最少空间原则】

这种做法是错误的，根据最左匹配原则，两条查询都可以走 index_name_phoneNum 索引，index_name 索引就是冗余索引。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的，这时索引的作用只是用于优化 WHERE 条件的查找，在这种情况下，这种设计的索引确实能够最快地过滤出需要的行，对于在 WHERE 字句中只使用了索引部分前缀列的查询来说选择性也更高，然而，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关，可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，具体的细节依赖于其实现方式，但 InnoDB 的聚簇索引实际上在同一个结构中保存了 B+Tree 索引和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中，术语 “聚簇” 表示数据行和相邻的键值紧凑地存储在一起（这并非总成立），因为无法同时把数据行存在两个不同的地方，所以一个表只能有一个聚簇索引（不过，覆盖索引可以模拟多个聚簇索引的情况）。

因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚簇索引，这里只关注 InnoDB。

找出离散性好的列，离散性越高，可选择性就越好。

例如：sex 字段，只有男和女，离散性很差，因此选择性很差

  • datetime 类型适合用来记录数据的原始的创建时间，修改记录中其他字段的值，datetime 字段的值不会改变，除非手动修改它。

  • timestamp 类型适合用来记录数据的最后修改时间，只要修改了记录中其他字段的值，timestamp 字段的值都会被自动更新。

  • 查看是否涉及多表和子查询，优化 Sql 结构，如去除冗余字段，是否可拆表等

  • 优化索引结构，看是否可以适当添加索引

  • 数量大的表，可以考虑进行分离 / 分表（如交易流水表）

  • 数据库主从分离，读写分离

  • 查看 mysql 执行日志，分析是否有其他方面的问题

  • Blob 用于存储二进制数据，而 Text 用于存储大字符串。

  • Blob 值被视为二进制字符串（字节字符串）, 它们没有字符集，并且排序和比较基于列值中的字节的数值。

  • text 值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较。

  • 货币在数据库中 MySQL 常用 Decimal 和 Numric 类型表示，这两种类型被 MySQL 实现为同样的类型。他们被用于保存与金钱有关的数据。

  • DECIMAL 和 NUMERIC 值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。

如何使用普通锁保证一致性？

①操作数据前，加锁，实施互斥，不允许其他的并发任务操作；

②操作完成后，释放锁，让其他任务执行；如此这般，来保证一致性。

简单的锁住太过粗暴，连 “读任务” 也无法并行，任务执行过程本质上是串行的。

简单的锁住太过粗暴，连 “读任务” 也无法并行，任务执行过程本质上是串行的。于是出现了共享锁与排他锁：

  • 共享锁之间不互斥，读读可以并行

  • 排他锁与任何锁互斥，写读，写写不可以并行

可以看到，一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。

有没有可能，进一步提高并发呢？

即使写任务没有完成，其他读任务也可能并发，MySQL 通过多版本控制解决此问题。（快照读）

意向锁是指，未来的某个时刻，事务可能要加共享 / 排它锁了，先提前声明一个意向。

事务要获得某些行的 S 锁，必须先获得表的 IS 锁

事务要获得某些行的 X 锁，必须先获得表的 IX 锁

④由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：

⑤既然意向锁之间都相互兼容，那其意义在哪里呢？它会与共享锁 / 排它锁互斥，其兼容互斥表如下：

补充：排它锁是很强的锁，不与其他类型的锁兼容。这也很好理解，修改和删除某一行的时候，必须获得强锁，禁止这一行上的其他并发，以保障数据的一致性。

因为共享锁与排它锁互斥，所以事务 B 在视图对 table 表加共享锁的时候，必须保证：

①当前没有其他事务持有 table 表的排它锁。

②当前没有其他事务持有 table 表中任意一行的排它锁 。

为了检测是否满足第二个条件，事务 B 必须在确保 table 表不存在任何排它锁的前提下，去检测表中的每一行是否存在排它锁。很明显这是一个效率很差的做法，但是有了意向锁之后，事务 A 持有了 table 表的意向排它锁，就可得知事务 A 必然持有该表中某些数据行的排它锁，而无需去检测表中每一行是否存在排它锁

意向锁之间为什么互相兼容？

①事务 A 获取了 users 表上的意向排他锁。

②事务 A 获取了 id 为 6 的数据行上的排他锁。

事务 B 检测到事务 A 持有 users 表的意向排他锁。 事务 B 对 users 表的加锁请求被阻塞（排斥）。

①事务 C 申请 users 表的意向排他锁。

②事务 C 检测到事务 A 持有 users 表的意向排他锁。

③因为意向锁之间并不互斥，所以事务 C 获取到了 users 表的意向排他锁。

④因为 id 为 5 的数据行上不存在任何排他锁，最终事务 C 成功获取到了该数据行上的排他锁。

如果意向锁之间互斥，行级锁的意义将会失去

记录锁，它封锁索引记录，例如：

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

隔离级别：可重复读隔离级别

为什么要阻止 id=10 的记录插入？ 如果能够插入成功，头一个事务执行相同的 SQL 语句，会发现结果集多出了一条记录，即幻影数据。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致 “不可重复读”。

如果把事务的隔离级别降级为读提交 (Read Committed, RC)，间隙锁则会自动失效。

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

更具体的，临键锁会封锁索引记录本身，以及索引记录之前的区间。

如果一个会话占有了索引记录 R 的共享 / 排他锁，其他会话不能立刻在 R 之前的区间插入新的索引记录。

隔离级别：可重复读隔离级别

PK 上潜在的临键锁为：

临键锁的主要目的，也是 为了避免幻读 (Phantom Read) 。如果把事务的隔离级别降级为 RC，临键锁则也会失效。

对已有数据行的修改与删除，必须加强互斥锁 X 锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

插入意向锁，是间隙锁 (Gap Locks) 的一种（所以，也是实施在索引上的），它是专门针对 insert 操作的。

多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

隔离级别：可重复读隔离级别

事务 A 先执行，在 10 与 20 两条记录中插入了一行，还未提交：

事务 B 后执行，也在 10 与 20 两条记录中插入了一行：

会使用什么锁？事务 B 会不会被阻塞呢？ 回答：虽然事务隔离级别是 RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里： 使用的是插入意向锁，并不会阻塞事务 B

隔离级别：可重复读隔离级别

事务 B 会不会被阻塞？

InnoDB 在 RR 隔离级别下，能解决幻读问题，上面这个案例中：

①事务 A 先执行 insert，会得到一条 (4, xxx) 的记录，由于是自增列，故不用显示指定 id 为 4，InnoDB 会自动增长，注意此时事务并未提交；

②事务 B 后执行 insert，假设不会被阻塞，那会得到一条 (5, ooo) 的记录；

此时，并未有什么不妥，但如果，

补充：不可能查询到 5 的记录，再 RR 的隔离级别下，不可能读取到还未提交事务生成的数据。

这对于事务 A 来说，就很奇怪了，对于 AUTO_INCREMENT 的列，连续插入了两条记录，一条是 4，接下来一条变成了 6，就像莫名其妙的幻影。

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入 AUTO_INCREMENT 类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

  • B + 树可以进行范围查询，Hash 索引不能。

  • B + 树支持联合索引的最左侧原则，Hash 索引不支持。

  • Hash 索引在等值查询上比 B + 树效率更高。

  • B + 树使用 like 进行模糊查询的时候，like 后面（比如 % 开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

  • Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集

  • left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。

  • right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

  • 内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。

  • 外连接（outer join）：取得两张表中满足存在连接匹配关系的记录，以及某张表（或两张表）中不满足匹配关系的记录。

  • 交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，也被称为：笛卡尔积。

  • 第一范式：数据表中的每一列（每个字段）都不可以再拆分。

  • 第二范式：在第一范式的基础上，分主键列完全依赖于主键，而不能是依赖于主键的一部分。

  • 第三范式：在满足第二范式的基础上，表中的非主键只依赖于主键，而不依赖于其他非主键。

  • user 权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。

  • db 权限表：记录各个帐号在各个数据库上的操作权限。

  • table_priv 权限表：记录数据表级的操作权限。

  • columns_priv 权限表：记录数据列级的操作权限。

  • host 权限表：配合 db 权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受 GRANT 和 REVOKE 语句的影响。

主从复制 binlog 格式有哪几种？有什么区别？

日志文件所需的存储空间大大减少。这也意味着从备份中获取和还原可以更快地完成。 日志文件包含所有进行了任何更改的语句，因此它们可用于审核数据库。

②ROW，基于行的日志记录，把每一行的改变写入 binlog，假设一条 sql 语句影响 100 万行，从节点需要执行 100 万次，效率低。 优点：可以复制所有更改，这是最安全的复制形式 缺点：如果该 SQL 语句更改了许多行，则基于行的复制可能会向二进制日志中写入更多的数据。即使对于回滚的语句也是如此。这也意味着制作和还原备份可能需要更多时间。此外，二进制日志被锁定更长的时间以写入数据，这可能会导致并发问题。

③MIXED，混合模式，如果 sql 里有函数，自动切换到 ROW 模式，如果 sql 里没有会造成主从复制不一致的函数，那么就使用 STATEMENT 模式。（存在问题：解决不了系统变量问题，例如 @@host name，主从的主机名不一致）

①异步复制 网络或机器故障时，会造成数据不一致

数据不一致缓解方案：半同步，插入主库时，不会及时返回给我们的 web 端，他会进行等待，等待从库的 I/OThread 从主节点 Binary log 读取二进制文件并拷贝到从节点的 relaybinlog 之后，在进行返回。（不是等待所有，一个从节点复制过去就行了）

数据强一致性了但是性能低：可以设置超时时间（多个 Slave，或者 Slave 非常卡，会导致响应非常慢？不会，有保护机制，超过时间就直接返回，一般情况下设置 1 秒）

注意：不是等待所有从节点同步从主节点 Binary log 读取二进制文件并拷贝到从节点的 relaybinlog 之后才返回，而是只要有一个节点拷贝成功就返回

根据业务场景选择同步和半同步

注意：半同步只会缓解数据不一致问题，并不能完全解决

②半同步复制（MySQL 8.0 还支持通过插件实现的半同步复制接口）

默认情况下，MySQL 复制是异步的。Master 将事件写入其二进制日志，Slave 将在事件就绪时请求它们。Master 不知道 Slave 是否或何时检索和处理了事务，并且不能保证任何事件都会到达 Slave。使用异步复制，如果 Master 崩溃，则它提交的事务可能不会传输到任何 Slave。在这种情况下，从 Master 到 Slave 的故障转移可能会导致故障转移到缺少相对于 Master 的事务的服务器。

在完全同步复制的情况下，当 Master 提交事务时，所有 Slave 也都已提交事务，然后 Master 才返回执行该事务的会话。完全同步复制意味着可以随时从 Master 故障转移到任何 Slave。完全同步复制的缺点是完成事务可能会有很多延迟。

半同步复制介于异步复制和完全同步复制之间。Master 等待直到至少一个 Slave 接收并记录了事件（所需数量的 Slave 是可配置的），然后提交事务。Master 不等待所有 Slave 都确认收到，它仅需要 Slave 的确认，而不是事件已在 Slave 端完全执行并提交。因此，半同步复制可确保如果 Master 崩溃，则它已提交的所有事务都已传输到至少一个

与异步复制相比，半同步复制提供了改进的数据完整性，因为众所周知，当提交成功返回时，数据至少存在两个位置。在半同步 Master 收到所需数量的 Slave 的确认之前，该事务处于暂挂状态且未提交。

与完全同步复制相比，半同步复制更快，因为半同步复制可以配置为平衡对数据完整性（确认已收到事务的 Slave 数）与提交速度的需求，提交速度较慢，因为需要等待 Slave。

与异步复制相比，半同步复制对性能的影响是增加数据完整性的权衡。减慢量至少是将提交发送到 Slave 并等待 Slave 确认接收的 TCP / IP 往返时间。这意味着半同步复制最适合通过快速网络通信的关闭服务器，而最不适合通过慢速网络通信的远程服务器。半同步复制还通过限制二进制日志事件从 Master 发送到 Slave 的速度，对繁忙的会话设置了速率限制。当一个用户太忙时，这会减慢速度，这在某些部署情况下很有用。

Master 及其 Slave 之间的半同步复制操作如下： Slave 表示连接到 Master 时是否具有半同步功能。 如果在 Master 端启用了半同步复制，并且至少有一个半同步 Slave，则在 Master 块上执行事务提交的线程将等待直到至少一个半同步 Slave 确认已接收到该事务的所有事件，或者直到发生超时。 仅在事件已被写入其中继日志并刷新到磁盘之后，Slave 才确认接收到事务事件。 如果在没有任何 Slave 确认事务的情况下发生超时，则 Master 将恢复为异步复制。赶上至少一个半同步 Slave 时，Master 将返回到半同步复制。 必须在 Master 端和 Slave 端都启用半同步复制。如果在 Master 上禁用了半同步复制，或者在 Master 上启用了半同步复制但没有任何 Slave，则 Master 使用异步复制。

③延迟复制 MySQL 8.0 还支持延迟复制，以使副本故意在源之后至少指定的时间量

  • 日志缓冲 (Log Buffer)，可以参考沈健老师文章 [事务已提交，数据却丢了，赶紧检查下这个配置！！！ | 数据库系列

  • 唯一索引可以保证数据库表中每一行的数据的唯一性

  • 索引可以加快数据查询速度，减少查询时间

  • 创建索引和维护索引要耗费时间

  • 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间

  • 以表中的数据进行增、删、改的时候，索引也要动态的维护。

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

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

  • 普通索引：基本的索引类型，没有唯一性的限制，允许为 NULL 值。

  • 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。

  • 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行

  • 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

  • 我们想要删除百万数据的时候可以先删除索引

  • 然后批量删除其中无用数据

  • 删除完成后重新创建索引。

  • 覆盖索引： 查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

  • 回表：二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

  • 在 B + 树的索引中，叶子节点可能存储了当前的 key 值，也可能存储了当前的 key 值以及整行的数据，这就是聚簇索引和非聚簇索引。 在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

  • 当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询（哈哈，覆盖索引就是这么回事）。

举个简单的例子，假设我们在学生表的上建立了索引，那么当进行 select age from student where age < 20 的查询时，在索引的叶子节点上，已经包含了 age 信息，不会再次进行回表查询。

组合索引，用户可以在多个列上建立索引，这种索引叫做组合索引。 因为 InnoDB 引擎中的索引策略的最左原则，所以需要注意组合索引中的顺序。

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。看图形象一点，如下：

死锁有四个必要条件：互斥条件，请求和保持条件，环路等待条件，不剥夺条件。 解决死锁思路，一般就是切断环路，尽量避免并发形成环路。

  • 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

  • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

  • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

  • 如果业务处理不好可以用分布式事务锁或者使用乐观锁

  • 死锁与索引密不可分，解决索引问题，需要合理优化你的索引，

监控的工具有很多，例如 zabbix，lepus，我这里用的是 lepus

作者：Java 程序鱼
著作权归作者所有。非商业转载请注明出处。

}

【版权声明】博客内容由厦门大学数据库实验室拥有版权，未经允许，请勿转载！版权所有，侵权必究！

MySQL数据库是最常用的轻量级数据库，本节介绍Python编程访问数据库MySQL。
此前，请确保已经安装了MySQL。可参考博文安装MySQL。
Python访问MySQL有多种方式，这里主要介绍两种最常用的访问方式：

执行完成后，屏幕上会输出id = '201702'的学生信息，如下：

查看另一个终端的MySQL命令行界面，可看出id = '201702'的学生信息已被成功删除。如下：

输出的结果显示利用支持Python的MySQL驱动访问MySQL实验成功！

本文通过两种方式访问MySQL数据库，一是通过PyMySQL模块，二是通过支持Python的MySQL驱动。两种方式都是很常用的，大家可二者择一。

}

SQL就是访问和处理关系数据库的计算机标准语言。也就是说，无论用什么编程语言（Java、Python、C++……）编写程序，只要涉及到操作关系数据库，比如，一个电商网站需要把用户和商品信息存入数据库，或者一个手机游戏需要把用户的道具、通关信息存入数据库，都必须通过SQL来完成。

你可能还听说过NoSQL数据库，也就是非SQL的数据库，包括MongoDB、Cassandra、Dynamo等等，它们都不是关系数据库。有很多人鼓吹现代Web程序已经无需关系数据库了，只需要使用NoSQL就可以。但事实上，SQL数据库从始至终从未被取代过。

数据库作为一种专门管理数据的软件就出现了。应用程序不需要自己管理数据，而是通过数据库软件提供的接口来读写数据。至于数据本身如何存储到文件，那是数据库软件的事情，应用程序自己并不关心： 

┌──────────────┐
└──────────────┘
┌──────────────┐
└──────────────┘

这样一来，编写应用程序的时候，数据读写的功能就被大大地简化了。

数据库按照数据结构来组织、存储和管理数据，实际上，数据库一共有三种模型：

层次模型就是以“上下级”的层次关系来组织数据的一种方式，层次模型的数据结构看起来就像一颗树：

┌───────┴───────┐ ┌─────┐ ┌─────┐ └─────┘ └─────┘ ┌───┴───┐ ┌───┴───┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │ │ │ │ │ │ │ │ └─────┘ └─────┘ └─────┘ └─────┘

网状模型把每个数据节点和其他很多节点都连接起来，它的数据结构看起来就像很多城市之间的路网： 

 ┌─────┐ ┌─────┐
 ┌─│ │──────│ │──┐
 │ └─────┘ └─────┘ │
 │ └──────┬─────┘ │
┌─────┐ ┌─────┐ ┌─────┐
│ │─────│ │─────│ │
└─────┘ └─────┘ └─────┘
 │ ┌─────┴─────┐ │
 │ ┌─────┐ ┌─────┐ │
 └──│ │─────│ │──┘
 └─────┘ └─────┘

关系模型把数据看作是一个二维表格，任何数据都可以通过行号+列号来唯一确定，它的数据模型看起来就是一个Excel表： 

┌─────┬─────┬─────┬─────┬─────┐
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
├─────┼─────┼─────┼─────┼─────┤
│ │ │ │ │ │
└─────┴─────┴─────┴─────┴─────┘

随着时间的推移和市场竞争，最终，基于关系模型的关系数据库获得了绝对市场份额。

为什么关系数据库获得了最广泛的应用？

因为相比层次模型和网状模型，关系模型理解和使用起来最简单。

基于数学理论的关系模型虽然讲起来挺复杂，但是，基于日常生活的关系模型却十分容易理解。我们以学校班级为例，一个班级的学生就可以用一个表格存起来，
其中，班级ID对应着另一个班级表

通过给定一个班级名称，可以查到一条班级记录，根据班级ID，又可以查到多条学生记录，这样，二维表之间就通过ID映射建立了“一对多”关系。

对于一个关系表，除了定义每一列的名称外，还需要定义每一列的数据类型。关系数据库支持的标准数据类型包括数值、字符串、时间等

选择数据类型的时候，要根据业务规则选择合适的类型。通常来说，BIGINT能满足整数存储的需求，VARCHAR(N)能满足字符串存储的需求，这两种类型是使用最广泛的。

什么是SQL？SQL是结构化查询语言的缩写，用来访问和操作数据库系统。SQL语句既可以查询数据库中的数据，也可以添加、更新和删除数据库中的数据，还可以对数据库进行管理和维护操作。不同的数据库，都支持SQL，这样，我们通过学习SQL这一种语言，就可以操作各种不同的数据库。

虽然SQL已经被ANSI组织定义为标准，不幸地是，各个不同的数据库对标准的SQL支持不太一致。并且，大部分数据库都在标准的SQL上做了扩展。也就是说，如果只使用标准SQL，理论上所有数据库都可以支持，但如果使用某个特定数据库的扩展SQL，换一个数据库就不能执行了。例如，Oracle把自己扩展的SQL称为PL/SQL，Microsoft把自己扩展的SQL称为T-SQL。

现实情况是，如果我们只使用标准SQL的核心功能，那么所有数据库通常都可以执行。不常用的SQL功能，不同的数据库支持的程度都不一样。而各个数据库支持的各自扩展的功能，通常我们把它们称之为“方言”。

总的来说，SQL语言定义了这么几种操作数据库的能力：

DDL允许用户定义数据，也就是创建表、删除表、修改表结构这些操作。通常，DDL由数据库管理员执行。

DML为用户提供添加、删除、更新数据的能力，这些是应用程序对数据库的日常操作。

DQL允许用户查询数据，这也是通常最频繁的数据库日常操作。

SQL语言关键字不区分大小写！！！但是，针对不同的数据库，对于表名和列名，有的数据库区分大小写，有的数据库不区分大小写。同一个数据库，有的在Linux上区分大小写，有的在Windows上不区分大小写。

所以，本教程约定：SQL关键字总是大写，以示突出，表名和列名均使用小写。

MySQL是目前应用最广泛的开源关系数据库。MySQL最早是由瑞典的MySQL AB公司开发，该公司在2008年被SUN公司收购，紧接着，SUN公司在2009年被Oracle公司收购，所以MySQL最终就变成了Oracle旗下的产品。

和其他关系数据库有所不同的是，MySQL本身实际上只是一个SQL接口，它的内部还包含了多种数据引擎，常用的包括：

  • MyISAM：MySQL早期集成的默认数据库引擎，不支持事务。

MySQL接口和数据库引擎的关系就好比某某浏览器和浏览器引擎（IE引擎或Webkit引擎）的关系。对用户而言，切换浏览器引擎不影响浏览器界面，切换MySQL引擎不影响自己写的应用程序使用MySQL的接口。

使用MySQL时，不同的表还可以使用不同的数据库引擎。如果你不知道应该采用哪种引擎，记住总是选择_InnoDB_就好了。

选择对应的操作系统版本，下载安装即可。在安装过程中，MySQL会自动创建一个root用户，并提示输入root口令。

MySQL安装后会自动在后台运行。为了验证MySQL安装是否正确，我们需要通过mysql这个命令行程序来连接MySQL服务器。

在命令提示符下输入mysql -u root -p，然后输入口令，如果一切正确，就会连接到MySQL服务器，同时提示符变为mysql>。

输入exit退出MySQL命令行。注意，MySQL服务器仍在后台运行。

我们已经知道，关系数据库是建立在关系模型上的。而关系模型本质上就是若干个存储数据的二维表，可以把它们看作很多Excel表。

表的每一行称为记录（Record），记录是一个逻辑意义上的数据。

表的每一列称为字段（Column），同一个表的每一行记录都拥有相同的若干字段。

字段定义了数据类型（整型、浮点型、字符串、日期等），以及是否允许为NULL。注意NULL表示字段数据不存在。一个整型字段如果为NULL不表示它的值为0，同样的，一个字符串型字段为NULL也不表示它的值为空串''。

通常情况下，字段应该避免允许为NULL。不允许为NULL可以简化查询条件，加快查询速度，也利于应用程序读取数据后无需判断是否为NULL。

和Excel表有所不同的是，关系数据库的表和表之间需要建立“一对多”，“多对一”和“一对一”的关系，这样才能够按照应用程序的逻辑来组织和存储数据。
一个班级表,每一行对应着一个班级，而一个班级对应着多个学生，所以班级表和学生表的关系就是“一对多”反过来，如果我们先在学生表中定位了一行记录，例如ID=1的小明，要确定他的班级，只需要根据他的“班级ID”对应的值201找到班级表中ID=201的记录，即二年级一班。所以，学生表和班级表是“多对一”的关系。班级表和教师表就是“一对一”关系。

在关系数据库中，关系是通过_主键_和_外键_来维护的。我们在后面会分别深入讲解。

每一条记录都包含若干定义好的字段。同一个表的所有记录都有相同的字段定义。

对于关系表，有个很重要的约束，就是任意两条记录不能重复。不能重复不是指两条记录不完全相同，而是指能够通过某个字段唯一区分出不同的记录，这个字段被称为_主键_。

例如，假设我们把name字段作为主键，那么通过名字小明或小红就能唯一确定一条记录。但是，这么设定，就没法存储同名的同学了，因为插入相同主键的两条记录是不被允许的。

对主键的要求，最关键的一点是：记录一旦插入到表中，主键最好不要再修改，因为主键是用来唯一定位记录的，修改了主键，会造成一系列的影响。

由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。

所以，选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均_不可_用作主键。

作为主键最好是完全业务无关的字段，我们一般把这个字段命名为id。常见的可作为id字段的类型有：

  1. 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
  2. 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc--76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求。我们在students表中定义的主键也是BIGINT NOT NULL AUTO_INCREMENT类型

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。

对于联合主键，允许一列有重复，只要不是所有主键列都重复即可
没有必要的情况下，我们尽量不使用联合主键，因为它给关系表带来了复杂度的上升。

我们如何确定students表的一条记录，例如，id=1的小明，属于哪个班级呢？

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个classes的记录可以对应多个students表的记录。

为了表达这种一对多的关系，我们需要在students表中加入一列class_id，让它的值与classes表的某条记录相对应

这样，我们就可以根据class_id这个列直接定位出一个students表的记录应该对应到classes的哪条记录。

在students表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

外键并不是通过列名实现的，而是通过定义外键约束实现的：

(id)指定了这个外键将关联到classes表的id列（即classes表的主键）。

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果classes表不存在id=99的记录，students表就无法插入class_id=99的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过ALTER TABLE实现的：

注意：删除外键约束并没有删除外键这一列。删除列是通过DROP COLUMN ...实现的。

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系

  • id=1的张老师对应id=1,2的一班和二班；
  • id=2的王老师对应id=1,2的一班和二班；
  • id=3的李老师对应id=1的一班；
  • id=4的赵老师对应id=2的二班。
  • id=1的一班对应id=1,2,3的张老师、王老师和李老师；
  • id=2的二班对应id=1,2,4的张老师、王老师和赵老师；

因此，通过中间表，我们就定义了一个“多对多”关系。

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

例如，students表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表contacts，我们就可以得到一个“一对一”关系

既然是一对一关系，那为啥不给students表增加一个mobile列，这样就能合二为一了？

如果业务允许，完全可以把两个表合为一个表。但是，有些时候，如果某个学生没有手机号，那么，contacts表就不存在对应的记录。实际上，一对一关系准确地说，是contacts表一对一对应students表。

还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表user_info和用户详细信息表user_profiles，大部分时候，只需要查询user_info表，并不需要查询user_profiles表，这样就提高了查询速度。

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。

在关系数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

如果要经常根据score列进行查询，就可以对score列创建索引：

使用ADD INDEX idx_score (score)就创建了一个名称为idx_score，使用列score的索引。索引名称是任意的，索引如果有多列，可以在括号里依次写上，例如：

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，例如gender列，大约一半的记录值是M，另一半是F，因此，对该列创建索引就没有意义。

可以对一张表创建多个索引。索引的优点是提高了查询效率，缺点是在插入、更新和删除记录时，需要同时修改索引，因此，索引越多，插入、更新和删除记录的速度就越慢。

对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。

但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设students表的name不能重复：

通过UNIQUE关键字我们就添加了一个唯一索引。

也可以只对某一列添加一个唯一约束而不创建唯一索引：

这种情况下，name列没有索引，但仍然具有唯一性保证。

无论是否创建索引，对于用户和应用程序来说，使用关系数据库不会有任何区别。这里的意思是说，当我们在数据库中查询时，如果有相应的索引可用，数据库系统就会自动使用索引来提高查询效率，如果没有索引，查询也能正常执行，只是速度会变慢。因此，索引可以在使用数据库的过程中逐步优化。

通过对数据库表创建索引，可以提高查询速度。

通过创建唯一索引，可以保证某一列的值具有唯一性。

数据库索引对于用户和应用程序来说都是透明的。

在关系数据库中，最常用的操作就是查询。

要查询数据库表的数据，我们使用如下的SQL语句：

SELECT是关键字，表示将要执行一个查询，*表示“所有列”，FROM表示将要从哪个表查询，本例中是students表。
该SQL将查询出students表的所有数据。注意：查询结果也是一个二维表，它包含列名和每一行的数据。

使用SELECT查询的基本语句SELECT * FROM <表名>可以查询一个表的所有行和所有列的数据。

SELECT查询的结果是一个二维表。

使用SELECT * FROM <表名>可以查询到一张表的所有记录。但是，很多时候，我们并不希望获得所有记录，而是根据条件选择性地获取指定条件的记录，例如，查询分数在80分以上的学生记录。在一张表有数百万记录的情况下，获取所有记录不仅费时，还费内存和网络带宽。

SELECT语句可以通过WHERE条件来设定查询条件，查询结果是满足查询条件的记录。例如，要指定条件“分数在80分或以上的学生”，写成WHERE条件就是:

其中，WHERE关键字后面的score >= 80就是条件。score是列名，该列存储了学生的成绩，因此，score >= 80就筛选出了指定条件的记录

因此，条件查询的语法就是：

条件表达式可以用<条件1> AND <条件2>表达满足条件1并且满足条件2。例如，符合条件“分数在80分或以上”，并且还符合条件“男生”，把这两个条件写出来：

  • 条件2：根据gender列的数据判断：gender = 'M'，注意gender列存储的是字符串，需要用单引号括起来。

第二种条件是<条件1> OR <条件2>，表示满足条件1或者满足条件2。例如，把上述AND查询的两个条件改为OR，查询结果就是“分数在80分或以上”或者“男生”，满足任意之一的条件即选出该记录：

第三种条件是NOT <条件>，表示“不符合该条件”的记录。例如，写一个“不是2班的学生”这个条件，可以先写出“是2班的学生”：class_id = 2，再加上NOT：NOT class_id = 2：

要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生：

如果不加括号，条件运算按照NOT、AND、OR的优先级进行，即NOT优先级最高，其次是AND，最后是OR。加上括号可以改变优先级。

使用SELECT * FROM <表名> WHERE <条件>可以选出表中的若干条记录。我们注意到返回的二维表结构和原表是相同的，即结果集的所有列与原表的所有列都一一对应。

如果我们只希望返回某些列的数据，而不是所有列的数据，我们可以用SELECT 列1, 列2, 列3 FROM ...，让结果集仅包含指定列。这种操作称为投影查询。

这样返回的结果集就只包含了我们指定的列，并且，结果集的列的顺序和原表可以不一样。

使用SELECT 列1, 列2, 列3 FROM ...时，还可以给每一列起个别名，这样，结果集的列名就可以与原表的列名不同。它的语法是SELECT 列1 别名1, 列2 别名2, 列3 别名3 FROM ...。

例如，以下SELECT语句将列名score重命名为points，而id和name列名保持不变：

我们使用SELECT查询时，细心的读者可能注意到，查询结果集通常是按照id排序的，也就是根据主键排序。这也是大部分数据库的做法。如果我们要根据其他条件排序怎么办？可以加上ORDER BY子句。例如按照成绩从低到高进行排序：

如果要反过来，按照成绩从高到底排序，我们可以加上DESC表示“倒序”：

如果score列有相同的数据，要进一步排序，可以继续添加列名。例如，使用ORDER BY score DESC, gender表示先按score列倒序，如果有相同分数的，再按gender列排序：

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

使用SELECT查询时，如果结果集数据量很大，比如几万行数据，放在一个页面显示的话数据量太大，不如分页显示，每次显示100条。

要实现分页功能，实际上就是从结果集中显示第1~100条记录作为第1页，显示第101~200条记录作为第2页，以此类推。

因此，分页实际上就是从结果集中“截取”出第M~N条记录。这个查询可以通过LIMIT <M> OFFSET <N>子句实现。我们先把所有学生按照成绩从高到低进行排序,把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3。

可见，分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

这样就能正确查询出第N页的记录集。

OFFSET超过了查询的最大数量并不会报错，而是得到一个空的结果集。

如果我们要统计一张表的数据量，例如，想查询students表一共有多少条记录，难道必须用SELECT * FROM students查出来然后再数一数有多少行吗？

这个方法当然可以，但是比较弱智。对于统计总数、平均数这类计算，SQL提供了专门的聚合函数，使用聚合函数进行查询，就是聚合查询，它可以快速获得结果。

仍然以查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：

COUNT(*)表示查询所有列的行数，要注意聚合的计算结果虽然是一个数字，但查询的结果仍然是一个二维表，只是这个二维表只有一行一列，并且列名是COUNT(*)。

除了COUNT()函数外，SQL还提供了如下聚合函数：.....

注意，MAX()和MIN()函数并不限于数值类型。如果是字符类型，MAX()和MIN()会返回排序最后和排序最前的字符。

执行这个查询，COUNT()的结果不再是一个，而是3个，这是因为，GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。

这种一次查询两个表的数据，查询的结果也是一个二维表，它是students表和classes表的“乘积”，即students表的每一行与classes表的每一行都两两拼在一起返回。结果集的列数是students表和classes表的列数之和，行数是students表和classes表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

你可能还注意到了，上述查询的结果集有两列id和两列name，两列id是因为其中一列是students表的id，而另一列是classes表的id，但是在结果集中，不好区分。两列name同理

要解决这个问题，我们仍然可以利用投影查询的“设置列的别名”来给两个表各自的id和name列起别名：

注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。但是，用表名.列名这种方式列举两个表的所有列实在是很麻烦，所以SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点：

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

但是，假设我们希望结果集同时包含所在班级的名称，上面的结果集只有class_id列，缺少对应班级的name列。

现在问题来了，存放班级名称的name列存储在classes表中，只有根据students表的class_id，找到classes表对应的行，再取出name列，就可以获得班级名称。
这时，连接查询就派上了用场。我们先使用最常用的一种内连接——INNER JOIN来实现：

  1. 先确定主表，仍然使用FROM <表1>的语法；

使用别名不是必须的，但可以更好地简化查询语句。

那什么是内连接（INNER JOIN）呢？先别着急，有内连接（INNER JOIN）就有外连接（OUTER JOIN）。我们把内连接查询改成外连接查询，看看效果：

RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段。

JOIN查询需要先确定主表，然后把另一个表的数据“附加”到结果集上；

关系数据库的基本操作就是增删改查，即CRUD：Create、Retrieve、Update、Delete。其中，对于查询，我们已经详细讲述了SELECT语句的详细用法。

而对于增、删、改，对应的SQL语句分别是：

  • INSERT：插入新记录；
  • UPDATE：更新已有记录；
  • DELETE：删除已有记录。

我们将分别讨论这三种修改数据的语句的使用方法。

当我们需要向数据库表中插入一条新记录时，就必须使用INSERT语句。

INSERT语句的基本语法是：

例如，我们向students表插入一条新记录，先列举出需要插入的字段名称，然后在VALUES子句中依次写出对应字段的值：

-- 查询并观察结果:

注意到我们并没有列出id字段，也没有列出id字段对应的值，这是因为id字段是一个自增主键，它的值可以由数据库自己推算出来。此外，如果一个字段有默认值，那么在INSERT语句中也可以不出现。

还可以一次性添加多条记录，只需要在VALUES子句中指定多个记录值，每个记录是由(...)包含的一组值：

如果要更新数据库表中的记录，我们就必须使用UPDATE语句。

UPDATE语句的基本语法是：

score=66，然后在WHERE子句中写出需要更新的行的筛选条件id=1：

-- 查询并观察结果:

注意到UPDATE语句的WHERE条件和SELECT语句的WHERE条件其实是一样的，因此完全可以一次更新多条记录：

-- 查询并观察结果:

在UPDATE语句中，更新字段时可以使用表达式。例如，把所有80分以下的同学的成绩加10分：

-- 查询并观察结果:

如果要删除数据库表中的记录，我们可以使用DELETE语句。

DELETE语句的基本语法是：

例如，我们想删除students表中id=1的记录，就需要这么写：

-- 查询并观察结果:

注意到DELETE语句的WHERE条件也是用来筛选需要删除的行，因此和UPDATE类似，DELETE语句也可以一次删除多条记录：

-- 查询并观察结果:

如果WHERE条件没有匹配到任何记录，DELETE语句不会报错，也不会有任何记录被删除。例如：

-- 查询并观察结果:

最后，要特别小心的是，和UPDATE类似，不带WHERE条件的DELETE语句会删除整个表的数据：

这时，整个表的所有记录都会被删除。所以，在执行DELETE语句时也要非常小心，最好先用SELECT语句来测试WHERE条件是否筛选出了期望的记录集，然后再用DELETE删除。

要管理MySQL，可以使用可视化图形界面。

MySQL Workbench可以用可视化的方式查询、创建和修改数据库表，但是，归根到底，MySQL Workbench是一个图形客户端，它对MySQL的操作仍然是发送SQL语句并执行。因此，本质上，MySQL Workbench和MySQL Client命令行都是客户端，和MySQL交互，唯一的接口就是SQL。

因此，MySQL提供了大量的SQL语句用于管理。虽然可以使用MySQL Workbench图形界面来直接管理MySQL，但是，很多时候，通过SSH远程连接时，只能使用SQL命令，所以，了解并掌握常用的SQL管理操作是必须的。

在一个运行MySQL的服务器上，实际上可以创建多个数据库（Database）。要列出所有数据库，使用命令：

要创建一个新数据库，使用命令：

要删除一个数据库，使用命令：

注意：删除一个数据库将导致该数据库的所有表全部被删除。

对一个数据库进行操作时，要首先将其切换为当前数据库：

列出当前数据库的所有表，使用命令：

要查看一个表的结构，使用命令：

还可以使用以下命令查看创建表的SQL语句：

修改表就比较复杂。如果要给students表新增一列birth，使用：

注意EXIT仅仅断开了客户端和服务器的连接，MySQL服务器仍然继续运行。

在编写SQL时，灵活运用一些技巧，可以大大简化程序逻辑。

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：

若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。

若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：

若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。

如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：

新创建的表结构和SELECT使用的表结构完全一致。

如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。

例如，创建一个统计成绩的表statistics，记录各班的平均成绩：

然后，我们就可以用一条语句写入各班的平均成绩：

确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：

在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。例如：

指定索引的前提是索引idx_class_id必须存在。

在执行SQL语句的时候，某些业务要求，一系列操作必须全部执行，而不能仅执行一部分。例如，一个转账操作：

-- 第一步：将id=1的A账户余额减去100 -- 第二步：将id=2的B账户余额加上100

这两条SQL语句必须全部执行，或者，由于某些原因，如果第一条语句成功，第二条语句失败，就必须全部撤销。

这种把多条语句作为一个整体进行操作的功能，被称为数据库_事务_。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

可见，数据库事务具有ACID这4个特性：

  • A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
  • C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
  • I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
  • D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

对于单条SQL语句，数据库系统自动将其作为一个事务执行，这种事务被称为_隐式事务_。

要手动把多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务，这种事务被称为_显式事务_，例如，把上述的转账操作作为一个显式事务：

很显然多条SQL语句要想作为一个事务执行，就必须使用显式事务。

COMMIT是指提交事务，即试图把事务内的所有SQL所做的修改永久保存。如果COMMIT语句执行失败了，整个事务也会失败。

有些时候，我们希望主动让事务失败，这时，可以用ROLLBACK回滚事务，整个事务会失败：

数据库事务是由数据库系统保证的，我们只需要根据业务逻辑使用它就可以。

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

Read Uncommitted是隔离级别最低的一种事务级别。在这种隔离级别下，一个事务会读到另一个事务更新后但未提交的数据，如果另一个事务回滚，那么当前事务读到的数据就是脏数据，这就是脏读（Dirty Read）。

首先，我们准备好students表的数据，该表仅一行记录：

然后，分别开启两个MySQL客户端连接，按顺序依次执行事务A和事务B：...

当事务A执行完第3步时，它更新了id=1的记录，但并未提交，而事务B在第4步读取到的数据就是未提交的数据。

随后，事务A在第5步进行了回滚，事务B再次读取id=1的记录，发现和上一次读取到的数据不一致，这就是脏读。

可见，在Read Uncommitted隔离级别下，一个事务可能读取到另一个事务更新但未提交的数据，这个数据有可能是脏数据。

不可重复读是指，在一个事务内，多次读同一数据，在这个事务还没有结束时，如果另一个事务恰好修改了这个数据，那么，在第一个事务中，两次读取的数据就可能不一致。

幻读是指，在一个事务中，第一次查询某条记录，发现没有，但是，当试图更新这条不存在的记录时，竟然能成功，并且，再次读取同一条记录，它就神奇地出现了。

事务B在第3步第一次读取id=99的记录时，读到的记录为空，说明不存在id=99的记录。随后，事务A在第4步插入了一条id=99的记录并提交。事务B在第6步再次读取id=99的记录时，读到的记录仍然为空，但是，事务B在第7步试图更新这条不存在的记录时，竟然成功了，并且，事务B在第8步再次读取id=99的记录时，记录出现了。

可见，幻读就是没有读到的记录，以为不存在，但其实是可以更新成功的，并且，更新成功后，再次读取，就出现了。

Serializable是最严格的隔离级别。在Serializable隔离级别下，所有事务按照次序依次执行，因此，脏读、不可重复读、幻读都不会出现。

虽然Serializable隔离级别下的事务具有最高的安全性，但是，由于事务是串行执行，所以效率会大大下降，应用程序的性能会急剧降低。如果没有特别重要的情景，一般都不会使用Serializable隔离级别。

如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用InnoDB，默认的隔离级别是Repeatable Read。

}

我要回帖

更多关于 sql取第一条和最后一条 的文章

更多推荐

版权声明：文章内容来源于网络，版权归原作者所有，如有侵权请点击这里与我们联系，我们将及时删除。

点击添加站长微信