Mysql面试题

52、Mysql中有哪几种锁？

MyISAM 支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

表级锁:开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量 最低。

行级锁:开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

53、Mysql支持事务吗？

在缺省模式下，MYSQL 是 autocommit 模式的，所有的数据库更新操作都会即时提交，所 以在缺省情况下，mysql 是不支持事务的。但是如果你的 MYSQL 表类型是使用 InnoDB Tables 或 BDB tables 的话，你的 MYSQL 就可以 使用事务处理,使用 SET AUTOCOMMIT=0 就可以使 MYSQL 允许在非 autocommit 模式，在非 autocommit 模式下，你必须使用 COMMIT 来提交你的更改，或者用 ROLLBACK 来回滚你的 更改。更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』

示例如下：

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1; 
UPDATE table2 SET summmary=@A WHERE type=1; 
COMMIT;

54、Mysql查询是否区分大小写？

不区分。

55、列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况?

答：它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

56、一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录， 再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15 ?

如果表的类型是 MyISAM，那么是 18。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大ID 也不会丢失。

如果表的类型是 InnoDB，那么是 15。InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行 OPTIMIZE 操作，都会导致最大 ID 丢失。

57、数据库三范式是什么?

第一范式(1NF):字段具有原子性,不可再分。(所有关系型数据库系 统都满足第一范式数据库表中的字段都是单一属性的，不可再分)
第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，即满足 第二范式(2NF)必须先满足第一范式(1NF)。要求数据库表中的每 个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储 各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。
满足第三范式(3NF)必须先满足第二范式(2NF)。简而言之，第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

所以第三范式具有如下特征:

1. 每一列只有一个值
2. 每一行都能区分
3. 每一个表都不包含其他表已经包含的非主关键字信息

58、mysql 的复制原理以及流程？

答：Mysql 内建的复制功能是构建大型，高性能应用程序的基础。将 Mysql 的数据 分布到多个系统上去，这种分布的机制，是通过将 Mysql 的某一台主机的数据 复制到其它主机(slaves)上，并重新执行一遍来实现的。* 复制过程中一 个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将 更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志 可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主 服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生 的任何更新，然后封锁并等待主服务器通知新的更新。 过程如下 ：1. 主服务器 把更新记录到二进制日志文件中。 2. 从服务器把主服务器的二进制日志拷贝 到自己的中继日志(replay log)中。 3. 从服务器重做中继日志中的时间， 把更新应用到自己的数据库上。

59、mysql 中 myISAM与 innodb 的区别?

1. 事务支持 > MyISAM:强调的是性能，每次查询具有原子性,其执行数 度比 InnoDB 类型更快，但是不提供事务支持。> InnoDB:提供事 务支持事务，外部键等高级数据库功能。具有事务(commit)、回滚 (rollback)和崩溃修复能力(crash recovery capabilities)的事务安全 (transaction-safe (ACID compliant))型表。
2. InnoDB 支持行级锁，而 MyISAM 支持表级锁. >> 用户在操作 myisam 表时，select，update，delete，insert 语句都会给表自动 加锁，如果加锁以后的表满足 insert 并发的情况下，可以在表的尾部插 入新的数据。
3. InnoDB 支持 MVCC, 而 MyISAM 不支持。
4. InnoDB支持外键，而MyISAM不支持。
5. 表主键 > MyISAM:允许没有任何索引和主键的表存在，索引都是保存行的地址。> InnoDB:如果没有设定主键或者非空唯一索引，就会 自动生成一个
6. 字节的主键(用户不可见)，数据是主索引的一部分，附 加索引保存的是主索引的值。 6.InnoDB不支持全文索引，而MyISAM支持。
7. 可移植性、备份及恢复 > MyISAM:数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进 行操作。> InnoDB:免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了。
8. 存储结构 > MyISAM:每个 MyISAM 在磁盘上存储成三个文件。第一 个文件的名字以表的名字开始，扩展名指出文件类型。.frm 文件存储表 定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名 是.MYI (MYIndex)。> InnoDB:所有的表都保存在同一个数据文件 中(也可能是多个文件，或者是独立的表空间文件)，InnoDB 表的大 小只受限于操作系统文件的大小，一般为 2GB。

60、MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区 别?

