Mysql面试题以及知识总结

MySQL架构

逻辑架构图和执行流程：

并发控制

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

事务

1. 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
3. 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

隔离级别

READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。

READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。

REPEATABLE-READ：可重复读，mysql 默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。

SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

补充：

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读 ：是指在一个事务内，多次读同一数据数据发生了变化。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

多版本并发控制(MVVC)

可以认为MVVC是行级锁的一个变种，innoDB采用了乐观锁的策略，在每行记录后保存两个隐藏列来实现，这两个列保存了行的版本号信息，每开启一个新事务，版本号自动更新，事务开始时刻的版本号作为事务的版本号。用来和查询到的记录所带的版本号进行比较来判断。

存储引擎

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ Next-Key Locking 防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

Myisam 和 InnoDB 的区别

• 是否支持行级锁 : MyISAM 只有表级锁 (table-level locking)，而 InnoDB 支持行级锁(row-level locking) 和表级锁, 默认为行级锁，适合高并发操作。
• 是否支持外键： MyISAM 不支持，而 InnoDB 支持
• 是否支持事务：MyISAM 不支持，而 InnoDB 支持
• 缓存：MyISAM 只缓存索引，InnoDB 缓存索引和真实数据，所以对内存要求高
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

数据库设计优化

字符串

**char 和 varchar **

char(n) ：固定长度类型，比如订阅 char(10)，当你输入 “abc” 三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。

char 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

使用场景：UTF-8这种复杂的字符集。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。 金额数据在程序中选用 BigDecimal 类，数据库中 DECIMAL

时间和日期

MySQL 提供了两种相似的日期时间类型：DATETIME 和 TIMESTAMP。

1. DATETIME

能够保存从 1000 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATETIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

2. TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年到 2038 年。

它和时区有关，也就是说一个时间戳在不同的时区所代表的具体时间是不同的。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

数据库的三范式

• 第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
• 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
• 第三范式：任何非主属性不依赖于其它非主属性。

如何设计一个关系型数据库？

首先划分为两大部分，一个存储部分，类似文件系统将数据持久化到硬盘上， 另一个是程序实例部分，创建模块： 存储管理，缓存机制，SQL 解析，日志管理，权限划分，容灾机制 **，索引管理，锁模块 **。

索引

B 树和 B + 树的概念和区别

1）B - 树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B + 树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。 2）在 B - 树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B + 树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 B - 树的性能好像要比 B + 树好，而在实际应用中却是 B + 树的性能要好些。

原因：

• 因为 B + 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B - 树多，树高比 B - 树小，这样带来的好处是减少磁盘访问次数。尽管 B + 树找到一个记录所需的比较次数要比 B - 树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B + 树的性能可能还会好些。
• B + 树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等）。
• B + 树的查询效率更加稳定，每次查询的效率一样。

哈希索引

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询一定要从索引的最左前列开始并且不跳过索引中的列。如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引。
在创建索引的字段中第一个就是最左，每个左边的字段都是后面一个字段的一整个树，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。即要按照顺序命中索引。

为什么使用索引能提高效率

1. 数据索引的存储是有序的
2. 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
3. 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)
4. 避免全表扫描

为什么要使用索引？

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
• 帮助服务器避免排序和临时表
• 将随机IO变为顺序IO

索引的优点

• 大大减少了服务器需要扫描的数据行数。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

什么情况下应不建或少建索引

• 非常小的表
• 非常大的表，建议使用分区技术
• 经常插入、删除、修改的表
• 数据重复且分布平均的表字段

索引优化

独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

前缀长度的选取需要根据索引选择性来确定。

多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引。

SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;

索引列的顺序

让选择性最强的索引列放在前面。

索引的选择性是指：不重复的索引值和记录总数的比值。最大值为 1，此时每个记录都有唯一的索引与其对应。选择性越高，每个记录的区分度越高，查询效率也越高。

聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行

聚簇索引具有唯一性， 聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引 。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

聚簇索引和非聚簇索引类似查字典时直接根据经验查字的大概位置和先去查偏旁部首再去翻页查询类似。

