MySQL全景

一、MySQL架构

1.1 MySQL架构

存储引擎架构：将查询处理与各类服务器任务和数据存储分离。
即服务器负责各类任务，硬盘负责存储，存储引擎负责二者交互。

连接器：每个client对应一个线程，server会缓存。(我们使用的连接池)
解析器：解析sql语句，构建解析树
优化器：决定读表的顺序，选择索引(选择执行计划时，会访问存储引擎，请求引擎提供某个具体操作的性能和具体的开销)

1.2 并发控制

使用锁来实现并发控制。

锁分为读锁(共享锁)和写锁(排它锁)

按粒度：全局锁、表级锁、行锁、间隙锁

• 全局锁：对整个数据库实例加锁，数据库只读，命令为 flush tables with read lock
• 表级锁：表级锁又分为表锁和元数据锁。
  表锁锁住整张表
  元数据锁(MDL)不需要显示使用，而是访问一个表之前就会自动获取,MDL保证了读写的正确性，对一个表进行增删改查之前需要获取读锁，修改表结构需要获取写锁。
• 行锁：锁某行
• 间隙锁：锁住某几条记录之间的间隙

InnoDB使用二段锁协议：即事务在执行的任何阶段都可以获得锁，但只有在执行commit或callback后，才可以释放锁。

InnoDB处理死锁的方式：回滚拥有最少排他行锁的事务。

1.3 MVCC

MVCC与行锁共同保障隔离。
MVCC工作于可重复读和读已提交级别

原理是：保存某些时刻的数据快照。

实现：为每行添加隐藏字段，删除并不是真正的删除，而是使用隐藏字段形成一些版本列表。

1.4 事务与隔离级别

特性：ACID
隔离级别：读未提交、读已提交、可重复读、串行化

读已提交和可重复读都使用了MVCC的机制，区别在于MVCC生成可读视图的时机不同，可重复读是在第一次读取数据时生成视图，后面的读取都是读取该视图，所以可以重复读。而读已提交则是每次读取都是生成新的读视图，所以不可重复读。

Mysql默认是自动提交，这样若非显示的开启事务，每一个查询都被默认为一个单独的事务，同时有些命令在当前事务中执行时，即便没有执行到commit，也会造成事务的提交。

MySQL在执行事务时，会先写日志，日志写入成功再写数据。
写日志的操作比较快速，为何快速呢？这是因为写日志仅涉及磁盘上很小区域的顺序IO，而不是大量的随机IO。

1.5 索引

仅针对innoDB存储引擎。
索引：分为B树索引、B+树索引和哈希索引。

• B+树索引：
  分为聚簇索引和非聚簇索引，InnoDB将索引和数据存储在同一个文件中，MyISAM引擎则是索引和数据分别存放在两个文件中。聚簇索引其实就是我们的表，是一棵B+树，树的叶子节点存储我们的行数据，非叶子节点则是索引，使用主键作为索引键。适用于匹配全名、匹配最左前缀、匹配列前缀、匹配范围值、覆盖索引(减少回表操作)
• 哈希索引：适用于精确查找，存在哈希冲突，使用拉链法解决。而且不能用于排序，不支持列前缀的匹配，因为算出的hash值是不同。同时范围查询的速度会很慢，因为哈希键会很分散，所以只适用于等值查询
• B树索引：类似于B+树索引，只是非叶子节点也会存储数据，造成树偏高，访问数据时，访问数据库的次数会增多。

InnoDB还拥有一种自适应hash索引，当InnoDB察觉到某些索引被频繁的访问，就会在B+树的顶端为这些索引建立内存中的哈希索引，提高访问效率。
InnoDB主要使用了B+树索引，为什么呢？
因为B+树相比B树，树高很低，降低了读磁盘的次数，查询和更新的效率都比较好。

二、架构优化

2.1 选择合适的数据类型

原则：

• 更小通常更好：使用能正确地存储和表示数据的最小类型，更小的数据类型通常更快，因为它们占用更小的磁盘空间、内存空间和cpu缓存，cpu可以处理的更快。
• 简单：越简单的数据类型，cpu处理所需要的时间就越少。(例如：整数的比较所需的时间少于比较字符串)
• 尽量避免NULL：即非必要，每一列都定义为NOT NULL。因为含NULL的列会使得空间索引失效。

