MySQL面试题

目录

1.SQL基础

1.1.NOSQL和SQL的区别？

1.2.数据库三大范式是什么？

1.3.MySQL 怎么连表查询？

1.4.MySQL如何避免重复插入数据？

1.5.CHAR 和 VARCHAR有什么区别？

1.6.Text数据类型可以无限大吗？

1.7.说一下外键约束

1.8.MySQL的关键字in和exist

1.9.mysql中的一些基本函数，你知道哪些？

1.10.SQL查询语句的执行顺序是怎么样的？

1.11.sql题：给学生表、课程成绩表，求不存在01课程但存在02课程的学生的成绩

1.12.给定一个学生表 student_score（stu_id，subject_id，score），查询总分排名在5-10名的学生id及对应的总分

2.事务

2.1.事务简介

2.2.事务操作

2.3.控制事务

2.3.1.方式一

2.3.2.方式二

2.4.事务的四大特性,如何实现？

2.5.并发事务问题

2.6.事务隔离级别

2.7.哪些场景不适合脏读，举个例子？

2.8.mysql的是怎么解决并发问题的？

2.9.可重复读隔离级别下，A事务提交的数据，在B事务能看见吗？

2.10.Mysql 设置了可重读隔离级后，怎么保证不发生幻读？

2.11.串行化隔离级别是通过什么实现的？

2.12.介绍MVCC实现原理

2.13.一条update是不是原子性的？为什么？

2.14.滥用事务，或者一个事务里有特别多sql的弊端？

3.存储引擎

3.1.存储引擎特点

3.1.1.InnoDB

3.1.2 Innodb逻辑存储结构

 3.1.3.Innodb架构图

3.1.4.事务原理

3.1.5 MVCC

3.1.5.1.基本概念

3.1.5.2.MVCC-隐藏字段 

3.1.5.3.MVCC-undolog

3.1.5.4.MVCC-ReadView

3.1.5.5.MVCC原理

3.2.MyISAM

4.索引 

4.1.索引是什么？有什么好处？

4.2.讲讲索引的分类是什么？

 4.3.索引失效的情况有哪些？

4.4.覆盖索引

4.5.MySQL聚簇索引和非聚簇索引的区别是什么？

4.6.如果聚簇索引的数据更新，它的存储要不要变化？

4.7.什么字段适合当做主键？

4.8.性别字段能加索引么？为啥？

4.9.表中十个字段，你主键用自增ID还是UUID，为什么？

4.10.什么自增ID更快一些，UUID不快吗，它在B+树里面存储是有序的吗?

4.11.Mysql中的索引是怎么实现的 ？

4.12.查询数据时，到了B+树的叶子节点，之后的查找数据是如何做？

4.13.B+树的特性是什么？

4.14.说说B+树和B树的区别

4.15.B+树的叶子节点链表是单向还是双向？

4.16.为什么 MysSQL 不用 跳表？

4.17.创建联合索引时需要注意什么？

4.18.什么情况下会回表查询？

4.19.如果一个列即使单列索引，又是联合索引，单独查它的话先走哪个？

4.20.索引已经建好了，那我再插入一条数据，索引会有哪些变化？

4.21.索引字段是不是建的越多越好？

4.22.如果有一个字段是status值为0或者1，适合建索引吗？

4.23.索引的优缺点？

4.24.怎么决定建立哪些索引?

4.25.索引优化详细讲讲

5.锁

5.1讲一下mysql里有哪些锁？

5.2.数据库的表锁和行锁有什么作用？

5.3.MySQL两个线程的update语句同时处理一条数据，会不会有阻塞？

5.4.两条update语句处理一张表的不同的主键范围的记录，一个<10，一个>15，会不会遇到阻塞？底层是为什么的？

5.5.如果2个范围不是主键或索引？还会阻塞吗？

6.日志

6.1.日志文件是分成了哪几种？

6.2.讲一下binlog

6.3.UndoLog日志的作用是什么？

6.4.有了undolog为啥还需要redolog呢？

6.5.redo log怎么保证持久性的？

6.6.能不能只用binlog不用relo log？

6.7.update语句的具体执行过程是怎样的？

7. 性能调优

7.1.mysql的explain有什么作用？

7.2.给你张表，发现查询速度很慢，你有那些解决方案??

7.3.如果Explain用到的索引不正确的话，有什么办法干预吗？

8.架构

8.1.MySQL主从复制了解吗？？

8.2.主从延迟都有什么处理方法？

1.SQL基础

1.1.NOSQL和SQL的区别？

QL数据库，指关系型数据库 - 主要代表：SQL Server，Oracle，，MySQL(开源)，PostgreSQL(开源)。

关系型数据库存储结构化数据。这些数据逻辑上以行列二维表的形式存在，每一列代表数据的一种属性，每一行代表一个数据实体。

 NoSQL指非关系型数据库 ，主要代表：MongoDB，Redis。NoSQL数据库逻辑上提供了不同于二维表的存储方式，存储方式可以是JSON文档、哈希表或者其他方式。

选择 SQL vs NoSQL，考虑以下因素。

ACID vs BASE

 关系型数据库支持 ACID 即原子性，一致性，隔离性和持续性。相对而言，NoSQL 采用更宽松的模型BASE ， 即基本可用，软状态和最终一致性。从实用的角度出发，我们需要考虑对于面对的应用场景，ACID 是否是必须的。比如银行应用就必须保证ACID，否则一笔钱可能被使用两次；又比如社交软件不必保证 ACID，因为一条状态的更新对于所有用户读取先后时间有数秒不同并不影响使用。

对于需要保证 ACID 的应用，我们可以优先考虑 SQL。反之则可以优先考虑 NoSQL。

扩展性对比

 NoSQL数据之间无关系，这样就非常容易扩展，也无形之间，在架构的层面上带来了可扩展的能力。比如 redis 自带主从复制模式、哨兵模式、切片集群模式。

相反关系型数据库的数据之间存在关联性，水平扩展较难 ，需要解决跨服务器 JOIN，分布式事务等问题。

1.2.数据库三大范式是什么？

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。举例说明：

 在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：

 可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）；

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关

举例说明：

 在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，这样就不满足第二范式的要求，调整如下，需分成两个表：

 第三范式（3NF）：在2NF基础上，任何非主属性 不依赖于其它非主属性（在2NF基础上消除传递依赖）第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

举例说明：

上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，而不是主键“学号”，所以需做如下调整：

 这样以来，就满足了第三范式的要求。

1.3.MySQL 怎么连表查询？

数据库有以下几种联表查询类型：

