mysql 面试常见知识点

一、MySQL Server 系统架构

            

二、MySql存储引擎

     多存储引擎是 MySQL 有别于其他数据库管理软件的最大特色，不同的存储引擎有不同的特点，可以应对不同的应用场景，这让我们在实际的应用中可以根据不同的应用特点来选择最有利的存储引擎，给了我们足够的灵活性。


1.MyISAM存储引擎
      MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。.frm文件(存放表的结构信息)、.MYD文件(存放表的数据)和 .MYI文件(存放索引数据)。

     MyISAM 支持以下三种类型的索引：
  1)B-Tree 索引
    B-Tree 索引，顾名思义，就是所有的索引节点都按照 balance tree 的数据结构来存储，所有的索引数据节点都在叶节点。
  2)R-Tree 索引
     R-Tree 索引的存储方式和 b-tree 索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以目前的 MySQL 版本来说，也仅支持 geometry 类型的字段作索引 。
  3)Full-text 索引
     Full-text 索引就是我们长说的全文索引，他的存储结构也是 b-tree。主要是为了解决在我们需要用 like 查询的低效问题。


2.Innodb 存储引擎
     Innodb 是 事务安全 的存储引擎，所以系统 Crash 对他来说并不能造成非常严重的损失，由于有 redo 日志的存在，有 checkpoint 机制的保护，Innodb 完全可以通过 redo 日志将数据库 Crash 时刻已经完成但还没有来得及将数据写入磁盘的事务恢复，也能够将所有部分完成并已经写入磁盘的未完成事务回滚并将数据还原。

     Innodb 存储引擎实现了 行级锁定，在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于 MyISAM 的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。


3.NDB Cluster 存储引擎
    NDB 存储引擎也叫 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，简单的说，Mysql Cluster 实际上就是在无共享存储设备的情况下实现的一种内存数据库 Cluster 环境，其主要是通过 NDB Cluster（简称 NDB）存储引擎来实现的。

三、MySQL 锁定机制

     数据库锁定机制简单来说就是数据库为了保证 数据的一致性 而使各种共享资源在被并发访问访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制，MySQL中每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。
    MySQL 各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定

行级锁定（row-level）
    行级锁定最大的特点就是锁定对象的颗粒度很小，由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。但是有可能发生死锁。

表级锁定（table-level）
    这是各存储引擎中最大颗粒度的锁定机制，该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。

页级锁定（page-level）
    页级锁定是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

1 ) MyISAM 存储引擎 使用的锁定机制是由 MySQL 提供的 表级锁定 实现，锁定的时候会将整个表锁定。
2 ) 行级锁定 不是 MySQL 自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的Innodb 存储引擎，以及 MySQL 的分布式存储引擎 NDB Cluster 等都是实现了行级锁定。

1、共享/排他锁

   共享锁（Share Locks），mysql中读取数据时会加 共享锁 (S锁)
        共享锁之间不互斥，不同客户端可以同时读数据，每有一个transaction对某记录执行读操作，都会给该记录上一个共享锁，共享锁可以叠加。只要该记录上有共享锁，其他transaction就无法对该记录执行写操作，而只能对该操作执行读操作。
        每个transaction对某个记录加的共享锁只在读取过程中有效，读取结束后便会立即释放。transaction B可以在transaction A的读取过程中执行读操作，transaction B对这个记录的写操作会一直等待直到A中的 读取过程 结束，而不需要整个transaction A的结束。

   排他锁（Exclusive Locks），mysql中修改数据时加 排他锁 (X锁)
        排他锁与任何锁互斥，当一个transaction 执行了写操作，并未提交的时候，其他transaction 的读和写操作都会被阻塞。

   共享锁和排他锁是行锁，锁定行记录，多个事务操作同一行数据时，后来的事务处于阻塞等待状态。这样可以避免了数据一致性的问题。并且后来的事务可以操作其他行数据，解决了表锁高并发性能低的问题。

[注]
InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁！

1. 在表有索引的情况下：
        transaction A对表中某条记录的读操作，会给该记录上S锁，transaction B在A读取的过程中是无法对该记录进行写操作的，但是transaction B可以对该表中的其他行记录进行写操作。
        transaction A对表中某条记录的写操作，会给该记录上X锁，transaction B在A写数据并提交之前是无法对该记录进行写操作的，但是transaction B可以对该表中的其他行记录进行读和写操作。
        这种情况下，是对行记录进行锁定，虽然之前的行被锁定了，但是其他事务可以操作其他行。

