php获取uniuxtime技巧_MySql数据库结构介绍及优化

1.1Mysql组件

Mysql紧张体系构造紧张由以下几个组件组成：连接池组件、管理做事和工具组件、SQL接口组件、剖析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件、连接池组件。

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

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

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

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

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

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

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

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

连接mysql的操作是一个连接进程和mysql数据库之间的通讯，下面先容下连接mysql的几种办法：

TCP/IP 套接办法是mysql数据库在任何平台下都供应的连接办法，是利用最多的一张办法，这种办法在TCP/IP 连接上建立一个基于网络的连接要求，一样平常情形是客户端在一台做事器上，而mysql实例在其余一台做事器上，这两台机器通过一个TCP/IP 网络连接。

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

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

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

由于我们现在用的存储引擎大多是innodb，下面着重先容一下innodb引擎。
Innodb体系构造是由一组后台进程和内存池组成。
个中后台线程的紧张浸染是卖力刷新内存池中的数据，担保缓冲池中的内存缓存的是最近的数据，此外将已修正的数据文件刷新到磁盘文件中，同时担保在数据库发生非常的情形下innodb能规复到正常的运行状态。
紧张有四种进程：Master Thread、IO Thread、Purge Thread、Page Cleaner Thread。

图1Innodb体系构造

紧张卖力将缓冲池中的数据异步刷新到磁盘，担保数据的同等性(包括脏页、合并插入缓冲)、undo页回收等）页=16K，利用下面命令查看:show engine innodb status。

紧张卖力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。

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

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

图2 innodb存储引擎内存构造

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中是将内存按照哈希值均匀分配到多个实例中。

缓冲池中存放各种类型的页，该段区域便是对缓冲池按照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管理。

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

刷新频率：

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

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

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

4、额外内存：

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

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

【办理的问题】：

1，缩短数据库的规复韶光；

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

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

4，刷脏页，每次刷多少，从哪里取脏页，以及什么韶光触发checkpoint（有很繁芜的算法）。

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

【每一秒】：

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

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

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

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

【每十秒】：

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

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

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

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

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

【参数解释】：

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线程，进一步提高并发性。

（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，用来掌握是否启用该特性，对传统机器硬盘建议启用。
固态硬盘可关闭。

【关闭】：

【参数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：不进行前滚操作。

（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 = 21024102410，重启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)检讨的事情。

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

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

缺点日志是mysql最主要的日志之一，它记录了当mysqld启动和停滞时，以及做事器在运行过程中发生任何严重缺点时的干系信息。
当数据库涌现任何故障导致无法正常利用时可以查看此日志。
（命名host_name.err）一样平常存在datadir目录下。

二进制文件记录了所有的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权限的客户端可以通过此语句禁止将自己的语句添加到二进制文件中。

查询日志记录了客户端所有的语句，而二进制日志不包含只查询数据的语句。
紧张的参数有以下几个：--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查看，对付日志访问频繁的系统，不建议开启这个参数，会对系统造成较大影响。

慢查询紧张记录所有实行韶光查过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(可以精确到奇妙)

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

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

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

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

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，这些单独的表空间文件仅存储该表的数据，索引等信息，其他的信息还是存放在默认的表空间中。

他是记录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指定了日志组所在的路径。
二进制文件和重做日志的差异：

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

本部分内容紧张剖析表的物理存储特色，即数据在表中如何组织和存放的，大略的说，表是关于特天命据的凑集，是关系型数据库的核心。

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

SELECT CHANNEL_ID,rowid FROM c_channel_user_login;

SELECT ID,_rowid FROM sys_create_partition_log;

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

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

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

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

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

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

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

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

SHOW TABLE STATUS LIKE 'c_channel_user_login';

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

变长字段长度列表

NULL标志位

记录头信息

列1数据

列2数据

…

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行记录格式存储办法如下：

字段长度偏移列表

记录头信息

列1数据

列2数据

列3数据

…

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这类的大工具列表类型的存储会把数据存放在数据页之外。

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

File header（头文件），38

Page header（页头），56

Infimum和supremum，

User record （行记录）

Free space（空闲空间）

Page directory （页目录）

File trailer （尾文件）8

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：存放的是记录的相对位置（页的相对位置）

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

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

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

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

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

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

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

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

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年的分区，这样扫描表的数量会降落，速率会提高。

事理和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 …。

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;

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

Create table t_key (

A int,

B datatime)

Engine=innodb

Partition by key (b)

Partition 4;

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分区。

子分区是在分区的根本上在进行分区，mysql许可range和list分区上进行hash和key分区。
子分区建立把稳问题：

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

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

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

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

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

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

在很多情形下，开拓职员创造自己写的sql中包含索引，但是实行起来还是很慢，通过查看实行操持并没有走索引，可能因此下缘故原由引起的。

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

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

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

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

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

