Mysql数据库进阶知识点

待我称王封你为后i 2021-11-16 20:32 729阅读 0赞

## 索引 ##

## **1. 索引失效的几种情况** ##

*  查询使用or查询
 *  like查询使用了后匹配，即%name，一般想用索引只能前匹配name%。
 *  组合索引，最左匹配，如果建立的组合索引顺序是name,age,datafrom，那么查询条件需要带上name，原理是因为索引树的建立会根据最左匹配，先根据name排序，相同的值再根据age排，再根据dataform,如果直接用age和datafrom是没办法和索引树对应上的。（顺便提一点，如果数据量特别大，基于多个字段搜索时，一定要用组合索引，假设name有个单独的索引，现在用name和age搜索，这时name的搜索虽然命中索引，但是如果name值对应的数据有几万条，这个时候搜索age值匹配的数据时，就会在这几万条线性遍历匹配，搜索性能会很低）
 *  列参与了数学运算或者函数
 *  网上很多说is Null  、<=>、  is not null 和 ! ＝不可以走索引，但我经过测试都可以走索引。is NUll 官方文档也有说明支持。 [https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html][https_dev.mysql.com_doc_refman_5.7_en_is-null-optimization.html]

**2. 索引类型**

*  FULLTEXT 全文索引，适合对存储长文本的字段建立此索引类型
 *  NORMAL 普通索引
 *  SPATIAL 空间索引，只有MYISAM引擎的表支持，用于对空间数据类型的字段建立索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
 *  UNIQUE 唯一索引，限制唯一的值

**3. hash索引和BTREE索引对比**

*  目前主流还是使用BTREE索引，MyISAM 、InnoDB引擎使用BTREE索引（ index method设置hash索引的时候显示成功，但是show index的时候显示还是btree），MEMORY引擎可以选择用BTREE或者hash索引。如果在InnoDB引擎下想要使用hash索引部分功能，可以设置系统参数innodb\_adaptive\_hash\_index为on,innodb底层还是用btree索引，但是会通过innodb监控自动针对一些热数据建立hash索引，提高=和in查询的效率(官方把这个称为 adaptive hash，即自适应hash,关于自适应hash更多详细介绍可以看官方文档。 [https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html][https_dev.mysql.com_doc_refman_5.7_en_innodb-adaptive-hash.html])。 
 *  BTREE索引基于B+树实现，Hash索引维护一个hash表，索引字段hash后得到的值作为hash表索引位，一个位置对应多条数据（因为存在hash碰撞的问题，所以同个hash值可能对应多条数据）。
 *  Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询，因为hash索引基于计算hash值索引，hash后值的位置跟字段值原有的顺序没有必然关系。
 *  hash基于某个值的搜索比b+树更快。但是如果同个hash值碰撞的量特别大，会导致索引需要线性遍历匹配值，速度不一定就有b+树的快了。
 *  hash索引创建组合索引后，索引值是基于组合索引几个字段的值一块计算hash值，所以搜索时必须组合索引的几个字段都要用上才能命中索引。
 *  hash索引不能用于加速order by。

**4. 聚簇索引和非聚簇索引**

*  innodb使用的是聚簇索引，myisam使用的是非聚簇索引。
 *  聚簇索引数据和索引存在一个文件，非聚簇索引索引和数据是两个文件。
 *  聚簇索引叶子节点存的是数据。非聚簇索引叶子节点存的是数据的地址，需要多一次io查数据，但是因为存的物理地址速度快。

**5. 二级索引（辅助索引）**

*  默认主键为一级索引，其他字段建立的索引为二级索引，一张表只有一个一级索引，没有建立主键mysql默认会设定一个隐藏列作为主键
 *  innodb的二级索引叶子节点存的是主键的id,查询时获取数据需要根据id再去回查主键索引树。myisam因为使用非聚簇索引，二级索引和一级索引一样存的都是数据的地址。（所以二级索引的查询效率myisam会比较高）。
 *  聚簇索引的二级索引如果查询的值只有主键或当前索引列就不用回表查两次。

**6. BTREE索引**

