MySQL相关知识整理

MySQL部分

1.MySQL存储引擎

---

1.1列出常见的MySQL数据存储引擎？

数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新、和删除数据

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能

使用不同的存储引擎，还可以获得其特定的功能

需要选择启动的引擎，好比一辆轿车，性能好的发动机会提升轿车的性能

---

MySQL引擎包括

InnoDB、MyISAM

Memory

Memory表比MyISAM表还快，因为它的数据都保存在内存中，不需要进行磁盘I/O

Memory表结构在重启以后还会保留，但数据会丢失，非持久化的数据哦

---

 

Blackhole

没有任何存储实现，插入数据都会被丢弃，但是操作记录会存储在日志里

所以可以做备份数据库使用

---

CSV

CSV引擎是将普通的CSV文件(逗号分隔值的文件)作为MySQL的表来处理，不支持索引

CSV引擎可以在数据库运行是拷入或者拷出CSV文件，作为一种数据交换的机制，非常有用

---

Archive

Archive存储引擎INSERT和SELECT,是对高速插入和压缩做了优化的简单引擎

Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩，所以比MyISAM磁盘I/O更少

但是每次SELECT查询都需要执行全表扫描，所以Archive表适合日志和数据采集类应用

这类应用做数据分析是往往需要全表扫描，或者在一些需要更快捷的INSERT操作的场合下也可以使用

---

其中MyISAM、InnoDB使用最为广泛，在这里建议大家记住5个引擎

---

查看支持引擎

 

SHOW ENGINES;

 

---

1.2 InnoDB存储引擎的特点？

 

InnoDB是事务性数据库的默认引擎，也是首选引擎

支持事务(ACID)、行锁定、外键，具有提交、回滚和崩溃恢复能力

InnoDB为处理巨大数据量的最大性能设计，所以InnoDB也经常别用在众多需要高性能 的大型数据库站点上

---

1.3 InnoDB存储引擎的高级特性？

• 自适应哈希索引

InnoDB建立索引时，只可以建立B+tree索引，是不可以建立hash索引的

hash索引相当于B+tree索引，虽然无法实现排序、范围检索的效果

但是在等值查询 的时候，毫无疑问要比B+tree索引的效率要高很多

等值查询：select id,name from table where name = 'zhangsan';

所以InnoDB就在B+tree索引的基础之上，又添加了自适应hash索引

只不过这个索引无法通过手动创建，是通过InnoDB存储引擎在运行时自己创建的

对于用户来说是透明的，也就是你看不到的，皇帝的索引

InnoDB会监控堆表上非聚集索引的查找，如果发现某个非聚集索引被频繁访问，那么就认为这个非聚集索引是热点数据，就会针对这个非聚集索引建立hash索引，下一次再检索时就可以直接通过hash索引检索

InnoDB认为最近连续三次被访问的非聚集索引是热点数据，就会自动创建hash索引

---

查看自适应哈希启动状态

SHOW VARIABLES LIKE '%ap%hash_index';

查看自适应哈希状态

SHOW ENGINE INNODB STATUS;

修改自适应哈希状态

SET GLOBAL innodb_adaptive_hash_index = ON/OFF;

• 插入缓冲(insert Buffer)

插入缓冲是针对于非聚集索引而言的，因为聚簇索引一般都是有顺序的

通过聚簇索引在执行批量插入时，第一条语句插入完成之后，后面的数据所在的也基本上都和第一条的数据在同一页，或者是相邻的页，所以进行批量插入的时候只需要加载1次页就可以完成多条数据的插入操作，这样可以减少数据库的I/O操作

但是对于非聚簇索引，索引基本上都是无序的，离散的，所以每次插入的时候就需要离散地访问非聚簇索引页，显然就降低了插入的性能

索引页说白了就是记录当前索引所处的一份索引记录

于是乎InnoDB为了解决这个问题就新增了插入缓冲功能

对于非聚簇索引的插入或更新，不是直接更新到索引页，而是先判断更新的非聚簇索引是否存在缓冲池中，在的话追加插入，不存在则会添加入缓冲池中，然后再以一定的频率将缓冲池中的缓存和非聚簇索引页的数据进行合并操作，由于在一个索引页，所以通常可以将多个插入操作合并成一个操作

插入缓冲减少了非聚簇索引页的I/O操作，提高了插入效率

还需要注意的是插入缓冲必须针对的是非聚簇索引的插入或更新，并且索引是非唯一的

如果是唯一性的非聚簇索引，那么在插入时需要校验唯一性，校验唯一性势必优惠发生离散读取的情况

又增加了开销，那么此时插入缓冲区失去了其原有的意义

插入缓冲也会带来一些问题

1.在应用程序插入数据时宕机，大量的插入缓冲区内的数据并没有合并到实际的非聚集索引中，此时数据库恢 复的话需要大量的时间

2.插入缓冲在写密集的情况下会占用大量的缓冲区内存，InnoDB中默认最大可以有一半的缓冲区内存，占据过多内存，可能影响其他操作

---

• 二次写(Double Write)

二次写主要是为了提升InnoDB的可靠性，确保数据不会丢失

 

---

工作流程

double write主要分成两个部分组成

1. 一部分是内存中的一个缓冲区double write buffer,大小为2M
2. 一部分是磁盘共享表空间(ibdata)中连续的2个区，也就是128页，大小也是2M

当脏页触发刷新时，并不是直接写入磁盘文件，而是先写到double write buffer中

接着从double write buffer中分两次写入磁盘共享表空间中，每次写1M

连续存储、顺序写效率高

之后，再将double write buffer中的脏页数据离散写入实际的各个表空间文件，等待脏页数据全部同步，同步结束，当前double write两部分的数据就可以被下次操作覆盖而不进行保留了

