数据库知识点(索引&数据库内数据间运算)

最新推荐文章于 2023-04-11 17:04:13 发布

一壶自由

最新推荐文章于 2023-04-11 17:04:13 发布

阅读量632

点赞数

数据库、缓存相关的问题。

ACID
事务隔离级别
MVCC
数据库分表
索引
缓存一致性

基本SQL语句参考文章 https://blog.csdn.net/rico_rico/article/details/72867791

InnoDB事务日志（redo log 和 undo log）详解 https://www.cnblogs.com/java-jun-world2099/p/9290966.html

数据库索引 B+树和B树

数据库中最常见的慢查询优化方式是什么？为什么加索引能优化慢查询？你知道哪些数据结构可以提高查询速度？

索引其实就是一种优化查询的数据结构，比如Mysql中的索引是用B+树实现的。哈希表、完全平衡二叉树、B树、B+树等等。

哈希表: 注意字段值所对应的数组下标是哈希算法随机算出来的，所以可能出现哈希冲突。哈希表的特点就是可以快速的精确查询，但是不支持范围查找

一个表的数据行数越多，那么对应的索引文件其实也是会很大的，实际上也是需要存储在磁盘中的，而不能全部都放在内存中，所以我们在考虑选用哪种数据结构时，我们可以换一个角度思考，哪个数据结构更适合从磁盘中读取数据，或者哪个数据结构能够提高磁盘的IO效率。

B树与B+树的区别:

同样的元素，B树的表示要比完全平衡二叉树要“矮”，原因在于B树中的一个节点可以存储多个元素。

同样的元素，B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。所有数据存储在叶子节点,非叶节点只放关键字和指针

为什么数据库选B-tree或B+tree而不是二叉树作为索引结构:

首先,最坏的情况下磁盘IO的次数由树的高度来决定。二叉树相比于顺序查找的确减少了查找次数，但是在最坏情况下，二叉树有可能退化为顺序查找。而且就二叉树本身来说，当数据库的数据量特别大时，其层数也将特别大。

B+树中一个节点到底多大合适？

答案是：B+树中一个节点为一页或页的倍数最为合适。这里说的“页”是Mysql自定义的单位（其实和操作系统类似），Mysql的Innodb引擎中一页的默认大小是16k（如果操作系统中一页大小是4k，那么Mysql中1页=操作系统中4页），可以使用命令SHOW GLOBAL STATUS like 'Innodb_page_size';(16384)查看。

16k的原因 : 假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)，,,,,所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

（叶子结点为数据的是innoDB）

*主键为什么递增参考 https://blog.csdn.net/wukong_666/article/details/54982712

B+树的数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页 ; 如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：

1、使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；

2、该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；
除此以外，如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。应使用auto_increment

主键一般都选择与用户数据不相关的自增序列ID,数据库一般会自动给主键添加索引,唯一键和主键的区别是主键不可以为null

*MySQL行级锁、表级锁、页级锁 https://www.jb51.net/article/50047.htm

页级:引擎 BDB。
表级:引擎 MyISAM ，理解为锁住整个表，可以同时读，写不行
行级:引擎 INNODB ，单独的一行记录加锁

表级，直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
行级,，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
页级，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
上述三种锁的特性可大致归纳如下：
1）表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2）行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3）页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如WEB应用；行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

MySQL表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

InnoDB有两种模式的行锁：

1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
( Select * from table_name where ......lock in share mode)

2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。(select * from table_name where.....for update)

为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。
InnoDB行锁是通过给索引项加锁来实现的，即只有通过索引条件检索数据，InnoDB才使用行级锁，否则将使用表锁！

死锁的关键在于：两个(或以上)的Session(会话)加锁的顺序不一致。

session一个事务只能有一个会话产生....

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

相同表记录行锁冲突--> 这种情况产生的死锁比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。-->主要在于2,4 和4,2 的顺序

间隙锁（Next-Key锁） : 针对于辅助索引,也称范围索引 间隙锁只会出现在辅助索引上，唯一索引和主键索引是没有间隙锁。间隙锁（无论是S还是X）只会阻塞insert操作

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数innodb_locks_unsafe_for_binlog。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录(select)，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！(insert)

Mysql中MyISAM和innodb使用B+树

B树一个很重要的特征就是：高度小。通常我们认为B+树的非叶子节点不存储数据，只有叶子节点才存储数据；而B树的非叶子和叶子节点都会存储数据，会导致非叶子节点存储的索引值会更少，树的高度相对会比B+树高，平均的I/O效率会比较低，所以使用B+树作为索引的数据结构，再加上B+树的叶子节点之间会有指针相连，也方便进行范围查找。

