mysql体系结构和查看当前的数据库请求

mysql体系结构：
    由：连接池组件、管理服务和工具组件、sql接口组件、查询分析器组件、优化器组件、       
        缓冲组件、插件式存储引擎、物理文件组成。
    mysql是独有的插件式体系结构，各个存储引擎有自己的特点。    
   
   


   
mysql各个存储引擎概述：
    innodb存储引擎 ：[/color][/b] 面向oltp(online transaction processing)、行锁、支持外键、非锁定读、默认采用repeaable级别（可重复读）通过next-keylocking策略避免幻读、插入 缓冲、二次写、自适应哈希索引、预读
    myisam存储引擎:   不支持事务、表锁、全文索引、适合olap（在线分析处理），其中myd:放数据文件，myi:放索引文件
    ndb存储引擎：     集群存储引擎，share nothing，可提高可用性
    memory存储引擎： 数据存放在内存中，表锁，并发性能差，默认使用哈希索引
    archive存储引擎： 只支持insert和select zlib算法压缩1：10，适合存储归档数据如日志等、行锁
    maria存储引擎：   目的取代myisam、缓存数据和索引、行锁、mvcc
   
    


   
innodb特性：
    主体系结构： 默认7个后台线程，4个io thread(insert buffer、log、read、write),1个master thread(优先级最高),1个锁(lock)监控线程，1个错误监控线程。可以通过show engine innodb status来查看。新版本已对默认的read thread和write thread分别增大到4个，可通过show variables like 'innodb_io_thread%'查看。
    存储引擎组成： 缓冲池(buffer pool)、重做日志缓冲池(redo log buffer)以及额外的内存池(additional memory pool).具体配置可由show variables like 'innodb_buffer_pool_size'、show variables like
'innodb_log_buffer_size'、show variables like 'innodb_additional_mem_pool_size'来查看。
    缓冲池： 占最大块内存，用来存放各种数据的缓存包 括有索引页、数据页、undo页、插入缓冲、自适应哈希索引、innodb存储的锁信息、数据字典信息等。工作方式总是将数据库文件按页(每页16k)读 取到缓冲池，然后按最近最少使用(lru)的算法来保留在缓冲池中的缓存数据。如果数据库文件需要修改，总是首先修改在缓存池中的页(发生修改后即为脏 页)，然后再按照一定的频率将缓冲池的脏页刷新到文件。通过命令show engine innodb status;来查看。
    日志缓冲： 将重做日志信息先放入这个缓冲区，然后按一定频率将其刷新到重做日志文件。
  



    master thread:
    loop主循环每秒一次的操作：
      日志缓冲刷新到磁盘，即使这个事务还没有提交。(总是执行，所以再大的事务commit 
      的时间也是很快的)           
      合并插入缓冲(innodb当前一秒发生的io次数小于5次则执行)
      至多刷新100个innodb的缓冲池中的脏页到磁盘(超过配置的脏页所占缓冲池比例则执
      行，在配置文件中由innodb_max_dirty_pages_pac决定，默认是90，新版本是75，
      google建议是80)
      如果当前没用用户活动，切换到backgroud loop        

    loop主循环每10秒一次的操作：
      刷新100个脏页到磁盘(过去10秒IO操作小于200次则执行)
      合并至多5个插入缓冲(总是)
      将日志缓冲到磁盘(总是)
      删除无用的Undo页(总是)
      刷新100个或者10个脏页到磁盘(有超过70%的脏页，刷新100个脏页;否则刷新10个脏页)
      产生一个检查点

    backgroud loop，若当前没有用户活动(数据库空闲时)或者数据库关闭时，就会切换到这个循环：
      删除无用的Undo页(总是)
      合并20个插入缓冲(总是)
      跳回到主循环(总是)
      不断刷新100个页，直到符合条件(可能在flush loop中完成)

    如果flush loop中也没有什么事情可以做了，InnoDB存储引擎会切换到suspend_loop，将master thread挂起，等待事件的发生。若启用了InnoDB存储引擎，却没有使用任何InnoDB存储引擎的表，那么master thread总是处于挂起状态

    插入缓冲： 不是缓冲池的一部分，Insert Buffer是物理页的一个组成部分,它带来InnoDB性能的提高。根据B+算法(下文会提到)的特点，插入数据的时候会主键索引是顺序的，不会造成数 据库的随机读取，而对于非聚集索引(即辅助索引)，叶子节点的插入不再是顺序的了，这时需要离散地访问非聚集索引，插入性能在这里变低了。InnoDB引 入插入缓冲，判断非聚集索引页是否在缓冲池中，如果在则直接插入;不在，则先放在 插入缓冲区中。然后根据上述master thread中介绍的，会有一定的频率将插入缓冲合并。此外，辅助索引不能是唯一的，因为插入到插入缓冲时，并不去查找索引页的情况，否则仍然会造成随机 读，失去插入缓冲的意义了。插入缓冲可能会占缓冲池中内存，默认也能会占到1/2，所以可以将这个值调小点，到1/3。通过 IBUF_POOL_SIZE_PER_MAX_SIZE来设置，2表示1/2,3表示1/3。

    两次写：   它带来InnoDB数据的可靠性。如果写失效，可以通过重做日志进行恢复，但是重做日志中记录的是对页的物理操作，如果页本身损坏，再对其进行重做是没有 意义的。所以，在应用重做日志前，需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewire。
     恢复数据=页副本+重做日志
             
   


             
   自适应哈希索引： InnoDB存储引擎提出一种自适 应哈希索引，存储引擎会监控对表上索引的查找，如果观察到建立建立哈希索引会带来速度的提升，则建立哈希索引，所以称之为自适应的。自适应哈希索引只能用 来搜索等值的查询，如select * from table where index_col='***', 此外自适应哈希是由InnoDB存储引擎控制的，我们只能通过innodb_adaptive_hash_index来禁用或启用，默认开启。
             
