一、二叉树、B树、B+树
二叉树具有以下性质:左子树的键值小于根的键值,右子树
的键值大于根的键值。二叉树的查询效率就低了。
平衡二叉树(AVL树)在符合二叉查找树的条件下,还满足任何节点的两个子树的高度最大差为1。
平衡多路查找树(B-Tree),系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来。InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位,默认16K。InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。
缺点:每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。
B+Tree相对于B-Tree有几点不同:
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中
在数据库中,B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
数据库索引采用B+树的主要原因是:B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题,正是为了解决这个问题,B+树应用而生。
B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而B树不支持这样的操作或者说效率太低。
二、聚集索引与非聚集索引
聚集索引: 数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引,一般指主键。
非聚集索引:除聚集索引外其他都是的,包括普通索引,唯一索引,全文索引。叶子节点并不包含行记录的全部数据,
而是存储相应行数据的聚集索引键。使用非聚集索引查询,而查询列中包含了其他该索引没有覆盖的列,
那么他还要进行第二次的查询,查询节点上对应的数据行的数据。
mysql索引一般建5-8个左右,索引过多,影响数据的操作(insert、update、delete)优化建议使用联合索引。大字段值不建议建索引。
创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加;
索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间;
当对表进行增、删、改、的时候索引也要动态维护,这样就降低了数据的维护速度。
三、如何解决非聚集索引的二次查询问题
如建立符复合索引,只查询复合索引里的列的数据而不需要进行回表二次查询,注意最左原则
四、Mysql 索引失效
1、查询时索引列不能为null值,因为索引是有序的,建索引时无法确定位置。
2、查询时 采用is Null,只能全表扫描。
3、数据库数据量较少时,mysql判断全表查询快时将不使用索引。
4、like查询以%开头,类型隐士转换、列参与函数运算、使用or查询,联合索引非最左
五、MySQL中Myisam与Innodb的区别
- InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
- InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效。
- MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
- InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效。
InnoDB支持事物、支持行级锁(基于索引来完成行锁),Myisam支持表级锁,不支持事物。
InnoDB支持MVCC、外键,Myisam不支持,支持全文索引。
Myisam查询表较快。
六、Innodb引擎四大特点
插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
七、Innodb的事务与日志的实现方式
隔离级别:读未提交(RU)、读已提交(RC)、可重复读(RR)、串行,mysql默认可重复读,mysql通过MVCC(多版本控制)和 Next-key lock(间隙锁)解决了幻读。
MVCC原理:通过在每行纪录后面保存三个隐藏的列来实现的。这两个列,一个保存了行的创建时间,一个保存了行的过期时间(或删除时间),当然存储的并不是实际的时间值,而是系统版本号。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行纪录的版本号进行比较。
MVCC是解决读写并行的幻读,而next-key lock 间隙锁 是解决写写并行的幻读。
Mysql日志:
慢查询日志(slow query log):记录慢查询的日志文件
查询日志(general log):记录所有对数据库请求的信息
重做日志(redo log):
回滚日志(undo log):
二进制日志(binlog):用于复制,在主从复制中,从库利用主库上的binlog进行重播,实现主从同步
错误日志(errorlog):
中继日志(relay log):
事务日志是通过redo和innodb的存储引擎插入缓冲(Innodb log buffer)来实现的,当开始一个事务的时候,会记录该事务的lsn(log sequence number)号; 当事务执行时,会往InnoDB存储引擎的日志
的日志缓存里面插入事务日志;当事务提交时,必须将存储引擎的日志缓冲写入磁盘(通过innodb_flush_log_at_trx_commit来控制),也就是写数据前,需要先写日志。这种方式称为“预写日志方式”。
八、MySQL数据库cpu飙升到500%的话他怎么处理?
列出所有进程,观察所有进程,没有状态变化的(干掉)
查看超时日志或者错误日志
九、你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?
主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等
十、你们数据库是否支持emoji表情,如果不支持,如何操作?
如果是utf8字符集的话,需要升级至utf8_mb4方可支持
十一、Mysql索引查询优化
1.避免在索引列上使用IS NULL和IS NOT NULL,列字段尽量要有默认值.
2.避免 SELECT *
3.千万不要 ORDER BY RAND()
4.在Join表的时候使用相同的类型并将其索引
5.当只要一行数据时使用 LIMIT 1
6.EXPLAIN 分析你的 SELECT 查询
7.每张表都设置ID
8.不要条件列进行函数处理
9.避免使用 or,like 等字段
10.选取最适用的字段属性,尽可能减少定义字段宽度
11.用EXISTS替代IN、用NOT EXISTS替代NOT IN。
十二、分布式事物理解
1.事物的ACID。原子性、一致性、隔离性、持久性
2.分布式系统中经常出现断电、网络异常、应用宕机等问题导致不一致问题。
3.执行事务的时候数据库首先会记录下这个事务的redo操作日志,然后才开始真正操作数据库,在操作之前首先会把日志文件写入磁盘,那么当突然断电的时候,即使操作没有完成,在重新启动数据库时候,数据库会根据当前数据的情况进行undo回滚或者是redo前滚,这样就保证了数据的强一致性。
4.随着业务扩展,单库无法满足,需对数据库垂直分库,此时单个数据库ACID已经不能适应。
5.CAP定理,一致性,可用性,分区容错性。
6.程序中追求可用性要高于一致性,此时出现BASE定理,基本可用、软状态、最终一致性。
解决方案:
- 两阶段提交
- 基于可靠消息服务MQ的分布式事务,主流MQ不支持,
- TCC(两阶段+补偿)二阶段提交失败,嵌入回滚代码,Confirm/Cancel服务的幂等性保障。增大业务复杂度。
- 本地消息表:将分布式事务分成多个本地事物,然后通过异步mq发送消息保证一致性。
十三、mysql集群原理及方案
采用主从复制,读写分离,主备模式
主从复制原理:
采用异步复制模型
十四、什么是索引?
索引是一种特殊的文件,它们包含着对数据表里所有记录的引用指针。
索引可以加快数据检索速度,但是创建索引和维护索引需要耗时,而且还要占用物理空间。
类型:主键索引、唯一索引、普通索引、全文索引。索引的数据结构B+树、hash。
十五、创建索引的原则?
1.较为频繁查询条件的字段;
2.字段区分度、离散值较高的;
3.索引字段长度不能太长;
4.索引的个数不要太多,能复用组合索引尽量复用;
5.索引列尽量指定为NOT NULL;
十六、索引下推ICP
索引条件下推,Index Condition Pushdown,简称ICP,是MySQL内部通过索引查询数据的一种优化方法,简单来说就是将原本需要在Server层对数据进行过滤的条件下推到了引擎层去做,在引擎层过滤更多的数据,这样从引擎层发送到Server层的数据就会显著减少,从而优化性能。
1、当 SQL 需要全表访问时,ICP 的优化策略可用于 range, ref, eq_ref, ref_or_null 类型的数据访问方式
2、只适用于 InnoDB 和 MyISAM 两种存储引擎
3、在 InnoDB 中,ICP 只适用于二级索引
ICP 的目的就是为了减少回表导致的磁盘 I/O,而 InnoDB 的聚簇索引的叶子节点存放的就是完整的数据记录,只要索引数据被读到内存了,那么索引项对应的完整数据记录也就读到内存了,那么通过索引项获取数据记录的过程就在内存中进行了,无需进行磁盘 I/O;也就说聚簇索引上应用 ICP,不会减少磁盘 I/O,也就没有使用的意义了
4、不支持覆盖索引
5、不支持子查询条件的下推
6、不支持存储过程条件、触发器条件的下推
十七、count 字段区别
1.count(可空字段) 扫描全表,读到server层,判断字段可空,拿出该字段所有值,判断每一个值是否为空,不为空则累加
2.count(非空字段)与count(主键 id) 扫描全表,读到server层,判断字段不可空,按行累加。
3.count(1) 扫描全表,但不取值,server层收到的每一行都是1,判断不可能是null,按值累加。
注意:count(1)执行速度比count(主键 id)快的原因:从引擎返回 id 会涉及到解析数据行,以及拷贝字段值的操作。
4.count(*) MySQL 执行count(*)在优化器做了专门优化。因为count(*)返回的行一定不是空。扫描全表,但是不取值,按行累加。