---

为啥需要二次写

io最小单位：mysql一页：16k、文件系统：4k/1k、磁盘io：512bytes

由于脏页数据可能需要多次写入到磁盘中，当写入过程出现问题，那么可食用二次写进行恢复

---

当需要将脏页16k的数据写入到磁盘文件时，假设每次是4k，那么就需要进行四次物理写的操作才能刷盘完成

如果在执行了2次物理写之后，系统出现故障，就会导致磁盘中已经被写入了一个不完整的数据页

系统恢复时，通过事务日志redo log只能通过已经校验完整的数据页恢复一个脏块，不能修复坏掉的数据页，从而会造成数据不一致的问题

此时就可以通过共享表空间的文件中找到改业的最近的副本，将其复制到表空间文件，在通过redo log就可以完成恢复操作

---

为啥通过log写不需要二次写呢

因为log属于磁盘I/O,写入的单位是512个字节，也就不会存在数据损坏的问题

---

那么二次写有啥问题呢

二次写位于共享表空间上的内容实际上也是一个文件，对其操作无形中增加了IO次数，但是问题不大，因为他不是离散写入，性能还是OK的

如果数据表空间放在一些本身就提供部分写失效防范机制的文件系统上，如ZFS、FusionIO、DirectFS,这种情况下，就可以不开启二次写了

---

查看二次写启动状态

 

SHOW VARIABLES LIKE '%double%';

 

关闭二次写，需要修改配置文件，然后重启数据库

skip_innodb_doublewrite= yes

查看二次写状态

SHOW STATUS LIKE 'InnoDB_dblwr';

---

• 缓冲池

InnoDB在内存中维护了多个缓冲池，用来缓存近期访问的数据和索引，提高访问速度

---

缓冲池内部通过List管理数据，采用LRU算法(最近不被访问)淘汰数据

当缓冲池满了之后，会删除掉最近没有被访问的数据，而插入缓冲池的时候，也不插入List的头部或尾部，而是插入List的中间部分

因为头部是热点数据，而尾部是即将淘汰的数据，采用保险策略将新的数据插入中间比较合理

---

InnoDB的所有操作几乎都是在缓冲池中实现，将磁盘中的数据加载到缓冲池，然后在进行下一步操作，更新的时候也是直接更新缓冲池中的数据，让后再按一定频率刷新到磁盘

缓冲池还有一个功能就是预读功能，预读功能是当InnoDB执行了一次IO操作，加载了一页或多页之后，会预计下一次需要加载到页面数据，

提前将未来数据加载到缓冲池，就可以避免下一次再进行IO操作

---

预读操作有两种：线性预读和随机预读

1. 线性预读：则会按数据页的顺序进行预读，预读页的个数可以通过配置设置
2. 随机预读：当某一块(extent)中的某一页或某一页被加载了之后，会将这个extent中的所有数据页都加载到缓冲池

---

1.4 MyISAM存储引擎的特点？

在MySQL5.1及之前的版本，MyISAM是默认的存储引擎

MyISAM不支持事务，不具备ACID特性

不支持行级锁，支持的是表锁，更新数据时锁定整个表、这样虽然可以让锁定的实现成本很小，但是同时大大降低了其并发的性能

读取速度快、占用资源比较少

崩溃后无法完全恢复

但是MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等

---

应用场景

不需要事务支持的场景

读多或者写多的单一业务场景、读写频繁的则不适合、会阻塞

读写并发访问较低，数据修改相对较少的业务

以读为主的业务，如博客、图片信息数据库、用户数据库等，以及面试题库

服务器硬件资源相对比较差的机器

---

尽管MyISAM引擎不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的

对于只读的数据，或者表比较小、可以忍受修复操作，则依然可以继续使用MyISAM

---

加锁

MyISAM对整张表加锁，而不是针对行

读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁，但是在表有读取查询的同时，也可以往表中插入新的记录(这被称为并发插入，CONCURRENT INSERT)

修复

 

对于MyISAM表，MySQL可以手工或者自动执行检查和修复操作

 

但这里说的修复和事务恢复以及崩溃恢复是不同的概念

 

执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的

索引

对于MyISAM表，支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询

延迟更新索引键

创建MyISAM表的时候，如果指定了DELAY_KEY_WRITE选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘

这种方式可以极大地提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引键的特性，可以在全局设置，也可以单个表设置。

---

1.5 InnoDB和MyISAM有什么区别呢？

事务安全：MyISAM不支持事务，InnoDB支持事务

外键：MyISAM不支持外键，InnoDB支持外键

锁机制：MyISAM是表锁，InnoDB是行锁

查询和添加速度：MyISAM批量插入比InnoDB速度快

内存使用：MyISAM内存空间使用率比InnoDB低

---

1.6 啥是脏页？

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为脏页

内存数据写入到磁盘后，内存和磁盘上的数据页的内容一致，称为干净页

平时很快的更新操作，都是在写内存和日志，他并不会马上同步到磁盘数据，这是内存数据页跟磁盘数据页内容不一致，我们称之为脏页

---

一条SQL语句，正常执行的时候特别快，偶尔很慢，那这时候可能就是在将脏页同步到磁盘了

---

1.7啥是B树？

为什么要有B树？

其实这些树最开始都是为了解决某种系统中，比如数据库系统中查询效率低的问题

B树其实最开始源于二叉树，二叉树是只有左右孩子的树，当数据量越大的时候，二叉树的节点越多，那么当从根节点搜索的时候，硬性查询效率

