MySQL

思维导图: https://mm.edrawsoft.cn/map.html?obj=qqUID_0863921F70C88FAA26B5D4D808912139/Personal/MySQL.emmx.

1、DQL语句

Delete、Truncate、Drop的区别

• Drop删除数据库和数据表，Delete可以删除表中的记录，Truncate可以删除数据表。
• 不同点：
  • delete删除记录后自增计数器不会归零，truncate清空后会使得计数器归零
  • truncate删除整个表，delete可以删除表中的一个数据（添加条件）
  • delete属于DML(数据操作语言)，操作时原数据会被放到rollback segment中，可以被回滚。而truncate是DDL(数据定义语言)，操作时不会进行存储，不会被回滚。

比较运算符

模糊查询

联表查询

• Inner Join(可省略为join)

  SELECT Students.ID,Students.Name,Majors.Name AS MajorName
  FROM Students INNER JOIN Majors
  ON Students.MajorID = Majors.ID   # inner join相当于只会查询交集 
  //如果将Majors的ID 12改为10，结果显示为图右  因为Tom一个人修了两门课，会出现重复信息
  

  

• Left Join

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students LEFT JOIN Majors
ON Students.MajorID = Majors.ID   # Left Join会显示左表中的所有信息，但是未匹配的列会显示null

• Right Join

SELECT Students.ID,Students.Name,Majors.Name AS MajorName
FROM Students RIGHT JOIN Majors
ON Students.MajorID = Majors.ID   # Right Join会显示右表中的所有信息，但是未匹配的列会显示null

联表查询的使用思路

为什么不建议使用连表查询

1、效率问题。表数据量较低时，可能连表查询的效率较低，但是当业务场景下表的数据量达到百万级别后，join性能很低
2、缓存利用率低。将连表查询拆分为多个单表查询的语句，可以提高缓存的利用率，提升效率。
3、不利于维护。单表查询的修改比较容易，join写的SQL语句，一旦表的结构发生变化，很可能整个join查询都不可用。
4、不利于代码复用。单表查询的复用率更高，连表查询基本上不可能被复用。
5、分库分表下的连表查询效率更差。
将连表查询分解为 单表查询+代码组装 相当于解耦。

Join查询的详细分析

  join查询还可以写成left join，表示的是根据左边的表来查询右边的表。但实际上，'优化器会进行优化'，选择合适的表来做驱动表，不一定是左边的表。NLJ算法，这是一种基于索引的算法，如果你的连表用到了索引，则会使用这个算法。 
  例如：select * from table_a left join table_b on a.id = b.uid;
  b.uid具有索引段，table_a是'小表'，优化器会选择table_a表作为驱动表，table_b作为被驱动表。
# 执行流程如下：
	1、读取a表第一行的数据，使用table_a中的id去table_b的索引树上查询对应的table_b的id
	2、通过查询的table_b的id回表查询table_b数据
	3、将table_a和table_b的数据放在一起
	4、然后重复以上步骤，直到table_a'遍历'完成
时间复杂度：table_a执行的是全表扫描，table_b根据索引执行查找对应id，再回表查询具体数据（如果id为聚集索引则不需要回表查）。因为存在索引查找效率为logm，拿到id后，再查数据，所以table_b的查找时间为2*logm。假设table_a有n行记录，那么需要n*2*logm。
# 优点：
	1、省略了多次连接数据库，连接数据库是比较耗费资源和时间的。
	2、可以实现分页查询
 缺点：当table_a和table_b的数据量都很大时，那么一次查询的时间为nlogm，时间复杂度还是很高的。

# 假设table_b无索引，则会凸显出连表查询的劣势。下面阐述table_b无索引的查询过程：
	'无MySQL优化'：先查询table_a，在对应在table_b全表扫描查找对应数据。重复直到查找完成。时间复杂度为N^M级，效率非常低。
	'默认优化'：假设无索引，则MySQL会采用BNL算法。读取table_a所有数据放入到join buffer，再对table_b进行全表扫描，每条数据与join buffer中的数据进行一次比对。在缓冲区中进行n*m次的对比数据，但是在内存中判断，比直接增加磁盘读取要好的多。
	但是join buffer存在一定限制，join_buffer_size默认为256k。如果table_a的数据量大小超过了join buffer，则需要进行分段进行。每次读取一部分table_a全表扫描一次table_b，这样会造成多次的全表扫描table_b。
# 总结：（如果一定需要使用join）
	1、使用小表(table_a)作为驱动表会更好
	2、能加索引就在被驱动表(table_b)上加索引
	3、在没有索引，且驱动量更大的情况下，可以调大join_buffer_size的值来加速连表查询

自连接

现在想要将其显示为子类父类的形式

解题思路：

总体查询语句的结构

排序和分页查询

需要有偏移量offset：

select * from comment
where entity_id = #{entityId}
and entity_type = #{entityType}
and status = 0
order by create_time asc
limit #{offset},#{limit}

聚合函数

• count(*)：计算所有的函数，包括null值
• count(1)：和count(*)的返回值是一样的。
• count(列)：返回该字段不包括null的数量。

2、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构**。

提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的优缺点

• 优点：
  • 索引可以大大加快数据检索的速度，这也是创建索引的目的。
  • 唯一性索引可以保证字段数据的唯一性。
• 缺点：
  • 创建和维护索引需要耗费时间。对表中数据的更改，如果数据存在索引，则索引也需要对应的调整，会降低SQL效率；
  • 索引需要使用物理文件存储，占用空间；
  • 大多数的索引查询比全表查询速度要快，但是如果数据量不大，使用索引也并不会带来很大的提升。

索引的分类

分为两大类，聚集索引和普通索引。

• 主键和索引的区别(Primary Key)：
  • 唯一的标识，主键不可重复且不能为null，只能有一个列作为主键
