mysql面试题总结

Myth丶恋晨 2022-05-14 07:42 197阅读 0赞

### 基础 ###

**一.主键 超键 候选键 外键**

*  超 键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
 *  候选键：最小超键，即没有冗余元素的超键。它需要同时满足下列两个条件：  
    1.这个属性集合始终能够确保在关系中能唯一标识元组。（满足这个条件的就是超键）  
    2.在这个属性集合中找不出合适的真子集能够满足条件。
 *  主 键：数据表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
 *  外 键：在一个表中存在的另一个表的主键称此表的外键  
    ![20181220212242173][]

**二、key和index的区别**

key 是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等

index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。

**三.解释null值**

1.  NULL 值代表遗漏的未知数据。
2.  默认地，表的列可以存放 NULL 值。
3.  如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
4.  NULL 值的处理方式与其他值不同。
5.  NULL 用作未知的或不适用的值的占位符。

**四.如何获取当前数据库版本？**

1.  方法一：打开mysql在命令提示符上输入  select version();
2.  方法二：在cmd里面输入 mysql -V 来获取mysql版本号

**五.char 和 varchar 的区别是什么？**

1.  char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：  
    char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）  
    在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．
2.  在MySQL中用来判断是否需要进行对据列类型转换的规则
    
    1.  在一个数据表里，如果每一个数据列的长度都是固定的，那么每一个数据行的长度也将是固定的．
    2.  只要数据表里有一个数据列的长度的可变的，那么各数据行的长度都是可变的．
    3.  如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．  
        例外：长度小于4个字符的char数据列不会被转换为varchar类型

**六.MySQL类型float double decimal的区别**

1.  float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；
2.  MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001

**七.mysql 的内连接、左连接、右连接有什么区别？**

1.  内部链接INNER JOIN关键字选择两个表中具有匹配值的记录。
2.  SQL左链接LEFT JOIN关键字返回左表（表1）中的所有行，即使在右表（表2）中没有匹配。如果在正确的表中没有匹配，结果是NULL。
3.  SQL右链接 RIGHT JOIN 关键字返回右表（table2）的所有行，即使在左表（table1）上没有匹配。如果左表没有匹配，则结果为NULL。
4.  完整外部连接：当左（表1）或右（表2）表记录匹配时，FULL OUTER JOIN关键字将返回所有记录。   
    **注意：** FULL OUTER JOIN可能会返回非常大的结果集！ 
5.  自联接是一种常规联接，但表本身是连接的。

**八、为什么用自增列作为主键**

1.  如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。  
    如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。  
    如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。
2.  数据记录本身被存于主索引（一颗B+Tree）的叶子节点上，这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放  
      
    因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）
3.  如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页
4.  如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置  
      
    此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销  
      
    同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

**九.完整性约束**

*  主键约束：primary key：唯一标识数据表中的行/记录，主键和唯一键的区别在于唯一键可以为空，主键不可以
 *  外键约束：foreign key：唯一标识其他表中的一条行/记录
 *  唯一约束：unique：保证列中的所有数据各不相同（在建表时使用unique、建表后添加、删除unique约束）
 *  默认约束：default：提供该列数据未指定时所采用的默认值
 *  检查约束：check：此约束保证列中的所有值满足某一条件
 *  非空约束：not null：保证列中数据不能有 NULL 值  （LastName varchar(255) NOT NULL）

**十.约束和索引的区别**

约束：偏重于约束和规范数据库的结构完整性

索引：用于在数据库中快速创建或检索数据

**十一.什么叫视图？**

*  视图：是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

**十二.触发器的作用？**

答：触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

**十三.存储过程(procedure)和函数(function)区别**

本质上它们都是存储程序。函数只能通过return语句返回单个值或表对象；而存储过程不允许执行return语句，但是可以通过output参数返回多个值。函数限制比较多，不能用临时变量，只能用表变量，还有一些函数都不可用等等；而存储过程的限制相对就比较少。函数可以嵌入在SQL语句中使用，可以在select语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行。

**十四.什么是存储过程？用什么来调用？**

答：存储过程是一个预编译的SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL ，使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象来调用存储过程。

**十五.存储过程与触发器的区别**  
触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。  
触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。  
触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，mysql就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

**十六.说一下 mysql 常用的引擎？（Mysql 中 MyISAM 和 InnoDB 的区别有哪些？）**

