Mysql面试题目

基础

1.什么是mysql

MySQL 是一个开源的关系型数据库，现在隶属于 Oracle 公司。是我们国内使用频率最高的一种数据库，我在本地安装的是最新的 8.3 版本。

怎么删除建立一张表？

使用DROP TABLE 来删除表

CREATE TABLE来创建表

创建表的时候可以使用PRIMARY KEY来创建主键

1
2
3
4
5
6
CREATE TABLE users (
    id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (id)
);

写一个升序/降序的sql语句

1
2
3
SELECT id, name, salary
FROM employees
ORDER BY salary DESC;
1
2
3
SELECT id, name, salary
FROM employees
ORDER BY salary DESC, name ASC;

排序的第一依据是salary 降序，第二一依据是name 升序

MYsql出现性能差的原因是什么？

可能是sql查询的时候使用了全表扫描，也可能是查询语句过于复杂

也有可能是单表数据量过大。

我们可以添加索引来解决大部分问题，对于一些热点数据，可以使用Redis缓存

2.表的连接

内连接：

返回两个表中有匹配关系的行，找出两张表的交集

1
2
3
SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

INNER JOIN orders ON users.id = orders.user_id;连接条件：

users.id = orders.user_id

外连接：

不仅返回两个表中匹配的行，还返回没有匹配的行，用 null 来填充。

想当与找并集

left join右表没匹配的null填充

right join左表没匹配的null填充

1
2
3
SELECT users.id, users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

交叉连接：

返回两张表的笛卡尔积，也就是将左表的每一行与右表的每一行进行组合，返回的行数是两张表行数的乘积。

3.数据库的三大范式

第一范式：确保表的每一列都是不可分割的基本数据单元，比如说用户地址，应该拆分成省、市、区、详细地址等 4 个字段。

第二范式：要求表中的每一列都和主键直接相关。比如在订单表中，商品名称、单位、商品价格等字段应该拆分到商品表中。

第三范式：非主键列应该只依赖于主键列。比如说在设计订单信息表的时候，可以把客户名称、所属公司、联系方式等信息拆分到客户信息表中，然后在订单信息表中用客户编号进行关联。

建表的原则：

考虑表是否符合数据库的三大范式，确保字段不可再分，消除非主键依赖，确保字段仅依赖于主键等。

然后在选择字段类型时，应该尽量选择合适的数据类型。

在字符集上，尽量选择 utf8mb4，这样不仅可以支持中文和英文，还可以支持表情符号等。

当数据量较大时，比如上千万行数据，需要考虑分表。比如订单表，可以采用水平分表的方式来分散单表存储压力。

4.varchar和char的区别

varchar是可变长度的字符类型，原则上最多可以容纳 65535 个字符，但考虑字符集，以及 MySQL 需要 1 到 2 个字节来表示字符串长度，所以实际上最大可以设置到 65533。

varch中空格也要占一个字节

char是固定长度的字符类型，当定义一个 CHAR(10) 字段时，不管实际存储的字符长度是多少，都只会占用 10 个字符的空间。如果插入的数据小于 10 个字符，剩余的部分会用空格填充。

5.blob和text有什么区别

blob用于存储二进制数据，如图片音频等等，但是实际应用中，我们会把这些文件存储到oss或者文件服务器上，数据库存储文件的url

text用于存储文本数据，如文章评论等。

6.DATETIME和TIMESTAMP有什么区别

DATETIME直接存储日期和时间的完整值，与时区无关。

TIMESTAMP存储的是 Unix 时间戳，1970-01-01 00:00:01 UTC 以来的秒数，受时区影响。

DATETIME 的默认值为 null，占用 8 个字节；TIMESTAMP 的默认值为当前时间——CURRENT_TIMESTAMP，占 4 个字节，实际开发中更常用，因为可以自动更新。

7.in和exists的区别

使用 IN 时，MySQL 会首先执行子查询，然后将子查询的结果集用于外部查询的条件。这意味着子查询的结果集需要全部加载到内存中。

EXISTS 会对外部查询的每一行，执行一次子查询。如果子查询返回任何行，则 EXISTS 条件为真。EXISTS 关注的是子查询是否返回行，而不是返回的具体值。

1
2
3
4
5
6
7
8
-- IN 的临时表可能成为性能瓶颈
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- EXISTS 可以利用关联索引
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o 
            WHERE o.user_id = u.id AND o.amount > 100);

In适用于子查询结果集较小的情况。如果子查询返回大量数据，IN 的性能可能会下降，因为它需要将整个结果集加载到内存。

EXISTS适用于子查询结果集可能很大的情况。由于 EXISTS 只需要判断子查询是否返回行，而不需要加载整个结果集，因此在某些情况下性能更好，特别是当子查询可以使用索引时。

NUll值陷？

IN：如果子查询的结果集中包含 NULL 值，可能会导致意外的结果。例如，WHERE column IN (subquery)，如果 subquery 返回 NULL，则 column IN (subquery) 永远不会为真，除非 column 本身也为 NULL。

EXISTS：对 NULL 值的处理更加直接。EXISTS 只是检查子查询是否返回行，不关心行的具体值，因此不受 NULL 值的影响。

8.记录货币

货币在数据库中 MySQL 常用 Decimal 和 Numeric 类型表示，这两种类型被 MySQL 实现为同样的类型。他们被用于保存与货币有关的数据。