• 普通索引
  • 唯一索引(UNIQUE KEY)
    • 避免重复的列出现，索引值必须唯一，但可以为null(只有一个值可以为null)，多个列都可以标识为唯一索引
    • 唯一性约束用来限制不受主键约束的列上的数据的唯一性，用于作为访问某行的可选手段
  • 普通索引(INDEX)：主要为了加快查询数据，一张表可以设置多个普通索引，允许数据重复和null
  • 前缀索引：只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，相比于普通索引建立的数据更小，因为只取前几个字符
  • 组合（联合）索引：指在多个字段上创建的索引。
  • 全文索引(FullTEXT)：快速定位数据，基于内容的检索。主要是数据引擎数据库使用的一种技术。

集聚索引和非聚集索引

• 聚集索引：索引结构和数据一起存放的索引。因此数据的物理存储就是有序的，和索引树结构相同。每个表只能有一个聚集索引。聚集索引并不一定是主键索引，如果定义了主键就是主键索引，如果没有定义，则可以使用其他列实现聚集索引。聚集索引适合建立在范围查询的列上。

• InnoDB必须存在聚集索引，但不一定存在主键。

  • 如果有主键，主键则为聚集索引。
  • 如果没有定义主键，第一个非空且唯一列是聚集索引。
  • 如果没有复合的列，会自动创建一个隐藏的row-id作为聚集索引
  • 默认建主键的同时建立聚集索引。

• 优缺点

  • 优点：
    • 查询速度很快，叶子节点就存放了数据。
    • 由于辅助索引中存放的是聚集索引中的key而不是具体的地址值，那么如果更新索引，数据发生移动时，不需要更新辅助树的地址信息。
  • 缺点：
    • 依赖于有序的数据；如果是无序的数据，插入很消耗时间。因为聚集索引的存储时在磁盘中有序的。
    • 更新的代价很大；如果对索引列数据进行修改，那么索引需要修改。且聚集索引的叶子节点还存放了数据，代价更大。因此一般主键是不可修改的。因为数据库数据的存储是按照聚集索引的顺序顺序存放的，如果要插入一个新的值，那么可能在磁盘中会强行将数据插入到存储满的页中，会面临需要分页的场景。这会造成极大的时间开销
    • 插入数据的速度较慢，因为存储是物理上有序的，因此需要先找到对应位置再插入。而非聚集索引可以插入到任意位置，只需要将对应地址信息放到索引树中。

• 非聚集索引：索引结构与数据分开放的索引，B+树中只存储辅助字段和主键/数据地址；

  • 优点：更新代价小于聚集索引
  • 缺点：可能会二次查询(回表)，属于非聚集索引的最大缺点。当查找到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件中查询。
    • 为什么是可能？如何尽量避免回表以优化SQL查询？（覆盖索引只针对InnoDB的查询，因为MyISAM的叶子节点存储的是指针，必须通过地址查询数据）
    • 那么不要用select *, 直接使用select (对应列)。如果需要检索到的列仅包含二级索引的列，那么辅助索引中就包括了需要的数据，不再需要回表到主键进行查找！这也称之为覆盖索引。本例中，name的索引可以查到直接找到id和name，此时就不需要回表到聚集索引中查询数据。使用explain可以看到Extra : index
    • 如何提高覆盖索引的概率？
      • 将单列索引升级为联合索引，如select id,name,sex from user where name = 'shenjian，在单列索引name上查询是需要回表查询的。如果将单列索引(name)升级为联合索引(name, sex)就不同了。

• InnoDB和MyISAM的索引类型

  • 总体上来说，InnoDB主键索引是聚集索引，辅助索引是非聚集索引。而MyISAM主键索引和辅助索引都是非聚集索引。
  • 但是InnoDB的聚集索引是将索引结构和数据全部置于叶子节点中，可以直接查询到。他的辅助索引B+树中存储辅助键信息和对应主键。查询到条件对应的主键信息，还需h要到主键索引中查询对应主键才能找到数据信息。
  • MyISAM的主键索引和辅助索引都是非聚集索引。他们叶子节点中存储的是一个指向真正数据的地址。辅助索引不需要再去查询主键索引。他们的索引树是独立的。
  

索引的数据结构

Hash索引

Hash索引指的是Hash表，最大的优点在于能够很短时间内定位到数据，时间复杂度接近O(1)。但是MySQL没有使用Hash表作为索引的数据结构是因为：

• Hash冲突。Hash索引是将索引键通过运算后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中。由于存在Hash冲突，导致还需要找到查询到相同Hash值的具体数据信息进行额外比较才能返回结果。如果hash冲突严重，效率并不一定比聚集索引高。
• Hash索引不支持顺序和范围查询（最大缺点）
• 组合索引不能利用部分索引键查询，必须使用所有组合字段。因为hash查询时将组合字段合并后计算出hash值进行映射。并不是单独计算hash值。

B+Tree索引

BTree：InnoDB的默认数据结构

B+树的特点：
	1、B+Tree中非叶子节点不存储Data，只存储key，以及指向子节点的指针；叶子节点不存储指针，只存储Data；B-树非叶节点也存Data。因此树高相对于其他数据结构更小，查询速度也更快。
	2、对叶子节点加了顺序访问指针，底层相当于形成了已排序的链表，极大提高了'区间查询效率'；例如图中如果要查询key为从15到60的所有数据记录，当找到15后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
	3、查询速度很稳定

