B+树、hash索引区别
hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.
那么可以看出他们有以下的不同:
hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.
因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.
hash索引不支持使用索引进行排序,原理同上.
hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性.
hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.
因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.
覆盖索引、索引下推
Mysql最左前缀优化原则
最左前缀原则是指在使用复合索引进行查询时,MySQL 会从索引的最左边开始匹配查询条件。
Innodb存储基本单位页详解
索引失效情况
数据库一般会对外键列、主键列、频繁更新、频繁修改索引的列建立索引
使用不等于查询,
列参与了数学运算或者函数
在字符串like时左边是通配符.类似于'%aaa'.
当mysql分析全表扫描比使用索引快的时候不使用索引.
当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.
ACID是什么?可以详细说一下吗?
A=Atomicity
原子性,就是上面说的,要么全部成功,要么全部失败.不可能只执行一部分操作.
C=Consistency
系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态.
I=Isolation
隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况.
D=Durability
持久性,一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果.
同时有多个事务在进行会怎么样呢?
多事务的并发进行一般会造成以下几个问题:
脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.
不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.
幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉".
对MySQL的锁了解吗?
当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制.
从锁的类别上来讲,有共享锁和排他锁.
共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个.
排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.
锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁.MyIsam实现表级锁。
他们的加锁开销从大大小,并发能力也是从大到小.
说下悲观锁、乐观锁
乐观锁:对于数据冲突保持一种乐观态度,操作数据时不会对操作的数据进行加锁,只有到数据提交的时候才通过一种机制来验证数据是否存在冲突。
悲观锁:对于数据冲突保持一种悲观态度,在修改数据之前把数据锁住,然后再对数据进行读写,在它释放锁之前任何人都不能对其数据进行操作,直到前面一个人把锁释放后下一个人数据加锁才可对数据进行加锁,然后才可以对数据进行操作,一般数据库本身锁的机制都是基于悲观锁的机制实现的。
PostGreSql锁机制
为什么要尽量设定一个主键?
主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键.设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全.
在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降.
主键是聚簇索引,如果没有主键,InnoDB会选择一个唯一键来作为聚簇索引,如果没有唯一键,会生成一个隐式的主键.
要求定义not null ,null值会占用更多的字节,且会在程序中造成很多与预期不符的情况.
InnoDB和MyISAM有什么区别?
InnoDB支持事物,而MyISAM不支持事物
InnoDB支持行级锁,而MyISAM支持表级锁
InnoDB支持MVCC, 而MyISAM不支持
InnoDB支持外键,而MyISAM不支持
InnoDB不支持全文索引,而MyISAM支持。
MVCC机制详解
Buffer pool
MySQL中的varchar和char有什么区别.
char是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用10个字符,而varchar是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.
在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用char,否则应该尽量使用varchar.例如存储用户MD5加密后的密码,则应该使用char.
varchar(10)和int(10)代表什么含义?
varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度,而int的10只是代表了展示的长度,不足10位以0填充.也就是说,int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的,只是在展示时按照长度展示.
sql执行流程
1. 词法分析(Lexical Analysis):将SQL语句分解成标记(token)序列。
1. 语法分析(Syntax Analysis):验证SQL语句的语法结构是否正确。
1. 语义分析(Semantic Analysis):验证SQL语句中的表和字段是否存在,以及是否有⾜够的权限进
⾏操作。
2. 查询优化器(Query Optimizer):为SQL语句创建执⾏计划,并优化执⾏顺序以提⾼性能。
2. 执⾏计划⽣成(Execution Plan Generation):⽣成最优的执⾏计划,包括表的访问顺序、连接算
法和读取⽅式。
3. 执⾏计划执⾏(Execution Plan Execution):根据⽣成的执⾏计划执⾏SQL语句,并返回结果。
Mysql内存使用情况
数据库结构优化
拆分表:冷热数据分离
增加中间表:经常关联的表经常用到的字段
增加冗余字段
优化数据类型
大表优化:限定查询范围、读写分离
垂直分库,垂直分表,水平分表