关于MYSQL的优化全面详解

Categories: 数据库, 网站相关
Comments: No Comments
Published on: 2011 年 12 月 21 日
MySQL如何优化ORDER BY

在某些情况中,MySQL可以使用一个索引来满足ORDER BY子句,而不需要额外的排序。即使ORDER BY不确切匹配索引,只要WHERE子句中的所有未使用的索引部分和所有额外的ORDER BY 列为常数,就可以使用索引。下面的查询使用索引来解决ORDER BY部分:
在某些情况下,MySQL不能使用索引来解决ORDER BY,尽管它仍然使用索引来找到匹配WHERE子句的行。这些情况包括:
· 对不同的关键字使用ORDER BY:
· SELECT * FROM t1 ORDER BY key1, key2;
· 对关键字的非连续元素使用ORDER BY:
· SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
· 混合ASC和DESC:
· SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
· 用于查询行的关键字与ORDER BY中所使用的不相同:
· SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
· 你正联接许多表,并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表。(这是EXPLAIN输出中的没有const联接类型的第1个表)。
· 有不同的ORDER BY和GROUP BY表达式。
· 使用的表索引的类型不能按顺序保存行。例如,对于HEAP表的HASH索引情况即如此。
通过EXPLAIN SELECT ...ORDER BY,可以检查MySQL是否可以使用索引来解决查询。如果Extra列内有Using filesort,则不能解决查询。参见7.2.1节,“EXPLAIN语法(获取关于SELECT的信息)”。

文件排序优化不仅用于记录排序关键字和行的位置,并且还记录查询需要的列。这样可以避免两次读取行。文件排序算法的工作象这样:
1. 读行匹配WHERE子句的行,如前面所示。
2. 对于每个行,记录构成排序关键字和行位置的一系列值,并且记录查询需要的列。
3. 根据排序关键字排序元组
4. 按排序的顺序检索行,但直接从排序的元组读取需要的列,而不是再一次访问表。
该算法比以前版本的Mysql有很大的改进。
为了避免速度变慢,该优化只用于排序元组中的extra列的总大小不超过max_length_for_sort_data系统变量值的时候。(将该变量设置得太高的的迹象是将看到硬盘活动太频繁而CPU活动较低)。

如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试下面的策略:
· 增加sort_buffer_size变量的大小。
· 增加read_rnd_buffer_size变量的大小。
· 更改tmpdir指向具有大量空闲空间的专用文件系统。该选项接受几个使用round-robin(循环)模式的路径。在Unix中路径应用冒号(‘:’)区间开,在Windows、NetWare和OS/2中用分号(‘;’)。可以使用该特性将负载均分到几个目录中。注释:路径应为位于不同物理硬盘上的文件系统的目录,而不是同一硬盘的不同的分区。

默认情况下,MySQL排序所有GROUP BY col1,col2,...查询的方法如同在查询中指定ORDER BY col1,col2,...。如果显式包括一个包含相同的列的ORDER BY子句,MySQL可以毫不减速地对它进行优化,尽管仍然进行排序。如果查询包括GROUP BY但你想要避免排序结果的消耗,你可以指定ORDER BY NULL禁止排序。例如:
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

MySQL如何优化GROUP BY

满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表,表中每个组的所有行应为连续的,然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中,MySQL能够做得更好,通过索引访问而不用创建临时表。

为GROUP BY使用索引的最重要的前提条件是 所有GROUP BY列引用同一索引的属性,并且索引按顺序保存其关键字(例如,这是B-树索引,而不是HASH索引)。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件,以及选择的累积函数。

有两种方法通过索引访问执行GROUP BY查询。在第1个方法中,组合操作结合所有范围判断式使用(如果有)。第2个方法首先执行范围扫描,然后组合结果元组。

