数据库程序员面试分类真题26

数据库程序员面试分类真题26简答题1.

profile的意义以及使用场景有哪些?正确答案：MySQL可以使用profile分析SQL语句的性能消耗情况。例如，查询到SQL会执行多少时间，并看出CP（江南博哥）U、内存使用量，执行过程中系统锁及表锁的花费时间等信息。通过have_profiling参数可以查看MySQL是否支持profile，通过profiling参数可以查看当前系统profile是否开启。

查看profile是否开启：

以下是有关profile的一些常用命令：

1)setprofiling=1；#基于会话级别开启，关闭则用setprofiling=off。

2)showprofilecpuforquery1；#查看CPU的消耗情况。

3)showprofilememoryforquery1；#查看内存消耗情况。

4)showprofileblockio,cpuforquery1；#查看I/O及CPU的消耗情况。

可以使用如下的语句查询SQL的整体消耗百分比：

SELECTSTATE,SUM(DURATION)ASTOTAL_R,

ROUND(100*SUM(DURATION)/(SELECTSUM(DURATION)FROMINFORMATION_SCHEMA.PROFILINGWHEREQUERY_ID=1),2)ASPCT_R,

COUNT(*)ASCALLS,SUM(DURATION)/COUNT(*)AS"R/Call"

FROMINFORMATION_SCHEMA.PROFILING

WHEREQUERY_ID=1GROUPBYSTATEORDERBYTOTAL_RDESC;

profile是一个非常量化的指标，可以根据这些量化指标来比较各项资源的消耗，有利于对SQL语句的整体把控。[考点]执行计划

2.

MySQL之CHECK、OPTIMIZE和ANALYZE的作用分别是什么?正确答案：分析表(ANALYZE)的主要作用是分析关键字的分布；检查表(CHECK)的主要作用是检查表是否存在错误；优化表(OPTIMIZE)的主要作用是消除删除或者更新造成的空间浪费。详细信息见下表。

需要注意以下几点：

1)对于InnoDB引擎的表来说，通过设置innodb_file_per_table参数，设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成一个独立的ibd文件，用于存储表的数据和索引，这样可以一定程度上减轻InnoDB表的空间回收问题。另外，在删除大量数据后，InnoDB表可以通过altertable但是不修改引擎的方式来回收不用的空间，该操作会重建表：

2)ANALYZE、CHECK、OPTIMIZE、ALTERTABLE执行期间将对表进行锁定(数据库系统会对表加一个只读锁，在分析期间，只能读取表中的记录，不能更新和插入记录)，因此一定注意要在数据库不繁忙的时候执行相关的操作。[考点]执行计划

3.

MySQL中的索引有什么优点?正确答案：索引(Index)是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。索引是帮助MySQL高效获取数据的数据结构，它用于快速找出在某个列中含有某一特定值的行。如果不使用索引，那么MySQL必须从第1条记录开始读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，那么MySQL就能快速到达一个位置去搜寻数据文件的中间，没有必要看所有数据。

索引在MySQL中也叫作“键(Key)”，是存储引擎用于快速找到记录的一种数据结构。总体来说，索引有如下几个优点：

1)索引大大减少了服务器需要扫描的数据量。

2)索引可以帮助服务器避免排序和临时表。

3)索引可以将随机I/O变为顺序I/O。[考点]索引

4.

MySQL有哪些索引种类?正确答案：MySQL的所有列类型都可以被索引。MyISASM和InnoDB类型的表默认创建的都是BTREE索引；MEMORY类型的表默认使用HASH索引，但是也支持BTREE索引；空间列类型的索引使用RTREE(空间索引)。

MvSQL中的索引是在存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供了以下几种索引：

1)BTREE索引：最常见的索引类型，大部分引擎都支持BTREE索引，例如，MyISASM、InnoDB、MEMORY等。

2)HASH索引：只有MEMORY和NDB引擎支持，使用场景简单。

3)RTREE索引(空间索引)：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。

4)FULLTEXT(全文索引)：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本开始提供对全文索引的支持。

MySQL目前还不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引，这个特性可以大大缩小索引文件的大小，从而提高性能。但是，前缀索引在排序ORDERBY和分组GROUPBY操作的时候无法使用，也无法使用前缀索引做覆盖扫描。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。[考点]索引

5.

