性8论坛：8 MySQL

MySQL基础篇

说一说三个范式

第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第二范式(确保表中的每列都和主键相关)（前提是第一范式）

在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式(确保表中每列都和主键直接相关,而不是间接相关)（前提是第一第二范式）

已经分好了多张表的话，一张表中只能有另一张表的ID，而不能有其他任何信息（其他任何信息，一律用主键在另一张表中查询）。

说一下 MySQL 执行一条查询语句的内部执行过程？

连接器 → 查询缓存 → 分析器 → 优化器 → 执行器

• 客户端先通过连接器连接到 MySQL 服务器。

• 连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。

• 分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。

• 优化器对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。

• 优化器执行完就进入执行器，开始执行查询语句直到查询出满足条件的所有数据，然后进行返回。

MySQL 查询缓存的功能有何优缺点？

优点是效率高，如果已经有缓存则会直接返回结果。

但是，如果数据库表中数据和结构发生变化时（增删改、表结构调整），查询缓存将会失效并被清除，导致缓存命中率比较低。

MySQL 的常用引擎都有哪些？

MySQL 的常用引擎有 InnoDB、MyISAM、Memory（所有数据放在内存中） 等，从 MySQL 5.5.5 版本开始 InnoDB 就成为了默认的存储引擎。

MySQL 可以针对表级别设置数据库引擎吗？怎么设置？

可以针对不同的表设置不同的引擎。在 create table 语句中使用 engine=引擎名（比如Memory）来设置此表的存储引擎。

InnoDB v.s. MyISAM

• 最大的区别是 InnoDB 支持事务，而 MyISAM 不支持事务；

• InnoDB 支持崩溃后安全恢复，MyISAM 不支持崩溃后安全恢复；

• InnoDB 支持行锁，MyISAM 不支持行锁，只支持表锁；

• InnoDB 支持外键，MyISAM 不支持外键；

InnoDB 有哪些特性？

1）插入缓冲(insert buffer)：插入缓冲带来的是性能。对于非聚集索引的插入和更新，不是每一次直接插入索引页中，而是首先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入，否则，先放入一个插入缓冲区中。好似欺骗数据库这个非聚集的索引已经插入到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引叶子节点的合并操作，这时通常能将多个插入合并到一个操作中，这就大大提高了对非聚集索引执行插入和修改操作的性能。（对于非聚集索引页，不是直接放到索引页中，而是先放到缓冲池中，这时候缓冲池中可能存放了多个非聚集索引页，然后将这多个非聚集索引页的插入合并到一个操作中，取代了之前的一个一个插入）

2）两次写(double write)：两次写带来的是可靠性，主要用来解决部分写失败。doublewrite 由两部分组成，一部分是内存中的 doublewrite buffer ，大小为 2M，另外一部分就是物理磁盘上的共享表空间中连续的 128 个页，即两个区，大小同样为 2M。当缓冲池的作业刷新时，并不直接写硬盘，而是通过 memcpy 函数将脏页先拷贝到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次写，每次写入 1M 到共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘。（要进行写操作时，不是直接写硬盘，而是写拷贝到内存中的两次写缓冲区大小2M，然后把两次写缓冲区的内容分两次写（每次写1M）到共享表空间的磁盘上，然后再将共享表空间的内容同步到数据文件上）

3）自适应哈希索引(adaptive hash index)：由于 InnoDB 不支持 hash 索引，但在某些情况下 hash 索引的效率很高，于是出现了 adaptive hash index 功能， InnoDB 存储引擎会监控对表上索引的查找，如果观察到建立 hash 索引可以提高性能的时候，则自动建立 hash 索引。（为了提高性能会自动建立一些 hash 索引，查找效率很高，直接通过 hash 值定位到数据）

一张自增表中有三条数据，删除了两条数据之后重启数据库，再新增一条数据，此时这条数据的 ID 是几？

等价于：自增主键能不能被持久化的问题？

MyISAM：自增主键会持久化到数据文件中

InnoDB：
5.7 以及之前：不会持久化
从 8 开始：自增主键会持久化到 redo log

如果表的引擎类型是MyISAM，那么这条记录的 ID 就是 4。因为MyISAM表会把自增主键的最大ID记录到数据文件里面，重启 MySQL 后，自增主键的最大 ID 也不会丢失。

如果表的引擎类型是InnoDB，那么这条记录的 ID 就是 2。因为InnoDB表把自增主键的最大ID记录到内存中，所以重启数据库后会使最大ID（其实是AUTO_INCREMENT 计数器的值）丢失；一旦数据库重新运行，数据库会自动计算自增主键的最大ID（其实就是把最后一条记录 ID 加 1 并赋值给 AUTO_INCREMENT）再次放入到内存中。

【注】这仅仅是 MySQL 8 以前的版本，也就是 MySQL 5.7 以及之前的版本。因为在MySQL 8.0中，InnoDB 的行为已更改。每次更改时，当前最大自动增量计数器值（AUTO_INCREMENT）都会写入重做日志 redo log，并保存到每个检查点的引擎专用系统表中。这些更改使当前的最大自动增量计数器值在服务器重新启动后保持不变，与MyISAM一样了。详见官方文档：InnoDB AUTO_INCREMENT 计数器初始化

【注】如果删除的不是最后的记录，那么自增的最大 ID 全部都一样，因为自动增量计数器值（AUTO_INCREMENT）不会改变。

MySQL 中什么情况会导致自增主键不能连续？

唯一键冲突

事务回滚

• 执行器执行插入，自增键+1，但是 innoDB 发现插入时唯一键冲突，无法插入，导致的不连续。

• 事务添加自增键+1，但是由于事务回滚，数据被清除，导致的不连续。

什么是覆盖索引？

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键上去取数据即回表。

如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法进行回表查询了？

如果把主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 作为主键。

内存表和临时表有什么区别？

内存表，指的是使用 Memory 引擎的表，数据放在内存中，重启会被清空；

临时表，指的是使用 InnoDB 引擎或者 MyISAM 引擎的表，数据放在磁盘上，重启不会清空。

MySQL 中的字符串类型都有哪些？

类型取值范围

CHAR(N)	0~255
VARCHAR(N)	0~65536
TINYBLOB	0~255
BLOB	0~65535
MEDUIMBLOB	0~167772150
LONGBLOB	0~4294967295
TINYTEXT	0~255
TEXT	0~65535
MEDIUMTEXT	0~167772150
LONGTEXT	0~4294967295
VARBINARY(N)	0~N个字节的变长字节字符集
BINARY(N)	0~N个字节的定长字节字符集

VARCHAR 和 CHAR 的区别是什么？分别适用的场景有哪些？

VARCHAR 的长度是可变的，而 CHAR 是固定长度。由于它们的特性决定了 CHAR 比较适合长度较短的字段和固定长度的字段，如身份证号、手机号等，反之则适合使用 VARCHAR。

MySQL 存储金额应该使用哪种数据类型？为什么？

MySQL 存储金额应该使用 decimal ，因为如果存储其他数据类型，比如 float 有导致小数点后数据丢失的风险。

limit 3,2 的含义是什么？

去除前三条数据之后查询两条信息。

lastinsertid() 函数功能是什么？有什么特点？

lastinsertid() 用于查询最后一次自增表的编号（全局的），它的特点是查询时不需要指定表名，使用 select last_insert_id() 即可查询，因为不需要指定表名所以它始终以最后一条自增编号为主，可以被其它表的自增编号覆盖。比如 A 表的最大编号是 10， lastinsertid() 查询出来的值为 10，这时 B 表插入了一条数据，它的最大编号为 3，这个时候使用 lastinsertid() 查询的值就是 3。

删除表的数据有几种方式？它们有什么区别？

删除数据有两种方式：delete 和 truncate，它们的区别如下：

• delete 可以添加 where 条件删除部分数据，truncate 不能添加 where 条件只能删除整张表；

• delete 的删除信息会在 MySQL 的日志中记录，而 truncate 的删除信息不被记录在 MySQL 的日志中，
  因此 detele 的信息可以被找回而 truncate 的信息无法被找回；

• truncate 因为不记录日志所以执行效率比 delete 快。

MySQL 中支持几种模糊查询？它们有什么区别？

MySQL 中支持两种模糊查询：regexp 和 like
like 是对任意多字符匹配或任意单字符进行模糊匹配，
而 regexp 则支持正则表达式的匹配方式，提供比 like 更多的匹配方式。
regexp 和 like 的使用示例如下：

select * from person where uname like ‘%SQL%’;
select * from person where uname regexp ‘.SQL*.’;

MySQL 支持枚举吗？如何实现？它的用途是什么？

MySQL 支持枚举，枚举的作用是预定义结果值，当插入数据不在枚举值范围内，则插入失败

create table t( sex enum('boy','grid') default 'unknown');

count(column) 和 count(*) 有什么区别？

count(column) 统计不会统计列值为 null 的数据，
而 count(*) 则会统计所有信息，所以最终的统计结果可能会不同。

为什么 InnoDB 不把总条数记录下来，查询的时候直接返回呢？

因为 InnoDB 支持事务，即使是在同一时间进行查询，得到的结果也可能不相同，所以 InnoDB 不能把结果直接保存下来，因为这样是不准确的。

能否使用 show table status 中的表行数作为表的总行数直接使用？为什么？

不能，因为 show table status 是通过采样统计估算出来的，官方文档说误差可能在 40% 左右，所以 show table status 中的表行数不能直接使用。

InnoDB 和 MyISAM 执行 select count(*) from t，哪个效率更高？为什么？

MyISAM 效率最高，因为 MyISAM 内部维护了一个计数器，直接返回总条数，而 InnoDB 要逐行统计。

在 MySQL 中有对 count(*) 做优化吗？做了哪些优化？

count(*) 在不同的 MySQL 引擎中的实现方式是不相同的，在没有 where 条件的情况下：

• MyISAM 引擎会把表的总行数存储在磁盘上，因此在执行 count(*) 的时候会直接返回这个这个行数，执行效率很高；

• InnoDB 引擎中 count(*) 就比较麻烦了，需要把数据一行一行的从引擎中读出来，然后累计基数。

