MySQL针对不同应用场景可以选择不同引擎，采用处理与存储分离的架构。
由于其自身架构的特点，每种引擎都不一样
逻辑架构：
连接处理、授权、认证安全/查询分析、优化、缓存、内置函数/引擎

1、InnoDB，默认，使用广泛的，事务型引擎；采用MVCC来支持高并发；支持真正的热备；基于聚簇索引建立；实现四个标准隔离级别；
2、MyISAM，5.1之前是默认引擎
全文检索、压缩、空间函数GIS等
不支持事务；表级锁，不支持行级锁；由于只将数据写到内存中，然后等待操所系统将数据刷出内存到磁盘，因此崩溃后无法安全恢复。
3、Archive，行级锁，只支持Insert和SELECT操作
4、Memory，不支持BLOB或TEXT类型列，表级锁，并发插入性能较低
5、NDB集群引擎
分布式的，share-nothing，容灾的，高可用的，NDB数据库组合（Mysql Cluster）
6、Blackhole
并没有实现任何的存储机制，只记Blackhole表的日志
第三方存储引擎
面向列的存储引擎，Infobright，为数据分析和数据仓库应用设计
选择存储引擎需要考虑的方面：
事务；备份；崩溃恢复；特有的特性；

文件
参数文件：告诉mysql实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数；
日志文件：用来记录MySQL实例对某种条件作出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等；
socket文件：当用UNIX域套接字方式进行连接时需要的文件；
pid文件：MySQL实例的进程ID文件；
MySQL表结构文件：用来存放MySQL表结构定义文件(.frm,v_a.frm：视图定义)；
存储引擎文件：MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据
InnoDB引擎相关文件，包括表空间文件和重做日志文件。表空间文件是用来管理InnoDB存储引擎的存储，分为共享表空间和独立表空间。重做日志非常重要，用来记录InnoDB存储引擎的事务日志，也因为重做日志的存在，才使得InnoDB存储引擎可以提供可靠的事务。

慢查询日志：使用命令mysqldumpslow slow.log,5.1开始可以将慢查询日志记录放入一张表中slow_log

表
在InnoDB存储引擎中，表都是根据主键顺序组织存放的，索引组织表
InnoDB逻辑存储结构
所有数据逻辑的存放在 表空间中（tablespace），表空间由段（segment）、区（extent）、页（page）组成

页是InnoDB磁盘管理的最小单位
分区表
分区功能并不是在存储引擎层完成的，InnoDB、MyISAM、NDB等都支持。分区过程是将一个表或索引分解为多个更小、更可管理的部分。逻辑上一个，物理上可能由多个物理分区组成。
分区主要用于数据库高可用性的管理，某些情况下可能造成io次数增多，性能反而急剧下降

事务
什么是？事务可由一条非常简单的SQL语句组成，也可以有一组复杂的SQL语句组成；是访问并更新数据库中各种数据项的一个程序执行单元。
特性
A（Atomicity），原子性
一个事务可以有很多操作，整个数据库事务操作时不可分割的工作单位。只有使事务中所有的操作都执行成功，才算整个事务成功。
C（consistency），一致性
指在事务开始前和结束后，数据库完整性约束没有被破坏。如果某个动作失败了，可以自动撤销之前的操作，返回初始的状态。
I（isolation），隔离性
事务提交前，对其他事务不可见。通常使用锁机制实现。并发控制方面 = 由锁来实现
D（durability），持久性
一旦提交，结果就是永久的

【redo log（重做日志），用来保证事务的原子性和持久性；undo log，用来保证一致性】
事务控制语句：
START TRANSCATION|BEGIN:显示的开启一个事务
COMMIT:提交事务
ROLLBACK：回滚事务
SAVEPOINT identifier：在事务中创建一个保存点，一个事务可以有多个保存点
RELEASE SAVEPOINT identifier：删除一个保存点
ROLLBACK TO[SAVEPOINT] identifier：回滚到标记点
SET TRANSACTION：设置事务的隔离级别

READ UNCOMMITTED（未提交读）、READ COMMITTED（提交读）、REPEATABLE READ（可重复的，解决了脏读）、SERIALIZABLE（可串行化，避免了幻读）
幻读：某个事务读取某个范围的数据，但是另一个事务，又向这个范围插入数据，导致的多次读取的时候，数据不一致

锁
什么是？
锁机制用于管理对共享资源的并发访问，实现事务的隔离性要求
涉及到多用户、高并发的应用时，最大的一个难点是：一方面需要最大程度的提高并发性能；另一方面要确保结果的正确性、一致性
在数据库中，锁是数据库系统区别于文件系统的一个关键特性。

