数据库面试题之一
数据库
数据库并发问题
脏读
A 事务执行过程中,B 事务读取了 A 事务的修改。但是由于某些原因,A 事务没有完成提交,发生 RollBack 操作,则 B 事务所读取的数据就会是不正确的,这个未提交数据就是脏读(Dirty Read)。脏读产生的流程如下:
幻读
B 事务读取了两次数据,在这两次的读取过程中 A 事务添加了数据,B 事务的这两次读取出来的集合不一样。幻读看起来和不可重复读差不多,但幻读强调的集合的增减,而不是单独一条数据的修改。幻读产生的流程如下:
不可重复读
B 事务读取了两次数据,在这两次的读取过程中 A 事务修改了数据,B 事务的这两次读取出来的数据不一样。B 事务这种读取的结果,即为不可重复读(Nonrepeatable Read)。不可重复读的产生的流程如下:
第一类丢失更新
在完全未隔离事务的情况下,两个事务更新同一条数据资源,某一事务完成,另一事务异常终止,回滚造成第一个完成的更新也同时丢失 。第一类丢失更新的问题,在现代关系型数据库已经不会发生,这里不再累述。
第二类丢失更新
不可重复读有一种特殊情况,两个事务更新同一条数据资源,后完成的事务会造成先完成的事务更新丢失,这种情况就是第二类丢失更新。主流的数据库已经默认屏蔽了第一类丢失更新问题(即:后做的事务撤销,发生回滚造成已完成事务的更新丢失),但日常开发的时候仍需要特别注意第二类丢失更新。它产生的流程如下:
数据库隔离级别
为了解决上面提及的数据库并发问题,主流关系型数据库都会提供四种事务隔离级别:
读未提交(Read Uncommitted)
在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。该隔离级别是最低的隔离级别,虽然拥有超高的并发处理能力及很低的系统开销,但很少用于实际应用。因为采用这种隔离级别只能防止第一类更新丢失问题,不能解决脏读,幻读及不可重复读问题。
读已提交(Read Committed)
这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的),例如 Oracle 数据库。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别可以防止脏读问题,但会出现幻读及不可重复读问题。
可重复读(Repeatable Read)
这是 MySQL 的默认事务隔离级别,它确保在整个事务过程中,对同一条数据的读取结果是相同的,不管其他事务是否在对共享数据进行更新,也不管其他事务更新提交与否,这种隔离级别可以防止除幻读外的其他问题。
串行化(Serializable)
这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读、第二类更新丢失问题。在这个级别,可以解决上面提到的所有并发问题,但可能导致大量的超时现象和锁竞争,通常数据库不会用这个隔离级别,可以其他的机制来解决这些问题,例如乐观锁和悲观锁。
案例说明
上图中是典型的第二类丢失更新问题,后果异常严重。当数据库隔离级别为读已提交(Read Committed)及以下隔离级别时,会出现不可重复读的现象。从上面的表格可以看出,当事务隔离级别设置为可重复读(Repeatable Read)时,可以避免不可重复读的现象出现。
总结
这四种隔离级别会产生的问题如下(YES 表示存在对应的问题):
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
中的列是不会使用索引的。因此如果数据库默认排序可以符合要求的情况下,不要使用排序操作,同时尽量使用不包含多个列的排序,如果需要最好给这些列创建组合索引