来源:B站图灵课堂的视频
1 B+树的特点
- 具备B树的特点,一个节点可以存储多个元素
- 叶子节点之间有双向指针。
- 所有数据都存储到叶子节点。中间的节点存储索引。
- 所有叶子节点按照关键词从小到大排序
B树和B+树的区别,为什么MySQL用B+树?
B树特点:
1.节点排序,从小到大
- 一个节点存储多个元素。
B+树的特点: - 拥有B树的特点
- 叶子节点之间有指针
- 非叶子节点都在叶子节点冗余了。即叶子节点存储了所有元素,并且排好序。
MySQL索引用b+树,数据排序,且一个节点可以存储多个元素,树叶子节点存储了所有数据,叶子节点之间有指针,树高度不高,可以提高查询速度。
MySQL一个InnoDB页=一个B+树节点,一个InnoDB页默认16kb,一颗2层的b+树可以存储2000万条数据。
2 InnoDb是如何实现事务?
- 执行update的sql前,会把修改数据所在页缓存到buffer pool中。
- 执行sql,修改buffer pool中的数据。
- 生成RedoLog的对象,并写入到LogBuffer中
- 生成UndoLog的日志,用于事务回滚。
- 事务提交,会把RedoLog对象持久化,以及把buffer pool所在页的数据写入磁盘。
- 事务回滚,会根据UndoLog日志进行回滚。
3 MySql的索引结构是什么样?
B+树
4 聚簇索引和非聚簇索引?
- 聚簇索引:数据+索引
优势:
a、可以直接获取数据,比非聚簇索引需要二次查询效率要高。
b、范围查询效率高,数据从小到大排序。
c、聚簇索引适合排序,非聚簇索引不适合。
劣势:
a、维护索引耗时耗资源。插入数据导致分页。
b、uuid作为聚簇索引,导致数据存储稀疏,导致索引比全表查询还慢,建议
用int的auto_increment作为主键
c、如果主键较大,那么索引存储的空间也大,导致非叶子节点占用较大的物理空间。
- 非聚簇索引:叶子节点存储数据行的地址。
- MyISAM使用的是非聚簇索引,树的子节点上的data不是数据本身,二是存储数据的地址。InnoDB采用的是聚簇索引,树的叶子节点上的data是数据本身。
- InnoDB如果定义了PK,PK是聚簇索引。如果没有PK,则找一个非空的unique的字段作为聚簇索引。如果都没有,InnoDB会创建一个row-id作为聚簇索引。
5 Mysql的锁有哪些?
从锁的粒度区分
-
行锁:加锁力度小,但是加锁资源消耗比较大。InndoDB支持。
1.1 共享锁:读锁,多个事务可以对同一数据共享同一把锁。只读不能改。select * LOCK IN SHARE MODE
1.2 排他锁:写锁,只有一个事务能获取改行的排他锁。InnoDB会对/insert/update/delete语句自动添加排他锁。select * for update
1.3 自增锁:自增字段,数据回滚,自增序号不会回滚。 -
表锁:加锁力度大,加锁资源开销比较小。MyISAM和InnoDB均支持。
2.1 表共享锁
2.2 表排他锁
2.3 表意向锁:InnoDB自动添加的锁,用户不需要干预 -
全局锁:Flush tabls with read lock。加锁后,整个数据库实例都在只读状态,所有数据变更操作都会挂起。一般用全库备份的时候。
6 常用的锁算法?
1.记录锁:锁一条具体的数据。
-
间隙锁:RR(Repeatable Read)隔离级别下,会加间隙锁。锁一定的范围,而不锁具体的记录。
-
Next-key:间隙锁+右记录锁
7 uuid/雪花id/自增id
比较:数字比较的效率比字符比较快
IO:cpu是一页一页读进内存,根据局部性原理,会把连续的页读进内存。UUID是随机零散的,需要读很多页;雪花id和自增id是基于局部页上读取。
并发:自增id是一个个阻塞排队生成;雪花id和uuid之后并发生成。
多库多表:多个库表合并数据时,自增id是唯一的,合并会有冲突。
生成的位置:雪花id和uuid是客户端生成;自增id是数据库生成。
整体速率:雪花id>自增id>uuid
8 数据库调优
1.数据库表结构优化
库表设计是否合理,是否需要索引,字段是否必须冗余。
2.查询sql优化。
索引,是否索引失效,排查失效的原因。
子查询,能否缩减子查询的,避免多表查询。比如冗余字段到同一个表,以空间换性能。
查询条件,where后面跟着很多条件。
函数,是否使用函数,或者字段计算等
3.主从复制。主服务器负责写操作,从服务器负责读操作。
4.库表分区。创表时设置。
5.分布式数据库。
6.数据库参数调优。调整innodb_buffer_pool_page参数,提高查询缓存的大小。
7.数据缓存技术。redis,ehcache
8.硬件升级。
9 数据库索引失效的几个场景
1.最左匹配原则。联合索引。
2.查询非索引字段。
3.函数
4.字段计算
5.字段对比
6.字段类型不同,char类型自动转int
7.like ‘%XXXX’
8.or 2边都需要索引字段
9.order by
没有where/limit
对不同索引一起做order by
不满足最左匹配原则
不同的排序规则
10.in exist 走索引
not in
普通索引不走索引
主键索引走索引
not exist
不是所有表都走索引
10 MySQL集群?读写分离?
- MySQL通过将主节点的Binlog把数据同步到从节点,完成主从之间的数据同步。
- 因为不会从节点把数据同步到主节点。所以需要做读写分离。这是需要业务系统实现的功能,主节点写操作,从节点都操作。、
11 MySQL慢查询怎么优化?
- 检查是否走索引。如果没有走索引,则优化索引。
- 检查所利用索引,是否是最优索引。
- 检查字段是否都是必须的,是否查询冗余字段。
- 检查表中的数据是否过多,是否可以进行数据库分库分表。
- 检查数据库实例所在的机器的性能配置。
- 分区
- 读写分离
- 内存缓存,redis,echehce
- 避免函数
- 子查询改成join连接
12 MVCC?
读已提交,可重复读隔离级别的事务,在执行普通的的查询操作访问记录的版本链过程。可以使不同事务的读-写、写-读操作并发进行,提高系统的性能。
读已提交:每一次select查询都会生成一个readView。
可重复读:只在第一次select操作生成一个readView,之后的查询操作都重复使用这个readView。
13 事务的隔离性有4个级别
- 读未提交:脏读:查询事务还没提交的数据,且事务进行回滚后,导致前后数据不一致。
- 读已提交:不可重复读:事务1在事务2操作前后查询的数据不一样。
- 可重复读:每次读取结果都一样。但可能出现幻读:同一个事务中2次查询的数据量不一致。中间有别的事务插入数据。(MySQL默认事务级别)
- 串行:一般不适用,给每一行加锁。
14 事务的4大特性?
- 原子性 A
- 一致性 C
- 隔离性 I
- 持久性 D
15 事务的隔离级别?
- 读未提交。存在脏读。
- 读已提交。解决脏读。readView快照生成多次。
- 可重复读。存在幻读。readView快照只生成一次。
- 串行
16 索引的基本原理
把无序的数据变成有序的查询
- 把创建索引列的内容进行排序
- 对排序结果生成排序表。
- 排序表存储数据的地址链
- 查询时,先查排序表,再根据地址链获取具体的数值。
17 mybatis如何分页
- 在mapping文件写sql语句时,增加分页关键词limit
- Mybatis的RowBounds对象,实现内存分页。
- Mybatis的Interceptor拦截器,获取查询的select语句后,动态拼接分页关键字。PageHelper。
18 mybatis的优缺点
优点:
- 基于SQL语句编程,灵活。
- 相比JDBC,减少冗余的的代码,不需要手动开关连接。
- 兼容各种支持JDBC的数据库。
- 能够跟Spring有很好的集成。
- 提供映射标签,支持对象与数据库ORM字段关系映射;提供对象关系映射标签,支持对象关系组件维护。
缺点: - SQL语句编写导致工作量变大,涉及到字段多,关联表多时,对开发人员编写sql语句的功底有一定的要求。
- SQL语句依赖数据库,导致迁移数据库时,会有兼容性问题。
19 mybatis的#{},${}区别?
-
{},占位符,等于prepareStatement的?
- ${}, 直接替换,等于statement。存在sql注入的风险。
20 索引设计的原则?
原则:查询更快,表占用空间更少
- 适合索引的列=where条件列,连接子句指定的字段。
- 使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,节省索引空间。如果搜索长度超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配。
- 不要过度索引。索引需要额外的磁盘空间,并降低写操作的空性能。在修改表内容时,索引会进行更新,甚至重构,索引列越多,越耗时。所以只需保持有需要的索引有利于查询即可。
- 外键的列建议建索引。
- 频繁更新的字段不建议建索引。
- 不能有效区分数据的列(重复值很多),不建议建索引。比如男女,未知。
- 尽量的扩展索引,不要新建索引。比如已经有a索引,需要新建(a,b)索引,那么只需要修改原本的索引即可。
- text,image,bit不要建立索引。
21 MyISAM和InnoDB的区别?
MyISAM:
- 不支持事务。每次查询都是原子的。
- 支持表级锁。每次操作都是整个表加锁。
- 存储表的总行数。
- 一个MyISAM表有三个文件:索引文件,表结构文件,数据文件。
- 非聚簇索引(索引文件的数据域存储指向数据的指针)。
InnoDB: - 支持ACID事务,支持事务的四种隔离界别
- 支持行级锁及外键的约束,支持并发写
- 不存储表的总行数
- 一个innoDB引擎存储在一个文件空间。
- 主键索引采用聚集索引(索引文件的数据域存储指向数据文件本身)。最好使用自增主键,防止插入数据时,为维持B+树的结构,文件大调整。
22 Explain语句结果各个字段分别表示什么?
id:一个select语句一个id,id 的值越大优先级越高,越先被执行
select_type:select 关键词对应的那个查询的类型
table:表名
partitions:匹配的分区信息
type:(重要)针对大表查询的方式(全表查询,索引)
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
possible_keys:可能用到的索引
key:实际用到的索引。
key_len:实际使用的索引长度
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或者值被用于查找索引列上的值。
rows:(核心指标)预估需要读取的记录条数
filtered:某个表经过搜索条件过滤后剩余条数的百分比
extra:包含不适合在其它列中显示但十分重要的额外信息,比如排序
23 索引覆盖是什么?
SQL查询,走索引。且查询的字段,在索引对应的字段中都包含了,在当前索引的叶子节点存在,意味着不需要回表, 直接作为结果返回。
24 最左前缀原则是什么?
sql利用索引查询,一定要提供索引对应的字段中 最左边的字段。比如现在针对a,b,c建立联合索引,那么sql的查询条件一定要有a字段,才能用联合索引。这是因为建立abc联合索引时,底层b+树按照abc从左到右去比较大小排序的,所以利用b+树查询时也得符合这个规则。
25 InnoDB如何实现事务?
Buffer pool,logBuffer,redo Log, undo Log实现事务。
- update语句,从磁盘查询数据,缓存在buffer Pool中。
- 修改Buffer Pool中的数据,内存数据
- 根据update语句,生成Redo Log 对象,存入log buffer
- 根据update语句,生成undo Log对象,用于事务回滚。
- 事务提交,redo log对象持久化(宕机,内存数据丢失,用于恢复),后续还会有其他机制将buffer pool修改的数据持久化。
- 事务回滚,利用undo log日志进行回滚。
26 MySQL锁有哪些?
按锁粒度分类:
- 行锁:力度最小,并发高
- 表锁:力度最大,并发低
- 间隙锁:锁一个区间
共享锁:读锁,支持其他事务读,但不能写
排他锁:写锁,不支持其他事务读和写。
乐观锁:不会真的上锁,只是通过版本号逻辑判断。
悲观锁:上面的行锁,表锁都是悲观锁。
利用锁才能解锁幻读。
网友评论