【MySQL】经典面试题

经典题目

1、MySQL的复制原理以及流程

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

从：sql执行线程——执行relay log中的语句；

2、MySQL中myisam与innodb的区别，至少5点

(1)、问5点不同；
1>.InnoDB支持事物，而MyISAM不支持事物

2>.InnoDB支持行级锁，而MyISAM支持表级锁
3>.InnoDB支持MVCC, 而MyISAM不支持
4>.InnoDB支持外键，而MyISAM不支持
5>.InnoDB不支持全文索引，而MyISAM支持。
(2)、innodb引擎的4大特性
插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
(3)、2者select count(*)哪个更快，为什么 myisam更快，因为myisam内部维护了一个计数器，可以直接调取。

3、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别char是一种固定长度的类型，varchar则是一种可变长度的类型

(2)、varchar(50)中50的涵义最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by col采用fixed_length计算col长度(memory引擎也一样)

(3)、int（20）中20的涵义是指显示字符的长度但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加020表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

(4)、mysql为什么这么设计对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

4、问了innodb的事务与日志的实现方式

(1)、有多少种日志；错误日志：记录出错信息，也记录一些警告信息或者正确的信息。查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。二进制日志：记录对数据库执行更改的所有操作。中继日志：事务日志：

(2)、事物的4种隔离级别隔离级别读未提交(RU)读已提交(RC)可重复读(RR)串行

(3)、事务是如何通过日志来实现的，说得越深入越好。事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号; 当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”

5、问了MySQL binlog的几种日志录入格式以及区别

(1)、binlog的日志格式的种类和分别

(2)、适用场景；

(3)、结合第一个问题，每一种日志格式在复制中的优劣。Statement：每一条会修改数据的sql都会记录在binlog中。优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题).使用以下函数的语句也无法被复制：

6、问了下MySQL数据库cpu飙升到500%的话他怎么处理？

(1)、没有经验的，可以不问；

(2)、有经验的，问他们的处理思路。列出所有进程 show processlist 观察所有进程多秒没有状态变化的(干掉)查看超时日志或者错误日志 (做了几年开发,一般会是查询以及大批量的插入会导致cpu与i/o上涨,当然不排除网络状态突然断了,导致一个请求服务器只接受到一半，比如where子句或分页子句没有发送,当然的一次被坑经历)

7、sql优化

(1)、explain出来的各种item的意义；

select_type

表示查询中每个select子句的类型

type

表示MySQL在表中找到所需行的方式，又称“访问类型”

possible_keys

指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

Extra

包含不适合在其他列中显示但十分重要的额外信息

(2)、profile的意义以及使用场景；查询到 SQL 会执行多少时间, 并看出 CPU/Memory 使用量, 执行过程中 Systemlock, Table lock 花多少时间等等作者：Ddaidai链接：https://www.jianshu/p/977a9e7d80b3来源：简书简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

8、备份计划，mysqldump以及xtranbackup的实现原理

(1)、备份计划；这里每个公司都不一样，您别说那种1小时1全备什么的就行

(2)、备份恢复时间；这里跟机器，尤其是硬盘的速率有关系，以下列举几个仅供参考20G的2分钟（mysqldump）80G的30分钟(mysqldump)111G的30分钟（mysqldump)288G的3小时（xtra)3T的4小时（xtra)逻辑导入时间一般是备份时间的5倍以上

(3)、xtrabackup实现原理在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。

9、mysqldump中备份出来的sql，如果我想sql文件中，一行只有一个insert…value()的话，怎么办？如果备份需要带上master的复制点信息怎么办？

--skip-extended-insert
[root@helei-zhuanshu ~]# mysqldump -uroot -p helei --skip-extended-insert
Enter password:  
  KEY `idx_c1` (`c1`),  
  KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `helei`
--

LOCK TABLES `helei` WRITE;
/*!40000 ALTER TABLE `helei` DISABLE KEYS */;
INSERT INTO `helei` VALUES (1,32,37,38,'2016-10-18 06:19:24','susususususususususususu');
INSERT INTO `helei` VALUES (2,37,46,21,'2016-10-18 06:19:24','susususususu');
INSERT INTO `helei` VALUES (3,21,5,14,'2016-10-18 06:19:24','susu');