从磁盘读取原理分析B+树作为数据库索引的优越性

  红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。由于索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在'磁盘上'。那么索引查找过程就要产生磁盘I/O消耗。（一次I/O指的就是，旋转磁头找到对应磁道，然后旋转找到对应扇区读取数据的过程），所以评价一个数据结构作为索引的优劣最重要的指标就是 # 在查找过程中磁盘I/O操作次数。
  数据库的设计者巧妙地将一个节点的大小设为一个页(主存和磁盘交换顺序的基本单位，默认16KB)，这样每次'节点只需要一次I/O就可以完全载入'。B-树进行一次检索最多需要'h-1次'(根节点常驻内存)，h是树的高度。相比之下，红黑树作为二叉树的一种，h就相对高得多，进行一次查询需要的I/O开销可能很大。B+Tree更适合'外存索引'，原因和内节点出度d(多叉数的子节点数)有关。
  dmax=floor(pagesize/(keysize+datasize+pointsize))  由于B+Tree的data不存储，因为出度d相比于B-Tree要大的多，因此拥有更好的性能，树高越小，查询速度越快。
  B+树的非叶节点不存储data，因此一个节点能够装更多的key，出度d也就越大，那么树高h就越小，查找效率也就越高！
  # 一个节点包括了对应的key和point，一层只需要读取一个节点，也就是进行一次I/O，所以B+树更优

磁盘读取数据的过程：先寻道，找到对应的磁道后，旋转到对应的扇区。

最左前缀匹配原则

特性

• 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
• = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。
• 如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。

对id列.name列.age列建立了一个联合索引 id_name_age_index，实际上相当于建立了三个索引（id）（id_name）（id_name_age）。

上图所示，无论where后的查询条件是(id-age-name)还是(name-id-age)，在查询时使用的都是联合索引。因为MySQL中的查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划。

匹配列前缀

MySQL索引的实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

可以看出MyISAM的索引文件仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做==“非聚集”==的，之所以这么称呼是为了与InnoDB的聚集索引区分。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

• InnoDB的数据文件本身就是索引文件！而MyISAM的data域存放的是指向数据的地址

    InnoDB中表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
    可以看到叶节点包含了完整的数据记录。这种索引叫做'聚集索引'。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为'6B'，类型为'长整形'。
    key和key之间是单向指针，页和页之间是双向的
  

  

• InnoDB的辅助索引data域存储相应记录主键的值而不是地址。即：InnoDB的所有辅助索引都引用主键作为data域。

    这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得'按主键的搜索十分高效'，但是'辅助索引'搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
    聚集索引不利于辅助索引的查询。
  

  

总结

知道了InnoDB的索引实现后，就很容易明白:
   1、为什么不建议使用过长的字段作为主键？
   	因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
   2、为什么用非单调的字段作为主键在InnoDB中不是个好主意？
   MySQL不得不为了将新记录插到合适位置而移动数据。频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

两个存储引擎在数据结构上实现的区别：

• 1、InnoDB主索引实现的是聚集索引，而MyISAM实现的是非聚集索引。InnoDB的Data本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
• 2、InnoDB的辅助索引data域记录的不是地址，而是主键key。而MyISAM的辅助索引data域存的是指向数据的地址。

索引优化

MySQL针对主键索引会维护一个B+树的结构，这个我们称之为聚簇索引，针对非主键（一般都是建立的联合索引）会对索引字段依次排序，然后从第一个字段值开始比较，第一个字段值相同就针对下一个字段值进行比较，依次往后推。如果联合索引中的字段值都是一样的，那么就根据主键来排序。另外聚簇索引（主键索引）的B+树中保存的是一行记录的所有信息，非聚簇索引（非主键索引）仅仅保存索引字段值和主键字段值。

• 索引不是越多越好

  索引虽然加快了查询速度，但是索引也是需要代价的。索引本身需要存储空间，同时索引的存在会加重插入、删除和修改记录的负担。另外在运行时，也需要消耗资源维护索引。
  

• 不要对更新很快的数据加索引，索引一般加在常用来查询的字段上

• 小数据量的表不需要索引(2000以上考虑一下)

• 避免为大字段建立索引；因为MySQL维护索引时会将字段值一起维护，会造成索引占用更多空间。排序时也需要花更多的时间进行对比

• 选择区分度大的列作为索引；区分度不高的字段不适合做索引，假如以性别列为索引，每次搜索都会得到一半的数据。MySQL优化器查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。分界线一般为30%。这也是索引失效的场景之一！

  • 如果单次查询数据量过大，优化器将不走索引！
  • Cardinality（索引基数）：指数据列所包含的不同值的数量。例如，某个数据列包含值1、2、3、4、5、1，那么它的基数就是5。索引选择性 = 索引基数/数据总数；一般索引选择性越高，作为索引的效果越好！

• 不要在条件中使用函数；如下图所示，那么age的索引就无法使用，因为MySQL为该索引维护的B+树是基于原始数据的，如果加了函数就不会被认为是原有字段。

• **尽可能的考虑建立联合索引而不是单列索引。**每个索引对应一颗B+Tree，索引过多会造成占用空间过大，且修改索引耗费的时间也很长。

• 应该尽量扩展已有的索引而不是创建新索引。

• 考虑在字符串类型的字段上使用前缀索引代替普通索引。

索引失效的场景

• 使用OR关键字可能会导致索引失效。除非or条件中的每个列加上索引。
• 联合索引如果不遵循最左前缀原则，那么索引也将失效
• 使用模糊查询以%开头也会导致索引失效。
• 隐式转换造成索引失效；当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。
  • 例如：where num = 1000；假设num为var型的，那么发生隐式转化，都转为float型，右边转为float=1000，左边也是1000，但是由于左边属于检索条件且为字符串，key可以有多个转为float=1000的值（例如“01000”，“001000”），因此索引失效！
• 如果索引的区分度不高，导致查询出来的数据量过多（30%），就会直接全表查询。
• 在条件中使用函数

InnoDB的主键选择与插入优化

选择自增主键

   InnoDB使用聚集索引，数据记录本身被存在主索引的叶子节点中。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。 如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

3、数据库的设计与规范

数据库备份(Dump)

数据库的设计

软件开发中关于数据库的设计：

• 需求分析：分析业务和需要处理的数据库需求
• 概要设计：设计关系图E-R图

设计数据库的步骤(个人博客举例)：

• 收集信息，分析需求
  • 用户表
  • 分类表
  • 文章表 (可以和分类表整合在一起)
  • 友情链接表
• 标识实体（把需求具体到每个字段）
• 标识实体之间的关系
  • 写博客 —— 用户表

