测开-面试题-MySQL

1 增删改查的关键字分别是什么？

答：

insert into \ replace into、delete、update、select

 

 

2 内连接和外连接的区别？

答：

（1）内连接,只会展示与两表关联条件匹配的数据。

关键字：inner join on
语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;

（2）外连接,如果是与两表关联条件不匹配的数据，也会在关联条件列展示为NULL。

左外连接和右外连接的区别：

左外连接，查询结果以左表为主,主表的数据会全部显示处理，从表中，与连接条件不匹配的数据都会以NULL展示。

关键字：left join on / left outer join on（left join 是left outer join的简写，它的全称是左外连接）
语句：select * from a_table a left join b_table b on a.a_id = b.b_id;

右外连接，查询结果以右表为主,主表的数据会全部显示处理，从表中，与连接条件不匹配的数据都会以NULL展示。

关键字：right join on / right outer join on
语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id;

 

全连接（全外连接）
MySQL目前不支持此种方式，可以用其他方式替代解决。
全外连接：左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充；
也就是：
全外连接=左表全部记录+右表全部记录+相关联结果=左外连接+右外连接-相关联结果（即去重复）

那么在MYSQL中谁可以做到呢？
UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

  

3 MySQL里面锁是怎么使用的？

答：

按粒度分为三种：全局锁、表锁、行锁。

全局锁
对数据库进行操作时使用，一般用在数据库备份恢复。

表锁
元数据锁：
CRUD操作-MDL读锁
表格式更改-MDL写锁

行锁
CRUD操作-共享锁（乐观锁，读锁）
结构更改-排他锁（悲观锁，写锁）

死锁解除方式（两种）：
等待超时，系统会自动回滚。
进行死锁检测，做法是人工回滚；缺点是有可能它并未死锁，但是操作员以为它死锁而手动回滚事务了。

 

如何加乐观锁？？

答：

 

如何加行锁？？（select语句如何加锁）

答：

 

意向锁？？

答：

 

 

 

 

3.1 业务题A，B同时点击支付，如何判定，如何解决问题

答：

加锁。

1、乐观锁
总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改。乐观锁并不是真实存在的锁，而是在更新的时候判断此时的库存是否是之前查询出的库存，如果相同，表示没人修改，可以更新库存，否则表示别人抢过资源，不再执行库存更新。

2、悲观锁
总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起。可以依靠数据库实现，如行锁、读锁和写锁等，都是在操作之前加锁。悲观锁类似于我们在多线程资源竞争时添加的互斥锁，容易出现死锁现象，采用不多。

 

 

4 Mysql体系架构？

答：

体系架构图，如下所示。

从MySQL的架构图，我们可以看出MySQL的架构自顶向下大致可以分为网络连接层、数据库服务层、存储引擎层、系统文件层四大部分。接下来，我们就来简单说说每个部分的组成信息。

网络连接层
网络连接层位于整个MySQL体系架构的最上层，主要担任客户端连接器的角色。提供与MySQL服务器建立连接的能力，几乎支持所有主流的服务端语言，例如：Java、C、C++、Python等，各语言都是通过各自的API接口与MySQL建立连接。

数据库服务层
数据库服务层是整个数据库服务器的核心，主要包括了系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存等部分。

连接池
主要负责存储和管理客户端与数据库的连接信息，连接池里的一个线程负责管理一个客户端到数据库的连接信息。

系统管理和控制工具
提供数据库系统的管理和控制功能，例如对数据库中的数据进行备份和恢复，保证整个数据库的安全性，提供安全管理，对整个数据库的集群进行协调和管理等。

SQL接口
主要负责接收客户端发送过来的各种SQL命令，并将SQL命令发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端。

解析树
主要负责对请求的SQL解析成一棵“解析树”，然后根据MySQL中的一些规则对“解析树”做进一步的语法验证，确认其是否合法。

查询优化器
在MySQL中，如果“解析树”通过了解析器的语法检查，此时就会由优化器将其转化为执行计划，然后与存储引擎进行交互，通过存储引擎与底层的数据文件进行交互。

