关于MYSQL的优化全面详解

Categories: 数据库, 网站相关
Comments: No Comments
Published on: 2011 年 12 月 21 日
锁定事宜

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定,对BDB表进行页级锁定,对InnoDB表进行行级锁定。

在许多情况下,可以根据培训猜测应用程序使用哪类锁定类型最好,但一般很难说出某个给出的锁类型就比另一个好。一切取决于应用程序,应用程序的不同部分可能需要不同的锁类型。

为了确定是否想要使用行级锁定的存储引擎,应看看应用程序做什么并且混合使用什么样的选择和更新语句。例如,大多数Web应用程序执行许多选择,而很少进行删除,只对关键字的值进行更新,并且只插入少量具体的表。基本MySQL MyISAM设置已经调节得很好。

在MySQL中对于使用表级锁定的存储引擎,表锁定时不会死锁的。这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。

对WRITE,MySQL使用的表锁定方法原理如下:
如果在表上没有锁,在它上面放一个写锁。
否则,把锁定请求放在写锁定队列中。
对READ,MySQL使用的锁定方法原理如下:

如果在表上没有写锁定,把一个读锁定放在它上面。
否则,把锁请求放在读锁定队列中。
当一个锁定被释放时,锁定可被写锁定队列中的线程得到,然后是读锁定队列中的线程。

这意味着,如果你在一个表上有许多更新,SELECT语句将等待直到没有更多的更新。

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+

如果INSERT语句不冲突,可以自由为MyISAM表混合并行的INSERT和SELECT语句而不需要锁定。也就是说,你可以在其它客户正读取MyISAM表的时候插入行。如果数据文件中间不包含空闲块,不会发生冲突,因为在这种情况下,记录总是插入在数据文件的尾部。(从表的中部删除或更新的行可能导致空洞)。如果有空洞,当所有空洞填入新的数据时,并行的插入能够重新自动启用。

如果不能同时插入,为了在一个表中进行多次INSERT和SELECT操作,可以在临时表中插入行并且立即用临时表中的记录更新真正的表。

这可用下列代码做到:
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

InnoDB使用行锁定,BDB使用页锁定。对于这两种存储引擎,都可能存在死锁。这是因为,在SQL语句处理期间,InnoDB自动获得行锁定和BDB获得页锁定,而不是在事务启动时获得。

行级锁定的优点:
· 当在许多线程中访问不同的行时只存在少量锁定冲突。
· 回滚时只有少量的更改。
· 可以长时间锁定单一的行。

行级锁定的缺点:
· 比页级或表级锁定占用更多的内存。
· 当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。
· 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表,比其它锁定明显慢很多。
· 用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。

在以下情况下,表锁定优先于页级或行级锁定:
· 表的大部分语句用于读取。
· 对严格的关键字进行读取和更新,你可以更新或删除可以用单一的读取的关键字来提取的一行:
· UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
· DELETE FROM tbl_name WHERE unique_key_col=key_value;
· SELECT 结合并行的INSERT语句,并且只有很少的UPDATE或DELETE语句。
· 在整个表上有许多扫描或GROUP BY操作,没有任何写操作。

不同于行级或页级锁定的选项:
· 版本(例如,为并行的插入在MySQL中使用的技术),其中可以一个写操作,同时有许多读取操作。这说明数据库或表支持数据依赖的不同视图,取决于访问何时开始。其它共同的术语是“时间跟踪”、“写复制”或者“按需复制”。
· 按需复制在许多情况下优先于页级或行级锁定。然而,在最坏的情况下,它可能比使用常规锁定使用更多的内存。
· 除了行级锁定外,你可以使用应用程序级锁定,例如在MySQL中使用GET_LOCK()和RELEASE_LOCK()。这些是建议性锁定,它们只能在运行良好的应用程序中工作。

表锁定事宜

为达到最高锁定速度,除InnoDB和BDB之外,对所有存储引擎,MySQL使用表锁定(而不是页、行或者列锁定)。

对于InnoDB和BDB表,如果你用LOCK TABLES显式锁定表,MySQL只使用表锁定。对于这些表类型,我们建议你根本不要使用LOCK TABLES,因为InnoDB使用自动行级锁定而BDB使用页级锁定来保证事务隔离。

对于大表,对于大多数应用程序,表锁定比行锁定更好,但存在部分缺陷。

表锁定使许多线程同时从一个表中进行读取操作,但如果一个线程想要对表进行写操作,它必须首先获得独占访问。更新期间,所有其它想要访问该表的线程必须等待直到更新完成。

表更新通常情况认为比表检索更重要,因此给予它们更高的优先级。这应确保更新一个表的活动不能“饿死”,即使该表上有很繁重的SELECT活动。

表锁定在这种情况下会造成问题,例如当线程正等待,因为硬盘已满并且在线程可以处理之前必须有空闲空间。在这种情况下,所有想要访问出现问题的表的线程也被设置成等待状态,直到有更多的硬盘空间可用。

表锁定在下面的情况下也存在问题:
· 一个客户发出长时间运行的查询。
· 然后,另一个客户对同一个表进行更新。该客户必须等待直到SELECT完成。
· 另一个客户对同一个表上发出了另一个SELECT语句。因为UPDATE比SELECT优先级高,该SELECT语句等待UPDATE完成,并且等待第1个SELECT完成。

下面描述了一些方法来避免或减少表锁定造成的竞争:
· 试图使SELECT语句运行得更快。你可能必须创建一些摘要(summary)表做到这点。
· 用--low-priority-updates启动mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下,在先前情形的第2个SELECT语句将在UPDATE语句前执行,而不需要等候第1个SELECT完成。
· 可以使用SET LOW_PRIORITY_UPDATES=1语句指定具体连接中的所有更新应使用低优先级。
· 可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级。
· 可以用HIGH_PRIORITY属性给与一个特定的SELECT语句较高优先级。
· 为max_write_lock_count系统变量指定一个低值来启动mysqld来强制MySQL在具体数量的插入完成后临时提高所有等待一个表的SELECT语句的优先级。这样允许在一定数量的WRITE锁定后给出READ锁定。
· 如果你有关于INSERT结合SELECT的问题,切换到使用新的MyISAM表,因为它们支持并发的SELECT和INSERT。
· 如果你对同一个表混合插入和删除,INSERT DELAYED将会有很大的帮助。
· 如果你对同一个表混合使用SELECT和DELETE语句出现问题,DELETE的LIMIT选项可以有所帮助。
· 对SELECT语句使用SQL_BUFFER_RESULT可以帮助使表锁定时间变短。
· 可以更改mysys/thr_lock.c中的锁代码以使用单一的队列。在这种情况下,写锁定和读锁定将具有相同的优先级,对一些应用程序会有帮助。
这里是一些MySQL中表锁定相关的技巧:
· 如果不混合更新与需要在同一个表中检查许多行的选择,可以进行并行操作。
· 可以使用LOCK TABLES来提高速度,因为在一个锁定中进行许多更新比没有锁定的更新要快得多。将表中的内容切分为几个表也可以有所帮助。
· 如果在MySQL中表锁定时遇到速度问题,可以将你的表转换为InnoDB或BDB表来提高性能。

优化数据库结构

设计选择
MySQL将行数据和索引数据保存在不同的文件中。许多(几乎所有)其它数据库将行数据和索引数据混合保存在用一个文件中。我们认为MySQL 选择对广范围的现代系统更好一些。