数据库的三大范式

为什么需要数据规范化？

• 信息重复
• 更新异常
• 插入异常
  • 无法正常显示信息
• 删除异常
  • 丢失有效信息

第一范式(1NF)

• 原子性：保证每一列都不可再分；

​ 如图：第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储。

第二范式(2NF)

• 满足第一范式的前提下，确保表中的每列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
  • 如图：要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。

需要将订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中。从而减小了数据库的冗余

第三范式(3NF)

• 满足第一范式和第二范式的前提下，第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

  • 如图：在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。
  

数据库查询优化（重点）

• 避免索引失效
  • 不要使用%开头的模糊查询
  • 尽量少用OR，OR除非每个条件都存在索引，否则会全表扫描。
  • 不要对区分度小的字段建立索引
  • 对于联合索引的使用记得满足最左匹配原则
  • 禁止对条件子句使用函数和计算表达式
  • 避免数据类型的隐式转换
• 效率问题
  • 避免使用JOIN和IN(子查询)
    • JOIN在数据量大的情况下效率很低。IN使用时会创建临时表，查询结束后销毁。多出了创建和销毁临时表的过程，效率受到影响。
  • 对于order by的字段最好建立索引。可以避免对结果集的排序，索引默认为B+树结构，是有序的。
  • 对于明显不存在重复值的情况下使用union all而不是union，可以节省去重的过程。
  • 将复杂的SQL拆分为多个小SQL。
  • 对于SQL的查询，可以根据需求，分批查询，不要一次查询过多数据。
  • select指明字段，替代使用select *

JDBC：Java连接数据库的驱动程序

SQL注入

SQL注入：SQL语句存在漏洞，会被攻击。针对程序员编写时的疏忽，通过SQL语句，实现无账号登录，甚至篡改数据库。

主要由于SQL会被拼接

正常的SQL语句判断：select * from user where username = ? and password = ?
但是：可以在username处填上' 1 or 1=1 -- '  or 1=1 会直接确保判断为true '--'表示将后面的语句注释掉 
使得最后结果SQL拼接的语句为：select * from user where username = 1 or 1=1 -- and password = 
# 导致数据库的所有用户均被查出，数据库信息泄漏！
甚至如果在username处填上' 1 or 1=1; Drop DataBase()  -- '  会造成整个数据库被删除

如何防御SQL注入？

• 遵循外部数据不可信任的原则

  • 1、检查变量数据类型和格式

    • 对存在固定格式的变量，在SQL语句执行前，严格按照固定格式检查。因此在注册前username和密码等都需要满足固定格式

  • 2、过滤特殊符号

    • 对于某些无固定格式的变量，例如帖子内容，评论等。无法通过检查格式防御SQL注入，因此可以通过特殊符号过滤或转义处理，现将内容过滤后再对其进行操作。HTMLUtils.escape()

  • 3、绑定变量，使用预编译语句；

    • 绑定变量使用预编译语句是预防SQL注入的最佳方式，使用预编译的SQL语句语义不会发生改变，在SQL语句中，变量用问号?表示，黑客即使本事再大，也无法改变SQL语句的结构

    • 预编译语句指的就是将SQL语句模板化，将变量的传入只作为参数的值，并不作为语句的一部分。数据库执行预编译语句时，还是按照模板语句的语义执行。

      select * from tablename where username = ? and password = ?	
      

MyBatis是如何防止SQL注入？

启用了SQL预编译的功能，执行时直接使用编译好的SQL替换占位符。

底层实现原理还是借助了JDBC中的PreparedStatement类，它的对象包含了编译好的SQL语句。

<!-- 使用#{username}进行取值 -->
<select id="selectByNameAndPassword" parameterType="java.util.Map" resultMap="BaseResultMap">
select id, username, password, role from user where username = #{username,jdbcType=VARCHAR}
    and password = #{password,jdbcType=VARCHAR}
</select>
<!-- 使用${username}进行取值 -->
<select id="selectByNameAndPassword" parameterType="java.util.Map" resultMap="BaseResultMap">
select id, username, password, role from user where username = ${username,jdbcType=VARCHAR}
and password = ${password,jdbcType=VARCHAR}
</select>

Mybatis中#和$的区别：

• # 能够防止SQL注入，$ 不能防止SQL注入！
  • #：将传入的对象当做一个字符串，会对传入的数据加一个双引号，$是直接将传入的数据显示生成在SQL中。
• ‘$’:一般用于传入动态表名和列名。一般能用#就用#，不得不用时也需要做好过滤工作防止SQL注入攻击

5、存储引擎

InnoDB和MyISAM的区别

MyISAM支持全文索引、压缩、空间函数等，但是不支持行级锁和事务，最大的缺陷在于崩溃后无法安全恢复。

• 是否支持行级锁

  • MyISAM只支持表级锁(MySQL Server实现的)，InnoDB支持行级锁和表级锁，默认为行级锁。并发下的性能差距很大！

• 是否支持事务

  • MyISAM不支持，InnoDB支持事务，具有提交和回滚事务的能力。

• 是否支持外键

  • MyISAM不支持，InnoDB支持。(一般我们也是不建议在数据库层面使用外键的，应用层面可以解决, 具体看项目)

• 是否支持数据库崩溃后的安全恢复

  • MyISAM不支持，InnoDB支持。使用InnoDB的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。

  MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
  MySQL InnoDB 引擎通过锁机制、MVCC 等手段来保证事务的隔离性（默认支持的隔离级别是 REPEATABLE-READ）。
  保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。
  

• 是否支持 MVCC

  • MyISAM 不支持，而 InnoDB 支持。MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提供性能。

锁机制和InnoDB锁算法

• 表级锁：MySQL锁定粒度最大的一种锁，对整个表加锁。实现简单，资源消耗较少，加锁快，不会出现死锁，但是锁冲突频率很高，并发度很低，MyISAM和InnoDB都支持。

