mysql数据库

1、 数据库的三大范式

第一范式 : 表中的列不可再分 。

• 例如：学生（姓名，年龄，身高，体重等等）

第二范式 : 在第一范式的基础上，非主键列完全依赖于主键，而不能依赖于主键的一部分。

• 例如：学生和课程为联合主键，那么需拆分成学生表和课程表

第三范式 : 在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键（没有传递依赖）

• 例子：河南省、周口市，那么周口市依赖于河南省，不满足第三范式

2、MySQL的binlog有几种录入格式？分别有什么区别？

三种格式，statement，row和mixed ：

• statement模式下，每一条修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来。但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。
  此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

3、存储引擎

常用的存储引擎有以下：

• Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。

• MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
  

• Innodb和MyIASM两种引擎所使用的索引的数据结构都是B+树

  • MyIASM
    MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。

• Innodb
  Innodb引擎的索引的数据结构也是B+树，只不过数据结构中存储的都是实际的数据，这种索引有被称为聚集索引。
  
• 数据结构比较
  • 平衡二叉树
  • 红黑树
    1、只有红色与黑色节点
    2、root节点为黑色节点
    3、从root节点到叶子节点（null），黑色的节点个数相同
    4、每个红色节点的两个孩子节点必须为黑色
    5、所有的叶子节点都没黑色
  • B树 是一种平衡的多路查找树。
    1、节点包含数据及索引。
    2、不是有序的。
  • B+树
    1、节点只保存数据的索引，单一节点存储更多的元素，使得查询的IO次数更少。
    2、叶子节点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。所有叶子节点形成有序链表，便于范围查询。
    3、数据对象的插入和删除仅在叶节点上进行。
    4、有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。
    5、所有查询都要查找到叶子节点，查询性能稳定。
  • 哈希索引
    类似于数据结构中HASH表一样，当我们在mysql中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞，则在对应Hash键下以链表形式存储。

4、索引

索引是一种快速查询数据库的数据结构。

4.1、索引有哪些优缺点？

• 优点
  可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
• 缺点
  时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
  空间方面：索引需要占物理空间。

4.2、索引使用场景及explain的使用？

• where

explain select * from table where id < 100

explain查询出的字段包括以下：

1、id
用来表示select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

1、相同则自上而下
2、不同则id越大优先级越高

2、select_type
用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

1、 SIMPLE 简单的select查询，查询中不包含子查询或者UNION
2、 PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
3、 SUBQUERY 在SELECT或WHERE列表中包含了子查询
4、 DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
5、 UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
6、 UNION RESULT 从UNION表获取结果的SELECT

3、table
指的就是当前执行的表。

4、type
指的是查询使用了哪种类型，type包含的类型包括以下几种，：

system > const > eq_ref > ref > range > index > all

一般来说，得保证查询至少达到range级别，最好能达到ref。

• system 表只有一行记录，这是const类型的特列，平时不会出现，这个也可以忽略不计。
• const 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
• eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
• ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
• range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
• index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
• all Full Table Scan 将遍历全表以找到匹配的行。

5、possible_keys 和 key
possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key 实际使用的索引

如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）。
查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中。

6、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

7、ref
显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

8、rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好。

9、Extra
包含不适合在其他列中显示，但十分重要的额外信息。

• order by
  当使用order by将查询结果按照某个字段排序时：

  • 如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。
  • 但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

• join on
  对join语句匹配关系 （on）涉及的字段建立索引 能够提高效率

• 索引覆盖
  如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

4.3、索引类型？

逻辑索引

• 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
• 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
  可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
• 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
  可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
  可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
• 全文索引： 是目前搜索引擎使用的一种关键技术。
  可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

主键是一种约束，而唯一索引是索引，本质不同。
主键只能有一个，而唯一索引可以有多个。
主键不允许有空值，而唯一索引可以，主键一定包含唯一索引，而唯一索引并不一定是主键。
主键可以被其他表引用，而唯一索引不可以。

物理角度索引

• 聚簇索引
  聚簇索引的叶节点是数据节点。

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

画外音：所以PK查询非常快，直接定位行记录。

• 非聚簇索引
  非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

每个表只有一个聚簇索引，因为每个表只能按一种顺序排序。

4.4、索引的数据结构（B+树，hash）

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等。当绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择B+Tree索引。

• hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
• hash索引不支持使用索引进行排序。
• hash索引不支持模糊查询以及多列索引的最左前缀匹配。
• hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
• hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

索引结构（二叉树，红黑树，B树，B+树、Hash表）

二叉树  Ologn

红黑树  Ologn

B树       每个节点存放数据加索引，每个节点存放的索引就少

B+树     3 ~ 5 层 

             除了叶子节点，都只存放索引，存放更多数据，查询IO更少

             所有的查询都到叶子节点，性能更加稳定

             所有的叶子节点形成链表，方便范围查询 

Hash     无法范围查找、不支持索引排序、不支持模糊查找、必须回表

4.5、创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则：

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

4.6、使用索引查询一定能提高查询的性能吗？

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

• 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
• 基于非唯一性索引的检索

最左匹配原则 （最左优先 A，B，C建立索引）

先按A有序，再按B有序，再按C有序

1、如果三者ABC同时存在，6种，顺序无所谓

2、A、AB走索引

3、B、C没有

4、AC，只用到A索引、BC没有

5、字符型 As% 走索引 %AS不走

6、范围查询多个只对最左边的走索引