MyISAM没有聚簇索引，都是二级索引。

优点

• 把相关数据保存在一起，减少了磁盘I/O。
• 数据访问更快。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称
之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢
覆盖索引就是把要查询出的列和索引是对应的，不做回表操作。

索引的使用经验

• 在经常需要搜索的列上，可以加快搜索的速度；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
• 在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
• 避免 where 子句中对宇段施加函数，这会造成无法命中索引。
• 在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
• 将打算加索引的列设置为 NOT NULL ，否则将导致引擎放弃使用索引而进行全表扫描
• 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗
• 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

性能分析

Explain

id

如果没有子查询或者联合就只会显示1，如果有就按照顺序编号，对应其在 原始语句的位置。

type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

简易版：
system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref。

三个较差情况：
1.range：
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。和index的消耗差不多。

2.index：
依然是全表扫描，只不过是按照索引的次序而不是行，可以避免排序，出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组 ，一般是where后面没有优化索引

3.all（最差）：
Full Table Scan，将遍历全表以找到匹配的行

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。
key_len字段能够帮你检查是否充分的利用上了索引，越大越好

Extra

包含不适合在其他列中显示但十分重要的额外信息，主要用来检测
order by，group by或者关联查询是否使用了索引。
有以下几种情况：
1.Using filesort 2.Using temporary 3.using join buffer都是很差的是使用方式。要建立索引。
4.impossible where：不可能出现的情况，一般为 逻辑出现错误。
5.USING index：
利用索引进行了排序或分组，
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

查询优化

单表查询优化
常见索引失效：
1 . 系统中经常出现的sql语句如下：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = ‘abcd’

解决：
where后筛选字段有多少就建立多少索引

CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)

1.最佳左前缀法则

2.不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描,在where后使用计算、函数、(自动or手动)类型转换都会使索引失效

3.存储引擎不能使用索引中范围条件右边的列，即在建立索引时，范围查询要放到最后

4.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

5.is not null 也无法使用索引,但是is null是可以使用索引的

6.like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

7.字符串不加单引号索引失效，javabean类型和mysql字段的字符类型要一致，不然所以会失效。

关联查询优化

1、保证被驱动表的join字段已经被索引

2、left join 时，选择小表作为驱动表，大表作为被驱动表。

3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

4、子查询尽量不要放在被驱动表，有可能使用不到索引。

5、能够直接多表关联的尽量直接关联，不用子查询。

子查询优化：

尽量不要使用not in 或者 not exists

用left outer join on xxx is null 替代

排序分组优化：

普通order by不能使用索引

无过滤 不索引，可加上过滤条件，使用索引

顺序错，必排序排序的顺序要对应

方向反 必排序 desc asc要一样

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

最后使用索引的手段：覆盖索引 即不要使用select *

---

---

---

MySQL面试题

如何设计一个关系型数据库？

首先划分为两大部分，一个存储部分，类似文件系统将数据持久化到硬盘上，
另一个是程序实例部分，创建模块：
存储管理，缓存机制，SQL解析，日志管理，权限划分，容灾机制**，索引管理，锁模块**。

数据库的三范式是什么？

• 第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
• 第二范式：要求实体的属性完全依赖于主关键字。所谓完全 依赖是指不能存在仅依赖主关键字一部分的属性。
• 第三范式：任何非主属性不依赖于其它非主属性。

事务的基本要素 ACID⭐

Atomicity（原子性）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行

Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。

Isolation（隔离性）：同一时间，只允许一个事务操作同一数据，不同的事务之间彼此没有任何干扰。 事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

Durability（持久性）：事务处理结束后，对数据的修改是永久的。

char 和 varchar 的区别是什么？

char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。

char 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varcahr 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

where与having的区别

• 用的地方不一样

  where可以用于select、update、delete和insert into values(select * from table where …)语句中。

  having只能用于select语句中

• 执行的顺序不一样

  where的搜索条件是在执行语句进行分组之前应用

  having的搜索条件是在分组条件后执行的

  即如果where和having一起用时，where会先执行，having后执行

数据库的事务隔离/事务隔离级别⭐

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

