尚硅谷Java技术之北京高频面试题

版本：V6.0

尚硅谷Java技术中心

第五章 MySQL数据库篇

1、SQL 的select 语句完整的执行顺序

SQL Select 语句完整的执行顺序：

（1）from 子句组装来自不同数据源的数据；

（2）where 子句基于指定的条件对记录行进行筛选；

（3）group by 子句将数据划分为多个分组；

（4）使用聚集函数进行计算；

（5）使用 having 子句筛选分组；

（6）计算所有的表达式；

（7）select 的字段；

（8）使用order by 对结果集进行排序。

2、MySQL的事务

事务的基本要素（ACID）：

原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏
。比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的并发问题：

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B
在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果
不一致
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL事务隔离级别：

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

3、简述在MySQL数据库中MyISAM和InnoDB的区别

InnoDB存储引擎

主要面向OLTP(Online Transaction Processing，在线事务处理)方面的应用

特点：

行锁设计、支持外键；

MyISAM存储引擎

主要面向OLAP(Online Analytical Processing,在线分析处理)方面的应用。

特点：

不支持事务，支持表所和全文索引。操作速度快。

4、悲观锁和乐观锁的怎么实现？

悲观锁： select…for update是MySQL提供的实现悲观锁的方式。

例如：select price from item where id=100 for update

此时在items表中，id为100的那条数据就被我们锁定了，其它的要执行select
price from items where id=100 for
update的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。MySQL有个问题是select…for
update语句执行中所有扫描过的行都会被锁上，因此在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

乐观锁： 乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。

利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的"version"字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

举例：

//1: 查询出商品信息
select (quantity,version) from items where id=100;
//2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//3: 修改商品的库存
update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version};

5、你们公司有哪些数据库设计规范

（一）基础规范

1、表存储引擎必须使用InnoD，表字符集默认使用utf8，必要时候使用utf8mb4

解读：

（1）通用，无乱码风险，汉字3字节，英文1字节

（2）utf8mb4是utf8的超集，有存储4字节例如表情符号时，使用它

2、禁止使用存储过程，视图，触发器，Event

解读：

（1）对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层

（2）调试，排错，迁移都比较困难，扩展性较差

3、禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径

4、禁止在线上环境做数据库压力测试

5、测试，开发，线上数据库环境必须隔离

（二）命名规范

1、库名，表名，列名必须用小写，采用下划线分隔

解读：abc，Abc，ABC都是给自己埋坑

2、库名，表名，列名必须见名知义，长度不要超过32字符

解读：tmp，wushan谁知道这些库是干嘛的

3、库备份必须以bak为前缀，以日期为后缀

4、从库必须以-s为后缀

5、备库必须以-ss为后缀

（三）表设计规范

1、单实例表个数必须控制在2000个以内

2、单表分表个数必须控制在1024个以内

3、表必须有主键，推荐使用UNSIGNED整数为主键

潜在坑：删除无主键的表，如果是row模式的主从架构，从库会挂住

4、禁止使用外键，如果要保证完整性，应由应用程式实现

解读：外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈

5、建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据

（四）列设计规范

1、根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节

2、根据业务区分使用char/varchar

解读：

（1）字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高

（2）字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间

3、根据业务区分使用datetime/timestamp

解读：前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime

4、必须把字段定义为NOT NULL并设默认值

解读：

（1）NULL的列使用索引，索引统计，值都更加复杂，MySQL更难优化

（2）NULL需要更多的存储空间

（3）NULL只能采用IS NULL或者IS NOT NULL，而在=/!=/in/not in时有大坑

5、使用INT UNSIGNED存储IPv4，不要用char(15)

6、使用varchar(20)存储手机号，不要使用整数

解读：

（1）牵扯到国家代号，可能出现+/-/()等字符，例如+86

（2）手机号不会用来做数学运算

（3）varchar可以模糊查询，例如like ‘138%’

7、使用TINYINT来代替ENUM

解读：ENUM增加新值要进行DDL操作

（五）索引规范

1、唯一索引使用uniq_[字段名]来命名

2、非唯一索引使用idx_[字段名]来命名

3、单张表索引数量建议控制在5个以内

解读：

（1）互联网高并发业务，太多索引会影响写性能

（2）生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL选择不到最优索引

（3）异常复杂的查询需求，可以选择ES等更为适合的方式存储

