• 欢迎访问VPS岛网站,国外VPS,国内VPS,国外服务器,国内服务器,服务器主机,测评及优惠码,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站 QQ群

MySQL的查询过程分析

mysql技术 zjdevops 174次浏览 已收录 0个评论

关系型数据库管理系统查询处理一般分为4个阶段:

见下图

MySQL的查询过程分析

怎么验证这几个阶段对应在MySQL的关系呢?

这里实验的数据库版本:5.6.16-64.2-56

OS:CentOS release 6.5

Kernel:2.6.32-431.el6.x86_64

创建测试库及表、数据:

root@localhost[(none)]:14: >CREATE DATABASE querydb /!40100 DEFAULT CHARACTER SET utf8 /;

Query OK, 1 row affected (0.00 sec)

root@localhost[(none)]:15: >use querydb;

Database changed

root@localhost[querydb]:20: >create table t(id int auto_increment ,name varchar(50),primary key(id)) engine=innodb;

Query OK, 0 rows affected (0.02 sec)

root@localhost[querydb]:21: >insert into t values(NULL,’a’);

Query OK, 1 row affected (0.00 sec)

root@localhost[querydb]:21: >insert into t values(NULL,’b’);

Query OK, 1 row affected (0.00 sec)

root@localhost[querydb]:21: >insert into t values(NULL,’c’);

Query OK, 1 row affected (0.01 sec)

打开MySQL的profile

root@localhost[querydb]:21: >set @@profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

首先我们来查询一条正常的sql语句,看MySQL内部进行哪些操作。

root@localhost[querydb]:22: >select id,name from t;

+—-+——+

| id | name |

+—-+——+

| 1 | a |

| 2 | b |

| 3 | c |

+—-+——+

3 rows in set (0.00 sec)

root@localhost[querydb]:24: >show profiles;

+———-+————+———————–+

| Query_ID | Duration | Query |

+———-+————+———————–+

| 1 | 0.00103500 | select id,name from t |

+———-+————+———————–+

1 row in set, 1 warning (0.00 sec)

root@localhost[querydb]:24: >show profile for query 1;

+———————-+———-+

| Status | Duration |

+———————-+———-+

| starting | 0.000313 |

| checking permissions | 0.000029 |

| Opening tables | 0.000073 |

| init | 0.000058 |

| System lock | 0.000066 |

| optimizing | 0.000007 |

| statistics | 0.000044 |

| preparing | 0.000025 |

| executing | 0.000002 |

| Sending data | 0.000321 |

| end | 0.000007 |

| query end | 0.000018 |

| closing tables | 0.000018 |

| freeing items | 0.000017 |

| cleaning up | 0.000038 |

+———————-+———-+

15 ows in set, 1 warning (0.00 sec)

从上面大体上可以看出,

首先检查权限,权限检查完后open table操作,然后进行对元数据进行lock操作、然后优化、预编译、最后执行,到Sending data时,这时已经推送到存储引擎层了进行拉取数据。最后释放lock、关闭表并进行清理操作。

先说说各个阶段的特征:

starting:语法分析与词法分析阶段

checking permissions:用户权限检查

Opening tables:表权限检查

init:表的列权限检查

System lock:获得表的一些lock信息

optimizing:逻辑优化(代数优化),主要RBO优化

statistics:物理优化(非代数优化),主要是CBO优化

preparing和executing:生成代码并执行

Sending data:也有可能包括执行、提取和发送数据的过程中。

一、查询分析

1.模拟sql关键字错误

root@localhost[querydb]:31: >selectt id,name from t;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘selectt id,name from t’ at line 1

root@localhost[querydb]:31: >show profiles;

+———-+————+————————+

| Query_ID | Duration | Query |

+———-+————+————————+

| 1 | 0.00103500 | select id,name from t |

| 2 | 0.00022225 | selectt id,name from t |

+———-+————+————————+

2 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:32: >show profile for query 2;

+—————+———-+

| Status | Duration |

+—————+———-+

| starting | 0.000154 |

| freeing items | 0.000026 |

| cleaning up | 0.000043 |

+—————+———-+

3 rows in set, 1 warning (0.00 sec)

再来一个

root@localhost[querydb]:45: >select id,name fr0m t;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘t’ at line 1

root@localhost[querydb]:45: >show profiles;

+———-+————+————————+

| Query_ID | Duration | Query |

+———-+————+————————+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

+———-+————+————————+

3 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:45: >show profile for query 3;

+—————+———-+

| Status | Duration |

+—————+———-+

| starting | 0.000134 |

| freeing items | 0.000018 |

| cleaning up | 0.000036 |

+—————+———-+

3 rows in set, 1 warning (0.00 sec)

通过对比发现,starting应该是进行语法分析和词法分析。

为什么要进行语法分析和词法分析?

其实跟我们平时的母语的造句一样,比如:

去 我 饭 吃 这个四个字

按照我们人正常的逻辑造句应该是:我去吃饭

但计算机就不一样了,可能会出现好多:

去我饭吃

去我吃饭

我去吃饭

我去饭吃

…….

……

