浅析优化MySQL特定类型的查询

1.优化COUNT()查询

1. COUNT()的作用：
   1、是一个特殊的函数，它可以统计某个列值的数量，也可以统计行数。在统计列值要求列值是非空的。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。
   2、另一个作用是统计结果集的行数。当确认括号内的表达式值不可能为空值，实际上就是在统计行数。
2. 简单的优化：
   1、有时候可以使用MyISAM在COUNT(*)全表非常快的这个特性，来加速一些特定条件的COUNT()查询。
3. 更复杂的优化：
   1、通常来说，COUNT()都需要扫描大量的行才能获得精确的结果，因此是很难优化。除了前面的方法，在mysql层面还能做的就只有索引覆盖扫描了。

2.优化关联查询

1. 确保ON或者USING子句中的列有索引。在创建索引的时候就要考虑到关联的顺序。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
2. 确保任何的GROUP BY和ORDER BY中的表达式只涉及到有个表中的列，这样mysql才有可能使用索引来优化这个过程。

3.优化子查询

1. 尽可能使用关联查询代替。如果使用的是5.6版本以上，那么就直接忽略关于子查询的这些建议了。

4.优化GROUP BY和DISTINCT

1. mysql优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化。
2. 当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。
3. 如果需要对关联查询做分组，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。
4. 在分组查询的SELECT中直接使用非分组列通常都不是什么好主意，因为这样的结果通常是不定的，当索引改变，或者优化器选择不同的优化策略时都可能导致结果不一样。建议将mysql的SQL_MODE设置为包含ONLY_FULL_GROUP_BY，这时会直接返回一个错误。
5. 如果没有通过ORDER BY子句显式地指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让mysql不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC。
6. 优化GROUP BY WITH ROLLUP
   1、分组查询的一种变种就是要求对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP。
   2、在应用程序中做超级聚合是更好的，虽然这需要返回给客户端更多的结果。也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终结果。

5.优化LIMIT分页

1. 如果有对应的索引，效率会不错，否则，需要做大量的文件排序操作。
2. 要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。
3. 一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是扫描所有的列。

6.优化UNION查询

1. mysql总是通过创建并填充临时表的方式来执行UNION查询。经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。
2. 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，mysql会给临时表加上DISTINCT选项，这会导致唯一性检查。

7.静态查询分析

1. Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。

8.使用用户自定义变量

1. 在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能会非常有用。

2. 用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都存在。可以用SET和SELECT语句来定义他们。

3. 自定义变量的属性和限制：
   1、使用自定义变量的查询，无法使用查询缓存。
   2、不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。
   3、用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
   4、如果使用连接池或持久化连接，自定义变量可能让看起来毫无关系的代码发生交互。
   5、在5.0之前的版本，是大小写敏感的。
   6、不能显式地声明自定义变量的类型。确定自定义变量的具体类型的时机在不同的版本也可能不一样。如果希望变量是整数，在初始化就赋值0；浮点就赋值0.0；字符串就赋值’'。
   7、优化器在某些场景下可能会将这些变量优化掉。
   8、赋值的顺序和复制的时间点并不总是固定的，这依赖于优化器的决定。
   9、赋值符号:=的优先级非常低。
   10、使用自定义变量不会产生任何语法错误。

4. 使用用户自定义变量的一个重要特性是可以在给一个变量赋值的同时使用这个变量。

1.优化COUNT()查询

1. COUNT()的作用：
   1、是一个特殊的函数，它可以统计某个列值的数量，也可以统计行数。在统计列值要求列值是非空的。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。
   2、另一个作用是统计结果集的行数。当确认括号内的表达式值不可能为空值，实际上就是在统计行数。
2. 简单的优化：
   1、有时候可以使用MyISAM在COUNT(*)全表非常快的这个特性，来加速一些特定条件的COUNT()查询。
3. 更复杂的优化：
   1、通常来说，COUNT()都需要扫描大量的行才能获得精确的结果，因此是很难优化。除了前面的方法，在mysql层面还能做的就只有索引覆盖扫描了。

2.优化关联查询

1. 确保ON或者USING子句中的列有索引。在创建索引的时候就要考虑到关联的顺序。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
2. 确保任何的GROUP BY和ORDER BY中的表达式只涉及到有个表中的列，这样mysql才有可能使用索引来优化这个过程。

3.优化子查询

1. 尽可能使用关联查询代替。如果使用的是5.6版本以上，那么就直接忽略关于子查询的这些建议了。

4.优化GROUP BY和DISTINCT

1. mysql优化器会在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化。
2. 当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组。
3. 如果需要对关联查询做分组，并且是按照查找表中的某个列进行分组，那么通常采用查找表的标识列分组的效率会比其他列更高。
4. 在分组查询的SELECT中直接使用非分组列通常都不是什么好主意，因为这样的结果通常是不定的，当索引改变，或者优化器选择不同的优化策略时都可能导致结果不一样。建议将mysql的SQL_MODE设置为包含ONLY_FULL_GROUP_BY，这时会直接返回一个错误。
5. 如果没有通过ORDER BY子句显式地指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让mysql不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC。
6. 优化GROUP BY WITH ROLLUP
   1、分组查询的一种变种就是要求对返回的分组结果再做一次超级聚合。可以使用WITH ROLLUP。
   2、在应用程序中做超级聚合是更好的，虽然这需要返回给客户端更多的结果。也可以在FROM子句中嵌套使用子查询，或者是通过一个临时表存放中间数据，然后和临时表执行UNION来得到最终结果。

5.优化LIMIT分页

1. 如果有对应的索引，效率会不错，否则，需要做大量的文件排序操作。
2. 要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。
3. 一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是扫描所有的列。

6.优化UNION查询

1. mysql总是通过创建并填充临时表的方式来执行UNION查询。经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。
2. 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL。如果没有ALL关键字，mysql会给临时表加上DISTINCT选项，这会导致唯一性检查。

7.静态查询分析

1. Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式，然后给出所有可能存在潜在问题的查询，并给出足够详细的建议。

8.使用用户自定义变量

1. 在查询中混合使用过程化和关系化逻辑的时候，自定义变量可能会非常有用。

2. 用户自定义变量是一个用来存储内容的临时容器，在连接mysql的整个过程中都存在。可以用SET和SELECT语句来定义他们。

3. 自定义变量的属性和限制：
   1、使用自定义变量的查询，无法使用查询缓存。
   2、不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。
   3、用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
   4、如果使用连接池或持久化连接，自定义变量可能让看起来毫无关系的代码发生交互。
   5、在5.0之前的版本，是大小写敏感的。
   6、不能显式地声明自定义变量的类型。确定自定义变量的具体类型的时机在不同的版本也可能不一样。如果希望变量是整数，在初始化就赋值0；浮点就赋值0.0；字符串就赋值’'。
   7、优化器在某些场景下可能会将这些变量优化掉。
   8、赋值的顺序和复制的时间点并不总是固定的，这依赖于优化器的决定。
   9、赋值符号:=的优先级非常低。
   10、使用自定义变量不会产生任何语法错误。

4. 使用用户自定义变量的一个重要特性是可以在给一个变量赋值的同时使用这个变量。