mysql常用的一些优化

来源深入浅出mysql数据库开发，优化，管理维护

 

定期分析表analyze

如果已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有 VARCHAR, BLOB 或 TEXT 列的表）进行了很多更改，则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的 INSERT 操作会重新使用旧的记录位置。您可以使用 OPTIMIZE TABLE 来重新利用未使用的空间，并整理数据文的碎片。OPTIMIZE TABLE 只对 MyISAM, BDB 和 InnoDB 表起作用。

 

大批量插入数据：

1.  对于 Myisam 类型的表，可以通过以下方式快速的导入大量的数据。

ALTER TABLE tblname DISABLE KEYS;

loading the data

ALTER TABLE tblname ENABLE KEYS;

这两个命令用来打开或者关闭 Myisam 表非唯一索引的更新。在导入大量的数据到一

个非空的 Myisam 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量

数据到一个空的 Myisam 表，默认就是先导入数据然后才创建索引的，所以不用进行

设置。

2.  而对于 Innodb 类型的表，这种方式并不能提高导入数据的效率。对于 Innodb 类型的表，我们有以下几种方式可以提高导入的效率：

a.  因为 Innodb 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 Innodb 表没有主键，那么系统会默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这个优势提高导入数据的效率。

b.  在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行

SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

c.  如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

 

优化insert语句：

3.  如果你同时从同一客户插入很多行，使用多个值表的 INSERT 语句。这比使用分开

INSERT 语句快(在一些情况中几倍)。

Insert into test values(1,2),(1,3),(1,4)…

4.  如果你从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。Delayed 的含义是让 insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；

5.  将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；

6.  如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能myisam 表使用；

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

8.  根据应用情况使用 replace 语句代替 insert；

9.  根据应用情况使用 ignore 关键字忽略重复记录。

 

优化group by语句：

默认情况下，MySQL 排序所有 GROUP 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;

 

优化order by：

在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序 。where 条件和 order by 使用相同的索引，并且 order by 的顺序和索引顺序相同 ，并且 order by 的字段都是升序或者都是降序。

例如：下列 sql 可以使用索引。

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是以下情况不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

--order by 的字段混合 ASC 和 DESC

SELECT * FROM t1 WHERE key2=constant ORDER BY key1；

--用于查询行的关键字与 ORDER BY 中所使用的不相同

SELECT * FROM t1 ORDER BY key1, key2；

--对不同的关键字使用 ORDER BY：