• 行级锁：只针对当前操作行的数据进行加锁。行级锁能大大减少数据库操作的冲突，并发度很高。但是加锁的开销也大，加锁慢，会出现死锁。

• InnoDB支持的锁算法：

  • Record lock：单个行记录上的锁
  • Gap lock：间隙锁，锁定一个范围，不包括记录本身
  • Next-key lock：record+gap 锁定一个范围，包含记录本身

6、事务(Transation)

数据库事务可以保证多个对数据库的操作构成一个逻辑上的整体。要么全部执行成功,要么全部不执行 。事务的本质是为了应用层服务的，我们不再需要考虑服务器宕机，网络异常可能造成的问题。

何为ACID特性

• Atomic(原子性)：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全失败回滚。不会只执行其中一部分。
• Consistency(一致性)：执行事务前后，数据保持一致。
• Isolation(隔离性)：一个事务所做的修改在最终提交前，对其他事务是不可见的。(避免发生脏读)
• Durability(持久性)：一个事务被提交后，他对数据库中数据的改变是持久的，即使发生数据库故障也不应该对其产生影响。
• 总结：一致性是想要实现的目的。AID为实现目的的手段。

如何实现这些特性？

并发事务带来的问题

• 脏读：A事务读取了B事务修改还未提交的数据，由于A事务回滚造成B事务读取的数据是有误的。
• 脏写：事务回滚覆盖了另一个事务的更新。（基本现在不会存在这种问题）
• 第二类丢失更新：事务提交覆盖了另外一个事务的更新（通过锁机制避免）
• 不可重复读：事务A在事务B执行期间修改了共享变量，会造成事务B在事务A执行前后读取的共享变量值不同。
  • 银行做活动 事务a查询某地区余额1000以下送一包餐巾纸 生成名单 事务b小明余额500，存了1000，变成1500 事务a查询1000到2000送一桶油 生成名单 这样小明收到了2个礼品（事务场景不同的不同要求！）
• 幻读：事务A在事务B执行期间插入/删除了新的行，造成事务B在事务A前后读取的行数不一致。

事务隔离级别

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。(通过S锁和X锁就可以实现RR)
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL的InnoDB存储引擎默认为RR级别

MySQL的锁机制

MySQL是如何实现锁机制的？

数据库里面的锁是基于索引实现的，在Innodb中我们的锁都是作用在索引上面的，当我们的SQL命中索引时，那么锁住的就是命中条件内的索引节点(行锁)，如果没有命中索引的话，那我们锁的就是整个索引树（表锁）

在一个事务内加锁，在事务结束才会释放锁！

记录锁(Record Lock)：记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录。例如：下图表示记录锁，只锁住了id=1的数据

间隙锁(Gap Lock)：属于行锁的一种，锁住的是表中的一个区间。

触发条件：范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。

临键锁(Next-Key Lock)：临键锁也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。

状态锁(意向锁)

当一个事务试图对整个表进行加锁（共享锁或排它锁）之前，首先需要获得对应类型的意向锁（意向共享锁或意向共享锁）

• 意向共享锁：当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。
• 意向排他锁：当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

为什么需要意向锁？

InnoDB的锁是基于索引的，并且粒度为行级。当事务A在对表中的某个索引结点加锁后，如果事务B需要对整个表加锁，需要对整个索引结点一个个遍历。查看是否存在锁，如果没有才加锁。这样每次对表加锁都需要对整个树的节点进行遍历效率太低。

行锁升级为表锁：当查询到的数据过大，行锁直接升级为表锁

MVCC

只是解决读时的问题，不针对写。

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。就是为了实现==读-写冲突不加锁==。MVCC中是写完就直接更新undo log，如果出错，再回滚。

首先需要了解一下MySQL的当前读和快照读的方式：

• 1、当前读：指的是读取记录的最新版本，并且读取时保证其他并发事务不能修改当前记录，会对读的记录加锁。
• 2、快照读：不加锁的读取操作(select)就是快照读。串行隔离级别下的快照读会退化为当前读。是基于MVCC实现的。它避免了假锁操作，降低开销。

MVCC指的是“维持一个数据的多个版本，使得读写操作没有冲突”的概念，仅仅是一个概念。快照读就是MySQL对MVCC模型的实现。MVCC在MySQL中的具体实现是由三个隐式字段、undo log、Read View实现的。

并发场景：

• 读-读：不存在任何问题，也不需要并发控制
• 读-写：有线程安全问题，可能会造成事务隔离性的问题：如脏读、不可重复读、幻读问题。
• 写-写：存在线程安全问题，可能会存在更新丢失。第一类更新丢失和第二类更新丢失。

MVCC解决的问题：为了实现读-写冲突的无锁并发，为每个修改保存一个版本，版本与事务的时间戳有关。读操作只读取该事务开始前的数据库快照。可以实现并发读写数据库，同时可以解决脏读。

undo log：主要记录修改的行信息。 DB_ROW_ID：当前行的隐式主键；DB_TRX_ID：当前更新的事务ID；DB_ROLL_PTR：回滚指针，指向上一条更新的信息

• insert undo log（因此insert只要提交后，在此之前的事务也可见，无法解决幻读问题）
  代表事务在insert新记录时产生的undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃。
• update undo log
  事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

Read View：事务进行查询时的时候产生的读视图(Read View)，在该事务执行查询的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(还未提交的事务)，ID递增，事务越新，ID越大。

当前事务创建时获取当前快照信息，生成Read View。对于可见性的判断：假设当前事务对某条数据进行读取时，首先判断当前数据行的事务ID是否小于Read View的low_limit_id(活跃事务中的最小id)，如果小于，则直接读取。

