一、基础知识

1、如何确定MySQL是否处于运行状态

netstat -nlp | grep mysqld

systemctl status mysql

2、如何获取表内所有列的名称和类型

desc table_name

3、如何创建表和删除表

创建表：create table

删除表：drop table

4、如何创建和删除数据库

创建数据库：create database db_name

删除数据库：drop database db_name

5、如何查看当前数据库里有哪些用户

通过查询mysql.user表

6、如何查看创建的索引及索引类型等信息

show index from table_name

7、如何查看数据库版本

mysql -V

8、MySQL有哪些数据类型

（1）数值类型：

类型	大小	范围（无符号）	用途
tinyint	1字节	（0，255）	小整数值，微小
smallint	2字节	（0，65535）	大整数值，小
mediumint	3字节	（0，16777215）	大整数值，中等大小
int	4字节	（0，4294967295）	大整数值，普通大小
bigint	8字节		极大整数值，大
float	4字节		单精度浮点数值
double	8字节		双精度浮点数值
decimal			小数值

（2）日期和时间类型

date、time、year、datetime、timestamp

（3）字符串类型

类型	大小（字节）	用途
char	0~255	定长字符串
varchar	0~65535	变长字符串
tinyblob	0~255	不超过255个字符的二进制字符串
tinytext	0~255	短文本字符串
blob	0~65535	二进制形式的文本数据
text	0~65535	长文本数据
mediumblob	0~16777215	二进制形式的中等长度文本数据
mediumtext	0~16777215	中等长度文本数据
logngblog	0~4294967295	二进制形式的极大文本数据
logntext	0~4294967295	极大文本数据
enum	1~2	枚举类型
set	1~8	类似枚举类型，但set类型一次可以选取多个成员

9、如何对一张表同时进行查询和更新

update actor a
    inner join (select lastName from actor) b
    on b.lastName = a.firstName
    set a.firstName = b.lastName
;

10、如何在表的指定位置添加列

alter table tabel_name add column FIELD_NAME int after ANOTHER_FIELD_NAME;

11、查询表结构的几种方式

show columns from table_name; desc table_name;

12、如何创建B表，使B表的表结构及索引和A表一样

create table B like A;

13、如何得到表A的建表语句

show create table A;

14、如何快速复制一张表的表结构及其数据
create table B as select * from A

15、如何查看数据表的详细信息，比如存储引擎、行数、更新时间

show table status from database_name like 'table_name\G;

16、什么是间隙锁

当使用范围条件检索数据时，并请求共享锁或排它锁时，innodb会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，称为“间隙”，innodb也会对这个“间隙”加锁。这种加锁机制就是所谓的间隙锁。

在MySQL的innodb存储引擎中，如果更新操作是针对一个区间的，那么它会锁住这个区间内的所有记录，例如“update xxx where id between A and B“，那么它会锁住A到B之间的所有记录，甚至如果这个记录不存在也会被锁住，在这个时候，如果另一个连接想要插入一条记录到A和B之间，那么它就必须等到上一个事务结束。

17、什么是MySQL的套接字文件

MySQL有两种连接方式，一种是常用的TCP/IP连接方式，另一种是套接字方式。

使用套接字方式进行连接时，需要套接字文件，但只适用于MySQL和客户端在同一台服务器上。

套接字文件一般在/tmp目录下，名为mysql.sock

MySQL [(none)]> show variables like 'socket';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| socket        | /tmp/mysql.sock |
+---------------+-----------------+
1 row in set (0.02 sec)

通过套接字连接：

mysql -plhr -S /tmp/mysql.sock

18、什么是MySQL的pid文件

pid文件是MySQL实例的进程文件。当MySQL实例启动时，会将自己的进程ID写入一个文件中，该文件即为pid文件。

19、MySQL有哪几类物理文件

（1）参数文件：my.cnf
（2）日志文件：错误日志、查询日志、慢查询日志、二进制日志
（3）表文件：用来存放表结构的文件
（4）socket文件：当用unix域套接字方式进行连接时需要
（5）pid文件：MySQL实例的进程ID文件
（6）存储引擎文件：每个存储引擎都有自己的文件夹来保存各种数据，这些存储引擎真正的存储了数据和索引等数据

20、MySQL常见的存储引擎

特点	myisam	innodb	memory	archive	ndb	bdb	merge
是否默认存储引擎	5.5.8以前是	5.58开始是	否	否	否	否	否
存储限制	256TB	64TB	RAM	无	无	无	无
事务支持	否	支持	否	否	否	支持	否
锁机制	表锁	行锁	表锁	行锁	行锁	页锁	行锁
多版本并发控制	否	支持	否	否	否	否	否
支持外键	否	支持	否	否	否	否	否
内存使用	低	高	中等	低		低	低

