关于mysql:三天吃透MySQL面试八股文

本文曾经收录到Github仓库，该仓库蕴含计算机根底、Java根底、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等外围知识点，欢送star~

Github地址：https://github.com/Tyson0314/Java-learning

事务的四大个性？

事务个性ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性是指事务蕴含的所有操作要么全副胜利，要么全副失败回滚。
• 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比方a与b账户共有1000块，两人之间转账之后无论胜利还是失败，它们的账户总和还是1000。
• 隔离性。跟隔离级别相干，如read committed，一个事务只能读到曾经提交的批改。
• 持久性是指一个事务一旦被提交了，那么对数据库中的数据的扭转就是永久性的，即使是在数据库系统遇到故障的状况下也不会失落提交事务的操作。

数据库的三大范式

第一范式1NF

确保数据库表字段的原子性。

比方字段 userInfo: 广东省 10086' ，按照第一范式必须拆分成 userInfo: 广东省 userTel: 10086两个字段。

第二范式2NF

首先要满足第一范式，另外蕴含两局部内容，一是表必须有一个主键；二是非主键列必须齐全依赖于主键，而不能只依赖于主键的一部分。

举个例子。假设选课关系表为student_course(student_no, student_name, age, course_name, grade, credit)，主键为(student_no, course_name)。其中学分齐全依赖于课程名称，姓名年龄齐全依赖学号，不合乎第二范式，会导致数据冗余（学生选n门课，姓名年龄有n条记录）、插入异样（插入一门新课，因为没有学号，无奈保留新课记录）等问题。

应该拆分成三个表：学生：student(stuent_no, student_name, 年龄)；课程：course(course_name, credit)；选课关系：student_course_relation(student_no, course_name, grade)。

第三范式3NF

首先要满足第二范式，另外非主键列必须间接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的状况。

假设学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone)，主键为”学号”，其中学院id依赖于学号，而学院地点和学院电话依赖于学院id，存在传递依赖，不合乎第三范式。

能够把学生关系表分为如下两个表：学生：(student_no, student_name, age, academy_id)；学院：(academy_id, academy_telephone)。

2NF和3NF的区别？

• 2NF根据是非主键列是否齐全依赖于主键，还是依赖于主键的一部分。
• 3NF根据是非主键列是间接依赖于主键，还是间接依赖于非主键。

事务隔离级别有哪些？

先理解下几个概念：脏读、不可反复读、幻读。

• 脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
• 不可反复读是指在对于数据库中的某行记录，一个事务范畴内屡次查问却返回了不同的数据值，这是因为在查问距离，另一个事务批改了数据并提交了。
• 幻读是当某个事务在读取某个范畴内的记录时，另外一个事务又在该范畴内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的论断不能撑持之后业务的执行。假如事务要新增一条记录，主键为id，在新增之前执行了select，没有发现id为xxx的记录，但插入时呈现主键抵触，这就属于幻读，读取不到记录却发现主键抵触是因为记录实际上曾经被其余的事务插入了，但以后事务不可见。

不可反复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可反复读则是读取了前一事务提交的数据。

事务隔离就是为了解决下面提到的脏读、不可反复读、幻读这几个问题。

MySQL数据库为咱们提供的四种隔离级别：

• Serializable (串行化)：通过强制事务排序，使之不可能互相抵触，从而解决幻读问题。
• Repeatable read (可反复读)：MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行，解决了不可反复读的问题。
• Read committed (读已提交)：一个事务只能看见曾经提交事务所做的扭转。可防止脏读的产生。
• Read uncommitted (读未提交)：所有事务都能够看到其余未提交事务的执行后果。

查看隔离级别：

select @@transaction_isolation;

设置隔离级别：

set session transaction isolation level read uncommitted;

生产环境数据库个别用的什么隔离级别呢？

生产环境大多应用RC。为什么不是RR呢？

可反复读(Repeatable Read)，简称为RR
读已提交(Read Commited)，简称为RC

原因一：在RR隔离级别下，存在间隙锁，导致呈现死锁的几率比RC大的多！
原因二：在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行!

也就是说，RC的并发性高于RR。

并且大部分场景下，不可反复读问题是能够承受的。毕竟数据都曾经提交了，读出来自身就没有太大问题！

互联网我的项目中mysql应该选什么事务隔离级别

编码和字符集的关系

咱们平时能够在编辑器上输出各种中文英文字母，但这些都是给人读的，不是给计算机读的，其实计算机真正保留和传输数据都是以二进制0101的格局进行的。

那么就须要有一个规定，把中文和英文字母转化为二进制。其中d对应十六进制下的64，它能够转换为01二进制的格局。于是字母和数字就这样一一对应起来了，这就是ASCII编码格局。

它用一个字节，也就是8位来标识字符，根底符号有128个，扩大符号也是128个。也就只能示意下英文字母和数字。

这显著不够用。于是，为了标识中文，呈现了GB2312的编码格局。为了标识希腊语，呈现了greek编码格局，为了标识俄语，整了cp866编码格局。

为了对立它们，于是呈现了Unicode编码格局，它用了2~4个字节来示意字符，这样实践上所有符号都能被收录进去，并且它还齐全兼容ASCII的编码，也就是说，同样是字母d，在ASCII用64示意，在Unicode里还是用64来示意。

但不同的中央是ASCII编码用1个字节来示意，而Unicode用则两个字节来示意。

同样都是字母d，unicode比ascii多应用了一个字节，如下：

D   ASCII:           01100100
D Unicode:  00000000 01100100

能够看到，下面的unicode编码，后面的都是0，其实用不上，但还占了个字节，有点节约。如果咱们能做到该暗藏时暗藏，这样就能省下不少空间，按这个思路，就是就有了UTF-8编码。

总结一下，依照肯定规定把符号和二进制码对应起来，这就是编码。而把n多这种曾经编码的字符聚在一起，就是咱们常说的字符集。

比方utf-8字符集就是所有utf-8编码格局的字符的合集。

想看下mysql反对哪些字符集。能够执行 show charset;

utf8和utf8mb4的区别

下面提到utf-8是在unicode的根底上做的优化，既然unicode有方法示意所有字符，那utf-8也一样能够示意所有字符，为了防止混同，我在前面叫它大utf8。

mysql反对的字符集中有utf8和utf8mb4。

先说utf8mb4编码，mb4就是most bytes 4的意思，从上图最左边的Maxlen能够看到，它最大反对用4个字节来示意字符，它简直能够用来示意目前已知的所有的字符。

再说mysql字符集里的utf8，它是数据库的默认字符集。但留神，此utf8非彼utf8，咱们叫它小utf8字符集。为什么这么说，因为从Maxlen能够看出，它最多反对用3个字节去示意字符，按utf8mb4的命名形式，精确点应该叫它utf8mb3。

utf8 就像是阉割版的utf8mb4，只反对局部字符。比方emoji表情，它就不反对。

而mysql反对的字符集里，第三列，collation，它是指字符集的比拟规定。

比方，”debug”和”Debug”是同一个单词，但它们大小写不同，该不该判为同一个单词呢。

这时候就须要用到collation了。