1. Read Uncommitted(读取未提交内容) >> 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读(Dirty Read)。
2. Read Committed(读取提交内容) >> 这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读(Nonrepeatable Read)，因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。
3. Repeatable Read(可重读) >> 这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题:幻读(Phantom Read)。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。InnoDB 和 Falcon 存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control 间隙锁)机制解决了该问题。注:其实多版本只是解决不可重复读问题，而加上间隙锁(也就是它这里所谓的并发控制)才解决了幻读问题。
4. Serializable(可串行化) >> 这是最高的隔离级别，它通过强制事务 排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个 读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

61、什么是索引？请简述常用的索引有哪些种类?

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则在表中搜索所有的行相比，索引有助于更快地获取信息

通俗的讲，索引就是数据的目录，就像看书一样，假如我想看第三章第四节的内容，如果有目录，我直接翻目录，找到第三章第四节的页码即可。如果没有目录，我就需要将从书的开头开始，一页一页翻，直到翻到第三章第四节的内容。

InnoDB：支持 B-tree，Full-text 等索引，不支持 Hash 索引；

MyISAM：支持 B-tree，Full-text 等索引，不支持 Hash 索引；

Memory：支持 B-tree，Hash 等索引，不支持 Full-text 索引；

NDB：支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive：不支持 B-tree、Hash、Full-text 等索引；

1、MySQL索引的分类

我们根据对以列属性生成的索引大致分为两类：

单列索引：以该表的单个列，生成的索引树，就称为该表的单列索引
组合索引：以该表的多个列组合，一起生成的索引树，就称为该表的组合索引。

2、单列索引又有具体细的划分：

主键索引：以该表主键生成的索引树，就称为该表的主键索引。
唯一索引：以该表唯一列生成的索引树，就称为该表的唯一索引。
普通索引：以该表的普通列（非主键，非唯一列）生成的索引树，就称为该表的普通索引。
全文索引：全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

62、索引是个什么样的数据结构呢?

答：索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等。

而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。

63、Hash索引和B+树所有有什么区别或者说优劣呢?

答：首先要知道Hash索引和B+树索引的底层实现原理:

hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。
B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

那么可以看出他们有以下的不同：

hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
hash索引不支持使用索引进行排序，原理同上。
hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测，AAAA和AAAAB的索引没有相关性。
hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
hash索引虽然在等值查询上较快，但是不稳定，性能不可预测。当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

64、上面提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,什么是聚簇索引?

答：在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引.。在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引，如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据,因此不用再次进行回表查询。

65、非聚簇索引一定会回表查询吗?

答：不一定。这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select age from employee where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

66、对MySQL的锁了解吗?

答：当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他使用完毕才可以再次使用。

67、MySQL都有哪些锁呢?像上面的例子进行锁定岂不是有点阻碍并发效率了?

答：从锁的类别上来讲，有共享锁和排他锁。

共享锁：又叫做读锁，当用户要进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个。
排他锁：又叫做写锁，当用户要进行数据的写入时，对数据加上排他锁，排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

用上面的例子来说就是用户的行为有两种，一种是来看房，多个用户一起看房是可以接受的。一种是真正的入住一晚，在这期间，无论是想入住的还是想看房的都不可以。锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。他们的加锁开销从大大小，并发能力也是从大到小。

68、MySQL的binlog有有几种录入格式?分别有什么区别?

答：有三种格式,statement,row和mixed。

statement模式下，记录单元为语句。即每一个sql造成的影响会记录，由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
row级别下，记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

69、一条sql执行过长的时间，你如何优化，从哪些方面？

1、查看 sql 是否涉及多表的联表或者子查询，如果有，看是否能进行业务拆分，相关字段冗余或者合并成临时表（业务和算法的优化）。

2、涉及链表的查询，是否能进行分表查询，单表查询之后的结果进行字段整合。

3、如果以上两种都不能操作，非要链表查询，那么考虑对相对应的查询条件做索引。加快查询速度。

4、针对数量大的表进行历史表分离（如交易流水表）。

5、数据库主从分离，读写分离，降低读写针对同一表同时的压力，至于主从同步，mysql 有自带的 binlog 实现主从同步。

6、explain 分析 sql 语句，查看执行计划，分析索引是否用上，分析扫描行数等等。

7、查看 mysql 执行日志，看看是否有其他方面的问题。

上面我将 explain 关键字加粗显示，就是很多面试官他并不直接问你 sql 优化，他会问你知道什么是 mysql 的执行计划吗？其实就是想考你知不知道 explain 关键字，所以乡亲们对 explain 这个不了解的，还需要自己线下去网上查看学习一下哦。

