卷王之王---MySQL最强篇 面试必备_卷王 数据库

MySQL常用存储引擎有什么？他俩的区别是什么？

  MyISAM:不支持外键和事务，只支持表级锁
  InnoDB:默认引擎，支持外键和事务，支持表级锁和行级锁

数据库三大范式

  第一范式：确保每列保持原子性，数据表中的所有字段值都是不可分解的原子值
  第二范式：确保表中的每列都和主键相关
  第三范式：确保每列都和主键列直接相关而不是间接相关

MySQL的数据类型有哪些？

整数，浮点数，字符串，日期

索引

1.优缺点？
优点：

• 大大加快数据检索速度
• 将随机I/O变成顺序I/O（因为B+树的叶子结点是连接在一起的）
• 加速表与表之间的连接

缺点：
空间角度考虑：建立索引需要占用物理空间，耗费资源
时间角度考虑：创建和维护索引都需要花费时间

索引创建原则：
应该创建索引的列：

•  在经常需要搜索的列上，可以加快搜索的速度
•  在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
•  在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因   为索   引已经排序，其指定的范围是连续的

不该创建索引的列：

•  对于那些在查询中很少使用或者参考的列不应该创建索引。
•  对于那些只有很少数据值或者重复值多的列也不应该增加索引。

 
 索引的数据结构：

 1.B-TREE

B-树就是B树，多路搜索树，树高一层意味着多一次的磁盘I/O

特征：

•  关键字集合分布在整颗树中
•  任何一个关键字出现且只出现在一个结点中
•  搜索有可能在非叶子结点结束
•  其搜索性能等价于在关键字全集内做一次二分查找 
•  自动层次控制

  2.B+TREE

B+树是B-树的变体，也是一种多路搜索树

特征：

•  所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
•  不可能在非叶子结点命中；
•  非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据   的数据层；
•  每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
•  更适合文件索引系统；

 3. HASH

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

懒得看每个的特点那你就看我给你总结好的也就是面试官爱问的

 1.B树和B+树的区别？

•  B树中的内部节点和叶子节点均存放键和值，而B+树的内部节点只有键没有值，叶子结点存   放所有的键和值
•  B+树的叶子结点是通过链表相连在一起的，方便检索，而B树没有

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

 因为两者的数据结构不同，所以导致他们的场景也不同，哈希索引一般用于精确查找，B+树索引则多用于除了精确的等值查找外的其他查找，但是在绝大多数情况下都是用B+树索引。
 

•  哈希索引不支持排序，因为哈希表无序
•  哈希索引不支持范围查找
•  哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的

 3. 数据库为什么使用B+树索引而不是B树索引？

 你可以答B树与B+树的区别，你也可以从特点答：

•  B树适用于随机检索，而B+树适用于随机检索和顺序检索
•  B+树的空间利用率更高，可以降低I/O次数，使得数据检索速度更高（因为B树的每个节点需要存键和值，而B+树内部结点只用存键，这样B+树的一个节点就可以存储更多的索引，从而减低树的高度，减少I/O）
•  B+树的叶子结点都是通过链表连接在一起的，所以范围内查找，顺序查找更加方便
• 那什么时候可以用B树呢，因为B树的结点可以存值，所以当一些频繁访问的值放在离根结点比较近的时候可以用，因为这样查询效率高

索引分类：
功能分类：
 1. 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入。实现代码：

CREATE INDEX IndexName ON TableName(字段名(length));

或者

ALTER TABLE TableName ADD INDEX IndexName(字段名(length));

 2. 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。实现代码：

 CREATE UNIQUE INDEX IndexName ON TableName(字段名(length));

 或者

 CREATE UNIQUE INDEX IndexName ON TableName(字段名(length));

 3. 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；

ALTER TABLE TableName ADD PRIMARY KEY(column_list); 

 4. 全文索引：它查找的是文本中的关键词，主要用于全文检索。（接着看 mybro）

物理分类：
 1. 聚簇索引：不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。 

聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

 2. 非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。
 

注意：虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

---

 一天一个小扩展：聚簇索引优缺点？

---

 优点：

•  数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
•  聚簇索引对于主键的排序查找和范围查找速度非常快

 缺点：

•  插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
•  二级索引（辅助索引）访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

 
弯道超车小tips

---

 Mysql中key 、primary key 、unique key 与index区别

---

 key 与 index 区别：

•  key具有两层含义：1.约束（约束和规范数据库的结构完整性）2.索引
•  index：索引

 
 key 种类：等价普通索引  key 键名 (列)
 

•  primary key：

约束作用（constraint），主键约束（unique，not null，一表一主键，唯一标识记录），规范存储主键和强调唯一性为这个key建立主键索引

•  unique key：

约束作用（constraint），unique约束（保证列或列集合提供了唯一性）为这个key建立一个唯一索引

•  foreign key：