锁的粒度
表级锁、页锁、行锁，因为不同数据库产品、甚至Mysql中不同引擎锁也是不同的，下面针对InnoDB说的：
InnoDB使用的是行级锁，实现了两种标准的行级锁：
共享锁（S lock），允许事务读取一行；排它锁（X lock）允许事务删除或更新一行；另外还有多行的意向锁
支持 一致性非锁定读（默认，通过多版本并发控制MVCC实现），一致性锁定读（SELECT...FOR UPDATE；SELECT...LOCK IN SHARE MODE）

锁问题：
脏读（脏页读取是正常的；事务隔离性控制，锁机制）、不可重复读（读到已提交的数据，违反一致性）、丢失更新（排他锁）
阻塞：加锁导致，InnoDB默认情况下不会回滚超时引发的错误异常
死锁：多个事务因争夺锁资源造成相互等待，解决死锁：1、解决死锁最简单的一种方法就是超时，即当两个事务相互等待，当一个等待时间超过设置阈值时，让其中一个事务进行回滚；这种被动等待超时，没有选择性；2、wait-for graph(等待图)的方式来进行死锁检测，这是一种主动的死锁检测方式，要求数据库保存锁的信息链表、事务等待链表，通过信息链和等待链可以构造出一张图。在这个图中若存在回路，就代表存在死锁。这种方式可以选择undo量最小的事务回滚
锁升级：粗化锁，将当前锁的粒度降低。保护系统资源，防止系统使用太多的内存资源来维护锁，在一定程度上提高了效率

死锁：
死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。
产生死锁的必要条件：
1、互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
2、请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己获得的其它资源保持不放。
3、不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
4、环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。
例子：

1. -- A事务先更新table1表，然后延时30秒，再更新table2表；  

2. begin tran  

3. update table1 set A='aa' where B='b2';  

4. --这将在 Table1 中生成排他行锁，直到事务完成后才会释放该锁。  

5. waitfor delay '00:00:30';  

6. --进入延时  

7. update table2 set D='d5' where E='e1' ;  

8. commit tran  

9. -- B事务先更新table2表，然后延时10秒，再更新table1表；  

10. begin tran  

11. update table2 set D='d5' where E='e1';  

12. --这将在 Table2 中生成排他行锁，直到事务完成后才会释放该锁  

13. waitfor delay '00:00:10'  

14. --进入延时  

15. update table1 set A='aa' where B='b2' ;  

16. commit tran

处理死锁的方式：
1、预防死锁
方法是通过设置某些限制条件，去破坏产生死锁的四个必要条件中的一个或者几个，来预防发生死锁。
2、避免死锁
在资源的动态分配过程中，用某种方法去防止系统进入不安全状态，从而避免发生死锁
3、检测死锁
此方法允许系统在运行过程中发生死锁。但可通过系统所设置的检测机构，及时地检测出死锁的发生，并精确地确定与死锁有关的进程和资源，然后采取适当措施，从系统中将已发生的死锁清除掉
4、解除死锁
这是与检测死锁相配套的一种措施。当检测到系统中已发生死锁时，须将进程从死锁状态中解脱出来。常用的实施方法是撤销或挂起一些进程，以便回收一些资源，再将这些资源分配给已处于阻塞状态的进程，使之转为就绪状态，以继续运行。死锁的检测和解除措施，有可能使系统获得较好的资源利用率和吞吐量，但在实现上难度也最大。

如何避免死锁：

1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；

2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；

3 所有的SP都要有错误处理（通过@error）

4 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁

5 优化程序，检查并避免死锁现象出现；

1）合理安排表访问顺序

2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。

3）采用脏读技术。脏读由于不对被访问的表加锁，而避免了锁冲突。在客户机/服务器应用环境中，有些事务往往不允许读脏数据，但在特定的条件下，我们可以用脏读。

4）数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个），防止资源过多占用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外，避免在前台交易高峰时间运行后台任务

5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。

6）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。

7）使用Bound Connections。Bound connections 允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。

8）考虑使用乐观锁定或使事务首先获得一个独占锁定。 

MVCC（多版本并发控制，Multi Version Concurrency Control）
是一种并发控制方法，实现对数据库的并发访问，在编程语言中实现事务内存。
简单的控制并发方式就是直接加锁，让所有读者等待写入者工作完成，这样效率很低。
MVCC使用了一种不同的手段，每个连接到数据库的读者，在某个瞬间看到的是数据库的一个快照。写入完成之前的数据对于其他的读操作时不可见的。

索引

索引是什么？用来干什么的

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。

索引类型

根据数据库的功能，可以在数据库设计器中创建四种索引：普通索引、唯一索引、主键索引和聚集索引。

普通索引

最基本的索引类型，没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表)；[3] 

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) )；

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在 employee 表中职员的姓 (lname) 上创建了唯一索引，则任何两个员工都不能同姓。