缓存
MySQL的缓存是由一系列的小缓存组成的。例如：MySQL的表缓存，记录缓存，MySQL中的权限缓存，引擎缓存等。MySQL中的缓存能够提高数据的查询性能，如果查询的结果能够命中缓存，则MySQL会直接返回缓存中的结果信息。

存储引擎层
MySQL中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互。值得一提的是，MySQL中的存储引擎是插件式的，服务器中的查询执行引擎通过相关的接口与存储引擎进行通信，同时，接口屏蔽了不同存储引擎之间的差异。MySQL中，最常用的存储引擎就是InnoDB和MyISAM。

InnoDB和MyISAM存储引擎需要小伙伴们重点掌握，高频面试考点，也是成为架构师必知必会的内容。

系统文件层
系统文件层主要包括MySQL中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行pid文件和socket文件等。

日志文件
MySQL中的日志主要包括：错误日志、通用查询日志、二进制日志、慢查询日志等。

错误日志
主要存储的是MySQL运行过程中产生的错误信息。可以使用下面的SQL语句来查看MySQL中的错误日志。

show variables like '%log_error%';
通用查询日志
主要记录MySQL运行过程中的一般查询信息，可以使用下面的SQL语句来查看MySQL中的通用查询日志文件。

show variables like '%general%';
二进制日志
主要记录对MySQL数据库执行的插入、修改和删除操作，并且也会记录SQL语句执行的时间、执行的时长，但是二进制日志不记录select、show等不修改数据库的SQL。主要用于恢复数据库的数据和实现MySQL主从复制。

 

数据文件
数据文件中主要包括了：db.opt文件、frm文件、MYD文件、MYI文件、ibd文件、ibdata文件、ibdata1文件、ib_logfile0和ib_logfile1文件等。

配置文件
用于存在MySQL所有的配置信息，在Unix/Linux环境中是my,cnf文件，在Windows环境中是my.ini文件。

 

 

5 MVCC机制实现原理？

答：

MVCC多版本并发控制的原理：通过undo_log多版本链条，加上开启事务时产生的readView（不同隔离级别有不同产生策略）,然后在查询的时候,根据readView进行判断，来决定读取哪个版本的数据。实现了多事务并发执行，保证只能读开启事务前提交的数据和当前事务修改的数据，其他情况都不会读到。

多版本并发控制（MVCC） 是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其它事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

 

通过 多版本并发控制 我们可以解决以下几个问题：

读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。

降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。

解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。

 

 

6 通过mysql做过哪些工作？

 

7 事务？

答：

（1）什么是事务

当数据库作为单个工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交。

事务在mysql5.0后引入的，用于保证数据库这一组不可再分割的操作集合（工作逻辑单元）要么全部完成，要么全部不完成；

（2）事务的四大特性

原子性 ：

一个事务中的所有操作，要么全部完成，要么全部不完成；

InnoDB引擎通过 undo log（回滚日志） 来保证事务的原子性。

一致性 ：

事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态；如事务执行失败，那么所有数据会恢复到事务执行操作之前的状态。

InnoDB引擎通过原子性+持久性+隔离性来保证事务的一致性。

隔离性 ：

一个事务的执行不受其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰；

InnoDB引擎通过 MVCC（多版本并发控制）或者锁机制来保证事务的隔离性。

持久性 ：

指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的

InnoDB通过 redo log （重做日志）来保证事务的持久性。

 

（3）事务--隔离级别

（1）事务并发带来的问题

在同时处理多个事务的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题

① 脏读: 一个事务读取到了另一个事务未提交的数据

② 不可重复读:是指在一个事务内，多次读同一数据，由于另一个事务对该数据修改并提交导致读取的结果不一致。

④ 幻读:一个事务中，查询两次数据的结果，因为其他事务向表中插入新的行数据，导致结果发生不一样。

 

（2）SQL 标准提出了四种隔离级别来规避这些现象

