MySQL校招面试题

用户3247699208174
153 阅读10分钟

MySQL

三范式

  • 保证每列属性的原子性

  • 每列都与主键相关(商品名称和商品价格对应商品编号)

  • 每列都与主键直接相关(可以商品表里面可以有外键：客户编号也是客户表的主键)

MySQL由哪些部分组成, 分别用来做什么

  1. Server

    • 连接器: 管理连接, 权限验证.

    • 分析器: 词法分析, 语法分析.

    • 优化器: 执行计划生成, 索引的选择.

    • 执行器: 操作存储引擎, 返回执行结果.

  2. 存储引擎: 存储数据, 提供读写接口.

有哪些数据类型、char和vchar的区别

INT、CHAR、VARCHAR、FLOAT、DOUBLE、timestamp

char是定长的，根据字符串长度分配足够的空间

cchar是可变长字符串，比定长更节省空间(使用额外的一个或两个字节存储)

什么是索引？优缺点？

是帮助MySQL高效获取数据的数据结构

优势：提高数据检索的效率，降低数据库的IO成本。通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势：索引需要占物理空间，对表中的数据进行增、删、改的时候，还需要动态的维护

MySQL支持哪几种索引？

常见的mysql主要有两种结构：hash索引和B+Tree索引

innodb引擎默认B+树

memory引擎默认hash

MyISAM索引与InnoDB索引的区别？

  • InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。

  • InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。

  • MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

什么是聚簇索引？何时使用聚簇索引与非聚簇索引？

  • 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

  • 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。

聚簇索引和非聚簇索引，在查询数据的时候有区别吗？为什么？

聚簇索引查询会更加快些。因为主键索引树的页子节点存储的是整行数据。也就是我们需要得到的数据。而非主键索引的页子节点是主键的值，查询的主键之后，我们还需要通过主键的值再次进行查询数据。（这个过程被称之为回表）。

InnoDB是通过B+树结构对主键创建索引，然后叶子节点中存储记录，如果没有主键那么会选择唯一键，如果没有唯一键那么会生成一个6字节的row_id作为主键

InnoDB聚簇索引，MyISAM非聚簇索引(判断数据和索引是否组织在一起)

分布式不推荐主键自增，单体的推荐使用主键自增

数据库的存储引擎类型和优缺点

  • MyISAM支持表级锁，不支持事务和外键，并发效率较低，读取数据快，更新数据慢

  • InnoDB支持行级锁、事务、外键，并发效率好，适用于增删操作

  • Memory，所有的数据都保存在内存中，访问速度快，一旦服务关闭数据将丢失。适合更新不太频繁的数据量小的表用来快速得到访问结果。

memory用的hash放在内存

磁盘速度慢，内存速度快，但一旦断电内存里的数据就没了，磁盘可以通过内存加快读取速度

磁盘预读：即使每次读一个很小的数，系统也会分页读取(一般4k或8k)

为什么采用B+树？和Hash索引的区别？

B+树是一种多路平衡查询树，他的节点是天然有序的，所以对于范围查询的时候不需要做全表扫描。

对于哈希索引来说，底层的数据结构就是哈希表，只适用于等值查询的场景，hash索引没办法利用索引完成排序

B+树是什么和B树有什么区别？

B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

MySQL的索引对B+树做了哪些优化？

答：MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

B+Tree的叶点都可以存放哪些东西？存储整行数据和主键的值的别？

1：innoDB的B+Tree可能存储的是整行数据，也有可能是主键的值。

  • 行数据：innoDB的B+Tree存储了整行数据的是主键索引，也被成为聚凑索引。

  • 存储主键的值：成为非主键索引，也被称为非聚凑索引

----------------------------------

索引有哪些分类？

普通索引: 基本的索引类型，允许在定义索引的列中插入重复值和空值

唯一索引: 索引列中的值必须是唯一的，允许为NULL值，一个表允许多个列创建唯一索引

主键索引: 索引列中的值必须是唯一的，不允许为NULL，一个表只能有一个主键

组合索引：多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

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

索引为什么不是越多越好

索引越多更新速度越慢，占用的物理空间更多

数据库的索引创建有哪些设计原则？

①对查询频次较高，数据量较大的表创建索引。

②限制索引数量：对于增删改操作较多的表，如果索引过多将需要很高的维护代价，降低操作效率，增加操作耗时。

