漫谈数据库表设计及索引设计

一.数据库表设计

在数据库表设计上有个很重要的设计准则&＃xff0c;称为范式设计。

什么是范式设计&＃xff1f;
范式来自英文Normal Form&＃xff0c;简称NF。MySQL是关系型数据库&＃xff0c;但是要想设计—个好的关系&＃xff0c;必须使关系满足一定的约束条件&＃xff0c;此约束已经形成了规范&＃xff0c;分成几个等级&＃xff0c;一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的&＃xff0c;同时&＃xff0c;不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟&＃xff0c;不仅给数据库的编程人员制造麻烦&＃xff0c;而且面目可憎&＃xff0c;可能存储了大量不需要的冗余信息。
目前关系数据库有六种范式&＃xff1a;第一范式&＃xff08;1NF&＃xff09;、第二范式&＃xff08;2NF&＃xff09;、第三范式&＃xff08;3NF&＃xff09;、巴斯-科德范式&＃xff08;BCNF&＃xff09;、第四范式(4NF&＃xff09;和第五范式&＃xff08;5NF&＃xff0c;又称完美范式&＃xff09;。满足最低要求的范式是第一范式&＃xff08;1NF&＃xff09;。在第一范式的基础上进一步满足更多规范要求的称为第二范式&＃xff08;2NF&＃xff09;&＃xff0c;其余范式以次类推。一般来说&＃xff0c;数据库只需满足第三范式(3NF&＃xff09;就行了。

1.1 第一范式

所有属性都不可再分&＃xff0c;即数据项不可分&＃xff0c;强调数据表的原子性&＃xff0c;是其他范式的基础。
比如一个表中需要记录一个人的姓名和年龄&＃xff0c;那么只能将name和名称拆分成两列&＃xff0c;id&＃xff0c;name&＃xff0c;age&＃xff0c;而不能设计为id&＃xff0c;name-age。
如果仅仅使用第一范式来规范表格是远远不够的&＃xff0c;依然会出现问题&＃xff0c;此时需要引入规范化概念&＃xff0c;将其转换为更标准的表格&＃xff0c;减少数据依赖。第一范式是关系型数据库最基本的要求&＃xff0c;如果数据库表的设计不符合这个最基本的要求&＃xff0c;那么操作一定是不能成功的。

1.2 第二范式

第二范式是在第一范式的基础上建立起来的&＃xff0c;要求数据库表中的每个实例或行必须可以被唯一地区分。即需要为表加上一个列&＃xff0c;以实现各个实例的唯一标识&＃xff0c;这个唯一属性列被称为主关键字或主码。还要求实体的属性完全依赖于主关键字。所谓完全依赖指不能存在仅仅依赖主关键字一部分的属性。如两张表

一个订单有多个产品&＃xff0c;所以订单的主键为 订单ID和产品ID组成的联合主键&＃xff0c;不符合第二范式&＃xff0c;而且产品ID和订单ID没有强关联&＃xff0c;所以把订单表进行拆分为订单表与订单与商品的中间表

中间表&＃xff1a;

1.3 第三范式

指每一个非主属性既不部分依赖于也不传递依赖于业务主键&＃xff0c;也就是在第二范式的基础上消除了非主键对主键的传递依赖。例如&＃xff0c;存在一个部门信息表&＃xff0c;其中每个部门有部门编号&＃xff08;dept_id&＃xff09;、部门名称、部门介绍等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表&＃xff0c;则根据第三范式&＃xff08;3NF&＃xff09;也应该构建它&＃xff0c;否则就会有大量的数据冗余

如果员工表中部门表的id发生变化&＃xff0c;那么部门名称也会发生变化&＃xff0c;这不符合第三范式&＃xff0c;应该把部门名称这一列从员工表中删除。

1.4 反范式设计

如果完全符合三范式&＃xff0c;也是有问题的&＃xff0c;在实际的业务查询中会大量存在着表的关联查询&＃xff0c;而大量的表关联很多的时候非常影响查询的性能。所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量得冗余&＃xff0c;换句话来说反范式化就是使用空间来换取时间。

例如&＃xff1a;商品信息表&＃xff1a;

分类表&＃xff1a;

商品和分类对应表

商品信息和分类信息经常一起查询&＃xff0c;所以把分类信息也放到商品表里面&＃xff0c;冗余存放。

范式化设计优缺点

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

范式化设计的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联&＃xff0c;也许更多。这不但代价昂贵&＃xff0c;也可能使一些索引策略无效。例如&＃xff0c;范式化可能将列存放在不同的表中&＃xff0c;而这些列如果在一个表中本可以属于同一个索引。

反范式化设计优缺点

1. 反范式设计可以减少表的关联
2. 可以更好的进行索引优化。

反范式设计缺点也很明显&＃xff0c;1、存在数据冗余及数据维护异常&＃xff0c;2、对数据的修改需要更多的成本。

1.5 实际工作中的反范式实现

范式化和反范式化的各有优劣&＃xff0c;怎么选择最佳的设计?
完全的范式化和完全的反范式化设计都是实验室里才有的东西&＃xff0c;在真实世界中很少会这么极端地使用。在实际应用中经常需要混用。
性能提升-缓存和汇总
最常见的反范式化数据的方法是复制或者缓存&＃xff0c;在不同的表中存储相同的特定列。
比如从父表冗余一些数据到子表的。前面我们看到的分类信息放到商品表里面进行冗余存放就是典型的例子。
缓存衍生值也是有用的。如果需要显示每个用户发了多少消息&＃xff0c;可以每次执行一个对用户发送消息进行count的子查询来计算并显示它&＃xff0c;也可以在user表用户中建一个消息发送数目的专门列&＃xff0c;每当用户发新消息时更新这个值。
有需要时创建一张完全独立的汇总表或缓存表也是提升性能的好办法。“缓存表”来表示存储那些可以比较简单地从其他表获取&＃xff08;但是每次获取的速度比较慢&＃xff09;数据的表&＃xff08;例如&＃xff0c;逻辑上冗余的数据)。而“汇总表”时,则保存的是使用GROUP BY语句聚合数据的表。
在使用缓存表和汇总表时&＃xff0c;有个关键点是如何维护缓存表和汇总表中的数据&＃xff0c;常用的有两种方式&＃xff0c;实时维护数据和定期重建&＃xff0c;这个取决于应用程序&＃xff0c;不过一般来说&＃xff0c;缓存表用实时维护数据更多点&＃xff0c;往往在一个事务中同时更新数据本表和缓存表&＃xff0c;汇总表则用定期重建更多&＃xff0c;使用定时任务对汇总表进行更新。
性能提升-计数器表
计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下载次数等。对于高并发下的处理&＃xff0c;首先可以创建一张独立的表存储计数器&＃xff0c;这样可使计数器表小且快&＃xff0c;并且可以使用一些更高级的技巧。
比如假设有一个计数器表&＃xff0c;只有一行数据&＃xff0c;记录网站的点击次数&＃xff0c;网站的每次点击都会导致对计数器进行更新&＃xff0c;问题在于&＃xff0c;对于任何想要更新这一行的事务来说&＃xff0c;这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行&＃xff0c;会严重限制系统的并发能力。
怎么改进呢&＃xff1f;可以将计数器保存在多行中&＃xff0c;每次随机选择一行进行更新。在具体实现上&＃xff0c;可以增加一个槽&＃xff08;slot)字段&＃xff0c;然后预先在这张表增加100行或者更多数据&＃xff0c;当对计数器更新时&＃xff0c;选择一个随机的槽&＃xff08;slot)进行更新即可。
这种解决思路其实就是写热点的分散&＃xff0c;在JDK的JDK1.8中新的原子类LongAdder也是这种处理方式&＃xff0c;而我们在实际的缓冲中间件Redis等的使用、架构设计中&＃xff0c;可以采用这种写热点的分散的方式&＃xff0c;当然架构设计中对于写热点还有削峰填谷的处理方式&＃xff0c;这种在MySQL的实现中也有体现。

