mysql优化八：高性能表结构及索引设计实践

数据库表结构设计

良好的表结构设计是高性能的基石，应该根据系统将要执行的业务查询来设计，这往往需要权衡各种因素。糟糕的表结构设计，会浪费大量的开发时间，严重延误项目开发周期，让人痛苦万分，而且直接影响到数据库的性能，并需要花费大量不必要的优化时间，效果往往还不怎么样。在数据库表设计上有个很重要的设计准则，称为范式设计

范式设计

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、 巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为 第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了。
1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

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

2．第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。

3.第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

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

反范式设计

完全符合范式化的设计真的完美无缺吗？很明显在实际的业务查询中会大量存在着表的关联查询，而大量的表关联很多的时候非常影响查询的性能。
所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行 违反。允许存在少量得冗余，换句话来说反范式化就是使用空间来换取时间。
按照上面的订单表和产品表。如果说每次查询订单都要带上产品信息的话，如果按照范式设计，那么订单表中就不会还有产品名称，而是通过产品id在产品表中定位到对应的产品名称。这样每次查询订单都要通过join方式关联产品表，进行联合查询，表多的话就会影响性能。所以说要适当的反范式设计。

范式设计和反范式设计的选择

范式设计优缺点
优点

1. 范式化的更新操作通常比反范式化要快
   当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
2. 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
3. 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP
   BY语句。在非范式化的结构中必须使用DISTINCT或者GROUPBY才能获得一份唯一的列表

缺点

反范式设计缺点也很明显，1、存在数据冗余及数据维护异常，2、对数据的修改需要更多的成本。
范式化和反范式化怎么选择最佳的设计
完全的范式化和完全的反范式化设计都是实验室里才有的东西，在真实世 界中很少会这么极端地使用。在实际应用中经常需要混用。
如果说新开发一个系统，那么建议表的设计尽量按照范式化设计。一个功能都没有实现，就考虑用户量、流量、并发等等上来以后关联查询怎么怎么样，这就想太多了。先把系统功能开发出来，按照范式化设计上线之后，根据实际的业务情况，确定了联合查询导致性能跟不上，在考虑反范式设计。

反范式化设计在工作中的实现

1、冗余、缓存和汇总
冗余：把需要join的信息放到父表中，比如说把商品信息也放到订单表中，这样订单中就不需要关联查询商品的信息。
缓存：比如说在一个博客系统中，需要统计用户发送了多篇博客，那么常用的方法就是通过count(*) 统计这个userID得到这个统计数，但是这个统计操作是一个经常需要的操作，那么可以用一个num字段设计到User表当中，每次user发表的时候这个num字段+1，以后只要查这个user表中的num字段就可以知道用户发表了多少博客。
汇总：比如说某些报表功能，需要很复杂sql进行group by等统计好几张表数据，可以提前通过定时任务把报表的数据统计好，放到一张统计表中，以后报表的数据通过统计表去查。
如果说按照上述的当时增加实现反范式设计的话，会出现数据一致性问题，要怎么去维护这些数据，怎么去做更新。按照上述的方式，更新策略主要有两种：要么实时维护，要么定期重建。
2、计数器表方式
计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下载次数等。对于 高并发下的处理，首先可以创建一张独立的表存储计数器，这样可使计数器表小且快， 并且可以使用一些更高级的技巧。
比如假设有一个计数器表，只有一行数据，记录网站的点击次数，网站的每次点击都会 导致对计数器进行更新，问题在于，对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行，会严重限制系统的并发能力。
那么我们可以这样处理，将计数器表中的一行记录，复制多行比如说复制出100行记录，然后每次更新都随机选择一条记录进行更新。这样的设计方式在架构方面叫做写热点分散。在JDK1.8中新的原子类LongAdder也是这种 处理方式，而我们在实际的缓冲中间件Redis等的使用、架构设计中，可以采用这种写热 点的分散的方式，当然架构设计中对于写热点还有削峰填谷的处理方式，这种在MySQL的 实现中也有体现，后面会讲到。
3、分库分表中的查询
例如,用户购买了商品,需要将交易记录保存下来,那么如果按照买家的纬度分表,则每个 买家的交易记录都被保存在同一表中, 我们可以很快、 很方便地査到某个买家的购买情况, 但是某个商品被购买的交易数据很有可能分布在多张表中, 査找起来比较麻烦 。
反之, 按照商品维度分表, 则可以很方便地査找到该商品的购买情况, 但若要査找到买家的交易记录, 则会比较麻烦 。 所以常见的解决方式如下。
( 1 ) 在多个分片表查询后合并数据集, 这种方式的效率很低。
( 2 ) 记录两份数据, 一份按照买家纬度分表, 一份按照商品维度分表
( 3 ) 通过搜索引擎解决, 但如果实时性要求很高, 就需要实现实时搜索