③利用最左前缀：如果索引字段值过长，会降低索引的执行效率。

④删除不常用索引。

⑤使用唯一索引，区分度越高，效率越高。

⑥使用短索引，如果索引值很长则占用磁盘变大，会影响效。

⑦为常作为查询条件、经常需要排序、分组和联合操作的字段建立索引。

⑧尽量扩展现有索引，联合索引的效率高于多个独立索引

设计组合索引需要注意什么

出现频率越高的靠最左边

为什么产生最左匹配原则，底层是什么引起的

做过哪些MySQL索引相关优化

  • 尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗.

  • MySQL5.6之后引入了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗.

  • 若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表.

  • 联合索引将高频字段放在最左边.

索引在什么情况下会失效?

答：①复合索引未使用最左列索引时或跳跃使用时失效，例如以name，age和sex字段建立索引，只使用age和sex或只使用name和sex时索引失效。

②在索引上进行运算或函数操作时索引失效。

③数字字符没有加单引号索引失效，因为MySQL查询优化器会自动进行类型转换。

④在索引字段上使用，NOT、 <>、！= 或LIKE以%开头的查询索引失效，会进行全表扫描。

⑤OR的前后没有同时使用索引时索引失效。

⑥当全表扫描比使用索引速度更快时会使用全表扫描。

主键、外键作用

主键保证的是数据的唯一性

外键保证的是数据的完整性

唯一索引和普通索引查询和更新上的区别

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。建议尽量选择普通索引。

查询操作

  • 普通索引 查找到第一个满足条件的记录后，继续向后遍历，直到第一个不满足条件的记录。

  • 唯一索引 由于索引定义了唯一性，查找到第一个满足条件的记录后，直接停止继续检索。

普通索引会多检索一次，几乎没有影响。

更新操作

更新操作并不是直接对磁盘中的数据进行更新，是先把数据页从磁盘读入内存，再更新数据页。

  • 普通索引 将数据页从磁盘读入内存，更新数据页。

  • 唯一索引 将数据页从磁盘读入内存，判断是否唯一，再更新数据页。

由于 MySQL 中有个 change buffer 的机制，会导致普通索引和唯一索引在更新上有一定的区别。

change buffer的作用是为了降低IO 操作，避免系统负载过高。change buffer将数据写入数据页的过程，叫做merge。

如果需要更新的数据页在内存中时，会直接更新数据页；如果数据不在内存中，会先将更新操作记入change buffer，当下一次读取数据页时，顺带merge到数据页中，change buffer也有定期merge策略。数据库正常关闭的过程中，也会触发merge。

对于唯一索引，更新前需要判断数据是否唯一（不能和表中数据重复），如果数据页在内存中，就可以直接判断并且更新，如果不在内存中，就需要去磁盘中读出来，判断一下是否唯一，是的话就更新。change buffer是用不到的。即使数据页不在内存中，还是要读出来。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

结论：唯一索引用不了change buffer，只有普通索引可以用。

唯一索引比普通索引快吗, 为什么

唯一索引不一定比普通索引快, 还可能慢.

  1. 查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微.

  2. 更新时, 这个情况就比较复杂了. 普通索引将记录放到change buffer中语句就执行完毕了. 而对唯一索引而言, 它必须要校验唯一性, 因此, 必须将数据页读入内存确定没有冲突, 然后才能继续操作. 对于写多读少的情况, 普通索引利用change buffer有效减少了对磁盘的访问次数, 因此普通索引性能要高于唯一索引.

非主键索引一定会查询多次吗？

不一定的，通过覆盖索引也可以只查询一次。

----------------------------------

MySQL查询缓存有什么弊端, 应该什么情况下使用, 8.0版本对查询缓存有什么变更.

  • 查询缓存可能会失效非常频繁, 对于一个表, 只要有更新, 该表的全部查询缓存都会被清空. 因此对于频繁更新的表来说, 查询缓存不一定能起到正面效果.

  • 对于读远多于写的表可以考虑使用查询缓存.

  • 8.0版本的查询缓存功能被删了 (￣.￣).

面试关键词

回表：一般在使用二级索引时(数据量大的时候才能看见效果)
索引覆盖：二级索引返回的值就是要查找的值
索引下推：5.6之后的因为现在的磁盘处理速度还可以