反范式设计在分库分表中的查询
例如,用户购买了商品,需要将交易记录保存下来,那么如果按照买家的纬度分表,则每个买家的交易记录都被保存在同一表中, 我们可以很快、 很方便地査到某个买家的购买情况, 但是某个商品被购买的交易数据很有可能分布在多张表中, 査找起来比较麻烦 。 反之, 按照商品维度分表, 则可以很方便地査找到该商品的购买情况, 但若要査找到买家的交易记录, 则会比较麻烦 。
所以常见的解决方式如下。
( 1 ) 在多个分片表查询后合并数据集, 这种方式的效率很低。
( 2 ) 记录两份数据, 一份按照买家纬度分表, 一份按照商品维度分表,
( 3 ) 通过搜索引擎解决, 但如果实时性要求很高, 就需要实现实时搜索
在某电商交易平台下, 可能有买家査询自己在某一时间段的订单, 也可能有卖家査询自已在某一时间段的订单, 如果使用了分库分表方案, 则这两个需求是难以满足的, 因此, 通用的解决方案是, 在交易生成时生成一份按照买家分片的数据副本和一份按照卖家分片的数据副本,查询时分别满足之前的两个需求,因此,查询的数据和交易的数据可能是分别存储的,并从不同的系统提供接口。

二. 索引

