MySQL常见面试题总结

用户421763281407
0 阅读8分钟

Mysql 基础

什么是关系型数据库：

  1. 关系型数据库与非关系型数据库相对，体现在，关系型数据库的数据建立在关系模型上，表明了数据之间的联系：一对一，一对多，多对多。
  2. 关系型数据库的所有数据都在表上，使用SQL语言来操作数据。
  3. 大多数关系型数据库都支持事务的四大特性 ACID。
  4. 常见的包括 MySQL、PostgreSQL、SQLlite、Oracle

SQL是什么？

  1. Structured Query Language. 结构化查询语言

MySQL的优点？

  1. 开源免费，社区活跃，任何问题几乎都能找到方案，适配所有主流编程语言，框架。
  2. 功能强大，支持事务、优秀的性能和扩展支持。
  3. 运维简单，开箱即用，上手简单。

MySQL字段类型

image.png

char 和 varcher 的区别

  1. char是定长，右边填充空格，检索时去掉空格。适合存长度固定的字符串。 如密钥、md5、加密后密码、身份证号码。
  2. varcher 适合存长度不确定的字符串。

为什么不推荐 TEXT 和 BLOB?

  1. 没有默认值
  2. 无法使用内存临时表，只有磁盘临时表
  3. 检索效率低
  4. 无法直接索引
  5. 消耗大量网络和IO带宽

DATETIME 和 TIMESTAMP的区别？

  1. DATETIME没有时区信息，TIMESTAMP是格林尼治时间。
  2. 时间戳是4个字节，datetime是8个字节

    • DATETIME：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'

    • Timestamp：'1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.999999' UTC

  3. MySQL可以自动转换时间戳，但是datetime需要自己处理。
  4. 但是时间错时间只能到2038年。

为什么 MySQL 不建议用 null作为默认值。

  1. 存储空间大小未知，需要一些空间， "" 空串可以直接一个字节，
  2. 任何值与 NULL 比较值都是 NULL。
  3. 聚合函数会忽略掉 NULL 。

Boolean 可以用 TYNYINT(1) 表示。

手机号用 INT 还是 VARCHAR ？

  1. 手机号可能包含前导0,甚至可能有分割符号 ‘-’、‘ ’。
  2. 查询灵活性，需要根据段号，如138**, 可以用like 138%
  3. 手机号可能需要加密，数字型没办法加密处理。

MySQL基础架构

  1. 连接器：管理连接和权限验证
  2. 旁路缓存：命中缓存直接返回（废弃）
  3. 分析器：词法分析和语法分析
  4. 优化器：执行计划生产，选择索引
  5. 执行器：操作引擎，返回结果，如果没有权限就报错。
  6. 存储引擎：

MySQL 存储引擎

  1. SHOW ENGINES 查看MySQL支持的引擎。
  2. 默认为InnoDB ，只有InnoDB支持事务。

MySQL存储引擎是可插拔的，甚至可以为不同的表设置不同的存储引擎，也可以自己编写存储引擎。

MyISAM 和 InnoDB 的区别

  1. 锁： MyISAM只支持表锁，但是InnoDB支持行锁和表锁，默认为行锁。
  2. 事务: InnoDB 支持事务，实现了ACID, 定义了4个隔离级别，脏读，读已提交，可重复读，串行化。InonoDB的RR可以解决幻读的发生。InnoDB实现了MVCC
  3. 外键： InnoDB 支持外键，可以实现数据一致性，但是并不好，难迁移，不适合分布式，阻塞更新，存在更新风暴，影响数据库分插入速度。
  4. InnoDB 通过 redolog实现了数据的持久化。
  5. 索引结构B+树： 都是 B+ 树但是 innodb 支持聚簇索引。
  6. InnoDB 可以实现更快的读取速度，随cpu核数，InnoDB读写增加，但是另一个不行。
  7. 前者用 KeyCache缓存，后者用 Buffer Pool 缓存数据页和索引页。
  8. 几乎找不到用 前者的理由~

MySQL 的索引

  1. 索引的优点
  • 快速定位位置，减少磁盘I/O。
  • 主键索引可以保证数据的唯一性
  • 加速排序和分组，因为索引是已经排好序的，避免额外的排序操作。
  1. 索引的缺点。
  • 创建和维护耗时耗空间。
  • 如果要查询表的20/30%，优化器可能直接就不用索引了。反而导致性能下降。

索引快的原因

  1. 主要是减少了磁盘IO的次数。