计算机会根据一定的规则,就像我们的主谓宾格式造句一样,从中组合,但有一种可能,就是组合了即没有语法错误也没有词法错误但意思不一样的情况。这里假设:我要吃饭和要我吃饭,语法和词法都没有错误,但语义不同,这时计算机就要进行语义分析了。

这里面的判断规则有自动机进行判断,也叫图灵机(是伟大的计算机科学之父:图灵 发明的,图灵奖想必大家都熟悉吧^_^),不过龙书(编译原理)里会有详细介绍

二、查询检查

root@localhost[querydb]:45: >select ida,name from t; #表存在,字段不存在

ERROR 1054 (42S22): Unknown column ‘ida’ in ‘field list’

root@localhost[querydb]:19: >show profiles;

+———-+————+————————+

| Query_ID | Duration | Query |

+———-+————+————————+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

+———-+————+————————+

4 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:19: >show profile for query 4;

+———————-+———-+

| Status | Duration |

+———————-+———-+

| starting | 0.000531 |

| checking permissions | 0.000037 |

| Opening tables | 0.000133 |

| init | 0.000116 |

| end | 0.000017 |

| query end | 0.000018 |

| closing tables | 0.000027 |

| freeing items | 0.000032 |

| cleaning up | 0.000052 |

+———————-+———-+

9 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:19: >select id,name from tab; #id,name是表t的字段,而tab表不存在

ERROR 1146 (42S02): Table ‘querydb.tab’ doesn’t exist

root@localhost[querydb]:23: >show profiles;

+———-+————+————————-+

| Query_ID | Duration | Query |

+———-+————+————————-+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

+———-+————+————————-+

5 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:23: >show profile for query 5;

+———————-+———-+

| Status | Duration |

+———————-+———-+

| starting | 0.000621 |

| checking permissions | 0.000039 |

| Opening tables | 0.000367 | 感觉是不是哪里

| query end | 0.000023 | 不对劲,是不是

| closing tables | 0.000006 | 少了init阶段

| freeing items | 0.000055 |

| cleaning up | 0.000066 |

+———————-+———-+

7 rows in set, 1 warning (0.00 sec)

所以从上面可以观察到,这里checking permissions你可以进行测试。这里我不在进行测试。【赋予用户test什么权限都没有:grant usage on . to test@’%’ identified by ‘test’;和赋予test2用户只有查询t表中id列的权限:grant select(id) on querydb.t to test2@’%’ identified by ‘test2’;】,这里测试略

checking permissions: 对MySQL的连接用户进行权限检查。

Opening tables: 对表权限进行检查。

init阶段:对表中的列进行权限检查。

三、检查优化

实验的sql语句:

select 1;

select id,name from t ;

select id,name from t where 0=1;

三个语句查看其变化。

root@localhost[querydb]:36: >select 1;

+—+

| 1 |

+—+

| 1 |

+—+

1 row in set (0.00 sec)

root@localhost[querydb]:36: >show profiles;

+———-+————+—————————-+

| Query_ID | Duration | Query |

+———-+————+—————————-+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

| 6 | 0.00115800 | select id,name,abc from t |

| 7 | 0.00029450 | select 1 |

+———-+————+—————————-+

7 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:36: >show profile for query 7;

+———————-+———-+

| Status | Duration |

+———————-+———-+

| starting | 0.000196 |

| checking permissions | 0.000006 |

| Opening tables | 0.000007 |

| init | 0.000016 |

| optimizing | 0.000010 |

| executing | 0.000013 |

| end | 0.000008 |

| query end | 0.000006 |

| closing tables | 0.000001 |

| freeing items | 0.000014 |

| cleaning up | 0.000019 |

+———————-+———-+

11 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:36: >select id,name from t ;

+—-+——+

| id | name |

+—-+——+

| 1 | a |

| 2 | b |

| 3 | c |

+—-+——+

3 rows in set (0.00 sec)

root@localhost[querydb]:38: >show profiles;

+———-+————+—————————-+

| Query_ID | Duration | Query |

+———-+————+—————————-+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

| 6 | 0.00115800 | select id,name,abc from t |

| 7 | 0.00029450 | select 1 |

| 8 | 0.00074025 | select id,name from t |

+———-+————+—————————-+

8 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:38: >show profile for query 8;

+———————-+———-+

| Status | Duration |

+———————-+———-+

| starting | 0.000274 |

| checking permissions | 0.000024 |

| Opening tables | 0.000059 |

| init | 0.000036 |

| System lock | 0.000029 |

| optimizing | 0.000008 |

| statistics | 0.000031 |

| preparing | 0.000021 |

| executing | 0.000002 |

| Sending data | 0.000172 |

| end | 0.000011 |

| query end | 0.000012 |

| closing tables | 0.000013 |

| freeing items | 0.000018 |

| cleaning up | 0.000031 |

+———————-+———-+

15 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:38: >select id,name from t where 0=1;

Empty set (0.00 sec)

root@localhost[querydb]:41: >show profiles;

+———-+————+———————————+

| Query_ID | Duration | Query |

+———-+————+———————————+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

