数据库面试宝典

数据库索引、数据库事务、数据库分库分表、数据库锁、数据库面试、Mysql索引、Mysql事务、Mysql分库分表、Mysql锁、Mysql面试

数据库

• 分库分表
  • 根本原因：数据库存储数据及读取数据的原理。
    • 一般来说，数据库存储数据的基本单位是块（一个数据中可以存储多行数据），而不是行。 而且数据库写入块时，一般会预留一部分空间，以便于这个表添加字段使用； 但是，如果添加的是个很大的字段，数据块预留的空间不够用的话，就只能将这行数据拆开到多个块当中来存储； 一旦发生了这种情况，无论是添加字段，还是增删改查操作，其IO成本都大幅度提高了。 所以对于比较大的表，或者字段数目很多的表，一般建议适当的拆分一下，来避免上面说的这种情况发生。
  • 数据库瓶颈
    • 场景1：单个库数据量太大（一般1T~2T是极限）——分库
    • 场景2：单个数据库服务器压力太大（达到了并发上限；遇到读写瓶颈等）——分库并做读写分离
    • 场景3：单个表数据量过大——分表
  • 分库
    • 目标1：读写分离
      • 主库：主要负责插入/更新/关键实时数据的查询； 多个从库：主要负责非实时数据的查询以便缓解主库的压力；
    • 目标2：减少单库的数据量
      • 在联通的时候是按照省份来分库的，每个大省用一个数据库，然后多个数量不大的小省共用一个数据库。
    • Java使用技术：注解/APO等，参考https://www.imooc.com/article/22556和https://www.imooc.com/article/22609
  • 分表
    • 横向分表
      • 将一个表切割为同样结构的不同的表；
      • 表结构完全相同，根据特定的规则来划分表；
      • 例如：根据时间划分、根据Id取模划分等；
    • 纵向分表
      • 将本来可以在一个表中的内容人为的划分为多个表；
      • 通常根据数据的活跃度进行分离；
      • 活跃度较低的数据称为冷数据； 活跃度较高的数据称为活跃数据；
      • Mybatis分表插件：shardbatis2.0 参考https://www.imooc.com/article/25256
• Mysql
  • 存储引擎
    • MyISAM
      • 不支持事务；不支持外键；
      • MyIsam默认用的是表级锁，不支持行级锁： MyISAM在读数据时会给表增加一个读锁，写数据前会增加写锁； 如果读锁没有释放，那么是加不上写锁的；
      • 保存数据时，其索引和数据保存在不同的文件中；索引是“文件名.myi”文件，数据是“表名.myd”文件；
      • 支持全文索引；
      • 有计数器，因此如果查询总记录（即不加任何where条件）数的话非常快，直接就出来；
      • 其索引均是稀疏索引，无论是主键索引、普通索引等等；
      • 使用场景： 频繁执行全表count语句； 对数据进行增删改的频率不高，查询非常频繁； 没有事务；
      • 5.5版本之前的默认引擎
    • InnoDb
      • 支持事务；支持外键；支持行锁定；
      • InnoDB默认用的是行级锁，也支持表级锁； 且InnoDB是非堵塞Select
      • 保存数据时，其索引和数据是保存在一个文件中，即“表名.ibd”文件
      • 不支持全文索引；
      • 必须有且仅有一个密集索引
        • 密集索引的规则如下： 如果有主键，则该主键作为密集索引； 如果没有主键，则该表的第一个唯一非空索引作为密集索引； 如果即没有主键，也没有唯一非空索引，则innodb内部会生成一个隐藏的自增主键，作为密集索引。
      • 使用场景： 数据库增删改查都特别频繁； 可靠性要求比较高，要求支持事务；
  • char和varchar的区别
    • 首先从mysql5开始，char和varchar存放的单位都是字符而不是字节了；
      • 假如test表有两个字段，aa 是char(4),bb是varchar(4); 如果 insert into test values ('中国北京','中国北京'); 那么在以前的版本会报错，因为汉字占用的空间要多余一个字节； 但是mysql5之后不会报错了，因为都是占用的字符；
    • char定长，varchar不定长；
      • * char(10)——表示存储10个字符，哪怕你只存了一个字符，那么这个字段仍然占用着10个字符的空间，其余9个字符是在右边用空格填满的。只不过在我们查询结果的时候，尾部的空格被删掉了而已。
      • char如果尾部有空格，会被忽略掉；
        • 假如test表有两个字段，aa 是char(4),bb是varchar(4); 那么 insert into test values ('ab ','ab '); 之后， 查询 select LENGTH(aa),LENGTH(bb) from test; 会得到如下结果 3 2
      • 由于char是定长的，所以用于索引的效率比varchar高很多；
      • 由于char是定长的，所以特别适合存储定长的值，比如状态码等等；
    • char的范围是0~255，varchar是0~65535；
    • varchar需要使用1到2个额外字节来记录字符串的长度；char不需要，因为它本身就是定长的；
      • varchar ：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节
