MySQL自身对性能的影响

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL自身对性能的影响，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含24701字，纯文字阅读大概需要36分钟。

内容图文

想要了解MySQL自身对性能的影响，就需要先熟悉MySQL的体系结构和常用的存储引擎。MySQL并不完美，却足够灵活，能够适应高要求的环境，例如Web类应用。同时，MySQL既可以嵌入到应用程序中，也可以支持数据仓库、内容索引和部署软件、高可用的冗余系统、在线事务处理系统（OLTP）等各种应用类型。

MySQL最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理（Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。

MySQL的体系结构，第一层：

客户端层，这一层代表了各种可以通过MySQL连接协议连接到MySQL服务器的客户端，例如 PHP、JAVA、C API、ODBC、JDBC等 。可以看得出来这些并不是MySQL所独有的，大多数基于网络的C/S (客户端/服务器) 的工具或者服务都有类似的架构。这一层主要是：连接处理，授权认证和安全相关的一些功能。每个连接到MySQL客户端都会在服务器进程中拥有一个线程，这个连接的查询，只会在这个单独的线程中执行，也就是说的每个连接的查询只能用到一个CPU的核心

第二层：

第二层架构是MySQL比较有意思的部分。大多数MySQL的核心服务功能都在这一层，这一层包括了：查询缓存器、查询解析器、查询优化器等。以及MySQL所有的内置函数（例如，日期、时间、数学和加密函数等）。总之，所有跨存储引擎的功能都是在这一层来实现的。因为这一层称之为MySQL服务层，这一层中实现了与存储引擎无关的特性。那什么是与存储引擎无关的特性呢？比如说select语句，这个语句对所有的存储引擎来说，所要实现的功能都是一样的。（获取存储在文件中的数据，并根据我们的过滤条件进行过滤，然后把数据显示出来）所以这个select语句的功能是可以在MySQL服务层实现的，但具体如何从文件中获得我们所要查询的数据，则是由下一层存储引擎层来实现的。

第三层：

存储引擎层，这是区别其他数据库最大的地方，MySQL定义了一系列存储引擎的接口，所以我们也可以根据这些接口开发、定制出符合自己需求的存储引擎，例如Innodb一开始就是第三方公司所开发的存储引擎。存储引擎主要负责MySQL中数据的存储和提取，每个存储引擎都有它的优势和劣势。存储引擎API包含了几十个底层函数，但存储引擎不会去解析SQL（注：InnoDB是一个例外，它会解析外键定义，因为MySQL服务层本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单地响应上层服务器的请求。目前常用的存储引擎有：innodb、myisam、XtraDB、CSV、Memory等。由于MySQL这种插件式的存储引擎所带来的灵活性，使得我们可以很方便的根据不同的特点选择不同的存储引擎。注意，存储引擎是针对于表的而不是针对于库的，（理论上一个库里面不同的表可以使用不同的存储引擎，但不建议这么干）

MySQL体系结构图如下：

MySQL常用存储引擎之MyISAM

MyISAM是在mysql5.58之前的版本默认的存储引擎，MyISAM存储引擎将表存储在两个系统文件中，一个是数据文件以.MYD结尾，另一个是索引文件以.MYI结尾的。对于mysql所有的存储引擎都会有一个以.frm结尾的文件，这个文件是记录表的结构的文件，所以.MYD和.MYI文件是MyISAM存储引擎特有的。

MyISAM的特性：

特性一：

首先是并发性及锁级别，MyISAM使用的是表级锁而不是行级锁，这就意味着，对表中的数据修改时，要对整个表进行加锁。而对表中的数据进行读取的时候也需要对所有的表加共享锁，从这里可以看出使用MyISAM引擎的表读取和写入这两种操作是互斥的。MyISAM对读写混合的操作的并发性不是很好，如果是只读的操作，并发性还算可以接受，因为共享锁并不会阻塞共享锁。

特性二：

第二个特性需要从表损坏修复上来看，MyISAM支持由于意外关闭而损坏MyISAM表进行检查和修复操作，但这里指的并不是事务的恢复。因为myisam并不是支持事务的存储引擎，所以它也不可能有事务恢复的相关日志。需要注意的是对MyISAM表进行修复会有可能造成数据的丢失，使用 check table tablename 命令可以对表进行检查，使用 repair table tablename 命令则可以对有问题的表进行修复。

