数据库面试题之一

发表于 阅读次数： 评论数： 本文字数： 3.5k 阅读时长 ≈ 3 分钟

数据库

数据库并发问题

脏读

A 事务执行过程中，B 事务读取了 A 事务的修改。但是由于某些原因，A 事务没有完成提交，发生 RollBack 操作，则 B 事务所读取的数据就会是不正确的，这个未提交数据就是脏读（Dirty Read）。脏读产生的流程如下：

db-dirty-read

幻读

B 事务读取了两次数据，在这两次的读取过程中 A 事务添加了数据，B 事务的这两次读取出来的集合不一样。幻读看起来和不可重复读差不多，但幻读强调的集合的增减，而不是单独一条数据的修改。幻读产生的流程如下：

db-phantom-read

不可重复读

B 事务读取了两次数据，在这两次的读取过程中 A 事务修改了数据，B 事务的这两次读取出来的数据不一样。B 事务这种读取的结果，即为不可重复读（Nonrepeatable Read）。不可重复读的产生的流程如下：

db-nonrepeatable-read

第一类丢失更新

在完全未隔离事务的情况下，两个事务更新同一条数据资源，某一事务完成，另一事务异常终止，回滚造成第一个完成的更新也同时丢失 。第一类丢失更新的问题，在现代关系型数据库已经不会发生，这里不再累述。

第二类丢失更新

不可重复读有一种特殊情况，两个事务更新同一条数据资源，后完成的事务会造成先完成的事务更新丢失，这种情况就是第二类丢失更新。主流的数据库已经默认屏蔽了第一类丢失更新问题（即：后做的事务撤销，发生回滚造成已完成事务的更新丢失），但日常开发的时候仍需要特别注意第二类丢失更新。它产生的流程如下：

db-nonrepeatable-read-2

数据库隔离级别

为了解决上面提及的数据库并发问题，主流关系型数据库都会提供四种事务隔离级别：

读未提交（Read Uncommitted）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。该隔离级别是最低的隔离级别，虽然拥有超高的并发处理能力及很低的系统开销，但很少用于实际应用。因为采用这种隔离级别只能防止第一类更新丢失问题，不能解决脏读，幻读及不可重复读问题。

读已提交（Read Committed）

这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的），例如 Oracle 数据库。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别可以防止脏读问题，但会出现幻读及不可重复读问题。

可重复读（Repeatable Read）

这是 MySQL 的默认事务隔离级别，它确保在整个事务过程中，对同一条数据的读取结果是相同的，不管其他事务是否在对共享数据进行更新，也不管其他事务更新提交与否，这种隔离级别可以防止除幻读外的其他问题。

串行化（Serializable）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读、第二类更新丢失问题。在这个级别，可以解决上面提到的所有并发问题，但可能导致大量的超时现象和锁竞争，通常数据库不会用这个隔离级别，可以其他的机制来解决这些问题，例如乐观锁和悲观锁。

案例说明

db-isolate-example

上图中是典型的第二类丢失更新问题，后果异常严重。当数据库隔离级别为读已提交（Read Committed）及以下隔离级别时，会出现不可重复读的现象。从上面的表格可以看出，当事务隔离级别设置为可重复读（Repeatable Read）时，可以避免不可重复读的现象出现。

总结

这四种隔离级别会产生的问题如下（YES 表示存在对应的问题）：

db-isolate-level

MySQL

索引

索引的类型（四种）

  • FULLTEXT：即为全文索引，目前只有 MyISAM 引擎支持，其可以在 CREATE TABLE，ALTER TABLE，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引
  • HASH：由于 HASH 的唯一性及类似键值对的形式，很适合作为索引，HASH 索引可以一次定位，不需要像树形索引那样逐层查找，因此具有极高的效率。但是，这种高效是有条件的，即只在 “=” 和 “in” 条件下才高效，对于范围查询、排序及组合索引仍然效率不高
  • BTREE：一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口 Root 开始，依次遍历 Node，获取 Leaf，这是 MySQL 里默认和最常用的索引类型
  • RTREE：在 MySQL 很少使用，仅支持 geometry 数据类型，支持该类型的存储引擎有 MyISAM、BDb、InnoDb、NDb、Archive

相对于 BTREE，RTREE 的优势在于范围查找。

