MySQL SQL优化 实践笔记

目录

SQL执行过程

EXPLAIN执行计划

SQL优化

优化SQL语句的一般步骤

高性能MySQL 第六章 查询优化

---

SQL执行过程

SQL语句在服务端的执行过程：

1. 查询缓存
2. 对SQL按关键字进行解析(顺序、位置)、预处理(表、列是否存在)，生成解析树
3. 查询优化器生成执行计划
4. 交给存储引擎来执行

概述：MySQL拥有分层的架构，上层是服务器层的服务和查询执行引擎，下层则是存储引擎。

MySQL的核心基础架构的精髓：MySQL在存储引擎和服务层之间处理查询时如何通过API来回交互

EXPLAIN执行计划

提供有关于MySQL如何执行语句的信息，可用于SELECT,DELECT,UPDATE；

EXPLAIN 为SELECT中的每一个表返回一行信息，列出的顺序按MySQL读取表的顺序，+\G，可以竖排显示

• possible_keys：表中索引
• key：使用的索引
• key_len：索引字段的最大长度
• rows：扫描行数
• type：访问类型
  • all：全表遍历，没用索引，O(k*n)，
    • 一些会全表的特殊情况：
      • 表<10行时
      • 没有对所有列的 ON 或 WHERE约束
      • 将索引列与常量比较，MySQL计算发现这个常量覆盖表的大部分            
  • index：索引遍历，用了索引，O(n)
  • range：索引范围查询，O(m)，m是范围
  • ref：普通索引的查询(索引字段有重复)，O(m)，m是重复次数
  • eq_ref：多表连接时，主键、唯一索引与前表字段比较时，比较次数取决于前表筛选后数目
  • const：单表，主键、唯一索引与常量比较时，O(1)
  • system：表只有1行数据

ref和eq_ref的区别：一个是主键或唯一键索引，另一个是平常索引

const和eq_ref的区别：const是单表，只要执行一次索引查询、取数据，是常量时间；eq_ref用于多表连接，是多次索引查询

• ref：与其他表关联的字段
• select_type：
  • simple
  • primary：子查询的语句的 最外层查询
  • subquery: 子查询

SQL优化

• WHERE中与NULL比较（IS NULL或NOT IS NULL）是否影响索引
  • 若是索引列允许为NULL，则WHERE中将索引列与NULL比较不影响索引；索引列不允许NULL时，索引失效
• count(*) 走主键索引的遍历，即type里的INDEX
• 索引列失效的场景
  • !=
  • where中对索引字段进行计算（如where age+2 < 5;)
  • where中用or
  • like '%随便一个字符串'
  • order by 非索引字段
  • 解释：这些场景的典型特征就是需要对索引列中的全部数据进行遍历，比如说!=，你不每个比较谁知道哪个不等啊；计算，每个都得先计算；or 即使你左边的索引列比较不满足，也得遍历，因为可能后面得条件也得判断；like 条件以%号起始时，相当于后缀判断，但索引是以前缀的字符作为判断大小的，所以索引失效；order by 好理解，都按非索引列排序了，肯定得遍历非索引列 
•  <>走范围索引，!=走索引遍历

优化SQL语句的一般步骤

• show status命令了解各种SQL的执行频率：以插入更新为主还是以查询操作为主
• 慢查询日志定位执行效率较低的SQL语句
  • 设置慢查询时长set global long_query_time=3;
  • 开启慢查询日志set global slow_query_log=1;
  • 查看慢查询日志位置slow_query_log_file
• 通过EXPLAIN分析低效SQL的执行计划
• 通过show profile分析SQL，更清楚地了解SQL执行的过程
• 通过 trace分析优化器如何选择执行计划，为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为
• 确定问题并采取相应的优化措施

高性能MySQL 第六章 查询优化

6.1 为什么查询会慢(响应时间)？

• 减小某个子任务的运行时间查询任务 = N个子任务，优化查询 = 减少子任务个数  |  减少某个子任务执行次数  |   减小某个子任务的运行时间
• 查询有哪些子任务？哪些子任务运行得很慢？
  • 查询的生命周期：从客户端，到服务器上进行解析，生成执行计划，执行，返回结果给客户端
    • 其中“执行”最耗时，包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
    • 在完成这些任务的时候，查询需要在不同的地方花费时间，
      • 包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作
      • 尤其是向 底层存储引擎 检索数据的调用操作
        • 这些调用需要在 内存操作、CPU操作和内存不足时 导致的I/O操作上消耗时间。
      • 根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。  
  • 优化查询的目的就是减少和消除这些操作所花费的时间

6.2 慢查询基础：优化数据访问    

对于MySQL，最简单的衡量查询开销的三个指标如下：响应时间、扫描的行数、返回的行数

查询性能低下 最基本的原因 是 访问的数据太多，通过下面两个步骤来分析：

• 6.2.1 是否请求了不需要的数据(客户端)
  • select *查询不需要的记录
    • 错误的以为MYSQL只返回需要的数据，而事实是先返回全部结果，再丢弃不需要的
    • 必须加上LIMIT
  • 多表关联时返回全部列
    •  select时只取需要的列
  • 总是取出全部列           
    • SELECT * 时怀疑下：是否需要取出所有列
    • 有时是好事：可以用来做缓存
  • 重复查询相同的数据：应该缓存         
• 6.2.2 MySQL是否扫描了额外的记录(服务端)
  • 多表连接的顺序
  • 索引

MySQL执行一个查询的过程：

• 客户端发送一条查询给服务器
  • 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果
• 否则服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
• MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
• 将结果返回给客户端

查询状态：表示MySQL当前正在做什么

• Sleep 等待客户端发送新的请求
• Query 线程正在执行查询 或者 正在将结果发送给客户端
• Locked 在MySQL服务器层，该线程正在等待表锁
• Analyzing and statistics 线程正在收集存储引擎的统计信息，并生成查询的执行计划
• Copying to tmp table [on disk] 线程正在执行查询，并且将其结果集都复制到一个临时表中，
  • 这种状态一般要么是在做GROUP BY 操作，
  • 要么是文件排序操作，
  • 或者是UNION操作。
  • 如果这个状态后面还有“on disk”标记，那表示MySQL正在将一个内存临时表放到磁盘上
• The thread is 线程正在对结果集进行排序

• Sending data 这表示多种情况：线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

• 6.4.3 SQL转换成执行计划的步骤：
  • 解析: 语法规则验证、解析查询，通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”
  • 预处理器：
    • 检查解析树是否合法，如：表、列是否存在
    • 验证权限        
  • 查询优化器: 基于成本，找到查询执行方式中最好的执行计划

• 优化关联查询
  • 确保ON 或者USING 子句中的列上有索引
  • 在创建索引时要考虑关联顺序，一般只需要在 关联顺序的第二个表的相应关联列上 创建索引 就够了
    •  (第一个表 必须全表扫描，在找匹配项时，第二个可以用索引优化)
  • 确保GOURP BY，ORDER BY中的表达式只涉及  一个表  的列，这样才能索引
  • 升级MYSQL时，注意关联语法可能变化