java基础巩固-宇宙第一AiYWM：为了维持生计，MySQL基础Part1(SQL的语法、操作数据库及表命令、Buffer Pool、排序、分页、MySQL数据类型、函数、主外键、约束）等)~整起

基础和技术就像无敌的深渊，小伙子，你要不断的学哟~~…
特此鸣谢优秀书籍、文章和视频的各位大神，让我能够对自己的笔记有如下补充：

数据库分为：

• 关系型数据库：通过行和行、列和列之间的关系进行数据的存储。关系型数据库里面最具有代表性的就是MySQL了。除了这，再区分几个常见概念：
  • 数据库 : 数据库(DataBase 简称 DB)就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。就是一堆数据嘛，只不过人家有编制有纪律
  • 数据库管理系统 : 数据库管理系统(Database Management System 简称 DBMS)是一种操纵和管理数据库的大型软件，通常用于建立、使用和维护数据库。
  • 数据库系统 : 数据库系统(Data Base System，简称 DBS)通常由软件、数据库和数据管理员(DBA)组成。
  • 数据库管理员 : 数据库管理员(Database Administrator, 简称 DBA)负责全面管理和控制数据库系统。
• NoSQL（非关系型数据库），可以根据对象自身的属性来存储对象

这篇先唠MySQL,咱们学基础也基本上都是从MySQL开始的喽，

• 那先来看看SQL的基本语法：javaGuide老师的博客，关于常用的SQL命令，很全
  • 一款 SQL 自动检查神器Yearning MYSQL ,Yearning MYSQL 是一个SQL语句审核平台。提供查询审计，SQL审核等多种功能，支持Mysql，可以在一定程度上解决运维与开发之间的那一环，功能丰富，代码开源，安装部署容易！文章来自Java基基lao’shi
    老规矩，上图
    
    
• DML 语句和 DDL 语句区别：
  • DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select），是开发人员日常使用最频繁的操作。
    • 由于select不会对表进行破坏，所以有的地方也会把select单独区分开叫做数据库查询语言DQL（Data Query Language）
  • DDL （Data Definition Language）是数据定义语言的缩写，简单来说，DDL 就是对数据库内部的对象进行创建、删除、修改的操作语言。DDL 和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。
• 如果数据库误操作, 如何执行数据恢复?
  • 数据库在某个时候误操作，就可以找到距离误操作最近的时间节点的bin log，重放到临时数据库里，然后选择误删的数据节点，恢复到线上数据库。
• 误删数据怎么办？DBA 的最核心的工作就是保证数据的完整性，先要做好预防，预防的话大概是通过这几个点：
  • 权限控制与分配(数据库和服务器权限)
  • 制作操作规范
  • 定期给开发进行培训
  • 搭建延迟备库
  • 做好 SQL 审计，只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核
  • 做好备份。备份的话又分为两个点 (1)如果数据量比较大，用物理备份 xtrabackup。定期对数据库进行全量备份，也可以做增量备份。(2)如果数据量较少，用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份binlog 文件也是很有必要的
  • 如果发生了数据删除的操作，又可以从以下几个点来恢复:
    • DML 误操作语句造成数据不完整或者丢失。可以通过 flashback，美团的 myflash，也是一个不错的工具，本质都差不多，都是先解析 binlog event，然后在进行反转。把 delete 反转为insert，insert 反转为 delete，update前后 image 对调。所以必须设置binlog_format=row 和 binlog_row_image=full，切记恢复数据的时候，应该先恢复到临时的实例，然后在恢复回主库上。
    • DDL语句误操作(truncate和drop)，由于DDL语句不管 binlog_format 是 row 还是 statement ，在 binlog 里都只记录语句，不记录 image 所以恢复起来相对要麻烦得多。只能通过全量备份+应用 binlog 的方式来恢复数据。一旦数据量比较大，那么恢复时间就特别长
    • rm 删除：使用备份跨机房，或者最好是跨城市保存。
