1、MySQL的体系架构

Mysql是一个可高度定制化的关系型数据库，提供很多可配置的参数，下面是mysql理论体系结构。

1.1Mysql组件

Mysql主要体系结构主要由以下几个组件组成：连接池组件、管理服务和工具组件、SQL接口组件、分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件、连接池组件。

（1）连接池组件（Connectors pool）

MySQL首先是一个网络程序，其在TCP之上定义了自己的应用层协议。所以要使用MySQL，我们可以编写代码，跟MySQL Server建立TCP连接，之后按照其定义好的协议进行交互。当然这样比较麻烦，比较方便的办法是调用SDK，比如Native C API、JDBC、PHP等各语言MySQL Connector，或者通过ODBC。但通过SDK来访问MySQL，本质上还是在TCP连接上通过MySQL协议跟MySQL进行交互。

（2）管理服务和工具组件（Connection Management）

每一个基于TCP的网络服务都需要管理客户端链接，MySQL也不例外。MySQL会为每一个连接绑定一个线程，之后这个连接上的所有查询都在这个线程中执行。为了避免频繁创建和销毁线程带来开销，MySQL通常会缓存线程或者使用线程池，从而避免频繁的创建和销毁线程。客户端连接到MySQL后，在使用MySQL的功能之前，需要进行认证，认证基于用户名、主机名、密码。如果用了SSL或者TLS的方式进行连接，还会进行证书认证。

（3）SQL接口组件（SQL Interface）

MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口。

（4）分析器组件（Parser）

MySQL会解析SQL查询，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。

（5）优化器组件（Optimizer）

语法解析和查询重写之后，MySQL会根据语法树和数据的统计信息对SQL进行优化，包括决定表的读取顺序、选择合适的索引等，最终生成SQL的具体执行步骤。这些具体的执行步骤里真正的数据操作都是通过预先定义好的存储引擎API来进行的，与具体的存储引擎实现无关。

（6）缓冲组件（Caches & Buffers）

MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条Select语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了。

（7）插件式存储引擎（Pluggable Storage Engine）

存储引擎的具体实现，这些存储引擎都实现了MySQl定义好的存储引擎API的部分或者全部。MySQL可以动态安装或移除存储引擎，可以有多种存储引擎同时存在，可以为每个Table设置不同的存储引擎。存储引擎负责在文件系统之上，管理表的数据、索引的实际内容，同时也会管理运行时的Cache、Buffer、事务、Log等数据和功能。

（8）物理文件（File System）

所有的数据，数据库、表的定义，表的每一行的内容，索引，都是存在文件系统上，以文件的方式存在的。当然有些存储引擎比如InnoDB，也支持不使用文件系统直接管理裸设备，但现代文件系统的实现使得这样做没有必要了。

1.2连接mysql的方法

连接mysql的操作是一个连接进程和mysql数据库之间的通讯，下面介绍下连接mysql的几种方式：

1、TCP/IP

TCP/IP 套接方式是mysql数据库在任何平台下都提供的连接方式，是使用最多的一张方式，这种方式在TCP/IP 连接上建立一个基于网络的连接请求，一般情况是客户端在一台服务器上，而mysql实例在另外一台服务器上，这两台机器通过一个TCP/IP 网络连接。

2、命名管道和内存共享

在windows2000 windows xp windows2000 windows2003 以及在此之上的平台，如果两个进程通讯的进程在同一台服务器上，那么可以使用命名管道这种方法连接mysql数据库服务。

3、UNIX域套接字

在LINUX或UNIX环境下，还可以使用UNIX域套接字。UNIX域套接字其实不是一个网络协议，所以只能在mysql客户端和数据库实例在同一台服务器上的情况使用。

2 Mysql支持的存储引擎以及各存储引擎之间的比较

可以使用以下命令查看mysql支持的引擎：show engines

MyISAM存储引擎，其特点是不支持事务、表锁和全文索引，对于一些OLAP系统，操作速度快。每个MyISAM在磁盘上存储成三个文件。文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。这里特别要注意的是MyISAM不缓存数据文件，只缓存索引文件。InnoDB存储引擎支持事务，主要面向OLTP方面的应用，其特点是行锁设置、支持外键，并支持类似于Oracle的非锁定读，即默认情况下读不产生锁。InnoDB将数据放在一个逻辑表空间中（类似Oracle）。InnoDB通过多版本并发控制来获得高并发性，实现了ANSI标准的4种隔离级别，默认为Repeatable，使用一种被称为next-key locking的策略避免幻读。对于表中数据的存储，InnoDB采用类似Oracle索引组织表Clustered的方式进行存储。InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比Myisam的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。NDB存储引擎是一个集群存储引擎，类似于Oracle的RAC，但它是Share Nothing的架构，因此能提供更高级别的高可用性和可扩展性。NDB的特点是数据全部放在内存中，因此通过主键查找非常快。关于NDB，有一个问题需要注意，它的连接(join)操作是在MySQL数据库层完成，不是在存储引擎层完成，这意味着，复杂的join操作需要巨大的网络开销，查询速度会很慢。Memory存储引擎（之前称为Heap）将表中数据存放在内存中，如果数据库重启或崩溃，数据丢失，因此它非常适合存储临时数据。它只支持INSERT和SELECT操作，其设计的主要目的是提供高速的插入和压缩功能。Archive非常适合存储归档数据，如日志信息。

