MySQL笔试面试题

1. SQL分析题

1、学生表user(uid,name)学科成绩表course result (id,uid,course,result)用SQL 查出有3个学科成绩大于90分的同学的姓名

user表:

course_restful表:

sql:

SELECT
    a.`name`
FROM
    `user` AS a
RIGHT JOIN (
    SELECT
        *, count(*) AS num
    FROM
        course_restful
    WHERE
        restful > 90
    GROUP BY
        uid
    HAVING
        num > 2
) AS b ON b.uid = a.uid

2、

2. 数据库设计题

3. 事务与锁以及索引

1、说说mysql事务的隔离级别,会有哪些问题?通常如何解决?项目中是否有修改过隔离级别?

mysql隔离级别分别是读取未提交,读取已提交,可重复读取,序列化四个级别,相对的会产生的问题是脏读,不可重复读,幻读.

脏读：

对于脏读问题,通常出现在读取未提交隔离级别下,在事务2中会查询到事务1还未提交的数据,可以通过提高隔离级别的方式解决该问题,也可以给相应数据加锁解决,以下是加锁演示:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from products where id = 1 for update;
+----+------------+---------+-------+
| id | name       | price   | stock |
+----+------------+---------+-------+
|  1 | HUAWEI P40 | 3299.00 |   100 |
+----+------------+---------+-------+
1 row in set (0.00 sec)

