1、六种范式
六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。
1.1、第一范式——原子性
定义： 属于第一范式关系的所有属性都不可再分，即数据项不可分。
理解： 第一范式强调数据表的原子性，是其他范式的基础。例如下表
name-age列具有两个属性，一个name,一个 age不符合第一范式，把它拆分成两列
上表就符合第一范式关系。但日常生活中仅用第一范式来规范表格是远远不够的，依然会存在数据冗余过大、删除异常、插入异常、修改异常的问题，此时就需要引入规范化概念，将其转化为更标准化的表格，减少数据依赖。
实际上，1NF是所有关系型数据库的最基本要求，你在关系型数据库管理系统（RDBMS），例如SQL Server，Oracle，MySQL中创建数据表的时候，如果数据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。也就是说，只要在RDBMS中已经存在的数据表，一定是符合1NF的。
1.2、第二范式——主键
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一的区分。通常在实现来说，需要为表加上一个列，以存储各个实例的惟一标识。
例如员工信息表中加上了员工编号（emp_id）列，因为每个员工的员工编号是惟一的，因此每个员工可以被惟一区分。这个惟一属性列被称为主关键字或主键、主码。
也就是说要求表中只具有一个业务主键，而且第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。什么意思呢？
有两张表：订单表，产品表
一个订单有多个产品，所以订单的主键为【订单ID】和【产品ID】组成的联合主键，这样2个组件不符合第二范式，而且产品ID和订单ID没有强关联，故把订单表进行拆分为订单表与订单与商品的中间表。
1.3、第三范式——冗余
指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主键对主键的传递依赖。
例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。
其中
产品 ID与订单编号存在关联关系
产品名称与订单编号存在关联关系
产品ID与产品名称存在关联关系
订单表里如果如果产品ID发生改变，同一个表里产品名称也要跟着改变，这样不符合第三范式，应该把产品名称这一列从订单表中删除。
范式说明
2、反范式化设计
完全符合范式化的设计真的完美无缺吗？很明显在实际的业务查询中会大量存在着表的关联查询，而大量的表关联很多的时候非常影响查询的性能。
所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式的要求进行违反。允许存在少量得冗余，换句话来说反范式化就是使用空间来换取时间。
反范式设计-商品信息
下面是范式设计的商品信息表
如果要根据商品ID查询对应的分类名称，就要先根据商品ID查询商品分类对应关系表找到分类ID，再根据分类的ID查询分类信息表获取到分类名称。
商品信息和分类信息经常一起查询，所以把分类信息也放到商品表里面，冗余存放，这个就是反范式设计。
总结：尽量的遵守范式，当范式成为性能瓶颈的时候果断选择反范式。
3、什么是回表和MRR?
3.1、回表
回表：数据库通过二级索引找到记录后，因为索引里没有查询需要的所有字段，再拿着主键回到聚簇索引/主表里取完整行数据的过程。
SELECT*FROMusersWHEREname='Alice';如果只有 idx_name(name) 索引，流程是：
- 先在 idx_name 里找到 name = ‘Alice’ 的记录；
- 拿到对应的主键 id；
- 再用这个 id 去主键索引里查完整行数据。
第 3 步就是回表。
如果查询只需要索引里已有的字段，例如：
SELECTid,nameFROMusersWHEREname='Alice';这时索引已经能直接返回结果，就不需要回表，这种情况叫覆盖索引。
3.2、MRR
MRR：全称 Multi-Range Read，多范围读取优化，可以理解为：回表时的“批量排序读取”优化。
它解决什么问题？
前面说回表时，如果通过二级索引查到很多主键，再逐条回聚簇索引取完整行，这些主键在聚簇索引里可能是散开的，就会形成大量 随机 IO。
MRR 的思路是：先不急着回表，把要回表的主键值攒起来，排好序，再按主键顺序批量回表，这样随机 IO 就变成了更接近顺序 IO,减少了回表次数。
执行流程，以这条 SQL 为例：
SELECT*FROMtWHEREage=18ANDnameLIKE'C%';假设只有 idx_age_name(age, name) 索引，但没有覆盖所有字段，需要回表。
开启 MRR 后大致流程是：
- 扫描二级索引，找到满足条件的记录；
- 把这些记录对应的主键值放入 read_rnd_buffer；
- 缓冲区满或扫描结束后，对主键值排序；
- 按排序后的主键顺序批量回表读取完整行；
- 返回结果。
| 参数 | 作用 |
|---|---|
read_rnd_buffer_size | MRR 缓冲区大小，默认 256KB，决定一次能攒多少主键再排序回表 |
optimizer_switch中的mrr | 是否允许使用 MRR |
optimizer_switch中的mrr_cost_based | 是否让优化器按成本决定是否启用 MRR |
用 EXPLAIN 看执行计划，如果 Extra 列出现：Using MRR
4、InnoDB中的自适应哈希索引( Adaptive Hash Index,AHI)
B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3~4层,故需要3～4次的IO查询。
所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。
InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。
通过命令show engine innodb status\G可以看到当前自适应哈希索引的使用状况，如:
哈希索引只能用来搜索等值的查询,如 SELECT* FROM table WHERE index co=xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的。
可以通过观察 SHOW ENGINE INNODB STATUS的结果及参数 innodb_adaptive_hash_index来考虑是禁用或启动此特性,默认AHI为开启状态。
什么时候需要禁用呢？如果发现监视索引查找和维护哈希索引结构的额外开销远远超过了自适应哈希索引带来的性能提升就需要关闭这个功能。
同时在MySQL 5.7中，自适应哈希索引搜索系统被分区。每个索引都绑定到一个特定的分区，每个分区都由一个单独的 latch 锁保护。分区由 innodb_adaptive_hash_index_parts 配置选项控制 。在早期版本中，自适应哈希索引搜索系统受到单个 latch 锁的保护，这可能成为繁重工作负载下的争用点。innodb_adaptive_hash_index_parts 默认情况下，该 选项设置为8。最大设置为512。当然禁用或启动此特性和调整分区个数这个应该是DBA的工作，我们了解即可。
innodb的三大特性： 双写缓冲区、自适应哈希索引、buffer pool
5、InnoDB中的全文检索（Full-Text Search）
5.1、全文检索
什么是全文检索（Full-Text Search）？它是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。我们比较熟知的Elasticsearch、Solr等就是全文检索引擎，底层都是基于Apache Lucene的。
举个例子，现在我们要保存唐宋诗词，数据库中我们们会怎么设计？诗词表我们可能的设计如下：
我们现在有个需求：要求找到包含“望”字的诗词怎么办？用select 诗词全文 from 诗词表 where诗词全文 like‘%望%’，这个意味着要扫描库中的诗词全文字段，逐条比对，找出所有包含关键词“望”字的记录，。基本上，数据库中一般的SQL优化手段都是用不上的。数量少，大概性能还能接受，如果数据量稍微大点，就完全无法接受了，更何况在互联网这种海量数据的情况下呢？怎么解决这个问题呢，用倒排索引。
比如现在有：
蜀道难（唐）李白 蜀道之难难于上青天，侧身西望长咨嗟。
静夜思（唐）李白 举头望明月，低头思故乡。
春台望（唐）李隆基 暇景属三春，高台聊四望。
鹤冲天（宋）柳永 黄金榜上，偶失龙头望。明代暂遗贤，如何向？未遂风云便，争不恣狂荡。何须论得丧？才子词人，自是白衣卿相。烟花巷陌，依约丹青屏障。幸有意中人，堪寻访。且恁偎红翠，风流事，平生畅。青春都一饷。忍把浮名，换了浅斟低唱！
都有望字，于是我们可以这么保存
如果查哪个诗词中包含上，怎么办，上述的表格可以继续填入新的记录
其实，上述诗词的中每个字都可以作为关键字，然后建立关键字和文档之间的对应关系，也就是标识关键字被哪些文档包含。
所以，倒排索引就是，将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字是，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。
在存储在关系型数据库中的数据，需要我们事先分析将数据拆分为不同的字段，而在es这类的存储中，需要应用程序根据规则自动提取关键字，并形成对应关系。
这些预先提取的关键字，在全文检索领域一般被称为term（词项），文档的词项提取在es中被称为文档分析，这是全文检索很核心的过程，必须要区分哪些是词项，哪些不是，比如很多场景下，apple和apples是同一个东西，望和看其实是同一个动作。
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。从InnoDB 1.2.x版本开始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。
注意，不管什么引擎，只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
不过MySQL从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，如中文、日语、韩语等。
所以如果有大批量或者专门的全文检索需求，还是应该选择专门的全文检索引擎ES。
具体如何使用InnoDB存储引擎的全文检索，只提供简单的使用说明，更多的详情请自行查阅相关官方文档或者书籍，我们不做任何技术支持。官方文档路径：https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
5.2、案例（不推荐使用全文索引）
创建表时创建全文索引
createtablefulltext_test(idint(11)NOTNULLAUTO_INCREMENT,contenttextNOTNULL,tagvarchar(255),PRIMARYKEY(id),FULLTEXTKEYcontent_tag_fulltext(content,tag))DEFAULTCHARSET=utf8;在已存在的表上创建全文索引
createfulltextindexcontent_tag_fulltextonfulltext_test(content,tag);通过 SQL 语句 ALTER TABLE 创建全文索引
altertablefulltext_testaddfulltextindexcontent_tag_fulltext(content,tag);使用全文索引
和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如
select*fromfulltext_testwherematch(content,tag)against('xxxxxx');6、MySQL有哪些索引类型
1、从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）；
2、从物理存储角度可分为聚集索引（clustered index）、非聚集索引（non-clustered index）；
3、从逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一索引等等。
7、面试题:什么是密集索引和稀疏索引?
密集索引是“每行数据都有对应索引项”，稀疏索引是“只对部分数据建立索引项”；在 MySQL InnoDB 中，主键索引通常是密集索引，普通二级索引通常是稀疏索引。
密集索引的定义：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引。
稀疏索引的定义：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息机器主键。
mysam存储引擎，不管是主键索引，唯一键索引、还是普通索引，都是稀疏索引
innodb存储引擎：有且只有一个密集索引，所以，密集索引就是innodb存储引擎里的聚簇索引，稀疏索引就是innodb存储引擎里的普通二级索引。
8、辨析覆盖索引/索引覆盖
并不是一种索引，而是索引优化，减少回表，减少大量的IO操作操作的一种方式。除了覆盖索引这个概念外，在索引优化的范围内，还有前缀索引、三星索引等一系列概念，都会在我们后面的课程中学习到。
9、前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢。
可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。
按照《阿里最新Java编程规范泰山版》中《(二) 索引规约》中的说法：
中建议，这个前缀的长度为20比较合适，但是其实后面的“count(distinct left(列名, 索引长度))/count(*)的区分度来确定”会更合适点。在工程实践中具体如何做呢？
可以这样：
SELECTCOUNT(DISTINCTLEFT(order_note,3))/COUNT(*)ASsel3,COUNT(DISTINCTLEFT(order_note,4))/COUNT(*)ASsel4,COUNT(DISTINCTLEFT(order_note,5))/COUNT(*)ASsel5,COUNT(DISTINCTLEFT(order_note,6))/COUNT(*)Assel6,COUNT(DISTINCTLEFT(order_note,7))/COUNT(*)Assel7,COUNT(DISTINCTLEFT(order_note,8))/COUNT(*)Assel8,COUNT(DISTINCTLEFT(order_note,9))/COUNT(*)Assel9,COUNT(DISTINCTLEFT(order_note,10))/COUNT(*)Assel10,COUNT(DISTINCTLEFT(order_note,11))/COUNT(*)Assel11,COUNT(DISTINCTLEFT(order_note,12))/COUNT(*)Assel12,COUNT(DISTINCTLEFT(order_note,13))/COUNT(*)Assel13,COUNT(DISTINCTLEFT(order_note,14))/COUNT(*)Assel14,COUNT(DISTINCTLEFT(order_note,15))/COUNT(*)Assel15,COUNT(DISTINCTorder_note)/COUNT(*)AstotalFROMorder_exp;
可以看见，从第10个开始选择性的增加值很高，随着前缀字符的越来越多，选择度也在不断上升，但是增长到第15时，已经和第14没太大差别了，选择性提升的幅度已经很小了，都非常接近整个列的选择性了。
那么针对这个字段做前缀索引的话，从第13到第15都是不错的选择，甚至第12也不是不能考虑。
在上面的示例中，已经找到了合适的前缀长度，如何创建前缀索引:
ALTERTABLEorder_expADDKEY(order_note(14));建立前缀索引后查询语句并不需要更改：
select*fromorder_expwhereorder_note='xxxx';前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
有时候后缀索引 (suffix index)也有用途（例如，找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。