2.1 Innodb体系结构介绍：

由于我们现在用的存储引擎大多是innodb，下面着重介绍一下innodb引擎。Innodb体系结构是由一组后台进程和内存池组成。其中后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据，此外将已修改的数据文件刷新到磁盘文件中，同时保证在数据库发生异常的情况下innodb能恢复到正常的运行状态。主要有四种进程：Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。

图1Innodb体系结构

2.1后台线程：

1、Master Thread

主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性(包括脏页、合并插入缓冲)、undo页回收等）页=16K，使用下面命令查看:show engine innodb status。

2、IO Thread

主要负责io请求的回调处理，参数有innodb1.0之前的版本共有4个IO Thread 分别是write、read、indert buffer和log IO ，innodb1.0之后的版本分别把write、read增加到4个。可修改一下两个参数来增加读和写的线程，相关的参数有innodb_read_io_thread和innodb_write_io_thread。

3、Purge Thread

事务被提交后，其所使用的undolog可能不再需要，因此需要该线程回收已经使用并分配的undo页16k，从而提高CPU的使用率以及提升存储引擎的性能。在mysqld下面添加一下命令：innodb_purge_threads=1

4、Page Cleaner Thread

将之前版本中脏页的刷新操作都放入到单独的线程中来完成。减轻原Master Thread的工作。

2.2内存组成

图2 innodb存储引擎内存结构

1、缓冲池

innodb存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。由于CPU速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的性能。简单的说，缓冲池就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。对数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，下一次读取到相同的页，首先判断页是否在缓冲池中，如在，直接命中，读取该页。然后在以一定的频率刷新到磁盘上。然而并不是每次操作完成后都将数据刷新到磁盘上，而是通过一种checkpoint机制刷新。

缓冲池中缓存的数据页类型有：索引页，数据页，undo页，插入缓冲，自适应哈希索引，innodb存储的锁信息，数据字典信息等。

缓冲池大小参数：innodb_buffer_pool_size，可以使用下面命令查看：show variables values like ‘innodb_buffer_pool_size’;

缓冲池实例个数参数：
innodb_buffer_pool_instance，可以使用下面命令查看：show variables values like ‘
innodb_buffer_pool_instance’;与oracle中RAC不同的是，RAC是共享一组存储，而mysql中是将内存按照哈希值平均分配到多个实例中。

2、LRU List Free List 和 Flush List

缓冲池中存放各种类型的页，该段区域就是对缓冲池按照LRU算法进行管理。

参数：innodb_old_blocks_pct (表示读取的页插入到LRU列表尾端的37%位置，即old位置) innodb_old_blocks_time(用于表示页读取到mid位置后需要多久等待才回被加入到LRU热端)数据库状态参数：pages made young 显示了LRU列表中页移动到前端的次数。

buffer pool hit rate:命中率，若小于95%，用户需要观察是否由于全表扫描引起的LRU列表被污染。

特例：该内存区域对缓冲池中给分配的自适应哈希索引、lock信息、insert buffer等也不做LRU管理。

3、重做日志缓冲：

innodb首先将重做日志信息先放入到重做日志缓冲区，然后按照一定频率将其刷新到重做日志文件。

刷新频率：

1，Master Thread 每一秒将重做日志缓冲刷新到重做日志文件；

2，每个事物提交时会将重做日志缓冲刷新到重做日志文件；

3，当重做日志缓冲剩余空间小于1/2时，重做日志缓冲刷新到重做日志文件。

4、额外内存：

理解：在对一些数据结构本身(记录诸如LRU,锁，等待等信息)的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从【缓冲池】中进行申请。

2.3checkpoint技术

如果在从缓冲池将页的新版本刷新到磁盘时发生了宕机，那么数据就不能恢复了，为了避免数据丢失的问题，当前事物数据库系统普遍采用了write ahead log策略，即当事务提交时，先写重做日志，再修改页。当由于发生宕机导致的数据丢失时，可以通过重做日志完成恢复(页的LSN,重做日志的LSN,checkpoint日志的LSN)。

【解决的问题】：

1，缩短数据库的恢复时间；

2，缓冲池不够用，将脏页刷新到磁盘；

3，重做日志不可用时，刷新脏页。

4，刷脏页，每次刷多少，从哪里取脏页，以及什么时间触发checkpoint（有很复杂的算法）。

主要参数：innodb_fast_shutdown=1:发生在数据库关闭时将所有的脏页都刷新回磁盘。

2.4Master Thread 工作方式(innodb 1.0.x版本)

【每一秒】：

1，日志缓冲刷新到磁盘，即使这个事务还没有提交-总是；

2，合并插入缓冲-可能；不是每秒发生，引擎判断当前一秒内发生的io次数是否小于5次，如果小于5次，引擎认为当前压力小，可以执行合并插入。

3，至多刷新100个innodb的缓冲池中的脏页到磁盘-可能；

4，如果当前没有用户活动，则切换到background loop-可能；

【每十秒】：

1，刷新100个脏页到磁盘-可能

2，合并至多5个插入缓冲-总是

3，将日志缓冲刷新到磁盘-总是