2.1 聚集索引/聚簇索引

InnoDB中使用了聚集索引&＃xff0c;就是将表的主键用来构造一棵B&＃43;树&＃xff0c;并且将整张表的行记录数据存放在该B&＃43;树的叶子节点中。也就是所谓的索引即数据&＃xff0c;数据即索引。由于聚集索引是利用表的主键构建的&＃xff0c;所以每张表只能拥有一个聚集索引。
聚集索引的叶子节点就是数据页。换句话说&＃xff0c;数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是&＃xff1a;通过聚集索引能获取完整的整行数据。另一个优点是&＃xff1a;对于主键的排序查找和范围查找速度非常快。
如果我们没有定义主键呢&＃xff1f;MySQL会使用唯一性索引&＃xff0c;没有唯一性索引&＃xff0c;MySQL也会创建一个隐含列RowID来做主键&＃xff0c;然后用这个主键来建立聚集索引。

2.2 辅助索引/二级索引

上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用&＃xff0c;因为B&＃43;树中的数据都是按照主键进行排序的,那如果我们想以别的列作为搜索条件怎么办&＃xff1f;我们一般会建立多个索引&＃xff0c;这些索引被称为辅助索引/二级索引。
对于辅助索引(Secondary Index&＃xff0c;也称二级索引、非聚集索引)&＃xff0c;叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外&＃xff0c;每个叶子节点中的索引行中还包含了相应行数据的聚集索引键。

比如辅助索引index(node)&＃xff0c;那么叶子节点中包含的数据就包括了(主键、note)。

回表
辅助索引的存在并不影响数据在聚集索引中的组织&＃xff0c;因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时&＃xff0c;InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键&＃xff0c;然后再通过主键索引&＃xff08;聚集索引&＃xff09;来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B&＃43;树----一次辅助索引&＃xff0c;一次聚集索引。

