mysql数据库总结及面试题

墨蓝 2022-04-17 06:49 257阅读 0赞

**知识点：**

1.  MySQL体系结构和储存引擎
    
    1.  说一下mysql的体系结构
    2.  **说一下 mysql 常用的引擎？（Mysql 中 MyISAM 和 InnoDB 的区别有哪些？）**
        
        1.  MyIsam：5.5版本前Mysql的默认的存储引擎，不支持事务、表锁设计，不支持外键，支持全文检索。
        2.  InnoDB ：5.5版本后Mysql的默认的存储引擎，支持事务，行锁设计，支持外键，并支持非锁定读，即默认读取操作不会产生锁。
        3.  **区别：**
            
            1.  InnoDB支持事务，MyISAM不支持  
                对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
            2.  InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
            3.  InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。
            4.  但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应该过大，因为主键太大，其他索引也都会很大。
            5.  而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
        4.  **如何选择：**
            
            1.  是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
            2.  如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB
            3.  系统奔溃后，MyISAM恢复起来更困难，能否接受；
            4.  MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。
2.  innodb存储引擎
    
    1.  体系结构
        
        1.  内存
            
            1.  维护进程/线程需要访问的多个内部数据结构
            2.  缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存。
            3.  重做日志缓冲
            4.  .......
        2.  后台线程
            
            1.  主要是负责刷新内存池中的数据，保证缓冲池中的内存缓存是最近的数据。
            2.  此外将已修改的数据文件刷新到磁盘文件
            3.  同时保证在数据库发生异常的情况下Innodb能恢复到正常的运行状态。
    2.  innodb关键特性
3.  文件
4.  表
    
    1.  约束
        
        1.  约束：保证数据完整性
            
            1.  主键约束：primary key：唯一标识数据表中的行/记录，主键和唯一键的区别在于唯一键可以为空，主键不可以
            2.  外键约束：foreign key：唯一标识其他表中的一条行/记录
            3.  唯一约束：unique：保证列中的所有数据各不相同（在建表时使用unique、建表后添加、删除unique约束）
            4.  默认约束：default：提供该列数据未指定时所采用的默认值
            5.  检查约束：check：此约束保证列中的所有值满足某一条件
            6.  非空约束：not null：保证列中数据不能有 NULL 值  （LastName varchar(255) NOT NULL） 
        2.  约束和索引的区别
            
            1.  约束：偏重于约束和规范数据库的结构完整性
            2.  索引：用于在数据库中快速创建或检索数据
        3.  key和index的区别
            
            1.  key 是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等
            2.  index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。
        4.  为什么选用自增量作为主键索引
            
            1.  如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。  
                如果没有显式定义主键，则InnoDB会判断表中是否有非空的唯一索引，如果有，则改列即为主键。  
                如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。
            2.  数据记录本身被存于主索引（一颗B+Tree）的叶子节点上，这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放  
                  
                因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）
            3.  如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页
            4.  如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置  
                  
                此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销  
                  
                同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
    2.  视图
        
        1.  视图：是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
    3.  分区
        
        1.  **什么是表分区？**
            
            1.  表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。
        2.  **表分区与分表的区别**
            
            1.  **分表**：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。  
                **分表与分区的区别在于**：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。
        3.  **MySQL支持的分区类型有哪些？**
            
            1.  **RANGE分区**：这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
            2.  **LIST分区**：这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。
            3.  **HASH分区** ：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
            4.  **KEY分区 **：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
        4.  **表分区有什么好处？**
            
            1.  **避免某些特殊的瓶颈**，例如InnoDB的单个索引的互斥访问，系统的inode锁竞争等。
            2.  **分区表更容易维护**。例如：想批量删除大量数据可以清除整个分区。
            3.  **优化查询**。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
            4.  **存储更多数据**。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据
