面试准备系列——Java基础技术篇（11）/MySQL数据库

文章主要借鉴于： 《Java高频面试题》

面试中针对MySQL数据库的考察主要集中在以下的知识点：

• MySQL索引
• 常用存储引擎
• MySQL日志模块
• 事务与隔离级别
• MySQL的锁机制
• SQL优化，线上故障排查

1.（重点）MySQL的索引有了解吗？

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，就像一本书的目录一样，可以加快查询速度。InnoDB 存储引擎的索引模型底层实现数据结构为B+树，所有数据都是存储在 B+ 树中的。

需要注意的是，索引并不是直接存在数据表中的，他由一个单独的索引文件存储，每次数据库启动的时候，会首先把索引文件加载到内存中去，这样就可以减少磁盘的IO，加快数据访问的速度。（理解不是非常正确的一种说法）

解析：
MySQL的索引是面试必考的一个知识点，希望大家可以理解掌握。不同的存储引擎中存在不同的索引结构，本小节中，我们以最常见的InnoDB为例来阐述。在面试中，还可以接着考察下边的索引知识点。

为什么底层数据结构使用B+树，而不是B树？

• B+树是B树的变种，B+树的非叶子节点只用来保存索引，不存储数据，所有的数据都保存在叶子节点；而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径。
• B+树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低。

另一解析：
要理解这个问题，我们先分析一下数据库的性质。数据库的数据被分割为多个Page以文件的形式储存在磁盘上的。因此我们每次进行数据库查询其实是在做Disk IO，而Disk IO是时间开销较大的操作(关键!)。而数据库在进行索引lookup的时候每次access一个page都是一次IO。因此我们需要选择一种能够尽量少做Disk IO的数据结构来构建索引。B+树之所以被选中主要是因为它的branch factor较大，树高较小。因而在进行索引搜索的时候需要进行的IO数量也较其他树的数量小，所以是最合适的做索引的数据结构。基于上述原因，在应用场景需要选树的时候我们都会做如下的思考：基于内存操作的我们考虑红黑树，AVL和BST，基于磁盘操作的我们优先考虑B或B+树。

聚簇索引和非聚簇索引有了解吗？

聚簇索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚簇索引。因为索引（目录）只能按照一种方法进行排序。

非聚簇索引（普通索引） 的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

案例解析：

为了让大家更好的理解主键索引和普通索引的区别，我们给出一个具体的案例如下：

我们先来创建一张表User，主键为id，并且拥有字段uid和字段name，uid字段上有索引，建表语句如下所示：

create table User(
id int primary key,
uid int not null,
name varchar(16),
index (uid))engine=InnoDB;

接着我们插入如下几条数据：

insert into User values(1,21,'zhangsan'),(2,22,lisi),(3,23,'wangwu'),(5,25,'ywq'),(6,26,'dym');

我们知道，主键上自动创建了主键索引，并且我们手动在uid字段上创建的普通索引。接下来，我们一起看下主键索引树和普通索引树的形状吧。

由上图可以看出，左边主键索引树的叶子节点存储的是完整的记录，而普通索引树上存储的是其对应的主键的值。那么主键索引和普通索引在查询方面的差距也就显而易见了吧。

MySQL回表：

• 如果语句是 select * from User where id=3，即主键查询方式，则只需要搜索 主键索引树。
• 如果语句是 select * from User where uid=23，即普通索引查询方式，则需要先搜索 普通索引树，得到其对应的主键值为 3，再到主键索引树搜索一次。这个过程称为回表。

覆盖索引：

如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

索引的最左前缀原则：

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

例如创建一个联合索引：

-- 创建teacher表
create table teacher
(
    id int not null primary key auto_increment,
    name varchar(10),
    age int
);

-- 创建联合索引
alter table teacher add index (name,age);

在使用联合索引的时候，我们要遵守一个最左原则,即index(name,age)支持 name 、name 和 age 组合查询,而不支持单独 age 查询，因为没有用到创建的联合索引。

-- 下面的查询使用到了联合索引
select * from stu where name='张三' -- 这里使用了联合索引的name部分
select * from stu where name='李四' and age=10 -- 这里完整的使用联合索引，包括 name 和 age 部分 
-- 下面的查询没有使用到联合索引
select * from stu where age=10 -- 因为联合索引里面没有这个组合，只有 name | name age 这两种组合

