侧边栏壁纸
博主头像
Mr.D的小破站博主等级

身如柳絮随风扬，无论云泥意贯一

  • 累计撰写 21 篇文章
  • 累计创建 9 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录
学习

MySQL

Mr.D
Mr.D
2024-10-29 / 0 评论 / 0 点赞 / 7 阅读 / 25086 字

MySQL解答

一、sql

1、inner join和in

总结：

  • inner join是内连接，负责连接由条件相匹配的数据

  • in就是匹配条件

2、select * from xx for update;

for update是在数据库中上锁用的，可以为数据库中的行上一个排它锁。当一个事务的操作未完成时候，其他事务可以读取但是不能写入或更新。

3、数据库来实现乐观锁?

乐观锁（Optimistic Locking）是一种并发控制机制，只有在提交时检查数据是否已经被其他事务修改过，如果有冲突，则回滚操作，否则成功提交。这种机制特别适用于读取操作多、写操作少的场景。

在数据库中实现乐观锁通常依赖于版本号或时间戳字段，以下是具体实现方式：

3.1、 使用版本号实现乐观锁

通过为每一行数据增加一个版本号字段，每次更新数据时，首先检查该版本号是否与读取时的一致。如果一致，则更新并递增版本号；如果不一致，说明数据在期间已经被其他事务修改过，需要回滚操作。

3.2、使用时间戳实现乐观锁

除了版本号，还可以使用时间戳字段来实现乐观锁。每次更新数据时，检查该记录的时间戳是否与之前读取时的一致。如果一致，则更新数据并修改时间戳；如果不一致，则表示数据已经被修改过。

4、手写sql

特别是关联查询(内连接，左连接，右连接)，各种分组统计，排序等。

-- 创建库
create database xx;
-- 查看库
show databases;
-- 删除库
drop database xx;
​
​
-- 创建表
create table xx(字段1 字段类型,字段2 字段类型...);
-- 创建备份表
create table xx_bak as select * from xx;
-- 查看表
show tables;
-- 删除表
drop table xx;
​
​
-- 添加字段
insert into 表名（字段1,字段2...） values(值1，值2...);
-- 修改字段
update xx set 字段1=值,字段2=值 where 条件(字段=?);
-- 删除
delete from xx where 条件;
-- 查询（like，and，or，in，on）
-- and（&&）、or（||）、not（!）用于多个条件逻辑连接
select * from 表名 where 条件1 and 条件2;
select * from 表名 where 条件1 or 条件2;
-- in、not in 判断列值是否是列表中指定的值
select * from 表名 where 字段 in ('值1','值2'...);
-- like的占位符：_表示单个任意字符，%表示多个任意字符
select * from 表名 where 字段 like '_m%';
-- ON 关键字常用于 JOIN 子句来指定连接条件
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
​
​
-- 分组
select product,sum(price) from orders group by product having sum(price)>100;
-- 排序（默认是升序，asc升序，desc降序）
select * from 表名 where 条件 order by 列名1,列名2... asc;
​
​
-- 内连接
select 列名 from 表1 inner join 表2 on 表1.列名 = 表2.列名;
-- 左连接
select 列名 from 左表 left join 右表 on 左表.列名=右表.列名;
-- 右连接
select 列名 from 右表 right join 左表 on 右表.列名=左表.列名;

5、根据具体的场景描述，进行表结构设计

6、mysql中有哪些函数？哪些聚合函数？

总结：

  • cast（类型转换函数）

  • lower，upper，concat，substr（字符串处理函数）

  • str_to_date，date_format（日期函数）

  • count、max、min、sum、avg（聚合函数）

7、char与varchar区别？

总结：

  • 长度不同

    • varchar为可变长度

    • char是固定长度

  • 存储方式

    • varchar需要1个或2个额外字节记录字符串长度

    • char存储很短和长度近似的字符串

  • 存储容量

    • char字符个数最多为255

    • varchar为65545-3个字节

8、limit 的优化

  • 使用索引

  • 优化sql语句

  • 分页缓存

  • 调整分页策略

  • 使用游标

  • 反向查找优化

  • 子查询优化

9、行列转换

通常涉及表中的行数据转换为列数据。

MySQL中可以通过case，group by、聚合函数来实现效果

二、事务

1.1、基本概念

1、什么是事务？

事务就是用户定义的一系列执行sql语句的操作

一系列的操作的要么同时成功，要么同时失败

2、事务特点？

A：原子性、C：隔离性、I：一致性、D：持久性

3、能解释什么是脏读，什么是不可重复读，什么是幻读？

  • 脏读：就是一个事务读到另一个事务未提交的数据

  • 不可重复读：是一个事务读到另一个事务提交的数据

  • 幻读：强调数据的插入操作

4、事务的隔离级别

  • 未提交读(read-uncommitted)：解决了丢失更新 还会存在脏读 和不可重复读 和幻读

  • 提交读(read-committed)：解决了丢失更新、脏读 ，还会存在不可重复读 和 幻读

  • 可重复读(repeatable-read)：解决了丢失更新、脏读、不可重复读 存在 幻读

  • 串读(serializable)：解决了所有问题(丢失更新、脏读、不可重复读、幻读)