对某个列建立UNIQUE索引后，插入新纪录时，数据库管理系统会自动检查新纪录在该列上是否取了重复值，在CREATE TABLE 命令中的UNIQE约束将隐式创建UNIQUE索引。

创建唯一索引的几种方式：

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表); ；

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；

主键索引

简称为主索引，数据库表中一列或列组合（字段）的值唯一标识表中的每一行。该列称为表的主键。

（主键是一种约束，唯一索引是一种索引，两者在本质上不同；主键创建后一定包含一个唯一索引，唯一索引不一定是主键；唯一索引允许空值，主键不允许；主键不可）

在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

提示尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键索引。

候选索引

与主索引一样要求字段值的唯一性，并决定了处理记录的顺序。在数据库和自由表中，可以为每个表建立多个候选索引。

聚集索引

也称为聚簇索引，在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引， 即如果存在聚集索引，就不能再指定CLUSTERED 关键字。

索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引更适用于对很少对基表进行增删改操作的情况。

如果在表中创建了主键约束，SQL Server将自动为其产生唯一性约束。在创建主键约束时，指定了CLUSTERED关键字或干脆没有制定该关键字，SQL Sever将会自动为表生成唯一聚集索引。

非聚集索引

也叫非簇索引，在非聚集索引中，数据库表中记录的物理顺序与索引顺序可以不相同。一个表中只能有一个聚集索引，但表中的每一列都可以有自己的非聚集索引。如果在表中创建了主键约束，SQL Server将自动为其产生唯一性约束。在创建主键约束时，如果制定NONCLUSTERED关键字，则将为表产生唯一聚集索引。

在数据库系统中建立索引主要有以下作用：

优点：

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组（group by）和排序（order by）子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

支持：B+树索引、全文索引、哈希索引

二分查找法（折半查找）
演化过程：二叉查找树-->平衡二叉树-->B树-->B+树
概念：
二分查找法

int Search_bin(SSTable st,KeyType key){
//在有序表ST中折半查找其关键字等于key的数据元素。若找到，则函数值为该元素在表中的位置，否则为0
low = 1;high = st.length;
while(low<=high){
  mid = (low + high)/2;
  if(EQ(key,st.elem[mid].key)) return mid;
  else if(LT(key,st.elem[mid].key)) high = mid-1;
  else low = mid +1;
}
return 0;
}

二叉树（Binary Tree）：每个结点至多只有两颗子树（即二叉树中不存在度大于2的结点），并且，二叉树的子树有左右之分，其次序不能任意颠倒。
二叉查找树（二叉排序树，Binary Sort Tree）：或者是一颗空树；或者具有下列性质的二叉树：
（1）若它的左子树不空，则左子树上的所有结点的值均小于它根节点的值；（2）若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；（3）它的左、右子树也分别为二叉排序树；
平衡二叉树（Balanced Binary Tree，AVL树）：或者是一颗空树，或者具有下列性质的二叉树：
它的左右子树都是平衡二叉树，且左右子树的深度之差的绝对值不超过1；平衡因子BF只可能是-1、0和1.
平衡因子（Balance Factor）：左右子树深度之差

B树（就是B-树，B-tree）：是一种平衡的多路查找树，它在文件系统里很有用。
一颗m阶的B-树，或为空树，或满足：
（1）树中每个结点至多有m棵子树；
（2）若根结点不是叶子结点，则至少有两颗子树；
（3）除根结点之外的所有非终端结点至少有m/2（向上取整）棵子树；
（4）所有的非终端结点中包含下列信息数据（n,A0,K1,A2,...,Kn,An）
（5）所有叶子结点都出现在同一层次，并且不带信息（看做查找失败的点，实际上这些结点不存在，指向这些结点的指针为空）
B+树：是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。
所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。
所有的非终端结点可以看成是索引部分，结点中仅含有其子树（根结点）中最大（或最小）关键字

B+树索引分类：聚集索引（clustered index）和辅助索引（secondary index，有时也称非聚集索引）
其内部都是B+树的，即高度平衡，叶子结点存放着所有的数据。不同的是，叶子结点存放的是否是一整行的信息

辅助索引：叶子结点并不包含行记录的全部数据，除键值外，还包含一个书签（bookmark）告诉InnoDB引擎哪里可以找到数据。

索引组织表：

B+树索引的使用
对于访问表中很少有重复，或者 大量数据查询出一条或很少数据时使用B+树索引才有意义；对于像性别字段、地区等它们可取值范围很小；（Cardinality预估）
联合索引，多个索引列，内部存储结构，联合索引也是一颗B+树，不同的是联合索引的键值数量是多个。适用where a=xx and b=xx的查询；

强制使用索引：select * from table force index(列) where xxx=xx   索引提示：select * from table use index(列) where xx

