MySQL高级面试题整理

1. 执行流程

1. mysql客户端先与服务器建立连接
2. Sql语句通过解析器形成解析树
3. 再通过预处理器形成新解析树，检查解析树是否合法
4. 通过查询优化器将其转换成执行计划，优化器找到最适合的执行计划
5. 执行器执行sql

2. MYISAM和InNoDB的区别

1. MYISAM：不支持外键、不支持事务、表锁
2. InNoDB：支持外键、支持事务、行锁

3. 什么是索引

1. 索引是数据结构
2. 索引的目的是提高查询效率

4. 索引的优劣势

1. 优势：提高数据检索的效率、降低数据排序和分组的成本
2. 劣势：降低更新表的速度、占用空间

5. 聚簇索引与非聚簇索引

1. 聚簇索引：叶子结点存储的是行数据
2. 非聚簇索引：叶子结点存储的是物理地址值，访问数据总是需要二次查找(回表)

6. MySQL索引分类

1. 按功能逻辑分：主键索引、唯一索引、全文索引、普通索引
2. 按物理实现方式分：聚簇索引、非聚簇索引
3. 按作用字段分：单列索引、联合索引

1. 主键索引：设定为主键后数据库会自动建立索引
2. 单值索引：一个索引只包含单个列
3. 唯一索引：索引列的值唯一，可以为空
4. 复合索引：一个索引包含多个列

7. 不同的存储引擎支持的索引类型

1. InNoDB：支持B-tree、Full-text，不支持Hash索引
2. MyISAM：支持B-tree、Full-text，不支持Hash索引
3. Archive：不支持B-tree、Full-text，Hash索引
4. NDB：支持Hash索引，不支持B-tree、Full-text
5. Memory：支持B-tree、Hash索引，不支持Full-text索引

8. 创建索引

1. 隐式创建：有主键约束、唯一性约束、外键约束的字段会自动创建索引
2. 显示创建：create Table table_name Index index_name

9. 查看索引

1. 方式一：show create table 表名;
2. 方式二：show index from 表名;

10. 适合创建索引的11种情况

1. 字段的数值有唯一性的限制
2. 频繁作为where查询条件的字段
3. 经常使用group by和order by 的列
4. update、delete的where条件列
5. distinct字段需要创建索引
6. 多表联查时，创建索引的注意事项：第一，连接表的数量尽量不要超过三张；第二，对where条件创建索引；第三，对连接的字段创建索引
7. 使用列的类型小的创建索引
8. 使用字符串前缀创建索引
9. 使用区分度高的创建索引
10. 联合索引中使用最频繁的列放到联合索引的左侧
11. 在多个字段都需要创建索引的情况下，联合索引优于单值索引

11. 限制索引的数目：单表的索引尽量不要超过6个

1. 索引需要占用磁盘空间，需要维护
2. 索引会影响insert,update,delete等语句的性能‘
3. 优化器在选择执行计划时，如果有多个索引可以用，会增加优化器生成执行计划，降低查询效率

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

1. 在where中使用不到的字段不建议建立索引
2. 数据量小的表不建议建立索引
3. 字段有大量重复的数据(重复率达到百分之10)不建议建立索引
4. 经常更新的表不建议建立索引
5. 无序的字段不建议建立索引(例如身份证)
6. 不经常使用的索引建议删除
7. 不要定义冗余或者重复的索引

13. explain的四种格式

1. 传统格式：输出是表格形式
2. JSON格式：输出信息最详细的格式，包含了执行的成本信息
3. TREE格式：描述各个部分之间的关系和各个部分的执行顺序
4. 可视化输出：

14. explain各字段含义

1. select_type：区别普通查询、联合查询、子查询
2. table：查询的表名
3. type：all--->index--->range--->ref--->eq_ref--->const
4. key：当前查询实际使用的索引
5. key_len：索引中使用到的字节数，可以算出具体使用了索引中的哪些列
6. ref：显示索引的那一列被使用
7. rows：执行查询时必须检查的行数
8. extra：不适合在其他列中显示但十分重要的额外信息

15. extra：

1. Using filesort：使用了非索引字段排序
2. Using temporary：临时表、根据非索引字段进行分组
3. Using index：使用索引字段进行了查询
4. Using where：使用where进行了过滤
5. Using join buffer：使用了缓存，非主键缓存
6. impossible where：where子句的值总是false

16. 索引失效的10种情况

1. 在使用联合索引不满足最左匹配原则
2. 使用了select *
3. 索引列上有计算
4. 索引列上用了函数
5. 字段类型不同
6. like查询条件中左边包含%
7. 列对比
8. 使用or关键字：or关键字连接的字段都要建立了索引则不会失效，否则索引失效
9. not in,not exists使用在非主键字段会导致索引失效
10. order by后没有where和limit条件，满足最左匹配原则但是多个字段的排序规则不同

17. 索引优化

1. 物理查询优化：通过索引和表连接方式优化
2. 逻辑查询优化：通过SQL等价变换提升查询效率

18. 范式的种类

1. 第一范式(1NF)：数据表中每个字段必须满足原子性
2. 第二范式(2NF)：满足第一范式、必须有主键、非主键字段必须完全依赖主键；
3. 第三范式(3NF)：满足第二范式、非主键字段只和主键字段直接关联(即非主键字段不能依赖于其他非主键字段)
4. 巴斯-科德范式(BCNF)：满足第三范式、并且只有一个候选键
5. 第四范式(4NF)：
6. 第五范式(5NF,又称完美范式)：

19. 如何定位调优问题

1. 用户的反馈
2. 日志分析
3. 服务器资源使用监控
4. 数据库内部状态监控

20. 优化MySQL的参数

1. innodb_buffer_pool_size：innoDB类型的表和索引的最大缓存
2. Key_buffer_size：索引缓冲区的大小
3. table_cache：同时打开表的个数
4. quert_cache_size：查询缓冲区的大小

21. 事务的原子性、一致性和持久性由事务的redo和undo日志保证

1. redo日志：重做日志，提供再写入操作
2. undo日志：回滚日志，回滚到某个记录版本