创建了索引但是不按照索引最左前缀原则执行的时候，执行查询使用的就不再是索引查询了，而是全表查询了。

关于最左前缀的案例可以参考：
https://www.cnblogs.com/guopengxia0719/p/10482539.html

索引下推

在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，（联合索引前提）可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率。

哪些列上适合创建索引？创建索引有哪些开销？

经常需要作为条件查询的列上适合创建索引，并且该列上也必须有一定的区分度。创建索引需要维护，在插入数据的时候会重新维护各个索引树（数据页的分裂与合并），对性能造成影响。

自增主键

为了在插入数据的时候不需要调整主键索引树的结构，强烈建议在建立表的时候使用自增主键。主键的顺序按照数据记录的插入顺序排列，自动有序。

面试的时候对于这个问题有个很常见的问法：mysql索引的原理是什么？

对于这个综合性的问题，首先需要答出，mysql的索引是对数据表中的某一列或者多个列按照一定的顺序进行排序的一种结构，就类似于一本书中的目录一样，可以加快内容的搜索速度。
然后还需要答出，mysql的索引底层实现依赖于存储引擎，存储引擎不同，索引的实现原理也不一样。mysql中常用的存储引擎包括：innodb和myISAM，这两种存储引擎的索引实现底层都依赖于B+树。而B树与B+树存储数据的最大区别就在于，B树一般在非叶子节点中也会存储数据，而B+树只会在叶子结点中存储数据，因此B+树的查询会比较稳定，而B树的查询就相对比较冗余。同时由于B树在叶子结点与非叶子节点中都会存储数据，耗费的内存也相对较多。
在两种存储引擎具体实现索引的回答上，其实还应该答出，MyISAM索引叶子节点存放的是数据的地址，主键索引与辅助索引除了值的唯一性，在结构上完全一样。InnoDB索引叶子节点存放的内容因索引类型不同而不同，主键索引叶子节点存放的是数据本身，辅助索引叶子节点上存放的是主键值。

对于MySQL中的索引，你知道使用like在什么时候会导致索引的使用失败吗？

正常情况下模糊查询%后置索引是有效的，%前置的话，会导致索引失效。当然也会存在%后置的时候索引失败的情况。也就说，like %a或者是like %a%这种查询都会导致失败，但是like a%这种查询可能就可以成功。
那么为什么前置索引的时候一定会失败呢？其实想想都应该知道，没有一个确切的值怎么能按一定的规律去查找数据呢？
至于前置索引还想继续走索引查询的话，那么应该怎么做呢？最简单的做法就是使用翻转函数，例如：

reverse(keyword) like reverse('%key') 

对于问题，为什么后置索引也有可能失败呢？解决这个问题必须得清楚一点，索引必须建立在区分度比较大的数据列上才有意义的，比如你对一个表中的性别字段加索引，那是完全没有什么意义的，mysql在查询这个字段的时候，无论你是使用何种方式走的肯定都是全表查询了。

要查找某一个sql语句是不是走全表索引应该怎么查？

答案：使用关键字 explain + 需要查询的那个sql语句。是索引查询的时候type是index，否则是all，而Extra字段也会显示：Using index condition。

2.（重点）MySQL常见的存储引擎有哪些？

MySQL中最常见的存储引擎有InnoDB和MyISAM，它们的主要区别如下：

• MyISAM不支持事务；InnoDB是事务类型的存储引擎。
• MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁。
• MyISAM引擎不支持外键；InnoDB支持外键。
• 对于count(*)查询来说MyISAM更有优势，因为其保存了行数。
• InnoDB是为处理巨大数据量时的最大性能设计的存储引擎。
• MyISAM支持全文索引（FULLTEXT）；InnoDB不支持。

总结：

最主要的区别就是MyISAM表不支持事务、不支持行级锁、不支持外键。 InnoDB表支持事务、支持行级锁、支持外键。

解析：

对MySQL的存储引擎的考察也是一个几乎必考的知识点，为了让大家对存储引擎有一个更好的了解，我们先来看下MySQL的基本逻辑架构图，从整体上了解下MySQL包括哪些逻辑架构。

由MySQL的逻辑架构图我们可以看出，逻辑架构包括Server层和存储引擎层。其中Server层包括连接器，分析器，优化器以及执行器；存储引擎层包括多种支持的存储引擎。各个逻辑部件的作用如下：

