MySQL - 面试题整理

有一条select语句，平常查询很快，突然有一天查询很慢，可能的原因是？

锁冲突
索引失效，导致统计数据不真实

列举常见的关系型数据库与非关系型数据库

关系型数据库：Oracle、DB2、SQL Server、Microsoft Access、MySQL
非关系型数据库：NoSql、Cloudant、MongoDb、redis、HBase

关系型数据库　　

关系型数据库的特性：
- 关系型数据库，是指采用了关系模型来组织数据的数据库；　　
- 关系型数据库的最大特点就是事务的一致性；　　
- 简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。　　
关系型数据库的优点: 　　
- 容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；　
- 使用方便：通用的SQL语言使得操作关系型数据库非常方便；　　
- 易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；　
- 支持SQL，可用于复杂的查询。　　
关系型数据库的缺点:　　
- 为了维护一致性所付出的巨大代价就是其读写性能比较差；固定的表结构；高并发读写需求；海量数据的高效率读写；

非关系型数据库

非关系型数据库的特性：
- 使用键值对存储数据；
- 分布式；
- 一般不支持ACID特性；
- 非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。　　
非关系型数据库的优点：
- 无需经过sql层的解析，读写性能很高；
- 基于键值对，数据没有耦合性，容易扩展；
- 存储数据的格式：nosql的存储格式是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，而关系型数据库则只支持基础类型。
非关系型数据库的缺点：
- 不提供sql支持，学习和使用成本较高；
- 无事务处理，附加功能和报表等支持也不好；

MYSQL数据库常见的引擎与区别

存储引擎:数据的存储方式
InnoDB:mysql5.5版本后默认的存储引擎，支持事务，行级锁,聚集索引,支持外键
MyISAM:mysql5.5及之前的存储引擎,不支持事物/行级锁/外键,非聚集索引,表级锁
Memory：将数据存储在内存的引擎,速度快,但是断电消失
BlackHole：不存储数据,只有一个表结构,用于大数据背景下做数据分流的

简述事务及其特性

事务：保证操作的完整性;数据库保证完成一系列操作的机制，中途遇到任意操作失败会导致事务内的所有操作失败，已完成的会发生回滚。
特性：ACID，原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

简述视图

视图是一个虚拟表（只包含表结构），其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用名称即可获取结果集，可以将该结果集当做表来使用。

create view show_all as select * from class inner join student on student.cid=class.class_id;
#删
drop view show_all;
#改
alter view as ...;
#查
select * from show_all;

注意：

当虚拟表中只有一张表的数据时可以对表数据进行修改，而多表关联后无法修改数据
视图本身效率不高，且表结构发生变化时，视图需要自己去手动修改，扩展差，因此不建议使用

简述触发器

使用触发器可以定制用户在对表进行增、删、改（没有查询）操作时前后的行为

基本语法：

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

举个例子：

delimiter //
create trigger tri_after_insert after insert on student for each row
begin
    if new.student_name="alex" then
        update class set class_name="全栈17期nb" where class_name="全栈17期";
    end if;
end //
delimiter ；

注意：

触发器不能由用户主动执行，而是对表进行一定操作后（前）自动触发的
上述代码中 delimiter表示更换SQL语句结束符， new表示新插入的数据，old表示旧的数据
触发器是在数据库层面做的操作，可以在业务逻辑中实现，建议在业务逻辑中完成

删除触发器

drop trigger tri_after_insert

查看触发器

show triggers

简述存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql，也可以对外提供接口。

优点

用于替代业务逻辑写的SQL语句，实现程序与sql解耦
基于网络传输，传别名的数据量小，而直接传sql数据量大

缺点

扩展功能不方便

创建存储过程

delimiter //
create procedure p1()
BEGIN
    select * from student;
    update class set class_name="Python全栈17期" where class_name="全栈17期" ;
END //
delimiter ;

在mysql中调用：

call p1()

在python中基于pymysql调用：

cursor.callproc('p1') 
print(cursor.fetchall())

Mysql的索引种类

主键索引（聚簇索引）：主键索引的叶子节点存储的是整行数据

普通索引（非主键索引）：也被称为二级索引，叶子节点存放的是主键的值，使用普通索引查询时需要先找到主键，再通过主键索引搜索一次，此过程称为回表。（普通索引又分出一种特殊情况，唯一索引，索引字段可以为空，但是必须唯一）