• 事务中的可见性判断。当前事务创建时获取当前快照信息，生成Read View。假设当前事务对某条数据进行读取时：
  • 1、if DB_TRX_ID(数据行事务ID) < Read View的low_limit_id(活跃事务中的最小id) 或 DB_TRX_ID = Cur_TRX_ID(等于当前事务ID，对于一次事务是具有可见性的)。如果是则可以读取，如果不是，则进行下一列判断
  • 2、if DB_TRX_ID > up_limit_id(活跃事务中最大id)，那么肯定无法读取，只能通过回滚指针到undo log中查找历史版本。如果小于，则继续判断(因为行事务ID大于最大事务ID，那么意味着这个修改是在当前事务之后的事务，必然是不可见的)
  • 3、如果DB_TRX_ID属于Read View中记录的活跃事务，那么就不可见，如果不是则可见。（活跃事务意味着事务还未提交，数据为不可见的。）

MVCC如何实现RR级别和RC级别

RR级别：在创建时生成ReadView，在事务执行期间判断是否可见只用这一个ReadView。MVCC在RR级别上，对于一条记录(可能是)的Read View在整个事务内是一致的。就算别的事务更改了这个数据，但是后续再读取时，还是根据之前生成的活跃事务去判断当前记录是否可读，保证了每条数据在每次查询时的一致性。

RC级别：MVCC在RC级别上，每次查询都会生成新的Read View，这样每次读取的信息都是根据当前事务最新信息，别的事务提交了的数据就可以读取，无法保证重复读。

是根据第一次快照读生成的Read View信息，RR阶段一直使用同一个。

7、SQL语句在MySQL中如何执行

MySQL的基本架构

• 连接管理：管理连接、账号认证、获取权限信息（登录MySQL）

  • 1、客户端访问MySQL服务器之前，第一件事是建立TCP连接。
    • 多个系统会同时连接数据库，一个系统会建立多个连接进行信息获取。因此为了解决TCP无限创建和频繁销毁带来的资源耗尽、性能下降问题。MySQL服务器里有专门的TCP连接池限制连接数量，采用长连接模式复用TCP连接，解决以上问题。
    • TCP连接收到请求后，必须要分配一个线程来执行请求，所以还会有个线程池。SQL的执行在MySQL中交给线程去做。
  • 2、通过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证和权限获取。
    • 用户名或密码不对，会收到Access Denied for user错误，客户端程序结束执行。
    • 用户名密码认证通过，会从权限表中查出账号对应的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限

• 解析与优化：缓存、SQL语法解析验证、SQL优化并生成执行计划、根据执行计划调用存储引擎接口。

  • 1、查询缓存（查询语句专有）
    • 经过了连接管理，MySQL服务器获取到SQL字符串。如果是查询语句，MySQL服务器会使用select SQL字符串作为key。去缓存中获取，命中缓存，直接返回结果（返回前需要做权限验证），未命中执行后面的阶段，这个步骤叫查询缓存。
    • 需要注意，select SQL字符串要完全匹配，有任何不同的地方都会导致缓存不被命中（空格、注释、大小写、某些系统函数）。虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销。MySQL 8.0移除了这功能，不建议使用查询缓存。
  • 2、分析器：分析SQL语句是否符合语法语义（词法分析，语义分析）
    • 如果没有命中缓存，或者==非select SQL==就来到分析器阶段。因为系统发送过来的只是一段文本字符串，所以MySQL服务器要按照SQL语法对这段文本进行解析。
    • 如果你的SQL字符串不符合语法规范，就会收到You have an error in your SQL syntax错误提醒。通过了分析器，说明SQL字符串符合语法规范，现在MySQL服务器要执行SQL语句了。
  • 3、优化器：生成执行计划并优化SQL语句（专属于查询语句）
    • 需要产出执行计划，交给MySQL服务器执行，所以来到了优化器阶段。优化器不仅仅只是生成执行计划，这个过程它会帮你**优化SQL**语句。
    • 如外连接转换为内连接、表达式简化、子查询转为连接、连接顺序、索引选择等一堆东西，优化的结果就是执行计划。
  • 4、执行器：执行语句，然后从存储引擎返回数据
    • 执行前，首先判断当前用户对该表有没有足够的权限，如果没有，则会返回权限错误。有权限，根据执行计划调用存储引擎API对表进行的读写。
    • 存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现还是要看表选择的存储引擎。
  

简单来说，MySQL主要分为Server层和存储引擎层：

• Server层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
• 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redo log 模块。现在最常用的存储引擎是 InnoDB

对于更新语句的执行过程：

• 1、先查询Buffer Pool中是否有对应数据，如果没有，则从磁盘中读取该数据。

  • Buffer Pool是在内存中，在内存中操作数据。Buffer Pool用于增删改

• 2、在操作Buffer Pool中数据前，先将数据存储到undo log(内存)中，用于后期的事务回滚。（事务开始前）

  • undo log中保存的是未操作前的SQL语句，如果事务更新失败，可以利用undo log回滚到执行前的状态，不会对原始数据产生影响。如果sql语句是insert，那么undo log中记录的就是delete的对应语句。

• 3、InnoDB在Buffer Pool中执行更新操作，并将更新后的数据记录在redo log buffer中。

  • redo log用于存储更新后的数据。redo log buffer是redo log在内存中的状态，用于暂存redo log的信息。
  • 为了提高效率，将从操作都放在内存中完成，会在某个时机将其持久化到磁盘中。
  • redo log属于InnoDB的特有的日志文件

• 同时执行以下任务：

  • 4、将redo log buffer中的数据刷入到redo log文件中，进入prepared状态

  • 5、将本次操作记录写入到bin log文件中

  • 6、将本次操作的bin log文件名和更新内容在bin log中的位置记录到redo log中，同时在redo log最后添加commit6记。此时才会将事务提交。只有先将redo log写入，再等待bin log写入磁盘才提交事务。

  • 注：

    • ① 对于上述4、5、6的操作其实利用了数据库的内部XA事务，俗称为日志的两阶段提交协议。将事务提交分为了两个阶段，prepare阶段和commit阶段！

      ​ prepare：写入redo log，并将回滚段置为prepared状态，此时binlog不做操作。

      ​ commit：innodb释放锁，释放回滚段，设置提交状态，写入binlog，然后存储引擎层提交。

    • ② bin log属于MySQL级别的日志，记录的是逻辑上的修改信息。如对 students 表中的 id 为 1 的记录做了更新操作

