项目6 监控、测试并优化 MySQL 性能

数据库运维与管理项目6监控、测试并优化MySQL性能教学目标知识目标熟悉MySQL性能监控常用指标掌握MySQL性能监控常用工具的用法掌握MySQL性能测试工具的使用方法掌握MySQL性能调优的概念和基本方法能力目标能使用MONyog等工具监控MySQL服务器能基于性能参数对MySQL数据库的读写性能进行简单调优能完成对MySQL数据库的压力测试能基于explain工具分析并优化SQL查询素养目标培养精益求精的工匠意识培养软件即服务意识加强规范操作意识思政目标民族自豪感国产软件振兴的使命担当项目6监控、测试并优化MySQL性能项目场景：夏天来临，天天电器商场的空调在线销售量及售后服务访问量逐渐增加，随着该商场的空调大促销活动的开始，有客户反馈购买空调商品的页面经常卡顿，甚至在申请提交空调服务时，偶尔会出现提交失败的情况。业务系统管理员也反馈在系统中按照电话号码尾号进行模糊查询时，会出现假死机状态。信息部门接到任务，要求对系统运行进行实时监控，排查原因，优化访问性能，改善用户的体验。项目知识导入一、性能监控1．性能监控常用指标（1）QPS（QueriesPerSecond，每秒查询数）是指MySQL服务器每秒执行的查询总量，通过查询数目状态值（Queries）每秒内的变化量来近似表示，即QPS=(Queries2-Queries1)/(Uptime2-Uptime1)（2）TPS（TransactionsPerSecond，每秒事务数）是指MySQLServer每秒处理的事务数量。事务数TC=Com_insert+Com_delete+Com_update。其中，Com_insert为insert语句执行的次数；Com_update为update语句执行的次数；Com_delete为delete语句执行的次数。TPS=(TC2-TC1)/(Uptime2-Uptime1)（3）线程状态Threads_running:当前正处于激活状态的线程个数，可以理解为当前并发数。threads_connected：当前连接的线程的个数，即当前打开的连接数。项目知识导入（4）流量状态Bytes_received：从所有客户端接收到的字节数。Bytes_sent：发送给所有客户端的字节数。（5）InnoDB引擎文件读写次数Innodb_data_reads：InnoDB引擎从文件中读取的次数。

Innodb_data_writes：InnoDB引擎从文件中写入的次数。

Innodb_data_fsyncs：InnoDB引擎进行fsync()操作的次数。

（6）

InnoDB引擎读写量Innodb_data_read：自服务器启动以来读取的数据量。

Innodb_data_written：InnoDB引擎写入的数据量（以字节为单位）。项目知识导入（7）InnoDB引擎缓冲池状态Innodb_buffer_pool_reads:表示InnoDB缓冲池无法满足的请求数，即从物理磁盘读取的次数。Innodb_buffer_pool_read_requests：从InnoDB缓冲池读取的请求数（逻辑读取的请求数）。

Innodb_buffer_pool_write_requests：向InnoDB缓冲池执行的写入次数。

Innodb_buffer_pool_pages_dirty:InnoDB缓冲池中脏页（在内存修改但未写入数据文件的数据页）的数量。

Innodb_buffer_pool_pages_flushed：InnoDB缓冲池中刷新页面的请求数。

Innodb_buffer_pool_pages_free：InnoDB缓冲池中的空闲页面数量。Innodb_buffer_pool_pages_total：InnoDB缓冲池的总大小，以page为单位。

Innodb_buffer_read_hit_ratio：InnoDB缓冲池的读命中率，计算公式如下。Innodb_buffer_read_hit_ratio=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)×100%Innodb_buffer_usage：InnoDB缓冲池的利用率，计算公式如下。Innodb_buffer_usage=(1-Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total)×100%项目知识导入（8）InnoDB日志Innodb_os_log_fsyncs：向日志文件完成fsync()写入的数Innodb_os_log_written：写入日志文件的字节数。Innodb_log_writes：向日志文件物理写入的次数。Innodb_log_write_requests：向日志文件写入的请求数。（9）InnoDB行Innodb_rows_deleted：从InnoDB表中删除的行数。