保存行数据的另一种方式是将每个列的信息保存在单独的区域(例如SDBM和Focus)。这样会对每个访问多个列的查询造成性能问题。因为当访问多个列时退化得很快,我们认为该模型对一般数据库不合适。

更常见的情形是索引和数据保存在一起(例如Oracle/Sybase)。在这种情况下,你可以在索引的叶级页找到行的信息。该布局比较好的事情是在许多情况下,根据索引缓存得怎样,可以保存一个硬盘读取。该布局的不利之处表现在:
· 表扫描要慢得多,因为你必须通读索引以获得数据。
· 你不能只使用表来检索查询的数据。
· 你需要使用更多的空间,因为你必须从节点复制索引(你不能保存节点上的行)。
· 删除要经过一段时间后才退化表(因为删除时通常不会更新节点上的索引)。
· 只缓存索引数据会更加困难。

使你的数据尽可能小

最基本的优化之一是使表在磁盘上占据的空间尽可能小。这能给出巨大的改进,因为磁盘读入较快,并且在查询执行过程中小表的内容被处理时占用较少的主存储器。如果在更小的列上做索引,索引也占据较少的资源。

MySQL支持许多不同的存储引擎(表类型)和行格式。对于每个表,可以确定使用哪个存储引擎和索引方法。为应用程序选择合适的表格式可以大大提高性能。

可以使用下面的技术可以使表的性能更好并且使存储空间最小:
· 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。
· 尽可能使用较小的整数类型使表更小。例如,MEDIUMINT经常比INT好一些,因为MEDIUMINT列使用的空间要少25%。
· 如果可能,声明列为NOT NULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL,应该毫无疑问使用它,只是避免 默认地在所有列上有它。
· 对于MyISAM表,如果没有任何变长列(VARCHAR、TEXT或BLOB列),使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed,也可以提示想使用固定长度的行。
· 在MySQL/InnoDB中,InnoDB表使用更紧凑的存储格式。在以前版本的MySQL中,InnoDB记录包含一些冗余信息,例如列数目和每个列的长度,即使对于固定大小的列。默认情况,创建的表为紧凑格式(ROW_FORMAT=COMPACT)。如果想要降级旧版本的MySQL/InnoDB,可以用ROW_FORMAT=REDUNDANT要求旧的格式。
· 紧凑的InnoDB格式也改变了包含UTF-8数据的CHAR列的保存方式。在ROW_FORMAT=REDUNDANT格式中,UTF-8 CHAR(n)占用3*n字节,假定UTF-8编码的字符的最大长度是3字节。许多语言可以主要用单字节UTF-8字符来编写,固定的存储长度通常会浪费空间。通过根据需要剥离尾部的空格,ROW_FORMAT=COMPACT格式为这些列分配可变数量的n..3*n字节。最小存储长度按顺序保存为n字节,以在典型情况下帮助更新。
· 每张表的主索引应该尽可能短。这使一行的识别容易而有效。
· 只创建你确实需要的索引。索引对检索有好处,但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表,对它们做一个索引。第一个索引部分应该是最常用的列。如果从表中选择时总是使用许多列,应该首先以更多的副本使用列以获得更好的索引压缩。
· 如果很可能一个索引在头几个字符上有唯一的前缀,仅仅索引该前缀比较好。·MySQL支持对一个字符列的最左边部分创建一个索引。更短的索引会更快,不仅因为它们占较少的磁盘空间,而且因为它们将在索引缓存中提供更多的访问,因此磁盘搜索更少。
· 在一些情形下,将一个经常被扫描的表分割为2个表是有益的。特别是如果它是一个动态格式的表,并且可能使用一个扫描表时能用来找出相关行的较小静态格式的表。

列索引

所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。

根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。

在索引定义中用col_name(length)语法,你可以创建一个只使用CHAR或VARCHAR列的第1个length字符的索引。按这种方式只索引列值的前缀可以使索引文件小得多。

MyISAM和InnoDB存储引擎还支持对BLOB和TEXT列的索引。当索引一个BLOB或TEXT列时,你必须为索引指定前缀长度。例如:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
在MySQL 5.1中,对于MyISAM和InnoDB表,前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量,而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行,不支持局部(前缀)索引。

也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R-树。默认情况MEMORY(HEAP)存储引擎使用hash索引,但也支持B-树索引。

多列索引

MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型,可以索引列的前缀。多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。

MySQL按这样的方式使用多列索引:当你在WHERE子句中为索引的第1个列指定已知的数量时,查询很快,即使你没有指定其它列的值。

假定表具有下面的结构:

CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);

name索引是一个对last_name和first_name的索引。索引可以用于为last_name,或者为last_name和first_name在已知范围内指定值的查询。因此,name索引用于下面的查询:

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
WHERE last_name='Widenius'
AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
WHERE last_name='Widenius'
AND first_name >='M' AND first_name < 'N';

然而,name索引不用于下面的查询:

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
WHERE last_name='Widenius' OR first_name='Michael';

MySQL使用索引提高查询性能的方式将在“MySQL如何使用索引”中讨论。

MySQL如何使用索引

索引用于快速找出在某个列中有一特定值的行。不使用索引,MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,MySQL能快速到达一个位置去搜寻到数据文件的中间,没有必要看所有数据。如果一个表有1000行,这比顺序读取至少快100倍。注意如果你需要访问大部分行,顺序读取要快得多,因为此时我们避免磁盘搜索。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树,并且MEMORY表还支持hash索引。字符串自动地压缩前缀和结尾空格。总的来说,按后面的讨论使用索引。本节最后描述hash索引(用于MEMORY表)的特征。

索引用于下面的操作:
· 快速找出匹配一个WHERE子句的行。
· 删除行。如果可以在多个索引中进行选择,MySQL通常使用找到最少行的索引。
· 当执行联接时,从其它表检索行。
· 对具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化,检查是否对索引中在key_col之前发生所有关键字元素使用了WHERE key_part_# = constant。在这种情况下,MySQL为每个MIN()或MAX()表达式执行一次关键字查找,并用常数替换它。如果所有表达式替换为常量,查询立即返回。例如:
· SELECT MIN(key_part2),MAX(key_part2)
· FROM tbl_name WHERE key_part1=10;
· 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有关键字元素后面有DESC,关键字以倒序被读取。
· 在一些情况中,可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列,为了更快,可以从索引树检索出值。
· SELECT key_part3 FROM tbl_name
· WHERE key_part1=1
假定你执行下面的SELECT语句:
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果col1和col2上存在一个多列索引,可以直接取出相应行。如果col1和col2上存在单列索引,优化器试图通过决定哪个索引将找到更少的行来找出更具限制性的索引并且使用该索引取行。

如果表有一个多列索引,优化器可以使用最左面的索引前缀来找出行。例如,如果有一个3列索引(col1,col2,col3),则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。

如果列不构成索引最左面的前缀,MySQL不能使用局部索引。假定有下面显示的SELECT语句。
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果 (col1,col2,col3)有一个索引,只有前2个查询使用索引。第3个和第4个查询确实包括索引的列,但(col2)和(col2,col3)不是 (col1,col2,col3)的最左边的前缀。