1. 内连接 (INNER JOIN)
2. 左外连接 (LEFT JOIN)
3. 右外连接 (RIGHT JOIN)
4. 全外连接 (FULL JOIN)

 1. 内连接 (INNER JOIN)

内连接返回两个表中有匹配关系的行。示例:

这个查询返回每个员工及其所在的部门名称。

2. 左外连接 (LEFT JOIN)

左外连接返回左表中的所有行，即使在右表中没有匹配的行。未匹配的右表列会包含NULL。示例：

 这个查询返回所有员工及其部门名称，包括那些没有分配部门的员工。

3. 右外连接 (RIGHT JOIN)

右外连接返回右表中的所有行，即使左表中没有匹配的行。未匹配的左表列会包含NULL。示例:

这个查询返回所有部门及其员工，包括那些没有分配员工的部门。 

4. 全外连接 (FULL JOIN)

全外连接返回两个表中所有行，包括非匹配行，在MySQL中，FULL JOIN 需要使用 UNION 来实现，因为MySQL 不直接支持 FULL JOIN。示例:

 这个查询返回所有员工和所有部门，包括没有匹配行的记录。

1.4.MySQL如何避免重复插入数据？

方式一：使用UNIQUE约束

在表的相关列上添加unique约束，确保每个值在该列中唯一。例如：

如果尝试插入重复的email，MySQL会返回错误。 

方式二：使用INSERT ... ON DUPLICATE KEY UPDATE

这种语句允许在插入记录时处理重复键的情况。如果插入的记录与现有记录冲突，可以选择更新现有记录：

 方式三：使用INSERT IGNORE： 该语句会在插入记录时忽略那些因重复键而导致的插入错误。例如：

 如果email已经存在，这条插入语句将被忽略而不会返回错误。

选择哪种方法取决于具体的需求：

• 如果需要保证全局唯一性，使用UNIQUE约束是最佳做法。
• 如果需要插入和更新结合可以使用ON DUPLICATE KEY UPDATE。
• 对于快速忽略重复插入，INSERT IGNORE是合适的选择

1.5.CHAR 和 VARCHAR有什么区别？

• CHAR是固定长度的字符串类型，定义时需要指定固定长度，存储时会在末尾补足空格。CHAR适合存储长度固定的数据，如固定长度的代码、状态等，存储空间固定，对于短字符串效率较高。
• ARCHAR是可变长度的字符串类型，定义时需要指定最大长度，实际存储时根据实际长度占用存储空间。VARCHAR适合存储长度可变的数据，如用户输入的文本、备注等，节约存储空间。

1.6.Text数据类型可以无限大吗？

MySQL 3 种text类型的最大长度如下：

• TEXT：65,535 bytes ~64kb
• MEDIUMTEXT：16,777,215 bytes ~16Mb
• LONGTEXT：4,294,967,295 bytes ~4Gb

1.7.说一下外键约束

外键约束的作用是维护表与表之间的关系，确保数据的完整性和一致性。让我们举一个简单的例子：假设你有两个表，一个是学生表，另一个是课程表，这两个表之间有一个关系，即一个学生可以选修多门课程，而一门课程也可以被多个学生选修。在这种情况下，我们可以在学生表中定义一个指向课程表的外键，如下所示：

这里，students表中的course_id字段是一个外键， 它指向courses表中的id字段。这个外键约束确保了每个学生所选的课程在courses表中都存在，从而维护了数据的完整性和一致性。

如果没有定义外键约束，那么就有可能出现学生选了不存在的课程或者删除了一个课程而忘记从学生表中删除选修该课程的学生的情况，这会破坏数据的完整性和一致性。

再举一个例子：有两张表，员工表与部门表。

 现在员工表中有五个员工都归属于1号部门(学工部)，当删除了1号部门后，数据变为：

 1号部门被删除了，但是依然还有5个员工是属于1号部门的。 此时：就出现数据的不完整、不一致了。

 1.8.MySQL的关键字in和exist

在MySQL中，IN 和 EXISTS 都是用来处理子查询的关键词，但它们在功能、性能和使用场景上有各自的特点和区别。

IN关键字

 IN 用于检查左边的表达式是否存在于右边的列表或子查询的结果集中。如果存在，则IN 返回TRUE，否则返回FALSE。语法结构：

 EXISTS关键字

 EXISTS 用于判断子查询是否至少能返回一行数据。它不关心子查询返回什么数据，只关心是否有结果。如果子查询有结果，则EXISTS 返回TRUE，否则返回FALSE。

语法结构：

 区别与选择：

• 性能差异：在很多情况下，EXISTS 的性能优于 IN，特别是当子查询的表很大时。这是因为EXISTS 一旦找到匹配项就会立即停止查询，而IN可能会扫描整个子查询结果集。
• 使用场景：如果子查询结果集较小且不频繁变动，IN 可能更直观易懂。而当子查询涉及外部查询的每一行判断，并且子查询的效率较高时，EXISTS 更为合适。
• NULL值处理：IN 能够正确处理子查询中包含NULL值的情况，而EXISTS 不受子查询结果中NULL值的影响，因为它关注的是行的存在性，而不是具体值。

1.9.mysql中的一些基本函数，你知道哪些？

一、字符串函数

 CONCAT(str1, str2, ...)：连接多个字符串，返回一个合并后的字符串。

 LENGTH(str)：返回字符串的长度（字符数）。

 SUBSTRING(str, pos, len)：从指定位置开始，截取指定长度的子字符串。

 REPLACE(str, from_str, to_str)：将字符串中的某部分替换为另一个字符串。

 二、数值函数

 ABS(num)：返回数字的绝对值。

 POWER(num, exponent)：返回指定数字的指定幂次方。

 三、日期和时间函数

 NOW()：返回当前日期和时间。

 CURDATE()：返回当前日期。

 四、聚合函数

 COUNT(column)：计算指定列中的非NULL值的个数。

SUM(column)：计算指定列的总和。

 AVG(column)：计算指定列的平均值。

MAX(column)：返回指定列的最大值。 

 MIN(column)：返回指定列的最小值。

 1.10.SQL查询语句的执行顺序是怎么样的？

 所有的查询语句都是从FROM开始执行，在执行过程中，每个步骤都会生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入，最后一个步骤产生的虚拟表即为输出结果。

例子：假设现在有两张表，员工表和部门表

employees 表：

| id | name   | department_id |
|----|--------|---------------|
| 1  | Alice  | 1             |
| 2  | Bob    | 2             |
| 3  | Charlie| 1             |
| 4  | David  | 3             |

 departments 表：

| id | department_name |
|----|-----------------|
| 1  | HR              |
| 2  | IT              |
| 3  | Marketing       |