16、数据库的优化

1.优化索引、SQL语句、分析慢查询。

2.设计表的时候严格根据数据库的设计范式来设计数据库。

三大范式：

1.表字段的原子性（不可拆分）；

2.满足第一范式的基础上，有主键依赖；

3.满足第一二范式的基础上，非主属性之间没有依赖关系。

3.使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘IO。

4.优化硬件；采用SSD，使用磁盘队列技术等。

5.采用MySQL内部自带的表分区技术，把数据分成不同的文件，能够提高磁盘的读取效率。

6.垂直分表；把一些不经常读的数据放在一张表里，节约磁盘IO。

7.主从分离读写；采用主从复制把数据库的读操作和写入操作分离开来；

8.分库分表机器（数据量特别大），主要的原理就是数据路由。

9.选择合适的表引擎，参数上的优化。

10.进行架构级别的缓存，静态化和分布式。

11.不采用全文索引。

12.采用更快的存储方式，例如NoSQL存储经常访问的数据。

17、SQL语句

我们后面查询用到的表：

mysql> select * from t_score;
+------+--------------+-----------+--------+
| c_id | c_student_id | c_english | c_math |
+------+--------------+-----------+--------+
|    1 |            1 |      60.5 |     99 |
|    2 |            2 |      65.5 |     60 |
|    3 |            3 |      70.5 |     88 |
|    4 |            4 |      60.5 |     77 |
|    5 |            5 |      60.5 |     89 |
|    6 |            6 |        90 |     93 |
|    7 |            7 |        80 |     99 |
|    8 |            8 |        88 |     99 |
|    9 |            9 |        77 |     60 |
|   10 |           10 |        75 |     86 |
|   11 |           11 |        60 |     60 |
|   12 |           12 |        88 |     99 |
|   13 |           13 |        77 |     59 |
|   14 |           14 |      NULL |     59 |
|   15 |           15 |        60 |   NULL |

+------+--------------+-----------+--------+

1.单表查询

1.1mysql中的分页查询。

语法：

select * from 表名 limit (page-1)*count,count;

page指的是页码，count指的是每页显示的条数。

# 每页3条数据，查询第三页的数据，(3-1)*3=6.
mysql> select * from t_score limit 6,3;
+------+--------------+-----------+--------+
| c_id | c_student_id | c_english | c_math |
+------+--------------+-----------+--------+
|    7 |            7 |        80 |     99 |
|    8 |            8 |        88 |     99 |
|    9 |            9 |        77 |     60 |
+------+--------------+-----------+--------+

1.2.求和：

# 求数学学科的总成绩
mysql> select sum(c_math) from t_score;
+-------------+
| sum(c_math) |
+-------------+
|        1127 |
+-------------+

1.3.求平均：

# 求数学学科的平均成绩
mysql> select avg(c_math) from t_score;
+-------------+
| avg(c_math) |
+-------------+
|        80.5 |
+-------------+

1.4.求最大最小值：

# 找到数学最高分
mysql> select max(c_math) from t_score;
+-------------+
| max(c_math) |
+-------------+
|          99 |
+-------------+
# 找到数学最低分
mysql> select min(c_math) from t_score;
+-------------+
| min(c_math) |
+-------------+
|          59 |
+-------------+

1.5.统计记录总数：

# 统计参加数学考试的人有多少
mysql> select count(*) from t_score;
+----------+
| count(*) |
+----------+
|       15 |
+----------+

1.6.分组：

group_by后面的字段名要和select后面的字段名相同，否则会报错。

# 从成绩表中取出数学成绩进行分组
mysql> select c_math from t_score group by c_math;
+--------+
| c_math |
+--------+
|   NULL |
|     59 |
|     60 |
|     77 |
|     86 |
|     88 |
|     89 |
|     93 |
|     99 |
+--------+

1.7.根据分组结果，使用group_concat()来获取分组中指定字段的集合

# 根据数据成绩进行分组，获取每个分数中学生的编号
mysql> select c_math,group_concat(c_student_id) from t_score group by c_math;
+--------+----------------------------+
| c_math | group_concat(c_student_id) |
+--------+----------------------------+
|   NULL | 15                         |
|     59 | 13,14                      |
|     60 | 2,9,11                     |
|     77 | 4                          |
|     86 | 10                         |
|     88 | 3                          |
|     89 | 5                          |
|     93 | 6                          |
|     99 | 1,7,8,12                   |
+--------+----------------------------+