• 连接器：验证客户端权限，建立和断开MySQL连接
• 分析器：进行SQL语句的语法分析
• 优化器：选择索引，生成具体的SQL语句执行计划
• 执行器：操作存储引擎，执行SQL，返回执行结果
• 存储引擎层：各个不同的存储引擎都提供了一些读写接口来操作数据库

好了，我们接着说存储引擎的知识点，在MySQL5.5.5版本之后，InnoDB已经成为了其默认的存储引擎，也是大部分公司的不二选择，毕竟谁家公司会不要求数据库支持事务呢？谁家公司又可以忍受表级锁导致的读写冲突呢？

除了InnoDB以及MyISAM存储引擎外，常见的考察存储引擎还有Memory，使用Memory作为存储引擎的表也可以叫做内存表，将数据存储在了内存中，所以适合做临时表来使用，在索引结构上支持B+树索引和Hash索引。

3.MySQL中where、group by、having关键字？

这三个MySQL关键字作用可以总结如下：

• where子句用来筛选from子句中指定的操作所产生的的行
• group by 子句用来分组where子句的输出
• having子句用来从分组的结果中筛选行

having和where的区别：

• 语法类似，where搜索条件在进行分组操作之前应用；having搜索条件在进行分组操作之后应用
• having可以包含聚合函数sum、avg、max等
• having子句限制的是组，而不是行

当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：

• 执行where子句查找符合条件的数据
• 使用group by 子句对数据进行分组
• 对group by 子句形成的组运行聚集函数计算每一组的值
• 最后用having 子句去掉不符合条件的组

4.MySQL的日志模块binlog和redo log有了解吗？

在MySQL的使用中，更新操作也是很频繁的，如果每一次更新操作都根据条件找到对应的记录，然后将记录更新，再写回磁盘，那么IO成本以及查找记录的成本都很高。所以，出现了日志模块，即我们的update更新操作是先写日志，在合适的时间才会去写磁盘，日志更新完毕就将执行结果返回给了客户端。

MySQL中的日志模块主要有redo log（重做日志）和binlog（归档日志）。

redo log：
redo log是InnoDB引擎特有的日志模块，redo log是物理日志，记录了某个数据页上做了哪些修改。InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么redo log总共就可以记录 4GB的操作。从头开始写，写到末尾就又回到开头循环写。

InnoDB的redo log保证了数据库发生异常重启之后，之前提交的记录不会丢失，这个能力称为crash-safe。

binlog：
binlog是Server层自带的日志模块，binlog是逻辑日志，记录本次修改的原始逻辑，说白了就是SQL语句。binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。通过mysqlbinlog可以解析查看binlog日志。binlog日志文件的格式：statement，row，mixed。

• statement格式的binlog记录的是完整的SQL语句，优点是日志文件小，性能较好，缺点也很明显，那就是准确性差，遇到SQL语句中有now()等函数会导致不准确
• row格式的binlog中记录的是数据行的实际数据的变更，优点就是数据记录准确，缺点就是日志文件较大。
• mixed格式的binlog是前面两者的混合模式

业界目前推荐使用的是 row 模式，因为很多情况下对准确性的要求是排在第一位的。

解析：

MySQL的日志模块属于有点深度的知识点，在校招面试中考察不深，我们仅仅对这部分知识有个初步的认识和了解，就足以在面试中留下好印象并且获得加分。

在更新数据库的时候，通过redo log和binlog的两阶段提交，可以确保数据库异常崩溃之后数据的正确恢复。

在对数据库误操作之后，可以通过备份库+binlog可以将数据库状态恢复到“任意“时刻。

接下来，我们看一个关于日志模块的问题分析：

为什么MySQL会突然变慢一下

当内存数据页和磁盘数据页内容不一致的时候，这个内存页就是“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，这个时候的内存页就是”干净页“。

前面我们介绍了更新数据库的时候是先写日志，当合适的机会（空闲）出现的时候才会更新磁盘。但是当redo log 写满了，要 flush 脏页，也就是把内存里的数据写入磁盘，会导致MySQL执行速度突然变慢一瞬间。

5.MySQL事务有哪些特性

事务是单个逻辑工作单元执行的一系列操作，是一个不可分割的工作单位。满足如下的四大特性（ACID）：

• 原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行；
• 一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态；
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；
• 持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存

如果不做控制，多个事务并发操作数据库会产生哪些问题吗？