mysql 文件
   参数文件： 告诉Mysql实例启动时在哪里可以找到 数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置。用文件存储，可编辑，若启动时加载不到则不能成功启动（与其他数据库不 同）。参数有动态和静态之分，静态相当于只读，动态是可以set的。如我们通过show variable like '***'查出来的key、value值，是可以通过set key=value直接修改的。同是，修改时还有作用域之分，即这个seesion个有效和全局有效，在对应的key前加上session或global 即可，如select @@seesion.read_buffer_size、set @@global.read_buffer_size。
   日志文件： 用来记录Mysql实例对某种条件做出响应时写入的文件。如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
   错误日志： 通过show variables like 'log_error'来查看错误日志存放地址
   慢查询日志： 通过show variables like '%long%' 查看慢查询日志记录的阈值，新版本设成了0.05；通过show variables like 'log_slow_queries'查看是否开启了，默认为关闭的；通过show variabes like 'log_queries_not_using_indexes'查看是将没有使用索引的查询记录到慢日志中。mysql中可以直接通过 mysqldumpslow命令来查看慢日志。
   二进制文件： 不记录查询，只记录对数据库所有的修改 操作。目的是为了恢复(point-in-time修复)和复制。通过show variables like 'datadir'查看存放路径。二进制日志支持STATEMENT、ROW、MIX三种格式，通过binlog_format参数设定，通常设置为 ROW，可以为数据库的恢复和复制带来更好的可靠性，但会带来二进制文件大小的增加，复制时会增加网络开销。mysql中通过mysqlbinlog查看 二进制日志文件内容。
   socket文件： 当用Unix域套接字方式进行连接时需要的文件。
   pid文件： Mysql实例的进程ID文件。
   Mysql表结构文件： 用来存放Mysql表结构定义文件。因为Mysql插件式存储引擎的体系结构，每个表都有一个对应的文件，以frm后缀结尾。
   存储引擎文件： 存储自己的文件来保存各种数据，真正存储了数据和索引等数据。下面主要介绍InnoDB的存储引擎下的表空间文件和重做日志文件。
   表空间文件： InnoDB默认的表空间文件为 ibdata1,可通过show variables like 'innodb_file_per_table'查看每个表是否产生单独的.idb表空间文件。但是，单独的表空间文件仅存储该表的数据、索引和插入缓冲 等信息，其余信息还是存放在默认的表空间中。




              
    重做日志文件： 实例和介质失败，重做日志文件就能 派上用场，如数据库掉电，InnoDB存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。参数 innodb_log_file_size指定了重做日志文件的大小；innodb_log_file_in_group指定了日志文件组中重做日志文件 的数量，默认为2，innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，代表只有一个日志文件组，没有镜 像；innodb_log_group_home_dir指定了日志文件组所在路径，默认在数据库路径下。
     二进制日志和重做日志的区别：首先，二进制日志会记录所有与Mysql有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。 而InnoDB存储引擎重做日志只存储有关其本身的事务日志；其次内容不同，不管将二进制日志文件记录的格式设为STATEMENT还是ROW，又或者是 MIXED，其记录的都是关于一个事务的具体操作内容。而InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况 。此外，写入时间不同，二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目被 写入重做日志文件中。
    