所以如果这些节点存储在硬盘儿上的话，每访问一个节点，相当于进行了一次I/O操作，而I/O操作大家都知道，读硬盘肯定比读内存要慢，也就影响效率

于是就有了在极多节点情况下，比二叉树高度少很多很多的B树

---

BTree树，也叫做Balance Tree,是一种多路平衡查找树，是一种数据结构

每一个节点最多包含K个孩子，K称为b树的阶(孩子数就是阶)，K的大小取决于磁盘页的大小相较于二叉树搜索，b树的高度更少，可以优化查询的磁盘IO次数，并且还兼顾了检索的速度

在MySQL中，使用的是更为高级的b+tree

---

b树的特点

1.根节点至少有两个子女

2.节点中的元素从小到大排列

3.每个节点包含k-1个元素(2个元素)，包含根节点

4.叶子节点包含k-1个元素，并且所有叶子节点位于同一层

---

b树查找过程

---

1.8 那啥是B+树？

B+树是B树的变体，有着比B树更高的查询效率

1.中间节点不存数据，存的是指向叶子节点的索引

2.所有数据都保存在叶子节点中，根节点存储着整个树中最大的元素

3.所有的中间节点的元素，在子节点中要么是最大的，要么是最小的

4.叶子节点包含所有数据，和指向这些元素的指针

5.叶子节点的元素会形成自小向大这样的链表

B+树还有一个重要特点在于，中间节点的元素个数可以是阶数(孩子节点个数k),这点和B树是不一样的，

B树的节点元素个数是孩子数-1

---

区别与B树，B+树中，只有叶子节点有卫星数据，中间节点是没有卫星数据

所以我们就可以得出结论，由于B+树的中间节点没有卫星数据，所以同样大小的磁盘页可以容纳更多的节点元素，数据量相同的情况下，B+树要比B树的高度更加的矮，更能节省I/O次数

此处由于B+树是稳定查找到叶子节点去拿取数据，而不像B树，可能中间节点就包含卫星数据，所以B+树的查询更稳定

最后一点，就是B+树具备更加便捷高效的范围查询，这是由于叶子节点已经成了一个链表，那么对于链表范围查询，要比对树的遍历范围查询更轻松

---

1.9 为什么B+比B树更适合文件/数据库索引？

因为文件与数据库都是需要较大的存储，不可能全部存储在内存中，故要存储到磁盘上，那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，而B+树相比B树，树的高度矮，查询的I/O次数更少

查询更稳定，B+树的查询每次都是查询到叶子节点

B+树更方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子节点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持。这是数据库选用B+树的最主要原因

---

2. MySQL索引

2.1 主建使用自增ID还是UUID?

如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，为了存储和查询性能应该使用自增长id做主键

对于InnoDB的主索引，数据会暗战主键进行排序，由于UUID的无序性，InnoDB会产生巨大的IO压力，此时不适合使用UUID做物理主键，可以把它作为逻辑主键，物理主键依然使用自增ID。为了全局的唯一性，应该用UUID做索引关联其他表或做外键

---

为什么有时会用UUID作为主键呢？

其实在InnoDB存储引擎下，自增长的id做主键性能已经达到了最佳，不论是存储和读取速度都是最快的，而且占的存储空间也是最小

但是在我们实际到项目中会碰到一些问题，历史数据表的主键id会于数据表的id重复，两张自增id做主键的表合并时，id一定会有冲突，但如果各自的id还关联了其他表，这就很不好操作如果使用UUID,生成id不仅是表独立的，而且是库独立的，对以后的数据合并这样的操作就很有好处

---

uuid作为主键的缺点

影响插入速度，并且造成硬盘使用率低，顺序不好维护

UUID之间比较大小相对数字慢不少，影响查询速度，对比玛法

UUID占空间大，如果你建的索引越多，影响越严重，内存的节点存放索引有限

---

2.2 什么是索引？

索引是一种数据结构，通过索引可以更快的查询，相当于一本书的目录

实现索引常用的数据结构就是B+树，这种数据组织下的增删改查平均时间复杂度为O(log(n))

还有的是Hash结构，例如java中的HashMap(哈希表)，在最好情况下，哈希的结构增删改查的时间复杂度是O(1)

---

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构

索引是针对表而建立的，它是由数据页面以外的索引页面组成的

每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据

---

2.3索引有哪些？

从数据结构角度

B+树索引、Hash索引

---

从物理存储角度

聚集索引(clustered index)

非聚集索引(non-clustered index)

---

从逻辑角度

主键索引：主键索引是一种特殊的唯一索引，不允许有空值，一般使用自增的ID作为主键

普通索引/单例索引：最基本的索引，没有啥限制，经常用的索引

多例索引/复合索引/组合索引：复合索引指多个字段上创建的组合索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用；使用这样的索引时必须遵循最左原则

唯一索引：与普通索引类似，不同支持在于，索引列的值必须唯一，但允许有空值

联合唯一索引：将多个列合并组合为索引，这多个列的整体数据在数据库中的出现是唯一的

全文索引：目前支持在InnoDB和MyISAM存储引擎，使用于CHAR,VARCHAR和TEXT列，

常用在字符串内的全文检索，简历倒排索引的机制进行全文检索

---

2.4 聚集索引、非聚集索引是什么？

聚集索引

聚集索引也叫做聚簇索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序

数据航的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同，一个表中只能拥有一个聚集索引

如果查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后

---

非聚集索引

除了聚集索引以外的索引都是非聚集索引，比如普通索引，唯一索引，全文索引等

该索引中索引的逻辑顺序与磁盘上的物理存储顺序不同，一个表中可以拥有多个非聚集索引

非聚集索引还有个特点，如果查询列中包含了其他该索引没有覆盖的列，还有进行二次查询

