【运维必备技术点】MySQL系列-MySQL体系结构-新盟教育-思科华为网络工程师认证-HCIE/CCIE|HCIP/CCNP|HCIA/CCNA线上培训机构

新闻资讯资讯详情

【运维必备技术点】MySQL系列-MySQL体系结构

发布作者：新盟教育发布日期：2022-10-26 浏览人数：3516人

运维必备技术点

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 -pEnter password:

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

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

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

① 查询MySQL服务是否启动，如若启动，关闭MySQL服务：

[root@bp18425116f0cojd1vnz ~]# ps -ef |grep mysqlroot       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_SUBSTITUTIONmysql      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=3306root     2725702 2724782  0 14:35 pts/0    00:00:00 grep --color=auto mysql

② 关闭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 foundFeb 09 14:30:11 bp18425116f0cojd1vnz mysqld[1174]: /etc/rc.d/init.d/mysqld: line 265: cd: /www/server/mysql: No such file or directoryFeb 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.

③ 修改vim /etc/my.cnf：在/etc/my.cnf添加一行skip-grant-tables：

[client]#password       = your_passwordport            = 3306socket          = /tmp/mysql.sock[mysqld]skip-grant-tablesport            = 3306socket          = /tmp/mysql.sockdatadir = /www/server/datadefault_storage_engine = InnoDBperformance_schema_max_table_instances = 400table_definition_cache = 400skip-external-lockingkey_buffer_size = 32Mmax_allowed_packet = 100G

④ 重新启动MySQL数据库：

[root@bp18425116f0cojd1vnz ~]# systemctl start mysql

⑤ 以免密模式登录数据库：

[root@bp18425116f0cojd1vnz ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.50-log Source distributionCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

⑥ 登录mysql数据库：

mysql> use mysql;Database changed

⑦ 修改密码：

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为自己想要替换的数据库密码。

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

⑨ 已修改之后的密码登录数据库：

[root@bp18425116f0cojd1vnz ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.50-log Source distributionCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>

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

（2）查询缓存

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

（3）解析器

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

① 词法分析

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

② 语法分析

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

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

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

（4）SQL执行器

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

① 预处理阶段

在开始执行的时候，预处理阶段你对这个表有没有执行查询的权限，如若没有，就会返回相应的错误。

检查查询的表或者字段是否存在，如若没有，也会返回相应的错误信息。

② 优化器

在优化器阶段，优化器会对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这个索引去检索数据。

③ 执行器

在执行器执行SQL语句会对权限进行校验，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口与存储引擎层进行交互，执行SQL语句，并将结果返回个客户端。