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

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

2. 如果表的引擎类型是 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. 唯一键冲突
2. 事务回滚

• 执行器执行插入，自增键+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 官方建议使用自增主键作为表的主键？

优点：

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

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

缺点：

1. 数据量过大，可能会超出自增长取值范围；
2. 无法满足分布式存储，分库分表的情况下无法合并表；
3. 主键有自增规律，容易被破解；

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

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

**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 位添加索引。
类似这种给某列部分信息添加索引的方式叫做前缀索引。

为什么要用前缀索引？

前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也