为什么我们还需要一次回表操作呢?直接把完整的用户记录放到辅助索引的叶子节点不就好了么&＃xff1f;如果把完整的用户记录放到叶子节点是可以不用回表&＃xff0c;但是太占地方了&＃xff0c;相当于每建立一棵B&＃43;树都需要把所有的用户记录再都拷贝一遍&＃xff0c;这就有点太浪费存储空间了。而且每次对数据的变化要在所有包含数据的索引中全部都修改一次&＃xff0c;性能也非常低下。
很明显&＃xff0c;回表的记录越少&＃xff0c;性能提升就越高&＃xff0c;需要回表的记录越多&＃xff0c;使用二级索引的性能就越低&＃xff0c;甚至让某些查询宁愿使用全表扫描也不使用二级索引。
那什么时候采用全表扫描的方式&＃xff0c;什么时候使用采用二级索引 &＃43; 回表的方式去执行查询呢&＃xff1f;这个就是查询优化器做的工作&＃xff0c;查询优化器会事先对表中的记录计算一些统计数据&＃xff0c;然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数&＃xff0c;需要回表的记录数越多&＃xff0c;就越倾向于使用全表扫描&＃xff0c;反之倾向于使用二级索引 &＃43; 回表的方式。

MRR
从上文可以看出&＃xff0c;每次从二级索引中读取到一条记录后&＃xff0c;就会根据该记录的主键值执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的&＃xff0c;也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。
每次执行回表操作时都相当于要随机读取一个聚簇索引页面&＃xff0c;而这些随机IO带来的性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR&＃xff0c;多范围读取)的优化措施&＃xff0c;即先读取一部分二级索引记录&＃xff0c;将它们的主键值排好序之后再统一执行回表操作。
相对于每读取一条二级索引记录就立即执行回表操作&＃xff0c;这样会节省一些IO开销。使用这个 MRR优化措施的条件比较苛刻&＃xff0c;所以我们直接认为每读取一条二级索引记录就立即执行回表操作。

2.3 联合索引/复合索引

前面我们对索引的描述&＃xff0c;隐含了一个条件&＃xff0c;那就是构建索引的字段只有一个&＃xff0c;但实践工作中构建索引的完全可以是多个字段。所以&＃xff0c;将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引&＃xff0c;比如index(a,b)就是将a,b两个列组合起来构成一个索引。
千万要注意一点&＃xff0c;建立联合索引只会建立1棵B&＃43;树&＃xff0c;多个列分别建立索引会分别以每个列则建立B&＃43;树&＃xff0c;有几个列就有几个B&＃43;树&＃xff0c;比如&＃xff0c;index(note)、index(b)&＃xff0c;就分别对note,b两个列各构建了一个索引。
index(note,b)在索引构建上&＃xff0c;包含了两个意思&＃xff1a;
1、先把各个记录按照note列进行排序。
2、在记录的note列相同的情况下&＃xff0c;采用b列进行排序

2.4 自适应哈希索引

InnoDB存储引擎除了我们前面所说的各种索引&＃xff0c;还有一种自适应哈希索引&＃xff0c;我们知道B&＃43;树的查找次数,取决于B&＃43;树的高度,在生产环境中,B&＃43;树的高度一般为3~4层&＃xff0c;故需3&＃xff5e;4次的IO查询。
所以在InnoDB存储引擎内部自己去监控索引表&＃xff0c;如果监控到某个索引经常用&＃xff0c;那么就认为是热数据&＃xff0c;然后内部自己创建一个hash索引&＃xff0c;称之为自适应哈希索引( Adaptive Hash Index,AHI)&＃xff0c;创建以后&＃xff0c;如果下次又查询到这个索引&＃xff0c;那么直接通过hash算法推导出记录的地址&＃xff0c;直接一次就能查到数据&＃xff0c;比重复去B&＃43;tree索引中查询三四次节点的效率高了不少。
InnoDB存储引擎使用的哈希函数采用除法散列方式&＃xff0c;其冲突机制采用链表方式。注意&＃xff0c;对于自适应哈希索引仅是数据库自身创建并使用的&＃xff0c;我们并不能对其进行干预。通过命令show engine innodb status\G可以看到当前自适应哈希索引的使用状况。
哈希索引只能用来搜索等值的查询,如 SELECT* FROM table WHERE index co&＃61;xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的,

