Mysql 语句的优化技巧

(一) MySQL 逻辑架构

MySQL 将逻辑架构分为4层:

• 连接层: 最上层是一些客户端和链接服务, 包含本地sock通信和大多数基于客户端/服务端工具实现的 TCP/IP 的通信
• 服务层: 在此层中完成了大多的核心功能
  - SQL Interface: 接受用户的SQL语句, 并且返回用户需要查询的结果
  - Parser: 解析器, 区分SQL语句的类型, 转发给对应的处理模块
  - Optimizer: 查询优化器, 在执行查询SQL之前会使用查询优化器进行优化, 如, SQL的执行顺序、是否使用索引
  - 其他: 查询缓存、存储过程、函数在该层解析执行
• 引擎层: 存储引擎层, 真正负责MySQL中数据的存储和提取, 服务器通过API与存储引擎进行通信, 不同的存储引擎具有的功能不同, 可以根据业务来选择具体的存储引擎. 常用的存储引擎有两种: MylSAM 和 InnoDB
• 存储层: 数据存储层, 主要是将数据存储在运行与裸设备的文件系统之上, 并完成与存储引擎的交互

MySQL 与其他数据相比, 最与众不同的点在于: 它的架构可以在多种不同场景中应用并发挥良好作用, 最主要提现在存储引擎的架构上

插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离, 这种架构可以根据业务的需求和实际需要选择合适的存储引擎

(二) MySQL 存储引擎

存储引擎: 是数据库底层软件组织, 数据库系统使用引擎进行 创建、查询、更新和删除数据的操作, 不同的存储引擎提供了不同的存储机制、索引技巧、锁定水平等功能, 使用不同的存储引擎, 还可以获得特定的功能. 常用的存储引擎有两种: MyISAM 和 InnoDB

show engines; # 查看当前版本的MySQL支持的存储引擎

show variables like '%storage_engine%'; # 查看当前版本的MySQL默认的存储引擎

MyISAM 和 InnoDB 存储引擎的比较

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁, 操作一条记录就会锁住整张表	行锁, 只锁住操作的某一行
缓存	只缓存索引, 不缓存真实数据	缓存索引以及真实数据, 对内存要求较高
表空间	小	大
关注点	性能	事务

MySQL 执行加载顺序

一条正常的SQL语句通常包含以下关键字:
SELECT DISTINCT 列名 FROM 表名 JOIN 表名 ON 表连接条件 WHERE 查询条件
GROUP BY 分组条件 HAVING 分组查询条件 ORDER BY 排序条件 LIMIT 分页条件

MySQL执行加载顺序为:
FROM 表名 JOIN 表名 ON 表连接条件 WHERE 查询条件 GROUP BY 分组条件 HAVING 分组查询条件
SELECT DISTINCT 列名 ORDER BY 排序条件 LIMIT 分页条件

7 种 JOIN 连接查询

• tbl_a INNER JOIN tbl_b ON tbl_a.key = tbl_b.key: 内连接, 取两张表的交集元素
• tbl_a LEFT JOIN tbl_b ON tbl_a.key = tbl_b.key: 左连接, 左表所有元素 + 交集元素 (NULL, 补齐右表数据)
• tbl_a RIGHT JOIN tbl_b ON tbl_a.key = tbl_b.key: 右连接, 右表所有元素 + 交集元素 (NULL, 补齐左表数据)

• tbl_a LEFT JOIN tbl_b ON tbl_a.key = tbl_b.key WHERE tbl_b.key IS NULL : 查询左表独有数据
• tbl_a RIGHT JOIN tbl_b ON tbl_a.key = tbl_b.key WHERE tbl_a.key IS NULL : 查询右表独有数据

• tbl_a FULL OUTER JOIN tbl_b ON tbl_a.key = tbl_b.key: 全连接, 查询两张表的所有元素
• tbl_a FULL OUTER JOIN tbl_b ON tbl_a.key = tbl_b.key WHERE tbl_a.key IS NULL OR tbl_b.key IS NULL: 查询两张表独有的数据

注: MySQL 不支持全关联 FULL (OUTER) JOIN, 可以使用 左右关联 + UNION 来实现全关联

(三) MySQL 索引

索引: MySQL 官方对索引的定义为: Index索引 是帮助MySQL高效获取数据的数据结构, 即 索引是一种 排好序的快速查找数据结构, 实现 快速查找(WHERE) 和 排序(ORDER) 的功能

数据结构: 在数据库中, 除了存储数据本身之外, 还维护着一个满足特定查找算法的数据结构, 这些数据结构以某种方式指向数据, 并再次数据结构的基础上实现高级查找算法, 这种数据结构就是索引

