mysql架构以及各个组件之间的关联
service层包含了mysql大多数核心功能，除了图中标注的连接器，查询缓存，分析器，优化器，执行器，还有所有的内置函数(日期，时间，数学和加密函数)，所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
存储引擎层 负责mysql中数据的存储和提取。和Linux下的各种文件系统一样，每个存储引擎都有自己的优势和劣势，各种存储引擎通过提供API和service层对接，通过API屏蔽各种存储引擎之间的差异。常见的存储引擎有InnoDB（安全，支持事务）、MyISAM（文件分为格式文件，数据文件，索引文件，支持压缩）、Memory（数据在内存中，快），现在最常用的是InnoDB，也是从mysql5.5版本开始成为默认的存储引擎，在5.5之前默认的是MyISAM。
1.连接器
连接器是mysql service层的第一个模块，也是处理客户端请求的第一个模块。
客户端和服务端的连接是使用经典的tcp协议，经过tcp握手之后，连接器开始进行身份验证。
登录命令
mysql -h$ip -P$port -u$user -p
#例如:交互输入密码
mysql -hlocalhost -P3306 -uroot -p
需要在bin目录下执行
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -p
Enter password: ******
- 如果账号或者密码错误，会提示Access denied for user
- 如果正确则认证通过，会查询当前用户的权限，之后用户的操作都是在这个权限范围中，如果在连接期间修改权限也是无效的，需要重新连接才会生效。
如下命令也是可以的，但是这种命令会把密码暴漏出来，建议还是使用如上图方式，采用交互交互输入密码更安全。
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -uroot -p123456
数据库建立连接的过程比较复杂，尽量使用长连接来减少建立连接的动作。但是全部使用长连接会导致MySQL占用的内存涨的特别快，因为MySQL在执行过程中临时使用的内存是管理在连接对象里面，这些资源在连接断开的时候才会得到释放。当内存占用太大的时候，就会被系统强行杀掉（OOM）,造成MySQL异常重启。
考虑以下两种方案来解决这个问题：
- 定期断开长连接。
- 每次执行一个占用内存比较大的查询后，执行mysql_reset_connection重新初始化连接资源。这个过程不需要重连和重新做权限验证，但会将连接恢复到刚创建的状态。
2.查询缓存(mysql8.0已去除)
客户端发送一个sql查询请求后，会先去缓存中查看是否存在。如果之前这条sql已经执行过，而且结果缓存起来，那么这次查询就会直接从缓存中获取结果返回，不会在走分析器，优化器，执行器。如果缓存中没有命中，才会继续走后面的模块。
缓存以key-value的形式存储，存放在一个引用表中，key是一个hash值，通过一个哈希值的引用，这个哈希值包括查询本身(sql),当前要查询的数据库，客户端协议的版本等一些其他会影响返回结果的的信息，查询结果作为value(任何字符上的变化，例如空格，注释都会导致缓存不命中)。
如果表被更改，所有的缓存都将失效，表的更改是指数据的改变和表结构的改变，包括INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等。
对于经常变更的数据库来说缓存命中率就很低了 ，查询缓存往往弊大于利，所以不建议使用mysql的缓存，而对于长时间不变化的表可以使用redis缓存。mysql在8.0就完全去掉查询缓存模块了。
3.分析器
我们根据mysql语法写出来一个sql之后交给服务层，分析器对sql语句进行词法分析和语法分析。
Mysql通过识别字符串中列名、表名、where、select/update/insert 等mysql关键字，在根据语法规则判断sql是否满足语法，最终会生成一个抽象语法树(AST)。
mysql分析器使用mysql语法规则验证和解析查询，例如验证是否使用错误的关键字或者使用关键字的顺序是否正确，再或者会验证引号是否能前后正确匹配。
如果关键字有误会提示
You have an error in your SQL syntax的信息，具体错误需要关注use near后的内容
4.优化器
经过分析器生成的语法树被认为是合法的，并由优化器转化成执行计划
mysql判断出了这条sql需要做什么之后，对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等。
mysql使用基于成本优化的优化器，尝试预测一个查询使用某种执行计划时的成本，选择最小的那一个。
例如表中有多个索引的时候决定使用哪一个；使用联合索引的时候,会根据索引调整where条件的顺序；
如果想知道优化器是怎么进行优化决策的，可以通过explain获取优化的信息。
explain具体的使用和解释：
5.执行器
调用存储引擎的API操作数据
优化器完成sql的优化后，提供一个执行计划给执行器，执行器开始执行这个执行计划来操作数据。
执行查询阶段：mysql只是简单的根据执行计划给出的指令逐步执行，通过调用存储引擎实现的接口来完成的。
- 客户端发送一条查询给服务器。
- 服务器先检查查询缓存，如果命中缓存则直接返回缓存中的结果。否则进入下一阶段。
- 服务器进行sql解析，预处理，再由优化器生成执行计划。
- mysql根据执行计划，调用存储引擎的API来执行查询。
- 将结果返回给客户端。
6.存储引擎
存储引擎是针对表的而不是库，对于同一个库不同的表可以使用不同的存储引擎。
常见的存储引擎有 MyISAM，InnoDB，Memory。
查看更多以及当前支持的存储引擎：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.06 sec)
- 在新建表的时候可以选择存储引擎
CREATE TABLE 'user' (
'id' bigint(20)
) ENGINE = MyISAM
ENGINE = MyISAM 代表这个表的存储引擎是MyISAM 。
- 查看表相关信息，例如mysql库中的user表
- 使用show table status 查看表信息(不限版本)
需要先切换到对应的数据库下再执行此命令
mysql> show table status like 't_stu' \G;
*************************** 1. row ***************************
Name: t_stu
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-12-23 16:58:18
Update_time: 2021-12-23 16:59:25
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.05 sec)
ERROR:
No query specified
- 还可以使用information_schema查看表信息(mysql5.0以后的版本支持)
mysql> select * from information_schema.tables where table_name = 't_stu' \G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: lgc
TABLE_NAME: t_stu
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 1
AVG_ROW_LENGTH: 16384
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2021-12-23 16:58:18
UPDATE_TIME: 2021-12-23 16:59:25
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_0900_ai_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)
ERROR:
No query specified
简单介绍下输出字段的含义：
Name: 表名。
Engine: 存储引擎。
Version：版本，默认10。
Row_format: 行的格式。
Rows: 表中的行数，对应MyISAM和其他一些存储引擎，该值是精确的; 而InnoDB该值是估计的。
Avg_row_length: 平均每行包含的字节数。
Data_length: 表数据的大小(字节)。
Max_data_length: 表数据的最大容量(和存储引擎有关)。
Index_length: 索引的大小(字节)。
Data_free: 对于MyISAM表，表示已经分配但是没有使用的空间。
Auto_increment: 下一个auto_increment值。
Create_time: 表的创建时间。
Update_time: 表数据最后修改时间。
Check_time: 使用check table命令或者myisamchk工具最后一次检查表的时间。
Collation: 表的默认字符集和字符列排序规则。
Checksum: 如果启用保存的是整个表的实时校验和。
Create_options: 创建表是指定的其他选项。
Comment: 包含其他额外信息。
6.1 InnoDB
InnoDB是mysql5.5.x开始默认的事务型引擎，也是使用最广泛的存储引擎。被设计用来处理大量短期事务的。
InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），表的大小只受限于操作系统文件的大小。表的结构定义存在 .frm后缀文件中，数据和索引集中存放在 .idb后缀文件中。因为表数据和索引是在同一个文件，所以InnoDB的索引是属于聚簇索引。
InnoDB采用MVCC支持高并发，并且实现了四种标准的隔离级别(读未提交，读已提交，可重复读，可串行化)，其默认级别是REPEATABLE-READ(可重复读)，并且通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁不仅仅锁定查询涉及的行，还会对索引中的间隙行进行锁定，以防止幻影行的插入。
InnoDB表是基于聚簇索引建立的，聚簇索引对主键的查询有很高的性能。但是InnoDB的非主键索引中必须包含主键列，所以如果主键列很大的话，非主键索引也会很大。如果一张表的索引较多，主键应该尽可能的小。
InnoDB的内部优化，包括磁盘预读(从磁盘读取数据时采用可预测性读取)，自适应哈希(自动在内存中创建hash索引以加速读操作)以及能够加速插入操作的插入缓冲区
6.2 MyISAM
在mysql5.1及之前的版本，MyISAM是默认的存储引擎。提供了大量的特性，包括全文索引，压缩，空间函数等，但是不支持事务和行级锁，只有表锁，而且有一个严重的问题是崩溃后无法安全恢复。
MyISAM的数据表存储在磁盘上是3个文件，表结构定义存在 .frm后缀文件中，表数据存储在 .MYD后缀文件中，表索引存储在 .MYI后缀文件中。表数据和表索引在不同的文件中，所以MyISAM索引是非聚簇索引。而且MyISAM可以存储表数据的总行数。
MyISAM表支持数据压缩，对于表创建后并导入数据以后，不需要修改操作，可以采用MyISAM压缩表。压缩命令：myisampack，压缩表可以极大的减少磁盘空间占用，因此也可以减少磁盘I/O，提高查询性能。而且压缩表中的数据是单行压缩，所以单行读取是不需要解压整个表。
6.3 Memory
Memory存储引擎的数据是存放在内存中的，所以如果服务器重启会导致数据丢失，但是表结构还是存在的，表结构是存储在以 .frm 为后缀的文件中。
Memory默认hash索引，因此查询非常快。Memory表是表级锁，因此并发写入的性能较低。不支持BLOB或TEXT类型的列，并且每行的长度都是固定的，所以即使指定了varchar列实际存储也会转换成char，会导致内存浪费。
如果mysql查询过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表，如果中间结果太大超出Memory表的限制或者含有BLOB或TEXT字段，那么临时表会转换成MyISAM表。
上面介绍了三种，你如何选择存储引擎呢：
- 事务 ：目前只有Innodb能完美的支持事务。
- 备份 ：只有Innodb有免费的在线热备方案，mysqldump不算在线热备的方案，它需要对数据加锁。
- 崩溃恢复：myisam表由于系统崩溃导致数据损坏的概率比Innodb高很多，而且恢复速度也没有innodb快。
- 特有的特性：如需要聚簇索引，那就需要选择innodb存储引擎，有的需要使用地理空间搜索，那就选择myisam 。
mysql中MyISAM和InnoDB有什么区别:
