MySQL：SQL优化的一些建议

1、索引优化

1.1 一般性建议

• 对于单键索引，尽量选择对当前query过滤性更好的索引；
• 在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好；
• 在选择复合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引；
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

1.2 避免索引失效

1. 以模糊匹配开头的like查询不能使用B-Tree索引；
2. 出现数据类型隐式转换的时候不能使用索引，尤其需要注意的是where中的字符串常量一定要用引号引起来；
3. 复合索引的情况下，如果查询条件不包含索引列的最左边一项，即不满足最左原则，则不会使用复合索引；
4. 如果MySQL任务使用索引比全表扫描更慢，则不会使用索引；
5. 用or分割开的条件，如果or前的条件中有索引列，但是后面的列中没有索引列，那么涉及到的索引都不会被用到。因为or后面的条件中没有索引，那么后面的查询肯定会走全表扫描，所以就没必要再多一次索引扫描增加IO访问了。

2、SQL语句优化

    重要的优化包括：

2.1 insert优化

• 如果同时从同一客户端向一个表中插入很多行，应尽量将多个插入合并到一行，以减少客户端和数据库之间的连接、关闭的消耗；
• 如果从不同客户端向一个表中插入很多行，可以通过使用INSERT DELAYED语句得到更高的速度。DEALYED的含义是让INSERT语句插入的数据放在内存队列中，等待MySQL空闲的时候再去插入，而MySQL返回OK状态给客户端；
• 将索引文件和数据文件分别保存在不同的磁盘上；
• 如果进行批量插入，可以通过增加bulk_insert_buffer_size的变量值的方法来提高速度，但是只对MyISAM有用；
• 当从文件中装载一个表的时候，使用load data infile比insert快20倍左右。

2.2 order by优化

    尽量使用有序索引排序：

• 当where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的顺序都是升序或者降序，此时会使用有序索引排序；

    Filesort优化：

• 适当增大max_length_for_sort_data的值，能够让MySQL选择一次扫描算法。当然max_length_for_sort_data过大会导致CPU利用率过低和磁盘IO过高；
• 适当增大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行。当然，由于排序区sort buffer是线程独占的，设置过大会导致服务器内存开销过大；
• select中尽量只使用必要的字段，避免select *的情况，可以减少排序区的使用，提高SQL性能。

2.3 group by优化

• 如果查询包含GROUP BY，但是用户想要避免排序结果的消耗，可以指定order by null来禁止排序；
• 其他优化手段参照order by。

2.4 其他优化

• 优化嵌套查询：使用连接join替换子查询效率更高，因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
• 优化OR条件：对于含有OR的查询子句，如果要使用索引，则OR之间的每个条件列都必须用到索引。如果没有索引，则应该考虑增加索引。

3、数据库对象优化

3.1 优化表的数据类型

    使用procedure analyse()函数对当前应用的表进行分析。可用优化：

• 字段长度缩减；
• varchar如果长度不变，可用修改成char；
• 字段的值只有少量确定结果的情况下，可用使用枚举enum；

3.2 表的拆分

• 垂直拆分：可以使数据行变小，一个数据页就能存放更多的数据，这样在查询时就能减少IO次数；
• 水平拆分：分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度

3.3 使用中间表

• 中间表复制源表的部分数据，并且和源表相隔离，在中间表上做统计查询不会对在线应用产生负面影响；
• 中间表上可以灵活地添加索引或者增加临时使用的新字段，从而达到提高统计查询效率和辅助统计查询的作用。

3.4 逆规范化

    逆操作化可以降低连接操作的需求，降低外键和索引的数量，甚至降低表的数量，可以提高查询效率。

• 增加冗余列
• 增加派生列
• 重新租表
• 分割表

4、锁优化

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁；
• 合理设计索引，尽量缩小锁的范围；
• 尽可能减少检索条件，避免间隙锁；
• 尽量控制事务大小，减少锁定资源量和时间长度；
• 尽可能使用低级别事务隔离。