10、500台db，在最快时间之内重启

puppet，dsh

11、innodb的读写参数优化

(1)、读取参数global buffer pool以及 local buffer；

(2)、写入参数；innodb_flush_log_at_trx_commitinnodb_buffer_pool_size

(3)、与IO相关的参数；innodb_write_io_threads = 8innodb_read_io_threads = 8innodb_thread_concurrency = 0

(4)、缓存参数以及缓存的适用场景。query cache/query_cache_type并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更

第一个：读操作多的话看看比例，简单来说，如果是用户清单表，或者说是数据比例比较固定，比如说商品列表，是可以打开的，前提是这些库比较集中，数据库中的实务比较小。

第二个：我们“行骗”的时候，比如说我们竞标的时候压测，把query cache打开，还是能收到qps激增的效果，当然前提示前端的连接池什么的都配置一样。大部分情况下如果写入的居多，访问量并不多，那么就不要打开，例如社交网站的，10%的人产生内容，其余的90%都在消费，打开还是效果很好的，但是你如果是qq消息，或者聊天，那就很要命。

第三个：小网站或者没有高并发的无所谓，高并发下，会看到很多 qcache 锁等待，所以一般高并发下，不建议打开query cache

12、你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

监控的工具有很多，例如zabbix，lepus，我这里用的是lepus

13、你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？

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

14、你们数据库是否支持emoji表情，如果不支持，如何操作？

如果是utf8字符集的话，需要升级至utf8_mb4方可支持

15、你是如何维护数据库的数据字典的?

这个大家维护的方法都不同，我一般是直接在生产库进行注释，利用工具导出成excel方便流通。16、你们是否有开发规范，如果有，如何执行的有，开发规范网上有很多了，可以自己看看总结下

16、你们是否有开发规范，如果有，如何执行的

百度或者借鉴阿里的

17、表中有大字段X(例如：text类型)，且字段X不会经常更新，以读为为主，请问

答：拆带来的问题：连接消耗 + 存储拆分空间；不拆可能带来的问题：查询性能；如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序IO,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗如果能容忍不拆分带来的查询性能损失的话:上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择

18、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？

答：InnoDB是基于索引来完成行锁例: select * from tab_with_index where id = 1 for update;for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起

19、如何从mysqldump产生的全库备份中只恢复某一个库、某一张表？

全库备份

[root@HE1 ~]# mysqldump -uroot -p --single-transaction -A --master-data=2 >dump.sql

只还原erp库的内容

[root@HE1 ~]# mysql -uroot -pMANAGER erp --one-database <dump.sql



可以看出这里主要用到的参数是--one-database简写-o的参数，极大方便了我们的恢复灵活性。

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

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

 

DROP TABLE IF EXISTS`t`;

/*!40101 SET@saved_cs_client     =@@character_set_client */;

/*!40101 SETcharacter_set_client = utf8 */;

