常见mysql笔试题

最新推荐文章于 2022-06-20 14:20:40 发布

NoFound_You

最新推荐文章于 2022-06-20 14:20:40 发布

阅读量3.8k

点赞数 1

文章标签： mysql 笔试题

1. 主键超键候选键外键

主键：

数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

超键：

在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：

是最小超键，即没有冗余元素的超键。

外键：

在一个表中存在的另一个表的主键称此表的外键。

2.数据库事务的四个特性及含义

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

3.视图的作用，视图可以更改么？

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图：create view XXX as XXXXXXXXXXXXXX;
对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

4.drop,delete与truncate的区别

drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。

（1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2）表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

（3）一般而言，drop > truncate > delete

（4）应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

（6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8） truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（9）在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12）对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

5.索引的工作原理及其种类

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

创建索引可以大大提高系统的性能。

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。

当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。 主键索引 数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。 聚集索引 在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

B-/+Tree索引的性能分析

到这里终于可以分析B-/+Tree索引的性能了。

上文说过一般使用磁盘I/O次数评价索引结构的优劣。先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

综上所述，用B-Tree作为索引结构效率是非常高的。

6.连接的种类

查询分析器中执行：
--建表table1,table2：
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,'lee'
insert into table1 select 2,'zhang'
insert into table1 select 4,'wang'
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
如表
-------------------------------------------------
table1 | table2 |
-------------------------------------------------
id name |id score |
1 lee |1 90|
2 zhang| 2 100|
4 wang| 3 70|
-------------------------------------------------

以下均在查询分析器中执行
一、外连接
1.概念：包括左向外联接、右向外联接或完整外部联接