• 丢失更新：
  两个不同事务同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务更新会被后提交事务的更新给覆盖掉，这种情况先提交的事务所做的更新就被覆盖，导致数据更新丢失。

• 脏读：
  事务A读取了事务B未提交的数据，由于事务B回滚，导致了事务A的数据不一致，结果事务A出现了脏读；
  更好的表述：一事务对数据进行了增删改，但未提交，另一事务可以读取到未提交的数据。如果第一个事务这时候回滚了，那么第二个事务就读到了脏数据。

• 不可重复读：
  一个事务在自己没有更新数据库数据的情况，同一个查询操作执行两次或多次得到的结果数值不同，因为别的事务更新了该数据，并且提交了事务。
  更好的表述：一个事务中发生了两次读操作，第一次读操作和第二次操作之间，另外一个事务对数据进行了修改，这时候两次读取的数据是不一致的。

• 幻读：
  事务A读的时候读出了N条记录，事务B在事务A执行的过程中增加 了1条，事务A再读的时候就变成了N+1条，这种情况就叫做幻读。
  更好的表述：第一个事务对一定范围的数据进行批量修改，第二个事务在这个范围增加一条数据，这时候第一个事务就会丢失对新增数据的修改。

注意：幻读是指一种结构上的改变，比如说条数发生了改变；不可重复读是指读出的数值发生了改变。

MySQL数据库事务的隔离级别有哪些？

为了避免数据库事务操作中的问题，MySQL定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

• 读未提交(Read Uncommitted)：
  允许脏读取。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。

• 读已提交(Read Committed)：
  允许不可重复读取，但不允许脏读取。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

• 可重复读(Repeatable Read)：
  禁止不可重复读取和脏读取，但是有时可能出现幻读。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。

• 序列化(Serializable)（串行化）：
  提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。

事务的隔离级别越高，对数据的完整性和一致性保证越佳，但是对并发操作的影响也越大。MySQL事务默认隔离级别是可重复读。

6.（重点）MySQL中的锁机制？

MySQL数据库的锁分为表级锁和行级锁。从数据库的角度看，行级锁又可以分为独占锁和共享锁。

独占锁（排他锁），也称X锁（Exclusive Lock）：
独占锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受。执行数据更新命令，即INSERT、UPDATE 或DELETE 命令时，MySQL会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放。

在select命令中使用独占锁的SQL语句为：select … for update;

共享锁，也叫S锁（Shared Lock）：
共享锁顾名思义，那就是其锁定的资源可以被其它用户读取，但其它用户不能修改。如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select … lock in share mode

这里需要注意：

一个事务在一行数据上加入了独占锁，那么其余事务不可以在该数据行上加入任何锁。也就是说加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

MySQL中的死锁：

多线程为了争夺资源可能会造成死锁，也就是一种环路等待的现象。MySQL中的死锁主要是多个事务使用行级锁对某行数据加锁造成的，由于MyISAM不支持行级锁，所以MySQL中的死锁主要是在说InnoDB存储引擎的死锁。

那么MySQL的死锁该如何解决呢？
我们可以在业务上和数据库设置上来解决MySQL死锁。分别介绍如下：

业务逻辑上的死锁解决方案：

• 指定锁的获取顺序
• 大事务拆分成各个小事务
• 在同一个事务中，一次锁定尽量多的资源，减少死锁概率
• 给表建立合适的索引以及降低事务的隔离级别等

数据库的设置来解决死锁：

• 通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。

我们在来看一个关于行级锁的问题吧~

行级锁什么时候会锁住整个表？

InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

悲观锁与乐观锁
从程序员的角度看，数据库中的锁又可以分为悲观锁和乐观锁。

悲观锁：利用数据库的锁机制实现，在整个数据处理过程中都加入了锁，以保持排他性。

乐观锁：乐观锁可以利用CAS实现，在操作数据的时候进行一个比较，按照当前事务中的数据和数据库表中的该数据是否一致来决定是否要执行本次操作。

我们来举例说下乐观锁的CAS实现，有如下的数据库表和数据：

create table cas_test(
phone varchar(32) primary key,
name varchar(32)
)engine=InnoDB;
 
// 插入数据
insert into cas_test values("18810101035","zhangsan");

当我们将数据更新后，会比较该行数据与数据库表中的该行数据是否一致，以此来决定是否要更新数据。