约束作用（constraint），外键约束，规范数据的引用完整性为这个key建立一个普通索引

 最左匹配原则：
 从左边为起点开始连续匹配，遇到阀内查询（<、>、between、like）会停止匹配
 前缀索引：
 有时候需要索引很长的字符列，这会让索引变得大且慢。通常可以以某列开始的部分字符作为索引，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值和数据表的记录总数的比值，索引的选择性越高则查询效率越高。
 两个小思考：
 使用索引查询一定能让性能提升吗？
     不一定，因为创建索引和维护索引需要花费时间和空间代价，所以如果没有合理使用索引反而会使查询性能下降
非聚簇索引一定会进行回表查询吗？

回表查询：上面说了非聚簇索引的叶子结点存储的是主键，也就是说要先通过非聚簇索引找到主键，在通过聚簇索引找到主键对应的数据，后面在通过聚簇索引找到主键对应的数据的过程就是回表查询。

        答案是不一定的，这里涉及到一个索引覆盖的问题，如果查询的数据在辅助索引上完全能获取到便不需要回表查询。
通过索引覆盖就能解决非聚簇索引回表查询的问题

索引失效：

•  只有前缀匹配可以使用索引，中缀匹配和后缀匹配会失效，进行全表扫描 
•  条件中有or 
•  在索引上进行计算会导致索引失效
•  在索引上使用函数会导致索引失效

数据库的事务

事务四大特性：

1. 原子性：原子性是指包含事务的操作要么一次全部成功，要么全部失败回滚
2. 一致性：一致性是指事务在执行前后的状态一致
3. 隔离性：一个事务所进行的修改在最终提交之前，对其他事务不可见
4. 持久性：数据一旦提交，其所作的修改将永远地保存在数据库中

数据库的并发一致性问题

        当多个事务并发执行时，可能会出现以下问题：

• 脏读：事务A更新了数据，但还没有提交，这时另一个事务B读取到了事务A更新后但未提交的数据，这个读的数据就是脏数据。
• 不可重复读：事务A对数据进行多次读取，事务B在事务A多次读取数据的过程中执行了更新操作并提交了，那么A对之前多次读取的数据并不一致。
• 幻读：事务A在读取数据后，事务B向事务A读取的数据中插入了几条数据或者删除几条数据，事务A再次读取数据时发现了多或者少了几条数据，和之前读的不一致。
• 丢失修改：事务A和事务B都同时对一个数据进行修改，事务A先修改，随后事务B进行修改，则事务B的修改就覆盖了事务A的修改，那么A的数据就丢失了。

数据库隔离级别：

• 未提交读：一个事务在提交前，他的修改对其他事物也是可见的,会出现脏读，不可重复读和幻读
• 提交读：一个事物提交之后，他的修改才能被其他事务看到。可以解决脏读
• 可重复读：在同一个事务中多次提取的数据是一致的。可以解决脏读和不可重复读（MySQL默认隔离级别）
• 串行化：需要加锁实现，会强制事务串行执行。可以解决脏读，不可重复读和幻读

隔离级别是如何实现的：

        事务的隔离机制只要是依靠锁机制和MVCC（多版本并发控制）实现的，提交读和可重复读可通过MVCC实现，串行化可以通过锁机制实现

MVCC：

        MVCC是一种控制并发的方法，主要提高数据库的并发性能。

在了解MVCC之前需要先了解当前读和快照读

• 当前读：读取的是数据库的最新版本，并且在读取是要保证其他事务不会修改当前记录，所以会对读取的记录加锁。
• 快照读：不加锁读取操作即为快照读，使用MVCC来读取快照中的数据，避免加锁带来的性能损耗

        由此可以看出MVCC的作用就是在不加锁的情况下，解决数据库读写冲突的问题，并且解决脏读、幻读以及不可重复读。但是不能解决丢失修改问题

MVCC的实现原理：

• 版本号
• 行记录隐藏的列
• undo日志

数据库的锁

什么是数据库的锁？

        当数据有并发事务的时候，保证数据访问顺序的机制称为锁机制

数据库的锁和隔离级别的关系？

数据库锁的类型？

按照锁的粒度可以将MySQL锁分为三种：

MyISAM默认采用表级锁，InnoDB默认采用行级锁

从锁的类别上区分：

• 共享锁：又称读锁，简写为S锁，一个事务对一个数据对象加了S锁，可以对这个数据对象进行读取操作，但不能进行更行操作，并且在加锁期间其他事务只能对这个数据对象加S锁，不能加X锁
• 排他锁：又称写锁，简写为X锁，一个事务对一个数据对象加了X锁，可以对这个对象进行读取和更新操作，加锁期间，其他事务不能对该数据对象进行加S锁或X锁

MySQL中的InnoDB引擎的行锁模式是如何实现的？

行锁模式：

   在存在行锁和表锁的情况下，一个事务相对某个表加X锁时，需要先检查是否有其他事务对这个表加了锁或对这个表的某一列加了锁，对表的每一行都进行检测一次，这是效率非常低的，为了解决这种问题，实现了多粒度锁机制，InnoDB还有两种内部使用的意向锁，两种意向锁都是表锁