5.  索引和算法
    
    1.  **为什么使用数据索引能提高效率**
        
        1.  数据索引的存储是有序的，在有序的情况下，通过索引查询一个数据是无需遍历索引记录。
        2.  极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)
    2.  平衡二叉树、B树（B-树）、B+树
        
        1.  平衡二叉树是带有平衡条件的二叉查找树。即一颗平衡二叉树是其每个节点的左右树的高度差最多差1的二叉查找树。
        2.  B树是一种平衡的多叉树，一个M阶的b树具有如下几个特征：所有节点的子节点不能超过M。
            
            1.  关键字集合分布在整颗树中；任何一个关键字出现且只出现在一个结点中；搜索有可能在非叶子结点结束；
            2.  根结点的儿子数为\[2, M\]；
            3.  除根结点以外的非叶子结点的儿子数在M/2（向上取整）到 M之间；
            4.  非叶子结点的关键字个数=儿子数-1；
            5.  所有叶子结点位于同一层；
            6.  k个关键字把节点拆成k+1段，分别指向k+1个儿子，同时满足查找树的大小关系。  
                ![format_png][]
        3.  b+树，是一种平衡多叉树，是b树的一种变体，查询性能更好。m阶的b+树的特征：
            
            1.  所有的非叶子结点不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。
            2.  所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
            3.  根结点的儿子数为\[2, M\]；
            4.  除根结点以外的非叶子结点的儿子数在M/2（向上取整）到 M之间；
            5.  非叶子结点的关键字个数=儿子数-1；
            6.  所有叶子结点位于同一层；
            7.  k个关键字把节点拆成k+1段，分别指向k+1个儿子，同时满足查找树的大小关系。  
                ![format_png 1][]
        4.  b+树相比于b树的查询优势：
            
            1.  B+的磁盘读写代价更低  
                b+树的中间节点不保存数据，那么盘块所能容纳的关键字数量也越多，一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；
            2.  B+-tree的查询效率更加稳定  
                b树查询只要匹配到即可不用管元素位置，b+树查询必须查找到叶子节点，所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当，也就更稳定。
            3.  对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历
    3.  **MySQL联合索引**
        
        1.  联合索引也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。联合索引是两个或更多个列上的索引。  
            对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。  
            例：索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
        2.  利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。  
            例：复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不知道姓，电话簿将没有用处。
    4.  哈希索引  
        就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的。  
        哈希索引有两个关键点：1.哈希算法：除法散列方式；2.哈希碰撞问题：采用链表方式（分离链接法，在Java中hashmap采用链表解决）
    5.  **哈希索引的使用场景  
        自适应哈希索引是innodb存储引擎自己控制的。**
        
        1.  **等值查询，**哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）  
            select id, name from table where name='李明';
        2.  **哈希索引不适用的场景：**
            
            1.  不支持范围查询
            2.  不支持索引完成排序
            3.  不支持联合索引的最左前缀匹配规则
        3.  innodb会自动建立哈希索引。  
            而常用的 InnoDB 引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况。  
            如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引）。通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。
        4.  **注意**：在某些工作负载下，通过哈希索引查找带来的性能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。  
            但某些时候，在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引，可能要关闭自适应哈希索引。
    6.  **什么是索引？索引的作用？索引的分类?什么时候应该用索引？mysql 索引是怎么实现的？**
        
        1.  索引：[索引][Link 1]是对[数据库][Link 2]表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。
        2.  作用：索引的一个主要目的就是加快检索表中数据的方法，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。
        3.  分类：根据数据库的功能，可以在[数据库设计][Link 3]器中创建三种[索引][Link 4]：[唯一索引][Link 5]、主键索引和[聚集索引][Link 6]。
            
            1.  唯一索引：唯一索引是不允许其中任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。
            2.  主键索引：数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的[主键][Link 7]。在数据库关系图中为表定义主键将自动创建主键[索引][Link 4]，主键索引是[唯一索引][Link 5]的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
            3.  聚集索引：在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。  
                与非聚集索引相比，聚集索引通常提供更快的数据访问速度。聚集索引和非聚集索引的区别，如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。
        4.  优点：
            
            1.  索引能够提高 SELECT 查询和 WHERE 子句的速度，
            2.  同 UNIQUE 约束一样，索引可以是唯一的。这种情况下，索引会阻止列中（或者列的组合，其中某些列有索引）出现重复的条目。
            3.  在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。
        5.  缺点：
            
            1.  在对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护，这给数据库的维护速度带来了一定的麻烦
            2.  在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，如果有必要建立起聚簇索引，所占用的空间还将进一步的增加
            3.  在数据库建立过程中，需花费较多的时间去建立并维护索引，特别是随着数据总量的增加，所花费的时间将不断递增。
        6.  避免使用索引：
            
            1.  小的数据表不应当使用索引；
            2.  需要频繁进行大批量的更新或者插入操作的表；
            3.  如果列中包含大数或者 NULL 值，不宜创建索引；
            4.  频繁操作的列不宜创建索引。
            5.  数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
    7.  面试题：
        
        1.  my sql 索引类别
        2.  什么是覆盖索引
        3.  b+树和b树的区别