MySQL有没有用B树和hash结构作为索引。

  1. memory 引擎有用，但是支持范围查询，排序，不支持部分索引键，hash冲突问题开销。

为什么不用B树，

  1. B+树矮胖，减少I/O次数。
  2. B+树深度稳定，查询效率可预期。
  3. B+树叶子用链表连接起来，支持范围查询。

索引覆盖

  1. 为所有的查询创建一个符合索引。 避免回表操作。

### MySQL 的联合索引及其最左前缀原则

  1. 使用多个字段建立索引，根据索引中字段顺序，从左到右匹配。直到遇到范围查询（> 、<），对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配，原因如下。
查询语句 (索引为 (a, b))结论原因分析
where a > 1 and b = 2只有 a 用到索引在 a > 1 的记录范围内，b 是无序的，无法利用索引定位。
where a >= 1 and b = 2a 和 b 都用到索引虽然在 a > 1 范围内 b 无序，但在 a = 1 的等值范围内 b 是有序的，优化器可以利用 b=2 缩小扫描范围。
where a BETWEEN 2 AND 8 AND b = 2a 和 b 都用到索引在 MySQL 中 BETWEEN 包含边界值，等同于 >= 和 <=。
where name like 'j%' and age = 22name 和 age 都用到索引在 name = 'j' 的情况下 age 是有序的，可以利用索引快速定位第一个 age=22 的记录。

索引失效的原因有哪些？

  1. 不遵守最左匹配原则。
  2. 索引上进行计算，函数，类型转换。
  3. 以%开头的like查询
  4. 查询的条件中使用OR，前后如果有一个列没有使用索引，涉及的索引都不会用到。

查询缓存为什么失效了？

  1. 每次查询后都要做缓存操作，失败后销毁。对于写密集很耗费性能。

ACID是什么，目的是什么？

  1. Atomic 原子性，事务要么都执行，要么都不执行
  2. Consistancy 一致性，每次事务执行前后都是合法的。
  3. Isolation 不同事务之间不应该能看到对方的中间状态。
  4. Duration 持久性，redo log，宕机之后恢复也可以看到整个状态。

并发事务带来的问题

  1. 脏读：读未提交
  2. 不可重复度：读已提交，读未提交都可能有这个问题。
  3. 幻读：发现两次读取的数据量不一样。

**不可重复读和幻读有什么区别？

  1. 主要是底层原因不一样。
  2. 幻读可以看作不可重复读的特殊情况，但是不可重复读是因为行的数据被修改，但是幻读是某个区间增加了行数。
  3. 解决方法也不一样，脏读是设置记录锁，幻读是设置间隙锁。 同时解决使用 二者结合， 称为临间锁。

MySQL的事务控制方法

  1. 锁和并发版本控制，一个是悲观一个是乐观
  2. MySQL 主要是用的读写锁来实现并发控制
  3. MVCC的实现手段是 隐藏字段，read view,undo log。 参见 如何用java自行实现一个并发版本控制

SQL 的4种事务隔离级别：

  1. 读未提交，很少用，数据一致性太弱
  2. 读已提交 不可重复读。
  3. 可重复读 可能会发生丢失更新。
  4. 可串行化 for update

SQL的几种锁

  1. 读锁，for share
  2. 排他锁 for update
  3. 意向锁，如果想用表锁，怎么判断有无行锁呢
  • 意向共享锁：事务意图给某些行加 行级 S 锁 之前，必须先获得表的 IS 锁。
  • 意向排他锁：事务意图给某些行加 行级 X 锁 之前，必须先获得表的 IX 锁。

如何分析 mysql 的性能？

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
  1. set varibles global long_query_time= 1s
  2. 重点关注字段：

  • type（连接类型）： 性能从好到差依次为 system > const > eq_ref > ref > range > index > ALL（全表扫描，重点优化对象）。

  • key（实际使用的索引）： 如果为 NULL，说明没走索引。

  • rows（预估扫描行数）： 该数值越小越好。

  • Extra（额外信息）：

    • Using filesort：说明需要额外排序，性能差。
    • Using temporary：使用了临时表，性能差。
    • Using index：说明触发了覆盖索引，性能极佳

MySQL怎么优化：

  1. 首先从慢SQL入手
  2. 再介绍索引优化
  3. 表结构设计
  4. MySQL参数，如缓冲池大小，最大连接数
  5. 架构层面的：读写分离，分库分表，数据冷热分离。
avatar
文章
阅读
粉丝