SQL 性能优化,太太太太太太太有用了!

前言本文主要针对的是关系型数据数据库 MySql。
先简单梳理下 Mysql 的基本概念,然后分创建时和查询时这两个阶段的优化展开。
1 基本概念简述

1.1 逻辑架构

SQL 性能优化,太太太太太太太有用了!

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

1.2 锁

数据库通过锁机制来解决并发场景 – 共享锁(读锁)和排他锁(写锁)。读锁是不阻塞的,多个客户端可以在同一时刻读取同一个资源。写锁是排他的,并且会阻塞其他的读锁和写锁。简单提下乐观锁和悲观锁。
乐观锁,通常用于数据竞争不激烈的场景,多读少写,通过版本号和时间戳实现。
悲观锁,通常用于数据竞争激烈的场景,每次操作都会锁定数据。
要锁定数据需要一定的锁策略来配合。
表锁,锁定整张表,开销最小,但是会加剧锁竞争。
行锁,锁定行级别,开销最大,但是可以最大程度的支持并发。
但是 MySql 的存储引擎的真实实现不是简单的行级锁,一般都是实现了多版本并发控制(MVCC)。MVCC 是行级锁的变种,多数情况下避免了加锁操作,开销更低。MVCC 是通过保存数据的某个时间点快照实现的。

1.3 事务

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

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

未提交读(Read UnCommitted),事务中的修改,即使没提交对其他事务也是可见的。事务可能读取未提交的数据,造成脏读。

提交读(Read Committed),一个事务开始时,只能看见已提交的事务所做的修改。事务未提交之前,所做的修改对其他事务是不可见的。也叫不可重复读,同一个事务多次读取同样记录可能不同。

可重复读(RepeatTable Read),同一个事务中多次读取同样的记录结果时结果相同。

可串行化(Serializable),最高隔离级别,强制事务串行执行。

1.4 存储引擎

InnoDB 引擎,最重要,使用最广泛的存储引擎。被用来设计处理大量短期事务,具有高性能和自动崩溃恢复的特性。
MyISAM 引擎,不支持事务和行级锁,崩溃后无法安全恢复。

2 创建时优化

2.1 Schema 和数据类型优化整数
TinyInt,SmallInt,MediumInt,Int,BigInt 使用的存储 8,16,24,32,64 位存储空间。使用 Unsigned 表示不允许负数,可以使正数的上线提高一倍。
实数
Float,Double , 支持近似的浮点运算。
Decimal,用于存储精确的小数。
字符串
VarChar,存储变长的字符串。需要 1 或 2 个额外的字节记录字符串的长度。
Char,定长,适合存储固定长度的字符串,如 MD5 值。
Blob,Text 为了存储很大的数据而设计的。分别采用二进制和字符的方式。
时间类型
DateTime,保存大