4，删除无用的undo页-总是

5，刷新100个或者10个脏页到磁盘-总是

2.5Master Thread 工作方式(innodb 1.2.x版本)：

【参数说明】：

innodb_io_capacity:磁盘IO的吞吐量。

1，在合并插入缓冲时，合并插入缓冲的数量为innodb_io_capacity的5%

2，在从缓冲区刷新脏页时，刷新脏页的数量为innodb_io_capacity

innodb_max_dirty_pages_pct:脏页占缓冲池的百分比，经过多方测试后75%为最优。详细看page 41.

innodb_purge_batch_size:回收的脏页数量，老版本中每次只回收20个，新版本可以通过该参数进行调整。

【工作方式】：

1，增加了上面可优化调整的参数；

2，仍然保留原来10秒的操作，在此基础上增加了Page Cleaner Thread线程，进一步提高并发性。

2.6InnoDb关键特性

（1）插入缓冲；

（2）两次写；

（3）自适应哈希索引；

（4）异步IO;

（5）刷新邻接页；

（1）插入缓冲

Insert Buffer:

理解：对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，直接插入，若不在，则先放入到一个insert buffer对象中。数据库这个非聚集索引已经插入到叶子节点，其实并没有，只是存放在另一个位置。然后再以一定的频率和情况进行insert buffer和辅助索引叶子节点的merge（合并）操作。

观察参数：Ibuf:size:***,free list len ***,seg size:***

[seg size]:当前insert buffer的大小*16KB

[free list]:空闲列表长度；

[inserts]:插入记录数；

Change Buffer:

理解： insert buffer的升级，只针对insert delete update 进行缓冲。其中，对某一条记录进行update操作可能分为两个过程：（1-将记录标记为已删除2-真正将记录删除）

从1.2.x版本开始，可以通过参数来控制change buffer最大使用内存数量。

【总结】：insert buffer区域实际上是一颗B+树，而且全局只有这一颗，保存在共享表空间ibdata中，详细参见page 51。

【合并时机】：

1，辅助索引页被读取到缓冲池时；

2，insert buffer bitmap页追踪到该辅助索引页已无可用空间时；

3，master thread;

（2）两次写

【理解】：在应用重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做。这就是两次写。(针对：16K的页，只写了前4K，之后发生宕机，这种情况被称为写失效)

【组成】：doublewrite由两部分组成，一部分是内存中的doublewrite buffer 大小2MB，另一部分是物理磁盘上共享表空间中连续的128个页，即2个区，大小同样为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。

【参数】：

innodb_dblwr_pages_written:一共写了多少页；

innodb_dblwr_writes:实际的写入次数。

skip_innodb_doublewrite:禁用doublewrite功能，可能会引起写失效。

（3）自适应哈希索引

【理解】innodb存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度的提升，则自动创建哈希索引(adaptive hash index,AHI)。自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

（4）异步IO ：

【理解】从innodb1.1.x开始，提供了内核级别的AIO，称为Native AIO,因此在编译或者运行该版本MySQL时，需要libaio库的支持，若没有，安装时会报错。

【参数】innodb_use_native_aio用来开启是否启用Native AIO；

（5）刷新邻接页

【理解】当刷新一个脏页时，innodb存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起刷新，这样做好处多多，通过AIO可以将多个IO写入操作合并为一个IO操作。在传统机械硬盘下有显著优势。

【参数】innodb_flush_neighbors，用来控制是否启用该特性，对传统机械硬盘建议启用。固态硬盘可关闭。

2.7启动和关闭

【关闭】：

【参数1】：innodb_fast_shutdown:

0：表示mysql关闭时，innodb需要完成所有的full purge和merge insert buffer，并且将所有脏页刷新回磁盘

1：不需要完成full pruge和merge insert buffer操作，在缓冲池中的一些数据脏页还是会刷新回磁盘--默认；

2：不完成full purge和merge insert buffer操作，也不将缓冲池中的数据脏页写回磁盘，而是将日志都写入日志文件。下次启动时会进行恢复。

【参数2】：innodb_force_recovery:

0：默认，进行所有的恢复操作，当无法有效恢复，如数据页发生了corruption，mysql可能发生宕机，并把错误日志写入日志；

1：忽略检查到的corrupt页；

2：阻止master thread线程运行，如master thread线程需要进行full purge操作，这会导致crash；

3：不进行事务的回滚操作；

4：不进行插入缓冲的合并操作；

5：不查看撤销日志（undo log），innodb存储引擎会将未提交的事务看作已提交；

6：不进行前滚操作。

2.8 innodb常用参数配置

（1）max_connect_error

表示当前机器连接失败超过服务器限制的max_connect_error的数量，然后在缓存里直接限制这台机子的暴力访问，在一定程度上可以缓解一些对数据库的莫名攻击。

（2）max_connections

参数用来设置最大连接（用户）数。每个连接MySQL的用户均算作一个连接，max_connections的默认值为100。本文将讲解此参数的详细作用与性能影响。MySQL无论如何都会保留一个用于管理（SUPER）登陆的连接，用于管理员连接数据库进行维护操作，即使当前连接数已经达到了max_connections。因此MySQL的实际最大可连接数max_connections+1；