*  BTREE底层的数据结构是B+树（平衡多路查找树），树的层数低，每一层有多个节点，每个节点中存多个索引值key(一个节点为一个磁盘块)，因为层数低io次数也低，查询效率高。
 *  b+树叶子结点存储了完整数据，指向下一节点的指针
 *  利用了磁盘预读原理，mysql将一个节点的大小设为等于一个页的大小，这样每个节点只需要一次I/O就可以完全载入。每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个块里，操作系统按块存储数据，就实现了一个node只需一次I/O。Innodb默认一页的大小是16k，即物理内存块的大小为16k，如果扇区的大小为512 byte，那么一页包含32个扇区。Mysql读取数据是以页为单位进行读取的，同时为了提高效率，还会有磁盘预读，会读多页。（看不懂这一段话的参考我另一篇文章关于页、块、扇区等知识的总结  [https://blog.csdn.net/asd804171023/article/details/105123640][https_blog.csdn.net_asd804171023_article_details_105123640]）
 *  B+树和B树的区别：

1.  B+树只有叶子节点才存数据，非叶子节点只存索引值key不存数据，而B树非叶子节点也存储索引值和数据。虽然B树在非叶子节点存数据可以让某些查询不用查到叶子节点就获取到数据，但是也因此节点占用的空间更大（特别是如果数据特别大，那么会占用很大空间），这会导致非叶子节点（一个节点一个磁盘页）中可以存储的key变得更少，从而导致树的层树更深。 也就是说B+树非叶子节点可以存储更多的key值，从而让树的深度能更低，且每次都要查到叶子节点，查询效率也比较稳定。
2.  B+树叶子节点之间用指针连成一个链表，在进行范围查询的时候，直接通过链表便可实现范围查询，而不用遍历树节点。b树只能通过中序遍历查询

**7.覆盖索引**

基于聚集索引的特性，避免回表查询即可提高查询速度。覆盖索引就是查询返回和查询条件都是直接查的主键或者当前索引值，不用回表查询。通过explain看类型为using index就是覆盖索引。

**什么情况需要建立索引？**

重复数据少，查询次数多，数据量大，字段更新次数少。

**查询优化器**

并非建立索引就一定会走索引，优化器会决定是否走索引。

## 引擎对比 ##

MYISAM、INNODB、MEMORY

*  InnoDB支持事务和外键，MYISAM不支持。因此MYISAM的性能会更高一点,但是数据一致性会弱一些。
 *  InnoDB采用了行级锁，MYISAM采用的是表级锁。
 *  全表统计行数InnoDB需要全表扫描，MYSAM会维护一个count值，直接返回。
 *  InnoDB使用聚簇索引，MYISAM使用的是非聚簇索引，从而二级索引也不一样。（详见索引部分）
 *  MEMORY引擎数据都是存储在内存中的，mysql重启或停止数据就会丢失，适合存临时数据。索引默认使用hash索引，也可以设置BTREE索引。（详见索引部分）

## 事务(mysql引擎中只有INNODB支持事务) ##

**1. ACID 事务的四个特性**

*  原子性(Atomicity)：事务内的sql操作要么全执行,要么不执行。即如果事务执行期间出错会回滚，而不会出现部分执行成功，部分执行失败的情况。 mysql事务原子性通过undo log日志实现，事务中执行的sql操作的前后信息都会记录在undo log日志中，执行出错会通过undo log日志中的信息进行回滚。
 *  隔离性(Isolation）: 事务之间有一定的隔离,事务之间不会出现交错执行的状态。隔离级别不同，事务间的隔离程度也不一样。
 *  持久性(Durability）:事务的持久性是指事务执行成功以后,该事务所对数据库所作的更改便是持久的保存在数据库之中，不可回滚。 mysql事务持久性的体现之一，防止突然宕机缓存丢失数据丢失，通过redo log实现，详细见下面讲的的mysql缓存）。
 *  一致性(Consistency)：事务执行前后数据要保证一致性。数据库层面有类型约束、长度约束、外建约束等来保证数据的一致性。应用层面比如下单，库存减1，销量就要加1，从应用层面保证数据的一致性。一致性是事务追求的最终目标， 只有保证原子性、独立性、持久性，才能保证数据一致性。

1.  如果没有事务的原子性，库存减1后，销量加1的时候出错中断没有事务回滚，数据就不一致了。
2.  如果没有事务间隔离性，不同事务对相同数据的修改相互影响，也会导致数据失去一致性，比如库存原本是4，两个事务都去减1，没有事务的隔离性，导致两个事务都拿4去减1，最终库存剩下3，但是实际上应该是剩下2.数据就不一致了
3.  如果没有事务的持久性，事务执行后提交了，其他事务对数据又做了修改，后面又把这个事务回滚了，导致数据出现问题。

**2. autocommit 自动提交事务**

*   mysql默认采用的是自动提交,show variables like 'autocommit'可以看到启用状态。
 *  在自动提交模式下，如果没有显性的start transaction开启事务，则每一条语句的都会被当作一个事务提交。
 *  如果把这个模式关掉，则所有sql操作都会在一个事务里，需要手动提交事务或回滚，才开启另一个事务。

**3. 事务隔离级别**

*  READ UNCOMMITTED（读未提交）。读未提交是事务最低的隔离级别，会造成脏读。正如这个隔离级别名字，读未提交即一个事务修改了数据还没有提交事务就可以被其他事务读取到，这可能会导致读取了脏数据，因为在事务未提交之间可能会出错导致回滚，那么其他事务如果在这个事务回滚前读取了这个事务修改的数据，就造成读了脏数据，即错误数据。（ 存在脏读、不可重复读、幻读问题）
 *  READ COMMITTED（读已提交）。读已提交，即事务的修改只有提交事务才被其他事务可见。虽然只有提交事务才可见，解决了脏读。但是还会造成不可重复读。按照读已提交的定义，比如事务A提交后造成的数据改变便对其他事务B可见了，但是如果事务B在事务A提交前对一条数据读取过一次，事务A提交后又把这条数据修改掉了，那么事务B再次读取那条数据获取到的数据就跟之前获取到的不一致了，因此叫做不可重复读。（ 存在不可重复读、幻读问题）
 *  REPEATABLE READ(可重复读)。 默认的隔离级别（为什么默认是RR与binlog相关，后面详解）。

1.  mysql通过 快照读（即普通的select \* from test where ..） 避免了脏读、不可重复读和幻读，缺点是读的数据可能 不是实时数据，快照生成的时间是事务第一次读取的时间，快照生成后其他事务的增删改，不会影响当前事务再次读取的结果，因为当前读的是第一次读取生成的快照。
2.  快照读通过MVCC实现，即多版本控制，目的是不加锁，提高并发能力，快照在RR级别只有当前事务更新数据才会更新快照(更新快照也只会更新当前事务更新的数据)，不受其他事务影响，而RR以下其实也是存在快照读，但是每次读取都会更新快照，所以存在不可重复读和幻读。
3.  如果要获取 实时数据，可以使用 当前读，即加锁读（select \* from test where ... lock in share mode( 加共享锁); select \* from test where .. for update;( 加排他锁) ),当前读会通过 间隙锁给读取到的范围的数据加上锁，在该事务没有提交前，其他事务在往间隙锁锁住的 数据范围内 插入/修改/删除数据时会被阻塞。
4.  新增/删除/修改数据都会加给修改到的数据添加间隙锁，比如数据有3、6、9，删除了id=6,那么（3,6\] (6,9\]这两个范围会锁起来，详细见下面讲的间隙锁相关。（如果是读已提交，则只会加上记录锁，并且如果事务A删除id=6的数据，但是id=6这条数据是不存在的，那么是不会加上锁的，也就是另一个事务B可以删除这条数据不会被阻塞，但是可重复读存在间隙锁，把3-9这个范围都锁住了）。

*  SERIALIZABLE（串行化）。最高的隔离级别， 显性声明事务并手动提交或者 关闭了自动提交事务（autocommit）的情况，读取都是 当前读，即select \* from test where ..都会被默认转成select \* from test where ... lock in share mode（ 自动提交读取没必要加锁，一条语句就是一个事务，不会在同个事务多次读取，直接查出已提交的事务更新的数据就行）。同样不存在脏读、不可重复读和幻读，但是因为所有查询存在记录锁（共享锁、间隙锁、next key锁等，并发性能会很差。（ 锁相关的知识后面讲）

事务隔离级别参考官方文档   [https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html][https_dev.mysql.com_doc_refman_5.7_en_innodb-transaction-isolation-levels.html]

**4.  事务使用的注意点**

*  同一个事务内的sql操作锁占用的锁，都是在事务提交的时候释放，因此独占锁尽量放在后面。
 *  事务可以设置折返点，出现问题，可以跳回到折返点。
 *  尽量不要服务层调用服务层（即尽量保证接口功能单一，避免长事务），锁的释放时机是事务提交的时刻，保证短事务可以减少锁占用时间。
 *  在事务中执行DDL语句create table/drop table/alter/table等，会强制提交当前事务。
 *  InnoDB存储引擎不会回滚超时引发的异常，除死锁外

## 锁 ##

**1. 共享锁shared（S）**、 **排他锁exclusive(X)**

*  innodb读写数据可申请的锁，隔离级别为可重复读以上的 当前读会申请共享锁、排他锁。（当前读的介绍看可重复读事务的介绍)。 插入一条新数据，在事务未提交前，这条数据会被加上排他锁。同样 修改数据时，被修改的数据会被加上排他锁。
 *  select \* from test where ... lock in share mode的当前读加 共享锁。
 *  select \* from test where .. for update的当前读加 排他锁。
 *  当一个事务持有 共享锁读取一条数据时，其他数据持有共享锁读取不会被阻塞，但是如果持有 排他锁去读取或者修改数据时会被阻塞。当一条数据被一个事务持有 排他锁占用时，其他事务持有任何锁读取和修改都会被阻塞。
 *  一个事务中如果有更新操作，应该直接申请排他锁，而不是先申请共享锁。如果一开始申请了共享锁，更新又申请排他锁，如果两边事务同时拥有共享锁，而又都去对同一行申请排他锁，都在等待对方的共享锁释放，则会出现死锁的情况。