mysql innodb表
    表空间：表空间可看做是InnoDB存储引擎逻辑结构的最高层。
    段：表空间由各个段组成，常见的段有数据段、索引段、回滚段等。
    区：由64个连续的页组成，每个页大小为16kb，即每个区大小为1MB。
    页：每页16kb，且不能更改。常见的页类型有：数据页、Undo页、系统页、事务数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。
    行：InnoDB存储引擎是面向行的(row-oriented)，每页最多允许存放7992行数据。
    行记录格式：常见两种行记录格式Compact和Redundant，mysql5.1版本后，主要是Compact行记录格式。对于Compact，不 管是char型还是varchar型，null型都是不占用存储空间的；对于Redudant,varchar的null不占用空间，char的null 型是占用存储空间的。
     varchar类型的长度限制是65535，其实达不到，会有别的开销，一般是65530左右，这还跟选取的字符集有关。此外这个长度限制是一整行的，例 如：create table test(a varchar(22000), b varchar(22000), cvarchar(22000)) charset=latin1 engine=innodb也会报错。
            
     对于blob类型的数据，在数据页面中只保存了varchar(65535)的前768个字节前缀数据，之后跟的是偏移量，指向行溢出页，也就是 Uncompressed BLOB Page。新的InnoDB Plugin引入了新的文件格式称为Barracuda，其有两种新的行记录格式Compressed和Dynamic，两者对于存入Blog字段采用了 完全溢出的方式，在数据库页中存放20个字节的指针，实际的数据都存入在BLOB Page中。
               





    数据页结构： 数据页结构由以下7个部分组成：
       File Header(文件头):记录页的一些头信息，如页偏移量、上一页、下一页、页类型等，固定长度为38个字节。
       Page Header(页头)：记录页的状态信息，堆中记录数、指向空闲列表的指针、已删除记录的字节数、最后插入的位置等，固定长度共56个字节。
       Infimun+Supremum Records:在InnoDB存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。
       Infimun记录是比该页中任何主键都要小的值，Supermum指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。在Compact行格式和Redundant行格式下，两者占用的字节数各不相同。
       

                             
        User Records(用户记录，即行记录):实现记录的内容。再次强调，InnoDB存储引擎表总是B+村索引组织的。
        Free Space(空闲空间)：指空闲空间，同样也是个链表数据结构。当一条记录被删除后，该空间会被加入空闲链 表中。
        Page Directory(页目录)：页目录存放了记录的相对位置，并不是偏移量，有些时候这些记录称为Slots(槽)，InnoDB并不是每个记录一个槽， 槽是一个稀疏目录，即一个槽中可能属于多个记录，最少属于4条记录，最多属于8条记录。需要牢记的是，B+树索引本身并不能找到具体的一条记录，B+树索 引能找到只是该记录所在的页。数据库把页载入内存，然后通过Page Directory再进行二叉查找。只不过二叉查找的时间复杂度低，同时内存中的查找很快，因此通过忽略了这部分查找所用的时间。
         File Trailer(文件结尾信息)：为了保证页完整地写入磁盘(如写过程的磁盘损坏、机器宕机等)，固定长8个字节。                                         

    视图： Mysql中的视图总是虚拟的表，本身不支持物化视图。但是通过一些其他技巧(如触发器)，同样也可以实现一些简单的物化视图的功能。

    分区： Mysql数据库支持RANGE、LIST、HASH、KEY、COLUMNS分区，并且可以使用HASH或KEY来进行子分区。
          
