MySQL(基于InnoDB存储引擎)

【MySQL简介】    MySQL被设计为一个单进程多线程的架构的数据库，通常一个数据库对应一个实例，但是在集群的情况下可能存在一个数据库被多个数据实例使用的情况。MySQL数据库实例在系统上的表现就是一个进程。从概念上讲，数据库是文件的集合，数据库实例是程序。当启动实例时，MySQL数据库会去读取配置文件，根据配置文件的参数来启动数据库实例。    数据库读取配置文件顺序：/etc/my.cnf -> /etc/mysql/my.cnf -> /usr/local/mysql/etc/my.cnf -> ~/.my.cnf 如果存在多个配置文件，MySQL会以读取到的最后一个配置文件中的参数为准。    MySQL数据库是插件式体系结构，其拥有众多存储引擎，其中一个十分优秀的存储引擎便是InnoDB。    连接MySQL操作实质是一个连接进程和MySQL数据库实例进行通信。【InnoDB存储引擎】    InnoDB存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。    1、Master Thread    这是个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲、UNDO页的回收等。    2、IO Thread    InnoDB存储引擎使用了大量的异步IO来处理写IO请求，这样可以极大提高数据库的性能。而IO Thread的工作主要是负责这些异步IO请求的回调处理。    3、Purge Thread    事务被提交后，需要Purge Thread来回收已经使用并分配的undo页。    4、Page Cleaner Thread    Page Cleaner Thread是在InnoDB1.2x版本引入的，其作用是将之前版本中脏页的刷新操作都放到单独的线程中完成，减轻原Master Thread的工作及对于用户查询线程的阻塞。    关于内存    1、缓冲池    在数据库中进行读取页的操作，首先将从磁盘读取到的页存放在缓冲池中，这个过程称为将页“FIX”在缓冲池中，下一次再读取相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。    对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时出发，而是通过一种称为Checkpoint的机制刷新回磁盘。同样，这也是为了提高数据库的整体性能。    InnoDB内存数据对象有：缓冲池（innodb_buffer_pool）、重做日志缓冲(redo log_buffer)、额外内存池(innodb_additional_mem_pool_size)。其中缓冲池中包括有：数据页（data page）、索引页(index page)、插入缓冲（insert buffer）、自适应哈希索引、锁信息（lock info）、数据字典信息。    从InnoDB1.0x版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中，这样是为了减少数据库内部的资源竞争，增加数据库的并发处理能力，可以通过参数innodb_buffer_pool_instances来进行配置，默认值为1。    2、LRU List、Free List和Flush List    缓冲池中页的大小默认为16KB，缓冲池使用LRU算法进行管理，但是对LRU算法做了一些优化，在LRU列表中加入了midpoint位置。新读取到的页不是直接放入LRU列表的首部，而是放到LRU列表的midpoint位置，默认配置下，该位置在LRU列表长度的5/8处，midpoint位置可由参数innodb_old_blocks_pct控制（如果值为37，表示插入到LRU列表尾端的37%的位置）。InnoDB存储引擎中，把midpoint之后的列表称为old列表，之前的列表称为new列表，new列表中的页都是活跃的热点数据。那么问题来了，页面何时才会加入到热端，InnoDB存储引擎有一个innodb_old_blocks_time参数，只有当页面的后续访问与第一次访问的时间间隔大于此值时，才将页面插入到LRU链表首部，这样是为了防止全表扫描导致大量的page被替换出内存。    LRU List用来管理已经读到的页，但当数据库刚启动的时候，LRU List是空的，即没有任何的页，这时页都存放在Free List列表中，当需要从缓冲池中分页时，首先从Free List中查找是否有可用的空闲页，如有则将该页从Free List中删除，加入到LRU列表中。否则根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。    在LRU List中的页被修改后，称该页为脏页，即缓冲池中的页和磁盘上的页的数据产生了不一致。这是数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush List中的页即为脏页列表。脏页既存在于LRU List中页存在于Flush List中。    3、重做日志缓冲    InnoDB存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。    Master Thread每一秒将重做日志缓冲刷新到重做日志文件；    每个事务提交时会将重做日志缓冲刷新到重做日志文件；    当重做日志缓冲池剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。    4、额外的内存池    在InnoDB存储引擎中，对内存的管理是通过一种称为内存堆得方式进行的。在对一些数据结构本身的内存进行分配时，例如分配了缓冲池，但是每个缓冲池中的帧缓冲还有对应的缓冲控制对象，这些对象记录了一些诸如LRU、锁、等待信息，而这个对象的内存需要从额外内存池中申请。    Checkpoint技术    如果一条DML语句如UPDATE或DELETE改变了页中的记录，那么此时页是脏的，倘若每次一个页发生变化，就将新页的版本刷新到磁盘，那么这个开销是非常巨大的。同时，如果在从缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了，为了避免发生数据丢失问题，当前事务数据库系统普遍采Write Ahead Log策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复，这也是事务ACID中D（持久性）的要求。    Checkpoint技术解决一下几个问题：    缩短数据库的恢复时间；    缓冲池不够用时，将脏页刷新到磁盘；    重做日志不可用时，刷新脏页。    当数据库发生宕机时，数据库不需要重做所有的日志，因为Checkpoint之前的页都已经刷新回磁盘，故数据库只需要对Checkpoint后的重做日志进行恢复，这样就打打缩短恢复的时间。    InnoDB存储引擎有两种Checkpoint，分别是Sharp Checkpoint和Fuzzy Checkpoint。Sharp Checkpoint发生在数据库关闭时将所有的脏页都刷新回磁盘，而Fuzzy Checkpoint存在以下几种方式：    Master Thread Checkpoint    FLUSH_LRU_LIST Checkpoint    Async/Sync Flush Checkpoint    Dirty Page too much Checkpoint    Master Thread Checkpoint差不多以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘，这个过程是异步的。    FLUSH_LRU_LIST Checkpoint在LRU List不足100个可用空闲页时，InnoDB存储引擎会将LRU List尾端的页移除，如果这些页中有脏页，那么需要进行Checkpoint，而这些页是来自LRU List的。    Async/Sync Flush Checkpoint指的是重做日志不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。为了保证重做日志的循环使用的可用性。    Dirty Page too much Checkpoint即脏页数量太多，导致InnoDB存储引擎强制进行Checkpoint，其目的总的来说还是为了保证缓冲池中有足够可用的页，其可由参数innodb_max_dirty_pages_pct控制（值为75表示当缓冲池脏页的数量占据75%时强制进行Checkpoint，刷新一部分脏页到磁盘）。    插入缓冲    InnoDB存储引擎对于非聚集索引的插入或者更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer对象中，然后以一定的频率和情况进行Insert Buffer和辅助索引页子节点的merge操作，这时通常能将多个插入合并到一个操作中，这就大大提高了对于非聚集索引插入的性能。    自适应哈希索引    InnoDB存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引。自适应哈希索引是通过缓冲池的B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引，InnoDB会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。    【文件】    MySQL数据库中的参数可以分为两类：动态参数和静态参数。动态参数意味着可以在MySQL实例中进行更改，静态参数说明在整个实例生命周期内不得进行更改。可以通过SET命令对动态参数值进行修改。    对于日志文件，MySQL数据库中常见的日志文件有：    错误日志        错误日志文件对MySQL的启动、运行、关闭过程进行了记录。SHOW VARIABLES LIKE 'log_error'\G;    慢查询日志        慢查询阈值：参数long_query_time，默认10，即运行时间超过10秒的所有SQL语句都记录到慢查询日志文件中。默认情况MySQL数据库不启动慢查询日志，需要手动将参数设为ON。SHOW VARIABLES LIKE 'long_query_time'\G;另一个和慢查询日志有关的参数是log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件中。    二进制日志        二进制日志记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作。二进制日志主要有以下几种作用：恢复、复制、审计。        【表】    在InnoDB存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。表空间由段（segment）、区（extent）(1MB，共64个连续的页)、页（page）(16kb)组成。    InnoDB存储引擎是面向行的，也就说数据是按行进行存放的。行溢出数据通过行溢出页存储。    触发器的作用是在执行INSERT、DELETE和UPDATE命令之前或者之后自动调用SQL命令或存储过程。创建触发器的命令是CREATE TAIGGER，最多可以为一个表建立6个触发器，即分别为INSERT、UPDATE、DELETE的BEFORE和AFTER各定义一个。    关于分区，从逻辑上讲，只有一个表或者一个索引，但是在物理上这个表或索引可能由舒适个物理分区组成，MySQL数据库只支持水平分区，不支持垂直分区，并且MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。    当前MySQL支持以下几种类型的分区：    RANGE分区：行数据基于属于一个给定连续区间的列值被放入分区。    LIST分区：和RANGE分区类似，只是LIST分区面向的是离散的值。    HASH分区：根据用户自定义的表达式的返回值来进行分区，返回值不能为负数。    KEY分区：根据MySQL数据库提供的哈希函数来进行分区。    数据库的应用分为两类：一类是OLTP（在线事务处理），如Blog、电子商务、网络游戏等；另一类是OLAP（在线分析处理），如数据仓库、数据集市。对于OLAP的应用，分区的确是可以很好地提高查询的性能，然而对于OLTP的应用，分区应该非常小心，这种应用下，通常不可能会获取一张大表中10%的数据，大部分都是通过索引返回几条记录即可，而根据B+树索引的原理可知，对于一张大表，一般的B+树需要2~3次的磁盘IO，因此B+树可以很好地完成操作，不需要分区的帮助。    【索引与算法】    InnoDB存储引擎支持以下几种常见的索引：    B+树索引    全文索引    哈希索引    InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。    B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用和最为有效的索引    这里有一个值得注意的问题，就是B+树索引并不能找到一个给定键值的具体行，B+树索引能找到的只是被查找数据行所在的页，然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。    聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。    对于辅助索引，叶子节点并不包含行记录的全部数据，叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签就是相应行数据的聚集索引。    如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树索引是最合适的，可以通过SHOW INDEX结果列中的Cardinality来观察索引中不重复记录数量的预估值。    【锁】    InnoDB存储引擎支持的是行级别的锁，并且支持多粒度锁定，允许事务在行级上的锁和表级上的锁同时存在，意向锁就是支持在不同粒度上进行加锁操作。    SELECT ... FOR UPDATE对读取的杭机路加一个X锁，SELECT ... LOCK IN SHARE MODE对读取的行记录加一个S锁。    对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成。    锁升级是指将当前锁的粒度降低（变粗），例如数据库可以将一个表的1000个行锁升级为一个页锁。    【事务】    事务符合ACID特性：原子性、一致性、隔离性、持久性。    对于子事务，任何子事务都在顶层事务提交后才真正的提交。    InnoDB存储引擎提供的事务隔离级别有：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。