【MySQL】高性能表结构及索引设计最佳实践（上）
一、前置知识点回顾
（1）深入理解MySQL索引底层数据结构与算法
索引底层数据结构红黑树、 B+树详解
面试常问的B树与B+树的区别是什么
索引在B+树上如何快速定位
千万级数据表如何用B+树索引快速查找
MylSAM与Innodb存储引擎底层索引实现区别
聚集索引、聚簇索引与稀疏索引到底是什么
为什么推荐使用 自增整型的主键而不是UUID
很少使用的索引底层结构Hash是怎样的
联合索引底层数据存储结构又是怎样的
索引最左前缀原则底层实现原理
（2）Explain详解与索引最佳实践
MySQL执行计划 Explain工具详解
MySQL优化经常用到的覆盖索引详解
从B+树底层来分析下常见索引优化规则
经常用到的 like查询应该如何优化
索引优化最佳实践
（3）SQL底层执行原理详解
梳理下MySQL内部组件结构
为什么说MySQL的查询缓存很鸡肋
MySQL词法分析器原理详解
MySQL底层优化器与执行器详解
MySQL归档日志 bin-log详解
不小心删库了如何快速恢复
（4）MySQL索引优化实战-上
MySQL索引下推优化详解
为什么范围查找MySQL没有用索引下推优化
MySQL内部选择索引机制揭秘
MySQL索引成本计算工具trace详解
看下常用的 Order by与Group by优化细节
Using filesort文件排序原理详解
文件 单路排序 与 双路排序详细过程
文件排序优化机制详解
互联网公司索引设计核心原则
社交场景APP索引设计优化实战
（5）MySQL索引优化实战-下
最常用的分页查询如何高效优化
Join表关联查询优化
表关联嵌套循环连接Nested-Loop Join( NLJ)算法详解
基于块的嵌套循环连接Block Nested-Loop Join( BNL)算法
in和exsits优化细节小表驱动大表详解
count查询的各种形式优化细节
阿里巴巴MySQL优化规范详解
MySQL数据类型选择优化
（6）深入理解MySQL事务隔离级别与锁机制
MySQL事务及其ACID属性详解
MySQL事务隔离级别详解
MySQL底层锁机制详解
实例演示各种事务隔离级别效果
MySQL底层脏读与幻读如何解决
MySQL底层间隙锁(Gap Lock)详解与优化
MySQL底层临键锁(Next-key Locks)详解
lnnoDB的行锁到底锁的是什么
（7）深入理解MVCC与BufferPoll缓存机制
【MySQL】深入理解MVCC与BufferPoll缓存机制
彻底理解 MVCC多版本并发控制机制
undo日志版本链与 read view机制详解
通过实例演示理解MVCC内部版本链比对规则
lnnodb引擎SQL执行的 BufferPool缓存机制
二、数据库表的设计
良好的表结构设计是高性能的基石，应该根据系统将要执行的业务查询来设计，这往往需要权衡各种因素。糟糕的表结构设计，会浪费大量的开发时间，严重延误项目开发周期，让人痛苦万分，而且直接影响到数据库的性能，并需要花费大量不必要的优化时间，效果往往还不怎么样。
在数据库表设计上有个很重要的设计准则，称为——范式设计。
范式设计
1.什么是范式？
范式的英文是Normal Form，所以我们通常把它称为——NF。
MySQL是关系型数据库，想要设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格！
目前关系数据库有六种范式：
第一范式（1NF）
第二范式（2NF）
第三范式（3NF）
巴斯-科德范式（BCNF）
第四范式(4NF）
第五范式（5NF，又称完美范式）
它们一级比一级严格，而且是像上图这种包含关系，即：2NF一定满足1NF、3NF一定满足2NF......
2.第一范式
定义：属于第一范式关系的所有属性都不可再分，即数据项不可分。
实际上，1NF是所有关系型数据库的最基本要求。
例如SQL Server，Oracle，MySQL中创建数据表的时候，如果数据表的设计不符合这个最基本的要求，那么操作一定是不能成功的。只要是关系型数据库，必然“天生”满足1NF。
3.第二范式
定义：如果关系模式R是第一范式的，而且关系中每一个非主属性不部分依赖于主键，称R是第二范式的。
通俗点来说就是：表中只具有一个业务主键，而且第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性！
如下图所示：
左边的一个订单有多个产品，所以订单的主键为【订单ID】和【产品ID】组成的联合主键，这样2个组件不符合第二范式，而且产品ID和订单ID没有强关联，故，把订单表进行拆分为订单表与订单与商品的中间表。
4.第三范式
定义：指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主键对主键的传递依赖。
举个例子：
存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。
再看个例子：
有一个订单表和一个产品表，如下所示
产品 ID与订单编号存在关联关系
产品名称与订单编号存在关联关系
产品ID与产品名称存在关联关系
订单表里如果如果产品ID发生改变，同一个表里产品名称也要跟着改变，这样不符合第三范式，应该把产品名称这一列从订单表中删除。
反范式设计
1.什么叫反范式化设计？
完全符合范式化的设计真的完美无缺吗？很明显在实际的业务查询中会大量存在着表的关联查询，而大量的表关联很多的时候非常影响查询的性能。
所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量得冗余。
2.反范式化设计案例分析
关于商品，我们设计了3张表——商品信息表、分类信息表、商品分类对应关系表（遵循第三范式）
当我们想要get到3张表里面的数据，就需要用关联查询将其全部关联起来！
在阿里的开发手册中提到，关联查询最好不要超过3张表，而如果我们一味的追求范式化设计，那么查询性能可能会很差！
所以，有的时候适当违反范式化设计是还有必要的！
商品信息和分类信息经常一起查询，所以把分类信息也放到商品表里面，冗余存放。
3.范式化设计优缺点
优点
1.范式化的更新操作比较快
2.重复数据少（或者没有）；如果需要修改，只需要修改更少的数据
3.范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
4.很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。在非范式化的结构中必须使用DISTINCT或者GROUPBY才能获得一份唯一的列表，但是如果是一张单独的表，很可能则只需要简单的查询这张表就行了。
缺点
最大的缺点就是需要关联。
稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联，可能会导致索引失效。
4.反范式化设计优缺点
优点
1.反范式设计可以减少表的关联
2.可以更好的进行索引优化（基本是单表）
缺点
1.存在数据冗余及数据维护异常
2.对数据的修改需要更多的成本
5.实际工作中的反范式实现
没有绝对的事情！在开发中，不会有完全的范式化，也不会有完全的反范式化。两者之间如何取舍？我们只看利弊！
在开发初期，业务体系尚未固定的时候，建议在设计表、新建表的时候先按照范式化的要求来！之后项目上线后，如果因为表结构设计而出现性能问题，才开始考虑反范式化设计！！！
于此，我们有如下几种手段，来进行相应的反范式化设计。
冗余、缓存和汇总
最常见的反范式化数据的方法是复制（冗余）或者缓存，在不同的表中存储相同的特定列。
比如从父表冗余一些数据到子表的。前面我们看到的分类信息放到商品表里面进行冗余存放就是典型的例子。（减少关联查询）
汇总表也是比较常用的方式。
比如，我们在某一个报表业务中需要的数据是经过很多张表关联，且有大量的order by、group by语句。这个时候，我们可以搞一个汇总表，将上述的内容用一个定时任务查询出来写入这个汇总表里面。
计数器表
计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下载次数等。对于高并发下的处理，首先可以创建一张独立的表存储计数器，这样可使计数器表小且快，并且可以使用一些更高级的技巧。
比如假设有一个计数器表，只有一行数据，记录网站的点击次数，网站的每次点击都会导致对计数器进行更新，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行，会严重限制系统的并发能力。
所以，我们可以将计数器保存在多行中，每次随机选择一行进行更新。在具体实现上，可以增加一个槽（slot)字段，然后预先在这张表增加100行或者更多数据，当对计数器更新时，选择一个随机的槽（slot)进行更新即可，需要统计的时候求和即可。
这种解决思路其实就是 写热点的分散，在JDK的JDK1.8中新的原子类 LongAdder也是这种处理方式，而我们在实际的缓冲中间件Redis等的使用、架构设计中，可以采用这种写热点的分散的方式，当然架构设计中对于写热点还有削峰填谷的处理方式，这种在MySQL的实现中也有体现，我们后面会讲到。
分库分表中的查询
对于一个商城项目，用户与商家是不同的维度，一般我们会按照这种维度进行分库分表。
但是对于交易记录这种东西，在买家中有、在卖家中也需要，是放在哪里呢？如果完全按照范式化设计，这个交易记录只能放在“一边”，或者单独再新建一张表。
通用的解决方案是，在交易生成时生成一份按照买家分片的数据副本和一份按照卖家分片的数据副本。这样子不管是卖家还是买家都可以很轻松的查到，这种冗余也是反范式化设计的体现。
三、字段数据类型优化
请看下面的文章
【MySQL】MySQL数据类型选择 与 阿里巴巴MySQL规范解读概要
四、高性能的索引创建策略
示例表
地址：MySQL/高性能表结构及索引设计最佳实践 · master · 金鳞踏雨 / 博客文章资源 · GitCode
在后续内容中会提到order_exp，还有它的几个派生表如s1，s2，order_exp_cut，相应的SQL文件见上述地址。
order_exp表结构：
关于InnoDB、索引的相关知识
在下述这一篇文章中已经将的很清楚了，就不再赘述~
前置知识：索引、聚集索引/聚簇索引、辅助索引/二级索引、回表
这些东西都在前面的章节讲过，这里也不再赘述。
MRR（新）
对于二级索引，B+树的叶子节点中只存放主键值，故需要获取主键所在行的其它字段信息就需要回表。
每次从二级索引中读取到一条记录后，就会根据该记录的主键值执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的，也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。（随机IO）
每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机IO带来的性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR-多范围读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作。相对于每读取一条二级索引记录就立即执行回表操作，这样会节省一些IO开销。
但是，使用这个 MRR优化措施的条件比较苛刻！！！
联合索引/复合索引
之前讲过：【MySQL】深入理解MySQL索引底层数据结构与算法
注意：一个索引对应一棵B+树。
自适应哈希索引（新）
InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知道B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般为3~4层，故需要3~4次的IO查询。
所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。
InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。需要注意的是：自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。
SHOW ENGINE INNODB STATUS
哈希索引只能用来搜索等值的查询。而对于其他查找类型，如范围查找，是不能使用哈希索引的。
因此这里会显示non-hash searches/s的统计情况。通过 hash searches: non-hash searches可以大概了解使用哈希索引后的效率。
由于AHI是由 InnoDB存储引擎控制的，因此这里的信息只供我们参考。不过我们可以通过观察 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。
InnoDB的三大特性
双写缓冲区（DoubleWrite Buffer）
AHI 自适应哈希
Buffer Pool
可以参考这篇文章：InnoDB 引擎三大特性_乐之终曲的博客-CSDN博客