通过SHOW COLLATION WHERE Charset = 'utf8mb4';能够查看到utf8mb4下反对什么比拟规定。

如果collation = utf8mb4_general_ci，是指应用utf8mb4字符集的前提下，挨个字符进行比拟（general），并且不辨别大小写（_ci，case insensitice）。

这种状况下，”debug”和”Debug”是同一个单词。

如果改成collation=utf8mb4_bin，就是指挨个比拟二进制位大小。

于是”debug”和”Debug”就不是同一个单词。

那utf8mb4比照utf8有什么劣势吗？

咱们晓得数据库表里，字段类型如果是char(2)的话，外面的2是指字符个数，也就是说不论这张表用的是什么编码的字符集，都能放上2个字符。

而char又是固定长度，为了能放下2个utf8mb4的字符，char会默认保留2*4（maxlen=4）= 8个字节的空间。

如果是utf8mb3，则会默认保留 2 * 3 (maxlen=3) = 6个字节的空间。也就是说，在这种状况下，utf8mb4会比utf8mb3多应用一些空间。

索引

什么是索引？

索引是存储引擎用于进步数据库表的访问速度的一种数据结构。它能够比作一本字典的目录，能够帮你疾速找到对应的记录。

索引个别存储在磁盘的文件中，它是占用物理空间的。

索引的优缺点？

长处：

• 放慢数据查找的速度
• 为用来排序或者是分组的字段增加索引，能够放慢分组和排序的速度
• 放慢表与表之间的连贯

毛病：

• 建设索引须要占用物理空间
• 会升高表的增删改的效率，因为每次对表记录进行增删改，须要进行动静保护索引，导致增删改工夫变长

索引的作用？

数据是存储在磁盘上的，查问数据时，如果没有索引，会加载所有的数据到内存，顺次进行检索，读取磁盘次数较多。有了索引，就不须要加载所有数据，因为B+树的高度个别在2-4层，最多只须要读取2-4次磁盘，查问速度大大晋升。

什么状况下须要建索引？

1. 常常用于查问的字段
2. 常常用于连贯的字段建设索引，能够放慢连贯的速度
3. 常常须要排序的字段建设索引，因为索引曾经排好序，能够放慢排序查问速度

什么状况下不建索引？

1. where条件中用不到的字段不适宜建设索引
2. 表记录较少。比方只有几百条数据，没必要加索引。
3. 须要常常增删改。须要评估是否适宜加索引
4. 参加列计算的列不适宜建索引
5. 区分度不高的字段不适宜建设索引，如性别，只有男/女/未知三个值。加了索引，查问效率也不会进步。

索引的数据结构

索引的数据结构次要有B+树和哈希表，对应的索引别离为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引，默认的索引类型为B+树索引。

B+树索引

B+ 树是基于B 树和叶子节点程序拜访指针进行实现，它具备B树的平衡性，并且通过程序拜访指针来进步区间查问的性能。

在 B+ 树中，节点中的 key 从左到右递增排列，如果某个指针的左右相邻 key 别离是 key_i 和 key_i+1，则该指针指向节点的所有 key 大于等于 key_i 且小于等于 key_i+1。

进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，而后递归地在指针所指向的节点进行查找。直到查找到叶子节点，而后在叶子节点上进行二分查找，找出key所对应的数据项。

MySQL 数据库应用最多的索引类型是BTREE索引，底层基于B+树数据结构来实现。

mysql> show index from blog\G;
*************************** 1. row ***************************
        Table: blog
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: blog_id
    Collation: A
  Cardinality: 4
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

哈希索引

哈希索引是基于哈希表实现的，对于每一行数据，存储引擎会对索引列进行哈希计算失去哈希码，并且哈希算法要尽量保障不同的列值计算出的哈希码值是不同的，将哈希码的值作为哈希表的key值，将指向数据行的指针作为哈希表的value值。这样查找一个数据的工夫复杂度就是O(1)，个别多用于准确查找。

Hash索引和B+树索引的区别？

• 哈希索引不反对排序，因为哈希表是无序的。
• 哈希索引不反对范畴查找。
• 哈希索引不反对含糊查问及多列索引的最左前缀匹配。
• 因为哈希表中会存在哈希抵触，所以哈希索引的性能是不稳固的，而B+树索引的性能是绝对稳固的，每次查问都是从根节点到叶子节点。

为什么B+树比B树更适宜实现数据库索引？

• 因为B+树的数据都存储在叶子结点中，叶子结点均为索引，不便扫库，只须要扫一遍叶子结点即可，然而B树因为其分支结点同样存储着数据，咱们要找到具体的数据，须要进行一次中序遍历按序来扫，所以B+树更加适宜在区间查问的状况，而在数据库中基于范畴的查问是十分频繁的，所以通常B+树用于数据库索引。
• B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中能够寄存更多的节点。缩小更多的I/O收入。
• B+树的查问效率更加稳固，任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查问的门路长度雷同，导致每一个数据的查问效率相当。

索引有什么分类？

1、主键索引：名为primary的惟一非空索引，不容许有空值。

2、惟一索引：索引列中的值必须是惟一的，然而容许为空值。惟一索引和主键索引的区别是：惟一索引字段能够为null且能够存在多个null值，而主键索引字段不能够为null。惟一索引的用处：惟一标识数据库表中的每条记录，次要是用来避免数据反复插入。创立惟一索引的SQL语句如下：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

3、组合索引：在表中的多个字段组合上创立的索引，只有在查问条件中应用了这些字段的右边字段时，索引才会被应用，应用组合索引时需遵循最左前缀准则。

4、全文索引：只能在CHAR、VARCHAR和TEXT类型字段上应用全文索引。

5、一般索引：一般索引是最根本的索引，它没有任何限度，值能够为空。

什么是最左匹配准则？

如果 SQL 语句中用到了组合索引中的最右边的索引，那么这条 SQL 语句就能够利用这个组合索引去进行匹配。当遇到范畴查问(>、<、between、like)就会进行匹配，前面的字段不会用到索引。

对(a,b,c)建设索引，查问条件应用 a/ab/abc 会走索引，应用 bc 不会走索引。

对(a,b,c,d)建设索引，查问条件为a = 1 and b = 2 and c > 3 and d = 4，那么a、b和c三个字段能用到索引，而d无奈应用索引。因为遇到了范畴查问。

如下图，对(a, b) 建设索引，a 在索引树中是全局有序的，而 b 是全局无序，部分有序（当a相等时，会依据b进行排序）。间接执行b = 2这种查问条件无奈应用索引。

当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a = 2时候，b的值为1，4也是有序状态。 当执行a = 1 and b = 2时a和b字段能用到索引。而执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范畴，不是固定的，在这个范畴内b值不是有序的，因而b字段无奈应用索引。

什么是汇集索引？

InnoDB应用表的主键结构主键索引树，同时叶子节点中寄存的即为整张表的记录数据。汇集索引叶子节点的存储是逻辑上间断的，应用双向链表连贯，叶子节点依照主键的程序排序，因而对于主键的排序查找和范畴查找速度比拟快。

汇集索引的叶子节点就是整张表的行记录。InnoDB 主键应用的是聚簇索引。汇集索引要比非汇集索引查问效率高很多。

