MySQL面经汇总

所幸你是例外

已于 2022-08-11 02:11:52 修改

阅读量3.8k

点赞数 10

分类专栏：八股文文章标签：数据库 mysql 面试索引

于 2021-04-03 03:09:15 首次发布

本文链接：https://blog.csdn.net/qq100344/article/details/115410943

版权

八股文专栏收录该内容

7 篇文章 6 订阅

订阅专栏

MySQL是什么

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

MySQL存储引擎

mysql常用引擎包括：innodb、myisam、memory、merge等

MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎
Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些
Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表
MERGE：是一组MYISAM表的组合

InnoDB与MyISAM的区别

在这里插入图片描述

SQL注入是什么

SQL注入就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

SQL注入的总体思路：

寻找到SQL注入的位置
判断服务器类型和后台数据库类型
针对不同的服务器和数据库特点进行SQL注入攻击

应对方法：

使用正则表达式过滤传入的参数
使用预编译手段，绑定参数是最好的防SQL注入的方法。
参数绑定

InnoDB四大特性

插入缓存（insert buffer）

InnoDB 存储引擎设计了 Insert Buffer ，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池（Buffer pool）中，若在，则直接插入；若不在，则先放入到一个 Insert Buffer 对象中，然后再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

插入缓冲的使用需要满足以下两个条件：

索引是辅助索引；
索引不是唯一的；

二次写（double write）

doublewrite 由两部分组成，一部分为内存中的 doublewrite buffer，其大小为2MB，另一部分是磁盘上共享表空间中连续的128个页，即2个区(extent)，大小也是2M。为了解决 partial page write（部分页定入）问题，当 MySQL 将脏数据刷新到磁盘的时候，会进行以下操作：

先将脏数据复制到内存中的 doublewrite buffer；
之后通过 doublewrite buffer 再分2次，每次1MB写入到共享表空间的磁盘上（顺序写，性能很高）；
完成第二步之后，马上调用 fsync 函数，将doublewrite buffer中的脏页数据写入实际的各个表空间文件（离散写）。

自适应哈希索引（adaptive hash index）

InnoDB 会监控对表上索引的查找，如果观察到某些索引被频繁访问，索引成为热数据，建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池的 B+ 树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB 会自动根据访问的频率和模式来为某些页建立哈希索引。

预读（read ahead）

线性预读（Linear read-ahead）：线性预读方式有一个很重要的变量 innodb_read_ahead_threshold，可以控制 Innodb 执行预读操作的触发阈值。如果一个 extent 中的被顺序读取的 page 超过或者等于该参数变量时，Innodb将会异步的将下一个 extent 读取到 buffer pool中，innodb_read_ahead_threshold 可以设置为0-64（一个 extend 上限就是64页）的任何值，默认值为56，值越高，访问模式检查越严格。

随机预读（Random read-ahead）: 随机预读方式则是表示当同一个 extent 中的一些 page 在 buffer pool 中发现时，Innodb 会将该 extent 中的剩余 page 一并读到 buffer pool中，由于随机预读方式给 Innodb code 带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置 innodb_random_read_ahead 为ON。

MySQL数据类型

分类	类型名称	说明
整数类型	tinyint	小整数(8位)
	smallint	较小整数(16位)
	mediumint	中小整数(24位)
	int	整数(32位)
小数类型	float	单精度浮点数
	double	双精度浮点数
	decimal(m,d)	压缩严格的定点数
日期类型	year	YYYY 1901~2155
	time	HH:MM:SS -838:59:59~838:59:59
	date	YYYY-MM-DD 1000-01-01~9999-12-3
	datetime	YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
	timestamp	YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
文本、二进制类型	char(m)	m为0~255之间的整数
	varchar(m)	m为0~65535之间的整数
	tinyblob	允许长度0~255字节
	blob	允许长度0~65535字节
	mediumblob	允许长度0~167772150字节
	longblob	允许长度0~4294967295字节
	tinytext	允许长度0~255字节
	text	允许长度0~65535字节
	mediumtext	允许长度0~167772150字节
	longtext	允许长度0~4294967295字节
	varbinary(m)	允许长度0~m个字节的变长字节字符串
	binary(m)	允许长度0~m个字节的定长字节字符串

