MySQL面试中常见问题总结
1. JDBC连接的过程
- 加载驱动
- Class.forName(“com.mysql.jdbc.Driver”);
- 通过协议,子协议,ip,端口,URL创建连接,获取到连接对象
- Connection connection = DriverManager.getConnection(“jdbc:mysql://xxx.xxx.xxx.xxx/jdbc?xxxxxx&xxxxx”)
- 编写sql语句
- 传入sql,获得statement对象
- PreparedStatement ps = connection.prepareStatement
- 如果是加参数的sql(?作为占位符),需要填充参数
- ps.setObject(1, obj)
- 执行statement中的sql,获得结果集
- ResultSet resultSet=statement.executeQuery();
- ResultSet resultSet=statement.executeUpdate();
- ResultSet resultSet=statement.execute();
- 处理结果集
- rs.next() 类似于迭代器
2. 连接池
在程序运行中,如果每次访问数据库都需要建立连接,使用完毕之后在关闭连接.网络开销和对系统资源的占用都非常大.那么依据资源池的思想,将连接使用完之后不进行关闭,而是放入连接池中,下一次进行重用.这就诞生了连接池
(1). 连接池的工作原理
- 连接池的建立
- 系统初始化时会根据系统配置建立连接池,并初始化几个连接对象.
- 连接池的管理
- 请求连接
- 当线程请求数据库连接时,如果有空闲连接,分配给线程使用
- 如果没有空闲连接,那么判断当前连接数量是否超出连接池上限
- 如果没有,那么创建新的连接
- 如果已经达到上限,任务等待
- 任务等待时间超出最大等待时间时,抛出异常
- 释放连接
- 当线程释放数据库连接时,会判断当前连接的引用次数是否超过规定值
- 如果超过,就从连接池中删除这个连接,否则进行白柳
- 请求连接
- 连接池的关闭
- 当应用程序退出时,关闭连接池中的所有连接,释放相关资源
(2). 连接池主要参数
- 最小连接数:初始化时就要达到的连接数量
- 最大连接数:字面意思
- 最大空闲时间:连接不被使用的最大时间,为了避免过多占用系统内存
- 获取连接超时时间
- 超时重连次数
3. 数据库范式
主码是指主键的集合(一张表被多个键唯一确定)
主码中的每一个属性都是主属性.
(1). 第一范式(1NF)
属性域不可分割,要具有原子性.
(2). 第二范式(2NF)
非主属性必须完全依赖于主码(完全依赖是指不能被其一部分决定).
(3). 第三范式(3NF)
非主属不能依赖于其他非主属性.
(4). 巴斯范式(BCNF)
所有属性(这里主要针对的是主属性)不能对主属性有传递依赖.(不能传递依赖,也就是主属性之间不能相互依赖)
4. InnoDB和MyISAM区别
(1). 区别
| InnoDB | MyISAM | |
|---|---|---|
| 锁 | 支持行锁 | 只支持表锁 |
| 事务 | 支持完整的事务 | 通过锁表来模拟事务 |
| 表的组织 | 索引组织表,共享表空间和多表空间存储 | 堆表,tableName.frm用于存储表的定义,tableName.MYD用于存放数据,tableName.MYI用于存放表索引 |
| 主键 | 如果没有显式的定义主键,会自动生成一个用户不可见的6字节的主键 | 允许没有主键的表存在 |
| 索引的保存 | 和数据一起保存,也就是聚集索引 | 单独存放,非聚集索引 |
| 辅助索引 | 存储主键 | 存储指向数据的指针 |
| 外键 | 支持 | 不支持 |
| count | 遍历整个表 | 有数据存储总数,读取该值即可 |
| 并发 | 分为读锁和写锁 | 读和写都是相互阻塞的 |
因为辅助索引存储指针,多查询的表使用MyISAM效率会高很多.
InnoDB的表锁会让表的插入删除更新操作的效率高于MyISAM
(2). InnoDB存储引擎的4大特性
- 插入缓冲
- 对于不唯一的辅助索引,会先在缓冲中查找有没有该索引值,如果有直接插入到该位置(相同索引值在索引表中的位置一样),如果没有先添加到插入缓冲中,在按一定频率合并到缓冲池中,然后在持久化在磁盘中
- 二次写
- 为了防止将一个页写入磁盘时,中途发生宕机,磁盘上的数据已经脏了,这种情况是不能使用重做日志进行恢复的
- 重做日志之能将数据库从一个状态更改为另一个状态,不能恢复这种随机性的数据错乱
- 在内存中有一个doublewrite buffer大小为2MB,磁盘中也会有一片连续的128个页,2MB
- 先将缓冲中的数据写入这2MB磁盘中,然后在写入原本应该写入的位置
- 这样保证了磁盘中要么保存了原先的数据,要么保存了新的数据,不会出现中间状态
- 自适应哈希索引
- 如果某个二级索引被频繁访问(连续访问模式必须一样),成为热数据.这个二级索引会被自动生成到hash索引中去
- 根据哈希算法计算索引值得出的值作为下标,在数组中存储指向聚集索引的页面地址的指针
- 预读
- InnoDB可能会再一次读取中顺便读取多量数据,减少磁盘访问的次数
- 线性预读:根据当前发生顺序读取的次数判断是否在本次磁盘访问中将下一个区中的数据顺便读入到缓冲池
- 随机预读:随机的将当前访问的页中的一些其他数据顺便读入缓冲池,已经弃用
5. 堆组织表,索引组织表和索引聚簇表
(1). 堆组织表
物理存放顺序是随机的,索引中记录了数据所在位置的rowid,查找的时候先找索引,然后根据rowid找到行数据
数据和索引是分离的.
(2). 索引组织表
行数据和索引一起存放,找到索引就找到了行数据.
(3). 索引聚簇表
一组表如果有共同的列,那么存储在相同的数据库块中.(可以理解为连接操作)
6. mysql优化
- 开启查询缓存，优化查询
- explain你的select查询，这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的
- 当只要一行数据时使用limit 1，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据
- 为搜索字段建索引
- 使用 ENUM 而不是 VARCHAR。如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是VARCHAR
- Prepared Statements
- Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合，我们可以从使用 prepared statements 获得很多好处，无论是性能问题还是安全问题。
- Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击
- 垂直分表
- 选择正确的存储引擎
7. B+树
InnoDB存储引擎中的索引是使用B+树来存储的.
(1). 特点
- 非叶子节点中指针域(子节点)和索引域(索引值)的数量一致,B树的指针域要加一
- 指针指向的子树中的所有数据都要小于此指针对应的索引域的值
- 所有数据都存放在叶节点中,非叶子节点中保存的索引值只是其中数据的复制
- 所有叶子节点上的数据都有连个指针,分别指向上一个和下一个节点(叶子节点双向链表连接)
(2). 较B树的优点
- 由于所有数据都存放在叶子节点,树更加矮胖,相同树高能存放更多数据
- 所有数据都在一层,查询效率更高
- 对数据的扫描通过双向链表实现,不需要中序遍历整棵树
8. 添加索引
(1). alter方式
alter table 表名
add 索引类型 索引名
(索引列[,索引列2]);
(2). create方式
create 索引类型 索引名
on 表名 (索引列[,索引列2]);
9. MySQL 5.6对DDL的优化
在MySQL 5.6之前MySQL本身不增加任何脚本的情况下,进行DDL操作(改变表或者库的结构)时都是建立一张包含新的结构的表,然后将数据导入新表.
MySQL 5.6开始支持Online DDL,其本质是在进行结构更改的时候,将此时的insert,update,delete这类操作日志写入缓存中,当DDL完成后,在重做到表上.
10. 事务
(1). 事务的特性
- 原子性:事务中的操作要么全部完成,要么全部失败
- 一致性:事务执行前后,数据库都要有一致性(事务中的操作要合法,否则回滚)
- 隔离性:事物之间根据隔离级别有不同程度的不可见
- 持久性:事务一旦提交,就永久的改变数据库中的数据
(2). 事务的隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 不可重复读（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read,MySQL默认） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
(3). 事务中出现的并发问题
脏读:
一个事务已经修改但还未提交的数据成为脏数据.脏读就是一个事务读到了另一个事务还未提交的事务,当另一个事务回滚时,这个事务当前得到的数据与数据库中不一致.
不可重复读:
一个事务多次读取表的同时,另一个事务对数据进行了修改,并进行了提交.这时该事务没有进行修改数据但是读取的数据发生了改变.
幻读:
一个事务多次读取表,另一个事务新增或者删除了数据.这时该数据读取到的数据数量发生了改变.
幻读和不可重复读的区别在于侧重点不同,不可重复读侧重数据的更新,而幻读侧重于数据的增减.
这就决定了,不可重复读可以通过行锁解决,而幻读需要一些别的措施(InnoDB中使用间隙锁完成).
11. MySQL主从复制原理
主服务:binlog(二进制日志,存储数据库的所有更改操作)线程会记录下所有改变数据库的语句,放入二进制日志中
从服务:io线程请求主服务的binlog,写入relay log(中继日志,只在这里使用)中
从服务:sql执行线程执行relay log中的语句
12. 表分区
(1). 水平分区
横向的将某张表截断成不同的分区(分离的是行,每一行的数据是完整的),有4中分区方式:
- range:按照给定的取值区间进行分区
- hash:按照给定的hash算法进行分区
- key:使用mysql提供的方法进行hash
- list:离散型的进行选择分区
(2). 垂直分区
纵向的将某张表划分成不同的分区(将数据库的列进行分离),查询时需要连接查询.
13. 关于MVCC
MVCC就是多版本并发控制,在Mysql的InnoDB引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。
(1). 版本链
在聚簇索引(主键索引)中会有两个隐藏列.
每次当有事务对某条数据进行修改的时候都会有一个trx_id来存储这个事务的id,然后将老版本地址写入undo page,并用roll_pointer指向这个版本.
通过roll_pointer来找到上个版本,然后通过比对trx_id找到是否是当前事务要查询的版本,如果不是继续在上个版本中找到上上个版本.
(2). 快照读
快照读是指每个事务都读取之前所有版本和当前版本的其中之一.
read view是一个列表来存储当前活跃的读写事务,也就是未提交事务,用这个列表来判断记录某个版本是否对当前版本可见.寻找到数据时,查找其当前版本对应的事务id,并判断是否能读取,如果不能,继续查找之前的版本,知道找到一个能读取的版本为止.
- 看不到read view创建时刻以后启动的事务
- 看不到read view创建时活跃的事务
读已提交(不可重复读)在每次进行select时创建新的read view,而可重复读只在第一次select时创建read view.
14. 什么是索引,其优缺点,注意事项
(1). 什么是索引
索引是数据库中的一种具有物理存储的数据结构.通常使用B树或者B+树实现.在这个数据结构上可以实现数据库维护的高级查找算法.
(2). 索引的作用
协助快速查询
(3). 索引的优缺点
优点:
- 提高数据的查找速度
- 加快连接速度
缺点:
- 创建和维护索引需要耗费时间
- 索引需要占用物理空间
(4). 建立索引的注意事项
应该建立索引的情况:
- 经常搜索的列
- 需要连接的列,尤其是外键
- 小范围查找的列
- 经常需要排序的列
- 经常使用where子句的列
不应该创建索引的列:
- 数据大量重复
- 数据量非常大(text等类型)
- 对修改性能需求远大于检索性能(实时更新数据,反例是用于统计的数据)
15. 嵌套事务
嵌套事务指的是子事务是父事务的一部分,进入子事务之前父事务会建立保存点.
如果子事务回滚,父事务会回滚到之前设置的保存点.
如果父事务回滚,子事务也会回滚.
提交事务时,子事务先提交,父事务再提交.
16. SQL执行顺序
查询语句中select, from, where, group by, having, order by的执行顺序
(1). 总体上的执行顺序
from -> where -> group by -> having -> select -> order by
- from确定数据的来源
- 从后往前进行连接,所以小表放后面
- where确定数据的筛选条件
- orcale从后往前
- mysql从前往后
- 要点就是尽量先排除尽可能多的数据
- group by先进行分组
- having对分组进行筛选
- 很耗资源,尽量使用where代替
- select确定要展示的列
- 少用*,尽量使用字段名
- order by确定要展示的顺序
- 很耗资源
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
