1.数据库三大范式、反模式

  1. 第一范式强调属性的原子性束缚，要求属性具备原子性，不可再合成
  2. 第二范式强调记录的唯一性束缚，表必须有一个主键，并且没有蕴含在主键中的列必须齐全依赖于主键，而不能只依赖于主键的一部分
  3. 第三范式强调属性冗余性的束缚，即非主键列必须间接依赖于主键

反模式：如果齐全依照三大范式来设计表构造，会导致业务波及表增多，查问数据须要多表联结查问，导致sql简单，性能变差，不利于保护，也不利于分库分表，比方会在表中冗余存储城市id对应的城市名称

2.Mysql 架构图

3.有一个组合索引（A,B,C），能够有哪几种查问形式

优: select * from test where a=10 and b>50

差: select * from test where b = 50

优: select * from test order by a

差: select * from test order by b

差: select * from test order by c

优: select * from test where a=10 order by a

优: select * from test where a=10 order by b

差: select * from test where a=10 order by c

优: select * from test where a>10 order by a

差: select * from test where a>10 order by b

差: select * from test where a>10 order by c

优: select * from test where a=10 and b=10 order by a

优: select * from test where a=10 and b=10 order by b

优: select * from test where a=10 and b=10 order by c

优: select * from test where a=10 and b=10 order by a

优: select * from test where a=10 and b>10 order by b

差: select * from test where a=10 and b>10 order by c

总结：索引最左准则 从最右边开始确定值。即a确定条件后再确定b, a b确定条件后再确定c.联合上述例子就可以看懂

  1. 最左匹配准则，直到遇到范畴查问(>, <, between, like)就进行，比方a = 1 and b = 2 and c >3 and d = 4 如果建设(a,b,c,d)程序的索引，d是用不到索引的，如果建设(a,b,d,c)的索引则都能够用到，abd的程序能够任意调整
  2. = 和 in能够乱序，比方a = 1 and b =2 and c = 3建设(a, b, c) 索引能够任意程序，mysql查问优化器会帮你优化

4.有如下表，找出每个城市的最新一条记录

id 城市 人口 信息 创立工夫
1 北京 100 info1 工夫戳
2 北京 100 info2 工夫戳
3 上海 100 info3 工夫戳
4 上海 100 info4 工夫戳

select name,MAX(time) from city GROUP BY name

5.InnoDB存储构造

逻辑存储单元分为表空间(TableSpace) -> 段(segment) -> 区(extent) -> 页(page)

Mysql 8.0 InnoDB架构图

  • 表空间：所有数据都存在表空间中，表空间分零碎表空间和独立表空间。

    零碎表空间

    在装置数据库的时候默认会初始化一个以ibdata1命名的零碎表空间，存储所有数据的信息以及回滚段信息，ibdata1默认的大小是10MB,在高并发状况下，会有性能影响，倡议初始大小调整为1GB。

    相干教程参考：https://blog.csdn.net/demonson/article/details/79863166

    独立表空间

    设置参数innodb_file_per_table = 1，目前MySQL默认都是独立表空间，每个表都有本人的表空间文件，存储对应表的B+树数据、索引和插入缓冲等信息，其余信息还是存储在共享表空间中

    撤销表空间

    蕴含撤销日志，初始化的时候会创立两个默认的撤销表空间

    通用表空间

    能够存储多个表的数据，相比独立表空间更节约元数据的内存开销

    长期表空间

    分会话长期表空间和全局长期表空间。

    • 会话长期表空间，在第一个申请中，会话长期表空间从长期表空间池调配给会话，最多两个长期表空间，一个用于用户创立的长期表，另一个用于优化器创立的外部长期表，当会话断开时，长期表空间将被开释进入长期表空间池中；
    • 全局长期表空间，用于存储用户创立的长期表的更改数据，用于回滚，在失常敞开或初始化停止时被删除，并在每次启动服务器时从新创立

  • 段

    表空间由段组成，一个表通常有数据段、回滚段、索引段等，每个段由N个区和32个零散的页组成

  • 区

    由间断的页组成，每个区大小固定1MB

  • 页

    一个区由64个间断页组成，页默认大小16KB