由于AHI是由 InnoDB存储引擎控制的,因此这里的信息只供我们参考。不过我们可以通过观察 SHOW ENGINE INNODB STATUS的结果及参数 innodb_adaptive_hash_index来考虑是禁用或启动此特性,默认AHI为开启状态。
什么时候需要禁用呢&＃xff1f;如果发现监视索引查找和维护哈希索引结构的额外开销远远超过了自适应哈希索引带来的性能提升就需要关闭这个功能。
同时在MySQL 5.7中&＃xff0c;自适应哈希索引搜索系统被分区。每个索引都绑定到一个特定的分区&＃xff0c;每个分区都由一个单独的 latch 锁保护。分区由 innodb_adaptive_hash_index_parts 配置选项控制 。在早期版本中&＃xff0c;自适应哈希索引搜索系统受到单个 latch 锁的保护&＃xff0c;这可能成为繁重工作负载下的争用点。innodb_adaptive_hash_index_parts 默认情况下&＃xff0c;该选项设置为8。最大设置为512。当然禁用或启动此特性和调整分区个数这个应该是DBA的工作&＃xff0c;我们了解即可。

InnoDB三大特性&＃xff1a;自适应hash&＃xff0c;双写缓冲区&＃xff0c;bufferpool

2.5 全文检索之倒排索引

