面试之关系型数据库

数据库设计三范式第一范式。任何一张表必须有主键每一个字段具有原子性不可再分。第二范式。所有非主键字段完全依赖主键字段不存在部分依赖复合主键可能存在此情况。第三范式。所有非主键字段直接依赖于主键字段不存在传递依赖比如员工表中存在部门编号和部门名。注多对多时建三个表用关系表存放一个主键和两个外键一对多时建两个表“多”表加外键一对一时拆分表字段太多外键加unique约束主键和外键一一对应。​几个语法使用细节count(*)统计表数据总行数count(字段)统计该字段下不为NULL的总数。使用group by时select后面的字段必须是分组的字段或者其他字段的聚合函数。distinct必须在查询内容最前面若后面跟多个内容则将组合后的内容去重。sql92语法表连接时用where条件直接过滤sql99语法表连接时用join关键字连接将连接条件与其他过滤条件隔离inner join和join等价inner可省略。union合并结果集时要求列和列类型都必须一致效率比表连接更高将笛卡尔积乘法变为加法。mysql默认短日期date格式’%Y-%m-%d’默认长日期datetime格式’%Y-%m-%d %h:%i:%s’若存取时满足要求则默认会自动进行字符串和日期间的相互转换。删除表所有数据时使用truncate比delete效率更高但delete删除时硬盘中的空间不会被释放故支持事务回滚。七种连接内连接INNER JOIN。SELECT*FROMtableA aINNERJOINtableB bONa.keyb.key左连接LEFT JOINSELECT*FROMtableA aLEFTJOINtableB bONa.keyb.key右连接RIGHT JOINSELECT*FROMtableA aRIGHTJOINtableB bONa.keyb.key左外连接LEFT JOINSELECT*FROMtableA aLEFTJOINtableB bONa.keyb.keyWHEREb.keyISNULL右外连接RIGHT JOINSELECT*FROMtableA aRIGHTJOINtableB bONa.keyb.keyWHEREa.keyISNULL全连接FULL OUTER JOINSELECT*FROMtableA aFULLOUTERJOINtableB bONa.keyb.key//但mysql不支持FULL OUTER JOIN操作可用下面语句实现SELECT*FROMtableA aLEFTJOINtableB bONa.keyb.keyUNIONSELECT*FROMtableA aRIGHTJOINtableB bONa.keyb.key全外连接FULL OUTER JOINSELECT*FROMtableA aFULLOUTERJOINtableB bONa.keyb.keyWHEREa.keyISNULLORb.keyISNULL//但mysql不支持FULL OUTER JOIN操作可用下面语句实现SELECT*FROMtableA aLEFTJOINtableB bONa.keyb.keyWHEREb.keyISNULLUNIONSELECT*FROMtableA aRIGHTJOINtableB bONa.keyb.keyWHEREa.keyISNULLB树和B树这里仅大致记录B树和B树的概念和由来具体细节操作不细说。平衡二叉树是二叉排序树的改进版每个结点只有一个键每个结点最多两个子树左小右大且二者高度绝对值不超过1。但当数据量明显增多时平衡二叉树的高度上升的很快这会显著影响平衡二叉树的查询效率。多路查找树多路查找树的每个结点可以有多个键每个结点的子树数量为键数1子树存在于键之间依然是左小右大。这样的好处是当一个结点的键特别多的时候可以进一步使用二分查找提高效率。但多路查找树并没有严格规定键的插入、删除方法故丢失了“平衡”的理念有着与二叉排序树一样的弊端。B树在多路查找树的基础上添加了一系列的规则又名多路平衡查找树。引入阶的概念m阶B树的每个结点最多有m个子树最少有m/2向上取整个子树根节点除外最少有2个子树1个键。相应的每个结点最多有m-1个键最少有m/2向上取整-1个键。即阶与子树数量对应。插入键时优先填充结点直到结点的键数量溢出再执行分裂操作发生溢出的结点一定是当前B树的叶子结点于是挑出结点中第m/2向上取整个键即中间靠右向上融合。这样一来就让多路查找树尽可能保持高度平衡维持了查询效率。B树在B树的基础上改进。m阶B树的每个结点最多有m个子树最少有m/2向上取整个子树根节点除外最少有2个子树1个键。但子树不再是存在于键之间而是由键直接引出故每个结点最多有m个键最少有m/2向上取整个键。且每个键存在于其引出的子结点中并是最大或最小全局一致即可的键。这使得所有的叶子结点的键加起来就是整个B树的键并将所有叶子结点链起来形成一个含有所有键的链表。这解决了上述所有查找树的一个弊端无法高效实现全局查询和范围查询需要回溯。B树可以直接在所有叶子结点形成的链表中向后遍历即可。B树与B树的区别B树中存在重复元素。B树只有叶子结点存放数据。B树所有叶子结点形成一个双向链表。为什么用B树不用B树基本都是因为只有叶子结点存放数据这个特性所引出的优点B树的非叶子结点可以存放更多的键一次IO操作可以读进内存的键更多从而减少IO次数。B树查询效率更稳定因为数据都在叶子结点故查询都得从根走到尾而B树可能在非叶子结点就找到了。B树有效解决了全表查询和范围查询直接找到特定叶子结点然后往前或后遍历即可。一亿条1kb的数据走主键索引mysql底层io次数多少MySql的存储单位为页每页16kbMySql的b树中索引结点的大小等同于一个页。一般而言绝大部分数据库主键都为自增bigint大小8byte此外还有一个指向子结点的指针大小为6byte。那么一个非叶结点中可以存放约16kb / (8b 6b) 1170个键即b树的阶一个叶子结点可以存放16kb / 1kb 16条数据。那么一个二层b树可以存放约1170 * 16 18720条数据一个三层b树可以存放约1170 * 1170 * 16 2190万条数据一个四层b树可以存放约1170 * 1170 * 1170 * 16kb 256亿条数据。故存放一亿条1kb的数据需要一个四层b树而通常每个表的主键索引根节点都是常驻内存的所有这里mysql底层需要3次io通常都是1~3次。MyISAM 和 InnoDBInnoDB支持行级锁、事务和外键缓存索引及真实数据存储内容多适用于高并发读写操作场景。MyISAM仅支持表级锁不支持事务和外键只缓存索引适用于快速读操作场景。MyISAM和InnoDB的索引都使用B树但MyISAM使用非聚簇索引叶节点存放数据的指针InnoDB使用聚簇索引叶节点存放真实数据。MyISAM存储文件包括frm文件表结构、MYD文件表数据、MYI文件表索引InnoDB存储文件包括frm文件、idb文件表数据和索引。MySql索引索引的定义帮助MySql快速获取数据的数据结构。mysql索引分类普通索引唯一索引不允许值重复可以为NULL主键索引特殊的唯一索引不允许NULL联合索引组合不重复不允许NULL全文索引类似搜索引擎。聚簇索引每个表都有唯一的聚簇索引默认创建表时指定主键就会创建主键聚簇索引后面默认用主键索引作为聚簇索引。聚簇索引的叶子结点存放的是完整的数据行其他所有索引树的叶子结点存放的都是索引主键。故当利用非主键索引查询时需要先获得对应的主键值再去主键索引树查询这个过程称为“回表”。聚簇索引的数据物理存储顺序与索引顺序一致故查询效率较高。但在插入数据时可能造成“页分裂”现象即为了保证数据存放有序但空间有限时可能会将数据移动到另一个数据页此时只需修改主键索引树其他索引无需修改。非聚簇索引非聚簇索引的叶子结点存放的是完整数据行的指针所有索引与数据都是分开存储主键索引与非主键索引并无区别叶子结点都是指针。非聚簇索引下存放的数据逻辑连续但物理不连续故查询效率较低。最左匹配原则当使用联合索引查询时会涉及到这个原则联合索引即利用多个字段组合成为索引。最左针对的是联合索引而言sql语句中where后面跟的条件的先后顺序并无任何影响。例创建bcd索引当查询条件为c1 and d1时违背了最左匹配原则无法走该索引。当查询条件为b1 and d1时可以走该索引但只会过滤掉b!1的内容因为后面紧跟的c字段没有指定无法比较此时会从叶子结点依次往后遍历。查询条件出现比较符号时会先查询等于的情况然后从叶子结点后面依次遍历此时跟在后面的查询条件也会无法利用索引。覆盖索引也叫索引覆盖当要查询的内容即select后跟的内容全部包含在索引之中这种情况称为覆盖索引。若非覆盖索引则需要进行“回表”。前缀索引当要创建的索引字段内容过多一般为字符串等需要进行裁剪取前面一部分作为前缀索引。前缀索引不能太短因为要使该索引的选择性足够高选择性指数据行对于索引的不重复度这样才能充分利用索引实现高效查询同时前缀索引不能太长因为这样会有内存大小限制且会将大量时间花费在B树中间结点的比较上影响效率。索引失效不遵循最左匹配原则。where后面and条件的先后顺序不影响结果在索引列做任何操作计算、函数、自动或手动类型转换等。范围查询不等于操作也属于范围查询后面的索引全部失效。like带前缀%。如何解决使用覆盖索引对索引进行全表扫描得到结果当like前缀是常量时不会中断索引的使用。使用or关键字。索引优化小表驱动大表。对INLJ而言外层循环决定索引查询次数对BNLJ而言外层循环决定IO次数。故外层循环次数越少越好即驱动表的大小越小越好mysql优化器会自动选择小表驱动。左连接给右表建索引因为左表是驱动表它的数据无论如何都会有即使有索引最后查询行数还是和全表查询一样。右连接给左表建索引。内连接都可以建但优先给大表。假设语句1. select * from A where id in (select id from B) 语句2. select * from A where exists (select 1 from B where A.id B.id)两条语句的效果等价。但in会将后面的查询结果缓存起来后逐一比较故当B的数据集较小时in的效率更高而exists只会看后面的语句是否返回true或falseA中查询有多少行就会执行多少次exists查询判断故当B的数据集较大时exists的效率更高。Nested-Loop Join算法Simple Nested-Loop JoinSNLJ算法简单嵌套循环连接。sql执行流程1. 取出驱动表中一行数据2.全表扫描找出被驱动表中满足关联字段的数据3. 合并数据4. 重复执行上述三步直到驱动表遍历结束。也就是双重循环暴力大法代价太大一般不用。Index Nested-Loop JoinINLJ算法索引嵌套循环连接。sql执行流程1. 取出驱动表中一行数据2.索引扫描找出被驱动表中满足关联字段的数据3. 合并数据4. 重复执行上述三步直到驱动表遍历结束。一般当被驱动表中的关联字段有索引时会采用此算法。Block Nested-Loop JoinBNLJ算法块嵌套循环连接。sql执行流程1. 把驱动表中的所有数据放进join_buffer中2. 把被驱动表中数据逐行取出与join_buffer中的数据做对比3. 合并数据。BNLJ算法可以有效减少IO次数但数据比较次数和SNLJ算法一样。简单表述SNLJ是对驱动表的每一行进行一次被驱动表的全表扫描而BNLJ是对每一个join_buffer进行一次被驱动表的全表扫描。总结一般不采用SNLJ算法当被驱动表的关联字段有索引时采用INLJ算法否则采用BNLJ算法。explain的type字段以下常见的6种type从上至下效果越来越好一般至少达到index最好ref。all全表扫描。index另一种全表扫描但扫描的是索引树当找到B树某个叶子结点后向后遍历所有结点。当extra列出现USING INDEX时说明出现覆盖索引即要查找的字段均包含在索引中当extra列仅有USING Where时表明需要回表当extra列出现USING Filesort时说明mysql引擎进行了额外的文件排序当extra列出现USING Tempory时说明出现了临时表保存中间结果。range基于索引的范围扫描。ref非唯一索引当找到特定叶子结点后还要向后进行小范围遍历因为索引字段或组合不唯一。eq_ref唯一索引当找到特定叶子结点后即结束因为索引字段或组合唯一。const常量指最终查找结果只有一条记录匹配索引唯一不代表实际数据唯一。system系统常量指整个表中只有一行记录。函数和存储过程二者都可以看做是一组为了完成特定功能的SQL 语句集区别是函数参数默认为IN且必须有返回值必须是一个值可以在sql语句中调用而存储过程有三种类型参数IN、OUT类似面向对象语言中的引用传递、INOUT没有返回值只能直接调用。定义函数的代码如下/*定义rand_string函数返回一个长度为n的随机字符串*/DELIMITER$$CREATEFUNCTIONrand_string(nINT)RETURNSVARCHAR(255)BEGINDECLAREchars_strVARCHAR(100)DEFAULTabcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ;DECLAREreturn_strVARCHAR(255)DEFAULT;DECLAREiINTDEFAULT0;WHILEinDOSETreturn_strCONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));SETii1;ENDWHILE;RETURNreturn_str;END$$函数可以在sql语句中或者存储过程中调用如select rand_num(1, 10);定义存储过程的代码如下/*定义存储过程批量插入部门表部门编号从start开始递增插入max_num条记录*/DELIMITER$$CREATEPROCEDUREinsert_dept(INstartINT(10),INmax_numINT(10))BEGINDECLAREiINTDEFAULT0;SETautocommit0;REPEATSETii1;INSERTINTOdept(deptNo,dName,location)VALUES((starti),rand_string(10),rand_string(8));UNTIL imax_numENDREPEAT;COMMIT;END$$存储过程只能直接调用如insert_dept(0, 100);存储过程的优点有重复使用减少网络传输开销只需传输过程名和参数防止用户对表的直接访问。视图理解为虚拟表和普通表一样使用只保存sql逻辑不保存结果主要作用是简化sql语句存储时也是以文件形式在硬盘上存放。视图和原表是有关联关系的对视图增删改也会对原表产生影响。创建视图语句create view xxx as ‘select xxx’。锁按锁粒度分为表锁和行锁表锁Myisam只支持表锁开销小加锁快不会出现死锁但锁冲突概率高并发效率低。语句lock table xxx read/write。行锁Innodb支持行锁和表锁通过给索引项加锁实现行锁故查询时没有索引或索引失效会导致行级锁升级为表级锁。加行锁的开销大加锁慢会出现死锁但锁冲突概率低并发效率高。按锁使用方式分为共享读锁和排它写锁共享锁也称读锁S锁share。多个事务可以同时获取读锁以读取数据但都不能写数据直到所有读锁被释放后有事务获取写锁手动加读锁语句select …… lock in share mode。排它锁也称写锁X锁exclusive。同一时刻只能有一个事务拥有写锁其他事务的读或写都会被阻塞手动加写锁语句select …… for update。按设计思想分为悲观锁和乐观锁悲观锁认为每次操作都可能出现异常对每次操作都加排它锁。乐观锁认为每次操作都不会出现异常通过添加数据版本字段实现并发安全当提交数据更新时与上一次获取的数据版本对比数据版本是否一致不一致则认为出现冲突即CAScompare and swap思想。间隙锁当使用索引的范围查询并请求加行锁时Innodb会把范围的所有索引项加锁即使这条记录并不存在这种情况称为间隙锁。事务事务是由一步或几步数据库操作序列组成逻辑执行单元这系列操作要么全部执行要么全部放弃执行。ACID特性原子性Atomicity事务不可再分解。mysql使用undo log实现。一致性Consistency事务执行后必须使数据库从一个一致性状态变到另一个一致性状态同时成功或同时失败。隔离性Isolation各个事务的执行互不干扰任意一个事务的内部操作对其他并发的事务都是隔离的。持久性Durability事务一旦提交对数据所做的任何改变都要记录到物理数据库中。mysql使用redo log实现。注mysql使用MVCC和锁实现事务的一致性与隔离性。mysql如何支持事务begin开始一个事务rollback事务回滚commit事务确认mysql默认是自动提交事务即一个语句一个事务需要执行set autocommit 0或者start transaction二者有区别语句来禁止自动提交。Innodb存储引擎会把开启事务后的所有执行语句记录到一个事务性日志文件中用于提交或者回滚事务。并发事务可能存在的问题更新丢失。A和B事务都基于同一数据进行更新从而造成某个事务的更新丢失类似两个线程都执行i却只加了一次。脏读。即A事务读取到了B事务尚未提交的更新数据。不可重复读。即A事务读取了B事务已提交的修改/删除数据。幻读。即A事务读取了B事务已提交的新增数据。mysql提供的事务隔离级别读未提交。最低的隔离级别可能出现脏读。大多数数据库最低的隔离级别是读已提交。《没有提交就读到了》读已提交。A事务只能读取到B事务已经提交的数据解决了脏读每次读取到的数据都是绝对真实的故可能出现多次读数据结果不一样即可能出现不可重复读。读已提交是Oracle数据库默认隔离级别。《提交之后才能读到》可重复读。A事务开启后每次读取到的数据都是一致的永远读取到的都是刚开启事务时的数据解决了不可重复读但可能出现幻读。可重复读是mysql数据库默认隔离级别。《提交之后也读不到除了新增的》序列化。最高的隔离级别所有事务排队执行解决了所有并发事务问题但并发效率很低。多版本并发控制MVCCMVCC是mysql中基于乐观锁理论实现读已提交和可重复读隔离级别的方式主要是为了实现不用加锁也能解决数据的读-写冲突问题读-读无需处理、写-写通过加锁实现换句话所MVCC是mysql实现快照读的方式。参考链接https://blog.csdn.net/SnailMann/article/details/94724197?utm_mediumdistribute.pc_relevant.none-task-blog-2defaultCTRLISTdefault-2.no_search_linkdepth_1-utm_sourcedistribute.pc_relevant.none-task-blog-2defaultCTRLISTdefault-2.no_search_link快照读和当前读快照读 (snapshot read)指读到的数据可能是历史数据并不是数据库最新的数据。Innodb中的普通select语句都属于快照读其中读已提交隔离级别下是每次执行select语句时都创建快照可重复读隔离级别下是仅第一次执行select语句时创建快照。当前读(current read)指读取到的数据是数据库最新版本。Innodb中的select xxx lock in share mode、select xxx for update、update、 insert、 delete都属于当前读因为它们都用到了锁来保证并发安全。undo logredo log和binlogredo log属于存储引擎层innodb专属是一种物理格式日志记录的是事务中对磁盘中每一个数据页的物理修改空间大小固定当空间满了后就必须将redo log中的内容刷新到磁盘中默认有2个redo log文件可以循环写入。redo log的写入在事务进行过程中不断发生不论事务是否提交都会被记录。undo log保存了事务发生之前的数据的一个版本可以用于回滚同时供MVCC实现非锁定读。undo log又分为两种一是insert undo log记录事务在insert新记录时产生的数据只在事务回滚时需要并且在事务提交后可以被立即丢弃二是update undo log事务在进行update和delete时产生的数据 不仅在事务回滚时需要在快照读时也需要所以不能随便删除只有在不需要时统一清除。binlog保存了事务的更新操作语句的二进制数据属于Server层不同存储引擎都会在binlog中写入是一种逻辑格式日志记录的是二进制更新操作语句可用于数据同步主从复制。binlog的写入仅发生在事务提交后即一次性写入。每行记录的三个隐藏字段行id默认的隐藏主键。事务id表明这条记录最后是谁新增/修改的全局递增。回滚指针指向undo log中存放的关于该记录的旧版本。当有事务对一行记录修改/删除时会把即将被修改的数据cpoy到undo log中同时往数据库中写入新的一行记录新的记录除了被事务修改的显示字段外对应隐藏字段的事务id会变为执行当前操作的事务id同时回滚指针会指向刚才copy到undo log中的旧记录。如此一来undo log则会存放关于该记录的一条版本链。read view读视图可以理解为数据快照是快照读的重要依据。read view中存放了三个内容创建该read view时活跃的事务id集合。活跃的事务id集合中最小的事务id。创建该read view时系统即将分配的下一个事务id。MVCC实现快照读当进行快照读时首先会创建一个read view。在找到数据库中存放的对应记录后获取该记录的隐藏字段事务id并用事务id与read view中的数据比对从而判断该记录是否属于该事务的可见版本不可见则到undo log中继续沿链表向后寻找该事务可见的最新的旧记录。判断条件共三个记录的事务id是否小于read view中记录的最小事务id是则说明最后操作该记录的事务先于我创建read view的时机故对我是可见的否则继续向下判断。记录的事务id是否大于等于read view中记录的系统即将分配的下一个事务id是则说明最后操作该记录的事务晚于我创建read view的时机故对我是不可见的否则继续向下判断。记录的事务id是否存在于read view中记录的活跃的事务id集合中是则说明我创建read view时操作该记录的事务还未commit故对我是不可见的否则说明最后操作该记录的事务已经在我创建read view前commit了故对我是可见的。读已提交和可重复读读已提交隔离级别是在每一次执行快照读时都创建read view故会出现不可重读问题。可重复读隔离级别是在第一次执行快照读时创建read view后续的快照读都依赖于该read view故实现了可重复读。根据幻读的定义以及mysql可重复读隔离级别的实现原理来看个人认为其实是解决了幻读问题的一个事务两次查询并不会出现结果集行数不一致的情况。但如果事务A开启后一直没有进行快照读期间其他事务对数据进行了新增/修改/删除操作并提交了那么这些操作对事务A都是可见的此时事务A进行快照读会获取到最新的数据。再者如果事务A开启后进行了一次快照读之后其他事务执行了写操作并提交此时事务A如果执行范围性的写操作也会对事务A“看不见”的数据产生影响因为在innodb中update和delete都属于当前读。mysql如何定位慢查询1、通过设置全局变量开启慢查询日志例SETGLOBALslow_query_logON;-- 开启慢查询日志SETGLOBALlong_query_time1;-- 超过1秒的SQL被记录SETGLOBALlog_queries_not_using_indexesON;-- 记录未走索引的查询上述方式为临时生效重启后失效。长期生效需修改配置文件2、分析慢查询日志使用mysqldumpslow工具按耗时倒序查看sql例mysqldumpslow -s t -t 10 /var/log/mysql/slow.log3、使用explain工具分析sql重点关注type字段尽量走索引而非全表扫描