1.8.分组和聚合函数的使用

# 根据性别进行分组，求出每组同学的最大年龄、最小年龄、年龄总和、平均年龄、人数
mysql> select c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(*) from t_student group by c_gender;
+----------+------------+------------+------------+------------+----------+
| c_gender | max(c_age) | min(c_age) | sum(c_age) | avg(c_age) | count(*) |
+----------+------------+------------+------------+------------+----------+
| 男       |         99 |         15 |       1084 |    47.1304 |       26 |
| 女       |         88 |         11 |        239 |    39.8333 |        7 |
+----------+------------+------------+------------+------------+----------+

1.9.having条件语句的使用。

# 从学生表中以性别进行分组，然后选出女生分组，并展示小组中所有的名字

mysql> select c_gender,group_concat(c_name) from t_student group by c_gender having c_gender='女';

+----------+-----------------------------------------------------------+

| c_gender | group_concat(c_name)                               

        |

+----------+-----------------------------------------------------------+

| 女       | 小龙女,白骨精,扈三娘,孙二娘,赵敏,嫦娥,孙                         |

+----------+-----------------------------------------------------------+

2.多表查询

# 学生表中保存了学生的信息和所在班级的ID，班级表中保存了班级的信息。 查询学生姓名和对应的班级

mysql> select t_student.c_name,t_class.c_name from t_student,t_class where t_student.c_class_id=t_class.c_id;

+-----------+-------------------------------------+

| c_name    | c_name                   
+-----------+-------------------------------------+

| 孙德龙    | 软件工程18级一班                        |

| 塔大      | 软件工程18级二班                        |

| 宋江      | 计算机科学与技术18级一班                 |

| 武松      | 计算机科学与技术18级二班                 |

| 孙二娘    | 网络工程18级一班                        |

| 扈三娘    | 网络工程18级二班                        |

| 鲁智深    | 软件工程18级一班                        |

| 林冲      | 软件工程18级二班                       |

| 阮小七    | 计算机科学与技术18级一班                 |

| 阮小五    | 计算机科学与技术18级二班                 |

| 阮小二    | 网络工程18级一班                       |

| 白骨精    | 网络工程18级二班                       |

| 孙悟空    | 软件工程18级一班                       |

| 猪八戒    | 软件工程18级二班                       |

| 沙和尚    | 计算机科学与技术18级一班                 |

| 唐三奘    | 计算机科学与技术18级二班                 |

| 哪吒      | 网络工程18级一班                       |

| 嫦娥      | 网络工程18级二班                       |

| 杨过      | 软件工程18级一班                       |

| 郭靖      | 软件工程18级二班                       |

| 洪七公    | 计算机科学与技术18级一班                 |

| 欧阳锋    | 计算机科学与技术18级二班                 |

| 黄药师    | 网络工程18级一班                       |

| 小龙女    | 网络工程18级二班                       |

| 孙%       | 软件工程18级一班                      |

| 张无忌    | 软件工程18级二班                       |

| 张翠山    | 计算机科学与技术18级一班                 |

| 张三丰    | 计算机科学与技术18级二班                 |

| 宋青书    | 网络工程18级一班                        |

| 赵敏      | 网络工程18级二班                       |

| 孙        | 计算机科学与技术18级一班                 |

| 孙子      | 计算机科学与技术18级一班                 |

| 孙        | 网络工程18级一班                       |

+-----------+-------------------------------------+

2.1.内连接查询

语法：

select * from 表1 inner join 表2 on 表1.列 运算符 表2.列

连接时必须指定连接条件，用on指定。如果无条件，那么会出现笛卡尔积。

#  查询学生姓名和对应的班级
mysql> select ts.c_name,tc.c_name from t_student as ts inner join t_class tc on ts.c_class_id=tc.c_id;
.....结果同上一个结果.......

上面的as代表的是为表起别名，也可以不写空格隔开。

2.2.左连接查询

语法：

select * from 表1 left join 表2 on 表1.列 运算符 表2.列

查询的结果为根据左表中的数据进行连接，如果右表中没有满足条件的记录，则连接空值。

mysql> select ts.c_name,tc.c_name from t_student as ts left join t_class tc on ts.c_class_id=tc.c_id;

+--------------+-------------------------------------+

| c_name       | c_name                          
    |

+--------------+-------------------------------------+