这里使用一个简单的例子演示一下这个特性，在数据库中新建一个myIsam表，存储引擎为myIsam，建表的SQL语句如下：

create table myIsam(id int,c1 varchar(10))engine=myisam;

建表完成后，在相应的MySQL数据存储目录中会出现myIsam.frm和myIsam.MYD以及myIsam.MYI这三个文件，其中myIsam.frm存放的是表的结构信息，myIsam.MYD文件存储的是myIsam表的数据信息，myIsam.MYI则存放的是索引的信息：

这时候我们使用check table myIsam;检查这个表，然后使用repair table myIsam;修复表，Msg_text 显示ok代表没有问题：

如果发现有损坏的表，可以使用repair table myIsam;来进行修复。除此之外，mysql还提供了一个命令行的工具：myisamchk 。这个工具也可以对myisam表进行修复，但是需要注意的是：如果使用这个命令行工具来修复myisam表的话，要先把mysql服务先停掉。因为如果在mysql运行的时候使用这个工具对表进行修复，有可能对表进行一些损坏。

特性三：

MyISAM支持的索引，myisam支持全文索引，而且是在mysql5.7之前版本中，唯一原生就是支持全文索引的官方存储引擎。另外myisam表还支持对 test、belog等字段建立前500个字符的这种前缀索引。

特性四：

MyISAM是支持表压缩的，如果myisam是一张很大的只读表的时候，也就是说在表创建完并导入数据后，就不会在对表进行任何操作了，那么我们就可以对这样的表进行压缩操作，可以减少磁盘I/O。如果对一张表压缩，可以使用myisampack命令来对表进行压缩表中数据。对表中的数据是独立进行压缩的，在读取单数据的时候呢，不必对整个表来进行解压。

使用如下命令，即可对一张表进行压缩：

myisampack -b myIsam.MYI

需要注意的是，对压缩后的表只能进行读操作，不能进行写操作，如果此时向myIsam表插入数据就会报错：

MyISAM存储引擎的限制：

在 mysql5.0 版本之前默认单表的最大存储大小是4G，如果想要存储超过4G的数据的时候，需要在建表的是指定MAX_Rows 和AVG_ROW_LENGTH参数的值，这两个参数的值相乘的大小就是表存储的最大的大小。如果对已存在的大表修改这两个参数，等于对表进行重建，会花费一些时间。在Mysql5.0之后单表最大支持256TB

MyISAM适用的场景：

• 非事务型应用，myisam 本身是非事务存储引擎，是不支持事务的
• myisam支持压缩，所以适合只读类的应用
• 在mysql5.7之前myisam是唯一个支持空间函数的一个存储引擎，所以也适合空间类应用

MySQL常用存储引擎之Innodb

在mysql5.58之后的版本默认存储引擎是Innodb，代替了之前的myisam。与myisam不同的是，Innodb是事务存储引擎，Innodb是支持事务特性的，适合大部分的应用场景，也更适合处理大量的小事务。

Innodb与myisam另一个区别是存储方式的不同，Innodb有自己的表空间的概念，表空间数据是存储在表空间之中的，具体存放到哪个表空间由 innodb_file_per_table 这个参数来决定。如果这个参数为ON，则会为每个Innodb表建立一个独立的表空间，以tablename.ibd为扩展名的文件。如果这个参数为OFF则会把数据存放到系统共享表空间，也就是ibdataX 这个空间里，X代表一个从 1 开始的整数。

我们可以使用如下SQL语句来查看该参数的值：

show variables like ‘innodb_file_per_table‘;

执行结果如下：

接下来新建一个myinnodb表，看看这个表是如何存储的，建表的SQL语句如下：

create table myinnodb(id int, c1 varchar(10)) engine=‘innodb‘;

可以看到有两个文件一个是myinnodb.frm、myinnodb.ibd，其中.frm文件是记录表结构的，而.idb文件就是表数据实际存储的地方了：

注：MySQL8.x 版本有所改变，只会存在.ibd文件，不会有.frm文件，具体可以查阅官方文档

我们可以使用如下命令更改表的存储方式，例如改为存储在系统表空间中：

set global innodb_file_per_table=off;

接下来新建一个myinnodb_g表，建表的SQL语句如下：