松散索引扫描
使用索引时最有效的途径是直接搜索组域。通过该访问方法,MySQL使用某些关键字排序的索引类型(例如,B-树)的属性。该属性允许使用 索引中的查找组而不需要考虑满足所有WHERE条件的索引中的所有关键字。既然该访问方法只考虑索引中的关键字的一小部分,它被称为松散索引扫描。如果没有WHERE子句, 松散索引扫描读取的关键字数量与组数量一样多,可以比所有关键字数小得多。如果WHERE子句包含范围判断式(关于range联接类型的讨论参见7.2.1节,“EXPLAIN语法(获取关于SELECT的信息)”), 松散索引扫描查找满足范围条件的每个组的第1个关键字,并且再次读取尽可能最少数量的关键字。在下面的条件下是可以的:
· 查询针对一个单表。
· GROUP BY包括索引的第1个连续部分(如果对于GROUP BY,查询有一个DISTINCT子句,则所有显式属性指向索引开头)。
· 只使用累积函数(如果有)MIN()和MAX(),并且它们均指向相同的列。
· 索引的任何其它部分(除了那些来自查询中引用的GROUP BY)必须为常数(也就是说,必须按常量数量来引用它们),但MIN()或MAX() 函数的参数例外。

此类查询的EXPLAIN输出显示Extra列的Using indexforgroup-by。
下面的查询提供该类的几个例子,假定表t1(c1,c2,c3,c4)有一个索引idx(c1,c2,c3):
SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2; 由于上述原因,不能用该快速选择方法执行下面的查询: 1. 除了MIN()或MAX(),还有其它累积函数,例如: SELECT c1, SUM(c2) FROM t1 GROUP BY c1; 2. GROUP BY子句中的域不引用索引开头,如下所示: SELECT c1,c2 FROM t1 GROUP BY c2, c3; 3. 查询引用了GROUP BY部分后面的关键字的一部分,并且没有等于常量的等式,例如: SELECT c1,c3 FROM t1 GROUP BY c1, c2; 紧凑索引扫描 紧凑式索引扫描可以为索引扫描或一个范围索引扫描,取决于查询条件。 如果不满足松散索引扫描条件,GROUP BY查询仍然可以不用创建临时表。如果WHERE子句中有范围条件,该方法只读取满足这些条件的关键字。否则,进行索引扫描。该方法读取由WHERE子句定义的每个范围的所有关键字,或没有范围条件式扫描整个索引,我们将它定义为紧凑式索引扫描。请注意对于紧凑式索引扫描,只有找到了满足范围条件的所有关键字后才进行组合操作。 要想让该方法工作,对于引用GROUP BY关键字元素的前面、中间关键字元素的查询中的所有列,有一个常量等式条件即足够了。等式条件中的常量填充了搜索关键字中的“差距”,可以形成完整的索引前缀。这些索引前缀可以用于索引查找。如果需要排序GROUP BY结果,并且能够形成索引前缀的搜索关键字,MySQL还可以避免额外的排序操作,因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。 上述的第一种方法不适合下面的查询,但第2种索引访问方法可以工作(假定我们已经提及了表t1的索引idx): · GROUP BY中有一个差距,但已经由条件c2 = 'a'覆盖。 SELECT c1,c2,c3 FROM t1 WHERE c2 = 'a' GROUP BY c1,c3; · GROUP BY不以关键字的第1个元素开始,但是有一个条件提供该元素的常量: SELECT c1,c2,c3 FROM t1 WHERE c1 = 'a' GROUP BY c2,c3;

MySQL如何优化LIMIT

