MySQL精选面试题-EW帮帮网

文章目录

1. sql优化
2. 数据库优化
3. 悲观锁和乐观锁
4. 共享锁与排他锁
5. 索引的目的是什么？
6.B+Tree对比BTree的优点：
7. 聚簇索引和非聚簇索引的区别
8. for update
9. 间隙锁Gap Locks
10. 临键锁Next-Key Locks
11. MVCC是什么?

1. sql优化

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描(如果or的每个条件都有索引,则or会走索引)，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10    
union all    
select id from t where num=20

in 和 not in 也要慎用，如果查询的数据过多会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

全模糊查询也将导致全表扫描(不使用左模糊会走索引)：

select id from t where name like '%abc%'

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id

应改为:

select id from t where name like 'abc%'

不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

in和exists要用小表驱动大表

https://blog.csdn.net/qq_38789941/article/details/83744271

2. 数据库优化

并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
尽量避免大事务操作，提高系统并发能力。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

https://blog.csdn.net/qq_38789941/article/details/83744271

连表的字段编码集应该一样 , 不一致会导致索引失效, 因为做了隐式转换

mysql的utf8 不是完整的utf-8, 是压缩版的, utf8只支持每个字符最多三个字节，而真正的 UTF-8 是每个字符最多四个字节。utf8mb4才是真正的utf-8, 所以编码集应该统一用这个, 编码集还涉及到排序规则 , 它表示各个图形字符之间的大小比较规则，比如：是否区分大小写，区分全角和半角等。

https://blog.csdn.net/wzngzaixiaomantou/article/details/119720356

https://blog.csdn.net/CSDN_WYL2016/article/details/119881723

https://www.infoq.cn/article/in-mysql-never-use-utf8-use-utf8

https://blog.csdn.net/u010476739/article/details/118653156

3. 悲观锁和乐观锁

乐观锁：

乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时（更新操作），想法很乐观，认为这次的操作不会导致冲突，在操作数据室，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。

通常是这样实现的：在表中的数据进行操作时（更新），会给数据表中加一个版本（version）字段，每操作一次将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段，如果涛对那条记录进行操作（更新），则先判断此刻version的值是否与刚刚查出来的version的值相等，如果相等，则说明这段期间，没有其他程序对齐进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的verison的值不相等，则说明这段期间已经有其他程序对齐进行操作了，则不进行更新操作。

例子：

1.查询出商品信息

select * from goods where id=#{id}

2.根据商品信息生成订单

3.修改商品status为2

update goods set status = 2,version = version +1 where id=#{id}

悲观锁：

与乐观所相对应的就是悲观锁。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这单跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现的，要用的时候，我们直接调用数据库的相关语句就可以了。

由悲观锁涉及到的两个锁概念：共享锁与排他锁。

4. 共享锁与排他锁

共享锁：

共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。相当于对于同一把门，它拥有多个钥匙一样。对于悲观锁，一般数据库已经实现了，共享锁也属于悲观锁的一种，共享锁，也叫读锁。就是我们对数据进行读取操作的时候，其实是不会改变数据的值的。

所以我们可以给数据库增加读锁，获得读锁的事务就可以读取数据了。当数据库已经被别人增加了读锁的时候，其他新来的事务也可以读数据，但是不能写。

也就是说，如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法:

在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结果中的每行都加共享锁。

SELECT ... LOCK IN SHARE MODE;

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排它锁：

排它锁与共享锁相对应，就是指对于多个不同的事务，对同一个资源只能有一把锁。排他锁，也叫写锁。就是我们对数据进行写操作的时候，要先获得写锁，获得写锁的事务既可以写数据也可以读数据。但是，如果数据库已经被别人增加了排他写锁，那么后面的事务是无法在获得该数据库的任何锁的。

也就是说，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法
在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁

SELECT ... FOR UPDATE;

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

来自: https://www.cnblogs.com/fanghl/p/11316540.html

5. 索引的目的是什么？

快速访问数据表中的特定信息，提高检索速度
创建唯一性索引，保证数据库表中每一行数据的唯一性。
加速表和表之间的连接
使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

索引不是越多越好, 索引越多, 每次更新数据就会越慢,因为要更新b+tree
索引字段类型越简单越短越好

6.B+Tree对比BTree的优点：

可能会问, b树和b+树的区别, 回答也类似, 要融会贯通

6.1 磁盘读写代价更低

一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为16K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

6.2 查询速度更稳定且能存更多索引

由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

因为中间节点不存数据, 可以存放更多的索引值, 所以可以存更多的数据

原文链接：https://blog.csdn.net/tongdanping/article/details/79878302

6.3 B+树叶子节点两两相连增快区间访问

根据空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。

B+树可以很好的利用局部性原理，若我们访问节点 key为 50，则 key 为 55、60、62 的节点将来也可能被访问，我们可以利用磁盘预读原理提前将这些数据读入内存，减少了磁盘 IO 的次数。当然B+树也能够很好的完成范围查询。比如查询 key 值在 50-70 之间的节点。

一文彻底搞懂MySQL基础：B树和B+树的区别_公众号：码农富哥的博客-CSDN博客

7. 聚簇索引和非聚簇索引的区别

非聚簇索引的数据表和索引表是分开存储的。而聚簇索引的数据和主键索引存储在一起。
比如,主键索引就是聚簇索引, 他的数据存在了树上

简单总结:

B树和B+树区分业务数据是放在中间节点上还是放在叶子节点值, (这里的业务数据可以是具体的值也可以是地址)

