MySql 初次见面

前言

MySQL 是一种流行的开源关系型数据库管理系统（RDBMS），它使用结构化查询语言（SQL）来管理或操作数据库。MySQL 是由瑞典公司 MySQL AB 开发，该公司后来被 Sun Microsystems 收购，之后 Sun Microsystems 又被 Oracle Corporation 收购。尽管其所有权有所变更，但 MySQL 仍然作为开源项目继续发展，并由 Oracle 以及其他公司和社区维护。

客户端在mysql的执行链路

1、连接层

通过连接层和mysql进行连接，客户端每产生一个连接或者一个会话，在服务端就会创建一个线程来处理。 反过来如果要杀死会话，就是 Kill 线程。
通过show status命令查询当前链接数， show status like ‘Threads%’;

显示用户正在运行的线程
show FULL PROCESSLIST;

最大的连接数，超过该值不允许建立连接 默认151 1~100000
SELECT @@max_connections;

非交互连接等待的时间 （单位s） 默认28800 = 8小时 如果线程空闲8小时，就会断开
SELECT @@wait_timeout;

2、查询缓存

show variables like ‘query_cache%’;
默认关闭
MySQL 自带的缓存的应用场景有限，

• 要求 SQL语句必须-模一样，中间多一个空格，字母大小写不同都被认为是不同的的 SQL
• 表里面任何一条数据发生变化的时候，这张表所有缓存都会失效
  不建议使用MySQL自带的查询缓存，消耗数据库的宝贵资源。且8.0已经舍弃这个功能了

3、解析器

3.1、词法解析

将sql语句打碎，转化成一个一个关键单词，然后交给语法解析器去构建语法树，判断语法是否正确

3.2、语法解析

语法解析已经知道每个sql语句的单词了，那么在语法解析的时候，会去检查语法是否正确，比如，select是不是写出seletc等。 如果写错了直接抛出错误，否则解析成如下的语法树。
比如

4、预处理器

它会检查生成的解析树，解决解析器无法解析的语义，比如检查表和列名是否存在，检查名字和别名，保证没有歧义。
它还能防止sql注入，
sql注入是因为客户端拼接用户传入的参数，然后拼接好语句给到mysql， 这样会导致会安全问题
案例

select * from user where id={id}
-- 假如在java程序里 id 是通过外界传进来的值拼接的,且id为 123 and 1=1
此时解析后的sql为
select * from user where id=123 and 1=1
可以查询出所有数据

如果你需要参数化，你只要告诉Mysql，传一个预处理语句就行，Mysql会将参数与语句编译分开。通过占位符？，来处理参数值，参数值可以包含未转义的 SQL 引号和定界符

5、优化器

优化器可以做很多事情，
表优化器：比如连表查询使用哪张表再签，使用派生表还是内部临时表
索引优化器：如果查询存在多个索引，哪个索引效率高，哪个索引优先，比如主键索引优先普通索引

具体可以参考官网 查询优化器

6、存储引擎

– 查询当前服务器支持的存储引擎
SHOW ENGINES;

此处列举常见的两种存储引擎分析并做对比

6.1、InnoDB 存储引擎

6.1.1、主要特性

支持事务（Transaction）：

• InnoDB支持ACID（原子性、一致性、隔离性、持久性）兼容的事务处理。事务是一组不可分割的操作序列，要么全部成功，要么全部失败。
• 通过redo log（重做日志）和undo log（撤销日志）保证事务的完整性和一致性。

支持行级锁（Row-level Locking）：

• 相比传统的表级锁，行级锁可以最大程度地支持并发处理，减少锁冲突，提高并发性能。
• InnoDB还提供了多版本并发控制（MVCC），允许读取操作不加锁，进一步提高并发性能。

支持外键约束（Foreign Key Constraints）：

• InnoDB支持外键约束，可以保证数据的完整性和一致性。通过外键约束，可以实现表之间的关联和数据操作的一致性检查。

支持崩溃恢复（Crash Recovery）：

• InnoDB具备崩溃恢复能力，在数据库异常关闭或崩溃时，可以通过事务日志自动进行恢复和修复，确保数据的安全性和一致性。

自动增长和收缩：

• InnoDB表的大小只受限于操作系统的文件大小，可以自动增长和收缩，无需手动管理表空间。

存储结构：

• InnoDB将数据和索引存储在表空间中，表空间可以包含多个文件（或原始磁盘分区）。这与MyISAM表不同，后者每个表被存储在分离的文件中。
• InnoDB支持聚簇索引和非聚簇索引。聚簇索引的叶子节点包含完整的数据行，数据的物理存储顺序与索引顺序一致；非聚簇索引的叶子节点存储的是索引值和指向数据行的指针。

