【82期】面试中被问到SQL优化

基础概念简述

逻辑架构

  • 第一层：客户端通过连接服务，将要执行的sql指令传输过来
  • 第二层：服务器解析并优化sql，生成最终的执行计划并执行
  • 第三层：存储引擎，负责数据的储存和提取

锁

数据库通过锁机制来解决并发场景-共享锁（读锁）和排他锁（写锁）。读锁是不阻塞的，多个客户端可以在同一时刻读取同一个资源。写锁是排他的，并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。

  • 乐观锁，通常用于数据竞争不激烈的场景，多读少写，通过版本号和时间戳实现。
  • 悲观锁，通常用于数据竞争激烈的场景，每次操作都会锁定数据。

要锁定数据需要一定的锁策略来配合。

  • 表锁，锁定整张表，开销最小，但是会加剧锁竞争。
  • 行锁，锁定行级别，开销最大，但是可以最大程度的支持并发。

但是MySQL的存储引擎的真实实现不是简单的行级锁，一般都是实现了多版本并发控制（MVCC）。MVCC是行级锁的变种，多数情况下避免了加锁操作，开销更低。MVCC是通过保存数据的某个时间点快照实现的。

事务

事务保证一组原子性的操作，要么全部成功，要么全部失败。一旦失败，回滚之前的所有操作。MySQL采用自动提交，如果不是显式的开启一个事务，则每个查询都作为一个事务。

隔离级别控制了一个事务中的修改，哪些在事务内和事务间是可见的。四种常见的隔离级别：

  • 未提交读（Read UnCommitted），事务中的修改，即使没提交对其他事务也是可见的。事务可能读取未提交的数据，造成脏读。
  • 已提交读（Read Committed），一个事务开始时，只能看见已提交的事务所做的修改。事务未提交之前，所做的修改对其他事务是不可见的。也叫不可重复读，同一个事务多次读取同样记录可能不同。
  • 可重复读（RepeatTable Read），同一个事务中多次读取同样的记录结果时结果相同。
  • 可串行化（Serializable），最高隔离级别，强制事务串行执行。

存储引擎

InnoDB引擎，最重要，使用最广泛的存储引擎。被用来设计处理大量短期事务，具有高性能和自动崩溃恢复的特性。

MyISAM引擎，不支持事务和行级锁，崩溃后无法安全恢复。

创建时优化

Schema和数据类型优化

知识精选

相关

标签