面试题答案（MySql）-CSDN博客

1.数据库的三范式是什么？

1.第一范式，确保每列保持原子性。 2.第二范式，确保表中的每列都和主键相关。 3.第三范式，确保每列都和主键列直接相关，而不是间接相关。参考链接， https://www.cnblogs.com/1906859953Lucas/p/8299959.html

2.一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

3.如何获取当前数据库版本？

1.select version();
2.mysql -V

4.说一下 ACID 是什么？

ACID 一般是指数据库事务的ACID

一个事务一般是指多个操作的集合，比如插入数据库分两段插入，第二次插入错误，第一次插入操作也需要回退

ACID的翻译

1.Atomicity 原子性

2.Consistency 一致性

3.Isolation 隔离性

4.Durability 耐久性原子性，指的是整个事务是一个独立的单元，要么操作成功，要么操作不成功。
一致性，事务必须要保持和系统处于一致的状态（如果不一致会导致系统其它的方出现bug）。
隔离性，事务是并发控制机制，他们的交错也需要一致性，隔离隐藏，一般通过悲观或者乐观锁实现。
耐久性，一个成功的事务将永久性地改变系统的状态，所以在它结束之前，所有导致状态的变化都记录在一个持久的事务日志中.

5.char 和 varchar 的区别是什么？

首先明确的是，char的长度是不可变的，而varchar的长度是可变的，

定义一个char[10]和varchar[10],如果存进去的是‘abcd’,那么char所占的长度依然为10，除了字符‘abcd’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的，

char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。

char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。

6.float 和 double 的区别是什么？

double 和 float 的区别是double精度高，有效数字16位，float精度7位。但double消耗内存是float的两倍，double的运算速度比float慢得多.

7.mysql 的内连接、左连接、右连接有什么区别？

1.内连接只显示两表中有关联的数据
select * from table1 inner join table2 on table1.字段1 = table2.字段2;
2.左连接显示左表所有数据，右表没有对应的数据用NULL补齐，多了的数据删除
select * from table1 left join table2 on table1.字段1 = table2.字段2;
3.右连接显示右表所有数据，左表没有对应的数据用NULL对齐，多了的数据删除
select * from table1 left join table2 on table1.字段1 = table2.字段2;

8.mysql 索引是怎么实现的？

mysql索引采用的是B+tree，InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

9.假设Mysql（innodb）数据表中A、B字段做了组合索引（A,B），那么单独使用A或单独使用B会有索引效果吗？使用like ‘%A%’,‘A%’,‘%A’,‘%B%’,‘B%’,‘%B’查询，这其中是否有触发索引效果的条件，如有请指出？

首先，先说组合索引，单独使用A会有索引效果。组合索引生效规则如下：
1、需要加索引的字段，要在where条件中
2、数据量少的字段不需要加索引
3、如果where条件中是OR关系，加索引不起作用
4、符合最左原则。
Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
下面再说模糊查询是索引的问题，模糊查询也是遵循最左原则的。比如：

SELECT `column` FROM `table` WHERE `field` like '%keyword%';
复制代码

假如field已经建了索引，这里也不会走索引的。但是像下面这个就可以：

SELECT `column` FROM `table` WHERE `field` like 'keyword%';
复制代码

但是很多时候，模糊查询都不是满足这种情形的，所以会采用其他方式。
1.LOCATE（'substr',str,pos）方法

SELECT LOCATE('1test',`wqeqetest`); 
###返回0 

SELECT LOCATE('test',`wqeqetest`); 
###返回6

SELECT LOCATE('test',`wqeqetest`,5);
###返回6  后面的参数的意思是 从第几位开始

复制代码

2.POSITION('substr' IN field)方法

SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)
复制代码

3.INSTR(str,'substr')方法

SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0 
复制代码

所以上面的答案是 A%

10 怎么验证 mysql 的索引是否满足需求？

explain 关键字，加载select 前面，possible_keys会显示索引名。

11.说一下数据库的事务隔离？

数据库隔离级别有四种:
未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

12.说一下mysql的常用的引擎