对于InnoDB来说，汇集索引个别是表中的主键索引，如果表中没有显示指定主键，则会抉择表中的第一个不容许为NULL的惟一索引。如果没有主键也没有适合的惟一索引，那么InnoDB外部会生成一个暗藏的主键作为汇集索引，这个暗藏的主键长度为6个字节，它的值会随着数据的插入自增。

什么是笼罩索引？

select的数据列只用从索引中就可能获得，不须要回表进行二次查问，也就是说查问列要被所应用的索引笼罩。对于innodb表的二级索引，如果索引能笼罩到查问的列，那么就能够防止对主键索引的二次查问。

不是所有类型的索引都能够成为笼罩索引。笼罩索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以MySQL应用b+树索引做笼罩索引。

对于应用了笼罩索引的查问，在查问后面应用explain，输入的extra列会显示为using index。

比方user_like 用户点赞表，组合索引为(user_id, blog_id)，user_id和blog_id都不为null。

explain select blog_id from user_like where user_id = 13;

explain后果的Extra列为Using index，查问的列被索引笼罩，并且where筛选条件合乎最左前缀准则，通过索引查找就能间接找到符合条件的数据，不须要回表查问数据。

explain select user_id from user_like where blog_id = 1;

explain后果的Extra列为Using where; Using index， 查问的列被索引笼罩，where筛选条件不合乎最左前缀准则，无奈通过索引查找找到符合条件的数据，但能够通过索引扫描找到符合条件的数据，也不须要回表查问数据。

索引的设计准则？

• 对于常常作为查问条件的字段，应该建设索引，以进步查问速度
• 为常常须要排序、分组和联合操作的字段建设索引
• 索引列的区分度越高，索引的成果越好。比方应用性别这种区分度很低的列作为索引，成果就会很差。
• 防止给”大字段”建设索引。尽量应用数据量小的字段作为索引。因为MySQL在保护索引的时候是会将字段值一起保护的，那这样必然会导致索引占用更多的空间，另外在排序的时候须要破费更多的工夫去比照。
• 尽量应用短索引，对于较长的字符串进行索引时应该指定一个较短的前缀长度，因为较小的索引波及到的磁盘I/O较少，查问速度更快。
• 索引不是越多越好，每个索引都须要额定的物理空间，保护也须要破费工夫。
• 频繁增删改的字段不要建设索引。假如某个字段频繁批改，那就意味着须要频繁的重建索引，这必然影响MySQL的性能
• 利用最左前缀准则。

索引什么时候会生效？

导致索引生效的状况：

• 对于组合索引，不是应用组合索引最右边的字段，则不会应用索引
• 以%结尾的like查问如%abc，无奈应用索引；非%结尾的like查问如abc%，相当于范畴查问，会应用索引
• 查问条件中列类型是字符串，没有应用引号，可能会因为类型不同产生隐式转换，使索引生效
• 判断索引列是否不等于某个值时
• 对索引列进行运算
• 查问条件应用or连贯，也会导致索引生效

什么是前缀索引？

有时须要在很长的字符列上创立索引，这会造成索引特地大且慢。应用前缀索引能够防止这个问题。

前缀索引是指对文本或者字符串的前几个字符建设索引，这样索引的长度更短，查问速度更快。

创立前缀索引的关键在于抉择足够长的前缀以保障较高的索引选择性。索引选择性越高查问效率就越高，因为选择性高的索引能够让MySQL在查找时过滤掉更多的数据行。

建设前缀索引的形式：

// email列创立前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));

索引下推

参考我的另一篇文章：图解索引下推！

常见的存储引擎有哪些？

MySQL中罕用的四种存储引擎别离是： MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默认的存储引擎为InnoDB。

InnoDB存储引擎

InnoDB是MySQL默认的事务型存储引擎，应用最宽泛，基于聚簇索引建设的。InnoDB外部做了很多优化，如可能主动在内存中创立自适应hash索引，以减速读操作。

长处：反对事务和解体修复能力；引入了行级锁和外键束缚。

毛病：占用的数据空间绝对较大。

实用场景：须要事务反对，并且有较高的并发读写频率。

MyISAM存储引擎

数据以严密格局存储。对于只读数据，或者表比拟小、能够容忍修复操作，能够应用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。

长处：访问速度快。

毛病：MyISAM不反对事务和行级锁，不反对解体后的平安复原，也不反对外键。

实用场景：对事务完整性没有要求；表的数据都会只读的。

MEMORY存储引擎

MEMORY引擎将数据全副放在内存中，访问速度较快，然而一旦零碎奔溃的话，数据都会失落。

MEMORY引擎默认应用哈希索引，将键的哈希值和指向数据行的指针保留在哈希索引中。

长处：访问速度较快。

毛病：

1. 哈希索引数据不是依照索引值顺序存储，无奈用于排序。
2. 不反对局部索引匹配查找，因为哈希索引是应用索引列的全部内容来计算哈希值的。
3. 只反对等值比拟，不反对范畴查问。
4. 当呈现哈希抵触时，存储引擎须要遍历链表中所有的行指针，逐行进行比拟，直到找到符合条件的行。

ARCHIVE存储引擎

ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩性能，领有高效的插入速度，然而这种引擎不反对索引，所以查问性能较差。

MyISAM和InnoDB的区别？

1. 存储构造的区别。每个MyISAM在磁盘上存储成三个文件。文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩大名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保留在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，个别为2GB。
2. 存储空间的区别。MyISAM反对反对三种不同的存储格局：动态表(默认，然而留神数据开端不能有空格，会被去掉)、动静表、压缩表。当表在创立之后并导入数据之后，不会再进行批改操作，能够应用压缩表，极大的缩小磁盘的空间占用。InnoDB须要更多的内存和存储，它会在主内存中建设其专用的缓冲池用于高速缓冲数据和索引。
3. 可移植性、备份及复原。MyISAM数据是以文件的模式存储，所以在跨平台的数据转移中会很不便。在备份和复原时可独自针对某个表进行操作。对于InnoDB，可行的计划是拷贝数据文件、备份 binlog，或者用mysqldump，在数据量达到几十G的时候就绝对麻烦了。
4. 是否反对行级锁。MyISAM 只反对表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表主动加锁，如果加锁当前的表满足insert并发的状况下，能够在表的尾部插入新的数据。而InnoDB 反对行级锁和表级锁，默认为行级锁。行锁大幅度提高了多用户并发操作的性能。
5. 是否反对事务和解体后的平安复原。 MyISAM 不提供事务反对。而InnoDB 提供事务反对，具备事务、回滚和解体修复能力。
6. 是否反对外键。MyISAM不反对，而InnoDB反对。
7. 是否反对MVCC。MyISAM不反对，InnoDB反对。应答高并发事务，MVCC比单纯的加锁更高效。
8. 是否反对汇集索引。MyISAM不反对汇集索引，InnoDB反对汇集索引。
9. 全文索引。MyISAM反对 FULLTEXT类型的全文索引。InnoDB不反对FULLTEXT类型的全文索引，然而innodb能够应用sphinx插件反对全文索引，并且成果更好。
10. 表主键。MyISAM容许没有任何索引和主键的表存在，索引都是保留行的地址。对于InnoDB，如果没有设定主键或者非空惟一索引，就会主动生成一个6字节的主键(用户不可见)。
11. 表的行数。MyISAM保留有表的总行数，如果select count(*) from table;会间接取出该值。InnoDB没有保留表的总行数，如果应用select count(*) from table；就会遍历整个表，耗费相当大，然而在加了where条件后，MyISAM和InnoDB解决的形式都一样。