比如简历非聚集索引的是name,如果现在查询name和age，age没有被该索引覆盖

那么查询name和age的结果就会引起二次查询，也是我们常说的回表查询，影响查询效率

---

2.5 联合索引的最左原则是什么？

以联合索引(a,b,c)为例，建立这样的索引相当于建立了索引a、ab、abc三个索引

如果不尊重此时的最左原则进行查询，那么会导致联合索引失效

这是由于联合索引是一棵B+树，其非叶子节点存储的是第一个关键字的索引，也就是a

叶节点存储的是三个关键字a,b,c三个关键字的数据，并按照a,b,c的顺序进行排序

如果a没有先确定，直接对b和c进行查询的话，就相当于乱序查询一样，因此索引无法生效，查询是全表查询，效率极低

---

2.6什么是哈希索引呢？

哈希索引(hash index)基于哈希表实现，并且只对精准匹配索引所有列的查询有效

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值(hash code)

哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样

哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针

---

2.7 Hash索引有什么特点呢？

InnoDB中采用触发散列函数进行hash运算，hash冲突采用链接法/拉链法

哈希算法时间复杂度为O(1),且不只存在于索引中，每个数据库应用中都存在该数据结构

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位

不像B-树索引需要从根节点到枝节点，最后才能访问到页节点这样需要多次的IO访问

所以Hash索引的等值查询效率要远高于B-树索引

---

可能很多人又有疑问了，既然Hash索引的效率要比B-树高很多，为什么 大家不都用Hash索引而还要用B-树索引呢？

任何事物都是有两面性的，Hash索引也一样，虽然Hash索引效率高

但是Hash索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些

---

Hash索引中存放的是进过Hash计算之后的Hash值，而hash算法会产生hash冲突

• Hash索引仅仅能满足“=”，“IN”和“<=>”查询，不能使用范围查询，没见过字典可以匹配一个范围的结果吧
• Hash索引不支持多列查询，这个准确的说是无法进行与建立索引时不同的字段查询方式，比如建立索引的字段只是name，那么查询时同时用name和age多个条件进行过滤，由于此时进行Hash运算可能会比之前建立索引时字段多，这样就会造成Hash结果不同，此时Hash索引无效
• Hash索引进行数据的排序操作比较麻烦，因为Hash值的大小关系并不一定和Hash运算前的键值完全一样，无序的索引结构查询时无法直接保证其顺序，所以不建议使用Hash索引进行查询排序
• Hash索引在任何时候都不能避免表扫描，Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键可能会存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果

---

Hash索引遇到大量Hash值相等的情况后，性能并不一定就会比BTree索引高

---

2.8索引失效的原因有哪些呢？

1.索引中有null值的列

只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值

那么这一列对于此复合索引就是无效的，所以我们在数据库设计时不要让字段的默认值为null

2.使用or语句时的条件不是全部具有索引的

 

如果条件有or,即使其中有条件带索引也不会使用索引

 

要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

3.复合索引未使用最左原则

对于多列索引，不尊重最左原则，那么索引没有被利用到，属于全表查询，此时索引失效

4.like以%开头会导致索引失效

 

like是模糊查询的语句，一般情况下不推荐使用like操作

 

如果非使用不可，like “%aaa%”不会使用索引而like “aaa%”可以使用索引

5.查询语句中触发类型转换

比如字段是varchar类型，如果查询时通过select * from user where age =123;

由于触发了类型转换，那么此时索引无法命中

除非我们使用select * from user where age = '123';

6.where 条件查询的索引列有函数或运算存在

目前，MySQL没有支持函数、运算索引，给字段进行运算或者使用函数会导致索引失效

比如使用select * from table where year(column)<2019;,没有利用到索引

而使用select * from table where column < '2019-01-01',就可以利用到了

7.如果MySQL觉得全表扫描更快时

由MySQL选择，数据不多时，检索数据不用索引反而更快，那么将不使用索引进行检索

---

2.9 什么是回表查询？

简单来说就是数据库根据索引(非主键)找到了指定的记录所在行后

还需要根据主键再次到数据块里获取数据

---

2.10 非聚集索引一定会回表查询吗？

不一定，如果查询结果中包含了没有被索引覆盖的列，就会导致回表查询

使用以下语句进行查询，不需要进行二次查询，现在设定非聚集索引的列username

直接就可以从非聚集索引的节点里面就可以获取到查询列的数据

select id,username from t1 where username = '小明';

select username from t1 where username ='小明';

---

但是使用以下语句进行查询，就需要二次的查询去获取原数据行的score

select username,score from t1 where username = '小明';

---

2.11 什么情况下应不建或少建索引？

频繁更新的字段少创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件

where条件里用不到的字段，不需要创建索引，既然你都不用他查询，那为啥还加索引

表记录太少，不需要创建索引，维护索引还不如不维护呢

经常增删改的表，这时建立索引会导致B+树不停的进行自平衡操作

数据包含大量重复数据，建立索引没有太大的效果，例如性别字段，只有男女，不建立索引

---

3.MySQL事务相关

3.1 什么是事务？

事务就是一组原子性的SQL查询，或者说一个独立的工作单元

事务要么全部执行，要么全部不执行

---

3.2 bin-log是干啥的？

二进制日志bin-log是服务层的日志，还被称为归档日志

bin-log主要记录数据库的变化情况，内容包括数据库所有的更新操作

所有涉及数据变动的操作，都要记录二进制日志中

因此有了bin-log可以很方便的对数据进行复制和备份，因而也常用作主从库的同步

---

3.3 MySQL事务日志有哪些？

InnoDB事务日志包括redo log和undo log

