关联查询优化

现有如下两张表

下面开始 explain 分析

可通过EXPLANIN查看SQL语句的执行计划，EXPLANIN分析的第一行的表即是驱动表

left join

1
EXPLAIN SELECT SQL_NO_CACHE * FROM class LEFT JOIN book ON class.card = book.card;

结论：type 类型为 All，并且 class 为驱动表；book 为被驱动表，并且 Extra 里面有 where 字段，where 在哪里，索引就在哪里加。

进行索引优化的时候：被驱动表添加索引

添加索引优化

1
2
ALTER TABLE book ADD INDEX Y (card);
ALTER TABLE class ADD INDEX X (card);

inner join

先删除上面添加的两条索引

换成 inner join（MySQL 自动选择驱动表）

1
EXPLAIN  SELECT SQL_NO_CACHE * FROM  class INNER JOIN book ON class.card=book.card;

添加索引优化

1
2
ALTER  TABLE book ADD INDEX Y (card);
ALTER  TABLE class ADD INDEX X (card);

总结

  • 保证被驱动表的join字段被索引
  • left join 时，选择小表作为驱动表，大表作为被驱动表
  • inner join 时，mysql 会自动将小结果集的表选为驱动表。
  • 子查询尽量不要放在被驱动表，衍生表建不了索引
  • 能够直接多表关联的尽量直接关联，不用子查询

子查询优化

不推荐使用子查询，尽量将子查询转换为关联查询

1
2
3
4
5
6
7
8
#不推荐使用自查询+

SELECT
  *
FROM
  t_emp
WHERE
  t_emp.id NOT IN ( SELECT t_dept.ceo FROM t_dept WHERE t_dept.ceo IS NOT NULL );

执行子查询时，MySQL 需要创建临时表，查询完毕后再删除这些临时表（表现为子查询会导致不同的 id 出现）所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程

1
2
3
4
5
6
7
8
9
#推荐尽量使用关联查询

SELECT
  t_emp.*
FROM
  t_emp
  LEFT JOIN t_dept ON t_emp.id = t_dept.ceo
WHERE
  t_dept.id IS NULL;

排序优化

目前emp表有一条索引

使用 order by 进行排序

order by 时不限制（Limit）索引失效

order by 时顺序错误，索引失效

和上面的 SQL 语句一样，但是打乱排序顺序时

order by 时规则不一致，可能导致索引失效

第一条 SQL 语句全部使用降序，索引生效

第二条 SQL 语句 age 使用升序，其它降序，索引失效

平时在写 SQL 语句的话，ORDER BY 子句若未显式指定升序(ASC)或降序(DESC),那么就认按默认升序排序

双路排序和单路排序

排序的字段若如果不在索引列上，则 filesort 会有两种算法双路排序和单路排序

双路排序

MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出, 从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。取一批数据，要对磁盘进行两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

单路排序

从磁盘读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

单路排序存在的问题

在 sort_buffer 中，单路比多路要多占用很多空间，因为单路是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取 sort_buffer 容量大小，再排……从而多次 I/O。单路本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。优化策略如下:

  • 增大 sort_buffer_size 参数的设置

  • 增大 max_length_for_sort_data 参数的设置

  • 减少 select 后面的查询的字段。

Order By 效率优化

  1. Order by 时 select * 是一个大忌

    • 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用单路排序，否则用多路排序
    • 两种算法的数据都有可能超出 sort_buffer 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size

  2. 尝试提高 sort_buffer_size

    • 不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M 之间调整。 MySQL5.7，InnoDB 存储引擎默认值是 1048576 字节，1MB。

  3. 尝试提高 max_length_for_sort_data

    • 提高这个参数， 会增加用改进算法的概率。
    • 但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。如果需要返回的列的总长度大于 max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192 字节之间调整

GROUP BY 优化

group by 使用索引的原则几乎跟 order by 一致 ，唯一区别：

  • group by 先排序再分组，遵照索引建的最佳左前缀法则

  • 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置

  • where 高于 having, 能写在 where 限定的条件就不要写在 having 中了

覆盖索引

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引

理解方式二：非聚集复合索引的一种形式，它包括在查询里的 Select、Join 和 Where 子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段，也即，索引包含了查询正在查找的数据）。

View 视图

视图是将一段查询 sql 封装为一个虚拟的表。 这个虚拟表只保存了 sql 逻辑，不会保存任何查询结果。

主要作用:

  • 封装复杂 sql 语句，提高复用性
  • 逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活

常用场景:

  • 共用查询结果
  • 报表

语法:

创建

1
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name  WHERE condition

使用

1
2
3
4
5
#查询
select * from view_name
#更新
CREATE OR REPLACE VIEW view_name
AS SELECT column_name(s) FROM table_name WHERE condition