什么是覆盖索引?正确答案：如果一个索引包含(或者说覆盖了)所有满足查询所需要的数据，那么就称这类索引为覆盖索引(CoveringIndex)。索引覆盖查询不需要回表操作。在MySQL中，可以通过使用explain命令输出的Extra列来判断是否使用了索引覆盖查询。若使用了索引覆盖查询，则Extra列包含“Usingindex”字符串。MySQL查询优化器在执行查询前会判断是否有一个索引能执行覆盖查询。

覆盖索引能有效地提高查询性能，因为覆盖索引只需要读取索引而不用回表再读取数据。覆盖索引有以下一些优点：

1)索引项通常比记录要小，所以MySQL会访问更少的数据。

2)索引都按值的大d,N序存储，相对于随机访问记录，需要更少的I/O。

3)大多数据引擎能更好地缓存索引，比如MyISAM只缓存索引。

4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，那么就不再需要在聚集索引中查找了。

下面的SQL语句就使用了覆盖索引：

[考点]索引

6.

什么是哈希索引?正确答案：哈希索引(ItashIndex)建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引的哈希码(HashCode)，它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中的每一行的指针。如果多个值有相同的哈希码，那么索引就会把行指针以链表的方式保存在哈希表的同一条记录中。

哈希索引只有MEMORY和NDB两种引擎支持，MEMORY引擎默认支持哈希索引，如果多个HASH值相同，出现哈希碰撞，那么索引以链表方式存储。若要使InnoDB或MyISAM支持哈希索引，那么可以通过伪哈希索引来实现。主要通过增加一个字段，存储HASH值，将HASH值建立索引，在插入和更新的时候，建立触发器，自动添加计算后的HASH值到表里。在查询的时候，在WHERE子句手动指定使用哈希函数。这样做的缺陷是需要维护哈希值。

MySQL最常用存储引擎InnoDB和MyISAM都不支持HASH索引，它们默认的索引都是BTREE。但是，如果在创建索引的时候定义其索引类型为HASH，那么MySQL并不会报错，而且通过SHOWCREATETABLE查看该索引也是HASH，只不过该索引实际上还是BTREE。

HASH索引检索效率非常高，索引的检索可以一次定位，不像BTREE索引需要从根节点到枝节点，最后才能访问到叶节点这样多次的I/O访问，所以HASH索引的查询效率要远高于BTREE索引。那么，既然HASH索引的效率要比BTREE高很多，为什么大家不都用HASH索引而还要使用BTREE索引呢?其实，任何事物都是有两面性的，HASH索引也一样，虽然HASH索引效率高，但是HASH索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些：

1)HASH索引仅仅能满足“=”“IN”和“＜=＞”查询，不能使用范围查询。由于HASH索引比较的是进行HASH运算之后的HASH值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的HASH算法处理之后的HASH值的大小关系，并不能保证和HASH运算前完全一样。

2)优化器不能使用HASH：索引来加速ORDERBY操作，即HASH索引无法被用来避免数据的排序操作。由于HASH索引中存放的是经过HASH计算之后的HASH值，而且HASH值的大小关系并不一定和HASH运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。

3)MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY表，会影响一些查询的执行效率。

4)只能使用整个关键字来搜索一行，即HASH索引不能利用部分索引键查询。对于组合索引，HASH索引在计算HASH值的时候是组合索引键合并后再一起计算HASH值，而不是单独计算HASH值，所以通过组合索引的前面一个或几个索引键进行查询的时候，HASH索引也无法被利用。

5)HASH索引在任何时候都不能避免表扫描。HASH索引是将索引键通过HASH运算之后，将HASH运算结果的HASH值和所对应的行指针信息存放于一个HASH表中，由于不同索引键存在相同HASH值，所以即使取满足某个HASH键值的数据的记录条数，也无法从HASH索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

6)HASH索引遇到大量HASH值相等的情况后，性能并不一定就会比BTREE索引高。对于选择性比较低的索引键，如果创建HASH索引，那么将会存在大量记录指针信息和同一个HASH值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。[考点]索引

7.

什么是自适应哈希索引(AdaptiveHashIndex)?正确答案：InnoDB引擎有一个特殊的功能叫作自适应哈希索引(AdaptiveHashIndex)。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于BTREE索引之上再创建一个哈希索引，这样就让BTREE索引也具有哈希索引的一些优点。例如，快速的哈希查找，这是一个全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，可以选择关闭这个功能(innodb_adaptive_hash_index=OFF，默认为ON)。