mysql默认隔离级别是可重复读

5、怎么开启事务？

start transaction;
​
set autocommit=0;
​
//jdbc怎么控制事务？
​
connection.setAutoCommit(false);
​
connection.commit()/rollback();

1.2、说一下内连接和外连接的区别

内连接是指两张表的交集，用inner join来实现。

左外连接是指左边这部分和中间的交集，left join，

右外连接是指右边这部分和交集，用 right join

内连接、外连接(左外、右外)面试经常写sql题

分组：group by having这些使用

1.3、Statement和PreparedStatement的区别

  • Statement有sql注入问题，需要拼接sql语句

  • PreparedStatement能提前编译sql，提高性能；避免sql注入问题；设置值非常简单。

1.4、数据库设计三范式

  • 第一范式：所有字段值是不可分解的

  • 第二范式：在满足第一范式的基础上，非主键列必须依赖主键

  • 第三范式：在满足第二范式的基础上，非主键列相互独立

1.5 Mysql中的B+树

什么是B树，B+树？

B树，B+树的区别？

  • B树的节点都可以存储数据，B+树只能在叶子节点存储数据

  • B树的叶子节点不构成链表，B+树的叶子节点构成链表

1.6、为什么B+树比B树更适合作为索引？

  1. B+树的磁盘读写代价更低

  2. B+树的查询效率更加稳定

  3. B+树便于遍历

  4. B+树更擅长范围查询

  5. B+树占用内存空间小

1.7、Mysql存储引擎

InnoDB、MyISAM、Memory

MyISAM与InnoDB的区别:

  1. InnoDB支持事务，MyISAM不支持

  2. InnoDB支持表锁和行锁，MyISAM只支持表锁

  3. InnoDB支持外键，MyISAM不支持

  4. InnoDB是聚簇索引，索引和数据存放到一起；MyISAM是非聚簇索引，索引和数据分开存储；都是使用B+树作为索引结构

  5. InnoDB不保存具体行数，MyISAM用一个变量保存了整表的行数

1.8、MySql优化

MySQL是运行在操作系统上的，os优化--运维

sql语句在MySQL Server上运行的，MySQL Server优化--运维或dba

sql优化--程序员

explains执行

  1. 查看表的读取顺序

  2. 查看数据库读取操作的操作类型

  3. 查看哪些索引有可能被用到

  4. 查看那些索引真正被用到

  5. 查看表之间的引用

  6. 查看表中有多少行记录被优化器查询

sql优化技巧

  1. 尽量走索引

  2. 遵循左匹配队则

  3. 不要再索引列上做操作

  4. 避免使用or执行连接

  5. 数据量较少时，可以全表扫描

  6. 索引对排序影响

  7. 索引覆盖

1.9、索引失效

什么是左匹配规则？

  1. 违背左匹配规则

  2. 对索引使用了函数，表达式或运算符

  3. 查询条件中使用了不等于操作符（！=），not null，not in

  4. 模糊查询

  5. or条件

  6. 范围查询

  7. 数据类型不匹配，需要隐式转换类型

1.10、读写分离

读写分离就是主库处理事务性查询，从库处理select查询。主要是为了将数据库的读写操作分散不同的数据库节点上，缓解服务器的压力。

  • 选择其中一台作为主数据库，另外一台作为从数据库

  • 保证主库和从库之间的数据是实时同步的。（也是常说的主从复制）

  • 写请求在主库，读请求在从库

1.11、主从复制

主库开启二进制日志，设置serverId，并给从库设置一个可以访问远程读取日志的账号，从库通过改账号访问日志来同步数据。

一种情况为半同步

复制的从服务器收到二进制日志文件保存到中继日志中，返回成功就主服务器继续执行下去，保证数据的一致性，如果超时，就降级为异步

1.12、分库分表

只分表：单表数据量大，读写出现瓶颈

只分库：整个数据库读写出现性能瓶颈，例如数据库连接数满了，或者时并发量太大了

分库分表：单表数据量大，库也出现了性能瓶颈，分为两种方式：

垂直拆分：把字段分开（字段太长了）

水平拆分：把记录分开（数据量达到百万，拆分表）

1.13、能说说什么是索引吗？索引的种类有哪些?

索引是有序的（排好序的）

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B+树的形式保存。

主键索引，哈希索引，唯一索引，普通索引，全文索引

1.14、什么情况下适合建索引?

  1. where和order by条件经常使用

  2. 该字段的内容不是唯一的几个值

  3. 字段内容不是频繁变化

1.15 、你知道B+树的叶子结点都可以存放哪些东西吗?

InnoDB的B+树可能时存储整行数据，也能的主键的值

索引B+树叶子节点存储整行数据的是主键索引（也叫聚簇索引）

而索引B+树叶子节点单存储了主键的key是非主键索引（非聚簇索引）

1.16、聚簇索引和非聚簇索引，在查询的时候有区别吗?

