MySQL

架构

1. 连接器：连接处理、授权认证
2. 缓存(x)：有缓存则直接返回(select) 注意：因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
3. 分析器：对查询语句进行语法分析和词法分析和语义分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器
4. 优化器：决定表的读取顺序、选择合适的索引
5. 执行器：开始执行语句进行查询比对，直到查询到满足条件的所有数据，然后进行返回

引擎种类

1. InnoDB：支持事务；
2. MyIsAM：支持全文索引、压缩表(导入数据后不能再进行修改)和空间函数；不支持事务和行级锁(对整张表加锁，并发低)；崩溃后无法安全恢复
3. MEMORY：数据存放在内存；不支持事务

并发控制

读写锁

也称共享锁和排他锁，可一起读，但不能一起写和一起读写

锁粒度

1. 表锁：锁粒度大，并发程度小，资源消耗小
2. 行锁：锁粒度小，并发程度高，资源消耗大

InnoDB解决死锁的方式：将持有最少行级写锁的事务进行回滚

事务

隔离级别

1. 未提交读：读到另一事务中未提交的数据，会出现脏读、不可重复读、幻读
2. 提交读：读到另一事务中已提交的数据，会出现不可重复读、幻读
3. 可重复读：多次读取同一记录，结果一样，会出现幻读，MySQL使用MVCC(快照读)和next-key锁(当前读)解决了幻读
4. 可串行化：强制事务串行执行，会在读取的每一行数据上都加锁

脏读：读到未提交的数据；不可重复读：多次读到的数据不一致，可能被修改；幻读：多次读到的数据不一致，可能插入新行

InnoDB 可以通过 Next-Key 锁 +MVCC 来解决幻读问题：
先介绍下 InnoDB 三种行锁的方式：

1. 记录锁：针对单个行记录添加锁。
2. 间隙锁（Gap Locking）：可以帮我们锁住一个范围（索引之间的空隙），但不包括记录本身。采用间隙锁的方式可以防止幻读情况的产生。
3. Next-Key 锁：帮我们锁住一个范围，同时锁定记录本身，相当于间隙锁 + 记录锁，可以解决幻读的问题。

在隔离级别为可重复读时，InnoDB 会采用 Next-Key 锁的机制，帮我们解决幻读问题。

事务日志

MVCC

MVCC 的核心就是 Undo Log+ Read View

MySQL中的行级锁不是简单的行级锁，而是MVCC，它实现了在很多情况下避免加锁操作，因此开销更低，大都实现了非阻塞的读操作，写操作也只锁定必要的行
其实现是通过保存数据在某个时间点的快照来实现的

行记录的隐藏列：

InnoDB 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段：

1. db_row_id：隐藏的行 ID，用来生成默认聚集索引。如果我们创建数据表的时候没有指定聚集索引，这时 InnoDB 就会用这个隐藏 ID 来创建聚集索引。采用聚集索引的方式可以提升数据的查找效率。
2. db_trx_id：操作这个数据的事务 ID，也就是最后一个对该数据进行插入或更新的事务 ID。
3. db_roll_ptr：回滚指针，也就是指向这个记录的 Undo Log 信息。

InnoDB 将行记录快照保存在了 Undo Log 里，我们可以在回滚段中找到它们，如下图所示：

索引

索引逻辑类别

1. 主键索引：列值是唯一的且不为NULL。主键最好是和应用无关的，如最好不要定义为uuid，最简单的方法就是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入的，对于根据主键做关联操作的性能也会更好
2. 唯一索引：相对于主键索引，就是其值可以为NULL；问题：对于索引列为NULL的一条数据可多次插入，解决方案：给字段设置空字符串初始值，NOT NULL DEFAULT ‘‘即可，不要用null值作为初始值。
3. 普通索引：相对唯一索引，就是其值可以重复
4. 全文索引：MyISAM引擎，是一种通过建立倒排索引，快速匹配文档的方式
5. 哈希索引：MEMEORY引擎，InnoDB的自适应哈希索引。基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，都会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针
6. 空间索引：MyISAM，可用作地理数据存储，其可从所有维度来索引数据

前缀索引：对于BLOG、TEXT或很长的VARCHAR类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的全部内容
覆盖索引：如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为“覆盖索引”

索引物理类别

1. 聚簇索引：索引和数据存放在一起，InnoDB的聚簇索引通常为主键索引，若没有主键则会选择一个唯一索引，若没有唯一索引，则会隐式定义一个主键
2. 非聚簇索引：索引和数据分别存放

索引失效

1. 索引是表达式的一部分
2. 索引是函数的参数
3. 未满足联合索引的最左前缀匹配规则
4. 遇到范围查询，其之后的索引列失效
5. 如果条件中有or, 即使条件中存在索引也不会使用索引,如果既想使用or,又想使用索引, 就给所有or条件控制的列加上索引
6. 使用like查询时, 如果以%开头,肯定是进行全表扫描；如果%在条件后面，对于主键索引, 索引失效，对于普通索引, 索引不失效
7. 如果列的类型是字符串类型, 那么一定要在条件中将数据用引号引起来,不然也会是索引失效
8. 索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效
9. 如果mysql认为全表扫描比用索引块, 同样不会使用索引

表空间

MySQL用表空间来组织数据，表空间就是磁盘上的文件

• 共享表空间：指的是数据库的所有的表数据，索引文件全部放在一个表空间中。

  • 优点：表空间可以分成多个文件存放到各个磁盘，所以表也就可以分成多个文件存放在磁盘上，表的大小不受磁盘大小的限制
  • 缺点：共享表空间分配后不能回缩，删除数据后会有空隙

• 独立表空间：每一个表都将会生成以独立的文件方式来进行存储。

  • 优点：将单个表复制到另一个实例会很方便； 当表被删除时这部分空间可以被回收
  • 缺点：单表增加过大，当单表占用空间过大时，存储空间不足；mysqld会维持很多文件句柄，表太多会影响性能；如果很多表都增长会导致碎片问题

日志

undo log

记录着事务执行之前的数据，用来回滚数据

redo log

记录着事务提交成功的操作，用来持久化恢复数据

bin-log

MySQL主从模式下的同步通过bin-log实现

优化

1. 慢查询：分析慢查询日志
2. EXPLAIN：查看语句的执行计划
3. show profile：查看每一个步骤的时间成本
4. 通过以上步骤分析是查询时间长还是等待时间长
   1. 查询时间长：缓存、索引、分库分表、读写分离
   2. 等待时间长：增加数据库连接缓冲池

其他

范式与反范式的优缺

1. 范式的更新操作较快，只需修改少量数据且表较小
2. 范式化设计的查询通常要关联多张表，反范式不需要关联表

现实中通常是两者结合，最常见的反范式化数据的方法是复制或缓存(可使用触发器来更新缓存值)，在不同的表中存储相同的特定列