Mysql常问面试题 - 教程

Mysql常问面试题

• 1、锁
• • 1.1、表级锁
  • 1.2、行级锁
  • 1.3、乐观锁
  • 1.4、悲观锁
• 2、索引
• • 2.1、索引分类
  • • 2.1.1、聚簇索引和非聚簇索引
    • 2.1.2、回表
    • 2.1.3、什么是覆盖索引
    • 2.1.4、 索引创建原则有哪些
    • 2.1.5、 什么情况下索引会失效
    • 2.1.6、 复合索引-最左匹配原则

1、锁

按锁粒度划分：‌全局锁、表级锁、行级锁;
按模式分类为：乐观锁与悲观锁；

1.1、表级锁

开销小，加锁快
不会出现死锁
锁定粒度大，发生锁冲突的概率最高，并发度最低。
读写比例严重失衡，读远多于写的场景使用表级锁
MyISAM 存储引擎只支持表级锁

1. 表级锁的类型
（1）表共享读锁（Table Read Lock） 多个会话可以同时获取表的读锁 持有读锁的会话只能读表，不能写表 其他会话不能获取写锁，直到所有读锁释放
（2）表独占写锁（Table Write Lock） 只有一个会话可以获取表的写锁 持有写锁的会话可以读写表 其他会话不能获取读锁和写锁，直到写锁释放

-- 会话1：获取表的读锁
LOCK TABLES users READ;
-- 可以读取数据
SELECT * FROM users WHERE id = 1;
-- 不能写入数据，会报错
UPDATE users SET name = 'test' WHERE id = 1;
-- 会话2：尝试获取写锁会被阻塞，直到会话1释放锁
LOCK TABLES users WRITE;
-- 释放锁
UNLOCK TABLES;

2、 如何加表锁
执行select前,会自动给涉及的所有表加 读
执行更新（update,delete,insert)会自动给涉及到的表加 写
不需要用户直接显式用lock table命令
对于给MyISAM显式加锁，一般是为了在一定程度上模拟事务操作，实现对某一个时间点多个表一致性读取

1.2、行级锁

开销小，加锁快
开销大，加锁慢
会出现死锁
锁定粒度小，发生锁冲突的概率最低，并发度最高。
InnoDB 支持行级锁，这也是其广泛应用的重要原因。

1. 行级锁的类型
（1）共享锁（S 锁，Shared Locks）
允许事务读取一行数据
多个事务可以同时对同一行加 S 锁
加了 S 锁的行，不能被加 X 锁，直到所有 S 锁释放
（2）排他锁（X 锁，Exclusive Locks）
允许事务更新或删除一行数据
同一行只能有一个 X 锁
加了 X 锁的行，不能被加 S 锁或 X 锁，直到 X 锁释放

2. 行级锁示例:
在 SELECT … FOR UPDATE 时加排他锁
在 SELECT … LOCK IN SHARE MODE 时加共享锁

-- 会话1：获取行的共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 会话2：可以获取同一行的共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 会话2：尝试更新会被阻塞，因为会话1持有S锁
UPDATE users SET name = 'test' WHERE id = 1;
-- 会话1：释放锁（提交事务）
COMMIT;
-- 会话2：此时更新操作会执行

-- 会话1：获取行的排他锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 会话2：尝试获取共享锁会被阻塞
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- 会话2：尝试获取排他锁会被阻塞
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 会话1：释放锁
COMMIT;
-- 会话2：此时查询会执行

3.值得注意的是：
3.1、行级锁只在事务中有效，也就是说，只有在一个事务开始(BEGIN)后并在事务提交(COMMIT)或回滚(ROLLBACK)之前，才能对数据行进行锁定。如果在非事务环境中执行SQL语句，那么InnoDB会在语句执行结束后立即释放所有的锁。
3.2、MySQL InnoDB默认行级锁，行级锁都是基于索引的,若一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁把整张表锁住
3.3、对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及及数据集加排他锁(X)，对于普通SELECT语句，InnoDB不会自动加任何锁，可以手动加锁。

行锁有死锁的风险：
死锁:当两个或更多的事务相互等待对方释放资源时，就会发生死锁。例如，事务1锁定了行A并试图锁定行B，同时事务2锁定了行B并试图锁定行A，这就形成了死锁。MySQL会检测到死锁并终止其中一个事务，但这仍可能导致性能问题和事务失败。

1.3、乐观锁

乐观锁(Optimistic Locking)是一种在数据库操作中用于处理并发问题的技术。它的基本思想是假设在多个事务同时访问同一条数据时，冲突发生的概率较低，因此在操作数据时不会立即进行锁定，而是在提交数据更改时检查是否有其他事务修改了这条数据。如果没有，就提交更改，否则就回滚事务。

