MySQL DBA必读：万字归总表设计与SQL编写技巧( 七 ) _MySQL

因此，我们并不推荐通过设置这个参数来避免insert...select...导致的锁，如果需要进行可能会扫描大量数据的insert...select操作，我们推荐使用select...into outfile和load data infile的组合来实现，这样是不会对纪录进行锁定的。
例子：
INSERT INTO SMAP_HISTORY.SMAP2_SESSION (SESSION_ID,SESSION_TICKET_ID) SELECT S.SESSION_ID,S.SESSION_TICKET_ID FROM SMAP.SMAP2_SESSION S WHERE SESSION_SID = #sessionId#;
以上语句会对表SMAP2_SESSION施加表锁，而由于业务上该表存在大量insert语句，业务压力大的时候极易造成严重的阻塞。
5、适当使用commit
适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

事务占用的undo数据块；
事务在redo log中记录的数据块；
释放事务施加的，减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit 。

6、减少表的锁冲突
对 Innodb 类型的表：
1）首先要确认，在对表获取行锁的时候，要尽量的使用索引检索纪录，如果没有使用索引访问，那么即便你只是要更新其中的一行纪录，也是全表锁定的。要确保 sql 是使用索引来访问纪录的，必要的时候，请使用 explain 检查 sql 的执行计划，判断是否按照预期使用了索引。
2）由于 MySQL 的行锁是针对索引加的锁，不是针对纪录加的锁，所以虽然是访问不同行的纪录，但是如果是相同的索引键，是会被加锁的。应用设计的时候也要注意，这里和 Oracle 有比较大的不同。
3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，当表有主键或者唯一索引的时候，不是必须使用主键或者唯一索引锁定纪录，其他普通索引同样可以用来检索纪录，并只锁定符合条件的行。
4）如果要使用锁定读，（SELECT ... FOR UPDATE 或 ... LOCK IN SHARE MODE），尝试用更低的隔离级别，比如 READ COMMITTED 。
7、使用SQL_BUFFER_RESULT减少锁定时间
将强制 MySQL 生成一个临时结果集。只要所有临时结果集生成后，所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助。当处理一个会让客户端耗费点时间才能处理的大结果集时，可以考虑使用SQL_BUFFER_RESULT 提示字。这样可以告诉MySQL将结果集保存在一个临时表中，这样可以尽早的释放各种锁。需注意，该参数不能用于子查询中以及union之后语法：SELECT SQL_BUFFER_RESULT …
8、正确使用hint优化语句
MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。一般而言，处于版本变更带来的表结构索引变化，更建议避免使用hint，而是通过Analyze table多收集统计信息。但在特定场合下，指定hint可以排除其他索引干扰而指定更优的执行计划。
1）USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...
2）IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作为 Hint 。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...
3）FORCE INDEX 为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为Hint 。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...
9、优化group by语句
默认情况下，MySQL 排序所有 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。
如果查询包括 GROUP BY 但你想要避免排序结果的消耗，你可以指定 ORDER BY 禁止排序。例如：
SELECT a, COUNT(1) FROM table GROUP BY a ORDER BY ;
10、优化order by语句
在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。where 条件和 order by 使用相同的索引，并且 order by 的顺序和索引顺序相同，并且 order by 的字段都是升序或者都是降序。
例如：下列 SQL 可以使用索引。
SELECT col1 FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT col1 FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
以上复合索引包含字段key_part1，key_part2...
但是以下情况不使用索引：
SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
以上由于order by 的字段混合 ASC和 DESC。