MySQL-优化SQL语句-SELECT查询优化

查询以 SELECT 语句的形式执行数据库中的所有查找操作。优化这些语句是首要内容，无论是实现*态网动**页的亚秒响应时间，还是缩短生成大量夜间数据报表的时间。

除了 SELECT 查询语句外，查询优化技术也适用于复制表结构或者数据，比如 CREATE TABLE...AS SELECT ， INSERT INTO...SELECT 和在 DELETE 的语句中的 WHERE 条件。因为这些语句包含了写操作和查询操作，因此也需要考虑对性能进行优化。

NDB Cluster 支持 JOIN 谓词下推的优化，其中合格的 join 被完整发送到 NDB Cluster 数据节点， JOIN 语句可以分布在各个数据节点并行执行。

优化查询的主要考虑因素是：

索引

要使 SELECT ... WHERE 语句查询效率更高，首先要检查是否可以使用索引。在 WHERE 子句中涉及的列上建立索引，以加快过滤查询结果。为避免浪费磁盘空间，在建立索引时，尽量选择最少的列构建组合索引来满足查询优化性能。

通过 join 和 foreign key 等关联其他的表进行查询时，索引显得尤为重要 。您可以使用该 EXPLAIN 语句来确定 SELECT 语句用了哪种类型的索引。

隔离和调整查询

隔离和调整查询的任何部分，例如函数调用需要耗费过多的时间。根据查询的结构方式，函数调用可以为结果集中的每一行调用一次函数，也可以为表中的每一行调用一次函数。

减少全表扫描次数

尽量减少查询中的全表扫描次数 ，尤其是对于大表查询。

保持表统计信息最新

定期使用 ANALYZE TABLE 语句对表统计信息进行更新，从而使执行计划和查询性能更加稳定。我们知道查询优化器使用了基于代价的查询执行计划进行估算。依赖于被查对象的各种数据又是动态变化的，如表的元组数。如果实时获取这些数据，系统计算的开销会比较大，因此需要对表统计信息进行定时的更新，并持久化统计信息已保证在服务器重新启动时保持不变。

掌握调优技术

对于不同的存储引擎（ InnoDB 和 MyISAM ），掌握不同存储引擎的调优技术、索引技术和配置参数。

优化 InnoDB 只读事务

InnoDB 可以避免与为已知为只读的事务设置事务 ID（ TRX_ID 字段）相关的开销。事务ID只有在写操作或者锁定读取的事务时才需要，例如： SELECT ... FOR UPDATE 。每次查询或更操作构造读取视图时，消除不必要的事务 ID能减少所需要参考的内部数据结构大小，进而提升效率。

使用 EXPLAIN 执行计划

如果某个基本准则无法轻松解决性能问题，请通过阅读 EXPLAIN 执行计划调整索引、 WHERE 子句、 JOIN 子句等来研究查询的内部细节 。（当您达到一定的专业水平时，阅读 EXPLAIN 计划可能是您每次查询的第一步。）

优化缓冲池

调整 MySQL 用于缓存的内存区域的大小和属性。通过有效地使用 InnoDB 缓冲池、 MyISAM 键缓存和 MySQL 查询缓存，使重复查询运行得更快，因为后续的重复查询均是从内存中获取上一次的查询结果。

减少缓存使用

即使对于使用高速缓存可以达到快速的查询速度，仍需进一步优化，以达到使用更少的缓存，从而使您的应用程序更具可扩展性。可扩展性意味着您的应用程序可以处理更多并发用户、更大的请求等，而不会导致性能大幅下降。

锁级别

处理锁问题（选择行级锁和表级锁），其中查询速度可能会受到同时访问表的其他会话的影响。

后续章节将从以下角度对各个优化进行详细描述：

• WHERE 子句优化
• 范围查询优化
• 索引合并优化
• Hash Join 优化
• 发动机状态下推优化
• 索引条件下推优化
• 嵌套循环连接算法
• 嵌套连接优化
• 外连接优化
• 外连接优化
• 多范围读取优化
• 阻止嵌套循环和批处理键访问（BKA）联接算法优化
• 条件过滤
• 常量折叠优化
• IS NULL 优化
• ORDER BY 优化
• GROUP BY 优化
• DISTINCT 优化
• LIMIT 查询优化
• 函数调用优化
• 窗口函数优化
• 行构造函数表达式优化
• 避免全表扫描