4、组合索引字段数不建议超过5个

解读：如果5个字段还不能极大缩小row范围，八成是设计有问题

5、不建议在频繁更新的字段上建立索引

6、非必要不要进行JOIN查询，如果要进行JOIN查询，被JOIN的字段必须类型相同，并建立索引

解读：踩过因为JOIN字段类型不一致，而导致全表扫描的坑么？

7、理解组合索引最左前缀原则，避免重复建设索引，如果建立了(a,b,c)，相当于建立了(a),
(a,b), (a,b,c)

（六）SQL规范

1、禁止使用select *，只获取必要字段

解读：

（1）select *会增加cpu/io/内存/带宽的消耗

（2）指定字段能有效利用索引覆盖

（3）指定字段查询，在表结构变更时，能保证对应用程序无影响

2、insert必须指定字段，禁止使用insert into T values()

解读：指定字段插入，在表结构变更时，能保证对应用程序无影响

3、隐式类型转换会使索引失效，导致全表扫描

4、禁止在where条件列使用函数或者表达式

解读：导致不能命中索引，全表扫描

5、禁止负向查询以及%开头的模糊查询

解读：导致不能命中索引，全表扫描

6、禁止大表JOIN和子查询

7、同一个字段上的OR必须改写问IN，IN的值必须少于50个

8、应用程序必须捕获SQL异常

解读：方便定位线上问题

说明：本规范适用于并发量大，数据量大的典型互联网业务，可直接参考。

6、索引的底层实现是什么？什么情况下会索引失效？

InnoDB 存储引擎是用 B+Tree 实现其索引结构

失效条件：

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

2.对于多列索引，不是使用的第一部分，则不会使用索引

3.like查询以%开头

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

6. 组合索引要遵循最左匹配原则

7、MySQL性能优化

（1）尽量选择较小的列

（2）将where中用的比较频繁的字段建立索引

（3）select子句中避免使用’*’

（4）避免在索引列上使用计算、not in 和<>等操作

（5）当只需要一行数据的时候使用limit 1

（6）保证单表数据不超过200W，适时分割表。针对查询较慢的语句，可以使用explain
来分析该语句具体的执行情况。

（7）避免改变索引列的类型。

（8）选择最有效的表名顺序，from字句中写在最后的表是基础表，将被最先处理，在from子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

（9）避免在索引列上面进行计算。

（10）尽量缩小子查询的结果

8、SQL 语句优化案例

例1：where 子句中可以对字段进行 null 值判断吗？

可以，比如 select id from t where num is null 这样的 sql
也是可以的。但是最好不要给数据库留NULL，尽可能的使用 NOT NULL
填充数据库。不要以为 NULL 不需要空间，比如：char(100)
型，在字段建立时，空间就固定了，不管是否插入值（NULL
也包含在内），都是占用 100 个字符的空间的，如果是 varchar
这样的变长字段，null 不占用空间。可以在 num 上设置默认值 0，确保表中 num
列没有 null 值，然后这样查询：select id from t where num= 0。

例2：如何优化?下面的语句？

select * from admin left join log on admin.admin_id = log.admin_id
where log.admin_id>10

优化为：select * from (select * from admin where admin_id>10) T1 lef
join log on T1.admin_id = log.admin_id。

使用 JOIN 时候，应该用小的结果驱动大的结果（left join
左边表结果尽量小如果有条件应该放到左边先处理， right join
同理反向），同时尽量把牵涉到多表联合的查询拆分多个
query（多个连表查询效率低，容易到之后锁表和阻塞）。

例3：limit 的基数比较大时使用 between

例如：select * from admin order by admin_id limit 100000,10

优化为：select * from admin where admin_id between 100000 and 100010
order by admin_id。

例4：尽量避免在列上做运算，这样导致索引失效

例如：select * from admin where year(admin_time)>2014

优化为： select * from admin where admin_time> '2014-01-01′

9、常见面试SQL

例1：

用一条SQL语句查询出每门课都大于80分的学生姓名

name kecheng fenshu
张三语文 81
张三数学     75
李四语文     76
李四数学    90
王五语文     81
王五数学     100
王五英语     90
答1：

select distinct name from table where name not in (select distinct name from table where fenshu<=80)
答2：

select name from table group by name having min(fenshu)>80

例2：

