MySQL基础面试题

MySQL是如何处理查询请求的

mysql是一个数据库软件，采用了客户端与服务器端的应用模式。

客户端向服务端请求数据主要有这么几个步骤：

1. 客户端向服务器端发送连接请求，建立连接。mysql支持三种连接方式，分别是：
   • TCP/IP
   • 命名管道/共享内存
   • Unix域套接字文件
2. 服务器端处理客户端请求。
   1. 连接管理。
      • 对客户端的连接信息进行认证。
      • 如果认证成功，则为当前连接创建一个新的线程或者使用缓存中的线程，来处理客户端请求；如果认证失败，则拒绝连接。
   2. 解析与优化。
      • 查询缓存。如果缓存中存在完全相同的查询请求，且缓存未失效，则缓存命中，返回缓存中的查询结果；如果缓存未命中，则走后续流程。
      • 语法解析。对sql语句进行编译操作，并将需要查询的表数据加载到内存中。
      • 查询优化。查询优化器对查询语句作一些优化。
      • 生成执行计划。基于优化后的sql语句生成存储引擎的执行语句。
   3. 存储引擎。执行存储引擎语句，并返回执行结果。
3. 将执行结果返回给客户端。

MySQL中字符串是如何进行比较的

在计算机中只能存储二进制数据，而将一个字符映射成二进制数据的过程称为编码，将二进制数据映射成字符的过程称为解码，不同的字符集有不同的编解码规则，所以在数据库中存储一个字符串需要先确定它所属的字符集，而一个字符集可能拥有多个比较规则用于比较字符大小，所以mysql中比较字符串大小流程如下：

1. 确定字符串所属字符集和比较规则。
   • 服务器级别
   • 数据库级别
   • 表级别
   • 列级别
2. 根据比较规则将两字符串进行从左至右的逐字符大小比较。

MySQL的有哪些常见的存储引擎？它们有什么却别？

存储引擎	B-tree indexes	Backup/point-in-time recovery	Cluster database support	Clustered indexes	MVCC	Transactions	Locking granularity
MyISAM	Y	Y	N	N	N	N	Table
Memery	Y	Y	N	N	N	N	Table
InnoDB	Y	Y	N	Y	Y	Y	Row
Archive	N	Y	N	N	N	N	Row
NDB	N	Y	Y	N	N	Y	Row

说说InnoDB的行格式

我们平时是以记录的方式向表中插入数据的，记录在表中存放的方式也叫行格式或记录格式。InnoDB有四种行格式：

• Compact
• Redundant
• Dynamic
• Compressed

现以Compact为例说明行格式存储了哪些信息（图文参考《MySQL是怎样运行的：从根上理解MySQL》）：

• 额外信息。
  • 变长字段长度列表
  • NULL值列表
  • 记录头信息
    • delete_mask。标记该记录是否被删除。
    • min_rec_mask。B+树的每层非叶子节点中的最小记录都会添加该标记。
    • n_owned。表示当前记录拥有的记录数。
    • heap_no。表示当前记录在记录堆的位置信息。
    • record_type。表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录。
    • next_record。表示下一条记录的相对位置。

• 真实数据。
  • 隐藏列数据。
    • row_id。行id，唯一标识一条记录。如果表没有定义主键则定义一个Unique键作为主键，如果连Unique键都没有，InnoDB会为表默认添加一个名为row_id的隐藏列作为主键。
    • transaction_id。事务ID。
    • roll_pointer。回滚指针。
  • 自定义列数据。

MySQL有哪些索引类型

• 按功能分类
  • 普通索引
  • 唯一索引
  • 全文索引
  • 空间索引
• 是否建立在主键上
  • 主键索引
  • 非主键索引
• 是否包含完整数据信息
  • 聚簇索引
  • 非聚簇索引

InnoDB的聚簇索引和非聚簇所有有什么区别？

索引是数据库中用来提高数据查询效率的一种存储结构。非聚簇索引又称为普通二级索引。

• 相同点。
  • 它们都是指的InnoDB存储引擎中的同一类B+树索引结构。
• 不同点。
  • 聚簇索引是按主键值大小进行排序的B+树，而普通二级索引是按指定索引值进行排序的B+树。
  • 聚簇索引的叶子结点存储了完整的用户数据，而普通二级索引值存储了指定的索引列值和主键值，并没有完整的用户记录。
  • 普通二级索引需要用Index显示创建，而聚簇索引是InnoDB自动为表创建的。