6.存储引擎的 InnoDB与MyISAM区别，优缺点，应用场景

ACID:

原子性（atomicity）、一致性（consistency）、隔离性（isolation）、持久性（durability）

存储引擎InnoDBMyISAM
存储文件.frm表定义文件 .ibd数据文件.frm表定义文件
.myd数据文件
.myi 索引文件
锁表锁，行锁表锁
事务ACID不反对
CRUD读写读多
count扫表专门存储的中央
索引构造B+TreeB+Tree

7.建设索引的准则

  1. 最左匹配准则，直到遇到范畴查问(>, <, between, like)就进行，比方a = 1 and b = 2 and c >3 and d = 4 如果建设(a,b,c,d)程序的索引，d是用不到索引的，如果建设(a,b,d,c)的索引则都能够用到，abd的程序能够任意调整
  2. = 和 in能够乱序，比方a = 1 and b =2 and c = 3建设(a, b, c) 索引能够任意程序，mysql查问优化器会帮你优化
  3. 尽量抉择区分度高的索引，区分度公式count(distinct col)/count(*) ，示意字段不反复的比例，比例越大咱们的扫描记录越少,比例个别是须要join的字段要求是0.1以上，即均匀1条扫描10条记录
    4. 索引不能参加计算，比方from_unixtime(create_time) = '2014-05-29' 就不能应用到索引，因为b+tree中存的都是数据表中的字段值，但进行检索时，须要把素有元素都利用到函数能力比拟，老本大，应该改成create_time = unix_timestamp('2014-05-29')
  4. 尽量扩大索引，不要新建索引，比方表中曾经有a索引，当初要加(a,b)索引，只须要批改原来的索引即可

8.索引生效状况总结

索引底层是B+树实现

  1. 恪守最左匹配准则，中间断索引，应用范畴查问
  2. 在索引列上做计算
  3. 索引字段应用 != 或者 < >
  4. 索引字段应用 is null 或者 is not null
  5. 应用通配符 %结尾
  6. 索引字段是字符串，查问条件没有应用字符串
  7. 索引字段应用or

https://blog.csdn.net/wuseyuk...

9.B+Tree 索引 和 哈希索引 限度

B+Tree索引：

分两类，汇集索引和 一般索引

汇集索引，在创立表的时候，会创立一个主键，这个主键就是汇集索引，在索引叶子节点中寄存了数据信息。InnoDB会给没有创立主键的表抉择第一个不蕴含null值的惟一索引作为主键，如果惟一索引也没有，就会为该表创立一个6字节的rowid作为主键

一般索引，索引叶子节点并不蕴含所有行的数据，只保留键值，通过键来查找行数据

  • 全值匹配，和索引中的所有列进行匹配
  • 匹配最左前缀
  • 匹配列前缀，能够只匹配某一列的值结尾局部
  • 匹配范畴值，如果匹配的列不是主键，只能应用第一个索引来匹配范畴，否则不走索引，如果匹配列是主键，能够不依照索引程序来，走的是主键索引
  • 准确匹配某一个列并范畴匹配另外一列

哈希索引：

  • 哈希索引只蕴含哈希值和行指针，而不存储字段值，所以不能应用索引中的值来防止读取行。不过，拜访内存中行的速度很快
  • 哈希索引数据并不是依照索引值顺序存储的，所以也无奈用于排序
  • 哈希索引不反对局部索引列匹配查找，因为哈希索引始终应用索引列的全部内容来计算哈希值
  • 只反对等值比拟查问，包含 =、 in()、<=>，不反对范畴查问
  • 数据访问速度快，当哈希抵触时，必须遍历链表中的所有行指针，直到查问到符合条件的行
  • 哈希抵触多的话，一些索引保护操作的代代价很高