• 索引
  • 为什么要使用索引
    • 索引一般用于数据量很大的表；
    • 为了避免全表扫描，从而实现快速查询数据；
    • 数据库存储的单位一般是块或者页，每个存储单位里包含多条数据，全表扫描的话就需要吧所有的存储单位都加载到内存中，然后逐个去轮询，这种方式是很慢的；
    • 通过索引可以先快速的定位到要查询的数据在那个存储单元里，然后只需要加载特定的存储单元到内存中即可，这样节省了磁盘IO，提高了速度
  • 什么样的信息能成为索引？ 设计索引的原则是什么？
    • 1、主键、唯一键等具备一定的区分性，且查询频率非常高的字段；
    • 2、特征明显的字段——比如一个表一共有10w条数据，而某个字段只有3种取值可能，那么这个字段肯定是不合适的
    • 3、字段存储内容长度尽可能短的字段。因为索引会占用磁盘空间！ 如果非要使用长字符串做索引，应该指定一个前缀长度，以节省磁盘空间。
    • 4、不能过度设置索引。一是为了节省磁盘空间，而是索引太多的话会降低写数据的性能。
  • 索引的数据结构—— 答：主流的就是B+树、Hash和BitMap，其中Mysql用的是B+树。
    • 二叉树
    • BTree
    • B+Tree
    • Hash索引
      • 缺点
        • 仅仅能满足“=”、“IN”，不能使用范围查询； ——因为Hash索引是拿hash运算之后的值来比较的，而计算之后的值其大小关系就不一定跟计算之前一致，所以不能用于范围查询；
        • 不是实现组合索引的部分索引键查询； ——因为组合索引是将所有索引字段一块进行的hash运算
        • Hash算法可能会出现重复的hash值，这种情况下就只能去再次进行扫描了
    • BitMap（位图）索引
      • 用的不多，好像只有oracle用了
      • 有个很大的缺陷就是锁的粒度太大
  • 问题：问什么mysql索引选择使用B+树？
    • 今天看了几篇文章，自己总结一下。 数据库使用B+树肯定是为了提升查找效率。 但是具体如何提升查找效率呢？ 查找数据，最简单的方式是顺序查找。但是对于几十万上百万，甚至上亿的数据库查询就很慢了。 所以要对查找的方式进行优化，熟悉的二分查找，二叉树可以把速度提升到O(log(n,2))，查询的瓶颈在于树的深度，最坏的情况要查找到二叉树的最深层，由于，每查找深一层，就要访问更深一层的索引文件。在多达数G的索引文件中，这将是很大的开销。所以，尽量把数据结构设计的更为‘矮胖’一点就可以减少访问的层数。在众多的解决方案中，B-/B+树很好的适合。B-树定义具体可以查阅，简而言之就是中间节点可以多余两个子节点，而且中间的元素可以是一个域。相比B-树，B+树的父节点也必须存在于子节点中，是其中最大或者最小元素，B+树的节点只存储索引key值，具体信息的地址存在于叶子节点的地址中。这就使以页为单位的索引中可以存放更多的节点。减少更多的I/O支出。因此，B+树成为了数据库比较优秀的数据结构，MySQL中MyIsAM和InnoDB都是采用的B+树结构。不同的是前者是非聚集索引，后者主键是聚集索引，所谓聚集索引是物理地址连续存放的索引，在取区间的时候，查找速度非常快，但同样的，插入的速度也会受到影响而降低。聚集索引的物理位置使用链表来进行存储。
    • 1：磁盘读写少，因为B+树内部只存储索引信息，不会存储具体数据；
    • 2、查询效率很稳定——得益于其特殊的存储方式，所有的查询效率几乎是相同的；
    • 3、B+树更有利于对数据库的扫描，尤其是范围查找的时候；
    • 4、其实Hash的索引速度也很快，但是Hash存在一些弊端，参考Hash索引的缺点
  • 密集索引和稀松索引的区别
    • 稠密索引：索引树（一般是B+Tree）的每个叶子节点的指针都指向了具体某个数据块的某条记录。因此查询的比较快，因为定位到叶子节点后就能直接定位到数据；
    • 稀疏索引：索引树的叶子节点的指针指向的是数据块的第一条记录的值，所以查询的时候会先根据叶子节点定位到具体的数据块，然后再逐一扫描这个数据块的每条记录；
    • 简单来说就是：稠密索引，每条记录都有索引；稀疏索引，仅部分记录有索引。
  • 以Mysql为例：如何定位并优化慢查询Sql？
    • 通过看日志文件来定位慢sql
      • 打开慢日志记录（一般生产环境建议关闭）
        • show variables like '%query%'; -- 查看mysql的变量： show_query_log: 从off改为on即可； long_query_time: 设置多长时间才被认为是慢查询 show_query_log_file： 慢日志文件 show status like '%show_query%'; -- 查看记录
    • 先大体看一下sql，能看出大多数问题
      • 比如是否使用了or——使用了or的话，即使条件中有索引字段也不会使用索引，除非所有的条件字段都是索引字段
      • 比如是否使用了like且条件是“%XXX”，即以%开头——以%开头的like不会使用索引
      • 比如命名是个字符串字段，查询条件中却没有使用引号引起来——如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
    • 通过explain分析
      • 如果explain结果的type字段为all，则说明存在全表扫描；
      • explan记过的extra字段
      • 一般慢sql的原因都是出现了大数据表的全表扫描导致；
      • 也有可能是联合索引用的有问题，因为联合索引是最左匹配的；
    • 优化：修改sql——尽量走索引
      • 有时候虽然用上了索引，但是索引也不一定最优的，这时候可以通过force index来强制指定使用哪个索引，对比一下
    • 优化：修改sql——尽量避免使用or
      • 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)；注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
  • 以Mysql为例：联合索引（多列组合索引）的最左匹配原则的成因？
    • 最左匹配原则：=和in是最左匹配，遇到第一个范围查询（大于、小于between、like）就停止匹配； =和in可以乱序写，mysql会自动优化；
    • 原因：mysql会对第一个字段进行排序，然后在第一个字段排好序的基础上在对第二个字段排序，依次类推； 所以最后的结果是，第一个索引字段是有序的，后续的索引字段其实是无序的；
  • 以Mysql为例：索引是建立的越多越好吗？——肯定不是
    • 索引也是占用存储空间的
    • 数据变更的时候需要维护索引，因此更多的索引意味着更多的维护成本，降低了效率；
    • 数据量很小的表不需要建索引；
  • mysql索引失效的原因有哪些?
    • 1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
    • 2.对于多列索引，不是使用的第一部分，则不会使用索引
    • 3.like查询是以%开头
    • 4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
    • 5.如果mysql数据量很小，就算创建了索引，也不会使用
  • 对于mysql中的一个表，主要的查询场景有两种：使用A字段查询或使用ABC三个字段组合查询，且两种查询的使用频率差不多，那么应该如何给这个表创建索引？
    • 答：只需要创建一个先A后B后C的组合索引即可。 这时候查询时 只要查询条件有A，那么就能用上索引(当然有些情况下会令索引失效，比如有or条件或者索引字段上使用了左like等等)。
  • mysql中in走不走索引？
    • 走！
      • 网上好多说in不走索引的。 我自己在innodb引擎用explain试了一下，发现其实是走索引的。 应该是mysql5.5之前in不走索引，5.5之后走索引了。
