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

54次阅读

共计 27064 个字符，预计需要花费 68 分钟才能阅读完成。

本文曾经收录到 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 别离是 keyi 和 keyi+1，则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+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 命令上波及的列建设索引
  1. 数据异构到 es
  2. 冷热数据拆散。几个月之前不罕用的数据放到冷库中，最新的数据比拟新的数据放到热库中
  3. 降级数据库类型，换一种能兼容 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

正文完
mysql
发表至： mysql
2023-03-11
 0
关于mysql:mysql事务对效率的影响
关于mysql:基于GTID的主备切换
关于mysql:五分钟让你明白MySQL是怎么选择索引死磕MySQL系列-六
MySql-Explain