MySQL学习笔记

1 数据库存储引擎MyISAM和InnoDB的区别

MyISAM

每一个MyISAM在磁盘上存储成3个文件，其文件名和表名相同，但扩展名指出文件类型，.frm为存储表定义，.MYD为数据文件的扩展名，.MYI为索引文件的扩展名。MyISAM表格可以被压缩，而且它们支持全文搜索（FULLTEXT）。不支持事务，而且也不支持外键。如果事务回滚将造成不完全回滚，不具有原子性。Update操作时，进行的是表锁，并发量小。如果执行大量的select，MyISAM是更好的选择。

MyISAM的索引和数据是分开存储的，并且索引是进行压缩的，内存的使用率就对应提高了不少，能加载更多的索引；而InnoDB是索引和数据紧密捆绑的，没有使用压缩，从而导致InnoDB比MyISAM体积庞大不少。 MyISAM缓存在内存的是索引，不是数据。而InnoDB缓存在内存的是数据。相对来说，服务器内存越大，InnoDB发挥的优势越大。

优点：查询速度快，适合大量的select，可以全文索引
缺点：不支持事务，不支持外键，update时为表锁，并发量小，不适合大量update。

InnoDB

这种类型是事务安全的，还支持外键。在update时进行的是行锁，并发量大。

优点：支持事务，支持外键，适合大量的update，并发量大。
缺点：查询慢，不适合大量的select；索引和数据的存储是捆绑的，且不压缩，内存的使用率较低

总结

• InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
• MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
• InnoDB支持外键，MyISAM不支持
• InnoDB不支持FULLTEXT类型的索引
• InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
• 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
• InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’）

2 事务的ACID特性

• A（atomicity）：原子性，指整个数据库事务是不可分割工作单位，只有使事务中所有的数据库操作都执行成功，才算整个事务成功。事务中任何一个SQL语句执行失败，已经执行成功的SQL语句必须撤回，数据库状态应该退回执行事务前的状态。
• C（consistency）：一致性，指事务将数据库从一种状态转变为下一种一致的状态，在事务开始之前和事务结束以后，数据库的完整性约束并没有被破坏。例如，表中有一个字段为姓名，为唯一约束，即在表中中姓名不能重复。如果一个事务对姓名字段进行了修改，但是在事务提交或事务操作发生回滚后，表的姓名变得非唯一了，这就破坏了事务的一致性要求，即事务将从一种状态变为一种不一致的状态，因此事务是一致性的单位，如果事务中的某个动作失败了，系统可以自动撤销事务—返回初始化状态。
• I（isolation）：隔离性，隔离性还有其他称呼，如并发控制(concurrency control)、可串行化(serializability)、锁(locking)等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能互相分离，即该事务提交对其他事务是不可见的，通常这使用锁来实现。当前数据库系统中提供了一种粒度锁(granular lock)的策略，允许事务锁住一个实体对象的自己，以此来提高事务之间的并发度。
• D（durability）：持久性，持久性事务一旦提交，其结果是永久性的，即发生宕机扥故障，数据库也能将数据恢复。需要注意的是，只能从事务本身的角度来保证结果永久性。例如，在事务提交后，所有的变化都是永久的。即使当数据库因为崩溃而需要恢复时，也能保证恢复后提交的数据都不会丢失。但若不是数据库本身发生故障，而是一些外部原因，如RAID卡损坏、自然灾害等原因问题导致数据库发生问题，那么所有提交的数据都有可能会丢失。因此持久性保证事务系统的高可靠性(High Reliability)，而不是高可用性(High Aavilability)。对于高可用性的实现，事务本身并不能保证，需要一些系统共同配合来完成。

3 索引的作用以及优缺点

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引和全文索引。

创建索引可以大大提高系统的性能（优点）：

（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
（2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
（4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
（5）通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面：（缺点）

（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在这些列上创建索引：

（1）在经常需要搜索的列上，可以加快搜索的速度；
（2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
（3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
（4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
（5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
（6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引：

（1）对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
（2）对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
（3）对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
（4）当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

4 主键索引和唯一性索引的区别

• 主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为空值 + 唯一索引了。
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
• 在 RBO 模式下，主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。

5 drop、delete、truncate的区别

• drop：直接删掉整个表
• truncate：删除表中的数据，但是保留了表，再插入时自增长id会从1开始
• delete：仅删除表中的部分数据，可以加where子句

6 数据库的事务

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

START TRANSATION;
    (一系列SQL 语句)
COMMIT;

一个有效的事务处理系统必须满足ACID特性

• 原子性（Atomicity）：一个事务必须被视为一个单独的内部“不可分”的工作单元，以确保整个事务要么全部执行，要么全部回滚。当一个事务具有原子性时，该事务绝对不会被部分执行，要么完全执行，要么根本不执行。
• 一致性（Consistency）数据库总是从一种一致性状态转换到另一种一致性状态。确保事务提交或者回滚前后数据保持一种一致性的状态。
• 隔离性（Isolation）：某个事务的结果只有在提交完之后才能被其他事务所看见。
• 持久性（Durability）：一旦事务被提交，事务对数据所做的修改就是永久性的，即使系统奔溃／夯机也不会丢失修改后的数据。

7 事务的四种隔离级别

SQL标准定义了4种类隔离级，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

• READ UNCOMMITTED：读取未提交内容，所有事务可以看到其他事务未提交的执行结果。这会导致脏读，即读取未提交数据。
• READ COMMITTED：读取提交内容，这符合ACID中的隔离的定义：一个事务在开始时，只能看见已经提交事务所做的改变，一个事务从开始到提交前，所做的任何数据改变都是不可见的，除非已经提交。但这会导致不可重复读，即用户运行用一语句两次，看到的结果不同。
• REPEATABLE READ：可重读，解决了READ UNCOMMITTED导致的脏读问题，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过会导致幻读的问题，即当用户读取某一范围的数据行时，另一事务又在这个范围内插入新行，当用户再读取该范围的数据行时，会发现有新的幻行。
• SERIALIZABLE：可串行化，是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，SERIALIZABLE是在每一个读的数据行上加锁，在这个级别，可能导致大量的超时现象和锁竞争现象。但是这个隔离级别可以保证数据的稳定性，且强制减少了并发量。

• 脏读：一个事务读取到了另外一个事务没有提交的数据；比如：事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取到了T1更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了；
• 不可重复读：在同一事务中，两次读取同一数据，得到内容不同；比如：事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化；

• 幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同；比如：事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样

    （1）Read Uncommitted（读取未提交内容）
    在该隔离级别中，所有事务都可以看到其他未提交事务的执行结果。该隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
    （2）Read Committed（读取提交内容）
    这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交的事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
    （3）Repeatable Read（可重复读）
    这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
    （4）Serializable（可串行化）
    这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
  
    这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：
    （1）脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
    （2）不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
    （3）幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。