数据类型间的比较

• 整数：整数类型有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。分别占据8、16、24、32、64字节。 整形的运算通常使用64位的BIGINT。
• 实数：double、float、decimal，float使用4字节，double使用8字节，decimal仅仅只是一种存储格式，在计算时会转换为double。
• 字符串：char与varchar。
  varchar是变长字符串，使用额外的1到2字节存储值得长度。char则是定长字符串，每一个字符串都占用相同的空间。varchar能节约一些存储空间，但是当更新时需要进行额外的工作，当最大长度远大于平均长度且很少发生更新时，比较适合用varchar。
  char 是定长的。适合用于字符串长度很短或者长度近似的情况。

tips：
主键的类型选择：不仅要考虑存储的类型，还要考虑MySQL如何对它们进行计算和比较。所以，要小心隐型的类型转换导致难以察觉的错误。整数类型通常是主键的最佳选择，不仅速度快，还支持自增操作。而字符串类型则尽量避免被用作主键，因为占用更多的空间且运算比整形更慢。

2.2 索引优化

• 隔离列：将索引列隔离出来，即该列不是表达式的一部分，也没有位于函数中。
• 前缀索引：当某列的数据都是很长的字符串，这时候为该列创建索引，会使得索引很大，查询效率比较慢，此时可以为该列数据的前缀创建索引。前缀索引不能用于order by或者group by 且不能当作覆盖索引。
• 覆盖索引：在innoDB中，非聚簇索引对应的B+树，叶子节点只会存储索引列的数据和一个指向聚簇索引对应行的指针，所以，如果我们查找的数据刚好仅仅是该索引列对应的值，则可以直接获取，而不用再通过指针进行回表，去聚簇索引获取，这样可以减少IO，提高效率。而且索引中数据是按照该索引列的键顺序排列的，不会产生大量的随机读取。
• 注意通配符开头会导致索引失效
• 注意不要建立重复索引：例如某一列如果声明为主键，则不需要再添加UNIQUE约束，因为MySQL是通过索引来实现这些约束的，如果某一列同时声明这两个约束，会产生两个相同的索引。
• 注意多余索引：创建新的索引时，优先考虑在现有的索引上进行扩展，例如在列A上创建了一个索引，如果我们想给A,B两列加索引，优先考虑扩展定义在A列上的索引，而不是重新建。注意，如果想创建(B,A)索引，则此时索引(A)不是多余的。
• 需要注意行锁是在引擎级别实现的，所以要尽可能在引擎级别使用索引过滤掉不需要的行，如果不可以，那么只能在这些数据返回到服务器级别后才能通过where子句进行过滤，此时这些数据行已经被锁定，只有事务提交后才能被释放。（使用explain查看查询计划，如果extra字段显示using where，则表示在引擎级别过滤后，在服务器级别也使用where子句进行了过滤）
• 避免多个范围查询，索引遇到范围查询后，后面的索引就不会再生效
• 如果想使用多个范围查询，则可以将一些范围查询转换为IN()列表，例如想查询年龄在18-25之间且最近5天上线的人，则可以在表中定义一个字段，最近5年上线，该字段为1，否则为0，则可以将最近5天上线这个范围查询用where xxx in (1)来实现。

三、SQL的执行过程

3.1 SQL语句执行的一般性过程

1. Client将查询发送到Server
2. Server查询缓存，命中则直接返回(现在的MySQL好像删除了缓存)
3. 解析器解析SQL语句
4. 优化器优化，生成执行计划
5. 执行引擎调用存储引擎API执行查询
6. Server返回结果

Client与Server进行交互：

交互的协议为半双工协议。所以，双方在通信时只能一方发送一方接收，如果客户端只需要服务端返回结果的某几行，那么只能等待所有数据接收完再丢弃或者直接断开连接，但这两种都不好，所以这就是为什么要使用limit关键字。

解析过程：

解析器将查询分解为一个个的标识(Token)，然后构造一棵“解析树”。 解析器会对其进行语法解析以及验证查询，保证每个标识都是有效的，且出现的位置是合理的。

然后由预处理器检查解析树生成的结果树，检查语义是否正确，如检查表和列是否存在，检查名字和别名等。确保语义没有错误。最后检查权限，是否有权执行该语句。