• 删除表数据后表的大小却没有变动,这是为什么?
  • 在使用 delete 删除数据时，其实对应的数据行并不是真正的删除，是逻辑删除，InnoDB 仅仅是将其标记成可复用的状态，所以表空间不会变小
  • 数据项删除之后 InnoDB 某个页 page A 会被标记为可复用。
  • delete 命令把整个表的数据删除，结果就是，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。
  • 经过大量增删改的表，都是可能是存在空洞的。这些空洞也占空间所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。
  • 重建表，就可以达到这样的目的。可以使用 alter table A engine=InnoDB 命令来重建表。
    
    
    再提取一下就是常见的操作数据库的命令以及操作数据库表的命令如下：
  • 常见的操作数据库的命令
    
  • 操作数据库表的命令
    
  • 查看、排序、分页、模糊查询
    
    • 模糊查询和分页，如果分开用，一般是没问题的。但如果它们要一起使用，一定要考虑排序问题。
  • 函数
    

上面的SQL语句的基本语法瞅瞅之外，还有一些基本的概念，比如：

• 约束(对表中数据进行限制，保证数据的正确性，有效性和完整性)
  

• 基础概念（DBMS、行、列、主键、外键、超键、候选键分别是什么？）

  • 元组 ： 元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。
  • 码 ：码就是能唯一标识实体的属性，对应表中的列
  • 外键：在一个表中存在的另一个表的主键称此表的外键，外键主要用于引用完整性的约束检查。
    • 阿里开发手册建议在应用层用java代码实现外键的相关概念。
      • 如果系统不涉及分库分表，并发量不是很高的情况还是可以考虑使用外键的
      • 阿里巴巴开发手册这样说到：【强制】不得使用外键与级联，一切外键概念必须在应用层解决。说明: 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群; 级联更新是强阻塞，存在数据库更新风暴的风 险; 外键影响数据库的插入速度
    • 外码 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码
      • 外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键
      • 用外键最大的好处就是防止在产品信息表中插入拥有非法供应商ID（即没有在供应商信息表中出现）的供应商生产的产品，则这些产品是不可访问的也就是不合法的咯，因为它们没有关联到某个供应商。此时就可指示MySQL只允许在产品信息表中的供应商ID列中出现合法值（即出现在供应商信息表中的供应商），这不就是通过在表的定义中指定主键和外键来实现的，也叫做维护引用完整性。【FK保证了数据库数据的一致性和完整性】
      • 外键其他的好处：
        • 省时间和空间；
        • 降低了表之间的耦合性，一个表的信息变动只需要改动单个表的记录其他表中的数据不用改动
        • 由于数据无重复，显然数据是一致的，这使得处理数据更简单
          
    • 在InnoDB存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB 存储引擎自动对其加一个索引，因为这样可以避免表锁。对于外键值的插人或更新，首先需要查询父表中的记录，即SELECT父表。但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是SELECT…LOCK IN SHARE MODE方式，即主动对父表加一个S锁。如果这时父表上已经这样加X锁,子表上的操作会被阻塞.
  • 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）
    • 主码 : 主码也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码【主键用于唯一标识一个元组，不能有重复，不允许为空。一个表只能有一个主键】。
    • MySQL 有哪些自增ID？各自场景是什么？
      • 表的自增 ID 达到上限之后，在申请值不会变化，进而导致联系插入数据的时候报主键冲突错误。
      • row_id 达到上限之后，归 0 在重新递增，如果出现相同的 row_id 后写的数据会覆盖之前的数据。
      • Xid 只需要不在同一个 binlog 文件出现重复值即可，理论上会出现重复值，但概率极小可忽略不计。
        • Xid 是由 server 层维护的。InnoDB 内部使用 Xid，就是为了能够在 InnoDB 事务和 server 之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。
        • Xid 在 MySQL 内部是怎么生成的呢？
          • MySQL 内部维护了一个全局变量 global_query_id，每次执行语句（包括select语句）的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。而 global_query_id 是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件，这就保证了，同一个 binlog 文件里，Xid 一定是惟一的。
      • InnoDB 的 max_trx_id 递增值每次 MySQL 重启会保存起来。
      • thread_id 是我们使用中最常见的，而且也是处理得最好的一个自增 id 逻辑了。使用了insert_unique算法
    • 为什么推荐使用自增 id 作为主键：InnoDB 为什么要用自增 ID 作为主键？
      • １.普通索引的 B+ 树上存放的是主键索引的值，如果该值较大，会导致普通索引的存储空间较大
      • ２.使用自增 id 做主键索引新插入数据只要放在该页的最尾端就可以，直接按照顺序插入，不用刻意维护
        • 每次插入新的记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
        • 而有业务逻辑的字段做主键，不容易保证有序插入，由于每次插入主键的值近似于随机
      • 3.页分裂容易维护，当插入数据的当前页快满时，会发生页分裂的现象，如果主键索引不为自增 id，那么数据就可能从页的中间插入，页的数据会频繁的变动，导致页分裂维护成本较高
        • 自增主键的插入模式，符合递增插入，每次都是追加操作，不涉及挪动记录，也不会触发叶子节点的分裂。
        • 因此每次新纪录都要被插到现有索引页得中间某个位置， 频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，写数据成本较高。
    • MySQL 自增主键 ID 不连续？
      • 唯一键冲突
      • 事务回滚
      • 自增主键的批量申请
        • 深层次原因是：MySQL 不判断自增主键是否存在，从而减少加锁的时间范围和粒度，这样能保持更高的性能，确保自增主键不能回退，所以才有自增主键不连续。
          自增主键怎么做到唯一性？自增值加1来通过自增锁控制并发
    • 在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器(aut-increment counter)。当对含有自增长的计数器的表进行插人操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值:SELECT MAX (auto_ inc col) FROM t FOR UPDATE;插人操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。AUTO-INC Locking这种锁其实是采用一种特殊的表锁机制，为了提高插人的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插人的SQL语句后立即释放。
      • 虽然AUTO-INC Locking从-定程度上提高了并发插人的效率，但还是存在一些性能上的问题。
        • 首先，对于有自增长值的列的并发插人性能较差，事务必须等待前一个插人的完成(虽然不用等待事务的完成)。
        • 其次，对于INSERT-SELECT的大数据量的插人会影响插人的性能，因为另一个事务中的插人会被阻塞。从MySQL 5.1.22版本开始，InnoDB 存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插人的性能。并且从该版本开始，InnoDB 存储引擎提供了一个参数innodb autoinc_ lock mode来控制自增长的模式，该参数的默认值为1.
      • 此外，还需要特别注意的是InnoDB存储引擎中自增长的实现和MyISAM不同，MyISAM存储引擎是表锁设计，自增长不用考虑并发插人的问题。因此在master上用InnoDB存储引擎，在slave上用MyISAM存储引擎的replication架构下，用户必须考虑这种情况。另外，在InnoDB存储引擎中，自增长值的列必须是索引，同时必须是索引的第一个列。如果不是第一个列， 则MySQL数据库会抛出异常，而MyISAM存储引擎没有这
        个问题。
  • 超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键
  • 候选键：是最小超键，即没有冗余元素的超键
    • 候选码 ： 若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码
      • 主属性 ： 候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）. 显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
      • 非主属性： 不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性