| 孙德龙       | 软件工程18级一班                        |

| 塔大         | 软件工程18级二班                       |

| 宋江         | 计算机科学与技术18级一班                |

| 武松         | 计算机科学与技术18级二班                |

| 孙二娘       | 网络工程18级一班                       |

| 扈三娘       | 网络工程18级二班                       |

| 鲁智深       | 软件工程18级一班                       |

| 林冲         | 软件工程18级二班                      |

| 阮小七       | 计算机科学与技术18级一班                |

| 阮小五       | 计算机科学与技术18级二班                |

| 阮小二       | 网络工程18级一班                       |

| 白骨精       | 网络工程18级二班                       |

| 孙悟空       | 软件工程18级一班                       |

| 猪八戒       | 软件工程18级二班                       |

| 沙和尚       | 计算机科学与技术18级一班                 |

| 唐三奘       | 计算机科学与技术18级二班                 |

| 哪吒         | 网络工程18级一班                       |

| 嫦娥         | 网络工程18级二班                       |

| 杨过         | 软件工程18级一班                       |

| 郭靖         | 软件工程18级二班                       |

| 洪七公       | 计算机科学与技术18级一班                 |

| 欧阳锋       | 计算机科学与技术18级二班                 |

| 黄药师       | 网络工程18级一班                       |

| 小龙女       | 网络工程18级二班                        |

| 孙%          | 软件工程18级一班                       |

| 张无忌       | 软件工程18级二班                        |

| 张翠山       | 计算机科学与技术18级一班                 |

| 张三丰       | 计算机科学与技术18级二班                 |

| 宋青书       | 网络工程18级一班                        |

| 赵敏         | 网络工程18级二班                       |

| 孙           | 计算机科学与技术18级一班                 |

| 孙子         | 计算机科学与技术18级一班                 |

| 孙           | 网络工程18级一班                       |

| 亦向枫     | NULL                                  |

+--------------+-------------------------------------+

2.3.子查询

语法：

select * from 表1 where 条件 运算符 (select查询)

子查询是单独可以执行的一条SQL语句，它作为主查询的条件或者数据源嵌套在主查询中。

2.3.1标量子查询（子查询返回的结果是一个数据(一行一列)）

# 查询班级中年龄大于平均年龄的学生信息
mysql> select * from t_student where c_age > (select avg(c_age) from t_student);
# 因为数据太多，为了展示效果，我们查询指定的一些字段
mysql> select c_id,c_name,c_gender,c_address from t_student where c_age > (select avg(c_age) from t_student);
+------+-----------+----------+-----------------------------+
| c_id | c_name    | c_gender | c_address                   |
+------+-----------+----------+-----------------------------+
|    7 | 鲁智深    | 男       | 北京市西城区西直门          |
|   15 | 沙和尚    | 男       | 北京市西城区西直门          |
|   16 | 唐三奘    | 男       | 北京市西城区西直门          |
|   18 | 嫦娥      | 女       | 北京市昌平霍营              |
|   19 | 杨过      | 男       | 北京市西城区西直门          |
|   20 | 郭靖      | 男       | 北京市西城区西直门          |
|   21 | 洪七公    | 男       | 北京市西城区西直门          |
|   22 | 欧阳锋    | 男       | 北京市西城区西直门          |
|   25 | 孙%       | 男       | 北京市西城区西直门          |
|   29 | 宋青书    | 男       | 北京市西城区西直门          |
|   30 | 赵敏      | 女       | 北京市昌平霍营              |
+------+-----------+----------+-----------------------------+

2.3.2列级子查询（子查询返回的结果是一列(一列多行)）

# 主查询 where 条件 in (列子查询)

# 查询出所有学生所在班级的班级名称

mysql> select c_name from t_class where c_id in (select c_class_id from t_student);

+-------------------------------------+

| c_name                              
|

+-------------------------------------+

| 软件工程18级一班                    |

| 软件工程18级二班                    |

| 计算机科学与技术18级一班            |

| 计算机科学与技术18级二班            |

| 网络工程18级一班                    |

| 网络工程18级二班                    |

+-------------------------------------+

2.3.3行级子查询（子查询返回的结果是一行(一行多列)）