但即使这样，在 InnoDB 中，MySQL 还是做了优化的，我们知道对于 count(*) 这样的操作，遍历任意索引树得到的结果，在逻辑上都是一样的，因此，MySQL 优化器会找到最小的那颗索引树来遍历，这样就能在保证逻辑正确的前提下，尽量少扫描数据量，从而优化了 count(*) 的执行效率。

在 InnoDB 引擎中 count(*)、count(1)、count(主键)、count(字段) 哪个性能最高？

具体参考：https://www.cnblogs.com/xiaolincoding/p/15769721.html

count(字段)<count(主键 id)<count(1)≈count(*) 题目解析：

• 对于 count(字段) 来说，遍历整张表，取这个字段，然后判断是否为 null，不为 null 则加1
• 对于 count(主键 id) 来说，遍历整张表，取主键id，不可能为空，每次加1。但是可能使用最小的索引树。
• 对于 count(1) 来说，遍历整张表，不取值。1 是不可能为空的，每次加 1。
• 对于 count(*) 来说，遍历整张表，不取值，直接按行累加。实际上，当使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

所以最后得出的结果是：count(字段)<count(主键 id)<count(1)≈count(*)。

MySQL 中内连接、左连接、右连接有什么区别？

• 内连（inner join）— 把匹配的关联数据显示出来；

• 左连接（left join）— 把左边的表全部显示出来，右边的表显示出符合条件的数据；

• 右连接（right join）— 把右边的表全部显示出来，左边的表显示出符合条件的数据；

什么是视图？如何创建视图？

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增、改、查操作。

CREATE VIEW <视图名> AS <SELECT语句>

视图有哪些优点？

• 获取数据更容易，相对于多表查询来说；

• 视图能够对机密数据提供安全保护；

• 视图的修改不会影响基本表，提供了独立的操作单元，比较轻量。

MySQL 中“视图”的概念有几个？分别代表什么含义？

MySQL 中的“视图”概念有两个，它们分别是：

• MySQL 中的普通视图也是我们最常用的 view，创建语法是 create view …,它的查询和普通表一样；

• InnoDB 实现 MVCC（Multi-Version Concurrency Control）多版本并发控制时用到的一致性读视图 read view，它没有物理结构，作用是事务执行期间进行数据版本的可见性判断。

MySQL索引篇

什么是索引？

索引是一种能帮助 MySQL 提高查询效率的数据结构。

索引分别有哪些优点和缺点？

索引的优点：

• 提高查询效率

索引的缺点：

• 虽然提高了查询速度，但却降低了更新表的速度，比如 update、insert；
  因为更新数据时，MySQL 不仅要更新数据，还要更新索引文件；

• 建立索引的索引文件会占用磁盘。

MySQL索引的注意事项

1、联合索引遵循前缀原则

2、like模糊查询，%不能在前

**3、**列值为空（NULL）时是可以使用索引的，但MySQL难以优化引用了可空列的查询,它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。

4、如果MySQL估计使用索引比全表扫描更慢，会放弃使用索引，例如：
表中只有100条数据左右。对于SQL语句WHERE id > 1 AND id < 100，MySQL会优先考虑全表扫描。

5、如果关键词or前面的条件中的列有索引，后面的没有，所有列的索引都不会被用到。

6、列类型是字符串，查询时一定要给值加引号，否则索引失效，例如：
列name varchar(16)，存储了字符串"100"
WHERE name = 100;
以上SQL语句能搜到，但无法用到索引。

7、不在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效而转向全表扫描

为什么 MySQL 官方建议使用自增主键作为表的主键？

优点：

页分裂问题。如果使用非自增主键作为表的主键的话，当新插入的数据会将数据页写满，mysql 就需要申请新的数据页，并且把上个数据页中的部分数据挪到新的数据页上，以确保索引有序。也就是说造成了页分裂，大量移动数据的过程严重影响插入效率。

存储空间问题。自增主键存储空间小，同一个结点内能存放更多的主键，树的高度可能降低，减少io次数。

缺点：

数据量过大，可能会超出自增长取值范围；

无法满足分布式存储，分库分表的情况下无法合并表；

主键有自增规律，容易被破解；

**综上所述：**是否需要使用自增主键，需要根据自己的业务场景来设计。如果是单表单库，则优先考虑自增主键，如果是分布式存储，分库分表，则需要考虑数据合并的业务场景来做数据库设计方案。

索引有几种类型？分别如何创建？

**MySQL 的索引有两种分类方式：逻辑分类和物理分类。 **

按照逻辑分类，索引可分为：

• 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
• 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引；
• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

按照物理分类，索引可分为：

• 聚簇索引：InnoDB 的主键索引存储采用聚簇索引，主键值和数据存放在一起
• 非聚簇索引：MyISAM 的主键索引存储采用非聚簇索引，主键值和指向数据的指针存放在一起，数据另开空间存储

各种索引的创建脚本如下：

-- 创建主键索引ALTER TABLE user ADD PRIMARY KEY (column_list);-- 创建唯一索引ALTER TABLE user ADD UNIQUE index_name (column_list);-- 创建普通索引ALTER TABLE user ADD INDEX index_name (column_list);-- 创建全文索引ALTER TABLE user ADD FULLTEXT index_name (column_list);

什么叫回表查询？

普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。

也就是说，基于非主键索引的查询需要多扫描一次主键索引树。因此，我们在应用中应该尽量使用主键查询。

例外就是使用非主键索引时，如果当前索引满足查询需求，则不用回表。

在 InnDB 中主键索引为什么比普通索引的查询性能高？

回表。因为普通索引的查询会多执行一次检索操作。比如主键查询 select * from t where id=10 只需要搜索 id 的这棵 B+ 树，而普通索引查询 select * from t where f=3 会先查询 f 索引树，得到 id 的值之后再去搜索 id 的 B+ 树，因为多执行了一次检索，所以执行效率就比主键索引要低。

如何查询一张表的所有索引？

SHOW INDEX FROM user

MySQL 最多可以创建多少个索引列？

MySQL 中最多可以创建 16 个索引列。

怎么解决like %abc 索引失效的问题？

使用覆盖索引，要求所有查询的字段都有索引，如果查询的字段没有被索引，则会引起索引失效，全表扫描。

MySQL 复合索引应该注意什么？

MySQL 中的复合索引，遵循最左匹配原则，比如，联合索引为 key(a,b,c)，则能触发索引的搜索组合是 a|ab|abc 这三种查询。

复合索引的作用是什么？

• 用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了key(a)、key(a,b)、key(a,b,c)等三个索引，我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤其是对大数据量的表来说，这可以减少一部分不必要的开销；
• 覆盖索引，比如，对于联合索引 key(a,b,c) 来说，如果使用 SQL：select a,b,c from table where a=1 and b = 1 ，就可以直接通过遍历索引取得数据，而无需回表查询，这就减少了随机的 IO 操作，减少随机的 IO 操作，可以有效的提升数据库查询的性能，是非常重要的数据库优化手段之一；
• 索引列越多，通过索引筛选出的数据越少。

什么是最左匹配原则？它的生效原则有哪些？

最左匹配原则也叫最左前缀原则，是 MySQL 中的一个重要原则，说的是索引以最左边的为起点任何连续的索引都能匹配上，当遇到范围查询（>、<、between、like）就会停止匹配。 生效原则来看以下示例，比如表中有一个联合索引字段 index(a,b,c)：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jb6fLviQ-1655014838311)(E:\Markdown文档图片\8 MySQL.assets%5CUsers%5CArthur%5CAppData%5CRoaming%5CTypora%5Ctypora-user-images%5C1648084827546.png)]

以下语句会走索引么？

select * from t where year(date)>2018;

不会，因为在索引列上涉及到了运算。

能否给手机号的前 6 位创建索引？如何创建？

可以，创建方式有两种：

alter table t add index index_phone(phone(6));create index index_phone on t(phone(6));

什么是前缀索引？

前缀索引也叫局部索引，比如给身份证的前 10 位添加索引。
类似这种给某列部分信息添加索引的方式叫做前缀索引。

为什么要用前缀索引？

前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。

什么情况下适合使用前缀索引？

当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性（不重复的索引值和表记录数的比值，越大说明索引价值越大）为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。

InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？

因为 B 树、Hash、红黑树或二叉树存在以下问题：

• B 树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
• Hash：虽然可以快速定位，但是没有顺序，无法进行范围查找，IO 复杂度高；
• 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高；
• 红黑树：树的高度随着数据量增加而增加，IO 代价高。

唯一索引和普通索引哪个性能更好？

• 对于查询操作来说：普通索引和唯一索引的性能相近，都是从索引树中进行查询；
• 对于更新操作来说：唯一索引要比普通索引执行的慢，因为唯一索引需要先将数据读取到内存中，再在内存中进行数据的唯一效验，所以执行起来要比普通索引更慢。

MySQL 如何指定查询的索引？

在 MySQL 中可以使用 force index 强行选择一个索引，具体查询语句如下：

select * from t force index(index_t);

在 MySQL 中指定了查询索引，为什么没有生效？

我们知道在 MySQL 中使用 force index 可以指定查询的索引，但并不是一定会生效，原因是 MySQL 会根据优化器自己选择索引，如果 force index 指定的索引出现在候选索引上，这个时候 MySQL 不会在判断扫描的行数的多少直接使用指定的索引，如果没在候选索引中，即使 force index 指定了索引也是不会生效的。

MySQL 会错选索引吗？

MySQL 会错选索引，比如 k 索引的速度更快，但是 MySQL 并没有使用而是采用了 v 索引，这种就叫错选索引，因为索引选择是 MySQL 的服务层的优化器来自动选择的，但它在复杂情况下也和人写程序一样出现缺陷。

如何解决 MySQL 错选索引的问题？

• 删除错选的索引，只留下对的索引；
• 使用 force index 指定索引；
• 修改 SQL 查询语句引导 MySQL 使用我们期望的索引，比如把 order by b limit 1 改为 order by b,a limit 1 语义是相同的，但 MySQL 查询的时候会考虑使用 a 键上的索引。

如何优化身份证的索引？

在中国因为前 6 位代表的是地区，所以很多人的前六位都是相同的，如果我们使用前缀索引为 6 位的话，性能提升也并不是很明显，但如果设置的位数过长，那么占用的磁盘空间也越大，数据页能放下的索引值就越少，搜索效率也越低。针对这种情况优化方案有以下两种：