关联查询⭐

内连接
- 等值连接：on A.id=B.id
- 不等值连接：on A.id > B.id
- 自连接：select* from A T1 inner join A T2 on T1.id=T2.pid
外连接

left join：返回包括左表中的所有记录和右表中联结字段相等的记录。

right join：返回包括右表中的所有记录和左表中联结字段相等的记录。
联合查询

union：对多个结果集进行合并时，对记录会去重，并按字段的默认规则排序；

union all：对多个结果集进行合并时，对记录不会去重和排序。

全连接(MySql不支持)

// 通过left join和right join合并结果集的方式，模拟full join
select *from A left join B on A.id=B.id 
union
select *from A right join B ON A.id=B.id;

子查询

条件：一条SQL语句的查询结果做为另一条SQL查询语句的条件或查询结果。

嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。

varchar和char的区别

char的特点：

char是定长字符串，长度固定；
若保存的字符串长度小于char的固定长度，则会用空格填充；
char的访问速度快，但是耗费空间；(空间换时间)
char最多能存放255个字符，和编码无关；

varchar的特点：

varchar是可变长字符串，长度是可变的；
varchar保存的字符串多长，就按多长来存储；
varchar访问速度慢，但是节约空间；(时间换空间)
varchar最多能存放65535个字符；

应用场景：

char适合存储很短的字符串，或所有值都接近同一个长度。对于经常变更的数据，char 也比 varchar 更好。对于非常短的列，char在存储空间上也更有效率，例如用 char 来存储只有 Y 和 N 的值只需要一个字节，但是 varchar 需要两个字节，因为还有一个记录长度的额外字节。

datetime和timestamp的区别

datetime能保存大范围的值，从1001~9999年，精度为秒。把日期和时间封装到了一个整数中，与时区无关，使用8字节存储空间。
timestamp**只使用4字节的存储空间**，范围比datetime小，只能表示1970~2038年，并且依赖于时区。

varchar(50)中50的含义

最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。

int(20)中20的含义

是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；不影响内部存储，只是影响带 zerofill 定义的 int 时，前面补多少个 0，易于报表展示。

float和double的区别

FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

drop、delete与truncate的区别

delete和truncate只删除表的数据不删除表的结构；

delete语句是DML操作，事务提交后才会生效；若有相应的触发器(trigger)，执行时会被触发；

truncate和drop是DDL操作，操作立即生效，不能回滚，不触发触发器（trigger）

SQL 执行速度： drop > truncate > delete

	delete	truncate	drop
SQL类型	DML	DDL	DDL
是否回滚	可回滚	不可回滚	不可回滚
删除内容	删除表的一条或多条记录，表结构还在	删除表中所有记录，表结构还在	删除表，包含表的结构，数据，索引和权限
删除速度	慢	快	最快

count(字段)、count(主键)、count(1)、count(*)的区别

作用：count是一个聚合函数，对于返回的结果集它会一行行去判断，只要不为NULL，就累加1。最后返回累计值。

count(可空字段) < count(主键 id) < count(1) ≈ count(\*)

数据库的三大范式

第一范式：所有的字段都是不可在分隔的(列不可再分)

第二范式：满足1NF的前提下，表必须有一个主键列，并且所有的非主键列都必须完全依赖于主键列

第三范式：满足2NF的前提下，消除了传递依赖，也就是说所有的非主键列都直接依赖主键列，不依赖其他非主键。

视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增、删、改、查操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。相比多表查询，它使得我们获取数据更容易

事务⭐

事务：是 一组SQL语句要么执行都成功，要么执行都失败。

事务的特性

原子性：一个事务不可分割。要么都执行，要么都回滚。
一致性：一个事务提交前和提交后的数据必须保持一致。
隔离性：多个事务之间是相互隔离的，相互独立的，事务A不能干扰事务B。
持久性：事务提交后，数据会持久化存储在数据库中。

事务并发带来的问题

脏读：事务A读到了事务B未提交的数据。
不可重复读：事务A多次读数据，事务B修改数据，事务A读到了事务B修改的数据，导致两次读到的数据不一致。
幻读：事务A读取数据，事务B插入数据，事务A读取到表中原本没有的数据。