CREATE TABLE `t` (

  `id` int(10) NOT NULL AUTO_INCREMENT,

  `age` tinyint(4) NOT NULL DEFAULT '0',

  `name` varchar(30) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=InnoDBAUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*!40101 SETcharacter_set_client = @saved_cs_client */;

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

[root@HE1 ~]# grep'INSERT INTO `t`' dump.sql

INSERT INTO `t`VALUES (0,0,''),(1,0,'aa'),(2,0,'bbb'),(3,25,'helei');

20.MySQL事务处理的特性

mysql事务可以理解为一系列操作，要么成功执行，要么失败。

原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

21.事务的并发问题

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

22.MySQL几种表的比较

MyISAM：节约空间，读取响应速度快,表应用于读的场景比较多，支持FULLTEXT类型的索引

InnoDB：如果应用程序需要用到事务,使用外键或需要更高的安全性,以及需要允许很多用户同时修改某个数据表里的数据,则InnoDB数据表更值得考虑。支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’

Memory: 存储在内存中，所以没有持久化。可以用于test中假数据的读写

实例：公司以前的一张表用的是MyISAM，突然有一天这张表不能读写几个小时。公司上下折腾了好久。最后原因是这张表用的是MyISAM，同时正好有人向表中新加入一列，同时进行索引，由于表很大，所以一直在做索引。因此整张表一直处于锁的状态。

23.索引原理及原则

24.索引种类

主键索引（把某列设为主键，则该列就是索引，主键不能重复）

唯一索引（unique），该列具有唯一性，同时又是索引

index 普通索引

全文索引（fulltext）只有MyISAM存储引擎支持（注：mysql 5.6之后，Innodb也开始支持全文索引，mysql5.6较之前版本有较大更新，有兴趣的小伙伴可以去查一查）

25.适合加索引的列

经常查询的列上加索引
唯一性太差的列不要加索引（重复太多），相反唯一性比较好的列适合加索引
不经常修改的列适合加。经常修改的列不要加索引（列修改，索引也要改）

因为索引的创建和更改是有开销的。

26.索引生效条件

假设index（a,b,c）

最左前缀匹配：模糊查询时，使用%匹配时：’a%‘会使用索引，’%a‘不会使用索引
条件中有or，索引不会生效
a and c，a生效，c不生效
b and c，都不生效
a and b > 5 and c,a和b生效，c不生效。

27.创建删除查看

创建方法一：

create [unique | fulltext] index 索引名 on 表名（列名）

创建方法二：

普通索引：alter table 表名 add index 索引名 （列名）

主键索引：alter table 表名 add primary key (列名)

唯一索引：alter table 表明 add unique (列名)

3.删除

drop index 索引名 on 表名；

alter table 表名 drop index 索引名

alter table 表明 drop primary key

查看

show index from 表名；

28.检测索引的效果

show status like '%handler_read%'越大越好

29.sql语句分类：

DDL：数据定义语言（create alter drop）

DML：数据操作语句（insert update delete）

select 数据查询语句

DTL：数据事务语句（commit collback savapoint）

DCL：数据控制语句（grant revoke）

30.mysql优化

1范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
2范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
3范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。没有冗余的数据库设计可以做到
但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

sql语句优化索引

使用order by null禁用排序。group by 默认情况下会排序（file sorting），非常费时。

使用join代替子查询（子查询会默认创建临时表）

垂直分割：将字段分开
水平分割：将记录分开

读写分离

31.Innodb中的事务隔离级别和锁的关系

一次封锁or两段锁？

因为有大量的并发访问，为了预防死锁，一般应用中推荐使用一次封锁法，就是在方法的开始阶段，已经预先知道会用到哪些数据，然后全部锁住，在方法运行之后，再全部解锁。这种方式可以有效的避免循环死锁，但在数据库中却不适用，因为在事务开始阶段，数据库并不知道会用到哪些数据。

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行。
解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作。

事务	加锁/解锁处理
begin；
insert into test …	加insert对应的锁
update test set…	加update对应的锁
delete from test …	加delete对应的锁
commit;	事务提交时，同时释放insert、update、delete对应的锁

这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化（串行化很重要，尤其是在数据恢复和备份的时候）的。

32.MySQL中锁的种类

MySQL中锁的种类很多，有常见的表锁和行锁，也有新加入的Metadata Lock等等,表锁是对一整张表加锁，虽然可分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做ddl处理时使用。

行锁则是锁住数据行，这种加锁方法比较复杂，但是由于只锁住有限的数据，对于其它数据不加限制，所以并发能力强，MySQL一般都是用行锁来处理并发事务。这里主要讨论的也就是行锁。

Read Committed（读取提交内容）

在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。效果如下

MySQL> select * from class_teacher;
+----+--------------+------------+
| id | class_name   | teacher_id |
+----+--------------+------------+
|  1 | 初三一班     |          1 |
|  3 | 初二一班     |          2 |
|  4 | 初二二班     |          2 |
+----+--------------+------------+

由于MySQL的InnoDB默认是使用的RR级别，所以我们先要将该session开启成RC级别，并且设置binlog的模式

SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';（或者是MIXED）

事务A	事务B
begin;	begin;
update class_teacher set class_name=‘初三二班’ where teacher_id=1;	update class_teacher set class_name=‘初三三班’ where teacher_id=1;
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
commit;

为了防止并发过程中的修改冲突，事务A中MySQL给teacher_id=1的数据行加锁，并一直不commit（释放锁），那么事务B也就一直拿不到该行锁，wait直到超时。

这时我们要注意到，teacher_id是有索引的，如果是没有索引的class_name呢？update class_teacher set teacher_id=3 where class_name = ‘初三一班’;

那么MySQL会给整张表的所有数据行的加行锁。这里听起来有点不可思议，但是当sql运行的过程中，MySQL并不知道哪些数据行是 class_name = '初三一班’的（没有索引嘛），如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤。

但在实际使用过程当中，MySQL做了一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录释放锁 (违背了二段锁协议的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。可见即使是MySQL，为了效率也是会违反规范的。

这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候，如果无法使用相应的索引，MySQL Server过滤数据的的时候特别慢，就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。

Repeatable Read（可重读）

这是MySQL中InnoDB默认的隔离级别。我们姑且分“读”和“写”两个模块来讲解。

读

读就是可重读，可重读这个概念是一事务的多个实例在并发读取数据时，会看到同样的数据行，有点抽象，我们来看一下效果。

RC（不可重读）模式下的展现

事务A	事务B
begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=1;idclass_nameteacher_id1初三二班12初三一班1
	update class_teacher set class_name=‘初三三班’ where id=1;
	commit;
select id,class_name,teacher_id from class_teacher where teacher_id=1;idclass_nameteacher_id1初三三班12初三一班1读到了事务B修改的数据，和第一次查询的结果不一样，是不可重读的。
commit;

事务B修改id=1的数据提交之后，事务A同样的查询，后一次和前一次的结果不一样，这就是不可重读（重新读取产生的结果不一样）。这就很可能带来一些问题，那么我们来看看在RR级别中MySQL的表现：

事务A	事务B	事务C
begin;	begin;	begin;
select id,class_name,teacher_id from class_teacher where teacher_id=1;
idclass_nameteacher_id1初三二班12初三一班1	update class_teacher set class_name=‘初三三班’ where id=1;commit;
		insert into class_teacher values (null,‘初三三班’,1);commit;
select id,class_name,teacher_id from class_teacher where teacher_id=1;idclass_nameteacher_id1初三二班12初三一班1没有读到事务B修改的数据，和第一次sql读取的一样，是可重复读的。没有读到事务C新添加的数据。
commit;

我们注意到，当teacher_id=1时，事务A先做了一次读取，事务B中间修改了id=1的数据，并commit之后，事务A第二次读到的数据和第一次完全相同。所以说它是可重读的。那么MySQL是怎么做到的呢？这里姑且卖个关子，我们往下看。

不可重复读和幻读的区别####

不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

悲观锁和乐观锁####

悲观锁

它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（ Version ）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

Serializable

这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

这里要吐槽一句，不要看到select就说不会加锁了，在Serializable这个级别，还是会加锁的！

34.数据库死锁概念

多数情况下，可以认为如果一个资源被锁定，它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时，其中每个进程拥有的锁都是其他进程所需的，由此造成每个进程都无法继续下去。简单的说，进程A等待进程B释放他的资源，B又等待A释放他的资源，这样就互相等待就形成死锁。
　　虽然进程在运行过程中，可能发生死锁，但死锁的发生也必须具备一定的条件，死锁的发生必须具备以下四个必要条件。
　　1）互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。
　　2）请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。
　　3）不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。
　　4）环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，•••，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。
　　下列方法有助于最大限度地降低死锁：
　　（1）按同一顺序访问对象。
　　（2）避免事务中的用户交互。
　　（3）保持事务简短并在一个批处理中。
　　（4）使用低隔离级别。
　　（5）使用绑定连接。
35.092 数据库有几种数据保护方式（AAA）

实现数据库安全性控制的常用方法和技术有：用户标识和鉴别；存取控制；视图机制；审计；数据加密；

36.union和union all 的区别以及使用

Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All两个要联合的SQL语句字段个数必须一样，而且字段类型要“相容”（一致）；
　　union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。
　　Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
　　Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
　　Intersect：对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
　　Minus：对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。
　　可以在最后一个结果集中指定Order by子句改变排序方式。
37.mysql的备份命令是什么

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
　　　　备份MySQL数据库为带删除表的格式
　　　　备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。
　　mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql
　　　　直接将MySQL数据库压缩备份
　　mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
　　　　备份MySQL数据库某个(些)表
　　mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
　　　　同时备份多个MySQL数据库
　　mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql
　　　　仅仅备份数据库结构
　　mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
　　　　备份服务器上所有数据库
　　mysqldump –all-databases > allbackupfile.sql
　　　　还原MySQL数据库的命令
　　mysql -hhostname -uusername -ppassword databasename < backupfile.sql
　　　　还原压缩的MySQL数据库
　　gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
　　　　将数据库转移到新服务器
　　mysqldump -uusername -ppassword databasename | mysql –host=… -C databasename
38.在mysql服务器运行缓慢的情况下输入什么命令能缓解服务器压力

第一步检查系统的状态
　　　　通过操作系统的一些工具检查系统的状态，比如CPU、内存、交换、磁盘的利用率，根据经验或与系统正常时的状态相比对，有时系统表面上看起来看空闲，这也可能不是一个正常的状态，因为cpu可能正等待IO的完成。除此之外，还应观注那些占用系统资源(cpu、内存)的进程。
　　　　1.1　使用sar来检查操作系统是否存在IO问题
　　　　1.2　使用vmstat监控内存 cpu资源
　　　　1.3　磁盘IO问题，处理方式：做raid10提高性能
　　　　1.4　网络问题，telnet一下MySQL对外开放的端口，如果不通的话，看看防火墙是否正确设置了。另外，看看MySQL是不是开启了skip-networking的选项，如果开启请关闭。
　　第二步检查mysql参数
　　　　2.1　max_connect_errors
　　　　2.2　connect_timeout
　　　　2.3　skip-name-resolve
　　　　2.4　slave-net-timeout=seconds
　　　　2.5　master-connect-retry
　　第三步检查mysql 相关状态值
　　　　3.1　关注连接数
　　　　3.2　关注下系统锁情况
　　　　3.3　关注慢查询（slow query）日志

39.怎么导出表结构?

1.导出整个数据库
　　　　mysqldump -u用户名 -p密码数据库名 > 导出的文件名
　　　　C:\Users\jack> mysqldump -uroot -pmysql sva_rec > e:\sva_rec.sql
　　2.导出一个表，包括表结构和数据
　　　　mysqldump -u用户名 -p 密码数据库名表名> 导出的文件名
　　　　C:\Users\jack> mysqldump -uroot -pmysql sva_rec date_rec_drv> e:\date_rec_drv.sql
　　3.导出一个数据库结构
　　　　C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec > e:\sva_rec.sql
　　4.导出一个表，只有表结构
　　　　mysqldump -u用户名 -p 密码 -d数据库名表名> 导出的文件名
　　　　C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec date_rec_drv> e:\date_rec_drv.sql
　　5.导入数据库
　　　　常用source 命令
　　　　进入mysql数据库控制台，
　　　　如mysql -u root -p
　　　　mysql>use 数据库
　　　　然后使用source命令，后面参数为脚本文件(如这里用到的.sql)
　　　　mysql>source d:wcnc_db.sql
40.正常登入MYSQL后使用什么命令查看其进程是否正常

输入show processlist;
　　如果有SUPER权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。

41.mysql远程连接命令

一、MySQL 连接本地数据库，用户名为“root”，密码“123”（注意：“-p”和“123” 之间不能有空格）
　　　　C:>mysql -h localhost -u root -p123
　　二、MySQL 连接远程数据库（192.168.0.201），端口“3306”，用户名为“root”，密码“123”
　　　　C:>mysql -h 192.168.0.201 -P 3306 -u root -p123
42.mysql主从用什么方式传输日志

MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新，以便从服务器可以对其数据拷贝执行相同的更新。

43.查看mysql数据库是否支持innodb

查看mysql的存储引擎：show plugins;
　　如何在mysql某个表中随机抽取10条记录
　　1.通过MYSQL内置的函数来操作，具体SQL代码如下：
　　　　SELECT * FROM tablename ORDER BY RAND() LIMIT 10
　　2.不要将大量的工作给数据库去做，这样会导致数据库在某一集中并发时间内锁死并阻塞。建议通过PHP随机生成一下1-X(总行数)之间的数字，然后将这10个随机数字作为查询条件，具体语句如：
　　　　SELECT * FROM tablename where ID in (2,8,4,11,12,9,3,1,33)
　　可能你还要进行重复排除，并且需要在程序中将10个值串联并连接进入SQL语句中。
44.如何查看连接mysql的当前用户

show full processlist，在user字段中查看有哪些用户

45.写出mysql怎么修改密码?

方法一: (适用于管理员或者有全局权限的用户重设其它用户的密码)
　　　　进入命令行模式
　　　　mysql -u root -p
　　　　mysql>use mysql;
　　　　mysql> UPDATE user SET password=PASSWORD(“new password”) WHERE user=’username’;
　　　　mysql> FLUSH PRIVILEGES;
　　　　mysql> quit;
　　方法二:
　　　　mysql -u root -p
　　　　mysql>use mysql;
　　　　mysql> SET PASSWORD FOR username=PASSWORD(‘new password’);
　　　　mysql> QUIT
　　方法三:
　　　　mysqladmin -u root “old password” “new password”
　　注：new password请输入你想要设置的密码。
46.MySQL怎么修复损坏的表?

有两种方法，一种方法使用mysql的check table和repair table 的sql语句，另一种方法是使用MySQL提供的多个myisamchk, isamchk数据检测恢复工具。

47.简单叙述一下MYSQL的优化（重点）

1.数据库的设计：尽量把数据库设计的更小的占磁盘空间.
　　　　1) 尽可能使用更小的整数类型.(mediumint就比int更合适).
　　　　2) 尽可能的定义字段为not null,除非这个字段需要null.
　　　　3) 如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.
　　　　4) 表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.
　　　　5) 只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。
　　　　6) 所有数据都得在保存到数据库前进行处理。
　　　　7) 所有字段都得有默认值。
　　　　8) 在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。
　　2.系统的用途
　　　　1) 尽量使用长连接.
　　　　2) explain复杂的SQL语句。
　　　　3) 如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.
　　　　4) LIMIT语句尽量要跟order by或者 distinct.这样可以避免做一次full table scan.
　　　　5) 如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.
　　　　6) 能使用STORE PROCEDURE 或者 USER FUNCTION的时候.
　　　　7) 在一条insert语句中采用多重纪录插入格式.而且使用load data infile来导入大量数据，这比单纯的insert快好多.
　　　　8) 经常OPTIMIZE TABLE 来整理碎片.
　　　　9) 还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。
　　3.系统的瓶颈
　　　　1) 磁盘搜索。并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.
　　　　2) 磁盘读写(IO)。可以从多个媒介中并行的读取数据。
　　　　3) CPU周期。数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
　　　　4) 内存带宽。当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈.
48.如何确定有哪些存储引擎可用？