• 使用身份证倒序存储，这样设置前六位的意义就很大了；
• 使用 hash 值，新创建一个字段用于存储身份证的 hash 值。

索引的使用场景

• 对于非常小的表，大部分情况下全表扫描效率更高。
• 中到大型表，索引非常有效。
• 特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术来解决。

聚簇索引和非聚簇索引

Innobd中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。

Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引

聚簇索引(聚集索引)

Innodb聚簇索引和MyIsam非聚簇索引的比较说明
参考博客：https://www.cnblogs.com/zlcxbb/p/5757245.html

InnoDB索引实现

InnoDB使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同.

1）主键索引：

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

(图inndb主键索引）是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2）InnoDB的辅助索引

InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

MyISAM索引实现

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址

1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，图myisam1是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

2）辅助索引（Secondary key）

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

问题：主键索引是聚集索引还是非聚集索引？

在InnoDB下主键索引是聚集索引，在MyISAM下主键索引是非聚集索引

MyISAM索引实现？

MyISAM存储引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做非聚簇索引的，之所以这么称呼是为了与InnoDB的聚簇索引区分。

MyISAM索引与InnoDB索引的区别？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着索引和行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要根据数据地址访问对应的地址才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

简单说一说drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

1、delete和truncate只删除表的数据不删除表的结构
2、速度,一般来说: drop> truncate >delete
3、delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
4、如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

drop、delete与truncate分别在什么场景之下使用？

1、不再需要一张表的时候，用drop
2、想删除部分数据行时候，用delete，并且带上where子句
3、保留表而删除所有数据的时候用truncate

⭐B-Tree 和 B+Tree

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

⭐为什么 B+Tree 比 B-Tree 更适合实际应用中操作系统的文件索引和数据库索引？

B+Tree 的磁盘读写代价更低

B+Tree 的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B-Tree 更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了。

B+Tree 的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

⭐Hash索引和B+树所有有什么区别或者说优劣呢?

首先要知道Hash索引和B+树索引的底层实现原理：

hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.
B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.

那么可以看出他们有以下的不同:

• hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.

• hash索引不支持使用索引进行排序,原理同上.

• hash索引不支持模糊查询.

• hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.

• hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.

因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.

非聚簇索引一定会回表查询吗?

有如下一张InnoDB表：

CREATE TABLE `user` (`id` INT NOT NULL ,`name` VARCHAR NOT NULL ,`age` INT NOT NULL);

其中id为自增主键，name是一个普通索引。在执行select * from user where id = 1时，会在主键索引对应的B+树的叶子结点上搜索到关键字id=1的节点，并读取位于该节点上的整行数据。但是在执行select * from user where name = 'tom’时，会分为两个步骤：

先到name索引对应的B+树的叶子结点上搜索到关键字name='tom’的节点，并从该节点上获取对应的主键id值。

然后再根据id值使用主键索引读取到整行数据。

其中第二个步骤叫作回表查询。需要扫描辅助索引和主键索引两棵B+树才能拿到整行数据，效率较低。

如果执行select id, name from user where name = ‘tom’,则只需要扫描name索引树就可以获取到所有的字段，因为id和name都保存在name索引B+树的叶子节点上，所以不需要再去主键索引上查找。这就是所谓的索引覆盖。只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

而select id, name, age from user where name = ‘tom’，因为age字段没有存储到name索引的叶子节点上，所以需要根据主键索引回表查询到age列值。如果把name索引改成(name，age)的联合索引就可以实现索引覆盖，无需回表了。（覆盖索引，sql语句中用到的字段都有索引）
————————————————
版权声明：本文为CSDN博主「lmyno」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_33603681/article/details/113387941

联合索引是什么?为什么需要注意联合索引中的顺序?

因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整.

创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

MySQL提供了explain命令来查看语句的执行计划

MySQL事务篇（重点）

可见性判断

总结：

大于等于下一个分配的事务ID不可见

包含在未提交事务集合中但是和创建read view的事务ID不相等则也不可见。

事务版本号小于当前最小的事务ID，那么说明它已经提交了，可见。

事务版本号大于等于下一个分配的事务ID，说明这个事务在当前read view生成后在生成，不可见。

事务版本号在 [最小事务ID，下一个分配的事务ID) 区间内：

​ 如果未提交事务集合包含该事务版本号，那么判断是否该事务版本号是否等于创建该read view的事务ID，

​ 等于则说明这个版本的数据是当前事务生成的，可见。不等于则不可见。

​ 如果不包含，说明这个事务版本号已经提交，可见。

查询一条记录，基于MVCC，是怎样的流程

获取数据版本号即数据行中的事务id隐藏字段

执行sql，得到Read View

根据数据版本号和read view 进行可见性判断，判断当前版本数据是否可见

如果不符合Read View的可见性规则， 即就需要Undo log中历史快照

最后返回符合规则的数据

InnoDB 实现MVCC，是通过 Read View+ Undo Log 实现的，Undo Log 保存了历史快照，Read View可见性规则帮助判断当前版本的数据是否可见。

RC下可以读到已提交数据，但不能读到未提交数据

RR下已提交和未提交数据都不能读到

为什么RR下解决了不可重复读问题？

实际上，各种事务隔离级别下的Read view工作方式，是不一样的，RR可以解决不可重复读问题，就是跟Read view工作方式有关。

• 在读已提交（RC）隔离级别下，同一个事务里面，每一次查询都会产生一个新的Read View，这样就可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）。

begin

select * from core_user where id =1	生成一个Read View
/	/
/	/
select * from core_user where id =1	生成一个Read View

• 在可重复读（RR）隔离级别下，一个事务里只会获取一次read view，都是副本共用的，从而保证每次查询的数据都是一样的。

begin

select * from core_user where id =1	生成一个Read View
/
/
select * from core_user where id =1	共用一个Read View副本

MVCC是否解决了幻读问题呢？

对于幻读来说，存在快照读（可以读到多个版本，普通的select）和当前读（读的是最新的 for update）的情况：

RR 隔离级别下为了解决幻读问题：快照读依靠MVCC控制，当前读通过间隙锁解决；

间隙锁和行锁合称 Next-Key Locks，每个 Next-Key Locks 是前开后闭区间；

间隙锁的引入，可能会导致同样语句锁住更大的范围，影响并发度。

并发事务有什么什么问题？应该如何解决？

并发事务可能造成：脏读、不可重复读和幻读等问题 ，这些问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，解决方案如下：

• 加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。

• 提供数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取，从用户的角度来看，好象是数据库可以提供同一数据的多个版本。

MySQL 事务实现原理是什么？

以 InnoDB 的事务实现为例，InnoDB 是通过多版本并发控制（MVCC，Multiversion Concurrency Control ）解决不可重复读问题，加上间隙锁解决幻读问题。因此 InnoDB 的 RR 隔离级别其实实现了串行化级别的效果，而且保留了比较好的并发性能。
事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。

MVCC详细版

1. 相关数据库知识点回顾

1.1 什么是数据库事务，为什么要有事务

事务，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

为什么要有事务呢？就是为了保证数据的最终一致性。

1.2 事务包括哪几个特性？

事务四个典型特性，即ACID，原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性： 事务作为一个整体被执行，包含在其中的对数据库的操作要么全部都执行，要么都不执行。
• 一致性： 指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
• 隔离性： 多个事务并发访问时，事务之间是相互隔离的，一个事务不应该被其他事务干扰，多个并发事务之间要相互隔离。
• 持久性： 表示事务完成提交后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

1.3 事务并发存在的问题

事务并发会引起脏读、不可重复读、幻读问题。

1.3.1 脏读

如果一个事务读取到了另一个未提交事务修改过的数据，我们就称发生了脏读现象。

1.3.2 不可重复读

同一个事务内，前后多次读取，读取到的数据内容不一致

1.3.3 幻读

如果一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入了一些符合那些搜索条件的记录（如insert、delete、update），两次读取到的结果集却不一样了，就意味着发生了幻读。

1.4 四大隔离级别

为了解决并发事务存在的脏读、不可重复读、幻读等问题，数据库大叔设计了四种隔离级别。分别是读未提交，读已提交，可重复读，串行化（Serializable）。

1.4.1 读未提交

读未提交隔离级别，只限制了两个数据不能同时修改，但是修改数据的时候，即使事务未提交，都是可以被别的事务读取到的，这级别的事务隔离有脏读、重复读、幻读的问题；

1.4.2 读已提交

读已提交隔离级别，当前事务只能读取到其他事务提交的数据，所以这种事务的隔离级别解决了脏读问题，但还是会存在重复读、幻读问题；

1.4 3 可重复读

可重复读隔离级别，限制了读取数据的时候，不可以进行修改，所以解决了重复读的问题，但是读取范围数据的时候，是可以插入数据，所以还会存在幻读问题；

1.4.4 串行化

事务最高的隔离级别，在该级别下，所有事务都是进行串行化顺序执行的。可以避免脏读、不可重复读与幻读所有并发问题。但是这种事务隔离级别下，事务执行很耗性能。

1.4.5 四大隔离级别，都会存在哪些并发问题呢

隔离级别脏读不可重复读幻读

读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
串行化	×	×	×

1.5 数据库是如何保证事务的隔离性的呢？

数据库是通过加锁，来实现事务的隔离性的。这就好像，如果你想一个人静静，不被别人打扰，你就可以在房门上加上一把锁。

加锁确实好使，可以保证隔离性。比如串行化隔离级别就是加锁实现的。但是频繁的加锁，导致读数据时，没办法修改，修改数据时，没办法读取，大大降低了数据库性能。

那么，如何解决加锁后的性能问题的？

答案就是,MVCC多版本并发控制！它实现读取数据不用加锁，可以让读取数据同时修改。修改数据时同时可读取。

2. 什么是 MVCC？

MVCC，即Multi-Version Concurrency Control （多版本并发控制）。它是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

通俗的讲，数据库中同时存在多个版本的数据，并不是整个数据库的多个版本，而是某一条记录的多个版本同时存在，在某个事务对其进行操作的时候，需要查看这一条记录的隐藏列事务版本id，比对事务id并根据事物隔离级别去判断读取哪个版本的数据。

数据库隔离级别读已提交、可重复读都是基于MVCC实现的，相对于加锁简单粗暴的方式，它用更好的方式去处理读写冲突，能有效提高数据库并发性能。

3. MVCC实现的关键知识点

3.1 事务版本号

事务每次开启前，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。这就是事务版本号。

3.2 隐式字段

对于InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer，如果表中没有主键和非NULL唯一键时，则还会有第三个隐藏的主键列row_id。

列名是否必须描述

row_id	否	单调递增的行ID，不是必需的，占用6个字节。
trx_id	是	记录操作该数据事务的事务ID
roll_pointer	是	这个隐藏列就相当于一个回滚指针，指向回滚段的undo日志

3.3 undo log

undo log，回滚日志，用于记录数据被修改前的信息。在表记录修改之前，会先把数据拷贝到undo log里，如果事务回滚，即可以通过undo log来还原数据。

可以这样认为，当delete一条记录时，undo log 中会记录一条对应的insert记录，当update一条记录时，它记录一条对应相反的update记录。

undo log有什么用途呢？

事务回滚时，保证原子性和一致性。

用于MVCC快照读。

3.4 版本链

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。如下：

其实，通过版本链，我们就可以看出事务版本号、表格隐藏的列和undo log它们之间的关系。我们再来小分析一下。

假设现在有一张core_user表，表里面有一条数据,id为1，名字为孙权：

现在开启一个事务A： 对core_user表执行update core_user set name ="曹操" where id=1,会进行如下流程操作

• 首先获得一个事务ID=100

• 把core_user表修改前的数据,拷贝到undo log

• 修改core_user表中，id=1的数据，名字改为曹操

• 把修改后的数据事务Id=101改成当前事务版本号，并把roll_pointer指向undo log数据地址。

  

3.5 快照读和当前读

快照读：读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读,如：

select * from core_user where id > 2;

当前读：读取的是记录数据的最新版本，显式加锁的都是当前读

select * from core_user where id > 2 for update;

3.6 Read View

• Read View是什么呢？它就是事务执行SQL语句时，产生的读视图。实际上在innodb中，每个SQL语句执行前都会得到一个Read View。
• Read View有什么用呢？它主要是用来做可见性判断的，即判断当前事务可见哪个版本的数据

Read View是如何保证可见性判断的呢？我们先看看Read view 的几个重要属性

• m_ids:当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List，[min_limit_id, max_limit_id)
• min_limit_id:表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。
• max_limit_id:表示生成ReadView时，系统中应该分配给下一个事务的id值，即即m_ids中的最大值+1
• creator_trx_id: 创建当前read view的事务ID

Read view 匹配条件规则如下：

如果新的数据事务ID trx_id < min_limit_id，表明生成该版本的事务在生成Read View前，已经提交(因为事务ID是递增的)，所以该版本可以被当前事务访问。

如果trx_id>= max_limit_id，表明生成该版本的事务在生成ReadView后才生成，所以该版本不可以被当前事务访问。

如果 min_limit_id =<trx_id< max_limit_id,需要分3种情况讨论

（1）如果m_ids包含trx_id,则代表Read View生成时刻，这个事务还未提交，但是如果数据的trx_id等于creator_trx_id的话，表明数据是自己生成的，因此是可见的。

（2）如果m_ids包含trx_id，并且trx_id不等于creator_trx_id，则Read View生成时，事务未提交，并且不是自己生产的，所以当前事务也是看不见的；

（3）如果m_ids不包含trx_id，则说明你这个事务在Read View生成之前就已经提交了，修改的结果，当前事务是能看见的。

4. MVCC实现原理分析

隔离级别设置为读已提交（RC），事务A和事务B同时对core_user表进行查询和修改操作。

事务A: select * fom core_user where id=1事务B: update core_user set name =”曹操”// 事务A两次读到的数据不一样，事务A会读到其他事务提交的数据，不能读到其他事务未提交的数据。

执行流程如下：

最后事务A查询到的结果是，name=曹操的记录，我们基于MVCC，来分析一下执行流程：

(1) A开启事务，首先得到一个事务ID为100

(2) B开启事务，得到事务ID为101

(3) 事务A生成一个Read View，read view对应的值如下

变量值

m_ids（未提交的事务ID）	100，101
max_limit_id（下一个应该分配的事务ID）	102
min_limit_id（当前read view中活跃的未提交事务的最小值）	100
creator_trx_id（创建该read view的事务ID）	100

然后回到版本链：开始从版本链中挑选可见的记录：

由图可以看出，最新版本的列name的内容是孙权，该版本的trx_id值为100。开始执行read view可见性规则校验：

min_limit_id(100)=<trx_id（100）<102;creator_trx_id = trx_id =100;

由此可得，trx_id=100的这个记录，当前事务是可见的。所以查到是name为孙权的记录。

(4) 事务B进行修改操作，把名字改为曹操。把原数据拷贝到undo log,然后对数据进行修改，标记事务ID和上一个数据版本在undo log的地址。

(5) 提交事务

(6) 事务A再次执行查询操作，新生成一个Read View，Read View对应的值如下

变量值

m_ids（未提交的事务ID）	100
max_limit_id（下一个应当分配的事务ID）	102
min_limit_id（最小的事务ID）	100
creator_trx_id（创建read view的事务ID）	100

然后再次回到版本链：从版本链中挑选可见的记录：

从图可得，最新版本的列name的内容是曹操，该版本的trx_id值为101。开始执行Read View可见性规则校验：

min_limit_id(100)=<trx_id（101）<max_limit_id（102);但是,trx_id=101，不属于m_ids集合，说明101这个事务已经提交了，当前事务可见

