MySQL 整理

1. 事务四大特性（ACID）原子性、一致性、隔离性、持久性？

• 原子性： 事务是一个整体，要么都完成，要么都失败回滚
• 一致性：事务总是从一个一致性状态转移至另一个一致性状态，事务开始前和结束后，数据库的完整性约束没有被破坏。
• 隔离性：并发时，每个事务之间互相隔离，互不干扰
• 持久性：事务提交后，对数据的修改时永久性的

2. 事务的并发？事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别？

• 事务隔离级别

  • 读未提交：事务可以读到其他事务，没有提交的修改，产生：脏读，不可重复读，幻读
  • 读提交：事务可以看到其他事务已经提交的修改，产生: 不可重复读，幻读
  • 可重复度： 事务重头到尾读到的值都是事务开始时的状态，幻读
  • 串行：并发的事务串行执行

• 引发问题

  • 脏读：事务B读取到了事务A未提交的数据
  • 不可重复读：事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变
  • 幻读：可重复读级别，当前读，就会出现幻读（后一次查询读到了前一次查询没有看到的行，新记录）

• innodb 默认隔离级别： 可重复读

3. MySQL常见的三种存储引擎（InnoDB、MyISAM、MEMORY）的区别？

• InnoDB：支持事务，行级锁，外键，索引和数据文件放在一起，聚簇索引节点存储数据，非聚簇索引节点存储数据主键
• MyISAM：不支持事务，表级锁，索引和数据文件分开存储，聚簇索引和非聚簇索引都存储数据地址
• MEMORY： 内存存储引擎，默认使用哈希索引

4. MySQL的MyISAM与InnoDB两种存储引擎在，事务、锁级别，各自的适用场景？

• InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

• MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

5. 查询语句不同元素（where、jion、limit、group by、having等等）执行先后顺序？

• from… join… on… where… group by… having… select distinct … order by… limit…

6. 什么是临时表，临时表什么时候删除?

• 临时表：临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见
• create temporary table Name
• 关闭连接时，Mysql会自动删除表并释放所有空间

7. MySQL B+Tree索引和Hash索引的区别？

• 查询时间：B+树索引 O(log(n))，hash索引O(1)
• 排序：B+支持排序，hash不支持
• 范围查询：B+支持范围查询，hash不支持
• 模糊查询：B+最左前缀匹配，hash不支持
• 联合索引：B+支持，hash不支持

8. sql查询语句确定创建哪种类型的索引？如何优化查询？

• 待定

9. 聚集索引和非聚集索引区别？

• 聚簇索引：数据存储和索引放在一起，特点是存储数据的顺序和索引顺序一致
• 非聚簇索引：索引和主键放在一起，查询时需要可能需要获取到主键然后回表查询
• 聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针

10. 有哪些锁（乐观锁悲观锁），select 时怎么加排它锁？

• 乐观锁：认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做

• 悲观锁：select quantity from items where id=100 for update;，这样id=100这条记录就被锁定了，其他查询（使用for-update）或修改这条记录的事务需要等待这个事务提交后再执行，select…for update语句执行中所有扫描过的行都会被锁上，因此MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。但是有一个弊端，加锁时间过长，影响程序并发性。

• 表级锁

• 行级锁

• 页级锁

InnoDB两种类型的行锁

• 共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
• 排他锁：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁

11. 非关系型数据库和关系型数据库区别，优势比较？

• 关系型数据库：采用了关系模型来组织数据的数据库
  • 优点
    • 二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解
    • 通用的SQL语言是操作关系型数据库变得非常方便
    • 丰富的完整性大大降低了数据冗余和数据不一致的概率
  • 缺点
    • 海量数据读写性能稍差
    • 格式固定统一，数据灵活度较低
• 非关系型数据库：数据结构化存储方法的集合，可以是文档或者键值对等。（文档型，键值对，列式数据库，图形数据库）
  • 优点
    • 格式灵活
    • 速度快
    • 高扩展
    • 成本低
  • 缺点
    • 无事务
    • 没有统一查询
    • 不方便复杂查询

12. 数据库三范式，根据某个场景设计数据表？

• 范式：符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。通俗来说就是一张数据表的表结构所符合的某种设计标准的级别
• 第一范式：关系中的每个属性都不可再分
• 第二范式：属性完全依赖于主键
  • 码：设 K 为某表中的一个属性或属性组，若除 K 之外的所有属性都完全函数依赖于 K（这个“完全”不要漏了），那么我们称 K 为候选码，简称为码
  • 主属性：在任何一个码中的属性成为主属性
• 第三范式：属性不依赖于其它非主属性，属性直接依赖于主键，没有传递函数依赖。基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题
• BCNF：在3NF基础上消除主属性对于码的部分与传递函数依赖

13. 数据库的读写分离、主从复制，主从复制分析的 7 个问题？