**2. 意向共享锁(IS)和意向排他锁(IX)**

*  意向共享锁和意向排他锁是申请共享锁和排他锁时需要申请的锁（innodb自动加上的锁）。
 *  意向锁是 表级锁，只会跟 表级别的共享锁和排他锁冲突，与行级别的共享锁排他锁之间不冲突。
 *  意向锁的作用主要是为了 解决行锁和表锁共存时导致的数据问题。假设事务1修改某行的数据，申请了某个行的排他锁，这时会先申请一个意向排他锁，事务2当前读所有数据，这时要申请一个表级共享锁，发现表已经存在一个意向排他锁了，由于意向排他锁与共享锁冲突，因此事务2会阻塞等待（ 如果没有意向锁，事务2想要知道有没有哪一行被加了锁，还要一行一行去找）。如果没有阻塞事务2，事务执行select \* from test lock in share mode（ 由于行锁加锁是基于索引实现的，这条语句没有查询条件没有命中索引，所以会申请表级共享锁），之后事务1修改了某行的数据并提交，事务2再次执行查询语句，获取到的数据就跟之前的不一样了。
 *  下图是mysql官方提供表级别的S、X、IS、IX之间的兼容性。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDgwNDE3MTAyMw_size_16_color_FFFFFF_t_70][] ![20200403102243777.gif][]