7、排序order by 文件排序很慢，但Order by ABC 直接走索引

索引不起作用：

1、前面出现范围查找 > < between like

2、like %AS

3、联合缺左边

4、使用or每一列必须都有索引

5、需要类型转换

6、参与数学运算或函数

索引不推荐

1、唯一性差

2、频繁更新字段

3、where使用<>

4、字段不在where中

4.7、百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
然后删除其中无用数据（此过程需要不到两分钟）
删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

5、事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。

5.1、四大特性

• Atomicity（原子性）
  事务是一个不可分割的工作单元，事务中的操作要么都发生，要么都不发生。
• Consistency(一致性)
  事务前后数据的完整性必须保持一致。
• Isolation（隔离性）
  事务隔离性是对于多个用户同时访问数据库的时候，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
• Durability(持久性)
  持久性指一个事务一旦被提交，数据库中的数据改变就是永久性的，接下来数据库发生故障也不应该对其有任何影响。

A ：通过undolog实现 insert、update、delete写入日志 回滚即执行回滚日志，做逆向操作

C ：由AID保证，判断数据库是否有效，commit或rollback

I ：读写锁+MVCC （管理多个并发读写请求的访问顺序）

D ：通过 redolog实现

持久化 表数据在磁盘上 -----磁盘IO ---- 修改一个页面的一个字节要讲整个页面刷入磁盘-----消耗性能 -----Innode提供缓冲池---------将磁盘数据页映射到缓存来使用

读： -----> 缓冲池 ------>N ---->磁盘读取

写： ------>缓冲池 --------> 定期同步到磁盘

MySQl宕机，数据丢失 -------->redolog出场 ------->处理数据已提交，但是还在缓存池，没来的及持久化的数据，为恢复数据使用。

1、redolog 按顺序存储，缓存同步随机操作。

2、缓存同步数据一数据页为单位、每次传输数据大小大于redolog。

MVCC +间隙锁或next - key锁（并发控制的机制，维持一个数据的多个版本，使读写没有冲突，不加锁）（

undolog （回滚）

readview（快照，将每个时刻的状态拍成照片，形成版本链，通过某个时刻，获取数据）

1、利用MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题

2、利用Gap Locks和Next-Key可以阻止其它事务在锁定区间内插入数据，因此解决了幻读问题

综上所述，默认隔离级别的实现依赖于MVCC和锁，再具体一点是一致性读和锁。

5.2、 事务并发出现的问题

• 脏读
  一个事务读到另一个事务还未提交的数据。
• 不可重复读
  一个事务更新了数据，导致其他事务多次读表中的某行数据，多次读取的结果不同。
• 幻读
  一个事务读取到其他事务插入的数据，导致前后读取不一致。
• 脏写
  指事务回滚了其他事务对数据项的已提交修改。
• 丢失更新
  指事务覆盖了其他事务对数据的已提交修改,导致这些修改好像丢失了一样。

5.3、 四种隔离级别设置

• Serializable（串行化）
  可避免脏读、不可重复读、幻读情况的发生。
• Repeatable read（可重复读） （默认）
  可避免脏读、不可重复读情况的发生。
• Read committed（读已提交）
  可避免脏读情况发生。
• Read uncommitted（读未提交）
  最低级别，以上情况均无法保证。
  

6、锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

6.1、行级锁、表级锁、页级锁

• 行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
  特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

行锁（通过索引加载，行锁加载索引响应的行上，不走索引，则全表扫描、两个事务不同锁同一个索引，insert/delete/update默认加排它锁）

• 表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
  特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

• 页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
  特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

6.2、共享锁、排他锁

• 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

• 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥

6.3、MySQL中InnoDB引擎的行锁是怎么实现的？

答：InnoDB是基于索引来完成行锁
例: select * from tab_with_index where id = 1 for update;
for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起

6.4、什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法：

• 1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
• 2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
• 3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

6.5、数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

6.6、两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

7、问题

1、mysql的索引是怎么实现的？

(先根据普通索引树找到主键，再根据主键索引找到对应的记录)

2、聚集索引（行记录），普通索引（主键值）

3、mysql主从复制原理以及步骤

4、为什么设定主键？

后续增删改查更加快速，确保数据范围安全。

5、推荐自增ID，不是UUID

主键索引是聚簇索引

6、为什么定义not null

null会占用更多的字节，会造成很多与预期不符的情况

7、char 而不是 varchar

1、char固定长度，而varchar长度是可变的

2、char如果插入的长度小于定义长度时，则用空格填充，varchar小于定义长度时，还是按实际长度存储，插入多长就存多长

3、char的存取速度还是要比varchar要快得多

4、char最多能存放的字符个数 255，和编码无关。varchar 最多能存放 65532 个字符。

8、binlog录入

1、statement  记录单元为语句

2、row 记录每一行

3、mixed 普通使用1，不能用1使用2

9、横向、纵向分表

横向：将数据存入100个表中

纵向：按列分表

10、依赖注入

# 是经过预编译的，防止SQL注入。

$ 为经过编译，存在SQL注入，1 or 1=1

11、数据库优化

1、选取适用的子段属性

2、适用join代替子查询

3、适用索引

4、优化查询语句

12、回表
回表是索引不包含全部查询数据时，需要先通过普通索引找到对应的id,然后再通过聚簇索引进行数据查询。

13、覆盖索引
索引包含所有要查询的字段。explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

---

EXPLAIN用法和结果分析
MySQL数据库面试题（2020最新版）