MySQL怎样优化LEFT JOIN

在MySQL中，A LEFT JOIN B实现如下：

1、表B被设置为依赖于表A。

2、表A被设置为依赖于所有用在LEFT JOIN条件的表(除B外)。

3、所有LEFT JOIN条件被移到WHERE子句中。

4、进行所有标准的联结优化，除了一个表总是在所有它依赖的表之后被读取。如果有一个循环依赖，MySQL将发出一个错误。

5、进行所有标准的WHERE优化。

6、如果在A中有一行匹配WHERE子句，但是在B中没有任何行匹配LEFT JOIN条件，那么在B中生成所有列设置为NULL的一行。

7、如果你使用LEFT JOIN来找出在某些表中不存在的行并且在WHERE部分你有下列测试：column_name IS NULL，这里column_name 被声明为NOT NULL的列，那么MySQL在它已经找到了匹配LEFT JOIN条件的一行后，将停止在更多的行后寻找(对一特定的键组合)。

MySQL怎样优化LIMIT

在一些情况中，当你使用LIMIT #而不使用HAVING时，MySQL将以不同方式处理查询。

1、如果你用LIMIT只选择一些行，当MySQL一般比较喜欢做完整的表扫描时，它将在一些情况下使用索引。

2、如果你使用LIMIT #与ORDER BY，MySQL一旦找到了第一个 # 行，将结束排序而不是排序整个表。

3、当结合LIMIT #和DISTINCT时，MySQL一旦找到#个唯一的行，它将停止。

4、在一些情况下，一个GROUP BY能通过顺序读取键(或在键上做排序)来解决，并然后计算摘要直到键值改变。在这种情况下，LIMIT #将不计算任何不必要的GROUP。

5、只要MySQL已经发送了第一个#行到客户，它将放弃查询。

6、LIMIT 0将总是快速返回一个空集合。这对检查查询并且得到结果列的列类型是有用的。

7、临时表的大小使用LIMIT #计算需要多少空间来解决查询。

记录转载和修改的速度

很多时候关心的是优化 SELECT 查询，因为它们是最常用的查询，而且确定怎样优化它们并不总是直截了当。相对来说，将数据装入数据库是直截了当的。然而，也存在可用来改善数据装载操作效率的策略，其基本原理如下：

·成批装载较单行装载更快，因为在装载每个记录后，不需要刷新索引高速缓存；可在成批记录装入后才刷新。

·在表无索引时装载比索引后装载更快。如果有索引，不仅必须增加记录到数据文件，而且还要修改每个索引以反映增加了的新记录。

·较短的 SQL 语句比较长的 SQL 语句要快，因为它们涉及服务器方的分析较少，而且还因为将它们通过网络从客户机发送到服务器更快。

这些因素中有一些似乎微不足道（特别是最后一个因素），但如果要装载大量的数据，即使是很小的因素也会产生很大的不同结果。

INSERT查询的速度

插入一个记录的时间由下列组成：

·连接：(3)

·发送查询给服务器：(2)

·分析查询：(2)

·插入记录：（1 x 记录大小）

·插入索引：（1 x 索引）

·关闭：(1)

这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。

表的大小以N log N (B 树)的速度减慢索引的插入。

加快插入的一些方法：

·如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。

·如果你从不同客户插入很多行，你能通过使用INSERT DELAYED语句得到更高的速度。

·注意，用MyISAM，如果在表中没有删除的行，能在SELECT:s正在运行的同时插入行。

·当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。

·当表有很多索引时，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列过程：

1、有选择地用CREATE TABLE创建表。例如使用mysql或Perl-DBI。

2、执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。

3、使用myisamchk –keys-used=0 -rq /path/to/db/tbl_name。这将从表中删除所有索引的使用。

4、用LOAD DATA INFILE把数据插入到表中，这将不更新任何索引，因此很快。

5、如果你有myisampack并且想要压缩表，在它上面运行myisampack。

6、用myisamchk -r -q /path/to/db/tbl_name再创建索引。这将在将它写入磁盘前在内存中创建索引树，并且它更快，因为避免大量磁盘寻道。结果索引树也被完美地平衡。

7、执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。

这个过程将被构造进在MySQL的某个未来版本的LOAD DATA INFILE。

·你可以锁定你的表以加速插入

mysql> LOCK TABLES a WRITE; mysql> INSERT INTO a VALUES (1，23)，(2，34)，(4，33); mysql> INSERT INTO a VALUES (8，26)，(6，29); mysql> UNLOCK TABLES;

 

　　主要的速度差别是索引缓冲区仅被清洗到磁盘上一次，在所有INSERT语句完成后。一般有与有不同的INSERT语句那样夺的索引缓冲区清洗。如果你能用一个单个语句插入所有的行，锁定就不需要。锁定也将降低多连接测试的整体时间，但是对某些线程最大等待时间将上升(因为他们等待锁)。例如：