MySQL有哪些锁？

按锁粒度分类，有行级锁、表级锁和页级锁。

1. 行级锁是mysql中锁定粒度最细的一种锁。示意只针对以后操作的行进行加锁。行级锁能大大减少数据库操作的抵触，其加锁粒度最小，但加锁的开销也最大。行级锁的类型次要有三类：

   • Record Lock，记录锁，也就是仅仅把一条记录锁上；
   • Gap Lock，间隙锁，锁定一个范畴，然而不蕴含记录自身；
   • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范畴，并且锁定记录自身。
2. 表级锁是mysql中锁定粒度最大的一种锁，示意对以后操作的整张表加锁，它实现简略，资源耗费较少，被大部分mysql引擎反对。最常应用的MyISAM与InnoDB都反对表级锁定。
3. 页级锁是 MySQL 中锁定粒度介于行级锁和表级锁两头的一种锁。表级锁速度快，但抵触多，行级抵触少，但速度慢。因而，采取了折衷的页级锁，一次锁定相邻的一组记录。

按锁级别分类，有共享锁、排他锁和意向锁。

1. 共享锁又称读锁，是读取操作创立的锁。其余用户能够并发读取数据，但任何事务都不能对数据进行批改（获取数据上的排他锁），直到已开释所有共享锁。
2. 排他锁又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其余事务不能再对A加任何类型的封闭。获准排他锁的事务既能读数据，又能批改数据。
3. 意向锁是表级锁，其设计目标次要是为了在一个事务中揭示下一行将要被申请锁的类型。InnoDB 中的两个表锁：

动向共享锁（IS）：示意事务筹备给数据行退出共享锁，也就是说一个数据行加共享锁前必须先获得该表的IS锁；

动向排他锁（IX）：相似下面，示意事务筹备给数据行退出排他锁，阐明事务在一个数据行加排他锁前必须先获得该表的IX锁。

意向锁是 InnoDB 主动加的，不须要用户干涉。

对于INSERT、UPDATE和DELETE，InnoDB 会主动给波及的数据加排他锁；对于个别的SELECT语句，InnoDB 不会加任何锁，事务能够通过以下语句显式加共享锁或排他锁。

共享锁：SELECT … LOCK IN SHARE MODE;

排他锁：SELECT … FOR UPDATE;

MVCC 实现原理？

MVCC(Multiversion concurrency control) 就是同一份数据保留多版本的一种形式，进而实现并发管制。在查问的时候，通过read view和版本链找到对应版本的数据。

作用：晋升并发性能。对于高并发场景，MVCC比行级锁开销更小。

MVCC 实现原理如下：

MVCC 的实现依赖于版本链，版本链是通过表的三个暗藏字段实现。

• DB_TRX_ID：以后事务id，通过事务id的大小判断事务的工夫程序。
• DB_ROLL_PTR：回滚指针，指向以后行记录的上一个版本，通过这个指针将数据的多个版本连贯在一起形成undo log版本链。
• DB_ROW_ID：主键，如果数据表没有主键，InnoDB会主动生成主键。

每条表记录大略是这样的：

应用事务更新行记录的时候，就会生成版本链，执行过程如下：

1. 用排他锁锁住该行；
2. 将该行本来的值拷贝到undo log，作为旧版本用于回滚；
3. 批改以后行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就造成一条版本链。

上面举个例子不便大家了解。

1、初始数据如下，其中DB_ROW_ID和DB_ROLL_PTR为空。

2、事务A对该行数据做了批改，将age批改为12，成果如下：

3、之后事务B也对该行记录做了批改，将age批改为8，成果如下：

4、此时undo log有两行记录，并且通过回滚指针连在一起。

接下来理解下read view的概念。

read view能够了解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t工夫点拍的“照片”上取数据。

在read view外部保护一个沉闷事务链表，示意生成read view的时候还在沉闷的事务。这个链表蕴含在创立read view之前还未提交的事务，不蕴含创立read view之后提交的事务。

不同隔离级别创立read view的机会不同。

• read committed：每次执行select都会创立新的read_view，保障能读取到其余事务曾经提交的批改。
• repeatable read：在一个事务范畴内，第一次select时更新这个read_view，当前不会再更新，后续所有的select都是复用之前的read_view。这样能够保障事务范畴内每次读取的内容都一样，即可反复读。

read view的记录筛选形式

前提：DATA_TRX_ID 示意每个数据行的最新的事务ID；up_limit_id示意以后快照中的最先开始的事务；low_limit_id示意以后快照中的最慢开始的事务，即最初一个事务。

• 如果DATA_TRX_ID < up_limit_id：阐明在创立read view时，批改该数据行的事务已提交，该版本的记录可被以后事务读取到。
• 如果DATA_TRX_ID >= low_limit_id：阐明以后版本的记录的事务是在创立read view之后生成的，该版本的数据行不能够被以后事务拜访。此时须要通过版本链找到上一个版本，而后从新判断该版本的记录对以后事务的可见性。

• 如果up_limit_id <= DATA_TRX_ID < low_limit_i：

  1. 须要在沉闷事务链表中查找是否存在ID为DATA_TRX_ID的值的事务。
  2. 如果存在，因为在沉闷事务链表中的事务是未提交的，所以该记录是不可见的。此时须要通过版本链找到上一个版本，而后从新判断该版本的可见性。
  3. 如果不存在，阐明事务trx_id 曾经提交了，这行记录是可见的。

总结：InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保留有历史版本记录，通过read view 判断以后版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，持续进行判断，直到找到一个可见的版本。

快照读和以后读

表记录有两种读取形式。

• 快照读：读取的是快照版本。一般的SELECT就是快照读。通过mvcc来进行并发管制的，不必加锁。
• 以后读：读取的是最新版本。UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是以后读。

快照读状况下，InnoDB通过mvcc机制防止了幻读景象。而mvcc机制无奈防止以后读状况下呈现的幻读景象。因为以后读每次读取的都是最新数据，这时如果两次查问两头有其它事务插入数据，就会产生幻读。

上面举个例子阐明下：

1、首先，user表只有两条记录，具体如下：

2、事务a和事务b同时开启事务start transaction；

3、事务a插入数据而后提交；

insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);

4、事务b执行全表的update；

update user set user_name = 'a';

5、事务b而后执行查问，查到了事务a中插入的数据。（下图右边是事务b，左边是事务a。事务开始之前只有两条记录，事务a插入一条数据之后，事务b查问进去是三条数据）

以上就是以后读呈现的幻读景象。

那么MySQL是如何防止幻读？

• 在快照读状况下，MySQL通过mvcc来防止幻读。
• 在以后读状况下，MySQL通过next-key来防止幻读（加行锁和间隙锁来实现的）。