假设我们要查询所有部门中员工的名字，并按部门名称排序，只查询属于部门 HR 或 IT 的员工。可以使用以下 SQL 查询：

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.department_name IN ('HR', 'IT')
ORDER BY d.department_name;

这个查询的执行顺序：

1. FROM：首先从 employees 表和 departments 表中读取数据。
2. JOIN：对 employees 和 departments 表进行连接，条件是 e.department_id = d.id。
3. ON：连接条件 e.department_id = d.id 被应用。
4. WHERE：筛选出部门名称为 HR 或 IT 的记录。
5. SELECT：选择要返回的列：e.name 和 d.department_name。
6. ORDER BY：按 department_name 对结果进行排序。

结果：

| name   | department_name |
|--------|-----------------|
| Alice  | HR              |
| Charlie| HR              |
| Bob    | IT              |

【注】

where与having区别（面试题）

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

• 判断条件不同：where不能对聚合函数进行判断，而having可以。

 1.11.sql题：给学生表、课程成绩表，求不存在01课程但存在02课程的学生的成绩

可以使用SQL的子查询和LEFT JOIN或者EXISTS关键字来实现

假设我们有以下两张表：

1. Student 表，其中包含学生的sid（学生编号）和其他相关信息。
2. Score 表，其中包含sid（学生编号），cid（课程编号）和score（分数）。

方法1：使用LEFT JOIN 和 IS NULL

 方法2：使用NOT EXISTS

1.12.给定一个学生表 student_score（stu_id，subject_id，score），查询总分排名在5-10名的学生id及对应的总分

可以使用以下 SQL 查询来检索总分排名在 5 到 10 名的学生 ID 及对应的总分。其中我们先计算每个学生的总分，然后为其分配一个排名，最后检索排名在 5 到 10 之间的记录。

解释：

1. 子查询 StudentTotalScores 中，我们通过对 student_score表中的 stu_id 分组来计算每个学生的总分。
2. 子查询 RankedStudents 中，我们使用 RANK() 函数为每个学生分配一个排名，按总分从高到低排序。
3. 最后，我们在主查询中选择排名在 5 到 10 之间的学生。

另：

RANK() 的特性：

1. 排名重复：对于有相同值的行，RANK() 会给它们分配相同的排名。
2. 跳过排名：如果有多个行排名相同，则会跳过后续排名。例如，如果两个值并列为第 1 名，则下一个排名将是第 3 名，而不是第 2 名。

示例：

假设有一个成绩表 students，内容如下：

 使用 RANK() 计算排名：

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

结果：

解释：

• Alice 和 Charlie 都得了 90 分，所以它们并列第 1 名，因此 RANK() 为它们分配了相同的排名 1。
• 然后，RANK() 会跳过排名 2，给 Bob 分配了排名 3，因为有两个并列的第一名。
• 接着，David 和 Eve 分别排在第 4 名和第 5 名。

使用 PARTITION BY 进行分组排名：

如果你希望对不同的分组分别进行排名，可以使用 PARTITION BY 子句。假设成绩表 students 增加了一列 class 表示班级，数据如下：

我们可以按班级分别为学生排名：

SELECT name, score, class, RANK() OVER (PARTITION BY class ORDER BY score DESC) AS rank
FROM students;

结果：

2.事务

2.1.事务简介

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐 式的提交事务。

2.2.事务操作

数据准备：

drop table if exists account;create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余额'
) comment '账户表';insert into account(name, money) VALUES ('张三',2000), ('李四',2000);

未控制事务：

1）测试正常情况

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。

2).测试异常情况

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了....
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

2.3.控制事务

2.3.1.方式一

1).查看/设置事务提交方式

SELECT @@autocommit ;
SET @@autocommit = 0 ;

COMMIT;

ROLLBACK;

2.3.2.方式二

START TRANSACTION 或 BEGIN ; 

2.5.并发事务问题

1). 赃读：一个事务读到另外一个事务还没有提交的数据

解释：比如说事务A有三个操作，事务A 1.先进行查询 2.再更新，这时候事务A还没提交，事务B来查询id为1的数据，此时B读取到了更新后ID为1的数据，这就称为脏读。

2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

事务A两次读取同一条记录，但是读取到的数据却是不一样的。

解释：事务A，先查询id为1的数据，数据库中没有此数据，接着事务B执行了插入操作，并提交了事务，此时数据库中就有了id为1的数据，此时又轮到事务A了，既然事务A第一次 没查到数据，那他试图进行插入操作，发现插入不了（因为数据库里面已经有了id为1的数据），插入不了，那它再查询，发现已经有数据了。

2.6.事务隔离级别

注意：事务隔离级别越高，数据越安全，但是性能越低。

2.7.哪些场景不适合脏读，举个例子？

脏读是指一个事务在读取到另一个事务未提交的数据时发生。脏读可能会导致不一致的数据被读取，并可能引起问题。以下是一些不适合脏读的场景：

• 银行系统：在银行系统中，如果一个账户的余额正在被调整但尚未提交，另一个事务读取了这个临时的余额，可能会导致客户看到不正确的余额。
• 库存管理系统：在一个库存管理系统中，如果一个商品的数量正在被更新但尚未提交，另一个事务读取了这个临时的数量，可能会导致库存管理错误。
• 在线订单系统：在一个在线订单系统中，如果一个订单正在被修改但尚未提交，另一个事务读取了这个临时的订单状态，可能导致订单状态显示错误，客户收到不准确的信息。

在以上这些场景中，脏读可能导致严重的问题，因此应该避免发生脏读，保证数据的一致性和准确性.

2.8.mysql的是怎么解决并发问题的？

• 锁机制：Mysql提供了多种锁机制来保证数据的一致性，包括行级锁、表级锁、页级锁等。通过锁机制，可以在读写操作时对数据进行加锁，确保同时只有一个操作能够访问或修改数据。
• 事务隔离级别：Mysql提供了多种事务隔离级别，包括读未提交、读已提交、可重复读和串行化。通过设置合适的事务隔离级别，可以在多个事务并发执行时，控制事务之间的隔离程度，以避免数据不一致的问题。
• MVCC（多版本并发控制）：Mysql使用MVCC来管理并发访问，它通过在数据库中保存不同版本的数据来实现不同事务之间的隔离。在读取数据时，Mysql会根据事务的隔离级别来选择合适的数据版本，从而保证数据的一致性。

2.9.可重复读隔离级别下，A事务提交的数据，在B事务能看见吗？

可重复读隔离级是由 MVCC（多版本并发控制）实现的，实现的方式是开始事务后（执行 begin 语句后），在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的。

2.10.Mysql 设置了可重读隔离级后，怎么保证不发生幻读？