mysql> show engines; 显示了可用的数据库引擎的全部名单以及在当前的数据库服务器中是否支持这些引擎。

49.MYSQL数据库设计数据类型选择需要注意哪些地方?（重点）

VARCHAR和CHAR类型，varchar是变长的，需要额外的1-2个字节存储，能节约空间，可能会对性能有帮助。但由于是变长，可能发生碎片，如更新数据；
使用ENUM代替字符串类型，数据实际存储为整型。
　　字符串类型
　　要尽可能地避免使用字符串来做标识符，因为它们占用了很多空间并且通常比整数类型要慢。特别注意不要在MYISAM表上使用字符串标识符。MYISAM默认情况下为字符串使用了压缩索引（Packed Index），这使查找更为缓慢。据测试，使用了压缩索引的MYISAM表性能要慢6倍。
　　还要特别注意完全‘随机’的字符串，例如由MD5（）、SHA1（）、UUID（）产生的。它们产生的每一个新值都会被任意地保存在很大的空间范围内，这会减慢INSERT及一些SELECT查询。
　　　　1）它们会减慢INSERT查询，因为插入的值会被随机地放入索引中。这会导致分页、随机磁盘访问及聚集存储引擎上的聚集索引碎片。
　　　　2)它们会减慢SELECT查询，因为逻辑上相邻的行会分布在磁盘和内存中的各个地方。
　　　　3）随机值导致缓存对所有类型的查询性能都很差，因为它们会使缓存赖以工作的访问局部性失效。如果整个数据集都变得同样“热”的时候，那么把特定部分的数据缓存到内存中就没有任何的优势了。并且如果工作集不能被装入内存中，缓存就会进行很多刷写的工作，并且会导致很多缓存未命中。
　　如果保存UUID值，就应该移除其中的短横线，更好的办法是使用UHEX（）把UUID值转化为16字节的数字，并把它保存在BINARY（16）列中。
50.mysql、oracle默认端口号