在一些情况中,当你使用LIMIT row_count而不使用HAVING时,MySQL将以不同方式处理查询。
· 如果你用LIMIT只选择一些行,当MySQL选择做完整的表扫描时,它将在一些情况下使用索引。
· 如果你使用LIMIT row_count与ORDER BY,MySQL一旦找到了排序结果的第一个row_count行,将结束排序而不是排序整个表。如果使用索引,将很快。如果必须进行文件排序(filesort),必须选择所有匹配查询没有LIMIT子句的行,并且在确定已经找到第1个row_count行前,必须对它们的大部分进行排序。在任何一种情况下,一旦找到了行,则不需要再排序结果的其它部分,并且MySQL不再进行排序。
· 当结合LIMIT row_count和DISTINCT时,MySQL一旦找到row_count个唯一的行,它将停止。
· 在一些情况下,GROUP BY能通过顺序读取键(或在键上做排序)来解决,然后计算摘要直到关键字的值改变。在这种情况下,LIMIT row_count将不计算任何不必要的GROUP BY值。
· 只要MySQL已经发送了需要的行数到客户,它将放弃查询,除非你正使用SQL_CALC_FOUND_ROWS。
· LIMIT 0将总是快速返回一个空集合。这对检查查询的有效性是有用的。当使用MySQL API时,它也可以用来得到结果列的列类型。(该技巧在MySQL Monitor中不工作,只显示Empty set;应使用SHOW COLUMNS或DESCRIBE)。
· 当服务器使用临时表来进行查询时,使用LIMIT row_count子句来计算需要多少空间。

如何避免表扫描

EXPLAIN的输出显示了当MySQL使用表扫描来解决查询时使用的所有类型列。这通常在如下条件下发生:
· 表很小,扫描表比查找关键字速度快。这对于少于10行并且行较短的表比较普遍。
· 在ON或WHERE子句中没有适用的索引列的约束。
· 正用常量值比较索引列,并且MySQL已经计算到(基于索引树)常数覆盖了表的很大部分并且表扫描将会比较快。参见7.2.4节,“MySQL怎样优化WHERE子句”。
· 你正通过另一个列使用一个低的集的势的关键字(许多行匹配关键字)。在这种情况下,MySQL假设通过使用关键字它可能会进行许多关键字查找,表扫描将会更快。

对于小表,表扫描通常合适。对于大表,尝试下面的技巧以避免优化器错选了表扫描:
· 使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。参见“ANALYZE TABLE语法”。
· 对扫描的表使用FORCE INDEX告知MySQL,相对于使用给定的索引表扫描将非常耗时。参见“SELECT语法”。
· SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
· WHERE t1.col_name=t2.col_name;
· 用--max-seeks-for-key=1000选项启动mysqld或使用SET max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。

INSERT语句的速度

插入一个记录需要的时间由下列因素组成,其中的数字表示大约比例:
连接:(3)
发送查询给服务器:(2)
分析查询:(2)
插入记录:(1x记录大小)
插入索引:(1x索引)
关闭:(1)
这不考虑打开表的初始开销,每个并发运行的查询打开。
表的大小以logN (B树)的速度减慢索引的插入。

加快插入的一些方法:
· 如果同时从同一个客户端插入很多行,使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据,可以调节bulk_insert_buffer_size变量,使数据插入更快。
· 如果你从不同的客户端插入很多行,能通过INSERT DELAYED语句加快速度。
· 用MyISAM,如果在表中没有删除的行,能在SELECT语句正在运行的同时插入行。
· 当从一个文本文件装载一个表时,使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。
· 当表有很多索引时,有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列过程:
有选择地用CREATE TABLE创建表。
执行FLUSH TABLES语句或命令mysqladmin flush-tables。
使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中取消所有索引的使用。
用LOAD DATA INFILE把数据插入到表中,因为不更新任何索引,因此很快。
如果只想在以后读取表,使用myisampack压缩它。参见15.1.3.3节,“压缩表特性”。
用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树,并且它更快,因为避免了大量磁盘搜索。结果索引树也被完美地平衡。
执行FLUSH TABLES语句或mysqladmin flush-tables命令。
请注意如果插入一个空MyISAM表,LOAD DATA INFILE也可以执行前面的优化;主要不同处是可以让myisamchk为创建索引分配更多的临时内存,比执行LOAD DATA INFILE语句时为服务器重新创建索引分配得要多。
也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk --keys-used=0 -rq /path/to/db/tbl_name,使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name。使用这种方式,还可以跳过FLUSH TABLES。
· 锁定表可以加速用多个语句执行的INSERT操作:
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
这样性能会提高,因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行,就不需要锁定。

对于事务表,应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。