create table myinnodb_g(id int, c1 varchar(10)) engine=‘innodb‘;

此时可以看到只有一个myinnodb_g.frm文件，没有.idb文件了：

关于系统表空间和独立表空间如何选择：

1.对表空间的管理方式比较：mysql5.6之前的Innodb的innodb_file_per_table参数的默认值为off，所以所有的数据都会存储在系统表空间中。在一个繁忙的系统中，会发现系统表空间会不断的增长，一旦我们的磁盘空间出现不足，需要释放一些磁盘空间时，就不得不删除大量的无效数据及一些无关紧要的日志数据，但删除完之后会发现系统表空间并不会缩小。而收缩系统表空间的唯一方法就是把整个数据库中的所有Innodb表导出后，删除Innodb相关的表空间文件，接着重启MySQL进行表空间的重建，然后再将之前导出的数据再次导入进去。想也知道这个过程是麻烦又耗时的，我们不可能在生产环境中这么干。由于系统表空间无法简单的收缩文件大小，会造成大量空间的浪费，并且产生大量的磁盘碎片，从而降低系统的性能。如果使用独立表空间的话这个问题就很好解决了，我们对表数据进行清理之后，可以直接通过optimize table命令来收缩系统文件，并且不需要重启MySQL，也不会影响数据库的访问。从这一点来看，使用独立表空间显然要比系统表空间要好得多。

2.然后我们再来看看使用系统表空间对I/O会有什么影响：对于系统表空间来说，因为只有一个文件，所以多个表空间进行数据刷新的时候，实际上在文件系统上是顺序进行的，这样就会产生大量的I/O瓶颈。而独立的表空间来说，每个表都有自己独立的表空间文件。所以在数据写入的时候，可以同时向多个文件刷新数据。结论：对于频繁写入的操作，不适合使用系统表空间存储数据，最好用独立表空间进行存储。建议，在Innodb下使用独立表空间进行数据存储。

由于版本原因，可能有些数据存储在系统表空间中，这里简单说明把原来存在于系统表空间中的表转移到独立表空间中的方法及步骤：

1. 使用mysqldump导出所有数据库表数据
2. 停止MySQL服务，修改innodb_file_per_table参数的值为on，并删除Innodb相关的表空间文件
3. 重启MySQL服务，重建Innodb系统表空间
4. 重新导入数据

Innodb存储引擎的特性：

特性一：

Innodb是一种事务性存储引擎，完全支持事务的ACID特性，也就是原子性、一致性、隔离性以及持久性。Innodb存储引擎为了实现原子性、一致性以及持久性这三个特性，它使用了两个特殊的日志类型：Redo Log（重做日志） 和 Undo Log（回滚日志）。

Redo Log主要实现事务的持久性，Redo Log主要由两部分组成，一是存在于内存中的重做日志缓冲区，该缓冲区大小由innodb_log_buffer_size参数来决定。二是重做日志文件，即ib_logfile开头的文件。

Undo Log主要作用是用于帮助未提交事务进行回滚和实现MVCC多版本并发控制，所以Redo Log中存储的是已提交的事务，Undo Log存储的则是未提交的事务。因此我们对Innodb表中的数据进行修改时，不仅会产生Redo Log，还会产生一定数量的Undo Log。Redo Log基本上是顺序写入的，因为在数据库运行时不需要对Redo Log进行读取操作，而Undo Log是需要进行随机读写的，所以我们有条件的话可以把Undo Log放在ssd这种随机读写性能高的磁盘上以提高性能

查看重做日志缓冲区大小，单位为字节：

重做日志文件的数量由innodb_log_files_in_group参数决定：

特性二：

Innodb支持行级锁，行级锁是在写操作时，我们所需要锁定的资源更少，这样我们支持的并发就会更多。需要注意的是Innodb的行级锁是由存储引擎层实现的，MySQL服务层是完全不了解存储引擎中锁的实现方式的。

什么是锁：锁是数据库系统区别于文件系统的重要特性，锁的作用主要是管理共享资源的并发访问。例如锁可以控制当一个用户向邮箱中投递邮件的时候，另一个用户会阻塞，无法向相同的邮件末尾写入邮件，这样就不会导致邮件的内容出现混淆。锁的另一个特性就是实现事务的隔离性，对于未提交的事物，锁定的数据，是无法被其他事务所查询到的。

