1. 数据库事务
原子性：整个事务中的所有操作，要么全部成功，要么全部失败。
一致性：事务之前到事务之后，数据库的完整性约束没有被破坏。
隔离性：同一时间，只允许一个事务请求同一数据，不同事务之间彼此没有任何干扰。
持久性：在事务提交之后，该事务对数据库的操作持久的保存在数据库之中，不能被回滚。
2. 事务的隔离级别
隔离级别
脏读：是指事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。
不可重复读：是指在数据库访问时，一个事务范围内的两次相同查询却返回了不同数据。在这个事务还没有结束时，另一个事务修改了该数据，导致第一个事务两次读到的数据不一样。
幻读：是指当事务不是独立执行时发生的一种现象，比如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么就会发生，操作第一个事务的用户发现表中还有没有修改的数据行，就好像发生了幻觉一样。
3. 数据库的三大范式
第一范式（1NF）
数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。
第二范式（2NF）
满足1NF后要求表中的所有列，都必需依赖于主键
第三范式（3NF）
满足2NF后，要求表中的每一列都要与主键直接相关，而不是间接相关。
4. MySQL存储引擎
4.1 MyISAM
MyISAM存储引擎不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有要求或者以select，insert为主的应用基本上可以用该引擎来创建表。
支持3种不同的存储格式：
静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格，在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略）
动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE。
压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支。
4.2 InnoDB
InnoDB是事务型数据库的首选引擎，支持事务（ACID），支持行锁定和外键，InnoDB是默认的MySQL引擎。
InnoDB主要特性有：
InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务存储引擎。
InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其它基于磁盘的关系型数据库引擎锁不能匹敌的。
InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池，InnoDB将它的表和索引在一个逻辑表空间中。
InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键。
4.3 MEMORY
MEMORY存储引擎数据存储在内存中，每个memory表只实际对应一个磁盘文件，格式是.frm，memory类型的表访问非常的快，默认使用HASH索引。
MEMORY存储引擎的表可以选择使用B-Tree索引或者Hash索引，两种不同类型的索引有其不同的使用范围：
Hash索引优点： Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到叶节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
Hash索引缺点：因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；
4.4 MEGER
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。
5. 数据库锁
MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。MySQL各存储引擎使用了三种类型（级别）的锁机制：表级锁，行级锁和页级锁。
5.1 表级锁
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并发大打折扣。
使用表级锁定的主要存储引擎：MyISAM，MEMORY等一些非事务性存储引擎。
MySQL表级锁模式：表共享读锁（Table Read Lock）和表独占写锁（TableWrite Lock）。
锁模式的兼容性：
对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
对MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
表级锁使用：
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。
5.2 行级锁
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力。虽然能够在并发处理能力上面有较大的优势，由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
使用行级锁定的主要存储引擎：InnoDB存储引擎。
InnoDB的行级锁类型：共享锁和排他锁。而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。
InnoDB锁兼容性：
兼容性
InnoDB锁使用：
意向锁是InnoDB自动加的，不需用户干预。事务可以通过以下语句显示给记录集加共享锁或排他锁。
共享锁(S)：SELECT *FROM table WHERE ...LOCK IN SHARE MODE
排他锁(X)：SELECT *FROM table WHERE ...FOR UPDATE
InnoDB锁的实现：InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁。
6. SQL优化
6.1 选取最适用的数据类型
数据库中的表越小，执行的查询也就会越快。因此，在创建表的时候，表中字段的宽度设得尽可能小；应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
6.2 使用连接代替子查询
连接之所以效率更高，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
6.3 使用联合代替手动创建临时表
查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。注意的是所有select语句中的字段数目要相同。
6.4 使用索引
索引是提高数据库性能的常用方法，尤其是在查询语句当中包含有MAX(),MIN()和ORDER BY这些命令的时候，性能提高更为明显。一般说来，索引应建立在那些将用于JOIN,WHERE和ORDER BY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。
6.5 索引失效的情况
隐式转换导致索引失效。
对索引列进行运算导致索引失效，运算包括(+，-，*，/，! 等)。
使用内部函数导致索引失效，对于这样情况应当创建基于函数的索引。
以下使用会使索引失效，应避免使用；
使用 <> 、not in 、not exist、!=
like "%_" 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
单独引用复合索引里非第一位置的索引列
不要将空的变量值直接与比较运算符比较，应使用 IS NULL 或 IS NOT NULL 进行比较。
7. B+/-树原理
7.1 B-Tree是一种多路搜索树
1.定义任意非叶子结点最多只有M个子节点；且M>2；
2.根节点的子结点数为[2,M]；
3.除根节点以外的非叶子节点的子结点数为[M/2, M]；
4.每个结点存放至少M/2-1（向上取整）和至多M-1个关键字；
5.非叶子结点的关键字个数=指向儿子的指针个数-1；
6.非叶子结点的关键字：K[1],K[2], …, K[M-1]；且K[i] < K[i+1]；
7.非叶子结点的指针：P[1],P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8.所有叶子结点位于同一层；
7.2 B-树的特性
1.关键字集合分布在整棵树中；
2.任何一个关键字出现且只出现在一个结点中；
3.搜索有可能在非叶子结点结束；
4.其搜索性能等价于在关键字全集内做一次二分查找；
5.自动层次控制；
7.3 B+Tree：B+树是B-树的变体，也是一种多路搜索树
1.非叶子结点的子树指针与关键字个数相同；
2.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
3.为所有叶子结点增加一个链指针；
4.所有关键字都在叶子结点出现；
7.4 B+树的特性
1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2.不可能在非叶子结点命中；
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4.更适合文件索引系统；
8. MySQL中的索引
MySQL中普遍使用B+Tree做索引，但在实现上又根据聚簇索引和非聚簇索引而不同。
8.1 聚簇索引
指主索引文件和数据文件为同一份文件，聚簇索引主要用在Innodb存储引擎中。在该索引实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键。如果是辅助索引，data指向对应的主索引。
在B+Tree的每个叶子结点增加一个指向相邻叶子结点的指针，就形成了带有顺序访问指针的B+Tree。目的是为了提高区间访问的性能，例如查询key为从18到49的所有数据记录，当找到18后，只需顺着结点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
8.2 非聚簇索
非聚簇索引就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主索引和辅助索引没区别，只是主索引中的key一定是唯一的。
8.3 聚簇索引的优势
主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处，减少了当出现行移动或者数据页分裂时辅助索引的维护工作。
8.4 聚簇索引的劣势
维护索引很昂贵，特别是插入新行或者主键被更新导致要分页(page split)的时候。
如果主键比较大，那么辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值。
如果表使用随机ID作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢。
网友评论