字段设计

在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：
（1）确定合适的大类型：数字、字符串、时间、二进制；
（2）确定具体的类型：有无符号、取值范围、变长定长等。
在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL。
对于整数型：

1. 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
2. 建议使用TINYINT代替ENUM、BITENUM、SET。
3. 避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用 INT。
4. DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意 长度设置。
5. 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
6. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

对于时间日期类：

1. MySQL能存储的最小时间粒度为秒。
2. 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
3. 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
4. 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），
   MySQL会自动返回记录插入的确切时间。
5. TIMESTAMP是UTC时间戳，与时区相关。
6. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
7. 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般
   会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
8. 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。

对于字符串类型：

1. 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
2. CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计
   算的准确性和完整性。
3. 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
4. BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
5. BLOB和TEXT都不能有默认值

索引设计

索引设计在之前的优化中有提到过，在这里做下补充和总结。
InnoDB中的索引是按照B+树来组织的，至于什么是B+树，B+树的数据结构在优化一中已经讲过不再重复。我们知道B+树的叶子节点用来放数据的，但是放什么数据呢？索引自然是要放的，因为B+树的作用本来就是就是为了快速检索数据 而提出的一种数据结构，不放索引放什么呢？但是数据库中的表，数据才是我们真正需要的数据，索引只是辅助数据，甚至于一个表可以没有索引。InnoDB中的数据到底是如何组织的？这个涉及到聚集索引(主键索引)和辅助索引(二级索引)的区别。

回表和MRR机制
回顾一下聚集索引(主键索引)和辅助索引(二级索引)的区别，这个之前有讲过。聚集索引的叶子节点保存了主键id以及对应行记录的所有字段。辅助索引的叶子节点保存了索引列以及主键id。
当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为回表
也就是说根据辅助索引来查询一条完整的用户记录需要使用到2棵B+树----一棵辅助索引，一棵聚集索引。
所以当我们回表次数多时，查询的效率就会低，有些时候通过explain查询，明明可以使用索引，但mysql执行引擎却没有使用，很大原因就是回表次数多了，还不如全盘扫描(这里涉及到mysql执行的成本计算，在下篇博客中再写)。
根据回表引出了MRR机制。

MRR机制
MRR全称：Disk-Sweep Multi-Range Read (多范围读取)。在了解MRR是什么之前，先回顾下辅助索引的查找。我们知道B+树具有有序性。我们通过辅助索引能够使用到B+树的有序性，但是查出来的主键id未必是有序的。此时通过无序的id主键进行回表扫描的话，此时的IO是随机IO。磁盘IO主要分为两大类：一个随机IO，一个顺序IO。顺序IO的效率大概是随机IO的两个数量级。因此MySQL提出了一个名为Disk-Sweep Multi-Range Read (MRR，多范围 读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作。
使用这个 MRR优化措施的条件比较苛刻，所以我们直接认为每读取一条二级索引记录就立即执行回表操作。MRR的详细信息，可以查询官方文档。
补充：为什么主键id无序时回表是会产生随机IO
首先磁盘读取数据通过扇区为单位进行读取的。一颗B+树它是有序的。比如说id 1到50 它是第一个扇区的。51到100是第二个扇区的以此类推。假设回表的id是这样的96、23、105、12、88等这些。那么磁盘读取的时候先读取第二个扇区，拿到96的数据，发现没有23的数据，在读取第一个扇区，拿到23的数据，发现没有105的数据，在读取第三个扇区。这样的IO就是随机IO。如果说通过MRR机制排好序了：12、23、88、96、105。读取第一个扇区的时候就能拿到12、23的数据，读取第二个扇区的时候就能拿到88、96的数据，减少了回表的次数。

索引的数据结构补充总结
这里补充说下自适应哈希索引和倒排索引
自适应哈希索引
InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3-4层,故需要3~4次的IO查询。
所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)。创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。
InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。
注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。通过命令show engine innodb status可以看到当前自适应哈希索引的使用 状况。