·未提交读(Read Uncommitted)：

指一个事务还没提交时，它做的变更就能被其他事务看到；会出现脏读，幻读，不可重复读；

实现方式是直接返回记录的最新值，没有视图概念。

 

·提交读(Read Committed)：

只能读取到已经提交的数据；不会出现脏读现象，但是会出现幻读，不可重复读；只对记录加记录锁，而不会在记录之间加间隙锁，所以允许新的记录插入到被锁定记录的附近，再多次使用查询语句时，可能得到不同的结果。Oracle等多数数据库默认都是该级别 (不重复读)。

实现方式是在每个SQL语句开始执行的时候创建视图，在执行过程中都会使用该视图。

 

·可重复读(Repeated Read)：

指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的。InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读。

InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它通过next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的“间隙”和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。

实现方式是在事务启动时创建一个一致性视图，整个事务存在期间都在使用这个视图。

 

串行读(Serializable)：

完全串行化的读，每次读都需要获得共享锁（若有索引则用行级共享锁，若没索引则使用表级共享锁），读写相互都会阻塞。

实现方式是 直接加锁的方式来避免并行访问。

 

隔离级别越高，性能效率就越低。

 

在读提交和可重复读两种事务隔离级别下，普通的SELECT操作使用“快照读”，不会对数据加锁，也不会被事务阻塞。

在读提交和可重复读两种事务隔离级别下，使用“当前读”的操作包括：

① SELECT LOCK IN SHARE MODE

② SELECT FOR UPDATE

③ DELETE\UPDATE\INSERT INTO\REPLACE INTO

 

当前读、快照读用于解决什么？？

答：

 

他俩如何解决幻读？？

答：

快照读-加next-key lock

当前读-

 

同一个代码，连了两个数据库，它会有分布式问题吗？？

答：

 

 

 

8 MySQL索引实现原理？底层数据结构？

答：

一种数据结构。

优势：可以提高数据检索的效率，降低数据库的IO成本

• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

• 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
• 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

劣势：

• 索引会占据磁盘空间
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引列。
  

（1） 索引的原理，为什么要用 B+树

可以从几个维度去看这个问题，查询效率，效率是否稳定，存储数据量，查找磁盘次数，查询的特点。

「为什么不是一般二叉树？」

如果极端情况下（只对二叉树的单边子树插入），二叉树为一个链表，查询的时间复杂度为最坏为n。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

「为什么不是平衡二叉树呢？」

我们知道，mysql的索引存储在磁盘中，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树每个节点只存储一个键值和数据，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

「那为什么不是B树而是B+树呢？」

① B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的高度（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快。

B-树的查询性能并不稳定，对于根结点中关键字可能只有一次磁盘 I/O，而对于叶子结点中的关键字需要树的高度次磁盘 I/O 操作

B+树查询所有关键字的磁盘 I/O 次数都一样，查询效率稳定

② B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，通过链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

 

-为什么B+树更适合范围查找：因为 叶子节点指向下一个叶子结点。

 

（2）聚集索引 VS 非聚集索引

B+树索引按照存储方式的不同分为聚集索引和非聚集索引：

1. 聚集索引（聚簇索引）：每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。
2. 非聚集索引（非聚簇索引）：辅助索引中的叶子节点存储的数据是该行的主键值

• 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
• 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
• 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

 

非聚集索引与聚集索引的区别: 在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

 

（3）什么情况下索引会失效？（ABC列是组合索引，用AB列是否可以进行查找？？）

答：

• • 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
  • 当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；
  • 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
  • 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

 ----------------详开始-----------------

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

various-system-software-hardware-latencies

 

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的数据结构

前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

详解b+树

b+树

 

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

----------------详结束-----------------

 

8.1 MySQL的索引有哪些？

答：

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引。

按物理存储分类（叶子节点存储的是否为完整表数据）可分为：聚集索引、非聚集索引（也叫二级索引、辅助索引）。

按字段特性分类可分为：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。