| 6 | 0.00115800 | select id,name,abc from t |

| 7 | 0.00029450 | select 1 |

| 8 | 0.00074025 | select id,name from t |

| 9 | 0.00058500 | select id,name from t where 0=1 |

+———-+————+———————————+

9 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:41: >show profile for query 9;

+———————-+———-+

| Status | Duration |

+———————-+———-+

| starting | 0.000279 |

| checking permissions | 0.000015 |

| Opening tables | 0.000046 |

| init | 0.000057 |

| System lock | 0.000019 |

| optimizing | 0.000025 |

| executing | 0.000014 |

| end | 0.000005 |

| query end | 0.000008 |

| closing tables | 0.000010 |

| freeing items | 0.000020 |

| cleaning up | 0.000089 |

+———————-+———-+

12 rows in set, 1 warning (0.00 sec)

为了便于观察,我这里查看其语句的执行计划然后看执行的语句。

下面输出的有点不好看,可以看下面的截图。

root@localhost[querydb]:10: >explain extended select id,name from t where 0=1;

+—-+————-+——-+——+—————+——+———+——+——+———-+——————+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+—-+————-+——-+——+—————+——+———+——+——+———-+——————+

| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |

+—-+————-+——-+——+—————+——+———+——+——+———-+——————+

1 row in set, 1 warning (0.00 sec)

root@localhost[querydb]:11: >show warnings;

+——-+——+————————————————————————————————————+

| Level | Code | Message |

+——-+——+————————————————————————————————————+

| Note | 1003 | / select#1 / select querydb.t.id AS id,querydb.t.name AS name from querydb.t where 0 |

+——-+——+————————————————————————————————————+

1 row in set (0.00 sec)

MySQL的查询过程分析

通过三个sql语句的执行情况发现。

optimizing阶段:为代数优化阶段

statistics和preparing阶段:为物理优化阶段,从字面信息上来看,可以理解是收集统计信息,生成执行计划,选择最优的存取路径进行执行。

同时有没有发现Sending data,说明这时已经到存储引擎层了去拉取数据,对比上面select 1和where 0=1 都没有Sending data,更可以很好理解,但这里有个问题?MySQL层和存储引擎层是通过接口实现的,当查询的结果返回时怎么知道返回哪个线程呢?其实在内部,每个查询都会分配一个查询线程ID的,根据线程ID编号来的。

一、 查询执行

root@localhost[querydb]:41: >select id,name from t;

+—-+——+

| id | name |

+—-+——+

| 1 | a |

| 2 | b |

| 3 | c |

+—-+——+

3 rows in set (0.00 sec)

root@localhost[querydb]:15: >show profiles;

+———-+————+———————————+

| Query_ID | Duration | Query |

+———-+————+———————————+

| 1 | 0.00034325 | select id,name from t |

| 2 | 0.00006925 | selectt id,name from t |

| 3 | 0.00018800 | select id,name fr0m t |

| 4 | 0.00096275 | select ida,name from t |

| 5 | 0.00117675 | select id,name from tab |

| 6 | 0.00115800 | select id,name,abc from t |

| 7 | 0.00029450 | select 1 |

| 8 | 0.00074025 | select id,name from t |

| 9 | 0.00058500 | select id,name from t where 0=1 |

| 10 | 0.00194675 | select id,name from t |

+———-+————+———————————+

10 rows in set, 1 warning (0.00 sec)

root@localhost[querydb]:15: >show profile for query 10;

+———————-+———-+

| Status | Duration |

+———————-+———-+

| starting | 0.000763 |

| checking permissions | 0.000050 |

| Opening tables | 0.000184 |

| init | 0.000178 |

| System lock | 0.000076 |

| optimizing | 0.000025 |

| statistics | 0.000095 |

| preparing | 0.000055 |

| executing | 0.000002 |

| Sending data | 0.000335 |

| end | 0.000018 |

| query end | 0.000028 |

| closing tables | 0.000023 |

| freeing items | 0.000041 |

| cleaning up | 0.000076 |

+———————-+———-+

15 rows in set, 1 warning (0.00 sec)

executing:为执行阶段了。

这里还有一个System lock阶段:其实当执行DDL、DML操作时,MySQL会在内部对表的元数据进行加锁还有其他的锁,比如S锁,X锁,IX锁,IS锁等,用于解决或者保证DDL操作与DML操作之间的一致性。【可以参看<<InnoDB存储引擎技术内幕>>或事务处理与实现】

参考书籍:

【编译原理】 [美] Alfred V.Aho,[美] Monica S.Lam,[美] Ravi Sethi 等 著;赵建华,郑滔 等 译

数据库系统概论 】 王珊,萨师煊 著

【数据库查询优化器的艺术:原理解析与SQL性能优化】 李海翔 著

【InnoDB存储引擎技术内幕】 姜承尧 著

【事务处理与实现】


VPS岛 的文章和资源来自互联网,仅作为参考资料,如果有侵犯版权的资源请尽快联系站长,我们会在24h内删除有争议的资源。丨 转载请注明MySQL的查询过程分析
喜欢 (1)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址