**3. 补充说明--死锁的情况**

*  InnoDB会出现死锁的情况。假设事务A和事务B都先申请了同条数据上的共享锁，然后事务A申请排他锁要修改这条数据被事务B的共享锁阻塞，接着事务B也申请排他锁要修改这条数据被事务A阻塞，就造成死锁了。
 *  InnoDB出现死锁的时候，会自动将导致死锁的且持有较少排它锁的事务回滚，另一个事务则正常执行。
 *  MyISAM采用表级别的锁，不会出现死锁的情况，且锁分为读锁和写锁，读锁可重入，读写锁之间是互斥的。且在等待锁的时候，同时有等待写锁和读锁，写锁会优先分配锁（默认情况下，可通过配置修改）

前面讲的共享锁和排他锁相当于是锁的程度，下面讲的锁不同的是锁的位置，但是同样都有共享和排他两种程度，比如共享记录锁，排他记录锁。

**4. 记录锁 record lock**

*  单个操作行上的锁。
 *  记录锁锁的当前修改/新增/读取/删除的数据，通过 锁住索引实现,如果没有建立索引，mysql会设置一个自增隐藏列作为主键,锁住匹配的数据。
 *  如果表一个索引都没有，INNODB会维护一个隐藏的索引，并使用此索引用于记录锁定。

**5. 间隙锁 gap lock (RR以上才存在)**

*  锁住记录两边的间隙，不包含记录本身。
 *  可重复读级别以上的 当前读会申请这个锁，mysql会对操作的记录两边的 数据间隙范围进行加锁，其他事务要操作的数据如果已经被加上间隙锁的数据则会被阻塞等待。
 *  假设数据age字段有3、6、9， 执行select \* from test where age=6 FOR UPDATE，这时（3，6）（6，9)这两个间隙的数据会被锁住。如果执行的是age>4,则（3，6）（6，9）（9，无限大）会被加上间隙锁。
 *  以上举的例子是age字段存在普通索引的情况。如果age字段是 唯一索引或主键，是不会加上间隙锁的， 包括范围查询、等值查询、基于该索引的删除和修改、插入数据，都不会加上间隙锁。

**6. 下一键锁Next-key lock (RR以上才存在)**

*  next-key lock实际上就是 间隙锁+记录锁，锁上记录和记录两边的间隙。mysql就是通过next-key lock 实现INNODB的行锁，也就是通过记录锁和间隙锁实现行锁，实际上就是使用记录锁和间隙锁进行加锁，只是又起了个名字。
 *  还是间隙锁那个例子，select \* from test where age=6上面讲了3-9和6-9会加上间隙锁，而6则会加上记录锁。
 *  下一键锁加锁的范围可以用 下开区间，上闭区间表示，如上面的age>4,（3，6\]（6，9\]（9，无限大）。

**7. 表锁**

*  Myisam更新数据时会加上表级锁，虽然会锁表，但是可以支持并发插入，即一个事务持锁读表时，另一个事务可以从表尾插入数据。concurrent\_insert默认为1，如果表数据中间存在空闲数据块则不启用并发插入（数据删除造成中间有空闲数据块）。如果设置成2则即使读的表文件中间有空闲块，也可以在数据并发插入，采用尾部插入，如果没有锁冲突则会插入空闲块。
 *  通过lock table … read/write可以给表加表锁。

**8.全局锁**

*  对整个实例加锁，flush tables with read lock

**9.mdl元数据锁**

*  MySQL 5.5以上，mdl表锁，访问表时自动加上，当执行dml语句时会加上mdl读锁，ddl语句加上mdl写锁，执行ddl语句会等待目前已经持有mdl读锁的事务执行完才能执行，而新的事务拿mdl读锁会被阻塞，等待ddl执行结束。
 *  online ddl也会拿写锁，但是拿到ddl写锁后，会降级成读锁，再开始真正做ddl(这一步不阻塞)，执行后再升级mdl写锁，再释放锁。即只有在初始化和结束时加写锁。（好处是在执行ddl时减少了阻塞的时间）。