• buffer pool ：buffer pool 是一块内存区域，为了提高数据库的性能，当数据库操作数据的时候，把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool 里面的数据，数据库的增删改查都是在 buffer pool 上进行，buffer pool 里面缓存的数据内容也是一个个数据页
  

  • InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。在 MySQL 启动的时候，InnoDB 会为 Buffer Pool 申请一片连续的内存空间，然后按照默认的16KB的大小划分出一个个的页， Buffer Pool 中的页就叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。
    • 所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。
    • Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 Undo 页，插入缓存、自适应哈希索引、锁信息等等。
      
      • Undo 页是用来记录什么：开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。
      • 当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。
      • 为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页。
        
        • 为什么会有碎片空间呢：每一个控制块都对应一个缓存页，那在分配足够多的控制块和缓存页后，可能剩余的那点儿空间不够一对控制块和缓存页的大小，自然就用不到喽，这个用不到的那点儿内存空间就被称为碎片了。
        • 查询一条记录，就只需要缓冲一条记录吗？no
          • 当我们查询一条记录时，InnoDB 是会把整个页的数据加载到 Buffer Pool 中，因为，通过索引只能定位到磁盘中的页，而不能定位到页中的一条记录。将页加载到 Buffer Pool 后，再通过页里的页目录去定位到某条具体的记录。
  • MySQL 的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，然后在内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？当然是缓存起来好，这样下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了，从缓存中拿肯定比从磁盘中拿快很多呀。为此，Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。有了 Buffer Poo 后：
    • 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
    • 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。
  • 其中有三大双向链表:
    • free 链表：用于帮助我们找到空闲的缓存页
    • flush 链表：用于找到脏缓存页，也就是需要刷盘的缓存页
    • lru 链表：用来淘汰不常被访问的缓存页，分为热数据区和冷数据区，冷数据区主要存放那些不常被用到的数据
  • 预读机制:：Buffer Pool 有一项特技叫预读，存储引擎的接口在被 Server 层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到 Buffer Pool
  • 如何管理空闲页？
    • Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。那当我们从磁盘读取数据的时候，总不能通过遍历这一片连续的内存空间来找到空闲的缓存页吧，这样效率太低了。所以，为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）。
      
  • 如何管理脏页:
    • 设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，而是将 Buffer Pool 对应的缓存页标记为脏页，然后再由后台线程将脏页写入到磁盘。那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。
      
    • 脏页什么时候会被刷入磁盘？
      • 引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改数据都刷入磁盘，则性能会很差，因此一般都会在一定时机进行批量刷盘。
        
  • 如何提高缓存命中率？
    • Buffer Pool 的大小是有限的，对于一些频繁访问的数据我们希望可以一直留在 Buffer Pool 中，而一些很少访问的数据希望可以在某些时机可以淘汰掉，从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还能保证常用数据留在 Buffer Pool 中。要实现这个，最容易想到的就是 LRU（Least recently used）算法。该算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。
    • 简单的 LRU 算法并没有被 MySQL 使用，因为简单的 LRU 算法无法避免下面这两个问题：
      • 预读失效；
        
        
      • Buffer Pool 污染；
        
        