10.事务隔离级别，设置事务办法

  1. read uncommitted(未提交读) ： 能够看到未提交的数据,脏读
  2. read committed (提交读)：只能读取已提交的数据，但屡次读取的数据后果可能不统一，导致幻读
  3. repeatable read(可反复读)：默认级别，能够反复读，解决了脏读问题，但会有幻读
  4. serializable(可串行化)：最高隔离级别，强制事务串行执行，防止幻读问题

查问以后会话级别：select @@tx_isolation;

查看零碎以后隔离级别：select @@global.tx_isolation;

设置以后会话隔离级别：set session transaction isolatin level repeatable read;

设置零碎以后隔离级别：set global transaction isolation level repeatable read;

11.什么是MVCC, MySQL的MVCC原理

MVCC即多版本并发管制，它能在很多状况下防止加锁操作，升高开销，不同的存储引擎实现形式不同，有乐观并发管制和乐观并发管制

MySQL的InnoDB引擎，通过在每行记录前面保留两个暗藏的列来实现，一个列保留了行的创立工夫，一个保留了行的过期工夫（或删除工夫）。理论存储的是零碎版本号，每开始一个新的事务，零碎版本号都会主动递增，事务开始时刻的零碎版本号会作为事务的版本号，用来和查问到的每行记录的版本号进行比拟。该MVCC只应用在repeatable read 和 read committed下

保留这两个额定的零碎版本号，使大多数读操作都不必加锁，并且也能保障只会读到符合标准的行。毛病是须要额定的存储空间和保护工作。

12.Mysql死锁

死锁是两个或者多个事务在同一资源上相互占用，并申请锁定对方资源，从而导致相互期待的景象。

死锁示例：

#事务1start transaction;update stockprice set close = 45 where stock_id = 4 and date = '2019-1-1';update stockprice set close = 20 where stock_id = 3 and date = '2019-1-3'#事务2start transaction;update stockprice set high = 36 where sockt_id = 3 and date = '2019-1-3';update stockprice set hight = 60 where stock_id = 4 and date = '2019-1-1';

两个事务别离执行两个更新语句，都执行第一个语句，锁定了该行数据，但该行数据将做为对方事务执行下条语句的条件，所以当事务继续执行第二条语句的时候，因为须要的条件所在行已被另外一个事务锁定，这是死锁景象

防止死锁的办法：

  • 约定以雷同的程序拜访表
  • 大事务分小事务
  • 一个事务中，一次锁定资源
  • 锁降级，采纳表锁

11.Msyql 执行SQL 过程

  1. 客户端发送一条查问给服务器
  2. 服务器先查看查问缓存，如果命中了缓存，则立即返回存储在缓存中的后果。否则进入下一阶段
  3. 服务器端进行SQL解析，预处理，再由优化器生成对应的执行打算
  4. MySQL依据优化器生成的执行打算，调用存储引擎的API来执行查问
  5. 将后果返回给客户端

12.如何优化sql翻页

  1. 只让用户一页页翻，不能跳页
  2. 确定每页的边界值，通过where条件查问来优化
  3. 应用提早关联，通过应用笼罩索引查问返回须要的主键，再依据这些主键关联原有表取得须要的行
select name,sex,rating from mysql_test a inner join (    select id from mysql_test where sex='F' order by rating limit 20000,100) as x USING(id)

13.如何优化SQL语句

  1. 先看表的数据类型是否设计的正当，恪守选取数据类型越简略越小的准则
  2. 表中的碎片是否整顿，MySQL表的碎片整顿和空间回收
  3. 表的统计信息是否收集，只有统计信息精确，执行打算才能够帮忙咱们优化SQL
  4. 查看执行打算，查看索引的应用状况，没有用到索引，创立索引
  5. 创立索引须要判断这个字段是否适宜创立索引，恪守建设索引的准则
  6. 创立索引后，通过explain剖析，前后性能变动

