MySQL

记录MySQL服务的启停时正确和错误的信息，还记录启动、停止1、运行过程中的错误信息

记录所有执行时间超过long_query_time的所有查询或不使用索引的查询

long_query_time=10 #指定慢查询超时时长（默认10秒），超出此时长的属于慢查询

log_queries_not_useing_indexes=OFF #查询没有使用索引的时候是否也计入慢查询日志

记录了所有对数据库请求的信息，不能正确执行的信息也会进行记录

记录所有更改数据或可能引起更改的语句，可用于数据恢复和复制

使用mysqlbinlog工具进行查看

以事件的形式记录，使用mysqlbinlog工具

概念

作用

构成

redo log 与二进制日志的区别

概念

作用

构成

不支持事务

只支持表锁

数据表和索引表分开（非聚集索引）

MyIASM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针

不支持

一般是非聚集索引

支持事务

提供行锁，表锁

数据表同时也是主索引表（聚集索引）

InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键值

支持外键

主键索引是聚集索引

聚集索引

辅助索引（非聚集索引）

没有任何限制（允许重复与空值），唯一目的就是加快系统对数据的访问速度

创建关键字时INDEX或KEY

创建唯一索引的目的不是为了提高访问速度，而是为了避免数据出现重复

不允许重复值，允许为null

创建关键字时UNION

主键索引就是专门为主键字段创建的索引，不允许重复值和null

创建关键字时PRIMARY KEY

全文索引主要用来查找文本中的关键字，只能在CHAR、VARCHAR、或TEXT类型的列上创建

允许有重复值和null

创建关键字时FULLTEXT

单列索引就是索引只包含原表的一个列，在表中的单个字段上创建索引，单列索引只根据该字段进行索引

多列索引时在表的多个字段上创建一个索引，该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中的第一个字段时，索引才会被使用

索引往往以索引文件的形式存在于磁盘上

而B+树的每个节点只存放索引信息，B树的节点还需要额外存放关键字具体信息地址指针

因此同样一次IO，在读入相同大小的情况下，B+树可以查找更多的关键字

B+树的叶子节点包含所有的关键字，并以有序链表结构存储，基于范围查询的效果更好，同时也利用了磁盘预读的特性（B+树扫库只要扫叶子节点，B树扫库要进行中序遍历）

B+树每次查询的时间复杂度都是logn,而B树的复杂度是不稳定的

平衡树的查找时间复杂度是和树高直接相关的，红黑树的出度为2，B+树的出度非常高，一般B+树的高度都在2-4层

数据库系统将索引的一个节点大小设置成一个页的大小，使得每次IO都可以完全载入一个节点，节省IO次数，同时相邻的节点也可以被预先载入

对主键的排序查找和范围查找的速度非常快，一方面数据页有序且用指针连通，另一方面不需要再去查询数据页，而是直接获取

首先遍历辅助索引，并通过叶子节点，获得指向对应主键索引的指针

然后在通过主键索引来找到一个完整的行记录

也就是要查两次

memory引擎可以不要求键值唯一，需要先找到键值所在链表，然后往后扫

在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关键数组实现，其拥有两种表现形式

总结

1、占用存储空间更大，如果内存一次性装不下全部索引，性能会非常差

2、增删改代价更大，修改文本中10各单词，则要操作维护索引10次，而不是普通索引的一次

3、如果一个列上有全文索引则一定会用上，即使有性能更好的其他索引也不会用上。由于只是存储文档指针，也就用不上索引覆盖

1、索引可以将随机IO变成顺序IO，可以大大提高系统的性能

2、通过创建唯一性索引，可以保证数据库中每一行数据的唯一性

3、索引大大减小了服务器需要扫描的数据量，提高数据的检索速度

4、可以加速表与表的连接

5、创建索引可以帮助服务器避免排序和临时表，在使用分组和排序子句进行检索的时候，可以显著减少时间

1、创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增加

2、索引需要占用物理空间，如果是聚集索引，占用空间更大

3、对表中的数据进行增加、删除和修改的时候，索引也要动态维护，降低了维护的速度

1、经常需要搜索的列

2、主键列

3、经常需要连接的列

4、经常需要范围搜索的列

5、经常需要排序的列

1、查询很少的列

2、不重复很少的列（比如性别）

3、频繁修改更新的列

4、定义为text，image的列

查看慢查询日志

使用explain进行分析

减少请求的数据量

减少服务器扫描的行数

切分大查询

分解大连接查询

还有一种情况如下：（A,B,C）建索引，如果任意一个字段使用了范围搜索（>,<,等）那么这个字段之后的索引是失效的

例如：select * from test where id-1=9

种类

特点

引擎默认锁

种类

特点

引擎默认锁

开销、加锁、粒度介于表锁和行锁之间

概念

具体流程（版本号实现)

应用场景

概念

具体流程

具体场景

当前写操作没完成前，会阻塞其他读和写操作（update、insert、delect）

针对同一份数据，多个读操作可以同时进行而不会相互影响（select）

在没有意向锁的情况下，事务T想对表A加排它锁（X锁），就要先检测是否有其他事务对表A或者表A的任意一行加锁，因此就需要对表A的每一行进行检测

在引入意向锁之后，一个事务如果先获得某个行的S锁，就需要先获得表的IS锁；一个事务如果先获得某个行的X锁，就需要先获得表的IX锁，不需要遍历每行进行检测

记录锁，单个行记录上的锁（注意，record lock锁的是索引，而不是数据本身）