这个参数实际起作用的最大值（实际最大可连接数）为16384，即该参数最大值不能超过16384，即使超过也以16384为准；增加max_connections参数的值，不会占用太多系统资源。系统资源（CPU、内存）的占用主要取决于查询的密度、效率等；该参数设置过小的最明显特征是出现”Too many connections”错误；

使用下面的命令修改：set global max_connections = 200;也可以修改my.cnf文件中的max_connections=200，这样的话会立即生效。

（3）table_open_cache ：

mysql每打开一个表，都会读入一些数据到table_open_cache 缓存 中，当mysql在这个缓存中找不到相应的信息时，才会去磁盘上直接读取。

Open_tables 当前打开的表数目 302 。table_open_cache 1024 。

每当客户端连接到mysql数据库，mysql数据库就会创建一个线程为它服务

但是首先mysql回去thread_cache中寻找可用的线程，找不到才会创建新的线程。

查看当前线程：show variables like "thread%";

查看当前线程状况：show status like "thread%";

（4）max_allowed_packet

会限制server接受的数据包大小。有时候大的插入和更新会受max_allowed_packet 参数限制，导致写入或者更新失败。

查看当前的配置：show VARIABLES like '%max_allowed_packet%';

修改当前配置：set global max_allowed_packet = 2*1024*1024*10，重启mysql后生效。

（5）Sort_buffer_size：

如果sort_merge_passes偏高，则考虑调大此参数，以加速group by/order by，但即使线程用不完也会 全部分配，故需要谨慎操作。

（6）Join_buffer_size：

用于索引范围扫描或执行全表扫描的join操作。

（7）Thread_cache_size：

最大可缓存的thread数量，通常每个占有256K，不会消耗太多内存

（8）Max_length_for_sort_data：

决定file sort时使用哪种算法，如果返回列的长度总和小于此，则使用最新的单路排序；

（9）tmp_table_size：

它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句，并且你有很多内存，增大tmp_table_size(和max_heap_table_size)的值。这个变量不适用于用户创建的内存表(memory table)。

你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总（Created_tmp_disk_tables和Created_tmp_tables），一般的比例关系是:

Created_tmp_disk_tables/Created_tmp_tables<5%。

（10）max_heap_table_size：

这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set@max_heap_table_size=#,但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。

（11）query_cache_size:

SHOW VARIABLES LIKE '%query_cache%';MYSQL的查询缓存用于缓存select查询结果，并在下次接收到同样的查询请求时，不再执行实际查询处理而直接返 回结果，有这样的查询缓存能提高查询的速度，使查询性能得到优化，前提条件是你有大量的相同或相似的查询，而很少改变表里的数据，否则没有必要使用此功 能。可以通过Qcache_lowmem_prunes变量的值来检查是否当前的值满足你目前系统的负载。注意：如果你查询的表更新比较频繁，而且很少有相同的查询，最好不要使用查询缓存。

使用下面命令可以查看下面相关参数：show status like ‘%Qcache%’;

Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。

Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。

Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。

Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次 数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当地调整缓存大小。

Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。

Qcache_queries_in_cache:当前缓存中缓存的查询数量。

Qcache_total_blocks:当前缓存的block数量。

测试：对比前面的参数值，我们发现Qcache_inserts变化了。Qcache_hits没有变，下边我们在执行同样的查询

select * from user where id = 2，按照前面的理论分析：Qcache_hits应该等于1，而Qcache_inserts应该值不变（其他参数的值变化暂时不关注，读者可以自行测试），再次执行：

show status like ‘%Qcache%’，看看有什么变化：

（12）sql_mode：

MySQL服务器能够工作在不同的SQL模式下，并能针对不同的客户端以不同的方式应用这些模式。这样，应用程序就能对服务器操作进行量身定制以满足自己的需求。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。这样，就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。可以使用“--sql-mode="modes"”选项，通过启动mysqld来设置默认的SQL模式。从MySQL 4.1开始，也能在启动之后，使用SET [SESSION|GLOBAL] sql_mode='modes'语句，通过设置sql_mode变量更改模式。

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

SQL_MODE可能是比较容易让开发人员和DBA忽略的一个变量，默认为空。SQL_MODE的设置其实是比较冒险的一种设置，因为在这种设置下可以允许一些非法操作，比如可以将NULL插入NOT NULL的字段中，也可以插入一些非法日期，如“2012-12-32”。因此在生产环境中强烈建议开发人员将这个值设为严格模式，这样有些问题可以在数据库的设计和开发阶段就能发现，而如果在生产环境下运行数据库后发现这类问题，那么修改的代价将变得十分巨大。此外，正确地设置SQL_MODE还可以做一些约束(Constraint)检查的工作。

3 mysql文件

构成Mysql数据库和innodb存储引擎的各类文件主要包括以下几类：参数文件、日志文件、socket文件、pid文件、mysql表结构文件、存储引擎文件。

3.1 参数文件

当mysql启动时，数据库首先会读取这个参数文件，用来寻找数据库各种文件所在的位置以及制定某些参数初始化，通常是指缓冲区中各个内存块的大小等（my.cnf文件）。 这个类似于oracle中的spfile或pfile这两个文件其实是一样的，只不过一个是二进制文件，一个是文本文件。