索引的种类（五种）

  • 普通索引：仅加速查询
  • 全文索引：对文本的内容进行分词和搜索
  • 唯一索引：加速查询 + 列值唯一（可以有 NULL）
  • 主键索引：加速查询 + 列值唯一（不可以有 NULL） + 表中只能有一个主键索引
  • 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（使用多个单列索引组合搜索）

创建索引的时机

一般来说，在 WHERE 和 JOIN 子句中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对 <、<=、=、>、>=、BETWEEN、IN 以及某些时候的 LIKE 才会使用索引。例如下述的 SQL 语句，就需要对 city 和 age 列建立索引，由于 mytable_m 表的 userame 也出现在了 JOIN 子句中，因此也有对它建立索引的必要。

1
SELECT t.Name  FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;

特别注意：上面提到只有某些时候的 LIKE 才需建立索引，因为在以通配符 % 开头作查询时，MySQL 不会使用索引；只有以通配符 % 结尾做查询时，MySQL 才会使用到索引。但有一种情况例外，那就是当触发了覆盖索引（select 的数据列只从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖）的情况下，以通配符 % 开头作查询 MySQL 也会使用索引。例如：如果表里面只有 id 和 username 两个字段且都加了索引，那么 select * like '%username' 查询也是会使用索引的，前提是 select 数据列都加了索引。

哪些字段应该创建索引

  • 增删改非常频繁的字段不适合作为索引
  • 查询中与其他表关联的字段，例如外键应该建立索引
  • WHERE 和 JOIN 子句中，较频繁作为查询条件的字段应该创建索引
  • 查询中排序（order by）、分组（group by）、统计的字段应该建立索引
  • 唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如：性别字段

索引不生效的情况

  • 对于多列索引，如果不是使用的第一部分，则不会使用索引
  • 如果 MySQL 估算使用全表扫描要比使用索引快，则不会使用索引
  • like 查询，即是以 % 开头的查询不会使用索引，除非 select 数据列都加了索引
  • 如果列类型是字符串，那一定要在条件中将数据使用单引号包起来，否则索引不生效
  • 如果条件中有 or，即使其中有部分条件带索引也不会使用。换言之，必须所有列都建有索引才有效

索引使用的代价

  • 索引虽然可以大大提高了查询速度，但同时也会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE 操作；因为更新表时，MySQL 不仅要保存数据，还要更新索引文件
  • 建立索引会占用更多的磁盘空间，这是因为需要分配磁盘空间给索引文件，一般情况这个问题不太严重，但如果在一个大表上创建了多种组合索引，索引文件的体积会膨胀得很快

索引使用注意事项

  • 针对普通查询

    • 避免使用 select *
    • 连表时注意条件类型需一致
    • 创建表时尽量时 char 代替 varchar
    •  count (1) 或 count (列) 代替 count (*)
    • 使用表连接（JOIN）来代替子查询（Sub-Queries）

  • 针对索引使用

    • 使用组合索引代替多个单列索引（经常使用多个条件查询时）
    • 索引散列值（重复多的值）不适合建索引，例如：性别字段不适合建索引
    • 索引不会包含有 NULL 值的列，只要列中包含有 NULL 值都将不会被包含在索引中，组合索引中只要有一列含有 NULL 值，那么这一列对于此组合索引就是无效的，因此在数据库设计时不要让字段的默认值为 NULL
    • 不要在列上进行运算，例如 select * from users where YEAR(adddate)<2007，将在每个行记录上进行运算，这将导致索引失效而进行全表扫描，因此可以改成 select * from users where adddate<’2007-01-01′
    • 尽量使用短索引，对串列进行索引，如果可能应该指定一个前缀长度。例如：如果有一个 CHAR (255) 的列，如果在前 10 个或 20 个字符内，多数值是惟一的，那么就不要对整个列进行索引；短索引不仅可以提高查询速度，还可以节省磁盘空间和 I/O 操作
    • MySQL 5.0 之前，SQL 查询只能使用一个索引，因此如果 WHERE 子句中已经使用了索引的话，那么 order by、group by 中的列是不会使用索引的。因此如果数据库默认排序可以符合要求的情况下，不要使用排序操作，同时尽量使用不包含多个列的排序，如果需要最好给这些列创建组合索引