3306、1521

参考与推荐如下文章：

ranjun940726 《PHP 面试指南》
紫葡萄0 《MySQL 索引的使用和优化》
Ddaidai 《【MySQL】20 个经典面试题》
瘦瘦鸭《MySQL 面试知识点总结》
立超的专栏《MyISAM 和 InnoDB 的索引实现》
时芥蓝《MySQL 面试之必会知识点》
derrantcm 《【面试】【MySQL常见问题总结】【04】》
mrlapulga 《MySQL 经典面试题》提供的面试题，难的想哭。
小麦苗《MySQL 笔试面试题集合》全的想哭。

更多推荐

【MySQL】经典面试题

【MySQL】经典面试题

【MySQL】经典面试题

经典题目

1、MySQL的复制原理以及流程

2、MySQL中myisam与innodb的区别，至少5点

3、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

4、问了innodb的事务与日志的实现方式

5、问了MySQL binlog的几种日志录入格式以及区别

6、问了下MySQL数据库cpu飙升到500%的话他怎么处理？

7、sql优化

8、备份计划，mysqldump以及xtranbackup的实现原理

9、mysqldump中备份出来的sql，如果我想sql文件中，一行只有一个insert…value()的话，怎么办？如果备份需要带上master的复制点信息怎么办？

10、500台db，在最快时间之内重启

