数据库优化——常用SQL优化

摘要

本文为数据库优化系列文章的第二篇文章 :《数据库优化——常用SQL优化》

更多文章参见

数据库优化 : http://blog.csdn.net/leyounger/article/details/70157087

---

常用SQL优化

1. 大批量插入数据

当用load命令导入数据的时候，适当的设置可以提高导入的速度，对于MyISAM表存储引擎的表，可以通过如下方式快速的导入大量的数据：

ALTER TABLE tbl_name DISABLE KEYS;loading the dataALTER TALBE tbl_name ENABLE KEYS;

其中的DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭MyISAM表非唯一索引的更新，如果表原来是非空的，那么导入过程中，数据库将会导入一条数据，修改一次索引，效率非常低下。因此我们干脆把索引关掉，一次性插入所有数据后，再打开索引。

注意！ 空MyISAM表默认先导入数据后再创建索引，所以不用设置

氮素，这种方式对于InnoDB来说没有效果！可以有如下几种方式提高InnoDB的导入效率：

• 导入的数据按主键的顺序保存
• 导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，导入结束后再恢复SET UNIQUE_CHECKS=1

mysql> SET UNIQUE_CHECKS=0;mysql> load data infile '/home/musql/film.txt' into table film;mysql> SET UNIQUE_CHECKS=1;

• 如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再恢复，可以提高效率

mysql> SET AUTOCOMMIT=0;mysql> load data infile '/home/musql/film.txt' into table film;mysql> SET AUTOCOMMIT=1;

---

2. 优化INSERT语句

• 如果同时从同一用户插入很多行，尽量使用多个值表的INSERT语句，这么做可以大大缩减客户端与数据库之间的连接、关闭等消耗：

INSERT INTO test values (1,2),(1,3),(1,4) ....

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

• 将索引文件和数据文件分在不同的磁盘上存放

• 如果是批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这支队MyISAM有效

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

---

3. 优化GROUP BY语句

默认情况下，MySQL对所有的GROUP BY语句进行排序。也就是默认会执行一次ORDER BY。。。

如果查询包括GROUP BY，但是用户想避免排序的消耗，则可以指定ORDER BY NULL 禁止排序：

// 低效率 GROUP BYmysql> SELECT id, sum(moneys) FROM sales GROUP BY id;// 高效率 GROUP BYmysql> SELECT id, sum(moneys) FROM sales GROUP BY id ORDER BY NULL;

具体可用EXPLAIN 语句来查看GROUP BY 语句是否使用了排序，在Extra选项中若有Using filesort，说明执行了排序。

---

4. 优化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_part2 DESC, key_part2 DESC;

氮素，以下几种情况不适用索引

//ORDER BY 的字段混合ASC 和 DESCSELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;//用于查询行的关键字与ORDER BY中所使用的不相同SELECT * FROM t1 WHERE key2=constant ORDER BY key1;//对不同的关键字使用ORDER BY，这里我也没看懂和上面第一句有啥区别SELECT * FROM t1 ORDER BY key1, key2;

---

5. 优化嵌套查询

使用子查询可以完成逻辑上复杂的SQL操作，避免失误或者表锁死。

很多情况下，子查询可以被更有效率的JOIN操作替代

举个栗子，要从sale2表中找到那些在company2表中不存在的所有公司的信息

SELECT * FROM sales2     WHERE company_id NOT IN         (SELECT id FORM company2);

如果使用JOIN来完成这个查询，速度会快得多，尤其当company2对id建立索引的话

SELECT * FROM sales2     LEFT JOIN company2 ON sales2.company_id=company2.id     WHERE sales2.company_id IS NULL;

JOIN之所以更有效率是因为MySQL不需要再内存中创建临时表来完成查询操作。

---

6. 优化OR条件

对于含有OR的查询语句，如果要利用索引，那么OR中的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引。

使用show index from tbl_name来查看索引

MySQL对于OR的操作，实际上是对OR的每个条件进行分别查询后，对结果进行了UNION

注意！ MySQL对联合索引内部列之间的OR操作，不使用索引！

举个栗子，假设我们对字段a,b进行了独立索引，对c,d进行了联合索引

//该查询使用了索引SELECT * FROM table WHERE a=0 OR b=0;//该查询未使用索引SELECT * FROM table WHERE c=0 OR d=0;

---

7. 使用SQL提示

SQL HINT是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

下面是一个使用SQL提示的例子：

SELECT SQL_BUFFER_RESULTS * FROM ...

这个语句强制MySQL生成一个临时结果集，只要临时结果集生成后，所有表上的锁都会被释放，这能在遇到锁定问题时或要花很长时间将结果传回客户端时有所帮助，因为可以尽快释放锁资源。

下面是一些MySQL中常用的SQL提示。

• USE INDEX

在查询语句的表名后，添加USE INDEX来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

mysql> SELECT * FROM sales2 USE INDEX (ind_sales_id) WHERE id = 3;

• IGNORE INDEX

如果用户单纯想让MySQL忽略一个或多个索引，则可以使用IGNORE INDEX

SELECT * FROM sales2 IGNORE INDEX (ind_sales_id) WHERE id = 3;

• FORCE INDEX

强制MySQL使用一个特定的索引，即使索引不会提高效率，MySQL还是会使用这个索引

SELECT * FROM sales2 FORCE INDEX (ind_sales_id) WHERE id = 3;