文章目录
常见面试题:
jdbc连接数据库的步骤:
- 加载jdbc驱动程序:Class.forName(“com.mysql.jdbc.Driver”);
2.提供jdbc连接的url
3.创建数据库连接:DriverManager.getConnectin()
4.创建一个statement
5.执行sql语句
6.处理返回结果
7.关闭连接
聚簇索引、覆盖索引(查询内容在索引里面就有)不用回表查询,其他索引需要回表,innodb中只有主键索引是聚簇索引。
mysql事务隔离级别:
mvcc怎么解决幻读的:
innodb默认的事务隔离级别为可重复读,mysql5.7之后已经解决了幻读的问题,解决方式:
mvcc(多版本并发控制):事务在操作行时会默认为行加一个tas_id作为版本控制,时间越往后id越大,在某个事务获取到行锁开始事务时会记录对应行的快照,并阻塞其他事务,读取的数据与其事务隔离开了,所以解决了幻读。
对于一个快照来说,它能够读到那些版本数据,要遵循以下规则:
(1)当前事务内的更新,可以读到;
(2)版本未提交,不能读到;
(3)版本已提交,但是却在快照创建后提交的,不能读到;
(4)版本已提交,且是在快照创建前提交的,可以读到.
mysql索引的长度限制:
innodb引擎的每个索引列长度限制为767字节(bytes),所有组成索引列的长度和不能大于3072字节
myisam引擎的每个索引列长度限制为1000字节,所有组成索引列的长度和不能大于1000字节
为什么innodb必须要有主键,主键为什么要选用自增的整型?
为了维护B+tree的索引结构,即使你没有创建主键,innodb会默认从字段中选择一列作为主键,无法选择时就默认创建一列rowid作为主键。
自增主键在tree中比对时效率更高,高于uuid的大小比对效率。
mysql索引:
索引是 加快数据查询的一种数据结构,能提高查询数据的效率
常见的索引模型
1.哈希表:
使用场景: 等值匹配、适用于NoSQL引擎
弊端: 范围查询很慢
2.数组:
使用场景: 等值查询、范围查询、一般用在静态存储引擎中(数据一般不会变更)
弊端: 插入数据较慢
3.搜索树:
查询和搜索的速度比较快、适应磁盘的访问模式、(减少单次磁盘访问IO 的次数)
B-tree:
(1)叶子节点具有相同的深度
(2)所有索引元素不重复
(3)节点中的数据索引从左到右递增排列
B+tree:
(1)非叶子节点不存储data,只存储索引,可以放更多索引
(2)叶子节点包含所有索引字段
(3)叶子节点使用指针连接,提高区间访问的性能
InnoDB引擎
1.InnoDB 引擎特点:
表根据主键顺序以索引的形式存放(索引组织表)、每一个索引在InnoDB中对应一棵树。
2.普通索引和主键索引的区别:
查询的时候 普通索引会回表(普通索引的叶子节点存放的是主键值、通过主键值要去重新扫描主键索引数查询数据)
3.为什么MySQL建议使用自增主键:
避免发生频繁的页分裂。递增插入,每次插入数据都是追加操作,不涉及挪动其他记录、从而避免页分裂代来的性能损失
页分裂:如果遇到不规则的插入数据、则会造成频繁的页分裂。不规则插入每次插入都会改变数之前的数据状态、从而导致页分裂。
页分裂弊端:大量移动数据,会严重影响插入数据的效率;页分裂会降低空间利用率。
使用业务信息作为主键,往往会占用更多的空间(普通索引的叶子节点也会存储主键信息)
4.重建索引:
重建普通索引
alter table T drop index k;
alter table T add index k;
重建主键索引
alter table T drop primary key;
alter table T add primary key(id);(这句指令会使得上句指令直接无效。)
为什么要重建索引:
(1)索引可能因为删除和页分裂等原因,导致数据页的空洞,重建索引会使得数据按序插入、页面利用率更高、更省空间。
(2)重建普通索引是推荐的。可以节省空间
(3)无论是删除主键还是、添加主键都会重构整个表(alter table T engine=InnoDB代替)
5.覆盖索引:
当前需要查询的信息、直接可以通过当前索引树定位到完整数据、不需要回表。 直接减少了搜索树的次数,提示数据查询的性能。
6.最左前缀原则:
最左前缀可以是联合索引(多个索引组合在一起)的最左N个字段,也可以是字符串索引的最左M个字符。
联合索引的建立原则:
(1)如果调整顺序,可以少维护一个索引,那么这个顺序就要被优先考虑。
(2)空间原则
7.索引下推:
MySQL5.6 引入,索引遍历过程中、对索引中包含的字段先做判断、直接过滤掉不满足的条件,减少回表次数。
普通索引和唯一索引的选择
1.查询过程:
- 模拟查询语句:select id from T where k=5;(k有索引)通过B+树的树根、逐层搜索到叶子节点,(数据页内部可以认为通过二分法来定位记录)。
- InnoDB读取数据是以页为单位。当需要读取一条数据,会将整页数据加载到内存中(默认每页16kb)
- 唯一索引:查到第一个满足条件的记录、则直接返回。
- 普通索引:查到第一个满足的记录后,需要查找下一个记录,直到碰到一个不匹配当前索引值的数据才返回。
- 两种索引查询性能的差不微乎其微(查找的数据基本都会在当前页中、整形字段一页可以放近千个key)
2.更新过程:
- 当前更新数据目标页已经在内存中,则都是直接更新,普通索引和唯一索引基本没有区别
- 当需要更新一个数据时候,如果数据页在内存中则直接更新;如果不在内存中,则会被缓存到change buffer中,这样就不需要从磁盘中读取数据来直接更新了,下次访问这个数据页的时候,将数据页读入内存,然后执行change buffer中这个页相关的操作。保证数据逻辑正确性。
- 当前更新数据目标页不在内存中:
- 普通索引,记录到change buffer中,语句执行结束。(记录redo log【包含更新语句、change buffer数据】)
- 唯一索引,将相关数据页读入内存(涉及随机IO访问 - 成本很高),判断是否违背唯一性约束,执行并结束。
3.change buffer
- change buffer在内存中有拷贝、数据可以持久化
- change buffer中的操作应用到原数据页,得到最新的结果的过程称为merge。
- merge的时机:
- 访问这个数据页
- 后台线程定期
- 数据库正常关闭(shutdown)
- 机制:将更新操作缓存在change buffer中,减少磁盘随机访问IO,且数据入内存需要占用buffer pool,这样还可以减少内存占用,提高内存利用率。
4.change buffer 使用场景:
(1) 只限于普通索引
(2) merge之前,change buffer中数据越多(减少随机访问的IO次数越多)收益就越大
(3) 适合业务模型:账单类、日志类的系统
(4)不适合在更新之后里面就要访问的数据(会提取触发merge操作)、这种IO次数不仅没减少,还增加了维护change buffer的代价
(5)适用写多读少的环境。
5.索引的选择:
(1) 普通索引、唯一索引在查询上来看基本没有区别
(2)主要考虑对更新性能的影响,建议尽量选择普通索引。
(3)如果更新后伴随着对这个记录的查询,那么应该关闭change buffer。其他情况下cahnge buffer都能提升性能。
6.change buffer 和 redo log
(1)redo log 减少了随机写IO的次数
(2) change buffer 减少了随机读IO的次数
索引优化器
1.SQL语句执行的快慢
- 扫描行数越少、访问磁盘次数越少、消耗CPU资源越少
- 是否使用临时表
- 是否排序等
2.MySQL如何统计扫描行数
- 是不准确的(都是估算的)
3.解决MySQL优化器索引选择异常
- force index 强制走我们所期望的索引
- 考虑修改SQL语句,引导MySQL使用我们期望的索引
- 某些场景,可以新建一个更合适的索引,供优化器做选择,或删除误用的索引。
binlog、undo log、redo log
binlog(逻辑日志):
复制 或 主从同步用日志,记录update/insert/delete等语句。
redo log(物理日志 innodb才有):
持久化用日志,数据库数据变更是先写到内存,在异步写到磁盘,在写入过程中同步记录redo log日志,通过redo log来进行持久化写入。
undo log:
undo log主要有两个作用:回滚和多版本控制(MVCC)
在数据修改的时候,不仅记录了redo log,还记录undo log,如果因为某些原因导致事务失败或回滚了,可以用undo log进行回滚
undo log主要存储的也是逻辑日志,比如我们要insert一条数据了,那undo log会记录的一条对应的delete日志。我们要update一条记录时,它会记录一条对应相反的update记录。
这也应该容易理解,毕竟回滚嘛,跟需要修改的操作相反就好,这样就能达到回滚的目的。因为支持回滚操作,所以我们就能保证:“一个事务包含多个操作,这些操作要么全部执行,要么全都不执行”。【原子性】
因为undo log存储着修改之前的数据,相当于一个前版本,MVCC实现的是读写不阻塞,读的时候只要返回前一个版本的数据就行了
redo log保证mysql事务的持久性, redo log和binlog保证事务的一致性,事务开始前写入redo log日志,事务提交前记录binlog日志,两个日志都写入成功时事务才提交成功。