READ-UNCOMMITTED：未提交读，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。

READ-COMMITTED：提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。

REPEATABLE-READ：可重复读，mysql默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读）。

SERIALIZABLE：序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

在MySQL可重复读的隔离级别中并不是完全解决了幻读的问题，而是解决了读数据情况下的幻读问题。而对于修改的操作依旧存在幻读问题，就是说MVCC对于幻读的解决是不彻底的。 通过next key lock解决了幻读的问题。

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身
• Next-key lock：record+gap 锁定一个范围，包含记录本身

补充：

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。

不可重复读 ：是指在一个事务内，多次读同一数据数据发生了变化。

幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

如何解决事务的并发问题⭐

1. 版本检查

在数据库中保留“版本”字段，跟随数据同时读写，以此判断数据版本。版本可能是时间戳或状态字段。

下例中的 WHERE 子句就实现了简单的版本检查：

UPDATE table SET status = 1 WHERE id=1 AND status = 0;

版本检查能够作为“乐观锁”，解决更新丢失的问题。

2.1 共享锁与排它锁

共享锁（Shared locks, S-locks）

基本锁类型之一。加共享锁的对象只允许被当前事务和其他事务读。也称读锁。

能给未加锁和添加了S锁的对象添加S锁。对象可以接受添加多把S锁。

排它锁（Exclusive locks, X-locks）

基本锁类型之一。加排它锁的对象只允许被当前事务读和写。也称独占锁，写锁。

只能给未加锁的对象添加X锁。对象只能接受一把X锁。加X锁的对象不能再加任何锁。

更新锁（Update locks, U-locks）

锁类型之一。引入它是因为多数数据库在实现加X锁时是执行了如下流程：先加S锁，添加成功后尝试更换为X锁。这时如果有两个事务同时加了S锁，尝试换X锁，就会发生死锁。因此增加U锁，U锁代表有更新意向，只允许有一个事务拿到U锁，该事务在发生写后U锁变X锁，未写时看做S锁。

悲观锁与乐观锁

这两种锁的说法，主要是对“是否真正在数据库层面加锁”进行讨论。

悲观锁（Pessimistic Locking）

悲观锁假定当前事务操纵数据资源时，肯定还会有其他事务同时访问该数据资源，为了避免当前事务的操作受到干扰，先锁定资源。悲观锁需使用数据库的锁机制实现，如使用行级排他锁或表级排它锁。

乐观锁（Optimistic Locking）

乐观锁假定当前事务操纵数据资源时，不会有其他事务同时访问该数据资源，因此不在数据库层次上的锁定。乐观锁使用由程序逻辑控制的技术来避免可能出现的并发问题。

唯一能够同时保持高并发和高可伸缩性的方法就是使用带版本检查的乐观锁。

乐观锁不能解决脏读的问题，因此仍需要数据库至少启用“读已提交”的事务隔离级别。

说一下乐观锁和悲观锁？⭐

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

（如SVN、GIT提交代码就是这样的）

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

一般是 where id=XX for update 来实现 （一般银行转账、工单审批）

优缺点：

乐观锁：性能高、重试失败成本不高建议乐观

悲观锁：性能低，但安全，失败成功高建议悲观，使用不当有死锁风险

多版本并发控制(MVCC)⭐

(Multi-Version Concurrency Control)

可以认为MVCC是行级锁的一个变种，innoDB采用了乐观锁的策略，在每行记录后保存两个隐藏列来实现，这两个列保存了行的版本号信息，每开启一个新事务，版本号自动更新，事务开始时刻的版本号作为事务的版本号。用来和查询到的记录所带的版本号进行比较来判断。

MySQL逻辑架构和执行流程？⭐

• 最上层的服务类似其他CS结构，比如连接处理，授权处理。
• 第二层是Mysql的服务层，包括SQL的解析分析优化，存储过程触发器视图等也在这一层实现。
• 最后一层是存储引擎的实现。

说一下 mysql 常用的引擎？