不可重复读和幻读的区别

不可重复读重点是修改。例如，多次读取一条记录发现其中基本列的值被修改；
幻读重点是新增或删除。例如，多次读取一条记录发现记录增多或减少了；

数据库的隔离级别

未提交读（Read Uncommitted）：允许读取尚未提交的数据，可能会导致脏读、不可重复读、幻读。

已提交读（Read Commmitted）：只能读取到已提交的数据。可能会导致不可重复读和幻读。Oracle数据库默认隔离级别。

可重复读（Repeated Read）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务所修改。该隔离级别还存在幻读。InnoDB默认级别。

注：SQL标准中规定的RR级别并不能消除幻读，但MySQL的RR级别可以，靠的就是间隙锁(Gap Lock)。在RR级别下，Gap锁是默认开启的，而在RC级别下，Gap锁是关闭的。

串行化（Serializable）：每次读都需要获得表级共享锁，读写相互都会阻塞。

如下表所示：

隔离级别/问题	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

MySQL的RR级别实现原理

MySQL RR 级别是通过MVCC多版本并发控制实现的。

MVCC⭐

MVCC是多版本并发控制，它 通过管理数据行的多个版本来实现数据库的并发控制。通过比较版本号来决定数据是否显示，读取数据时不需要加载也能保证事务的隔离效果。

MVCC多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞的读操作，写操作也只锁定必要的行。InnoDB在自每行记录后面保存两个隐藏列，分别是创建版本号和删除版本号。每开始一个新的事务系统版本号都会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。在RR级别下，MVCC的工作方式：

select：必须同时满足以下两个条件，才能查询到数据。
1. 只查版本号早于当前版本的数据行；
2. 行的删除版本要么未定义，要么大于当前事务版本号。
insert：为插入的每一行记录保存当前系统版本号作为创建版本号。
delete：为删除的每一行记录保存当前系统版本号作为删除版本号。
update：插入一条新记录，保存当前系统版本号作为创建版本号，同时保存当前系统版本号作为原来的数据行的删除版本号。

注：MVCC只作用于 RC（Read Committed）和RR（Repeatable Read）级别，因为 RU（Read Uncommitted）级别总是读取最新的数据版本，而不是符合当前事务版本的数据行，而 Serializable 会对所有读取的行都加锁。

MVCC 能否解决幻读

幻读场景

出现幻读的场景：

事务A第一次查询：select *from user where id < 5时查询到id=1的数据。
事务B插入了一条id=2的记录
事务A使用同样的查询语句查询第二次，这时查询到了两条数据。

快照读和当前读

快照读：生成一个事务快照（ReadView），之后都从这个快照获取数据。普通 select 语句就是快照读。

当前读：读取数据的最新版本。常见的 update/insert/delete、还有 select … for update、select … lock in share mode 都是当前读。

注：在RR级别下，快照读是通过MVCC和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。

对于快照读，因为MVCC是从ReadView读取，所以必然不会看到新插入的记录，所以是可以解决幻读问题的。

对于当前读，则MVCC是无法解决的。需要使用间隙锁(Gap Lock)或Next-Key Lock来解决。

注：MVCC只能解决快照读下的幻读问题。

InnoDB存储引擎的锁的算法

Record Lock：单个行记录上的锁。

Gap Lock：间隙锁，锁定一个范围，不包括记录本身。

Next-key Lock：record+gap 锁定一个范围，包含记录本身。

锁⭐

概述：当数据库有并发事务时，可能会产生数据的不一致，这时就需要锁机制来保证访问的次序。

隔离级别与锁的关系

RU级别下，读操作不需要加共享锁，这样就不会与被修改数据上的排它锁冲突。
RC级别下，读操作需要加共享锁，在语句执行完后释放共享锁。
RR级别下，读操作需要加共享锁，只有在事务提交后才释放共享锁。
Serializable级别下，锁定整个范围的记录，并一直持有锁，直到事务提交。

行锁

操作时只锁住某一行，不能其它行有影响。行锁是一种排他锁（写锁），防止其他事务修改当前事务操作的数据。InnoDB默认的锁机制。

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