什么是MySQL回表操作？

回表操作是MySQL在单表查询时，特定情况下才会执行的一种操作，即通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录的过程。

为什么互联网企业禁止使用存储过程

• 调试性差。设计复杂逻辑时，定位问题比较麻烦。
• 可移植性差。需要将数据从MySQL迁移到其他数据库时，涉及到独有特性表的存储过程需要重写。
• 可维护性差。表字段的变化，需要同步到存储过程；对执行计划并不是最优，优化时需要重写。
• 管理困难。

索引的优点与缺点

索引是数据库中用来提高数据查询效率的一种存储结构。InnoDB采用的是B+树的结构来实现索引和存储。

• 优点
  • 提升查询效率。通过B+树来存储数据，能大大减少数据检索时的磁盘OI的次数，提高数据查询效率。
  • 通过唯一索引能保证表中每一行数据的唯一性。
• （不合理使用的）缺点。
  • 索引会占据大量的存储空间，本质上是一种空间换时间的思想，在存储空间有限的场景下，索引可能不是最优解。
  • 在增删改时需要对索引结构进行维护，这涉及到页分裂操作、用户数据迁移等耗时操作，所以在对数据进行频繁增删的业务场景下，索引可能会降低数据操作效率，所以在添加索引时需要考虑数据修改和查询操作的性能平衡问题。
  • 冗余索引会对不但不会增加查询效率，还会增加数据修改时的执行效率。
  • 在列数据的基数较低时，创建索引可能并不会带来查询性能上的提升。

MySQL有哪几个事务隔离级别？默认的事务隔离级别是什么？

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	Possible	Possible	Possible
READ COMMITTED	Not Possible	Possible	Possible
REPEATABLE READ（MySQL默认事务隔离级别）	Not Possible	Not Possible	Possible
SERIALIZABLE	Not Possible	Not Possible	Not Possible

MySQL的可重复读事务隔离级别其实已经解决了幻读问题。

MySQLB+树索引查询数据的流程

• 聚簇索引查询。
  • 确定记录所在的页。
    • 根据索key值进行二分查找，找到记录所在的页。
  • 页内查找。
    • 在页内根据页目录进行二分查找找到记录所在的槽。
    • 最后根据槽定位记录所在的组，通过next_record指针遍历组内记录，找到查询的数据。
• 普通二级索引。
  • 确定记录所在页。
  • 页内查找到匹配的索引记录。
  • 获取到主键值，根据主键值进行回表查询（聚簇索引步骤）。

什么是MySQL索引的最左匹配原则？

指的查询条件只有匹配联合索引从最左边开始的子串字段才能使用当前联合索引进行查询优化，不然会造成索引失效情况。

原因是联合索引的索引结构内的页和记录都是基于联合索引中的字段进行顺序排序的，只有在排在前面的索引字段相等时才会按后面的字段进行排序。

例如name和age组成了联合索引，只有在name值相同时，才会按照age字段进行记录。

所以不是按最左匹配原则的查询条件不符合索引构建时页和记录的排序方式，会让当前索引失效。

MySQL的覆盖索引是什么？

只需要用到索引而不需要回表的查询方式称为覆盖索引。一般情况下当查询列表和查询条件中只有索引列时，此时的查询称为覆盖索引。

什么是MySQL的索引下推？

索引下推是一种数据库查询优化技术，指的是通过把索引过滤条件下推到存储引擎，来减少MySQL存储引擎访问基表的次数和MySQL服务层访问存储引擎的次数。

举例：加入现在有一张表user有id，name，age三个字段，有一个普通二级索引(name,age)。现有一条查询语句：

select id, name, age, address from user where name like 'li%' and age = 18;

id	name	age	address
1	lisan	15	**
2	lisi	16	**
3	liwu	18	**
4	zhangyi	18	**

当采用索引下推优化时，根据最左匹配原则，会在二级索引中匹配到三条1、2、3这3条记录，然后根据这三条记录中的主键id进行3次回表查询，最后再基于回表得到的三条记录进行age=18的条件过滤。

当采用索引下推优化时，根据最左匹配原则，依然会有1、2、3这三条记录，但是这是会将age=18条件过滤提前到回表之前，这样就能过滤后就只剩一条记录了，然后根据这一条记录进行回表操作并返回数据，这时回表次数就从3次降低到了1次，降低了查询的总时间，提高了查询效率。