尽量在开启事务之后，马上执行 select ... for update 这类锁定读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录，就避免了幻读的问题。

2.11.串行化隔离级别是通过什么实现的？

是通过行级锁来实现的，序列化隔离级别下，普通的 select 查询是会对记录加 S 型的 next-key 锁，其他事务就没没办法对这些已经加锁的记录进行增删改操作了，从而避免了脏读、不可重复读和幻读现象。  

2.12.介绍MVCC实现原理

MVCC允许多个事务同时读取同一行数据，而不会彼此阻塞，每个事务看到的数据版本是该事务开始时的数据版本。这意味着，如果其他事务在此期间修改了数据，正在运行的事务仍然看到的是它开始时的数据状态，从而实现了非阻塞读操作。

对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。

• 「读提交」隔离级别是在「每个select语句执行前」都会重新生成一个 Read View；
• 「可重复读」隔离级别是执行第一条select时，生成一个 Read View，然后整个事务期间都在用这个Read View。

Read View 有四个重要的字段：

• m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
• min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是m_ids 的最小值。
• max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的id 值，也就是全局事务中最大的事务 id 值 + 1；
• creator_trx_id ：指的是创建该 Read View 的事务的事务 id。

对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：

• trx_id，当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
• roll_pointer，每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

在创建 Read View 后，我们可以将记录中的 trx_id 划分这三种情况：

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

• 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
• 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 ReadView 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
• View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。需要判断 trx_id 是否在 m_ids 列表中：
• 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
• 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为就叫 MVCC（多版本并发控制）。

2.13.一条update是不是原子性的？为什么？

是原子性，主要通过锁+undolog 日志保证原子性的

• 执行 update 的时候，会加行级别锁，保证了一个事务更新一条记录的时候，不会被其他事务干扰
• 事务执行过程中，会生成 undolog，如果事务执行失败，就可以通过 undolog 日志进行回滚。

2.14.滥用事务，或者一个事务里有特别多sql的弊端？

事务的资源在事务提交之后才会释放的，比如存储资源、锁。

• 如果一个事务特别多 sql，锁定的数据太多，容易造成大量的死锁和锁超时。
• 回滚记录会占用大量存储空间，事务回滚时间长，在MySQL (opens new window)中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值，sql 越多，所需要保存的回滚数据就越多。
• 执行时间长，容易造成主从延迟，主库上必须等事务执行完成才会写入binlog，再传给备库。所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟。

3.存储引擎

3.1.存储引擎特点

3.1.1.InnoDB

show variables like 'innodb_file_per_table';

可以看到里面有很多的 ibd 文件，每一个 ibd 文件就对应一张表，比如：我们有一张表 account ，就

有这样的一个 account.ibd 文件，而在这个 ibd 文件中不仅存放表结构、数据，还会存放该表对应的

索引信息。

3.1.2 Innodb逻辑存储结构

 3.1.3.Innodb架构图

 磁盘架构图略~~~~

3.1.4.事务原理

下面解释一下redo log是怎么保证持久性的：当客户端发起事务操作，在这次事务操作中包含了多条update和delete语句，当要进行update更新时，首先要去操作缓冲区， 在缓冲区当中去查找有无我们所更新的这一块的数据，如果没有，她会通过后台线程，把数据从磁盘当中读取出来，然后再缓存在缓冲区当中，接下来就可执行更新和删除操作，直接去操作缓冲区的数据就可以啦，然后缓冲区当中的数据就放生了变更，但是磁盘中的数据没有变更，这个时候，数据页称为脏页，我们会在一定的时机把脏页的数据通过后台线程刷新到磁盘当中，这样就可以保持缓冲区的数据与磁盘中的数据保持一致。但是，假如说，脏页的数据在刷新到磁盘的时候出错了，那么此时内存当中的数据没有刷新到磁盘当中，事务都已经提交了，也告诉用户事务提交成功了，但是最终在脏页刷新的时候失败了，这个时候，持久性就没有得到保障，为了解决这个问题，redo log 就出现了，当对缓冲区的数据进行增删改后，他会把增删改的数据记录在redo log buffer当中，在redo log buffer当中会记录数据页的物理变化，当在事务提交的时候，它会把redo log buffer当中的数据页变化直接刷新到磁盘当中，持久化的保存在磁盘文件当中，在脏页刷新的时候，如果出错了，可以通过redo log 进行恢复。

再解释一个问题：为什么每一次提交的时候，要把redo log直接刷新到磁盘当中，而不是直接将buffer pool当中变更的数据页直接刷新到磁盘当中？？两者之间是有区别的。

直接把buffer pool当中变更的数据页直接刷新到磁盘，存在严重的性能问题，因为在事务当中通常会操作很多条记录，这些记录都是随机的去操作数据页的，这个时候就会涉及到大量的随机磁盘io,性能太低。如果用了redo log，在事务的提交过程中，不会把脏页直接刷新到磁盘，先把redo log异步刷新到磁盘当中，由于它是log日志文件，日志文件都是追加的，它是顺序磁盘IO，性能高于随机磁盘io,这种机制叫做WAL（write ahead logging）。

3.1.5 MVCC

3.1.5.1.基本概念

3.1.5.2.MVCC-隐藏字段 

 3.1.5.3.MVCC-undolog

4.索引 

4.1.索引是什么？有什么好处？

索引类似于书籍的目录，可以减少扫描的数据量，提高查询效率。

• 如果查询的时候，没有用到索引就会全表扫描，这时候查询的时间复杂度是O（n）
• 如果用到了索引，那么查询的时候，可以基于二分查找算法，通过索引快速定位到目标数据， mysql索引的数据结构一般是 b+树，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d个。

4.2.讲讲索引的分类是什么？

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

按数据结构分类

 从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。每一种存储引擎支持的索引类型不一定相同。

 InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型。

B-Tree

知识小贴士: 树的度数指的是一个节点的子节点个数。

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据。

B+Tree

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

需要注意的是：MySQL索引数据结构对经典的B+tree进行了优化，在原来的基础上，增加了一个指向相邻叶子结点的链表指针，也即原来是单链表，现在变成双向循环链表了，提高了区间访问的性能。

面试题：为什么InnoDB存储引擎选择使用B+tree索引结构?

A. 相对于二叉树，层级更少，搜索效率高；

B. 对于 B-tree ，无论是叶子节点还是非叶子节点，都会保存数据， 这样导致一页（16K）中存储 的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度， 导致性能降低；

C. 相对 Hash 索引， B+tree 支持范围匹配及排序操作；

按物理存储分类

 从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

这两个区别：

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree的叶子节点里；
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

聚集索引选取规则：

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

