mysql查询过程

客户端发送一条sql给mysql服务器, 经历了连接器、查询缓存、解析器、预处理器、执行器,最后拿到结果返回给客户端。

image

下面是具体内容:(以下演示基于MySQL5.7.24)

1. 连接管理

在发送查询指令给mysql服务器之前,首先要连接mysql服务器,连接命令通常为:

mysql -h$ip -P$port -u$user -p

连接器负责跟客户端建立连接、获取用户权限、维持和管理客户端连接连接。

在使用以上连接命令时,需要输入账号密码认证,如果认证失败,会得到以下结果:

[root@localhost ~]# mysql -uroot -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

如果通过认证, 就会成功建立连接, 这时连接器会到权限表中查出用户拥有的权限, 接下来这个连接里面的权限判断都基于此, 所以在整个连接过程中, 即使管理员修改了此用户的权限, 也不会受到影响。 管理员修改完该用户的权限之后, 只能在新建的连接时才会生效。

连接完成之后, 如果没有后续动作, 这个连接会处于空闲状态:

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  4 | root | localhost | NULL | Query   |    0 | starting | show processlist |
|  5 | root | localhost | NULL | Sleep   |    5 |          | NULL             |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

上面id为4的process,是当前连接,执行了“ show processlist”指令, 另外还有一个id为5的连接, 是我在另一个会话中建立的, 没有任何后续动作, 所以command是Sleep,处于空闲状态。

客户端如果太久没有后续动作, 连接器会自动断开。 这个时间默认是8小时, 由参数interactive_timeout和wait_timeout控制。

mysql> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

其中 interactive_timeout : 服务器关闭交互式连接前等待活动的秒数
;而 wait_timeout: 服务器关闭非交互连接之前等待活动的秒数。

比如修改 interactive_timeout 参数:

mysql> set global interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 10    |
+---------------------+-------+
1 row in set (0.01 sec)

mysql> 

然后重新在本机窗口打开一个会话等待10秒:

mysql> select date_format(now(),'%H:%i:%s');  
+-------------------------------+
| date_format(now(),'%H:%i:%s') |
+-------------------------------+
| 11:26:43                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%H:%i:%s');
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    9
Current database: *** NONE ***
+-------------------------------+
| date_format(now(),'%H:%i:%s') |
+-------------------------------+
| 11:26:55                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> 

如果是在项目中进行程序调用,控制此时长的就是 wait_timeout 。

比如修改此参数:

mysql> set global wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 10    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> 

通过本机客户端连接10s是不会断开重连的, 因为此时生效的是 interactive_timeout ,时长为 28800:

mysql> select date_format(now(),'%H:%i:%s');
+-------------------------------+
| date_format(now(),'%H:%i:%s') |
+-------------------------------+
| 11:34:41                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> select date_format(now(),'%H:%i:%s');
+-------------------------------+
| date_format(now(),'%H:%i:%s') |
+-------------------------------+
| 11:34:53                      |
+-------------------------------+
1 row in set (0.00 sec)

mysql> 

然后通过php程序调用:

$sql = 'select date_format(now(),\'%H:%i:%s\');';
$ret = db::connect('test')->query($sql);
print_r($ret);
sleep(11);

$ret = db::connect('test')->query($sql);
print_r($ret);

----
输出结果为:
/usr/bin/php /Users/yihuaiyuan/www/company/ogenes/app/cron/Test.php
Array
(
    [0] => Array
        (
            [date_format(now(),'%H:%i:%s')] => 11:48:54
        )

)
PHP Warning:  mysqli::ping(): MySQL server has gone away in /Users/yihuaiyuan/www/company/kdhelp/artisan/db/mysqlDriver.php on line 330
PHP Stack trace:
PHP   1. {main}() /Users/yihuaiyuan/www/company/ogenes/app/cron/Test.php:0
PHP   2. artisan\db->query() /Users/yihuaiyuan/www/company/ogenes/app/cron/Test.php:18
PHP   3. artisan\db->getResult() /Users/yihuaiyuan/www/company/kdhelp/artisan/db.php:812
PHP   4. artisan\db\mysqlDriver->query() /Users/yihuaiyuan/www/company/kdhelp/artisan/db.php:959
PHP   5. artisan\db\mysqlDriver->ping() /Users/yihuaiyuan/www/company/kdhelp/artisan/db/mysqlDriver.php:219
PHP   6. mysqli->ping() /Users/yihuaiyuan/www/company/kdhelp/artisan/db/mysqlDriver.php:330
Array
(
    [0] => Array
        (
            [date_format(now(),'%H:%i:%s')] => 11:49:05
        )

)