MyISAM中的B+树

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。其采用索引文件与数据文件，索引文件只存放索引，叶子节点存放数据的物理地址。数据文件存放数据。其索引方式是非聚集的。主键索引和辅助索引没有太多区别。

InnoDB中的B+树

InnoDB也使用B+Tree作为索引结构。但是它的主索引与数据都放在一个文件中。这种索引叫做聚集索引，因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有)，如果一个表没有手动建立主键索引，Innodb会查看有没有唯一索引，如果有则选用唯一索引作为主键索引，如果连唯一索引也没有，则会默认建立一个隐藏的主键索引（用户不可见），这个字段长度为6个字节，类型为长整形。InnoDB中主键索引的叶子节点的数据区域存储的是数据记录，辅助索引存储的是主键值。另外，Innodb的主键索引要比MyISAM的主键索引查询效率要高（少一次磁盘IO），并且比辅助索引也要高很多。所以，我们在使用Innodb作为存储引擎时，我们最好：

手动建立主键索引
尽量利用主键索引查询

B+树中的一个节点存储的内容是：

非叶子节点：主键+指针
叶子节点：数据

两个引擎间区别:

区别一：InnoDB的主索引与数据都放在一个文件中。而MYISAM是分开存放的。
区别二：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
区别三：InnoDB的主键索引是聚集索引，而MYISAM不是聚集索引。
MyISAM是非事务安全(不支持事务)的，而InnoDB是事务安全的
MyISAM支持全文类型索引(不支持行级锁)，而InnoDB支持行级锁和外键 : 因此可以支持写并发??
MyISAM存储总行数，而InnoDB不存储总行数

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。

主键选择

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

链接：https://www.nowcoder.com/discuss/105302
分表分库

水平拆分：库内分表(根据表内数据内在逻辑关系同一表按不同条件分散到多个库或表中) 库内分表()
垂直拆分：垂直分库(根据业务耦合性,不相关的分到不同数据库) 垂直分表(针对数据库的列进行,不常用的字段拆分到扩展表中)

最左前缀原则

我们模拟数据建立一个联合索引 select*,concat(right(emp_no,1),"-",right(title,1),"-",right(from_date,2))fromemployees.titles limit10;

mysql联合索引

命名规则：表名_字段名
1、需要加索引的字段，要在where条件中
2、数据量少的字段不需要加索引
3、如果where条件中是OR关系，加索引不起作用
4、符合最左原则

alter table tablename_ add index indexname_(c1,c2,c3,c4); 通过命令行前加explain 看输出中的key 可知是否使用索引

存储时, 是按照第一个字段来分存在左子树还是右子树, 如上图 , 对于 select*from employees.titles where emp_no=1是能用到索引的，因为它能利用上面的索引缩小查询范围，首先和第一个节点“4-r-01”比较，1<4，所以可以直接确定结果在左子树，同理，依次按顺序进行比较，逐步可以缩小查询范围。对于 select*from employees.titles where title='1'是不能用到索引的，因为它不能用到上面的所以，和第一节点进行比较时，没有empno这个字段的值，不能确定到底该去左子树还是右子树继续进行查询。对于 select*from employees.titles where title='1'and emp_no=1是能用到索引，按照我们的上面的分析，先用title='1'这个条件和第一个节点进行比较，是没有结果的，但是mysql会对这个sql进行优化，优化之后会将empno=1这个条件放到第一位，从而可以利用索引。

我们判断一个查询条件能不能用到索引，我们要分析这个查询条件能不能利用某个索引缩小查询范围

最左原则的原理:(参考)

索引本质是一棵B+Tree，联合索引(col1, col2,col3)也是。

其非叶子节点存储的是第一个关键字的索引，而叶节点存储的则是三个关键字col1、col2、col3三个关键字的数据，且按照col1、col2、col3的顺序进行排序。

（图以innodb引擎为例，对应地址指的是数据记录的地址）

联合索引(年龄, 姓氏,名字)，叶节点上data域存储的是三个关键字的数据。且是按照年龄、姓氏、名字的顺序排列的。

而最左原则的原理就是，因为联合索引的B+Tree是按照第一个关键字进行索引排列的。

索引失效

失效的原因很好的一篇 https://blog.csdn.net/student__software/article/details/82078786

　　1用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

　　要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

　　2.对于多列索引，不是使用的第一部分，则不会使用索引

　　3.like查询以%开头

　　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

　　6对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

　　7如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。因为用的是哈希索引。

　　8.使用NOT IN和<>操作

　　9、is null或者is not null 也会导致无法使用索引。

　　10、字符串不加单引号索引失效。