乐观锁的ABA问题有了解吗？如何解决？

ABA问题是指在当前事务读取该行数据时是A，经过别的事务修改成B，但是在当前事务要更新数据的时候，该行数据又被别的事务修改为A，事实上数据行是发生过改变的，存在并发问题。

ABA问题可以通过基于数据版本（Version）记录机制来解决。也就是为数据增加一个版本标识。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。根据当前事务的数据版本号和数据库中数据的版本号对比来决定是否更新数据。

与给当前数据增加一个数据版本类似，我们也可以增加基于时间戳机制来解决ABA问题，通过时间戳来记录当前数据行变化。

基于数据版本Version机制，表结构设计如下所示(version表示版本号)：

基于时间戳机制，表结构设计如下所示：

7.有SQL优化或者MySQL故障排查经历吗？

一般情况下，我们遇到一个SQL异常的时候，比如说执行时间超时等，可以通过explain查看当前SQL语句的执行情况。explain +SQL语句可以查看当前的SQL语句使用的索引以及其扫描了多少行数据。也可以使用下边的语句来查看数据表的一些信息：

• show create table TableXX；查看当前表TableXX的建表语句
• show index from TableXX；查看当前表TableXX上的索引

查看了数据表的信息，一般情况下我们可以通过建立索引来提高查询速度，或者修改SQL语句，利用索引下推或者最左前缀原则等来加快查询速度。

8.MySQL建表的约束条件有哪些？

约束条件是我们建表的时候对数据库表做的一个限制条件。MySQL建表时候一般有如下的五个约束条件：

• 主键约束（Primay Key Coustraint） 唯一性，非空性
• 唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个
• 检查约束 (Check Counstraint) 对该列数据的范围、格式的限制
• 默认约束 (Default Counstraint) 该数据的默认值
• 外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列

9.MySQL的优化解决方案（面试答法）

• 1.对数据库进行分库分表处理，对于部分比较大的表，读取比较频繁的分表处理
• 2.对数据库建立索引，并对sql语句做一定的优化，按照最左前缀原则，将数据库的索引优化
• 3.将一些大的事务分割成小的事务，例如本来在一期工程中的专家信息导到现在的系统中需要半个小时的(主要是delete from 这语句耗时比较多)，现在把专家的信息一个个地取过来，并将每个信息都处理好了，放到一个结果集中再继续放回数据库中，这样的用时就大大缩小了。
• 4.常用的优化方案一般都包括：使用最左前缀原则、覆盖索引、索引下推。
• 5.还有一个最常用的就是将大查询语句中的子查询改成连接语句
• 6.适当地使用外键，通过外键去连接关联表，一个表中的信息尽可能地属性单一，设计表的时候需要符合关系型数据库设计范式的第三原则
• 7.从硬件上来说，也可以直接更换更好的磁盘去完成该任务。

对于数据库的分库分表问题深入

1.垂直切分

垂直切分又可以分为垂直分库、垂直分表。
其中垂直分库一般来说就是在企业中按照业务来划分数据库，把原来的一个很大的数据库，例如一个人才库（里边包含了专家库以及人力资源的优秀招聘人员），可以按照业务（项目部的还是人力资源部的）拆分成一个个单库。同时需要注意的是大的数据库一般都得到4 5百万级别的才勉强算大。其实垂直分库说到底了，就是微服务的拆分那意思差不多。

垂直分表，具体来说就是按照数据库表中的列来进行划分，某个表的字段比较多，可以新建一个扩展表。把那些经常需要查询的字段单独地抽取出来，或者把那些特别长的字段比较多的列抽取出来，通过大表拆小表的形式，使得数据库更加容易维护，也能减少磁盘的IO次数，内存能够加载更多的数据，从而提升数据库的性能。

垂直划分的优点很明显就是方便了维护，对不同的业务划分得清晰很多，对于高并发的情况，也在一定程度上减少了系统的磁盘IO次数，从而使得数据库的性能得到提升。但是垂直划分的缺点也是很明显的，对于分布式的事务处理是一个大难题。。。并且有部分表的数据量还是太多了，所以这种时候就需要使用水平分表。

2.水平切分

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。

库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。

经典的分库分表的方式包括：根据数值的范围或者时间的区间进行划分，根据数值的取模计算划分。