6.  锁
    
    1.  **锁**
        
        1.  管理共享资源的并发访问。
        2.  确保每一个用户能以一致的方式读取和修改数据。
    2.  MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。
        
        1.  **innodb存储引擎中的锁的类型-标准的行级锁**
            
            1.  共享锁：允许事务读一行数据
            2.  排它锁：允许事务删除或更新一行数据
            3.  为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁：意向共享锁和意向排他锁，这两种意向锁都是表锁。一个事务在给数据行加锁之前必须先取得对应表对应的意向锁。  
                意向锁是InnoDB自动加的，不需用户干预。  
                对于UPDATE、DELETE 和INSERT 语句，InnoDB会自动给涉及数据意向排他锁（X）；对于普通SELECT语句，InnoDB 不会加任何锁；  
                事务可以通过以下语句显式给记录加共享锁或排他锁。  
                select ...for  updata  
                select ... lock in share mode
        2.  MyISAM采用的是表级锁；表级锁有两种模式：表共享读锁和表独占写锁。
    3.  **行级**
        
        1.  **行级锁定的优点：**
            
            1.  当在许多线程中访问不同的行时只存在少量锁定冲突。
            2.  回滚时只有少量的更改
            3.  可以长时间锁定单一的行。
        2.  **行级锁定的缺点：**
            
            1.  比页级或表级锁定占用更多的内存。
            2.  当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。  
                如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
    4.  行锁的实现-三种算法
        
        1.  Record Lock：单个记录的锁
        2.  Gap Lock：间隙锁，锁定一个范围，但不包括记录本身
        3.  Next Lock：Record Lock+Gap Lock，锁定一个范围，并包括记录本身
    5.  锁问题
        
        1.  脏读：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。
        2.  不可重复读：在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这导致锁竞争加剧，影响性能。（另一种方法是通过MVCC可以在无锁的情况下，避免不可重复读。）
        3.  幻读：在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读仅指由于并发事务增加记录导致的问题，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。
        4.  数据丢失
    6.  死锁：死锁是指两个或两个以上的事务在执行的中，因争夺锁资源而造成的一种互相等待的情况。
        
        1.  原因：MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。  
            但在InnoDB 中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB 中发生死锁是可能的。  
            如果程序是串行的，那么不会发生死锁。死锁只存在于并发的情况，而数据库本身就是一个并发的程序，因此可能发生死锁。
        2.  死锁的4个必要条件
            
            1.  互斥条件：一个资源每次只能被一个进程使用，即在一段时间内某 资源仅为一个进程所占有。此时若有其他进程请求该资源，则请求进程只能等待。
            2.  请求与保持条件：进程已经保持了至少一个资源，但又提出了新的资源请求，而该资源 已被其他进程占有，此时请求进程被阻塞，但对自己已获得的资源保持不放。
            3.  不可剥夺条件:进程所获得的资源在未使用完毕之前，不能被其他进程强行夺走，即只能 由获得该资源的进程自己来释放（只能是主动释放)。
            4.  循环等待条件: 若干进程间形成首尾相接循环等待资源的关系  
                  
                这四个条件是死锁的必要条件，只要系统发生死锁，这些条件必然成立，而只要上述条件之一不满足，就不会发生死锁。
        3.  innodb存储引擎解决死锁的方法：发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务
        4.  通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法
            
            1.  从源头避免出现死锁：
                
                1.  在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
            2.  出现死锁的解决方案
                
                1.  设计事务超时机制，事务获取锁超过时间点就释放锁并回退，另一个事务获得锁，继续完成事务
                2.  事务隔离级别设计最高级别：序列化，将事务以串行的方式执行
    7.  面试题：
        
        1.  mysql如何在RR隔离级别下避免幻读问题：间隙锁
        2.  表锁 行锁 乐观锁 悲观锁的特点和区别
        3.  死锁的条件及应对措施