3.2日志文件

3.2.1错误日志

错误日志是mysql最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时可以查看此日志。（命名host_name.err）一般存在datadir目录下。

3.2.2二进制文件

二进制文件记录了所有的DDL和DML语句，但是不包括数据的查询select语句。它记录了数据库所有数据的更改过程，此日志用于灾难时的数据恢复。默认文件名为host_name-bin。

有三种格式，statement，row，mixed。

Statement，每条造成数据修改的sql都会被记录在日志中。Row，它将每行的变更记录到日志中，恢复的时候比较完全，缺点是数据量太大，对IO影响较大。Mixed，是mysql目前的默认日志格式，混合了以上两种日志。查看二进制文件的话需要使用mysqlbinlog工具。用法如下：mysqlbinlog localhost-bin.0007，如果是row日志，则需要加上-vv参数查看。

日志的删除：reset master；删除所有日志；purge master logs to ‘localhost-bin.00006删除00006以前的所有日志。Purge master logs before ‘2017-01-01 12:00:00’，删除设置时间以前的所有日志。--expire_logs_days参数设置保留日志的天数（需要在my.cnf中修改）。

system ls –ltr localhost-bin.log。mysqladmin flush-log用于刷新日志。和日志相关的参数，--binlog-do-db=db_name：如果当前的数据库是db_name，那么应将更新记录到二进制文件中；--binlog-ignore-db=db_name，如果当前的数据库是db_name就不写日志到二进制文件中，如果想多忽略几个数据库，可以用逗号隔开。

--innodb-safe-binlog：此选项和—sync-binlog=N（每写N次日志同步磁盘）一起使用，使得事务在日志中记录更加安全。

set sql_log_bin=0：具有super权限的客户端可以通过此语句禁止将自己的语句添加到二进制文件中。

3.2.3查询日志

查询日志记录了客户端所有的语句，而二进制日志不包含只查询数据的语句。主要的参数有以下几个：--log-output=【value】来进行控制，value可以上table，file，none的一个或多个，分别表示保存在表，文件，不保存等。这里的表是general_log（慢查询日志是slow_sql）表，如果要启用查询日志，可以通过设置—general-log=0或1和指定—general-log-file=file_name来控制。如果不指定file_name，那么日志将写入datadir目录下，默认文件名为host_name.log。查询日志不是二进制文件，可以使用cat，less，more查看，对于日志访问频繁的系统，不建议开启这个参数，会对系统造成较大影响。

3.2.4慢查询日志

慢查询主要记录所有执行时间查过long_query_time（慢查询设置时间）设置值并扫描记录不小于min_examined_row_limit的所有sql语句的日志，其中管理语句和不适用索引查询的语句。管理语句包括alter table，analyze、check、create index、drop index、optimize table、repair table等，如果想监控上面的语句可以设置—log-slow-admin-statements和log-queries-not-using-indexes控制。默认是关闭的，--slow-query-log=0或1来开启，不指定或者为1时开启。--slow_query_log_file=file_name指定路径和文件名。为日志文件，可以使用cat，less，more查看。set long_query_time=2和set global long_query_time=0.02(可以精确到微妙)

3.3 表结构定义文件

因为mysql是插件式存储引擎的体系结构关系，mysql数据的存储时根据表进行的，每个表都会有与之对应的文件。无论表采取哪种存储引擎，都会有一个frm为后缀的文件，这个文件记录了表的结构。可以使用cat查看。

3.4 套接字文件

在uniux系统下本地连接mysql可以采用unix域套接字方式，这种方式需要一个套接字（socket）文件，可以使用下面的命令查看：show variables like ‘socket’ \G;也可以自行配置该文件的位置，修改my.cnf下的socket=（某个路径。）

3.5 pid文件

当mysql实例时，会将自己的进程ID写入到这个文件中，可以使用下面命令查看该文件的路径：show variables like ‘pid_file’\G; 然后使用cat命令查看。

3.6 innodb存储引擎文件

之前介绍的文件都是mysql本身的文件，和存储引擎无关的文件，除了这些文件外，每个表存储引擎还有自己独有的文件，这些文件包括重做日志文件和表空间文件。

3.6.1表空间文件