salary DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而 2(scale)代表将被用于存储小数点后的位数。存储在 salary 列中的值的范围是从-9999999.99 到 9999999.99。

不使用 float 或者 double 的原因：因为 float 和 double 是以二进制存储的，所以有一定的误差。

会出现大问题的！

9.如何存储emoji

utf8 字符集仅支持最多 3 个字节的 UTF-8 字符，但是 emoji 表情（😊）是 4 个字节的 UTF-8 字符，所以在 MySQL 中存储 emoji 表情时，需要使用 utf8mb4 字符集。

1
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

10.drop,delete,turncate的区别

都表示删除，但是三者有一些差别：

区别 delete truncate drop
类型 属于 DML 属于 DDL 属于 DDL
回滚 可回滚 不可回滚 不可回滚
删除内容 表结构还在，删除表的全部或者一部分数据行 表结构还在，删除表中的所有数据 从数据库中删除表，所有数据行，索引和权限也会被删除
删除速度 删除速度慢，需要逐行删除 删除速度快 删除速度最快

因此，在不再需要一张表的时候，用 drop；在想删除部分数据行时候，用 delete；在保留表而删除所有数据的时候用 truncate。

11.UNION和UNION ALL的区别

  • 如果使用 UNION，会在表链接后筛选掉重复的记录行
  • 如果使用 UNION ALL，不会合并重复的记录行
  • 从效率上说，UNION ALL 要比 UNION 快很多，如果合并没有刻意要删除重复行，那么就使用 UNION All

12.count(*)，count(1)，count(列)，这三种有什么区别

COUNT(*) —— 统计总行数（包括 NULL）

会读取表中的所有行，但不访问具体字段的数据，只是计数。

在大多数数据库（如 MySQL、Oracle）中，数据库优化器会对其进行优化，性能非常好。

比较推荐

COUNT(1) —— 统计总行数，本质类似 COUNT(*)

数据库会把 1 作为一个常量值，对所有行都有效。

与 COUNT(*) 区别：

  • 理论上相同，因为 1 永远不为 NULL，相当于每行都有值；
  • 实际执行时性能几乎一致，有些数据库会将其优化为 COUNT(*)；
  • 有些人误以为 COUNT(1) 比 COUNT(*) 快，这在现代数据库中已经不成立。

COUNT(列) —— 统计指定列中非 NULL 的行数

关键区别：

  • 会忽略该列为 NULL 的记录；
  • 需要访问该列的值，性能可能稍低；
  • 可用于判断某一字段是否完整填充或做数据质量检查；

sql优化

1.慢sql问题怎么排查和解决

分析查询语句：使用EXPLAIN命令分析SQL执行计划，找出慢查询的原因，比如是否使用了全表扫描，是否存在索引未被利用的情况等，并根据相应情况对索引进行适当修改。

创建或优化索引：根据查询条件创建合适的索引，特别是经常用于WHERE子句的字段、Orderby 排序的字段、Join 连表查询的字典、 group by的字段，并且如果查询中经常涉及多个字段，考虑创建联合索引，使用联合索引要符合最左匹配原则，不然会索引失效

避免索引失效：比如不要用左模糊匹配、函数计算、表达式计算等等。

查询优化：避免使用SELECT ，只查询真正需要的列；使用覆盖索引，即索引包含所有查询的字段；联表查询最好要以小表驱动大表，并且*被驱动表的字段要有索引，当然最好通过冗余字段的设计，避免联表查询。

分页优化：针对 limit n,y 深分页的查询优化，可以把Limit查询转换成某个位置的查询：select * from tb_sku where id>20000 limit 10，该方案适用于主键自增的表。

读写分离：搭建主从架构， 利用数据库的读写分离，Web服务器在写数据的时候，访问主数据库（master），主数据库通过主从复制将数据更新同步到从数据库（slave），这样当Web服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的Web应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是一举两得。

优化数据库表：如果单表的数据超过了千万级别，考虑是否需要将大表拆分为小表，减轻单个表的查询压力。也可以将字段多的表分解成多个表，有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。

使用缓存技术：引入缓存层，如Redis，存储热点数据和频繁查询的结果，但是要考虑缓存一致性的问题，对于读请求会选择旁路缓存策略，对于写请求会选择先更新 db，再删除缓存的策略。

2.有sql索引优化的经验吗？

常见优化索引的方法：

  • 前缀索引优化：使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

  • 覆盖索引优化：覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

  • 主键索引最好是自增的：

  • 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。

  • 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

  • 防止索引失效：

    • 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
    • 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
    • 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
    • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

存储引擎

1.你了解mysql的哪些存储引擎，请详细的讲讲？

InnoDB：InnoDB是MySQL的默认存储引擎，具有ACID事务支持、行级锁、外键约束等特性。它适用于高并发的读写操作，支持较好的数据完整性和并发控制。

MyISAM：MyISAM是MySQL的另一种常见的存储引擎，具有较低的存储空间和内存消耗，适用于大量读操作的场景。然而，MyISAM不支持事务、行级锁和外键约束，因此在并发写入和数据完整性方面有一定的限制。

Memory：Memory引擎将数据存储在内存中，适用于对性能要求较高的读操作，但是在服务器重启或崩溃时数据会丢失。它不支持事务、行级锁和外键约束。