表锁

操作某一条记录会锁住整张表。MyISAM默认的锁机制。

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

页锁

操作时锁住一页数据（16kb）；

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

读写锁

在处理并发读或写时，通过实现一个由两种类型组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁（读锁）和排它锁（写锁）。

读锁是共享的，相互不阻塞，多个事务在同一时刻可以同时读取同一个资源，共享同一把锁，但是只能读取不能修改。
写锁是排它的。若一个事务获取了一条数据的排他锁，则其他事务就不能获取这条数据的读锁和写锁。获取到排它锁的事务可读可写。

加锁的SQL：

select *from table_name：不加锁

update/insert/delete：加排它锁

select *from table_name where id=1 for update：id是索引，加排它锁

select *from table_name where id=1 lock in share mode：id是索引，加共享锁

死锁

当多个事务以不同顺序锁定资源，或者同时锁定同一个资源时都会产生死锁。

解决方法：

InnoDB可以自动检测到死锁，并使用一个事务回滚，另一个事务继续
设置超时等待参数 innodb_local_wait_timeout。

避免：

不同业务并发访问多个表时，编写以相同的顺序访问。
在事务中，如果要更新记录，使用排它锁。

数据库并发策略

并发控制一般有三种方法，分别是乐观锁、悲观锁、时间戳

乐观锁

乐观锁认为一个用户读数据的时候，其他用户不会去写自己所读的数据。乐观锁一般会使用版本号机制或CAS算法实现。

注：乐观锁适合多读少写的场景。

悲观锁

悲观锁在读取数据时，会先对当前读取的数据进行加锁（写锁），只有等数据读取完之后，才会释放锁（写锁），才允许其它用户修改数据；

悲观锁在修改数据时，会先对当前修改的数据进行加锁（读锁），不允许其它用户读取该数据，只有等整个事务都提交后，才会释放锁（读锁），才允许其它用户读取数据；

注：相对于乐观锁，悲观锁比较适合多写少读的场景。

时间戳

时间戳就是在数据库表中单独加1列作为时间戳列。每次读取数据时，把该字段也读出来，当写数据时，把该字段加1，提交前跟数据库的字段比较1次，如果比数据库的值大，就允许保存，否则就不允许保存

注：悲观锁中的所说的加锁，主要分为2种锁，分别是读锁（共享锁）和写锁（排它锁）

索引⭐

索引的作用

索引是一种数据结构。数据库索引是数据库管理系统中一个排序的数据结构，提高查询效率、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。索引是一个文件，它是要占据物理空间的。

优点：

可以大大加快数据的检索速度；
可以在查询过程中使用优化隐藏器，提高系统的性能；

缺点：

时间方面：创建索引和维护索引要耗费时间，具体的，当对表中的数据进行增、删、改操作时，索引也要动态的维护，会降低增、删、改的执行效率；
空间方面：索引需要占用物理空间；

索引的使用场景

频繁作为查询条件的字段应该创建索引；alter table 表名 add index(字段名);
查询中排序(order by)的字段；
查询中统计或者分组的字段；
对join语句匹配关系（on）涉及的字段建立索引能够提高效率

索引覆盖

若查询的字段都建立了索引，那么引擎会直接在索引表中查询而不会访问原始数据，否则只要有一个字段没有建立索引就会做全表扫描，这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

最左前缀原则

在创建多列索引时，要根据业务需求，where子句中使用最频繁的列放在最左边。

最左前缀原则：MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a=1 and b=2 and c>3 and d=4，若建立(a,b,c,d)顺序的索引，d是用不到索引的。若建立(a,b,d,c)顺序的索引，则可以使用到，a,b,d的顺序根据业务需要可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

索引的类型

主键索引：一个表只能有一个主键。列不允许重复，不允许为NULL；

组合索引：组合索引是在多个字段上创建的索引，需要遵守最左前缀原则，即在查询条件中，只有使用了组合索引中的第一个字段，索引才会使用；

唯一索引：列允许为NULL，但不允许重复。一个表允许多个列创建唯一索引。若是组合索引，则列值的组合必须唯一；

# 创建唯一索引 
alter table 表名 add unique(字段名);
# 创建唯一组合索引
alter table 表名 add unique(字段名1,字段名2,...);