按字段个数分类可分为：单列索引、联合索引（也叫复合索引、组合索引）。

 

 

8.2 索引生效 判断？

答：

在需要执行查询的语句前面添加关键词explain即可

 

8.3 在建立索引的时候，就建议让它按数字类型递增？ 

答：

 如果主键是按照顺序递增地，则新插入的数据只需追加到末尾或生成新的叶子节点即可，不会对前面的节点造成修改。而假如主键是随机并非递增的，则新插入的主键有可能需要插入到之前的叶子节点中，这就可能导致叶子节点的分裂以及B+树的重新平衡，这造成的代价是比较大的。这也是为什么主键索引建议顺序递增。

 

 

9 MySQL优化？

答：

1、选择最合适的字段属性
Mysql是一种关系型数据库，可以很好地支持大数据量的存储，但是一般来说，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小。

例如：在定义邮政编码这个字段时，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的，因为char(6)就可以很好地完成了任务。同样的如果可以的话，我们应该是用MEDIUMINT而不是BIGINT来定义整形字段。

2、尽量把字段设置为NOT NULL
在可能的情况下，尽量把字段设置为NOT NULL,这样在将来执行查询的时候，数据库不用去比较NULL值。

对于某些文本字段来说，例如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。

3、使用连接(JOIN)来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用select语句来创建一个单例的查询结果，然后把这个结果作为过滤条件用在另一个查询中。
连接(JOIN)之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上 需要两个步骤的查询工作。

sql中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）四种方式。

4、使用联合(UNION)来代替手动创建的临时表
MySQL从4.0版本开始支持union查询，他可以把需要使用临时表的两条或更多的select查询合在一个查询中。在客户端查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用union作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要相同。

下面一个例子就演示了一个使用union额查询。

当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候尽量使用union all而不是union，因为union和union all的差异主要是前者需要将两个或者多个结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，增大资源消耗及延迟。

5、事务
尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作，都可以只用一条或少数几条就可以完成的。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。

设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是要么语句块中每条语句都操作成功，要么都失败。

换句话说，就是可以保持数据库中的数据的一致性和完整性。事务以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL语句操作失败，那么Rollback命令就可以把数据库恢复到begin开始之前的状态。

BEGIN;
INSERTINTOsalesinfoSETCustomerID=14;
UPDATEinventorySETQuantity=11WHEREitem='book';
COMMIT;
事务的另一个作用是当多个用户同时使用相同的数据源时，他可以使用锁定数据库的方式来为用户提供一种安全的访问机制，这样可以保证用户的操作不被其它的用户所干扰。

6、使用外键
锁定表的方法可以维护数据的完整性，但是他却不能保证数据的关联性。这个时候我们可以使用外键。例如：外键可以保证每一条销售记录都指向某一个存在的客户。

在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有办法合法customerid的记录都不会被跟新或插入到salesinfo中.

CREATE TABLE customerinfo(customerid int primary key) engine = innodb;
CREATE TABLE salesinfo(

salesid int not null,

customerid int not null,

primary key(customerid,salesid),

foreign key(customerid) references customerinfo(customerid) on delete cascade

)engine = innodb;

7、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为他的独占性，有时会影响数据库的性能，尤其是很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其他的用户请求只能暂时等待直到该事务结束。

如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方式来获得更好的性能。下面的例子就是锁定表的方法来完成前面一个例子中事务的功能。

8、使用索引
索引是提高数据库性能的常用方法，他可以令数据库服务器比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对那些字段进行索引呢？

一般来说，索引应该建立在那些将用于join，where判断和orderby排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引，对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。

9、优化的查询语句
不使用子查询

子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。

在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为

但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询

避免函数索引

 

由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。

 

用IN来替换OR

低效查询

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
—–> 高效查询

SELECT * FROM t WHERE LOC_IN IN (10,20,30);
LIKE双百分号、左百分号无法使用到索引

读取适当的记录LIMIT M,N

 

避免数据类型不一致

SELECT * FROM t WHERE id = ’19’;
—–>

