一、逻辑架构
1、MySQL逻辑架构
我们可以根据上图来对sql的执行过程进行分析
- 第一步：客户端与服务器建立一个连接，从连接池中分配一个线程处理SQL语句
- 第二步：SQL接口接受SQL指令
- 第三步：如果是5.7版本，就会先去缓存中检查是否已经有查询结果存在，如果存在就返回此结果给客户端。如果是8.0版本，就会使用解析器，将SQL语句解析成语法树，如果SQL语法有问题，就会在此报错
- 第四步：SQL会使用优化器生成执行计划，例如决定索引的使用，表之间的连接顺序等
- 第五步：调用存储引擎，去内存中查询数据（由文件系统加载到内存中）
- 第六步：查询到结果后，如果是5.7版本，会将结果写到缓存中，再走到SQL接口，释放占用的工作线程，将结果返回给客户端（如果是8.0，就跳过写入缓存的步骤）
2、MySQL服务架构
- 第一层：连接层
- 对客户端的请求进行身份认证
- 从权限表中查询当前客户端的权限信息
- 提供控制客户端连接数量的连接池以及处理SQL请求的线程池
- 第二层：服务层，包括上面介绍的SQL接口、解析器、优化器和缓存
- 第三层：引擎层，真正的负责了MySQL中数据的存储和提取，对物理服务器维护的底层数据进行操作
3、查看SQL执行耗时
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql>
mysql>
mysql> SET @@session.profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
首先，我们要打开profiling这个变量。便于我们查看SQL执行的耗时show profiles：查看所有的查询语句的信息show profile;：查看最近一次查询语句的执行耗时show profile for query id编号：查看指定id的查询语句执行耗时
- MySQL8.0，可以发现同样的查询语句执行流程是一样的
mysql> select * from test1;
+------+--------+
| id | name |
+------+--------+
| 2 | 十年 |
+------+--------+
1 row in set (0.01 sec)
mysql>
mysql> select * from test1;
+------+--------+
| id | name |
+------+--------+
| 2 | 十年 |
+------+--------+
1 row in set (0.00 sec)
mysql>
mysql> show profiles;
+----------+------------+---------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------+
| 1 | 0.00018475 | select @@profiling |
| 2 | 0.00150575 | SELECT DATABASE() |
| 3 | 0.00339475 | show databases |
| 4 | 0.00165200 | show tables |
| 5 | 0.00973450 | select * from test1 |
| 6 | 0.00024725 | select * from test1 |
+----------+------------+---------------------+
6 rows in set, 1 warning (0.00 sec)
mysql>
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000108 |
| Executing hook on transaction | 0.000003 |
| starting | 0.000006 |
| checking permissions | 0.000005 |
| Opening tables | 0.000027 |
| init | 0.000004 |
| System lock | 0.000006 |
| optimizing | 0.000003 |
| statistics | 0.000009 |
| preparing | 0.000014 |
| executing | 0.000033 |
| end | 0.000002 |
| query end | 0.000003 |
| waiting for handler commit | 0.000007 |
| closing tables | 0.000006 |
| freeing items | 0.000007 |
| cleaning up | 0.000006 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.01 sec)
mysql>
mysql> show profile for query 5;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000075 |
| Executing hook on transaction | 0.000005 |
| starting | 0.000009 |
| checking permissions | 0.000007 |
| Opening tables | 0.000037 |
| init | 0.000006 |
| System lock | 0.000010 |
| optimizing | 0.000007 |
| statistics | 0.000014 |
| preparing | 0.000018 |
| executing | 0.009485 |
| end | 0.000014 |
| query end | 0.000004 |
| waiting for handler commit | 0.000010 |
| closing tables | 0.000010 |
| freeing items | 0.000016 |
| cleaning up | 0.000009 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
mysql>
mysql> show profile for query 6;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000108 |
| Executing hook on transaction | 0.000003 |
| starting | 0.000006 |
| checking permissions | 0.000005 |
| Opening tables | 0.000027 |
| init | 0.000004 |
| System lock | 0.000006 |
| optimizing | 0.000003 |
| statistics | 0.000009 |
| preparing | 0.000014 |
| executing | 0.000033 |
| end | 0.000002 |
| query end | 0.000003 |
| waiting for handler commit | 0.000007 |
| closing tables | 0.000006 |
| freeing items | 0.000007 |
| cleaning up | 0.000006 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
mysql>
- MySQL5.7
因为这个版本存在缓存，所以我们开启一下看看同样的SQL，两次执行会有什么区别
我们需要打开缓存配置，将其设置为query_cache_type=2，按需开启，重启MySQL服务
[root@myLinux1 ~]# vim /etc/my.cnf
[root@myLinux1 ~]# systemctl restart mysqld
[root@myLinux1 ~]#
重复执行2次使用缓存的查询，可以看出，使用缓存的情况下，如果SQL语句一样并且缓存中已有结果，就不会继续往下执行了
mysql> select SQL_CACHE * from test1 WHERE id = 1;
+------+--------+
| id | name |
+------+--------+
| 1 | decade |
+------+--------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> select SQL_CACHE * from test1 WHERE id = 1;
+------+--------+
| id | name |
+------+--------+
| 1 | decade |
+------+--------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------+
| 1 | 0.00017600 | SELECT DATABASE() |
| 2 | 0.00025400 | show databases |
| 3 | 0.00007900 | show tables |
| 4 | 0.00016950 | select * from test1 |
| 5 | 0.00022650 | select * from test1 |
| 6 | 0.00320425 | select SQL_CACHE * from test1 WHERE id = 1 |
| 7 | 0.00004550 | select SQL_CACHE * from test1 WHERE id = 1 |
+----------+------------+--------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
mysql>
mysql> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000018 |
| Waiting for query cache lock | 0.000002 |
| starting | 0.000001 |
| checking query cache for query | 0.000006 |
| checking privileges on cached | 0.000002 |
| checking permissions | 0.000008 |
| sending cached result to clien | 0.000006 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)
mysql>
mysql>
mysql>
mysql> show profile for query 6;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000024 |
| Waiting for query cache lock | 0.000003 |
| starting | 0.000002 |
| checking query cache for query | 0.002889 |
| checking permissions | 0.000014 |
| Opening tables | 0.000017 |
| init | 0.000020 |
| System lock | 0.000007 |
| Waiting for query cache lock | 0.000002 |
| System lock | 0.000014 |
| optimizing | 0.000088 |
| statistics | 0.000014 |
| preparing | 0.000010 |
| executing | 0.000001 |
| Sending data | 0.000030 |
| end | 0.000002 |
| query end | 0.000005 |
| closing tables | 0.000004 |
| freeing items | 0.000031 |
| Waiting for query cache lock | 0.000003 |
| freeing items | 0.000012 |
| Waiting for query cache lock | 0.000001 |
| freeing items | 0.000001 |
| storing result in query cache | 0.000002 |
| cleaning up | 0.000010 |
+--------------------------------+----------+
25 rows in set, 1 warning (0.00 sec)
mysql>
mysql>
mysql> select SQL_NO_CACHE * from test1 WHERE id = 2;
+------+--------+
| id | name |
+------+--------+
| 2 | 十年 |
+------+--------+
1 row in set, 1 warning (0.00 sec)
mysql>
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000078 |
| checking permissions | 0.000007 |
| Opening tables | 0.000020 |
| init | 0.000023 |
| System lock | 0.000007 |
| optimizing | 0.000009 |
| statistics | 0.000015 |
| preparing | 0.000011 |
| executing | 0.000002 |
| Sending data | 0.000041 |
| end | 0.000004 |
| query end | 0.000007 |
| closing tables | 0.000006 |
| freeing items | 0.000013 |
| cleaning up | 0.000014 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)
mysql>
4、数据库缓冲池
- 因为磁盘I/O会消耗很多时间，所以DBMS会申请使用内存作为数据缓冲池（数据库缓冲池和查询缓存不是一回事），减少直接与磁盘进行I/O
- InnoDB是以页为单位来管理存储空间的，我们进行的增删查改操作本质上都是在访问内存中一页一页的数据
- 当执行更新操作时，会先刷新缓冲池中的数据，然后再按照一定的频率同步到磁盘的文件系统
- 那如果同步到一半断电了怎么办？那就要用到下面两个文件
- Redo.log—记录要重新同步的动作
- Undo.log—记录要回滚的动作
在多线程情况下，可能要申请多个buffer pool，通过改变变量innodb_buffer_pool_instances为每个线程去申请独立的内存空间，避免相互影响
mysql> show variables like '%innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1 |
+------------------------------+-------+
1 row in set (0.00 sec)
mysql>
查看缓冲池大小innodb_buffer_pool_size
当buffer pool实例数量发生变化时，每个实例分配到的缓冲池大小是此变量/instance数量
mysql> show variables like '%innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql>
二、存储引擎
1、存储引擎的查看
1）如下所示，我们可以查看当前系统默认存储引擎和系统支持哪些存储引擎
Transactions：是否支持事务
XA：是否支持分布式事务
Savepoints：保存点，回滚时使用
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)
mysql>
mysql>
mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB |
+--------------------------+
1 row in set (0.00 sec)
mysql>
2）在建表时，如果没有显式指明存储引擎，那么就会使用系统默认的存储引擎
CREATE TABLE table_name(
id INT,
name VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、存储引擎的差别
1）InnoDB
- 优势：
- 支持外键
- InnoDB是MySQL的
默认事务型引擎，它被设计用于处理大量的短期事务，可以保证事务的完整提交和回滚 - 如果业务涉及大量更新和删除操作，推荐使用InnoDB
- InnoDB是行级锁，操作时只锁住某一行的数据，不影响其他行，适合高并发的场景。而MyISAM是表级锁，即使操作一条记录也会锁住整个表，不适合高并发
- 劣势：
- 对比MyISAM，InnoDB在写数据方面的效率差一些，并且InnoDB会占用更多的磁盘空间保存数据和索引
- MyISAM索引和数据是独立开的，只缓存索引。InnoDB因为索引和数据都在一个文件中，所以不仅缓存索引还缓存真实数据，对内存要求较高，内存大小对性能会有很大影响
2）MyISAM
- 优势：访问速度快，对事务没有安全要求且主要以SELECT、INSERT为主的应用比较适合
- 劣势：不支持外键、事务、行级锁，所以崩溃后无法安全恢复
两种存储引擎的数据结构可以参考博主之前写的【MySQL】Linux下MySQL的目录结构、用户、权限与角色，此博客第一节介绍了两种存储引擎的数据结构
如有错误，欢迎指正！！！