• 意向共享锁：简称IS锁，一个事务打算给数据行加共享锁之前必须先获得该表的IS锁
• 意向排他锁：简称IX锁，一个事务打算给数据行加排他锁之前必须先获得该表的IX锁

有了意向锁，一个事务想对某个表加X锁，只需检查是否有其他事务对这个表加了X/IX/S/IS锁即可

锁的兼容性：

 行锁实现方式：

        InnoDB的行锁是通过给索引上的索引项加锁实现的，如果没有索引，InnoDB将通过稳藏的聚簇索引来对记录进行加锁

InnoDB行锁主要分为三种情况：

• Record lock：对索引项加锁
• Grap lock：对索引之间的“间隙”、第一条记录前的“间隙”或最后一条后的间隙加锁
• Next-key lock：前两种放入组合，对记录及前面的间隙加锁，默认的行锁算法

        InnoDB行锁的特性：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际产生的效果和表锁是一样的。

乐观锁和悲观锁以及实现

1. 乐观锁：系统假设数据的更新在大多数是不会产生冲突的，所以数据库只在更新操作提交的时候对数据检测，如果产生冲突，则数据更新失败。 一般通过版本号和CAS算法实现。
2. 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作，通俗的讲就是每次去拿数据的时候都认为别人会修改，所以每次在那数据的时候都会上锁。一般通过数据库的锁机制（行锁、表锁）实现

死锁：

        死锁是指两个或者两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象。在MySQL中，MyISAM是一次获得所需的全部锁，要么全部满足，要么全部等待，所以不会出现死锁，在InnoDB存储引擎中，除了单个SQL组成的事务外，锁都是逐步获得的，所以存在死锁问题。

如何避免死锁或者锁冲突：

• 尽量使用比较低的隔离级别
• 尽量使用索引访问数据，使加锁更加准确，从而减少锁冲突
• 如果不同的程序并发存取多个表，尽量以相同的顺序访问表

SQL语句

SQL语句分类：

• 数据库定义语言DDL：CREATE,DROP,ALTER等对逻辑结构有操作的，包括表结构，视图和索引
• 数据库查询语言DQL：主要以SELECT为主
• 数据库操纵语言DML：主要包括INSERT,UPDATE,DELETE
• 数据库控制功能DCL：主要是权限控制操作，包括GRANT,COMMIT,ROLLBACK等

子查询：

        把一个查询结果在另一个查询中使用

连接查询：

• 外连接：1.左外连接（LEFT JOIN）：显示左表中所有的数据以及右表中复合条件的数据，右表中不符合的数据为null    2.右外连接（RIGHT JOIN）：显示右表中所有的数据以及左表中符合条件的数据，左表中不符合条件的数据为null  3.全连接：MySQL不支持
• 内连接：只显示符合条件的数据
• 交叉连接：使用笛卡尔积的一种连接

drop、delete、truncate的区别？

    一般来说，删除整个表使用drop，删除表的部分数据使用delete，保留表的结构删除表的全部数据使用truncate。

UNION和UNION ALL的区别？

    union和union all的作用都是将两个结果集和并在一起

• union会对结果去重并排序，union all 直接返回合并后的结果，不去重也不排序
• union all 的性能比union好

SQL语句优化：

• 查询语句不要用select *
• 尽量减少使用子查询，使用关联查询替代
• or查询尽量用union或者union all替代
• 尽量避免在where子句中对字段进行null值判断，会使引擎放弃使用索引进行全表查询

视图：

     视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增改查操作，对视图的修改不影响基本表，使得我们获取数据更容易，相比多表查询

大表数据查询如何优化？

• 索引优化
• SQL语句优化
• 水平拆分
• 垂直拆分
• 使用缓存技术

了解慢查询吗？统计过吗？如何优化？：

     慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志

相关参数：

• slow_query_log:是否开启慢日志查询，1表示开启，0表示关闭
• slow_query_log_file:MySQL数据库慢查询日志存储路径
• slow_query_time:慢查询阈值，当SQL语句查询时间大于阈值，会被记录在日志上
• log_output:日志存储方式。“FILE”表示将日志存入文件，“TABLE”表示将日志存入数据库

如何对慢查询优化？

• 优化数据库的结构，将字段很多的表分解成多个表，考虑建立中间表
• 分析语句的执行计划，查看SQL语句的索引是否命中
• 优化limit分页

主键一般用自增ID还是UUID？

      一般情况MySQL推荐使用自增ID，因为在MySQL的InnoDB的存储引擎中，追歼索引是一种聚簇索引，主键索引的B+树的叶子结点按照顺序存储主键值及数据，如果主键索引是自增ID，只需要按照顺序往后排列即可，如果是UUID，id是随机生成的，在数据插入的时候会造成大量的数据移动，产生大量的内存碎片，造成插入性能下降