7.  事务
    
    1.  **数据库事务的四个特性及含义**
        
        1.  原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
        2.  一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
        3.  隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
        4.  持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
    2.  事务的实现
        
        1.  隔离性由锁实现
        2.  原子性和持久性由redo日志实现，redo恢复提交事务修改的操作
        3.  一致性由undo日志实现，undo回滚记录到某个特定的版本
    3.  控制事务语句 - 6个
        
        1.  BEGIN、SET TRANSACTION=0 、start transaction：开启事务；
        2.  SAVEPOINT 还原点：在事务内部创建一系列可以 ROLLBACK 的还原点；
        3.  ROLLBACK 还原点：回滚到还原点；
        4.  ROLLBACK：回滚更改；
        5.  COMMIT：提交更改；
    4.  **事务的隔离级别从低到高有：**
        
        1.  Read Uncommitted：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
        2.  Read Committed：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题。
        3.  Repeated Read：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读。
        4.  Serialization：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。
        5.  注意：innodb储存引擎默认支持的隔离级别是 Repeated Committed，但是与标准sql不同的是，innodb储存引擎在Repeated Committed隔离级别下，使用Next-Key Lock锁的算法，因此避免了幻读的产生。所以说，InnoDB储存引擎在默认的REPEATABLE READ的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABL隔离级别。
    5.  分布式事务
        
        1.  概念：分布式事务指的是允许多个独立的事务资源参与到一个全局的事务中。
        2.  innodb存储引擎提供了对XA事务的支持，并通过XA事务来支持分布式事物的实现。
        3.  XA事务
            
            1.  XA事务允许不同数据库之间的分布式事务，只要参与在全局事务中的每个结点都支持XA 事务。Oracle、MySQL 和SQL Server 都支持XA 事务。
            2.  XA 事务由一个或多个资源管理器（RM）、一个事务管理器（TM）和一个应用程序（ApplicationProgram）组成。  
                资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。  
                事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。  
                应用程序：定义事务的边界。
            3.  XA事务实现原理  
                XA 协议主要定义了事务管理器TM（Transaction Manager，协调者）和资源管理器RM（Resource Manager，参与者）之间的接口。其中，资源管理器往往由数据库实现，如Oracle、DB2、MySQL，这些商业数据库都实现了XA 接口，而事务管理器作为全局的调度者，负责各个本地资源的提交和回滚。XA 事务是基于两阶段提交（Two-phaseCommit，2PC）协议实现的，可以保证数据的强一致性，许多分布式关系型数据管理系统都采用此协议来完成分布式。阶段一为准备阶段，即所有的参与者准备执行事务并锁住需要的资源。当参与者Ready时，向TM 汇报自己已经准备好。阶段二为提交阶段。当TM 确认所有参与者都Ready 后，向所有参与者发送COMMIT 命令。
            4.  XA 事务的缺点是性能不好，且无法满足高并发场景。一个数据库的事务和多个数据库间的XA 事务性能会相差很多。因此，要尽量避免XA 事务，如可以将数据写入本地，用高性能的消息系统分发数据，或使用数据库复制等技术。只有在其他办法都无法实现业务需求，且性能不是瓶颈时才使用XA。
    6.  面试题：
        
        1.  分布式事务的解决方案
8.  备份和恢复
9.  性能调优
    
    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.  尽量使用索引
            
            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之数据查询
        2.  有些情况会使索引失效，而进行全表查询
            
            1.  避免在 where 子句中对字段进行 null 值判断，也避免在索引列上使用IS NULL和IS NOT NULL
            2.  避免在 where 子句中使用!=或<>操作符
            3.  in 和 not in 也要慎用，很多时候用 exists 、not exists 代替 in、not in 是一个好的选择
            4.  使用like 查询时，尽量避免不在字段前加%，可以使用后面带%，前%的话会使索引失效
        3.  其他
            
            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列的索引。
            8.  日常维护
                
                1.  定期检查慢的查询日志并优化查询机制以有效使用缓存来减少磁盘I/O。优化它们，以扫描最少的行数，而不是进行全表扫描。
                2.  其他可以帮助DBA检查和分析性能的日志包括：错误日志、常规查询日志、二进制日志、DDL日志(元数据日志)。
                3.  定期刷新缓存和缓冲区以降低碎片化。使用OPTIMIZETABLE语句重新组织表并压缩任何可能被浪费的空间。
        4.  开启慢查询日志，定位哪一条语句出现过慢问题，然后用explain分析（一般看type、key、rows这几个字段）。