mysql innodb常见索引与算法：
   B+树索引： B+树的数据结构相对较复杂，B代表的 是balance最早是从平衡二叉树演化而来，但B+树并不是一个二叉树，对其较详细的介绍可以参见这篇文章：http://blog.csdn.net /v_JULY_v/article/details/6530142 由于B+树索引的高扇出性，因此在数据库中，B+树的高度一般都在2~3层，也就对于查找某一键值的行记录，最多只要2到3次IO,现在一般的磁盘每秒至 少可以做100次IO，2~3次的IO意味着查询时间只需0.02~0.03秒。
   数据库中的B+索引可以分为聚集索引(clustered index)和辅助聚集索引(secondary index),但其内部都是B+树的，即高度平衡的，叶子节点存放数据。
   聚集索引： 由于聚集索引是按照主键组织的，所以每一张表只能有一个聚集索引，每个数据页都通过双向链表进行连接，叶子节点存放一整行的信息，所以查询优化器更倾向走聚集索引。此外，对于聚集索引的存储是逻辑上连续的。所以，聚集索引对于主键的排序查找和范围查找速度非常快。
   辅助索引： 也叫非聚集索引，叶子节点不存全部数据， 主要存键值及一个boomark(其实就是聚集索引的键)告诉InnoDB哪里可以找到与索引相对应的行数据，如一个高度为3的辅助索引和一个高度为3的 聚集索引，若根据辅助索引来查询行记录，一共需要6次IO。另外辅助索引可以有多个。
           
     索引的使用原则： 高选择、取出表中的少部分数据(也称为唯一索引)。一般取出的数据量超过表中数据的20%，优化器不会使用索引，而进行全表扫描。如对于性别等字段是没有意义的。
     联合索引： 也称复合索引，是在多列 （>=2）上建立的索引。Innodb中的复合索引也是b+ tree结构。索引的数据包含多列(col1, col2, col3…)，在索引中依次按照col1, col2, col3排序。如(1, 2), (1, 3),(2,0)…使用复合索引要充分利用最左前缀原则，顾名思义，就是最左优先。如创建索引ind_col1_col2(col1, col2)，那么在查询where col1 = xxx and col2 = xx或者where col1 = xxx都可以走ind_col1_col2索引，但where col2=****是走不到索引的。在创建多列索引时，要根据业务需求，where子句中使用最频繁且过滤效果好的的一列放在最左边。
             
    哈希索引： 哈希算法也是比较常见的算法，mysql innoDB中使用了比较常见的链地址法进行去重。此外上面已经提及，innoDB中的hash是自适应的，什么时候使用hash是系统决定的，无法进行人工设置。
    二分查找法： 这个算法比较常见，这里就不多提及了。在InnoDB中，每页Page Directory中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过对Page Directory进行二分查找得到的。