间隙锁，锁定一个范围，但不包含记录本身

当使用范围条件查询的时候然后更新的时候，而不是等值查询的时候，就会给符合条件的范围索引加锁

普通索引加锁

唯一索引加锁

临键锁（Record Lock+Gap Lock），锁定一个范围，并锁定记录本身

InnoDB对行的查询都是采用next-key lock

如果索引具有唯一性，就会降级为Record Lock

select(读锁)

insert、update、delete（写锁）

lock table tableName read

lock table tableName write

unlock table tableName #单表

unlock tables #所有表

select(不自动加锁)

insert、update、delete（自动加排它锁）

select * from user lock in share mode 共享锁

select * from user for update 排它锁

提交事务（commit）

回滚事务（rollback）

kill阻塞进程

table_lock_waits #出现竞争等待次数

table_locks_immediate #产生表锁的次数

innodb_row_lock_current_waits #当前正在等待锁定的数量

innodb_row_lock_times #从启动到现在锁定的时间总长度

innodb_row_lock_time_avg #每次等待平均所花时间

innodb_row_lock_waits #系统启动到现在总共等待次数

innldb_lock_waits表

innodb_locks表

innodb_trx表

show engine innodb status \G

innodb_lock_wait_timeout默认是50s

wait for graph

show processlist

redo log叫做重做日志，是用来实现事务的持久性

redo log buffer

redo log file

mysql为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool(缓存池)里头，把这个当作缓存来用，然后使用后台线程去做缓冲池和磁盘之间的同步

那么问题来了，如果还没来及同步的时候宕机或者断电了怎么办，还没来得及执行持久化操作，这样会导致丢失部分已提交事务的修改信息

所以引入了redo log来记录已成功提交事务的修改信息，冰球会把redo log持久化到磁盘，系统重启之后在读取redo log恢复更新数据

而binlog在每次事务提交之后，是记录到缓冲池的，然后在异步写道磁盘中去，因此不能保证事务的持久性

作用不同

层次不同

内容不同

写入时机不同

undo log 叫做回滚日志，用于保障未提交事务的原子性，同时也是隔离性实现的基础

undo log属于逻辑日志，它记录的是sql执行相关的信息，当发生回滚时，InnoDB会根据undo log的内容作与之前相反的工作，对于每个insert 回滚时会执行delete，回滚时会执行delete；对于每个delete，回滚是也会执行insert；对于每个update，回滚时会执行一个相反的update，把数据该回去

InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log，如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子

像select lock in share mode(共享锁)，select for update;update,insert,delete(排它锁)这些操作都是一种当前读，为什么叫当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提时隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC，可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读，而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

就是在快照读的情况下不需要加锁解决读写冲突

系统版本号

事务版本号

创建版本号

删除版本号

MVCC使用的快照存储在undo日志里面，这个日志通过回滚指针把一个数据行的所有快照连接起来

当开始一个新的事务的时候，该事务的版本号一定会大于当前所有数据行快照的创建版本号

将当前系统版本号作为数据行快照的创建版本号

将当前系统版本号作为数据行快照的删除版本号

原则

实现

相当于先进性delete,后进行insert

先将更新前的数据行快照的删除版本号改为当前的系统版本号

然后更新后的数据行快照的创建版本号改为当前的系统版本号

参与角色

流程

存在问题

流程

优缺点

子主题 1

主库log dump线程

从库I/O线程

从库SQL线程

master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中

slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件

同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒

冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库，冷备份时将关键性文件拷贝到另外位置的一种说法。对于备份数据库信息而言，冷备份是最快最安全的方法

1.是非常快速的备份方法（只需拷贝文件）

2.容易归档（简单拷贝即可）

3.容易恢复到某个时间点上（只需将文件再拷贝回去）

4.能与归档方法相结合，做数据库“最新状态”的恢复

5.低度维护，高度安全

1.单独使用时，只能提供到“某一时间点上”的恢复

2.在实施备份的全过程中，数据库必须要做备份而不能做其他工作。也就是说，在冷备份过程中，数据库必须是关闭的

3.若磁盘空间有限，只能拷贝到磁带等其他外部存储设备上，速度会很慢

4.不能按表或者按用户恢复

热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一边备份的sql语句

1.可以在表空间或数据文件备份，备份时间段

2.备份时数据库仍可使用

3.可达到秒级恢复（恢复到某一时间上）

4.可对几乎所有数据库实体做恢复

5.恢复时快速的，在大多数情况下在数据库仍工作时恢复

1.不能出错，否则后果严重

2.若热备份不成功，所得结果不可用于时间点的恢复

3.困难于维护，所以要特别仔细小心，不允许“以失败告终”

根据业务耦合性，将关联度低的不同表存储在不同的数据库

例如电商项目中，商品库、订单库

垂直分表时基于数据库的“列”进行，某个表字段较多，可以新建一张扩展表，将不经常用或者字段长度较大的字段拆分出去到扩展表中

优点

缺点

表内数据内在逻辑关系，将同一个表按不同的条件分散到多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小

表内数据内在逻辑关系，将同一个表按不同的条件分散到多个数据库中，每个表中只包含一部分数据，从而使得单个表的数据量变小

优点

缺点

基于XA协议的两阶段提交

基于XA协议的三阶段提交

基于消息中间件的最终一致性

将原来的连接分为多个单表连接，然后在用户程序中进行连接

UUID

Snowflake雪花算法