Innodb和MyIASM。 a.Innodb引擎，Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。它本身实际上是基于Mysql后台的完整的系统。Mysql运行的时候，Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是，该引擎是不支持全文搜索的。同时，启动也比较的慢，它是不会保存表的行数的。当进行Select count(*) from table指令的时候，需要进行扫描全表。所以当需要使用数据库的事务时，该引擎就是首选。由于锁的粒度小，写操作是不会锁定全表的。所以在并发度较高的场景下使用会提升效率的。

b.MyIASM引擎，它是MySql的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行Insert插入和Update更新语句时，即执行写操作的时候需要锁定这个表。所以会导致效率会降低。不过和Innodb不同的是，MyIASM引擎是保存了表的行数，于是当进行Select count(*) from table语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的。可以将MyIASM作为数据库引擎的首先。补充2点： c.大容量的数据集时趋向于选择Innodb。因为它支持事务处理和故障的恢复。Innodb可以利用数据日志来进行数据的恢复。主键的查询在Innodb也是比较快的。 d.大批量的插入语句时（这里是INSERT语句）在MyIASM引擎中执行的比较的快，但是UPDATE语句在Innodb下执行的会比较的快，尤其是在并发量大的时候。

两种引擎所使用的索引的数据结构是什么？答案:都是B+树! MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。 Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。

13.说一下 mysql 的行锁和表锁 ?

下面的都基于innodb引擎下。

表级锁：每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高. 一般来说，如果where条件中只用到索引项，则加的是行锁；否则加的是表锁。比如说主键索引，唯一索引和聚簇索引等。如果sql的where是全表扫描的，想加行锁也爱莫能助。共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE mysql 还提供了LOCK TABLES，UNLOCK TABLES，用于加表锁，

14.说一下乐观锁和悲观锁？

如果一个事务先读后写同一份数据，就可能发生丢失修改，要做一些处理。所以这里就用到了乐观锁和悲观锁。
悲观锁和乐观锁的概念：

悲观锁（Pessimistic Concurrency Control，PCC）：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。至于怎么加锁，加锁的范围也没讲。

乐观锁（Optimistic Concurrency Control，OCC）：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。也没具体指定怎么检查。就是这么概念，什么都不说清楚。毕竟乐观锁和悲观锁也不仅仅能用在数据库中，也能用在线程中。

悲观的缺陷是不论是页锁还是行锁，加锁的时间可能会很长，这样可能会长时间的限制其他用户的访问，也就是说悲观锁的并发访问性不好。

乐观锁不能解决脏读，加锁的时间要比悲观锁短（只是在执行sql时加了基本的锁保证隔离性级别），乐观锁可以用较大的锁粒度获得较好的并发访问性能。但是如果第二个用户恰好在第一个用户提交更改之前读取了该对象，那么当他完成了自己的更改进行提交时，数据库就会发现该对象已经变化了，这样，第二个用户不得不重新读取该对象并作出更改。

可见，乐观锁更适合解决冲突概率极小的情况；而悲观锁则适合解决并发竞争激烈的情况，尽量用行锁，缩小加锁粒度，以提高并发处理能力，即便加行锁的时间比加表锁的要长。我们在使用悲观锁时一般就是在读取的时候加上for update，对记录进行锁定。而乐观锁一版时用类似版本好的东西来控制，即不做实际的锁定。

使用数据版本（Version）。在P数据上（通常每一行）加version字段(int)，A事务在读数据P 时同时读出版本号，在修改数据前检测最新版本号是否等于先前取出的版本号，如果是，则修改，同时把版本号+1；否则要么回滚，要么重新执行事务。另外，数据P的所有修改操作都要把版本号+1。有一个非常重要的点，版本号是用来查看被读的变量有无变化，而不是针对被写的变量，作用是防止被依赖的变量有修改。
使用时间戳(TimeStamp)。做法类似于1中。

总结一下：

乐观锁更适合并发竞争少的情况，最好隔那么3-5分钟才有一次冲突。当并发量为10时就能明显感觉乐观锁更慢；

上面只是一读一写。考虑如果一个事务中有3个写，如果每次写都是九死一生，事务提交比小蝌蚪找妈妈还难，这时就更要考虑是不是要用乐观锁了。

