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

SELECT col1 FROM t1 WHERE key2=constant ORDER BY key1；
以上用于查询行的关键字与 ORDER BY 中所使用的不相同。
SELECT col1 FROM t1 ORDER BY key1, key2；
对不同的索引关键字使用 ORDER BY：
11、优化join语句
MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。
例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID
FROM salesinfo )
如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：
SELECT col1 FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID IS
连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
12、优化or条件
对于 or 子句，如果要利用索引，则or 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。
13、优化union查询
MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all 。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。
高效：
SELECT COL1, COL2, COL3
FROM TABLE
WHERE COL1 = 10
UNION ALL
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';
低效：
SELECT COL1, COL2, COL3
FROM TABLE WHERE COL1 = 10
UNION
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';
14、拆分复杂SQL为多个小SQL，避免大事务

简单的SQL容易使用到MySQL的QUERY CACHE；
减少锁表时间特别是使用MyISAM存储引擎的表；
可以使用多核CPU 。

15、使用truncate代替delete
当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位。
16、使用合理的分页方式以提高分页效率
使用合理的分页方式以提高分页效率针对展现等分页需求，合适的分页方式能够提高分页的效率。
案例1：
select * from t
where thread_id = 10000
and deleted = 0
order by gmt_create asc limit 0, 15;
上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO 。因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。
适用场景：当中间结果集很小（10000行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。
案例2：
select t.* from (
select id from t
where thread_id = 10000 and deleted = 0 order by gmt_create asc limit 0, 15) a, t
where a.id = t.id;
上述例子必须满足t表主键是id列，且有覆盖索引secondary key:(thread_id, deleted, gmt_create) 。通过先根据过滤条件利用覆盖索引取出主键id进行排序，再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果（例子中是15行）对应的表数据IO 。因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。
适用场景：当查询和排序字段（即where子句和order by子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。
17、避免不走索引的各种场景
在下面的SQL语句中的WHERE子句不使用索引：
1）条件中有or，且or左右列并非全部由索引 Select col1 from table where key1=1 or no_key=2
2）like查询以%开头
3）where条件仅包含复合索引非前置列
Select col1 from table where key_part2=1 and key_part3=2
索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列。