Mysql 修改表结构的三种模式，包含创建临时表进行数据迁移、直接修改原表、直接只修改 元数据。 [https://www.sohu.com/a/329824658\_610509][https_www.sohu.com_a_329824658_610509]

**10. MVVC 多版本并发控制 和 一致性读（无锁读）**

*  一致性读通过MVCC实现，在事务中读取数据时会根据当前读取时间点（不是事务开始时间）生成一个查询快照，其他事务数据的修改不会影响到当前快照，只有当前事务的改动会更新快照，并且只会更新后的快照还是只会包含当前事务的改动，不会包含快照生成之后其他事务更新的数据。通过无锁的版本控制避免了不可重复读，幻读等。
 *  读取的方式是快照读（也叫 一致性读），可能不是实时数据（其他事务修改了数据，但是由于已经存在查询快照，在事务提交之前不会查询到别的事务的改动）。如果要读取实时数据需要当前读，通过锁来控制。如果是当前事务的更改数据，那么不加锁读的时候会出现两次读取数据不一致的情况，但是其他事务的修改是不可见的。
 *  一致性读的实现是通过MVCC实现，实现的原理：通过在行记录上增加三个隐藏字段实现。 DB\_TRX\_ID：占6个字节，表示该行被插入或更新的最后一个事务的事务id，而删除也被内部处理成一个更新，用一个特殊位标记被删除。 DB\_ROLL\_PTR 为占7个字节的回滚指针，指向undo log中旧数据的日志段，当事务需要回滚就通过此记录重新构建行，除了回滚操作一致性读也会通过此字段找到符合当前一致性读的版本号的旧数据。 DB\_ROW\_ID：6字节的行id,自动递增，只有当表没有定义主键的时候，innodb会自动生成这个隐藏列，自动递增作为行的id。
 *  更新数据时，会锁住当前数据，将当前数据记录到undo log，更新当前数据并将 DB\_TRX\_ID更新为当前事务id, DB\_ROLL\_PTR更新为指向undo log中那条旧的数据（多次更新形成链表）。一致性读时会生成一个事务id，只会查询在这个事务id之前已经提交的数据，如果查询后其他事务更新了数据并提交了事务，当前事务再次查询时，如果有数据的事务id高于当前版本，会根据数据上的回滚指针指向undo log，根据undo log查询到旧版本符合当前事务id的数据去匹配

官方文档说明 锁：  [https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html][https_dev.mysql.com_doc_refman_8.0_en_innodb-locking.html]

官方文档说明 mvcc :  [https://dev.mysql.com/doc/refman/5.6/en/innodb-multi-versioning.html][https_dev.mysql.com_doc_refman_5.6_en_innodb-multi-versioning.html]

官方文档说明 一致性读：  [https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html][https_dev.mysql.com_doc_refman_5.7_en_innodb-consistent-read.html]

## ##

### 查询缓存 ###

mysql查询缓存三种机制，INNODB的buffer pool，MyISAM的key cache，公有的mysql query cache。

1. buffer pool

*  缓存数据和索引。sql查询的数据进入buffer pool缓存，按页为单位存储,一个page的大小默认为16kb。
 *  INNODB的数据都是存在磁盘中，为避免每次读写数据都需要磁盘io,mysql提供了Buffer Pool缓存部分数据页，数据的修改定期从缓存刷到磁盘，但是存在系统宕机缓存丢失的情况。所以数据存到缓存前会先通过fsync将数据存到redo log日志中，如果突然宕机也可以从redo log中读取恢复，保证了数据持久性。
 *  通过LRU算法维护缓存的数据，缓冲池列表分为两个部分，一部分存放热点数据new sublist（默认占5/8），一部分用来存储即将淘汰的数据old sublist（默认占3/8）。两个sublist都分有头部和尾部，查询到的新数据页进入buffer pool的时候，会先进入old sublist的头部（不直接放到new sublist是为了防止影响到热点数据，某些查询只查一次，就很久没有再查，直接进new sublist会导致真正热点的数据被挤掉），而old sublist中的数据被访问达到设定的每秒几次访问后（ innodb\_old\_blocks\_time，这个参数可以让新进来的数据在一段时间内重复访问页无法进入new sublist,增加成为热点数据的难度），则会向new sublist头部移动成为热点数据，old sublist头部的数据一段时间内没有被访问，则会移动到old sublist的尾部，最终数据被淘汰。
 *  可以通过参数设置mysql启动时将热数据预加载到buffer pool中，也可以通过设置各种参数影响LRU算法，这里不一一详解，详见官方文档。 [https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html][https_dev.mysql.com_doc_refman_5.6_en_innodb-buffer-pool.html]。
 *  show variables like '%innodb\_buffer\_pool%';   查询buffer pool的配置。
 *  show status like '%innodb\_buffer\_pool%'; 查询buffer pool的运行状态。

2. MYISAM  key cache

*  只缓存常用的索引块，通过找到对应索引块找到数据的物理地址地址（MYISAM使用非聚簇索引上存储的数据内存地址去访问数据），物理地址查找对应的数据没有单独的缓存，依赖操作系统文件缓存。
 *  索引块淘汰通过LRU算法，维护一个列表，最近最少访问的放在表头，最常访问放表尾，最少访问会先淘汰。
 *  设定缓存池大小可以设置key\_buffer\_size 参数。
 *  当缓存的索引快修改时，会先把缓存内容刷盘，再替换缓存。

3. query cache

*  缓存的是SQL语句及对应的结果集，如果查询语句修改了，就命中不了缓存。
 *  数据不常修改和查询条件不会经常变的场景会很有用，如果想不缓存当前查询，可以通过SQL\_NO\_CACHE实现，select SQL\_NO\_CACHE id from test。

缓存相关详细参数配置调优和更详细的原理，参考官方文档。 [https://dev.mysql.com/doc/refman/5.6/en/buffering-caching.html][https_dev.mysql.com_doc_refman_5.6_en_buffering-caching.html]

### log ###

**1. undo log**

*  可以用于事务回滚和一致性读的数据读取。事务中执行的sql操作的前后信息都会记录在undo log日志中，执行出错会通过undo log日志中的信息进行回滚.
 *  undo log分为插入日志和更新日志（包括删除，删除也被处理成一条更新日志）。插入日志只有事务回滚时需要用到，并且会在事务更新后立即删除。更新日志除了用于事务回滚，还可以用于一致性读，一致性读需要用到更新日志用于构建旧的数据行，因此只有 该更新日志没有被任一个事务所依赖时才会清除。

**2. redo log(innodb 独有的机制)**

*  防止缓存数据丢失。innodb新增或修改的数据不会立刻刷盘，会先把数据放入缓存buffer pool中，为防止宕机数据丢失，会先把数据写到log里面，再往数据缓存写。往log写也不是立马就进行磁盘io，log也有一个log buffer,会先写到日志缓存，默认 事务提交的时候，再把日志缓存刷到硬盘。
 *  innodb\_flush\_log\_at\_trx\_commit参数用于设置日志缓存刷到硬盘的机制。值为0时每隔一秒刷一次，值为1事务提交时刷一次，值为2事务提交刷一次还有每秒刷一次。
 *  为什么不直接将数据直接存到硬盘再做缓存而是写到log中? 因为log是顺序io的，数据修改的位置是随机的是随机io，顺序io速度快很多。还有就是buffer pool的刷盘是以一个数据页16k为单位写数据的，新进来的数据会往还有数据空间的数据页写数据，如果没有可用数据页再重新申请一个数据页，如果每次更新都去刷盘会造成不必要的io（只有一条数据变动，却要刷盘整个数据页）。

**3.bin log （mysql服务层的机制，跟存储引擎无关）**

*  bin log记录所有对数据库执行的 新增、修改、删除操作（不记录select、show），如果操作没有影响到的行不会记录到bin log。写进bin log与redo log类似，都是先写进log bug,提交事务的时候刷盘，并且数据以二进制的形式存储在磁盘。
 *  可以用于数据库主从同步（从库连接到主库请求获取主库bin log日志， 注意是拉取不是主库推送,从库根据bin log信息进行相同的操作），查看变更历史，数据备份和恢复。
 *  bin log有三种日志格式：

1.  STATEMENT，存储sql语句，基于语句记录。启动mysql加上此启动参数即可启用 --binlog-format=STATEMENT。有些函数语句不支持复制，比如UUID(), SYSDATE()。缺点是由于是基于语句的复制，某些场景下，有可能会导致主从不一致，下面细说。
2.  ROW，基于改动的数据行进行详细记录。通过主键确保有效的识别修改的行，启动参数  --binlog-format=ROW。相比STATEMENT，ROW基于数据进行赋值不会出现某些函数语句无法复制的问题，也不会造成主从不一致的情况。但是因为是基于被改动的数据记录，如果语句改动的数据行很多，那就会占用很多空间。新版本的优化，把改动表结构的语句用sql语句的形式记录，避免把表所有语句都记录下来。
3.  MIXED，混合日志模式，默认情况下使用基于语句的形式记录，但是有些情况会切换成基于行的记录（mysql判断如果基于语句的复制 不能确保是安全的（下面讲的例子就是不安全的）或者是某些函数无法基于语句复制，就会切换成行记录形式）。启动参数--binlog-format=MIXED。

*  前面说到为什么mysql要把默认隔离级别设置成可重复读与binlog有关，在mysql 5.1以前bin log只支持STATEMENT格式，如果事务隔离级别是读未提交和读已提交会导致主从数据不一致的问题。在读已提交和读未提交级别下，假设事务A先删除一条数据data（data不存在），然后事务B执行新增数据data（因为删除的数据不存在，所以不会加记录锁，新增不会被阻塞）,然后事务B先提交事务，事务A再提交事务，这个时候data数据是存在的（因为数据是按照语句执行顺序来的），但是因为 bin log是在事务提交的时候刷盘，而事务b先提交事务然后事务A再提交就导致binlog保存的顺序是增加数据data再删除数据data,从库执行之后就没有data数据了。
 *  可重复读因为存在间隙锁，事务A执行删除时data不存在，但是会把data和两边的间隙包括data本身锁起来，事务B新增数据就会被阻塞，所以事务A必须先提交事务，事务B才能执行新增，就不会导致binlog保存的语句顺序存在问题。
 *  如果开启binlog，事务的隔离级别设置成读未提交或者读未提交，那么bin log日志格式得设置成ROW和MIXED。

**4. SlowQueryLog （记录慢sql的日志）**

*  show variables like slow\_query\_log. 查看慢sql日志开启情况。set global slow\_query\_log=1;开启慢sql日志
 *  long\_query\_time 这个参数可以设置达到慢sql的时间，单位是秒
 *  通过这个log可以用来排查系统中的慢sql，优化系统性能。
 *  通过在配置文件配置慢日志文件地址，存储的是文本数据，包含执行语句和执行时间等。 mysqlslowdump可以用于分析慢日志。

以下参数配置说明摘自网络

slow_query_log=1       #是否启用慢查询日志，1为启用，0为禁用
    slow_query_log_file=slow.log       #指定慢查询日志文件的路径和名字，可使用绝对路径指定；默认值是'主机名_slow.log'，位于datadir目录
    long_query_time=2     #SQL语句运行时间阈值，执行时间大于参数值的语句才会被记录下来
    min_examined_row_limit=100     #SQL语句检测的记录数少于设定值的语句不会被记录到慢查询日志，即使这个语句执行时间超过了long_query_time的阈值
    log_queries_not_using_indexes=1   #将没有使用索引的语句记录到慢查询日志
    log_throttle_queries_not_using_indexes=10     #设定每分钟记录到日志的未使用索引的语句数目，超过这个数目后只记录语句数量和花费的总时间
    log-slow-admin-statements=1    #记录执行缓慢的管理SQL，如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。
    log_slow_slave_statements=0    #记录从库上执行的慢查询语句
    log_timestamps=system     #5.7版本新增时间戳所属时区参数，默认记录UTC时区的时间戳到慢查询日志，应修改为记录系统时区
    log_output=FILE,TABLE     #指定慢查询日志的输出方式，从5.5版本开始可以记录到日志文件(FILE，慢查询日志)和数据库表(TABLE，mysql.slow_log)中

### qps ###

mysql 5.7  读写 qps平均大概5000，纯读qps可以达到几十万qps

官方测试  [https://www.mysql.com/it/why-mysql/benchmarks/][https_www.mysql.com_it_why-mysql_benchmarks]

### 排查当前sql执行情况 ###

设置执行sql后分析执行情况 show variables; set profiling = 1;

再执行show profiles可查看sql完整耗时和拆分后耗时。

### **Mysql查询和更新数据的过程** ###

连接器（连接mysql,权限校验）->查询缓存->分析器（分析语句是否合法）->优化器（多个查询条件哪个优先匹配，用哪个索引）->执行器（执行具体的sql）-> 引擎返回结果（如果sql没权限则不会通过引擎执行，直接报错）

更新数据和插入数据过程也是类似，但是多了记录binlog的步骤。

innodb还需要记录redo log, 更新插入数据时先写redo log，进入预提交阶段，再写binlog，最后将redo log改为提交状态。

如果不分两个阶段提交，会导致如果写入redo log后异常重启bin log没写入（数据同步从库丢失），如果先写bin log后异常又会导致从库有了数据，但是主库数据丢失不可恢复。分两阶段如果redo log预提交后异常，mysql判断bin log缺失会回滚事务。如果预提交、写bin log后异常，mysql判断bin log数据完整，提交事务。

分两个日志是因为redo log是innodb独有的。

### **连接数** ###

mysql的最大连接数默认是100, 最大可以达到16384。

set GLOBAL max\_connections=1000;可以修改最大连接数，重启后失效

永久生效可以修改mysql myini配置文件

最大连接数设置越好占用内存越大，也会耗费更多的cpu性能。

show global status like ‘max\_used\_connections’ 可以查看最大使用的连接数。

show variables like 'max\_connections’;查看目前配置的连接数

最大连接数使用连接值占服务器上限连接数值的比例值在10%以上，如果在10%以下，说明mysql服务器最大连接上限值设置过高。

Max\_used\_connections / max\_connections \* 100%

**查看连接使用情况：** **show processlist;**

## 自增主键 ##

优点：插入数据有序，顺序io插入速度快。

缺点：分表无法保证多表的id一致。系统集成时，多个系统都是自增主键的话，主键id大量重复。

## UUID主键 ##

优点：全局唯一，分库分表时也能保证各表主键不冲突。

缺点：插入效率低，数据插入是随机io，插入效率低, 会造成内存碎片，占用空间大，相比数字查询速度慢。

## 为什么自增主键是顺序io，UUID是随机io？ ##

mysql写数据的时候会创建一个页大小的数据节点，数据页对象的磁盘文件扇区都是连续的，所以如果保证插入的数据是往后插的，那么就能保证数据插入是顺序io的。因为数据的存放按照主键的顺序插入，构成b+树，那么自增主键就能保证数据是沿着当前节点最后的数据继续插入，插满了再申请一个节点，这就是顺序io，顺序io的写入只是等待磁盘旋转的时间，不用移动到别的磁道，速度就快了。

如果是UUID，那么数据的插入因为uuid是随机的，不是递增的，那么该数据可能都不能插入当前节点，那么就得通过索引树找到这个数据应该插入的节点，获取到对应位置的物理地址，再耗费寻道时间移动磁头写入，自然速度久慢了。

（看不懂？看下上面b+树讲的内容）

### **主从架构** ###

一主多从：

主库同步多从库，影响插入更新效率，从库数据存在延迟。基于主从可以做数据备份、读写分离、故障转移。

链式同步，同步链某个数据库挂了，导致同步中断。

主库挂了就不能写入了。

### **Mysql Cluster 集群** ###

Mysql官方提供的mysql集群版本，分为管理节点、数据节点、sql节点。

管理节点负责管理数据节点、sql节点，监控节点状态等等。

数据节点负责数据存储

sql节点负责接收sql请求。

支持 故障转移、故障恢复、读写分离、负载均衡

### **Xa事务（解决跨库事务）** ###

**资源管理器**：用于管理mysql系统资源，管理事务的提交和回滚。

**事务管理器**：分布式事务的管理者，与每个资源管理器进行通信，协调事务的处理。

即资源管理器为传统mysql端管理事务的管理器，事务管理器指连接了mysql的客户端，客户端通过协调各数据库的事务完成分布式事务。

**基于两阶段提交**。第一阶段：准备阶段，对两个连接执行准备提交，锁定资源。第二阶段：如果两个连接都返回成功，正式开始提交。如果有一个返回失败，则都执行回滚。 通过预备的情况保证两个连接都正常提交事务。

**支持xa的条件：**

1) 事务隔离级别要设置serializable。

2) innodb引擎下。

3) 5.0以上版本。

4) Show variables like ‘innodb\_support\_xa’ 状态为ON