锁定也将降低多连接测试的整体时间,尽管因为它们等候锁定最大等待时间将上升。例如:
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
如果不使用锁定,2、3和4将在1和5前完成。如果使用锁定,2、3和4将可能不在1或5前完成,但是整体时间应该快大约40%。

INSERT、UPDATE和DELETE操作在MySQL中是很快的,通过为在一行中多于大约5次连续不断地插入或更新的操作加锁,可以获得更好的整体性能。如果在一行中进行多次插入,可以执行LOCK TABLES,随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能。
INSERT装载数据比LOAD DATA INFILE要慢得多,即使是使用上述的策略。
· 为了对LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度,通过增加key_buffer_size系统变量来扩大 键高速缓冲区。

UPDATE语句的速度

更新查询的优化同SELECT查询一样,需要额外的写开销。写速度依赖于更新的数据大小和更新的索引的数量。没有更改的索引不被更新。

使更改更快的另一个方法是推迟更改然后在一行内进行多次更新。如果锁定表,同时做多个更新比一次做一个快得多。

请注意对使用动态记录格式的MyISAM表,更新一个较长总长的记录可能会切分记录。如果经常这样该,偶尔使用OPTIMIZE TABLE很重要。

DELETE语句的速度

删除一个记录的时间与索引数量确切成正比。为了更快速地删除记录,可以增加键高速缓冲的大小。如果想要删除一个表的所有行,使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name。

其它优化技巧

该节列出了提高查询速度的各种技巧:
· 使用持久的连接数据库以避免连接开销。如果不能使用持久的连接并且你正启动许多新的与数据库的连接,可能要更改thread_cache_size变量的值。
· 总是检查所有查询确实使用已经在表中创建了的索引。在MySQL中,可以用EXPLAIN命令做到。“EXPLAIN语法(获取关于SELECT的信息)”。
· 尝试避免在频繁更新的表上执行复杂的SELECT查询,以避免与锁定表有关的由于读、写冲突发生的问题。
· 对于没有删除的行的MyISAM表,可以在另一个查询正从表中读取的同时在末尾插入行。如果这很重要,应考虑按照避免删除行的方式使用表。另一个可能性是在删除大量行后运行OPTIMIZE TABLE。
· 要修复任何ARCHIVE表可以发生的压缩问题,可以执行OPTIMIZE TABLE。
· 如果你主要按expr1,expr2,...顺序检索行,使用ALTER TABLE ... ORDER BY expr1, expr2, ...。对表大量更改后使用该选项,可以获得更好的性能。
· 在一些情况下,使得基于来自其它表的列的信息引入一个“ 哈希”的列有意义。如果该列较短并且有合理的唯一值,它可以比在许多列上的一个大索引快些。在MySQL中,很容易使用这个额外列:
· SELECT * FROM tbl_name
· WHERE hash_col=MD5(CONCAT(col1,col2))
· AND col1='constant' AND col2='constant';
· 对于频繁更改的MyISAM表,应试图避免所有变长列(VARCHAR、BLOB和TEXT)。如果表包括单一的变长列则使用动态记录格式。
· 只是因为行太大,将一张表分割为不同的表一般没有什么用处。为了访问行,最大的性能冲击是磁盘搜索以找到行的第一个字节。在找到数据后,大多数新型磁盘对大多数应用程序来说足够快,能读入整个行。确实有必要分割的唯一情形是如果它是使用动态记录格式使之变为固定的记录大小的MyISAM表(见上述),或如果你需要很频繁地扫描表而不需要大多数列。
· 如果你需要很经常地计算结果,例如基于来自很多行的信息的计数,引入一个新表并实时更新计数器可能更好一些。下面形式的更新会更快一些:
UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;
当你使用象MyISAM那样的只有表级锁定的MySQL存储引擎(多重读/单个写)时,这确实很重要。这也给大多数数据库较好的性能,因为行锁定管理器在这种情况下有较少的事情要做。
· 如果你需要从大的记录文件表中收集统计信息,使用总结性的表而不是扫描整个表。维护总结应该比尝试做“实时”统计要快些。当有变化时从日志重新生成新的总结表比改变运行的应用(取决于业务决策)要快得多。