• 锁&事务
  • 共享锁 和 排他锁：
    • 无论表级锁还是行级锁，都可以分为共享锁和排他锁；
    • 有了共享锁，仍然可以继续上共享锁，但是不能上排他锁
    • 有了排他锁，既不能再上共享锁，也不能在上其他的拍他锁
  • 悲观锁&&乐观锁：
    • 悲观锁指对数据被意外修改持保守态度，依赖数据库原生支持的锁机制来保证当前事务处理的安全性，防止其他并发事务对目标数据的破坏或破坏其他并发事务数据，将在事务开始执行前或执行中申请锁定，执行完后再释放锁定。这对于长事务来讲，可能会严重影响系统的并发处理能力。 自带的数据库事务就是典型的悲观锁。
    • 乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。乐观锁适用于读多写少的应用场景，这样可以提高吞吐量。 一般是加一个版本号字段 每次更新时候比较版本号
  • 有哪些锁？
    • 表级锁
    • 行级锁
    • 页级锁：没怎么了解
    • MyIsam引擎只支持表级锁； InnoDb引擎既支持表级锁也支持行级锁； BDB引擎支持页级锁；
  • 数据库事务的四大特定？
    • 原子性：事务中的所有操作要么全部执行，要么全部回滚；
    • 一致性：事务前后的数据必须能对得上。比如说转账过程用事务控制，则必须转账前和转账后双方加起来的总和不变才行；
    • 隔离性：多个事务并发执行时，一个事务不应该影响到另一个事务的运行；
    • 持久性：事务提交后必须永久性的保存，不能出现事务提交了但是数据每保存成功的情况；
  • 事务隔离级别以及各个级别下的并发访问问题？
    • https://www.cnblogs.com/huanongying/p/7021555.html
  • 解决死锁的办法：
    • 1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
    • 2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
    • 3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
  • InnoDb可重复读隔离级别下如何避免幻读？
  • 事务隔离是怎么实现的？
    • 是基于锁来实现的
  • 如何显示的给一张表增加一个读锁？
    • lock tables XXXX read;
  • MyISAM与InnoDb关于锁方面的区别？
    • MyIsam默认用的是表级锁，不支持行级锁： MyISAM在读数据时会给表增加一个读锁，写数据前会增加写锁； 如果读锁没有释放，那么是加不上写锁的；
    • InnoDB默认用的是行级锁，也支持表级锁； 且InnoDB是非堵塞Select
  • RC、RR级别下的InnoDB的非堵塞读如何实现？
