1.mysql中的索引:
明明哈希的存储和读取都比树结构更快,为什么mysql还要选择用B+树进行索引,并且InnoDB引擎是不支持hash索引的
因为取一条数据的时候哈希的速度的确很快,时间复杂度为1,B+树的时间复杂度是log(n),但是,我们sql查询中很多情况会是范围查询,条件有group,排序,大于或小于等条件,这个时候hash的时间复杂度上升到了n,二B+树的时间复杂度还是log(n)
B+树和B树以及二叉树的比较
首先二叉树,每个节点只能存储一个数据,当数据量大时,树的高度会很高,查询会很慢
B树则节点和叶子节点都存储数据,需要遍历节点才能获得所有数据
B+树则是,数据只存储在叶子节点上,并且所有的叶子节点都连接成为了一个链表,所以查询数据时不用遍历所有的节点,由于中间节点只用于索引,所以B+树的根到每个叶子节点的路径长度是一致的
B树和B+树都设置为了按页读取,一页的数据一般是4K,那么可以大大减少磁盘IO
树的分裂:例如五阶的B+树,则每个节点能存储的最大数值为4,大于则需要分裂,取中间数为索引,左边为小于索引的数,右边为大于或等于索引的数,而5阶段的B数,中间的数不会变为索引而会上升为中间节点
由于索引是按页从磁盘上读取,所以当索引慢的时候可以通过
SHOW GLOBAL STATUS LIKE 'Qcache%' 命令查询Qcache_hits的值查看缓存的命中数量
2.mysql中MyISAM引擎和InnoDB引擎中的索引对比
MyISAM引擎是mysql5.1以前的默认引擎,InnoDB是5.1版本后的默认引擎
二者的索引实现都是B+树
区别在于:
MyISAM引擎的主键索引B+树的叶子节点存储的是主键和对应行记录的指针,而行记录和索引是分开存储的,所以查找的过程为:主键索引树->主键->对应行记录的指针->行记录
普通索引的B+树的叶子节点存储的是,索引值和对应行记录的指针,行记录与索引也是分开存储的,
查找的过程为:索引树->索引值->对应行记录的指针->行记录
MyISAM引擎的主键索引和普通索引是两个单独的B+树,类别是非聚簇索引
InnoDB引擎的主键索引则做了优化,是聚簇索引,他的主键索引B+树的叶子节点存储的是主键,而对应行记录与主键索引是存储子在一起的,通过主键直接能找到对应的行记录,所以,他的查询过程是:主键索引树->主键->行记录
InnoDB引擎的普通索引也是单独存储的,但是他的叶子节点存储的是主键的值,当定位到主键后,会再通过主键索引去取对应的值,所以是扫了两遍索引树,他的查询过程是:普通索引树->主键->主键索引树->主键->行记录
InnoDB中有且只能有一个聚簇索引,所以在mysql中通过主键查询的速度是最快的
3.InnoDB引擎中是如何控制并发的
首先为什么要控制并发,其实就是当并发来临时,两个不同的线程会操作相同的数据,如果不控制,可能会导致数据不一致
谈到控制并发,最先想到的是锁,最简单的普通锁,就是在当前线程操作这个数据时加上锁以后,不允许其他任何线程对这块数据进行任何操作
但是对于数据库来说,对数据的操作有两种,一种是读,一种是写,那么这时候数据库针对两种操作衍生出来两种锁,一种是共享锁,一种是排他锁,当线程对数据是读的操作时,加入共享锁,这时候其他线程来读的时候,是可以共享的,当线程是写的时候,加排他锁,这时候其他线程来读或者写的时候都不能操作,到这里,一旦发生写的时候,读也不能读了,这时候引入了数据多版本的概念
当线程写数据时,拷贝一份原来的数据,然后对这份拷贝的数据加排他锁,二读的时候,继续给原数据加上共享锁,到写的动作完成后,事务提交后,再将原数据覆盖,这样就避免了,写的时候,不能读
对应到InnoDB中,引入了undo日志,redo日志,回滚段的概念
一般的数据库事务流程是事务开始时,将动作写入undo日志缓冲(存储在缓冲池中,以页存储)中,事务提交后将动作写入redo日志缓冲区中,后面再从redo日志缓冲区中刷新到undo日志的磁盘中,当数据库宕机后重启时,将undo日志中的动作回滚,redo日志中的动作执行,来保证数据库事务的ACID特性
那么redo日志中的动作什么时候会刷到redolog日志中呢,又是什么时候刷进磁盘的呢,InnoDB提供了innodb_flush_log_at_trx_commit属性来配置,支持三种模式,默认值是1,每次事务提交的时候刷新,2,事务提交后写入缓存中,每秒主进程刷新一次,3,超过redo缓存日志空间大小的一半刷新
redo缓存默认大小是8M,所以怎么配置需要根据实际情况决定,在从redo缓冲区刷入redo日志中的同时会将日志中的数据写入数据库磁盘
undo的日志缓冲什么时候刷到undo的日志文件中的呢,又是什么时候删除呢,首先undo日志缓冲存在缓冲池中,master线程会每秒将日志缓冲刷到磁盘中,其次undo日志不会立即删除,而是等待purge线程检测到当前undo日志可以删除时,由purge线程删除,purge线程每秒最多清楚20个无用的undo页
redo日志则只是增加,不会删除,redo日志缓冲和undo日志缓冲分别存在不同的内存块中,undo日志缓冲存储在缓冲池中按页存储,redo日志缓冲区是单独的内存块
InnoDB中控制并发的专业术语叫MVCC,数据多版本并发控制
4.浅谈四种事务隔离级别
先来熟悉下几个名词:
脏读,不可重复读,幻读
首先,我们每个数据库所有的事务都可以分为两步(执行,提交)
脏读是什么:A先执行,未提交,B后执行,未提交,B事务读到了A执行后的结果,两个未提交的事务之间的数据能互相读取,未提交的事务为脏页,所以是脏读
不可重复读是什么:A先执行未提交,B后执行,提交了,A事务查询到了B执行后的结果,对于A事务来说两次查询查到了不同的结果,所以叫不可重复读
幻读是什么:A事务先执行了查询,发现id没有大于3的,然后B事务插入了一条id为四的记录,并且提交了事务,A事务这时再插入一条id为四的记录,报主键冲突,
那么不可重复读和幻读有什么区别呢,看起来对于A事务来说,都是后执行的B事务对A事务造成了影响,一个针对的是update和select,一个针对的是insert
这么说可能比较难以理解,换个说法就是看数据库对哪种操作加了什么锁,接下来我们来看下数据库的几种锁和事务隔离级别
首先数据库有四种隔离级别都知道,关联到上面的三个名词来讲,第一种允许脏读的事务隔离级别:读未提交和不允许幻读出现事务隔离级别串行化,一种是对查询完全不加锁,一种是针对查询都加上意向共享锁,当出现了事务要对记录记性更改时,select会被阻塞住,一般不会采用这两种隔离级别
那么主要来讨论下面两种事务隔离级别,读提交和可重复读
读提交和可重复读两种事务隔离级别针对普通的select语句,都是使用快照读,所以不影响,具体快照读的底层实现之前已经说过
而针对加锁的select:
读提交的处理是,只有在外键检查和主键重复检查时会对索引加间隙锁,其余时间都只加记录锁,不影响其他事务的新插入操作
可重复读的处理是:针对在唯一索引的加锁的唯一查询使用记录锁,不影响插入,而当使用范围查询时,使用间隙锁和临键锁,锁住一个范围,这时候插入动作会被阻塞,当查询事务完成后,才可以插入
5.mysql数据库的几种锁
排他锁,共享锁
意向锁(意向排他锁和意向共享锁)
记录锁,间隙锁(插入意向锁),临键锁
自增锁,
首先:自增锁,mysql专门针对自增长列的锁,他是表级别的锁,所以当一个事务在插入自增列时,所有的事务必须等待,以便第一个事务插入的值,是连续的主键值
共享锁和排他锁:mysql针对不同操作实现的不同的锁,查询的时候获取到共享锁才可查询,获取到排它锁才可以进行修改和删除,这两种锁都是行级别的锁
意向排他锁和意向共享锁都是意向锁:一般应用场景为:select ...for update和select ...share model
在之前的串行化中事务隔离级别时,会将所有的查询都加上意向共享锁,这时只要有插入或者修改,查询操作会阻塞住,所以,意向锁与意向锁之前是不会互斥的,但是意向锁和排他锁之间是互斥的
插入意向锁:针对已有数据的修改和删除,数据库会加上排他锁,那么针对插入呢,其实数据库会加上插入意向锁,插入意向锁是间隙锁的一种,他只锁住了索引,只要插入的位置不冲突,不会互斥
记录锁:顾名思义,只锁住行记录,并且是确定的行记录,所以不会影响新的插入,但是对当前记录的修改和删除,必须获得当前记录的记录锁(所以,当update使用的是id时加的是记录锁)
间隙锁:间隙锁加在索引上,锁住了索引的范围,一般在范围条件查询时加上,只要不在范围之内的插入不会互斥,间隙锁主要针对插入动作,范围之内的修改和删除不会互斥
临键锁:间隙锁+记录锁,锁住后,范围之内的插入,修改,删除都会互斥
所以上面提的几种锁,都是从不同维度来定义锁的维度
6.快照读在不同的事务隔离级别下的玩法
快照读在RC(读提交)和RR(可重复读)下,读的内容是不一致的
在读提交的隔离级别下:快照读始终能读到最新事务提交后的结果,这就是幻读
而在可重复读的隔离级别下:设定一个时间T,A查询事务开始于时间T的话,读不到T时间后开始的事务提交的结果
7.InnoDB的日志
之前将过了快照读的实现方式:undo Log和redo Log
mysql的还有其他几种日志:错误日志,binLog(二进制日志),慢日志,查询日志
binLog记录了所有的DDL语句和DML语句
慢日志记录了超过指定时间的操作日志
错误日志:服务器启动和关闭过程中的信息(未必是错误信息,如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等)、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息
查询日志:查询日志记录了所有的语句(一般是关闭的,比较影响性能)
8.Mysql的插入缓冲
mysql的特性还有插入缓冲以及针对插入的优化
针对聚簇索引的插入优化,由于mysql的聚簇索引是按页存储的,并且是连续的,所以当他前一页插入满了后,不用遍历,直接插入到后一页即可
而针对非聚簇索引的插入优化,mysql采用了插入缓冲的机制:对于每一次的插入不是直接写到索引页中,而是先判断插入的非聚集索引页是否在缓冲池中,如果在则直接插入;若不在,则先放到Insert Buffer 中,再按照一定的频率进行合并操作。合并的频率有几种策略,
一种是master主线程每秒,每十秒会合并一次,
二是辅助索引页没有空间了,超过了缓冲池的32分之一,会合并一次
三十当正常的select发现该索引页再插入缓冲中,会强制合并一次
所以自增锁,一般加在主键索引页中,由于主键索引页非常快,所以一定意义上优化了插入操作