mysql innodb中的锁
     InnoDB存储引擎锁的实现和Oracle非常类似，提供一致性的非锁定读、行级锁支持、行级锁没有相关的开销，可以同时得到并发性和一致性。
     InnoDB存储引擎实现了如下两种标准的行级锁：
     共享锁(S Lock)： 允许事务读一行数据；
     排他锁(X Lock)： 允许事务删除或者更新一行数据。
     当一个事务已经获得了行r的共享锁，那么另外的事务可以立即获得行r的共享锁，因为读取没有改变行r的数据，我们称这种情况为锁兼容。但如果有事务想获得行r的排他锁，则它必须等待事务释放行r上的共享锁————这种情况称为锁不兼容。
    


     在InnoDB Plugin之前，只能通过SHOW FULL PROCESSLIST，SHOW ENGINE INOODB STATUS等命令来查看当前的数据库请求，然后再判断当前事务中的锁的情况。新版本的InnoDB Plugin中，在INFORMATION_SCHEMA架构下添加了INNODB_TRX、INNODB_LOCKS、 InnoDB_LOCK_WAITS。通过这三张表，可以更简单地监控当前的事务并分析可能存在的锁的问题。
     INNODB_TRX由8个字段组成：
     trx_id:InnoDB存储引擎内部唯一的事务ID
     trx_state:当前事务的状态。
     trx_started:事务的开始时间。
     trx_requested_lock_id:等待事务的锁ID。如trx_state的状态为LOCK WAIT,那么该值代表当前的等待之前事务占用锁资源的ID.
     若trx_state不是LOCK WAIT,则该值为NULL。
     trx_wait_started:事务等待开始的时间。
     trx_weight:事务的权重，反映了一个事务修改和锁住的行数。在InnoDB存储引擎中，当发生死锁需要回滚时，InnoDB存储会选
     择该值最小的进行回滚。
     trx_mysql_thread_id:Mysql中的线程ID,SHOW PROCESSLIST显示的结果。
     trx_query:事务运行的sql语句。
     通过select * from infomation_schema.INNODB_TRX;可查看
    
     INNODB_LOCKS表，该表由如下字段组成：
     lock_id:锁的ID。
     lock_trx_id:事务ID。
     lock_mode:锁的模式。
     lock_type:锁的类型，表锁还是行锁。
     lock_table:要加锁的表。
     lock_index:锁的索引。
     lock_space:InnoDB存储引擎表空间的ID号。
     lock_page:被锁住的页的数量。若是表锁，则该值为NULL。
     lock_rec:被锁住的行的数量。若是表锁，则该值为NULL。
     lock_data:被锁住的行的主键值。当是表锁时，该值为NULL。
     通过select * from information_schema.INNODB_LOCK;可查看
    
     INNODB_LOCK_WAIT由4个字段组成：
     requesting_trx_id:申请锁资源的事务ID。
     requesting_lock_id:申请的锁的ID。
     blocking_trx_id:阻塞的锁的ID。
     通过select * from information_schema.INNODB_LOCK_WAITS;可查看。
    
     一致性的非锁定读： InnoDB存储引擎通过行 多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行Delete、update操作，这时读取操作不会因此而会等待行上锁的释 放，相反，InnoDB存储引擎会去读取行的一个快照数据。快照数据是指该行之前版本的数据，该实现是通过Undo段来实现。而Undo用来事务中回滚数 据，因此快照本身是没有额外开销的。此外，快照数据是不需要上锁的，因为没有必要对历史的数据进行修改。一个行可能有不止一个快照数据，所以称这种技术为 行多版本技术。由此带来并发控制，称之为多版本并发控制(Multi VersionConcurrency Control, MVCC)。
     事务的隔离级别：Read uncommitted、Read committed、Repeatable read、serializable。在Read Committed和Repeatable Read下，InnoDB存储引擎使用非锁定一致性读。然而，对于快照的定义却不同。在Read Committed事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。在Repeatable事务隔离级别下，对于快照数 据，非一致性读总是读取事务开始时的行数据版本。                 


                              

     锁的算法：
     Record Lock:单行记录上的锁
     Gap Lock:间隙锁，锁定一个范围，但不包含记录本身
     Next-Key Lock:Gap Lock + Record Lock，锁定一个范围，并且锁定记录本身。更加详细的介绍可以参见这篇blog,http://www.db110.com/?p=1848

     锁的问题：
       丢失更新：经典的数据库问题，当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。 　　
         例:
             事务A和事务B同时修改某行的值，
              1.事务A将数值改为1并提交
              2.事务B将数值改为2并提交。
              这时数据的值为2，事务A所做的更新将会丢失。
              解决办法：事务并行变串行操作，对更新操作加排他锁。

        脏读：一个事务读到另一个事务未提交的更新数据，即读到脏数据。
          例:
              1.Mary的原工资为1000, 财务人员将Mary的工资改为了8000(但未提交事务)       
              2.Mary读取自己的工资 ,发现自己的工资变为了8000，欢天喜地！
              3.而财务发现操作有误，回滚了事务,Mary的工资又变为了1000, 像这样,Mary记取的工资数8000是一个脏数据。
              解决办法：脏读只有在事务隔离级别是Read Uncommitted的情况下才会出现，innoDB默认隔离级别是Repeatable Read，所以生产环境下不会出现脏读。
               
        不可重复读：在同一个事务中,多次读取同一数据,返回的结果有所不同。换句话说就是,后续读取可以读到另一个事务已提交的更新数据。相反"可重复读"在同 一事务多次读取数据时,能够保证所读数据一样,也就是后续读取不能读到另一事务已提交的更新数据。脏读和不可重复读的主要区别在于，脏读是读到未提交的数 据，不可重复读是读到已提交的数据。
          例:
              1.在事务1中，Mary 读取了自己的工资为1000,操作并没有完成
              2.在事务2中，这时财务人员修改了Mary的工资为2000,并提交了事务.
              3.在事务1中，Mary 再次读取自己的工资时，工资变为了2000
              解决办法：读到已提交的数据，一般数据库是可接受的，因此事务隔离级别一般设为Read Committed。Mysql InnoDB通过Next-Key Lock算法避免不可重复读，默认隔离级别为Repeatable Read。
    
    
mysql innodb中的事务    
     事务的四个特性：原子性、一致性、隔离性、持久性
     隔离性通过锁实现，原子性、一致性、持久性通过数据库的redo和undo来完成。

     重做日志记录了事务的行为，通过redo实现，保证了事务的完整性，但事务有时还需要撤销，这时就需要产生undo。undo和redo正好相反，对于数 据库进行修改时，数据库不但会产生redo，而且还会产生一定的undo，即使执行的事务或语句由于某种原因失败了，或者如果用一条rollback语句 请求回滚，就可以用这些undo信息将数据回滚到修改之前的样子。与redo不同的是,redo存放在重做日志文件中，undo存放在数据库内部的一个特 殊段(segment)中，这称为undo段(undo segment)，undo段位于共享表空间内。还有一点重要的是，undo记录的是与事务操作相反的逻辑操作，如insert undo 记录一个delete，所以undo只是逻辑地将数据库恢复成事务开始前的样子。如:insert 10万行的数据，可能导致表空间增大，回滚后，表空间不会减小回去。