回表查询： 这种先到二级索引中查找数据，找到主键值 ，然后再到聚集索引中根据主键值，获取 数据的方式，就称之为回表查询

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

思考题：

以下两条SQL语句，那个执行效率高? 为什么?

• A. select * from user where id = 10 ;
• B. select * from user where name = 'Arm' ;

备注 : id 为主键， name 字段创建的有索引；

 解答：

A 语句的执行性能要高于 B 语句。 因为A 语句直接走聚集索引，直接返回数据。 而 B 语句需要先查询 name 字段的二级索引，然 后再查询聚集索引，也就是需要进行回表查询。

思考题：

InnoDB主键索引的B+tree高度为多高呢?

假设 :

一行数据大小为 1k ，一页中可以存储 16 行这样的数据。 InnoDB 的指针占用 6 个字节的空

间，主键即使为 bigint ，占用字节数为 8 。

高度为 2 ：

n * 8 + (n + 1) * 6 = 16*1024 , 算出 n 约为 1170

1171* 16 = 18736

也就是说，如果树的高度为 2 ，则可以存储 18000 多条记录。

高度为 3 ：

1171 * 1171 * 16 = 21939856

也就是说，如果树的高度为 3 ，则可以存储 2200w 左右的记录

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

• 主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

在创建表时，创建主键索引的方式如下：

•  唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

在创建表时，创建唯一索引的方式如下：

建表后，如果要创建唯一索引，可以使用这面这条命令：

•  普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。

建表后，如果要创建普通索引，可以使用这面这条命令：

•  前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

建表后，如果要创建前缀索引，可以使用这面这条命令：

 按字段个数分类

 从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

通过将多个字段组合成一个索引，该索引就被称为联合索引。通过将多个字段组合成一个索引，该索引就被称为联合索引。创建联合索引的方式如下：

create unique index idx_user_phone_name on tb_user(phone,name);

可以看到，联合索引的非叶子节点用两个字段的值作为 B+Tree 的 key 值。当在联合索引查询数据时，先按 phone字段比较，在 name相同的情况下再按 name 字段比较。

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引， 而非单列索引。

 最左前缀法则

 上述的SQL查询时，存在profession字段，最左边的列是存在的，索引满足最左前缀法则的基本条 件。但是查询时，跳过了age这个列，所以后面的列索引是不会使用的，也就是索引部分生效。

思考题：

当执行 SQL 语句 : explain select * from tb_user where age = 31 and

status = '0' and profession = ' 软件工程 ' ； 时，是否满足最左前缀法则，走不走

上述的联合索引，索引长度？

可以看到，是完全满足最左前缀法则的，索引长度 54，联合索引是生效的。

注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是

第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

 4.3.索引失效的情况有哪些？

联合索引中， 出现范围查询(>,<)，范围查询右侧的列索引失效。 

当范围查询使用 > 或 < 时，走联合索引了，但是索引的长度为 49 ，就说明范围查询右边的 status字

段是没有走索引的。

单列索引中，不要在索引列上进行运算操作， 索引将失效

 在tb_user表中，除了前面介绍的联合索引之外，还有一个索引，是phone字段的单列索引

 A. 当根据phone字段进行等值匹配查询时, 索引生效。

 B. 当根据phone字段进行函数运算操作之后，索引失效。

 字符串类型字段使用时，不加引号，索引将失效。

 经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字 前面加了%，索引将会失效。

 由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。

4.4.覆盖索引

接下来，我们来看一组 SQL 的执行计划，看看执行计划的差别，然后再来具体做一个解析。

 上述这几条SQL的执行结果为:

 因为，在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段 profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主 键id。 所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引 直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表 查询（除非是根据主键查询，此时只会扫描聚集索引）。

 C. 执行SQL：selet id,name from tb_user where name = 'Arm';

 思考题：

一张表 , 有四个字段 (id, username, password, status), 由于数据量大 , 需要对 以下SQL 语句进行优化 , 该如何进行才是最优方案？

select id,username,password from tb_user where username = 'itcast';

答案 : 针对于 username, password 建立联合索引 , sql 为 : create index idx_user_name_pass on tb_user(username,password);

这样可以避免上述的SQL语句，在查询的过程中，出现回表查询

4.5.MySQL聚簇索引和非聚簇索引的区别是什么？

• 数据存储：在聚簇索引中，数据行按照索引键值的顺序存储，也就是说，索引的叶子节点包含了实际的数据行。这意味着索引结构本身就是数据的物理存储结构。非聚簇索引的叶子节点不包含完整的数据行，而是包含指向数据行的指针或主键值。数据行本身存储在聚簇索引中。
• 索引与数据关系：由于数据与索引紧密相连，当通过聚簇索引查找数据时，可以直接从索引中获得数据行，而不需要额外的步骤去查找数据所在的位置。当通过非聚簇索引查找数据时，首先在非聚簇索引中找到对应的主键值，然后通过这个主键值回溯到聚簇索引中查找实际的数据行，这个过程称为“回表”。
• 唯一性：聚簇索引通常是基于主键构建的，因此每个表只能有一个聚簇索引，因为数据只能有一种物理排序方式。一个表可以有多个非聚簇索引，因为它们不直接影响数据的物理存储位置。
• 效率：对于范围查询和排序查询，聚簇索引通常更有效率，因为它避免了额外的寻址开销。非聚簇索引在使用覆盖索引进行查询时效率更高，因为它不需要读取完整的数据行。

4.6.如果聚簇索引的数据更新，它的存储要不要变化？

• 如果更新的数据是非索引数据，也就是普通的用户记录，那么存储结构是不会发生变化
• 如果更新的数据是非索引数据，也就是普通的用户记录，那么存储结构是不会发生变化

4.7.什么字段适合当做主键？

• 字段具有唯一性，且不能为空的特性
• 字段最好的是有递增的趋势的，如果字段的值是随机无序的，可能会引发页分裂的问题，造型性能影响。
• 不建议用业务数据作为主键，比如会员卡号、订单号、学生号之类的，因为我们无法预测未来会不会因为业务需要，而出现业务字段重复或者重用的情况。
• 通常情况下会用自增字段来做主键，对于单机系统来说是没问题的。但是，如果有多台服务器，各自都可以录入数据，那就不一定适用了。因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题，这时候就需要考虑分布式 id 的方案了。

4.8.性别字段能加索引么？为啥？

不建议针对性别字段加索引。实际上与索引创建规则之一区分度有关，性别字段假设有100w数据，50w男、50w女，区别度几乎等于 0,区分度的计算方式 ：select count(DISTINCT sex)/count(*) from sys_user