通过“SHOWENGINEINNODBSTATUS;”可以看到当前自适应哈希索引的使用情况：

--------------------------------

INSERTBUFFERANDADAPTIVEHASHINDEX

--------------------------------

Ibuf:size1,freelistlen0,segsize2,94merges

mergedoperations:

insert280,deletemark0,delete0

discardedoperations:

insert0,deletemark0,delete0

Hashtablesize4425293,nodeheaphas1337buffer(s)

174.24hashsearches/s,169.49non-hashsearches/s

可以看到自适应哈希索引的使用信息，包括自适应哈希索引的大小、使用情况，每秒使用自适应哈希索引搜索的情况。[考点]索引

8.

什么是前缀索引?正确答案：有时候需要索引很长的字符列，这会让索引变得大且慢，此时可以考虑前缀索引。MySQL目前还不支持函数索引，但是支持前缀索引，即对索引字段的前N个字符创建索引，这个特性可以大大缩小索引文件的大小，从而提高索引效率。用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。前缀索引是一种能使索引更小、更快的有效办法。

前缀索引的缺点：在排序ORDERBY和分组GROUPBY操作的时候无法使用，也无法使用前缀索引做覆盖扫描，并且前缀索引降低了索引的选择性。索引的选择性是指不重复的索引值(也称为基数，Cardinality)和数据表的记录总数(COUNT(*))的比值，范围为(0，1]。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

使用前缀索引的诀窍在于，要选择足够长的前缀以保证较高的选择性，同时又不能太长(以便节约空间)。前缀应该足够长，以使得前缀索引的选择性接近于索引的整个列。换句话说，前缀的“基数”应该接近于完整的列的“基数”。[考点]索引

9.

简单描述在MySQL中，索引、唯一索引、主键、联合索引的区别，它们对数据库的性能有什么影响?正确答案：索引、唯一索引、主键、联合索引的区别如下：

1)索引：一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

2)唯一索引：普通索引允许被索引的数据列包含重复的值，如果能确定某个数据列只包含彼此各不相同的值，在为这个数据索引创建索引的时候就应该用关键字UNIQE把它定义为一个唯一索引，唯一索引可以保证数据记录的唯一性。

3)主键：一种特殊的唯一索引，在一张表中只能定义一个主键索引，逐渐用于唯一标识一条记录，是用关键字PRIMARYKEY来创建。

4)联合索引：索引可以覆盖多个数据列，如INDEX索引就是联合索引。

索引可以极大地提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。[考点]索引

10.

在表中建立了索引以后，导入大量数据为什么会很慢?正确答案：对已经建立了索引的表中插入数据时，插入一条数据就要对该记录按索引排序。因此，导入大量数据的时候速度会很慢。解决这种情况的办法是，在没有任何索引的情况插入数据，然后建立索引。[考点]索引

11.

MySQL原生支持的备份方式及种类有哪些?正确答案：MySQL原生支持的备份方式有如下几种方式：

1)直接拷贝数据文件，必须是MyISAM表，且使用flushtableswithreadlock语句，优点是简单方便，缺点是必须要锁定，且只能在同版本的MySQL上恢复使用。

2)mysqldump，由于导出的是SQL语句，所以，可以跨版本恢复，但是需要导入数据和重建索引，恢复用时会较长，如果是MyISAM表，那么同样需要锁表；如果是InnoDB表，那么可以使用--single-transaction参数避免此问题。[考点]备份恢复

12.

MySQL支持的备份类型包括哪些?正确答案：MySQL支持的备份类型如下图所示。

1)根据备份方法，备份可以分为如下3种：

①热备份(HotBackup)：热备份也称为在线备份(OnlineBackup)，是指在数据库运行的过程中进行备份，对生产环境中的数据库运行没有任何影响。常见的热备方案是利用mysqldump、XtraBackup等工具进行备份。

②冷备份(ColdBackup)：冷备份也称为离线备份(OfflineBackup)，是指在数据库关闭的情况下进行备份，这种备份非常简单，只需要关闭数据库，复制相关的物理文件即可。目前，线上数据库一般很少能够接受关闭数据库，所以该备份方式很少使用。