使用哈希有两个问题，一个是哈希的局限性，对于范围查询无能为力，只能用于等值查询。
另一个是当出现并发访问的时候会出现并发安全问题，解决并发安全问题就要加锁，加锁的话就会影响性能。因此在MySQL 5.7中，自适应哈希索引搜索系统被分区。每个索引都绑定到一个特定的分区，每个分区都由一个单独的 latch 锁保护。分区由 innodb_adaptive_hash_index_parts 配置选项控制 。如上图
由于AHI是由 InnoDB存储引擎控制的,因此这里的信息只供我们参考。不过我们可以通过观察 SHOW ENGINE INNODB STATUS的结果及参数 innodb_adaptive_hash_index来考虑是禁用或启动此特性,默认AHI为开启状态。
全文检索：倒排索引
什么是全文检索（Full-Text Search）？它是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。从InnoDB 1.2.x版本开 始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。
注意，不管什么引擎，只有字段的数据类型为 char、varchar、text 及其系列才可以建 全文索引。
不过MySQL从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构 上对全文检索支持并不好而且限制很多，所以就不展开描述。至于什么是倒排索引，这个在写ES的时候就有描述。不再重复。
MySQL索引类型总结
从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB 引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）；
从物理存储角度可分为聚集索引（clustered index）、非聚集索引（non-clustered index）；
从逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一索引等等。

索引代价
世界上从来没有只有好处没有坏处的东西。虽然索引是个好东西，在学习如何更好的使用索引之前先要了解一下使用它的代价，它在空间和时间上都会拖后腿。
空间上的代价
这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成会占据很多的存储空间。
时间上的代价
每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。 而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影响。
早期的mysql和硬件设施来说，一张表建立3-4个索引比较好，对于当前环境下的硬件设施和mysql，一般来说，一张表6-7个索引以下都能够取得比较好的性能权衡。

高性能的索引创建策略
正确地创建和使用索引是实现高性能查询的基础。

索引列的类型尽量小
我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、 NEDUMNT、INT、BIGTNT这么几种，它们占用的存储空间依次递增，我们这里所说的类型大小指的就是该类型表示的数据范围的大小。在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用NEDIUMINT就不要使用INT，这是因为:

1. 数据类型越小，在查询时进行的比较操作越快（CPU层次)
2. 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记
   录，从而减少磁盘IO带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中， 从而加快读写效率。

利用索引离散型和前缀索引

离散性
创建索引应该选择离散性高的列。什么是离散性，指的是mysql不重复的索引值 （也称为基数，cardinality)和数据表的记录总数（N)的比值，范围从1/N到1之间。
索引的离散性越高则查询效率越高，因为离散性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的离散性是1，这是最好的索引选择性，性能也是最好的。
怎么算索引的选择性/离散性？

很明显在订单表中给order_no加上索引比order_status的要好，order_no的离散性0.9676，order_status的离散性0.0001。因为订单状态来来去去也就那几种。
前缀索引
有些时候某个字段的值是字符串很长，需要加上索引。如果把全部的值都作为索引这会让索引变得大且慢。
比如说订单表有个order_note字段很长。我们可以增加一个 order_not_hash字段来存储order_note的哈希值，然后在order_not_hash上建立索引。
相对于之前的索引速度会有明显提升，一个是对完整的 order_note做索引，而后者则是用整数哈希值做索引，显然数字的比较比字符串的匹配要高效得多。 但是缺陷也很明显：