MySQL查询优化器是如何优化查询的？

查询优化器主要有两种优化方式：

• 基于成本的优化。
  • 单表查询优化，有两种方式计算查询成本。
    • 直接计算成本。
      • 根据所有搜索条件，找出所有可能的索引。
      • 计算全表扫描的代价。
      • 计算使用不同索引执行查询的代价。
      • 对比各种执行方案的代价，并找出成本最低的那一个。
    • 基于索引统计数据的成本计算。
  • 连接查询优化。
    • 根据不同连接方案计算查询成本最低的那一个（连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本）。
• 基于规则的优化。会将糟糕的查询语句转换成高效执行的形式，这个过程称为查询重写。查询重写的方式有以下几种：
  • 条件化简。
  • 外连接消除。
  • 子查询优化。

MySQL的可重复读隔离级别解决了幻读没有？如何解决的？

MVCC

MySQL是如何解决脏读的？

MVCC

MySQL是如何解决不可重复读的？

MVCC

MySQL是如何实现事务的？

针对不同的事务隔离级别，实现的方式不同：

隔离级别	事务实现方式
READ UNCOMMITTED	直接读取记录的最新版本
READ COMMITTED	MVCC+锁
REPEATABLE READ（MySQL默认事务隔离级别）	MVCC+锁
SERIALIZABLE	MVCC+锁

MySQL的MVCC是什么？

MVCC又称多版本并发控制，是事务隔离级别的无锁实现方式。

在修改数据时维护旧数据。修改聚簇索引数据时会做两件事：

• 将当前事务的事务ID赋值给trx_id隐藏列。
• 将旧版本信息写入到undo日志中，并让最新记录的隐藏列roll_pointer指向旧版本信息。这样最新记录和它的undo日志就能形成一个版本链：

ReadView。ReadView解决的核心问题就是能判断版本链中那个版本是当前事务可见的。

ReadView的核心属性：

• m_ids。表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
• min_trx_id。表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。

• max_trx_id。表示生成ReadView时系统中应该分配给下一个事务的id值。
• creator_trx_id。表示生成该ReadView的事务的事务id。

有了这个ReadView，这样在访问某条记录时，只需要按照下面的步骤判断记录的某个版本是否可见：

• 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
• 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。（解决了脏读）

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上面的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。（解决了幻读）

UNREPEATABLE READ和REPEATABLE READ生成ReadView的时机不同：

• UNREPEATABLE READ会在每次读取数据前生成一个ReadView（造成不可重复读）。
• REPEATABLE READ仅在第一次读取数据时生成一个ReadView（解决不可重复读）。

MySQL有哪些锁类型？

MySQL中的锁可以从这几个方面进行划分。

• 按功能划分
  • 读锁
  • 写锁
• 按粒度划分
  • 表锁
  • 行锁
    • 记录锁（Record Locks）
    • 间隙锁（Gap Locks）
    • 临键锁（Next-key Locks）
    • 插入意向锁（Insert Intention Locks）
    • 隐式锁
• 按排他性
  • 共享锁
  • 独占锁

MySQL乐观锁和悲观锁有什么区别？

悲观锁逻辑悲观，默认数据会被其他线程修改，所以在做数据操作时会先尝试获取锁。MySQL的大部分锁机制都是属于悲观锁的范畴，例如表锁、行锁、读锁、写锁等。

乐观锁逻辑乐观，默认数据不会被其他线程修改，所以在做数据操作后才会去判断是否有人对数据进行了修改，可以根据版本号等方式进行实现。例如在列中加入版本号字段，没修改一次数据就让版本号递增，这样可以将数据修改完后的版本号和修改前的版本号进行比对，如果相同则表示没有其他线程修改数据，可以进行数据更新，如果不同则表示其他线程更新了当前记录，需要重新处理数据。

MySQL如何进行性能调优？

• 表结构和索引。
  • 分库分表。
  • 为字段选择适合的数据类型。
  • 为查询操作创建适当的索引。
  • 适当的反范式设计。
• SQL语句优化。
  • 避免使用Select *查询。
  • 尽量基于索引扫描。
• 参数调优。
• 硬件调整。

MySQL索引优化