Innodb采用表数据按照表空间的存储方式。默认会创建一个名为ibdata1的文件。可以使用下面命令查看：show variables like ‘innodb_data_file_path’\G;可以看到默认创建的表空间，设置了innodb_data_file_path，mysql数据库中所有的为innodb引擎的表共享该表表空间中。与之相关的一个参数innodb_file_per_table,如果将这个参数设置为on的时候，表示每个表单独使用一个表空间。使用下面命令查看该参数是否开启：show variables like ‘innodb_file_per_table’\G;如果开启，使用命令即可查看：system ls –lh /opt/local/data/*;独立表空间的命名规则是表名.ibd，这些单独的表空间文件仅存储该表的数据，索引等信息，其他的信息还是存放在默认的表空间中。

3.6.2 重做日志文件

他是记录innodb存储引擎的事务日志，当实例或介质失败时，innodb存储引擎会使用重做日志恢复到发生故障前的时刻，以此来保证数据的完整。重做日志有以下参数：

Innodb_log_file_size：指定每个重做日志文件的大小（innodb1.2x之前，重做日志文件总的大小要小于4G，1.2x版本将该限制扩大到512G）；innodb_log_files_in_group指定文件组中重做日志的数据量。Innodb_mirrored_log_groups指定组数。Innodb_log_group_home_dir指定了日志组所在的路径。二进制文件和重做日志的区别：

重做日志只记录与innodb存储引擎有关的数据页的更改，而二进制文件则记录了所有与mysql数据库有关的日志，包括其他引擎产生的日志。二进制记录日志的格式无论是用statement、rows、mixed，其记录的都是关于事务的具体内容，即该日志是逻辑日志。而重做日志记录的是每个数据页更改的物理情况。写入时间不同，二进制文件是在事务提交前提交的，即只写磁盘一次；而在事务进行过程中，却不断有重做条目被写到重做日志中。

重做日志的条目结构有四个字段，redo_log_type表示重做日志的类型；space表示表空间的ID；page_no表示页的偏移量；redo_log_body表示每个重做日志的数据部分，恢复的时候需要调用相应参数进行解析。从缓冲池往磁盘写入时是按照512个字节写入的。主线程中每秒是将重做日志写入到磁盘。触发写磁盘的过程是由innodb_flush_log_at_trx_commit控制，表示在提交操作时，将重做日志写入到磁盘上。

4 innodb存储引擎表

本部分内容主要分析表的物理存储特征，即数据在表中如何组织和存放的，简单的说，表是关于特定数据的集合，是关系型数据库的核心。

4.1 索引组织表

在innodb存储引擎中，表都是根据主键顺序存放的，这种方式叫做索引组织表。可以这样理解，即每张表都有个主键，可以再建表的时候指定，也可以不指定，不指定的话会，innodb存储引擎会判断表中是否有非空唯一索引，如果有这列就为表的主键，如果有多个会选择第一个作为主键，如果没有，innodb存储引擎会自动创建6字节大小的指针。_rowid表示表的主键，但是只能显示单个列为主键的情况，不能显示多个列为主键的情况。

SELECT CHANNEL_ID,rowid FROM c_channel_user_login;

SELECT ID,_rowid FROM sys_create_partition_log;

4.2 innodb逻辑存储结构

从innodb存储引擎的逻辑存储结构看，所有数据都被逻辑的存放在一个空间中，称之为表空间。表空间由段、区、页组成。可以理解成逻辑存储主要是用于描述数据的内部组织和管理的方法。物理存储主要用于描述数据在操作系统中文件组成情况。

4.2.1表空间

表空间是由段组成的，常见的段类型有数据段、索引段、回滚端等。是innodb存储引擎逻辑结构的最高层，如果开启innodb_file_per_table参数，那么每个表的数据单独放到一个表空间中；如果没有开启这个参数，那么所有的数据都会放到一个表空间中。但是每个表空间只存放数据、索引、插入缓冲页，其他的数据如回滚信息、插入缓存索引页、二次写缓冲等还是存放在共享表空间中。这就是为什么有些人会问，开启了独享表空间，共享表空间的数据还是会增加。

4.2.2段

常见的段类型有数据段、索引段、回滚端，由于innodb存储引擎表是索引组织的，

因此数据即索引，索引即数据。数据段即为B+数的叶子节点，索引段为B+树的非索引节点。对段的管理是由存储引擎自动完成的。

4.2.3 区

区是由连续的页组成的空间，在任何情况下一个区的大小都为1M，为了保证区中页的连续性，innodb每次会从磁盘上申请4到5个区，在默认情况下页的大小为16k，即一个区中可包含64个连续的页。

4.2.4 页

页是innodb磁盘管理的最小单位，在innodb存储引擎中，页的大小默认是16k，而在innodb1.2x开始，可以通过设置innodb_page_size设置页的大小为4k，8k，16k，设置完成后不能对其进行更改，除非使用mysqldump导入和到处来产生新的库。

常见的数据也有以下几种类型：数据也、undo页、系统页、事物数据页、插入缓冲位图页、插入缓冲空闲列表页、未压缩的二进制大对象页、压缩的二进制大对象页。

4.2.5 行

Innodb存放数据是按行进行存放的，每个页存放的行记录也是有影响定义的，最多存放7992行记录。Innodb主要有两种：compact和redundant两种格式存放行记录数据，可使用下面命令查看row_format。

SHOW TABLE STATUS LIKE 'c_channel_user_login';

Compact行记录格式：其设计目标是高效的存储数据，简单来说就是一个页中存放的行数据越多，性能就越高。Compact行记录格式存储方式如下：

变长字段的长度列表，其是按照列的逆序进行存放的。NULL标识符指示了该行数据中是否有NULL值，否则用1表示。记录头信息固定占用5个字节，主要有以下参数：

Delete_flag:改行记录是否被删除过。

Min_rec_flag：为1，如果该记录是预先被定义为最小的记录。

N_owned：该记录拥有的记录数。

Heap_no:索引堆中该条记录的排序记录。

Recore_type:记录类型，000表示普通，001B+树节点指针，010表示infimum，011表示supremum。

Next_record：页中下一条记录的相对位置。

Total：实际存储每个列的数据，NULL并不占用实际空间，还有就是若innodb存储引擎没有明确指出主键，每行还会增加一个6字节的rowid列。

Redundant行记录格式是mysql5.0之前的默认格式，主要是为了兼容之前版本的页格式，Redundant行记录格式存储方式如下：

Delete_flag:改行记录是否被删除过。

Min_rec_flag：为1，如果该记录是预先被定义为最小的记录。

N_owned：该记录拥有的记录数。

Heap_no:索引堆中该条记录的排序记录。

Recore_type:记录类型，000表示普通，001B+树节点指针，010表示infimum，011表示supremum。

Next_record：页中下一条记录的相对位置。

Total：实际存储每个列的数据，NULL并不占用实际空间，还有就是若innodb存储引擎没有明确指出主键，每行还会增加一个6字节的rowid列。

行移除数据：innodb存储引擎可以将一条数据中的某些数据存储在真正的数据页之外，一般为BLOB、LOB这类的大对象列表类型的存储会把数据存放在数据页之外。

4.3 innodb数据页结构

数据页是磁盘管理的最小单位，innodb存储引擎数据页结构如下：

File header、Page header、File trailer主要记录的是该页的信息，User record、Free space、Page directory这些部分为实际的行记录存储空间，大小是动态的。

File header：用来记录页的一些头信息。

Page header：用来记录数据页的状态信息。

Infimum和supremum：用来记录该页中存放键值的边界值，Infimum小鱼最小的键值，supremum大于最多的键值。

User record:是实际存储行记录的内容。

Free space：指的是空闲空间，同样也是个链表数据结构，在一条行记录被删除后，该空间会被添加到空闲链表中。

Page directory：存放的是记录的相对位置（页的相对位置）

4.4分区表

分区表是将一个表或者索引部分分解为多个更小，更容易管理的部分。分区的原理从逻辑上讲还是一张表或一个索引，但是在物理上已将该表或分成多个小表，每个分区是独立的对象，我们在查询的时候只需查询对应的分区即可。Mysql只支持横向分区，不支持纵向分区。横向分区是指将同一表中不同的行分配到不同的物理文件中；纵向分区是将不同的列放到不同的物理文件中。此外mysql分区是局部分区，即一个分区中即存放数据有存放索引数据；全局分区是指将数据存在各个分区中，但是索引数据存放在一个对象中。Mysql只支持局部分区。

SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema = 'test'；查看分区情况。

Mysql数据库支持的分区类型：

range分区：行数据基于一个给定的连续区间的列值被放入分区中。

list分区：和rang分区类似，只是不是一个连续的区间，是离散的。

hash分区：根据用户定义的表达式来进行分区，但是返回的值不能是复数。

key分区：根据mysql提供的哈希函数来进行分区。

不论使用上面哪种方法进行分区，如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。

4.4.1 Rang分区

Rang分区建表语句：create table t (

Id int

) engine=innodb

Partition by rang(id) (

Partition p0 values less than (100),

Patririon p1 values less than (200)

);

这些信息可以在information.partitions中查看，table_rows列反应了每个分区的记录数。Range在partition_mothod表示分区的类型。

SELECT

*

FROM

information_schema.`PARTITIONS`

WHERE table_schema = 'test'

AND table_name = 't_test_record'；

查看分区表执行计划explain partition + sql语句。如果以分区字段作为查询条件的话，只需要查询这个时间段的分区即可。但是要注意时间跨度只能在该分区内，如一个分区值只存储了16年的数据，查询条件是date>=’2016-01-01’ and date<’2017-01-01’的话，那么查询的时候只会扫描16和17年的分区，但是条件写成date>=’2016-01-01’ and date<=’2016-12-31’只会查询16年的分区，这样扫描表的数量会降低，速度会提高。

4.4.2 list分区

原理和range相似，只不过分区列的值是离散的，不连续。不同于range中定义的values less than，由于数据是离散的，指定分区为定值，建表语法如下：

create table t (

a int,

b int)

engine=innodb

partition by list (b) (

partition p0 values in (1,3,5,7,9),

partition p1 values in (0,2,4,6,8)

);

如果插入的值不在定义的列，mysql数据库会跑出table has no partition for values …。

4.4.3 Hash分区

Hash分区的目的是将数据库均匀地分布到预先定义的各个分区中，保证各分区的数据量大致是一样的。在range和list分区中，必须指定一个给定的列值；而在hash分区中，mysql自动完成这些工作，用户所要做的是基于将要进行hash分区的列值的表达式，以及要指定备份区的表将要被分割成的分区数量，如果没有Partitions 4指定分区数量，默认是1。建表语法如下：

Create table t (

A int,

B datetime

)engine=innodb

Partition by hash (YEAR(B))

Partitions 4;

4.4.4 Key分区

Key分区和hash分区类似，不同之处在于hash分区使用用户定义的函数进行分区，key分区使用mysql数据库提供的函数进行分区。分区的编号是通过2的幂算法得到的，而不是通过幂算法得到。

Create table t_key (

A int,

B datatime)

Engine=innodb

Partition by key (b)

Partition 4;

4.4.5 Columns分区

range、list、hash、key这四种的分区条件都是整数型，如果不是整数，应该通过函数将其转化成整数，而Columns分区可以直接使用非整数型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。

Columns分区支持的数据类型及建表语法：

所有的整型类型：INT SMALLLINT TINYINT BIGINT.

日期类型：DATE DATETIME

字符串类型：CHAR VARCHAR BINARY VARBINARY

Create table t_columns_range (

A int,

B datetime

)engine=innodb

Partition by range columns (B) (

Partition p0 values less than (‘2009-01-01’),

Partition p0 values less than (‘2010-01-01’)

);

Create table customer (

First_name varchar (25),

Last_name varchar (25),

City varchar (15)

)

Partition by list columns(city) (

Partition p1 values in (china , …),

Partition p1 values in (china , …)

);

如果mysql的版本高于5.5及5.5.的，可以使用range columns和list columns 代替之前的range和list分区。

4.4.6 子分区

子分区是在分区的基础上在进行分区，mysql允许range和list分区上进行hash和key分区。子分区建立注意问题：

每个子分区的数量必须相同。要在一个分区表的任何分区上使用subpartition来明确定义任何子分区，就必须定义所有的字分区。每个subpartition字句必须包含子分区的一个名字。子分区的名字必须是唯一的。

5 索引

创建一张表之后，数据库会在数据文件中会新增两个文件，表结构定义文件：表名.frm；数据文件：表名.ibd；其中索引文件和数据文件都是保存在.ibd文件中，保存在ibd文件中的索引有两种类型：聚集索引和非聚集索引。不论是哪种索引或数据在硬盘和内存中都是以B+树的形式存在。

5.1聚集索引

就是按照每张表的主键构造一颗【顺序】B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接；由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。

（1）所以通过主键查找的速度是最快的，先扫描到索引主键，再扫描主键对应的列值，一次IO就可以搞定；

（2）每张表都必须创建自增的主键，默认是随机生成8字节主键(隐含的)，且无序；Sql优化中消除性能瓶颈的最好方式就是向主键靠拢，主键不行可以考虑其他重复率低的索引；

5.2 非聚集索引

是按照索引(非主键)【顺序】创建B+树，但此时的叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉innodb存储引擎哪里可以找到与索引相对应的行数据。当通过辅助索引来寻找数据时，innodb存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

5.3覆盖索引

innodb支持覆盖索引，即从辅助(非聚集)索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远远小于聚集索引，因此可以减少大量的io操作(覆盖索引的特征：possible_keys列为NULL，但实际执行时优化器选择了其他索引)。

5.4不适用索引的集中情况

在很多情况下，开发人员发现自己写的sql中包含索引，但是执行起来还是很慢，通过查看执行计划并没有走索引，可能是以下原因引起的。

（1）以%开头的like查询不能使用B-TREE索引。

（2）数据类型隐式转换的时候不会使用索引，最典型的是如果索引字段是varchar型的，在where条件引用的时候没有使用单引号，会导致索引实现。

（3）复合索引情况下，where条件后不满足最左前缀的情况索引失效。

（4）如果mysql的优化器认为走全表扫面比走索引消耗内存更小的话也不会走索引，所以优化器选择走全表扫描。

（5）用or分割开的条件，如果or前面的列有索引，后面的列没有索引，那么涉及的所有不会被引用。因为or后面的条件没有索引，那么后面的查询肯定走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增减I/O访问，一次全表扫描就够了。

5.5查看执行计划

查看mysql中的sql语句的执行计划，我们只需在sqlyog中，在sql语句的最前面，加上explain单词，即可查看sql语句的执行计划，看执行计划是优化sql的第一步，我们通过查看执行计划，能清楚的看到哪张表走全表扫描，影响效率。这样我们可以有针对性地添加合适的索引。一般我们需要关注两个问题：

哪张表走全表扫描，是否有文件排序，使用到临时表之类；

哪张表虽然走索引，但这个索引是否合理，是否对其他sql也能充分使用(hospital_id,create_date)有些同事创建成了(create_date,hospital_id)，对于where条件后只有hospital的将无法走索引，有可能还会新增一个冗余的索引；

查看mysql中的sql语句的执行计划，我们只需在sqlyog中，在sql语句的最前面，加上explain单词，即可查看sql语句的执行计划：

（1）Id：select 查询的序列号；

（2）Select_type:select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

Simple：查询中不包含子查询或union；

Primary：查询中若包含任何复杂的子部分，最外层查询则被标记为primary；

Subquery：在SELECT或WHERE列表中包含了子查询，该子查询被标记为subquery；

Derived：在FROM列 标记为derived；

UNION RESULT：从UNION表获取结果的SELECT被标记为：UNION RESULT；

（3）Table列：输出的行所引用的表；

（4）Type列：显示的是访问类型，是sql优化里面一个非常重要的指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。

ALL:扫描全表；

Index：扫描全部索引树；

Range：扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询；

Ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即索引字段的值重复率较高（性别字段）；

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；

const, system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。system是const类型的特例，当查询的表只有一行的情况下， 使用system。

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。

（5）Possible_keys列：指出MySQL能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引,比如(hospital_id),(hospital_id,create_date)。

（6）key 例:显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

（7）key_len:显示MySQL决定使用的键长度。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。 注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

（8）ref:显示哪个字段或常数与key一起被使用。

（9）rows:这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的（只是一个大概的值）。

（10）Extra:包