普通索引：基本索引类型，没有唯一性的限制，允许值为NULL；

# 创建普通索引
alter table 表名 add index 索引名(字段名);
# 创建组合索引
alter table 表名 add index 索引名(字段名1,字段名2,...)

全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较（InnoDB中不支持使用全文索引）

# 创建全文索引
alter table 表名 add fulltext(列字段);

索引的数据结构

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；

B树索引

B-Tree 意味着所有的值都是顺序存储的，并且每个叶子页到根的距离相同。B-Tree 索引能够加快访问数据的速度，存储引擎不再需要进行全表扫描来获取数据，而是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。叶子节点的指针指向的是被索引的数据，而不是其他节点页。

限制：

必须按照索引的最左列开始查找；
不能跳过索引的列，例如索引为（id,name,sex)，不能只使用id和sex而跳过name列;
如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引；

B+tree性质：

n棵子tree的节点包含n个关键字，用来保存数据的索引。

所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

B+ 树中，数据对象的插入和删除仅在叶节点上进行。

B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶子节点。

B树和B+树的区别⭐

在B树中，可以将键和值存放在内部节点和叶子节点；在B+树中，内部节点都是键，没有值，叶子节点同时存放了键和值；
B树的叶子节点相互独立；B+树的叶子节点是相互连接的，形似一个链表；

为什么B+树更适合应用于数据库索引⭐

B+树更适应磁盘特性，相比B树减少了I/O读写的次数。B+树的非叶子结点只存key不存数据，因此单个页可以存储更多key，一次性读入内存需要查找的key也就更多，磁盘的I/O读取次数就相对较少。
B+树的查询效率比B树更稳定，因为数据只存在叶子结点上，所以查找效率为O(logN)；
B树非叶子结点存了数据，所有只能通过中序遍历按序遍历。B+树叶子结点间用链表链表，所以遍历所有数据只需遍历一遍叶子结点，相对于B树效率更高；

hash索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。

限制：

数据不是按照索引值顺序存储的，无法排序；
不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的；
只支持等值比较查询，不支持范围查询；

索引的原理⭐

索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。

索引的原理就是把无序的数据变成有序的查询。

索引原理：

将创建了索引的列的内容进行排序；
对排序结果生成倒排表；
在倒排表内容上拼上数据地址链；
在查询时先拿到倒排表内容，再取出数据地址链，从而拿到具体数据；

创建索引的原则

最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
频繁作为查询条件的字段才去创建索引；
定义有外键的数据列一定要建立索引；
更新频繁的字段不适合创建索引；
对于定义为text、image和bit的数据类型的列不要建立索引；
查询中很少涉及的列，重复值比较多的列不要建立索引；
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

百万级别或以上的数据如何删除

由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

先删除索引
再删除一些无用数据

InnoDB索引实现

数据文件本身就是索引文件
表数据文件本身就是按B+树组织的一个索引结构文件
聚集索引的叶子节点包含了完整的数据记录
表必须有主键且推荐使用整型的自增主键
普通索引结构的叶子节点存储的是主键值

Innodb主键索引查找流程：通过 .idb 文件找到对应的索引，索引的value就是对应的完整数据

MySQL中的索引叶子节点存放的是什么

MyISAM：主键索引和辅助索引（普通索引）的叶子节点都是存放 key 和 key 对应数据行的地址。在MyISAM中，主键索引和辅助索引没有任何区别。

InnoDB：主键索引存放的是 key 和 key 对应的数据行。辅助索引存放的是 key 和 key 对应的主键值。因此在使用辅助索引时，通常需要检索两次索引，首先检索辅助索引获得主键值，然后用主键值到主键索引中检索获得记录。

回表查询

在InnoDB中，对于主键索引，只需要跑一遍主键索引的查询就能获取叶子节点的数据。

对于普通索引，叶子节点存储的是 key + 主键值，所以还需要跑一遍主键索引的查询才能找到数据行，这就是回表查询，先定位主键值，再定位数据行。

问题：普通索引一定会出现回表查询吗？