10. 架构设计
    
    1.  复制
        
        1.  **主从的好处是？**
            
            1.  主库故障，可以快速切换至从库提供服务；
            2.  在从库执行查询操作，降低主库的访问压力；
            3.  在从库执行备份，避免备份期间对主库影响；
        2.  **主从复制原理**
            
            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重现中继日志中的消息，从而改变数据库的数据）
        3.  **主从复制延迟问题及解决方案**
            
            1.  **主从复制延迟原因**
                
                1.  人为出错造成的原因
                    
                    1.  往从库写入数据
                    2.  主从sql\_mode 不一致
                    3.  MySQL自增列主从不一致
                2.  **在不认为出错的情况下，出现复制延迟的原因**  
                    主库和从库之间不在同一个主机上，数据同步之间不可以避免地具有延迟
            2.  **解决方案：**
                
                1.  ****设置从库库为只读模式
                2.  binlog row格式
                3.  优化的设置：
                    
                    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
                4.  由于主库和从库之间不在同一个主机上，数据同步之间不可以避免地具有延迟，解决的方法有添加缓存，业务层的跳转等待，如果非得从数据库层面去减缓延迟问题，可以从复制时候的三个过程入手（主库产生日志，主从传输日志，从库还原日志内容）：
                    
                    1.  .主库写入到日志的速度  
                        控制主库的事务大小，分割大事务为多个小事务。  
                        如插入20w的数据，改成插入多次5000行（可以利用分页的思路）
                    2.  二进制日志在主从之间传输时间  
                        主从之间尽量在同一个机房或地域。  
                        日志格式改用MIXED，且设置行的日志格式未minimal，原理详见上面的日志格式介绍。
                    3.  减少从库还原日志的时间  
                        在MySQL5.7版本后可以利用逻辑时钟方式分配SQL多线程。  
                        设置逻辑时钟：slave\_parallel\_type=‘logical\_clock';  
                        设置复制线程个数：slave\_parallel\_workers=4;
    2.  分库分表
        
        1.  当数据量达到多少的时候需要分表
    3.  集群
11. 基础
    
    1.  安装mysql数据库
    2.  语言结构
    3.  字符集支持：比如utf8
    4.  列类型
    5.  操作符和函数
    6.  sql语句语法
        
        1.  数据定义语句
        2.  数据操作语句
        3.  使用工具语句
        4.  事务处理和锁定语句
        5.  数据库管理语句
        6.  复制语句
        7.  用于预处理语句的sql语法
12. 基础面试题
    
    1.  **主键 超键 候选键 外键**
        
        1.  ****超 键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
        2.  候选键：最小超键，即没有冗余元素的超键。它需要同时满足下列两个条件：  
            1.这个属性集合始终能够确保在关系中能唯一标识元组。（满足这个条件的就是超键）  
            2.在这个属性集合中找不出合适的真子集能够满足条件。
        3.  主 键：数据表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
        4.  外 键：在一个表中存在的另一个表的主键称此表的外键  
            ![20181220212242173][]
    2.  **解释null值**
        
        1.  ****NULL 值代表遗漏的未知数据。
        2.  默认地，表的列可以存放 NULL 值。
        3.  如果表中的某个列是可选的，那么我们可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味着该字段将以 NULL 值保存。
        4.  NULL 值的处理方式与其他值不同。
        5.  NULL 用作未知的或不适用的值的占位符。
    3.  **char 和 varchar 的区别是什么？**
        
        1.  ****char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：
        2.  char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）  
            在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．
        3.  在MySQL中用来判断是否需要进行对据列类型转换的规则
            
            1.  在一个数据表里，如果每一个数据列的长度都是固定的，那么每一个数据行的长度也将是固定的．
            2.  只要数据表里有一个数据列的长度的可变的，那么各数据行的长度都是可变的．
            3.  如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．  
                例外：长度小于4个字符的char数据列不会被转换为varchar类型
    4.  **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
    5.  **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.  自联接是一种常规联接，但表本身是连接的。 
    6.  **触发器的作用？**  
        触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。
    7.  **存储过程(procedure)和函数(function)区别**  
        本质上它们都是存储程序。函数只能通过return语句返回单个值或表对象；而存储过程不允许执行return语句，但是可以通过output参数返回多个值。函数限制比较多，不能用临时变量，只能用表变量，还有一些函数都不可用等等；而存储过程的限制相对就比较少。函数可以嵌入在SQL语句中使用，可以在select语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行。
    8.  **什么是存储过程？用什么来调用？**  
        存储过程是一个预编译的SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL ，使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象来调用存储过程。
    9.  **存储过程与触发器的区别**  
        触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。  
        触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。  
        触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，mysql就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

[format_png]: /images/20220417/19355bda853848f796e7220de8ac6fbb.png
[format_png 1]: /images/20220417/6ede8dfeb06b4f07a2932418a5cc3d6c.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
[Hive shelll]: https://blog.csdn.net/yiguang_820/article/details/86657007
[MySQL]: http://lib.csdn.net/base/mysql
[20181220212242173]: /images/20220417/27836ca163f44edaa289a9532a2f1036.png