联合索引：在多个字段上建立索引，只有在查询条件中使用了创建索引的最左N个字段或者最左N个字符，索引才会被使用，遵循最左前缀。

全文索引：MyISAM引擎特有的。

主键索引，唯一索引，index普通，联合主键，联合唯一，联合index

索引在什么情况下遵循最左前缀的规则

在联合索引中，从左到右，遇到一个范围,索引就失效了；或者遇到or，就失效了
在查询条件中使用了创建索引的最左N个字段或者最左N个字符时，会遵循最左原则。

Mysql中常见的函数

聚合函数：avg，sum，count，max，min
数学函数：绝对值函数：abs(x)，取模函数：mod(x,y)，随机数函数：rand() 四舍五入函数：round(x,y)
字符串函数：
- 合并字符串concat(str1,str2,str3…)。
- 比较字符串大小：strcmp(str1,str2)。
- 获取字符串字节数函数：length(str)
日期和时间函数：
- 获取当前日期时间：now()
- 从日期中选择出月份数：month(date)，monthname(date)
- 从日期中选择出周数：week(date)
- 从时间中选择出小时数：hour(time)
- 从时间中选择出分钟数：minute(time)， year()，day()
- 条件判断函数： if 处理双分支、case 处理多分支

列举创建索引但是无法命中索引的情况

如果条件中有 or ，即使其中有条件带索引也不会命中(这也是为什么尽量少用or的原因）
使用了范围
like查询是以%开头，如果是int型索引不会命中，字符型的命中 'test%' 百分号只有在右边才可以命中。
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
没有查询条件，或者查询条件没有建立索引
查询条件中，在索引列上使用函数（ + , - , * , / ）, 这种情况下需建立函数索引 - 采用 not in, not exist
B-tree 索引 is null 不会走， is not null 会走
联合索引遵循最左原则，不满足的不会命中

数据库导入导出的命令（结构和数据）

MySQL命令行导出数据库

进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录，如我输入的命令行：cd C:\Program Files\MySQL\MySQL Server 4.1\bin(或者直接将windows的环境变量path中添加该目录)
导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名，如我输入的命令行:mysqldump -u root -p news > news.sql (输入后会让你输入进入MySQL的密码)（如果导出单张表的话在数据库名后面输入表名即可）
会看到文件news.sql自动生成到bin文件下

命令行导入数据库

将要导入的.sql文件移至bin文件下，这样的路径比较方便
同上面导出的第1步
进入MySQL：mysql -u 用户名 -p。如我输入的命令行:mysql -u root -p (输入同样后会让你输入MySQL的密码)
在MySQL-Front中新建你要建的数据库，这时是空数据库，如新建一个名为news的目标数据库
输入：mysql>use 目标数据库名，如我输入的命令行:mysql>use news;
导入文件：mysql>source 导入的文件名;如我输入的命令行：mysql>source news.sql;MySQL备份和还原，都是利用mysqldump、mysql和source命令来完成的。

你了解那些数据库的优化方案

合理的建立索引
分表,降低树的高度
优化数据结构，用char代替varchar
定长字段放在前面，变长放在后面
读写分离

char和varchar的区别及varchar(50)中的50代表的含义

char：定长，效率高，一般用于固定长度的表单提交数据存储；例如：身份证号，手机号，电话，密码等
varchar：不定长，效率偏低,
总的来说：
长度的区别，char范围是0～255，varchar最长是64k，但是注意这里的64k是整个row的长度,要考虑到其它的 column，还有如果存在not null的时候也会占用一位，对不同的字符集，有效长度还不一样，比如utf8的，最多21845。

varchar 编码长度限制：
字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766;
字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。
若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。
在mql4中,50代表的是该字段可以存储的数据的最大长度是50个字节,使用utf-8存汉字时,最大只能存储6个汉字。
在mysql5中，varchar(50)代表着50个字符，最大65535个字节，也就是说使用utf-8存汉字时，最大21845个字符

1000w行数据，使用limit offset分页时，为什么越往后翻越慢？如何解决？

当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。

如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。

如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

 SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);

如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并且auto_increment

SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;

这条语句，大意如下:

SELECT * FROM users WHERE uid >=  (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10;

如果limit的offset值过大，用户也会翻页疲劳，你可以设置一个offset最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户。

limit 分页优化方法

先找出第一条数据，然后大于等于这条数据的id就是要获取的数据
- 缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性实验下
倒排表优化法，倒排表法类似建立索引，用一张表来维护页数，然后通过高效的连接得到数据
- 缺点：只适合数据数固定的情况，数据不删除，维护页表困难
反向查找优化法，当偏移超过一半记录数的时候，先用排序，这样偏移就反转了
- 缺点：order by优化比较麻烦，要增加索引，索引影响数据的修改效率，并且要知道总记录数，偏移大于数据的一半
limit限制优化法，把limit偏移量限制低于某个数。。超过这个数等于没数据，我记得alibaba的dba说过他们是这样做的
只查索引法

select * from table limit 2,1;

含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据

select * from table limit 2 offset 1;

含义是从第1条（不包括）数据开始取出2条数据，limit后面跟的是2条数据，offset后面是从第1条开始读取，即读取第2，3条。

什么是索引合并？

索引合并是把几个索引的范围扫描合并成一个索引。
索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

在使用explain对sql语句进行操作时，如果使用了索引合并，那么在输出内容的type列会显示 index_merge，key列会显示出所有使用的索引。

什么是覆盖索引

覆盖索引又可以称为索引覆盖:查询辅助索引的时候不需要进行回表操作,即你查询的内容刚好是你的索引

select id from 表名 where id=10000; #id是辅助索引
select name from 表名 where id=10000; #id是辅助索引,没有覆盖索引

就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。

简述数据库读写分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
用一句话概括，读写分离是用来解决数据库的读性能瓶颈的。
其实就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。

简述数据库分库分表（水平、垂直）

数据库分表：
把一张表按照一定的规则分解成不同的实体表。比如垂直划分和水平划分垂直切分：把不同功能，不同模块的数据分别放到不同的表中，但是如果同一个模块的数据量太大就会存在性能瓶颈水平切分：垂直切分解决不了大表的瓶颈，如果同一个功能中表的数据量过大，就要对该表进行切分，为水平切分

通俗理解：垂直切分---分不同的模块表；水平切分---分同一个模块下的多个表

分库将一堆数据放到不同的数据库中保存，上面说的都是在同一个数据库上，分库是分到不同的数据库上
垂直分表:经常查的字段放在一个表里

where 子句中有a,b,c三个查询条件，创建一个组合索引abc(a,b,c),以下哪种会命中索引？(a) (b) (c) (a,b) (b,c) (a,c) (a,b,c)

按照最左原则：

命中：(a)(a,b) (a,c) (a,b,c)
不命中：(b) (c)(b,c)

mysql下面哪些查询不会使用索引?between,like,'c%',not in,not exists,!=, <,<=,=,>,>=,in

不使用索引：> / < / >= / <= / != / like / between and

请简述项目中优化SQL语句执行效率的方法

连表查询代替子查询
尽量在where过滤
创建有效正确的索引

优化查询过程中的数据访问：
- 访问数据太多导致性能下降
- 不要使用 SELECT *，总是取出全部列，SELECT *会让优化器无法完成索引覆盖扫描的优化
- 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列
优化长难的查询语句：
- 切分查询：将一个大的查询分为多个小的相同的查询
- 一次性删除 1000万的数据要比一次删除1万，暂停一会的方案更加损耗服务器开销
优化特定类型的查询语句：
- 优化子查询（即嵌套查询）
- 尽可能使用关联查询来替代

从 delete语句中省略 where子句将产生什么后果?

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除，请在下面的选项中选择： (B)

A. delete语句将失败因为没有记录可删除
B. delete语句将从表中删除所有的记录
C. delete语句将提示用户进入删除的标准
D. delete语句将失败，因为语法错误

叙述 mysql半同步复制原理

主库在执行完客户端提交的事物后，不会立即返回给客户端,而是等至少一个从库接收并写到relaylog之后才返回客户端。

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库，这一动作就结束了，并不会验证从库是否接收完毕，这一过程，也就意味着有可能出现当主服务器或从服务器端发生故障的时候，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。
注意：半同步复制模式必须在主服务器和从服务器端同时开启，否则主服务器默认使用异步复制模式。
为了解决上述可能发生的错误，MySQL 5.5 引入了一种半同步复制模式。该模式可以确保从服务器接收完主服务器发送的binlog日志文件并写入到自己的中继日志relay log里，然后会给主服务器一个反馈，告诉主服务器已经接收完毕，这时主服务线程才返回给当前session告知操作完成。
当出现超时情况是，主服务器会暂时切换到异步复制模式，直到至少有一个从服务器从及时收到信息为止。

考虑如下表结构，写出建表语句

create table t11 (
    id int primary key auto_increment,
    name char(32) not null,
    balance float(5,2) not null
);
insert into t11(name, balance) values('A', 19.59), ('A', 29.59),('A', 198.90);
select * from t11;

mysql中怎么创建索引

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
1．ALTER TABLE
ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

2．CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义，索引名不可选。另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

索引有什么作用？有哪些分类？有什么好处和坏处?

作用：加快查询，拖慢删除/添加的速度
分类：聚集索引：辅助索引
聚集索引：
- 每张表只能有一个聚集索引
- 叶子节点直接对应数据，所以找到索引就是找到数据
- 数据的存储物理地址是按照索引顺序来存的，所以按照聚集索引列排序非常快

辅助索引(非聚集索引)：
- 每张表可以有多个辅助索引，查询速度快，但占用更多磁盘空间，影响删除和添加的效率
- 叶子节点不直接存放数据，而是存放数据的地址，所以找到叶子节点后还需再做一次IO操作
- 数据的物理地址和索引顺序无关.

为了加快查询速度根据二分法速度快的原理，产生可平衡二叉树，但是b树高度高，查询次数多，就增加了分叉，形成了b-树：

b-树：会把数据行存储在中间节点中，所以导致节点中能存储的数据太少
b+树：中间节点不存放数据.innodb，myisam都是基于b+树创建索引

什么决定树的高度?数据的量和数据的长度。
什么是索引? 把数据的某个字段按照特殊的算法计算成一个树型结构，再根据树型结构提供的指针缩小范围，找到对应的磁盘块.通过这棵树，可以将我们每次的查询范围缩小1/3，加快了我们的查询速度，这棵树就是索引。

什么是MySQL慢日志?

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

在对name做了唯一索引前提下，简述以下区别

select * from tb where name = "张开"
select * from tb where name = "张开" limit 1

如果是唯一索引的话两者本质上没有什么区别，都是查询到一条数据后就不往下查询了，但是如果不是唯一索引的前提下，第二种加limit的当查询到一条数据后就不往下执行了，而第一种还是需要继续查询

posted @ 2020-12-09 17:17 Tracydzf 阅读(132) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

MySQL - 面试题整理

有一条select语句，平常查询很快，突然有一天查询很慢，可能的原因是？

列举常见的关系型数据库与非关系型数据库

关系型数据库

非关系型数据库

MYSQL数据库常见的引擎与区别

简述事务及其特性

简述视图

简述触发器

简述存储过程

Mysql的索引种类

索引在什么情况下遵循最左前缀的规则

Mysql中常见的函数

列举创建索引但是无法命中索引的情况

数据库导入导出的命令（结构和数据）

MySQL命令行导出数据库

命令行导入数据库

你了解那些数据库的优化方案

char和varchar的区别及varchar(50)中的50代表的含义

1000w行数据，使用limit offset分页时，为什么越往后翻越慢？如何解决？

limit 分页优化方法

什么是索引合并？

什么是覆盖索引

简述数据库读写分离

简述数据库分库分表（水平、垂直）

where 子句中有a,b,c三个查询条件，创建一个组合索引abc(a,b,c),以下哪种会命中索引？(a) (b) (c) (a,b) (b,c) (a,c) (a,b,c)

mysql下面哪些查询不会使用索引?between,like,'c%',not in,not exists,!=, <,<=,=,>,>=,in

请简述项目中优化SQL语句执行效率的方法

从 delete语句中省略 where子句将产生什么后果?

叙述 mysql半同步复制原理

考虑如下表结构，写出建表语句

mysql中怎么创建索引

索引有什么作用？有哪些分类？有什么好处和坏处?

什么是MySQL慢日志?

在对name做了唯一索引前提下，简述以下区别

公告

关系型数据库　　