14.如何剖析explain执行打算

  • 先查看type列，如果呈现all关键词，就代表sql执行全表扫描
  • 再看key列，如果null代表没有应用索引
  • 再看rows列，如果越大，代表须要扫描的行数越多，相应耗时就长
  • 最初看 extra列，是否有影响性能的 Using filesort 或者 Using temporary

explain 各个字段含意：https://blog.csdn.net/weixin_...

15.slect from a left join b on 条件 和 select from a left join b where 条件一样么，为什么

不一样，返回的后果不一样。

select * from a left join b on 条件 会返回 a 中没有匹配的数据(left join 优先)

select * from a left join b where 条件 只返回where中匹配的数据(where优先)

https://www.cnblogs.com/caowe...

16.数据连接池的工作机制

J2EE 服务器启动时会建设肯定数量的池连贯，并始终维持不少于此数目的池连贯。客户端程序须要连贯时，池驱动程序会返回一个未应用的池连贯并将其表记为忙。如果以后没有闲暇连贯，池驱动程序就新建肯定数量的连贯，新建连贯的数量由配置参数决定。当应用的池连贯调用实现后，池驱动程序将此连贯表记为闲暇，其余调用就能够应用这个连贯。

17.你理解继承映射吗，请简略讲讲你的了解

继承关系的映射策略有三种：
① 每个继承构造一张表（table per class hierarchy），不论多少个子类都用一张表。
② 每个子类一张表（table per subclass），公共信息放一张表，特有信息放独自的表。
③ 每个具体类一张表（table per concrete class），有多少个子类就有多少张表。
第一种形式属于单表策略，其长处在于查问子类对象的时候无需表连贯，查问速度快，适宜多态查问；毛病是可能导致表很大。后两种形式属于多表策略，其长处在于数据存储紧凑，其毛病是须要进行连贯查问，不适宜多态查问。

18.数据库优化办法

（1）选取最实用的字段属性

MySQL能够很好的反对大数据量的存取，然而一般说来，数据库中的表越小，在它下面执行的查问也就会越快。因而，在创立表的时候，为了取得更好的性能，咱们能够将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库减少了不必要的空间，甚至应用VARCHAR这种类型也是多余的，因为CHAR(6)就能够很好的实现工作了。同样的，如果能够的话，咱们应该应用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的办法是在可能的状况下，应该尽量把字段设置为NOTNULL，这样在未来执行查问的时候，数据库不必去比拟NULL值。
对于某些文本字段，例如“省份”或者“性别”，咱们能够将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来解决，而数值型数据被解决起来的速度要比文本类型快得多。这样，咱们又能够进步数据库的性能。

（2）应用连贯（JOIN）来代替子查问(Sub-Queries)

MySQL从4.1开始反对SQL的子查问。这个技术能够应用SELECT语句来创立一个单列的查问后果，而后把这个后果作为过滤条件用在另一个查问中。例如，咱们要将客户根本信息表中没有任何订单的客户删除掉，就能够利用子查问先从销售信息表中将所有收回订单的客户ID取出来，而后将后果传递给主查问

（3）应用联结(UNION)来代替手动创立的长期表

MySQL从4.0的版本开始反对union查问，它能够把须要应用长期表的两条或更多的select查问合并到一个查问中。在客户端的查问会话完结的时候，长期表会被主动删除，从而保障数据库参差、高效。应用union来创立查问的时候，咱们只须要用UNION作为关键字把多个select语句连接起来就能够了，要留神的是所有select语句中的字段数目要想同。

（4）事务