　　11 范围之后全失效存储引擎不能使用索引中范围条件右边的列

索引无法存储null值

　　a.单列索引无法储null值，复合索引无法储全为null的值。

　　b.查询时，采用is null条件时，不能利用到索引，只能全表扫描。

　　为什么索引列无法存储Null值？

　　a.索引是有序的。NULL值进入索引时，无法确定其应该放在哪里。（将索引列值进行建树，其中必然涉及到诸多的比较操作，null 值是不确定值无法　　

　　比较，无法确定null出现在索引树的叶子节点位置。）　

　　b.如果需要把空值存入索引，方法有二：其一，把NULL值转为一个特定的值，在WHERE中检索时，用该特定值查找。其二，建立一个复合索引。例如　

　　create index ind_a on table(col1,1); 通过在复合索引中指定一个非空常量值，而使构成索引的列的组合中，不可能出现全空值。

mysql是如何解决脏读、不可重复读、幻读？

(https://blog.csdn.net/starlh35/article/details/76445267)

或设置事务隔离级别:

为了解决上述问题，数据库通过锁机制解决并发访问的问题。根据锁定对象不同：分为行级锁和表级锁；根据并发事务锁定的关系上看：分为共享锁定和独占锁定，共享锁定会防止独占锁定但允许其他的共享锁定。而独占锁定既防止共享锁定也防止其他独占锁定。为了更改数据，数据库必须在进行更改的行上施加行独占锁定，insert、update、delete和selsct for update语句都会隐式采用必要的行锁定。

但是直接使用锁机制管理是很复杂的，基于锁机制，数据库给用户提供了不同的事务隔离级别，只要设置了事务隔离级别，数据库就会分析事务中的sql语句然后自动选择合适的锁。

注意：事务的隔离级别和数据库并发性是成反比的，隔离级别越高，并发性越低。

数据库事务处理中出现的数据不一致的情况

(带实例的参考据库事务隔离级别和锁实现机制 https://comedsh.iteye.com/blog/698733 对应的还有回复问答)

1、一级封锁协议 (对应 read uncommited) 　　
     一级封锁协议是：事务在对需要修改的数据上面（就是在发生修改的瞬间）对其加共享锁（其他事务不能更改，但是可以读取-导致“脏读”），直到事务结束才释放。事务结束包括正常结束（COMMIT）和非正常结束（ROLLBACK）。
     一级封锁协议不能避免 丢失更新，脏读，不可重复读，幻读！
2、二级封锁协议（对应read commited)　
     二级封锁协议是：1）事务在对需要更新的数据 上（就是发生更新的瞬间）加 排他锁（直到事务结束） ，防止其他事务读取未提交的数据，这样，也就避免了 “脏读” 的情况。2）事务对当前被读取的数据 上面加共享锁 （当读到时加上共享锁），一旦读完该行，立即释放该该行的共享锁 - 从数据库的底层实现更深入的来理解，既是，数据库会对游标当前的数据上加共享锁 ，但是当游标离开当前行的时候，立即释放该行的共享锁。　
     二级封锁协议除防止了“脏读”数据，但是不能避免 丢失更新，不可重复读，幻读 。

事务A	事务B
读取X=100（同时上共享锁）	读取X=100（同时上共享锁）
读取成功（释放共享锁）	读取成功（释放共享锁）
UPDATE X=X+100 （上排他锁）
	UPDATING A（等待事务A释放对X的排他锁）
事务成功（释放排他锁）X=200
	UPDATE X=X+200（成功上排他锁）
	事务成功（释放排他锁）X=300

由此可以看到，事务A的提交被事务B覆盖了，所以不能防止丢失更新。
如果要避免 丢失更新，我们需要额外的操作， 对凡是读到的数据加 共享锁 和排他锁 ，这个往往需要程序员自己编程实现，比如在Oracle 中，需要加 SELECT FOR UPDATE 语句，表明，凡是该事务读到的数据，额外的加上排他锁，防止其他数据同一时间获取相同数据，这样就防止了 丢失更新 ！

3、三级封锁协议（对应reapetable read ）
      三级封锁协议是：二级封锁协议加上事务在读取数据的瞬间必须先对其加 共享锁 ，但是直到事务结束才释放 ，这样保证了可重复读（既是其他的事务职能读取该数据，但是不能更新该数据）。　
      三级封锁协议除防止了“脏”数据 和不可重复读 。但是这种情况不能避免 幻读和丢失更新 的情况，在事务 A 没有完成之前，事务 B 可以新增数据，那么当事务 A 再次读取的时候，事务B 新增的数据会被读取到，这样，在该封锁协议下，幻读就产生了。如果事务A 和事务B 同时读取了资源X=100，同样，如果事务A先对X进行更新X=X+100，等待事务A执行完成X=200，那么事务B 获得X的排他锁，进行更新 X=X+200，然后提交 X=300，同样A的更新被B所覆盖！( 如果要避免 丢失更新，我们需要额外的操作，对凡是读到的数据加 共享锁 和排他锁 ，这个往往需要程序员自己编程实现，比如在Oracle 中，需要加 SELECT FOR UPDATE 语句，表明，凡是读到的数据，我会加排他锁，防止其他数据同一时间获取相同数据) ！
      进阶：repeatable read 导致死锁的情况（即便是不同的资源在相同的顺序下获取）。 比如事务1 读取 A，同时事务2 也读取 A，那么事务1和事务2 同时对 A 上了共享锁，然后事务1 要UPDATE A，而此时事务2 也要 UPDATE A，这个时候事务1 等待事务2 释放其在 A 上的共享锁，然后事务2 要等待事务1 释放其在 A 上的共享锁，这样，事务1 和事务2 相互等待，产生死锁！（SQL Server/DB2 里面有 UPDATE LOCK 可以解决这种情况，具体的思路是，在 repeatable read 的情况下，将读取的数据上的 UPDATE 锁，介于共享锁和排他锁之间的一种锁，该锁的作用是当出现上面这种情况后，事务1 和事务2 对 A 上的是 UPDATE 锁，那么谁先要修改 A，那么该事务就会将 UPDATE 锁可以顺利升级为排他锁对该数据进行修改！）

4、最强封锁协议（对应Serialization)