2. 在表无索引的情况下：
   这种情况下，行锁会升级为表锁
        transaction A对表中某条记录的读操作，会给该记录上S锁，但是这个S锁会升级为表级别，transaction B在A读取的过程中是无法对该表中的任何记录进行写操作的。
        transaction A对表中某条记录的写操作，会给整个表上X锁，这个X锁会升级为表级别，transaction B在A写数据并提交之前是无法对表中任何记录进行写操作的。
        这种情况下，是对整个表进行锁定。

2、意向锁

   意向锁是一个表级锁，意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。意向锁分为共享意向锁 (IS锁) 和排他意向锁 (IX锁) 。

   意向锁的作用是：通知数据库接下来需要施加什么锁并对表加锁，意向锁是系统自己完成的。

   IS锁表明：事务有意向对表中的某些行加共享锁
            如果需要对记录A加共享锁，那么此时innodb会先找到这张表，对该表加意向共享锁之后，再对记录A添加共享锁
   IX锁表明：事务有意向对表中的某些行加排他锁
            如果需要对记录A加排他锁，那么此时innodb会先找到这张表，对该表加意向排他锁之后，再对记录A添加排他锁

意向共享锁和意向排他锁锁定的是整个表，是系统自动添加和自动释放的，不是人为操作。

      如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。

3、间隙锁

 select * from lock_example
 where id between 8 and 18 
 for update;

这条SQL语句会封锁区间(8,18)，以阻止其他事务插入id位于该区间的记录。
    间隙锁的目的，为了防止其他事务在间隔中插入数据，以导致“不可重复读”。如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

四、数据一致性问题

数据库事务的 ACID特性 ：
    ACID分别指：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。
     原子性： 事务中的所有操作，要么全部完成、要么全部不完成，不可能停滞在中间某个环节，如果一个事务在执行过程中发生错误，要回滚到事务开始前的状态，就像这个事务从来没有执行一样。
     一致性： 事务必须始终保证系统处于一致的状态，和原子性密切相关，事务执行成功，说明全部都执行了，那么就使数据库从一个一致性状态变到另一个一致性状了。
     隔离性： 一个事务的执行不能被其他事务干扰
     持久性： 事务一旦提交成功(没有回滚)，那么数据库里的数据就会永久性的改变。


1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从数字分数97、76改为字母分数ABC，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表.
　


　InnoDB有4种事务隔离级别：

修改MySQL事务隔离级别的方法：set session transaction isolation level + 隔离级别

读未提交
     设置当前事务模式为read uncommitted。有一行数据，事务A读取了数据，但未提交，这时，事务B更新了数据，这时事务A马上就能看到数据变化，但是这时A读到的更新后的数据不一定正确，因为事务B有可能回滚，一旦事务B回滚，A读到的数据就作废了，这种情况事务A就可能发生 脏读。

读已提交
     设置当前事务模式为read committed。有一行数据，事务A读取了数据，但未提交，这时，事务B更新了数据，但是未提交。这种情况下，事务A不能马上读取到事务B的更改。这就解决了 “读未提交” 中的数据脏读问题。但是又有新的问题出现，事务B提交之后，事务A可以读取到变化后的数据，但是和A之前读的那一次的数据就不同了，这是一个新的问题------不可重复读。

可重复读
     设置当前事务模式为repeatable read，有一行数据score=100，事务A读取了数据，但未提交，这时事务B更新了数据score+50，并且提交了，这时事务A再去读取数据，会发现数据并没有改变score=100，和他之前查询的结果一样，这解决了 “不可重复读” 问题，却产生了 “幻读” 问题，因为这时候如果事务A执行score+50的话，再查询，得到的是score=200，200是在B修改后的基础上得来的。等于说虽然事务A两次读取到的数据相同，但是其实是幻觉，真正的数据悄悄改变了。

串行化
     串行化格式下只能进行读-读并发。只要有一个事务操作一条记录的写，那么其他要访问这条记录的事务都得等着。
串行化这种级别会将表锁定，这种隔离级别并发性极低。

总结：

mysql中默认事务隔离级别是 读已提交

1、事务隔离级别为读提交时，写数据只会锁住相应的行

2、事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间隙锁、行锁、下一键锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表。

3、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。