SELECT * FROM t WHERE id = 19;
分组统计可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;
默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。

 

避免随机取记录

MySQL不支持函数索引，会导致全表扫描 —–>

禁止不必要的ORDER BY排序

 

批量INSERT插入

 

 

9.1 查询语句优化？

 

9.2 索引优化？

答：

这里说一下几种常见优化索引的方法：

• 前缀索引优化：使用某个字段中字符串的前几个字符建立索引。
• 覆盖索引优化：建立一个联合索引，即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据，查询将不会再次检索主键索引，从而避免回表。
• 主键索引最好是自增的；
• 防止索引失效：-14

 

 

10 多表联合？

 

11 主从同步？

答：

（1）业务复杂的系统中，有一句sql语句需要锁表，导致暂时不能提供读的服务，这很影响运行中的业务，使用主从复制，主库负责写，从库负责读，即使主库出现写情况，通过读从库也能保证业务正常运行。（主从）

 

（2）对数据的热备。主机g了立马用备机（主备）

MySQL主从备份的实现细节：

MySQL使用3个线程来执行复制功能（其中1个在主服务器上，另2个在从服务器上）。当开始同步时，从服务器开始创建一个I/O线程，以连接主服务器，并且让主服务器发送在其二进制日志中的语句。主服务器创建一个Binlog Dump线程将二进制日志中的内容发送到从服务器。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容，并将该数据复制到从服务器数据目录中的本地文件（即中继日志）中。 第3个线程是SQL线程，由从服务器创建，用于读取中继日志并执行日志中包含的更新。 在从服务器上，读取和执行更新语句被分成两个独立的任务。当从服务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容。

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念

全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

① 从库是不是越多越好？

不是的。

因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库资源消耗比较高，同时还受限于主库的网络带宽。

（3）架构的扩展。
业务量提升，io访问频率提高，单机无法满足，此时需要做多库的存储，降低磁盘io访问频率，提高单个机器的io性能。

 

 

12 存储引擎？ 

答：

存储引擎是一种插拔式的插件方式，提供了一套API标准；是指定在表之上的，即一个库中的每一个表都可以指定专用的存储引擎；不管表采用什么样的存储引擎，都会在数据区，产生对应的一个frm文件（表结构定义描述文件）。

 

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySql的核心就是存储引擎。

 

MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等。可以使用SHOW ENGINES;语句查看系统所支持的引擎类型。

 

InnoDB与MyISAM的区别

●

InnoDB支持表、行级锁，而MyISAM支持表级锁。

●

InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全,更好的支持并发，MyISAM不支持事务

●

MyISAM中的B+树索引实现与InnoDB中的略有不同：

○

MyISAM的数据文件和索引文件是分开存储的，MyISAM使用B+树构建索引树时，非聚集索引，非叶子节点中存储的键值为索引列的值，叶子结点数据为索引所在行的磁盘地址。 

○

InnoDB是聚集索引，数据文件是和（主键）索引绑在一起的，即索引 + 数据 = 整个表数据文件，通过主键索引到整个记录，必须要有主键，通过主键索引效率很高。

但是辅助索引需要两次查询，因为辅助索引是以建索引的字段为关键字索引到主键，所以需要两次，先查询到主键，然后再通过主键查询到数据。

○

InnoDB表必须有主键，而MyISAM可以没有主键

 

 

13 如何添加外键？

 

 

14 慢查询怎么排查的？

 答：

慢查询优化基本步骤

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE

1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）

3.order by limit 形式的sql语句让排序的表优先查

4.了解业务方使用场景

5.加索引时参照建索引的几大原则

6.观察结果，不符合预期继续从0分析

 

 

15 写SQL语句：找出一个班级里面成绩最高的前三名男生的名字？

答：

单科无并列：select * from tablename order by score desc limit3;

单科有并列：select * from tablename where score >= (select score from tablename order by score desc limit2,1);    ？？

多科成绩： select sum(score) total_score from tablename group by student_id order by total_score desc limit3;

 

