MySQL系列-MySQL体系结构

1、MySQL的体系结构

MySQL整体的逻辑结构可以分为4层,客户层、服务层、存储引擎层、数据层

客户层

• 客户层:进行相关的连接处理、权限控制、安全处理等操作

服务层

• 服务层负责与客户层进行连接处理、处理以及执行SQL语句等,主要包含连接器、查询缓存、优化器、执行器、存储引擎。触发器、视图等也在这一层

存储引擎层

• 存储引擎层负责对数据的存储和提取，常见的存储引擎有InnoDB、MyISAM、Memory等，在MySQL5.5之后，MySQL默认的存储引擎就是InnoDB,InnoDB默认使用的索引结构就是B+树,上面的服务层就是通过API接口与存储引擎层进行交互的

数据层

• 数据层系主要包括MySQL中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行pid文件和socket文件等。
  那么一条SQL语句在MySQL的整个体系结构是如何执行的呢？

2、SQL语句的执行过程

当向MySQL发送一条SQL语句的时候

1、客户层

• 首先连接器与客户端进行连接、以linux系统为例，通过在Mysql服务启动成功之后通过一下命令进行数据库的登录

[root@bp18425116f0cojd1vnz ~]# mysql -uroot -p
Enter password: 

如果密码输入错误的话就会有以下提示

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES

如果出现MySQL密码忘记的情况下，可以通过以下方法进行登录

1、查询MySQL服务是否启动，如若启动，关闭MySQL服务

[root@bp18425116f0cojd1vnz ~]# ps -ef |grep mysql
root       87531       1  0 Feb09 ?        00:00:00 /bin/sh /www/server/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --datadir=/www/server/data --pid-file=/www/server/data/bp18425116f0cojd1vnz.pid --sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
mysql      88147   87531  0 Feb09 ?        00:43:28 /www/server/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/www/server/mysql --datadir=/www/server/data --plugin-dir=/www/server/mysql/lib/plugin --user=mysql --sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION --log-error=bp18425116f0cojd1vnz.err --open-files-limit=65535 --pid-file=/www/server/data/bp18425116f0cojd1vnz.pid --socket=/tmp/mysql.sock --port=3306
root     2725702 2724782  0 14:35 pts/0    00:00:00 grep --color=auto mysql

2、关闭MySQL服务

[root@bp18425116f0cojd1vnz ~]# systemctl stop mysql
[root@bp18425116f0cojd1vnz ~]# systemctl status mysql
● mysqld.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysqld; generated)
   Active: inactive (dead) since Thu 2022-06-09 14:36:55 CST; 2s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 2725788 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)

Feb 09 14:30:10 bp18425116f0cojd1vnz systemd[1]: Starting LSB: start and stop MySQL...
Feb 09 14:30:11 bp18425116f0cojd1vnz mysqld[1174]: /etc/rc.d/init.d/mysqld: line 244: my_print_defaults: command not found
Feb 09 14:30:11 bp18425116f0cojd1vnz mysqld[1174]: /etc/rc.d/init.d/mysqld: line 265: cd: /www/server/mysql: No such file or directory
Feb 09 14:30:11 bp18425116f0cojd1vnz mysqld[1174]: Starting MySQLCouldn't find MySQL server (/www/server/mysql/bin/mysqld_safe)[FAILED]
Feb 09 14:30:11 bp18425116f0cojd1vnz systemd[1]: Started LSB: start and stop MySQL.
Jun 09 14:36:52 bp18425116f0cojd1vnz systemd[1]: Stopping LSB: start and stop MySQL...
Jun 09 14:36:55 bp18425116f0cojd1vnz mysqld[2725788]: Shutting down MySQL..[  OK  ]
Jun 09 14:36:55 bp18425116f0cojd1vnz systemd[1]: mysqld.service: Succeeded.
Jun 09 14:36:55 bp18425116f0cojd1vnz systemd[1]: Stopped LSB: start and stop MySQL.

3、修改 vim /etc/my.cnf

在/etc/my.cnf 添加一行 skip-grant-tables

[client]
#password       = your_password
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]
skip-grant-tables
port            = 3306
socket          = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 100G

4、重新启动MySQL数据库

[root@bp18425116f0cojd1vnz ~]# systemctl start mysql

5、以免密模式登录数据库