在MySQL中，乐观锁并没有内置的实现，但是可以通过一些编程技巧来实现。一种常见的实现方式是使用版本号(或时间戳)字段。每当一条记录被修改时，就增加版本号(或更新时间戳)。在更新记录时，先检查版本号(或时间戳)是否和读取记录时的版本号(或时间戳)一致，如果一致则执行更新并增加版本号(或更新时间戳)，否则就拒绝更新。这样就可以保证只有当记录没有被其他事务修改时，当前事务的更改才会被提交。

乐观锁的优点在于，由于大部分时间都不需要锁定，所以在冲突较少的情况下可以获得较高的并发性能。然而，如果冲突较多，那么乐观锁可能会导致大量的事务回滚，从而影响性能。因此，选择使用乐观锁还是其他锁定技术，需要根据实际的并发情况和性能需求来决定。

乐观锁(Optimistic Locking)是一种对并发控制的策略，适用于以下的应用场景:
低冲突环境:在多数情况下，数据并发修改的冲突较低，即同一时间内，同一条数据不会被多个事务同时修改。
读多写少的场景:在读操作远多于写操作的情况下，乐观锁可以避免由于频繁的读操作导致的不必要的锁定开销。
短事务操作:如果数据库的事务都是简短并且快速完成的，那么使用乐观锁可以减少因为等待锁而导致的时间消耗。
分布式系统:在分布式系统中，由于网络延迟等原因，事务冲突的可能性较低，因此乐观锁是一个合适的选择。
需要注意的是，如果事务冲突的可能性较高，或者需要长时间锁定某个资源，那么使用乐观锁可能会导致大量的事务冲突和回滚，这种情况下，悲观锁或者其他并发控制技术可能会是更好的选择。

SELECT id, name, quantity,version FROM Products WHERE id = 1;
--oldVersion是上面查出来的版本号
UPDATE Products SET name = 'NewName', quantity = 10，version = version +1
WHERE id = 1 AND version = oldVersion;

1.4、悲观锁

悲观锁(PessimisticLocking)是一种并发控制的方法，基于一个假设:认为数据在并发处理过程中很可能会出现冲突。因此，为了保证数据的完整性和一致性，每次在读写数据时都会先加锁，这样可以避免其他事务进行并发的读写操作。
是否使用悲观锁需要根据应用的具体需求和场景来决定。在冲突较少，但需要保证数据完整性和一致性的情况下，可以考虑使用悲观锁。

悲观锁适用哪些场景
悲观锁的策略是假设数据在并发处理过程中会发生冲突，因此在进行任何读写操作前，都会预先加锁。这种策略在某些特定的应用场景下是比较有优势的，主要包括:
写操作较多的场景:如果一个系统中的写操作比读操作多，或者说写操作占主导，那么悲观锁可能是一个比较好的选择。因为在这种场景下，数据冲突的可能性相对较高，预先加锁可以确保数据的完整性和一致性。
并发冲突高的场景:在并发冲突较高的场景，使用悲观锁可以避免重复尝试操作，提高系统的整体效率。
业务需要强一致性的场景:在一些需要保证数据强一致性的业务场景下，例如银行转账等金融业务，通常会选择使用悲观锁，以确保在任何情况下数据的一致性和准确性。
但是值得注意的是，悲观锁也可能引入死锁等问题，也可能因为锁定过程中事务长时间等待而影响性能。因此，选择和使用悲观锁都需要根据具体业务场景和需求来进行。

悲观锁的缺点：
性能开销:在悲观锁机制下，锁定资源的操作会影响到系统性能。因为每次对数据的读写都需要进行加锁和解锁的操作，这会增加系统的开销，特别是在高并发的环境下，锁的竞争更是会严重影响到系统性能。
并发度降低:由于悲观锁在操作数据前就会加锁，这就导致了在同一时间，只有一个事务能操作数据，其他事务只能等待，大大降低了系
统的并发度。
死锁:悲观锁在并发事务中可能导致死锁的情况发生。当两个或者更多的事务互相等待对方释放锁时，就可能发生死锁。虽然数据库系统通常能够检测并解决死锁，但这会导致事务回滚，增加了系统的开销。
锁超时:如果一个事务长时间持有锁而不释放，可能导致其他等待锁的事务超时。这不仅可能导致等待的事务失败，还可能影响到整个系统的稳定性。
因此，虽然悲观锁能有效地防止数据冲突，但由于其在并发处理中的限制，以及可能引发的问题，如死锁、锁竞争和锁超时，我们需要根据具体的应用场景和需求，来权衡是否使用悲观锁。