语法小贴士：

• MySQL中常用的数据类型都有哪些？（数据类型指的是定义（列中）存什么数据，以及怎么存）
  • 最常用的数据类型是串数据类型：
    • 有两种基本的串类型：（串值都必须括在引号内（通常单引号更好）。）
      • 定长串：存储的字符串的长度在创建表时指定的
        • CHAR:1～255个字符的定长串。它的长度必须在创建时指定，否则MySQL假定为CHAR(1)
      • 变长串：MySQL处理定长列远比处理变长列快得多，并且MySQL不允许对变长列（或一个列的可变部分）进行索引，（变长列有些具有最大的定长，和定长列一样，有些是完全变长的。）因为性能的缘故，所以有了变长列之外还得有定长列。
        • VARCHAR：长度可变，最多不超过255字节。如果在创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串（其中n≤255）。
          • 为什么 VarChar 建议不要超过255?
            
          • MySQL 中的 varchar 和 char 有什么区别？
            
        • TEXT：最大长度为64 K的变长文本
        • TINYTEXT：与TEXT相同，但最大长度为255字节。
    • 串类型的基本使用规则：如果数值是计算（求和、平均等）中使用的数值，则应该存储在数值数据类型列中。如果作为字符串（可能只包含数字）使用，则应该保存在串数据类型列中。比如如果在数值字段中存储邮政编码01234，则保存的将是数值1234，实际上丢失了一位数字。
      • 如果将值与串类型的列进行比较，则需要限定引号。用来与数值列进行比较的值不用引号。
  • 数值数据类型：有符号或无符号 所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字，这样做将允许你存储两倍大小的值。
    • INT（或INTEGER）：整数值，支持-2147483648～2147483647（如果是UNSIGNED， 为0～4294967295）的数
    • TINYINT：整数值，支持-128～127（如果为UNSIGNED，为0～255）的数
    • FLOAT：单精度浮点值
    • DOUBLE：双精度浮点值
    • DECIMAL（或DEC）： 精度可变的浮点值。MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)
    • BOOLEAN（或BOOL） ：布尔标志，或者为0或者为1，主要用于开/关（on/off）标志
  • 日期和时间数据类型：
    • DATE ：表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD
    • DATETIME ：DATE和TIME的组合
      • DateTime 类型是没有时区信息的（时区无关） ，当你的时区更换之后，比如你的服务器更换地址或者更换客户端连接时区设置的话，就会导致你从数据库中读出的时间错误。
      • DateTime 类型耗费空间更大
    • TIMESTAMP：功能和DATETIME相同（但范围较小）：通常我们都会首选 Timestamp
      • Timestamp 类型字段的值会随着服务器时区的变化而变化，自动换算成相应的时间，说简单点就是在不同时区，查询到同一个条记录此字段的值会不一样。
      • Timestamp 只需要使用 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间。但是Timestamp 表示的时间范围更小。
        
      • 每种方式都有各自的优势，根据实际场景才是王道。下面再对这三种方式做一个简单的对比，以供大家实际开发中选择正确的存放时间的数据类型
        
    • TIME ：格式为HH:MM:SS
    • YEAR ：用2位数字表示，范围是70（1970年）～69（2069年），用4位数字表示，范围是1901年～2155年
  • 二进制数据类型：二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等
    • BLOB ：Blob最大长度为64 KB
    • LONGBLOB ：Blob最大长度为4 GB
    • TINYBLOB ：Blob最大长度为255字节
  • 常用的数据库字段的相关知识点：
    