语法：

XA START 'xaid’; // 开启事务 事务进入ACTIVE状态

INSERT INTO mytable (i) VALUES(10); // sql

XA END 'xaid’; // start和end之间的sql处于xa事务范围 事务进入IDLE状态

XA PREPARE 'xaid’;// 准备提交事务  事务进入PREPARED状态

XA COMMIT 'xaid’;// 提交事务

XA ROLLBACK 'xaid’;// 回滚事务

### **查询优化** ###

1. 深分页

使用覆盖索引提高速度。子查询走索引，查询条件和返回都走索引

SELECT \* FROM test a JOIN (SELECT id  FROM test LIMIT 9000000,100) b ON [a.id][] = [b.id][]

id连续还可以通过索引范围查询提高速度，比如查第1w条数据，可以直接id>1W的id，limit 0，100;

2.小表驱动大表。

①当B表的数据集小于A表数据集时，用in优于exists。

select \* from tb\_emp\_bigdata A where A.deptno in ( select B.deptno from tb\_dept\_bigdata B)

②当A表的数据集小于B表的数据集时，用exists优于in。

select \* from tb\_dept\_bigdata A where A.deptno in ( select B.deptno from tb\_emp\_bigdata B);

Exists和in的区别是，exists先查外面再查里面，in是先查里面再查外面