redo log是重做日志，提供前滚操作

undo log是回滚日志，提供回滚操作

---

重做日志(redo log)是InnoDB引擎层的日志

用来记录事务操作引起数据的变化，记录的是数据页的物理修改

我打个比方，数据库中数据的修改就好比你写论文，万一哪天论文丢了怎么办？以防这种不幸的发生，我们可以在写论文的时候，每一次修改都那个小本记录一下，记录什么时间对某一页进行了怎么样的修改，这就是重做日志

InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中

这就是所谓的预写式技术(Write Ahead logging),这种技术可以大大减少IO操作的频率，提升数据刷新的效率

---

回滚日志(undo log)同样也是InnoDB引擎提供的日志

顾名思义，回滚日志的作用就是对数据进行回滚

当事务对数据库进行修改，InnoDB引擎不仅会记录redo log，还会生成对应的undo log日志

如果事务执行失败或调用了rollback,导致事务需要回滚，就可以利用undo log中的信息将数据回滚到事务之前的样子

但是undo log和redo log不一样，它属于逻辑日志，他对SQL语句执行相关的信息进行记录

当发生回滚时，InnoDB引擎会根据undo log日志中的记录做与之前相反的工作

比如对于每个数据插入操作(insert),回滚时会执行数据删除操作(delete)

undo log有两个作用：一是提供回滚，二是实现MVCC

---

3.4 bin-log 和redo log的区别

这里redo log存储的内容看起来似乎与bin-log很相似，但其实不然

二进制日志是存储引擎上层产生的，不管是什么存储引擎，对数据库修改都会产生二进制日志

redo log是InnoDB层产生的，是一种物理日志，记录的是实际上对某个数据进行了怎么样的修改

而二进制bin-log日志记录操作的方法是逻辑性的语句，记录的是SQL语句的原始逻辑

二进制bin-log日志先于redo log被记录

---

3.5 事务的四大特性ACID?

原子性(atomicity)

一个事务必须视为一个不可分割的最小工作单元

整个事务中的所有操作要么全部提交成功，要么全部失败回滚

对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性(consistency)

数据库总数从一个一致性的状态转换到另一个一致性的状态

比如你有1000块，转给别人，那么别人必须有这1000块，类似于守恒

一致性是事务的最终目的，原子性、隔离性、持久性都是为了实现一致性

隔离性(isolation)

一个事务所做的修改在最终提交前，对其他事务是不可见的

持久性(durability)

 

一旦事务提交，则其所做的修改就会永久保存到数据库中

 

此时即使系统崩溃，修改的数据也不会丢失

---

3.6 同时有多个事务在进行会怎么样？

事务的并发，可能出现三种问题

脏读

一个线程中的事务读到了，另一个线程中的事务未提交的数据

不可重复读

一个线程中的事务读到了另一个线程中提交的更新数据，前后两次读到的内容不一致

幻读

一个线程中的事务读到了另一个线程中提交的插入或更新的数据

前后读到的记录条数不一致

---

3.7 事务的隔离级别有哪些？

脏读：读到了事务未成功提交的数据，这是不存在数据库中的，这就是脏读

幻读：在事务中，第一次检查这个数据不在，第二次检查由于别的事务提交了插入，导致这条数据居然在了，两次读到了不一样的结果

可重复读：在整个事务操作过程中，你拿到的数据绝对不会变，随便重复读

不可重复读：别人比你先修改这个数据，并且先于你提交了事务，导致你再读就不一样了，和幻读很像，不可重复读常常针对是当前的数据

容易搞混不可重复读和幻读，确实有些相似，但不可重复读重点在于update和delete，而幻读的重点在于insert

---

读未提交(READ UNCOMMITTED)

一个事务可以读取到另一个 事务未提交的修改

另一个事务提交不成功带来脏读，提交成功带来幻读、不可重复读问题

读已提交(READ COMMITTED)

一个事务只能读取另一个事务已经提交的修改

其避免了脏读，但另一个事务提交成功了，仍然存在不可重复读和幻读问题

可重复读(REPATABLE READ)MySQL默认隔离级别

同一个事务中多次读取相同的数据返回的结果是一样的

其避免了脏读和不可重复读问题，但幻读依然存在

可串行(xing)化

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题

简言之，就是一个事务执行结束，再执行另一个事务

在这个级别，可能导致大量的超时现象和锁竞争，效率不行，没人用

---

3.8 什么是MySQL的MVCC？

多版本并发控制(Multi-Version Concurrency Control),是乐观锁的一种实现方式

MVCC手段只适用于隔离级别中的读已提交(RC)、可重复读(RR)

实现了非阻塞的读操作，写操作也只锁定必要的行，提高了对于数据库的访问效率

言简意赅的来说，就是通过给每行数据维护隐藏起来的版本号，来确定是否修改操作是保存成功的，这条数据是否可以访问

3.9 Innodb使用的是那种隔离级别呢？

MySQL的默认隔离级别是可重复读

---

3.10 事务是如何通过日志来实现的？

• 事务在修改页时，要先记undo
• 在记undo之前要记undo的redo,然后修改数据页
• 再记数据页修改的redo

---

redo里面包括undo的修改，一定要比数据页先持久化到磁盘

当事务需要回滚时，因为有undo，可以把数据页回滚到前镜像的状态

崩溃恢复时，如果redo log中事务没有对应的commit记录

那么需要用undo把该事务的修改回滚到事务开始之前

如果有commit记录，就用redo前滚到该事务完成时并提交掉

---

事务的原子性是通过undo log来实现

事务的持久性是通过redo log来实现

事务的隔离性是通过(读写锁+MVCC(乐观锁的一种实现方式))来实现