• MySQL中 in 和 exists 区别
  
  • 假设表A表示某企业的员工表，表B表示部门表，查询所有部门的所有员工，我们要选择最外层循环小的，也就是，如果B的数据量小于A，适合使用in，如果B的数据量大于A，即适合选择exist。
    • 查询所有部门的所有员工：一般我们自然而然就会想着：
      
    • 除了使用in，我们也可以用exists实现一样的查询功能
      
• drop、delete与truncate的区别
  • DELETE 语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
    • 如果想删除部分数据用 delete，注意带上 where 子句，回滚段要足够大；
    • 如果和事务有关，或者想触发 trigger，还是用 delete
      • truncate 和 drop 属于 DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。而 delete 语句是 DML (数据库操作语言)语句，这个操作会放到 rollback segement 中，事务提交之后才生效。
    • delete from 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和truncate table 表名作用类似。
    • 捡田螺的小男孩老师对于update或者delete子查询的语句，MySQL官网也是推荐join的方式优化的文章
  • TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
    • 如果想删除表，当然用 drop；如果想保留表而将所有数据删除，如果和事务无关，用 truncate 即可；
      • truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
    • 如果是整理表内部的碎片，可以用 truncate 跟上 reuse stroage，再重新导入/插入数据。
  • drop语句将表所占用的空间全释放掉。
    • drop table 表名 ，直接将表都删除掉，在删除表的时候使用。
  • 在速度上，一般来说，drop> truncate > delete。
    
• MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS中是很流行的。
• having关键字：sql中的having语句是在使用group by的时候使用的，对分组之后的数据进行筛选。
• on 和 where的差别：
  • on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
  • where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
