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 查询优化
• 函数调用优化
• 窗口函数优化
• 行构造函数表达式优化
• 避免全表扫描