• 只为用于搜索、排序或分组的列创建索引。
• 考虑列的基数。最好只为那些基数大的列创建索引，基数小的列创建索引的效果并不好。
• 索引列的类型尽可能小。原因：
  • 数据类型越小，在查询时进行的比较操作越快。
  • 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
• （建立索引时）索引字符串值的前缀。
• 让索引列在比较表达式中单独出现。不然会让索引失效。
• 主键插入顺序。
• 冗余和重复索引。

MySQL如何实现读写分离的？

MySQL读写分离机制依赖于binlog文件，任何数据修改操作都会保存到binglog中。

在读写分离的服务器集群中，主库会启动一个异步线程，会将binlog文件复制到从库，从库会根据binlog创建relaylog，并启动一个SQL线程执行relaylog中的数据修改操作。这样就将主库的数据同步到了从库中，实现了MySQL的读写分离。

• 优点。
  • 提高读写性能。
  • 提高了可用性。当主库宕机后，从库可以升级为主库代替主库。
• 缺点。当主库宕机时binlog来不及复制到从节点时，可能会造成数据丢失，导致主从数据不一致。

MySQL的分库分表策略有哪些？

MySQL分库分表目的有两个，一个是为了满足日益扩张的业务复杂度需求，另一个是为了实现性能提升。

垂直方向

垂直方向的分库分表操作是为了将各业务模块数据进行解耦，让数据系统更加清晰。

• 单库。当业务框架处于初期阶段，并没有那么多复杂场景时，表的数量和表中字段都不是很多，放在一个库中也能满足业务需求。
• 分表。随着业务扩展，字段不断增加，单表中的字段结构越来越复杂时，就可以进行分表，有几种分表策略。
  • 按业务场景进行划分。例如订单表可以按订单类型分为研发订单表和客户订单表，两者针对不同的业务场景。研发订单表示以测试为目的的测试单，可能有不需要走完完整工序流程、可以不计入报表统计数据等特性；而客户订单则必须记录每一道工序日志并进入统计数据，一定时间内验证是否以完成或关闭。
  • 按字段的主次关系分表。例如订单表中可能有订单编号、计划数量、实际数量、报废数量、不良数量、状态、订单合同信息、订单文件、订单负责人等字段，可以将订单基础信息拆成一个表，订单详情拆成另一个表。
• 分库。业务复杂到一定程度后，每一个业务模块都能单独作为一个领域存在，如仓储管理、生产管理、质检管理、报表管理、入库管理等，复杂场景下每一个管理模块都能创建一个库，这样的好处是使系统框架更加清晰，更方便定位问题，部分服务出错并不会影响其他模块的运行。
• 分库分表。有的业务场景需要既分库又分表，例如财务系统可以按年份进行分库，再在库中按月份进行分表，这样能保证每个库每个表的数据量不至于太大，还能提高数据的查询效率。

水平方向

水平方向的分库分表主要是为了提升系统性能。

• 单库。此时是项目初期，系统并发量低，针对单库的请求并不会有问题。
• 分库。不同的分库方案适用于不同的场景。
  • 读写分离。当读多写少的情况时，主从库是一种主流的解决方案，主要的做法是：基于原有库创建多个库，一个库作为主库负责处理写请求，其他库作为从库负责接收读请求，并在主库完成数据更新后进行数据同步。
  • 多库处理读写请求。当同时存在大量读写请求时，单个写库就处理不了了，这时可以将数据进行分段处理，为每一段数据创建一个数据库。
• 分表。分库是为了解决单库并发请求量大的问题，而分表则是为了解决单表存储量过大导致的单个请求耗时过长的问题。当单表数据量达到千万级别，索引一次数据将会非常耗时，所以我们可以将原表拆分为若干张相同的表，尽可能让单表存储的数据量少于千万。
• 分库分表。当并发量和存储数据都十分庞大的场景下，分库并分表就能同时降低每库的并发请求数，和每表的单次请求时间。

数据库设计三大范式

• 第一范式。
  • 表结构要有主键。
  • 强调表结构每列的原子性，即确保每个属性只代表一个含义，不可再分。
• 第二范式。
  • 符合第一范式的要求。
  • 表结构必须有主键。
  • 各数据列必须直接依赖于主键列。不允许存在部分依赖的非主键列。
• 第三范式。
  • 符合第二范式的要求。
  • 非主键列必须直接依赖于主键列，不能存在依赖传递。即不能存在类似非主键列A依赖于主键列B，主键列B又依赖于主键A。