15.1 手写SQL代码：平均分低于80的同学的姓名，删除平均分大于60的同学

（1）平均分低于80的同学的姓名
select name,sum(score)/count(subjects) as 平均成绩
from tablename
group by 学号
having AVG(平均成绩) < 80;

（2）删除平均分大于60的同学
delete from tablename
where 学号 in（
　　select name,sum(score)/count(subjects) as 平均成绩
　　from tablename
　　group by 学号
　　having AVG(平均成绩) > 60;

）；

 

 

16 缓存三大问题以及解决方案？

答： 

缓存击穿(一)
触发条件：
​ 一个并发访问量比较大的Key在某个时间过期，导致所有的请求直接打在DB上。

解决方案：
方案一：加锁更新
​ 查询缓存，发现缓存中不存在，加锁让其他线程等待，只让一个线程去更新缓存。

方案二：异步更新
​ 缓存设置为永不过期。通过异步的方式去更新缓存。后台开启另外一个守护线程，让其定时去更新缓存，但是这种实现相对复杂，难以把握。

缓存穿透(二)
触发条件：
​ 查询缓存和数据库中都不存在的数据，这样每次请求直接打到数据库，就像缓存不存在一样，失去了缓存保护的作用。

解决方案：
方案一：设置默认值
​ 为该数据设置一个默认值(可以为空值),之后访问缓存的时候，获取到这个默认值就知道数据库中数据为空，间接的保护了数据库。

​ 但是可能会产生部分影响，例如：1.在缓存层保存默认值，又增加了内存消耗。2. 需要给该默认值设置过期时间。3. 因为缓存层和存储层的时间窗口不一致，导致影响业务。(当存储层的数据已经修改之后，但是缓存层的状态还没及时更新，导致在这个时间差内，用户访问不到。)

方案二：添加布隆过滤器
详细介绍见：大数据之布隆过滤器学习

缓存雪崩(三)
触发条件：
​ 某一时刻发生大规模的缓存失效的情况，例如缓存服务器宕机、大量key在同一时间过期，这样的后果就是大量的请求直接打到DB上，可能导致整个系统的崩溃，成为雪崩。

解决方案：
方案一：提高缓存可用性
​ 总共有两个思路，第一种是集群部署，避免单一节点出问题，导致整体雪崩。第二个思路是多级部署，不同级别设置不同的过期时间。

方案二：过期时间
​ 两种处理方案：针对热点数据，设置永不过期。对于普通数据，打散过期时间，随机设置不同的key的过期时间。

方案三：熔断降级
​ 两种思路：在服务器当即或者连接超时的情况下，为防止出现雪崩，可以暂时停止业务服务访问缓存系统。或者可以舍弃一些非核心的请求，返回准备好的错误提示。

 

17 日志

答：

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

• 通过 trx_id 可以知道该记录是被哪个事务修改的；
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链；

undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）。

 

redo log:（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术，指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上

优点：

• 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失；
• 将写操作从「随机写」变成了「顺序写」，提升 MySQL 写入磁盘的性能。

 

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

• undo log 记录了此次事务**「开始前」的数据状态，记录的是更新之「前」**的值；

• redo log 记录了此次事务**「完成后」的数据状态，记录的是更新之「后」**的值；

1.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行变更信息进行记录。

2.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

 

bin Log（归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

binlog 文件是记录了所有数据库表结构变更和表数据修改的日志

binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

• STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致；
• ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句而已；
• MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

 

• redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了AAA 更新；

写入方式：

binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。

redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

用途不同：

• binlog 用于备份恢复、主从复制；
• redo log 用于掉电等故障恢复。

 

 

18 Redis数据结构

答： 

https://blog.csdn.net/u014723137/article/details/125658176

 

 

18.1 Redis过期键删除

答：

 

 

 

19 MySQL关系型数据库，关系含义？

答：

存储结构性数据，数据之间存在联系，可以通过sql语句进行曾删改查。

 

 

20