锁的常见分类：共享锁（也称读锁），独占锁（也称写锁），从名字中可以看出读锁是共享的，也就是说相互不会被阻塞的，多个线程可以在同一时间读取同一资源，而不相互干扰。写锁是独占的，也就是排他的，一个写锁会阻塞其他的写锁或读锁。这是处于数据完整性的考虑，只有这样才能保证，在给定的时间里，只有一个线程能执行写入，并防止其他用户读取正在写入的同一资源，也就是前面所说的实现了事务的隔离性。

写锁不兼容写锁和读锁，而读锁兼容读锁。需要注意的是，对Innodb来说读锁和写锁都是行锁，所谓兼容性是指同一行记录的兼容性情况。另外一点，实际上在Innodb里锁的兼容关系并不是如此，由于Innodb实现的锁利用了Undo Log，所以实际使用时读锁和写锁是兼容的。

关于锁的粒度含义：

锁的粒度含义，就是如果加锁资源的最小单位，比如在行上加锁，最小单位就是行，这个锁就称为行级锁。如果锁的最小单位是数据页，我们就称为页级锁。同理如果锁的最小单位是表的话，这个锁就是表级锁。通常提高共享资源并发性的方式就是让锁定义的对象尽可能的小，最理想的方式就是对修改的数据进行精确的锁定。任何时候在给定的资源上锁定的数据越少，系统的并发性就会越高，只要相互之间不产生阻塞就可以

mysql所支持的两种锁的粒度，表级锁和行级锁：

表级锁开销小，并发性低，表锁会在加锁时候锁定整张表，当用户对表进行写操作的时候，要先进行解锁，这时候就会阻塞其他用户对表的读写操作，只有没有写锁的时候，其他读取的用户才能获得读锁，读锁之前说的是不会相互堵塞的，表级锁通常是在mysql服务器层所实现的。

行级锁可以最大程度的支持并发处理，同时锁的开销也比表级锁开销要大，所以它是并发性高，Innodb实现了行级锁，行级锁是在mysql存储引擎中实现的，而不在mysql服务器中实现，

锁的另外两个概念，阻塞和死锁：

阻塞是因为不同锁之间兼容性的关系，在有些时刻，一个事务的锁在需要等待另一个事务释放它所占用的资源，这就形成了阻塞。比如前面所提到的表级锁，如果当第一个连接，在一张Innodb表上加了排他锁，此时第二个连接想要在该表上加共享锁的话，就要等第一个连接释放排他锁，这样第一个连接就阻塞了第二个连接。阻塞是为了确保事务可以并发且可以正常的运行。但是如果一个系统出现大量的阻塞就说明系统出现了问题，也许是在一个被频繁更新的表上出现了慢查询，或是一个频繁访问的资源加上了排他锁。阻塞过多的时候可会令数据库连接大量的堆积，从而占用大量的系统资源，使得系统性能整体下降。

死锁是两个或两个以上事务在执行过程中，相互占用了对方等待的资源而产生的异常。从定义中可以看到，处在阻塞中的多个事务，阻塞事务占用了被等待阻塞事务的资源。而死锁呢，则是多个事务，相互之间互相占用对方等待的资源，这是阻塞和死锁的最大不同之处。另外一个不同点就是死锁数据库会自动发现，并且在多个死锁的事务中，找到一个资源占用最少的事务来进行回滚操作，这样就可以是其他事务正常运行了。所以说死锁是可以由系统自动处理的，如果只有少量的死锁并不会对系统造成什么样的影响，只要在应用程序中发现死锁并进行处理就可以。但是如果一个系统频繁的出现大量的死锁就需要留意了，大多数情况下可以通过在多个事务中按相同顺序访问所需要资源来解决死锁问题，也可以通过增加相关索引来解决。

关于锁的一些实际操作，可以参考我早期写的两篇文章：

• MySQL-锁
• MySQL-锁02

Innodb引擎与其他大部分引擎不同的是，Innodb还提供了一个状态监查监控工具，我们可以使用如下SQL语句查看监控信息：

show engine innodb status;

注：两次采样之间至少间隔30s，这样才能保证采集到足够的数据