3. or 转换为 union all。or不能走索引

4. 通过函数查询指定时间的数据

SELECT \* FROM 表名 where DATE\_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);

5.now、sysdate区别

now()返回执行语句时的时间，sysdate()返回实时系统时间。主从同步执行结果会不一样，要用now。

**横向分表、纵向分表（单表数据量太大就要考虑分表，比如单表1千万数据）**

横向分表将表通过某个字段计算分片，将数据分到多个表上，每个表字段相同

纵向分表是按列分表，将表的某些不常用字段拆到另一个表，避免每次查询并不需要那些字段，但是被这些字段占用了空间，降低了查询效率。

**数据库三大范式**

1 第一范式。不可拆分，每一个字段的值都不可拆分。比如地址，又可拆分成省份，城市等

2 第二范式。完全依赖，数据列要有一个唯一标识，要有主键

3  第三范式。消除依赖传递。每一项只与该表主键直接相关，而不与其他字段相关，再间接与主键相关。比如订单表存放了商品id，又存放了商品名称。商品名称与商品id相关，就不满足第二范式。主要是数据冗余问题。(有数据一致性的问题，但实际场景经常会做一些冗余，为了提供性能可用性，降低一定的数据一致性)

**字段类型**

TEXT 65,535 bytes ~64kb