索引类型: 索引类型一般为B树(多路搜索树, 并不一定是二叉树) 结构组织索引. 其中 聚集索引、次要索引、覆盖索引、复合索引、唯一索引 默认使用B+树索引. 除此之外, 还有其他索引: 哈希索引(hash)、全文索引(full-text)、R-Tree索引等

索引的分类:

• 单值索引: 一个索引只包含单个列, 一个表可以有多个单列索引
• 唯一索引: 索引列的值必须唯一, 但允许有空值
• 复合索引: 一个索引包含多个列
• 覆盖索引: MySQL只需要通过索引就可以返回查询所需要的数据, 而不需要根据索引在此读取数据文件. 即 查询的字段刚好 存在于 建立的索引中 (一定是覆盖, 可以查索引中的一个或几个字段, 不能超出 且与查询的字段顺序无关)

基本语法:

• 创建索引:
  • CREATE [UNIQUE] INDEX indexName ON tbl(colunmName, …)
  • ALTER TABLE tbl ADD [UNIQUE] INDEX [indexName] (colunmName, …)
• 删除索引: DROP INDEX indexName ON tbl
• 查看索引: SHOW INDEX FROM tbl

需要使用索引的情况:

• 主键自动建立唯一索引
• 频繁作为查询条件的字段
• 需要排序的字段
• 查询中统计或分组的字段
• 外键对应的字段
• 优先创建复合索引

不需要使用索引的情况

• 表中数据量太少
• 经常增删改的表
• 数据重复且分布平均的表字段, 如 性别等. 提供一个公式, 公式得到的值越接近1, 该字段建立的索引效率越高: 该字段不同值的数量 / 表中的记录

索引优劣势:

• 优势:
  • 建立索引, 可以提高数据检索的效率, 降低数据库的IO成本;
  • 通过索引列对数据进行排序, 降低数据排序的成本, 降低了CPU的消耗
• 劣势:
  • 其实索引也是一张表, 该表保存了主键与索引字段, 并指向实体表的记录, 所以索引列也是要占用空间的
  • 索引大大提高了查询速度, 但同时会降低更新表的速度, 因为 MySQL不仅要更新数据, 还更新索引信息文件

(四) MySQL 性能分析 Explain

Explain 执行计划: explain命令查看SQL语句的执行计划, 模拟服务层的Optimizer优化器执行SQL查询语句, 从而知道MySQL是如何处理你的SQL语句的. 分析SQL查询语句或是表结构的性能瓶颈

Explain 的作用:

• 表的读取顺序
• 数据读取的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

Explain 的使用语法:

EXPLAIN SELECT columnName FROM tbl;

解析 Explain 执行计划信息:

• id: select查询的序列号, 包含一组数字, 表示查询中执行select子句或操作表的顺序, 有三种情况:

  • id 相同: 执行顺序从上到下, 如图, 表的执行顺序为 tbl_b —> tbl_a —> tbl_c

  EXPLAIN SELECT * FROM  tbl_a, tbl_b, tbl_c WHERE tbl_a.id = tbl_b.id and tbl_a.id = tbl_c.id;
  

  

  • id 不同: 发生在子查询中, id序列号会递增, id值越大执行优先级越高, 表的执行顺序为 tbl_c —> tbl_b —> tbl_a

  EXPLAIN SELECT * FROM tbl_a WHERE tbl_a.id = (
  	SELECT id FROM tbl_b WHERE id = (
  		SELECT id FROM tbl_c WHERE name = '1'
  	)
  );
  

  

  • id 相同和不相同, 同时存在: 发生存在虚表(临时表)中, derived: 衍生表(虚表), 2: 由id为2产生的衍生虚表

  EXPLAIN SELECT * FROM (
  	SELECT id FROM tbl_a WHERE name = '1'
  ) tmp, tbl_b
  WHERE tmp.id = tbl_b.id;
  

• select_type: 查询的类型
  • SIMPLE: 简单的select查询, 不包含子查询或者UNION
  • PRIMARY: 查询中若包含任何复杂的子部分, 最外层查询则标记为PRIMARY
  • SUBQUERY: 子查询
  • DERIVED: 衍生查询, 在FROM 列表中包含的子查询被标记为DERIVED, MySQL会递归执行这些子查询, 并把结果放到临时表中
  • UNION: 若第二个SELECT出现在UNION之后, 则标记为UNION
  • UNION RESULT: 从UNION表获取结果的SELECT

