MySQL 一 体系结构

一、MySQL 体系结构

1、 Client Connectors 接入方
2、 Management Serveices & Utilities 系统管理和控制工具。例如：备份恢复、mysqldump、 mysql复制集群、分区管理
3、 Connection Pool 连接池。管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求 
4、 SQL Interface SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
5、 Parser 解析器。SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的
6、 Optimizer 查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化
    查询执行的路径：mysql 客户端/服务端通信 ——查询缓存 ——查询优化处理 ——查询执行引擎 ——返回客户端
7、 Cache 和 Buffer 高速缓存区。查询缓存，如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
8、 pluggable storage Engines 插件式存储引擎。存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。 Mysql的存储引擎是插件式的
9、 file system 文件系统。数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等
10、MySQL 执行一条查询语句的内部执行过程
    1）客户端先通过连接器连接到 MySQL 服务器
    2）连接器权限验证通过之后，先查询是否有缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。
    3）分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
    4）优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
    5）优化器执行完就进入执行器，就开始执行语句进行查询比对，直到查询到满足条件的所有数据，然后进行返回。
    客户端——连接器（权限验证）——分析器（解析parser，SQL 语法验证）——优化器（优化处理）——执行器
11、MySQL 提示“不存在此列”是执行到解析器阶段报出的，因为解析器阶段检查 SQL 语句的正确性
12、查询优化处理的三个阶段：
    1）解析sql，通过lex词法分析,yacc语法分析将sql语句解析成解析树
    2）预处理阶段，根据mysql的语法的规则进一步检查解析树的合法性，如：检查数据的表和列是否存在，解析名字和别名的设置。还会进行权限的验证
    3）查询优化器，优化器的主要作用就是找到最优的执行计划
13、MySQL 查询缓存
    1）MySQL 查询缓存是发生在连接器之后的
    2）使用场景：以读为主的业务，数据生成之后就不常改变的业务。比如门户类、新闻类、报表类、论坛类等
    3）缓存的内容：SQL语句、SELECT操作的结果集
    4）判断标准：与缓存的SQL语句是否完全一样，区分大小写(简单认为存储了一个key-value结构，key为sql，value为sql查询结果集)
14、MySQL 查询缓存的优缺点
    优点：效率高，如果已经有缓存则会直接返回结果
    缺点：任何更新表操作都会清空查询缓存，导致查询缓存非常容易失效，失效太频繁导致缓存命中率比较低
15、查询缓存配置参数为 query_cache_type ，MySQL 8.0 之后直接删除了查询缓存的功能
    0：不启用查询缓存，默认值
    1：启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集都可以缓存起来，供其他客户端使用，加上 SQL_NO_CACHE将不缓存
    2：启用查询缓存，只要查询语句中添加了参数 SQL_CACHE，且符合查询缓存的要求，客户端的查询语句和记录集，则可以缓存起来，供其他客户端使用
    
    query_cache_size    最小值为40K，默认1M，推荐设置为：64M/128M
    query_cache_limit    限制查询缓存区最大能缓存的查询记录集，默认1M
16、查看缓存情况的命令：
    1）show status like 'Qcache%' 
    2）SHOW VARIABLES LIKE "%cache%" 可以查看 query_cache_type 的状态
17、MySQL 查询不缓存的情况：
    1）当查询语句中有一些不确定的数据时，则不会被缓存。如包含函数NOW()，CURRENT_DATE()等类似的函数，或者用户自定义的函数，存储函数，用户变量等都不会被缓存
    2）当查询的结果大于query_cache_limit设置的值时，结果不会被缓存
    3）对于InnoDB引擎来说，当一个语句在事务中修改了某个表，那么在这个事务提交之前，所有与这个表相关的查询都无法被缓存。因此长时间执行事务会大大降低缓存命中率
    4）查询的表是系统表
    5）查询语句不涉及到表
18、为什么mysql默认关闭了缓存开启
    1）在查询之前必须先检查是否命中缓存,浪费计算资源
    2）如果这个查询可以被缓存，那么执行完成后，MySQL发现查询缓存中没有这个查询，则会将结果存入查询缓存，这会带来额外的系统消耗
    3）针对表进行写入或更新数据时，将对应表的所有缓存都设置失效。
    4）如果查询缓存很大或者碎片很多时，这个操作可能带来很大的系统消耗
19、MySQL 的常用存储引擎有 InnoDB、MyISAM、Memory等，从 MySQL 5.5.5 版本开始 InnoDB 就成为了默认的存储引擎。
20、InnoDB 的特性：1）插入缓冲(insert buffer)  2）两次写(double write)  3）自适应哈希索引(adaptive hash index)
21、MySQL 可以针对不同的表设置不同的引擎。在 create table 语句中使用 engine=引擎名（比如InnoDB）来设置此表的存储引擎。
    如：create table student(
            id int primary key auto_increment,
            username varchar(120),
            age int
        ) ENGINE=InnoDB
22、InnoDB 和 MyISAM 的区别
    1）最大的区别是 InnoDB 支持事务，MyISAM 不支持事务
    2）InnoDB 支持崩溃后安全恢复，MyISAM 不支持崩溃后安全恢复
    3）InnoDB 支持行级锁，MyISAM 不支持行级锁，只支持到表锁
    4）InnoDB 支持外键，MyISAM 不支持外键
    5）InnoDB 主键查询性能高于 MyISAM
    6）MyISAM 性能比 InnoDB 高
    7）MyISAM 支持 FULLTEXT 类型的全文索引，InnoDB 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好
23、一张自增表中有三条数据，删除两条数据之后重启数据库，再新增一条数据，此时这条数据的 ID 是几
    如果这张表的引擎是 MyISAM，那么 ID=4，如果是 InnoDB 那么 ID=2（MySQL 8 之前的版本）
24、内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。
25、临时表，可以使用各种引擎类型。使用 InnoDB 引擎或 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的