只管咱们能够应用子查问（Sub-Queries）、连贯（JOIN）和联结（UNION）来创立各种各样的查问，但不是所有的数据库操作都能够只用一条或少数几条SQL语句就能够实现的。更多的时候是须要用到一系列的语句来实现某种工作。然而在这种状况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。构想一下，要把某个数据同时插入两个相关联的表中，可能会呈现这样的状况：第一个表中胜利更新后，数据库忽然出现意外情况，造成第二个表中的操作没有实现，这样，就会造成数据的不残缺，甚至会毁坏数据库中的数据。要防止这种状况，就应该应用事务，它的作用是：要么语句块中每条语句都操作胜利，要么都失败。换句话说，就是能够放弃数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字完结。在这之间的一条SQL操作失败，那么，ROLLBACK命令就能够把数据库复原到BEGIN开始之前的状态。

19.mysql的主从复制

MySQL主从复制是其最重要的性能之一。主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据主动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的根底是主服务器对数据库批改记录二进制日志，从服务器通过主服务器的二进制日志主动执行更新。

MySQL主从复制的两种状况：同步复制和异步复制，理论复制架构中大部分为异步复制。

复制的根本过程如下：

Slave下面的IO过程连贯上Master，并申请从指定日志文件的指定地位（或者从最开始的日志）之后的日志内容。

Master接管到来自Slave的IO过程的申请后，负责复制的IO过程会依据申请信息读取日志指定地位之后的日志信息，返回给Slave的IO过程。返回信息中除了日志所蕴含的信息之外，还包含本次返回的信息曾经到Master端的bin-log文件的名称以及bin-log的地位。

Slave的IO过程接管到信息后，将接管到的日志内容顺次增加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和地位记录到master-info文件中，以便在下一次读取的时候可能分明的通知Master“我须要从某个bin-log的哪个地位开始往后的日志内容，请发给我”。

Slave的Sql过程检测到relay-log中新减少了内容后，会马上解析relay-log的内容成为在Master端实在执行时候的那些可执行的内容，并在本身执行。

20.讲一讲，数据库ACID的个性

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都产生，要么都不产生。

一致性指事务前后数据的完整性必须保持一致。

隔离性指多个用户并发拜访数据库时，一个用户的事务不能被其余用户的事务所烦扰，多个并发事务之间数据要互相隔离。

持久性是指一个事务一旦提交，它对数据库中数据的扭转就是永久性的，即使数据库产生故障也不应该对其有任何影响。

21.数据库乐观锁和乐观锁

乐观锁

乐观锁（Pessimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为他人会批改，所以每次在拿数据的时候都会上锁，这样他人想拿这个数据就会block直到它拿到锁。乐观锁：假设会产生并发抵触，屏蔽所有可能违反数据完整性的操作。

Java synchronized 就属于乐观锁的一种实现，每次线程要批改数据时都先取得锁，保障同一时刻只有一个线程能操作数据，其余线程则会被block。

乐观锁

乐观锁（Optimistic Lock），顾名思义，就是很乐观，每次去拿数据的时候都认为他人不会批改，所以不会上锁，然而在提交更新的时候会判断一下在此期间他人有没有去更新这个数据。乐观锁实用于读多写少的利用场景，这样能够进步吞吐量。

乐观锁：假如不会产生并发抵触，只在提交操作时查看是否违反数据完整性。

乐观锁一般来说有以下2种形式：

应用数据版本（Version）记录机制实现，这是乐观锁最罕用的一种实现形式。何谓数据版本？即为数据减少一个版本标识，个别是通过为数据库表减少一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一起读出，数据每更新一次，对此version值加一。当咱们提交更新的时候，判断数据库表对应记录的以后版本信息与第一次取出来的version值进行比对，如果数据库表以后版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

应用工夫戳（timestamp）。乐观锁定的第二种实现形式和第一种差不多，同样是在须要乐观锁管制的table中减少一个字段，名称无所谓，字段类型应用工夫戳（timestamp）, 和下面的version相似，也是在更新提交的时候查看以后数据库中数据的工夫戳和本人更新前取到的工夫戳进行比照，如果统一则OK，否则就是版本抵触。

22.数据库的隔离级别