Process finished with exit code 0

2. 查询缓存

客户端与服务端成功建立连接之后,会开始执行查询语句, 对于select语句,如果系统缓存功能开启的话, mysql会先去查询缓存, 如果命中缓存, 会从缓存中拿到结果直接返回给客户端。

缓存中的数据是在select查询完成之后, mysql将select语句做hash运算得到一个key,查询结果为value存储在内存中的。

查看一条sql是否命中缓存,可以通过"Qcache_hits"查看:


mysql> show status like "Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

但是通常情况下, 因为mysql命中缓存的条件必须是sql一模一样,而且任何对数据表的修改插入删除操作都会导致缓存失效,反而会消耗大量的服务器内存, 所以建议关闭查询缓存。在当前版本查询缓存默认是关闭的,所以都不会命中缓存:

mysql> show global variables like "query_cache_type";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+
1 row in set (0.00 sec)
mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> use test
Database changed
mysql> select * from t1;
+------+------+------+------+---------------------+
| id   | name | sex  | age  | create_at           |
+------+------+------+------+---------------------+
|    1 | John |    1 |   14 | 2018-11-28 11:11:11 |
|    2 | Tina |    0 |   14 | 2018-11-28 12:12:12 |
+------+------+------+------+---------------------+
2 rows in set (0.01 sec)

mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+------+------+------+---------------------+
| id   | name | sex  | age  | create_at           |
+------+------+------+------+---------------------+
|    1 | John |    1 |   14 | 2018-11-28 11:11:11 |
|    2 | Tina |    0 |   14 | 2018-11-28 12:12:12 |
+------+------+------+------+---------------------+
2 rows in set (0.00 sec)

mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

如果要开启查询缓存, 不能在mysql服务开启时直接去修改全局变量, 需要修改配置文件,重启服务:

mysql> set global query_cache_type=1;
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
mysql> exit
Bye
[root@localhost ~]# vim /etc/my.cnf 
…………
[mysqld]
query_cache_type = 1
…………
[root@localhost ~]# mysqladmin shutdown -uroot -p
Enter password: 
2018-11-29T06:02:19.279010Z mysqld_safe mysqld from pid file /usr/local/mysql/log/mysql.pid ended
[1]+  Done                    mysqld_safe --user=mysql
[root@localhost ~]# mysqld_safe --user=mysql &   
[1] 3258
[root@localhost ~]# 2018-11-29T06:02:26.002148Z mysqld_safe Logging to '/usr/local/mysql/log/error.log'.
2018-11-29T06:02:26.038052Z mysqld_safe Starting mysqld daemon with databases from /data/mysql

重新连接mysql服务, 缓存已开启:


mysql> show global variables like "query_cache_type";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.01 sec)

测试查询命中缓存:

mysql> select * from t1;
+------+------+------+------+---------------------+
| id   | name | sex  | age  | create_at           |
+------+------+------+------+---------------------+
|    1 | John |    1 |   14 | 2018-11-28 11:11:11 |
+------+------+------+------+---------------------+
1 row in set (0.00 sec)

mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+------+------+------+---------------------+
| id   | name | sex  | age  | create_at           |
+------+------+------+------+---------------------+
|    1 | John |    1 |   14 | 2018-11-28 11:11:11 |
+------+------+------+------+---------------------+
1 row in set (0.00 sec)

mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 1     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+------+------+------+---------------------+
| id   | name | sex  | age  | create_at           |
+------+------+------+------+---------------------+
|    1 | John |    1 |   14 | 2018-11-28 11:11:11 |
+------+------+------+------+---------------------+
1 row in set (0.00 sec)

mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 2     |
+---------------+-------+
1 row in set (0.00 sec)

如果此时表t1有任何该变, 缓存将会失效:

mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> insert into t1 value(2,'Tina',0,14,'2018-11-28 12:12:12');      
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+------+------+------+---------------------+
| id   | name | sex  | age  | create_at           |
+------+------+------+------+---------------------+
|    1 | John |    1 |   14 | 2018-11-28 11:11:11 |
|    2 | Tina |    0 |   14 | 2018-11-28 12:12:12 |
+------+------+------+------+---------------------+
2 rows in set (0.00 sec)

mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

所以通常不建议开启查询缓存,保持默认关闭状态, mysql查询的执行过程会跳过不经过此步。
另外sql_cache在mysql8中已经废除,也就是说以后mysql没有查询缓存这一步了。

如果在历史版本中,可以将query_cache_type设置成DEMAND, 这样可以通过SQL_CACHE显示的指定是否使用缓存,但是在当前版本上,同样不会用到缓存, 还会得到一个sql_cache将会被废弃的警告信息:

mysql> show global variables like "query_cache_type";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| query_cache_type | DEMAND |
+------------------+--------+
1 row in set (0.00 sec)

mysql> show status like "%Qcache_hits";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select SQL_CACHE * from  t1;
+------+------+------+------+---------------------+
| id   | name | sex  | age  | create_at           |
+------+------+------+------+---------------------+
|    1 | John |    1 |   14 | 2018-11-28 11:11:11 |
|    2 | Tina |    0 |   14 | 2018-11-28 12:12:12 |
+------+------+------+------+---------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------+
| Level   | Code | Message                                                            |
+---------+------+--------------------------------------------------------------------+
| Warning | 1681 | 'SQL_CACHE' is deprecated and will be removed in a future release. |
+---------+------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

3. 解析器与预处理

对于非select语句和没有命中缓存的select语句, mysql会对对应的sql语句进行词法分析和语法分析, 将对应的sql语句解析为解析树。

解析器主要验证sql语句中是否存在错误的错误关键字, 是否存在语法错误,比如语法顺序等。
比如以下的错误sql, 会在此得到报错:

mysql> select * from  t1 order by id where id =1;
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 'where id =1' at line 1

mysql> select * fro  t1 order by id; 
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 'fro  t1 order by id' at line 1

具体提示信息会返回错误的位置, 通常这种问题在开发过程总就可以发现并避免。

对于没有语法错误的sql,在解析完成之后, 预处理器会对解析树作进一步检查, 主要检查数据表和数据列是否存在, 还会解析字段别名等, 看是否存在歧义。 比如以下错误:

mysql> select id,ii from  t1 order by id;  
ERROR 1054 (42S22): Unknown column 'ii' in 'field list'

优化器

经过分析器和预处理器之后, mysql并不是直接执行查询计划的, 这是要先经过查询优化器的处理, 查询优化器会将解析树转化成真正的执行计划。

通常一条查询可以有多种执行方法,最后都是返回相同结果。查询优化器用就是找到这其中最好的执行计划,重写原sql。

在mysql中可以通过“explain extended和show warnings”命令来查看优化器重写之后的sql。

比如:

mysql> explain extended select * from t1 where 1=1 and id=2 order by age desc limit 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                        |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                                                                                                                                              |
| Note    | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`name` AS `name`,`test`.`t1`.`sex` AS `sex`,`test`.`t1`.`age` AS `age`,`test`.`t1`.`create_at` AS `create_at` from `test`.`t1` where (`test`.`t1`.`id` = 2) order by `test`.`t1`.`age` desc limit 1 |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

查询执行引擎

通过了之前的解析器、预处理器以及查询优化器, mysql得到一个具体的执行计划, 然后就会交给执行引擎来开始执行语句, 查询执行引擎会根据表的引擎调用对应的存储引擎API, 得到查询结果。

得到查询结果后, 如果系统缓存开启, 会将查询结果存入缓存, 然后返回给连接服务, 通过连接服务返回给客户端, 如果系统缓存没有开启, 则直接返回查询结果。

参考资料:

  1. MySQL实战四十五讲:一条SQL查询语句是如何执行的?
  2. MySQL :: MySQL 8.0 Reference Manual :: B.6.2.8 MySQL server has gone away
  3. MySQL :: MySQL 5.6 Reference Manual :: 8.10.3.3 Query Cache Configuration
  4. MySQL Bugs: #69396: Can't set query_cache_type to 0 when it is already 0
  5. MySQL内核源码解读-SQL解析之解析器浅析-王伟-51CTO博客
  6. MySQL探秘(二):SQL语句执行过程详解 - 掘金

标签: none

添加新评论