---

4. MySQL锁的使用

4.1 您对MySQL的锁了解吗？

当数据库有并发事务的时候，可能会产生数据的不一致

这时候需要一些机制来保证访问的次序，锁机制就是这样的一个 机制

例如酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入职并且将房间锁起来 ，其他人只有等他使用完毕才可以再次使用

---

按操作划分：DML锁，DDL锁

按锁的粒度划分：表级锁、行级锁、页级锁

按锁级别划分：共享锁、排他锁

按加锁方式划分:自动锁、显示锁

按使用方式划分：乐观锁、悲观锁

---

4.2 您知道MySQL三种锁的级别有哪些吗？

MySQL的锁级别有三种：行级锁、表级锁、页级锁

行级锁的典型代表引擎为InnoDB,仅对正在操作的指定记录进行加锁，这样其他进程还是可以对同一个表中的其他记录进行操作

表级锁的典型代表引擎为MyISAM,表级锁直接锁定整张表，如果是表的读锁，那么在你锁定期间，其他进程无法对该表进行写操作，但如果是写锁，则其它进程则读也不允许，可见这种锁读写操作多时，性能还是不行

页级锁的典型代表引擎为BDB，表级锁速度快，但冲突多，行级冲突少，但速度慢，所以取了折中的页级，一次锁定相邻的一组记录

---

4.3 什么是乐观锁、悲观锁？

乐观锁

就像它的名字一样，乐观锁对于并发间操作产生的线程安全问题持有乐观状态，认为竞争不总是会发生，因此它不需要持有锁

乐观锁事务的成功与否的标志位是否发生冲突，发生冲突则代表当前事务处理失败，需要重新持有乐观锁，重新进行事务操作

乐观锁机制避免了长事务中的加锁开销，大大提升了大并发量下的系统整体性能表现，但是会被产生的冲突打断而重新进入事务的处理过程

---

悲观锁

悲观锁对于并发间操作产生的线程安全问题持悲观状态，悲观锁认为竞争总是会发生

因此每次对某资源进行操作时，都会持有一个独占的锁，然后再操作资源

这会导致如果持有锁的事务执行时间较长，那么之后排队的事务完成时间也会越来越久，在并发情况下，性能不佳，但是事务不会产生冲突

---

4.4 什么是共享锁、排他锁？

共享锁和排他锁是悲观锁的不同实现，它俩都属于悲观锁的范畴

---

共享锁

又称读锁，是读取操作创建的锁

若事务对数据对象加上共享锁，则其他事务之鞥呢再对这个数据对象加共享锁，而不能加排他锁，直到事务释放共享锁

读操作由于不会修改数据，所以是可以大家在对的过程都加上读锁，也就是读时共享

---

排他锁

又称写锁，在修改操作时的锁

若事务对数据对象加上排他锁，则只允许当前持有排他锁的事务，读取和修改这个数据对象

其他任何事务不能再对这个数据对象加任何类型的锁，直到事务释放他的排他锁

排他锁也就是和写时互斥

---

4.5 MySQL死锁如何造成的？

两个事务都持有对方需要的锁，并且在等待对方释放，并且双方都不会释放自己的锁

这就好比你有一个人质，对方有一个人质，你们俩去谈判说换人。你让对面放人，对面让你放人

---

4.6 MySQL如何查看锁的状态？

查询是否锁表

show OPEN TABLES where In_use >0;

查询进程

show processlist
#查询到相对应的进程，之后kill id

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务

SELECT  * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

---

4.7 死锁如何避免呢？

如果不同程序并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率

对于非常人容易产生死锁的业务部分，可以升级锁的颗粒度，通过表级锁定减少死锁产生的概率

---

4.8 InnoDB默认是如何对待死锁的？

• 等待，直到超时INNODB_LOCK_WAIT_timeout=50s
• 发起死锁检测，主动回滚一条事务，让其他事务继续执行innodb_deadlock_detect=on

---

4.9 优化锁方面你有什么建议？

减少锁持有时间

减少锁粒度，少用表锁，缩小锁定对象的范围，从而减小锁冲突的可能性，进而提高并发能力

读写分离锁来替换独占锁，允许多线程同时读，减少操作之间相互等待，有效的提高性能

---

5. MySQL优化相关

5.1 配置文件的优化

参数设置贵在合适，而不是越高越好，看你的服务器性能结合压力测试来选择

设置缓存表大小

table_cache = 1024

物理内存越大，设置就越大，默认为2402，调到512-1024最佳

该设置可以为连接用户提高查询速度

设置cpu数量

innodb_thread_concurrency=8

你的服务器CPU有几个就设置为几，建议用默认一般为8

调整读缓冲区大小

read_buffer_size=4M

如果对表的顺序扫描请求非常频繁，并且扫描还太慢，可以通过增加这个属性提高性能

设置单个查询能使用的缓存大小

query_cache_limit = 4M

如果查询结果却是量级较大，可以考虑调高这个属性

调整索引和行数据缓冲innodb

innodb_buffer_pool_size = 105M

innodb使用缓冲池来缓存索引和行数据，该值设置的越大，则磁盘IO越少

一般将该值设为物理内存的80%

设置查询缓存大小

query_chcae_size = 64M

这个属性用于缓存SELECT查询结果

如果有许多返回相同查询结果的SELECT查询，并且很少改变表

可以设置该属性大于0，可以极大改善查询效率

---

5.2 SQL语句的优化

SQL语句主要优化查询，而查询主要目的是要快，那么就得减少IO次数，多多利用索引

---

