mysql面试

1.数据库的常用范式

• 第一范式：指表的列不可再分，数据库中表的每一列都是不可分割的基本数据项，同一列中不能有多个值。例如，一个人有多个手机号，但表中的手机号字段不能填写多个手机号
• 第二范式：
  • 表必须要有主键
  • 非主键必须完全依赖于主键
• 第三范式：消除非主键列对主键的传递依赖，非主键列必须直接依赖于主键
• 巴斯范式：
• 第四范式：
• 第五范式：

每个范式都是自底向上包含的，即满足第五范式，也必然满足第四范式。

2.范式的优缺点

• 优点：数据的标准化有助于消除数据的冗余，第三范式通常被认为在性能，扩展性和数据完整性方面达到了最好的平衡
• 缺点：范式的使用，可能会降低查询的效率。因为范式的等级越高，设计出来的数据表也就越多，越精细，数据的冗余度也就越低，进行数据的查询时可能就需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效

3.sql的执行顺序

• from

• on

• join

• where

• group by(开始使用select中的别名，后面的语句中都可以使用)

• avg,sum…

• having

• select

• distinct

• order by

• limit

4.常用的存储引擎？InnoDB与MyISAM的区别？

存储引擎是对底层物理数据执行实际操作的组件，为Server服务层提供各种操作数据的API。常用的存储引擎有InnoDB、MyISAM、Memory。这里我们主要介绍InnoDB 与 MyISAM 的区别：

• 事务：MyISAM不支持事务，innoDB支持事务

• 锁级别：MyISAM只支持表级锁，InnoDB支持行级锁和表级锁，默认使用行级锁，但是行锁只有通过索引查询数据才会使用，否则将使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。使用行锁可能会存在死锁的情况，但是表级锁不存在死锁

• 主键和外键：MyISAM 允许没有任何索引和主键的表存在，不支持外键。InnoDB的主键不能为空且支持主键自增长，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键，支持外键完整性约束

• 索引结构：MyISAM 和 InnoDB 都是使用B+树索引，MyISAM的主键索引和二级索引的Data域都是保存行数据记录的地址。但是InnoDB的主键索引的Data域保存的不是行数据记录的地址，而是保存该行的所有数据内容，而二级索引的Data域保存的则是主索引的值

• 全文索引：MyISAM支持全文索引，InnoDB在5.6版本之前不支持全文索引，5.6版本及之后的版本开始支持全文索引

• 表的具体行数：

  • MyISAM：有一个值保存有表的总行数，如果使用 select count() from table 会直接取出出该值，不需要进行全表扫描。

  • InnoDB：没有保存表的总行数，如果使用 select count() from table 需要会遍历整个表，消耗相当大。

• 表的存储结构：

  • MyISAM：

    • mysql5.7中：.frm：描述表结构文件，字段长度等

      mysql8.0中：变为.sdi：描述表结构文件，字段长度等

    • .MYD(mydata)：数据信息文件，存储数据信息

    • .MYI(myindex)：存放索引信息文件

  • innoDB：

    • mysql5.7中：db.opt文件用于保存数据库的相关配置，mysql8.0不再提供db.opt
    • .ibd：存储数据信息和索引信息
    • .frm：描述表结构文件，字段长度等，在mysql8.0中合并到了.ibd文件中

• 表的存储空间：

  • MyISAM可被压缩，存储空间较小
  • InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引

• 适用场景：

  • 如果需要提供回滚、崩溃恢复能力的ACID事务能力，并要求实现行锁级别并发控制，InnoDB是一个好的选择
  • 如果数据表主要用来查询记录，读操作远远多于写操作且不需要数据库事务的支持，则MyISAM引擎能提供较高的处理效率

5.B-树和B+树的区别

• 在B-树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值
• B+树的叶子节点有一条双向链相连，而B树的叶子节点各自独立

6.Hash索引和B+树的优劣

• hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询
• hash索引不支持模糊查询以及联合索引的最左前缀匹配。因为hash是将多个字段合并进行hash计算，无法比对单个字段
• hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过二级索引完成查询
• hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，会形成链表，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低

7.索引有哪几种类型？

• 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

  • 可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建联合索引

• 全文索引： 是目前搜索引擎使用的一种关键技术。

  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

8.聚簇索引和非聚簇索引

• 聚簇索引：叶子节点存放的是数据记录。innoDB中的主键索引就是聚簇索引，一个表最多只能有一个聚簇索引
• 非聚簇索引：在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
  • MyISAM使用的都是非聚簇索引
  • Innodb二级索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键

9.回表和覆盖索引

​ 由于innoDB的二级索引的叶子节点并没有存储完整的数据，如果我们先通过二级索引找到叶子节点中并没有保存我们所需要的所有数据，那么数据库会拿着符合条件的二级索引的叶子节点中记录的对应的主键，根据主键再去聚簇索引中寻找我们需要的数据，这个过程称之为回表。

​ 但如果我们找到二级索引中的叶子节点已经包含我们所需要的全部数据了，那么我们就不再需要进行回表操作，这就称为覆盖索引

10.联合索引的最左前缀原则

​ mysql可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引

​ 因为msql使用索引时，需要索引有序。在B+树中，肯定是先按照一个字段进行排序，当这个字段值相同时，再按照另一个字段排序。如果我们定义了一个(age,name,class)的联合索引，那应该先按照age进行排序，如果age相同，那么再按照name进行排序…当我们使用name或者class进行等值查询时，此时数据库并没用使用到索引。所以我们在创建联合索引时，应当将使用频率较高的字段放在联合索引的前面