优化器优化：

优化器负责将解析树变成执行计划。一般sql语句会有多种执行方式，优化器的作用就是找出最好的方式，虽然并不总是能找到最好的。

优化器有两种基本的优化方式：静态优化和动态优化。

• 静态优化：可以简单地通过探测解析树来完成，例如可以通过代数化法则将where子句转换为等价的代数形式，这种一般只优化一次，且始终有效，即使用不同的参数重新执行查询计划也不会改变，类似于编译时优化。
• 动态优化：和上下文有关，每次执行都会重新执行优化，类似于运行时优化。

一些优化类型：

• 对联接的表重新排序：表并不总是按照查询中定义的顺序进行联接，优化器会优化联接顺序
• 将外联接转换为内联接：在可以将外联接转为等价的内联接的时候，进行转换。
• 代数等价法则：使用代数化转换来简化和规范化表达式。
• 优化COUNT()、min()、max()
• 计算和减少常量表达式：将可以转换为常量的表达式转换为常量。
• 覆盖索引：当索引包含查询需要的所有列时，可以使用索引来避免读取行数据。
• 子查询优化：将某些子查询转为等价的效率更高的形式。
• 早期终结：一旦达到满足某个查询或者某个步骤的条件，就会立即停止处理该查询，例如limit。
• 相等传递
• 排序优化：如果要排序的值得数量内存放得下，则在内存进行快排，如果不能则分块，对每块进行快排，最后合并所有的结果。(如何合并呢？)

执行计划：

MySQL的执行计划是树形结构。采用的是左深度树的形式。

注意：

• 联合的原理，即嵌套循环，MySQL会把联合查询，当作一个个的单表查询，首先查找第一个表，通过嵌套循环从下一个表匹配数据。
• MySQL会把所有的查询都转换成联接来执行，单表查询即单重循环。
• MySQL不支持全外联接是因为全外联接无法转换为嵌套循环。
• where子句的执行时机：
  • 对索引查找应用where子句来消除不匹配的行，发生在存储引擎层
  • 使用覆盖索引来避免访问行，并且从索引取得数据后过滤掉不匹配的行，发生在服务器层
  • 从表中检索出数据，然后过滤掉不匹配的行，发生在服务器层

流程图

3.2 查询

select * from T where ID=10

1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

3.3 更新

更新语句与查询语句经过步骤类似，不过多了两个写日志的环节，分别是写redo Log和bin log。

update T set c=c+1 where ID=2;

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

流程图

3.4 其他子句&关键字

四、SQL性能优化

4.1 查询相关

基本原则

查询的基本原则：优化数据访问。

• 查明应用程序是否在获取超过需要的数据。
• 查明服务器是否分析了超过需要的行。

检查MySQL是否检查了太多的数据可以看执行时间、检查的行数、返回的行数这三个最基本的指标。

重构查询语句

• 将复杂查询分解为多个简单查询
• 缩短查询：分治法，让查询在逻辑不变的条件下，每次只处理一部分。
• 分解联接：将一个多表联接查询分解为多个单个查询，然后在应用程序中实现联接。

特定类型查询优化

1. count的优化：count用于统计值得数量或者统计行的数量，传入一个列名或其他表达式，会统计该列或者该表达式非Null的次数。当MySQL知道括号中的表达式永远不为NULL的情况下，就会统计行的个数。优化的手段很少，可以选择覆盖索引，或者更改sql语句。
2. 优化联接：
   • 确保on或using使用的列上有索引，并且在添加索引时考虑联接的顺序，比如联接表A和B使用了列c，且联接顺序为从B到A的联接，则不要在B上添加索引。
   • 确保group by 或order by只引用一个表中的列。
3. 优化group by和distinct：索引
4. 优化limit和offset：当偏移量很大，而要取得数据量很小时，如果不优化也会造成很大的性能浪费，例如limit 10000,20。这会让mysql取出10020行数据，然后抛弃前10000行。优化的方式是可以在覆盖索引上进行偏移，而不是对全行数据进行偏移，即使用联接，先通过索引查出需要的行，然后再通过id或者索引键进行联接。
5. 优化UNION：始终要使用UNION ALL，除非需要服务器消除重复的行。