查看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:包含不适宜在其他列中显示但十分主要的额外信息。

Only index，这意味着信息只用索引树中的信息检索出的，这比扫描全体表要快。

using where是利用上了where限定,如果查询未能利用索引，Using where的浸染只是提醒我们MySQL将用where子句来过滤结果集。

impossible where 表示用不着where，一样平常便是没查出来啥;

Using filesort（MySQL中无法利用索引完成的排序操作称为“文件排序”）当我们试图对一个没有索引的字段进行排序时，便是filesoft。
它跟文件没有任何关系，实际上是内部的一个快速排序;

Using temporary（表示MySQL须要利用临时表来存储结果集，常见于排序和分组查询），利用filesort和temporary的话会很吃力，WHERE和ORDER BY的索引常常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就一定会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

MRR，前面已经解释过。

锁是打算机折衷多个进程或线程并发访问某一资源的机制，在数据库中除了打算机资源（cpu、内存、IO）的争用外，数据也是一种供用户共享的资源，如何担保数据并发访问的同等性，有效性是所有数据库必须办理的一个问题，锁冲突也是影响数据库并发访问的一个资源。
Mysql锁紧张分为以下几种：

表级锁：开销小，加锁快；不会涌现去世锁，锁定粒度大，发生锁冲突的概率较大，并发度较低。

页面锁：开销和加锁韶光位于表级锁和行级锁之间，会涌现去世锁，锁定粒度介于表级锁和行级锁之间。

行级锁：开销大，加锁慢，不会涌现去世锁，锁定粒度最小，发生锁冲突概率较低，并也高。

共享锁和排他锁，如果一个事务已经得到了某一行的共享锁，那么另一个事务也可以得到该行数据的共享锁，这是由于读并不会改变行的数据，这种情形称为锁兼容；但如果有其他事务想要得到某一行的排他锁，则必须等待其他的事务开释该行的共享锁才能得到，这个称为锁的不兼容。

查询innodb的锁情形可以在information.schema下的innodb_locks表中查看。
该表中把稳有以下几个参数：

Lock_id：锁ID

lock_trx_id:事务ID

lock_mode:锁模式

lock_type：锁的类型，表锁还是行锁

lock_table：须要锁的表

lock_index：锁住的索引

lock_space：锁工具的space id

lock_page：事务锁定页的数量，若是表锁，显示为null

lock_rec：事务锁定行的数量。
若是表锁，显示null

lock_data：事务锁定记录的主键值，若是表锁，显示为null；

innodb_lock_waits只要参数

requesting_trx_id：申请锁资源的事务id

requesting_lock_id：申请的锁的id

select from information.schema.innodb_lock_wiats;通过上面的语句可以查询到哪个事务壅塞了其余一个事务。
也可以利用下面的sql查询更为详细的信息：

select r.trx_id wait_trx_id,

r.trx_mysql_thread_id waiting_thread,

r.trx_query waiting_query,

b.trx_id blocking_trx_thread,

b.tx_mysql_thread_id blocking_thread,

b.trx_query blocking_query

from information.scchema.innodb_lock_waits w

inner join information.schema.innodb_trx b

on b.trx_id=w.blocking_trx_id

inner join information.schema.innodb_trx r

on r.trx_id=w.requesting_trx_id;

同等性的非锁定读是指innodb存储引擎通过多版本掌握的方法来读取当前实施韶光数据中的行数据。
如果读取的行正在实行update或delete操作，这时读取操作不会等待行上的锁开释，而是会读取其余一个快照。
快照数据是指该行的之前的版本是通过undo段来完成的。
而undo用来在事务回滚数据，因此快照本身没有额外的开销，读取快照是不须要上锁的，由于事务不会对历史的数据进行变动操作。

在事务隔离级别read committed 和repeatable read下，innodb存储引擎利用非锁定的同等性读，但在read committed事务隔离级别下。

即事务隔离级别为repeatable read模式下，innodb存储引擎的select操作利用同等性非锁定读。
但是在某些情形下，用户须要显示的对数据库读取操作进行枷锁以担保数据的同等性，而这哀求数据库支持加锁语句，纵然是对付select的制度操作。
这样的话随意马虎产生等待。

在innodb存储引擎的内存构造中，对每个包含自增长值得表都会有一个自增长计数器。
当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，插入操作会依据这个自增长的计数器的值加1授予自增长序列，这个实现办法称为自动增长锁。
这是一种分外的锁机制，为了提高插入的性能，锁不是在一个事务完成后开释的，而是在完成对自增长值插入的sql语句后开释的。

外键紧张用于引用完全性的约束检讨，在innodb存储引擎中，对付一个外加列，如果没有显示对这个列添加索引，innodb存储引擎自动对其加一个索引，这样会避免产生表锁。