分库分表可能带来的问题包括：
事务一致性问题（分布式事务），跨节点关联查询join问题，跨节点分页、排序、函数问题，全局主键避重问题，数据迁移以及扩容问题。

10.MySQL一些的实践方案举例

• 案例一：mysql 对大数据量（百万行以上级别）的表进行删除操作：

delete from user where userId > 111;

一般来说，删除的操作这样写就行了，但是对于大数据量，特别是当数据量级别到达百万级的时候，一定一定不要轻易使用这个这个语句，一般遇到这种情况，最基本的必须考虑，假如我删除的数据到达了五十万行的时候，遇到错误，然后数据回滚了，那么这个回滚的耗时是巨可怕的，所以，最基本的处理方案就是，将数据集切割，分组成多个数据集：

int minId = findMinId();
int maxId = findMaxId();
// 后边分段删除，每次偏移 10 万

for(int i = minId ; i <= maxId; i += 100000)
{    
    int temp = i + 100000;
    delete from `users` where `id` >= i and `id` < temp and `age` > 10;
}

11.数据库中的六大范式总结

数据库的范式一般大家说的有六大范式。但是主要的有三大范式。

• 范式的简介

范式的英文名称是Normal Form，它是英国人E.F.Codd（关系数据库的老祖宗）在上个世纪70年代提出关系数据库模型后总结出来的。范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。目前有迹可寻的共有8种范式，依次是：1NF，2NF，3NF，BCNF，4NF，5NF，DKNF，6NF。通常所用到的只是前三个范式，即：第一范式（1NF），第二范式（2NF），第三范式（3NF）。

数据往往种类繁多，而且每种数据之间又互相关联，因此，在设计数据库时，所需要满足的范式越多，那表的层次及结构也就越复杂，最终造成数据的处理困难。这样，还不如不满足这些范式呢。所以在使用范式的时候也要细细斟酌，是否一定要使用该范式，必须根据实际情况做出选择。一般情况下，我们使用前三个范式已经够用了，不再使用更多范式，就能完成对数据的优化，达到最优效果。

• 通俗的理解

第一范式就是属性不可分割，每个字段都应该是不可再拆分的。比如一个字段是姓名（NAME），在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。

第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。比如说有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任，系主任也存到这个学生表中，那就会造成数据大量的冗余，一是这些信息在系信息表中已存在，二是系中有1000个学生的话这些信息就要存1000遍。因此第三范式的做法是在学生表中增加一个系编号的字段（外键），与系信息表做关联。
（又比如有一个专家信息表，假如专家的姓名，性别，邮箱，住址，照片，另外还有一个表存着专家的专业领域的，但是某一次查询中，为了方便起见，你在专家的信息表中
添加了专家的领域信息，那么这个专家的信息表就会存在大量的信息冗余，造成查询的过程中内存的消耗过大，所以，对于这种问题最好的就是加个外键，联系到专家领域表中去查询。）
同时需要注意的是，使用外键其实是在一定程度上可以加快查询的速度而已，但是对于增加，删除，修改操作的时候，使用了外键反而会将速度降低。

12.MySQL中的常用的防SQL注入的方式

SQL注入的定义：

通过操作输入来修改SQL语句来非法获取数据库中的数据

解决思路：
使用正则表达式或者PreparedStatement之类的。（这是初级内容）

面试常用答案：

SQL注入产生的原因：程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行。

防止SQL注入的方式：

开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置

执行sql语句时使用addslashes进行sql语句转换 Sql语句书写尽量不要省略双引号和单引号。 过滤掉sql语句中的一些关键词：update、insert、delete、select、 * 。

提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的

13.MySQL中常考的题型总结

求出员工表的中的前三高的工资

我们先找出公司里前 3 高的薪水，意思是不超过三个值比这些值大

SELECT e1.Salary 
FROM Employee AS e1
WHERE 3 > 
		(SELECT  count(DISTINCT e2.Salary) 
		 FROM	Employee AS e2 
	 	 WHERE	e1.Salary < e2.Salary 	AND e1.DepartmentId = e2.DepartmentId) ;

举个栗子：
当 e1 = e2 = [4,5,6,7,8]

e1.Salary = 4，e2.Salary 可以取值 [5,6,7,8]，count(DISTINCT e2.Salary) = 4

e1.Salary = 5，e2.Salary 可以取值 [6,7,8]，count(DISTINCT e2.Salary) = 3