# 主查询 where (字段1,2,...) = (行子查询) 
# 查询班级年龄最大，所在班号最小的学生
mysql> select c_id,c_name,c_gender,c_address from t_student where(c_age,c_class_id) = (select max(c_age),min(c_class_id) from t_student);
+------+-----------+----------+-----------------------------+
| c_id | c_name    | c_gender | c_address                   |
+------+-----------+----------+-----------------------------+
|    7 | 鲁智深     | 男        | 北京市西城区西直门             |
|   25 | 孙%       | 男        | 北京市西城区西直门             |
+------+-----------+----------+-----------------------------+

2.3.4.自连接查询

22、数据库优化的措施，你们项目开发中做过哪些优化？

答：数据库的优化措施有很多，常见的有优化索引、SQL语句；设计表的时候严格根据数据库的设计范式来设计数据库；使用缓存，将经常访问且不需要经常变化的数据放到缓存中，节约磁盘IO；优化硬件，采用固态等；垂直分表，就是将不经常读取的数据放到一张表中，节约磁盘IO；主从分离，读写分离；选择合适的引擎；不采用全文索引等措施。

我们在项目开发过程中尽量少的使用外键，因为外键约束会影响插入和删除性能；使用缓存，减少对数据库的访问；需要多次连接数据库的一个页面，将需要的数据一次性的取出，减少对数据库的查询次数。在我们查询操作中尽量避免全表扫描，避免使用游标，因为游标的效率很差，还避免大事务操作，提高并发能力。

24、MySQL引擎有哪些了解，用过什么？

答：主流的引擎有两个，分别是InnoDB和MyISAM。其中InnoDB支持事务，支持外键约束，它还支持行锁（比如select…for update语句，会触发行锁，但是锁定的是索引不是记录）。MyISAM不支持事务，不支持外键，它是数据库默认的引擎。InnoDB保存表的行数，如果看这个表有多少行的时候，InnoDB扫描整张表，MyISAM则是直接读取保存的行数即可。删除表的时候InnoDB是一行一行的删，而MyISAM则是重建表。InnoDB适合频繁修改以及安全性要求较高的应用，MyISAM适合查询为主的应用。在我们的项目中使用的是InnoDB。

25、缓存穿透、缓存击穿、缓存雪崩？

答：

缓存穿透指的是缓存和数据库中该数据没有，但是用户不断的发起请求（如发起id为-1或者id特别大不存在该数据的请求），从而使得数据库压力过大。这样就要考虑是不是受到了攻击。解决方法就是接口层增加校验，对id进行校验，过滤非法请求；如果对方执着于同一个ID暴力攻击，那么我们可以在缓存中将key-value写成key-null，缓存有效时间设置的短一点。

缓存击穿指的是缓存中没有，但是数据库中有（一般就是缓存时间到期了）的数据，这时并发用户特别多，缓存读不到，同时去数据库读数据，造成数据库压力瞬间增大的现象。解决的方法就是热点数据永远不过期；另一种方法就是牺牲一点用户体验保护数据库，加互斥锁。

缓存雪崩指的是缓存中数据大规模的到期，而查询数据量巨大，引发数据库压力过大。你也许会想，这不是缓存击穿吗？不是的，缓存击穿是用户查询同一条数据，而缓存雪崩则是用户查询不同的数据。解决方案就是缓存数据的时间设置为随机，防止同一时间大量数据过期；如果缓存数据采用分布式部署，那么热点数据给其他缓存数据库中也分点，雨露均沾嘛；还可以将热点数据设置为永不过期。

39、数据库事务

事务 Transaction 是指作为一个基本工作单元执行的一系列SQL语句的操作，要么完全地执行，要么完全地都不执行。事务的四大特性(ACID)：原子性、一致性、隔离性、持久性。

一个简单的例子(三个步骤打包为一个事务，任何一个失败，则必须回滚所有)：

1. 检查支票账户的余额高于或者等于200美元。
2. 从支票账户余额中减去200美元。
3. 在储蓄帐户余额中增加200美元。

1.原子性(Atomicity)

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

2.一致性(Consistency)

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行语句时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

3.隔离性(Isolation)

通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，一个事务未完成，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

4.持久性(Durability)

一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

39.1、事务的操作

开启事务(开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中)：

begin;或：start transaction;

提交事务(将缓存中的数据变更维护到物理表中)：

commit;

回滚事务(放弃缓存中变更的数据 表示事务执行失败 应该回到开始事务前的状态)：

rollback;

40、MySQL数据库索引

数据库索引是什么大家应该都已经知道。为什么建立索引，大家应该张口就来。算了，我还是简简单单的说一下吧：