隔离级别脏读（Dirty Read）不可反复读（NonRepeatable Read）幻读（Phantom Read）
未提交读（Read uncommitted）可能可能可能
已提交读（Read committed）不可能可能可能
可反复读（Repeatable read）不可能不可能可能
可串行化（Serializable ）不可能不可能不可能

未提交读(Read Uncommitted)：容许脏读，也就是可能读取到其余会话中未提交事务批改的数据。

提交读(Read Committed)：只能读取到曾经提交的数据。Oracle等少数数据库默认都是该级别 (不反复读)。

可反复读(Repeated Read)：可反复读。在同一个事务内的查问都是事务开始时刻统一的，InnoDB默认级别。在SQL规范中，该隔离级别打消了不可反复读，然而还存在幻象读。

串行读(Serializable)：齐全串行化的读，每次读都须要取得表级共享锁，读写互相都会阻塞。

23.数据库程度切分与垂直切分

垂直拆分就是要把表按模块划分到不同数据库表中（当然准则还是不毁坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和保护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来撑持，这时就有按模块和性能把表划分进去的需要。其实，绝对于垂直切分更进一步的是服务化革新，说得简略就是要把原来强耦合的零碎拆分成多个弱耦合的服务，通过服务间的调用来满足业务需要看，因而表拆出来后要通过服务的模式裸露进来，而不是间接调用不同模块的表，淘宝在架构一直演变过程，最重要的一环就是服务化革新，把用户、交易、店铺、宝贝这些外围的概念抽取成独立的服务，也十分有利于进行部分的优化和治理，保障外围模块的稳定性。

垂直拆分：单表大数据量仍然存在性能瓶颈

程度拆分，下面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而程度切分就是要把一个表依照某种规定把数据划分到不同表或数据库里。例如像计费零碎，通过按工夫来划分表就比拟适合，因为零碎都是解决某一时间段的数据。而像SaaS利用，通过按用户维度来划分数据比拟适合，因为用户与用户之间的隔离的，个别不存在解决多个用户数据的状况，简略的按user_id范畴来程度切分。

艰深了解：程度拆分行，行数据拆分到不同表中， 垂直拆分列，表数据拆分到不同表中。

24.JDBC操作数据库时，如何晋升读取数据的性能，以及更新数据的性能

要晋升读取数据的性能，能够指定通过后果集（ResultSet）对象的setFetchSize()办法指定每次抓取的记录数（典型的空间换工夫策略）；要晋升更新数据的性能能够应用PreparedStatement语句构建批处理，将若干SQL语句置于一个批处理中执行。

25.Statement 和 PreparedStatement 的区别

与Statement相比，①PreparedStatement接口代表预编译的语句，它次要的劣势在于能够缩小SQL的编译谬误并减少SQL的安全性（缩小SQL注射攻打的可能性）；②PreparedStatement中的SQL语句是能够带参数的，防止了用字符串连贯拼接SQL语句的麻烦和不平安；③当批量解决SQL或频繁执行雷同的查问时，PreparedStatement有显著的性能上的劣势，因为数据库能够将编译优化后的SQL语句缓存起来，下次执行雷同构造的语句时就会很快（不必再次编译和生成执行打算）。

为了提供对存储过程的调用，JDBC API中还提供了CallableStatement接口。存储过程（Stored Procedure）是数据库中一组为了实现特定性能的SQL语句的汇合，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。尽管调用存储过程会在网络开销、安全性、性能上取得很多益处，然而存在如果底层数据库产生迁徙时就会有很多麻烦，因为每种数据库的存储过程在书写上存在不少的差异。

26.JDBC的反射

通过反射com.mysql.jdbc.Driver类，实例化该类的时候会执行该类外部的动态代码块，该代码块会在Java实现的DriverManager类中注册本人,DriverManager治理所有曾经注册的驱动类，当调用DriverManager.geConnection办法时会遍历这些驱动类，并尝试去连贯数据库，只有有一个能连贯胜利，就返回Connection对象，否则则报异样。