mysql高可用面试题_MySQL - MySQL面试题

MySQL面试题

列举几种数据库存储引擎，并说一说InnoDB和MyISAM的区别

存储引擎：MyISAM、InnoDB、HEAP、BOB、ARCHIVE、CSV等。

MyISAM是非事务的存储引擎，适合用于频繁查询的应用。表锁，不会出现死锁。适合小并发。

Innodb是支持事务的存储引擎，适合于插入和更新操作比较多的应用。设计合理的话是行锁(最大区别就在锁的级别上)，适合大并发。

存储引擎

MyISAM

InnoDB

事务处理

不支持

支持

数据行锁定

不支持,只有表锁定

支持

外键约束

不支持

支持

表空间大小

相对小

相对大

全文索引

支持

不支持

COUNT问题

无

执行COUNT(*)查询时,速度慢

主键

必须有主键(没有指定的话会自己找或生产一个主键)

可以没有

储存文件

frm是表定义文件，ibd是数据文件

frm是表定义文件，myd是数据文件，myi是索引文件

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构)，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：

InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值。

MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。

MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快(注意不能加有任何WHERE条件)。

为什么InnoDB没有了这个变量呢：因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。

InnoDB为什么推荐使用自增ID作为主键：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

谈谈数据库的隔离级别

Read Uncommitted(未提交读)：在Read Uncommitted级别，事务的修改，即使没有提交，对其他事务也是可见的。事务可以读取其它事务未提交的数据，这也被称为脏读(Dirty Read)。这个级别会导致很多问题，从性能上说，Read Uncommitted不会比其他级别好太多，但缺乏其他级别的很多好处。除非真的有非常必要的理由，在实际应用中一般很少使用。

Read Committed(已提交读)：大多数数据库的默认隔离级别都是Read Committed(但MySQL 不是)。Read Committed满足隔离性的简单定义：一个事务开始时，只能看见已经提交的事务所作的修改。换句话说，一个事务从开始直到提交之前，所作的任何修改对其它事务是不可见的。这个级别有时候也叫做不可重复读(Nonrepeatable Read)，同一条数据可能有两个事务先后进行过操作，存在先后两次读取到的数据不一致(中间存在另一个事务提交的数据)的情况。执行两次同样的查询，得到的结果不一致。

Repeatable Read(可重复读，MySQL默认隔离级别)：Repeatable Read解决了不可重复读的问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另一个事务又在这个范围内插入了新记录，当之前的事务再次读取该范围的记录，会产生幻行。也就是说可重复读只会在修改事务有效，比如一个事务先后读取同一个范围的记录，而在这中间另一个事务对某一条记录做了修改，当前事务两次读取到的结果是一样的，但是如果是新增数据就会产生幻读的现象。为了解决在可重复读级别下发生的幻读的问题，MySQL的InnoDB和XtraDB存储引擎通过多版本并发控制机制(MVCC)解决了幻读的问题。

Serializable(可串化读)：Serializable是最高的隔离级别。它通过强制事务串行执行，避免幻读问题。简单来说Serializable会在读取的每一行上加锁，所以可能导致大量的超时和锁竞争问题。实际开发中也很少用到这个隔离级别，只有在非常需要保证数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

说出一条SQL语句的关键字执行顺序

顺序：

FROM

ON

JOIN

WHERE

GROUP BY (开始使用SELECT中的别名，后面的语句中都可以使用)

ROLLUP, CUBE, AVG, SUM ...

HAVING

SELECT

DISTINCT

ORDER BY

TOP, LIMIT

解析：

FORM：对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1

ON：对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中。

JOIN：如果指定了OUTER JOIN(比如left join、 right join)，那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3。from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。

WHERE：对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。

GROUP BY：根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.

ROLLUP | CUBE：对表VT5进行cube或者rollup操作，产生表VT6.

HAVING：对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中。

SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。

DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.

ORDER BY: 将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10.

TOP | LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。

简单说一下MySQL主从同步

为什么需要主从同步：

1. 假如网站使用单机数据库，一旦MySQL宕机，整个网站将无法访问。引入MySQL从库，意图保证网站数据库不宕机或者宕机之后能够快速恢复。

2. Master负责写操作的负载，Slave分摊读操作负载，这样一来的可以大大提高读取的效率。在一般的互联网应用中，经调查发现读/写的比例大概在 10：1左右 ，也就是说大量的数据操作是读操作，这也就是为什么我们会有多个Slave的原因。分离读和写是因为，写操作涉及到锁的问题，不管是行锁、表锁还是块锁，都是降低系统执行效率的事情。读写分离可以有效提高读的效率，保证系统的高可用性。

主从同步：

1.主从同步主要有三种形式：statement、row、mixed

statement: 会将对数据库操作的sql语句写到binlog中。

row: 会将每一条数据的变化写道binlog中。

mixed: statement与row的混合。Mysql决定何时用statement, 何时用row。

2.过程：

MySQL主从同步是异步复制的过程，整个同步需要开启3线程

Master上开启bin-log日志(记录数据库增加、删除、修改、更新操作)。

Slave开启I/O线程来请求Master服务器，请求指定bin-log中position点之后的内容。Master端I/O线程响应请求，将bin-log中position之后内容返给salve。Slave将收到的内容存入relay-log中继日志中，生成master.info(记录master ip、bin-log、position、用户名密码)。

Slave端实时监测relay-log日志有更新，将更新的内容解析成sql语句，在Salve库中执行。

注：DQL数据查询语言，DML数据操纵语言，DDL数据定义语言，DCL数据控制语言。

简述数据库设计的三个范式

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解。

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性。

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

优点：可以尽量得减少数据冗余，更新快，表体积小。