InnoDB 引擎：MySQL 的5.5之后的默认引擎，InnoDB 引擎提供了对数据库事务的支持，并且还提供了行级锁和外键的约束，它的设计的目标就是处理大数据容量的数据库系统。MySQL 运行的时候，InnoDB 会在内存中建立缓冲池，用于缓冲数据和索引。由于锁的粒度小，写操作是不会锁定全表的,所以在并发度较高的场景下使用会提升效率的。

MyISAM 引擎：不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count(*) from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

Myisam和InnoDB的区别⭐

• 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁，适合高并发操作。
• 是否支持外键： MyISAM不支持，而InnoDB支持
• 是否支持事务：MyISAM不支持，而InnoDB支持
• 缓存：MyISAM只缓存索引，InnoDB缓存索引和真实数据，所以对内存要求高
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

mysql 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的 。

B树和B+树的概念和区别

1）先说一下B-树是一种多路搜索树，关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。
2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。

为什么选择B+树作为索引结构⭐

• 因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些。
• B+树的叶子节点使用指针连接在一起，方便顺序遍历和范围查询，这也是优于hash索引的地方。
• B+树的查询效率更加稳定，每次查询的效率一样。

Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ 树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描

• 二叉查找树：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表。
• 平衡二叉树：通过旋转解决了平衡的问题，但是旋转操作效率太低。
• 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了 AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多。

B+树的叶子节点都可以存哪些东西⭐

可能存储的是整行数据，也有可能是主键的值

什么样的信息能成为索引

主键，唯一键，普通键都可，只要能让数据有一定区分性的字段。

聚簇索引⭐

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

非聚簇索引：将数据存储金和索引分开的结构，索引结构的叶子节点指向了数据的对应行

聚簇索引具有唯一性， 一个表仅有一个聚簇索引 。

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。

聚簇索引和非聚簇索引类似查字典时直接根据经验查字的大概位置和先去查偏旁部首再去翻页查询类似。

MyISAM没有聚簇索引，都是二级索引。

优点

• 把相关数据保存在一起，减少了磁盘I/O。
• 数据访问更快。

覆盖索引⭐

指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

最佳左前缀法则⭐

指的是查询从索引的最左前列开始并且不跳过索引中的列。
在创建索引的字段中第一个就是最左，每个左边的字段都是后面一个字段的一整个树，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。要按照顺序命中索引

mysql 问题排查都有哪些手段？

• 使用 show processlist 命令查看当前所有连接信息。
• 使用 explain 命令查询 SQL 语句执行计划。
• 开启慢查询日志，查看慢查询的 SQL。

性能分析的重点字段

type

type显示的是访问类型 system>const>eq_ref>ref>range>index>ALL,一般来说，得保证查询至少达到range级别，最好能达到ref。

三个较差情况：
1.range：
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

2.index：
出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组 ，一般是where后面没有优化索引

3.all（最差）：
Full Table Scan，将遍历全表以找到匹配的行

备注：一般来说，得保证查询至少达到range级别，最好能达到ref。

Extra

包含不适合在其他列中显示但十分重要的额外信息，主要用来检测
order by，group by或者关联查询是否使用了索引。
有以下几种情况：
1.Using filesort 2.Using temporary 3.using join buffer都是很差的是使用方式。要建立索引。
4.impossible where：不可能出现的情况，一般为 逻辑出现错误。
5.USING index：
利用索引进行了排序或分组，
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

1. 模糊查询 %like
2. 索引列参与计算,使用了函数
3. 非最左前缀顺序
4. where对null判断
5. where不等于
6. or操作有至少一个字段没有索引
7. 需要回表的查询结果集过大（超过配置的范围）

sql 优化可以从哪些方面考虑？⭐

主要是从怎么合理创建索引 合理编写 SQL 语句和防止索引失效 合理创建表字段这3个方面入手

• 合理创建索引：

• 合理编写 SQL 语句：

  不使用 select *，使用 LIMIT 语句来限制返回的数据，IN包含的值不应过多等

• ** 防止索引失效： **保证最左前缀法则，尽量不适用前缀模糊查询 %like，避免索引列参与计算或使用了函数，避免在where子句中对字段进行null值判断，看看表编码，表字段是否一样，联合索引中范围查询会让后面的索引字段失效，join查询时要用小表驱动大表

