MySQL常见面试总结
-
并发事务带来哪些问题?
脏读(Dirty read):一个事务读到另一个事务未提交的更新数据。
丢失修改(Lost to modify):一个事务访问数据并对其修改时,另外一个事务也访问了该数据并进行了修改。第二次修改覆盖了第一次的修改,导致第一次修改的数据丢失。
不可重复读(Unrepeatableread):一个事务两次读取同一行数据,两次读到的数据不一样。(重点在于修改)
幻读(Phantom read):一个事务执行两次查询,第二次查询比第一次查询多出了一些数据行。(重点在于数据库中的数据有新增或者删除)
-
如何解决并发带来的问题呢?四种隔离级别(由低到高)
数据库系统采用不同的锁类型来实现以上四种隔离级别,具体的实现过程对用户是透明的。
MySQL隔离级别
read uncommitted:读未提交。就是一个事务可以读取另一个未提交事务的数据,导致脏读(脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据)。
例:老板要给程序员发工资,程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字,按成3.9万/月,该钱已经打到程序员的户口,但是事务还没有提交,就在这时,程序员去查看自己这个月的工资,发现比往常多了3千元,以为涨工资了非常高兴。但是老板及时发现了不对,马上回滚差点就提交了的事务,将数字改成3.6万再提交。分析:实际程序员这个月的工资还是3.6万,但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据,这就是脏读。如何解决脏读呢?–》读提交
read committed:读已提交,就是一个事务要等另一个事务提交后才能读取数据,导致不可重复读(不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。)
例:程序员拿着信用卡去享受生活(卡里当然是只有3.6万),当他埋单时(程序员事务开启),收费系统事先检测到他的卡里有3.6万,就在这个时候!!程序员的妻子要把钱全部转出充当家用,并提交。当收费系统准备扣款时,再检测卡里的金额,发现已经没钱了(第二次检测金额当然要等待妻子转出金额事务提交完)。程序员就会很郁闷,明明卡里是有钱的。那怎么解决可能的不可重复读问题?–》Repeatable read !
repeatable read(mysql默认的隔离级别):同一个事务在执行的不同阶段看到的数据结果一致
例:程序员某一天去消费,花了2千元,然后他的妻子去查看他今天的消费记录(全表扫描FTS,妻子事务开启),看到确实是花了2千元,就在这个时候,程序员花了1万买了一部电脑,即新增INSERT了一条消费记录,并提交。当妻子打印程序员的消费记录清单时(妻子事务提交),发现花了1.2万元,似乎出现了幻觉,这就是幻读。那怎么解决幻读问题?Serializable!
重复读可以解决不可重复读问题。写到这里,应该明白的一点就是,不可重复读对应的是修改,即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作,而不是UPDATE操作。
serializable:可串行化。通过强制事务串行执行,避免了幻读的问题。serializable会在读取的每一行数据上都加锁,可能导致大量的超时和锁争用问题。
Serializable 是最高的事务隔离级别,在该级别下,事务串行化顺序执行,可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下,比较耗数据库性能,一般不使用。
-
乐观锁和悲观锁
-
B+树与Hash索引:表是根据主键顺序以索引形式存放。每一个索引在InnoDB中对应一个B+树。
二叉树
<>
如图所示,有一个user表的数据有id和name字段,id是主键,主键是聚集索引。如果用二叉树存储这个user表的聚集索引,结构就如右图所示。二叉树的特点是左侧的子节点的值都比父节点小,右侧的子节点的值都比父节点大。如果我们要查找某一个值,就从顶节点开始查找,这样不会超过数的高度的次数,就能查找到指定的值,效率比全表扫描是要高。
<>
B树
使用平衡二叉树存储索引有两个缺点
平衡二叉树每个节点只存储一个键值,当数据量特别的大的时候,树的高度也必然很高,查找次数也会很多,影响效率,毕竟每次查找都是一次读盘的操作。
从磁盘读取数据时是每次读取一个页的数据,如果每次读取只读取一个键值,也是对磁盘的浪费
<>
B 树相对于平衡二叉树,有两个改进。基于这两点改进,高度变小了,读取磁盘的次数变少了,索引查找效率就高了。
<>
每个节点(页)存储了更多的键值(key)和数据(data)。
每个节点拥有更多的子节点,并不只是二叉树了。B+树
B树缺点
非叶子节点既存key也存数据,而每个页大小是固定的,所以每个页中存储的key比较少,数据量大的时候树依然比较高。
数据不是按顺序存储的,当范围查找、排序查找、去重查找的时候,需要读取多个级别的多个页,效率比较低。
特点
非叶子节点只存key不存数据。
B+树中,非叶子节点是不存储数据的,只存储key,这样每个页能够存储更多的key,使得树更胖更矮,所以读取磁盘次数更少。假如B+树一个节点可以存储 1000 个键值,那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据,一般根节点是常驻内存的,所以一般我们查找 10 亿数据,只需要 2 次磁盘 IO。 所有数据都存在叶子节点
所有的数据都存在叶子节点,所以数据是按顺序存储的,使得范围查找、排序查找更加方便。
B+树的页之间用双向链表连接,数据间用单项链表链接页之间有双向链表链接,使得扫描数据更加快捷。
聚集索引
上面说的B+树在叶子节点存储数据,这样的索引实际上就是聚集索引,像MySQL里会默认根据主键创建的索引就是聚集索引,根据主键构建一棵B+树,主键所对应的值直接存在叶子节点中。
根据主键查找的意义图如下所示:select * from user where id>=18 and id <40
<>
非聚集索引
根据主键意外的字段创建的索引一般都是非聚集索引,非聚集索引也是用B+树构建的,他和聚集索引的唯一不同就是叶子节点中保存的值不是实际的值,而是主键值,找到主键值后再去聚集索引中查找。
举例:有这样一张表,id是主键,我们在luckyNum字段上创建的非聚集索引 select * from user where luckNum=33
非聚集索引是否一定要会表查询
不一定,当查询语句的字段全部命中了索引,就不必回表查询。例如在员工表的年龄上建立了索引,当进行select age from employee where age<20的查询时,在索引的叶子节点上,已经包含了age信息,不会在回表查询。
如何避免回表查询
索引覆盖被查询的字段 例如:select age_1 from employee where age<20。此时age_1是被查询的字段,age是索引。索引覆盖了被查询的字段。但select age_1, name from employee where age<20,此时的索引age并没有覆盖被查询的字段age,name.
覆盖索引
查询的索引覆盖了所有需要查询的字段值
优势:覆盖索引可以减少树的搜索次数(避免回表),显著提高查询性能,是一个常用的优化手段。
Hash索引
索引只存储哈希码及行指针,而不是字段值,所以查找到对应的指针后同样需要读取数据中的行。
哈希索引不支持索引列的部分查找,因为哈希索引始终使用索引列的全部内容来计算哈希码。
哈希索引不是按照索引值的顺序存储的,所以无法用于排序。
只支持等值比较查询,包括=,IN(),<=>,不支持范围查询。
哈希冲突会影响查询速度,此时需遍历索引中的行指针,逐行进行比较。