③温备份(WarmBackup)：温备份也是在数据库运行的过程中进行备份，但是备份会对数据库操作有所影响。该备份利用锁表的原理备份数据库，由于影响了数据库的操作，故该备份方式也很少使用。

2)根据备份文件的种类，备份可以分为如下两种：

①物理备份(PhysicalBackup)：物理备份也称为裸文件备份(RawBackup)，是指复制数据库的物理文件。物理备份既可以在数据库运行的情况下进行备份(常见备份工具：MySQLEnterpriseBackup(商业)、XtraBackup等)，也可以在数据库关闭的情况下进行备份。该备份方式不仅备份速度快，而且恢复速度也快，但是由于无法查看备份后的内容，所以只能等到恢复之后，才能检验备份出来的数据是否是正确的。

②逻辑备份(LogicalBackup)：逻辑备份是指备份文件的内容是可读的，该文本一般是由一条条SQL语句或者表的实际数据组成。常见的逻辑备份方式有mysqldump、SELECT...INTOOUTFILE等。这类备份方法的好处是可以观察备份后的文件内容，缺点是恢复时间往往会很长。逻辑备份的最大优点是对于各种存储引擎都可以用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法。因此，对于不同存储引擎混合的数据库，用逻辑备份会更简单一些。

3)根据备份内容，备份可以划分为如下3种：

①全量备份(FullBackup)：全量备份(完全备份)是指对数据库进行一次完整的备份，备份所有的数据，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。这是一般常见的备份方式，可以使用该备份快速恢复数据库，或者搭建从库。恢复速度也是最快的，但是每次备份会消耗较多的磁盘空间，并且备份时间较长。所以，一般推荐一周做一次全量备份。

②增量备份(IncrementalBackup)：增量备份也叫差异备份，是指基于上次完整备份或增量备份，对数据库新增的修改进行备份。这种备份方式有利于减少备份时使用的磁盘空间，加快备份速度。但是恢复的时候速度较慢，并且操作相对复杂。推荐每天做一次增量备份。

③日志备份(BinaryLogBackup)：日志备份是指对数据库二进制日志的备份。二进制日志是一个单独的文件，它记录数据库的改变，备份的时候只需要复制自上次备份以来对数据库所做的改变，所以只需要很少的时间。该备份方式一般与上面的全量备份或增量备份结合使用，可以使数据库恢复到任意位置。所以，推荐每小时甚至更频繁地备份二进制日志。

在生产环境上，一般都会选择以物理备份为主，逻辑备份为辅，加上日志备份，来满足线上使用数据库的需求。[考点]备份恢复

13.

如何从mysqldump工具中备份的全库备份文件中恢复某个库和某张表?正确答案：恢复某个库可以使用--one-database(简写-o)参数，如下：

全库备份：

[root@rhe16lhr～]#mysqldump-uroot-p--single-transaction-A--master-data=2＞dump.sql

只还原erp库的内容：

[root@rhe16lhr～]#mysql-uroot-pMANAGERerp--one-database＜dump.sql

那么如何从全库备份中抽取某张表呢?可以用全库恢复，再恢复某张表即可。但是，对于小库还可以，大库就很麻烦了，所以，此时可以利用正则表达式来进行快速抽取，具体实现方法如下：

从全库备份中抽取出t表的表结构：

[root@HE1～]#sed-e'/./{H;$!d;}'-e'x;/CREATETABLE't'/!d;q'dump.sql

DROPTABLEIFEXISTS't';

/*!40101SET@saved_cs_client

=@@character_set_client*/;

/*!40101SETcharacter_set_client=utf8*/;

CREATETABLE't'(

'id'int(10)NOTNULLAUTO_INCREMENT,

'age'tinyint(4)NOTNULLDEFAULT'0',

'name'varchar(30)NOTNULLDEFAULT",

PRIMARYKEY('id')

)ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8;

/*!40101SETcharacter_set_client=@saved_cs_client*/;

再从全库备份中抽取出t表的内容：

[root@HE1～]#grep'INSERTINTO't''dump.sql