• table: 显示这一行的数据是关于哪张表的

• type: 连接(访问)类型, 是数据库引擎查找表的访问方式
  • system: 表只有一行记录(等于系统表), 这是const类型的特例
  • const: 表示通过索引一次就找到了, const 用于比较 primary key主键索引
  • eq_ref: 唯一性索引扫描, 对于每个索引键, 表中只有一条记录与之匹配. 常见于主键或唯一索引扫描
  • ref: 非唯一性索引扫描, 返回所有匹配某个单独值的所有行. 使用了复合索引中某个列
  • range: 只检索给定范围的行, 进行有范围的索引扫描, 如: BETWEEN AND、>、<、 IN 等
  • index: Full Index Scan, index 与 All 的区别是 index类型只遍历索引数, 全盘扫描索引, 通常比ALL快. 如: SELECT 主键 FROM tbl
  • ALL: Full Table Scan, 将遍历全表来找到匹配的行

• possible_keys: 显示可能应用在这张表中的索引, 一个或多个. 但不一定被查询实际使用

• key:实际使用的索引, NULL表示没有使用索引, 查询中若使用了覆盖索引, 则该索引仅出现在key列表中

• key_len: 表示索引中使用的字节数(索引字段的最大可能长度), 可通过该列计算查询中使用的索引的长度, 长度越短越好.

• ref: 显示索引的哪一列被使用了, 如果可能的话, 是一个常数. 哪些列或者常量被用于查找索引列上的值

• rows: 根据表统计信息及索引选用情况, 大致估算出找到所需的记录需要要读取的行数

• Extra: 包含不适合在其他列中显示但十分重要的额外信息
  • Using filesort: 文件排序, MySQL中无法利用索引完成的排序操作称为 文件排序. 需要优化
  • Using temporay: 使用了临时表保存中间结果, MySQL 在对查询结果排序时使用临时表, 常见于排序 order by 和 分组查询 group by, 需要优化
    
  • Using index: 表示相应的select操作中使用了覆盖索引, 避免了访问表的数据行. 推荐达到此效果
    
  • 其他: Using where(使用了WHERE关键字)、Using join buffer(使用了连接缓存) 等

(五) MySQL 索引优化之 WHERE

多表关联查询, 建立索引的方式:

• lEFT JOIN: 由于左连接特性, 左表一定会全部查询. LEFT JOIN 条件用于确定如何从右表匹配行, 因此在右表创建外键字段索引
• RIGHT JOIN: 同理可得, 右表一定会全部查询(建索引的意义不大), 只需要关注左表外键字段

对一张表建立复合索引: idx_col1_col2_col3, 实际建立了三个索引: col1、col1_col2 和 col1_col2_col3

explain select * from tbl_a where col1 = 1 GROUP BY col2 # type: ref, key: idx_col1_col2_col3
explain select * from tbl_a where col2 = 1 GROUP BY col1 # type: index, key: idx_col1_col2_col3
explain select * from tbl_a where col2 = 1 ORDER BY col1 # type: ALL

使用索引应遵守的原则:

• 最佳左前缀法则: 复合索引中, 查询从索引的最左前列开始并且 不跳过索引中的列(WHERE 第一个索引列必须匹配到, 中间索引列未匹配到, 那么索引只使用到部分, 且 连续使用索引列可以不按顺序匹配)
  

• 在索引列上不能做如下操作: 计算、函数、类型转换(数字 与 字符串 的相互转换)

• 存储引擎不能使用索引中范围条件右边的列: SELECT * FROM tbl WHERE col1 = 1 AND col2 > 2 AND col3 = 3, 且建立复合索引(col1_col2_col3), 此时 col3 的索引是失效的

• 尽量使用覆盖索引(只访问索引列的查询), 减少 SELECT *, 达到 Using index效果

• 在索引列中使用 != 、<> 会导致索引失效

• IS NULL、IS NOT NULL 也无法使用索引

• LIKE 以通配符开头(’%abc…’) MySQL 索引失效会变成全表扫描, 但只要不以%开头的LIKE语句(‘abc%’), 索引的访问类型type为优化成 range 且 后面索引依旧有效, 或者使用覆盖索引将type优化成index

• 字符串不加单引号索引失效, 如: SELECT * FROM name = 2000

• 少用 OR, 用OR链接会导致索引失效

(六) MySQL 索引优化之 ORDER BY

ORDER BY 子句, 尽可能在索引类上完成排序操作, 遵照索引建的最佳左前缀, 尽量达到 Using index 方式排序, 避免使用 Using filesort方式排序