倒排索引就是&＃xff0c;将文档中包含的关键字全部提取处理&＃xff0c;然后再将关键字和文档之间的对应关系保存起来&＃xff0c;最后再对关键字本身做索引排序。用户在检索某一个关键字时&＃xff0c;先对关键字的索引进行查找&＃xff0c;再通过关键字与文档的对应关系找到所在文档。
MySQL 5.6 以前的版本&＃xff0c;只有 MyISAM 存储引擎支持全文索引。从InnoDB 1.2.x版本开始&＃xff0c;InnoDB存储引擎开始支持全文检索&＃xff0c;对应的MySQL版本是5.6.x系列&＃xff0c;官方文档https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html。
注意&＃xff0c;不管什么引擎&＃xff0c;只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
不过MySQL从设计之初就是关系型数据库&＃xff0c;存储引擎虽然支持全文检索&＃xff0c;整体架构上对全文检索支持并不好而且限制很多&＃xff0c;比如每张表只能有一个全文检索的索引&＃xff0c;不支持没有单词界定符( delimiter&＃xff09;的语言&＃xff0c;如中文、日语、韩语等。
所以如果有大批量或者专门的全文检索需求&＃xff0c;还是应该选择专门的全文检索引擎。
创建
创建表时创建全文索引

create table fulltext_test (
id int(11) NOT NULL AUTO_INCREMENT,
content text NOT NULL,
tag varchar(255),
PRIMARY KEY (id),
FULLTEXT KEY content_tag_fulltext(content,tag)
) DEFAULT CHARSET&＃61;utf8;


在已存在的表上创建全文索引

create fulltext index content_tag_fulltext on fulltext_test(content,tag);


通过 SQL 语句 ALTER TABLE 创建全文索引

alter table fulltext_test add fulltext index content_tag_fulltext(content,tag);


和常用的模糊匹配使用 like &＃43; % 不同&＃xff0c;全文索引有自己的语法格式&＃xff0c;使用 match 和 against 关键字&＃xff0c;比如

select * from fulltext_test where match(content,tag) against(&＃39;xxx xxx&＃39;);


总结&＃xff1a;MySQL有哪些索引类型

• 从数据结构角度可分为B&＃43;树索引、哈希索引、以及FULLTEXT索引&＃xff08;现在MyISAM和InnoDB引擎都支持了&＃xff09;和R-Tree索引&＃xff08;用于对GIS数据类型创建SPATIAL索引&＃xff09;&＃xff1b;
• 从物理存储角度可分为聚集索引&＃xff08;clustered index&＃xff09;、非聚集索引&＃xff08;non-clustered index&＃xff09;&＃xff1b;
• 从逻辑角度可分为主键索引、普通索引&＃xff0c;或者单列索引、多列索引、唯一索引、非唯一索引等等。

2.6 密集索引和稀疏索引的区别

密集索引的定义&＃xff1a;叶子节点保存的不只是键值&＃xff0c;还保存了位于同一行记录里的其他列的信息&＃xff0c;由于密集索引决定了表的物理排列顺序&＃xff0c;一个表只有一个物理排列顺序&＃xff0c;所以一个表只能创建一个密集索引。
稀疏索引&＃xff1a;叶子节点仅保存了键位信息以及该行数据的地址&＃xff0c;有的稀疏索引只保存了键位信息机器主键。
mysam存储引擎&＃xff0c;不管是主键索引&＃xff0c;唯一键索引还是普通索引都是稀疏索引&＃xff0c;innodb存储引擎&＃xff1a;有且只有一个密集索引。
所以&＃xff0c;密集索引就是innodb存储引擎里的聚簇索引&＃xff0c;稀疏索引就是innodb存储引擎里的普通二级索引。

2.7 索引覆盖

即从辅助索引中就可以得到查询的记录&＃xff0c;而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息&＃xff0c;故其大小要远小于聚集索引&＃xff0c;因此可以减少大量的IO操作。所以记住&＃xff0c;覆盖索引可以视为索引优化的一种方式&＃xff0c;而并不是索引类型的一种。

三. 索引在查询中的使用

1、一个索引就是一个B&＃43;树&＃xff0c;索引让我们的查询可以快速定位和扫描到我们需要的数据记录上&＃xff0c;加快查询的速度。
2、一个select查询语句在执行过程中一般最多能使用一个二级索引来加快查询&＃xff0c;即使在where条件中用了多个二级索引。

3.1 索引的代价

1.空间上的代价
这个是显而易见的&＃xff0c;每建立一个索引都要为它建立一棵B&＃43;树&＃xff0c;每一棵B&＃43;树的每一个节点都是一个数据页&＃xff0c;一个页默认会占用16KB的存储空间&＃xff0c;一棵很大的B&＃43;树由许多数据页组成会占据很多的存储空间。
2.时间上的代价
每次对表中的数据进行增、删、改操作时&＃xff0c;都需要去修改各个B&＃43;树索引。而且我们讲过&＃xff0c;B&＃43;树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录&＃xff0c;还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。
而增、删、改操作可能会对节点和记录的排序造成破坏&＃xff0c;所以存储引擎需要额外的时间进行一些记录移位&＃xff0c;页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引&＃xff0c;每个索引对应的B&＃43;树都要进行相关的维护操作&＃xff0c;这必然会对性能造成影响。
既然索引这么有用&＃xff0c;我们是不是创建越多越好&＃xff1f;既然索引有代价&＃xff0c;我们还是别创建了吧&＃xff1f;当然不是&＃xff01;按照经验&＃xff0c;一般来说&＃xff0c;一张表6-7个索引以下都能够取得比较好的性能权衡。

3.2 高性能的索引创建策略

1. 索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型&＃xff0c;以整数类型为例&＃xff0c;有TTNYINT、MEDUMNT、INT、BIGTNT这么几种&＃xff0c;它们占用的存储空间依次递增&＃xff0c;我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增&＃xff0c;如果我们想要对某个整数列建立索引的话&＃xff0c;在表示的整数范围允许的情况下&＃xff0c;尽量让索引列使用较小的类型&＃xff0c;比如我们能使用INT就不要使用BIGINT&＃xff0c;能使用NEDIUMINT就不要使用INT&＃xff0c;这是因为:
·数据类型越小&＃xff0c;在查询时进行的比较操作越快&＃xff08;CPU层次)
·数据类型越小&＃xff0c;索引占用的存储空间就越少&＃xff0c;在一个数据页内就可以放下更多的记录&＃xff0c;从而减少磁盘/0带来的性能损耗&＃xff0c;也就意味着可以把更多的数据页缓存在内存中&＃xff0c;从而加快读写效率。
这个建议对于表的主键来说更加适用&＃xff0c;因为不仅是聚簇索引中会存储主键值&＃xff0c;其他所有的二级索引的节点处都会存储一份记录的主键值&＃xff0c;如果主键适用更小的数据类型&＃xff0c;也就意味着节省更多的存储空间和更高效的I/0。