Innodb_rows_inserted：向InnoDB表中插入的行数。

Innodb_rows_read：从InnoDB表中读取的行数。

Innodb_rows_updated：InnoDB表中更新的行数。

Innodb_row_lock_waits:：等待行锁的次数。

Innodb_row_lock_time：获取行锁花费的总时间，以毫秒为单位。

Innodb_row_lock_time_avg：获取行锁花费的平均时间，以毫秒为单位。项目知识导入（10）MyISAM索引缓存MyISAM存储引擎的缓存仅仅缓存索引数据，并不会缓存实际的表数据信息到内存中，索引数据以块（Block）为最小存储单位。key_buffer_size：MyISAM索引缓存大小。

Key_read_requests：从MyISAM索引缓存中读取块的请求数。

Key_write_requests：将块写入MyISAM索引缓存中的请求数。

Key_reads：将块从磁盘文件物理读取到MyISAM索引缓存的次数。缓存未命中的概率可以为Key_reads/Key_read_requests。

Key_writes：从MyISAM索引缓存中物理写入磁盘文件的次数。

Key_blocks_used：MyISAM索引缓存中已使用的块数。项目知识导入（10）MyISAM索引缓存Key_blocks_unused：MyISAM索引缓存中未使用的块数。Key_usage_ratio=Key_blocks_used/(Key_blocks_used+Key_blocks_unused)×100%Key_read_hit_ratio：MyISAM的索引缓存的读命中率，计算公式如下。Key_read_hit_ratio=(1-Key_reads/Key_read_requests)×100%Key_write_hit_ratio：MyISAM的索引缓存的写命中率，计算公式如下。Key_write_hit_ratio=(1-Key_writes/Key_write_requests)×100%

项目知识导入（11）临时表Created_tmp_disk_tables：服务器执行语句时在磁盘上自动创建的临时表数量。

Created_tmp_tables：服务器执行语句时在内存中自动创建的临时表数量。

Created_tmp_disk_tables/Created_tmp_tables的值最好不要超过10%，如果Created_tmp_tables的值比较大，可能是排序句子过多或者连接句子优化不足。（12）其他Slow_queries：执行时间超过long_query_time的查询的个数（重要）。

Sort_rows：已经排序的行数。

Open_files：打开的文件的数量。

Open_tables：当前打开的表的数量。

Select_scan：对第一个表进行完全扫描的连接数量。项目知识导入二、性能监控常用工具1．mysqladmin mysqladmin是MySQL的客户端命令行管理工具，用于执行监视流程管理操作、检查服务器配置、重装权限、检查当前状态、设置root账户的密码、更改root账户的密码，以及创建、删除数据库等操作。例如，要检查MySQL状态及正常运行时间，可以执行mysqladmin-uroot-pversion命令。只有具有root权限才能从Shell执行命令。命令及执行结果如下。项目知识导入2．MONyog

MONyog可以实时监控MySQL服务器，查看MySQL服务器的运行状态。 MONyog提供了一个日志分析模块，可以识别在服务器上运行缓慢的语句和应用程序。 MONyog提供各种过滤和explain选项，用于实现高效的工作流程。 MONyog可以提取错误日志的内容，同时可以通过邮件报告MySQL服务器状态。 MONyog提供了图表，用户通过图表可以监控单个或多组服务器的MySQL和操作系统特定指标。从而可以轻松了解所有MySQL服务器的安全性、可用性和性能。

MONyog的查询分析器功能可帮助识别有问题的SQL语句。项目知识导入三、性能测试

行业中的性能测试工具主要有以下几类:

1．Mysqlslap：mysqlslap是MySQL自带的基准测试工具，随MySQL5.1.4推出，可以通过模拟多个并发客户端访问MySQL来进行压力测试。

2．tpcc-mysql：tpcc-mysql是Percona公司按照TPC-C开发的工具，主要用于MySQL的压力测试。