执行该命令，输出的内容大致如下，不同的版本可能输出的内容不太一样，我这里使用的是5.7.18版本：

=====================================
2018-10-10 11:59:55 0x7f755c9c5700 INNODB MONITOR OUTPUT # 当前日期和时间
=====================================
Per second averages calculated from the last 57 seconds  # 这行显示的是计算出这一平均值的时间间隔，即自上次输出以来的时间，或者是距上次内部复位的时长
-----------------
BACKGROUND THREAD  # backgroup 线程信息
-----------------
srv_master_thread loops: 61445 srv_active, 0 srv_shutdown, 5196496 srv_idle
srv_master_thread log flush and writes: 5257941
----------
SEMAPHORES  # 如果有高并发的工作负载，你就要关注下接下来的段（SEMAPHORES信号量）,它包含了两种数据：事件计数器以及可选的当前等待线程的列表，如果有性能上的瓶颈，可以使用这些信息来找出瓶颈，不幸的是，想知道怎么使用这些信息还是有一点复杂
----------
OS WAIT ARRAY INFO: reservation count 219819 # 这行给出了关于操作系统等待数组的信息，它是一个插槽数组，innodb在数组里为信号量保留了一些插槽，操作系统用这些信号量给线程发送信号，使线程可以继续运行，以完成它们等着做的事情，这一行还显示出innodb使用了多少次操作系统的等待：保留统计（reservation count）显示了innodb分配插槽的频度，而信号计数（signal count）衡量的是线程通过数组得到信号的频度，操作系统的等待相对于空转等待（spin wait）要昂贵些。
OS WAIT ARRAY INFO: signal count 219805  # 进行OS WAIT线程，接收到多少次信号(single)被唤醒，如果这个single数值越大，几十万或者几百万，可能是很多I/0等待或者是InnoDB争用问题(关于争用问题可能与OS调度有关，可以尝试减少innodb_thread_concurrency参数)
RW-shared spins 0, rounds 168443, OS waits 84271  # RW-shared 共享锁，
RW-excl spins 0, rounds 180120, OS waits 1119  # RW-excl 排他锁
RW-sx spins 60, rounds 6000, OS waits 50
Spin rounds per wait: 168443.00 RW-shared, 180120.00 RW-excl, 100.00 RW-sx
------------
TRANSACTIONS  # 包含了InnoDB事务(transaction)的统计信息
------------
Trx id counter 264168  # 当前的transaction id ,这是个系统变量，随着每次新的transaction产生而增加
Purge done for trx‘s n:o < 264167 undo n:o < 0 state: running but idle  # 正在进行清空的操作操作的transaction ID，Purge的原则就是记录没有被其它事务继续使用了
History list length 10  # 记录了undo spaces 内unpurged 的事务个数
LIST OF TRANSACTIONS FOR EACH SESSION:   # 当前活跃事务列表
---TRANSACTION 421617178700976, not started  # 每个事务的第一行以事务的ID和状态开始，not started表示这个事务已经提交并且没有再发起影响事务的语句，可能刚好空闲
0 lock struct(s), heap size 1136, 0 row lock(s) # 该事务锁定结构的数目和堆大小以及锁定的行数，堆的大小指的是为了持有这些行锁而占用的内存大小
---TRANSACTION 421617178702800, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178697328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178701888, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178700064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178699152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178698240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178695504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178704624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421617178696416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O  # 显示了I/O  Helper thread d的状态，包含一些统计信息
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread) # insert buffer thread
I/O thread 1 state: waiting for i/o request (log thread) # log thread
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread) # 以上为默认的4个read thread
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread) # 以上为默认的4个write thread
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,  # 读线程和写线程挂起操作的数目等，aio的意思是异步I/O
 ibuf aio reads:, log i/o‘s:, sync i/o‘s:   # insert buffer thread挂起的fsync()操作数目等
Pending flushes (fsync) log: 0; buffer pool: 0  # log thread挂起的fsync()操作数目等
469 OS file reads, 1083592 OS file writes, 570823 OS fsyncs  # 这行显示了读，写和fsync()调用执行的数目，在你的机器环境负载下这些绝对值可能会有所不同，因此更重要的是监控它们过去一段时间内是如何改变的
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s  # 这行显示了当前被挂起的读和写操作数