因此，trx_id=101这个记录，对于当前事务是可见的。所以SQL查询到的是name为曹操的记录。

综上所述，在读已提交（RC）隔离级别下，同一个事务里，两个相同的查询，读取同一条记录（id=1），却返回了不同的数据（第一次查出来是孙权，第二次查出来是曹操那条记录），因此RC隔离级别，存在不可重复读并发问题。

4.3 可重复读（RR）隔离级别，解决不可重复读问题的分析

在RR隔离级别下，是如何解决不可重复读问题的呢？我们一起再来看下，

还是4.2小节那个流程，还是这个事务A和事务B，如下：

4.3.1 不同隔离级别下，Read view的工作方式不同

实际上，各种事务隔离级别下的Read view工作方式，是不一样的，RR可以解决不可重复读问题，就是跟Read view工作方式有关。

• 在读已提交（RC）隔离级别下，同一个事务里面，每一次查询都会产生一个新的Read View副本，这样就可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）。

begin

select * from core_user where id =1	生成一个Read View
/	/
/	/
select * from core_user where id =1	生成一个Read View

• 在可重复读（RR）隔离级别下，一个事务里只会获取一次read view，都是副本共用的，从而保证每次查询的数据都是一样的。

begin

select * from core_user where id =1	生成一个Read View
/
/
select * from core_user where id =1	共用一个Read View副本

4.3.2 实例分析

我们穿越下，回到刚4.2的例子，然后执行第2个查询的时候：

事务A再次执行查询操作，复用老的Read View副本，Read View对应的值如下

变量值

m_ids	100，101
max_limit_id	102
min_limit_id	100
creator_trx_id	100

然后再次回到版本链：从版本链中挑选可见的记录：

从图可得，最新版本的列name的内容是曹操，该版本的trx_id值为101。开始执行read view可见性规则校验：

min_limit_id(100)=<trx_id（101）<max_limit_id（102);因为m_ids{100,101}包含trx_id（101），并且creator_trx_id (100) 不等于trx_id（101）

所以，trx_id=101这个记录，对于当前事务是不可见的。这时候呢，版本链roll_pointer跳到下一个版本，trx_id=100这个记录，再次校验是否可见：

min_limit_id(100)=<trx_id（100）< max_limit_id（102);因为m_ids{100,101}包含trx_id（100），并且creator_trx_id (100) 等于trx_id（100）

所以，trx_id=100这个记录，对于当前事务是可见的。即在可重复读（RR）隔离级别下，复用老的Read View副本，解决了不可重复读的问题。

4.4 MVCC是否解决了幻读问题呢？

对于幻读来说，存在当前读和快照读的情况：

RR 隔离级别下为了解决幻读问题：快照读依靠MVCC控制，当前读通过间隙锁解决；

间隙锁和行锁合称 Next-Key Locks，每个 Next-Key Locks 是前开后闭区间；

间隙锁的引入，可能会导致同样语句锁住更大的范围，影响并发度。

作者：捡田螺的小男孩

链接：https://juejin.cn/post/7016165148020703246

来源：稀土掘金

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

MySQL锁篇

什么是锁？MySQL 中提供了几类锁？

锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能够正常运行。

排他锁（写锁），共享锁（读锁）
乐观锁，悲观锁
全局锁，表锁，行锁，页锁

什么是死锁？

死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的过程称为死锁。

如何处理死锁？

对待死锁常见的两种策略：

• 设置超时时间

• 发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。

如何避免死锁？

使用 SELECT … FOR UPDATE 语句来获取必要的锁。

什么是全局锁？它的应用场景有哪些？

全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份。 这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞。

什么是共享锁？

共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。

什么是排它锁？

排他锁 exclusive lock（也叫 writer lock）又称写锁。

若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

排它锁是悲观锁的一种实现，在上面悲观锁也介绍过。

使用全局锁会导致什么问题？

如果在主库备份，在备份期间不能更新，业务停摆，所以更新业务会处于等待状态。

如果在从库备份，在备份期间不能执行主库同步的 binlog，导致主从延迟。

如果使用全局锁进行逻辑备份就会让整个库成为只读状态，幸好官方推出了一个逻辑备份工具 MySQLdump 来解决了这个问题，只需要在使用 MySQLdump 时，使用参数 -single-transaction 就会在导入数据之前启动一个事务来保证数据的一致性，并且这个过程是支持数据更新操作的。

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

顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。正因为如此，悲观锁需要耗费较多的时间，另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。

说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

乐观锁是用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version 字段来实现。当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加 1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据。

乐观锁有什么优点和缺点？

