代码拉取完成，页面将自动刷新
title: 关系型数据库面试
date: 2020-01-15 23:21:02
categories:
- 数据库
- 关系型数据库
- 综合
tags:
- 数据库
- 关系型数据库
- 面试
permalink: /pages/9bb28f/
索引是对数据库表中一或多个列的值进行排序的结构，是帮助数据库高效查询数据的数据结构。
✔ 索引的优点：
❌ 索引的缺点：
INSERT/UPDATE/DELETE）时很可能需要更新索引，导致数据库的写操作性能降低。索引能够轻易将查询性能提升几个数量级。
✔ 什么情况适用索引：
SELECT 操作；WHERE 或连接（JOIN）条件中❌ 什么情况不适用索引：
INSERT/UPDATE/DELETE ）- 需要更新索引空间；WHERE 或连接（JOIN）条件中 - 索引就会经常不命中，没有意义，还增加空间开销。主流的关系型数据库一般都支持以下索引类型：
从逻辑类型上划分（即一般创建表时设置的索引类型）：
UNIQUE）：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。PRIMARY）：一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。INDEX）：最基本的索引，没有任何限制。从物理存储上划分：
Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个。Non-clustered)：非聚集索引指定表的逻辑顺序，也可以视为二级索引。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于 249 个。主流数据库的索引一般使用的数据结构为：B 树、B+ 树。
一棵 M 阶的 B-Tree 满足以下条件：
对于任意结点，其内部的关键字 Key 是升序排列的。每个节点中都包含了 data。
对于每个结点，主要包含一个关键字数组 Key[]，一个指针数组（指向儿子）Son[]。
在 B-Tree 内，查找的流程是：
B+Tree 是 B-Tree 的变种：
由于并不是所有节点都具有相同的域，因此 B+Tree 中叶节点和内节点一般大小不同。这点与 B-Tree 不同，虽然 B-Tree 中不同节点存放的 key 和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中 B-Tree 往往对每个节点申请同等大小的空间。
带有顺序访问指针的 B+Tree
一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 的基础上进行了优化，增加了顺序访问指针。
在 B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree。
这个优化的目的是为了提高区间访问的性能，例如上图中如果要查询 key 为从 18 到 49 的所有数据记录，当找到 18 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
Hash 索引只有精确匹配索引所有列的查询才有效。
对于每一行数据，对所有的索引列计算一个 hashcode。哈希索引将所有的 hashcode 存储在索引中，同时在 Hash 表中保存指向每个数据行的指针。
哈希结构索引的优点：
哈希结构索引的缺点：
WHERE 过滤条件的列应该考虑添加索引如果查询中的列不是独立的列，则数据库不会使用索引。
“独立的列” 是指索引列不能是表达式的一部分，也不能是函数的参数。
❌ 错误示例：
SELECT actor_id FROM actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(current_date) - TO_DAYS(date_col) <= 10;
有时候需要索引很长的字符列，这会让索引变得大且慢。
解决方法是：可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。
索引的选择性是指：不重复的索引值和数据表记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，查询效率也越高。
对于 BLOB/TEXT/VARCHAR 这种文本类型的列，必须使用前缀索引，因为数据库往往不允许索引这些列的完整长度。
要选择足够长的前缀以保证较高的选择性，同时又不能太长（节约空间）。
❌ 低效示例：
SELECT COUNT(*) AS cnt, city FROM sakila.city_demo
GROUP BY city ORDER BY cnt DESC LIMIT 10;
✔ 高效示例：
SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo
GROUP BY city ORDER BY cnt DESC LIMIT 10;
不要为每个列都创建独立索引。
将选择性高的列或基数大的列优先排在多列索引最前列。但有时，也需要考虑 WHERE 子句中的排序、分组和范围条件等因素，这些因素也会对查询性能造成较大影响。
举例来说，有一张 user 表，其中含 name, sex, age 三个列，如果将这三者组合为多列索引，应该用什么样的顺序呢？从选择性高的角度来看：name > age > sex。
聚簇索引不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于实现方式。如 InnoDB 的聚簇索引实际是在同一个结构中保存了 B 树的索引和数据行。
聚簇表示数据行和相邻的键值紧凑地存储在一起，因为数据紧凑，所以访问快。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
若没有定义主键，InnoDB 会隐式定义一个主键来作为聚簇索引。
索引包含所有需要查询的字段的值。
具有以下优点：
Mysql 有两种方式可以生成排序结果：通过排序操作；或者按索引顺序扫描。
索引最好既满足排序，又用于查找行。这样，就可以使用索引来对结果排序。
MySQL 会一直向右匹配直到遇到范围查询 (>,<,BETWEEN,LIKE) 就停止匹配。
例子：
不需要考虑=、in 等的顺序，Mysql 会自动优化这些条件的顺序，以匹配尽可能多的索引列。
例子：如有索引(a, b, c, d)，查询条件 c > 3 and b = 2 and a = 1 and d < 4 与 a = 1 and c > 3 and b = 2 and d < 4 等顺序都是可以的，MySQL 会自动优化为 a = 1 and b = 2 and c > 3 and d < 4，依次命中 a、b、c。
数据库约束（CONSTRAINT）有哪些：
NOT NULL - 用于控制字段的内容一定不能为空（NULL）。UNIQUE - 字段内容不能重复，一个表允许有多个 Unique 约束。PRIMARY KEY - 数据表中对储存数据对象予以唯一和完整标识的数据列或属性的组合，它在一个表中只允许有一个。主键的取值不能为空值（Null）。FOREIGN KEY - 在一个表中存在的另一个表的主键称此表的外键。用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。CHECK - 用于控制字段的值范围。
- 数据库的乐观锁和悲观锁是什么？
- 数据库的乐观锁和悲观锁如何实现？
确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性，乐观锁和悲观锁是并发控制主要采用的技术手段。
悲观锁 - 假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
乐观锁 - 假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
- 什么是行级锁和表级锁？
- 什么时候用行级锁？什么时候用表级锁？
从数据库的锁粒度来看，MySQL 中提供了两种封锁粒度：行级锁和表级锁。
二者需要权衡：
在 InnoDB 中，行锁是通过给索引上的索引项加锁来实现的。如果没有索引，InnoDB 将会通过隐藏的聚簇索引来对记录加锁。
- 什么是读写锁？
SELECT ... FOR UPDATE;
SELECT ... LOCK IN SHARE MODE;
写锁和读锁的关系，简言之：独享锁存在，其他事务就不能做任何操作。
InnoDB 下的行锁、间隙锁、next-key 锁统统属于独享锁。
- 什么是意向锁？
- 意向锁有什么用？
意向锁的作用是：当存在表级锁和行级锁的情况下，必须先申请意向锁（表级锁，但不是真的加锁），再获取行级锁。使用意向锁（Intention Locks）可以更容易地支持多粒度封锁。
意向锁是 InnoDB 自动加的，不需要用户干预。
什么是 MVCC？
MVCC 有什么用？解决了什么问题？
MVCC 的原理是什么？
多版本并发控制（Multi-Version Concurrency Control, MVCC）是 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。
MVCC 的思想是：
CopyOnWrite 类似。Next-Key 锁是 MySQL 的 InnoDB 存储引擎的一种锁实现。
MVCC 不能解决幻读问题，Next-Key 锁就是为了解决幻读问题。在可重复读（REPEATABLE READ）隔离级别下，使用 MVCC + Next-Key 锁 可以解决幻读问题。
另外，根据针对 SQL 语句检索条件的不同，加锁又有以下三种情形需要我们掌握。
Record Lock - 行锁对索引项加锁，若没有索引则使用表锁。Gap Lock - 对索引项之间的间隙加锁。锁定索引之间的间隙，但是不包含索引本身。例如当一个事务执行以下语句，其它事务就不能在 t.c 中插入 15。SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;
Next-key lock -它是 Record Lock 和 Gap Lock 的结合，不仅锁定一个记录上的索引，也锁定索引之间的间隙。它锁定一个前开后闭区间。索引分为主键索引和非主键索引两种，如果一条 SQL 语句操作了主键索引，MySQL 就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL 会先锁定该非主键索引，再锁定相关的主键索引。在 UPDATE、DELETE 操作时，MySQL 不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的 next-key lock。
当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。发生死锁后，InnoDB 一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。
事务简单来说：一个 Session 中所进行所有的操作，要么同时成功，要么同时失败。具体来说，事务指的是满足 ACID 特性的一组操作，可以通过
Commit提交一个事务，也可以使用Rollback进行回滚。
ACID — 数据库事务正确执行的四个基本要素：
一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。
在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题。
T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
并发一致性解决方案：
产生并发不一致性问题主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。
并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。
数据库隔离级别：
未提交读（READ UNCOMMITTED） - 事务中的修改，即使没有提交，对其它事务也是可见的。提交读（READ COMMITTED） - 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。重复读（REPEATABLE READ） - 保证在同一个事务中多次读取同样数据的结果是一样的。串行化（SERIALIXABLE） - 强制事务串行执行。数据库隔离级别解决的问题：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 未提交读 | ❌ | ❌ | ❌ |
| 提交读 | ✔️ | ❌ | ❌ |
| 可重复读 | ✔️ | ✔️ | ❌ |
| 可串行化 | ✔️ | ✔️ | ✔️ |
在单一数据节点中，事务仅限于对单一数据库资源的访问控制，称之为 本地事务。几乎所有的成熟的关系型数据库都提供了对本地事务的原生支持。
分布式事务 是指事务的参与者、支持事务的服务器、资源服务器以及事务管理器分别位于不同的分布式系统的不同节点之上。
两阶段提交（XA）对业务侵入很小。 它最大的优势就是对使用方透明，用户可以像使用本地事务一样使用基于 XA 协议的分布式事务。 XA 协议能够严格保障事务 ACID 特性。
严格保障事务 ACID 特性是一把双刃剑。 事务执行在过程中需要将所需资源全部锁定，它更加适用于执行时间确定的短事务。 对于长事务来说，整个事务进行期间对数据的独占，将导致对热点数据依赖的业务系统并发性能衰退明显。 因此，在高并发的性能至上场景中，基于 XA 协议的分布式事务并不是最佳选择。
如果将实现了ACID 的事务要素的事务称为刚性事务的话，那么基于BASE事务要素的事务则称为柔性事务。 BASE是基本可用、柔性状态和最终一致性这三个要素的缩写。
在ACID事务中对隔离性的要求很高，在事务执行过程中，必须将所有的资源锁定。 柔性事务的理念则是通过业务逻辑将互斥锁操作从资源层面上移至业务层面。通过放宽对强一致性要求，来换取系统吞吐量的提升。
基于ACID的强一致性事务和基于BASE的最终一致性事务都不是银弹，只有在最适合的场景中才能发挥它们的最大长处。 可通过下表详细对比它们之间的区别，以帮助开发者进行技术选型。
| 本地事务 | 两（三）阶段事务 | 柔性事务 | |
|---|---|---|---|
| 业务改造 | 无 | 无 | 实现相关接口 |
| 一致性 | 不支持 | 支持 | 最终一致 |
| 隔离性 | 不支持 | 支持 | 业务方保证 |
| 并发性能 | 无影响 | 严重衰退 | 略微衰退 |
| 适合场景 | 业务方处理不一致 | 短事务 & 低并发 | 长事务 & 高并发 |
什么是分库分表？什么是垂直拆分？什么是水平拆分？什么是 Sharding？
分库分表是为了解决什么问题？
分库分表有什么优点？
分库分表有什么策略？
分库分表的基本思想就是：把原本完整的数据切分成多个部分，放到不同的数据库或表上。
分库分表一定是为了支撑 高并发、数据量大两个问题的。
垂直切分，是 把一个有很多字段的表给拆分成多个表，或者是多个库上去。一般来说，会 将较少的、访问频率较高的字段放到一个表里去，然后 将较多的、访问频率较低的字段放到另外一个表里去。因为数据库是有缓存的，访问频率高的行字段越少，就可以在缓存里缓存更多的行，性能就越好。这个一般在表层面做的较多一些。
一般来说，满足下面的条件就可以考虑扩容了：
在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中，例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。
水平拆分 又称为 Sharding，它是将同一个表中的记录拆分到多个结构相同的表中。当 单表数据量太大 时，会极大影响 SQL 执行的性能 。分表是将原来一张表的数据分布到数据库集群的不同节点上，从而缓解单点的压力。
一般来说，单表有 200 万条数据 的时候，性能就会相对差一些了，需要考虑分表了。但是，这也要视具体情况而定，可能是 100 万条，也可能是 500 万条，SQL 越复杂，就最好让单表行数越少。
| # | 分库分表前 | 分库分表后 |
|---|---|---|
| 并发支撑情况 | 单机部署，扛不住高并发 | 从单机到多机，能承受的并发增加了多倍 |
| 磁盘使用情况 | 单机磁盘容量几乎撑满 | 拆分为多个库，数据库服务器磁盘使用率大大降低 |
| SQL 执行性能 | 单表数据量太大，SQL 越跑越慢 | 单表数据量减少，SQL 执行效率明显提升 |
hash(key) % N 或 id % N
❓ 常见问题：
你用过哪些分库分表中间件，简单介绍一下？
不同的分库分表中间件各自有什么特性，有什么优缺点？
分库分表中间件技术如何选型？
建议使用的是 sharding-jdbc 和 mycat。
sharding-jdbc 这种 client 层方案的优点在于不用部署，运维成本低，不需要代理层的二次转发请求，性能很高，但是如果遇到升级啥的需要各个系统都重新升级版本再发布，各个系统都需要耦合 sharding-jdbc 的依赖。其本质上通过配置多数据源，然后根据设定的分库分表策略，计算路由，将请求发送到计算得到的节点上。
Mycat 这种 proxy 层方案的缺点在于需要部署，自己运维一套中间件，运维成本高，但是好处在于对于各个项目是透明的，如果遇到升级之类的都是自己中间件那里搞就行了。
通常来说，这两个方案其实都可以选用，但是我个人建议中小型公司选用 sharding-jdbc，client 层方案轻便，而且维护成本低，不需要额外增派人手，而且中小型公司系统复杂度会低一些，项目也没那么多；但是中大型公司最好还是选用 mycat 这类 proxy 层方案，因为可能大公司系统和项目非常多，团队很大，人员充足，那么最好是专门弄个人来研究和维护 mycat，然后大量项目直接透明使用即可。
分库分表的常见问题有哪些？
你是如何解决分库分表的问题的？
下文一一讲解常见分库分表的问题及解决方案。
方案一：使用数据库事务
方案二：由应用程序和数据库共同控制
只要是进行切分，跨节点 Join 的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的 id，根据这些 id 发起第二次请求得到关联数据。
这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。
解决方案：与解决跨节点 join 问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和 join 不同的是每个节点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。
业务角度上的解决方案：
一旦数据库被切分到多个物理节点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的 ID 无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得 ID，以便进行 SQL 路由。
一些常见的主键生成策略：
来自淘宝综合业务平台团队，它利用对 2 的倍数取余具有向前兼容的特性（如对 4 取余得 1 的数对 2 取余也是 1）来分配数据，避免了行级别的数据迁移，但是依然需要进行表级别的迁移，同时对扩容规模和分表数量都有限制。总得来说，这些方案都不是十分的理想，多多少少都存在一些缺点，这也从一个侧面反映出了 Sharding 扩容的难度。
这个专题需要根据熟悉哪个数据库而定，但是主流、成熟的数据库都会实现一些基本功能，只是实现方式、策略上有所差异。由于本人较为熟悉 Mysql，所以下面主要介绍 Mysql 系统架构问题。
Mysql 支持两种复制：基于行的复制和基于语句的复制。
这两种方式都是在主库上记录二进制日志（binlog），然后在从库上以异步方式更新主库上的日志记录。这意味着：复制过程存在时延，这段时间内，主从数据可能不一致（即最终一致性）。
主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。
主服务器用来处理写操作以及实时性要求比较高的读操作，而从服务器用来处理读操作。
读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。
MySQL 读写分离能提高性能的原因在于：
MyISAM 引擎，提升查询性能以及节约系统开销；
数据库优化的路线一般为：SQL 优化、结构优化、配置优化、硬件优化。前两个方向一般是普通开发的考量点，而后两个方向一般是 DBA 的考量点。
SQL 优化是数据库优化的最常见、最初级手段。
在执行 SQL 语句，语句中字段的顺序、查询策略等都可能会影响到 SQL 的执行性能。
如何检验修改后的 SQL 确实有优化效果？这就需要用到执行计划（EXPLAIN）。
使用执行计划 EXPLAIN 用来分析 SELECT 查询效率，开发人员可以通过分析 EXPLAIN 结果来优化查询语句。
比较重要的字段有：
select_type - 查询类型，有简单查询、联合查询、子查询等key - 使用的索引rows - 扫描的行数更多内容请参考：MySQL 性能优化神器 Explain 使用分析
减少请求的数据量：
SELECT * 语句。WHERE 语句进行查询过滤，有时候也需要使用 LIMIT 语句来限制返回的数据。减少服务器端扫描的行数：
WHERE 后的过滤查询字段最好是索引字段）。一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
rows_affected = 0
do {
rows_affected = do_query(
"DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0
将一个大连接查询（JOIN）分解成对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，这样做的好处有：
IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);
数据库按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表将被最先处理。
在 FROM 子句中包含多个表的情况下：
如果有 3 个以上的表连接查询：
例如：查询员工的编号，姓名，工资，工资等级，部门名
emp 表被引用得最多，记录数也是最多，因此放在 form 字句的最后面
select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)
数据库按照从右到左的顺序解析 WHERE 子句。
因此，表之间的连接必须写在其他 WHERE 条件的左边，那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的之右。
emp.sal 可以过滤多条记录，写在 WHERE 字句的最右边
select emp.empno,emp.ename,emp.sal,dept.dname
from dept,emp
where (emp.deptno = dept.deptno) and (emp.sal > 1500)
* 号我们当时学习的时候，“*” 号是可以获取表中全部的字段数据的。
如果需要清空所有表记录，使用 TRUNCATE 比 DELETE 执行效率高：
DELETE 是一条一条记录的删除，而 Truncate 是将整个表删除，仅保留表结构
例如使用 mysql 的 concat() 函数会比使用 || 拼接速度快，因为 concat() 函数已经被 mysql 优化过了。
如果表或列的名称太长了，使用一些简短的别名也能稍微提高一些 SQL 的性能。毕竟要扫描的字符长度就变少了。
我们在编写 SQL 的时候，官方推荐的是使用大写来写关键字，因为 Oracle 服务器总是先将小写字母转成大写后，才执行
>= 替代 >
❌ 低效方式：
-- 首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
SELECT * FROM EMP WHERE DEPTNO > 3
✔ 高效方式：
-- 直接跳到第一个DEPT等于4的记录
SELECT * FROM EMP WHERE DEPTNO >= 4
❌ 低效方式：
select * from emp where sal = 1500 or sal = 3000 or sal = 800;
✔ 高效方式：
select * from emp where sal in (1500,3000,800);
如果索引是建立在多个列上，只有在它的第一个列被 WHERE 子句引用时，优化器才会选择使用该索引。 当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引
create index emp_sal_job_idex
on emp(sal,job);
----------------------------------
select *
from emp
where job != 'SALES';
SQL 关键字尽量大写，如：Oracle 默认会将 SQL 语句中的关键字转为大写后在执行。
数据库结构优化可以从以下方向着手：
范式和反范式各有利弊，需要根据实际情况权衡。
范式化的目标是尽力减少冗余列，节省空间。
范式化的优点是：
DISTINCT 或 GROUP BY 操作减少。范式化的缺点是：增加关联查询。
反范式化的目标是适当增加冗余列，以避免关联查询。
反范式化的缺点是：
配置优化主要是针对 Mysql 服务器，例如：
max_connections、max_heap_table_size、open_files_limit、max_allowed_packet等等。在不同环境，不同场景下，应该酌情使用合理的配置。这种优化比较考验 Mysql 运维经验，一般是 DBA 的考量，普通开发接触的较少。
Mysql 配置说明请参考：Mysql 服务器配置说明
数据库扩容、使用高配设备等等。核心就是一个字：钱。
记 A->B 表示 A 函数决定 B，也可以说 B 函数依赖于 A。
如果 {A1，A2，... ，An} 是关系的一个或多个属性的集合，该集合函数决定了关系的其它所有属性并且是最小的，那么该集合就称为键码。
对于 A->B，如果能找到 A 的真子集 A'，使得 A'-> B，那么 A->B 就是部分函数依赖，否则就是完全函数依赖；
对于 A->B，B->C，则 A->C 是一个传递依赖。
以下的学生课程关系的函数依赖为 Sno, Cname -> Sname, Sdept, Mname, Grade，键码为 {Sno, Cname}。也就是说，确定学生和课程之后，就能确定其它信息。
| Sno | Sname | Sdept | Mname | Cname | Grade |
|---|---|---|---|---|---|
| 1 | 学生-1 | 学院-1 | 院长-1 | 课程-1 | 90 |
| 2 | 学生-2 | 学院-2 | 院长-2 | 课程-2 | 80 |
| 2 | 学生-2 | 学院-2 | 院长-2 | 课程-1 | 100 |
| 3 | 学生-3 | 学院-2 | 院长-2 | 课程-2 | 95 |
不符合范式的关系，会产生很多异常，主要有以下四种异常：
范式理论是为了解决以上提到四种异常。
高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。
属性不可分。
每个非主属性完全函数依赖于键码。
可以通过分解来满足。
分解前
| Sno | Sname | Sdept | Mname | Cname | Grade |
|---|---|---|---|---|---|
| 1 | 学生-1 | 学院-1 | 院长-1 | 课程-1 | 90 |
| 2 | 学生-2 | 学院-2 | 院长-2 | 课程-2 | 80 |
| 2 | 学生-2 | 学院-2 | 院长-2 | 课程-1 | 100 |
| 3 | 学生-3 | 学院-2 | 院长-2 | 课程-2 | 95 |
以上学生课程关系中，{Sno, Cname} 为键码，有如下函数依赖：
Grade 完全函数依赖于键码，它没有任何冗余数据，每个学生的每门课都有特定的成绩。
Sname, Sdept 和 Mname 都部分依赖于键码，当一个学生选修了多门课时，这些数据就会出现多次，造成大量冗余数据。
分解后
关系-1
| Sno | Sname | Sdept | Mname |
|---|---|---|---|
| 1 | 学生-1 | 学院-1 | 院长-1 |
| 2 | 学生-2 | 学院-2 | 院长-2 |
| 3 | 学生-3 | 学院-2 | 院长-2 |
有以下函数依赖：
关系-2
| Sno | Cname | Grade |
|---|---|---|
| 1 | 课程-1 | 90 |
| 2 | 课程-2 | 80 |
| 2 | 课程-1 | 100 |
| 3 | 课程-2 | 95 |
有以下函数依赖：
上面的 关系-1 中存在以下传递依赖：Sno -> Sdept -> Mname，可以进行以下分解：
关系-11
| Sno | Sname | Sdept |
|---|---|---|
| 1 | 学生-1 | 学院-1 |
| 2 | 学生-2 | 学院-2 |
| 3 | 学生-3 | 学院-2 |
关系-12
| Sdept | Mname |
|---|---|
| 学院-1 | 院长-1 |
| 学院-2 | 院长-2 |
Mysql 有多种存储引擎，不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在 Mysql 服务层统一处理的。
简单列举几个存储引擎：
InnoDB 和 MyISAM 是目前使用的最多的两种 Mysql 存储引擎。
Oracle - 久负盛名的商业数据库。功能强大、稳定。最大的缺点就是费钱。Mysql - 曾经是互联网公司的最爱，但自动 Mysql 被 Oracle 公司收购后，好日子可能一去不复返。很多公司或开源项目已经逐渐寻找其他的开源产品来替代 Mysql。MariaDB - 开源关系型数据库。 MySQL 的真正开源的发行版本，由 Mysql 部分核心人员创建。可作为 Mysql 的替代产品。PostgreSQL - 开源关系型数据库。和 MySQL 的工作方式非常相似，社区支持做得很好。可作为 Mysql 的替代产品。SQLite - 开源的轻量级数据库，移动端常常使用。H2 - 内存数据库，一般用作开发、测试环境数据库。SQL Server - 微软 Windows 生态系统的数据库。我想，Java 程序员应该没人用吧。目前为止，Java 领域用的最多的关系型数据库，应该还是 Oracle 和 Mysql，所以这里做一下比较。
在 Mysql 中，一个用户可以创建多个库。
而在 Oracle 中，Oracle 服务器是由两部分组成
一个数据库实例可拥有多个用户，一个用户默认拥有一个表空间。
表空间是存储我们数据库表的地方，表空间内可以有多个文件。
（1）主键递增
Mysql 可以设置 AUTO_INCREMENT 约束来指定主键为自增序列。
Oracle 需要通过 CREATE SEQUENCE 创建序列。
（2）分页查询
Mysql 分页基于 SELECT ... FROM ... LIMIT ... 完成，较简单。
select * from help_category order by parent_category_id limit 10,5;
Oracle 分页基于 SELECT ... FROM (SELECT ROWNUM ...) WHERE ... 完成，较复杂。
select * from
(select rownum rr,a.* from (select * from emp order by sal) a )
where rr>5 and rr<=10;
COMMIT。REPEATABLE READ）READ COMMITTED）和串行化（SERIALIZABLE） 两种事务隔离级别，默认事务隔离级别是读已提交（READ COMMITTED）不同数据库中，对数据类型的支持是不一样的。
即使存在同一种数据类型，也可能存在名称不同、或大小不同等问题。
因此，对于数据类型的支持详情必须参考各数据库的官方文档。
下面列举一些常见数据类型对比：
| 数据类型 | Oracle | MySQL | PostgreSQL |
|---|---|---|---|
boolean |
Byte | N/A | Boolean |
integer |
Number | Int Integer | Int Integer |
float |
Number | Float | Numeric |
currency |
N/A | N/A | Money |
string (fixed) |
Char | Char | Char |
string (variable) |
Varchar Varchar2 | Varchar | Varchar |
binary object |
Long Raw | Blob Text | Binary Varbinary |
数据类型对比表摘自 SQL 通用数据类型、SQL 用于各种数据库的数据类型
在 MySQL InnoDB 存储引擎中，COUNT(*) 和 COUNT(1) 都是对所有结果进行 COUNT。因此COUNT(*)和COUNT(1)本质上并没有区别，执行的复杂度都是 O(N)，也就是采用全表扫描，进行循环 + 计数的方式进行统计。
如果是 MySQL MyISAM 存储引擎，统计数据表的行数只需要O(1)的复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了row_count值，而一致性则由表级锁来保证。因为 InnoDB 支持事务，采用行级锁和 MVCC 机制，所以无法像 MyISAM 一样，只维护一个row_count变量，因此需要采用扫描全表，进行循环 + 计数的方式来完成统计。
需要注意的是，在实际执行中，COUNT(*)和COUNT(1)的执行时间可能略有差别，不过你还是可以把它俩的执行效率看成是相等的。
另外在 InnoDB 引擎中，如果采用COUNT(*)和COUNT(1)来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于COUNT(*)和COUNT(1)来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。
然而如果想要查找具体的行，那么采用主键索引的效率更高。如果有多个二级索引，会使用 key_len 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。
这里我总结一下：
COUNT(*)= COUNT(1)> COUNT(字段)。我们尽量使用COUNT(*)，当然如果你要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。COUNT(*)，尽量在数据表上建立二级索引，系统会自动采用key_len小的二级索引进行扫描，这样当我们使用SELECT COUNT(*)的时候效率就会提升，有时候可以提升几倍甚至更高。——摘自极客时间 - SQL 必知必会
ORDER BY 就是对记录进行排序。如果你在 ORDER BY 前面用到了 GROUP BY，实际上这是一种分组的聚合方式，已经把一组的数据聚合成为了一条记录，再进行排序的时候，相当于对分的组进行了排序。
一条完整的 SELECT 语句内部的执行顺序是这样的：
——摘自极客时间 - SQL 必知必会
索引是个前提，其实选择与否还是要看表的大小。你可以将选择的标准理解为小表驱动大表。在这种方式下效率是最高的。
比如下面这样：
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc=A.cc)
当 A 小于 B 时，用 EXISTS。因为 EXISTS 的实现，相当于外表循环，实现的逻辑类似于：
for i in A
for j in B
if j.cc == i.cc then ...
当 B 小于 A 时用 IN，因为实现的逻辑类似于：
for i in B
for j in A
if j.cc == i.cc then ...
哪个表小就用哪个表来驱动，A 表小就用 EXISTS，B 表小就用 IN。
——摘自极客时间 - SQL 必知必会
此处可能存在不合适展示的内容，页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容，可点击提交进行申诉，我们将尽快为您处理。