e1.Salary = 6，e2.Salary 可以取值 [7,8]，count(DISTINCT e2.Salary) = 2

e1.Salary = 7，e2.Salary 可以取值 [8]，count(DISTINCT e2.Salary) = 1

e1.Salary = 8，e2.Salary 可以取值 []，count(DISTINCT e2.Salary) = 0

最后 3 > count(DISTINCT e2.Salary)，所以 e1.Salary 可取值为 [6,7,8]，即集合前 3 高的薪水

再把表 Department 和表 Employee 连接，获得各个部门工资前三高的员工。

SELECT
	Department.NAME AS Department,
	e1.NAME AS Employee,
	e1.Salary AS Salary 
FROM
	Employee AS e1,Department 
WHERE
	e1.DepartmentId = Department.Id 
	AND 3 > (SELECT  count( DISTINCT e2.Salary ) 
			 FROM	Employee AS e2 
			 WHERE	e1.Salary < e2.Salary 	AND e1.DepartmentId = e2.DepartmentId 	) 
ORDER BY Department.NAME,Salary DESC;

当然，对于这个题目，还有一种暴力的解法就是，直接查询出第三高的工资，然后排序、去重，取出前边的三位。

求出员工表中的第2高的工资

这里需要特别注意 offset 的用法

select (
    select distinct salary from Employee 
    order by salary desc 
    limit 1 offset 1
) as SecondHighestSalary

求出员工表中的第N高的工资

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    set n := N - 1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct salary from employee order by salary desc limit 1 offset n
  );
END

删除id不重复，但是email重复的员工表中的数据

delete from Person where id not in (select min(id) from (select * from Person) as t group by t.email);

对于这种情况，存在的子查询太多了，所以一般使用自连接的方式简化。
首先，这种情况下，最关键的是需要寻找出所有的需要删除的数据，所以首先做的工作就是:

select p1.* from Person p1,Person p2 where p1.email = p2.email and p1.id > p2.id;

找出来需删除的数据之后，由于mysql语言的特性，所以可以直接吧查询出来的结果直接删除：

delete p1 from Person p1,Person p2 where p1.email = p2.email and p1.id > p2.id;

学生分数排名问题

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

返回：

最简单的解法就是：

select Score,
(select count(distinct s1.Score) from Scores s1 where s1.Score >= s2.Score) as 'rank'
 from Scores s2 order by Score desc 

第二种解法就是借鉴oracle中的窗口函数 rank() 解决，在oracle没有收购mysql之前，要注意的是那些mysql版本是不能使用rank()这个关键字的。

select Score, dense_rank() over (order by Score desc)  as `Rank`
from Scores;

对于rank，dense_rank()，row_number()这几个函数的使用可以参考：

列表中的重复元素问题

题目描述：

解答：

select distinct l1.num as ConsecutiveNums 
from logs l1,logs l2,logs l3
where l1.id = l2.id - 1
and l2.id = l3.id - 1
and l1.num = l2.num
and l2.num = l3.num;

关键就是distinct关键字的使用。

统计超过经理工资收入的员工

使用子查询做：

select name as employee from employee e1 where e1.salary > (select salary from employee e2 where e1.managerId = e2.id);

使用连接查询做(速度比上一个提升四倍)：

select e1.name as employee from employee e1 ,employee e2 where e1.managerId = e2.id and e1.salary > e2.salary;

select e1.name as employee from employee e1 join employee e2 on e1.managerId = e2.id and e1.salary > e2.salary;

查找出重复的邮箱

方法一：使用视图的的形式来做：

select email from
(select email,count(email) as num from person group by email) as statistic
where num > 1;

方法二：使用group by + having字段来做，性能有较大的提升

select email from
person group by email having count(email) > 1;

找出从不订购的客户姓名

方法1：使用子查询

select name as Customers  from customers where id not in (select c.id from Customers c,Orders o where c.id = o.CustomerId );

上边的语句可以简化成：

select name as Customers  from customers where id not in (select CustomerId from orders);

统计出各个部门中工资最高的员工

select d.name as Department,e.name as Employee,e.salary as Salary 
from employee e 
join department d 
on e.departmentId = d.id
where (e.salary,e.departmentId) in 
(select max(salary),departmentId from employee group by departmentId);

按照姓名的最后两个字母进行排序

select first_name from employees order by substr(first_name,length(first_name) - 1)