因为没有加锁所以乐观锁的优点就是执行性能高。它的缺点就是有可能产生 ABA 的问题，ABA 问题指的是有一个变量 V 初次读取的时候是 A 值，并且在准备赋值的时候检查到它仍然是 A 值，会误以为没有被修改会正常的执行修改操作，实际上这段时间它的值可能被改了其他值，之后又改回为 A 值，这个问题被称为 ABA 问题。

⭐InnoDB 存储引擎有几种锁算法？

• Record Lock — 单个行记录上的锁；

• Gap Lock — 间隙锁，锁定一个范围，不包括记录本身；

• Next-Key Lock（包含行锁和间隙锁） — 锁定一个范围，包括记录本身。

InnoDB 如何实现行锁？

共享锁：in share mode

排他锁：for update

优化锁方面你有什么建议？

• 尽量使用较低的隔离级别。
• 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会。
• 选择合理的事务大小，小事务发生锁冲突的几率也更小。
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
• 不要申请超过实际需要的锁级别。
• 除非必须，查询时不要显示加锁。 MySQL 的 MVCC 可以实现事务中的查询不用加锁，优化事务性能；MVCC 只在 COMMITTED READ（读提交）和 REPEATABLE READ（可重复读）两种隔离级别下工作。
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

MySQL 中有哪几种锁？

1、表级锁：开销小， 加锁快； 不会出现死锁； 锁定粒度大， 发生锁冲突的概率最高， 并发度最低。

2、行级锁：开销大， 加锁慢； 会出现死锁； 锁定粒度最小， 发生锁冲突的概率最低， 并发度也最高。

3、页锁：开销和加锁时间界于表锁和行锁之间； 会出现死锁； 锁定粒度界于表锁和行锁之间， 并发度一般。

锁机制与InnoDB锁算法

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

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

• 行级锁：MySQL中锁定粒度最小的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

锁的优化策略

1、读写分离

2、分段加锁

3、减少锁持有的时间

多个线程尽量以相同的顺序去获取资源

不能将锁的粒度过于细化， 不然可能会出现线程的加锁和释放次数过多， 反而效率不如一次加一把大锁。

MySQL日志篇

MySQL 有哪些重要的日志文件？

MySQL 中的重要日志分为以下几个：

**① 错误日志：**用来记录 MySQL 服务器运行过程中的错误信息

② 查询日志：查询日志在 MySQL 中被称为 general log(通用日志)，查询日志里的内容不要被“查询日志”误导，认为里面只存储 select 语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，具体原因如下:

• insert 查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错；

• update 时也会查询因为更新的时候很可能会更新某一块数据；

• delete 查询，只删除符合条件的数据；

因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。

查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的 select 语句对应的数据是否能够被缓存，同时也可以帮助我们分析问题，所以，我们可以根据自己的实际情况来决定是否开启查询日志。

③ 慢日志：慢查询会导致 CPU、IOPS、内存消耗过高，当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。使用如下命令记录当前数据库的慢查询语句：

set global slowquerylog='ON';

使用 set global slowquerylog=‘ON’ 开启慢查询日志，只是对当前数据库有效，如果 MySQL 数据库重启后就会失效。所以如果要永久生效，就要修改配置文件 my.cnf，设置 slowquerylog=1 并重启 MySQL 服务器。

④ redo log（重做日志）：为了最大程度的避免数据写入时，因为 IO 瓶颈造成的性能问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。

⑤ undo log（回滚日志）：用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。 undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。

⑥ bin log（二进制日志）：是一个二进制文件，主要记录了对 MySQL 数据库执行更改的所有操作，比如，CREATE、ALTER TABLE 等，以及表数据修改，比如，INSERT、UPDATE、DELETE 的所有操作，不记录 SELECT、SHOW 等那些不修改数据或数据库结构的 SQL 语句。

binlog 的作用如下：

• 恢复（recovery）：某些数据的恢复需要二进制日志。比如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复；

• 复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为 slave 或者 standby）与一台 MySQL 数据库（一般称为 master 或者 primary）进行实时同步；

• 审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

除了上面介绍的几个作用外，binlog 对于事务存储引擎的崩溃恢复也有非常重要的作用，在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL 将采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内部的状态可能为 prepared（准备状态）和 commit（提交状态）两种，对于 prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 binlog，如果事务在 binlog 中存在，那么将其提交；如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。

binlog 默认是关闭状态，可以在 MySQL 配置文件（my.cnf）中通过配置参数 log-bin = [base-name] 开启记录 binlog 日志，如果不指定 base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，比如：mysql-bin.000001，所在目录为数据库所在目录（datadir）。

通过以下命令来查询 binlog 是否开启：

show variables like 'log_%';

binlog 格式分为: STATEMENT、ROW 和 MIXED 三种：

• STATEMENT 格式的 binlog 记录的是数据库上执行的原生 SQL 语句。这种格式的优点是简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。并且通过 mysqlbinlog 工具容易读懂其中的内容。缺点就是同一条 SQL 在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的 SQL。比如，使用 INSERT INTO TB1 VALUE(CUURENT_DATE()) 这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化，存储过程和触发器在使用基于语句的复制模式时也可能存在问题；另外一个问题就是基于语句的复制必须是串行化的，比如：InnoDB 的 next-key 锁等，并不是所有的存储引擎都支持基于语句的复制；

• ROW 格式是从 MySQL 5.1 开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据，一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的 SQL 构造、触发器、存储过程等都能正确执行；它的缺点就是二进制日志可能会很大，而且不直观，所以，你不能使用 mysqlbinlog 来查看二进制日志，也无法通过看二进制日志判断当前执行到那一条 SQL 语句。现在对于 ROW 格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点，并且由于 ROW 格式记录行数据，所以可以基于这种模式做一些 DBA 工具，比如数据恢复，不同数据库之间数据同步等；

• MIXED 也是 MySQL 默认使用的二进制日志记录方式，但 MIXED 格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到 UUID()、USER()、CURRENTUSER()、ROWCOUNT() 等无法确定的函数。

redo log 和 binlog 有什么区别？

redo log（重做日志）和 binlog（归档日志）都是 MySQL 的重要的日志，它们的区别如下：

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”。

• binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统，也就是 redo log 来实现 crash-safe 能力。

什么是 crash-safe？

crash-safe 是指发生宕机等意外情况下，服务器重启后数据依然不会丢失的情况。

什么是脏页和干净页？

MySQL 为了操作的性能优化，会把数据更新先放入内存中，之后再统一更新到磁盘。当内存数据和磁盘数据内容不一致的时候，我们称这个内存页为脏页；内存数据写到磁盘后，内存的数据和磁盘上的内容就一致了，我们称为“干净页”。

什么情况下会引发 MySQL 刷脏页（flush）的操作？

• 内存写满了，这个时候就会引发 flush 操作，对应到 InnoDB 就是 redo log 写满了；

• 系统的内存不足了，当需要新的内存页的时候，就会淘汰一些内存页，如果淘汰的是脏页这个时候就会触发 flush 操作；

• 系统空闲的时候，MySQL 会同步内存中的数据到磁盘也会触发 flush 操作；

• MySQL 服务关闭的时候也会刷脏页，触发 flush 操作。

MySQL 刷脏页的速度很慢可能是什么原因？

在 MySQL 中单独刷一个脏页的速度是很快的，如果发现刷脏页的速度很慢，说明触发了 MySQL 刷脏页的“连坐”机制，MySQL 的“连坐”机制是指当 MySQL 刷脏页的时候如果发现相邻的数据页也是脏页也会一起刷掉，而这个动作可以一直蔓延下去，这就是导致 MySQL 刷脏页慢的原因了。

如何控制 MySQL 只刷新当前脏页？

在 InnoDB 中设置 innodbflushneighbors 这个参数的值为 0，来规定 MySQL 只刷当前脏页，MySQL 8 这个值默认是 0。

MySQL 的 WAL 技术是解决什么问题的？

A.防止误删除，找回数据用的 B.容灾恢复，为了还原异常数据用的 C.事务处理，为了数据库的稳定性 D.为了降低 IO 成本 答：D 题目解析：WAL 技术的全称是 Write Ahead Logging（中文：预写式日志），是先写日志，再写磁盘的方式，因为每次更新都写磁盘的话 IO 成本很高，所以才有了 WAL 技术。

为什么有时候会感觉 MySQL 偶尔卡一下？

如果偶尔感觉 MySQL 卡一下，可能是 MySQL 正在刷脏页，正在把内存中的更新操作刷到磁盘中。

redo log 和 binlog 是怎么关联的?

它们有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

• 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；

• 如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务。

MySQL 怎么知道 binlog 是完整的?

• statement 格式的 binlog，完整的标识是最后有 COMMIT 关键字。

• row 格式的 binlog，完整的标识是最后会有一个 XID event 关键字。

MySQL 中可不可以只要 binlog，不要 redo log？

不可以，binlog 没有崩溃恢复的能力。

MySQL 中可不可以只要 redo log，不要 binlog？

不可以，原因有以下两个：

• redo log 是循环写不能保证所有的历史数据，这些历史数据只能在 binlog 中找到；

• binlog 是高可用的基础，高可用的实现原理就是 binlog 复制。

为什么 binlog cache 是每个线程自己维护的，而 redo log buffer 是全局共用的？

因为 binlog 是不能“被打断的”，一个事务的 binlog 必须连续写，因此要整个事务完成后，再一起写到文件里。而 redo log 并没有这个要求，中间有生成的日志可以写到 redo log buffer 中，redo log buffer 中的内容还能“搭便车”，其他事务提交的时候可以被一起写到磁盘中。

事务执行期间，还未提交，如果发生 crash，redo log 丢失，会导致主备不一致呢？

不会，因为这时候 binlog 也还在 binlog cache 里，没发给备库，crash 以后 redo log 和 binlog 都没有了，从业务角度看这个事务也没有提交，所以数据是一致的。

在 MySQL 中用什么机制来优化随机读/写磁盘对 IO 的消耗？

redo log 是用来节省随机写磁盘的 IO 消耗，而 change buffer 主要是节省随机读磁盘的 IO 消耗。redo log 会把 MySQL 的更新操作先记录到内存中，之后再统一更新到磁盘，而 change buffer 也是把关键查询数据先加载到内存中，以便优化 MySQL 的查询。

以下说法错误的是？

