mysql基础&高频面试题

背景

主要想分享之前在面试过程中遇到的一些关于mysql基础&高频面试题.我发现工作几年以后,基本上面试基本不问mysql围绕sql基本的问题了,开始围绕mysql的一些 八股文的问题开始问,在之前面试之前,刷了大概mysql关于高可用、隔离级别、事务、保持一致性、mysql执行原理、mysql底层引擎等相关执行,基本上这些能都能命中一些面试题.

从我的感觉是为什么面试官要问这些问题,可能大概有几点出发:

1)、工作几年以后, 应该能owner比较复杂的项目, 应该对整体架构有个清晰的了解, 技术架构选型、 数据存储类型、数据表结构设计, QA是必须要参与其中的.

2)、工作几年以后,多少也会接触性能测试,其中会发现数据库层的优化会有很多,有的是加索引就能解决、有的是加的索引不对、sql查询语句导致的慢sql等等问题.

3)、还能可能就是在测试业务过程中,一些偏离线计算或者要求数据精准的业务, 比如“隔离级别”这种问题,就会在一些极端的测试场景会有bug.

总之现在对QA的能力也在越来越高,唯有不断学习才能走到更高~

mysql基本类型

常用SQL语句

创建数据库

CREATE DATABASE test_base;

删除数据库

drop database dbname

创建新表

CREATE TABLE `db_table`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

旧表基础上创建新表

create table db_table_1 like db_table;

删除新表

drop table db_table_1

增加一列表

Alter table db_table add column name VARCHAR(40);

添加PRIMARY KEY（主键索引）

ALTER TABLE `db_table` ADD PRIMARY KEY ( `name` )

添加UNIQUE(唯一索引)

ALTER TABLE `db_table` ADD UNIQUE ( `runoob_title` )

添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

UNION

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据.

SELECT * FROM db_table UNION SELECT * FROM db_table_copy1;

UNION ALL

SELECT * FROM db_table UNION ALL SELECT * FROM db_table_copy1;`

MySQL INTERSECT模拟

两个表的交集

SELECT DISTINCT
    name
FROM
    db_test.db_table
WHERE
    name IN (SELECT 
            name
        FROM
            db_test.db_table_copy1);

MySQL except模拟

SELECT DISTINCT
    name
FROM
    db_test.db_table
WHERE
    name Not IN (SELECT 
            name
        FROM
            db_test.db_table_copy1);

修改字段的长度

alter table app_info modify column mappingPath varchar(1000)

Mysql的Having

1.Where 是一个约束声明，使用Where约束来自数据库的数据，Where是在结果返回之前起作用的，Where中不能使用聚合函数。

2.Having是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行。而where子句在查询过程中执行优先级高于聚合语句。

Mysql的if函数

IF函数根据条件的结果为true或false，返回第一个值，或第二个值

SELECT IF(500<1000, 5, 10);

SELECT IF(STRCMP("hello","bye") = 0, "YES", "NO");

mysql如何高可用

https://zhuanlan.zhihu.com/p/48793931

https://www.jianshu.com/p/cc6746ac4fc2

怎么查看MySQL语句有没有用到索引？

EXPLAIN

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

什么情况下不走索引（索引失效）？

1、使用!= 或者 <> 导致索引失效 2、类型不一致导致的索引失效 3、函数导致的索引失效 如：

SELECT * FROM user WHERE DATE(create_time) = '2020-09-03'; 如果使用函数在索引列，这是不走索引的。

4、运算符导致的索引失效 SELECT * FROM user WHERE age - 1 = 20; 如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

5、OR引起的索引失效 SELECT * FROM user WHERE name = '张三' OR height = '175'; OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

6、模糊搜索导致的索引失效 SELECT * FROM user WHERE name LIKE '%冰'; 当%放在匹配字段前是不走索引的，放在后面才会走索引。

7、NOT IN、NOT EXISTS导致索引失效

隔离级别

1.读未提交（Read uncommitted）：

这种事务隔离级别下，select语句不加锁。

此时，可能读取到不一致的数据，即“读脏 ”。这是并发最高，一致性最差的隔离级别。

2.读已提交（Read committed）：

可避免脏读的发生。

在互联网大数据量，高并发量的场景下，几乎 不会使用 上述两种隔离级别。

3.可重复读（Repeatable read）：

MySql默认隔离级别。

可避免 脏读 、不可重复读 的发生。

4.串行化（Serializable ）：

可避免 脏读、不可重复读、幻读 的发生。

ead uncommitted——不作任何隔离，具有脏读、不可重复读、幻读问题

read committed——可防止脏读，不能防止不可重复读和幻读问题

repeatable read——可以防止脏读、不可重复读，不能防止幻读问题（mysql默认是这个隔离级别）

serializable——数据库运行在串行化，上述问题都可以防止，只是性能非常低

如何解决幻读？

现在你知道了，产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

SELECT语句的处理过程

(5)SELECT DISTINCT

(1)FROM JOIN ON

(2)WHERE

(3)GROUP BY

(4)HAVING

(6)ORDER BY (7)LIMIT n, m

幻读

•一个事务多次查询整表数据，由于其他事务新增（删除）记录造成多次查询的记录条数不同（一个事务读取到另一个事务已经提交的数据）。

脏读

•一个事务读取到另一个事务未提交的数据

不可重复读

•一个事务读取到另一个事务已经提交的数据

慢查询性能问题

•索引没有设计好；•SQL 语句没写好；•MySQL 选错了索引;

查询慢查询

慢查询日志（slow log）打开，并且把 long_query_time 设置

MySQL是怎么保证数据不丢的？

https://time.geekbang.org/column/article/76161

参考

MySQL索引连环18问!

https://github.com/cosen1024/Java-Interview/blob/main/MySQL/MySQL%E7%B4%A2%E5%BC%95%E8%BF%9E%E7%8E%AF18%E9%97%AE%EF%BC%81.md

MySQL 实战 45 讲 https://time.geekbang.org/column/intro/100020801