3．Sysbench：sysbench是一个主流的、开源的、模块化的、跨平台的多线程性能d测试工具，可以用来对CPU、内存、磁盘I/O、线程、数据库进行性能测试。sysbench目前支持的数据库系统有MySQL、Oracle和PostgreSQL。项目知识导入Sysbench的使用方法:sysbench的安装请参考任务6-3，或者直接在LinuxCentOS7中使用命令yuminstall-ysysbench进行快捷安装。安装完成后，可以通过命令sysbench--help来查看sysbench支持哪些功能参数，命令及执行结果如下。项目知识导入sysbench内置如下测试。fileio：文件I/O测试。cpu：CPU性能测试。memory：内存功能速度测试。threads：线程子系统性能测试。mutex：互斥锁性能测试。项目知识导入Fileiosysbench提供了fileio测试功能可以执行

sysbench--test=fileiohelp命令查看其使用方法项目知识导入cpu

sysbench提供了cpu测试功能，可以执行sysbench--test=cpuhelp命令查看其使用方法项目知识导入Memorysysbench提供了memory测试功能可以执行sysbench--test=memoryhelp命令查看其使用方法项目知识导入threadssysbench提供了threads测试功能可以执行sysbench--test=threadshelp命令查看其使用方法。项目知识导入Mutexsysbench提供了mutex测试功能可以执行sysbench--test=mutexhelp命令查看其使用方法项目知识导入二、mysqlslap压力测试mysqlslap是一个MySQL官方提供的压力测试工具。它通过模拟多个并发客户端访问MySQL来进行压力测试，并且能很好地对比多个存储引擎在相同环境下的并发压力性能差别。选项名称选项描述--auto-generate-sql自动生成测试表和数据，表示用mysqlslap自己生成的SQL脚本来测试并发压力。--auto-generate-sql-add-autoincrement对生成的表自动添加auto_increment列。--auto-generate-sql-load-type测试语句的类型，取值包括：read，key，write，update和mixed(默认)。--auto-generate-sql-write-number每个线程执行多少行的插入。--commit设置N条DML后提交一次。--compress如果服务器和客户端支持都压缩，则压缩信息传递。--concurrency表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，例如，--concurrency=100,200,500。--create-schema自定义的测试库名称。项目知识导入选项名称选项描述--debug-info打印内存和CPU的相关信息。--delimiter在SQL语句中使用的定界符。--detach执行N条语句后断开重连。--engine要测试的引擎，可以有多个，用分隔符隔开。例如，--engines=myisam,innodb--help显示帮助信息。--hostMySQL服务器所在的主机。--iterations测试执行的迭代次数，表示要在不同并发环境下，各自运行测试多少次。--number-char-cols自动生成的测试表中包含N个字符类型的列，默认为1--number-int-cols自动生成的测试表中包含N个数字类型的列，默认为1--number-of-queries总的测试查询次数(并发客户数×每客户查询次数)--only-print只打印测试语句而不实际执行。--password连接服务器时使用的密码。--port连接的TCP/IP端口号。--post-query包含要在测试完成后执行的语句的文件或字符串。--query使用自定义脚本执行测试，例如可以自定义一个存储过程或者sql语句来执行测试。--user连接服务器时要使用的MySQL账户名。项目知识导入二、mysqlslap压力测试mysqlslap的运行分为三个阶段。创建架构，表及可选的任何存储程序或数据以供测试使用。此阶段使用单个客户端连接。运行负载测试。此阶段可以使用很多客户端连接。清理（断开连接，删除指定表）。此阶段使用单个客户端连接。项目知识导入三、性能调优（一）查询优化据统计，80%的响应时间问题都是应用了性能差的SQL语句造成的。那么如何定位并优化慢查询语句？具体场景需要具体分析，一般思路如下。（1）根据慢查询日志定位慢查询语句。（2）使用explain等工具分析SQL语句。（3）修改SQL语句或者尽量利用索引。查询优化的原则是：最大化利用索引；尽可能地避免全表扫描；减少对无效数据的查询项目知识导入explain工具是MySQL中常用的查询分析工具，可以帮助分析select语句，以便管理员了解查询效率低下的原因。需要注意的是，执行explain并不会真正的执行SQL，而是对SQL做了一些分析。1、explain工具项目知识导入explain列名称explain列说明id查询的唯一标识。select_type查询类型。table查询的那个表。partitions匹配的分区。type访问类型。possible_keys可能使用的索引。key最终使用的索引。key_len最终使用的索引的长度。ref与索引一起被使用的字段或常数。rows查询扫描的行数，是个估算值。filtered查询条件所过滤的数据的百分比。Extra额外的信息。项目知识导入下面对其中的一些重点字段做详细的介绍。(1)type:显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL，一般来说，得保证查询至少达到range级别，最好能达到ref，如右所示。type重要项项说明system表仅有一行(=系统表)，这是const连接类型的一个特例。const单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询。eq_ref类似ref,

