mysql常用笔记

引擎

myisam

        5.5.8版本前默认的储存引擎，适合读多写少的表，支持表锁，支持全文索引，会储存表的总行数，不支持事务，不支持行锁，一个表有储存成三个文件，结构(frm），数据(myd)，索引(myi）。索引结构默认使用b+Tree类型

innodb

        5.5.8版本后默认的储存引擎，适合需要数据一致性要求高的表，支持行锁，支持外键，支持事务，支持自动灾难恢复，不储存总行数，一个表有两个文件，数据，结构。索引结构默认使用b+Tree类型

索引

介绍

        索引其实是一种数据结构，可以帮助我们快速检索数据库的数据。

索引的数据结构

        介绍：常用的有B+tree和hash结构。myisam和innodb引擎默认的是B+tree结构，也只支持B+tree（但是innoDB存储引擎支持hash索引是自适应的，innoDB存储引擎会根据表的使用情况自动为表生成hash索引，不能人为干预是否在一张表中生成hash索引。)
        Hash：是以key-value储存数据的结构，数据结构上是没有顺序的，所以适合等值查询，对于区间查询无法直接通过索引查询，需要全表扫描，也无法通过索引进行排序，并且不支持联合索引的最左匹配原则

        B+tree：是一种多路平衡查询树，节点是有序的(左子节点小于父节点，父节点小于右子节点)，因为是有序的，所以对于范围查询不需要全表扫描。引擎不同结构也会不同。

        在innodb中，表数据本身就是按照B+树结构进行储存的，叶子节点存放的是整行数据，key就是表的主键，所以innodb表必须有一个主键，如果建表的时候指定了主键，那就用这个，没指定就会选择第一个非空唯一索引做主键，如果两个都没有，innodb会建立一个隐藏的主键列(字段长度为6个字节，类型为长整形)，这就是常说的主键(聚集｜聚簇)索引。再之后建立的索引也是按照B+树形式存储，不同的是叶子节点存的是主键的key值和索引的key值，这是非主键(非聚集｜非聚簇｜辅助)索引。主键索引因为叶子节点存的是整行数据，所以查到数据后可以直接返回。非主键索引会分为两种情况，一种是当有查询列不在索引中时会进行回表操作，也就是根据非主键索引叶子节点的主键id再去主键索引那取出数据，另一种是当查询列都在索引中时直接返回数据，这也就是我们说的覆盖索引。

        在myisam中叶子节点存放的是数据文件中对应记录的地址，节点上只有这个索引列的值。辅助索引和主索引在结构上没有区别，只是主索引要求key是唯一的，辅助索引可以重复。

索引类型

【普通索引】只为查询更快一点，没别的限制
【主键索引】值是唯一的，且不允许有空值
【唯一索引】值是唯一的，允许有空值
【联合索引】包含多个列，需遵循最左前缀
【全文索引】myisam支持，5.7后innodb开始支持全文索引

索引下推

        5.6版本对索引引入了索引下推，默认开启，例如表中有联合索引name,age，查询条件为name like ‘张%’ and age = 20，未开启索引下推则查询到在辅助索引中第一个字为张的主键值后，再根据主键到聚集索引中查询并判断age(回表)，符合age=20的返回记录，开启了索引下推则在辅助索引中查询第一个字为张的主键值后，因为age在也在索引中，会顺便判断一下age是否等于20，等于20的再回表查询

事务

【基本要素】

　　原子性，一致性，隔离性，持久性
【并发问题】
　　脏读：事务A读取了事务B更新的数据，然后B回滚了，那么A读取的数据就是脏数据
　　不可重复读：事务A第一次查询数据后，事务B修改并提交了数据，之后事务A第二次查询发现数据结果不一致
　　幻读：事务A第一次查询数据后，事务B插入或删除了一条符合事务A查询的数据，之后事务A再一次查询发现条数不一样了
【隔离级别】
　　读未提交：最低级别，一般不用
　　读提交：可解决脏读
　　可重复读：默认，可解决脏读，不可重复读，使用间隙锁可解决幻读。
　　串行化：可解决脏读，不可重复读，幻读，但是并发能力太差，一般不用
【锁】
　　表锁：开销小，加锁快，不会出现死锁，锁冲突概率大，并发能力最差

　　行锁：开销大，加锁慢，会出现死锁，锁冲突概率小，并发能力最高，主要用于innodb

　　页锁：开销中等，加锁中等，会出现死锁，并发度一般

　　myisam表锁：分为表共享读锁、和表独占写锁。也就是读操作不会堵塞其他用户对同一表的读操作，但会堵塞写操作。另外，写操作会堵塞其他用户对同一表的读和写操作。myisam执行select前，会自动加读锁。执行update｜delete｜insert前会自动加写操作。concurrent_insert=2允许在表尾并发插入记录

　　innodb行锁：分为共享锁和排他锁，为了让行锁和表锁共存，innodb也加入了意向锁概念，也就是意向共享锁和意向排他锁。当一个事务需要给某个资源加锁的时候，如果别的共享锁也在锁定自己需要的资源，那么自己可以再加一个共享锁，但不能加排他锁。如果遇见的是排他锁，那么就需要等待资源释放才可以加共享锁或者排他锁。意向锁的作用是当事务需要给资源加锁时，如果遇到别的事务已经加了排他锁，那么自己可以在需要锁定行的表上面加一个意向共享锁或者意向排他锁，但是意向共享锁可以是多个，意向排他锁只能是一个。意向锁是innodb自动加的，不需要用户操作。而对于update｜delete｜insert操作innodb会自动加排他锁，select则需要用户自己指定锁，共享锁lock in share mode，排他锁for update。innodb是通过在索引上加锁实现的行锁，所以只有通过索引操作数据才会加行锁，要不然会加表锁

　　死锁：两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源

　　间隙锁：如果使用范围索引查询数据时，innodb会将这个范围加锁，同时也包括了在范围内但不存在的数据，这些数据就是间隙，间隙锁就是给这些间隙也加上了锁，可以防止幻读

MVCC

【介绍】　　
　　多版本并发控制，主要是为了提高数据库的并发性能，更好的处理读写冲突。最早的数据库系统只有读读之间可以并行，读写、写写之间都会阻塞；引入了多版本后，只有写写会阻塞，其他操作都可以并行。
【当前读】
　　读取的记录都是目前数据库的最新版本，读取时要保证其他并发事务不能修改当前记录，所以会对读取数据加锁。例如select lock in share mode，select for update，update，insert，delete
【快照读】
　　读取的记录不一定是目前数据库的最新版本，有可能是历史版本。例如不加锁的select，前提是隔离级别不能是串行级别，串行级别下快照读会退化成当前读
【实现原理】
　　主要依赖row中额外的隐式字段，undo日志，Read View来实现
　　隐式字段
　　　　db_row_id：6byte，隐含的自增ID（隐藏主键）。如果数据表没有主键，InnoDB会自动以db_row_id生成一个主键索引。
　　　　db_trx_id：6byte，最近修改（插入）事务ID：记录创建这条记录/最后一次修改该记录的事务ID。
　　　　db_roll_ptr：7byte，指向这条记录的上一个版本。
　　　　deleted_bit：1byte，记录是否被删除，并不代表真的删除，相当于记录一次逻辑删除，真正删除是在commit的时候
　　事务ID
　　　　当我们开启事务时，事务ID并没有获得，执行select也不会获得，只有在执行insert/update/delete语句时才能获得事务ID。事务ID是自增的。所以事务越新，事务ID越大。
　　undo日志
　　　　通过undo日志可以找到数据库的历史版本，找到后就可以提供给用户读
　　Read View
　　　　事务进行快照读时产生，在事务进行快照读时会生成当前数据库的一个快照，记录并维护当前数据库活跃的事务ID。

　　　　　　主要内容：

1. 1. m_ids：表示在生成ReadView时，系统中活跃的事务id集合。
   2. min_trx_id：表示在生成ReadView时，系统中活跃的最小事务id，也就是 m_ids中的最小值。
   3. max_trx_id：表示在生成ReadView时，系统应该分配给下一个事务的id。
   4. creator_trx_id：表示生成该ReadView的事务id。

　　　　　　判断方式：

1. 1. 如果被访问的版本的db_trx_id和ReadView中的creator_trx_id相同，就意味着当前版本就是由你“造成”的，可以读出来。
   2. 如果被访问的版本的db_trx_id小于ReadView中的min_trx_id，表示生成该版本的事务在创建ReadView的时候，已经提交了，所以该版本可以读出来。
   3. 如果被访问版本的db_trx_id大于或等于ReadView中的max_trx_id值，说明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被读出来。
   4. 如果生成被访问版本的db_trx_id在min_trx_id和max_trx_id之间，那就需要判断下trx_id在不在m_ids中：如果在，说明创建ReadView的时候，生成该版本的事务还是活跃的（没有被提交），该版本不可以被读出来；如果不在，说明创建ReadView的时候，生成该版本的事务已经被提交了，该版本可以被读出来。

　　　　如果某个数据的最新版本不可以被读出来，就会依据db_roll_ptr找到上一个版本进行判断，如果第一个版本也不可见的话，代表此数据对当前事务不可见，结果就不包含此记录

　　读提交和可重复读差别

　　　　读提交：每次快照读都会新生成一个快照和Read View

　　　　不可重复读：只有第一次快照读会创建一个快照及Read View，此后在调用快照读的时候，还是使用的是同一个Read View

日志

【回滚日志（undo log）】可以保证数据的原子性
　　假设有A、B两个数据，值分别为1,2。
　　　　A.事务开始.
　　　　B.记录A=1到undo log.
　　　　C.修改A=3.
　　　　D.记录B=2到undo log.
　　　　E.修改B=4.
　　　　F.将undo log写到磁盘。
　　　　G.将数据写到磁盘。
　　　　H.事务提交
　　特点
　　　　A. 更新数据前记录Undo log。
　　　　B. 为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。
　　　　C. Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的，可以用来回滚事务。
　　　　D. 如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

【重做日志（redo log）】可以确保事务的持久性

　　假设有A、B两个数据，值分别为1,2.
　　　　A.事务开始.
　　　　B.记录A=1到undo log.
　　　　C.修改A=3.
　　　　D.记录A=3到redo log.
　　　　E.记录B=2到undo log.
　　　　F.修改B=4.
　　　　G.记录B=4到redo log.
　　　　H.将redo log写入磁盘。
　　　　I.事务提交
　　特点
　　　　A. 为了保证持久性，必须在事务提交前将Redo Log持久化。
　　　　B. 数据不需要在事务提交前写入磁盘，而是缓存在内存中。
　　　　C. Redo Log 保证事务的持久性。
　　　　D. Undo Log 保证事务的原子性。
　　　　E. 有一个隐含的特点，数据必须要晚于redo log写入持久存储。

【二进制日志（binlog）】用于主从复制，基于时间点的还原

【错误日志（errorlog）】默认关闭，mysqld启动和停止,以及服务器在运行过程中发生的错误的相关信息

【慢查询日志（slow query log）】记录执行时间过长和没有使用索引的查询语句

【中继日志（relay log）】用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放

【普通日志（general log）】默认关闭，记录接收到的每一个查询或是命令