也可以在表达式通过=、>、>=、<、<=或者BETWEEN操作符使用B-树索引进行列比较。如果LIKE的参数是一个不以通配符开头的常量字符串,索引也可以用于LIKE比较。例如,下面的SELECT语句使用索引: SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'; 在第1个语句中,只考虑带'Patrick' <=key_col < 'Patricl'的行。在第2个语句中,只考虑带'Pat' <=key_col < 'Pau'的行。 下面的SELECT语句不使用索引: SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'; SELECT * FROM tbl_name WHERE key_col LIKE other_col; 在第一条语句中,LIKE值以一个通配符字符开始。在第二条语句中,LIKE值不是一个常数。 如果使用... LIKE '%string%'并且string超过3个字符,MySQL使用Turbo Boyer-Moore算法初始化字符串的模式然后使用该模式来更快地进行搜索。 如果col_name被索引,使用col_name IS NULL的搜索将使用索引。 任何不跨越WHERE子句中的所有AND级的索引不用于优化查询。换句话说,为了能够使用索引,必须在每个AND组中使用索引前缀。 下面的WHERE子句使用索引: ... WHERE index_part1=1 AND index_part2=2 AND other_column=3 /* index = 1 OR index = 2 */ ... WHERE index=1 OR A=10 AND index=2 /* optimized like "index_part1='hello'" */ ... WHERE index_part1='hello' AND index_part3=5 /* Can use index on index1 but not on index2 or index3 */ ... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3; 下面的WHERE子句不使用索引: /* index_part1 is not used */ ... WHERE index_part2=1 AND index_part3=2 /* Index is not used in both parts of the WHERE clause */ ... WHERE index=1 OR A=10 /* No index spans all rows */ ... WHERE index_part1=1 OR index_part2=10 有时MySQL不使用索引,即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下,表扫描可能会更快些,因为需要的搜索要少)。然而,如果此类查询使用LIMIT只搜索部分行,MySQL则使用索引,因为它可以更快地找到几行并在结果中返回。 Hash索引还有一些其它特征: · 它们只用于使用=或<=>操作符的等式比较(但很快)。它们用于比较 操作符,例如发现范围值的<。 · 优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目)。 · MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表,会影响一些查询。 · 只能使用整个关键字来搜索一行。(用B-树索引,任何关键字的最左面的前缀可用来找到行)。

MyISAM键高速缓冲

为了使硬盘I/O最小化,MyISAM存储引擎使用一个被许多数据库管理系统使用的策略。它使用一个缓存机制将经常访问的表锁在内存中:
· 对于索引块,维护一个称之为键高速缓冲(或键高速缓冲区)的特殊结构。该结构包含大量块缓存区,其中放置了最常用的索引块。
· 对于数据块,MySQL不使用特殊缓存。而使用原生的操作系统文件系统的缓存。

本节首先描述了MyISAM键高速缓冲的基本操作。然后讨论了提高 键高速缓冲性能并使你更好地控制缓存操作的最新的更改:
· 多个线程可以并行访问缓存。
· 可以设置多个键高速缓冲,并将表索引指定给具体缓存。

可以使用key_buffer_size系统变量控制 键高速缓冲的大小。如果该变量设置为零,不使用键高速缓冲。如果key_buffer_size值太小不能分配最小数量的块缓存区(8),也不使用 键高速缓冲。

如果键高速缓冲不工作,只使用操作系统提供的原生文件系统缓存区访问索引文件。(换句话说,使用与表数据块相同的策略表来访问索引块)。

索引块是一个连续的访问MyISAM索引文件的单位。通常一个索引块的大小等于索引B-树节点的大小。(在硬盘上使用B-树数据结构表示索引。树底部的节点为叶子节点。叶子节点上面的节点为非叶子节点)。

键高速缓冲结构中的所有块缓存区大小相同。该大小可以等于、大于或小于表索引块的大小。通常这两个值中的一个是另一个的几倍。

当必须访问表索引块中的数据时,服务器首先检查是否它可以用于键高速缓冲中的某些块缓存区。如果适用,服务器访问键高速缓冲中的数据而不是硬盘上的数据。也就是说,从缓存读取或写入缓存,而不是从硬盘读写。否则,服务器选择一个包含一个不同的表索引块的缓存块缓存区,并用需要的表索引块的拷贝替换那里的数据。一旦新的索引块位于缓存中,可以访问索引数据。

如果用于替换的块已经被修改了,块被视为“脏了”。在这种情况下,在替换前,其内容被刷新到它来自的表索引。

通常服务器遵从LRU(最近最少使用)策略:当选择一个块用于替换时,它选择最近最少使用的索引块。为了使该选择更容易, 键高速缓冲模块维护所有使用的块的专门队列(LRU链)。当访问块时,它被放到队列最后。当块需要替换时,队列开头的块是最近最少使用的块,并成为第1个候选者。

共享键高速缓冲访问

在以下条件下,线程可以同时访问键高速缓冲缓存区:
· 没有被更新的缓存区可以被多个线程访问。
· 正被更新的缓存区让需要使用它的线程等待直到更新完成。
· 多个线程可以发起请求替换缓存块,只要它们不彼此干扰(也就是说,只要它们需要不同的索引块,并且使不同的缓存块被替换)。
对键高速缓冲的共享访问允许服务器大大提高吞吐量。

多键高速缓冲

对键高速缓冲的共享访问可以提高性能但不能完全消除线程之间的竟争。它们仍然竞争对键高速缓冲缓存区的访问进行管理的控制结构。为了进一步降低 键高速缓冲访问竟争,MySQL 5.1还提供了多个键高速缓冲,允许你为不同的键高速缓冲分配不同的表索引。

有多个键高速缓冲时,当为给定的MyISAM表处理查询时,服务器必须知道使用哪个缓存。默认情况,所有MyISAM表索引被缓存到默认 键高速缓冲中。要想为具体键高速缓冲分配表索引,应使用CACHE INDEX语句(参见13.5.5.1节,“CACHE INDEX语法”)。

例如,下面的语句将表t1、t2和t3的索引分配给名为hot_cache的 键高速缓冲:

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status | OK |
| test.t2 | assign_to_keycache | status | OK |
| test.t3 | assign_to_keycache | status | OK |
+---------+--------------------+----------+----------+

可以用SET GLOBAL参数设置语句或使用服务器启动选项设置在CACHE INDEX语句中引用的键高速缓冲的大小来创建键高速缓冲。例如:

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
要想删除键高速缓冲,将其大小设置为零:

mysql> SET GLOBAL keycache1.key_buffer_size=0;
请注意不能删除默认键高速缓冲。删除默认键高速缓冲的尝试将被忽略:

mysql> set global key_buffer_size = 0;

mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8384512 |
+-----------------+---------+

键高速缓冲变量是结构式系统变量,有一个名和组件。对于keycache1.key_buffer_size,keycache1是缓存变量名,key_buffer_size是缓存组件。关于引用结构式 键高速缓冲系统变量所使用的语法的描述,参见9.4.1节,“结构式系统变量”

默认情况下,表索引被分配给服务器启动时创建的主要(默认)键高速缓冲。当 键高速缓冲被删除后,所有分配给它的索引被重新分配给默认键高速缓冲。

对于一个忙的服务器,我们建议采用使用三个键高速缓冲的策略:
· 占用为所有键高速缓冲分配的空间的20%的“热”键高速缓冲。该缓存用于频繁用于搜索但没有更新的表。
· 占用为所有键高速缓冲分配的空间的20%的“冷”键高速缓冲。该缓存用于中等大小、大量修改的表,例如临时表。
· 占用键高速缓冲空间的20%的“温”键高速缓冲。使用它作为默认 键高速缓冲,默认情况被所有其它表使用。