在完成事务操作后，并没有将Buffer Pool中的数据更新到磁盘中，此时的Buffer Pool是脏数据。MySQL存在一个后台进程，会在'某个时机'将Buffer Pool中的脏数据刷到MySQL数据库中，使内存和数据库中的数据保持一致。

先将redo log写入，再等待bin log写入磁盘才提交事务？
答：如果单独拆分开，存在以下两种情况：
	1、先写完redo log提交事务后，再写入bin log。如果写完redo log后提交，bin log还未写入则发生数据库宕机。如果使用binlog进行备库，由于bin log中未有修改后的数据信息，则会发生主从不一致的情况。
	2、先写完bin log，再写redo log提交事务。如果当写完bin log，未写完redo log时，数据库发生宕机。由于redo log未写入，则事务回滚。而此时bin log中已记录了本次修改，如果用bin log备份，仍会发生主从不一致的情况。

bin log

binlog：二进制日志，用于数据库的数据恢复和主从复制。由MySQL的server层进行维护，不属于某一存储引擎特有。

• 作用：

  • 主从复制：从库利用主库中的binlog进行重播，实现主从同步。
  • 数据恢复：用于数据库的基于时间点的还原。

• 无限大小，追加写的方式。记录所有数据库表结构和表数据变更的二进制日志，比如insert、update、delete、create、truncate等等操作，不会记录select、show操作。

• 记录的是逻辑日志，是对应的SQL语句。类似于Redis的AOF持久化。

• bin log刷盘的方式

  • 0(默认)：表示在事务提交后，mysql会将日志缓冲区中的binlog数据写入到 OS cache（磁盘缓存），但并不会调用 fsync 操作进行刷盘，而是由操作系统自己控制它的缓存的刷盘。
  • 1： 表示在事务提交时就会进行binlog的写入操作，实时的将日志缓冲区中的重做日志写入到 OS cache（磁盘缓存），同时立即调用 fsync 操作将 OS cache 中的重做日志写入到磁盘文件中（刷盘）；
  • N： 表示每次进行提交后都只是将日志缓冲区中的binlog数据写入到 OS cache（磁盘缓存），在进行N次事务提交以后，Mysql将执行一次fsync操作指令将OS cache中的binlog数据批量刷新到磁盘中； 注意 N 代表的是数值；

redo log

• redo log：保证事务的一致性，属于InnoDB存储引擎专属的日志文件。crash-safe

• 作用：

  • 确保事务的持久性。redo log记录的是事务执行后的状态，用来恢复已提交事务，但尚未写入磁盘的数据。

• 有限大小，循环写，记录的是物理日志—“在某个数据页上做了某个修改”，只会记录未刷盘的日志。

  • 默认是提供了一个重做日志文件组，文件组下包含两个大小相同的redo log。

  • 写入流程为：先对redo log1写，写满后，再对redo log2写入。写满后，循环写入redo log1。

  • # redo log大小的设置
      如果redo log过大，会造成数据库宕机恢复恢复时间过长（因为redo log较大，需要刷盘的数据很多）。如果设置的过小，会导致一个事务的日志需要多次切换redo log进行写入。而每次覆盖之前的redo log前需要将内存中的脏数据刷入到磁盘中，造成一次事务会产生多次磁盘IO，造成数据库性能的抖动。
      redo log一般设置为能容纳服务器'一个小时的活动内容'。（这个redo log大小指的是redo log组累加的大小，如一小时活动内容大小为500M，存在两个redo log文件，那么每次redo log文件大小为250M）
      那么Buffer Pool中的数据更新可以等redo log日志中等待刷盘，因为覆盖数据则自动刷盘。根据redo log中的信息进行刷盘。
    

• redo log刷盘的方式

  • 0：暂存于内存中，每秒执行一次刷盘

    表示'事务提交时不进行写入重做日志的操作'，MySql会使用其后台线程每一秒将日志缓冲区中的重做日志写入到 OS cache（磁盘缓存），同时立即调用 fsync 操作将 OS cache 中的重做日志写入到磁盘文件中（刷盘）；
    

  • 1：提交事务即刷盘（默认）

    表示在事务提交时就会进行重做日志的写入操作，实时的将日志缓冲区中的重做日志写入到 OS cache（磁盘缓存），同时立即调用 fsync 操作将 OS cache 中的重做日志写入到磁盘文件中（刷盘）； **此值为默认值** ，因为当设置值为1时**可以保证事务的ACID中的持久性**；
    

  • 2：暂存于操作系统中，每秒执行一次刷盘。

    表示在事务提交时就会进行重做日志的写入操作，但是只是将日志缓冲区中的重做日志写入到 OS cache（磁盘缓存），不会立即调用 fsync 操作进行刷盘，MySQL后面会主动将OS cache中的重做日志数据每秒批量进行一次刷盘； 选择此值时，mysql的并发性最好，但是存在风险，当操作系统一旦宕机，会丢数据，但是如果MySql数据库宕机的话，则不会丢失数据，因为数据保存在了OS cache中；
    

• 只能保证数据库宕机时，事务的一致性数据。但是想要恢复数据库的全部数据，还是得使用bin log。

  

undo log

• undo log：回滚日志，可以保证事务的原子性，主要用于事务提交失败的回滚，同时可以提供多版本并发控制下的读（MVCC），即非锁定锁。
• 逻辑格式的日志，在执行undo log的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上执行操作，这是不同于redo log的。例如要insert一条数据，那么undo log就会生成一条对应的delete日志，并且与当前事务的txid（事务唯一标识）进行了关联，用于支持回滚和MVCC；
  • 写入undo log这个操作也要记录到redo log中，因为undo log的信息也是在log buffer中存储的，并没有立即刷盘。因此如果数据库宕机，要对事务。