next-key包含两局部：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。

Serializable隔离级别也能够防止幻读，会锁住整张表，并发性极低，个别不会应用。

共享锁和排他锁

SELECT 的读取锁定次要分为两种形式：共享锁和排他锁。

select * from table where id<6 lock in share mode;--共享锁
select * from table where id<6 for update;--排他锁

这两种形式次要的不同在于LOCK IN SHARE MODE 多个事务同时更新同一个表单时很容易造成死锁。

申请排他锁的前提是，没有线程对该后果集的任何行数据应用排它锁或者共享锁，否则申请会受到阻塞。在进行事务操作时，MySQL会对查问后果集的每行数据增加排它锁，其余线程对这些数据的更改或删除操作会被阻塞（只能读操作），直到该语句的事务被commit语句或rollback语句完结为止。

SELECT... FOR UPDATE 应用注意事项：

1. for update 仅实用于innodb，且必须在事务范畴内能力失效。
2. 依据主键进行查问，查问条件为like或者不等于，主键字段产生表锁。
3. 依据非索引字段进行查问，会产生表锁。

bin log/redo log/undo log

MySQL日志次要包含查问日志、慢查问日志、事务日志、谬误日志、二进制日志等。其中比拟重要的是 bin log（二进制日志）和 redo log（重做日志）和 undo log（回滚日志）。

bin log

bin log是MySQL数据库级别的文件，记录对MySQL数据库执行批改的所有操作，不会记录select和show语句，次要用于复原数据库和同步数据库。

redo log

redo log是innodb引擎级别，用来记录innodb存储引擎的事务日志，不论事务是否提交都会记录下来，用于数据恢复。当数据库产生故障，innoDB存储引擎会应用redo log复原到产生故障前的时刻，以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit设置为1，那么在执行commit时会将redo log同步写到磁盘。

undo log

除了记录redo log外，当进行数据批改时还会记录undo log，undo log用于数据的撤回操作，它保留了记录批改前的内容。通过undo log能够实现事务回滚，并且能够依据undo log回溯到某个特定的版本的数据，实现MVCC。

bin log和redo log有什么区别？

1. bin log会记录所有日志记录，包含InnoDB、MyISAM等存储引擎的日志；redo log只记录innoDB本身的事务日志。
2. bin log只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有redo log一直写入磁盘。
3. bin log是逻辑日志，记录的是SQL语句的原始逻辑；redo log是物理日志，记录的是在某个数据页上做了什么批改。

讲一下MySQL架构？

MySQL次要分为 Server 层和存储引擎层：

• Server 层：次要包含连接器、查问缓存、分析器、优化器、执行器等，所有跨存储引擎的性能都在这一层实现，比方存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
• 存储引擎： 次要负责数据的存储和读取。server 层通过api与存储引擎进行通信。

Server 层根本组件

• 连接器： 当客户端连贯 MySQL 时，server层会对其进行身份认证和权限校验。
• 查问缓存: 执行查问语句的时候，会先查问缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会间接返回给客户端，如果没有命中，就会执行后续的操作。
• 分析器: 没有命中缓存的话，SQL 语句就会通过分析器，次要分为两步，词法剖析和语法分析，先看 SQL 语句要做什么，再查看 SQL 语句语法是否正确。
• 优化器： 优化器对查问进行优化，包含重写查问、决定表的读写程序以及抉择适合的索引等，生成执行打算。
• 执行器： 首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会依据执行打算去调用引擎的接口，返回后果。

分库分表

当单表的数据量达到1000W或100G当前，优化索引、增加从库等可能对数据库性能晋升成果不显著，此时就要思考对其进行切分了。切分的目标就在于缩小数据库的累赘，缩短查问的工夫。

数据切分能够分为两种形式：垂直划分和程度划分。

垂直划分

垂直划分数据库是依据业务进行划分，例如购物场景，能够将库中波及商品、订单、用户的表别离划分出成一个库，通过升高单库的大小来进步性能。同样的，分表的状况就是将一个大表依据业务性能拆分成一个个子表，例如商品根本信息和商品形容，商品根本信息个别会展现在商品列表，商品形容在商品详情页，能够将商品根本信息和商品形容拆分成两张表。

长处：行记录变小，数据页能够寄存更多记录，在查问时缩小I/O次数。

毛病：

• 主键呈现冗余，须要治理冗余列；
• 会引起表连贯JOIN操作，能够通过在业务服务器上进行join来缩小数据库压力；
• 仍然存在单表数据量过大的问题。

程度划分

程度划分是依据肯定规定，例如工夫或id序列值等进行数据的拆分。比方依据年份来拆分不同的数据库。每个数据库构造统一，然而数据得以拆分，从而晋升性能。

长处：单库（表）的数据量得以缩小，进步性能；切分出的表构造雷同，程序改变较少。

毛病：

• 分片事务一致性难以解决
• 跨节点join性能差，逻辑简单
• 数据分片在扩容时须要迁徙

什么是分区表？

分区是把一张表的数据分成N多个区块。分区表是一个独立的逻辑表，然而底层由多个物理子表组成。

当查问条件的数据分布在某一个分区的时候，查问引擎只会去某一个分区查问，而不是遍历整个表。在治理层面，如果须要删除某一个分区的数据，只须要删除对应的分区即可。

分区个别都是放在单机里的，用的比拟多的是工夫范畴分区，不便归档。只不过分库分表须要代码实现，分区则是mysql外部实现。分库分表和分区并不抵触，能够联合应用。

分区表类型

range分区，依照范畴分区。比方依照工夫范畴分区

CREATE TABLE test_range_partition(
       id INT auto_increment,
       createdate DATETIME,
       primary key (id,createdate)
   ) 
   PARTITION BY RANGE (TO_DAYS(createdate) ) (
      PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
      PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
      PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
      PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
      PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
      PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
      PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
      PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
      PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
      PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
      PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
      PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
   );

在/var/lib/mysql/data/能够找到对应的数据文件，每个分区表都有一个应用#分隔命名的表文件：

   -rw-r----- 1 MySQL MySQL    65 Mar 14 21:47 db.opt
   -rw-r----- 1 MySQL MySQL  8598 Mar 14 21:50 test_range_partition.frm
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
   -rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...

list分区

list分区和range分区类似，次要区别在于list是枚举值列表的汇合，range是间断的区间值的汇合。对于list分区，分区字段必须是已知的，如果插入的字段不在分区时的枚举值中，将无奈插入。

create table test_list_partiotion
   (
       id int auto_increment,
       data_type tinyint,
       primary key(id,data_type)
   )partition by list(data_type)
   (
       partition p0 values in (0,1,2,3,4,5,6),
       partition p1 values in (7,8,9,10,11,12),
       partition p2 values in (13,14,15,16,17)
   );

hash分区

能够将数据平均地散布到事后定义的分区中。

create table test_hash_partiotion
   (
       id int auto_increment,
       create_date datetime,
       primary key(id,create_date)
   )partition by hash(year(create_date)) partitions 10;

分区的问题？