mysql> update products set stock = stock - 1 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from products where id = 1 for update;
+----+------------+---------+-------+
| id | name       | price   | stock |
+----+------------+---------+-------+
|  1 | HUAWEI P40 | 3299.00 |    99 |
+----+------------+---------+-------+
1 row in set (12.87 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

可以看到的是事务依然是查询到了修改的数据,但是这并不属于脏读,而是mysql事务中查询语句加锁之后的当前磁盘数据读取,如果是未加锁的可能会是快照读取,读取的是事务开启前的数据.

不可重复读取:

对于不可重读读取,是在读取已提交的情况下,事务1修改了数据,并已经提交数据,此时按照事务的一致性特点,事务2是不能读取事务开启前不一样的数据的即,此时事务2只能读取到事务开启前的数据.要保证事务的一致性,我们需要提高事务的隔离级别到可重复读

幻读:

对于幻读问题,主要是在新增的数据情况下,事务1查询数据是否存在,不存在则新增数据,事务2一样查询数据是否存在,不存在则一样新增数据,因为mysql对于新增,修改,删除语句是默认加锁的,事务2必然会进入锁等待,等到事务1提交后会发现数据已存在导致数据新增失败,但是查询明明是不存在的,这就是幻读.这个问题我们可以通过将隔离级别提升到串行化解决亦可以给查询的数据加个间隙锁,即锁住一块区域的数据,使查询阶段就进入到锁等待,锁的查询会是磁盘此时实际的数据读取,事务1提交后是能够查询到数据的,这样就能够不去新增数据了.

下面是演示:

事务1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from products where id > 1 and id < 10 for update;
+----+------------+----------+-------+
| id | name       | price    | stock |
+----+------------+----------+-------+
|  2 | HUAWEI P50 |  4999.00 |   100 |
|  3 | HUAWEI P60 | 10000.00 |   100 |
|  5 | HUAWEI P60 | 10000.00 |   100 |
+----+------------+----------+-------+
3 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

事务2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from products where id > 1 and id < 10 for update;
+----+------------+----------+-------+
| id | name       | price    | stock |
+----+------------+----------+-------+
|  2 | HUAWEI P50 |  4999.00 |   100 |
|  3 | HUAWEI P60 | 10000.00 |   100 |
|  5 | HUAWEI P60 | 10000.00 |   100 |
+----+------------+----------+-------+
3 rows in set (48.11 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

2、索引的底层是怎样的?BTree树与B+Tree树有什么区别?innodb存储引擎为什么使用B+Tree树而不使用B-Tree?

3、谈谈你对锁的理解

说说自己对于 MySQL 常见的两种存储引擎：MyISAM与InnoDB的理解？

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

MyIASM 引擎：MySQL 的默认引擎，但不提供事务的支持，也不支持行级锁和外键。因此当执行插入和更新语句时，即执行写操作的时候需要锁定这个表，所以会导致效率会降低。不过和 InnoDB 不同的是，MyIASM 引擎是保存了表的行数，于是当进行 select count() from table 语句时，可以直接的读取已经保存的值而不需要进行扫描全表。所以，如果表的读操作远远多于写操作时，并且不需要事务的支持的，可以将 MyIASM 作为数据库引擎的首选。

MySQL里有2000w数据，Redis中只存20w的数据，如何保证Redis中的数据都是热点数据?

redis 提供 6种数据淘汰策略：

volatile-lru：从已设置过期时间的数据集（server.db[i].expires）中挑选最近最少使用的数据淘汰
volatile-ttl：从已设置过期时间的数据集（server.db[i].expires）中挑选将要过期的数据淘汰
volatile-random：从已设置过期时间的数据集（server.db[i].expires）中任意选择数据淘汰
allkeys-lru：当内存不足以容纳新写入数据时，在键空间中，移除最近最少使用的key（这个是最常用的）
allkeys-random：从数据集（server.db[i].dict）中任意选择数据淘汰
no-eviction：禁止驱逐数据，也就是说当内存不足以容纳新写入数据时，新写入操作会报错。这个应该没人使用吧！
4.0版本后增加以下两种：
volatile-lfu：从已设置过期时间的数据集(server.db[i].expires)中挑选最不经常使用的数据淘汰
allkeys-lfu：当内存不足以容纳新写入数据时，在键空间中，移除最不经常使用的key
使用策略规则：

1、如果数据呈现幂律分布，也就是一部分数据访问频率高，一部分数据访问频率低，则使用allkeys-lru

2、如果数据呈现平等分布，也就是所有的数据访问频率都相同，则使用allkeys-random

如何进行大表优化？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围
   务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
2. 读/写分离
   经典的数据库拆分方案，主库负责写，从库负责读；
3. 垂直分区
   根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。 数据库垂直分区

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
4. 水平分区
保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

数据库水平拆分

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

解释一下什么是池化设计思想。什么是数据库连接池?为什么需要数据库连接池?

池话设计应该不是一个新名词。我们常见的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好比你去食堂打饭，打饭的大妈会先把饭盛好几份放那里，你来了就直接拿着饭盒加菜即可，不用再临时又盛饭又打菜，效率就高了。除了初始化资源，池化设计还包括如下这些特征：池子的初始值、池子的活跃值、池子的最大值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。——这篇文章对池化设计思想介绍的还不错，直接复制过来，避免重复造轮子了。

数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。连接池还减少了用户必须等待建立与数据库的连接的时间。

分库分表之后,id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章：https://tech.meituan.com/2017/04/21/mt-leaf.html 。

做过哪些MySQL索引相关优化？

尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗.
MySQL5.6之后引入了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗.
若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表.
联合索引将高频字段放在最左边.

说一下乐观锁和悲观锁？

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。 数据库的乐观锁需要自己实现，在表里面添加一个 version 字段，每次修改成功值加 1，这样每次修改的时候先对比一下，自己拥有的 version 和数据库现在的 version 是否一致，如果不一致就不修改，这样就实现了乐观锁。

简单描述mysql中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。
索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

为什么elasticsearch 的搜索就是比mysql要快一点呢？数据结构上的特性？

Mysql 只有 term dictionary 这一层，是以 b-tree 排序的方式存储在磁盘上的。检索一个 term 需要若干次的 random access 的磁盘操作。而 Lucene 在 term dictionary 的基础上添加了 term index 来加速检索，term index 以树的形式缓存在内存中。从 term index 查到对应的 term dictionary 的 block 位置之后，再去磁盘上找 term，大大减少了磁盘的 random access 次数。

额外值得一提的两点是：term index 在内存中是以 FST（finite state transducers）的形式保存的，其特点是非常节省内存。Term dictionary 在磁盘上是以分 block 的方式保存的，一个 block 内部利用公共前缀压缩，比如都是 Ab 开头的单词就可以把 Ab 省去。这样 term dictionary 可以比 b-tree 更节约磁盘空间。

两者对比
对于倒排索引，要分两种情况：

1、基于分词后的全文检索

这种情况是es的强项，而对于mysql关系型数据库而言完全是灾难

因为es分词后，每个字都可以利用FST高速找到倒排索引的位置，并迅速获取文档id列表

但是对于mysql检索中间的词只能全表扫（如果不是搜头几个字符）

2、精确检索

这种情况我想两种相差不大，有些情况下mysql的可能会更快些

如果mysql的非聚合索引用上了覆盖索引，无需回表，则速度可能更快

es还是通过FST找到倒排索引的位置并获取文档id列表，再根据文档id获取文档并根据相关度算分进行排序，但es还有个杀手锏，即天然的分布式使得在大数据量面前可以通过分片降低每个分片的检索规模，并且可以并行检索提升效率

用filter时更是可以直接跳过检索直接走缓存

什么是Term Index？

将分词后的term进行排序索引，类似的mysql对于”term”(即主键，或者索引键)只是做了排序, 并且是大部分是放在磁盘上的，只有B+树的上层才是放在内存中的，查询仍然需要logN的访问磁盘，而ES将term分词排序后还做了一次索引，term index，即将term的通用前缀取出，构建成Trie树
通过这个树可以快速的定位到Term dictionary的本term的offset，再经过顺序查找，便可以很快找到本term的posting list。

mysql都有什么锁，死锁判定原理和具体场景，死锁怎么解决?

MySQL有三种锁的级别：页级、表级、行级。

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
什么是死锁？

死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。

表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

死锁的解决办法?

1.查出的线程杀死 kill
SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

2.设置锁的超时时间
Innodb 行锁的等待时间，单位秒。可在会话级别设置，RDS 实例该参数的默认值为 50（秒）。

生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值
该参数支持在会话级别修改，方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间，如下：
set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间，单位秒。

3.指定获取锁的顺序

Redis 和 Mysql 的数据不一致怎么办？

采用延时双删策略

第二个删redis。如果删除了缓存Redis，还没有来得及写库MySQL，另一个线程就来读取，发现缓存为空，则去数据库中读取数据写入缓存，此时缓存中为脏数据。
第一个删redis。如果先写了库，在删除缓存前，写库的线程宕机了，没有删除掉缓存，则也会出现数据不一致情况。

（1）先淘汰缓存

（2）再写数据库（这两步和原来一样）

（3）休眠800ms，再次淘汰缓存

这么做，可以将800ms内所造成的缓存脏数据，再次删除。

根据你以往的经验简单叙述一下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来导入大量数据，这比单纯的indert快好多.
8).经常OPTIMIZE TABLE 来整理碎片.
9).还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。
3.系统的瓶颈
1).磁盘搜索.
并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.
2).磁盘读写(IO)
可以从多个媒介中并行的读取数据。
3).CPU周期
数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
4).内存带宽
当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈.