11.适合创建索引的情况

• 字段的数值有唯一性
• 频繁作为where查询条件的字段
• 经常 group by 和 order by 的字段
• update和delete的where条件的字段
• distinct字段
• 多表join连接操作时，创建索引注意事项
  • 首先，连接表的数量尽量不要超过3张，因为每多增加一张表就相当于增加了一次嵌套循环，数量级增长会非常快，严重影响查询效率
  • 其次，对where条件创建索引，因为where才是对数据条件的过滤，如果在数据量非常大的情况下，没有where条件过滤非常可怕的
  • 最后，对用于连接的字段创建索引，并且该字段在多表中的类型必须一致，
• 使用列的类型小的创建索引
• 使用字符串前缀创建索引
• 区分度高得列(散列性高)得列适合作为索引
• 使用最频繁得列放到联合索引得最左边
• 多个字段都要创建索引得情况下，联合索引会优于单值索引

12.不适合创建索引的情况

• 在where中用不到的字段，不要设置索引
• 数据量小的表最好不要设置索引
• 有大量重复数据的列上不要创建索引
• 避免对经常更新的表创建索引
• 不建议用无序的值作为索引
• 删除不再使用或者很少使用的索引
• 不要定义冗余或者重复的索引

13.索引失效的情况

• 查询条件包含or
• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效
• 索引字段上使用is null， is not null，可能导致索引失效
• 复合索引未用左列字段
• like以%开头
• 需要类型转换
• where中索引列有运算
• where中索引列使用了函数
• 如果mysql优化器觉得全表扫描更快时（数据少）

14.事务的ACID特性

• 原子性(atomicity)：原子性是指事务是一个不可分割的状态，要么都执行成功，要么都回滚。

• 一致性(consistency)：根据定义，一致性是指事务执行的前后，数据从一个合法性状态变换到另一个合法性状态。这种状态是语义上的，而不是语法上的，跟具体的业务有关。例如，A向B转账，转账前后，A+B的值应该是不变的

• 隔离性(isolation)：隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰

• 持久性(durability)：持久性是指一个事务一旦被提交，它对数据库中的数据就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

  持久性是通过事务日志来保证的。日志包括了重做日志和回滚日志

总结：ACID是事务的四大特性，在这四个特性中，原子性是基础，隔离性是手段，一致性是约束，而持久性是我们的目的

15.什么是脏读、不可重复读、幻读？

• 脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如A事务执行过程中，B事务读取了A事务的修改。但是由于某些原因A事务没有完成提交，发生了回滚操作，则B事务所读取的数据是不正确的，这就是脏读。
• 不可重复读 ：表示一个事务读到另一个事务已经提交的updated数据，导致多次查询结果不一致。比如B事务读取了两次数据，在这两次的读取过程中A事务修改了数据，导致B事务的这两次读取出来的数据不一样，这就是不可重复读
• 幻读 ：表示一个事务读到另一个事务已经提交的insert数据，导致多次查询结果不一致。比如B事务读取了两次数据，在这两次的读取过程中A事务添加了数据，导致B事务的这两次读取出来的数据不一样，这就是幻读

16.事务的隔离级别

• 未提交读（read uncommited）：是最低的事务隔离级别，它允许另外一个事务可以读取当前事务未提交的数据。脏读，不可重复读，幻读都有可能发生
• 已提交读（read commited）： 保证一个事务提交后才能被另外一个事务读取，另外一个事务不能读取该事务未提交的数据。避免了脏读，但是不可重复读和幻读都有可能发生
• 可重复读（repeatable read）：保证一个事务不会修改已经由另一个事务读取但未提交或者未回滚的数据，避免了脏读和不可重复读，但是幻读有可能发生
• 可串行化（serializable）：最严格的事务隔离级别，支持事务串行执行，资源消耗最大，避免了脏读，不可重复读，幻读

17.锁

锁从不同角度的分类：

• 从数据操作类型划分：
  • 读锁：
  • 写锁：
• 从数据操作的粒度划分：
  • 表锁：
    • 表级别的S锁和X锁：
    • 意向锁：
    • 自增锁：
    • 元数据锁(MDL锁)：
  • innoDB的行锁：
    • 记录锁：
    • 间隙锁：
    • 临键锁：
    • 插入意向锁：
  • 页锁：
• 从对待锁的态度划分：
  • 乐观锁：
  • 悲观锁：

18.主键设计

• 自增主键的问题：
  • 可靠性不高：存在自增ID回溯的问题，这个问题直到mysql8.0才得到解决
  • 安全性不高：对外暴露的接口可以非常容易猜测对应的信息
  • 性能差：自增的id性能差，需要在数据库服务器端生成
  • 交互多：业务还需要额外执行一次类似last_insert_id()的函数才能知道刚才插入的自增值，只需要多一次网络交互
  • 局部唯一性：最重要的一点，自增ID是局部唯一的，只在当前数据库实例中唯一，而不是全局唯一，在任意服务器都是唯一的。对于目前分布式系统来说，简直是噩梦

推荐的主键设计：

非核心业务:对应表的自增主键，如警告、日志、监控等信息

核心业务:主键设计至少全局唯一且单调递增。全局唯一保证在各个系统之间都是唯一的，单调递增是希望插入时不影响数据库的性能