1. 关上和锁住所有底层表的老本可能很高。当查问拜访分区表时，MySQL 须要关上并锁住所有的底层表，这个操作在分区过滤之前产生，所以无奈通过分区过滤来升高此开销，会影响到查问速度。能够通过批量操作来升高此类开销，比方批量插入、LOAD DATA INFILE和一次删除多行数据。
2. 保护分区的老本可能很高。例如重组分区，会先创立一个长期分区，而后将数据复制到其中，最初再删除原分区。
3. 所有分区必须应用雷同的存储引擎。

查问语句执行流程？

查问语句的执行流程如下：权限校验、查问缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子，查问语句如下：

select * from user where id > 1 and name = '大彬';

1. 首先查看权限，没有权限则返回谬误；
2. MySQL8.0以前会查问缓存，缓存命中则间接返回，没有则执行下一步；
3. 词法剖析和语法分析。提取表名、查问条件，查看语法是否有谬误；
4. 两种执行计划，先查 id > 1 还是 name = '大彬'，优化器依据本人的优化算法抉择执行效率最好的计划；
5. 校验权限，有权限就调用数据库引擎接口，返回引擎的执行后果。

更新语句执行过程？

更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）

举个例子，更新语句如下：

update user set name = '大彬' where id = 1;

1. 先查问到 id 为1的记录，有缓存会应用缓存。
2. 拿到查问后果，将 name 更新为大彬，而后调用引擎接口，写入更新数据，innodb 引擎将数据保留在内存中，同时记录redo log，此时redo log进入 prepare状态。
3. 执行器收到告诉后记录binlog，而后调用引擎接口，提交redo log为commit状态。
4. 更新实现。

为什么记录完redo log，不间接提交，而是先进入prepare状态？

假如先写redo log间接提交，而后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log复原数据，然而这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会失落这一条数据，同时主从同步也会失落这一条数据。

exist和in的区别？

exists用于对表面记录做筛选。exists会遍历表面，将外查问表的每一行，代入内查问进行判断。当exists里的条件语句可能返回记录行时，条件就为真，返回表面以后记录。反之如果exists里的条件语句不能返回记录行，条件为假，则表面以后记录被抛弃。

select a.* from A awhere exists(select 1 from B b where a.id=b.id)

in是先把后边的语句查出来放到长期表中，而后遍历长期表，将长期表的每一行，代入外查问去查找。

select * from Awhere id in(select id from B)

子查问的表比拟大的时候，应用exists能够无效缩小总的循环次数来晋升速度；当外查问的表比拟大的时候，应用in能够无效缩小对外查问表循环遍从来晋升速度。

truncate、delete与drop区别？

相同点：

1. truncate和不带where子句的delete、以及drop都会删除表内的数据。
2. drop、truncate都是DDL语句（数据定义语言），执行后会主动提交。

不同点：

1. truncate 和 delete 只删除数据不删除表的构造；drop 语句将删除表的构造被依赖的束缚、触发器、索引；
2. 一般来说，执行速度: drop > truncate > delete。

MySQL中int(10)和char(10)的区别？　

int(10)中的10示意的是显示数据的长度，而char(10)示意的是存储数据的长度。

having和where区别？

• 二者作用的对象不同，where子句作用于表和视图，having作用于组。
• where在数据分组前进行过滤，having在数据分组后进行过滤。

为什么要做主从同步？

1. 读写拆散，使数据库能撑持更大的并发。
2. 在主服务器上生成实时数据，而在从服务器上剖析这些数据，从而进步主服务器的性能。
3. 数据备份，保证数据的平安。

什么是MySQL主从同步？

主从同步使得数据能够从一个数据库服务器复制到其余服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。

因为复制是异步进行的，所以从服务器不须要始终连贯着主服务器，从服务器甚至能够通过拨号断断续续地连贯主服务器。通过配置文件，能够指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

乐观锁和乐观锁是什么？

数据库中的并发管制是确保在多个事务同时存取数据库中同一数据时不毁坏事务的隔离性和统一性以及数据库的统一性。乐观锁和乐观锁是并发管制次要采纳的技术手段。

• 乐观锁：假设会产生并发抵触，会对操作的数据进行加锁，直到提交事务，才会开释锁，其余事务能力进行批改。实现形式：应用数据库中的锁机制。
• 乐观锁：假如不会产生并发抵触，只在提交操作时查看是否数据是否被批改过。给表减少version字段，在批改提交之前查看version与原来取到的version值是否相等，若相等，示意数据没有被批改，能够更新，否则，数据为脏数据，不能更新。实现形式：乐观锁个别应用版本号机制或CAS算法实现。

用过processlist吗？

show processlist 或 show full processlist 能够查看以后 MySQL 是否有压力，正在运行的SQL，有没有慢SQL正在执行。返回参数如下：

1. id：线程ID，能够用kill id杀死某个线程
2. db：数据库名称
3. user：数据库用户
4. host：数据库实例的IP
5. command：以后执行的命令，比方Sleep，Query，Connect 等
6. time：耗费工夫，单位秒

7. state：执行状态，次要有以下状态：

   • Sleep，线程正在期待客户端发送新的申请
   • Locked，线程正在期待锁
   • Sending data，正在解决SELECT查问的记录，同时把后果发送给客户端
   • Kill，正在执行kill语句，杀死指定线程
   • Connect，一个从节点连上了主节点
   • Quit，线程正在退出
   • Sorting for group，正在为GROUP BY做排序
   • Sorting for order，正在为ORDER BY做排序
8. info：正在执行的SQL语句

MySQL查问 limit 1000,10 和limit 10 速度一样快吗？

两种查问形式。对应 limit offset, size 和 limit size 两种形式。

而其实 limit size ，相当于 limit 0, size。也就是从0开始取size条数据。

也就是说，两种形式的区别在于offset是否为0。

先来看下limit sql的外部执行逻辑。

MySQL外部分为server层和存储引擎层。个别状况下存储引擎都用innodb。

server层有很多模块，其中须要关注的是执行器是用于跟存储引擎打交道的组件。

执行器能够通过调用存储引擎提供的接口，将一行行数据取出，当这些数据齐全符合要求（比方满足其余where条件），则会放到后果集中，最初返回给调用mysql的客户端。

以主键索引的limit执行过程为例：

执行select * from xxx order by id limit 0, 10;，select前面带的是星号，也就是要求取得行数据的所有字段信息。

server层会调用innodb的接口，在innodb里的主键索引中获取到第0到10条残缺行数据，顺次返回给server层，并放到server层的后果集中，返回给客户端。

把offset搞大点，比方执行的是：select * from xxx order by id limit 500000, 10;

server层会调用innodb的接口，因为这次的offset=500000，会在innodb里的主键索引中获取到第0到（500000 + 10）条残缺行数据，返回给server层之后依据offset的值挨个摈弃，最初只留下最初面的size条，也就是10条数据，放到server层的后果集中，返回给客户端。

能够看出，当offset非0时，server层会从引擎层获取到很多无用的数据，而获取的这些无用数据都是要耗时的。

因而，mysql查问中 limit 1000,10 会比 limit 10 更慢。起因是 limit 1000,10 会取出1000+10条数据，并摈弃前1000条，这部分耗时更大。

