MySQL高级面试题

MySQL高级面试题 一、MySQL逻辑架构

1.Connectors

• Connectors，指的是不同语言中与SQL的交互。

2. 连接层

• 最上层是客户端的连接服务，采用 TCP/IP，该层有一个 线程池 ，每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

3.服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。

• Management Serveices & Utilities： 系统管理和控制工具
• SQL Interface: SQL接口
  • 接收用户的SQL命令，并且返回用户需要查询的结果。
• Parser: 解析器
  • 对 SQL 语句进行语法分析、语义分析。
• Optimizer: 查询优化器
  • SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个
    执行计划 。
• Caches & Buffers： 查询缓存组件
  • 以key-value的方法缓存查询结构

4.引擎层

• 真正的负责了MySQL中数据的存储和提取

5.存储层

• 文件系统(主要是表数据)，日志文件

二、SQL执行流程

SQL执行流程：SQL 语句 → 查询缓存 → 解析器 → 优化器 → 执行器

三、InnoDB 和 MyISAM 的区别

• 事务、分布式事务： InnoDB 支持 ， MyISAM不支持
• 锁：InnoDB:支持行锁 ；MyISAM：支持表锁
• 外键： InnoDB 支持 ， MyISAM不支持
• 数据表结构： InnoDB： .frm 、 .ibd ;
  MyISAM: .frm 、 .myd 、.myi
• 主键索引的不同：InnoDB：索引即数据
  MyISAM：索引和数据是分开的
  相同点：默认索引都使用B+树
• 默认引擎 ：5.5之前默认MyISAM ，5.5之后默认InnoDB
• 开发中InnoDB ：读写效率较 MyISAM差一些，占用空间大
  MyISAM : 适合于小型设备，读写效率高，占用空间小

四、B+树的数据结构

• 明确概念：目录项页、数据项页；叶子节点、内节点、根节点
• 根节点位置自始至终不变，只有一个；目录项页之间构成双向链表、目录项存储索引对应的字段+指向的下层页的地址、目录项之间构成单向链表;数据项页之间构成双向链表、数据项存（聚簇索引：表中的一条记录;二级索引:索引的字段+主键值）、数据项之间构成单项链表;
  

五、索引的优缺点 1.优点

• 降低数据库的IO成本
• 通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性
• 加速表和表之间的连接，以提高查询速度
• 可以显著 减少查询中分组和排序的时间 ，降低了CPU的消耗

2.缺点

• 创建索引和维护索引要耗费时间
• 索引需要占磁盘空间
• 降低更新表的速度

六、索引在哪些情况下适合添加

• 字段的数值有唯一性的限制
• 频繁作为 WHERe 查询条件的字段，尤其在数据表大的情况下
• 需要经常 GROUP BY 和 ORDER BY 的列
• UPDATE、DELETE 的 WHERe 条件列，一般也需要创建索引
• DISTINCT 字段需要创建索引
• 做多表 JOIN 连接 *** 作时(对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致)
• 尽量使用索引列的类型小的创建索引(指的就是该类型表示的数据范围的大小)
• 尽量使用字符串前缀来索引
• 组合索引把散列性高(区分度高)的值放在前面
• 在多个字段都要创建索引的情况下，联合索引优于单值索引

七、索引在哪些情况下不适合添加

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

八、常见索引的概念

• 按 功能逻辑 划分，索引主要有 4 种，分别是普通索引、唯一索引、主键索引和全文索引。
• 按 物理实现方式 划分 ，索引可以分为 2 种：聚簇索引和非聚簇索引(二级索引)。
• 按照 字段个数 划分，分成单列索引和联合索引。

1. 普通索引：在创建普通索引时，不附加任何限制条件，主要用于提高查询效率。
2. 唯一性索引： 使用 UNIQUE参数 可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里 可以有多个 唯一索引。
3. 主键索引：是一种 特殊的唯一性索引 ，在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，一张表里 最多只有一个 主键索引。
4. 单列索引 ：在表中的单个字段上创建索引。一个表 可以有多个 单列索引。
5. 多列(组合、联合)索引 ：多列索引是在表的多个字段组合上创建一个索引。只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循 最左前缀集合
6. 全文索引（也称全文检索）：是目前 搜索引擎 使用的一种关键技术。它能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。使用参数 FULLTEXT 可以设置索引为全文索引。
7. 聚簇索引(主键索引)：不用主动定义；一个表有且只有一个；建议使用主键充当，而且主键要auto_increment；数据项页里保存表的数据：数据及索引，索引即数据
   • 优点：数据访问快、排序和范围查找上快、节省io。
   • 缺点：插入数据慢、更新主键代价高、不需要回表 *** 作
8. 二级索引（辅助索引）：可以定义多个，理解为非聚簇索引；内节点：索引的字段+主键对应的列值+指向下层页的地址；叶子节点：索引的字段+主键对应的列值
   通常会有回表 *** 作；相比于聚簇索引，插删改的效率更高

九、索引在哪些情况下会失效

1. 全值匹配 我最爱
2. 最佳左前缀法则：检索数据时从联合索引的最左边，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。
3. 主键插入顺序：如果我们插入的 主键值忽大忽小 的话，会造成 页面分裂，导致 性能损耗
4. 计算、函数、类型转换(自动或手动)导致索引失效
5. 范围条件右边的列索引失效
6. 不等于(!= 或者<>)索引失效
7. is not null无法使用索引，is null可以使用索引
8. like以通配符%开头索引失效
9. 类型转换导致索引失效：设计实体类属性时，一定要与数据库字段类型相对应，否则，就会出现类型转换的情况。
10. OR 前后存在非索引的列，索引失效 ：只要有条件列没有进行索引，就会进行 全表扫描

十、 EXPLAIN(执行计划)各列作用

1. table：将整个SQL语句分为多个单表访问的表，查询的每一行记录都对应着一个单表
2. id：在一个大的查询语句中每个SELECT关键字都对应一个唯一的id(优化器将子查询转换为连接查询除外)
3. select_type：SELECT关键字对应的那个查询的类型,确定小查询在整个大查询中扮演了一个什么角色
4. partition：匹配的分区信息
5. type：针对单表的访问方法
6. possible_keys和key：可能用到的索引 和 实际上使用的索引
7. key_len：实际使用到的索引长度
8. ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息
9. rows：预估的需要读取的记录条数
10. filtered: 某个表经过搜索条件过滤后剩余记录条数的百分比
11. Extra:一些重要的额外信息