MySQL数据库(review中)

• 数据库：物理操作系统文件或其他形式文件的集合。
• 数据库实例：MySQL数据库由后台线程以及一个共享内存区组成。数据库实例才是真正用于操作数据库文件的。

     当启动数据库实例时，MySQL数据库会去读取配置文件，根据配置文件的参数来启动数据库实例。若无配置文件，MySQL会按照编译时的默认参数设置来启动实例。多个配置文件以读取到的最后一个配置文件中的参数为准。

一.MySQL体系架构

     如图，MySQL由连接池组件、管理服务和工具组件、SQL接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件组成。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必须的。存储引擎是基于表的，而不是数据库。

1.MySQL存储引擎

     用户可以根据MySQL预定义的存储引擎接口编写自己的存储引擎。
InnoDB引擎和MyISAM引擎的区别

InnoDB	MyISAM
支持行级锁(通过MVCC来提高高并发性)	仅支持表级锁(影响并发性能)
支持事务	不支持事务
聚簇索引结构，5.6版本支持全文索引	支持全文索引
支持崩溃自动恢复	崩溃后无法自动恢复，只能修复，耗时且存在数据丢失
\	MyISAM压缩表，只读，减少磁盘I/O，提高了查询性能，为行记录独立压缩

InnoDB存储引擎
     InnoDB存储引擎支持事务，其特点是支持行级锁、支持外键、支持非锁定读，即默认读取操作不会产生锁。InnoDB通过使用多版本并发控制(MVCC)来获得高并发性，并且实现了SQL标准的4种隔离级别，默认为Repeatable级别。同时，使用next-key-locking策略来避免幻读现象。
     除此之外，InnoDB还提供了插入缓冲、二次写、自适应哈希索引、预读等高性能和高可用的功能。
     InnoDB对于其表空间中数据的存储，采用聚簇索引的方式，每张表的存储都是按主键的顺序进行存放。若无主键，则默认为每行生成一个6字节RowID。

MyISAM存储引擎
     MyISAM存储引擎不支持事务、仅支持表级锁、支持全文索引，其缓冲池只缓冲索引文件，而不缓冲数据文件(数据文件的缓冲由系统自身完成)。
     MyISAM存储引擎表由MYD和MYI组成，MYD用于存放数据文件，MYI用于存放索引文件。可以通过使用myisampack工具来压缩数据，压缩后表是只读的，也可通过myisampack工具来解压数据文件。

Memory存储引擎
     Memory存储引擎将表中的数据存放在内存中，如果数据库重启或崩溃，表中的数据都将消失，其默认使用哈希索引，仅支持表锁，不支持TEXT和BLOB列类型，且存储变长字段(vachar)时是按照定长字段(char)方式进行的。
     MySQL数据库使用Memory存储引擎作为临时表来存放查询的中间结果集。若中间结果集大于引擎表容量设定值或者中间结果含有TEXT或BLOB列字段类型，则MySQL数据库会把其转换为MyISAM存储引擎表而存放在磁盘中，但MyISAM不缓存数据文件，因此这时产生的临时表的性能对于查询会有损失。

2.连接MySQL

     连接MySQL的操作是一个连接进程与MySQL数据库实例j进行通信，本质上是进程通信。常用的进程通信的方式有管道、命名管道、命名字、TCP/IP套接字、UNIX域套接字。

TCP/IP
     TCP/IP套接字方式是MySQL数据在任何平台下都提供的连接方式，是在TCP/IP连接上建立一个基于网络的连接请求。在通过TCP/IP连接到MySQL实例时，MySQL数据库会先检查一张权限表(user)，用于判断发起请求的客户端IP是否允许连接到MySQL实例。

二.InnoDB存储引擎

1.InnoDB体系架构

InnoDB存储引擎体系架构

InnoDB存储引擎有多个内存块，共同组成了一个大的内存池，负责如下工作：

• 维护所有进程/线程需要访问的多个内部数据结构；
• 缓存磁盘上的数据，方便快速读取，同时在对磁盘文件的数据修改之前在这里缓存；
• 重做日志(redo log)缓冲；