使用3个键高速缓冲有好处的一个原因是对一个键高速缓冲结构的访问不会阻挡对其它的访问。访问分配给一个缓存的表的查询不会与访问分配给其它缓存的表的查询竞争。由于其它原因也会提高性能:
· 热缓存只用于检索查询,因此其内容决不会被修改。结果是,无论何时需要从硬盘上拉入索引块,选择用于替换的缓存块的内容不需要先刷新。
· 对于分配给热缓存的索引,如果没有查询需要索引扫描,很有可能对应索引B-树的非叶子节点的索引块仍然在缓存中。
· 当更新的节点位于缓存中并且不需要先从硬盘读入时,为临时表频繁执行的更新操作会执行得更快。如果临时表的索引的大小可以与冷键高速缓冲相比较,很可能更新的节点位于缓存中。

CACHE INDEX在一个表和 键高速缓冲之间建立一种联系,但每次服务器重启时该联系被丢失。如果你想要每次服务器重启时该联系生效,一个发办法是使用选项文件:包括配置 键高速缓冲的变量设定值,和一个init-file选项用来命名包含待执行的CACHE INDEX语句的一个文件。例如:
key_buffer_size = 4G
hot_cache.key_buffer_size = 2G
cold_cache.key_buffer_size = 2G
init_file=/path/to/data-directory/mysqld_init.sql
每次服务器启动时执行mysqld_init.sql中的语句。该文件每行应包含一个SQL语句。下面的例子分配几个表,分别对应hot_cache和cold_cache:
CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache
CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache

中点插入策略

默认情况,键高速缓冲管理系统采用LRU策略选择要收回的键高速缓冲块,但它也支持更复杂的方法,称之为“中点插入策略”。

当使用中点插入策略时,LRU链被分为两个部分:一条热子链和一条温子链。两部分之间的划分点不固定,但 键高速缓冲管理系统关注温部分不“太短”,总是包含至少key_cache_division_limit比例的 键高速缓冲块。key_cache_division_limit是结构式 键高速缓冲变量的一个组件,因此其值是一个可以根据每个缓存进行设置的参数。

当一个索引块从表中读入键高速缓冲,它被放入温子链的末端。经过一定量的访问后(访问块),它被提升给热子链。目前,需要用来提升一个块(3)的访问次数与所有索引块的相同。

提升到热子链的块被放到子链的末端。块然后在该子链中循环。如果块在子链的开头停留足够长的时间,它被降到温链。该时间由键高速缓冲key_cache_age_threshold组件的值确定。

对于包含N个块的 键高速缓冲,阈值表示,热子链开头的没有在最后N *key_cache_age_threshold/100次访问中被访问的块将被移动到温子链开头。该块然后变为第1个挤出的候选者,因为替换的块总是来自温子链的开头。

中点插入策略允许你将更有价值的块总是在缓存中。如果你想使用简单的LRU策略,使key_cache_division_limit值保持其默认值100。

若执行的查询要求索引扫描有效推出所有索引块对应有数值的高级B-树节点的缓存,中点插入策略可以帮助提高性能。要想避免,必须使用中点插入策略,而key_cache_division_limit设置为远小于100。然后在索引扫描操作过程中,有数值的经常访问的节点被保留在热子链中。

索引预加载

如果键高速缓冲内有足够的块以容纳整个索引的块,或者至少容纳对应其非叶节点的块,则在使用前,预装含索引块的键高速缓冲很有意义。预装可以以更有效的方式将表索引块放入 键高速缓冲缓存区中:通过顺序地从硬盘读取索引块。

不进行预装,块仍然根据查询需要放入键高速缓冲中。尽管块将仍然在缓存中(因为有足够的缓存区保存它们),它们以随机方式从硬盘上索取,而不是以顺序方式。

要想将索引预装到缓存中,使用LOAD INDEX INTO CACHE语句。例如,下面的语句可以预装表t1和t2索引的节点(索引块):

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status | OK |
| test.t2 | preload_keys | status | OK |
+---------+--------------+----------+----------+

IGNORE LEAVES修改器只允许预装索引非叶节点所用的块。这样,上述的语句预装t1中的所有索引块,但只预装t2中的非叶节点对应的块。

如果已经使用CACHE INDEX语句为一个索引分配了一个键高速缓冲,预装可以将索引块放入该缓存。否则,索引被装入默认键高速缓冲。

键高速缓冲块大小

可以使用key_cache_block_size变量为具体的 键高速缓冲指定块缓存区的大小。这样允许为索引文件调节I/O操作的性能。

当读缓存区的大小等于原生操作系统I/O缓存区的大小时,可以获得I/O操作的最佳性能。但是将关键字节点的大小设置为等于I/O缓存区的大小并不总是能保证最佳整体性能。当读取大的叶节点时,服务器读入大量的不需要的数据,结果防止读入其它叶子的节点。

目前,你不能控制表内索引块的大小。该大小由服务器在创建.MYI索引文件时设置,取决于表定义中索引的关键字大小。在大多数情况下,它被设置为与I/O缓存区大小相等。

重构键高速缓冲

键高速缓冲可以通过更新其参数值随时重新构建。例如:
mysql> SET GLOBAL cold_cache。key_buffer_size=4*1024*1024;

如果你为key_buffer_size或key_cache_block_size键高速缓冲组件分配的值与组件当前的值不同,服务器将毁掉缓存的旧结构并根据新值创建一个新的。如果缓存包含任何脏的块,服务器在销毁前将它们保存到硬盘上并重新创建缓存。如果你设置其它 键高速缓冲参数,则不会发生重新构建。

当重新构建键高速缓冲时,服务器首先将任何脏缓存区的内容刷新到硬盘上。之后,缓存内容不再需要。然而,重新构建并不阻塞需要使用分配给缓存的索引的查询。相反,服务器使用原生文件系统缓存直接访问表索引。文件系统缓存不如使用 键高速缓冲有效,因此尽管查询可以执行,但速度会减慢。缓存被重新构建后,它又可以缓存分配给它的索引了,并且索引不再使用文件系统缓存。

MyISAM索引统计集合

存储引擎搜集优化器使用的表的统计信息。表统计基于数数值组,其中数数值组是一系列有相同的关键字前缀值的记录。对于优化器,重要的统计即为数数值组的平均大小。

MySQL用下述方式使用平均数数值组:
· 估计必须为每个ref访问读取多少行
· 估计部分联接将产生多少行;也就是说,下述形式的操作将产生的行数:
· (...) JOIN tbl_name ON tbl_name.key = expr
随着索引的平均数数值组大小的增加,索引将更没有用,因为每个查找的平均行数增加:为了让索引有利于优化目的,最好是每个索引值对应表内的少量行数。当某个给定的索引值产生较多行时,索引更加没有用,MySQL更不可能使用它。

平均数数值组大小与表的集的势相关,即数数值组的数目。SHOW INDEX语句显示集的势值(基于N/S),其中N是表内的记录数,S是平均数数值组大小。该比例产生表内数数值组的大约数。

对于基于<=>比较 操作符的联接,NULL并不视为与任何其它值不同:NULL <=> NULL,正如对于其它N ,N <=> N。

然而,对于基于=操作符的联接,NULL与非NULL值不同:当expr1或expr2(或两者)为NULL时,expr1 = expr2不为真。这样影响比较形式tbl_name.key = expr的ref访问:如果expr当前的值为NULL,MySQL不会访问表,因为比较不能为真。

对于=比较,表内有多少NULL值并不重要。为了优化目的,相关值为非NULL数值组的平均大小。然而,MySQL目前不允许搜集或使用该平均大小。