NO，若查询SQL所要求的字段全部命中索引，那就不用进行回表查询。比如有一个user表，主键为id，name是个普通索引，执行SQL：select id,name from user where name='aitao'时，通过name的索引就可以获取到id和name数据，所以无需回表查询数据行。

聚簇索引和非聚簇索引的区别⭐

聚簇索引：将索引与数据行放在了一起，找到索引也就找到了数据。无需进行回表查询操作，效率高；

InnoDB必然会有聚簇索引，且只会存在一个。通常是主键，若没有主键，则优先选择非空的唯一索引，若唯一索引也没有，则会创建一个隐藏的 row_id 作为聚簇索引。

非聚簇索引：将索引与数据行分开，找到索引后需要通过对应的地址找到的数据行。

存储过程与函数

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

存储过程

存储过程是一组SQL语句集合，相当于Java中定义一个函数，函数体中执行的全是SQL语句。

优点

1）存储过程是预编译过的，执行效率高。

2）存储过程的代码直接存放于数据库中，通过存储过程名直接调用，减少网络通讯。

3）安全性高，执行存储过程需要有一定权限的用户。

4）存储过程可以重复使用，减少数据库开发人员的工作量。

触发器

触发器是一段能自动执行的SQL语句集合，是一种特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。触发器是当对某一个表进行update、insert、delete操作时，mysql会自动调用该表上对应的触发器。

mysql中的触发器：

Before Insert
After Insert
Before Update
After Update
Before Delete
After Delete

SQL优化

查询语句中不要使用 select *
尽量减少子查询，使用关联查询（left join,right join,inner join）替代
减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代
or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)
应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，
如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0

explain字段⭐

id：标识符
select_type：查询类型

select_type	描述
SIMPLE	不包含任何子查询或union等查询
PRIMARY	包含子查询最外层查询就显示PRIMARY
SUBQUERY	在select或where子句中包含的查询
DERIVED	from子句中包含的查询
UNION	出现在union后的查询
UNION RESULT	从union中获取结果集

table：输出结果集的表
partitions：匹配的分区
type：表的连接类型

按类型排序，从好到坏，常见的有：const > eq_ref > ref > range > index > all。

type	描述
const	通过主键或唯一键查询，并且结果只有1行（也就是用等号查询）
eq_ref	通常出现于两表关联查询时，使用主键或非空唯一键关联，并且查询条件不是主键或唯一键的等号查询
ref	通过普通索引查询，并且使用的等号查询
range	索引的范围查找（>=、<、in 等）
index	全索引扫描。
all	全表扫描

possible_keys：查询时可能使用的索引
key：实际使用的索引
key_len：使用的索引字段的长度
ref：列与索引的比较
rows：预计要检查的行数
filtered：按表条件过滤的行百分比
extra：附加信息

extra	描述
Using index	使用覆盖索引
Using where	使用了where子句来过滤结果集
Using filesort	使用文件排序。使用非索引列排序时，非常消耗性能
Using temporary	使用临时表
…	…

如何做慢SQL优化

分析原因：是查询条件没有命中索引？还是加载了不需要的字段？还是数据量太大？

优化步骤：

先用 explain 分析SQL的执行计划，查看使用索引的情况。
分析SQL语句或重写，检查是否存在一些导致索引失效的用法，是否加载了额外的数据，是否加载了许多结果中并不需要的字段。
若对语句的优化无法进行，可以考虑表中的数据量是否太大，若是，可以垂直拆分或水平拆分。

SQL生命周期

应用服务器与数据库服务器建立连接
数据库进行拿到请求SQL
解析并生成执行计划，执行
读取数据到内存并进行逻辑处理
将结果发送给客户端
关闭连接，释放资源

查询执行流程

客户端发送一条查询给服务器；
服务器先查询缓存，若命中了缓存则立即返回结果，否则进入下一阶段；
服务器进行SQL解析、预处理，再由优化器生成对象的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
最后将结果返回到客户端。

参考文章：

所幸你是例外

关注

10
点赞
踩
63

收藏

觉得还不错? 一键收藏
打赏
5
评论
MySQL面经汇总

MySQL面试汇总MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。MySQL存储引擎mysql常用引擎包括：innodb、myisam、memory、merg
复制链接

扫一扫