mysql的原理及经验

1. 存储引擎

存储引擎是MySQL的核心组件之一，它负责数据的存储和检索。MySQL支持多种存储引擎，每种引擎都有其独特的特点和适用场景。

• InnoDB：这是MySQL的默认存储引擎，支持事务处理（ACID特性）、行级锁定和外键。它非常适合需要高并发和事务支持的应用场景。

• MyISAM：虽然它不支持事务，但提供了全文索引和较高的读取速度，适合读多写少的应用场景。

• Memory：将数据存储在内存中，提供极高的读写速度，但数据在服务器重启后会丢失。

• 其他引擎：如Archive（用于存储大量不常访问的数据）、CSV（将数据存储为CSV文件）等。

2. 数据结构

MySQL使用多种数据结构来组织和存储数据，主要包括：

• 表（Table）：数据的基本存储单元，由行（记录）和列（字段）组成。

• 索引（Index）：用于加速数据检索的结构，常见的索引类型包括B树索引、哈希索引等。

• 视图（View）：虚拟表，其内容由SQL查询定义，可以简化复杂的查询操作。

• 存储过程（Stored Procedure）：预编译的SQL语句集合，可以提高代码复用性和执行效率。

3. 事务处理

事务是数据库操作的基本单位，它确保一系列操作要么全部成功，要么全部失败。MySQL的事务特性主要由InnoDB存储引擎支持，包括以下内容：

• ACID特性：

  • 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。

  • 一致性（Consistency）：事务执行前后，数据库的状态保持一致。

  • 隔离性（Isolation）：并发事务之间互不干扰，MySQL支持多种隔离级别（如READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）。

  • 持久性（Durability）：事务提交后，其结果是永久性的，即使系统故障也不会丢失。

• 事务控制语句：

  • BEGIN TRANSACTION 或 START TRANSACTION：开始一个事务。

  • COMMIT：提交事务。

  • ROLLBACK：回滚事务。

4. SQL解析器和执行引擎

当用户发送SQL语句到MySQL服务器时，SQL解析器和执行引擎会处理这些语句：

• SQL解析器：解析SQL语句，检查语法错误，并将其转换为内部的查询树结构。

• 查询优化器：分析查询树，选择最优的执行计划。

• 执行引擎：根据优化后的执行计划，执行SQL语句，从存储引擎中读取或写入数据。

5. 数据库连接和权限管理

MySQL提供了强大的连接和权限管理功能，确保数据库的安全性和高效性：

• 连接管理：

  • 支持多种客户端连接方式，如TCP/IP、Unix Socket等。

  • 可以设置连接池，管理并发连接数。

• 权限管理：

  • 用户账户管理：创建、删除用户，设置用户密码。

  • 权限控制：通过GRANT和REVOKE语句，授予或撤销用户对数据库、表、列等的访问权限。

  • 角色管理：MySQL 8.0引入了角色（Role）的概念，可以将一组权限分配给角色，再将角色分配给用户，简化权限管理。

6. 其他重要特性

• 备份和恢复：支持多种备份方式，如物理备份、逻辑备份，以及恢复操作。

• 复制（Replication）：通过主从复制，实现数据的高可用性和读写分离。

• 分区（Partitioning）：将大表分割成多个分区，提高查询效率和管理便利性。

• 性能优化：提供多种性能优化工具和技术，如慢查询日志、索引优化等。

经验：

1.使用关联查询的时候，用小表驱动大表的方式效率会提升很多。而 inner join 会自动的进行小表驱动大表的优化

2.避免大事务，大事务就是运行的时间比较长，操作的数据比较多的事务
大事务会影响数据库的性能，应当尽量把大事务拆成若干个小事务，禁止写过于复杂的sql语句，除了造成大事务不说，还会让别人头大，无法维护。

3.删除操作应该采用逻辑删除，不能物理删除。

4.长度较长的字段上建立索引时，必须指定索引长度，没必要对全字段建立索引

5.设计索引的时候尽量使用复合索引，并将区分度高的字段放在前面

6.不要使用外键与其它表进行关联，避免高并发场景的性能问题