SQL优化
一条SQL经过优化器的解析生成执行计划,然后执行器根据表的存储引擎定义去调用接口读取数据,最后返回满足条件的查询结果。执行计划就是一条SQL在数据库中的执行过程或者访问路径的描述,执行计划就好比日常生活中你要从一个目的地去另一个目的地选择的交通工具,选择不同的交通工具,到达目的地的时间是不同的。
不同的执行计划,虽然最终的返回查询结果是相同的,但是由于执行过程的不同,性能有很大的差异。通常情况下, 影响执行计划的常见因素有统计信息、是否使用临时表、是否排序、是否回表、分页查询的偏移量、优化器参数等,本文会介绍以下几个方面:
1)SQL的执行过程
2)StoneDB 执行计划的名词解释
3)StoneDB 常见的执行计划
4)常见的优化思想
5)表连接
SQL 的执行过程
1)客户端通过网络向服务端发送SQL语句,连接器负责与客户端建立连接,检查用户名密码等是否正确,从权限表里检查用户的权限
2)连接建立完成后,到查询缓存检查之前是否执行过的语句及结果,如果没有,继续下一步
3)分析器对查询语句进行词法分析(表、列是否存在)和语法分析(SQL语法是否正确)
4)优化器根据统计信息生成执行计划,并且选择代价最小的执行计划
5)执行器先判断对表是否有相关权限,然后根据表的引擎定义去使用这个引擎提供的接口取需要的行,如果需要的行所在的数据页在内存中,则直接返回给执行器,否则需要从磁盘读入内存,再返回给执行器
StoneDB 执行计划的名词解释
id:代表执行操作顺序,分别有三种不同的执行结果
1)id 值相同时,执行顺序由上而下
2)id 值不同时,id 值越大,越优先执行
3)id 值既有相同,又有不同时,id 值越大,越优先执行,id 值相同的,执行顺序由上而下
select_type:查询类型,主要用于区别普通查询、关联查询、子查询等
1)SIMPLE:单表查询
2)PRIMARY:主查询,含有子查询时,最外层的查询被标记为 PRIMARY
3)SUBQUERY:子查询,通常出现在 select 子句或 where 子句后面的子查询
4)DEPENDENT SUBQUERY:子查询的执行依赖于于外层查询,外层查询有多少行结果集,子查询就会被执行多少次
5)DERIVED:派生查询,通常出现在 from 子句后面的子查询
6)UNION:union 后面的查询语句
7)UNION RESULT:union 的结果集
table:当前步骤需要访问的表
partitions:匹配的分区
type:连接类型
1)eq_ref:主键或唯一索引扫描,只返回一条记录;或者在多表连接中,连接字段是主键或唯一索引,返回一行或多行记录,且每行记录是唯一的
2)ref:非唯一索引扫描,常用于非唯一索引等值查询,返回一行或多行记录
3)range:索引范围扫描,常用于<
,<=
,>
,>=
,between
and
等操作
4)index_merge:索引合并,既可以是一张表的两个索引字段使用 or(Using union),又可以是一张表的两个索引字段使用 and(Using intersect)
5)index_subquery:外层查询关联子查询,子查询的连接字段有索引
6)all:全表扫描
possible_keys:可能选择的索引
key:实际选择的索引
key_len:使用的索引长度,用于判断组合索引是否被完全使用
注:不同字符集,各数据类型的 key_len 的长度计算公式是不同的
ref:指明当前表所参照的字段,如果是常量,则为 const,如果是连接字段,则是驱动表的连接字段
rows:执行过程中需要扫描的行数,该值越大,意味着需要扫描的行数越多,相应的耗时越 长,是个预估值,由于 StoneDB 是列式存储,且数据是被高度压缩的,预估值和实际值会相差很大
filtered:表示存储引擎层返回的数据在Server层过滤后,剩下多少满足查询的记录数量的比例,5.7之前使用 explain extended 时会出现这个列,5.7 之后默认就有这个字段
extra:执行情况的说明和描述
1)Using where with pushed condition:表示在 Server 层对存储引擎层返回的数据做的过滤(和是否使用索引无关)
2)Using filesort:表示需要排序
3)Using temporary:表示需要创建一个临时表来存储结果集,例如:使用 union、distinct、group by、子查询、排序没有走索引等
4)Using union:表示根据至少两个索引得到的结果集,通常是至少两个索引字段使用 or
5)Using join buffer (Block Nested Loop):使用 Block Nested-Loop Join 算法,说明被驱动表的连接字段没有索引
StoneDB 常见的执行计划
索引扫描的执行计划
执行计划中,索引扫描的 type 有 eq_ref、ref、range、index_merge、index_subquery。
> explain select * from t_atomstore where id=1;
+----+-------------+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_atomstore | NULL | eq_ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
> explain select first_name from t_atomstore where first_name='zhou';
+----+-------------+-------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_atomstore | NULL | ref | idx_firstname | idx_firstname | 32 | const | 10 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
注:执行计划中,即使只查询索引列,不发生回表,Extra 也不会显示 "Using index",而是显示 "NULL"。
> explain select * from t_atomstore where first_name in('zhou','liu');
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SIMPLE | t_atomstore | NULL | range | idx_firstname | idx_firstname | 32 | NULL | 20 | 100.00 | Using where with pushed condition (`test`.`t_atomstore`.`first_name` in ('周','刘'))(t0) Pckrows: 2, susp. 2 (0 empty 0 full). Conditions: 1 |
+----+-------------+-------------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+