MySQL模块面试题

1.SQL优化

（1）避免使用*

（2）合理创建索引

（3）尽量避免where子句中对索引字段进行null值判断，否则全表扫描。

（4）尽量避免在where子句中使用or来连接条件，也会进行全表扫描。用IN替换OR

（5）like时，不以%开头，否则全表扫描

（6）尽量避免在where子句中对索引字段进行表达式操作，否则全表扫描

（7）尽量避免在where子句中对索引字段进行函数操作，否则全表扫描

（8）复合索引，要遵循最左原则（只要最左边的在where中出现即可）

（9）左右外连接要遵循小表驱动大表

（10）尽量避免使用子查询。MySql的优化器对子查询的处理能力比较弱，可以改写成Inner Join，因为 Inner Join MySQL不需要在内存中创建临时表。

（11）大分页优化：如果数据量巨大，造成分页过多，导致越往后面翻页，SQL的耗时越长。可以先使用ID>上一页的最大ID进行查询，再使用LIMIT pageSize来优化。

select * from order limit 99,980,20. – 优化前

select * from order id>99,980 limit 20 – 优化后

（12）在使用group by的时候，尽量使用where替换having，having只会在检索出所有记录之后才会对结果集进行过滤，这个处理需要排序分组，如果能通过where子句提前过滤查询的数目，就可以减少这方面的开销。

2.如何判断是否用到索引

explain分析sql语句，通过key、key_len查看是否命中索引或索引失效。

3.常见的索引有哪些

主键索引、唯一索引、普通索引、复合索引

4.最左匹配(常见)和其原理(不常见)

概念：最左匹配就是只有包含索引键左边的查询才会走联合索引。

原理：mysql会对索引进行排序，联合索引也是。比如联合索引a,b；会先对a进行排序，如果a相等，再对b进行排序，如a=1 and b=2，此时a值确定b时相对有序的，所以会用到索引。如果a出现了排序(也就是遇到了范围查询如a>1 and b=2)，则无法保证b的顺序(1,3、2,1)，此时a字段可以匹配上索引，剩下的字段索引就失效了。

5.EXPLAIN的作用

explain可以分析sql性能的好坏和是否用到了索引。根据type字段的值，判断性能的好坏，要求是ref级别，至少达到range级别。

6.创建索引的原则

在经常需要搜索、排序、分组或联合操作的列上创建索引。

对于具有唯一性特征的列，如学号、身份证号等，创建唯一性索引可以快速定位到特定记录。

限制索引数目：过多的索引会占用额外的磁盘空间，并可能降低更新操作的速度。

尽量使用数量少的索引：如果索引的值很长，则占用的磁盘较大，查询速度会受到影响。

更新频繁的字段不适合创建索引，会导致索引树频繁的去维护其结构，从而导致性能下降。

7.一级索引和二级索引的区别

mysql的索引时B+树结构，其中一级索引又叫聚簇索引，其中非叶子节点存储的是索引，最底层的叶子节点存储的是索引和数据。
二级索引也叫做非聚簇索引，非聚簇索引的叶子节点包含的是主键的值而不是数据记录的地址。因此，使用非主键索引查询时需要先找到主键的值，然后再通过主键的值来查询数据记录，这个动作叫做回表。

8.分页公式

1.limit分页公式
（1）limit分页公式：curPage,是当前第几页；pageSize是一页多少条记录。

limit （curPage-1）*pageSize,pageSize

（2）使用方法：

select * from user limit （curPage-1）*pageSize,pageSize;

2.总页数公式
（1）总页数公式：totalRecord是总记录数；pageSize是一页分多少记录

int totalPageNum = (totalRecord+pageSie-1) / pageSize;

好处：
1.减轻数据库压力
2.降低内存消耗
3.支持高并发
在开发中，我们应该充分利用分页查询，尽可能减少一次查询数据的量，以达到优化系统性能的目的。

9.索引的作用？为什么？

● 索引（index）是帮助MySQL高效获取数据的数据结构

● 提高数据检索的效率，降低数据库的I/O成本（不需要全表扫描）

●通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗

10.索引失效的场景

组合索引没有遵循最左匹配。

模糊查询中%在左侧。

在索引的列上使用表达式。

在索引的列上使用函数。

在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。

在查询条件中使用OR连接多个条件会导致索引失效，除非OR连接的每个条件都加上索引。

11.金额用什么数据类型

一般使用分的话，mysql使用bigint，java使用Long。

使用元可以使用decimal，java使用BigDecimal.

12.int(n)n是什么

n只是显示宽度， 不表示存储数字长度的上限。

13.什么是回表

通过二级索引找到对应的主键值，到聚簇索引中查找整行数据，这个过程就是回表

14.数据库事务四大特性？隔离等级

• 原子性（Atomicity）:事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致的状态。
• 隔离性(Isolation)：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性(Durability)：事务一旦提交或回滚，他对数据库中的数据的改变就是永久的。

四种隔离级别，以及解决了什么问题

1.读未提交（Read uncommitted）：

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

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

2.读已提交（Read committed）：

可避免 脏读 的发生。

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

3.可重复读（Repeatable read）：

MySql默认隔离级别。

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

4.串行化（Serializable ）：

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

15.三大范式和反范式

第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

第二范式(2NF)：满足第一范式；且不存在部分依赖，即非主属性必须完全依赖于主属性。(主属性即主键；完全依赖是针对于联合主键的情况，非主键列不能只依赖于主键的一部分)

第三范式(3NF)：满足第二范式；且不存在传递依赖，即非主属性不能与非主属性之间有依赖关系，非主属性必须直接依赖于主属性，不能间接依赖主属性。（A -> B, B ->C, A -> C）

反范式： 反范式设计是一种与传统规范化设计相对的数据库设计方法，它允许在数据库中引入冗余数据以提高查询性能或简化查询操作。反范式设计的主要思想是通过增加冗余数据来消除关系型数据库中的连接操作，从而提高查询性能。

16.如何一次添加50万条数据

如果一次性添加50万条数据会导致mysql压力过大，从而有宕机的风险。可以采用分批添加的方案，比如使用定时任务，一次添加5000条数据。

17.常见的存储引擎

最常见的有InnoDB和MyISAM。

区别：

数据存储结构：

MyISAM的数据存储文件有三个：.frm（表结构定义）、.MYD（数据文件）、.MYI（索引文件）。

InnoDB数据存储文件有两个：.frm（表结构定义）和.ibd（数据和索引文件）。

事务支持：

MyISAM不支持事务，InnoDB支持事务处理。

锁机制：

MyISAM使用表级锁，InnoDB即支持行级锁（默认），也支持表锁。

外键支持：

MyISAM不支持外键约束，InnoDB支持外键约束。

全文索引支持：

MyISAM支持，InnoDB不支持。

18.utf-8和utf-8 mb4区别

UTF-8 是一种变长编码，用于表示 Unicode 字符集中的字符，最多可以表示 1,112,064 个字符。
UTF-8MB4 是对 UTF-8 的扩展，支持更广泛的字符集范围，可以表示 Unicode 字符集中的所有字符，包括辅助平面和 Emoji 表情等。
需要注意的是，UTF-8MB4 编码需要更多的存储空间和处理能力,在大多数情况下，使用 UTF-8 编码是常见且足够的。只有在需要存储或处理特殊字符集时，才需要考虑使用 UTF-8MB4 编码。