后台线程的主要作用是负责刷新内存池中的数据，保证其是最近的数据，此外将已修改的数据文件刷新到磁盘文件中，同时保证在数据库发生异常时InnoDB能够恢复到正常运行状态。

后台线程

InnoDB存储引擎是多线程的模型，其后台有多个不同的线程，负责处理不同的任务。

• Master Thread
  主要负责将缓冲池中的数据异步刷新到磁盘，保证数据一致性，包括脏页的刷新、合并插入缓存、UNDO页的回收等。
• IO Thread
  InnoDB存储引擎大量使用了AIO来处理写IO请求，而IO Thread主要负责这些IO请求的回调处理。
• Purge Thread
       事务提交后，其所使用的undolog可能不再需要，需要Purge Thread回收已经使用并分配的undo页。(在InnoDB 1.1之前，purge操作仅在Master Thread中完成。而从InnoDB 1.1之后，purge操作可以独立到单独的线程中进行，以此来减轻Master Thread的工作，从而提高CPU的使用率以及提升存储引擎的性能)。
• Page Cleaner Thread
  Page Cleaner Thread是在InnoDB 1.2.x版本中引入的，其作用是将之前版本中脏页的刷新操作都放入到单独的线程中来完成。

内存

• 缓冲池
       InnoDB存储引擎是基于磁盘存储的，并将其中的记录按页的方式进行管理，因此可将其视为基于磁盘的数据库系统。由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统常使用缓冲池技术来提高数据库整体性能。

InnoDB内存数据对象

     缓冲池通过内存的速度来弥补磁盘速度较慢对数据库性能的影响：

①读操作，将从磁盘读到的页存放在缓冲池中，下一次再读相同页时，首先判断该页是否在缓冲池中，若在，称该页在缓冲池中被命中，直接读取该页，否则，读取磁盘上的页。
②写操作，首先修改缓冲池中的页，然后再以一定的频率刷新到磁盘上。不过页刷新回磁盘的操作并不是每次页发生更新时触发的，而是通过一种成为CheckPoint的机制刷新回磁盘。

     从InnoDB 1.0.x版本开始，允许有多个缓冲池实例 (innodb_buffer_pool_instance， 默认值为1)。每个页根据哈希值平均分配到不同的缓冲池实例中，这样减少了数据库内部资源的竞争，增加数据库的并发处理能力。

• LRU List
       在InnoDB存储引擎中，缓冲池中页的大小默认为16KB，通过LRU(Latest Recent Used，最近最少使用)算法来进行管理。即最频繁使用的页在LRU列表的前端，而最少使用的页在LRU列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放列表中尾端的页。
       InnoDB存储引擎在传统的LRU算法上做了优化，在LRU列表中加入了midpoint参数，新读取到的页，不直接放入到LRU列表的首部，而是放到midpoint位置(默认列表的5/8处)。通过innodb_old_blocks_time参数，表示页读取到mid位置后需要等待多久才会被加入到LRU列表的热端。

• Free List
       当数据库刚启动时，LRU列表为空，这时页都存放在Free List中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。

• Flush List(脏页列表)
       在LRU列表中的页被修改后，该页称为脏页(dirty page)，即缓冲池中的页和磁盘上的页的数据产生了不一致。这是数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。脏页即存在于LRU列表中，也存在于Flush列表中。LRU列表用于管理缓冲池中页的可用性，Flush列表用来管理页刷新回磁盘，两者互不影响。

• 重做日志缓冲(redo log buffer)
       InnoDB存储引擎首先将重做日志信息放入到此缓冲区，然后以一定频率将其刷新到日志文件。其大小由参数innodb_log_buffer_size控制，默认为8Mb。在下列三种情况下，会将重做日志文件缓冲中的内容刷新到外部磁盘的重做日志文件：
  ①Master Thread每一秒将重做日志缓冲刷新到日志文件；
  ②每个事务提交时会将重做日志缓冲刷新到日志文件；
  ③当重做日志缓冲池剩余空间小于1/2时，将重做日志缓冲刷新到日志文件。