• ORDER BY 语句使用索引最左前列
• WHERE 字句 与 ORDER BY 字句 条件组合满足索引最左前列

ORDER BY 排序列的顺序, 必须与复合索引建立的列一致. 否则会产生 Using filesort, 但 如果 ORDER BY 排序的列 是一个常量(即 在WHERE条件中固定了值), 无需排序, 可以忽略不计算顺序位置

ORDER BY 排序的列不在索引列上, 即: Using filesort 有两种算法:

• 双路排序: MySQL 之前是使用双路排序, 即 两次扫描磁盘(I\O)
  • 第一次: 读取指针和ORDER BY 的列, 进行排序, 存储到临时缓存buffer表中
  • 第二次: 扫描已排序的临时表, 查询对应的SELECT 字段
• 单路排序: 先查询需要的所有列, 在按照ORDER BY列 在 buffer中进行排序, 然后在输出(避免了两次扫描)

单路排序性能是优于双路排序的, 但是单路排序存在些问题: buffer缓冲区是有大小限制的, 如果查询需要的所有列的大小 大于 buffer缓冲区, 那么每次只能取出 buffer容量大小的数据排序, 存储到tmp文件, 然后再循环取出, 直至全部取出, 最后再在tmp合并排序. 导致了大量的I/O操作

解决方案:

• 设置数据库全局参数sort_buffer_size 和 max_length_for_sort_data, 增大buffer缓冲区大小
• ORDER BY 时 不要使用 SELECT *, 只查询SELECT需要的字段, 减小buffer缓冲区的大小

小表驱动大表, 即 小的数据集驱动大的数据集

(七) MySQL 索引优化之 GROUP BY

GROUP BY: 实质是先排序后分组, 遵照索引列的最佳左前缀法则, 且与 ORDER BY 优化技巧一致, 唯一不同的一点是 HAVING

WHERE 效率高于 HAVING, 能在WHERE限定的条件就不要再HAVING限定

(八) MySQL 优化之 慢查询日志

慢查询日志: MySQl的慢查询日志是MySQL提供的一种日志记录, 它用来记录在MySQL中响应时间超过(大于)阈值的语句, 具体指运行时间超过long_query_time值的SQL(默认10s), 则会被记录到慢查询日志中

默认情况下, MySQL是未开启慢查询日志, 因为慢查询日志会后多或少带来一定的性能影响. 因此尽量在调优需要的时才开启

show variables LIKE '%slow_query_log%'; #查看MySQL是否开启慢查询日志, window日志默认在 C:\ProgramData\MySQL\MySQL Server 8.0\Data 下

set global slow_query_log = 1; # 开启慢查询日志, 且只对当前数据库生效, MySQL重启失效

show variables like 'long_query_time%' # 查看SQL语句响应查询时间阈值, 默认 10s
set global long_query_time = 3; # 修改阈值参数, 需要重新连接才能看到

show global status like '%Slow_queries%' # 查询当前系统中有多少条慢查询记录

日志分析工具: mysqldumpslow, 筛选日志

(九) MySQL 优化之 Show Profile

Show Profile: 是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况, 如: 磁盘IO、CPU、内存、上下文切换、执行优化器等分别消耗的时间. 可以用于SQL调优的测量. 默认的情况下, 参数处于关闭状态, 并保存最近15次的运行结果(由全局参数profiling_history_size常量决定)

show  variables like 'profiling'; # 查看Show Profile功能是否开启
set profiling = on; # 开启Show Profile

show profiles; # 默认显示最近15条的sql执行情况

show profile 参数, ... for query Query_ID;  # 根据Query_ID 查询具体SQL的具体消耗时间
show profile cpu, block io for query 2; # 查询Query_ID为 2 的 CPU和IO 消耗的时间

常用查询参数type:

• ALL: 显示所有的开销信息
• BLOCK IO: 显示块IO相关开销
• CONTEXT SWITCHES: 上下文切换相关开销
• CPU: 显示CPU相关开销信息
• IPC: 显示发送和接收相关开销信息
• MEMORY: 显示内存相关开销信息
• PAGE FAULTS: 显示页面错误相关开销信息
• SOURCE: 显示和Source_function，Source_file，Source_line相关的开销信息
• SWAPS: 显示交换次数相关开销的信息

当status(SQL执行的声明周期)出现以下状态, 就必须优化

• converting HEAP to MyISAM: 查询结果太大, 内存都不够用, 需要使用磁盘空间
• Creating tmp table: 创建临时表
• Copying to tmp table on disk: 将内存中的临时表复制到磁盘中
• locked: 死锁