MYSQL 常见的面试题，100道经典面试题及答案

全部面试题答案，更新日期：12月30日，直接下载吧！

下载链接：高清500+份面试题资料及电子书，累计 10000+ 页大厂面试题 PDF

MySQL

题1：MySQL 假设一天几万以上的增量，预计运维三年，怎么优化？

1）设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。

2）选择合适的表字段数据类型和存储引擎，适当的添加索引。

3）mysql库主从读写分离。

4）找规律分表，减少单表中的数据量提高查询速度。

5）添加缓存机制，比如memcached，apc等。

6）不经常改动的页面，生成静态页面。

7）书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

题2：业务实践中如何优化 MySQL？

1、SQL语句及索引的优化

2、数据库表结构的优化

3、系统配置的优化

4、硬件的优化

题3：MySQL 调优数据库都有哪些方法？

1、选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM。

2、使用连接(JOIN)来代替子查询。

3、适用联合(UNION)来代替手动创建的临时表。

4、事务处理。

5、锁定表、优化事务处理。

6、适用外键，优化锁定表。

7、建立索引。

8、优化查询语句。

题4：如何通俗地理解三个范式？

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

范式化设计优缺点

优点：可以尽量得减少数据冗余，使得更新快，体积小。

缺点：对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化。

反范式化

优点：可以减少表得关联，可以更好得进行索引优化。

缺点：数据冗余以及数据异常，数据得修改需要更多的成本。

题5：说说对 SQL 语句优化有哪些方法？

1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。

3） 避免在索引列上使用计算。

4）避免在索引列上使用IS NULL和IS NOT NULL。

5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

题6：MySQL 中 having 和 where 有什么区别？

having子句和where都是设定条件筛选的语句。

其中having与where的区别主要有以下几方面：

1、having是在分组后对数据进行过滤，而where是在分组前对数据进行过滤；

2、having后面可以使用聚合函数，而where后面不可以使用聚合。

3、在查询过程中执行顺序：from>where>group（含聚合）>having>order>select。

4、聚合语句（sum、min、max、avg、count）要比having子句优先执行，而where子句在查询过程中执行优先级别优先于聚合语句（sum、min、max、avg、count）。

where子句

select sum(num) as rmb from order where id>10

只有先查询出id大于10的记录才能进行聚合语句

having子句

select reports,count(*)  from employees group by reports having count(*) > 4

having条件表达示为聚合语句，having子句查询过程执行优先级别低于聚合语句。如果把上面的having换成where则会出错，统计分组数据时用到聚合语句。

对分组数据再次判断时要用having。如果不用这些关系就不存在使用having。直接使用where就行了。having是用来弥补where在分组数据判断时的不足。所以where执行优先级别要快于聚合语句。

聚合函数

例如SUM、COUNT、MAX、AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

having子句可以直接筛选成组后的各组数据，也可以在聚合后对组记录进行筛选，而where子句在聚合前先筛选记录，也就是说作用在group by子句和having子句前。

题7：MySQL 中如何有效的删除一个大表？

1、复制表结构，切勿复制数据。

2、创建硬链接减少mysql DDL时间，加快锁释放。

如果不知道的存储位置，可使用show variables like "datadir";命令查看数据存储位置。

ln jingxuan.frm jingxuan.frm.bak
ln jingxuan.ibd jingxuan.ibd.bak

3、删除指定的表

DROP TABLE "表格名"; 2G的数据量，删除只用了大概3秒左右。

4、修改表名删除的表名

将复制的表，表名修改为删除的表名即可。

5、删除物理文件，使用truncate分段删除文件，避免IO hang。

切记物理文件不可直接删除，直接操作会导致磁盘IO和CPU利用率升高，使用truncate来进行删除操作。

truncate -s 2G jingxuan.ibd.bak

题8：MySQL 中如何去掉重复数据记录？

MySQL数据库表中可能存在重复数据，有些情况是允许重复数据存在的，有些情况是不允许的，这个时候需要查找并删除这些重复数据，以下是具体的处理方法。

方法一：防止表中出现重复数据

当表中未添加数据时，可以在表中设置指定的字段为PRIMARY KEY（主键）或者UNIQUE（唯一）索引来保证数据的唯一性。

例如在用户信息表中员工号no不允许重复，需设置员工号no为主键，且默认值不能为NULL。

CREATE TABLE user 
( 
no CHAR(12) NOT NULL, 
name CHAR(20), 
sex CHAR(10), 
PRIMARY KEY (no) 
); 

方法二：过滤删除重复值

对于表中原有数据去掉重复的，需要经过重复数据查找、过滤以及删除等步骤。

1. 统计重复数据

mysql> SELECT COUNT(*) as sex,no 
-> FROM user GROUP BY no 
-> HAVING repetitions > 1; 

以上查询语句将返回student表中重复的记录数。

2. 过滤重复数据

如果读取不重复的数据可以在SELECT语句中使用DISTINCT关键字来过滤重复数据。

mysql> SELECT DISTINCT no FROM user; 

也可以使用GROUP BY来读取表中不重复的数据。

mysql> SELECT no FROM user GROUP BY (no); 

3. 删除重复数据

删除数据表中重复数据，可以使用以下SQL语句：

mysql> CREATE TABLE tmp SELECT no, name, sex FROM user GROUP BY (no, sex); 
mysql> DROP TABLE user; 
mysql> ALTER TABLE tmp RENAME TO student; 

也可以在数据表中添加INDEX（索引）和 PRIMAY KEY（主键）来删除表中的重复记录，方法如下：

mysql> ALTER IGNORE TABLE user 
-> ADD PRIMARY KEY (no); 

题9：SQL 连接查询时 on 和 where 有什么区别？

SQL中的连接查询分为3种，cross join（交叉连接）、inner join（内连接）和outer join（外连接）。

在cross join和inner join中，筛选条件放在on后面还是where后面是没区别的，在编写这两种连接查询的时候，只用on不使用where也没有什么问题。

on筛选和where筛选的差别只是针对outer join，也就是平时最常使用的left join和right join。

以左连接为例：

1、用on的时候，只对右表做筛选条件，而左表不受控制

2、用where的时候，对临时表的组合后的结果进行筛选，所以对左右表都是有作用的。

总的来说outer join的执行过程分为4步：

1、先对两个表执行交叉连接(笛卡尔积)

2、应用on筛选器

3、添加外部行

4、应用where筛选器

题10：MySQL 中如何获取当前日期？

SELECT CURRENT_DATE();