如果可能,应该将报告分类为“实时”或“统计”,这里统计报告所需的数据仅仅基于从实际数据定期产生的总结表中产生。
充分利用列有默认值的事实。只有当插入的值不同于默认值时,才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。
在一些情况下,包装并存储数据到一个BLOB列中是很方便的。在这种情况下,必须在你的应用中增加额外的代码来打包/解包信息,但是这种方法可以在某些阶段节省很多访问。当有不符合行和列表结构的数据时,这很实用。
在一般情况下,应该尝试以非冗余方式(查看数据库理论中的第三正则形式)保存数据,但是为了获得更快的速度,可以复制信息或创建总结表。
存储过程或UDF(用户定义函数)可能是获得更好性能的一个好方法,总是能通过在应用程序中缓存查询/答案并尝试同时执行很多插入/更新来获得一些好处。如果数据库支持锁定表(象MySQL和Oracle),这应该有助于确保索引缓存只在所有更新后刷新一次。还可以利用MySQL的查询缓存来获得类似的结果;
当不需要知道何时写入数据时,使用INSERT DELAYED。这样可以加快处理,因为很多记录可以通过一次磁盘写入被写入。
当你想要让选择显得更重要时,使用INSERT /*! LOW_PRIORITY */。
使用INSERT LOW_PRIORITY来取得插入队列的检索,也就是即使有另一个客户等待写入也要执行SELECT。
使用多行INSERT语句通过一个SQL命令来存储很多行(许多SQL服务器支持它,包括MySQL)。
使用LOAD DATA INFILE装载较大数量的数据。这比使用INSERT要快得多。
使用AUTO_INCREMENT列构成唯一值。
当MyISAM使用动态表格式时,偶尔使用OPTIMIZE TABLE可以避免碎片。
可能时使用MEMORY表以得到更快的速度。
在Web服务器中,图象和其它二进制资产应该作为文件存储。也就是仅在数据库中存储的本文件的引用而不是文件本身。大多数Web服务器在缓存文件方面比数据库内容要好得多,因此使用文件一般要快得多。
对经常访问的不重要数据(如为没有在Web 浏览器中启用cookie的用户最后显示的标语的相关信息)使用内存表。在许多Web应用程序环境中也可以使用用户会话来处理可变状态数据。
在不同表中具有相同信息的列应该被声明为相同的并有相同的名字。尝试使名字简单化。例如,在customer表中使用name而不是customer_name。为了使名字能移植到其它SQL服务器,应该使名字短于18个字符。
如果确实需要很高的速度,应该研究一下不同SQL服务器支持的数据存储的低层接口!例如直接访问MySQL MyISAM存储引擎,比起使用SQL接口,速度可以提高2-5倍。为了能实现,数据必须与应用程序在同一台服务器上,并且通常只应该被一个进程访问(因为外部文件锁定确实很慢)。通过在MySQL服务器中引进低层MyISAM命令能消除以上问题(如果需要,这可能是获得更好性能的一个简单的方法)。通过精心设计数据库接口,应该能相当容易地支持这类优化。
如果正使用数字数据,在许多情况下,从一个数据库访问信息(使用实时连接)比访问一个文本文件快些。这是因为数据库中的信息比文本文件更紧凑,因此这将涉及更少的磁盘访问。还可以在应用程序中节省代码,因为不须分析文本文件来找出行和列的边界。
· 通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份时主服务器变慢,可以使用一个从服务器来备份。参见第6章:MySQL中的复制。
· 用DELAY_KEY_WRITE=1选项声明MyISAM表可以使索引更新更快,因为在表关闭之前它们不刷新到硬盘上。不利之处是当表打开时如果杀掉服务器,应确保用--myisam-recover选项运行服务器保证没有问题,或者在重启服务器之前运行myisamchk。(然而,即使在这种情况下,应通过使用DELAY_KEY_WRITE保证不丢失数据,因为关键字信息总是可以从数据行产生)。

我猜你可能也喜欢:

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 日