2.左连接：left join 或 left outer join
(1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
(2)sql 语句
select * from table1 left join table2 on table1.id=table2.id
-------------结果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
------------------------------
注释：包含table1的所有子句，根据指定条件返回table2相应的字段，不符合的以null显示

3.右连接：right join 或 right outer join
(1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
(2)sql 语句
select * from table1 right join table2 on table1.id=table2.id
-------------结果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
NULLNULL370
------------------------------
注释：包含table2的所有子句，根据指定条件返回table1相应的字段，不符合的以null显示

4.完整外部联接:full join 或 full outer join
(1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
(2)sql 语句
select * from table1 full join table2 on table1.id=table2.id
-------------结果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
4wangNULLNULL
NULLNULL370
------------------------------
注释：返回左右连接的和（见上左、右连接）

二、内连接
1.概念：内联接是用比较运算符比较要联接列的值的联接

2.内连接：join 或 inner join

3.sql 语句
select * from table1 join table2 on table1.id=table2.id
-------------结果-------------
idnameidscore
------------------------------
1lee190
2zhang2100
------------------------------
注释：只返回符合条件的table1和table2的列

4.等价（与下列执行效果相同）
A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * from table1 cross join table2 where table1.id=table2.id (注：cross join后加条件只能用where,不能用on)

三、交叉连接(完全)

1.概念：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记录）

2.交叉连接：cross join (不带条件where...)

3.sql语句
select * from table1 cross join table2
-------------结果-------------
idnameidscore
------------------------------
1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370
------------------------------
注释：返回3*3=9条记录，即笛卡尔积

4.等价（与下列执行效果相同）
A:select * from table1,table2

7.数据库范式

1 第一范式（1NF）

在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。

2 第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

3 第三范式（3NF）

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）

8.数据库优化的思路

这个我借鉴了慕课上关于数据库优化的课程。

1.SQL语句优化

1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3）很多时候用 exists 代替 in 是一个好的选择
4）用Where子句替换HAVING 子句因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

2.索引优化

看上文索引

3.数据库结构优化

1）范式优化：比如消除冗余（节省空间。。） 2）反范式优化：比如适当加冗余等（减少join） 3）拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
4）拆分其实又分垂直拆分和水平拆分：案例：简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表（数据量100w，且有增长趋势）以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争不解决问题：单表中数据量增长出现的压力方案：把产品表和用户表放到一个server上订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力不解决问题：表与表之间的io争夺
方案：用户表通过性别拆分为男用户表和女用户表订单表通过已完成和完成中拆分为已完成订单和未完成订单产品表未完成订单放一个server上已完成订单表盒男用户表放一个server上女用户表放一个server上(女的爱购物哈哈)

4.服务器硬件优化

这个么多花钱咯！

9.存储过程与触发器的区别

触发器与存储过程非常相似，触发器也是SQL语句集，两者唯一的区别是触发器不能用EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发（激活）执行。触发器是在一个修改了指定表中的数据时执行的存储过程。通常通过创建触发器来强制实现不同表中的逻辑相关数据的引用完整性和一致性。由于用户不能绕过触发器，所以可以用它来强制实施复杂的业务规则，以确保数据的完整性。触发器不同于存储过程，触发器主要是通过事件执行触发而被执行的，而存储过程可以通过存储过程名称名字而直接调用。当对某一表进行诸如UPDATE、INSERT、DELETE这些操作时，SQLSERVER就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合这些SQL语句所定义的规则。

一．基础笔试命令考察

1. 开启MySQL服务

service mysqld start

/init.d/mysqld start

safe_mysql &

关闭mysql服务

service mysqld stop

/etc/init.d/mysqld stop

mysqladmin -uroot -p123456 shutdown

2. 检测端口是否运行

lsof -i:3306

netstat -tunlp|grep 3306

ss -tulnp|grep 3306

3. 为MySQL设置密码或者修改密码。

方法一

mysqladmin -u root -p123456 password 'abc123' #比较常用

方法二（sql语句修改）

update mysql.user set password=password(123456) where user='root' and host='localhost';

flush privileges;

方法三（sql语句修改）

set password=password('abc123');

4. 登陆MySQL数据库。

单实例登陆

mysql -uroot -p123456

多实例登陆

mysql -uroot -p123456 -S /data/3306/mysql.sock

5. 查看当前数据库的字符集

mysql> show variables like "%charac%";

6. 查看当前数据库版本

# mysql -V

mysql> select version();

7. 查看当前登录的用户。

mysql> select user();

查看T1数据库中有哪儿些表

mysql> use T1;

mysql> show tables;

8. 创建GBK字符集的数据库oldboy，并查看已建库完整语句

mysql> create database oldboy default character set gbk;

mysql> show create database oldboy;

9. 创建用户oldboy，使之可以管理数据库oldboy

mysql> grant select,update,insert,delete,alter on oldboy.* to oldboy@'localhost' identified by '123456';

10. 查看创建的用户oldboy拥有哪些权限

mysql> show grants for oldboy@'localhost';

11. 查看当前数据库里有哪些用户

mysql> select user,host from mysql.user;

12. 进入oldboy数据库

mysql> use oldboy();

13. 创建一个innodb GBK表test，字段id int(4)和name varchar(16)

mysql> create table test (id int(4),name varchar(16)) engine=InnoDB default charset=gbk;

14. 查看建表结构及表结构的SQL语句

mysql> desc test;

mysql> show create table test\G

15. 插入一条数据“1,oldboy”

mysql> insert into test (id,name) values (1,'oldboy');

16. 再批量插入2行数据 “2,老男孩”，“3,oldboyedu”

mysql> insert into test (id,name) values (2,'老男孩'),(3,'oldboyedu');

17. 查询名字为oldboy的记录

mysql> select * from test where name='oldboy';

18. 把数据id等于1的名字oldboy更改为oldgirl

mysql> update test set name='oldgirl' where id=1;

19. 在字段name前插入age字段，类型tinyint(2)

mysql> alter table test add age tinyint(2) after id;

20. 不退出数据库,完成备份oldboy数据库

mysql> system mysqldump -uroot -p123456 -B -x -F --events oldboy >/opt/bak.sql

21. 删除test表中的所有数据，并查看

mysql> delete from test;

22. 删除表test和oldboy数据库并查看

mysql> drop table test;

mysql> drop database oldboy;

23. 不退出数据库恢复以上删除的数据

mysql> system mysql -uroot -p123456 </opt/bak.sql

24. 把库表的GBK字符集修改为UTF8

mysql> alter database oldboy default character set utf8;

mysql> alter table test default character set utf8;

25. 把id列设置为主键，在Name字段上创建普通索引。

mysql> alter table test add primary key(id);

方法一：

mysql> alter table test add index index_name(name);

方法二：

mysql> create index index_name on test(name);

26. 在字段name后插入手机号字段(shouji)，类型char(11)。

mysql> alter table test add shouji char(11) after name;

27. 所有字段上插入2条记录（自行设定数据）

mysql> insert into test (id,age,name,shouji) values ('4','27','wangning','13833573773');

mysql> insert into test (id,age,name,shouji) values ('5','30','litao','13833573773');

28. 在手机字段上对前8个字符创建普通索引

方法一：

mysql> alter table test add index index_shouji(shouji(8));

方法二：

mysql> create index index_shouji on test(shouji(8));

28.1 添加主键索引

mysql> alter table test add primary key id_name(id)

29. 查看创建的索引及索引类型等信息。

mysql> show index from test\G

30. 删除Name，shouji列的索引。

mysql> alter table test drop index index_name;

mysql> alter table test drop index index_shouji;

31. 对Name列的前6个字符以及手机列的前8个字符组建联合索引。

mysql> create index index_name_shouji on test(name(6),shouji(8));

32. 查询手机号以135开头的，名字为oldboy的记录（提前插入）。

mysql> select * from test where name='oldboy' and shouji like "135%";

33. 查询上述语句的执行计划（是否使用联合索引等）。

mysql> explain select * from test where name="oldboy" and shouji like "135%"\G

34. 把test表的引擎改成MyISAM。

mysql> alter table test engine=myisam; #myisam不区分大小写

35. 收回oldboy用户的select权限。

mysql> revoke select on oldboy.* from oldboy@'localhost';

36. 删除oldboy用户。

mysql> drop user oldboy@'localhost';

37. 删除oldboy数据库。

mysql> drop database oldboy;

38. 使用mysqladmin关闭数据库。

mysqladmin -uroot -p123456 shutdown

39. MySQL密码丢了，请找回？

# pkill mysql #先关闭mysql服务

#使用--skip-grant-tables启动mysql，忽略授权登陆验证

# mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables &

# mysql #此时再登陆，已经不需要密码了

mysql> update mysql.user set password=password('abc123') where user='root' and host="localhost"; #设置新的密码

mysql> flush privileges;

# mysql -uroot -pabc123 #再次用新设置的密码登陆即可

二．MySQL运维基础知识面试问答题

40. 请解释关系型数据库概念及主要特点？

概念：

关系型数据库是支持采用了关系模型的数据库，简单来说，关系模型就是指二维表模型，而一个关系数据库就是由二维表及其之间的联系所组成的一个数据组织。

特点：

最大的特点就是事务的一致性。

优点：容易理解、使用方便、易于维护、支持SQL。

缺点：

高并发读写需求：网站的用户并发非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
海量数据的高效读写：对于数据量巨大的网站来说，关系型数据库的查询效率非常低。
固定的表结构。

41. 请说出关系型数据库的典型产品、特点及应用场景？

1.SQLserver

特点：真正的客户机/服务器体系结构

图形化用户界面

丰富的编程接口工具

与Windows NT完全集成

具有很好的伸缩性

应用场景：

主机为Windows系统，主要应用于web网站的建设，承载中小型web后台数据。

2.MySQL

特点：体积小，总拥有成本低，开放源代码，可运行在多数系统平台上，轻量级易扩展。

应用场景：广泛的应用在Internet上的中小型网站中。

3.Oracle

特点：跨平台运行，安全性方面，性能最高。对硬件要求高，价格昂贵。

应用场景：大部分国企事业单位都用Oracle，在电信行业占用最大的份额。

42. 请解释非关系型数据库概念及主要特点？
使用键值对存储数据，且结构不固定
一般不支持ACID特性。
基于键值对，数据没有耦合性，容易扩展。
不提供SQL支持，学习和使用成本较高。

43. 请说出非关系型数据库的典型产品、特点及应用场景？

MongoDB

特点：1.高性能，易部署，易使用。

2.面向集合存储，易存储对象类型的数据。

3.模式自由

4.自动处理碎片，以支持云计算层次的扩展性。

应用场景：

网站数据：mongodb非常适合实时的插入，更新与查询。

缓存：适合作为信息基础设施的缓存层

大尺寸、低价值的数据

高伸缩性的场景

Redis

特点：1.性能极高，能支持超过100k+每秒的读写频率

2.丰富的数据类型

3.所有操作都是原子性的

使用场景：

少量的数据存储，高速读写访问

SQLlite

特点：

1.嵌入式的，零配置，无需安装和管理配置

2.ACID事务

3.存储在单一磁盘文件中的一个完整的数据库。

应用场景：

1.需要数据库的小型桌面软件。

2.需要数据库的手机软件。

3.作为数据容器的应用场景。

44. 请详细描述SQL语句分类及对应代表性关键字。

（1）DDL(Data Definition Language)---数据库定义语言（create、alter、drop)，管理基础数据库，例如：库，表

（2）DCL(Data Control Language)---数据控制语言（grant、revoke、commit、rollback），用户授权，权限回收，数据提交回滚等

（3）DML（Data Manipulation Language)---数据操作语言（select、insert、delete、update）,针对数据库里的表，记录

45. 请详细描述char(4)和varchar(4)的差别。

char(4)定义的是固定长度4，存储时，如果字符数不够4位，会在后面用空格补全存入数据库。

varchar(4)定义的是变长长度，存储时，如果字符没有达到定义的位数4时，也不会在后面补空格。

46. 如何授权oldboy用户从172.16.1.0/24访问数据库。

mysql> grant all on *.* to oldboy@'172.16.1.%' identified by '123456';

47. 什么是MySQL多实例，如何配置MySQL多实例？

在一台服务器上，mysql服务开启多个不同的端口，运行多个服务进程，这些mysql服务进程通过不同的socket来监听不同的数据端口，进而互不干涉的提供各自的服务。

48. 如何加强MySQL安全，请给出可行的具体措施？

1.避免直接从互联网访问mysql数据库，确保特定主机才拥有访问权限。

2.定期备份数据库

3.禁用或限制远程访问

在my.cnf文件里设置bind-address指定ip

4.移除test数据库（默认匿名用户可以访问test数据库）

5.禁用local infile

mysql> select load_file("/etc/passwd");

在my.cnf里[mysqld]下添加set-variable=local-infile=0

6.移除匿名账户和废弃的账户

7.限制mysql数据库用户的权限

8.移除和禁用.mysql_history文件

# cat ~/.mysql_history

# export MYSQL_HISTFILE=/dev/null

49. delete和truncate删除数据的区别？

truncate table test执行更快，清空物理文件，清空表中的所有内容

delete from test是逻辑删除，按行删除，而且可以通过where语句选择要删除的行

50. MySQL Sleep线程过多如何解决？

mysql> show processlist\G

# mysqladmin -uroot -p123456 processlist

修改my.cnf文件里的wait_timeout的值，让其更小一些，默认wait_timeout =28800，这里改为100

mysql> set global wait_timeout=100;

mysql> show global variables like "wait_timeout";

51. sort_buffer_size参数作用？如何在线修改生效？

mysql执行排序使用的缓冲大小。如果想要增加order by的速度，首先看是否可以让mysql使用索引而不是额外的排序阶段，如果不能，可以尝试增加sort_buffer_size变量的大小。

mysql> set global sort_buffer_size =131072; #单位为B，即128KB，默认64K

52. 如何在线正确清理MySQL binlog？

自动清除

mysql> set global expire_logs_days=30; #设置binlog过期时间为30天

手动清除

mysql> purge binary logs to "mysql-bin.000007"; #/删除mysql-bin.000007之前的所有binlog日志

53. Binlog工作模式有哪些？各什么特点，企业如何选择？

1.row level行级模式

优点：记录数据详细（每行），主从一致

缺点：占用大量的磁盘空间，降低了磁盘的性能

2.statement level模式（默认）

优点：记录的简单，内容少，节约了IO，提高性能缺点：导致主从不一致

3.MIXED混合模式

结合了statement和row模式的优点，会根据执行的每一条具体的SQL语句来区分对待记录的日志形式。对于函数，触发器，存储过程会自动使用row level模式

企业场景选择：

1.互联网公司使用mysql的功能较少（不用存储过程、触发器、函数），选择默认的statement模式。

2.用到mysql的特殊功能（存储过程、触发器、函数）则选则MIXED模式

3.用到mysql的特殊功能（存储过程、触发器、函数），有希望数据最大化一致则选择row模式。

54. 误操作执行了一个drop库SQL语句，如何完整恢复？

如果条件允许，操作前最好禁止外面一切服务器访问mysql数据库，这里假设禁止外面访问数据库，具体步骤如下：

手动切割binlog日志并记好切割好的binlog日志文件位置，这里假设为009，备份全部binlog日志
找到之前全备数据最后备份到的binlog文件位置并记好位置，这几假设为005
用mysqladmin命令将005到008binlog文件中的SQL语句分离出来，并找到drop库的语句将其删掉
将之前全备数据导入mysql服务器
将步骤3中分离出的SQL语句导入mysql服务器
将009binlog文件删除，再次刷新binlog日志，到此数据库已恢复成功。

55. mysqldump备份使用了-A -B参数，如何实现恢复单表？
先用sed或awk将全库中的需要的表结构过滤出来

sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `SC`/!d;q' /opt/bak_2017-12-07.sql

再用grep将全库中相应的表内容过滤出来

grep 'INSERT INTO `SC`' /opt/bak_2017-12-07.sql

将1和2中过滤出来的SQL语句导入数据库即可

详述MySQL主从复制原理及配置主从的完整步骤。

主从复制原理：

主：binlog线程，记录所有改变了数据库数据的语句，放进master上的binlog中
从：IO线程，在使用start slave之后，负责从master上拉取binlog内容，放进自己的relay log中
从：SQL执行线程，执行relay log中的语句。

配置步骤：

主库开启binlog日志功能
全备数据库，记录好binlog文件和相应的位置
从库上配置和主库的连接信息
将全备数据导入从库
从库启动slave
在从库上查看同步状态，确认是否同步成功

57. 如何开启从库的binlog功能？

在my.cnf文件中写入log-bin=mysql-bin

58. MySQL如何实现双向互为主从复制，并说明应用场景?

两台数据库都开启binlog功能，相互为主从配置。

双主的实现方式主要有两种：

让表的ID自增，然后主1写1、3、5，主2写2、4、6
不让表的ID自增，然后通过web端程序去seq服务器取ID，写入双主。

双主工作场景为高并发写的场景，慎用。

59. MySQL如何实现级联同步，并说明应用场景?

第一台数据库开启binlog功能设为主服务器，第二台数据库也开启binlog功能，设为第一台服务器的从服务器，设为其他数据库的主服务器

60. MySQL主从复制故障如何解决？

解决办法1：

登陆从库上操作：

stop slave 临时停止同步开关
set global sql_slave_skip_counter=1，将同步指针向下移动一个，也可以多个，如果多次不同步，可以重复操作。
start slave，重启主从复制开关

解决办法2：

在my.cnf配置文件中加入参数

slave-skip-errors=1032,1062,1007

61. 如何监控主从复制是否故障?

查看slave端的IO和SQL进程状态是否OK，同步延迟时间是否小于1分钟

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

62. MySQL数据库如何实现读写分离？
通过程序实现读写分离（性能，效率最佳，推荐

PHP和Java程序都可以通过设置多个连接文件轻松的实现对数据库的读写分离，即当select时，就去连接读库的连接文件，当update、insert、delete是就去连接写库的连接文件。

通过软件实现读写分离

MySQL-proxy,Amoeba等代理软件也可以实现读写分离功能，但最常用最好用的还是程序实现读写分离。

开发dbproxy

63. 生产一主多从从库宕机，如何手工恢复？

处理方法：重做slave

停止slave
导入备份数据
配置master.info信息
启动slave
检查从库状态

64. 生产一主多从主库宕机，如何手工恢复？

主库宕机分为数据库宕机和服务器宕机2种，不管哪种都要进行主从切换。

1.登陆从库检查IO线程和SQL线程状态show processlist\G，确认SQL线程已读完所有relay-log

2.登陆所有从库检查master.info信息，查看哪个从库的binlog文件和位置是最新的，选择最新的从库切换为主库（或利用半同步功能，直接选择做了实时同步的从库为主库）

3.如果主库只是数据库宕了，服务器还在运行，则可以把binlog拉取到提升为主库的从库应用。

4.登陆要切换为主库的从库，进行切换操作。

stop slave;reset master;quit

5.进入要切换的从库数据目录，删除master.info和relay-log.info文件，并检查授权表，read_only等参数

6.修改my.cnf配置文件，开启binlog，注释从库参数

log-bin=/data/3307/mysql-bin

#log-slave-updates

#read-only

对同步用户进行提权，保证权限与主库用户权限一样
重启数据库提生为主库
其他从库操作

（1）检查运行环境和用户

（2）停止从库，修改master信息

（3）启动从库同步，检查同步状态

修改web程序的连接配置，从原主库指向新主库
维护损坏的主库，完成后作为从库使用，或切换回来
如果主库没有宕机，只是想按计划切换一下主库，就非常简单

（1）主库锁表

（2）登陆所有从库检查同步状态，查看是否完成同步。

（3）其他按上面步骤进行切换

65. MySQL出现复制延迟有哪些原因？如何解决？
一个主库的从库太多，导致复制延迟

建议从库数量3-5个为宜，要复制的从节点数量过多，会导致复制延迟

从库硬件比主库差，导致复制延迟

查看master和slave的系统配置，可能会因为机器配置问题，包括磁盘IO、CPU、内存等各方面因素造成复制的延迟，一般发生在高并发大数据量的写入场景。

慢SQL语句过多

假如一条SQL语句执行时间是20秒，那么执行完毕到从库上能查到数据也至少是20秒，可以修改后分多次写入，通过查看慢查询日志或show full processlist命令找出执行时间长的查询语句或者大的事务。

主从复制设计问题

主从复制单线程，如果主库写并发太大，来不及传送到从库就会导致延迟。更高版本的mysql可以支持多线程复制，门户网站则会自己开发多线程同步功能。

主从库之间网络延迟

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

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

主库硬件要搞好一点，架构的前端要加buffer。

66. 给出企业生产大型MySQL集群架构可行备份方案？
利用mysqldump做定时备份，根据情况可按天或按周做全库备份。
用rsync+inotify对主库binlog做实时备份

67. 什么是数据库事务，事务有哪些特性？企业如何选择？

事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句，执行时要么全成功要么全失败。

事务的四大特性(ACID)：

原子性（atomicity）

整个事务的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性（consistency）

事务发生前和发生后，数据的完整性必须保持一致

隔离性（isolation）

当并发访问数据库时，一个正在执行的事务在执行完毕前，对于其他的会话是不可见的，多个并发事务之间的数据是相互隔离的。

持久性（durability）

一个事务一旦被提交，它对数据库中的数据改变就是永久性的，如果出了错误，事务也不允许撤销。

68. 请解释全备、增备、冷备、热备概念及企业实践经验？

全备：备份数据库所有数据

增备：一次性备份所有数据，然后再增量备份。

冷备：需要关闭mysql服务，读写请求均不允许状态下进行。

温备：服务在线，但仅支持读请求，不允许写请求的情况下备份。

热备：备份的同时，业务不受影响。

69. MySQL的SQL语句如何优化？
在表中建立索引，优先考虑where、group by使用到的字段
尽量避免使用select *，返回无用的字段会降低查询效率
尽量避免使用in和not in，会导致数据库引擎放弃索引进行全表扫描
尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描
尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

70. MySQL中MyISAM与InnoDB的区别，至少5点

（1）问5点不同

InnoDB支持事务，而MyISAM不支持事务。
InnoDB支持行级锁，而MyISAM支持表级锁
InnoDB支持MVCC，而MyISAM不支持
InnoDB支持外键，而MyISAM不支持
InnoDB不支持全文索引，而MyISAM支持

（2） InnoDB引擎的3大特性

插入缓存（insert buffer）、二次写（double write）、自适应哈希索引（ahi）、预读（read ahead）（3）二者select count(*)哪个更快，为什么？

MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。

面试题035：开发有一堆数据发给dba执行，DBA执行需注意什么？

71. 如何调整生产线中MySQL数据库的字符集。
修改my.cnf文件中的字符集配置
查看当前字符集设置

mysql> show global variables like "character%";

用set分别设置字符集变量值

mysql> set global character_set_client=utf8;

mysql> set global character_set_connection=utf8;

mysql> set global character_set_results=utf8;

mysql> set global character_set_database;

mysql> set global character_set_server;

mysql> set global character_set_system;

72. 请描述MySQL里中文数据乱码原理，如何防止乱码？

mysql客户端 mysql服务端操作系统等字符集不一致导致的乱码，将上述字符集调成一致。

73. 企业生产MySQL如何优化（请多角度描述）？
硬件优化

CPU、内存、磁盘、网卡

软件优化

（1）操作系统：64位，内核优化

（2） MySQL编译安装、优化

my.cnf里的参数优化

74. MySQL高可用方案有哪些，各自特点，企业如何选择？
主从复制+读写分离

优点：成本低、架构简单、易实施、维护方便

缺点：master出现问题后不能自动到slave上，需要人工干涉。

MySQL Cluster

优点：安全性高，稳定性高。可以在线增加节点

缺点：架构复杂，至少三个节点，对于引擎只能用ndb，不支持外键，管理复杂，部署费时而且是收费的。

Heartbeat /keepalived+双主从复制

优点：安全性、稳定性高，出现故障系统将自动切换，从而保证服务的连续性。

缺点：可能会发生脑裂

HeartBeat+DRBD+MySQL

优点：安全性、稳定性、出现故障系统将自动切换，从而保证服务的连续性。

缺点：只用一台服务器提供服务，成本高，可能发生脑裂

75. 如何分表分库备份及批量恢复（口述脚本实现过程）？

备份库：

mysqldump -u 用户名 -p 密码数据库名 >备份的文件名

备份表

mysqldump -u 用户名 -p 密码数据库名表名 >备份的文件名

面试题041：如何批量更改数据库表的引擎？

#!/bin/sh

cmd="mysql -uroot -pabc123 -e"

$cmd "use 库名;show tables;"|grep -v Tables >/file.txt #将表名存到file.txt文件里

tables=/file.txt

for n in `cat $tables` #让变量n分别取file.txt文件里的表名

$cmd "use 库名;alter table $n engine=myisam"

done

如何批量更改数据库字符集?

#!/bin/sh

cmd="mysql -uroot -pabc123 -e"

$cmd "show databases;"|grep -v Database >/file.txt

databases=/file.txt

for n in `cat $databases`

$cmd "alter database $n default character set utf8;"

done

77. 网站打开慢，请给出排查方法，如是数据库慢导致，如何排查并解决，请分析并举例？
检查操作系统是否负载过高
登陆mysql查看有哪些sql语句占用时间过长，show processlist;
用explain查看消耗时间过长的SQL语句是否走了索引
对SQL语句优化，建立索引

78. xtranbackup的备份、增量备份及恢复的工作原理？

XtraBackup基于InnoDB的crash-recovery功能，它会复制InnoDB的data file，由于不锁表，复制出来的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。

InnoDB维护了一个redo log，又称为transaction log（事务日志），它包含了InnoDB数据的所有改动情况。当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做两步操作：

XtraBackup在备份的时候，一页一页的复制InnoDB的数据，而且不锁定表，与此同时，XtraBackup还有另外一个线程监视着transaction log，一旦log发生变化，就把变化过的log pages复制走。为什么要着急复制走呢？因为transaction log文件大小有限，写满之后，就会从头再开始写，所以新数据可能会覆盖到旧的数据。

在prepare过程中，XtraBackup使用复制到的transaction log对备份出来的InnoDB data file进行crash recovery

79. 误执行drop数据，如何通过xtrabackup恢复？
关闭mysql服务
移除mysql的data目录及数据
将备份的数据恢复到mysql的data目录
启动mysql服务

如何做主从数据一致性校验？

主从一致性校验有多种工具例如checksum、mysqldiff、pt-table-checksum等

81. 如何监控MySQL的增删改查次数？

mysql> show global status where variable_name in('com_select','com_insert','com_delete','com_update');

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Com_delete | 0 |

| Com_insert | 0 |

| Com_select | 31897 |

| Com_update | 0 |

+---------------+-------+

4 rows in set (0.00 sec)

MySQL索引的种类及工作原理。

普通索引：最基本的索引，没有任何限制。

唯一索引：与普通索引类似，不同的是，索引列的值必须唯一，但允许有空值。

主键索引：它是一种特殊的唯一索引，不允许有空值。一张表只能有一个主键。

组合索引：就是将多个字段建到一个索引里。

83. 如何自定义脚本启动MySQL(说出关键命令)

mysqld_safe --defaults-file=/data/3306/my.cnf &

84. 如何自定义脚本平滑关闭MySQL(说出关键命令)

mysqladmin -u root-p123456 -S /data/3306/mysql.sock shutdown

85. 你们的公司如何实现数据库读写分离的？

通过程序实现的读写分离

insert、update、delete、alter等走主库，select等走从库

mysqldump导入导出默认把所有数据都缩减在一行里面，为了查看和修改方便，如何将数据以多行插入的形式导出。

用--skip-extend-insert选项

87. 你是如何监控你能数据库的？

开源监控工具有很多，如zabbix，nagios

Lepus(天兔)：简洁、直观、强大的开源数据库监控系统，MySQL/Oracle/MongoDB/Redis一站式性能监控，让数据库监控更简单

公司现有的数据库架构，总共有几组mysql库？

我们公司现在有两组MySQL。其中一套是生产库，一套是测试库。

生产库和测试库都是用的mha +半同步复制做的高可用。
我们所有的项目web前端量（大概有10个项目）指向的都是一个机器上的mysql实例。因为我们是传统行业，并发访问量并不是很大，所以目前我们的生产mysql数据库未出现性能问题。

mysql的权限怎么管理？
只给insert,update，select和delete四个权限即可。有时候delete都不给。

90.  如果发现CPU，或者IO压力很大，怎么定位问题？
   1、首先我会用top命令和iostat命令，定位是什么进程在占用cpu和磁盘io；
   2、如果是mysql的问题，我会登录到数据库，通过show full processlist命令，看现在数据库在执行什么sql语句，是否有语句长时间执行使数据库卡住；
   3、执行show engine innodb status\G命令，查看数据库是否有锁资源争用；
   4、查看mysql慢查询日志，看是否有慢sql；
   5、找到引起数据库占用资源高的语句，进行优化，该建索引的建索引，索引不合适的删索引，或者根据情况kill掉耗费资源的sql语句等

NoFound_You

关注

1
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
常见mysql笔试题

1. 主键超键候选键外键主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。候选键：是最小超键，即没有冗余元素的超键。外键：...
复制链接

扫一扫