思考：
	数据库宕机后恢复的是什么？丢失了哪些东西？
	1、Buffer Pool中的数据
	2、log buffer中的log信息（undo log，redo log，可能会丢失bin log）
	恢复是内存中的数据。内存中因为不及时刷盘，所以会造成SQL语句执行后的结果并不即时刷新到磁盘中，内存中的数据丢失。（这个可以通过re）

总结

MySQL的缓存机制

   缓存SQL文本及结果。用K-V的形式保存到'服务器内存'中，如果运行相同的SQL，服务器直接从缓存中得到结果，不需要再解析、优化、执行。但是如果这个数据表中'任意数据被修改'，那么这个表的所有'缓存都会失效'，缓存清空。因此对于频繁更新的表不适合用缓存。
   对于查找缓存数据，以SQL语句、查询数据库、客户端协议等作为key。SQL上任何字符的不同都会导致缓存不命中。

为什么不采用MySQL缓存？

1、缓存失效很频繁；只要有对一个表的更新，整个表上的缓存就会失效。
2、缓存会占用服务器的内存；
	一般来是数据库都是单独部署在一台服务器上，我们应该尽可能减少这台服务器的压力，必要时还会对其进行扩容，这些操作都是为了提高单台MySQL的处理能力。如果真的需要缓存来提高响应速度，应该将'缓存和数据库独立分开部署'。
3、已经有非常成熟的缓存中间件；
	Redis、Memcache这些中间件的性能和功能都比MySQL缓存要强大得多，并且有利于性能扩展。(搭载集群)

数据库查询优化

1、使用索引
	避免子查询，在子查询得到的结果集中不能使用索引
	隐式数据转换：最经典的就是str = 100
	避免模糊查询(%x)
	不要使用select * 查询语句，避免冗余字段。而且可能会触发覆盖查询（非聚集索引的原因，查到结果key，会通过key再查询）
	where条件子句中尽量不要使用计算表达式和函数（会使索引失效）
	避免or的条件判断，因为除非or的每个条件字段都有索引，否则不会使用索引查询
	order by后的字段建立索引。因为order by在查询后会在结果集中在进行排序后输出结果。索引查询的自动是有序的
	较长的字符串类型字段，尽量采用前缀索引
2、增加服务器
	分库分表，避免单表的查询速度太慢
	主从复制，读写分离。采用多服务器分布式部署，一台服务器用于写，其他服务器用于读。使用同步机制，保证主从一致性。

SQL优化思路：
1、善用EXPLAIN查看SQL执行计划
	重点数据：
		type列：连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
		key列：使用到的索引名。如果没有用到索引，值是null。可以采取强制索引的方式。
		key_len列，索引长度
		rows列，扫描行数。这是个预估值
		extra列，详细说明。注意常见的不太友好的值有：Using filesort, Using temporary
2、SQL语句中IN包含的值不应过多
	MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。
3、SELECT语句指明字段名称
4、使用(not) exist替换(not) in
5、union all替换union。union在运行时会先取出两个表的结果，再用排序空间进行排序删除重复元素，最后返回结果集。union all之间只是简单的将两个结果合并后返回，效率会提高。
6、如果前段一次只需求15行数据，而查询结果集会返回1W行数据，那么最好使用limit限制查询返回的数据行数。

实习真实场景下：
    SQL问题：
        1、全表扫描  
        2、索引设计不恰当，或者没有走到索引
        3、SQL查询量多大，现在要求一次SQL查询的数据量尽可能小 （分页查询 limit）
        4、JOIN查询   
        5、业务问题：扫描全部数据，改成查询一次数据量尽量小  
        6、SQL查询死锁   锁等待 （）
    监控手段：
        1、慢查询监控（索引不恰当   数据量多大 ）  
        2、锁等待监控 
    优化手段：
        1、有些已经无法优化，SQL优化解决不了，只能从业务层面改造。分库分表 
        2、尽量少排序
        3、多个业务之间进行业务隔离，避免业务连带造成数据库崩溃，导致整个产品线无法生效！  
        例如：阅卷的核心业务

面试总结

实习收获： 
	了解到企业中的SQL优化和规范  -->  例如：不允许使用连表查询 （自己私下查阅资料了解了一些） -->  存在一系列的缺点  -->  连表查询的底层实现原理  -->  

面试题

MySQL数据库的三大范式：

Redis和MySQL的区别

MySQL的索引有哪些？

• 主键索引 辅助索引(普通索引、唯一索引、全文索引)

一定需要索引吗？缺点是什么？

为什么使用索引的速度会快，底层是什么数据结构？

• B+树

B+树是一种什么样的树，和红黑树的区别？

• B+树是多叉树，具有一个特点，非叶节点不存数据，只存key。红黑树是具有特殊规则的二叉树。

B+树索引和hash索引的优缺点？

• hsah索引缺点：① 不支持区间查找；② 容易发生hash冲突 ③ 索引利用率低

集。union all之间只是简单的将两个结果合并后返回，效率会提高。
6、如果前段一次只需求15行数据，而查询结果集会返回1W行数据，那么最好使用limit限制查询返回的数据行数。

```bash
实习真实场景下：
    SQL问题：
        1、全表扫描  
        2、索引设计不恰当，或者没有走到索引
        3、SQL查询量多大，现在要求一次SQL查询的数据量尽可能小 （分页查询 limit）
        4、JOIN查询   
        5、业务问题：扫描全部数据，改成查询一次数据量尽量小  
        6、SQL查询死锁   锁等待 （）
    监控手段：
        1、慢查询监控（索引不恰当   数据量多大 ）  
        2、锁等待监控 
    优化手段：
        1、有些已经无法优化，SQL优化解决不了，只能从业务层面改造。分库分表 
        2、尽量少排序
        3、多个业务之间进行业务隔离，避免业务连带造成数据库崩溃，导致整个产品线无法生效！  
        例如：阅卷的核心业务