但是，当分布式数据库规模大到一定程度后，又另说了。基于悲观锁的分布式锁在集群大到一定程度后（从几百台扩展到几千台时），性能开销就打得无法接受。所以目前的趋势是大规模的分布式数据库更倾向于用乐观锁来达成external consistency。

15.mysql 问题排查都有哪些手段？

www.jianshu.com/p/8d205e946… 事物级别

select @@global.tx_isolation;
复制代码

输出数据当前状态

--返回最近一次死锁场景，等等信息
SHOW ENGINE INNODB STATUS ;
复制代码

可用于排查死锁问题，锁定行数等问题查询数据库连接信息

select * from information_schema.PROCESSLIST
复制代码

查询事务信息

--观察事务启动时间，判断是否为最近的创建的
select * from information_schema.INNODB_TRX;
复制代码

查询数据库锁等待信息

--如果存在数据表示当前存在所等待情况
select * from information_schema.INNODB_LOCK_WAITS;
复制代码

手动杀掉某个进程

--来源于select * from information_schema.INNODB_TRX;
kill trx_mysql_thread_id; 
复制代码

数据库客户端连接ip统计 --用户判断客户端连接数问题

SELECT
  substr(host, 1, instr(host, ':') - 1),
  count(*)
FROM information_schema.processlist
WHERE command <> 'Binlog Dump'
GROUP BY substr(host, 1, instr(host, ':') - 1)
ORDER BY count(*) DESC;

查询数据库指定连接的当前执行sql
SELECT *
FROM performance_schema.events_statements_current
WHERE THREAD_ID IN (SELECT THREAD_ID
                    FROM performance_schema.threads
                    WHERE PROCESSLIST_ID = 1333192);
复制代码

查询锁等待的前后事物和客户端调用的简单sql

SELECT
  '程序等待获取锁' name,
  t1.requesting_trx_id,
  p1.HOST,
  p1.DB,
  p1.INFO,
  t4.trx_mysql_thread_id,
  t4.trx_state,
  t4.trx_started,
  t1.requested_lock_id,
  t2.lock_mode  req_lockmode,
  t2.lock_type  req_locktype,
  t2.lock_table req_locktable,
  t2.lock_index req_lockindex,
  #   t2.lock_page  req_lockpage,
  #   t2.lock_rec   req_lockrec,
  t2.lock_data  req_lockdata,
  '程序hold事务不释放' name,
  t1.blocking_trx_id,
  t5.trx_mysql_thread_id,
  p1.HOST,
  p1.DB,
  p1.INFO,
  t5.trx_state,
  t5.trx_started,
  t1.blocking_lock_id,
  t3.lock_mode  blocking_lockmode,
  t3.lock_type  blocking_locktype,
  t3.lock_table blocking_locktable,
  t3.lock_index blocking_lockindex,
  #   t3.lock_page  blocking_lockpage,
  #   t3.lock_rec   blocking_lockrec,
  t3.lock_data  blocking_lockdata
FROM information_schema.INNODB_LOCK_WAITS t1
  LEFT JOIN information_schema.INNODB_LOCKS t2
    ON (t1.requesting_trx_id = t2.lock_trx_id)
  LEFT JOIN information_schema.INNODB_LOCKS t3
    ON (t1.blocking_trx_id = t3.lock_trx_id)
  LEFT JOIN information_schema.INNODB_TRX t4
    ON (t4.trx_id = t1.requesting_trx_id)
  LEFT JOIN information_schema.INNODB_TRX t5
    ON (t5.trx_id = t1.blocking_trx_id)
  LEFT JOIN information_schema.processlist p1
    ON (p1.id = t4.trx_mysql_thread_id)
  LEFT JOIN information_schema.processlist p2
    ON (p1.id = t5.trx_mysql_thread_id);
复制代码

数据库客户端连接简介

SELECT
 p.id,
 p.user,
 p.host,
 p.db,
 p.state,
 p.info,
#   t.trx_id,
#   t.trx_state,
#   t.trx_mysql_thread_id,
#   t.trx_query,
#   t.trx_ad,
 t.*
FROM information_schema.processlist p
 LEFT JOIN information_schema.INNODB_TRX t
   ON (p.id = t.trx_mysql_thread_id) where p.DB='test';
复制代码