注：三行挂起读写线程、缓冲池线程、日志线程的统计信息的值是检测I/O受限的应用的一个好方法，如果这些I/O大部分有挂起操作，那么负载可能I/O受限。在linux系统下使用参数：innodb_read_io_threads和innodb_write_io_threads两个变量来配置读写线程的数量，默认为各4个线程。
insert buffer thread：负责插入缓冲合并，如：记录被从插入缓冲合并到表空间中
log thread：负责异步刷事务日志
read thread：执行预读操作以尝试预先读取innodb预感需要的数据
write thread：刷新脏页缓冲

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX  # 这部分显示了insert buffer和adaptive hash index两个部分的结构的状态
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges  # 这行显示了关于size（size 12代表了已经合并记录页的数量）、free list（代表了插入缓冲中空闲列表长度）和seg size大小（seg size 27572显示了当前insert buffer的长度，大小为27572*16K=440M左右）的信息。18074934 merges代表合并插入的次数
merged operations:  # 这个标签下的一行信息insert，delete mark，delete分别表示merge操作合并了多少个insert buffer，delete buffer，purge buffer
 insert 0, delete mark 0, delete 0  # insert 插入的记录数，delete mark 打上的标记,delete 删除的次数
discarded operations:   # 这个标签下的一行信息表示当change buffer发生merge时表已经被删除了，就不需要再将记录合并到辅助索引中了
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 13 buffer(s)  # 这行显示了自使用哈希索引的状态，其中，Hash table size 87709057表示AHI的大小，node heap has 10228 buffer(s)表示AHI的使用情况
Hash table size 34679, node heap has 8 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 13 buffer(s)
Hash table size 34679, node heap has 14 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
11.61 hash searches/s, 0.00 non-hash searches/s   # 这行显示了在头部第1部分提及的时间内Innodb每秒完成了多少哈希索引操作，1741.05 hash searches/s表示每秒使用AHI搜索的情况，539.48 non-hash searches/s表示每秒没有使用AHI搜索的情况(因为哈希索引只能用于等值查询，而范围查询，模糊查询是不能使用哈希索引的。)，通过hash searches: non-hash searches的比例大概可以了解使用哈希索引后的效率，哈希索引查找与非哈希索引查找的比例仅供参考，自适应哈希索引无法配置，但是可以通过innodb_adaptive_hash_index=ON|OFF参数来选择是否需要这个特性。
---
LOG  # 这部分显示了关于innodb事务日志（重做日志）子系统的统计
---
Log sequence number 113764958  # 这行显示了当前最新数据产生的日志序列号
Log flushed up to   113764958  # 这行显示了日志已经刷新到哪个位置了（已经落盘到事务日志中的日志序列号）
Pages flushed up to 113764958  
Last checkpoint at  113764949  # 这行显示了上一次检查点的位置（一个检查点表示一个数据和日志文件都处于一致状态的时刻，并且能用于恢复数据），如果上一次检查点落后与上一行太多，并且差异接近于事务日志文件的大小，Innodb会触发“疯狂刷”，这对性能而言非常糟糕。
0 pending log flushes, 0 pending chkp writes  # 这行显示了当前挂起的日志读写操作，可以将这行的值与第7部分FILE I/O对应的值做比较，以了解你的I/O有多少是由于日志系统引起的。
350037 log i/o‘s done, 0.00 log i/o‘s/second  # 这行显示了日志操作的统计和每秒日志I/O数，可以将这行的值与第7部分FILE I/O对应的值做比较，以了解你的I/O有多少是由于日志系统引起的。
---------------------- 
BUFFER POOL AND MEMORY  # 这部分显示了关于innodb缓冲池及其如何使用内存的统计
----------------------
Total large memory allocated 137428992  # 这行显示了由innodb分配的总内存，以及其中多少是额外内存池分配，额外内存池仅分配了其中很小一部分内存，由内部内存分配器分配，现在的innodb版本一般使用操作系统的内存分配器，但老版本使用自己的，这是由于在那个时代有些操作系统并未提供一个非常好的内存分配实现。
Dictionary memory allocated 1401012
Buffer pool size   8192  # 从这行开始的下面4行显示缓冲池度量值，以页为单位，度量值有总的缓冲池大小，空闲页数，分配用来存储数据库页的页数，以及脏数据库页数。这行显示了缓冲池总共有多少个页，即即2705015*16K，共有43G的缓冲池
Free buffers       6147  # 这行显示了缓冲池空闲页数
Database pages     1986  # 这行显示了分配用来存储数据库页的页数，即，表示LRU列表中页的数量,包含young sublist和old sublist
Old database pages 713   # 这行显示了LRU中的old sublist部分页的数量
Modified db pages  0     # 这行显示脏数据库页数
Pending reads      0     # 这行显示了挂起读的数量
Pending writes: LRU 0, flush list 0, single page 0  # 这行显示了挂起写的数量
# 注意，这里挂起的读和写操作并不与FILE I/O部分的值匹配，因为Innodb可能合并许多的逻辑读写操作到一个物理I/O操作中，LRU代表最近使用到的被挂起数量，它是通过冲刷缓冲中不经常使用的页来释放空间以供给经常使用的页的一种方法，冲刷列表flush list存放着检查点处理需要冲刷的旧页被挂起的数量，单页single page被挂起的数量（single page写是独立的页面写，不会被合并)。
Pages made young 0, not young 0   # 这行显示了LRU列表中页移动到LRU首部的次数，因为该服务器在运行阶段改变没有达到innodb_old_blocks_time阀值的值，因此not young为0
0.00 youngs/s, 0.00 non-youngs/s  # 表示每秒young和non-youngs这两类操作的次数
Pages read 428, created 1558, written 680621  # 这行显示了innodb被读取，创建，写入了多少页，读/写页的值是指的从磁盘读到缓冲池的数据，或者从缓冲池写到磁盘中的数据，创建页指的是innodb在缓冲池中分配但没有从数据文件中读取内容的页，因为它并不关心内容是什么（如，它们可能属于一个已经被删除的表）
0.00 reads/s, 0.00 creates/s, 0.00 writes/s  # 这行显示了对应上面一行的每秒read，create，write的页数
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000  # 这行显示了缓冲池的命中率，它用来衡量innodb在缓冲池中查找到所需页的比例，它度量自上次Innodb状态输出后到本次输出这段时间内的命中率，因此，如果服务器自那以后一直很安静，你将会看到No buffer pool page gets since the last printout。它对于度量缓存池的大小并没有用处。
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s   # 这行显示了页面预读，随机预读的每秒页数
LRU len: 1986, unzip_LRU len: 0   # innodb1.0.x开始支持压缩页的功能，将原来16K的页压缩为1K，2K，4K，8K，而由于页的大小发生了变化，LRU列表也有了些改变，对于非16K的页，是通过unzip_LRU列表进行管理的，可以看到unzip_LRU len为0表示没有使用压缩页.
I/O sum[0]:cur[0], unzip sum[0]:cur[0]  # 统计信息及解压信息
--------------
ROW OPERATIONS  # 这部分显示了其他各项的innodb统计
--------------
0 queries inside InnoDB, 0 queries in queue  # 这行显示了innodb内核内有多少个线程，队列中有多少个线程，队列中的查询是innodb为限制并发执行的线程数量而不运行进入内核的线程。查询在进入队列之前会休眠等待。
0 read views open inside InnoDB  # 这行显示了有多少打开的innodb读视图，读视图是包含事务开始点的数据库内容的MVCC快照，你可以看看某特定事务在第6部分TRANSACTIONS是否有读视图
Process ID=32728, Main thread ID=140141416933120, state: sleeping  # 这行显示了内核的主线程状态
Number of rows inserted 115492, updated 164886, deleted 1698, read 1351954675  # 这行显示了多少行被插入，更新和删除，读取
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 34.46 reads/s  # 这行显示了对应上面一行的每秒平均值，如果想查看innodb有多少工作量在进行，那么这两行是很好的参考值
----------------------------
END OF INNODB MONITOR OUTPUT  # 信息结束标记，要注意了，如果看不到这行输出，可能是有大量事务或者是有一个大的死锁截断了输出信息
============================

MySQL自身对性能的影响

标签：type   src   比例   不同   dict   关心   接收   当前日期   nal   

本文系统来源：http://blog.51cto.com/zero01/2298166

内容总结

以上是互联网集市为您收集整理的MySQL自身对性能的影响全部内容，希望文章能够帮你解决MySQL自身对性能的影响所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端