对于MyISAM表,你可以使用myisam_stats_method系统变量部分控制表统计信息的搜集。该变量有两个可能的不同值,如下所示:
· 当myisam_stats_method为nulls_equal时,所有NULL值被视为相等的(也就是说,它们都形成一个数值组)。
如果NULL数值组大小远大于平均非NULL数值组大小,该方法向上倾斜平均数数值组大小。这样使索引对于优化器来说比它实际为查找非NULL值的联接更加没有用。结果是,nulls_equal方法会使优化器进行ref访问时本应使用索引而没有使用。
· 当myisam_stats_method为nulls_unequal时,NULL值不视为相同。相反,每个NULL值形成一个单独的数值组,大小为1。

如果你有许多NULL值,该方法向下倾斜平均数数值组大小。如果平均非NULL数值组较大,统计大小为1的每个组的NULL值会使优化器过高估计查找非NULL值的联接的索引值。结果是,当其它方法会更好时,nulls_unequal方法会使优化器为ref查找使用该索引。

如果你要使用许多使用<=>而不是=的联接,在比较过程中NULL值并不特殊,一个NULL等于另一个NULL。在这种情况下,nulls_equal是合适的统计方法。

myisam_stats_method系统变量有全局和会话值。设置全局值会影响MyISAM 为所有MyISAM表的统计的搜集。设置会话值只影响当前客户连接的统计的搜集。这说明你可以强制用给定的方法重新生成表的统计的搜集,而不需要因为设置myisam_stats_method的会话值而影响其它客户。

可以使用下面任一方法来重新生成表的统计信息:
· 设置myisam_stats_method,然后执行CHECK TABLE语句
· 执行myisamchk --stats_method=method_name --analyze
· 更改表,使其统计信息不为最新(例如,插入一行然后删除它),然后设置myisam_stats_method并执行ANALYZE TABLE语句
使用myisam_stats_method的一些警告:

你可以强制显式搜集表的统计信息,如上所述。然而,MySQL也可以自动搜集统计信息。例如,如果在为表执行语句的过程中,一些语句修改了表,MySQL可以搜集统计信息。(例如,大批插入或删除,或者执行ALTER TABLE语句时可能发生)。如果发生,使用myisam_stats_method此时所有的值搜集统计信息。这样,如果你使用一个方法搜集统计信息,但当后面自动搜集一个表的统计信息时myisam_stats_method被设置为另一个方法,将使用其它方法。

对于给定的MyISAM表,还不能说出使用哪个方法来产生统计信息。
myisam_stats_method只适合MyISAM表。其它存储引擎只有一个方法来搜集表的统计信息。通常它接近于nulls_equal方法。

MySQL如何计算打开的表

当运行mysqladmin status时,将看见象这样的一些东西:
Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

如果你仅有6个表,Open tables值为12可能有点令人困惑。
MySQL是多线程的,因此许多客户可以同时在同一个表上进行查询。为了使多个客户线程在同一个表上有不同状态的问题减到最小,表被每个并发进程独立地打开。这样需要额外的内存但一般会提高性能。对于MyISAM表,数据文件需要为每个打开表的客户提供一个额外的文件描述符。(索引文件描述符在所有线程之间共享)。

下一节中提供了该主题的更多的信息。

MySQL如何打开和关闭表

table_cache、max_connections和max_tmp_tables系统变量影响服务器保持打开的文件的最大数量。如果你增加这些值其中的一个或两个,会遇到操作系统为每个进程打开文件描述符的数量强加的限制。许多操作系统允许你增加打开的文件的限制,尽管该方法随系统的不同而不同。查阅操作系统文档以确定是否可以增加限制以及如何操作。

table_cache与max_connections有关。例如,对于200个并行运行的连接,应该让表的缓存至少有200 * N,这里N是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符。

确保操作系统可以处理table_cache设置所指的打开的文件描述符的数目。如果table_cacheis设得太高,MySQL可能为文件描述符耗尽资源并拒绝连接,不能执行查询,并且很不可靠。还必须考虑到MyISAM存储引擎需要为每个打开的表提供两个文件描述符。可以在mysqld_safe中使用--open-files-limit启动选项来增加MySQL适用的文件描述符的数量。

打开表的缓存可以保持在table_cache条。 默认为64;可以用mysqld的--table_cache选项来更改。请注意 MySQL可以临时打开更多的 表以执行查询。

在下面的条件下,未使用的表将被关闭并从表缓存中移出:
· 当缓存满了并且一个线程试图打开一个不在缓存中的表时。
· 当缓存包含超过table_cache个条目,并且缓存中的表不再被任何线程使用。
· 当表刷新操作发生。当执行FLUSH TABLES语句或执行mysqladmin flush-tables或mysqladmin refresh命令时会发生。

当表缓存满时,服务器使用下列过程找到一个缓存入口来使用:
· 当前未使用的表被释放,以最近最少使用顺序。
· 如果缓存满了并且没有表可以释放,但是一个新表需要打开,缓存必须临时被扩大。

如果缓存处于一个临时扩大状态并且一个表从在用变为不在用状态,它被关闭并从缓存中释放。

对每个并发访问打开一个表。这意味着,如果2个线程访问同一个表或在同一个查询中访问表两次(例如,将表连接为自身时),表需要被打开两次。每个并行的打开要求在表缓存中有一个条目。任何表的第一次打开占2个文件描述符:一个用于数据文件另一个用于索引文件。表的每一次额外使用仅占一个数据文件的文件描述符。索引文件描述符在所有线程之间共享。

如果你正用HANDLER tbl_name OPEN语句打开一个表,将为该线程专门分配一个表。该表不被其它线程共享,只有线程调用HANDLER tbl_name CLOSE或线程终止后才被关闭。表关闭后,被拉回表缓存中(如果缓存不满)。

可以通过检查mysqld的状态变量Opened_tables确定表缓存是否太小:

mysql> SHOW STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741 |
+---------------+-------+

如果值很大,即使你没有发出许多FLUSH TABLES语句,也应增加表缓存的大小。

在同一个数据库中创建多个表的缺陷

如果在同一个数据库目录中有许多MyISAM表,打开、关闭和创建操作将会很慢。如果对许多不同的表执行SELECT语句,当表缓存满时,将有一点开销,因为对每个必须打开的表,另外一个必须被关闭。可以通过使表缓存更大些来减少这个开销。

优化MySQL服务器

系统因素和启动参数的调节

我们从系统级因素开始,因为必须尽早地进行部分决策以获得较大性能。在其它情况下,快速浏览该节就足够了。但是,了解一下更改该层次的参数能够获得多少性能提高是很有意义的。

使用的操作系统很重要。为了更好地使用多CPU机器,应使用Solaris(因为其线程工作得很好)或Linux(因为2.4和以后的内核有很好的SMP支持)。请注意默认情况旧的Linux内核有一个2GB的文件大小限制。如果有这样的一个内核并且需要文件大于2GB,应得到ext2文件系统的大文件支持(LFS)补丁。其它文件系统例如ReiserFS和XFS没有此2GB限制。

将MySQL用于生产前,我们建议你在想用的平台上对它进行测试。
其它技巧:
· 如果有足够的RAM,可以移除所有的交换设备。有些操作系统即使有自由内存也使用交换设备。
· 使用--skip-external-locking MySQL选项以避免外部锁定。该选项默认开启。