实际上对于性别字段不适合创建索引，是因为select * 操作，还得进行50w次回表操作,根据主键从聚簇索引中找到其他字段 ，这一部分开销从上面的测试来说还是比较大的，所以从性能角度来看不建议性别字段加索引，加上索引并不是索引失效，而是回表操作使得变慢的。 既然走索引的查询的成本比全表扫描高，优化器就会选择全表扫描的方向进行查询，这时候建立的性别字段索引就没有启到加快查询的作用，反而还因为创建了索引占用了空间。

4.9.表中十个字段，你主键用自增ID还是UUID，为什么？

用的是自增 id。因为 uuid 相对顺序的自增 id 来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以innodb 无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题:

• 写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb 在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机 IO。
• 因为写入是乱序的，innodb 不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，影响性能。
• 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。

结论：使用 InnoDB 应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行。

4.10.什么自增ID更快一些，UUID不快吗，它在B+树里面存储是有序的吗?

自增的主键的值是顺序的，所以 Innodb 把每一条记录都存储在一条记录的后面，所以自增 id 更快的原因：

• 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费。
• 新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
• 减少了页分裂和碎片的产生

但是 UUID 不是递增的，MySQL 中索引的数据结构是 B+Tree，这种数据结构的特点是索引树上的节点的数据是有序的，而如果使用 UUID 作为主键，那么每次插入数据时，因为无法保证每次产生的 UUID 有序，所以就会出现新的 UUID 需要插入到索引树的中间去，这样可能会频繁地导致页分裂，使性能下降。

而且，UUID 太占用内存。每个 UUID 由 36 个字符组成，在字符串进行比较时，需要从前往后比较，字符串越长，性能越差。另外字符串越长，占用的内存越大，由于页的大小是固定的，这样一个页上能存放的关键字数量就会越少，这样最终就会导致索引树的高度越大，在索引搜索的时候，发生的磁盘 IO 次数越多，性能越差。

4.11.Mysql中的索引是怎么实现的 ？

MySQL InnoDB 引擎是用了B+树作为了索引的数据结构。B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息并且每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表。主键索引的 B+Tree 如图所示：

 B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，这意味着从千万级的表查询目标数据最多需要3-4 次磁盘 I/O，所以B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次。

4.12.查询数据时，到了B+树的叶子节点，之后的查找数据是如何做？

数据页中的记录按照「主键」顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此，数据页中有一个页目录，起到记录的索引作用，就像我们书那样，针对书中内容的每个章节设立了一个目录，想看某个章节的时候，可以查看目录，快速找到对应的章节的页数，而数据页中的页目录就是为了能快速找到记录。那 InnoDB 是如何给记录创建页目录的呢？???

页目录与记录的关系如下图：

页目录创建的过程如下：

1. 将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
2. 每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
3. 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。以上面那张图举个例子，5 个槽的编号分别为0，1，2，3，4，我想查找主键为 11 的用户记录：

• 先二分得出槽中间位是 (0+4)/2=2  ，2号槽里最大的记录为 8。因为 11 > 8，所以需要从 2 号槽后继续搜索记录；
• 再使用二分搜索出 2 号和 4 槽的中间位是 (2+4)/2= 3，3 号槽里最大的记录为 12。因为 11 < 12，所以主键为 11 的记录在 3 号槽里；
• 再从 3 号槽指向的主键值为 9 记录开始向下搜索 2 次，定位到主键为 11 的记录，取出该条记录的信息即为我们想要查找的内容。

4.13.B+树的特性是什么？

• 所有叶子节点都在同一层：这是B+树的一个重要特性，确保了所有数据项的检索都具有相同的I/O延迟，提高了搜索效率。每个叶子节点都包含指向相邻叶子节点的指针，形成一个链表，由于叶子节点之间的链接，B+树非常适合进行范围查询和排序扫描。可以沿着叶子节点的链表顺序访问数据，而无需进行多次随机访问。
• 非叶子节点存储键值：非叶子节点仅存储键值和指向子节点的指针，不包含数据记录。这些键值用于指导搜索路径，帮助快速定位到正确的叶子节点。并且，由于非叶子节点只存放键值，当数据量比较大时，相对于B树，B+树的层高更少，查找效率也就更高。
• 叶子节点存储数据记录：与B树不同，B+树的叶子节点存储实际的数据记录或指向数据记录的指针。这意味着每次搜索都会到达叶子节点，才能找到所需数据。
• 自平衡：B+树在插入、删除和更新操作后会自动重新平衡，确保树的高度保持相对稳定，从而保持良好的搜索性能。每个节点最多可以有M个子节点，最少可以有ceil(M/2)个子节点（除了根节点），这里的M是树的阶数。

4.14.说说B+树和B树的区别

• 在B+树中，数据都存储在叶子节点上，而非叶子节点只存储索引信息；而B树的非叶子节点既存储索引信息也存储部分数据。
• B+树的叶子节点使用链表相连，便于范围查询和顺序访问；B树的叶子节点没有链表连接。
• B+树的查找性能更稳定，每次查找都需要查找到叶子节点；而B树的查找可能会在非叶子节点找到数据，性能相对不稳定。

4.15.B+树的叶子节点链表是单向还是双向？

双向的，为了实现倒序遍历或者排序。

Innodb 使用的 B+ 树有一些特别的点，比如：

• B+ 树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
• B+ 树节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16KB。

Innodb 根据索引类型不同，分为聚集和二级索引。他们区别在于，聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。

因为表的数据都是存放在聚集索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚集索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个，而二级索引可以创建多个。

4.16.为什么 MysSQL 不用 跳表？

B+树的高度在3层时存储的数据可能已达千万级别，但对于跳表而言同样去维护千万的数据量那么所造成的跳表层数过高而导致的磁盘io次数增多，也就是使用B+树在存储同样的数据下磁盘io次数更少。

4.17.创建联合索引时需要注意什么？

建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL使用到。

区分度就是某个字段 column 不同值的个数「除以」表的总行数，计算公式如下：

 比如，性别的区分度就很小，不适合建立索引或不适合排在联合索引列的靠前的位置，而 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。

因为如果索引的区分度很小，假设字段的值分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是"30%"）很高的时候，它一般会忽略索引，进行全表扫描。

联合索引ABC，现在有个执行语句是A = XXX and C < XXX，索引怎么走?？

根据最左匹配原则，A可以走联合索引，C不会走联合索引，但是C可以走索引下推

联合索引(a,b,c) ，查询条件 where b > xxx and a = x 会生效吗？？？

索引会生效，a 和 b 字段都能利用联合索引，符合联合索引最左匹配原则。

联合索引 (a, b，c)，where条件是 a=2 and c = 1，能用到联合索引吗？