学生表如下:
自动编号学号姓名课程编号课程名称分数
1      2005001 张三 0001    数学   69
2      2005002 李四 0001    数学   89
3      2005001 张三 0001    数学   69
删除除了自动编号不同，其他都相同的学生冗余信息
答：

delete tablename where 自动编号 not in(select min(自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

例3：

一个叫team的表，里面只有一个字段name,一共有4条纪录，分别是a,b,c,d,对应四个球队，现在四个球队进行比赛，用一条sql语句显示所有可能的比赛组合.

答：

select a.name, b.name
from team a, team b
where a.name < b.name

例4：

怎么把这样一个表
year month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4
答：

select year,
(select amount from aaa m where month=1 and m.year=aaa.year) as m1,
(select amount from aaa m where month=2 and m.year=aaa.year) as m2,
(select amount from aaa m where month=3 and m.year=aaa.year) as m3,
(select amount from aaa m where month=4 and m.year=aaa.year) as m4
from aaa group by year

*
*例5：

说明：复制表(只复制结构,源表名：a新表名：b)

答：
SQL:

select * into b from a where 1<>1 (where1=1，拷贝表结构和数据内容)

ORACLE:

create table b
As
Select * from a where 1=2

[<>（不等于）(SQL Server Compact)

比较两个表达式。
当使用此运算符比较非空表达式时，如果左操作数不等于右操作数，则结果为
TRUE。否则，结果为 FALSE。]

例6：

原表：
courseid coursename score
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80

为了便于阅读,查询此表后的结果显式如下(及格分数为60):
courseid coursename score mark
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
写出此查询语句

答：

select courseid, coursename ,score ,if(score>=60, “pass”,“fail”) as mark from course

例7：

表名：购物信息

购物人商品名称数量

A 甲 2

B 乙 4

C 丙 1

A 丁 2

B 丙 5

给出所有购入商品为两种或两种以上的购物人记录

答：

select * from 购物信息 where 购物人 in (select 购物人 from 购物信息 group by 购物人 having count(*) >= 2);

例8：

info 表

date result

2005-05-09 win

2005-05-09 lose

2005-05-10 win

2005-05-10 lose

如果要生成下列结果, 该如何写sql语句?

date win lose

2005-05-09 2 2

2005-05-10 1 2

答1：

select date, sum(case when result = "win" then 1 else 0 end) as "win",
sum(case when result = "lose" then 1 else 0 end) as "lose" from info
group by date;

答2：

select a.date, a.result as win, b.result as lose   
from   
(select date, count(result) as result from info where result = "win" group by date) as a   
join   
(select date, count(result) as result from info where result = "lose" group by date) as b   
on a.date = b.date;

{{ item.userVo.nickname}} | {{ item.createTimeString}}

内容

{{ item.isReply == 1 ? '回复' : '取消回复'}} | 删除

{{ child.userVo.nickname }} {{ child.parent.userVo.nickname }} | {{ child.createTimeString }}

"{{ child.parent.content }}"

{{ child.isReply == 1 ? '回复' : '取消回复'}} | 删除

没有更多啦~ 加载中...

尚硅谷Java技术之北京高频面试题：第五章 MySQL数据库篇

第五章 MySQL数据库篇

1、SQL 的select 语句完整的执行顺序

2、MySQL的事务

3、简述在MySQL数据库中MyISAM和InnoDB的区别

4、悲观锁和乐观锁的怎么实现？

5、你们公司有哪些数据库设计规范

6、索引的底层实现是什么？什么情况下会索引失效？

7、MySQL性能优化

8、SQL 语句优化案例

9、常见面试SQL

尚硅谷Java技术之北京高频面试题：第五章 MySQL数据库篇

关于作者

随便看看

目录

尚硅谷Java技术之北京高频面试题：第五章 MySQL数据库篇

第五章 MySQL数据库篇

1、SQL 的select 语句完整的执行顺序

2、MySQL的事务

3、简述在MySQL数据库中MyISAM和InnoDB的区别

4、悲观锁和乐观锁的怎么实现？

5、你们公司有哪些数据库设计规范

6、索引的底层实现是什么？什么情况下会索引失效？

7、MySQL性能优化

8、SQL 语句优化案例

9、常见面试SQL

尚硅谷Java技术之北京高频面试题：第五章 MySQL数据库篇

关于作者

随便看看

{{ item.title }}

目录