请注意只要你只运行一个服务器,--skip-external-locking选项不影响MySQL的功能。只要记住运行myisamchk之前关闭服务器(或锁定并刷新相关表)。在一些系统上该选项是强制的,因为在任何情况下外部锁定均不工作。

不能使用--skip-external-locking的唯一情况是对相同的数据运行多个MySQL服务器(非客户)的情况,或者如果没有事先告诉服务器刷新并锁定一个表即运行myisamchk来检查(非修复)该表。请注意一般不建议使用多个MySQL服务器来并行访问相同的数据,除了使用MySQL Cluster时。

即使使用--skip-external-locking,仍然可以使用LOCK TABLES和UNLOCK TABLES。

调节服务器参数

可以用这个命令得到mysqld服务器 默认缓存区的大小:
shell> mysqld --verbose --help
这个命令生成所有mysqld选项和可配置变量的列表。输出包括 默认值并且看上去象这样:

help TRUE
abort-slave-event-count 0
allow-suspicious-udfs FALSE
auto-increment-increment 1
auto-increment-offset 1
automatic-sp-privileges TRUE
......

如果有一个mysqld服务器正在运行,通过连接它并执行这个命令,可以看到实际上使用的变量的值:
mysql> SHOW VARIABLES;
还可以通过下面的语句看到运行服务器的统计和状态指标:
mysql>SHOW STATUS;
使用mysqladmin还可以获得系统变量和状态信息:
shell> mysqladmin variables
shell> mysqladmin extended-status

MySQL使用完全可以升级的算法,因此通常运行时可以用很少的内存。然而,通常情况若给MySQL更多的内存性能会更好。

当调节MySQL服务器时,要配置的两个最重要的变量是key_buffer_size和table_cache。在试图更改其它变量前你应先确信已经适当地配置了这些变量。

下面的例子显示了部分典型的不同的运行时配置的变量值。
· 如果至少有256MB内存和许多表,想要在中等数量的客户时获得最大性能,应使用:
· shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \
· --sort_buffer_size=4M --read_buffer_size=1M &
· 如果只有128MB内存和少量表,但仍然要进行大量的排序,可以使用:
· shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
如果有许多并行连接,交换问题会发生,除非mysqld已经配置成为每个连接分配很少的内存。如果有足够的内存用于所有连接,mysqld会执行得更好。
· 对于少量内存和大量连接,应使用:
· shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
· --read_buffer_size=100K &
或甚至为:
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
--table_cache=32 --read_buffer_size=8K \
--net_buffer_length=1K &
如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作,应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。

如果已经安装了MySQL,support-files目录包含一些不同的my.cnf示例文件:my-huge.cnf、my-大.cnf、my-medium.cnf和my-small.cnf。可以使用这些文件来优化系统。

请注意如果在命令行中为mysqld或mysqld_safe指定一个选项,它只在该次服务器调用中保持有效。要想每次服务器运行时使用该选项,将它放在选项文件中。

要想看参数更改的效果,应执行:
shell> mysqld --key_buffer_size=32M --verbose ---help
变量值列于输出的最后。确保--verbose和---help选项在最后。否则,在命令行中列于它们后面的选项的效果不会反映到输出中。

控制查询优化器的性能

查询优化器的任务是发现执行SQL查询的最佳方案。因为“好”方案和“坏”方案之间的性能差别会巨大(也就是说,秒相对于小时或甚至天),大多数查询优化器,包括MySQL的查询优化器,总或多或少地在所有可能的查询评估方案中搜索最佳方案。对于联接查询,MySQL优化器所调查的可能的方案数随查询中所引用的表的数目呈指数增长。对于小数量的表(典型小于7-10),这不是一个问题。然而,当提交的查询更大时,查询优化所花的时间会很容易地成为服务器性能的主要瓶颈。

查询优化的一个更加灵活的方法是允许用户控制优化器详尽地搜索最佳查询评估方案。一般思想是优化器调查的方案越少,它编译一个查询所花费的时间越少。另一方面,因为优化器跳过了一些方案,它可能错过一个最佳方案。

优化器关于方案数量评估的行为可以通过两个系统变量来控制:
· optimizer_prune_level变量告诉优化器根据对每个表访问的行数的估计跳过某些方案。我们的试验显示该类“有根据的猜测”很少错过最佳方案,并且可以大大降低查询编辑次数。这就是为什么默认情况该选项为on(optimizer_prune_level=1)。然而,如果你认为优化器错过了一个更好的查询方案,则该选项可以关闭(optimizer_prune_level=0),风险是查询编辑花费的时间更长。请注意即使使用该启发,优化器仍然可以探测呈指数数目的方案。
· optimizer_search_depth变量告诉优化器对于每个未完成的“未来的”方案,应查看多深,以评估是否应对它进一步扩大。optimizer_search_depth值较小会使查询编辑次数大大减小。例如,如果optimizer_search_depth接近于查询中表的数量,对12、13或更多表的查询很可能需要几小时甚至几天的时间来编译。同时,如果用optimizer_search_depth等于3或4编辑,对于同一个查询,编译器编译时间可以少于1分钟。如果不能确定合理的optimizer_search_depth值,该变量可以设置为0,告诉优化器自动确定该值。

编译和链接怎样影响MySQL的速度

下列大多数测试是在Linux上并用MySQL基准进行的,但是它们能对其它操作系统和工作负载给出一些指示。

当你用-static链接时,可以得到最快的可执行文件。

在Linux上,最好用pgcc和-O3编译服务器。为了用这些选项编译“sql_yacc.cc”,需要大约200M内存,因为gcc或pgcc需要大量的内存使所有函数嵌入(inline)。在配置MySQL时,也应该设定CXX=gcc以避免包括libstdc++库(它不需要)。请注意对于某些版本的pgcc,生成的二进制只能运行在真Pentium处理器上,即使你使用编译器选项说明你想让最终的代码在所有x586-类处理器上工作(例如AMD)。

只通过使用一个较好的编译器或较好的编译器选项,在应用中能得到10-30%的加速。如果你自己编译SQL服务器,这特别重要!

当我们测试Cygnus CodeFusion或Fujitsu编译器时,二者均还没足够不出错来让MySQL启用优化进行编译。

标准MySQL二进制分发编译为支持所有字符集。当你自己编译MySQL时,应只包括将使用的字符集的支持。通过configure的--with-charset选项来控制。

这里是我们做过的一些测量表:
· 如果你使用pgcc并用-O6编译,mysqld服务器比用gcc 2.95.2快11%。
· 如果你动态地链接(没有-static),在Linux中结果慢了13%。注意你仍能在客户应用程序中使用动态链接MySQL库。只有服务器对性能是关键的。
· 如果你用strip mysqld剥离mysqld二进制,生成的二进制可以快4%。
· 对于在同一主机上运行的客户与服务器之间的连接,如果你使用TCP/IP而非Unix套接字文件进行连接,结果慢7.5%。(在Unix中,如果你连接localhost主机,MySQL默认使用一个套接字文件)。
· 对于从客户到服务器的TCP/IP连接,从另一台主机连接一台远程服务器要比连接同一主机上的服务器慢8-11%,即使通过100Mb/s以太网进行连接。
· 当使用安全连接运行我们的基准测试时(所有数据用内部SSL支持进行加密),性能比未加密连接慢55%。
· 如果你用--with-debug=full编译,大多数查询慢20%。部分查询时间会很长;例如,MySQL基准的运行要慢35%。如果你使用--with-debug(没有=full),速度只下降15%。对于用--with-debug=full编译的mysqld版本,可以用--skip-safemalloc选项启动以便在运行时禁用内存检查。执行速度则接近用--with-debug配置的时候。
· 在Sun UltraSPARC-Iie上,用Forte 5.0编译的服务器比用gcc 3.2编译的要快4%。
· 在Sun UltraSPARC-Iie上,用Forte 5.0编译的32位模式服务器比64位模式服务器要快4%。
· 用gcc 2.95.2编译带-mcpu=v8 -Wa的UltraSPARC,使用-xarch=v8plusa选项性能会提高4%。
· 在Solaris 2.5.1上,在单个处理器上MIT-pthreads比带原生线程的Solaris慢8-12%。如果有更大的负载/cpus,差别应该更大。
· 在Linux-x86上使用gcc编译而不用帧指针(-fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp),可以使mysqld快1-4%。

