Mysql面试题

发布时间:2023年12月28日

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 * from1 inner join2 on1.列 运算符 表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 * from1 left join2 on1.列 运算符 表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 * from1 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。

文章来源:https://blog.csdn.net/weixin_53909748/article/details/135256372
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。