MySQL（一）基础架构，一条sql怎么执行的

小灰灰 2023-01-15 13:22 139阅读 0赞

## 环境描述 ##

*  mysql：8.0.16

## 一条SQL语句如何执行 ##

mysql> select * from T where ID=10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzc2NzQw_size_16_color_FFFFFF_t_70_pic_center]

以上就是mysql 一条查询语句大概的执行流程图。

## 连接器 ##

*  正常的使用`mysql -u root -p`的命令，这个时候不建议直接写上密码，比如`mysql -uroot -proot` 这样会暴露自己的密码。尤其是生产环境中。
    
     *  密码错误：ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
     *  密码正确：会在权限表中读取你的权限，注：在连接过程中，如果在其他地方修改权限是不会影响到当前连接的，需要重新进行连接。
        
        ## 例子： ##
        
            conncection:root
            CREATE USER 'user'@'localhost' IDENTIFIED BY 'user';  # 创建一个user用户，密码是user
            CREATE TABLE user(
            	user_id INT PRIMARY KEY auto_increment,
            	name VARCHAR(10)
            )
            GRANT SELECT ON test.`user` TO 'user'@'localhost';
            -----------------------------------------------------------------------------
            conncection:user
            mysql -uuser -p
            mysql> show databases;
            +--------------------+
            | Database           |
            +--------------------+
            | information_schema |
            | test               |
            +--------------------+
            2 rows in set (0.00 sec)
            
            mysql> use test;
            Reading table information for completion of table and column names
            You can turn off this feature to get a quicker startup with -A
            
            Database changed
            mysql> select * from user;
            +---------+------+
            | user_id | name |
            +---------+------+
            |       1 | a    |
            +---------+------+
            1 row in set (0.00 sec)
            
            mysql> update user set name='b' where user_id = 1;
            ERROR 1142 (42000): UPDATE command denied to user 'user'@'localhost' for table 'user'
            ####这个时候是没有update权限的，我们在connection：root中添加权限（此时connection：user不关闭）
            -----------------------------------------------------------------------------
            
            conncection:root
            GRANT UPDATE ON test.`user` TO 'user'@'localhost';
            -----------------------------------------------------------------------------
            
            conncection:user
            mysql> update user set name='b' where user_id = 1;
            Query OK, 1 row affected (0.00 sec)
            Rows matched: 1  Changed: 1  Warnings: 0
            ####显然上面说的修改权限之后，需要重新开始连接才生效在8.0的版本中是不正确的。
 *  > 连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。文本中这个图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。
    
    Root:  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzc2NzQw_size_16_color_FFFFFF_t_70_pic_center 1]
    
    User:  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzc2NzQw_size_16_color_FFFFFF_t_70_pic_center 2]
    
    很明显权限不一样，processlist下来的结果似乎也不一样啊。
    
    > 客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait\_timeout 控制的，默认值是 8 小时。
    
    我们查看一下看看是不是：
    
        SHOW VARIABLES LIKE '%wait_timeout%';
        mysql> SHOW VARIABLES LIKE '%wait_timeout%';
        +--------------------------+----------+
        | Variable_name            | Value    |
        +--------------------------+----------+
        | innodb_lock_wait_timeout | 50       |
        | lock_wait_timeout        | 31536000 |
        | mysqlx_wait_timeout      | 28800    |
        | wait_timeout             | 28800    |   <---就是8小时
        +--------------------------+----------+
        4 rows in set (0.00 sec)

## query cache 查询缓存 ##

关于查询缓存于8.0去除了，[mysql server team][]中有如下解释：以下是翻译：

> mysql查询缓存是查询结果的缓存，它把即将到来的且以sel开头的查询和hash table进行比较，如何和之前执行的query查询结果一样，就算命中缓存，这也有一些限制。
> 
>  *  查询必须是每个字节都匹配（查询缓存避免了分析）
>  *  一些具有非特定的操作将导致查询不被缓存(including temporary tables, user variables, `RAND()`, `NOW()` and `UDFs`.)
>  *  查询缓存是不会服务过时的结果的，任何对基础表的修改都会引起缓存失效。
>  *  如果缓存应用于innodb会有很多限制。(to respect MVCC; as you have a transaction open, the ‘cache’ might not represent the data in your expected view.)
> 
> *The ideal scenario for the query cache tends to be largely read-only, where there are a number of very expensive queries which examine millions of rows only to return a few. A hypothetical example might be a complex query to build a list of values for a drop-down list that always appears on a webpage form. In a situation like this, the query cache can mask performance problems caused by missing indexes, which makes it helpful for novice users.* 意思就是说设计之初是用来查询哪些read-only的场景。
> 
> 下面的意思就是说在client端做缓存会更好。

## 分析器 ##

>  *  如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么， 因此需要对 SQL 语句做解析。
>  *  分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句， MySQL 需要识别出里面的字符串分别是什么，代表什么。
>  *  MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符 串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
>  *  做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法 规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。
>  *  如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比 如下面这个语句 select 少打了开头的字母“s”。

理论意思，不用解释。

## 优化器 ##

一言以蔽之优化语句。这边是简单论述。

## 执行器 ##

> 开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返 回没有权限的错误。
> 
> 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这  
> 个引擎提供的接口。
> 
> 比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的:
> 
> 1.  调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如 果是则将这行存在结果集中;
> 2.  调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
> 3.  执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

## 总结 ##

MySQL实战45讲中的第一讲，基础架构，部分，大概就是实验了一下conncetion部分，然后整体把握了下一条select 语句如何执行，里面内容不错，词法分析，语法分析等新概念。然后看了下，为什么查询缓存被删除弊端在哪，做个了结。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzc2NzQw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221022/45a083dcd1844836abd69107134acd38.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzc2NzQw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221022/4f3dfd5f84d545aea9096448715cd29c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMzc2NzQw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221022/95538f46ce4a45abab535008a77906ff.png
[mysql server team]: https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/