【数据库】mysql

目录

1、关于索引

（1）基于B+树的索引存储

（2）聚簇索引和非聚簇索引

（3）最左匹配原则

（4）索引定义需要注意的

2、事务保证

事务的ACID

mysql的MVCC

3、四种隔离级别

事务的几个问题

四种隔离级别

4、几种锁

5、一条语句的执行过程

6、慢查询排查与优化过程

(1) 分析

(2) 单表优化

(2) 关联优化

(3) 索引下推

(4) 大数据量的优化

---

1、关于索引

（1）基于B+树的索引存储

B树, B+树, B*树, 红黑树, 平衡二叉树-CSDN博客

（2）聚簇索引和非聚簇索引

• 聚簇索引：会决定数据在磁盘上的物理存储顺序。也就是说，表中的数据行实际上按照聚簇索引的顺序存储。一个表只能有一个聚簇索引，因为数据行只能按照一种顺序进行物理存储。
• 非聚簇索引：与物理存储顺序无关。它包含指向数据行的指针和索引字段的值。一个表可以有多个非聚簇索引，因为非聚簇索引只是数据的副本，并不会改变数据的物理存储顺序。
• 聚簇索引通常比非聚簇索引提供更好的数据检索性能，因为它可以直接找到数据行。
• 如果经常根据某个字段进行查询，可以创建聚簇索引。如果多个字段进行查询，可以创建非聚簇索引

（3）最左匹配原则

对一个多字段索引，mysql 会以最左边的索引列为起点，一直向右匹配直到遇到范围查询(>、<、between、like) 停止。

如果有一个联合索引(a,b,c)，查询条件为a=1 and b=2时，可以命中索引。但如果查b=2，则无法利使用索引，因为查询优化器无法从最左边的索引列开始匹配。

同样，如果查询条件包含范围查询，如a=1 and b>2 and c=3，那么索引匹配在b字段上就会停止，因为b字段是一个范围查询，它之后的字段（如c字段）将无法使用索引。

（4）索引定义需要注意的

联合索引的定义原则：

• 最左匹配原则（经常使用的列优先）
• 最小空间原则（字段值占用小的优先）
• 离散度高原则（值多样化的列优先）

具体如：

• is not null 无法使用索引，但 is null 可以
• IN可以使用索引， NOT IN 无法使用索引
• 如果把  DECIMAL 字段和  INT 字段 Join 在一起，无法用索引
• ike 9%可以用到索引， like %9%  like%9 用不到索引
• 联合索引中，如果不是按照索引最左列开始查找，无法使用索引
• 联合索引中，如果查询有某个列的范围查询，其右边所有的列都无法使用索引

2、事务保证

事务的ACID

• Atomicity 原子性：要么全成功，要么全失败
• Consistency 一致性：全局数据保持一致
• Isolation 隔离性：并发事务之间互不干扰
• Durability 持久性：事务提交后永久生效

mysql的MVCC

• 多版本并发控制： 在数据行上添加两个额外的列（createT, deleteT）
• 每个事务在开始时有一个唯一ID，用于标识该事务在数据库中所做的所有修改。当有修改时，InnoDB 不会立即覆盖原始数据，而是生成一个新的数据版本。这样，其他事务在读取数据时，可以根据其事务 ID 选择合适的数据版本来读取

3、四种隔离级别

事务的几个问题

• 脏读：一个事务读取了另一个尚未提交的事务中的数据
• 幻读：在一个事务内执行相同的查询，但由于其他事务插入了满足其查询条件的新行，导致第二次查询返回了更多的行。
• 不可重复读：在同一个事务中，由于其他事务的修改，导致多次读取同一数据返回的结果不同

四种隔离级别

(1) 读未提交（Read Uncommitted）

• 最低的隔离级别，允许事务读取尚未被其他事务提交的数据。
• 可能导致 “ 脏读 ” （ Dirty Read ），因为读取的数据可能最终不会被提交。
• 也可能导致 “ 不可重复读 ” （ Non-repeatable Read ）和 “ 幻读 ” （ Phantom Read ）。

(2) 读已提交（ Read Committed ）

• 是大多数数据库系统的默认隔离级别（例如 Oracle, PostgreSQL ）。
• 只允许事务读取已经被其他事务提交的数据。
• 防止了 “ 脏读 ” ，但可能导致 “ 不可重复读 ” 和 “ 幻读 ” 。

(3) 可重复读（ Repeatable Read ）

• 是 MySQL 的 默认隔离级别。
• 在同一事务中，多次读取同一数据的结果是一致的。
• 防止了 “ 脏读 ” 和 “ 不可重复读 ” ，但可能导致 “ 幻读 ” 。
• InnoDB 存储引擎通过多版本并发控制（ MVCC ）实现此隔离级别。

(4) 串行化（ Serializable ）

• 最高的隔离级别。
• 强制事务串行执行，从而消除了所有并发问题。
• 性能开销最大，因为事务必须等待其他事务完成。
• 防止了“脏读”、“不可重复读”和“幻读”

4、几种锁

• 读锁（共享锁）：多事务同时读取数据，但不能修改
• 写锁（排他锁）：只能由一个事务写，其他事务不能读写
• 行锁：锁定单行。 是基于索引的，如果sql 没有走索引，那将使用表级锁把整张表锁住。
• 表锁：给一个表加锁，用于表结构变更时
• 间隙锁：锁定一个范围(两条物理数据之间的间隔)，防止幻读(事务查询时插入新纪录)

5、一条语句的执行过程

• 初步解析：数据库server接收到SQL语句后，先进行语法分析
• 预处理：权限检查（用户是否有执行该语句的权限）、是否存在引用的表、字段。解析表名和列名
• 优化查询计划：查询优化器生成一个或多个可能的查询执行计划，优化器会评估每个计划的成本（磁盘/CPU/内存），选择成本最低的
• 在这个过程中，可能会使用统计信息（如表的行数、列的分布等）来帮助选择最佳计划。
• 开始执行查询。读取表、索引、连接多个表、对结果进行排序、分组、过滤等。
• 返回数据、响应行数

6、慢查询排查与优化过程

(1) 分析

• 启用慢查询日志  slow_ query_log=ON， 设置慢查询耗时时间阈值  long_query_time = 0.1 (100ms)
• 查看日志找到慢的sql语句， 用explain分析sql
• 打开profiling开关，查看sql语句的耗时情况，分析哪个步骤耗时长

(2) 单表优化

• 优化查询语句，减少复杂度；limit分页
• 使用不等于 != 或者 <> 时无法使用索引，会导致全表扫描；is not null 无法使用索引但 is null 可以
• 如果把  DECIMAL 字段和  INT 字段 Join 在一起，无法用索引，对  STRING 类型 需要有相同的字符集
• 把过滤性最好的字段放在索引顺序的前面
• 范围查询时，把这个字段放在索引次序的最后面
• 索引条件下推

(2) 关联优化

• 分库，分表
• 内连接和左外连接 left join 会对左表全表扫描，右外连接 right join 会对右表全表扫描，所以全表扫描的一方必须是小表
• 用join代替子查询

(3) 索引下推

mysql5.6的优化，将server层的部分工作交给下层引擎层去管理，减少回表次数

(4) 大数据量的优化

• 数据库读写分离
• 冷热分离
• 使用缓存
• 数据库分区partition
• 使用监控工具监控数据库性能