文章目录
1 事务的特性（ACID）
原子性、一致性、隔离性、持久性
Atomicity、Consistency、Isolation、Durability
- 原子性：事务在逻辑上是必须不可分割的最小单元，事务中的操作要么全部执行，要么全都不执行，不能只完成部分操作。
- 一致性：数据库总是从一个一致性的状态转换到另一个一致性的状态。
- 隔离性：系统必须保证事务不受其他并发执行事务的影响，即当多个事务同时运行时，各事务之间相互隔离，不可互相干扰。
- 持久性：一个已完成的事务对数据所做的任何变动在系统中是永久有效的，即使系统崩溃，修改的数据也不会丢失。
2 MySQL中InnoDB支持的四种事务隔离级别
名词解释
- 脏读：一个事务读取到了另外一个事务还没有提交的数据。
- 不可重复读：在一个事务内多次读取数据结果不一样。
- 虚读（幻读）：一个事务在前后两次查询同一个范围的时候、后一次查询看到了前一次查询未看到的行.，这是因为在两次查询过程中有另外一个事务插入数据造成的。
针对上述问题，可以通过设置隔离级别解决
- read uncommited ：未提交读(可能出现脏读、不可重复读、虚读)
- read committed：提交读(避免了脏读)
- repeatable read：可重读(避免了脏读、不可重复读)，
- serializable ：串行化事务(避免了脏读、不可重复读、虚读)
未提交读：事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还存在不可重复读和虚读，很少使用。
提交读：多数数据库的默认隔离级别，事务只能看见已提交事务的修改，避免了脏读。存在不可重复读，两次执行同样的查询可能会得到不同结果。
可重复读（MySQL默认的隔离级别）：解决了不可重复读，保证同一个事务中多次读取同样的记录结果一致，。但无法解决幻读，幻读指当某个事务在读取某个范围内的记录时，会产生幻行。
可串行化：最高隔离级别，通过强制事务串行执行避免幻读。在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际很少使用，只有非常需要确保数据一致性时考虑。
3 存储引擎
3.1 MyISAM
① MySQL5.1及之前的默认引擎，提供的特性包括全文索引、空间索引等，不支持事务、行锁和外键。
② 最大的缺陷是崩溃后无法恢复，在插入和更新数据时需要锁定整张表，效率低。
③ 对于只读的数据或者表比较小、可以忍受修复操作的情况可以使用MyISAM。
3.2 InnoDB
① MySQL5.1 开始的默认引擎，最大的优点是支持事务和外键，InnoDB 的性能和自动崩溃恢复特性使它在非事务型需求中也很流行，一般应该优先考虑使用 InnoDB。
② 底层存储结构是 B+ 树，每个节点都对应 InnoDB 的一个页。非叶子节点只有 key 值，叶子节点包含完整的数据。
③ 支持行锁，采用 MVCC 支持高并发，实现了四个标准的隔离级别，默认级别是可重复读，通过间隙锁防止幻读。
④ 基于聚簇索引，对主键查询有很高的性能。
⑤ 内部做了很多优化，例如加速读操作的自适应哈希索引、加速插入操作的缓冲区等。
3.3 Memory
① 如果需要快速访问数据且这些数据不会被修改，重启以后丢失也没有关系，可以使用 Memory 表。
② 数据保存在内存，不需要磁盘 IO，表的结构在重启后会保留，数据会丢失。
③ 支持哈希索引，查找速度快。
④ 使用表锁，并发性能低
4 什么是索引？都有哪些索引？
4.1 索引定义、优缺点
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
但是创建索引和维护需要耗费时间，并且该耗费时间与数据量的大小成正比；另外，索引需要占用物理空间，给数据的维护造成很多麻烦。
4.2 索引类型
普通索引
唯一索引
主键索引
全文索引
组合索引
- 普通索引（单列索引）：单列索引是最基本的索引，它没有任何限制。
- 唯一索引：唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）。
- 主键索引：主键索引的列的数据非空,唯一的；一个表中建议只有一个主键列。
- 全文索引：通过建立倒排索引,可以提高数据的检索效率,解决判断字段中 是否包含 的问题。全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。全文索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。全文索引配合match against操作使用，而不是一般的where语句加like。
- 组合索引：组合索引是在多个字段上创建的索引。组合索引遵守“最左前缀”原则，即在查询条件中使用了组合索引的第一个字段，索引才会被使用。因此，在组合索引中索引列的顺序至关重要。
4.3 索引存储类型
B-Tree索引（InnoDB支持B+Tree索引）
Hash索引
全文索引
- Hash索引： 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。
- B+Tree索引：
InnoDB 使用 B+ Tree。所有的值都是顺序存储的，并且每个叶子页到根的距离相同。B+Tree 索引能够加快访问数据的速度，存储引擎不再需要进行全表扫描来获取数据，而是从索引的根节点开始搜索。根节点的键中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。叶子节点的指针指向的是被索引的数据，而不是其他节点页。 - 全文索引：
通过建立倒排索引,可以提高数据的检索效率,解决判断字段中 是否包含 的问题。全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。全文索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。全文索引配合match against操作使用，而不是一般的where语句加like。
4.4 B+Tree索引
- B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。
- 另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。
- 因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。
- B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
4.5 聚簇索引与聚集索引
B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。
-
聚集索引（聚簇索引）：以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。
-
非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。
- 聚集索引查找 id>=18 并且 id<40 的用户数据
select * from user where id >=18 and <40
- 非聚集索引查找 id=33的用户数据
select * from user where id=33
4.6 非聚集索引与聚集索引的区别
非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。
4.7 聚簇索引的优缺点
-
优点：可以把相关数据保存在一起；将索引和数据保存在同一个 B 树中，获取数据比非聚簇索引要更快。
-
缺点：如果数据全部在内存中会失去优势；更新代价高，强制每个被更新的行移动到新位置；插入行或主键更新时，可能导致页分裂，占用更多磁盘空间。
5 简述内连接和外连接
6 索引失效的情况
① 隐式类型转换，常见情况是在 SQL 的 WHERE 条件中字段类型为字符串，其值为数值，如果没有加引号那么 MySQL 不会使用索引。
② 如果条件中 OR 只有部分列使用了索引，索引会失效。
③ 执行 LIKE 操作时，最左匹配会被转换为比较操作，但如果以通配符开头，存储引擎就无法做比较，索引失效。
④ 如果查询中的列不是独立的，则 MySQL 不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
⑤ 对于多个范围条件查询，MySQL 无法使用第一个范围列后面的其他索引列，对于多个等值查询则没有这种限制。
⑥ 如果 MySQL 判断全表扫描比使用索引查询更快，则不会使用索引。
7 查询优化？
① 避免全表扫描：考虑在 WHERE 和 ORDER BY 涉及的列上建立索引，IN 和 NOT IN 也要慎用，尽量用 BETWEEN 取代。
② 优化 COUNT：某些业务不要求完全精确的 COUNT 值，此时可以使用近似值来代替，EXPLAIN 估算的行数就是一个不错的近似值。
③ 避免子查询：在 MySQL5.5 及以下版本避免子查询，因为执行器会先执行外部的 SQL 再执行内部的 SQL，可以用关联查询代替。
④ 禁止排序：当查询使用 GROUP BY 时，结果集默认会按照分组字段排序，如果不关心顺序，可以使用 ORDER BY NULL 禁止排序。
⑤ 优化分页：从上一次取数据的位置开始扫描，避免使用 OFFSET。
⑥ 优化 UNION：MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要消除重复的行，否则使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，对整个临时表的数据做唯一性检查，代价非常高。
⑦ 使用用户自定义变量：用户自定义变量是一个用来存储内容的临时容器，在连接 MySQL 的整个过程中都存在，可以在任何可以使用表达式的地方使用自定义变量，避免重复查询刚刚更新过的数据。
8 delete、drop、truncate的区别
-
DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
-
TRUNCATE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
-
TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。
-
速度来说，一般 drop > truncate > delete。
使用建议：
- 如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；
- 如果想删除表，当然用drop；
- 如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；
- 如果和事务有关，或者想触发trigger，还是用delete；
9 数据库范式
范式是数据库设计规范，范式越高则数据库冗余越小，但查询也更复杂，一般只需满足第三范式。
10 innoDB锁机制
10.1 innoDB锁机制介绍
- 从锁的颗粒来说锁分为行锁和表锁;
在innodb中提供了两种锁机制:
-
乐观锁: 并不是硬编码的实现,而是通过version版本号来进行实现(innodb中并没有实现乐观锁)
-
悲观锁: 这是innodb存储引擎默认实现的锁机制,这种锁是表锁,而悲观锁的实现又分为两种实现:
- 共享锁(S锁),读锁
- 在读取的行设置一个共享模式的锁,这个共享锁允许其他的会话读取数据,但是不允许修改,如果其他的会话也需要修改数据,则要等待持有共享锁的会话结束锁的释放,才能修改数据;
- 可以在多个会话中加多个共享锁
- 添加多个共享锁容易出现互相等待释放的情况,造成死锁问题,所以使用多个共享锁一定要慎重;
- 排它锁(X锁),写锁
排它锁是不允许重复添加的
排它锁没有死锁问题
- 共享锁(S锁),读锁
10.2 显式加锁
共享锁的添加: lock in share mode
排它锁的添加: for update
10.3 mvcc并发系统快照读与当前读
-
快照读: 不加锁的select操作就属于快照读
-
当前读: 加锁的操作属于当前读
当前读读到的是最新的数据,而且在读取的过程中是不允许其他的事务修改数据;
268
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