聚簇和非聚簇区分业务数据是挂在树上还是外置单独存储

8. for update

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

这叫获得写锁或者排它锁 , 获得排他锁后,别人将不能获得排它锁 , 也不能修改该数据,只能读取

SELECT * FROM user WHERE id = 1 FOR UPDATE;

InnoDB 默认是行级锁，当有明确指定的主键/索引时候(索引要生效)，是行级锁，否则是表级锁。
如果没有命中数据则不会锁

9. 间隙锁Gap Locks

简单记忆: 就是锁区间的,但是不包括边界 , 只包括区间内的值以及它们的"间隙", 数学用语是: 左开右开

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据, (这个区间内有几条就锁几条, 并且想修改或者插入到这个区间都不允许)。

加锁规则有以下特性:

加锁的基本单位是临键锁（next-key lock）,临间锁是前开后闭原则
插叙过程中访问的对象会增加锁
索引上的等值查询–给唯一索引加锁的时候，next-key lock升级为行锁
索引上的等值查询–向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁
唯一索引上的范围查询会访问到不满足条件的第一个值为止

例如:

SELECT * FROM 表名称 WHERE id BETWEN 1 AND 10 FOR UPDATE;

即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。

MySQL如何解决幻读和不可重复读？

MYSQL（04）-间隙锁详解 - 简书 (jianshu.com)

10. 临键锁Next-Key Locks

简单记忆: 是间隙锁+行锁, 所以不光锁范围, 还锁住了区间末位值, 数学用语是: 左开右闭

Next-Key 可以理解为一种特殊的间隙锁，本质是间隙锁和行锁的结合。 通过临建锁和MVCC可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

假设有如下表：

引擎：InnoDB，隔离级别：Repeatable-Read：table(id PK, age KEY, name )

id	age	name
1	10	Lee
3	24	Soraka
5	32	Zed
7	45	Talon

该表中 age 列潜在的临键锁有：

(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],

执行如下命令：

-- 根据非唯一索引列 UPDATE 某条记录
UPDATE table SET name = Vladimir WHERE age = 24;

因为24在边界上, 会锁住上一个区间和下一个区间, 也就是说会锁住 (10,32)

执行如下命令：

-- 根据非唯一索引列 UPDATE 某条记录
UPDATE table SET name = Vladimir WHERE age = 22;

22在区间内, 会锁住其所在区间, 也就是说会锁住 (10,24]

执行如下命令:

-- 根据非唯一索引列 delete 不存在的值
DELETE FROM table WHERE age = '100';// 100是不存在的值

会锁住100到无穷大都会锁住 (100,∞],

一次MySQL死锁问题的排查与分析(一) | Throwable (throwx.cn)

MySQL:insert 加锁 - 简书 (jianshu.com)

面试官：MySQL的UPDATE语句会加哪些锁？ | 毛英东的个人博客 (maoyingdong.com)

MDL锁

意向锁

行锁

间隙锁

Next-key Locking

11. MVCC是什么?

MVCC全称叫多版本并发控制, MVCC 是行级锁的一个变种，属于乐观锁，避免了加锁的操作，因此有了更低的开销和更高的性能, mysql通过给记录一个版本号, 来判断当前操作是否允许更新.

例如, 在修改数据时, 如果大家同时修改, 就会出现被覆盖的场景, 如果修改时判断是否为当前版本,是最新才能修改.
模拟一下版本号的使用: 查询出当前版本号是2, 则将版本号带入到修改语句中, 如中途有人更新了数据, 则版本号会增加成为3, 修改时发现版本号对不上则修改不成功, 这样就达到了不加锁还保证了数据安全
UPDATE user SET creativity_id=5 WHERE id=10 and version = 2;

深入版
MVCC实现原理主要是依赖记录中的 3个隐式字段，undo日志 ， Read View 来实现的。

三个隐式字段

DB_TRX_ID
6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID

DB_ROLL_PTR
7 byte，回滚指针，指向这条记录的上一个版本地址（存储于 rollback segment 里）

DB_ROW_ID
6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引

读视图

因为记录了版本号, 所以就会有很多条数据在数据库中, 这么多数据就形成了一个视图, 方便通过版本号去读取, 称为快照读

Read View 就是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大)

undo日志
快照读时用得到, 当数据被删除时, 就需要undo去查询历史数据, 才能把删除数据查询出来

MySQL精选面试题

文章目录

1. sql优化

2. 数据库优化

3. 悲观锁和乐观锁

4. 共享锁与排他锁

5. 索引的目的是什么？

6.B+Tree对比BTree的优点：

6.1 磁盘读写代价更低

6.2 查询速度更稳定且能存更多索引

6.3 B+树叶子节点两两相连增快区间访问

7. 聚簇索引和非聚簇索引的区别

8. for update

9. 间隙锁Gap Locks

10. 临键锁Next-Key Locks

11. MVCC是什么?

网站公告

今日签到

热门文章

最新发布

MySQL精选面试题

文章目录

1. sql优化

2. 数据库优化

3. 悲观锁和乐观锁

4. 共享锁与排他锁

5. 索引的目的是什么？

6.B+Tree对比BTree的优点：

6.1 磁盘读写代价更低

6.2 查询速度更稳定且能存更多索引

6.3 B+树叶子节点两两相连 增快区间访问

7. 聚簇索引和非聚簇索引的区别

8. for update

9. 间隙锁Gap Locks

10. 临键锁Next-Key Locks

11. MVCC是什么?

网站公告

今日签到

热门文章

最新发布

6.3 B+树叶子节点两两相连增快区间访问