• 读写分离：因为写操作比较耗时，所以写操作影响了查询性能。主数据库处理增改删等操作。从数据库处理select操作
• 通过主从复制解决数据同步，通过读写分离提高数据库并发性
• 主从复制：
  • 基于语句的复制，在主服务器上执行的SQL语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高
  • 基于行的复制，主服务器改变的内容复制到从服务器上
  • 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制

  当master的二进制日志每产生一个事件，都需要发往slave，如果我们有N个slave,那是发N次，还是只发一次？如果只发一次，发给了slave-1，那slave-2,slave-3,…它们怎么办？
  显 然，应该发N次。实际上，在MySQL master内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作，还的维护N个线程，负担会很重。可以这样，slave-1是master的从，slave-1又是slave-2,slave-3,…的主，同时slave-1不再负责select。 slave-1将master的复制线程的负担，转移到自己的身上。这就是所谓的多级复制的概念。

14. 使用explain优化sql和索引？

• type：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
• select type：
  • simple:简单select（不使用union或子查询）。
  • primary:最外面的select。
  • union:union中的第二个或后面的select语句。
  • dependent union:union中的第二个或后面的select语句，取决于外面的查询。
  • union result:union的结果。
  • subquery:子查询中的第一个select。
  • dependent subquery:子查询中的第一个select，取决于外面的查询。
  • derived:导出表的select（from子句的子查询）
• table：属于哪张数据表
• possible_keys：可能应用在这次查询的索引
• key：实际使用的索引
• ref：表示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：MySQL认为必须检查的用来返回请求数据的行数
• extra：执行计划其他信息
• 优化
  • count和max优化考虑添加索引
  • 子查询优化，考虑使用join查询（考虑重复数据）
  • group by 会用到 临时表 filesort，尾部增加order by null会省掉filesort
  • 对group by 列加索引（列索引或组合索引）可以使用最左前缀原则
  • group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。

15. MySQL慢查询怎么解决？

• sql优化
• 加机器

16. 什么是 内连接、外连接、交叉连接、笛卡尔积等？

• 自然连接：自然连接只考虑两个关系中在共同属性上取值相同的元组对。结果中无null，不需要使用修饰词限制连接属性

• 内连接：inner join只显示两表中有关联的数据，就是当两个表没有相同主键时，不会显示结果，也就是返回两个表的交集部分用using或on关联字段，using会显示一个join的列，on会显示两个join的列
  

• 左外连接：左表的记录会全部显示，右表只显示符合连接条件的数据

• 右外连接：右表的记录会全部显示，左表只显示符合连接条件的数据

• 全外连接：union左外连接和右外连接
  

• 交叉连接：两个表的数据交叉组合，与笛卡尔积相同

17. mysql都有什么锁，死锁判定原理和具体场景，死锁怎么解决？

• 按锁粒度分类
  • 行级锁：行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁。开销大，加锁慢，会出现死锁。发生锁冲突的概率最低，并发度也最高。
  • 表级锁：表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）
  • 页级锁：页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。
• 按锁级别分类
  • 共享锁：共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据SELECT … LOCK IN SHARE MODE;。当没有其他线程对查询结果集中的任意一行使用排他锁时，可以申请成功
  • 排他锁：排他锁又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。SELECT … FOR UPDATE;，当没有其他线程对查询结果集中的任何一行使用共享锁和排他锁时，可以成功申请排他锁，否则会被阻塞
  • 意向锁：其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型
    • 意向共享锁（IS）
    • 意向排他锁（IX）

18. varchar和char的使用场景？

• 两者都是字符串类型
• char
  • 定义的列长度为固定的，长度取值可以为0-255之间
  • 存储时，如果字符数没有达到定义的位数，会在后面用空格补全，再存入数据库中。比如定义 CHAR(10)，那么不论存储的数据是否达到了10个字符，都要占去10个字符的空间,不足的自动用空格填充
  • 数据库取CHAR值时，尾部的空格会被删除
• varchar
  • 定义的列的长度为可变长，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用 的字符集确定。整体最大长度是65,532字符）
  • 值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充
  • 数据库取VARCHAR值时，尾部的空格仍然保留

19. mysql 高并发环境解决方案？

• 分库
• 分表
• 读写分离
• 查询缓存

20. 数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）

• lsn：redo log序号
• redo log
• undo log

21 innodb更新数据两阶段提交（redo log 和binlog）

• 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。（在提交的时候一次性写入）
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

22 为什么innodb采用B+树，相比hash和B树

• 哈希虽然能够提供 O(1) 的单数据行操作性能，但是对于范围查询和排序却无法很好地支持，最终导致全表扫描；
• B 树能够在非叶节点中存储数据，但是这也导致在查询连续数据时可能会带来更多的随机 I/O，而 B+ 树的所有叶节点可以通过指针相互连接，能够减少顺序遍历时产生的额外随机 I/O；

23 OLTP和OLAP

• OLTP： online transaction processing 联机事务处理：传统的关系型数据库的主要应用，基本的日常事务处理，记录的增删改查
• OLAP：online analytical processing 联机分析处理：数据仓库系统的主要应用，支持复杂额分析操作，侧重决策支持，并且提供直观易懂的查询结果