— 影响性能的因素;
1.环境问题,MySQL的性能受主机的性能特征影响:
1.CPU速度和数量;
2.磁盘I/O;
3.网络性能;
4.操作系统争用;
2.MySQL配置:
1.数据库设计:索引,数据类型(合适且尽可能小),标准化;
2.应用程序性能:特定请求(仅请求特定的行和列),短时事务(根据主键访问的短事务);
3.配置变量:调整缓冲区,高速缓存,InnoDB设置;
— 监视;
1.要调节服务器的性能,必须了解其性能特征;可以针对整体性能进行基准测试,也可以使用日志和EXPLAIN逐个分析事件或者使用ERFORMANCE_SCHEMA按组分析事件;
2.基准测试:
1.mysqlslap:是标准MySQL分发的一部分,是一个基准测试工具,用来模拟MySQL服务器实例上客户机负载,并显示每个阶段的计时信息;
2.sql-bench:是MySQL源码分发的一部分,它是一系列Perl脚步,用于执行多个语句和手机状态计时数据;
3.分析:
1.日志:
1.一般查询日志;
2.慢查询日志;
2.语句:
1.EXPLAIN;
2.PROCEDURE ANALYSE;
3.SHOW STATUS:还可以使用mysqladmin extended-status查看;
4.PERFORMANCE_SCHEMA数据库;
— 性能模式;
“性能模式”是在较低级别监视MySQL服务器执行情况的功能:
1.该功能是使用PERFORMANCE_SCHEMA存储引擎和performance_schema数据库实现的;
2.性能模式监视并允许您检查MySQL服务器中的被检测代码的性能特征:
1.开发者将检测函数和其他编码事件以收集计时信息;
2.公开的性能数据对以下人员或任务很有帮助:
1.MySQL代码库的贡献者;
2.插件开发者;
3.识别低级别的性能瓶颈,如日志文件I/O等待或缓冲池互斥;
3.从Oracle下载的所有二进制版本的MySQL中都提供了性能模式;默认情况下,将启用性能模式,并在服务器启动时使用performance_schema变量对其进行控制;
mysql> SHOW VARIABLES LIKE ‘performance_schema’;
mysql> SHOW TABLE STATUS FROM performance_schema\G
— 检测,实例,事件和使用者;
性能模式数据库包含配置和事件信息:
1.性能模式中的“检测”是服务器代码中引发要监视的事件的点;
1.每个检测由其类型/所属的模块/该特定检测的变量或类组成,通过查performance_schema.setup_instruments表可查看所有可用的检测;
2.性能模式将记录实例表中的每个检测实例;例如,以下查询显示检测wait/io/file/sql/FRM记录文件实例/var/lib/mysql/mem/tags.frm上的事件;
mysql> SELECT file_name, event_name FROM file_instances LIMIT 1\G *************************** 1. row ***************************
FILE_NAME: /var/lib/mysql/mem/tags.frm
EVENT_NAME: wait/io/file/sql/FRM
2.每个被检测的对象都是该检测的一个“实例”,记录在一系列实例表中;
3.当线程执行检测实例中的代码时,MySQL将识别所发生的“事件”,将其记录在事件和汇总表中;
4.每个“使用者”都是性能模式中表的名称,用于记录和查询事件以及事件的摘要,在SETUP_CONSUMERS表中进行配置;每个使用者的NAME是性能模式中用于查询事件和摘要的表的名称,被禁用的使用者不记录信息,从而节省了系统资源;
mysql> SELECT * FROM setup_consumers;
5.当MySQL识别了发生在检测实例中的事件后,会将其记录在事件表中:
1.主事件表为events_waits_current,该表中存储了每个线程最近的事件;
2.events_waits_history存储每个线程的最近10个事件;
3.events_waits_history_long共存储10,000个最近事件;
6.当使用性能模式识别瓶颈或其他问题时,请执行以下操作:
1.确保已针对适用于您所遇到的问题类型的一系列检测和使用者启用了性能模式;例如,如果您确定问题出在I/O限制上,请使用wait/io/file/*检测;如果不确定根本原因,请使用更广范围的检测;
2.运行用于产生该问题的测试用例;
3.查询events_waits_*表等使用者,尤其是使用适用的WHERE子句过滤器查询events_waits_history_long,以便进一步缩小问题原因的范围;
4.禁用那些用于评估已排除的问题的检测;
5.重试该测试用例;
— 一般数据库优化;
1.对数据进行标准化可以:
1.标准化是移除数据库中的冗余和不当依赖关系(以避免将相同的数据存储在多个地方以及出现异常的风险)的行为;
2.标准化通常会产生以下结果:许多表的列变少,整体存储要求降低,I/O需求降低以及单次插入,更新和删除操作加快;
3.这提高了频繁执行小更新的事务性工作负荷的性能,但会使检索大量数据的查询变得复杂;
2.选择正确的数据类型和大小可以:
1.选择正确的数据类型是表设计中一个很重要却常常被忽视的部分,数据类型的大小可能会对表操作产生较大的影响;例如,选择将SMALLINT数字存储为INT会使该列所需的空 间翻倍,在包含一百万个行的表中,该决策将导致浪费额外的2MB存储空间,并且磁盘操作速度会变慢,缓冲区和高速缓存将需要使用更多内存;
2.使用INSERT…COMPRESS(field_name)…和SELECT…UNCOMPRESS(column_name)…可以在存储和检索字符串数据时对其进行压缩和解压缩;尽管也可以使用CHAR或VARCHAR字段来实现此目的,但是通过使用VARBINARY或BLOB列存储压缩数据可以避免字符集转换出现问题;
3.创建最佳索引可以:
1.如果您通过在WHERE子句中指定一个字段来查询表中的特定行,并且该表没有为该字段创建索引,MySQL将读取该表中的每一行以找到每个匹配的行;这将导致很多不必要的磁盘访问,并且对于大型表性能将显著降低;
2.索引是有序的成组数据,通过索引,MySQL可以更容易地找到查询行的正确位置;默认情况下,InnoDB将按主键排列表的顺序,该有序表称为群集索引;
3.InnoDB表上的每个附加索引或辅助索引会在文件系统中占用额外的空间,因为索引包含索引字段的额外副本以及主键的副本;
4.每次使用INSERT/UPDATE/REPLACE/DELETE操作修改数据时,MySQL也必须更新所有包含修改字段的索引,因此,向表中添加多个索引会降低影响该表的数据修改操作的性能;
5.不过,如果对索引进行了适当设计,依赖于索引字段的查询便会在性能上有较大的获益;如果查询无法使用索引找到特定行,则必须执行全表扫描;即,必须读取整个表来找到该行;
— PROCEDURE ANALYSE;
1.PROCEDURE ANALYSE分析给定查询中的列,并提供对每个字段的调节反馈:mysql> SELECT CountryCode, District, Population FROM City PROCEDURE ANALYSE(250,1024)\G
2.提供参数来调节如何建议ENUM值:
1.处理列时所使用的最大元素数和最大内存;
2.示例:…PROCEDURE ANALYSE(100,256);
3.用于最大程度地减小字段大小:
1.MySQL通常按最大的字段大小分配内存;
2.隐式MEMORY临时表,排序缓冲区等;
4.用于确定字段是否允许NULL;
补充:
1.默认设置通常建议使用ENUM类型来优化表的设计,如果确定不想在分析列时使用PROCEDURE ANALYSE()所建议的ENUM值,请使用非默认参数:
1.第一个参数是分析ENUM值是否适当时要考虑的不同元素数,此参数的默认值为256;
2.第二个参数是用于收集不同的值以供分析的最大内存量,此参数的默认值为8192,表示8KB;如果为此参数设置值0,则PROCEDURE ANALYSE()无法检查不同的值以建议使用ENUM类型;
2.如果PROCEDURE ANALYSE()无法存储可接受范围内的候选ENUM值(在参数设置的限制内),则不会建议对该列使用ENUM类型;
3.本幻灯片中的示例建议对City.CountryCode列使用CHAR(3)类型;另一方面,如果使用默认参数,则PROCEDURE ANALYSE()将建议ENUM(‘ABW’,’AFG’,…,’ZMB’,’ZWE’),这是一种包含超过200个元素的ENUM类型,其中针对每个相应的CountryCode值都包含一个不同值;
— EXPLAIN;
1.EXPLAIN命令:
1.描述MySQL打算如何执行特定的SQL语句;
2.不返回数据集的任何数据;
3.提供有关MySQL打算如何执行该语句的信息;
2.使用EXPLAIN可检查SELECT/INSERT/REPLACE/UPDATE/DELETE语句;
3.将EXPLAIN置于语句之前:
1.EXPLAIN SELECT …;
2.EXPLAIN UPDATE…;
补充:
1.EXPLAIN将为语句中使用的每个表生成一行输出,该输出包含以下列:
1.id:编号;若没有子查询和联合查询,id则都是1;MySQL会按照id从大到小的顺序执行query,在id相同的情况下,则从上到下执行;
2.select_type:查询中使用的选择类型;
3.table:输出的行所引用的表;有时看到的是,其中N对应的是id列的值;
4.partitions:输出行所对应的表分区;
5.type:访问类型,连接的方式;
6.possible_keys:在查询过程中可能用到的索引;在优化初期创建该列,但在以后的优化过程中会根据实际情况进行选择,所以在该列列出的索引在后续过程中可能没用;该列为NULL意味着没有相关索引,可以根据实际情况看是否需要加索引;
7.key:访问过程中实际用到的索引;有可能不会出现在possible_keys中(这时可能用的是覆盖索引,即使query中没有where);possible_keys揭示哪个索引更有效,key是优化器决定哪个索引可能最小化查询成本,查询成本基于系统开销等总和因素,有可能是“执行时间”矛盾;如果强制mysql使用或者忽略possible_keys中的索引,需要在query中使用FORCE INDEX,USE INDEX或者IGNORE INDEX;
8.key_len:显示使用索引的字节数;由根据表结构计算得出,而不是实际数据的字节数;如ColumnA(char(3)) ColumnB(int(11)),在utf-8的字符集下,key_len=3*3+4=13;计算该值时需要考虑字符列对应的字符集,不同字符集对应不同的字节数;
9.ref:显示了哪些字段或者常量被用来和key配合从表中查询记录出来;显示那些在index查询中被当作值使用的在其他表里的字段或者constants
10.rows:估计为返回结果集而需要扫描的行;不是最终结果集的函数,把所有的rows乘起来可估算出整个query需要检查的行数,有limit时会不准确;
11.filtered:根据条件过滤的行百分比;
12.Extra:优化程序提供的每个查询的附加信息;
2.有关输出列的完整论述:http://dev.mysql.com/doc/refman/5.6/en/explain-output.html;
3.使用EXPLAIN EXTENDED…可查看优化程序提供的其他信息:http://dev.mysql.com/doc/refman/5.6/en/explain-extended.html;
select_type列:
1.SIMPLE:简单查询,没有使用UNION和子查询;
EXPLAIN SELECT * FROM actor WHERE actor_id = 1;
2.PRIMARY:包含子查询或联合查询的query中,最外层的select查询;
3.UNION:在联合查询中第二个及其以后的select对应的类型;
EXPLAIN SELECT actor_id, last_update FROM film_actor UNION ALL SELECT actor_id, last_update FROM actor;
4.DEPENDENT UNION:子查询中的union,且为union中第二个select开始的后面所有select,同样依赖于外部查询的结果集;
EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM film_actor UNION ALL SELECT actor_id FROM film_actor);
5.UNION RESULT:从UNION临时表获取结果集合;
6.SUBQUERY:子查询在SELECT的目标里,不在FROM中,子查询中的第一个SELECT;
EXPLAIN SELECT (SELECT actor_id FROM actor) FROM film_actor;
7.DEPENDENT SUBQUERY:子查询中的第一个查询,依赖于外部查询的结果集;
8.DERIVED:子查询在from子句中,执行查询的时候会把子查询的结果集放到临时表(衍生表);
EXPLAIN SELECT * FROM (SELECT * FROM actor) AS a;
9.MATERIALIZED:物化子查询;
10.UNCACHEABLE SUBQUERY:表示子查询,但返回结果不能被cache,必须依据外层查询重新计算;
11.UNCACHEABLE UNION:表示union第二个或以后的select,但结果不能被cache,必须依据外层查询重新计算;
type列:访问路径(效率依次降低):
0.NULL:在优化过程中就已得到结果,不用再访问表或索引;
EXPLAIN SELECT * FROM actor WHERE actor_id = -1;
1.system:当表只有一行时就会出现system类型;是const join类型的特例;在没有任何索引的情况下,只有一条数据,MyISAM会显示system,InnoDB会显示ALL;
use test;
CREATE TABLE t_sys1(id INT) ENGINE = InnoDB;
CREATE TABLE t_sys2(id INT) ENGINE = MyISAM;
INSERT INTO t_sys1 VALUES(1);
COMMIT;
INSERT INTO t_sys2 VALUES(1);
EXPLAIN SELECT * FROM t_sys1;
EXPLAIN SELECT * FROM t_sys2;
2.const:最多会有一条记录匹配,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数;const表很快,因为它们只读取一次,发生在有一个unique key或者主键,并且where子句给它设定了一个比较值;
eg:EXPLAIN SELECT * FROM actor WHERE actor_id = 1;
3.eq_ref:在做关联查询中,前一个表返回的值在此表中只有一条记录对应,这是关联查询的最好选择;所以,唯一性索引(Primary or UNIQUE NOT NULL)才会出现eq_ref(非唯一性索引会出现ref),因为唯一,所以最多只返回一条记录,找到后无需继续查找,因此比ref更快;
CREATE TABLE t_eq_ref(actor_id SMALLINT(5) PRIMARY KEY);
INSERT INTO t_eq_ref VALUES(1);
COMMIT;
EXPLAIN SELECT * FROM actor, t_eq_ref WHERE actor.actor_id = t_eq_ref.actor_id;
4.ref:这是一种索引访问;只有当使用一个非唯一性索引或者唯一性索引的非唯一性前缀(换句话说,就是无法根据该值只取得一条记录)时才会发生,将索引和某个值相比较,这个值可能是一个常数,也可能是来自前一个表里的多表查询的结果值;如果使用的键仅仅匹配少量行,该联接类型是不错的;
EXPLAIN SELECT * FROM film_actor, actor WHERE film_actor.actor_id=actor.actor_id AND film_actor.actor_id = 1;
5.ref_or_null:类似ref,不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录,他意味着MySQL必须进行二次查找,在初次查找的结果中找出NULL条目;
6.index_merge:查询中使用两个或多个索引,然后对索引结果进行合并;在这种情况下,key列包含所有使用的索引,key_len包含这些索引的最长的关键元素;
7.unique_subquery:用来优化有子查询的in,并且该子查询是通过一个unique key选择的,子查询返回的字段组合是主键或者唯一索引;
value IN (SELECT primary_key FROM single_table WHERE some_expr)
EXPLAIN SELECT * FROM actor WHERE actor_id IN (SELECT actor_id FROM actor);
8.index_subquery:与unique_subquery类似,子查询中的返回结果字段组合是一个索引或索引组合,但不是一个主键或者唯一索引;
value IN (SELECT key_column FROM single_table WHERE some_expr)
EXPLAIN SELECT * FROM film_actor WHERE film_id IN (SELECT film_id FROM film_actor);
9.range:使用索引扫描,只查询给定范围的记录;比如出现=, <>, >, >=, < , <=, IS NULL, <=>, BETWEEN, or IN()操作符时:
EXPLAIN SELECT * FROM actor WHERE id < 10;
10.index:按索引次序扫描数据,因为按照索引扫描所以会避免排序,但也会扫描整表数据,若随机读取开销会更大;如果extra列显示using index,说明使用的是覆盖索引(覆盖索引:包含所有满足查询需要的数据列的索引);对于InnoDB表特别有用,此时只访问索引数据即可,不用再根据主键信息获取原数据行,避免了二次查询;而MyISAM表优化效果相对InnoDB来说没有那么的明显;
11.ALL:全表扫描;一般情况下,应该添加索引来避免全表扫描;出了使用limit子句或者Extra列有distinct信息;
Extra列:
1.Using index:此查询使用了覆盖索引(Covering Index),即通过索引就能返回结果,无需访问表;若没显示"Using index"表示读取了表数据;
EXPLAIN SELECT id FROM City WHERE id = 1890;
2.Using where:表示MySQL服务器从存储引擎收到行后再进行“后过滤”(Post-filter);所谓“后过滤”,就是先读取整行数据,再检查此行是否符合where句的条件,符合就留下,不符合便丢弃;因为检查是在读取行后才进行的,所以称为“后过滤”;
EXPLAIN SELECT id FROM City WHERE id < 10;
3.Using temporary:使用到临时表
EXPLAIN SELECT DISTINCT Continent FROM Country;
4.Using filesort:若查询所需的排序与使用的索引的排序一致,因为索引是已排序的,因此按索引的顺序读取结果返回;否则,在取得结果后,还需要按查询所需的顺序对结果进行排序,这时就会出现Using filesort;
EXPLAIN SELECT id, CountryCode FROM City WHERE id < 10 ORDER BY CountryCode;
latin1/utf8/gbk字符数/字节数/汉字的对应关系:
latin1:1character=1byte,1汉字=2character;一个字段定义成varchar(200),可以存储100个汉字或者200个字符,占用200个字节;尤其是当字段内容是字母和汉字组成时,尽量假设字段内容都是由汉字组成,据此来设置字段长度;
utf8:1character=3bytes,1汉字=1character;一个字段定义成varchar(200),则它可以存储200个汉字或者200个字母,占用600个字节;
gbk:1character=2bytes,1汉字=1character一个字段定义成varchar(200),则它可以存储200个汉字或者200个字母,占用400个字节;
-- EXPLAIN格式;
EXPLAIN输出也提供其他格式:
1.可视化EXPLAIN:在MySQL Workbench中提供图形格式的输出;
2.EXPLAIN FORMAT=JSON:
1.JSON格式的输出;JSON(JavaScript Object Notation,JavaScript对象表示法)是一种简单的数据交换格式;
2.当要将EXPLAIN输出传递给程序以供进一步处理/分析时十分有用;
mysql> EXPLAIN FORMAT=JSON SELECT * FROM City\G
— 检查服务器状态;
MySQL提供了多种查看服务器状态变量的方法:
1.在mysql提示符下:
1.STATUS;
2.SHOW STATUS;
2.在终端上:
1.mysqladmin –login-path=login-path status
2.mysqladmin -u user -p extended-status
3.对mysqladmin使用选项可提供附加功能;例如,–sleep(或-i)选项可指定在迭代之间等待的秒数,并在等待该时间后自动重新执行命令;–relative(或-r)选项显示自上次迭代后每个变量的差异,而不是变量值;
shell> mysqladmin -i 5 -r status
— 主要状态变量;
1.Created_tmp_disk_tables:显示磁盘上的内部临时表的数量;获取执行语句时服务器所创建的临时表数;如果该数值较高,则服务器已在磁盘上(而不是在内存中)创建多个临时表,从而导致查询执行较慢;
2.Handler_read_first:显示索引中第一个条目的读取次数;如果该数值较高,则服务器已执行多次完整索引扫描以完成查询请求;
3.Innodb_buffer_pool_wait_free:显示服务器等待干净页面的次数;等待InnoDB缓冲池中的页面刷新后才可以完成查询请求;如果该数值较高,则未正确设置InnoDB缓冲池的大小,因而查询性能受到影响;
4.Max_used_connections:显示自服务器启动以来的最大并发连接数;此变量提供非常有用的信息来确定服务器必须支持的并发连接数;
5.Open_tables:显示给定时间内打开的表的数量;将此变量与服务器系统变量table_cache比较,可提供有关应该为表高速缓存预留多少内存量的有用信息;如果Open_tables状态变量的值通常很低,请减小服务器系统变量table_cache的大小;如果该值很高(接近服务器系统变量table_cache的值),请增加分配给表高速缓存的内存量来缩短查询响应时间;
6.Select_full_join:显示执行表扫描而不是使用索引的联接数量;如果该值不是0,则应该仔细检查表的索引;
7.Slow_queries:显示用时比long_query_time系统变量所指定的秒数长的查询数;此状态变量取决于对long_query_time变量(默认值为10秒)设置的了解;如果Slow_queries状态变量不是0,请检查long_query_time的值和慢速查询日志,并改进所捕获的查询;
8.Sort_merge_passes:显示排序算法所执行的合并传递次数,排序操作需要内存中的缓冲区;此状态变量计算排序操作所需的经过排序缓冲区的传递次数;如果该值较高,则可能表明排序缓冲区大小不足以执行查询的一次通过排序;请考虑增大sort_buffer_size系统变量的值;
9.Threads_connected:显示当前打开的连接数;定期捕获该值可提供有关服务器何时最活跃的有用信息,使 用此变量可确定执行服务器维护的最佳时间,或者可将其作为为服务器分配更多资源的依据;
10.Uptime:显示服务器持续运行的秒数;该值可以提供有关服务器运行状况的有用信息,例如服务器需要重新启动的频率;
— 调节系统变量;
1.首先调节查询,模式和索引:
1.一个常见的误区是认为服务器变量配置是服务器调节中最重要的部分;
2.事实上,优化模式,常见查询和典型数据库的索引可获得比调节变量更多的好处;
2.针对服务器大小进行调节内存和I/O:
1.Oracle的MySQL工程师选择默认设置来适应大多数生产系统,这些系统常常要处理频繁的小事务,许多更新和少数大型慢速查询(如用于生成报告的查询);
2.然而,由于MySQL在从小型设备(如销售点系统和路由器)到具有大量内存和快速磁盘阵列的大型Web服务器等各种系统上都在使用,可能会发现,对于您的特定环境和工作负荷,可以从更改服务器的某些默认设置中获益;
3.针对应用程序配置进行调节:存储引擎设置
1.将物理RAM的70%–85%提供给InnoDB缓冲池;在仅使用InnoDB用户表的MySQL专用服务器上,可以将innodb_buffer_pool_size的值增大到占服务器总内存的较大比例(70%–85%),同时要记住操作系统的需要,如cron作业,备份,病毒扫描以及管理连接和任务;如果有几GB的RAM,则还可以通过使用多个innodb_buffer_pool_instances而获益,该设置可启用多个缓冲池,从而避免争用;
2.最小化MyISAM高速缓存和缓冲区;在不将MyISAM用作用户表的系统上,减小仅适用于MyISAM的选项的值(例如将key_buffer_size的值减小为16MB等较小值),同时要记住某些内部MySQL操作将使用MyISAM;
4.调节工作负荷类型:
1.连接数;
1.当为每个查询或每个连接的高速缓存和缓冲区设置较大的值时,会减少缓冲池的可用大小;
2.调节服务器的配置变量是一个平衡过程,需要从默认值开始,提供尽可能多的内存给缓冲池,然后调节与以下项最紧密相关的变量:调节目标,通过检查服务器状态识别出的问题以及通过查询性能模式识别出的瓶颈;
2.事务服务器:
1.在用于支持许多反复断开并重新连接的快速并发事务的服务器上,请将thread_cache_size的值设置为足够大的值,以便大多数新连接可以使用高速缓存的线程;
2.这可避免创建和断开每个连接的线程时的服务器开销;在支持多写入操作的服务器上,请提高innodb_log_file_size和innodb_log_buffer_size等日志设置,因为数据修改操作的性能在很大程度上依赖 于InnoDB日志的性能;
3.请考虑更改innodb_flush_log_at_trx_commit的值以提高每次提交的性能,但风险是:如果服务器出现故障,可能会丢失某些数据;
4.如果您的应用程序反复执行相同的查询(或多个相同的查询),请考虑启用查询高速缓 存,并根据常见查询的结果调节其大小,方法是为query_cache_type和query_cache_size设置适当的值;
3.报表服务器:在用于运行少数大型慢速查询(例如用于业务智能报表的查询)的服务器上,使用join_buffer_size和sort_buffer_size等设置增加专用于缓冲区的内存量;虽然默认服务器设置更适合事务系统,但默认的my.cnf文件包含这些变量适用于报表服务器的替代值;
— 主要服务器系统变量;
1.innodb_buffer_pool_size:定义InnoDB用于缓存表数据和索引的内存缓冲区大小;要获得最佳性能,请将此值设置为尽可能大,同时要记住值过高会导致操作系统交换页面,从而大大降低性能;如果在专用数据库服务 器上仅使用了InnoDB用户表,请考虑将此变量设置为介于物理RAM的70%到85%之间的值;
2.innodb_flush_log_at_trx_commit:定义InnoDB将日志缓冲区写入日志文件的频率,以及对日志文件执行刷新到磁盘操作的频率;此变量有三种可能的设置:
1.0:每秒将日志缓冲区写入磁盘一次;
2.1:每次提交时将日志刷新到磁盘;如果未发生提交,则每秒刷新一次;
3.2:将日志刷新到操作系统高速缓存中,并且每隔innodb_flush_log_at_timeout秒(默认为一秒)刷新到磁盘一次;
3.innodb_log_buffer_size:定义InnoDB用于写入磁盘上的日志文件的缓冲区的大小;此变量的默认值为8MB;事务超过此大小会导致InnoDB在事务提交之前将日志刷新到磁盘,从而降低性能;对于使用大量BLOB或者在更新活动中具有较大峰值的应用程序,可通过增大该值提高事务性能;
4.innodb_log_file_size:定义日志组中每个日志文件的大小;对于大型数据集上的写入密集型工作负荷,请设置此变 量以便所有日志文件的最大总大小(通过innodb_log_files_in_group设置)小于或等于缓冲池的大小;大型日志文件会减缓故障恢复,但可以通过减少检查点刷新活动来提高整体性能;
5.join_buffer_size:定义用于使用表扫描的联接的最小缓冲区大小;对于包含无法使用索引的联接的查询,请以默认值(256 KB)为起点增大该值;运行此类查询时请更改每个会话的值,以避免设置全局设置而使无需这么大值的查询浪费内存;
6.query_cache_size:定义为缓存查询结果而分配的内存量;通过使用查询高速缓存,提高针对极少更改的数据发出重复查询的应用程序的性能;作为基线,请根据重复查询的数量和所返回数据的大小将此变量设置为介于32MB和512MB之间的值,请监视高速缓存命中率以确定此变量的有效性,并根据您的观察调节其值;
7.sort_buffer_size:定义分配给需要进行排序的会话的最大内存量;如果Sort_merge_passes状态变量的值很高,请增大该值以提高ORDER BY和GROUP BY操作的性能;
8.table_open_cache:定义所有线程打开的表的数量;请设置该值以使其大于N * max_connections,其中,N是在应用程序的所有查询中所使用的最大表数量;该值过高会导致出现错误“Too many open files(打开的文件太多)”;Open_tables状态变量的值较高表示MySQL频繁打开和关闭表,因此应该增大table_open_cache;
9.thread_cache_size:义服务器应缓存以供重用的线程数;默认情况下,此变量将自动调节大小;评估Threads_created状态变量可确定是否需要更改thread_cache_size的值;
— 准备调节环境;
1.尽可能地复制生产系统;
1.要减小与正在调节的变量无关的已更改因素的影响;
2.请在停机期间对生产服务器执行调节,或者最好在复制的系统上进行调节;
2.决定调节目标:
1.每秒处理更多事务;
2.更快生成复杂报表;
3.通过并发连接的峰值提高性能;
3.选择适当的变量进行调节:缓冲区,高速缓存,日志设置;
4.为了最准确地模拟正在针对其进行调节的工作负荷,需要收集一组有代表性的语句:
1.从应用程序中选择查询和修改操作比例正确的语句序列;
2.在要优化的每天或每周期间内,使用一般查询日志从生产服务器收集实际语句;
— 练习调节;
1.查找每个变量的最佳值的基准测试:
1.mysqlslap或mysql来运行工作负荷并获取平均执行时间;
2.sql-bench来运行更一般的基准测试;
3.mysqladmin extended-status来获取工作负荷前后的状态变量的值;
4.top等操作系统工具或/proc文件系统来访问过程度量;
2.将变量设置为低于其默认值的设置;
3.进行基准测试,测量相关度量:
1.虚拟内存使用;
2.所花费的平均时间;
3.相关状态变量;
4.增大变量值并重复基准测试:如果需要,刷新状态变量;
5.将结果绘制成图:
1.查找收益的下降点和性能的高峰;
2.根据所用资源和性能之间的最佳平衡来决定最终变量值;
6.如果要针对特定变量使用多个不同值运行微调基准测试,或者如果要在很长一段时间内反复运行相同的基准测试,请考虑使用脚本语言来自动化基准测试中所使用的步骤;
— 调节示例:排序缓冲区大小;
1.本幻灯片中的示例显示了一系列针对具有繁重排序工作负荷的数据库的测试结果,其中,运行测试时更改了sort_buffer_size变量;
2.图表显示:
1.在sort_buffer_size从32KB增大到512KB时,Sort_merge_passes状态变量的值(可使用mysqladmin extended_status-r查看)急剧下降,在此之后又缓慢降低;
2.测试工作负荷所花的平均时间(可使用mysqlslap查看)在sort_buffer_size为512KB时降低,在4MB时达到极大峰值,然后在8MB时下降,最终在32MB时达到最佳性能;
3.mysqld进程的总虚拟内存(可使用top查看)在sort_buffer_size为512KB时最小,此后一直到16MB都稳步上升,在32MB时急剧上升;
3.查询的平均时间最短时,sort_buffer_size为32MB,该设置使用了大量内存,而缓冲池本来可以更好地利用这些内存;在本示例中,针对测试中所使用的工作负荷,服务器和数据库的特定组合,512KB设置可在性能和所用内存之间提供最佳平衡;
— 课后练习;
— 补充:Extra其它信息;
1.distinct:当MySQL找到第一条匹配的结果值时,就停止该值的查询,然后继续该列其他值的查询;
2.not exists:在左连接中,优化器可以通过改变原有的查询组合而使用的优化方法;当发现一个匹配的行之后,不再为前面的行继续检索,可以部分减少数据访问的次数;
3.const row not found:涉及到的表为空表,里面没有数据;
4.Full scan on NULL key:是优化器对子查询的一种优化方式,无法通过索引访问NULL值的时候会做此优化;
5.Impossible Having:Having子句总是false而不能选择任何列;例如having 1=0;
6.Impossible WHERE:Where子句总是false而不能选择任何列;例如where 1=0;
7.Impossible WHERE noticed after reading const tables:MySQL通过读取“const/system tables”,发现Where子句为false;也就是说:在where子句中false条件对应的表应该是const/system tables;这个并不是MySQL通过统计信息做出的,而是真的去实际访问一遍数据后才得出的结论;当对某个表指定了主键或者非空唯一索引上的等值条件,一个query最多只可能命中一个结果,MySQL在explain之前会优先根据这一条件查找对应记录,并用记录的实际值替换query中所有用到来自该表属性的地方;
8.No matching min/max row:没有行满足如下的查询条件;
9.no matching row in const table:对一个有join的查询,包含一个空表或者没有数据满足一个唯一索引条件;
10.No tables used:查询没有From子句,或者有一个From Dual(dual:虚拟表,是为了满足select…from…习惯)子句;
EXPLAIN SELECT VERSION()
11.Range checked for each record (index map: N):MySQL发现没有好的index,但发现如果进一步获取下一张join表的列的值后,某些index可以通过range等使用;MySQL没找到合适的可用的索引;取代的办法是,对于前一个表的每一个行连接,它会做一个检验以决定该使用哪个索引（如果有的话）,并且使用这个索引来从表里取得记录;这个过程不会很快,但总比没有任何索引时做表连接来得快;
12.Select tables optimized away:当我们使用某些聚合函数来访问存在索引的某个字段时,优化器会通过索引直接一次定位到所需要的数据行完成整个查询;在使用某些聚合函数如min, max的query,直接访问存储结构(B树或者B+树)的最左侧叶子节点或者最右侧叶子节点即可,这些可以通过index解决;Select count(*) from table(不包含where等子句),MyISAM保存了记录的总数,可以直接返回结果,而Innodb需要全表扫描;Query中不能有group by操作;
13.unique row not found:对于SELECT … FROM tbl_name,没有行满足unique index或者primary key;从表中查询id不存在的一个值会显示Impossible WHERE noticed after reading const tables;
14.Using filesort:指MySQL将用外部排序而不是按照index顺序排列结果;数据较少时从内存排序,否则从磁盘排序;Explain不会显示的告诉客户端用哪种排序;
15.Using index:表示MySQL使用覆盖索引避免全表扫描,不需要再到表中进行二次查找数据;注意不要和type中的index类型混淆;
16.Using index for group-by:类似Using index,所需数据只需要读取索引,当query中有group by或distinct子句时,如果分组字段也在索引中,extra就会显示该值;
17.Using temporary:MySQL将创建一个临时表来容纳中间结果;在group by和order by的时,如果有必要的话;例如group by一个非键列,优化器会创建一个临时表,有个按照group by条件构建的unique key,然后对于每条查询结果（忽略group by）,尝试insert到临时表中,如果由于unique key导致insert失败,则已有的记录就相应的updated;例如,name上没有索引,SELECT name,COUNT(*) FROM product GROUP BY name,为了排序,MySQL就需要创建临时表;此时一般还会显示using filesort;
18.Using where:表示MySQL将对storage engine提取的结果进行过滤;例如,price没有index,SELECT * FROM product WHERE price=1300.00;有许多where的条件由于包含了index中的列,在查找的时候就可以过滤,所以不是所有带where子句的查询会显示Using where;
19.Using join buffer:5.1.18版本以后才有的值;join的返回列可以从buffer中获取,与当前表join;
例如:explain select * from t1,t2 where t1.col < 10 and t2.col < 10
20.Scanned N databases:指在处理information_schema查询时,有多少目录需要扫描;
EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES
21.Open_full_table:指示从information_schema查询信息时有关文件开启的优化; Skip_open_table:表信息已经获得,不需要打开;
22.Open_frm_only:只打开.frm文件; Open_trigger_only:只打开.trg文件; Open_full_table:没有优化;.frm,.myd和.myi文件都打开;
23.Using sort_union(…), Using union(…), Using intersect(…):都出现在index_merge读取类型中;
1.Using sort_union:用两个或者两个以上的key提取数据,但优化器无法确保每个key会提取到一个自然排好序的结果,所以为了排除多余的数据,需要额外的处理;
2.例如,customer的state,（lname,fname）是key,但lname不是key,SELECT COUNT(*) FROM customer WHERE (lname = ‘Jones') OR (state = ‘UT'),由于lname上面没有key,所以使用（lname,fname）,使得结果可能不按照顺序,优化器需要额外的一些工作;
3.Using union:用两个或者两个以上的key提取数据,分别取得结果是已排序,通过合并就可以获得正确结果;例如,customer中的state和（lname,fname）是key,SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') OR (state = ‘UT'); Using intersect:用两个或者两个以上的key提取数据,分别取得结果是已排序,通过求交就可以获得正确结果;
4.例如,customer中的state和（lname,fname）是key,SELECT COUNT(state) FROM customer WHERE (lname = ‘Jones' AND fname='John') AND (state = ‘UT');
24.Using where with pushed condition:仅用在ndb上;MySQL Cluster用Condition Pushdown优化改善非索引字段和常量之间的直接比较;condition被pushed down到cluster的数据节点,并在所有数据节点同时估算,把不合条件的列剔除避免网络传输;