• 额外的内存池
       在InnoDB存储引擎中，对内存的管理时通过内存堆的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当此区域内存不够时，会向缓冲池申请，如：帧缓充、缓冲控制对象(记录了LRU、锁、等待等信息)。

2.CheckPoint技术

     若每次页发生变化，就将新页刷新到磁盘，开销很大，或者若在将新页刷新到磁盘时发生了宕机，那么数据就无法恢复了。因此当前事务数据库系统采用Write Ahead log策略，即当事务提交时，先写重做日志，再修改页。但是当数据库运行足够久时，就算有重做日志，宕机后数据库的恢复时间也会很久，因此CheckPoint技术的目的是解决一下几个问题：

• 缩短数据库的恢复时间：当数据库发生宕机时，不需要重做所有的日志，CheckPoint之前的页都已经刷新回磁盘了；
• 缓冲池不够用时，将脏页刷新到磁盘：此时根据LRU算法会溢出最近最少使用的页，若此页为脏页，需强制执行CheckPoint，将脏页(新页)刷新到磁盘；
• 重做日志不可用时，刷新脏页：当前重做日志时循环使用的，可重用的重做日志部分是指这部分已经不需要，即发生宕机时，恢复数据库不需要这部分重做日志，因此可以覆盖重用。而若此时重做日志还需要使用，必须强制产生CheckPoint，将缓冲池中的页至少刷新到当前重做日志的位置。

并发控制

1)读写锁
      在处理并发读或者写时，可以通过实现一个由两种类型组成的锁系统来解决问题，这两种类型的锁通常被成为共享锁和排他锁，也叫读锁和写锁。
     读锁是共享的，相互不阻塞；写锁是排他的，写锁会阻塞其他写锁和读锁
2)锁粒度
      只锁定会修改的数据片，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。但加锁也需要消耗资源，包括获得锁、检查锁是否解除、释放锁等都会增加系统的开销。
     a.表锁，开销最小，粒度最大，锁定整张表，对该表的写操作会阻塞其他用户的读写操作(MyISAM)
      b.行锁，粒度最小，最大程度支持并发(InnoDB)
      隐式和显式锁定：InnoDB会根据隔离级别在需要的时候自动加锁，在事务执行过程中，随时都可以执行锁定，锁只有在执行Commit/RollBack 的时候才会释放。在SQL后加入Lock table、Unlock table来对表进行显式锁定。
3)多版本并发控制(MVCC)
      通过保存数据在某个时间的快照来实现，典型的有乐观和悲观并发控制，只在可重复读和提交读两个隔离级别下工作。InnoDB的MVCC是通过在每行记录后门保存两个隐藏列来实现，一列保存行创建时间，一列保存行过期时间(删除时间)。并通过间隙锁策略防止幻读，间隙锁使得InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定。

2.事务

       一组原子性的SQL查询或者一个单独的工作单元。事务内的语句，要么全部执行，要么全部执行失败。
(1)事务属性(ACID)
       A-atomicity 原子性，一个事务必须被视为一个不可分割的最小工作单元，事务的操作要么全部提交成功，要么全部失败并回滚；
       C-consistency 一致性，数据库总是从一个一致性状态转换到另一个一致性状态；
       I-isolation 隔离性，事务所做的修改在最终提交之前对其他事务通常是不可见的；
       D-durability 持久性，事务一经提交，所修改的数据将会永久保存在数据库中。
(2)事务隔离级别
       1)未提交读(脏读)：事务中的修改，即使没有提交，对其他事务也都是可见的。若事务A已经修改数据，但未提交，而事务B读取了修改后的数据，这时事务A回滚，则发生脏读，事务B读取的数据无效；
       2)提交读(不可重复读)：事务从开始到提交，所做的修改对其他事务都是不可见。但存在不可重复读，即同一事务中两次同样的查询结果不一致；
       3)可重复读(幻读)：MySQL默认事务隔离级别，保证在同一个事务中多次读取同样记录的结果是一致的。但未解决幻读，即当事务A在读取某个范围内的记录时，事务B又在该范围插入新的记录，事务A再次读取该范围的记录时，会产生幻读。
       4)可串行化：最高隔离级别，强制事务串行执行