MySQL AB提供的Linux上的二进制MySQL分发一般用pgcc编译。我们必须返回到常规gcc,因为pgcc中有一个bug,使生成的二进制不能在AMD上运行。我们将继续使用gcc直到该bug被解决。同时,如果你有一个非AMD机,你可以用pgcc编译构建一个更快的二进制。标准MySQL Linux二进制是通过静态链接,以使它更快并且更加易于移植。

MySQL如何使用内存

下面的列表中列出了mysqld服务器使用内存的一些方法。在适用的地方,给出了内存相关的系统变量名:
· 键缓存(变量key_buffer_size)被所有线程共享;服务器使用的其它缓存则根据需要分配。参见7.5.2节,“调节服务器参数”。
· 每个连接使用具体线程的空间:
 o 堆栈(默认64KB,变量thread_stack)
 o 连接缓存区(变量net_buffer_length)
 o 结果缓存区(变量net_buffer_length)
连接缓存区和结果缓存区可以根据需要动态扩充到max_allowed_packet。当某个查询运行时,也为当前查询字符串分配内存。
· 所有线程共享相同的基本内存。
· 只有压缩MyISAM表映射到内存。这是因为4GB的32位内存空间不足以容纳大多数大表。当64位地址空间的系统变得越来越普遍后,我们可以增加常规的内存映射支持。
· 对表进行顺序扫描的请求将分配一个缓存区(变量read_buffer_size)。
· 当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读 缓存区(变量read_rnd_buffer_size)以避免硬盘搜索。
· 所有联合在一个令牌内完成,并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。

如果某个内部heap(堆积)表大小超过tmp_table_size,MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置mysqld的tmp_table_size选项来增加临时表的大小,或设置客户程序中的SQL选项SQL_BIG_TABLE。参见13.5.3节,“SET语法”。
· 进行排序的大多数请求将分配一个排序缓存区,并根据结果集的大小为两个临时文件分配零。参见A.4.4节,“MySQL将临时文件储存在哪里”。
· 几乎所有解析和计算在局部内存中完成。小项目不需要内存,因此避免了普通的慢内存分配和释放。只为不期望的大字符串分配内存;使用函数malloc()和free()来完成。
· 对于每个打开的MyISAM表,索引文件打开一次;数据文件为每个并行运行的线程打开一次。对于每个并行线程,将分配一个表结构、一个每个列的列结构和大小为3 * N的缓存区(其中N是最大行的长度,而不是计算BLOB列)。一个BLOB列需要5至8个字节加上BLOB数据的长度。MyISAM 存储引擎维护一个额外的行缓存区供内部应用。
· 对于每个具有BLOB列的表,将对缓存区进行动态扩大以读入大的BLOB 值。如果你扫描一个表,则分配一个与最大的BLOB值一样大的缓存区。
· 所有使用的表的句柄结构保存在高速缓存中并以FIFO管理。默认情况,高速缓存有64个入口。如果某个表同时被两个运行的线程使用,高速缓存则为该提供两个入口。参见7.4.9节,“MySQL如何打开和关闭表”。
· 当并行执行的线程结束时,FLUSH TABLE语句或mysqladmin flush-table命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭。这样可以有效释放大多数使用中的内存。FLUSH TABLE在关闭所有表之前不返回结果。

ps和其它系统状态程序可以报导mysqld使用很多内存。这可以是在不同的内存地址上的线程栈造成的。例如,Solaris版本的ps将栈间未用的内存算作已用的内存。你可以通过用swap -s检查可用交换区来验证它。我们用商业内存漏洞探查器测试了mysqld,因此应该有没有内存漏洞。

MySQL如何使用DNS

当新的客户连接mysqld时,mysqld创建一个新的线程来处理请求。该线程先检查是否主机名在主机名缓存中。如果不在,线程试图解析主机名:
· 如果操作系统支持线程安全gethostbyaddr_r ()和gethostbyname_r()调用,线程使用它们来执行主机名解析。
· 如果操作系统不支持线程安全调用,线程锁定一个互斥体并调用gethostbyaddr()和gethostbyname()。在这种情况下,在第1个线程解锁互斥体前,没有其它线程可以解析不在主机名缓存中的主机名。
你可以用--skip-name-resolve选项启动mysqld来禁用DNS主机名查找。然而,在这种情况下,你只可以使用MySQL中的授权表中的IP号。

如果你有一个很慢的DNS和许多主机,你可以通过用--skip-name-resolve禁用DNS查找或增加HOST_CACHE_SIZE定义(默认值:128)并重新编译mysqld来提高性能。

你可以用--skip-host-cache选项启动服务器来禁用主机名缓存。要想清除主机名缓存,执行FLUSH HOSTS语句或执行mysqladmin flush-hosts命令。如果你想要完全禁止TCP/IP连接,用--skip-networking选项启动mysqld。

磁盘事宜

使用符号链接
· 磁盘搜索是巨大的性能瓶颈。当数据量变得非常大以致于缓存性能变得不可能有效时,该问题变得更加明显。对于大数据库,其中你或多或少地随机访问数据,你可以确信对读取操作需要至少一次硬盘搜索,写操作需要多次硬盘搜索。要想使该问题最小化,应使用搜索次数较少的磁盘。
· 通过链接文件到不同的磁盘或对硬盘分段来增加可用磁盘锭数量(因此降低搜索成本):
o 使用符号链接
这说明,对于MyISAM表,你符号链接索引文件和/或数据文件,从它们数据目录内的通常位置到另一个硬盘(也可以被条纹化)。这将使搜索和读次数达到最好,假定硬盘不再为其它目的使用。参见7.6.1节,“使用符号链接”。
o 分条
分条意味着你有许多磁盘,将第1个块放到第1个硬盘,第2个块放到第2个磁盘,并且第N块在(N mod number_of_disks)磁盘上等等。这意味着如果正常数据大小小于分条大小(或完全匹配),能够得到最佳性能。分条完全取决于操作系统和分条大小,因此用不同的条纹大小对应用程序进行基准测试。参见7.1.5节,“使用自己的基准”。
分条的不同速度完全依赖于参数。依赖于怎样设置条纹参数和硬盘数量,可以根据不同数量级别得到不同的标准。你必须进行选择以便优化随机或顺序存取。
· 为了高可靠性你可能想使用RAID 0+1(条纹加镜像),但在这种情况下,需要2*N块磁盘来保持N个磁盘的数据。如果你肯为它花钱,这可能是最好的选项。然而,你可能还必须投资一部分资金到卷管理软件中以便有效地管理它。
· 一个较好的选择是根据数据类型的重要性程度改变RAID级别。例如,保存可以在RAID 0硬盘上重新生成的不太重要的数据,但保存 真正重要数据(例如主机信息和日志)到RAID 0+1或RAID N硬盘。如果你有许多写操作,RAID N可能会存在问题,因为需要时间来更新校验位。
· 在Linux上,通过配置磁盘接口时使用hdparm,你可以获得更多的性能。(在一般负载下达到100%并不困难。)下面的hdparm选项 应该非常适用于MySQL,并且可能适用于许多其它应用程序:
hdparm -m 16 -d 1
请注意,当使用该命令时,性能和可靠性依赖于硬件,因此我们强烈建议,使用hdparm后,完全测试你的系统。为获取更多详细信息,请查阅hdparm手册帮助页。如果hdparm使用的不好,会导致文件系统破坏,因此试验之前请做好每个备份!
· 也可以设置数据库使用的文件系统参数:
如果不需要知道文件最后一次访问的时间(这对数据库服务器并没有实际的用途),可以用-o noatime选项安装文件系统。这将跳过对文件系统中的节点的最后一次访问时间的更新,从而能够避免一些硬盘搜索。
在许多操作系统中,用-o async选项安装,可以将文件系统设置为异步更新。如果电脑相当稳定,这应该给予你更高的性能而不需要牺牲太多的可靠性。(该标志是Linux中默认开启。)