2. 创建索引应选择离散性高的列。

索引的选择性/离散性是指&＃xff0c;不重复的索引值&＃xff08;也称为基数&＃xff0c;cardinality)和数据表的记录总数&＃xff08;N)的比值&＃xff0c;范围从1/N到1之间。索引的选择性越高则查询效率越高&＃xff0c;因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1&＃xff0c;这是最好的索引选择性&＃xff0c;性能也是最好的。
很差的索引选择性就是列中的数据重复度很高&＃xff0c;比如性别字段&＃xff0c;不考虑政治正确的情况下&＃xff0c;只有两者可能&＃xff0c;男或女。那么我们在查询时&＃xff0c;即使使用这个索引&＃xff0c;从概率的角度来说&＃xff0c;依然可能查出一半的数据出来。

3. 前缀索引

有时候需要索引很长的字符列&＃xff0c;这会让索引变得大且慢&＃xff0c;我们可以使用前缀索引&＃xff0c;这样可以大大节约索引空间&＃xff0c;从而提高索引效率。但这样会降低索引的选择性&＃xff0c;一般情况下我们需要保证某个列前缀的选择性也是足够高的&＃xff0c;以满足查询性能。&＃xff08;尤其对于BLOB、TEXT或者很长的VARCHAR类型的列&＃xff0c;应该使用前缀索引&＃xff0c;因为MySQL不允许索引这些列的完整长度&＃xff09;。诀窍在于要选择足够长的前缀以保证较高的选择性&＃xff0c;同时又不能太长&＃xff08;以便节约空间)。前缀应该足够长&＃xff0c;以使得前缀索引的选择性接近于索引整个列。
阿里规范泰山版建议这个前缀长度为20比较合适&＃xff0c;可以使用“count(distinct left(列名, 索引长度))/count(*)的区分度来确定”。
前缀索引是一种能使索引更小、更快的有效办法&＃xff0c;但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY&＃xff0c;也无法使用前缀索引做覆盖扫描。
有时候后缀索引 (suffix index)也有用途&＃xff08;例如&＃xff0c;找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引&＃xff0c;但是可以把字符串反转后存储&＃xff0c;并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。

4. 只为用于搜索、排序或分组的列创建索引

只为出现在WHERE 子句中的列、连接子句中的连接列创建索引&＃xff0c;而出现在查询列表中的列一般就没必要建立索引了&＃xff0c;除非是需要使用覆盖索引。又或者为出现在ORDER BY或GROUP BY子句中的列创建索引。
如果有了索引的话&＃xff0c;恰巧这个分组顺序又和我们索引中的索引列的顺序是一致的&＃xff0c;而我们的B&＃43;树索引又是按照索引列排好序的&＃xff0c;这不正好么&＃xff0c;所以可以直接使用B&＃43;树索引进行分组。和使用B&＃43;树索引进行排序是一个道理&＃xff0c;分组列的顺序也需要和索引列的顺序一致。

5. 合理设计组合索引

在一个多列B-Tree索引中&＃xff0c;索引列的顺序意味着索引首先按照最左列进行排序&＃xff0c;其次是第二列&＃xff0c;等等。所以&＃xff0c;索引可以按照升序或者降序进行扫描&＃xff0c;以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。
所以多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则&＃xff1a;将选择性最高的列放到索引最前列。当不需要考虑排序和分组时&＃xff0c;将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下&＃xff0c;这样设计的索引确实能够最快地过滤出需要的行&＃xff0c;对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。
然而&＃xff0c;性能不只是依赖于索引列的选择性&＃xff0c;也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序&＃xff0c;比如排序和分组&＃xff0c;让这种情况下索引的选择性最高。
同时&＃xff0c;在优化性能的时候&＃xff0c;可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