21、innodb引擎有哪两类表空间模式

innodb存储表和索引的方式主要有两种，分别是：使用共享表空间存储和使用独立表空间存储。

22、MySQL中索引的使用原则有哪些

（1）最适合索引的列是出现在where子句中的列，或连接子句中指定的列
（2）使用唯一索引，比如身份证号、员工工号
（3）使用短索引，如果对字符串列进行索引，那么应该指定一个前缀长度
（4）索引不是越多越好，每个索引都会占用额外的磁盘空间，降低写操作的性能，且MySQL在生成执行计划时，要考虑各个索引，这也要花费时间，因此只保持所需的索引有利于查询优化
（5）为经常需要排序、分组和联合操作的字段建立索引

23、什么是覆盖索引

如果一个索引包含了所有满足查询所需要的数据，那么就称这类索引为覆盖索引（covering index）。

索引覆盖查询不需要进行回表操作，因此MySQL得以访问更少的数据，更少的IO来获取到结果集，性能大大提升。

在explain命令中，如果MySQL使用了索引覆盖查询，则Extra列将包含”Using index“字符串

24、为什么索引没有被使用

索引没有被使用有很多原因，下面列举几种常见的：

（1）如果索引列出现了隐式类型转换，则MySQL不会使用索引

举例：有一个字段telphone，char(11)。那么当条件为 where telphone = 1311234567时，将不会使用索引，而查询条件为 where telphone = ‘1311234567’ 时，将会使用到索引

（2）如果where条件中，包含有‘or’，除非or条件中的所有列都是索引列，否则MySQL不会选择索引

（3）如果MySQL估计使用全表扫描要比使用索引快，MySQL将不会使用索引

（4）如果对索引字段进行函数、算术运算或其他表达式等操作，那么MySQL将不会使用索引

25、简述MySQL中，索引、唯一索引、主键、联合索引的区别，他们对数据库有什么影响

（1）索引

索引是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。普通索引（由KEY、INDEX定义的索引）的唯一作用是加快对数据的访问速度。

（2）唯一索引

普通索引允许被索引的数据列包含重复的值，如果能确定某个数据列只包含彼此各不相同的值，那就可以为这个字段添加unique唯一索引，唯一索引可以保证数据记录的唯一性

（3）主键

一种特殊的唯一索引，在一张表中只能定义一个主键索引，但可以定义多个唯一索引。用关键字primary key来创建

（4）联合索引

索引包含多个数据列，就是联合索引

26、MySQL如何查看执行计划，执行计划中的每列的含义分别是什么

详见 - MySQL执行计划

二、基础进阶

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

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

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

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

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

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

2、MySQL 提示“不存在此列”是执行到哪个节点报出的？

此错误是执行到分析器阶段报出的，因为 MySQL 会在分析器阶段检查 SQL 语句的正确性。

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

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

查询缓存的缺点是失效太频繁导致缓存命中率比较低，任何更新表操作都会清空查询缓存，因此导致查询缓存非常容易失效。

４、如何关闭 MySQL 的查询缓存功能？

MySQL 查询缓存默认是开启的，配置 querycachetype 参数为 DEMAND（按需使用）关闭查询缓存，MySQL 8.0 之后直接删除了查询缓存的功能。

5、MySQL 的常用引擎都有哪些？

MySQL 的常用引擎有 InnoDB、MyISAM、Memory 等，从 MySQL 5.5.5 版本开始 InnoDB 就成为了默认的存储引擎。

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

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

create table student(
   id int primary key auto_increment,
   username varchar(120),
   age int
) ENGINE=Memory

７、常用的存储引擎 InnoDB 和 MyISAM 有什么区别？

InnoDB 和 MyISAM 最大的区别是 InnoDB 支持事务，而 MyISAM 不支持事务。

主要区别如下：

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

InnoDB 支持行级锁，MyISAM 不支持行级锁，只支持到表锁；

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

MyISAM 支持 FULLTEXT 类型的全文索引，InnoDB 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好；

InnoDB 主键查询性能高于 MyISAM。

8、InnoDB 有哪些特性？

（1）插入缓冲(insert buffer)：

对于非聚集索引的插入和更新，不是每一次直接插入索引页中，而是首先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入，否则，先放入一个插入缓冲区中。