A.redo log 是 InnoDB 引擎特有的，它的固定大小的 B.redo log 日志是不全的，只有最新的一些日志，这和它的内存大小有关 C.redo log 可以保证数据库异常重启之后，数据不丢失 D.binlog 是 MySQL 自带的日志，它能保证数据库异常重启之后，数据不丢失 答：D 题目解析：binlog 是 MySQL 自带的日志，但它并不能保证数据库异常重启之后数据不丢失。

以下说法正确的是？

A.redo log 日志是追加写的，后面的日志并不会覆盖前面的日志 B.binlog 日志是追加写的，后面的日志并不会覆盖前面的日志 C.redo log 和 binlog 日志都是追加写的，后面的日志并不会覆盖前面的日志 D.以上说法都正确 答：B 题目解析：binlog 日志是追加写的，后面的日志并不会覆盖前面的日志，redo log 日志是固定大小的，后面的日志会覆盖前面的日志。

有没有办法把 MySQL 的数据恢复到过去某个指定的时间节点？怎么恢复？

可以恢复，只要你备份了这段时间的所有 binlog，同时做了全量数据库的定期备份，比如，一天一备，或者三天一备，这取决于你们的备份策略，这个时候你就可以把之前备份的数据库先还原到测试库，从备份的时间点开始，将备份的 binlog 依次取出来，重放到你要恢复数据的那个时刻，这个时候就完成了数据到指定节点的恢复。比如，今天早上 9 点的时候，你想把数据恢复成今天早上 6:00:00 的状态，这个时候你可以先取出今天凌晨（00:01:59）备份的数据库文件，还原到测试库，再从 binlog 文件中依次取出 00:01:59 之后的操作信息，重放到 6:00:00 这个时刻，这就完成了数据库的还原。

MySQL命令与内置函数

如何用命令行方式连接 MySQL 数据库？

使用 mysql -u用户名 -p密码; 输入用户名和密码就可以正常进入数据库连接了，实例如下：

mysql -uroot -p123456;

其中，用户名为 root，密码为 123456。

关于命令 mysql -h 127.0.0.1 -uroot -P 3307 -p3307 以下说法错误的是？

A.-h 和 -P 可以省略
B.-u 和用户名之间不能有空格
C.-p 和密码之间不能用空格
D.小写 -p 对应的是用户密码，大写 -P 对应的是 MySQL 服务器的端口

答：B 题目解析：-p 和密码之间不能用空格，否则空格会被识别为密码的一部分，提示密码错误。-u 和用户名之间可以有空格。

如何创建用户？并给用户授权？

创建用户使用关键字：CREATE USER ，授权使用关键字： GRANT ，具体实现脚本如下：

-- 创建用户 laowangcreate user 'laowang'@'localhost' identified by '123456';-- 授权 test 数据库给 laowanggrant all on test.* to 'laowang'@'localhost'

如何修改 MySQL 密码？

使用如下命令，修改密码：

mysqladmin -u用户名 -p旧密码 password 新密码;

注意：刚开始 root 没有密码，所以 -p 旧密码一项就可以省略了。

如何使用 SQL 创建数据库，并设置数据库的编码格式？

创建数据库可使用关键字： CREATE DATABASE ，设置编码格式使用关键字： CHARSET ，具体 SQL 如下：

create database learndb default charset utf8 collate utf8_general_ci;

如何修改数据库、表的编码格式？

使用 alter 关键字设置库或表的编码格式即可，具体代码如下：

mysql> alter database dbname default character set utf8; mysql> alter table t default character set utf8;

如何使用 SQL 创建表？

创建表的 SQL 如下：

create table t( tid int not null autoincrement, tname char(50) not null, tage int null default 18, primary key(t_id) )engine=innodb;

其中：

• auto_increment：表示自增；

• primary key：用于指定主键；

• engine：用于指定表的引擎。

在 MySQL 命令行中如何查看表结构信息？

使用 desc 表名 查看表结构信息，示例信息如下：

使用 desc person; 查看表 person 的结构信息。

如何使用 SQL 查看已知表的建表脚本？

查看已知表的建表脚本，命令如下：

mysql> show create table 表名;

效果如下图所示：

如何使用 SQL 语句更新表结构？

更新表结构信息可以使用 alter table 子句，如，为表增加一列的脚本如下：alter

alter table t add name char(20)；

如果要重命名表名，使用如下命令：

rename table new_t to t;

MySQL 有哪些删除方式？有什么区别？

MySQL 有三种删除方式：

1）删除表数据：

delete from t;

2）删除数据，保留表结构：

truncate table t;

3）删数据和表结构：

drop table t;

它们的区别如下：

• delete 可以有条件的删除，也可以回滚数据，删除数据时进行两个动作：删除与备份，所以速度很慢；

• truncate 删除所有数据，无条件选择删除，不可回滚，保留表结构；

• drop：删除数据和表结构 删除速度最快。

如何开启和关闭 MySQL 服务？

使用 systemctl stop mysqld 停止 MySQL 服务，使用 systemctl start mysqld 启动 MySQL 服务。

如何查询当前 MySQL 安装的版本号？

使用 SELECT VERSION(); 可以查询当前连接的 MySQL 的版本号。

如何查看某张表的存储引擎？

可使用 show table status from db where name='t'; 查询数据库 db 中表 t 的所有信息，其中 Engine 列表示表 t 使用的存储引擎

如何查看当前数据库增删改查的执行次数统计？

使用以下命令行查看：