• 对条件/排序查询进行优化，应避免全表扫描，在where及order by涉及的列上建立索引
• 避免在where子句中对字段进行NULL值判断，虽然建表时NULL是默认值，但大多数的时候应该使用NOT NULL去判断，使用一些特殊的值如0、-1作为默认值，因为含有NULL的列在复合索引中是无效的
• 应不命中索引尽量避免在子句中使用!=或<>操作符，这俩操作符不能使用索引，而以下操作可以使用索引：<、<=、=、>、>=、BETWEEN、IN
• 应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用UNION合并查询
• in和not in也要慎用，否则会导致全表扫描,对于连续的数值,能用between就不要用in了
• 避免在where子句中对字段进行函数、运算、类型转换等操作，这样不会利用索引
• 一个表的索引叔最好不要超过6个，虽然一张表支持创建最多16个索引，但是若太多索引则会降低了insert及update的效率
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销，并且还要注意避免使用时类型转换
• 避免向客户端返回大量数据，尤其这数据里还有很多是无用的数据，解析麻烦，渲染慢，还传输费时
• 在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数

---

5.3 表结构的优化

• 给字段选取最合适的数据类型
• 数据类型的宽度尽可能的小
• 允许部分数据冗余，可以节省连表查询的次数
• 字段要尽可能的设置为NOT NULL特别是使用了索引的字段

---

5.4 索引的优化

全职匹配心上人(这是基本原则)、最左前缀要遵行(联合索引一般都围绕最左前缀优化)

带头大哥活才行(联合索引从最左边字段开始使用)、中间兄弟规矩行(不能跳过中间地段，跳过后索引无效)

索引列上少计算(索引列上尽量不要进行计算)、范围之后全完蛋(where后面使用范围查询的之后的索引无效)

like百分最右写(%号写最右边，写左边会导致索引失效)、覆盖索引别写星(尽量避免select * 这样的语句，能写索引列最好)

空值不等还有or,索引失效最无情(is null、is not null、!=、<>、or会导致索引无效)

---

6. MySQL主从相关

6.1 主从复制的意义是啥？

为什么要做主从复制

在业务复杂的系统中，有这么一个情景，有一句SQL语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务

使用主从复制，让主库负责写，从库负责读，实现读写分离

这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运行

除此之外，主从复制 还可以做数据的热备，主库宕机后能够及时替换主库，保证业务可用性

再牛逼点就是随着架构的扩展，业务量越来越大，I/O访问频率过高，单机无法满足压力负载，此时做多库的存储，降低单机上的磁盘I/O访问的频率，可以让用户的访问更加顺心

---

6.2 如何实现的主从呢？

首先需要俩MySQL环境一致的主机

数据库有个bin-log二进制文件，记录了所有操作的sql语句

我们的目标就是把主库的bin-log文件的sql语句复制到从库上

然后把这些数据写入到从库的relay-log中继日志中

再把这个relay-log日志里的sql语句执行一遍将主库的操作执行到从库即可

---

需要三个线程来操作

主库的bin-log输出线程：每当有从库连接到主库的时候，主库都会创建一个线程然后发送bin-log内容到从库

在从库里，当复制开始的时候，从库就会创建两个线程进行处理

从库I/O线程：当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送bin-log里面的更新记录到从库上。从库I/O线程读取主库的bin-log输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件

从库的SQL线程：从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行

---

6.3 主从复制的模式有几种？

异步复制(Asynchronous replication)

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返回给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题

主库如果宕掉了，此时之前主库已经提交的事务可能并没有传到从库上

如果此时，强行将从库提升为主库，可能导致新上的主库的数据不完整

全同步复制(Fullly synchronous replication)

指当主库执行完一个事务，所有的从库 都执行了该事务才返回给客户端

因为需要等待所有从库执行完该事务才能放回，所有全同步复制的性能必然会受到严重的影响

半同步复制(Semisynchronous replication)

这种复制模式介于异步复制和全同步复制之间

主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写入relay log中才返回给客户端

相当于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间

所以，半同步复制最好在低延时的网络中使用

---

6.4 主从复制过程出现过什么问题？

主机宕机，数据可能会丢失

解决方案

使用半同步复制

---

从库中同步主库数据文件不一致

解决方案

找到错误日志路径，查看错误日志得知是主从数据库直接的bin-log或者file-pos位置不一致或者查看从库的2个线程是否就绪，是不是有线程不工作了

---

6.5 主从复制同步过程延迟较大怎么办？

主从同步的延迟的原因

当主库的TPS(系统吞吐量参数)并发较高时，由于主库上面是多线程写入的，而从库的SQL线程是单线程的，导致从库SQL可能会跟不上主库的处理速度

解决方案

网络方面：将从库分布在相同局域网内或网络延迟较小的环境中,比如手机和电脑之间的备份

硬件方面：从库配置更好的硬件，提升随机写的性能

配置方面：从库关闭bin-log日志记录，并且提高缓冲池大小，让更多操作在内存中完成，减少磁盘操作

架构方面：比如在事务中尽量对主库读写，其他非事务中的读在从库，消除一部分延迟带来的数据库不一致。增加缓存降低一些从库的负载

---

6.6 主从复制后DJANGO如何能实现写主读从呢？

通过编写路由表，手动指明读写所使用的数据库连接配置即可

首先在django中加入主和从的数据库配置

DATABASES = {
    'default':{
        'ENGINE':'django.db.backends.mysql',
        'HOST':'192.168.1.101',
        ...
    },
    'slave':{
         'ENGINE':'django.db.backends.mysql',
        'HOST':'192.168.1.102',
        ...
    }
}

编写路由表