区别在于使用的是唯一索引，使用主键的关联查询。ref使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中。ref_or_null如同ref,但是MySQL必须在初次查找的结果里找出null条目,然后进行二次查找。index_merge说明索引合并优化被使用了。unique_subquery在某些IN查询中使用此种类型,而不是常规的ref:valueIN(SELECTprimary_keyFROMsingle_tableWHEREsome_expr)。index_subquery在某些IN查询中使用此种类型,与unique_subquery类似,但是查询的是非唯一性索引:valueIN(SELECTkey_columnFROMsingle_tableWHEREsome_expr)range索引范围扫描。只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。当使用=、<>、>、>=、<、<=、ISNULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range。index索引全扫描。扫描表的时候按照索引次序进行而不是行。主要优点是避免了排序,但是开销仍然非常大。all最坏的情况,从头到尾全表扫描。项目知识导入(2)Extra是另外一个很重要的字段，该字段显示MySQL在查询过程中的一些详细信息Extra重要项项说明Usingindex说明查询使用了覆盖索引(CoveringIndex)，不需要读取数据文件，从索引树（索引文件）中即可获得信息。Usingindexcondition索引条件推送，用于二级索引上，可以在索引上执行如like这样的操作，减少了不必要的IO操作。Usingwhere使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。Usingjoinbuffer使用了连接缓存：BlockNestedLoop,连接算法是块嵌套循环连接；BatchedKeyAccess,连接算法是批量索引连接。impossiblewherewhere子句的值总是false,不能用来获取任何元组。selecttablesoptimizedaway在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。distinct优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。Usingfilesort说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Usingtemporary使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序orderby和分组查询groupby项目知识导入（二）表设计优化1．表设计规范化

单表的设计首先要规范化，满足第三范式，消除数据冗余。第一范式（1NF）：无重复的列。所谓1NF是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据表的每一列都是不可分割的原子数据项，而不能是集合、数组、记录等非原子数据项。第二范式（2NF）：属性。在1NF的基础上，非主属性必须完全依赖于主码（在1NF的基础上消除非主属性对主码的部分函数依赖）。第三范式（3NF）：属性。在1NF的基础上，任何非主属性不依赖于其他非主属性，在2NF的基础上消除传递依赖。项目知识导入2．数据类型优化表设计过程中，首先要注意的就是MySQL的数据类型优化。MySQL支持的数据类型非常多，选择正确的数据类型对于实现高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于提高数据库的访问性能。（1）较小的数据类型通常更好。（2）简单就好。简单数据类型的操作通常需要更少的CPU周期。（3）尽量避免NULL。很多表都包含可为NULL的列，即使应用程序并不需要保存NULL，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOTNULL，除非真的需要存储NULL。项目知识导入3．索引优化索引用于快速查找具有特定列值的行。如果没有索引，MySQL就必须从第一行开始读取整个表以找到相关的行。索引优化一般遵循以下一些原则。（1）索引并不是越多越好，要根据查询有针对性地创建索引，考虑给WHERE和ORDERBY子句上涉及的列建立索引，可根据explain来查看是用了索引还是全表扫描。（2）应尽量避免在WHERE子句中对字段进行NULL判断，否则将导致存储引擎放弃使用索引而进行全表扫描。（3）值很少的字段不适合创建索引，例如“性别”这种只有两个值的字段。（4）字符字段只创建前缀索引。（5）字符字段最好不要作为主键。（6）不用外键，由程序保证约束。（7）尽量不用UNIQUE，由程序保证约束。（8）使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引。项目知识导入（三）配置优化配置优化即对MySQL的配置文件中的参数进行优化。可以通过启动时指定参数和使用配置文件两种方法配置MySQL。下面对MySQL配置文件的常用参数做简单介绍。1．max_connectionsmax_connections表示MySQL的最大连接数2．back_logback_log表示MySQL能暂存的连接数量。项目知识导入3．interactive_timeoutinteractive_timeout表示交互连接在被服务器关闭前等待行动的秒数。4．key_buffer_sizekey_buffer_size用于指定索引缓冲区的大小，它能够决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。Key_reads/Key_read_requests的值应该尽可能小，至少小于1:100，1:1000更好（上述状态值可以使用showglobalstatuslike'key_read%'命令获得）5．read_rnd_buffer_size