使用符号链接

你可以将表和数据库从数据库目录移动到其它的位置并且用指向新位置的符号链接进行替换。你可能需要这样作,例如,移动数据库到一个有更多空闲空间的文件系统中或通过将表分散到不同的磁盘上以增加系统的访问速度。

推荐的方法值需要将数据库通过符号链接指到不同的磁盘。符号链接表仅作为是最后的办法。

在Unix上使用针对数据库的符号链接
在Unix中,符号链接一个数据库的方法是,首先在一些有空闲空间的硬盘上创建一个目录,然后从MySQL数据目录中创建它的一个符号链接。

shell> mkdir /dr1/databases/test
shell> ln -s /dr1/databases/test /path/to/datadir
MySQL不支持链接一个目录到多个数据库。用一个符号链接替换一个数据库目录,只要你不在数据库之间制作符号链接。假定你有一个数据库db1在MySQL数据目录下,然后生成一个符号链接db2指向 db1:
shell> cd /path/to/datadir
shell> ln -s db1 db2
对于db1中的任何表tbl_a,在db2也出现并且还是表tbl_a。如果一个客户更新db1.tbl_a并且另一个客户更新db2.tbl_a,可能会出现问题。

然而,如果你真的需要这样做,可以通过改变源文件mysys/my_symlink.c来实现,在该文件中你应查找下面的语句:
if (!(MyFlags & MY_RESOLVE_LINK) ||
(!lstat(filename,&stat_buff) && S_ISLNK(stat_buff.st_mode)))
将该语句修改为:
if (1)
请注意,对于所有的Windows服务器,默认启用符号链接支持。

在Unix平台上使用表的符号链接

你不应在没有完全可操作的realpath()调用的系统中对表进行符号链接。(Linux和Solaris支持realpath())。可以通过发出一个SHOW VARIABLES LIKE 'have_symlink'语句,检查系统是否支持符号链接。

只有MyISAM表完全支持符号链接。对于其它表类型,如果试图在操作系统中的文件上用前面的任何语句使用符号链接,可能会出现奇怪的问题。

对于MyISAM表的符号链接的处理如下:
· 在数据目录指,一定会有表定义文件、数据文件和索引文件。数据文件和索引文件可以移到别处和在数据目录中符号链接替代。表定义文件不能进行符号链接替换。
· 可以分别通过符号链接将数据文件和索引文件指到不同的目录。
· 如果mysqld没有运行,符号链接可以从服务器命令行使用ln -s手动完成。同样,通过使用DATA DIRECTORY和INDEX DIRECTORY选项创建表,你可以指示运行的MySQL服务器执行符号链接。参见13.1.5节,“CREATE TABLE语法”。
· myisamchk不用数据文件或索引文件替换符号链接。它直接工作在符号链接指向的文件。任何临时文件创建在数据文件或索引文件所处的目录中。
· 注释:当你删掉一个表时,如果该表使用了符号链接,符号链接和该符号链接指向的文件都被删除掉。这就是你不应以系统root用户运行mysqld或允许系统用户对MySQL数据库目录有写访问权限的原因。
· 如果你用ALTER TABLE ... RENAME重命名一个表并且不将表移到另一个数据库,数据库目录中的符号链接被重新命名为一个新名字并且数据文件和索引文件也相应地重新命名。
· 如果你用ALTER TABLE ... RENAME移动一个表到另一个数据库,表移动到另一个数据库目录。旧的符号链接和其所指向的文件被删除。换句话说,新表不再被链接。
· 如果不使用符号链接,你应对mysqld使用--skip-symbolic-links选项以确保没有人能够使用mysqld来删除或重新命名数据目录之外的文件。

表符号链接还不支持以下操作:
· ALTER TABLE忽略DATA DIRECTORY 和INDEX DIRECTORY表选项。
· BACKUP TABLE 和RESTORE TABLE不考虑符号链接。
· .frm文件必须绝不能是一个符号链接(如前面所述,只有数据和索引文件可以是符号链接)。如果试图这样做(例如,生成符号链接)会产生不正确的结果。假定你在MySQL数据目录下有一个数据库db1,该数据库有一个表tbl1,并且在db1目录中你制作了一个符号链接tbl2指向tbl1:
· shell> cd /path/to/datadir/db1
· shell> ln -s tbl1.frm tbl2.frm
· shell> ln -s tbl1.MYD tbl2.MYD
· shell> ln -s tbl1.MYI tbl2.MYI
如果一个线程读取db1.tbl1同时另一个线程更新 db1.tbl2会发生问题:
o 查询缓存将变为“傻瓜” (它没有办法知道tbl1是否被更新,因此它返回过时的结果)。
o tbl2上的ALTER语句也会失败。

在Windows平台上使用关于数据库的符号链接
Windows的mysqld-max和mysql-max-nt服务器使用-DUSE_SYMDIR选项编译成。允许你放置数据库目录到一个不同的硬盘,通过设置一个符号链接指向它。这类似于Unix中的符号链接,尽管设置链接的过程不同。

符号链接默认为启用。如果你不需要,使用skip-symbolic-links选项来禁用它:

[mysqld]
skip-symbolic-links

在Windows中,通过在数据目录中创建一个文件,该文件包含目标目录的路径,你可以为MySQL数据库创建一个符号链接。该文件应该被命名为db_name.sym,其中db_name是数据库名。

假定MySQ数据目录是C:\mysql\data并且你想要数据库foo放置在D:\data\foo。设置一个符号链接如下所示:
1. 确保D:\data\foo目录存在,如果必要创建它。如果你在数据目录内有一个命名为foo的数据库目录,你应移动它到D:\data目录。否则,符号链接无效。为避免出现问题,当你移动数据库目录时服务器不应该运行。
2. 创建一个文本文件C:\mysql\data\foo.sym,该本文文件包含路径名D:\data\foo\。
此后,数据库foo创建的所有表将创建在D:\data\foo文件中。请注意,如果在MySQL数据目录中存在同名的数据库目录,不能使用符号链接。

我猜你可能也喜欢:

Pages: 1 2 3 4 5
No Comments - Leave a comment

Leave a comment

电子邮件地址不会被公开。 必填项已用*标注

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Welcome , today is 星期四, 2018 年 01 月 18 日