class MasterSlaveDBRouter:
    def db_for_read(self,model,**hints):       #读数据库使用从
        return "slave"
    def db_for_write(self,model,**hints):      #写数据库使用主
        return "default"
    def allow_relation(self,obj1,obj2,**hints):
        return True

将路由表加入settings使其生效

DATABASE_ROUTERRS = ['pro.utils.MasterSlaveDBRouter']

---

7.其他

7.1什么是表分区？

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的容易管理的部分

从逻辑上看，只有一张表，但是底层却是由多个物理分区组成

好处

分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备

和单个磁盘或者文件系统相比，可以存储更多数据

在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率

分区表更容易维护，比如想批量删除大量数据可以清除整个分区

可以使用分区表来避免某些特殊的瓶颈，例如innodb的单个索引的互斥访问

---

7.2 什么是三范式？

第一范式：原子性，就是属性不可分割，每个字段都应该是不可再拆分的

比如一个字段是姓名(NAME)，在国内的话通常理解姓名都是一个不可再拆分的单位，这时候就符合第一范式

但是在国外的话还要分为FIRST NAME(名，西方人名的第一个字)和LAST NAME(姓)，这时候姓名这个字段就是还可以拆分为更小的单位字段，就不符合第一范式了

---

第二范式：主键约束，就是要求表中要有主键，表中其他字段都依赖于主键

比如在第一范式的基础上，有个学生表，学生表中有一个值是唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思就是相关的意思，因为学号的值也是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002里去

---

第三范式：外键约束，就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联来避免这样的问题

比如有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任也存到这个学生表中，那就会造成数据大量的冗余，表中会存储大量重复的主任信息，不如直接用个外键关联系的表

---

7.3 什么是反三范式？

比如学生表关联老师表，现在查询学生的时候总要获取到对应的老师信息，但由于学生表中只有外键关联老师表，只能通过连表查询获取对应老师信息，不让入直接把老师的姓名字段拿出来写入学生表，虽然看起来违背了三范式，但是却减少了查询的性能损耗，这就是反三范式

---

7.4 MySQL中的VarChar和Char有什么区别？

char是定长字符串，长度是固定的

如果插入数据的长度小于char的固定长度时，则用空格填充

因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，和编码无关

---

varchar表示是可变长字符串，长度可以伸缩

插入的数据是多长，就按照多长来存储

varchar在存取方面与char相反，它存取慢，因为长度不固定

但正因如此，不占据多余的空间，是时间换空间的做法

对于varchar来说，最多能存放的字符个数为65535

---

总之，char更快，varchar更小，彼此都具备优秀的特质，怎么使用就看业务

---

7.5 MySQL的binlog日志有哪几种录入格式

Stetament: 每一条会修改数据的sql都会记录在binlog中

Row：不记录sql语句的上下文相关信息，仅保存那条记录被修改

Mixedlevel：是以上两种的混合使用，根据执行的sql来选择Stetament和Row保存日志

---

7.6 SQL注入是啥？

SQL注入是一种非常常见的数据库攻击手段，SQL漏洞也是网络世界中最普遍的漏洞之一

SQL注入其实就是恶意用户通过在表单中填写包含SQL关键字的数据来使数据库执行非常规代码的过程

比如输入密码时，输入的是一个SQL的表达式：'or '1==1'

那么在一些弱类型语言，比如PHP就会把这样的语句真的以表达式来处理，而不是字符串

---

7.7 什么是SQL慢查询日志？

一说到慢查询就要想到的是查询的时间过长，是查询性能低下，给用户带来了不良的体验

造成这样的最基本原因是访问的数据太多，某些查询可能不可避免地需要筛选大量的数据

MySQL可以记录查询超过指定时间的语句，并且可以将这些SQL语句记在慢查询日志里，这个功能就叫做SQL慢查询日志

开启这个功能后可以查看究竟是哪些语句在慢查询，方便我们去对这些SQL进行优化

---

7.8 MySQL人如何开启慢查询日志？

先查看慢查询是否已经开启了

show variables like '%quer%';

log_slow_queries状态为OFF,说明当前并没有开启慢查询，那么我们就来开启他

找到MySQL的配置文件my.ini,在mysqld配置下方加入慢查询的配置语句

log_slow_queries = log_path   #存放日志的位置
long_query_time = 5   #最长执行时间

log_slow_queries : 慢查询的日志存储位置

---

7.9 如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储

这样可以节省空间且提高检索效率

---

7.10 什么是水平分表，什么又是垂直分表？

水平分表

水平分表就是把一个表的数据分开存储，每个表的结构都一样

只不过每个表放的数据是不同的，所有表的数据加起来就是全部数据

拆分前

用户t_user含有字段：**id、name、sex、age、create_time

拆分后

假设现在有需求统计用户性别，那么可以建立2张表，t_user_male和t_user_female

当用户为男时，存入t_user_male表中

当用户为女时，存入t_user_female表中

这样用户表的数据会一分为二，单表数据量会缩小，执行数据库操作时效率会增高

如果后期表数据过大时，还可以继续按照年龄段水平分表，分表策略还要根据实际情形来制定

---

垂直分表

垂直分表就是把一个表拆分为多个表，每个表结构都不一样

表与表之间通过外键性质的字段进行关联，一般情况是根据表字段查询次数来拆分表

查询次数少的字段根据提取到一个表或多个表中

查询次数多的字段根据也提取到一个表或多个表中

---

拆分前

假设文章表t_article含有字段：id、content、url、create_time

拆分后

t_article拆分为t_article和t_article_content

t_article中字段：id、url、create_time

t_article_content中字段：id、content、article_id、create_time

因为content字段包含的数据较多，影响查询效率且查询热度低

所以将content字段单独提取出来，并在表中加入article_id与t_article进行关联

---