mysql 查询语句优化

4、索引使用的常数表达式仅计算一次。 5、在一个单个表上的没有一个 WHERE的 COUNT(*)直接从表中检索信息。 当仅使用一个表时，对任何 NOT NULL表达式也这样做。 6、无效常数表达式的早期检测。MySQL 快速检测某些 SELECT语句是不可 能的并且不返回行。 7、如果你不使用 GROUP BY或分组函数(COUNT()、MIN()，HAVING 与 WHERE合并。 8、为每个子联结(sub join)，构造一个更简单的 WHERE以得到一个更快的 WHERE计算并且也尽快跳过记录。 9、所有常数的表在查询中的任何其他表前被首先读出。一个常数的表是： 一个空表或一个有 1行的表。 与在一个 UNIQUE索引、或一个 PRIMARY KEY的 WHERE子句一起使用的表， 这里所有的索引部分使用一个常数表达式并且索引部分被定义为 NOT NULL。 所有下列的表用作常数表 mysql SELECT * FROM t WHERE primary_key=1; mysql SELECT * FROM t1，t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id; 10、对联结表的最好联结组合是通过尝试所有可能性来找到:(。如果所有 在 ORDER BY和 GROUP BY的列来自同一个表，那么当廉洁时，该表首先被选中。 11、如果有一个 ORDER BY子句和一个不同的 GROUP BY子句，或如果 ORDER BY或 GROUP BY包含不是来自联结队列中的第一个表的其他表的列，创 建一个临时表。 12、如果你使用 SQL_SMALL_RESULT，MySQL 将使用一个在内存中的表。 13、因为 DISTINCT被变换到在所有的列上的一个 GROUP BY，DISTINCT 与 ORDER BY结合也将在许多情况下需要一张临时表。 14、每个表的索引被查询并且使用跨越少于 30% 的行的索引。如果这样的 索引没能找到，使用一个快速的表扫描。 15、在一些情况下，MySQL 能从索引中读出行，甚至不咨询数据文件。如 果索引使用的所有列是数字的，那么只有索引树被用来解答查询。 16、在每个记录被输出前，那些不匹配 HAVING子句的行被跳过。 下面是一些很快的查询例子 mysql SELECT COUNT(*) FROM tbl_name; mysql SELECT MIN(key_part1)，MAX(key_part1) FROM tbl_name; mysql SELECT MAX(key_part2) FROM tbl_name WHERE key_part_1=constant; mysql SELECT . FROM tbl_name ORDER BY key_part1，key_part2，. LIMIT 10; mysql SELECT . FROM tbl_name ORDER BY key_part1 DESC，key_part2 DESC，. LIMIT 10; 下列查询仅使用索引树就可解决(假设索引列是数字的)： mysql SELECT key_part1，key_part2 FROM tbl_name WHERE key_part1 =val; mysql SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2; mysql SELECT key_part2 FROM tbl_name GROUP BY key_part1; 下列查询使用索引以排序顺序检索，不用一次另外的排序： mysql SELECT . FROM tbl_name ORDER BY key_part1，key_part2，. mysql SELECT . FROM tbl_name ORDER BY key_part1 DESC，key_part 2 DESC，. 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 服务器主机上有一个帐户，并对表文件有写入权。为了禁用表索引， 可进入相应的数据库目录，执行下列命令之一： shellmyisamchk -keys-used=0 tbl_name shellisamchk -keys-used=0 tbl_name 对具有 .MYI 扩展名的索引文件的 MyISAM 表使用 myisamchk，对具有 .ISM 扩展名的索引文件的 ISAM 表使用 isamchk。在向表中装入数据后，按如 下激活索引： shellmyisamchk -recover -quick -keys-used=0 tbl_name shellisamchk -recover -quick -keys-used=0 tbl_name n 为表具有的索引数目。可用 -description 选项调用相应的实用程序得 出这个值： shellmyisamchk -discription tbl_name $isamchk -discription tbl_name 如果决定使用索引禁用和激活，应该使用第 13章中介绍的表修复锁定协议 以阻止服务器同时更改锁（虽然此时不对表进行修复，但要对它像表修复过程 一样进行修改，因此需要使用相同的锁定协议）。 上述数据装载原理也适用于与需要执行不同操作的客户机有关的固定查询。例 如，一般希望避免在频繁更新的表上