2、索引

2.1、索引分类

2.1.1、聚簇索引和非聚簇索引

在innodb 中，mysql主要是通过索引这种数据结构增加查询效率，索引主要由聚簇索引和非聚簇索引所构成。聚簇索引主要是通过显式id的来存储，如果表中有id，并且给这个id加一个键，那么这个id就作为主键(聚簇)索引；如果没有定义一个id，那么就会去表中找是否存在 唯一键，如果有唯一键那么这个唯一索引作为主键索引，如果也没有唯一索键，那么就会通过一个隐式的id来存储。

非聚簇索引： 非聚簇索引就是以非主键创建的索引，在叶子节点存储的是表主键和索引列。

2.1.2、回表

回表问题 举例说明：比如说给库存表中的 sku_code商品码加一个索引，那么就需要先从这个 sku_code 的这列所对应的 b+ 树中先找到他的值，于此同时需要返回他的主键值，通过他的主键值再去主键(聚簇)索引对应的那棵b+树中找到对应的信息，因为表中所有的信息都存储在聚簇索引的叶子节点上，这个就称为回表，需要通过两次的IO进行获取数据。

2.1.3、什么是覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列 在该索引中全部能够找到。
索引覆盖可以避免回表，减少IO次数。

2.1.4、 索引创建原则有哪些

1）针对于数据量较大，且查询比较频繁的表建立索引。 单表超过10万数据（增加用户体验）

2）针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3）尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4）如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5）尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6）要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7）如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

2.1.5、 什么情况下索引会失效

1） > < 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。mysql 会一直向右匹配直到遇到索引搜索键使用>、<就停止匹配。一旦权重最高的索引搜索键使用>、<范围查询，那么其它>、<搜索键都无法用作索引。即索引最多使用一个>、<的范围列，因此如果查询条件中有两个>、<范围列则无法全用到索引。

2） like %xx 模糊查询

当使用LIKE操作符进行模糊查询，并且搜索键值以通配符%开头（如：like ‘%abc’），则索引失效，直接全表扫描。这是因为以%开头的模式匹配意味着匹配的字符串可以在任何位置，这使得索引无法有效定位数据
若只是以%结尾，索引不会失效

3）对索引列进行运算

当我们在查询条件中对索引列进行函数或表达式计算，会导致索引失效而进行全表扫描。

4）or 条件索引问题

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

当or连接的条件，左右两侧字段都有索引时，索引才会生效

5）数据类型不一致，隐式转换导致索引失效

当列是字符串类型，传入条件 必须用引号引起来，不然报错或索引失效

6）!= 问题

普通索引使用 !=索引失效，主键索引没影响。

where语句中索引列使用了负向查询，可能会导致索引失效。负向查询包括：NOT、!=、<>、NOT IN、NOT LIKE等。

7）联合索引 违背 最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

例如，user表有一个联合索引 (profession, age, status)。对于最左前缀法则指的是，查询时，最左边的列 也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。

注意：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

8）order by问题

order by 对主键索引排序会用到索引，其他的索引失效

2.1.6、 复合索引-最左匹配原则

在2个以上的列上创建的索引称为复合索引。
1.必须包含最左列： 查询条件中必须包含组合索引定义中的最左边第一列 (name)，否则索引对该查询无效。

2.连续前缀匹配： 索引可以被用于匹配查询条件中索引列的连续前缀 ((name), (name,age), (name, age, city)）。

3.不能跳过中间列： 如果查询条件没有包含索引定义中某个连续前缀的中间列（例如，只有 name和 city，跳过了 age），那么只有跳过的列之前的列（name）能用于索引查找，跳过的列之后的列（city）不能用于查找，只能作为扫描后的过滤条件。

-- 创建组合索引索引
CREATE INDEX idx_user_name_age_city ON user(name, age, city);
-- 充分利用索引的查询
SELECT * FROM user WHERE name = 'Tom';
SELECT * FROM user WHERE name = 'Tom' AND age = 25 ;
SELECT * FROM user WHERE name = 'Tom' AND age = 25 AND city = 'Beijing';
-- 跳过了索引中定义的age列，只有name是利用了索引，city这里只是作为查询条件
SELECT * FROM user WHERE name = 'Tom' AND city = 'Beijing';

合理使用复合索引替代多个单列索引

-- 单独创建两个索引
CREATE INDEX idx_user_age ON user(age);
CREATE INDEX idx_user_city ON user(city);
-- MySQL通常只会选择一个索引
SELECT * FROM user WHERE age = 25 AND city = 'Beijing';