四级封锁协议是对三级封锁协议的增强，其实现机制也最为简单，直接对事务中所读取或者更改的数据所在的表加表锁，也就是说，其他事务不能读写该表中的任何数据。这样所有的 脏读，不可重复读，幻读 ，都得以避免！

在一个事务中，我们应该尽量把 SELECT Queries 放到最前面，把所有的 Update 放到最后面，避免不必要的等待！

MySQL 到底是怎么解决幻读的？

参考 https://mp.weixin.qq.com/s/U-mzFnooeQ5GboUu_0yXNw

一、什么是幻读

在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做幻行。(insert)

二、为什么要解决幻读

在高并发数据库系统中，需要保证事务与事务之间的隔离性(针对不同隔离级别)，还有事务本身的一致性(逻辑上的正确性)。

三、MySQL 是如何解决幻读的

1. 多版本并发控制（MVCC）（快照读/一致性读）

多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。以 InnoDB 为例，每一行中都冗余了两个字段。

一个是行的创建版本，一个是行的删除（过期）版本。具体的版本号（trx_id）存在 information_schema.INNODB_TRX 表中。版本号（trx_id）随着每次事务的开启自增。

事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。

普通的 select 就是快照读。

select * from T where number = 1;

原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。

2. next-key 锁（当前读）

next-key 锁包含两部分：

记录锁（行锁）
间隙锁

记录锁(行锁)是加在索引上的锁，间隙锁是加在索引之间的。

select * from T where number = 1 for update;

select * from T where number = 1 lock in share mode;

insert

update

delete

原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。

其他：MySQL InnoDB 引擎 RR 隔离级别是否解决了幻读

引用一个 github 上面的评论地址：

Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。

a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。

如果这样理解的话，Mysql的RR级别确实防不住幻读

有道友回复地址：

在快照读读情况下，mysql通过mvcc来避免幻读。
在当前读读情况下，mysql通过next-key来避免幻读。
select * from t where a=1;属于快照读
select * from t where a=1 lock in share mode;属于当前读

不能把快照读和当前读得到的结果不一样这种情况认为是幻读，这是两种不同的使用。所以我认为mysql的rr级别是解决了幻读的。

先说结论，MySQL 存储引擎 InnoDB 隔离级别 RR 解决了幻读问题。面试问烂的 MySQL 四种隔离级别，这篇文章建议大家看下。

如引用一问题所说，T1 select 之后 update，会将 T2 中 insert 的数据一起更新，那么认为多出来一行，所以防不住幻读。看着说法无懈可击，但是其实是错误的，InnoDB 中设置了快照读和当前读两种模式，如果只有快照读，那么自然没有幻读问题，但是如果将语句提升到当前读，那么 T1 在 select 的时候需要用如下语法： select * from t for update (lock in share mode) 进入当前读，那么自然没有 T2 可以插入数据这一回事儿了。

注意

next-key 固然很好的解决了幻读问题，但是还是遵循一般的定律，隔离级别越高，并发越低。