• 合理创建表字段： 最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库

索引的使用经验

创建索引考虑几个因素：

覆盖索引：因为覆盖索引可以减少回表的次数，而且在MySQL5.6后增加了一个索引下推的功能，可以在让覆盖索引配合索引下推，尽量减少回表的次数。

可以explain命令查看执行计划时看到 extra 列的 using index condition 是说明用到了索引， Using filesort，Using temporary 都是不好的，看rows 列可以知道扫描的行数，可以根据这个判断是否需要优化。

我们可以考虑在读少写多的场景下（日志，账单），我们可以使用普通索引，因为innodb对普通索引做了优化，使用了 Change buffer，它可以把写操作缓存下来，在读的时候再去merge，这样可以减少io次数，提高语句执行速度，提高内存利用率。

还可以考虑索引统计信息是否有问题，analyze table重新统计信息，因为索引信息并不是一个准确值，是一个随机采样的过程。如果发现执行计划中的key列使用的索引不好时，应急预案可以考虑使用 force index 强制索引

数据库调优经验

使用了索引却仍然不是很快，就使用 explain 分析了一下发现表中有多个索引，因为可能涉及回表，排序的操作，MySQL 优化器选用了错误的索引导致查询效率偏低，然后通过 SQL 语句中使用 useindex 来指定索引解决。

批量往mysql导入1000万数据有什么方法？

• 减少IO次数

• SQL写法优化，一条SQL语句插入多条数据

• 合理设置批量大小

• 尽量顺序插入， 减少索引的维护压力

redolog,undolog,binlog

• undoLog 也就是我们常说的回滚日志文件 主要用于事务中执行失败，进行回滚，以及MVCC中对于数据历史版本的查看。由引擎层的InnoDB引擎实现,是逻辑日志,记录数据修改被修改前的值,比如"把id=‘B’ 修改为id = ‘B2’ ，那么undo日志就会用来存放id ='B’的记录”。当一条数据需要更新前,会先把修改前的记录存储在undolog中,如果这个修改出现异常,则会使用undo日志来实现回滚操作,保证事务的一致性。当事务提交之后，undo log并不能立马被删除,而是会被放到待清理链表中,待判断没有事物用到该版本的信息时才可以清理相应undolog。它保存了事务发生之前的数据的一个版本，用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。
• redoLog 是重做日志文件是记录数据修改之后的值，用于持久化到磁盘中。redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。由引擎层的InnoDB引擎实现,是物理日志,记录的是物理数据页修改的信息,比如“某个数据页上内容发生了哪些改动”。当一条数据需要更新时,InnoDB会先将数据更新，然后记录redoLog 在内存中，然后找个时间将redoLog的操作执行到磁盘上的文件上。不管是否提交成功我都记录，你要是回滚了，那我连回滚的修改也记录。它确保了事务的持久性。
• binlog由Mysql的Server层实现,是逻辑日志,记录的是sql语句的原始逻辑，比如"把id=‘B’ 修改为id = ‘B2’。binlog会写入指定大小的物理文件中,是追加写入的,当前文件写满则会创建新的文件写入。 产生:事务提交的时候,一次性将事务中的sql语句,按照一定的格式记录到binlog中。用于复制和恢复在主从复制中，从库利用主库上的binlog进行重播(执行日志中记录的修改逻辑),实现主从同步。业务数据不一致或者错了，用binlog恢复。

binlog和redolog的区别

1. redolog是在InnoDB存储引擎层产生，而binlog是MySQL数据库的上层服务层产生的。
2. 两种日志记录的内容形式不同。MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的重做日志是物理日志。
3. 两种日志与记录写入磁盘的时间点不同，binlog日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入，并日志不是随事务提交的顺序进行写入的。
4. binlog不是循环使用，在写满或者重启之后，会生成新的binlog文件，redolog是循环使用。
5. binlog可以作为恢复数据使用，主从复制搭建，redolog作为异常宕机或者介质故障后的数据恢复使用。