Multi-Range Read优化
MySQL5.6版本开始支持。Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并将随机访问转化为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能的极大提升。适用于rang，ref，eq_ref类型的查询。
对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：
将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
将缓存中的键值根据RowID进行排序；
根据RowID的排序顺序来访问实际的数据文件；
Index Condition Pushdown（ICP）优化
Mysql5.6开始支持，之前的不支持情况下，当进行索引查询时，首先根据索引来查找记录，然后再根据Where条件来过滤记录。在支持Index Condition Pushdown后，MySQL数据会在取出索引的同时，判断是否可以进行where条件的过滤，也就是将where的部分过滤操作放在存储引擎层。在某些查询下，可以大大减少上层SQL层对记录的索取（fetch），从而提高数据库整体性能。

哈希算法
哈希表也称散列表，由直接寻址表改进而来。是一个键和值的映射表。
哈希函数是一个映像，任何关键字由此计算出的哈希函数值都落在表长允许的范围之内；不同关键字可能得到同一哈希地址（处理冲突）；
在一般情况下，哈希函数是一个压缩映像；
哈希函数构造方法：
1、直接定址法（线性函数，一对一的）；
2、数字分析法（数字矩阵，取关键字的若干位组成哈希地址）；
3、平方取中法（取关键字平方后的中间几位）；
4、折叠法（关键字位数很多，而且每一位上数字分布大致均匀时，将关键字分割成位数相同的几部分（最后一部分位数可以不同），然后取这几部分的叠加和）
5、除留余数法
6、随机数法
处理冲突的方法：
1、开放定址法（线性探测再散列）
2、再哈希法（计算另一个哈希函数）
3、链地址法（冲突关键字，存储在同一线性链表中），java中HashMap
4、建立公共溢出取（一旦冲突都填入溢出表）

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。
哈希索引只能用来搜索等值的查询；（范围查找不能使用哈希索引）
引擎自动控制的，只能通过innodb_adaptive_hash_index启用或关闭

全文检索
B+树索引支持：select * from blog where content like 'xxx%'; 对于'%xxx%'这样的内容搜索，即便添加了索引页需要进行扫描来得到结果；
InnoDB 1.2.x开始支持全文索引

倒排索引（其实就是，关键词是搜索内容，键值是文档号或位置）区别于一般的通过位置找内容，这是通过内容找位置
它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。两种表现形式：
inverted file index，{单词，单词所在的文档ID}
full inverted index，{单词，（单词所在文档ID，在具体文档中的位置）}
FTS Index Cache（全文检索索引缓存）提高全文检索性能

由于文档的DML操作实际并不删除索引中的数据，相反还会在对应的DELETED表中插入记录，因此随着应用程序的允许，索引会变的非常大，即使索引中的有些数据已经被删除，查询也不会选择这类记录。为此InnoDB存储引擎提供了一种方式，允许用户手工地将已经删除的记录从索引中彻底删除，该命令就是：OPTIMIZE TABLE。因为OPTIMIZE TABLE还会进行像Cardinality重新统计等的操作，可以通过：
SET GLOBAL innodb_optimize_fulltext_only=1;
optimize tablefts_a;
进行设置

MySQL数据库通过MATCH() ... AGAINST()语法支持全文检索查询
1、Natural Language
全文检索通过match函数查询，默认采用Natural Language模式
select * from fts_a where match(body) against('Proridge' in natural language mode);
2、Boolean
select * from fts_a where match(body) against ('+Please -hot' in boolean mode);
+ 表示该word必须存在
- 表示该word必须排除
3、Query Expansion
全文检索的扩展查询，这种查询通常在查询的关键词太短，用户需要implied knowledge（隐含知识）时进行
该查询分两段：
第一阶段：根据搜索词进行全文索引查询
第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询
select * from articles where match(title,body) against('database' with query expansion);

总结
优化系统性能：
应用级：
1、使用缓存，降低服务器压力；
2、避免重复查询；
3、读写分离；利用MySQL主从复制功能，分流查询到从库；
4、使用集群，MySQL cluster功能（NDB引擎）；
5、池化连接；
数据库：
1、server优化：
对mysql server根据具体需要，定制化参数。像内部缓存大小（表缓存）
2、SQL优化：
通过Explain分析低效率的SQL执行计划，针对性的修改；
慢查询日志分析，定位慢的sql；
show status 可以查看服务器状态信息；
建索引；分析索引使用情况；
对于全文索引，定期optimize table；（MyISAM、InnoDB表，支持全文索引）；
尽量避免表扫描，使用索引（order by的字段asc和desc同时使用；查询的关键字与order by的关键字不同；对不同关键字order by）；
如果可以，使用join操作，代替子查询（子查询会创建临时表）；
or条件使用索引，必须每个条件都索引；

干预锁；避免或减少等待；