假如组合索引有两列

  • 没索引下推时：查到匹配的第一列索引值时直接从存储引擎(磁盘)返回到服务(内存)再做筛选

  • 有索引下推时：必须全部查到才返回服务器

最左匹配：组合索引靠左原则
MRR：mult_range read

排好序方便按范围查询

----------------------------------

MySQL事务的隔离级别, 分别有什么特点

  1. 读未提交(RU): 一个事务还没提交时, 它做的变更就能被别的事务看到.

  2. 读提交(RC): 一个事务提交之后, 它做的变更才会被其他事务看到.

  3. 可重复读(RR): 一个事务执行过程中看到的数据, 总是跟这个事务在启动时看到的数据是一致的. 当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的.

  4. 串行化(S): 对于同一行记录, 读写都会加锁. 当出现读写锁冲突的时候, 后访问的事务必须等前一个事务执行完成才能继续执行.

MySQL数据库的隔离级别有哪些？分别有什么特点？

答：①未提交读，一个事务会读取到另一个事务没有提交的数据，存在脏读、不可重复读、幻读的问题。

②已提交读，一个事务可以读取到另一个事务已经提交的数据，解决了幻读的问题，存在不可重复读、幻读的问题。

③可重复读，MySQL默认的隔离级别，在一次事务中读取同一个数据结果是一样的，解决了不可重复读的问题，存在幻读问题。

④可串行化，每次读都需要获得表级共享锁，读写互相阻塞，效率低，解决了幻读问题。

读取数据库时可能出现哪些问题？

答：①脏读，一个事务中会读取到另一个事务中还没有提交的数据，如果另一事务最终回滚了数据，那么所读取到的数据就是无效的。

②不可重复读，一个事务中可以读取到另一个事务中已经提交的数据，在同一次事务中对同一数据读取的结果可能不同。

③幻读，一个事务在读取数据时，当另一个事务在表中插入了一些新数据时再次读取表时会多出几行，如同出现了幻觉。

MYSQL的MVCC是否解决了幻读

是

简述事务的ACID属性

答：①Atomicity表示原子性，事务中的所有操作都是不可分割的原子单位，要么全部成功，要么全部失败。

②Consistency表示一致性，无论正常执行还是异常退出，事务执行前后数据的完整性必须保持一致，比如转账前后双方的总金额是不变的。

③Isolation表示隔离性，并发操作中不同事务是互相隔离的，之间不会互相影响。

④Durability表示持久性，事务操作完成后数据就会被持久化修改到永久存储中。

----------------------------------

简述主从复制的基本原理

答：①主从复制是指一台服务器充当主数据库服务器，另外一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器中。对于多级复制，数据库服务器既可充当主机也可充当从机。

②MySQL主从复制的基础是主服务器对数据库修改二进制记录，从服务器通过主服务器的二进制日志自动执行更新。

一千万条数据的表, 如何分页查询

数据量过大的情况下, limit offset分页会由于扫描数据太多而越往后查询越慢. 可以配合当前页最后一条ID进行查询, SELECT * FROM T WHERE id > #{ID} LIMIT #{LIMIT}. 当然, 这种情况下ID必须是有序的, 这也是有序ID的好处之一.

订单表数据量越来越大导致查询缓慢, 如何处理

分库分表. 由于历史订单使用率并不高, 高频的可能只是近期订单, 因此, 将订单表按照时间进行拆分, 根据数据量的大小考虑按月分表或按年分表. 订单ID最好包含时间(如根据雪花算法生成), 此时既能根据订单ID直接获取到订单记录, 也能按照时间进行查询.

MySQL数据库cpu飙升到500%的话他怎么处理？

  • 当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。

  • 如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql，看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。

  • 一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。

  • 也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

MySQL怎么恢复半个月前的数据

通过整库备份+binlog进行恢复. 前提是要有定期整库备份且保存了binlog日志.

一个亿的数据找最大的一千个

一千万条数据B+树大概有多高

----------------------------------

数据库怎么读写数据、怎么存放数据

如何将表存在磁盘中

如何添加一个字段

alter table + 表名 + add + 要添加的字段 字段类型 + after + 要跟随的字段名

索引语法

主键索引: 数据列不允许重复，不允许为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);创建全文索引
avatar
文章
阅读
粉丝