11、innodb的读写参数优化

12、你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

13、你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？

14、你们数据库是否支持emoji表情，如果不支持，如何操作？

15、你是如何维护数据库的数据字典的?

16、你们是否有开发规范，如果有，如何执行的

17、表中有大字段X(例如：text类型)，且字段X不会经常更新，以读为为主，请问

18、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？

19、如何从mysqldump产生的全库备份中只恢复某一个库、某一张表？

一次封锁or两段锁？

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

Read Committed（读取提交内容）

Repeatable Read（可重读）

读

不可重复读和幻读的区别####

悲观锁和乐观锁####

Serializable

发布评论取消回复

最近发表

热门文章

标签列表

【MySQL】经典面试题

【MySQL】经典面试题

经典题目

1、MySQL的复制原理以及流程

2、MySQL中myisam与innodb的区别，至少5点

3、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

4、问了innodb的事务与日志的实现方式

5、问了MySQL binlog的几种日志录入格式以及区别

6、问了下MySQL数据库cpu飙升到500%的话他怎么处理？

7、sql优化

8、备份计划，mysqldump以及xtranbackup的实现原理

9、mysqldump中备份出来的sql，如果我想sql文件中，一行只有一个insert…value()的话，怎么办？如果备份需要带上master的复制点信息怎么办？

10、500台db，在最快时间之内重启

11、innodb的读写参数优化

12、你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

13、你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？

14、你们数据库是否支持emoji表情，如果不支持，如何操作？

15、你是如何维护数据库的数据字典的?

16、你们是否有开发规范，如果有，如何执行的

17、表中有大字段X(例如：text类型)，且字段X不会经常更新，以读为为主，请问

18、MySQL中InnoDB引擎的行锁是通过加在什么上完成(或称实现)的？为什么是这样子的？

19、如何从mysqldump产生的全库备份中只恢复某一个库、某一张表？

一次封锁or两段锁？

数据库遵循的是两段锁协议，将事务分成两个阶段，加锁阶段和解锁阶段（所以叫两段锁）

Read Committed（读取提交内容）

Repeatable Read（可重读）

读

不可重复读和幻读的区别####

悲观锁和乐观锁####

Serializable

相关文章

发布评论取消回复

最近发表

热门文章

标签列表