Mysql

背景

步骤

宏观

我们首先找到一条线将所有关于mysql的知识串起来

客户端：最上层的服务并不是 MySQL 所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
Server 层：大多数 MySQL 的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
存储引擎层：第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。Server 层通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。

真正跑起来的时候：
先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限会先查询缓存 (MySQL8.0 版本以前)。
如果没有缓存，分析器进行语法分析，提取 sql 语句中 select 等关键元素，然后判断 sql 语句是否有语法错误，比如关键词是否正确等等。
语法解析之后，MySQL 的服务器会对查询的语句进行优化，确定执行的方案。
完成查询优化后，按照生成的执行计划调用数据库引擎接口，返回执行结果。

微观

数据库设计

表字段设计

所有数据都要有数据类型，那数据类型到底如何选择呢？？？？

varchar 与 char 的区别，所有的选择都要付出代价。

日常的设计，对于长度相对固定的字符串，可以使用 char，对于长度不确定的，使用 varchar 更合适一些。

索引设计

为什么

全文检索太慢了，索引文件相较于数据库文件，体积小得多，查到索引之后再映射到数据库记录，查询效率就会高很多。

是什么

加快查询的一种方式

有什么

主键索引：是一种特殊的唯一索引，其值必须是唯一的，并且不允许有空值。每个表只能有一个主键，通常与业务逻辑无关，并且与自动增长属性一起使用，确保每次插入新数据时主键值都是唯一的且递增的。
唯一索引：与主键索引类似，要求索引列的值必须唯一，但允许有空值。它可以用于任何类型的列，不仅限于主键列，有助于确保数据完整性和提高查询性能。
普通索引：没有任何限制，允许被索引的数据列包含重复的值。
全文索引：主要用于文本搜索，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引更类似于搜索引擎的工作方式，对于包含大量文本的字段特别有用。

B+树索引：最常见的索引类型，一种将索引值按照一定的算法，存入一个树形的数据结构中（二叉树），每次查询都从树的根节点开始，一次遍历叶子节点，找到对应的值。查询效率是 O(logN)。

Hash 索引：基于哈希表的索引，查询效率可以达到 O(1)，但是只适合 = 和 in 查询，不适合范围查询。

①、聚簇索引：聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说，聚簇索引的叶子节点中，包含了一个完整的记录行。

②、非聚簇索引：它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。

引擎设计

MyISAM 为非聚簇索引，索引和数据分开存储，索引保存的是数据文件的指针。
InnoDB 为聚簇索引，索引和数据不分开。

运行问题

慢 SQL 如何定位呢？发现问题

IO 成本：从磁盘读取数据到内存的开销。
CPU 成本：CPU 处理内存中数据的开销。

慢查询日志：开启 MySQL 慢查询日志，再通过一些工具比如 mysqldumpslow 去分析对应的慢查询日志，找出问题的根源。
服务监控：可以在业务的基建中加入对慢 SQL 的监控，常见的方案有字节码插桩、连接池扩展、ORM 框架过程，对服务运行中的慢 SQL 进行监控和告警

解决问题

解决问题的办法

日志解决

①、错误日志（Error Log）：记录 MySQL 服务器启动、运行或停止时出现的问题。

②、慢查询日志（Slow Query Log）：记录执行时间超过 long_query_time 值的所有 SQL 语句。这个时间值是可配置的，默认情况下，慢查询日志功能是关闭的。可以用来识别和优化慢 SQL。

③、一般查询日志（General Query Log）：记录所有 MySQL 服务器的连接信息及所有的 SQL 语句，不论这些语句是否修改了数据。

④、二进制日志（Binary Log）：记录了所有修改数据库状态的 SQL 语句，以及每个语句的执行时间，如 INSERT、UPDATE、DELETE 等，但不包括 SELECT 和 SHOW 这类的操作。

以及两个 InnoDB 存储引擎特有的日志文件：

⑤、重做日志（Redo Log）：记录了对于 InnoDB 表的每个写操作，不是 SQL 级别的，而是物理级别的，主要用于崩溃恢复。

⑥、回滚日志（Undo Log，或者叫事务日志）：记录数据被修改前的值，用于事务的回滚。

锁

MySQL 的乐观锁和悲观锁了解吗？
悲观锁（Pessimistic Concurrency Control）：
悲观锁认为被它保护的数据是极其不安全的，每时每刻都有可能被改动，一个事务拿到悲观锁后，其他任何事务都不能对该数据进行修改，只能等待锁被释放才可以执行。

数据库中的行锁，表锁，读锁，写锁均为悲观锁。

乐观锁（Optimistic Concurrency Control）
乐观锁认为数据的变动不会太频繁。

乐观锁通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现，其中，版本最为常用。

事务在从数据库中取数据时，会将该数据的版本也取出来(v1)，当事务对数据变动完毕想要将其更新到表中时，会将之前取出的版本 v1 与数据中最新的版本 v2 相对比，如果 v1=v2，那么说明在数据变动期间，没有其他事务对数据进行修改，此时，就允许事务对表中的数据进行修改，并且修改时 version 会加 1，以此来表明数据已被变动。

如果，v1 不等于 v2，那么说明数据变动期间，数据被其他事务改动了，此时不允许数据更新到表中，一般的处理办法是通知用户让其重新操作。不同于悲观锁，乐观锁通常是由开发者实现的

事务

总结