thread 1 does 1000 inserts thread 2， 3， and 4 does 1 insert thread 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行)以允许另外的线程存取表。这仍然将导致获得好的性能。当然，LOAD DATA INFILE对装载数据仍然是更快的。

为了对LOAD DATA INFILE和INSERT得到一些更快的速度，扩大关键字缓冲区。

UPDATE查询的速度

更改查询被优化为有一个写开销的一个SELECT查询。写速度依赖于被更新数据大小和被更新索引的数量。

使更改更快的另一个方法是推迟更改并且然后一行一行地做很多更改。如果你锁定表，做一行一行地很多更改比一次做一个快。

注意，动态记录格式的更改一个较长总长的记录，可能切开记录。因此如果你经常这样做，时不时地OPTIMIZE TABLE是非常重要的。

DELETE查询的速度

删除一个记录的时间精确地与索引数量成正比。为了更快速地删除记录，你可以增加索引缓存的大小。

从一个表删除所有行比删除行的一大部分也要得多。

索引对有效装载数据的影响

如果表是索引的，则可利用批量插入（LOAD DATA 或多行的 INSERT 语句）来减少索引的开销。这样会最小化索引更新的影响，因为索引只需要在所有行处理过时才进行刷新，而不是在每行处理后就刷新。

·如果需要将大量数据装入一个新表，应该创建该表且在未索引时装载，装载数据后才创建索引，这样做较快。一次创建索引（而不是每行修改一次索引）较快。

·如果在装载之前删除或禁用索引，装入数据后再重新创建或启用索引可能使装载更快。
·如果想对数据装载使用删除或禁用策略，一定要做一些实验，看这样做是否值得（如果将少量数据装入一个大表中，重建和索引所花费的时间可能比装载数据的时间还要长）。

可用DROP INDEX和CREATE INDEX 来删除和重建索引。

另一种可供选择的方法是利用 myisamchk 或 isamchk 禁用和启用索引。这需要在 MySQL 服务器主机上有一个帐户，并对表文件有写入权。为了禁用表索引，可进入相应的数据库目录，执行下列命令之一：

shell>myisamchk –keys-used=0 tbl_name shell>isamchk –keys-used=0 tbl_name

　　对具有 .MYI 扩展名的索引文件的 MyISAM 表使用 myisamchk，对具有 .ISM 扩展名的索引文件的 ISAM 表使用 isamchk。在向表中装入数据后，按如下激活索引：

shell>myisamchk –recover –quick –keys-used=0 tbl_name shell>isamchk –recover –quick –keys-used=0 tbl_name

　　n 为表具有的索引数目。可用 –description 选项调用相应的实用程序得出这个值：

shell>myisamchk –discription tbl_name $isamchk –discription tbl_name

 
　　如果决定使用索引禁用和激活，应该使用第13章中介绍的表修复锁定协议以阻止服务器同时更改锁（虽然此时不对表进行修复，但要对它像表修复过程一样进行修改，因此需要使用相同的锁定协议）。

上述数据装载原理也适用于与需要执行不同操作的客户机有关的固定查询。例如，一般希望避免在频繁更新的表上长时间运行 SELECT 查询。长时间运行 SELECT 查询会产生大量争用，并降低写入程序的性能。一种可能的解决方法为，如果执行写入的主要是 INSERT 操作，那么先将记录存入一个临时表，然后定期地将这些记录加入主表中。如果需要立即访问新记录，这不是一个可行的方法。但只要能在一个较短的时间内不访问它们，就可以使用这个方法。使用临时表有两个方面的好处。首先，它减少了与主表上 SELECT 查询语句的争用，因此，执行更快。其次，从临时表将记录装入主表的总时间较分别装载记录的总时间少；相应的索引高速缓存只需在每个批量装载结束时进行刷新，而不是在每行装载后刷新。

这个策略的一个应用是进入 Web 服务器的Web 页访问 MySQL 数据库。在此情形下，可能没有保证记录立即进入主表的较高权限。

如果数据并不完全是那种在系统非正常关闭事件中插入的单个记录，那么减少索引刷新的另一策略是使用 MyISAM 表的 DELAYED_KEY_WRITE 表创建选项（如果将 MySQL 用于某些数据录入工作时可能会出现这种情况）。此选项使索引高速缓存只偶尔刷新，而不是在每次插入后都要刷新。

如果希望在服务器范围内利用延迟索引刷新，只要利用 –delayed-key-write 选项启动 mysqld 即可。在此情形下，索引块写操作延迟到必须刷新块以便为其他索引值腾出空间为止，或延迟到执行了一个 flush-tables 命令后，或延迟到该索引表关闭。