*  MyIsam：5.5版本前Mysql的默认的存储引擎，不支持事务、表锁设计，不支持外键，支持全文检索。
 *  InnoDB ：5.5版本后Mysql的默认的存储引擎，支持事务，行锁设计，支持外键，并支持非锁定读，即默认读取操作不会产生锁。

**区别：**

InnoDB支持事务，MyISAM不支持

对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。

但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应该过大，因为主键太大，其他索引也都会很大。

而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

InnoDB不保存表的具体行数，执行select count(\*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

**如何选择：**

是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB

系统奔溃后，MyISAM恢复起来更困难，能否接受；

MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

### 表分区 ###

**十七、什么是表分区？**

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

**十八、表分区与分表的区别**

**分表**：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

**分表与分区的区别在于**：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

**十九、如何判断当前MySQL是否支持分区？**

命令：show variables like '%partition%' 运行结果:

have\_partintioning 的值为YES，表示支持分区。

**二十、MySQL支持的分区类型有哪些？**

*  **RANGE分区**：这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
 *  **LIST分区**：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。
 *  **HASH分区** ：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
 *  **KEY分区 **：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

**二十一、分区表的限制因素**

一个表最多只能有1024个分区

MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

分区表中无法使用外键约束

MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

**二十二、表分区有什么好处？**

1.  **存储更多数据**。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据
2.  **优化查询**。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
3.  **分区表更容易维护**。例如：想批量删除大量数据可以清除整个分区。
4.  **避免某些特殊的瓶颈**，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

### 索引 ###

**二十三、为什么使用数据索引能提高效率**

数据索引的存储是有序的

在有序的情况下，通过索引查询一个数据是无需遍历索引记录的

极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

**二十四、B+树索引和哈希索引的区别**

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，是有序的，如下图：

![format_png][]

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的，如下图所示：

![format_png 1][]

**二十五、哈希索引的优势：**

**等值查询，**哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

**二十六、哈希索引不适用的场景：**

不支持范围查询

不支持索引完成排序

不支持联合索引的最左前缀匹配规则

通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引，例如这种SQL：

\# 仅等值查询

select id, name from table where name='李明';

而常用的 InnoDB 引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况。

如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引）。

通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

**注意**：在某些工作负载下，通过哈希索引查找带来的性能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。

但某些时候，在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引，可能要关闭自适应哈希索引。

**二十七、B树和B+树的区别**

*  一个M阶的b树具有如下几个特征：所有节点的子节点不能超过M。
    
    1.  根结点的儿子数为\[2, M\]；
    2.  除根结点以外的非叶子结点的儿子数为\[M/2, M\]，向上取整；
    3.  非叶子结点的关键字个数=儿子数-1；
    4.  所有叶子结点位于同一层；
    5.  k个关键字把节点拆成k+1段，分别指向k+1个儿子，同时满足查找树的大小关系。
 *  有关b树的一些特性，注意与后面的b+树区分：
    
    1.  关键字集合分布在整颗树中；
    2.  任何一个关键字出现且只出现在一个结点中；
    3.  搜索有可能在非叶子结点结束；
    4.  其搜索性能等价于在关键字全集内做一次二分查找；
 *  b+树，是b树的一种变体，查询性能更好。m阶的b+树的特征：
    
    1.  有n棵子树的非叶子结点中含有n个关键字（b树是n-1个），这些关键字不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
    2.  所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
    3.  所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
    4.  通常在b+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。
    5.  同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。
 *  b+树相比于b树的查询优势：
    
    1.  b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”，读写代价更低；
    2.  b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；
    3.  对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历，

**二十八、为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？**

1、B+的磁盘读写代价更低。

B+的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B树更小。

如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2、B+-tree的查询效率更加稳定。

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

**二十九、MySQL联合索引**

1、联合索引也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。联合索引是两个或更多个列上的索引。

对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

2、利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。

如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不知道姓，电话簿将没有用处。

**三十.有四种方式来添加数据表的索引：**

*  ALTER TABLE tbl\_name ADD PRIMARY KEY (column\_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
 *  ALTER TABLE tbl\_name ADD UNIQUE index\_name (column\_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
 *  ALTER TABLE tbl\_name ADD INDEX index\_name (column\_list): 添加普通索引，索引值可出现多次。
 *  ALTER TABLE tbl\_name ADD FULLTEXT index\_name (column\_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

**三十一.什么是索引？索引的作用？索引的分类?什么时候应该用索引？mysql 索引是怎么实现的？**

索引：[索引][Link 1]是对[数据库][Link 2]表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

作用：索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

分类：根据数据库的功能，可以在[数据库设计][Link 3]器中创建三种[索引][Link 4]：[唯一索引][Link 5]、主键索引和[聚集索引][Link 6]。

*  唯一索引：唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。
 *  主键索引：数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的[主键][Link 7]。在数据库关系图中为表定义主键将自动创建主键[索引][Link 4]，主键索引是[唯一索引][Link 5]的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
 *  聚集索引：在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。  
    与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。

优点：

1.  索引能够提高 SELECT 查询和 WHERE 子句的速度，
2.  同 UNIQUE 约束一样，索引可以是唯一的。这种情况下，索引会阻止列中（或者列的组合，其中某些列有索引）出现重复的条目。
3.  在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

缺点：

1.  在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
2.  在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，如果有必要建立起聚簇索引，所占用的空间还将进一步的增加
3.  在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦

避免使用索引：

1.  小的数据表不应当使用索引；
2.  需要频繁进行大批量的更新或者插入操作的表；
3.  如果列中包含大数或者 NULL 值，不宜创建索引；
4.  频繁操作的列不宜创建索引。
5.  数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

mysql索引是通过B+树实现。再讲一下B+树的结构。

**三十二.怎么验证 mysql 的索引是否满足需求？**

需要根据查询需求来决定配置索引的类型，一旦确定索引类型之后，可以使用 explain 查看 SQL 执行计划，确认索引是否满足需求。

### 锁 ###

**三十三、关于MVVC**

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)

**注**：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control

**MVCC最大的好处**：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC。

LBCC：Lock-Based Concurrency Control，基于锁的并发控制

MVCC：Multi-Version Concurrency Control

基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提高了并发量。

**三十四、在MVCC并发控制中，读操作可以分成两类：**

**快照读 (snapshot read)**：读取的是记录的可见版本 (有可能是历史版本)，不用加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写）

**当前读 (current read)**：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录

**三十五、行级锁定的优点：**

1、当在许多线程中访问不同的行时只存在少量锁定冲突。

2、回滚时只有少量的更改

3、可以长时间锁定单一的行。

**三十六、行级锁定的缺点：**

比页级或表级锁定占用更多的内存。

当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。

如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。

用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

**三十七.为什么要使用锁？mysql数据库有哪些锁，用在哪里？说一下 mysql 的行锁和表锁？发生死锁怎么解决？**

1.  管理共享资源的并发访问。
2.  MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。  
    比如，  
    MyISAM采用的是表级锁；  
    InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下采用行级锁。  
    MySQL 的表级锁有两种模式：表共享读锁和表独占写锁。  
    InnoDB的行锁有两种：共享锁（S）和排他锁（X）。  
    为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁：意向共享锁和意向排他锁，这两种意向锁都是表锁。一个事务在给数据行加锁之前必须先取得对应表对应的意向锁。意向锁是InnoDB自动加的，不需用户干预。  
    对于UPDATE、DELETE 和INSERT 语句，InnoDB会自动给涉及数据集加排他锁（X）；对于普通SELECT语句，InnoDB 不会加任何锁；  
    事务可以通过以下语句显式给记录集加共享锁或排他锁。  
    select ...for  updata  
    select ... lock in share mode
3.  死锁  
    MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。  
    但在InnoDB 中，除单个SQL 组成的事务外，锁是逐步获得的，这就决定了在InnoDB 中发生死锁是可能的。发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb\_lock\_wait\_timeout来解决。  
      
    通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。
    
    1.  在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
    2.  在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
    3.  当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

**三十八.悲观锁和乐观锁（共享读锁（S锁）和排他写锁（X锁）又是什么？），抽象锁，不真实存在**

*  悲观锁（Pessimistic Lock）： 每次获取数据的时候，都会担心数据被修改，所以每次获取数据的时候都会进行加锁，确保在自己使用的过程中数据不会被别人修改，使用完成后进行数据解锁。由于数据进行加锁，期间对该数据进行读写的其他线程都会进行等待。
 *  乐观锁（Optimistic Lock）： 每次获取数据的时候，都不会担心数据被修改，所以每次获取数据的时候都不会进行加锁，但是在更新数据的时候需要判断该数据是否被别人修改过。如果数据被其他线程修改，则不进行数据更新，如果数据没有被其他线程修改，则进行数据更新。由于数据没有进行加锁，期间该数据可以被其他线程进行读写操作。

适用场景：

1.  悲观锁：比较适合写入操作比较频繁的场景，如果出现大量的读取操作，每次读取的时候都会进行加锁，这样会增加大量的锁的开销，降低了系统的吞吐量。
2.  乐观锁：比较适合读取操作比较频繁的场景，如果出现大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，这样会增加大量的查询操作，降低了系统的吞吐量。
3.  总结：两种所各有优缺点，读取频繁使用乐观锁，写入频繁使用悲观锁。

## ##

### 事务 ###

**三十九.事务控制**  
有6个命令用于控制事务：

*  BEGIN、SET TRANSACTION=0 、start transaction：开启事务；
 *  SAVEPOINT 还原点：在事务内部创建一系列可以 ROLLBACK 的还原点；
 *  ROLLBACK 还原点：回滚到还原点；
 *  ROLLBACK：回滚更改；
 *  COMMIT：提交更改；

**四十.数据库事务的四个特性及含义**  
数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。

1.  原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
2.  一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
3.  隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
4.  持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

**四十一.事务的隔离级别从低到高有：**

1.  Read Uncommitted：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
2.  Read Committed：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题。
3.  Repeated Read：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读。
4.  Serialization：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。

注意：innodb储存引擎默认支持的隔离级别是 Repeated Committed，但是与标准sql不同的是，innodb储存引擎在Repeated Committed隔离级别下，使用Next-Key Lock锁的算法，因此避免了幻读的产生。所以说，InnoDB储存引擎在磨人的REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABL隔离级别。

通常，在工程实践中，为了性能的考虑会对隔离性级别，隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

**四十二.隔离性为了解决的问题主要有三个（将事务的隔离级别和问题联系在一起理解）：在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。**

1.   脏读(Drity Read)：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。
2.  不可重复读(Non-repeatable read) : 在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这导致锁竞争加剧，影响性能。（另一种方法是通过MVCC可以在无锁的情况下，避免不可重复读。待了解。。）
3.  幻读(Phantom Read) : 在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读仅指由于并发事务增加记录导致的问题，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。

*  Read Committed可以解决脏读问题，无法解决不可重复读和幻读。
 *  Repeated Read可以解决不可重复读问题和脏读问题，无法解决幻读。
 *  Serialization解决了以上所有问题，但是性能效率较低。

## ##

### 备份和恢复 ###

**四十三.主从的好处是？主从的原理是？从数据库的读的延迟问题了解吗？如何解决？做主从后主服务器挂了怎么办？**  
**1.优点**

*  主库故障，可以快速切换至从库提供服务；
 *  在从库执行查询操作，降低主库的访问压力；
 *  在从库执行备份，避免备份期间对主库影响；

**四十四.主从复制原理**

1.  在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。
2.  此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容（ master记录更改的明细，存入到二进制日志（binary log））
3.  Master服务器接收到来自Slave服务器的IO线程的请求后，二进制转储IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。（master发送同步消息给slave）
4.  当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（[MySQL][]\-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容（ slave收到消息后，将master的二进制日志复制到本地的中继日志（relay log））
5.  Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点（ slave重现中继日志中的消息，从而改变数据库的数据）

**四十五.主从复制延迟问题**

1.  由于主库和从库之间不在同一个主机上，数据同步之间不可以避免地具有延迟，解决的方法有添加缓存，业务层的跳转等待，如果非得从数据库层面去减缓延迟问题，可以从复制时候的三大步骤（主库产生日志，主从传输日志，从库还原日志内容）入手：
    
    1.主库写入到日志的速度  
    控制主库的事务大小，分割大事务为多个小事务。  
    如插入20w的数据，改成插入多次5000行（可以利用分页的思路）
    
    2.二进制日志在主从之间传输时间  
    主从之间尽量在同一个机房或地域。  
    日志格式改用MIXED，且设置行的日志格式未minimal，原理详见上面的日志格式介绍。
    
    3.减少从库还原日志的时间  
    在MySQL5.7版本后可以利用逻辑时钟方式分配SQL多线程。  
    设置逻辑时钟：slave\_parallel\_type=‘logical\_clock';  
    设置复制线程个数：slave\_parallel\_workers=4;  
      
    4.从服务器升级为主服务器

**四十六.MySQL主从复制什么原因会造成不一致，如何预防及解决？**

1.  人为原因导致从库与主库数据不一致（从库写入）
2.  主从复制过程中，主库异常宕机
3.  设置了ignore/do/rewrite等replication等规则
4.  .binlog非row格式 
5.  异步复制本身不保证，半同步存在提交读的问题，增强半同步起来比较完美。 但对于异常重启（Replication Crash Safe），从库写数据（GTID）的防范，还需要策略来保证。
6.  从库中断很久，binlog应用不连续，监控并及时修复主从
7.  从库启用了诸如存储过程，从库禁用存储过程等
8.  数据库大小版本/分支版本导致数据不一致？，主从版本统一
9.  备份的时候没有指定参数 例如mysqldump --master-data=2 等
10. 主从sql\_mode 不一致
11. 一主二从环境，二从的server id一致。
12. MySQL自增列 主从不一致
13. 主从信息保存在文件里面，文件本身的刷新是非事务的，导致从库重启后开始执行点大于实际执行点

**预防措施：**

1.  master:innodb\_flush\_log\_at\_trx\_commit=1&sync\_binlog=1
2.  slave:master\_info\_repository="TABLE"&relay\_log\_info\_repository="TABLE"&relay\_log\_recovery=1
3.  设置从库库为只读模式
4.  可以使用5.7增强半同步避免数据丢失等
5.  binlog row格式
6.  必须引定期的数据校验机制

### 性能调优 ###

**四十七、MySQL优化**

1.  选择好的cpu、内存、固态
2.  选择合适的存储引擎
    
    1.  在线事务分析，大型产品的数据库对于可靠性和并发性的要求较高，InnoDB作为默认的MySQL存储引擎，相对于MyISAM来说是个更佳的选择。
    2.  离线事务分析，可以采用MyISAM。
3.  配置合适参数
    
    1.  开启查询缓存：query\_cache\_size = 20M；query\_cache\_type = ON
    2.  开启慢查询日志：long\_slow\_queries=on
    3.  开启二进制日志：log-bin = /data/mysql/mysql-bin（日志文件命名方式）；expire\_logs\_days = 7（日志文件过期时间），开启二进制日志会使性能下降1%，但是可以复制和数据恢复，所以可以接受。  
        sync\_binlog=\[N\];  
        在默认情况下，二进制日志文件并不是每次写的时候同步到磁盘。因此当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中，这给恢复和复制带来了问题。参数sync\_binlog=\[N\]表示每写缓冲多次就同步到磁盘。如果将N设为1，即sync\_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用才做系统的缓冲来写二进制日志。（备注：该值默认为0，采用操作系统机制进行缓冲数据同步）。  
        当sync\_binlog=1，还会存在另外问题。当使用InnoDB存储引擎时，在一个事务发出commit动作之前，由于sync\_binlog设为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在Mysql数据库下次启动时，由于commit操作并没有发生，所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。  
        这个问题，可以将innodb\_support\_xa设为1来解决，确保二进制日志和InnoDB存储引擎数据文件的同步。  
        注意：二进制日志记录是事务的具体内容，即该日志的逻辑日志。是在事务提交前提交。
    4.  重做日志文件同步参数：从日志缓冲写入磁盘上的重做日志文件的条件：  
        在主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否提交。另一个触发这个过程是由参数innodb\_flush\_log\_at\_trx\_commit控制，表示在提交时，处理重做日志的方式。  
        参数innodb\_flush\_log\_at\_trx\_commit可设的值有0、1、2,0代表当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新，而1和2不同的地方在于：1是在commit时将重做日志缓冲同步写到磁盘；2是重做日志异步写到磁盘，即不能完全保证commit时肯定会写入重做日志文件，只是有这个动作。
    5.  优化InnoDB磁盘I/O  
        增加InnoDB缓冲池大小innodb\_buffer\_poll\_size=8G，可以让查询从缓冲池访问而不是通过磁盘I/O访问。  
        通过调整系统变量innodb\_flush\_method来调整清除缓冲的指标使其达到最佳水平。
4.  优化数据库结构
    
    1.  使用varchar类型代替char，因为varchar会动态分配长度，char指定长度是固定的；
    2.  使用 ENUM 而不是 VARCHAR。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是VARCHAR
    3.  对于大数据字段，独立表进行存储，以便影响性能（例如：简介字段）；
    4.  给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响；表的主键应尽可能短。·对于InnoDB表，主键所在的列在每个辅助索引条目中都是可复制的，因此如果有很多辅助索引，那么一个短的主键可以节省大量空间。
    5.  避免表字段运行为null，建议设置默认值（例如：int类型设置默认值为0）在索引查询上，效率立显；
5.  为表添加合适索引
    
    1.  建立索引，最好建立在唯一和非空的字段上，建立太多的索引对后期插入、更新都存在一定的影响（考虑实际情况来创建）
6.  sql查询语句优化
    
    1.  优化查询：explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的
    2.  尽量使用索引
        
        1.  考虑在 where 及 order by 涉及的列上建立索引
        2.  同一个表中，索引越少越好
        3.  尽量只给长度小的字段建立索引
        4.  当索引列有大量数据重复时（如果某列1/3是重复的），使用索引也不是一种好的办法
        5.  索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率
        6.  count(\*)、count(1)、count(id)的区别    [Hive shelll编程][Hive shelll]之HIVE- DQL之数据查询
    3.  有些情况会使索引失效，而进行全表查询
        
        1.  避免在 where 子句中对字段进行 null 值判断
        2.  避免在 where 子句中使用!=或<>操作符
        3.  in 和 not in 也要慎用，很多时候用 exists 、not exists 代替 in、not in 是一个好的选择
        4.  避免在索引列上使用IS NULL和IS NOT NULL（应该使用什么代替）
        5.  使用like 查询时，尽量避免不在字段前加%，可以使用后面带%，前%的话会使索引失效
        6.  避免在 where 子句中对字段进行表达式操作
        7.  避免在where子句中对字段进行函数操作
    4.  其他
        
        1.  sql语句中in包含的值不应该超过1000个
        2.  尽可能的使用not null。除非你有一个很特别的原因要去使用null值，你应该总让你的字段保持为not null。
        3.  **谨慎使用select \***
        4.  避免频繁创建和删除临时表，以减少系统表资源的消耗
        5.  临时表并不是不可使用，适当地使用它们可以使某些例程更有效
        6.  如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 
        7.  **exist和in的区别**   
            in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。因此，in用到的是外表的索引， exists用到的是内表的索引。  
            如果查询的两个表大小相当，那么用in和exists差别不大。  
            如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
            
                例如：表A（小表），表B（大表）
                1：
                select * from A where cc in (select cc from B)
                效率低，用到了A表上cc列的索引；
                
                select * from A where exists(select cc from B where cc=A.cc)
                效率高，用到了B表上cc列的索引。
                2：
                select * from B where cc in (select cc from A)
                效率高，用到了B表上cc列的索引；
                
                select * from B where exists(select cc from A where cc=B.cc)
                效率低，用到了A表上cc列的索引。
7.  日常维护
    
    1.  定期检查慢的查询日志并优化查询机制以有效使用缓存来减少磁盘I/O。优化它们，以扫描最少的行数，而不是进行全表扫描。
    2.  其他可以帮助DBA检查和分析性能的日志包括：错误日志、常规查询日志、二进制日志、DDL日志(元数据日志)。
    3.  定期刷新缓存和缓冲区以降低碎片化。使用OPTIMIZETABLE语句重新组织表并压缩任何可能被浪费的空间。

### 分表、分库、集群 ###

先使用集群，进行读写分离。

项目中使用了分库分表，我们先分库，后分表。

1.  my sql 索引类别
    
    什么是覆盖索引
    
    b+树和b树的区别
    
    为什么选用自增量作为主键索引
    
    mysql如何优化查询
    
    mysql如何在RR隔离级别下避免幻读问题：间隙锁
    
    mysql范式和反范式的区别以及彼此的优缺点
2.  表锁 行锁 乐观锁 悲观锁的特点和区别
3.  my sql数据量多大的时候需要分表
    
    my sql常用的存储引擎及区别
    
    死锁的条件及应对措施
4.  分布式事务的解决方案

[20181220212242173]: /images/20220514/d8de27a947ec416f866d202e6609aa30.png
[format_png]: /images/20220514/f85815febd60419da20d852cc8ce3372.png
[format_png 1]: /images/20220514/9dc87dda6bd1455dac0dfdeff3e0686e.png
[Link 1]: https://baike.baidu.com/item/%E7%B4%A2%E5%BC%95/5716853
[Link 2]: https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93/103728
[Link 3]: https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1
[Link 4]: https://baike.baidu.com/item/%E7%B4%A2%E5%BC%95
[Link 5]: https://baike.baidu.com/item/%E5%94%AF%E4%B8%80%E7%B4%A2%E5%BC%95
[Link 6]: https://baike.baidu.com/item/%E8%81%9A%E9%9B%86%E7%B4%A2%E5%BC%95
[Link 7]: https://baike.baidu.com/item/%E4%B8%BB%E9%94%AE
[MySQL]: http://lib.csdn.net/base/mysql
[Hive shelll]: https://blog.csdn.net/yiguang_820/article/details/86657007