48小时系统班试听入口

【运维必备技术点】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语句,并将结果返回个客户端。







推荐阅读

>>>新手必备-Linux入门之云计算是什么

>>>红帽认证入门-Linux系统介绍及企业版本选型

>>>新手必备-Linux系统安装配置+Xshell远程连接

>>>Linux常用命令行合集之绝对路径和相对路径

>>>软连接与硬连接



运维界升职加薪必备的云计算技术,你学了吗?

学完高级运维云计算课程之后,你可以:

  • 跨越90%企业的招聘硬门槛

  • 增加70%就业机会

  • 拿下BAT全国TOP100大厂敲门砖

  • 体系化得到运维技术硬实力

  • 技术大佬年薪可达30w+


客服电话 15822722968 邮箱 799830458@qq.com 地址 天津市红桥区光荣道157号宝能创业中心2号楼-1、2门-907号
官方公众号
商务合作