• orderby 排序的内部原理:
  • MySQL 会为每个线程分配一个内存（sort-buffer）用于排序该内存大小为 sort_buffer_size；
  • 如果排序的数据量小于 sort_buffer_size，排序就会在内存中完成；
  • 内部排序分为两种
    • 全字段排序：到索引树上找到满足条件的主键ID根据主键ID去取出数据放到sort_buffer然后进行快速排序
    • rowid排序：通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据
  • 如果数据量很大，内存中无法存下这么多，就会使用磁盘临时文件来辅助排序，称为外部排序；
    • 外部排序，MySQL会分为好几份单独的临时文件来存放排序后的数据，一般是磁盘文件中进行归并，然后将这些文件合并成一个大文件；
• join 用法:
  • 使用 left join 左边的表不一定是驱动表
  • join不走索引的原因：
    
  • 如果需要 left join 的语义，就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断，必须都写在 on 里面
  • 阿里巴巴开发手册中说到 禁止超过三张表的join【最主要的原因就是join的效率比较低】
    
    • 具体到 join的算法实现上主要有simple nested loop，block nested loop和index nested loop这三种。这三种的效率都没有特别高【虽然MySQL已经尽可能的在优化了，但是这几种算法复杂度都还是挺高的，这也是为什么不建议在数据库中多表JOIN的原因。随着表越多，表中的数据量越多，JOIN的效率会呈指数级下降。】【当进行 join 操作时，mysql是如何工作的。常见的 join 方式有哪些？】
      
      • 最差的算法就是simple nested loop，他的做法简单粗暴，就是 全量扫描连接两张表进行数据的两两对比，所以他的复杂度可以认为是O（n^2)
        
      • 好一点的算法是index nested loop，当Inner Loop的表用到字段有索引的话，可以用到索引进行查询数据，因为索引是B+树的，复杂度可以近似认为是O（nlogn）
        
        • 驱动表的记录并不是所有列都会被放到 join buffer，只有查询列表中的列和过滤条件中的列才会被放入 join buffer，因此 我们不要把 * 作为查询列表，只需要把我们关心的列放到查询列表就好了，这样可以在 join buffer 中放置更多的记录
      • block nested loop这种算法，其实是引入了一个Buffer，会提前把外循环的一部分结果提前放到多个JOIN BUFFER中，然后内循环的每一行都和多个buffer中的所有数据作比较，从而减少内循环的次数。他的复杂度是O（M*N），这里的M是buffer的个数。
        
        • Block Nested-Loop Join批量取数据能减少很多IO操作，因此执行效率比较高，这种连接操作也被MySQL采用
        • 这块内存在MySQ中有一个专有的名词，叫做 join buffer，我们可以执行如下语句查看 join buffer 的大小：show variables like '%join_buffer%'
          
    • 不能通过数据库做关联查询，那么需要查询多表的数据的时候咱们应该怎么干呢？应该这样干：
      • 在内存中自己做关联，即先从数据库中把数据查出来之后，我们在代码中再进行二次查询，然后再进行关联
      • 数据冗余，那就是把一些重要的数据在表中做冗余，这样就可以避免关联查询了
        • 数据冗余是互联网业务中比较常见的做法，其实 数据冗余本质上是软件开发中一个比较典型的方案，那就是"用空间换时间"，通过做一些数据冗余，来提升查询速度。在互联网业务中，比较典型的就是数据量大，并发高，并且通常查询的频率要远高于写入的频率，所以适当的做一些反范式，通过做一些字段的冗余，可以提升查询性能，降低响应时长，从而提升并发度。
    • 如何选择驱动表？我们让 小表做驱动表即可
      • 如果是 Block Nested-Loop Join 算法：
        • 当 join buffer 足够大时，谁做驱动表没有影响
        • 当 join buffer 不够大时，应该选择小表做驱动表（小表数据量少，放入 join buffer 的次数少，减少表的扫描次数）
      • 如果是 Index Nested-Loop Join 算法:显然M对扫描行数影响更大，因此应该让小表做驱动表。当然这个结论的前提是可以使用被驱动表的索引
        • 假设驱动表的行数是M，因此需要扫描驱动表M行。被驱动表的行数是N，每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一颗树近似复杂度是以2为底N的对数，所以在被驱动表上查一行的时间复杂度是2log2^N。驱动表的每一行数据都要到被驱动表上搜索一次，整个执行过程近似复杂度为M+M2*log2 ^ N
  • 标准的 group by 语句，是需要在 select 部分加一个聚合函数，比如select a,count(*) from t group by a order by null;
    • count(*)实现方式以及各种 count 对比:所以按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(※)，所以建议尽量使用 count(*)。【当我们对一张数据表中的记录进行统计的时候，习惯都会使用 count 函数来统计，但是 count 函数传入的参数有很多种，比如 count(1)、count(*)、count(字段) 等。count() 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。】
      
      • 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
        • count(主键字段) 执行过程：在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。InnoDB 是通过 B+ 树来保持记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。【如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。】
          
      • 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
        
        • count(1) 执行过程：如果表里只有主键索引，没有二级索引时，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。可以看到，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 count(1) 执行效率会比 count(主键字段) 高一点。
          但是，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。
      • 对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。
        • count(字段) 执行过程：count(字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 执行效率是最差的。
      • 但是 count * 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。
        • count() 执行过程：count(*) 其实等于 count(0)，也就是说，当你使用 count() 时，MySQL 会将 * 参数转化为参数 0 来处理。所以，count() 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异。InnoDB以相同的方式处理SELECT COUNT（*）和SELECT COUNT（1）操作，没有性能差异。而且 MySQL 会对 count() 和 count(1) 有个优化，如果有多个二级索引的时候，优化器会使用key_len 最小的二级索引进行扫描。只有当没有二级索引的时候，才会采用主键索引来进行统计。
      • 为什么要通过遍历的方式来计数：在 MyISAM 存储引擎里，执行 count 函数的方式是不一样的，通常在没有任何查询条件下的 count(*)，MyISAM 的查询速度要明显快于 InnoDB。使用 MyISAM 引擎时，执行 count 函数只需要 O(1 )复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息有存储了row_count值，由表级锁保证一致性，所以直接读取 row_count 值就是 count 函数的执行结果。而 InnoDB 存储引擎是支持事务的，同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的，所以无法像 MyISAM一样，只维护一个 row_count 变量。
      • 优化 count()：如果对一张大表经常用 count() 来做统计，其实是很不好的。
        • 第一种，近似值*。可以使用 show table status 或者 explain 命令来表进行估算。
        • 第二种，额外表保存计数值。如果是想精确的获取表的记录总数，我们可以将这个计数值保存到单独的一张计数表中。当我们在数据表插入一条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，我们需要额外维护这个计数表。
• MySQL 的两个 kill 命令:
  • 两个 kill 命令
    • 一个是 kill query + 线程 id，表示终止这个线程中正在执行的语句
    • 一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接
  • kill 不掉的原因
    • kill命令被堵了，还没到位
    • kill命令到位了，但是没被立刻触发
    • kill命令被触发了，但执行完也需要时间
• grant 和 flush privileges语句：
  • grant语句会同时修改数据表和内存，判断权限的时候使用的内存数据，因此，规范使用是不需要加上 flush privileges 语句。
  • flush privileges 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下再使用。
• ER 图;
  • E-R 图 也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。 它是描述现实世界关系概念模型的有效方法。 是表示概念关系模型的一种方式。
    
  • 我们做一个项目的时候一定要试着 画 ER 图来捋清数据库设计

巨人的肩膀：
https://www.javalearn.cn
高性能mysql
Mysql技术内幕
小林coding
javaguide
一个Github的开源项目：手把手教大家写一个最简单的数据库出来：https://cstack.github.io/db_tutorial/