会用到联合索引，但是只有 a 才能走索引，c 无法走索引，因为不符合最左匹配原则。虽然 c 无法走索引， 但是 c 字段在 5.6 版本之后，会有索引下推的优化，能减少回表查询的次数。

4.18.什么情况下会回表查询？

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

4.19.如果一个列即使单列索引，又是联合索引，单独查它的话先走哪个？

mysql 优化器会分析每个索引的查询成本，然后选择成本最低的方案来执行 sql。如果单列索引是 a，联合索引是（a ，b），那么针对下面这个查询：

优化器会选择联合索引，因为查询成本更低，查询也不需要回表，直接索引覆盖了。

4.20.索引已经建好了，那我再插入一条数据，索引会有哪些变化？

插入新数据可能导致B+树结构的调整和索引信息的更新，以保持B+树的平衡性和正确性，这些变化通常由数据库系统自动处理，确保数据的一致性和索引的有效性。

如果插入的数据导致叶子节点已满，可能会触发叶子节点的分裂操作，以保持B+树的平衡性。

4.21.索引字段是不是建的越多越好？

不是，建的的越多会占用越多的空间，而且在写入频繁的场景下，对于B+树的维护所付出的性能消耗也会越大

4.22.如果有一个字段是status值为0或者1，适合建索引吗？

不适合，区分度低的字段不适合建立索引。

4.23.索引的优缺点？

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

• 需要占用物理空间，数量越大，占用空间越大；
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；
• 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

4.24.怎么决定建立哪些索引?

什么时候适用索引？

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

•  字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，B+ 树为了维护索引有序性，会耗费大量的时间。

4.25.索引优化详细讲讲

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

1. 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高。
2. 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂，页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

• 防止索引失效

5.锁

5.1讲一下mysql里有哪些锁？

在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类。

全局锁：通过flush tables with read lock 语句会将整个数据库就处于只读状态了，这时其他线程执行以下操作，增删改或者表结构修改都会阻塞。全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

表级锁：MySQL 里面表级别的锁有这几种：

表锁：通过lock tables 语句可以对表加表锁，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

• 表共享读锁（read lock）
• 表独占写锁（write lock）

语法：

加锁： lock tables 表名 ... read/write 。 释放锁：unlock tables / 客户端断开连接 。

特点 :

A. 读锁

 B. 写锁

元数据锁：当我们对数据库表进行操作时，会自动给这个表加上 MDL，对一张表进行 CRUD 操作时，加的是  MDL 读锁；对一张表做结构变更操作的时候，加的是  MDL 写锁；MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

 MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与 DDL冲突，保证读写的正确性。这里的元数据，大家可以简单理解为就是一张表的表结构。 也就是说，某一张表涉及到未提交的事务时，是不能够修改这张表的表结构的

在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享)；当对表结构进行变更操作的时候，加MDL写锁(排他)。 

演示：

当执行 SELECT 、 INSERT 、 UPDATE 、 DELETE 等语句时，添加的是元数据共享锁（ SHARED_READ / SHARED_WRITE），之间是兼容的。

意向锁：当执行插入、更新、删除操作，需要先对表加上「意向独占锁」然后对该记录加独占锁。 意向锁的目的是为了快速判断表里是否有记录被加锁。

1). 介绍

为了避免 DML 在执行时， 加的行锁与表锁的冲突 ，在 InnoDB 中引入了意向锁， 使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查 。

假如没有意向锁，客户端一对表加了行锁后，客户端二如何给表加表锁呢，来通过示意图简单分析一 下：

首先客户端一，开启一个事务，然后执行 DML 操作，在执行 DML 语句时，会对涉及到的行加行锁

当客户端二， 想对这张表加表锁时，会检查当前表是否有对应的行锁，如果没有，则添加表锁，此时就 会从第一行数据，检查到最后一行数据，效率较低。

 2). 分类

意向共享锁(IS): 由语句select ... lock in share mode添加 。 与表锁共享锁 (read)兼容，与表锁排他锁(write)互斥

意向排他锁(IX): 由insert、update、delete、select...for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

一旦事务提交了，意向共享锁、意向排他锁，都会自动释放。

 演示： A. 意向共享锁与表读锁是兼容的

B. 意向排他锁与表读锁、写锁都是互斥的

行级锁：InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

无索引行锁升级为表锁的情况：

间隙锁：

• 间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

 C. 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。 

5.2.数据库的表锁和行锁有什么作用？

表锁的作用：

• 整体控制：表锁可以用来控制整个表的并发访问，当一个事务获取了表锁时，其他事务无法对该表进行任何读写操作，从而确保数据的完整性和一致性。
• 粒度大：表锁的粒度比较大，在锁定表的情况下，可能会影响到整个表的其他操作，可能会引起锁竞争和性能问题。
• 适用于大批量操作：表锁适合于需要大批量操作表中数据的场景，例如表的重建、大量数据的加载等。

行锁的作用：

•  细粒度控制：行锁可以精确控制对表中某行数据的访问，使得其他事务可以同时访问表中的其他行数据，在并发量大的系统中能够提高并发性能。
• 减少锁冲突：行锁不会像表锁那样造成整个表的锁冲突，减少了锁竞争的可能性，提高了并发访问的效率。
• 适用于频繁单行操作：行锁适合于需要频繁对表中单独行进行操作的场景，例如订单系统中的订单修改、删除等操作。

5.3.MySQL两个线程的update语句同时处理一条数据，会不会有阻塞？

如果是两个事务同时更新了 id = 1，比如 update ... where id = 1，那么是会阻塞的。因为 InnoDB 存储引擎实现了行级锁。当A事务对 id =1 这行记录进行更新时，会对主键 id 为 1 的记录加X类型的记录锁，这样第二事务对 id =1 进行更新时，发现已经有记录锁了，就会陷入阻塞状态。

5.4.两条update语句处理一张表的不同的主键范围的记录，一个<10，一个>15，会不会遇到阻塞？底层是为什么的？

不会，因为锁住的范围不一样，不会形成冲突。

• 第一条 update sql 的话（ id<10），锁住的范围是（-♾️，10）
• 第二条 update sql 的话（id >15），锁住的范围是（15，+♾️）

5.5.如果2个范围不是主键或索引？还会阻塞吗？

如果2个范围查询的字段不是索引的话，那就代表 update 没有用到索引，这时候触发了全表扫描，全部索引都会加行级锁，这时候第二条 update 执行的时候，就会阻塞了。因为如果 update 没有用到索引，在扫描过程中会对索引加锁，所以全表扫描的场景下，所有记录都会被加锁，也就是这条 update 语句产生了 4 个记录锁和 5 个间隙锁，相当于锁住了全表。