[root@bp18425116f0cojd1vnz ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.50-log Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

6、登录mysql数据库

mysql> use mysql;
Database changed

7、修改密码

mysql> update mysql.user set authentication_string=password('your_password') where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

your_password为自己想要替换的数据库密码

8、修改 /etc/my.cf

修改/etc/my.cf 文件 去除 skip-grant-tables

9、已修改之后的密码登录数据库

[root@bp18425116f0cojd1vnz ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.50-log Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

当连接器与客户端通过TCP进行三次握手连接成功之后，就会要求用户输入密码进行登录，当输入密码无误时，客户端与服务器建立连接成功之后，连接器就会去查询出改用户的权限然后存储到查询缓存中

2、查询缓存

当客户端的查询语句为select查询语句的时候，如若再查询缓存里面已经查询到了结果，就会直接把查询结果返回给客户端

3、解析器

在查询缓存并没有查询到结果之后，就会走到解析器，在解析器这儿，会做如下工作

1、词法分析

词法分析会根据客户端的SQL语句分析出各个关键词，简单地说就是把整个SQL拆分为一个个的单词，然后生茶一颗词法分析树

2、语法分析

在语法分析层面会根据上面生成的词法分析树判断SQL语句是否符合语法规则，如果不符合，就会进行相应的提示信息

mysql> select djglfdjg from user;
ERROR 1054 (42S22): Unknown column 'djglfdjg' in 'field list'

如若在解析器执行正确之后，就会去执行相应的SQL，走到执行器

4、SQL执行器

在执行器这个阶段，会进行SQL语句的执行，主要包括以下这几个部分

1、预处理阶段

• 在开始执行的时候，预处理阶段你对这个表有没有执行查询的权限，如若没有，就会返回相应的错误
• 检查查询的表或者字段是否存在，如若没有，也会返回相应的错误信息

2、优化器

在优化器阶段，优化器会对SQL的执行顺序，使用哪个索引进行优化，确定SQL的执行方案，在这里会生产explain的执行计划
比如这个语句

mysql> explain SELECT Host  FROM `user` where Host='localhost';
+----+-------------+-------+------+-------------------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys           | key     | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+-------------------------+---------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | user  | ref  | PRIMARY,index_user_Host | PRIMARY | 180     | const |    3 | Using where; Using index |
+----+-------------+-------+------+-------------------------+---------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

通过explan执行语句可以查询到，在执行语句时，有以下结论

• id=1 SELECT识别符,查询序号即为sql语句执行的顺序
• select_type=SIMPLE 表示SQL查询语句走的是单表查询
• table=user 输出的行所用的表
• type=ref 显示了连接使用了哪种类别,有无使用索引,type扫描方式由快到慢
  system > const > eq_ref > ref > range > index > ALL
  system：系统表，少量数据，往往不需要进行磁盘IO
  const：常量连接
  eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描 ref：非主键非唯一索引等值扫描
  range：范围扫描
  index：索引树扫描
  all：全表扫描
• possible_keys 表示查询语句可能会用到的索引，在这里有两个,PRIMARY表示为主键索引，index_user_Host为另一个索引
• key 表示在查询语句时实际用到的索引，在这里为PRIMARY,那为什么这里只用到了PRIMARY这个索引呢,别急，后面会说到
• key_len 表示使用的索引长度
• ref 列显示使用哪个列或常数与key一起从表中选择行
• rows 显示MySQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引
• Extra 该列包含MySQL解决查询的详细信息,
  Using index表示相应的select操作中使用了覆盖索引(covering index)，避免访问了表的数据行,using where，表明索引被用来执行索引键值的查找
  Using where表明使用了where过滤
  Using join buffer使用了连接缓存
  Using temporary 使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
  group by一定要遵循所建索引的顺序与个数
  using filesort，using temporary，using index最为常见，出现前两种表示是需要优化的地方
  通过观察上面的执行语句，在查询时，有2个索引，但是只用到了PRIMARY这个索引,并没有用到index_user_Host，查询表所建立的索引

mysql> show  index from mysql.user;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY         |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | PRIMARY         |            2 | User        | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_user_Host |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

该表有建立3个索引，查询语句SELECT Host FROM user where Host='localhost';中查询字段为Host ,Extra为Using where; Using index表明用到了覆盖索引，也就是二级索引的 B+ 树的叶子节点的数据存储的是主键值，没有必要再索引检索磁盘IO来查询数据，也就是覆盖索引优化，所以并没有通过index_user_Host这个索引去检索数据

3、执行器

在执行器执行SQL语句会对权限进行校验，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口与存储引擎层进行交互，执行SQL语句，并将结果返回个客户端。这里只是简单介绍，后面章节详细讲解。