深分页怎么优化？

还是以下面的SQL为空：select * from xxx order by id limit 500000, 10;

办法一：

从下面的剖析能够看出，当offset十分大时，server层会从引擎层获取到很多无用的数据，而当select前面是*号时，就须要拷贝残缺的行信息，拷贝残缺数据相比只拷贝行数据里的其中一两个列字段更消耗工夫。

因为后面的offset条数据最初都是不要的，没有必要拷贝残缺字段，所以能够将sql语句批改成：

select * from xxx  where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;

先执行子查问 select id from xxx by id limit 500000, 1, 这个操作，其实也是将在innodb中的主键索引中获取到500000+1条数据，而后server层会摈弃前500000条，只保留最初一条数据的id。

但不同的中央在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比拟显著的。

在拿到了下面的id之后，假如这个id正好等于500000，那sql就变成了

select * from xxx  where id >=500000 order by id limit 10;

这样innodb再走一次主键索引，通过B+树疾速定位到id=500000的行数据，工夫复杂度是lg(n)，而后向后取10条数据。

办法二：

将所有的数据依据id主键进行排序，而后分批次取，将以后批次的最大id作为下次筛选的条件进行查问。

select * from xxx where id > start_id order by id limit 10;

mysql

通过主键索引，每次定位到start_id的地位，而后往后遍历10个数据，这样不论数据多大，查问性能都较为稳固。

高度为3的B+树，能够寄存多少数据？

InnoDB存储引擎有本人的最小贮存单元——页（Page）。

查问InnoDB页大小的命令如下：

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+

能够看出 innodb 默认的一页大小为 16384B = 16384/1024 = 16kb。

在MySQL中，B+树一个节点的大小设为一页或页的倍数最为适合。因为如果一个节点的大小 < 1页，那么读取这个节点的时候其实读取的还是一页，这样就造成了资源的节约。

B+树中非叶子节点存的是key + 指针；叶子节点存的是数据行。

对于叶子节点，如果一行数据大小为1k，那么一页就能存16条数据。

对于非叶子节点，如果key应用的是bigint，则为8字节，指针在MySQL中为6字节，一共是14字节，则16k能寄存 16 * 1024 / 14 = 1170 个索引指针。

于是能够算出，对于一颗高度为2的B+树，根节点存储索引指针节点，那么它有1170个叶子节点存储数据，每个叶子节点能够存储16条数据，一共 1170 x 16 = 18720 条数据。而对于高度为3的B+树，就能够寄存 1170 x 1170 x 16 = 21902400 条数据（两千多万条数据），也就是对于两千多万条的数据，咱们只须要高度为3的B+树就能够实现，通过主键查问只须要3次IO操作就能查到对应数据。

所以在 InnoDB 中B+树高度个别为3层时，就能满足千万级的数据存储。

参考：https://www.cnblogs.com/leefreeman/p/8315844.html

MySQL单表多大进行分库分表？

目前支流的有两种说法：

1. MySQL 单表数据量大于 2000 万行，性能会显著降落，思考进行分库分表。
2. 阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才举荐进行分库分表。

事实上，这个数值和理论记录的条数无关，而与 MySQL 的配置以及机器的硬件无关。因为MySQL为了进步性能，会将表的索引装载到内存中。在InnoDB buffer size 足够的状况下，其能实现全加载进内存，查问不会有问题。然而，当单表数据库达到某个量级的下限时，导致内存无奈存储其索引，使得之后的 SQL 查问会产生磁盘 IO，从而导致性能降落。当然，这个还有具体的表构造的设计无关，最终导致的问题都是内存限度。

因而，对于分库分表，须要结合实际需要，不宜适度设计，在我的项目一开始不采纳分库与分表设计，而是随着业务的增长，在无奈持续优化的状况下，再思考分库与分表进步零碎的性能。对此，阿里巴巴《Java 开发手册》补充到：如果预计三年后的数据量基本达不到这个级别，请不要在创立表时就分库分表。

至于MySQL单表多大进行分库分表，该当依据机器资源进行评估。

大表查问慢怎么优化？

某个表有近千万数据，查问比较慢，如何优化？

当MySQL单表记录数过大时，数据库的性能会显著降落，一些常见的优化措施如下：

• 正当建设索引。在适合的字段上建设索引，例如在WHERE和ORDER BY命令上波及的列建设索引，可依据EXPLAIN来查看是否用了索引还是全表扫描
• 索引优化，SQL优化。最左匹配准则等，参考：https://topjavaer.cn/database/mysql.html#%E4%BB%80%E4%B9%88%E…
• 建设分区。对关键字段建设程度分区，比方工夫字段，若查问条件往往通过工夫范畴来进行查问，能晋升不少性能
• 利用缓存。利用Redis等缓存热点数据，进步查问效率
• 限定数据的范畴。比方：用户在查问历史信息的时候，能够管制在一个月的工夫范畴内
• 读写拆散。经典的数据库拆分计划，主库负责写，从库负责读
• 通过分库分表的形式进行优化，次要有垂直拆分和程度拆分
• 正当建设索引。在适合的字段上建设索引，例如在WHERE和ORDERBY命令上波及的列建设索引

7. 数据异构到es
8. 冷热数据拆散。几个月之前不罕用的数据放到冷库中，最新的数据比拟新的数据放到热库中
9. 降级数据库类型，换一种能兼容MySQL的数据库（OceanBase、tidb）

说说count(1)、count(*)和count(字段名)的区别

嗯，先说说count(1) and count(字段名)的区别。

两者的次要区别是

1. count(1) 会统计表中的所有的记录数，蕴含字段为null 的记录。
2. count(字段名) 会统计该字段在表中呈现的次数，疏忽字段为null 的状况。即不统计字段为null 的记录。

接下来看看三者之间的区别。

执行成果上：

• count(*)包含了所有的列，相当于行数，在统计后果的时候，不会疏忽列值为NULL
• count(1)包含了疏忽所有列，用1代表代码行，在统计后果的时候，不会疏忽列值为NULL
• count(字段名)只包含列名那一列，在统计后果的时候，会疏忽列值为空（这里的空不是只空字符串或者0，而是示意null）的计数，即某个字段值为NULL时，不统计。

执行效率上：

• 列名为主键，count(字段名)会比count(1)快
• 列名不为主键，count(1)会比count(列名)快
• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
• 如果有主键，则 select count(主键)的执行效率是最优的
• 如果表只有一个字段，则 select count(*)最优。

MySQL中DATETIME 和 TIMESTAMP有什么区别？

嗯，TIMESTAMP和DATETIME都能够用来存储工夫，它们次要有以下区别：

1.示意范畴

• DATETIME：1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
• TIMESTAMP：’1970-01-01 00:00:01.000000′ UTC 到 ‘2038-01-09 03:14:07.999999’ UTC

TIMESTAMP反对的工夫范畴比DATATIME要小，容易呈现超出的状况。

2.空间占用

• TIMESTAMP ：占 4 个字节
• DATETIME：在 MySQL 5.6.4 之前，占 8 个字节 ，之后版本，占 5 个字节

3.存入工夫是否会主动转换