缺点：查询需要多个表进行关联，索引优化困难。

反范式：在原本已满足三范式的基础上再做调整，增加冗余字段。

优点：可以减少表关联查询，可以更好地进行索引优化。

缺点：有数据冗余以及数据异常，数据的修改需要更多的成本。

说一说事务ACID

原子性(Atomicity)

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性(Consistency)

事务前后数据的完整性必须保持一致。

隔离性(Isolation)

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性(Durability)

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

介绍一下数据库索引

存储类型：

B+TREE、HASH

优缺点：

优点：

所有的MySQL列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引。

大大加快数据的查询速度。

缺点：

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

索引也需要占空间。我们知道数据表中的数据也会有最大上限设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上限值。

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

使用原则：

对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，

数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引。

索引分类：

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引：索引列中的值必须是唯一的，但是允许为空值。

主键索引：是一种特殊的唯一索引，不允许有空值。

组合索引：根据表中多个字段组合创建的索引，使用组合索引时遵循最左前缀集合。(若组合是【id，name，phone】，则【id】、【id，name】等可触发索引，【name，phone】不能触发索引)

全文索引：在一堆文字中，通过其中的某个关键字找到该字段所属的记录行。只有在MyISAM引擎上才能使用，只能在CHAR，VARCHAR，TEXT类型字段上使用全文索引。

空间索引：是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

不触发索引的情况：

where语句里面如果带有or条件，MyISAM表能用到索引， InnoDB不行。

or语句前后没有同时使用索引。

多列索引不满足最左匹配。

like查询以%开头时，不使用索引。

如果MySQL估计使用全表扫描要比使用索引快，则不使用索引。

where语句索引列使用函数，则不触发索引。

数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型)。

摘自：

数据库锁粒度

行锁

行锁是粒度中最小的资源。行锁就是指事务在操作数据的过程中，锁定一行或多行的数据，其它事务不能同一时候处理这些行的数据。行级锁占用的数据资源最小，所以在事务的处理过程中，允许其它事务操作同一表的其它数据。

行锁在引擎层由n各个数据库引进行擎独自实现。在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

两阶段锁协议指的是事务的执行分为两个阶段

扩展阶段(此阶段只能创建锁而不能释放锁，即需要加锁的时候才加锁)

收缩阶段(只能释放锁而不加锁)

两阶段协保证了多个事务在并发的情况下等同于串行的执行，即事务的隔离性。

页锁

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。

使用页级锁定的主要是BerkeleyDB存储引擎。

表锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁(meta data lock，MDL)。

表锁用于为单个表加锁，表锁的类型分为读锁和写锁。

加表锁：lock tables tb_name(表名) read/write;

释放锁：unlock tables;

举个例子, 如果在某个线程A中执行

lock tables t1 read, t2 write

则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表。

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。

你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。于是在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁。当要对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表查询。读写锁、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

虽然MDL锁是系统默认会加的，但却是不能忽略的一个机制。

事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

这就面临长事务的问题：事务不提交，就会一直占着MDL锁。在MySQL的information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。

如果你要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，或者kill掉这个长事务。但是数据量大了话，你kill掉这个长事务，可能未必管用，因为新的请求马上就来了。

比较理想的机制是，在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者DBA再通过重试命令重复这个过程。

数据库锁

对整个数据库实例加锁，使得整个库处于只读状态，同时会阻塞DML和DDL语句以及更新类事务的提交语句。

加全局锁：flush tables with read lock;(简称FTWRL)

释放全局锁：unlock tables;

全局锁非常典型使用场景是做全库逻辑备份。也就是把整库每个表都select出来存成文本。

如何进行数据库优化

选择正确的存储引擎

以MySQL为例。

MyISAM适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至当你只需要update一个数据行时，整个表都会被锁起来，而且别的进程(就算是读进程)无法操作直到update操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB是一个非常复杂的存储引擎，对于一些小的应用，它会比MyISAM还慢。但是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务、外键等。

优化字段的数据类型

记住一个原则，越小的列越快。如果一个表只会有几行(比如说字典表，配置表)，那么我们就没有理由使用 INT 来做主键，使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间，使用DATE要比DATETIME好得多。当然，你也需要留够足够的扩展空间。

为搜索字段添加索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么最好是为其建立索引，除非你要搜索的字段是大的文本字段，那应该建立全文索引。

使用ENUM而不是VARCHAR

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。例如，性别、民族、部门和状态之类的这些字段的取值是有限而且固定的，那么，你应该使用ENUM而不是VARCHAR。

尽可能的使用 NOT NULL

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。 当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

完整性约束包括哪些

实体完整性：规定表的每一行在表中是惟一的实体。

域完整性：是指表中的列必须满足某种特定的数据类型约束，其中约束又包括取值范围、精度等规定。

参照完整性：是指两个表的主关键字和外关键字的数据应一致，保证了表之间的数据的一致性，防止了数据丢失或无意义的数据在数据库中扩散。

用户定义的完整性：不同的关系数据库系统根据其应用环境的不同，往往还需要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。

char和varchar的区别

char

char固定长度的类型。char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足(在检索操作中那些填补出来的空格字符将被去掉)。

适用于：

有界长度的字符串(md5值,身份证，手机号)。

长度比较短小得字符串(因为varchar需要额外空间记录字符串长度)。

更新频繁的字符串。更新时不会出现页分裂得情况，避免出现存储碎片，从而获得更好的io性能。

varchar

varchar可变长度的类型。在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节(即总长度为L+1字节)。

适用于：

字符串列得最大长度比平均长度大很多。

字符串很少被更新，不然容易产生存储碎片。

使用多字节字符集存储字符串。