mysql> show global status where variablename in('comselect’,‘cominsert’,'comdelete’,'comupdate’); ±--------------±------+ | Variablename | Value | ±--------------±------+ | Comdelete | 0 | | Cominsert | 1 | | Comselect | 40 | | Comupdate | 0 | ±--------------±------+

如何查询线程连接数？

使用如下命令：

mysql> show global status like ‘threads_%’;

其中：

• Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程；

• Threads_connected：代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数；

• Threads_created：代表从最近一次服务启动，已创建线程的数量；

• Threads_running：代表当前激活的（非睡眠状态）线程数。

如何查看 MySQL 的最大连接数？能不能修改？怎么修改？

查询 MySQL 最大连接数，使用如下命令：

mysql> show variables like ‘max_connections%’;

此命令输出的结果如下：

可以修改 MySQL 的最大连接数，可以在 MySQL 的配置文件 my.cnf 里修改最大连接数，通过修改 maxconnections 的值，然后重启 MySQL 就会生效，如果 my.ini 文件中没有找到 maxconnections，可自行添加 max_connections 的设置，内容如下：

max_connections=200

CHAR_LENGTH 和 LENGTH 有什么区别？

CHARLENGTH 是字符数，而 LENGTH 是字节数。它们在不同编码下，值是不相同的，比如对于 UTF-8 编码来说，一个中文字的 LENGTH 为 1，而 CHARLENGTH 通常等于 3

UNION 和 UNION ALL 的用途是什么？有什么区别？

UNION 和 UNION ALL 都是用于合并数据集的，它们的区别如下：

• 去重：UNION 会对结果进行去重，UNION ALL 则不会进行去重操作；

• 排序：UNION 会对结果根据字段进行排序，而 UNION ALL 则不会进行排序；

• 性能：UNION ALL 的性能要高于 UNION。

以下关于 WHERE 和 HAVING 说法正确的是？

A.任何情况 WHERE 和 HAVING 都可以相互替代
B.GROUP BY 前后都可以使用 WHERE
C.使用 SELECT X FROM T HAVING Y>20 查询报错
D.使用 SELECT X FROM T WHERE Y>20 查询报错

答：C，HAVING 非报错用法是 SELECT X,Y FROM T HAVING Y>20 。

空值和 NULL 的区别是什么？

空值表示字段的值为空，而 NULL 则表示字段没有值，它们的区别如下：

• 空值不占用空间，NULL 值是未知的占用空间；

• 空值判断使用 ='' 或 <>'' 来判断，NULL 值使用 IS NULL 或 IS NOT NULL 来判断；

• 使用 COUNT 统计某字段时，如果是 NULL 则会忽略不统计，而空值则会算入统计之内。

比如，其中字段 name 有两个 NULL 值和一个空值，查询结果如图：

MySQL 的常用函数有哪些？

• sum(field) – 求某个字段的和值；

• count(*) – 查询总条数；

• min(field) – 某列中最小的值；

• max(field) – 某列中最大的值；

• avg(field) – 求平均数；

• current_date() – 获取当前日期；

• now() – 获取当前日期和时间；

• concat(a, b) – 连接两个字符串值以创建单个字符串输出；

• datediff(a, b) – 确定两个日期之间的差异，通常用于计算年龄。

MySQL性能优化

MySQL 性能指标都有哪些？如何得到这些指标？

MySQL 的性能指标如下：

① TPS（Transaction Per Second）每秒事务数，即数据库每秒执行的事务数。

MySQL 本身没有直接提供 TPS 参数值，如果我们想要获得 TPS 的值，只有我们自己计算了，可以根据 MySQL 数据库提供的状态变量，来计算 TPS。

需要使用的参数：

• Com_commit ：表示提交次数，通过命令 show global status like 'Com_commit'; 获取；

• Com_rollback：表示回滚次数，通过命令 show global status like 'Com_rollback'; 获取。

我们定义第一次获取的 Comcommit 的值与 Comrollback 值的和为 c_r1，时间为 t1；

第二次获取的 Comcommit 的值与 Comrollback 值的和为 cr2，时间为 t2，t1 与 t2 单位为秒。 那么 TPS = ( cr2 - c_r1 ) / ( t2 - t1 ) 算出来的就是该 MySQL 实例在 t1 与 t2 生命周期之间的平均 TPS。

② QPS（Query Per Second）每秒请求次数，也就是数据库每秒执行的 SQL 数量，包含 INSERT、SELECT、UPDATE、DELETE 等。 QPS = Queries / Seconds Queries 是系统状态值—总查询次数，可以通过 show status like 'queries'; 查询得出

Seconds 是监控的时间区间，单位为秒。 比如，采样 10 秒内的查询次数，那么先查询一次 Queries 值（Q1），等待 10 秒，再查询一次 Queries 值（Q2），那么 QPS 就可以通过，如下公式获得：

QPS = (Q2 - Q1) / 10

③ IOPS（Input/Output Operations per Second）每秒处理的 I/O 请求次数。

IOPS 是判断磁盘 I/O 能力的指标之一，一般来讲 IOPS 指标越高，那么单位时间内能够响应的请求自然也就越多。理论上讲，只要系统实际的请求数低于 IOPS 的能力，就相当于每一个请求都能得到即时响应，那么 I/O 就不会是瓶颈了。

注意：IOPS 与磁盘吞吐量不一样，吞吐量是指单位时间内可以成功传输的数据数量。

可以使用 iostat 命令，查看磁盘的 IOPS，命令如下：

yum install sysstat iostat -dx 1 10

执行效果如下图所示：

IOPS = r/s + w/s 其中：

• r/s：代表每秒读了多少次；

• w/s：代表每秒写了多少次。

什么是慢查询？

慢查询是 MySQL 中提供的一种慢查询日志，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 longquerytime 值的 SQL，则会被记录到慢查询日志中。 longquerytime 的默认值为 10，意思是运行 10s 以上的语句。默认情况下，MySQL 数据库并不启动慢查询日志，需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

使用 mysql> show variables like '%slow_query_log%'; 来查询慢查询日志是否开启

如何开启慢查询日志？

开启慢查询日志，可以使用如下 MySQL 命令：

mysql> set global slowquerylog=1

不过这种设置方式，只对当前数据库生效，如果 MySQL 重启也会失效，如果要永久生效，就必须修改 MySQL 的配置文件 my.cnf，配置如下：

slowquerylog =1 slowquerylogfile=/tmp/mysqlslow.log

如何定位慢查询？

使用 MySQL 中的 explain 分析执行语句，比如：

explain select * from t where id=5;

如下图所示：

其中：

• id — 选择标识符。id越大优先级越高，越先被执行。

• select_type — 表示查询的类型。

• table — 输出结果集的表

• partitions — 匹配的分区

• type — 表示表的连接类型

• possible_keys — 表示查询时，可能使用的索引

• key — 表示实际使用的索引

• key_len — 索引字段的长度

• ref— 列与索引的比较

• rows — 大概估算的行数

• filtered — 按表条件过滤的行百分比

• Extra — 执行情况的描述和说明

其中最重要的就是 type 字段，type 值类型如下：

• all — 扫描全表数据

• index — 遍历索引

• range — 索引范围查找

• index_subquery — 在子查询中使用 ref

• uniquesubquery — 在子查询中使用 eq*ref

• refornull — 对 null 进行索引的优化的 ref

• fulltext — 使用全文索引

• ref — 使用非唯一索引查找数据

• eq_ref — 在 join 查询中使用主键或唯一索引关联

• const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点

⭐MySQL 的优化手段都有哪些？

MySQL 的常见的优化手段有以下五种：

① 查询优化

• 避免 SELECT *，只查询需要的字段。

• 小表驱动大表，即小的数据集驱动大的数据集，比如，当 B 表的数据集小于 A 表时，用 in 优化 exist，两表执行顺序是先查 B 表，再查 A 表，查询语句：select * from A where id in (select id from B) 。

• 一些情况下，可以使用连接代替子查询，因为使用 join 时，MySQL 不会在内存中创建临时表。

② 优化索引的使用

• 尽量使用主键查询，而非其他索引，因为主键查询不会触发回表查询。

• 不做列运算，把计算都放入各个业务系统实现

• 查询语句尽可能简单，大语句拆小语句，减少锁时间

• 不使用 select * 查询

• or 查询改写成 in 查询

• 不用函数和触发器

• 避免 %xx 查询

• 少用 join 查询

• 使用同类型比较，比如 ‘123’ 和 ‘123’、123 和 123

• 尽量避免在 where 子句中使用 != 或者 <> 操作符，查询引用会放弃索引而进行全表扫描

• 避免在索引列上使用 is null 和 is not null

• 尽量使用主键查询

• 避免在 where 子句中对字段进行表达式操作

• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

③ 表结构设计优化

• 使用可以存下数据最小的数据类型。

• 使用简单的数据类型，int 要比 varchar 类型在 MySQL 处理简单。

• 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int。

• 尽可能使用 not null 定义字段，因为 null 占用 4 字节空间。

• 尽量少用 text 类型，非用不可时最好考虑分表。

• 尽量使用 timestamp，而非 datetime。

• 单表不要有太多字段，建议在 20 个字段以内。

④ 表拆分

当数据库中的数据非常大时，查询优化方案也不能解决查询速度慢的问题时，我们可以考虑拆分表，让每张表的数据量变小，从而提高查询效率。

a）垂直拆分：是指数据表列的拆分，把一张列比较多的表拆分为多张表，比如，用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中，插入数据时，使用事务确保两张表的数据一致性。 垂直拆分的原则：

• 把不常用的字段单独放在一张表；

• 把 text，blob 等大字段拆分出来放在附表中；

• 经常组合查询的列放在一张表中。

b）水平拆分：指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。

通常情况下，我们使用取模的方式来进行表的拆分，比如，一张有 400W 的用户表 users，为提高其查询效率我们把其分成 4 张表 users1，users2，users3，users4，然后通过用户 ID 取模的方法，同时查询、更新、删除也是通过取模的方法来操作。

⑤ 读写分离

一般情况下对数据库而言都是“读多写少”，换言之，数据库的压力多数是因为大量的读取数据的操作造成的，我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。

MySQL 常见读写分离方案有哪些？

MySQL 常见的读写分离方案，如下列表：

1）应用层解决方案可以通过应用层对数据源做路由来实现读写分离，比如，使用 SpringMVC + MyBatis，可以将 SQL 路由交给 Spring，通过 AOP 或者 Annotation 由代码显示的控制数据源。 优点：路由策略的扩展性和可控性较强。 缺点：需要在 Spring 中添加耦合控制代码。

2）中间件解决方案通过 MySQL 的中间件做主从集群，比如：Mysql Proxy、Amoeba、Atlas 等中间件都能符合需求。 优点：与应用层解耦。 缺点：增加一个服务维护的风险点，性能及稳定性待测试，需要支持代码强制主从和事务。

怎样保证确保备库无延迟？

通常保证主备无延迟有以下三种方法：

• 每次从库执行查询请求前，先判断 secondsbehindmaster 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求，secondsbehindmaster 参数是用来衡量主备延迟时间的长短；

• 对比位点确保主备无延迟。MasterLogFile 和 ReadMasterLogPos，表示的是读到的主库的最新位点，RelayMasterLogFile 和 ExecMasterLog_Pos，表示的是备库执行的最新位点；

• 对比 GTID 集合确保主备无延迟。AutoPosition=1 ，表示这对主备关系使用了 GTID 协议；RetrievedGtidSet，是备库收到的所有日志的 GTID 集合；ExecutedGtid_Set，是备库所有已经执行完成的 GTID 集合。

MySQL开放问题

有一个超级大表，如何优化分页查询？

超级大表的分页优化分有以下两种方式：

• 数据库层面优化：利用子查询优化超多分页场景，比如：SELECT a.* FROM 表 1 a, (select id from 表 1 where 条件 LIMIT 100000,20 ) b where a.id=b.id ，先快速定位需要获取的 id 段，然后再关联查询。MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写，
  利用子查询先快速定位需要获取的 id 段，然后再关联查询，就是对分页进行 SQL 改写的具体实现；

• 程序层面优化：可以利用缓存把查询的结果缓存起来，这样再下一次查询的时候性能就非常高了。

⭐查询长时间不返回可能是什么原因？应该如何处理？

查询速度慢的原因很多，常见如下几种：

1）查询字段没有索引或者没有触发索引查询，没有触发索引查询的情况如下：

• 以 % 开头的 like 查询

• 数据类型出现隐式转换时不会使用索引

• 不符合最左前缀原则；

• 如果查询条件有 or 分割，如果有or的字段没有索引，会引起全表查询；

• 如果 MySQL 认为使用索引会比全表查询更慢，则不会使用索引。

2）I/O 压力大，读取磁盘速度变慢，df 看硬盘

3）内存不足，free看内存（top看cpu）

4）网络速度慢

5）查询出的数据量过大，可以采用多次查询或其他的方法降低数据量

6）死锁，一般碰到这种情况的话，大概率是表被锁住了，可以使用 show processlist; 命令，看看 SQL 语句的状态，再针对不同的状态做相应的处理。

其中，当 State 列值为 Locked 时，表示被锁定。

以上问题的解决方案如下：

1）正确创建和使用索引。

2）把数据、日志、索引放到不同的 IO 设备上，减少主数据库的 IO 操作。更换 MySQL 的磁盘为固态硬盘，以提高磁盘的 IO 性能。

3）升级内存，更换更大的内存。

4）提升网速，升级带宽。

5）用 Profiler 来跟踪查询，得到查询所需的时间，找出有问题的 SQL 语句，优化 SQL。

6）查询时值返回需要的字段。

7）设置死锁的超时时间，限制和避免死锁消耗过多服务器的资源。 8）尽量少用视图，它的效率低，对视图操作比直接对表操作慢,可以用存储过程来代替视图。不要用视图嵌套，嵌套视图增加了寻找原始数据的难度。

MySQL 主从延迟的原因有哪些？

主从延迟可以根据 MySQL 提供的命令判断，比如，在从服务器使用命令： show slave status;，其中 SecondsBehindMaster 如果为 0 表示主从复制状态正常。 导致主从延迟的原因有以下几个：

• 主库有大事务处理；

• 主库做大量的增、删、改操作；

• 主库对大表进行字段新增、修改或添加索引等操作；

• 主库的从库太多，导致复制延迟。从库数量一般 3-5 个为宜，要复制的节点过多，导致复制延迟；

• 从库硬件配置比主库差，导致延迟。查看 Master 和 Slave 的配置，可能因为从库的配置过低，执行时间长，由此导致的复制延迟时间长；

• 主库读写压力大，导致复制延迟；

• 从库之间的网络延迟。主从库网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外跨公网主从复制很容易导致主从复制延迟。

如何保证数据不被误删？

保证数据不被误删的方法如下列表：

• 权限控制与分配（数据库和服务器权限）

• 避免数据库账号信息泄露，在生产环境中，业务代码不要使用明文保存数据库连接信息；

• 重要的数据库操作，通过平台型工具自动实施，减少人工操作；

• 部署延迟复制从库，万一误删除时用于数据回档，且从库设置为 read-only；

• 确认备份制度及时有效；

• 启用 SQL 审计功能，养成良好 SQL 习惯；

• 启用 sqlsafeupdates 选项，不允许没 where 条件的更新/删除；

• 将系统层的 rm 改为 mv；

• 线上不进行物理删除，改为逻辑删除（将 row data 标记为不可用）；

• 启用堡垒机，屏蔽高危 SQL；

• 降低数据库中普通账号的权限级别；

• 开启 binlog，方便追溯数据。

⭐MySQL 服务器 CPU 飙升应该如何处理？

使用 show full processlist; 查出慢查询，为了缓解数据库服务器压力，先使用 kill 命令杀掉慢查询的客户端，然后再去项目中找到执行慢的 SQL 语句进行修改和优化。

MySQL 毫无规律的异常重启，可能产生的原因是什么？该如何解决？

可能是积累的长连接导致内存占用太多，被系统强行杀掉导致的异常重启，因为在 MySQL 中长连接在执行过程中使用的临时内存对象，只有在连接断开的时候才会释放，这就会导致内存不断飙升，解决方案如下：

• 定期断开空闲的长连接；

• 如果是用的是 MySQL 5.7 以上的版本，可以定期执行 mysqlresetconnection 重新初始化连接资源，这个过程会释放之前使用的内存资源，恢复到连接刚初始化的状态。

如何实现一个高并发的系统？

这道面试题涉及的知识点比较多，主要考察的是面试者的综合技术能力。高并发系统的设计手段有很多，主要体现在以下五个方面。

1）前端优化

① 静态资源缓存：将活动页面上的所有可以静态的元素全部静态化，尽量减少动态元素；通过 CDN、浏览器缓存，来减少客户端向服务器端的数据请求。
② 禁止重复提交：用户提交之后按钮置灰，禁止重复提交。
③ 用户限流：在某一时间段内只允许用户提交一次请求，比如，采取 IP 限流。

2）中间层负载分发

可利用负载均衡，比如 nginx 等工具，可以将并发请求分配到不同的服务器，从而提高了系统处理并发的能力。 nginx 负载分发的五种方式：

① 轮询（默认） 每个请求按时间顺序逐一分配到不同的后端服务器，如果后端服务器不能正常响应，nginx 能自动剔除故障服务器。 ② 按权重（weight） 使用 weight 参数，指定轮询几率，weight 和访问比率成正比，用于后端服务器性能不均的情况，配置如下：

upstream backend { server 192.168.0.14 weight=10; server 192.168.0.15 weight=10; }

③ IP 哈希值（ip_hash） 每个请求按访问 IP 的哈希值分配，这样每个访客固定访问一个后端服务器，可以解决 session 共享的问题，配置如下：

upstream backend { ip_hash; server 192.168.0.14:88; server 192.168.0.15:80; }

④ 响应时间（fair） 按后端服务器的响应时间来分配请求，响应时间短的优先分配，配置如下：

upstream backend { fair; server server1.com; server server2.com; }

⑤ URL 哈希值（url_hash） 按访问 url 的 hash 结果来分配请求，和 IP 哈希值类似。

upstream backend { hash $request_uri; server server1.com; server server2.com; }

3）控制层（网关层）

限制同一个用户的访问频率，限制访问次数，防止多次恶意请求。

4）服务层

① 业务服务器分离：比如，将秒杀业务系统和其他业务分离，单独放在高配服务器上，可以集中资源对访问请求抗压。
② 采用 MQ（消息队列）缓存请求：MQ 具有削峰填谷的作用，可以把客户端的请求先导流到 MQ，程序在从 MQ 中进行消费（执行请求），这样可以避免短时间内大量请求，导致服务器程序无法响应的问题。
③ 利用缓存应对读请求，比如，使用 Redis 等缓存，利用 Redis 可以分担数据库很大一部分压力。

5）数据库层

① 合理使用数据库引擎 ② 合理设置事务隔离级别，合理使用事务 ③ 正确使用 SQL 语句和查询索引 ④ 合理分库分表 ⑤ 使用数据库中间件实现数据库读写分离 ⑥ 设置数据库主从读写分离

补充

查询语句不同元素（where、jion、limit、group by、having 等等）执行先后顺序？

查询中用到的关键词主要包含六个，并且他们的顺序依次为 select--from--where--group by--having--order by

其中 select 和 from 是必须的，其他关键词是可选的，这六个关键词的执行顺序 与 sql 语句的书写顺序并不是一样的，而是按照下面的顺序来执行

• from:需要从哪个数据表检索数据
• where:过滤表中数据的条件
• group by:如何将上面过滤出的数据分组
• having:对上面已经分组的数据进行过滤的条件
• select:查看结果集中的哪个列，或列的计算结果
• order by :按照什么样的顺序来查看返回的数据

from 后面的表关联，是自右向左解析 而 where 条件的解析顺序是自下而上的。

也就是说，在写 SQL 文的时候，尽量把数据量小的表放在最右边来进行关联（用小表去匹配大表），而把能筛选出小量数据的条件放在 where 语句的最左边 （用小表去匹配大表）

非关系型数据库和关系型数据库区别，优势比较？

非关系型数据库的优势

1. 性能

NOSQL 是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过 SQL 层的解析，所以性能非常高。

2. 可扩展性

同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势

1. 复杂查询

可以用 SQL 语句方便的在一个表以及多个表之间做非常复杂的数据查询。

2. 事务支持

使得对于安全性能很高的数据访问要求得以实现。

总结

对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

NOSQL 数据库慢慢开始具备 SQL 数据库的一些复杂查询功能，比如 MongoDB。

对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国，比如 Redis set nx。

数据库三范式，根据某个场景设计数据表？

• 所有字段值都是不可分解的原子值。
• 在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
• 数据表中的每一列数据都和主键直接相关，而不能间接相关。

第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

上表所示的用户信息遵循了第一范式的要求，这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。

第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。

第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

MySQL 高并发环境解决方案？

MySQL 高并发环境解决方案 分库 分表 分布式 增加二级缓存。

需求分析：互联网单位 每天大量数据读取，写入，并发性高。

• 现有解决方式：水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力。
• 集群方案：解决 DB 宕机带来的单点 DB 不能访问问题。
• 读写分离策略：极大限度提高了应用中 Read 数据的速度和并发量。无法解决高写入压力。

数据库崩溃时事务的恢复机制（REDO 日志和 UNDO 日志）？

Undo Log

Undo Log 是为了实现事务的原子性，在 MySQL 数据库 InnoDB 存储引擎中，还用了 Undo Log 来实现多版本并发控制(简称：MVCC)。

• 事务的原子性(Atomicity)事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。
• 原理Undo Log 的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为 UndoLog）。然后进行数据的修改。如果出现了错误或者用户执行了 ROLLBACK 语句，系统可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。

之所以能同时保证原子性和持久化，是因为以下特点：

• 更新数据前记录 Undo log。
• 为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。
• Undo log 必须先于数据持久化到磁盘。如果在 G,H 之间系统崩溃，undo log 是完整的， 可以用来回滚事务。
• 如果在 A-F 之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

缺陷：每个事务提交前将数据和 Undo Log 写入磁盘，这样会导致大量的磁盘 IO，因此性能很低。

如果能够将数据缓存一段时间，就能减少 IO 提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即 Redo Log。

Redo Log

• 原理和 Undo Log 相反，Redo Log 记录的是新数据的备份。在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是 Redo Log 已经持久化。系统可以根据 Redo Log 的内容，将所有数据恢复到最新的状态。

Explain 性能分析

是什么

查看执行计划：使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的。分析查询语句或是表结构的性能瓶颈。

能干嘛

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

怎么玩

Explain + SQL 语句。
Explain 执行后返回的信息：

各字段解释

id：select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。

• id 相同，执行顺序由上至下
• id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
• id 有相同也有不同：id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

id 号每个号码，表示一趟独立的查询。一个 sql 的查询趟数越少越好。

select_type：代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询，取值范围如下：

• simple：简单的 select 查询，查询中不包含子查询或者 UNION
• primary：查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
• derived：在 FROM 列表中包含的子查询被标记为 DERIVED (衍生)，MySQL 会递归执行这些子查询, 把结果放在临时表里。
• subquery：在 SELECT 或 WHERE 列表中包含了子查询
• depedent subquery：在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层
• uncacheable subquery：无法使用缓存的子查询
• union：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为：DERIVED
• union result：从 UNION 表获取结果的 SELECT

table：这个数据是基于哪张表的。

type：是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

只需要记住：system > const > eq_ref > ref > range > index > ALL 就行了，其他的不常见。

• system：表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计。
• const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量。
• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
• range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
• index：出现 index 是 sql 使用了索引但是没用索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。
• all：将遍历全表以找到匹配的行。

其他 type 如下：

• index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 关键字的 sql 中。
• ref_or_null：对于某个字段既需要过滤条件，也需要 null 值的情况下。查询优化器会选择用 ref_or_null 连接查询。
• index_subquery：利用索引来关联子查询，不再全表扫描。
• unique_subquery：该联接类型类似于 index_subquery。子查询中的唯一索引。

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引。如果为 NULL，则没有使用索引。

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len 显示的值为索引字段的最大可能长度，并非实际使用长度。如何计算 key_len？

• 先看索引上字段的类型 + 长度，比如：int=4; varchar(20)=20; char(20)=20
• 如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3，GBK 要乘 2
• varchar 这种动态字符串要加 2 个字节
• 允许为空的字段要加 1 个字节

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows：显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

Extra：其他的额外重要的信息。

• Using filesort：说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。排序字段若通过索引去访问将大大提高排序速度。
• Using temporary：使用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
• Using index：表示相应的 select 操作中使用了覆盖索引 (Covering Index)，避免访问了表的数据行，效率不错！如果同时出现 using where，表明索引被用来执行索引键值的查找；如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。
• Using where：表明使用了 where 过滤。
• Using join buffer：使用了连接缓存。
• impossible where：where 子句的值总是 false，不能用来获取任何数据。
• select tables optimized away：在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
• distinct：优化 distinct 操作，在找到第一匹配的元祖后即停止找同样值的动作。