缓存机制：

• InnoDB存储引擎完全与MySQL服务器整合，并在主内存中维护自己的缓冲池（Buffer Pool），用于缓存数据和索引，减少磁盘I/O操作，提高数据访问效率。

性能优化：

• InnoDB支持多种优化技术，如数据页压缩、多版本并发控制、缓冲池管理等，以提高数据库的性能和可靠性。

6.1.2、应用场景

高可靠性应用程序

• 事务支持：InnoDB 支持 ACID（原子性、一致性、隔离性、持久性）事务，确保数据的一致性和完整性。当一个事务被提交时，要么完全执行，要么完全回滚，这种机制大大降低了数据丢失或损坏的风险。
• 自动崩溃恢复：InnoDB 具有自动崩溃恢复的能力，在数据库重新启动时，会自动将未完成的事务回滚或提交，确保数据库的一致性。

高并发读写场景

• 行级锁定：InnoDB 使用行级锁定机制，而不是表级锁定，这大大提高了并发性能。多个事务可以同时操作表中的不同行，减少了锁竞争的概率。
• 多版本并发控制（MVCC）：InnoDB 实现 MVCC，允许读写操作同时进行，提高了并发性和性能。读操作不会阻塞写操作，反之亦然。
• 缓冲池：InnoDB 使用缓冲池来缓存表和索引数据，提高数据读取的性能。当查询请求到达时，MySQL 会首先检查缓冲池中是否已存在该查询的结果，从而避免了不必要的磁盘I/O操作。

数据一致性要求高的场景

• 外键约束：InnoDB 支持外键约束，确保关联表之间的数据一致性。当插入、更新或删除数据时，外键约束将进行检查，防止数据不一致的情况发生。
• 双写机制：InnoDB 通过双写缓冲区等机制，进一步保证了数据在写入磁盘过程中的一致性和完整性。

需要热备份和灾难恢复的场景

• 热备份：InnoDB 支持在线热备份，允许在数据库运行时进行备份，而不会中断数据库服务。
• 灾难恢复：InnoDB 提供了恢复工具和日志文件，可以在系统崩溃或灾难性事件后迅速恢复数据库。

大型数据集

• 数据缓存：InnoDB 的数据缓存机制适用于大型数据集，通过缓存数据提高了读取性能。
• 可扩展性：InnoDB 支持可扩展性，可以通过添加更多的服务器实例来提高数据库的性能和容量。

6.2、MyISAM 存储引擎

6.2.1、主要特性

轻量级与读取速度快：

• MyISAM采用表级锁设计，查询时只需锁定涉及的表，而不是行，这在读取密集的应用中可以大大提高并发性能。
• MyISAM还拥有一个独特的缓冲机制——键缓存（Key Cache），它可以把索引块读入内存，进一步提高读取速度。

全文索引支持：

• MyISAM支持全文索引，这使得数据库能够对文本数据进行高效的搜索，这在博客系统、新闻网站或文档管理系统等需要文本搜索的应用中非常有用。

空间和性能友好：

• MyISAM对磁盘空间的利用率高，可以压缩数据以减少磁盘空间的占用。
• 同时，MyISAM对CPU资源的利用也非常高效，它可以使用并行的方式来进行查询操作，以提高性能。

容易管理：

• MyISAM的数据文件和索引文件都是独立的，这使得在发生问题时，可以单独对数据文件或索引文件进行修复，而不需要影响整个数据库。
• MyISAM还提供了许多工具，可以用来检查、优化和修复表。

6.2.2、不足之处

不支持事务处理：

• MyISAM不支持事务处理，这意味着无法使用回滚和提交等事务控制操作。这在一定程度上限制了其在需要高数据一致性和完整性的场景中的应用。

表级锁定：

• MyISAM采用表级锁定，当对表进行修改时，其他用户无法对同一表进行读写操作。这可能会导致在写操作频繁的场景下，性能下降。

并发写操作性能低：

• 由于MyISAM不支持行级锁，且写操作会锁定整个表，因此在并发写操作较多的场景下，性能可能会受到影响。

6.2.3、应用场景

只读或大量读操作的应用：

• 对于只需要进行大量读操作而很少进行写操作的应用场景，MyISAM引擎的表级锁定和快速查询性能使其成为理想选择。例如，一些静态的网站内容、日志数据或只读的数据仓库等。

全文搜索应用：

• MyISAM引擎支持全文索引，使得它非常适合于需要进行文本搜索的应用场景。例如，博客系统、新闻网站或文档管理系统等。

简单的数据备份和恢复：

• 由于MyISAM引擎将数据和索引分开存储，因此在备份和恢复数据时，可以分别处理数据文件和索引文件，提高备份和恢复的效率和灵活性。