• 常见问题
  • 如何设计一个关系型数据库？
    • 可以结合mysql来记忆 
  • mysql的varchar和char有什么区别？——见mysql中整理的内容
  • mysql中varchar(50)中的50代表的涵义？
    • 首先50代表这个字段可以存储50个字符，无论是字母、数字、还是汉字； 另外，这个字段的长度肯定不是50个字节，因为字母、数字、汉字占用的字节数不同，而且varchar需要额外的1到2个字节来存储字符串的长度。所以假如这个字段存放了50个字节的字符串，那么这个字段实际占用的空间也应该是51个字节。 字符串小于等于255的时候，会占用1个额外字节来存储字符串长度； 字符串大于255的时候，会占用2个额外字节来存储字符串长度；
  • mysql中varchar(254)有什么讲究？
    • 这么定义是为了尽量节省空间，因为varchar存储数据时会额外占用1到2个字节来存储字符串的长度 字符串小于等于255的时候，会占用1个额外字节来存储字符串长度； 字符串大于255的时候，会占用2个额外字节来存储字符串长度；
  • mysqldump备份时，如果想要sql中每一条数据都是单独的insert语句怎么处理？
    • --skip-extended-insert 参数。 mysqldump -uroot -p helei --skip-extended-insert
  • mysql中如何实现字段值的加解密？
    • 1、直接用AES_ENCRYPT加密成二进制数据，这种情况需要将字段设置为blob类型 // AES_ENCRYPT 对123456进行加密， 密钥为system_word insert into XXX values (AES_ENCRYPT('123456','mypassword')); // AES_DECRYPT进行解密 SELECT AES_DECRYPT(UNHEX(YYY),'mypassword') AS YYY FROM XXX； 2、用AES_ENCRYPT加密后，转换成16进制存储，这种情况可以存储为varchar； // AES_ENCRYPT 对123456进行加密， 密钥为system_word;然后用HEX加密完成的二进制数据转换成16进制 insert into XXX values (HEX(AES_ENCRYPT('123456','mypassword'))); // UNHEX 先把数据库里村的16进制字串 转成2进制 ，然后AES_DECRYPT进行解密 SELECT AES_DECRYPT(UNHEX(YYY),'mypassword') AS YYY FROM XXX；
  • mysql联合索引最左匹配原则？——见mysql中整理的内容
  • mysql中in、exists、not in、not exists的区别？
    • in适合A表数据量大B表数据量小的情况；
    • exists适合A表数据量小B表数据量大的情况；
    • 当A表和B表数据量差不多大的时候，用In和exists效率差不多；
    • 至于not in 和 not exists，只要记住尽可能避免使用not in就是了， 因为not in 的A表和B表都会进行全表扫描