MySQL 性能优化策略

1. 优化表的数据类型，选择合适的数据类型：

   • 原则：在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
     • 在定义邮政编码这个字段时，如果将其设置为 CHAR (255)，显然给数据库增加了不必要的空间，甚至使用 VARCHAR 这种类型也是多余的，因为 CHAR (6）就可以很好的完成任务了。
     • 同样的，如果可以的话，我们应该使用 MEDIUMINT 而不是 BIGIN 来定义整型字段，应该尽量把字段设置为 NOT NULL，这样在将来执行查询的时候，数据库不用去比较 NULL 值。
     • 对于某些文本字段，例如"省份”或者"性别”，我们可以将它们定义为 ENUM 类型，因为在 MySQL 中，ENUM 类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多，这样，我们又可以提高数据库的性能。

2. 最好不要使用返回所有，用具体的字段列表代替 *，不要返回用不到的任何字段。

3. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引。

4. 避免索引失效。

5. 使用反范式表避免联表查询效率低的问题。

6. 使用表的别名（Alias)：当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上，这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。

7. 一些 SQL 查询语句应加上 nolock，读。写是会相互阻塞的，为了提高并发性能，对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据，使用 nolock 有 3 条原则：

   • 查询的结果用于插，删，改的不能加 nolock
   2. 查询的表属于频繁发生页分裂的，慎用 nolock
   • 使用临时表一样可以保存数据前影，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock

8. 常见的简化规则如下：不要有超过 5 个以上的表连接（JOIN)，考虑使用临时表或表变量存放中间结果，少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜。

9. 用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询，他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 UNION all 执行的效率更高，多个 OR 的字句没有用到索引，改写成 UNION 的形式再试图与索引匹配，一个关键的问题是否用到索引。

10. 在 IN 后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

11. 当服务器的内存够多时，配制线程数量 = 最大连接数 + 5，这样能发挥最大的效率，否则使用配制线程数量< 最大连接数，启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数 + 5，严重的损害服务器的性能。

12. 尽量使用 EXISTS 代替 select count(1) 来判断是否存在记录，count 函数只有在统计表中所有行数时使用，而且 count(1) 比 count(*) 更有效率。

13. 当有一批处理的插入或更新时，用批量插入或批量更新，不要一条条记录的去更新。

14. 在所有的存储过程中，能够用 SQL 语句的，不要用循环去实现，例如：列出上个月的每一天，用 connect by 去递归查询，不用循环从上个月第一天到最后一天。

15. 选择最有效率的表名顺序（只在基于规则的优化器中有效）

    • Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。
    • 如果有 3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。

16. 提高 GROUP BY 语句的效率，可以通过将不需要的记录在 GROUP BY 之前过滤掉，下面两个查询返回相同结果，但第二个明显就快了许多。

SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER';

SELECT JOB, AVG(SAL) FROM EMPWHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB;

16. SQL 语句用大写，因为 Oracle 总是先解析 SQL 语句，把小写的字母转换成大写的再执行。
17. 避免死锁，在你的存储过程和触发器中访问同一个表时总是以相同的顺序，事务应尽可能地缩短，在一个事务中应尽可能减少涉及到的数据量，永远不要在事务中等待用户输入。
18. 最好不要使用触发器：
    • 触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程。
    • 如果能够使用约束实现的，尽量不要使用触发器。
    • 不要为不同的触发事件（Insert. Update 和 Delete）使用相同的触发器。
    • 不要在触发器中使用事务型代码。
19. 使用慢查询日志去发现慢查询，使用执行计划去判断查询是否正常运行，总是去测试你的查询看看是否他们运行在最佳状态下。
20. 有时候 MySQL 会使用错误的索引，对于这种情况使用 USE INDEX，检查使用 SQL_MODE=STRICT 的问题，对于记录数小于 5 的索引字段，在 UNION 的时候使用 LIMIT 不是用 OR
21. 为了避免在更新前 SELECT，使用 INSERT ON DUPLICATE KEY 或者 INSERT IGNORE，不要用 UPDATE 去实现，不要使用 MAX，使用索引字段和 ORDER BY 子句 LIMIT，实际上可以减缓查询在某些情况下，有节制地使用，在 WHERE 子句中使用 UNION 代替子查询，在重新启动的 MySQL，记得来温暖你的数据库，以确保数据在内存和查询速度快，考虑持久连接，而不是多个连接，以减少开销。
22. 基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询，当负载增加在服务器上，使用 SHOW PROCESSLIST 查看慢的和有问题的查询，在开发环境中产生的镜像数据中测试的所有可疑的查询。
23. 查询缓冲并不自动处理空格，因此，在写 SQL 语句时，应尽量减少空格的使用，尤其是在 SQL 首和尾的空格（因为查询缓冲并不自动截取首尾空格）
24. MySQL 查询可以启用高速查询缓存，这是提高数据库性能的有效 MySQL 优化方法之一，当同一个查询被执行多次时，从缓存中提取数据和直接从数据库中返回数据快很多。
25. EXPLAIN SELECT 查询用来跟踪查看效果：使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的，这可以帮你分析你的查询语句或是表结构的性能瓶颈，EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的。
26. 当只要一行数据时使用 LIMIT 1
    • 当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去 fetch 游标，或是你也许会去检查返回的记录数。
    • 在这种情况下，加上 LIMIT 1 可以增加性能，这样一来，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。
27. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源，若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。