(3)事务日志
       在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加方式，因此写日志的操作是磁盘上一小块区域的顺序I/O，而不像随机I/O需要在磁盘多处移动磁头。

4.优化的数据类型

1)更小的通常更好，尽量使用可以正确存储数据的最小数据类型；
2)简单就好，简单数据类型操作通常需要更少的CPU周期；
3)尽量避免NULL

5.索引

索引对多个值进行排序的依据是定义索引时列的顺序；

1. B-Tree索引，按照顺序存储数据，适用于全值匹配、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀的查找；

2. 哈希索引，只有精确匹配索引所有列的查询才有效。缺陷：无法排序，不支持部分索引列匹配查询，只支持等值比较查询；

3. 空间数据索引（R-Tree），无需前缀查询，从所有维度来索引数据；

4. 全文索引，查找文本中的关键词，可在相同列上同时创建全文索引和B-Tree索引；

高效索引策略：

1. 独立的列，索引列不能是表达式的一部分，也不能是函数的参数，应将索引列单独放在比较符号的一侧；

2. 前缀索引和索引选择性，若需要索引很长的字符串列，可以通过模拟哈希索引或者索引开始的部分字符，索引选择性值不重复的索引值与记录总数的比值，选择性越高查询效率越高；

3. 多列索引，“索引合并”可以使用表上的多个单列索引来定位指定的行，但索引合并往往意味着索引建得很糟糕，通过需要一个包含所有相关列的多列索引而不是多个独立索引列；

4. 聚簇索引，一种数据存储方式，InnoDB的聚簇索引实际上是将数据放在索引的叶子页中，其聚簇索引列为主键列，只聚集在同一个页面中的记录（紧凑存储）
   优点：相关数据被连续存放在一起，能够减少随机IO；数据访问更快；
   缺点：插入速度依赖于插入顺序，按照主键顺序插入速度最快；更新聚簇索引列代价很高，主键更新/插入新行可能面临“页分裂”问题；可能导致全表扫描变慢；二级索引更大，因为二级索引叶子节点保存的是行的主键值，需要二次索引查找；

5. 覆盖索引，一个索引包含所有需要查询的字段的值，也就是通过多列索引，将需要查询的列都放进索引，使一个索引包含了查询所需的所有字段，这样就不需要回表进行二次查询；

6. 使用索引扫描来进行排序，只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引来对结果进行排序；

6. 数据库三范式

1. 1NF(第一范式)，符合1NF的关系中的每个属性都不可再分，每个数据库表中的每一列都是不可分割的基本数据项，同一列中不能有多个值；
2. 2NF(第二范式)，2NF是在1NF的基础上，消除了非主属性对于码的部分函数依赖，要求数据库表中的每个实例或行必须被唯一区分；
3. 3NF(第三范式)，3NF在2NF的基础上，消除了非主属性对于码的传递依赖；

7. 查询执行的流程

1. 客户端发送一条查询给服务器，MySQL客户端和服务器之间的通信协议是“半双工”的；
2. 通过对大小写敏感的哈希查找，检查缓存命中，若命中则检查权限后返回缓存值，整个过程无需解析SQL语句；
3. 服务器端通过关键字对SQL解析生成解析树，并使用MySQL语法规则验证和解析查询、预处理验证权限并进一步验证解析树是否合理，再由优化器生成对应的执行计划；
          MySQL能够处理的优化类型：重新定义关联表的顺序；将外连接转化为内连接；使用等价变换原则，可以减少合并一些比较；优化COUNT()、MIN()和MAX()；预估并转换为常数表达式；覆盖索引扫描；子查询优化；提前终止查询；等值传播
4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
5. 将结果返回给客户端；

8. SQL中各种JOIN的区别