MEDIUMTEXT 16,777,215 bytes ~16Mb

LONGTEXT 4,294,967,295 bytes ~4Gb

字段用空值会占用更大的空间

varchar和char区别。char是固定字符长度，varchar是可变的，为实际数据+1字符空间。char查询性能高些。

int 指定长度只是影响展示，实际占用空间是相同的。

关系型数据库 **与nosql对比**

关系型数据库：高度组织化结构化数据、结构化查询语言（SQL）、数据和关系都存储在单独的表中、严格的一致性、基础事务ACID

Nosql: 不仅仅是SQL、没有声明性查询语言、没有预定义的模式、键 - 值对存储或列存储或文档存储或图形数据库、最终一致性、非结构化和不可预知的数据、高性能、高可用性和可伸缩性。

[https_dev.mysql.com_doc_refman_5.7_en_is-null-optimization.html]: https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html
[https_dev.mysql.com_doc_refman_5.7_en_innodb-adaptive-hash.html]: https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html
[https_blog.csdn.net_asd804171023_article_details_105123640]: https://blog.csdn.net/asd804171023/article/details/105123640
[https_dev.mysql.com_doc_refman_5.7_en_innodb-transaction-isolation-levels.html]: https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDgwNDE3MTAyMw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200403102243782.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDgwNDE3MTAyMw==,size_16,color_FFFFFF,t_70
[20200403102243777.gif]: /images/20211116/66fea70c1c4c441bab69a5d4b54f82c0.png
[https_www.sohu.com_a_329824658_610509]: https://www.sohu.com/a/329824658_610509
[https_dev.mysql.com_doc_refman_8.0_en_innodb-locking.html]: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
[https_dev.mysql.com_doc_refman_5.6_en_innodb-multi-versioning.html]: https://dev.mysql.com/doc/refman/5.6/en/innodb-multi-versioning.html
[https_dev.mysql.com_doc_refman_5.7_en_innodb-consistent-read.html]: https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
[https_dev.mysql.com_doc_refman_5.6_en_innodb-buffer-pool.html]: https://dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html
[https_dev.mysql.com_doc_refman_5.6_en_buffering-caching.html]: https://dev.mysql.com/doc/refman/5.6/en/buffering-caching.html
[https_www.mysql.com_it_why-mysql_benchmarks]: https://www.mysql.com/it/why-mysql/benchmarks/
[a.id]: http://a.id/
[b.id]: http://b.id/