运维必备技术点
48小时系统班试听入口
运维必备技术点
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: 0your_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系统安装配置+Xshell远程连接
运维界升职加薪必备的云计算技术，你学了吗？
学完高级运维云计算课程之后，你可以：
跨越90%企业的招聘硬门槛
增加70%就业机会
拿下BAT全国TOP100大厂敲门砖
体系化得到运维技术硬实力
技术大佬年薪可达30w+