什么是聚簇索引和非聚簇索引？

  • 聚簇索引也叫主键索引，是将索引和数据存放到一起，找到索引就找到了数据

  • 非聚簇索引是指二级索引，也叫辅助索引，通过非聚簇索引查到的主键值，再使用主键值通过聚簇索引找到查找的值。

区别

  • 数据存储方式

    • 聚簇索引：叶子节点包含了完整的数据行

    • 非聚簇索引：索引和数据是分开存储的，索引的叶子节点存储指向数据行的指针

  • 数据查找效率

    • 聚簇快，非聚簇慢

  • 范围查找性能

    • 聚簇性能好，非聚簇性能低

1.17、 mysql日志：redo log、binlog、undo log区别与作用

  1. redo log（重做日志）

    1. 确保事务的持久性，防止再发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务时，根据redo log进行重做，从而达到事务的持久性

  2. bin log（二进制日志）

    1. 用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。用于数据库的基于时间点的还原。

  3. undo log（回滚日志）

    1. 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读

1.18、MySQL锁

共享锁，也叫S锁，读锁

当有事务对数据加读锁后，其他事务只能对锁定的数据加读锁。不能加写锁，所有其他事务只能读，不能写。主要为了支持并发读的场景，读时不允许写操作

排他锁，也叫X锁，写锁

当有事务对数据加写锁后，其他事务不能再对锁定的数据加任何锁，又因为InnoDB对select语句默认不加锁，所以其他事务除了不能写操作外，照样是允许读的。主要为了在事务进行写操作时，不允许其他事务修改。

1.19、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来是实现的。只有通过索引条件检索数据，InnoDB才使用行锁，否则使用表锁。

注意：行锁一定要使用索引

1.20、什么是MVCC？

MVCC（多版本并发控制），一般在数据库管理中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC在MySQL InnoDB中实现主要是为了提高数据库并发性能，用更好的方法处理读写冲突。即使有读写冲突，也能做到不加锁，非阻塞并发读。

  1. 当前读

    1. 定义：就是它读取的是记录的最新版本，读取时还要保证其它事务不能修改当前记录，会对读取的记录进行加锁。常见的当前读有共享锁，排他锁，for update，update，insert

    2. 如果在可重复读（rr）隔离级别下，当前读，就是事务开启的第一条select语句

    3. 在提交读（rc）隔离级别下，当前读，就是每使用一个select就会产生一个当前读

  2. 快照读（提高数据库的并发查询能力）

    1. 像不加锁的select操作就是快照读，即不加锁的非阻塞读；

    2. 快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；

    3. 之所以出现快照读的情况，是基于提高并发性能考虑，快照读的实现是基于多版本并发控制（MVCC），但在很多情况下，避免了加锁操作，降低开销；

    4. 既然是基于MVCC，即快照读可能读到的并不一定是最新版本的数据，有可能是之前的历史版本

  3. 当前读，快照读，MVCC的关系

    1. MVCC是维持一个数据多个版本，使读写操作没有冲突，快照读是MySQL为实现MVCC的一个非阻塞读功能。

    2. MVCC模块在mysql中的具体实现是由三个隐式字段，undo日志、read view三个组件来实现。

1.21、MVCC解决的问题是什么？

数据库并发场景有三种，分别是：

  1. 读读：不存在任何问题，也不需要并发控制

  2. 读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读

  3. 写写：有线程安全问题，可能存在更新丢失问题

功能：

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳的关联，读操作只读该事务开始前的数据库的快照。

解决的问题：

  1. 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

  2. 解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。

1.22、同步锁、死锁、乐观锁、悲观锁

同步锁：并发执行多个线程时，在同一时间内只能一个线程访问共享资源。代表synchroned，lock

死锁：多个线程被阻塞，等待资源的释放

乐观锁：认为数据不会被别人修改，不会去加锁。所以再提交时检查数据是否被修改，是就回滚，否则就提交

悲观锁：认为数据会被别人修改，就会加锁。这样共享资源就上了一把锁，只能一个线程一个线程的访问

1.23、MySQL执行流程

客户端发起sql查询，

首先通过连接器，进行用户校验

查询缓存，有缓存就直接返回，没缓存执行后续操作

到达分析器，检查sql语法错误

到达优化器，以它自己的方式优化我们写的sql

到达执行器，执行sql返回结果

1.24、优化sql从那些方面着手

  1. 不需要的字段就不要查询出来

  2. 小结果集驱动大结果集，将能过滤更多数据的条件写到前面

  3. in和not in尽量不要用

  4. 避免where中使用or连接

  5. 给经常使用查询的字段建立索引

  6. 表数据量太大了，考虑分表优化

1.25、如何定位慢sql

  1. 通过druid连接池的内置监控定位慢sql

  2. 通过MySQL的慢查询日志查看慢SQL

  3. 通过show processlist，查看当前数据库sql执行情况定位慢sql

1.26、页面上发起的一个查询很慢，你怎么去优化

先定位再优化sql

0
MySQL
版权归属： Mr.D
本文链接： http://47.103.58.12/archives/mysqlmian-shi-ti-jie-da
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权
博主关闭了当前页面的评论