read_rnd_buffer_size表示随机读缓冲区大小。当按任意顺序（如按照排序顺序）读取行时，系统将分配一个随机读缓冲区。进行排序查询时，MySQL会首先扫描一遍该缓冲区，以避免搜索磁盘，提高查询速度。如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接分配缓冲区，所以应适当设置该值，以避免内存开销过大，一般可将其设置为16MB。项目知识导入6.sort_buffer_size系统会为每个需要进行排序的线程分配大小为sort_buffer_size的一个缓冲区。增加这个值可加速ORDERBY或GROUPBY操作。7．join_buffer_sizejoin_buffer_size表示联合查询操作使用的缓冲区大小。它与read_rnd_buffer_size、sort_buffer_size一样，系统会为每个连接分配指定大小的联合查询操作缓冲区，也就是说join_buffer_size的值为16MB，现在有100个线程连接，则服务器分配内存大小为100×16MB。

8．table_cachetable_cache表示表高速缓冲区的大小。当MySQL访问一个表时，如果表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表的内容。通过检查峰值时间的状态值Open_tables（当前在缓冲区中打开表的数量）和Opened_tables（自MySQL启动起打开表的数量），可以决定是否需要增加table_cache的值。项目知识导入9．max_heap_table_sizemax_heap_table_size表示用户可以创建的内存表（MemoryTable）的大小。这个参数用来计算内存表的最大行数。

10．tmp_table_size通过设置tmp_table_size参数可以增加一张临时表（如通过高级GROUPBY操作生成的临时表）的大小。如果调高该值，MySQL将同时增加heap表的大小，从而达到提高连接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于磁盘的MyISAM表。项目知识导入11．thread_cache_sizethread_cache_size表示保存在缓冲区中的线程的数量。12．thread_concurrency推荐将该值设置为服务器CPU核数的2倍

13．wait_timeoutwait_timeout用于指定一个请求的最大连接时间。wait_timeout的默认值是28800秒（8小时），MySQL会自动关闭空闲时间超过wait_timeout值的连接，防止连接数过多。对于4GB左右内存的服务器可以将该值设置为5～10小时。项目任务分解任务6-1安装MONyog工具监控MySQL服务器任务说明

MONyog支持查询分析功能，能够轻松找出MySQL的问题所在。此外，它还可以帮助用户掌握服务器的运行状态，并查看在任一时间点绘制的具有详细查询信息的图表。本任务要求下载并安装MONyog工具以监控MySQL服务器。任务实施过程详见任务手册项目任务分解任务6-2对MySQL数据库读写性能调优任务说明

首先我们要知道，大多数性能优化不是一项单纯的工作，需要考虑方方面面，例如CPU、内存、I/O、数据库参数等，不是简单地修改参数就能让数据库“快”起来，虽然有时候修改参数确实能优化数据库性能。本任务要求通过调整来熟悉影响性能的主要参数，以便对性能优化形成一定认知。任务实施过程

详见任务手册项目任务分解任务6-3创建MySQL压力测试报告任务说明

sysbench是一个跨平台且支持多线程的模块化基准测试工具，用于评估系统在运行高负载的数据库时相关核心参数的性能表现。本任务要求在LinuxCentOS7中安装并使用sysbench完成对