博客

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万行的数据，可能导致表空间增大，回滚后，表空间不会减小回去。