数据库索引可以理解为数据库中一种排序的数据结构。它的存在就是为了协助快速查询、更新数据库表中的数据。优化查询效率。（简直和废话一样，谁不知道索引就像新华字典前面的音节索引和部首检字表一样…）

那么索引的原理呢？什么时候创建索引呢？索引有哪些呢？这些你想过吗？不知道就对了，我也不知道（会不会被打死…）。

MySQL中的索引用到了B+树、哈希桶等索引数据结构，但是主流还是B+树。那么为什么B+树适合做数据库索引呢？

1.B+树使得IO读写次数变少。

+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2.B+树查询效率稳定。

搜索任何一个关键字，所走的路径长度是一样的，也就是说查每一个数据的效率相同。

3.B+树只需要遍历叶子节点(也就是最底层没有子节点的节点)就可以达到遍历整棵树的目的，这也解决了数据库的范围查询问题，而B数是不支持这样的操作的。

什么时候建立索引，什么时候少建或者不建索引呢？

1.表记录太少的话，不要建立索引了，因为建立索引表会增加查询的步骤，处理变慢；

2.经常插入、删除、修改的表尽量少的建立索引，因为索引表的维护也会降低性能；

3.对于那些数据都是重复且分布平均的字段，比如一个字段只有True和False两种数据，但是记录超多（假设100万行），这样建立索引是提高不了查询速度的；

4.不要将超多的字段建立在一个索引里，它会增加数据修改、插入和删除的时间的。

5.对于百万、千万级的数据库建立索引，相信我，它会有质的飞跃。

6.对于不会出现在where条件中的字段不要建立索引，不要再增加索引表的体积了。

40.1创建索引的语句

1.1 ALTER TABLE
1.创建普通的索引

alter table <table_name> add index <index_name> (`字段名`);

2.创建多个索引

alter table <table_name> add index <index_name> (`column`,`column1`,`column_N`.......);

3.创建主键索引

alter table <table_name> add primary key (`字段名`);

4.创建唯一索引

alter table <table_name> add unique (`字段名`);

5.创建全文的索引

alter table <table_name> add fulltext (`字段名`);

1.2 CREATE INDEX
1.增加普通索引

create index <index_name> on table_name (`字段名`)

2.增加UNIQUE索引

create unique index <index_name> on <table_name> (`字段名`)

CREATE INDEX中索引名必须指定，而且只能增加普通索引和UNIQUE索引，不能增加PRIMARY KEY索引。

40.2、删除索引

drop index <index_name> on <table_name>;
alter table <table_name> drop index <index_name>;
alter table <table_name> drop primary key;

52事务隔离级别。

MySQL数据库事务隔离级别主要有四种：

Serializable (串行化)，一个事务一个事务的执行。
Repeatable read (可重复读)，无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响。
Read committed (读取已提交)，其他事务提交了对数据的修改后，本事务就能读取到修改后的数据值。
Read uncommitted (读取为提交)，其他事务只要修改了数据，即使未提交，本事务也能看到修改后的数据值。

MySQL数据库默认使用可重复读（ Repeatable read）。

61、简单谈谈ACID，并解释每一个特性。

答：ACID是事务的四大特性。分别为原子性，一致性，隔离性和持久性。原子性(Atomicity)指的是一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。一致性(Consistency)指的是数据库总是从一个一致性的状态转换到另一个一致性的状态，如果事务没有提交，中间某一步执行失败，那么事务中所做的修改并不会保存到数据库中。隔离性(Isolation)指的是一个事务所做的修改在最终提交以前，对其他事务是不可见的。持久性(Durability)指的是一旦事务提交，则其所做的修改会永久保存到数据库

62、MySQL的两个主流引擎，并介绍它们的区别。

答：主流的引擎有两个，分别是 InnoDB和 MyISAM。其中 InnoDB支持事务，支持外键约束，它还支持行锁（比如select…for update语句，会触发行锁，但是锁定的是索引不是记录）。 MyISAM不支持事务，不支持外键，它是数据库默认的引擎。 InnoDB保存表的行数，如果看这个表有多少行的时候， InnoDB扫描整张表， MyISAM则是直接读取保存的行数即可。删除表的时候 InnoDB是一行一行的删，而 MyISAM则是重建表。 InnoDB适合频繁修改以及安全性要求较高的应用， MyISAM适合查询为主的应用。在我们的项目中使用的是 InnoDB。