6.日志

6.1.日志文件是分成了哪几种？

• redo log 重做日志，是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• undo log 回滚日志，是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。
• bin log 二进制日志，是 Server 层生成的日志，主要用于数据备份和主从复制；
• relay log 中继日志，用于主从复制场景下，slave通过io线程拷贝master的bin log后本地生成的日志
• 慢查询日志，用于记录执行时间过长的sql，需要设置阈值后手动开启

6.2.讲一下binlog

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写 入 binlog 文件，binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用。

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，，保存的是全量的日志，用于备份恢复、主从复制；binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

• STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
• ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
• MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT模式；

6.3.UndoLog日志的作用是什么？

undo log 是一种用于撤销回退的日志，它保证了事务的 ACID 特性中的原子性（Atomicity）。

在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用undo log 来进行回滚。如下图：

 每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undolog 里，比如：

• 在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉就好了；
• 在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了；
• 在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值就好了。

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。比如当 delete 一条记录时，中会把记录中的内容都记下来，然后执行回滚操作的时候undo log就读取 undo log 里的数据，然后进行 insert操作。

6.4.有了undolog为啥还需要redolog呢？

Buffer Pool 是提高了读写效率没错，但是问题来了，Buffer Pool 是基于内存的，而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，，这个时候更新就算完成了。后续，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是WAL （Write-Ahead Logging）技术。

WAL 技术指的是， MySQL 的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

redo log 是物理日志，记录了某个数据页做了什么修改比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新，每当执行一个事务就会产生这样的一条或者多条物理日志。在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，可以根据redo log 的内容，将所有数据恢复到最新的状态。

redo log 和 undo log 这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

• redo log 记录了此次事务「完成后」的数据状态，记录的是更新之后的值；
• undo log 记录了此次事务「开始前」的数据状态，记录的是更新之前的值；

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过redo log 恢复事务，如下图：

所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出来， redo log 保证了事务四大特性中的持久性。

写入 redo log 的方式使用了追加操作， 所以磁盘操作是顺序写，而写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

针对「顺序写」为什么比「随机写」更快这个问题，可以比喻为你有一个本子，按照顺序一页一页写肯定比写一个字都要找到对应页写快得多。

可以说这是 WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上而是先记录在日志上，然后在合适的时间再更新到磁盘上 。

至此， 针对为什么需要 redo log 这个问题我们有两个答案：

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

6.5.redo log怎么保证持久性的？

1. Write-ahead logging（WAL）：在事务提交之前，将事务所做的修改操作记录到redo log中，然后再将数据写入磁盘。这样即使在数据写入磁盘之前发生了宕机，系统可以通过redo log中的记录来恢复数据。
2. Redo log的顺序写入：redo log采用追加写入的方式，将redo日志记录追加到文件末尾，而不是随机写入。这样可以减少磁盘的随机I/O操作，提高写入性能。
3. Checkpoint机制：MySQL会定期将内存中的数据刷新到磁盘，同时将最新的LSN（Log SequenceNumber）记录到磁盘中，这个LSN可以确保redo log中的操作是按顺序执行的。在恢复数据时，系统会根据LSN来确定从哪个位置开始应用redo log。

6.6.能不能只用binlog不用relo log？

不行，binlog是 server 层的日志，没办法记录哪些脏页还没有刷盘，redolog 是存储引擎层的日志，可以记录哪些脏页还没有刷盘，这样崩溃恢复的时候，就能恢复那些还没有被刷盘的脏页数据。

6.7.update语句的具体执行过程是怎样的？

具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1;的流程如下：

1.执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：

（1）如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；

（2）如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。

2.执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：

（1）如果一样的话就不进行后续更新流程；

（2）如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；

3.开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。

4.nnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。

5.至此，一条记录更新完了。

6.在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有binlog 刷新到硬盘。

7.事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：

• prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
• commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；

8.至此，一条更新语句执行完成。

7. 性能调优

7.1.mysql的explain有什么作用？

explain 是查看 sql 的执行计划，主要用来分析 sql 语句的执行过程，比如有没有走索引，有没有外部排序，有没有索引覆盖等等。

如下图，就是一个没有使用索引，并且是一个全表扫描的查询语句。

 对于执行计划，参数有：

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度；
• rows 表示扫描的数据行数。
• type 表示数据扫描类型，我们需要重点看这个。

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• All（全表扫描）：在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。
• index（全索引扫描）：index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。
• range（索引范围扫描）：range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
• ref（非唯一索引扫描）：ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
• eq_ref（唯一索引扫描）：eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
• const（结果只有一条的主键或唯一索引扫描）：const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

7.2.给你张表，发现查询速度很慢，你有那些解决方案??

• 分析查询语句：使用EXPLAIN命令分析SQL执行计划，找出慢查询的原因，比如是否使用了全表扫描，是否存在索引未被利用的情况等，并根据相应情况对索引进行适当修改。
• 创建或优化索引：根据查询条件创建合适的索引，特别是经常用于WHERE子句的字段、Orderby 排序的字段、Join 连表查询的字典、 group by的字段，并且如果查询中经常涉及多个字段，考虑创建联合索引，使用联合索引要符合最左匹配原则，不然会索引失效。
• **避免索引失效：**比如不要用左模糊匹配、函数计算、表达式计算等等。
• 查询优化：避免使用SELECT *，只查询真正需要的列；使用覆盖索引，即索引包含所有查询的字段；联表查询最好要以小表驱动大表，并且被驱动表的字段要有索引，当然最好通过冗余字段的设计，避免联表查询。
• **分页优化：**针对 limit n,y 深分页的查询优化，可以把Limit查询转换成某个位置的查询：select *from tb_sku where id>20000 limit 10，该方案适用于主键自增的表，
• 使用缓存技术：引入缓存层，如Redis，存储热点数据和频繁查询的结果，但是要考虑缓存一致性的问题，对于读请求会选择旁路缓存策略，对于写请求会选择先更新 db，再删除缓存的策略。

7.3.如果Explain用到的索引不正确的话，有什么办法干预吗？

可以使用 force index，强制走索引。

8.架构

8.1.MySQL主从复制了解吗？？

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

 MySQL 集群的主从复制过程梳理成 3 个阶段：

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程如下：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

在完成主从复制之后，你就可以在写数据时只写主库，在读数据时只读从库，这样即使写请求会锁表或者锁记录，也不会影响读请求的执行。

8.2.主从延迟都有什么处理方法？

强制走主库方案：对于大事务或资源密集型操作，直接在主库上执行，避免从库的额外延迟。