INSERTINTO't'VALUES(0,0,"),(1,0,'aa'),(2,0,'bbb'),(3,25,'helei');[考点]备份恢复

14.

MySQL数据表在什么情况下容易损坏?正确答案：服务器突然断电导致数据文件损坏；强制关机，没有先关闭mysqld服务等。[考点]备份恢复

15.

数据表损坏后的主要现象是什么?正确答案：从表中选择数据时，得到如下错误：

Incorrectkeyfilefortable:'…'.Trytorepairit

查询不能在表中找到行或返回不完全的数据：

Error:Table'p'ismarkedascrashedandshouldberepaired

打开表失败：

Can'topenfile:'xxx.MYI'(ermo:145)[考点]备份恢复

16.

数据表损坏的修复方式有哪些?正确答案：可以使用myisamchk来修复，具体步骤如下：

1)修复前将mysqld服务停止。

2)打开命令行方式，然后进入mysql的/bin目录。

3)执行myisamchk-recover数据库所在路径/*.MYI。

使用repairtable或者OPTIMIZEtable命令来修复，REPAIRTABLEtable_name修复表，OPTIMIZETABLEtable_name优化表，REPAIRTABLE用于修复被破坏的表。

OPTIMIZETABLE用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZETABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排(注意：是磁盘上，而非数据库)。[考点]备份恢复

17.

MySQL有哪几个默认数据库?正确答案：在MySQL中，数据库也可以称为Schema。在安装MySQL后，默认有informationschema、mysql、performance_schema和sys这几个数据库，如下：

1.数据库information_schema

information_schema是信息数据库，是MySQL5.0新增的一个数据库，其中保存着关于MySQL服务器所维护的所有其他数据库的信息。information_schema提供了访问数据库元数据的方式。元数据是关于数据的数据，例如，数据库名或表名、列的数据类型、访问权限等。information_schema是一个虚拟数据库，有数个只读表，它们实际上是视图，而不是基本表，因此，无法看到与之相关的任何文件。

2.数据库mysql

这个是MySQL的核心数据库，主要存储着数据库的用户、权限设置、MySQL自己需要使用的控制和管理信息。它不可以被删除，如果对MySQL不是很了解，那么也不要轻易修改这个数据库里面的表信息。

3.数据库performance_schema

这是从MySOL5.5版本开始新增的一个数据库，主要用于收集数据库服务器性能数据，需要设置参数performance_schema才可以启动该功能。这个功能从MySQL5.6.6开始，默认是开启的(在MySQL5.6.6版本以下默认是关闭的)，其值为1或ON表示启用，为0或OFF表示关闭。需要注意的是，该参数是静态参数，只能写在f中，不能动态修改。

4.数据库sys

MySOL5.7提供了sys系统数据库。sys数据库结合了information_schema和performance_schema的相关数据，里面包含了一系列的存储过程、自定义函数以及视图来帮助DBA快速地了解系统的元数据信息，为DBA解决性能瓶颈提供了巨大帮助。sys数据库目前只包含一个表，表名为sys_config。

另外需要注意的一点是，在MySQL5.7以前还存在一个默认的test库，用于测试，而在MySQL5.7及其之后的版本中去掉了该库。

18.

MySQL区分大小写吗?正确答案：在MySQL中，一个数据库会对应一个文件夹，数据库里的表则以文件的方式存放在文件夹内，所以，操作系统对大小写的敏感性决定了数据库和表的大小写敏感。其实，在MySQL中，有一个只读的系统变量“lower_case_file_system”，其值反映的正是当前文件系统是否区分大小写。所以，MySQL在Windows下是不区分大小写的，而在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1)数据库名与表名是严格区分大小写的，但是，可以在/etc/f中添加lowerl_case_table_names=1，然后重启MySQL服务，这样就不区分表名的大小写了。当lower_case_table_names为0时表示区分大小写，为1时表示不区分大小写。

2)表的别名是严格区分大小写的。

3)列名与列的别名在所有的情况下均是忽略大小写的。

4)变量名也是严格区分大小写的。

另外需要说明的一点是，MySQL在查询字符串时是大小写不敏感的。如果想在查询时区分字段值的大小写，那么字段值需要设置BINARY属性。

关键字、函数名、存储过程和事件的名字不区分字母的大小写。例如，abs、bin、now、version、floor等函数和SELECT、WHERE、ORDER、GROUPBY等关键字。触发器的名字要区分字母的大小写。

19.

什么是MySQL的复制(Replication)?正确答案：MySQL内建的复制功能是构建大型、高