1. 需要额外维护order_not_hash字段；
2. 哈希算法的选择决定了哈希冲突的概率，不良的哈希算法会导致重复值很多；
3. 不支持范围查找。

那么还可以用什么方法优化呢？可以考虑之索引order_note的部分字符而不是全部字符。这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。一般情况下我们需要保证某个列前缀的离散性也是足够高的，以满足查询性能。（尤其对于BLOB、TEXT或者很长的VARCHAR类 型的列，应该使用前缀索引，因为MySQL不允许索引这些列的完整长度）。
诀窍在于要选择足够长的前缀以保证较高的离散性，同时又不能太长（以便节约空间)。
可以通过“count(distinct left(列名, 索引长度))/count(*);来进行判断。

根据上面几张图可以看到 当选择前16个字符的时候是性价比最高的时候，虽然选择18，它的离散性比16的高，但是之高了0.001。
在上面的示例中，已经找到了合适的前缀长度，如何创建前缀索引: ALTER TABLE order_exp ADD KEY (order_note(16));

只为用于搜索、排序或分组的列创建索引
也就是说，只为出现在WHERE 子句中的列、连接子句中的连接列创建索引，而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引。又或者为出现在 ORDER BY或GROUP BY子句中的列创建索引。主要也是避免了文件排序。这都比较好理解。

合理设计多列索引
很多人对多列索引的理解不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。
对于如何选择索引的列顺序有一个经验法则：将离散性最高的列放到索引最前列。当不需要考虑排序和分组时，将离散性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说离散性也更高。
然而，性能不只是依赖于索引列的离散性，也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组。
同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求

尽可能设计三星索引
三星索引概念是在《Rrelational Database Index Design and the optimizers》 一书 （这本书也是《高性能MySQL》作者强烈推荐的一本书）中提出来的。

• 索引将相关的记录放到一起则获得一星；
• 如果索引中的数据顺序和查找中的排列顺序一致则获得二星；
• 如果索引中的列包含了查询中需要的全部列则获得三星。

怎么去理解这三个星，说的简单点就是
第一星就是sql中带有范围或者等值等等查询，缩小相关范围，扫描范围越小越好；第二星(排序星)就是为排序、分组的字段设置索引，是得排序、分组使用的是索引而不是文件排序；第三星(宽索引星)就是创建的索引满足了覆盖索引条件，这样就能直接从一颗B+树放回数据，不用进行回表。
这三颗星，哪颗最重要？第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是50%， 第一颗星为27%，第二颗星为23%，所以在大部分的情况下，会先考虑第一颗星，但会根据业务情况调整这两颗星的优先度。

主键尽量不要修改
我们知道，行是按照聚集索引物理排序的，如果主键频繁改变(update)，物理顺序会改变，MySQL要不断调整B+树，并且中间可能会产生页面的分裂和合并等等，会导致性能会急剧降低。

处理冗余和重复索引
MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。
比如说创建了索引(A B)，再创建索引(A)就是冗余索引，因为A是索引(A,B)的最左前缀列。索引(AB)也可以当作索引(A)来使用（这种冗余只是对B+Tree索引来说的)。但是如果再创建索引 (B,A)，则不是冗余索引，索引 (B)也不是，因为B不是索引(A,B)的最左前缀列。 已有的索引(A)，扩展为(A，ID)，其中ID是主键，对于InnoDB来说主键列已经包含在二 级索引中了，所以这也是冗余的。
解决冗余索引和重复索引的方法很简单，删除这些索引就可以，但首先要做的是找出这 样的索引。可以通过写一些复杂的访问INFORMATION_SCHEMA表的查询来找。
除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议考虑删除。