好似欺骗数据库这个非聚集的索引已经插入到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引叶子节点的合并操作，这时通常能将多个插入合并到一个操作中，这就大大提高了对非聚集索引执行插入和修改操作的性能。

（2）两次写(double write)：

两次写给 InnoDB 带来的是可靠性，主要用来解决部分写失败(partial page write)。

doublewrite 由两部分组成，一部分是内存中的 doublewrite buffer ，大小为 2M，另外一部分就是物理磁盘上的共享表空间中连续的 128 个页，即两个区，大小同样为 2M。

当缓冲池的作业刷新时，并不直接写硬盘，而是通过 memcpy 函数将脏页先拷贝到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次写，每次写入 1M 到共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘。如下图所示

（3）自适应哈希索引(adaptive hash index)：

由于 InnoDB 不支持 hash 索引，但在某些情况下 hash 索引的效率很高，于是出现了 adaptive hash index 功能， InnoDB 存储引擎会监控对表上索引的查找，如果观察到建立 hash 索引可以提高性能的时候，则自动建立 hash 索引。

9、什么是独立表空间和共享表空间？它们的区别是什么？

共享表空间：指的是数据库的所有的表数据、索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。

独立表空间：每一个表都将会以独立的文件方式来进行存储。

共享表空间和独立表空间最大的区别是如果把表放在共享表空间，即使表删除了空间也不会删除，所以表依然很大，而独立表空间如果删除表就会清除空间。

10、如何设置独立表空间？

独立表空间是由参数 innodbfileper_table 控制的，把它设置成 ON 就是独立表空间了，从 MySQL 5.6.6 版本之后，这个值就默认是 ON 了。

11、表的结构信息存在哪里？

表结构定义占有的存储空间比较小，在 MySQL 8 之前，表结构的定义信息存在以 .frm 为后缀的文件里，在 MySQL 8 之后，则允许把表结构的定义信息存在系统数据表之中。

12、什么是覆盖索引？

只需扫描索引就可以得到想要的数据而无须回表。

13、什么叫回表查询？

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

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

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

15、执行一个 update 语句以后，我再去执行 hexdump 命令直接查看 ibd 文件内容，为什么没有看到数据有改变呢？

可能是因为 update 语句执行完成后，InnoDB 只保证写完了 redo log、内存，可能还没来得及将数据写到磁盘。

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

内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。

临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。

17、并发事务会带来哪些问题？

脏读、修改丢失、不可重复读、幻读

18、什么是脏读和幻读？

脏读是一个事务在处理过程中读取了另外一个事务未提交的数据；

幻读是指同一个事务内多次查询返回的结果集不一样（比如增加了或者减少了行记录）。

19、为什么会出现幻读？幻读会带来什么问题？

因为行锁只能锁定存在的行，针对新插入的操作没有限定，所以就有可能产生幻读。

幻读带来的问题如下：对行锁语义的破坏；破坏了数据一致性。

20、如何避免幻读？

MySQL 存储引擎 InnoDB 隔离级别 RR 解决了幻读问题

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

VARCHAR 和 CHAR 最大区别就是，VARCHAR 的长度是可变的，而 CHAR 是固定长度。

CHAR 比较适合长度较短的字段和固定长度的字段，如身份证号、手机号等，反之则适合使用 VARCHAR。

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

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

当然，也可以在存储为 int 类型，在存储前将 19.38 元转换为 1938 分，再存储到数据库

23、limit 10,2 的含义是什么？

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

24、now() 和 current_date() 有什么区别？

now() 返回当前时间包含日期和时分秒 2019-05-04 19:23:45

current_date() 只返回当前时间 2019-05-04

25、如何去重计算总条数？

使用 distinct 去重，使用 count 统计总条数

select count(distinct f) from t

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

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

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

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

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

delete from t where username='redis'; 

truncate table t;

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

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

regexp 和 like 的使用示例如下：

select * from person where uname like '%SQL%';

select from person where uname regexp '.SQL*.';

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

count(column) 和 count() 最大区别是统计结果可能不一致，count(column) 统计不会统计列值为 null 的数据，而 count() 则会统计所有信息，所以最终的统计结果可能会不同。

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

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增、改、查操作。视图通常是一个表或者多个表的行或列的子集。 视图创建脚本如下：

create view vname as select column_names from table_name where condition

30、视图有哪些优点？

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

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

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

31、使用 delete 误删数据怎么找回？

可以用 Flashback 工具通过闪回把数据恢复回来。

32、Flashback 恢复数据的原理是什么？

Flashback 恢复数据的原理是是修改 binlog 的内容，拿回原库重放，从而实现数据找回。