6. 主键尽量是很少改变的列

行是按照聚集索引物理排序的&＃xff0c;如果主键频繁改变&＃xff0c;物理顺序会变&＃xff0c;mysql要不断调整B&＃43;树&＃xff0c;中间可能会出现页面的分裂和合并等&＃xff0c;会导致性能急剧下降。

7. 处理冗余和重复索引、未使用的索引

MySQL允许在相同列上创建多个索引&＃xff0c;无论是有意的还是无意的。MySQL需要单独维护重复的索引&＃xff0c;并且优化器在优化查询的时候也需要逐个地进行考虑&＃xff0c;这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引&＃xff0c;发现以后也应该立即移除。

8. 尽量使用三星索引

如果查询使用三星索引&＃xff0c;一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描&＃xff0c;因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。

三星索引概念是在《Rrelational Database Index Design and the optimizers》 一书&＃xff08;这本书也是《高性能MySQL》作者强烈推荐的一本书&＃xff09;中提出来的。原文如下&＃xff1a;
The index earns one star if it places relevant rows adjacent to each other,
a second star if its rows are sorted in the order the query needs,
and a final star if it contains all the columns needed for the query.
索引将相关的记录放到一起则获得一星&＃xff1b;
如果索引中的数据顺序和查找中的排列顺序一致则获得二星&＃xff1b;
如果索引中的列包含了查询中需要的全部列则获得三星。

二星&＃xff08;排序星&＃xff09;&＃xff1a;
在满足一星的情况下&＃xff0c;当查询需要排序&＃xff0c;group by、 order by&＃xff0c;如果查询所需的顺序与索引是一致的&＃xff08;索引本身是有序的&＃xff09;&＃xff0c;是不是就可以不用再另外排序了&＃xff0c;一般来说排序可是影响性能的关键因素。
三星&＃xff08;宽索引星&＃xff09;&＃xff1a;
在满足了二星的情况下&＃xff0c;如果索引中所包含了这个查询所需的所有列&＃xff08;包括 where 子句 和 select 子句中所需的列&＃xff0c;也就是覆盖索引&＃xff09;&＃xff0c;这样一来&＃xff0c;查询就不再需要回表了&＃xff0c;减少了查询的步骤和IO请求次数&＃xff0c;性能几乎可以提升一倍。
一星按照原文稍微有点难以理解&＃xff0c;其实它的意思就是&＃xff1a;如果一个查询相关的索引行是相邻的或者至少相距足够靠近的话&＃xff0c;必须扫描的索引片宽度就会缩至最短&＃xff0c;也就是说&＃xff0c;让索引片尽量变窄&＃xff0c;也就是我们所说的索引的扫描范围越小越好。
这三颗星&＃xff0c;哪颗最重要&＃xff1f;第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘随机读&＃xff08;回表操作&＃xff09;。第一和第二颗星重要性差不多&＃xff0c;可以理解为第三颗星比重是50%&＃xff0c;第一颗星为27%&＃xff0c;第二颗星为23%&＃xff0c;所以在大部分的情况下&＃xff0c;会先考虑第一颗星&＃xff0c;但会根据业务情况调整这两颗星的优先度。

四.总结

本文主要讲了数据库表设计的规范&＃xff0c;三范式&＃xff0c;反范式设计&＃xff0c;及其各自优缺点&＃xff0c;还介绍了索引&＃xff0c;包括聚集索引、二级索引、组合索引、倒排索引等&＃xff0c;以及对索引创建的优化策略&＃xff0c;利用各种手段提高数据库表设计的性能。