分库分表-数据扩容与迁移

配置mycat数据迁移配置表(conf/migrateTables.properties)

TESTDB=article

TESTDB为需要进行扩容与数据迁移的库,article为需要扩容与迁移的表
如果有多个以’,’进行分隔,多个库则是单独进行配置

新建新的规则配置文件与数据源配置文件:
原配置文件名:

rule.xml
schema.xml

复制他们为新的配置文件:

newRule.xml
newSchema.xml

修改配置文件:

newRule.xml

    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">128</property>
    </function>

假设旧的分表规则是对64进行取模,接下来我们扩大两倍,就将配置改为128

newSchema.xml

<table name="article" primaryKey="id" subTables="article$0-127" dataNode="dn1" rule="mod-long" />

原先对表的切分是0-63,因为我们目前扩容到128,改为从0-127

然后进入mycat的bin目录下进行配置:

dataMigrate.sh

[root@localhost bin]./dataMigrate.sh

运行起来后程序会有4个阶段,为需要迁移的数据创建一个临时的目录进行存储,然后在判断目前的切分表中的数据,如果有数据,则会规划到迁移与扩容规划中,如果无数据,则判定为无需进行扩容与迁移.

第二阶段是开始进行数据迁移
第三阶段是删除临时存储的数据
第四阶段是验证迁移的数据与老库是否一致

隐藏问题:
脚本可能执行报错如下:

这个可以使用vim命令进入脚本,然后按下esc键,打个冒号,输入set ff=unix,最后wq保存退出即可.

4. 集群问题解决

使用主从的原因:

1.实现服务器负载均衡
即可以通过在主服务器和从服务器之间切分处理客户查询的负荷，从而得到更好的客户相应时间。通常情况下，数据库管理员会有两种思路。

一是在主服务器上只实现数据的更新操作。包括数据记录的更新、删除、新建等等作业。而不关心数据的查询作业。数据库管理员将数据的查询请求全部 转发到从服务器中。这在某些应用中会比较有用。如某些应用，像基金净值预测的网站。其数据的更新都是有管理员更新的，即更新的用户比较少。而查询的用户数 量会非常的多。此时就可以设置一台主服务器，专门用来数据的更新。同时设置多台从服务器，用来负责用户信息的查询

二是在主服务器上与从服务器切分查询的作业。在这种思路下，主服务器不单单要完成数据的更新、删除、插入等作业，同时也需要负担一部分查询作业。而从服务器的话，只负责数据的查询。当主服务器比较忙时，部分查询请求会自动发送到从服务器重，以降低主服务器的工作负荷。

2.通过复制实现数据的异地备份
可以定期的将数据从主服务器上复制到从服务器上，这无疑是先了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份 作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

3.提高数据库系统的可用性

数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用来数据的更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员也会采用两种手段。

一是主服务器故障之后，虽然从服务器取代了主服务器的位置，但是对于主服务器可以采取的操作仍然做了一些限制。如仍然只能够进行数据的查询，而 不能够进行数据的更新、删除等操作。这主要是从数据的安全性考虑。如现在一些银行系统的升级，在升级的过程中，只能够查询余额而不能够取钱。这是同样的道 理。

二是从服务器真正变成了主服务器。当从服务器切换为主服务器之后，其地位完全与原先的主服务器相同。此时可以实现对数据的查询、更新、删除等操 作。为此就需要做好数据的安全性工作。即数据的安全策略，要与原先的主服务器完全相同。否则的话，就可能会留下一定的安全隐患