TIMESTAMP类型在默认状况下，insert、update 数据时，TIMESTAMP列会主动以以后工夫（CURRENT_TIMESTAMP）填充/更新。DATETIME则不会做任何转换，也不会检测时区，你给什么数据，它存什么数据。

4.TIMESTAMP比拟受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响。因为TIMESTAMP存的是工夫戳，在不同的时区得出的工夫不统一。

5.如果存进NULL，两者理论存储的值不同。

• TIMESTAMP：会主动存储以后工夫 now() 。
• DATETIME：不会主动存储以后工夫，会间接存入 NULL 值。

说说为什么不倡议用外键？

外键是一种束缚，这个束缚的存在，会保障表间数据的关系始终残缺。外键的存在，并非全然没有长处。

外键能够保证数据的完整性和一致性，级联操作不便。而且应用外键能够将数据完整性判断托付给了数据库实现，缩小了程序的代码量。

尽管外键可能保证数据的完整性，然而会给零碎带来很多缺点。

1、并发问题。在应用外键的状况下，每次批改数据都须要去另外一个表检查数据，须要获取额定的锁。若是在高并发大流量事务场景，应用外键更容易造成死锁。

2、扩展性问题。比方从MySQL迁徙到Oracle，外键依赖于数据库自身的个性，做迁徙可能不不便。

3、不利于分库分表。在程度拆分和分库的状况下，外键是无奈失效的。将数据间关系的保护，放入应用程序中，为未来的分库分表省去很多的麻烦。

应用自增主键有什么益处？

自增主键能够让主键索引尽量地放弃递增程序插入，防止了页决裂，因而索引更紧凑，在查问的时候，效率也就更高。

InnoDB的自增值为什么不能回收利用？

次要为了晋升插入数据的效率和并行度。

假如有两个并行执行的事务，在申请自增值的时候，为了防止两个事务申请到雷同的自增 id，必定要加锁，而后程序申请。

假如事务 A 申请到了 id=2， 事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。

事务 B 正确提交了，但事务 A 呈现了惟一键抵触。

如果容许事务 A 把自增 id 回退，也就是把表 t 的以后自增值改回 2，那么就会呈现这样的状况：表外面曾经有 id=3 的行，而以后的自增 id 值是 2。

接下来，继续执行的其余事务就会申请到 id=2，而后再申请到 id=3。这时，就会呈现插入语句报错“主键抵触”。

而为了解决这个主键抵触，有两种办法：

• 每次申请 id 之前，先判断表外面是否曾经存在这个 id。如果存在，就跳过这个 id。然而，这个办法的老本很高。因为，原本申请 id 是一个很快的操作，当初还要再去主键索引树上判断 id 是否存在。
• 把自增 id 的锁范畴扩充，必须等到一个事务执行实现并提交，下一个事务能力再申请自增 id。这个办法的问题，就是锁的粒度太大，零碎并发能力大大降落。

可见，这两个办法都会导致性能问题。

因而，InnoDB 放弃了“容许自增 id 回退”这个设计，语句执行失败也不回退自增 id。

自增主键保留在什么中央？

不同的引擎对于自增值的保留策略不同：

• MyISAM引擎的自增值保留在数据文件中。
• 在MySQL8.0以前，InnoDB引擎的自增值是存在内存中。MySQL重启之后内存中的这个值就失落了，每次重启后第一次关上表的时候，会找自增值的最大值max(id)，而后将最大值加1作为这个表的自增值；MySQL8.0版本会将自增值的变更记录在redo log中，重启时依附redo log复原。

自增主键肯定是间断的吗？

不肯定，有几种状况会导致自增主键不间断。

1、惟一键抵触导致自增主键不间断。当咱们向一个自增主键的InnoDB表中插入数据的时候，如果违反表中定义的惟一索引的惟一束缚，会导致插入数据失败。此时表的自增主键的键值是会向后加1滚动的。下次再次插入数据的时候，就不能再应用上次因插入数据失败而滚动生成的键值了，必须应用新滚动生成的键值。

2、事务回滚导致自增主键不间断。当咱们向一个自增主键的InnoDB表中插入数据的时候，如果显式开启了事务，而后因为某种原因最初回滚了事务，此时表的自增值也会产生滚动，而接下里新插入的数据，也将不能应用滚动过的自增值，而是须要从新申请一个新的自增值。

3、批量插入导致自增值不间断。MySQL有一个批量申请自增id的策略：

• 语句执行过程中，第一次申请自增id，调配1个自增id
• 1个用完当前，第二次申请，会调配2个自增id
• 2个用完当前，第三次申请，会调配4个自增id
• 顺次类推，每次申请都是上一次的两倍（最初一次申请不肯定全副应用）

如果下一个事务再次插入数据的时候，则会基于上一个事务申请后的自增值根底上再申请。此时就呈现自增值不间断的状况呈现。

4、自增步长不是1，也会导致自增主键不间断。

MySQL数据如何同步到Redis缓存？

参考：https://cloud.tencent.com/developer/article/1805755

有两种计划：

1、通过MySQL主动同步刷新Redis，MySQL触发器+UDF函数实现。

过程大抵如下：

1. 在MySQL中对要操作的数据设置触发器Trigger，监听操作
2. 客户端向MySQL中写入数据时，触发器会被触发，触发之后调用MySQL的UDF函数
3. UDF函数能够把数据写入到Redis中，从而达到同步的成果

2、解析MySQL的binlog，实现将数据库中的数据同步到Redis。能够通过canal实现。canal是阿里巴巴旗下的一款开源我的项目，基于数据库增量日志解析，提供增量数据订阅&生产。

canal的原理如下：

1. canal模仿mysql slave的交互协定，假装本人为mysql slave，向mysql master发送dump协定
2. mysql master收到dump申请，开始推送binary log给canal
3. canal解析binary log对象（原始为byte流），将数据同步写入Redis。

为什么阿里Java手册禁止应用存储过程？

先看看什么是存储过程。

存储过程是在大型数据库系统中，一组为了实现特定性能的SQL 语句集，它存储在数据库中，一次编译后永恒无效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程次要有以下几个毛病。

1. 存储过程难以调试。存储过程的开发始终短少无效的 IDE 环境。SQL 自身常常很长，调试式要把句子拆开分别独立执行，十分麻烦。
2. 移植性差。存储过程的移植艰难，个别业务零碎总会不可避免地用到数据库独有的个性和语法，更换数据库时这部分代码就须要重写，老本较高。
3. 治理艰难。存储过程的目录是扁平的，而不是文件系统那样的树形构造，脚本少的时候还好办，一旦多起来，目录就会陷入凌乱。
4. 存储过程是只优化一次，有的时候随着数据量的减少或者数据结构的变动，原来存储过程抉择的执行打算兴许并不是最优的了，所以这个时候须要手动干涉或者从新编译了。

最初给大家分享一个Github仓库，下面有大彬整顿的300多本经典的计算机书籍PDF，包含C语言、C++、Java、Python、前端、数据库、操作系统、计算机网络、数据结构和算法、机器学习、编程人生等，能够star一下，下次找书间接在下面搜寻，仓库继续更新中~

Github地址：https://github.com/Tyson0314/java-books