优衣库网站建设的目的,医院网站建设公司价格,深圳平面设计公司招聘,网站架构 规划一.数据库表设计
在数据库表设计上有个很重要的设计准则#xff0c;称为范式设计。 什么是范式设计#xff1f; 范式来自英文Normal Form#xff0c;简称NF。MySQL是关系型数据库#xff0c;但是要想设计—个好的关系#xff0c;必须使关系满足一定的约束条件#xff0c…一.数据库表设计
在数据库表设计上有个很重要的设计准则称为范式设计。 什么是范式设计 范式来自英文Normal Form简称NF。MySQL是关系型数据库但是要想设计—个好的关系必须使关系满足一定的约束条件此约束已经形成了规范分成几个等级一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的同时不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟不仅给数据库的编程人员制造麻烦而且面目可憎可能存储了大量不需要的冗余信息。 目前关系数据库有六种范式第一范式1NF、第二范式2NF、第三范式3NF、巴斯-科德范式BCNF、第四范式(4NF和第五范式5NF又称完美范式。满足最低要求的范式是第一范式1NF。在第一范式的基础上进一步满足更多规范要求的称为第二范式2NF其余范式以次类推。一般来说数据库只需满足第三范式(3NF就行了。 1.1 第一范式
所有属性都不可再分即数据项不可分强调数据表的原子性是其他范式的基础。 比如一个表中需要记录一个人的姓名和年龄那么只能将name和名称拆分成两列idnameage而不能设计为idname-age。 如果仅仅使用第一范式来规范表格是远远不够的依然会出现问题此时需要引入规范化概念将其转换为更标准的表格减少数据依赖。第一范式是关系型数据库最基本的要求如果数据库表的设计不符合这个最基本的要求那么操作一定是不能成功的。
1.2 第二范式
第二范式是在第一范式的基础上建立起来的要求数据库表中的每个实例或行必须可以被唯一地区分。即需要为表加上一个列以实现各个实例的唯一标识这个唯一属性列被称为主关键字或主码。还要求实体的属性完全依赖于主关键字。所谓完全依赖指不能存在仅仅依赖主关键字一部分的属性。如两张表
订单表ID订单时间产品ID12023-02-261
产品表ID产品名称1java2C
一个订单有多个产品所以订单的主键为 订单ID和产品ID组成的联合主键不符合第二范式而且产品ID和订单ID没有强关联所以把订单表进行拆分为订单表与订单与商品的中间表
订单表ID订单时间12023-02-26
中间表
表ID订单表ID产品ID112
产品表ID产品名称1java2C
1.3 第三范式
指每一个非主属性既不部分依赖于也不传递依赖于业务主键也就是在第二范式的基础上消除了非主键对主键的传递依赖。例如存在一个部门信息表其中每个部门有部门编号dept_id、部门名称、部门介绍等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表则根据第三范式3NF也应该构建它否则就会有大量的数据冗余
部门表ID部门编号部门名称部门介绍1dep01部门一业务2dep02部门二销售
员工表ID员工编号员工所在部门ID部门名称1dem011部门一2dem022部门二
如果员工表中部门表的id发生变化那么部门名称也会发生变化这不符合第三范式应该把部门名称这一列从员工表中删除。
1.4 反范式设计
如果完全符合三范式也是有问题的在实际的业务查询中会大量存在着表的关联查询而大量的表关联很多的时候非常影响查询的性能。所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量得冗余换句话来说反范式化就是使用空间来换取时间。
例如商品信息表
商品名称出版社名称图书价格作者java人民出版社10xxx
分类表
分类ID分类名称1计算机
商品和分类对应表
商品ID分类ID11
商品信息和分类信息经常一起查询所以把分类信息也放到商品表里面冗余存放。
商品名称出版社名称图书价格作者分类名称java人民出版社10xxx计算机
范式化设计优缺点
范式化的更新操作通常比反范式化要快。当数据较好地范式化时就只有很少或者没有重复数据所以只需要修改更少的数据。范式化的表通常更小可以更好地放在内存里所以执行操作会更快。很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。在非范式化的结构中必须使用DISTINCT或者GROUPBY才能获得一份唯一的列表但是如果是一张单独的表很可能则只需要简单的查询这张表就行了。
范式化设计的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联也许更多。这不但代价昂贵也可能使一些索引策略无效。例如范式化可能将列存放在不同的表中而这些列如果在一个表中本可以属于同一个索引。
反范式化设计优缺点
反范式设计可以减少表的关联可以更好的进行索引优化。
反范式设计缺点也很明显1、存在数据冗余及数据维护异常2、对数据的修改需要更多的成本。
1.5 实际工作中的反范式实现
范式化和反范式化的各有优劣怎么选择最佳的设计? 完全的范式化和完全的反范式化设计都是实验室里才有的东西在真实世界中很少会这么极端地使用。在实际应用中经常需要混用。 性能提升-缓存和汇总 最常见的反范式化数据的方法是复制或者缓存在不同的表中存储相同的特定列。 比如从父表冗余一些数据到子表的。前面我们看到的分类信息放到商品表里面进行冗余存放就是典型的例子。 缓存衍生值也是有用的。如果需要显示每个用户发了多少消息可以每次执行一个对用户发送消息进行count的子查询来计算并显示它也可以在user表用户中建一个消息发送数目的专门列每当用户发新消息时更新这个值。 有需要时创建一张完全独立的汇总表或缓存表也是提升性能的好办法。“缓存表”来表示存储那些可以比较简单地从其他表获取但是每次获取的速度比较慢数据的表例如逻辑上冗余的数据)。而“汇总表”时,则保存的是使用GROUP BY语句聚合数据的表。 在使用缓存表和汇总表时有个关键点是如何维护缓存表和汇总表中的数据常用的有两种方式实时维护数据和定期重建这个取决于应用程序不过一般来说缓存表用实时维护数据更多点往往在一个事务中同时更新数据本表和缓存表汇总表则用定期重建更多使用定时任务对汇总表进行更新。 性能提升-计数器表 计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下载次数等。对于高并发下的处理首先可以创建一张独立的表存储计数器这样可使计数器表小且快并且可以使用一些更高级的技巧。 比如假设有一个计数器表只有一行数据记录网站的点击次数网站的每次点击都会导致对计数器进行更新问题在于对于任何想要更新这一行的事务来说这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行会严重限制系统的并发能力。 怎么改进呢可以将计数器保存在多行中每次随机选择一行进行更新。在具体实现上可以增加一个槽slot)字段然后预先在这张表增加100行或者更多数据当对计数器更新时选择一个随机的槽slot)进行更新即可。 这种解决思路其实就是写热点的分散在JDK的JDK1.8中新的原子类LongAdder也是这种处理方式而我们在实际的缓冲中间件Redis等的使用、架构设计中可以采用这种写热点的分散的方式当然架构设计中对于写热点还有削峰填谷的处理方式这种在MySQL的实现中也有体现。
反范式设计在分库分表中的查询 例如,用户购买了商品,需要将交易记录保存下来,那么如果按照买家的纬度分表,则每个买家的交易记录都被保存在同一表中, 我们可以很快、 很方便地査到某个买家的购买情况, 但是某个商品被购买的交易数据很有可能分布在多张表中, 査找起来比较麻烦 。 反之, 按照商品维度分表, 则可以很方便地査找到该商品的购买情况, 但若要査找到买家的交易记录, 则会比较麻烦 。 所以常见的解决方式如下。 ( 1 ) 在多个分片表查询后合并数据集, 这种方式的效率很低。 ( 2 ) 记录两份数据, 一份按照买家纬度分表, 一份按照商品维度分表, ( 3 ) 通过搜索引擎解决, 但如果实时性要求很高, 就需要实现实时搜索 在某电商交易平台下, 可能有买家査询自己在某一时间段的订单, 也可能有卖家査询自已在某一时间段的订单, 如果使用了分库分表方案, 则这两个需求是难以满足的, 因此, 通用的解决方案是, 在交易生成时生成一份按照买家分片的数据副本和一份按照卖家分片的数据副本,查询时分别满足之前的两个需求,因此,查询的数据和交易的数据可能是分别存储的,并从不同的系统提供接口。
二. 索引
2.1 聚集索引/聚簇索引
InnoDB中使用了聚集索引就是将表的主键用来构造一棵B树并且将整张表的行记录数据存放在该B树的叶子节点中。也就是所谓的索引即数据数据即索引。由于聚集索引是利用表的主键构建的所以每张表只能拥有一个聚集索引。 聚集索引的叶子节点就是数据页。换句话说数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是通过聚集索引能获取完整的整行数据。另一个优点是对于主键的排序查找和范围查找速度非常快。 如果我们没有定义主键呢MySQL会使用唯一性索引没有唯一性索引MySQL也会创建一个隐含列RowID来做主键然后用这个主键来建立聚集索引。 2.2 辅助索引/二级索引
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用因为B树中的数据都是按照主键进行排序的,那如果我们想以别的列作为搜索条件怎么办我们一般会建立多个索引这些索引被称为辅助索引/二级索引。 对于辅助索引(Secondary Index也称二级索引、非聚集索引)叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外每个叶子节点中的索引行中还包含了相应行数据的聚集索引键。 比如辅助索引index(node)那么叶子节点中包含的数据就包括了(主键、note)。
回表 辅助索引的存在并不影响数据在聚集索引中的组织因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键然后再通过主键索引聚集索引来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B树----一次辅助索引一次聚集索引。 为什么我们还需要一次回表操作呢?直接把完整的用户记录放到辅助索引的叶子节点不就好了么如果把完整的用户记录放到叶子节点是可以不用回表但是太占地方了相当于每建立一棵B树都需要把所有的用户记录再都拷贝一遍这就有点太浪费存储空间了。而且每次对数据的变化要在所有包含数据的索引中全部都修改一次性能也非常低下。 很明显回表的记录越少性能提升就越高需要回表的记录越多使用二级索引的性能就越低甚至让某些查询宁愿使用全表扫描也不使用二级索引。 那什么时候采用全表扫描的方式什么时候使用采用二级索引 回表的方式去执行查询呢这个就是查询优化器做的工作查询优化器会事先对表中的记录计算一些统计数据然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数需要回表的记录数越多就越倾向于使用全表扫描反之倾向于使用二级索引 回表的方式。
MRR 从上文可以看出每次从二级索引中读取到一条记录后就会根据该记录的主键值执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。 每次执行回表操作时都相当于要随机读取一个聚簇索引页面而这些随机IO带来的性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR多范围读取)的优化措施即先读取一部分二级索引记录将它们的主键值排好序之后再统一执行回表操作。 相对于每读取一条二级索引记录就立即执行回表操作这样会节省一些IO开销。使用这个 MRR优化措施的条件比较苛刻所以我们直接认为每读取一条二级索引记录就立即执行回表操作。
2.3 联合索引/复合索引
前面我们对索引的描述隐含了一个条件那就是构建索引的字段只有一个但实践工作中构建索引的完全可以是多个字段。所以将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引比如index(a,b)就是将a,b两个列组合起来构成一个索引。 千万要注意一点建立联合索引只会建立1棵B树多个列分别建立索引会分别以每个列则建立B树有几个列就有几个B树比如index(note)、index(b)就分别对note,b两个列各构建了一个索引。 index(note,b)在索引构建上包含了两个意思 1、先把各个记录按照note列进行排序。 2、在记录的note列相同的情况下采用b列进行排序 2.4 自适应哈希索引
InnoDB存储引擎除了我们前面所说的各种索引还有一种自适应哈希索引我们知道B树的查找次数,取决于B树的高度,在生产环境中,B树的高度一般为3~4层故需34次的IO查询。 所以在InnoDB存储引擎内部自己去监控索引表如果监控到某个索引经常用那么就认为是热数据然后内部自己创建一个hash索引称之为自适应哈希索引( Adaptive Hash Index,AHI)创建以后如果下次又查询到这个索引那么直接通过hash算法推导出记录的地址直接一次就能查到数据比重复去Btree索引中查询三四次节点的效率高了不少。 InnoDB存储引擎使用的哈希函数采用除法散列方式其冲突机制采用链表方式。注意对于自适应哈希索引仅是数据库自身创建并使用的我们并不能对其进行干预。通过命令show engine innodb status\G可以看到当前自适应哈希索引的使用状况。 哈希索引只能用来搜索等值的查询,如 SELECT* FROM table WHERE index coxxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的, 由于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。当然禁用或启动此特性和调整分区个数这个应该是DBA的工作我们了解即可。 InnoDB三大特性自适应hash双写缓冲区bufferpool 2.5 全文检索之倒排索引
倒排索引就是将文档中包含的关键字全部提取处理然后再将关键字和文档之间的对应关系保存起来最后再对关键字本身做索引排序。用户在检索某一个关键字时先对关键字的索引进行查找再通过关键字与文档的对应关系找到所在文档。 MySQL 5.6 以前的版本只有 MyISAM 存储引擎支持全文索引。从InnoDB 1.2.x版本开始InnoDB存储引擎开始支持全文检索对应的MySQL版本是5.6.x系列官方文档https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html。 注意不管什么引擎只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。 不过MySQL从设计之初就是关系型数据库存储引擎虽然支持全文检索整体架构上对全文检索支持并不好而且限制很多比如每张表只能有一个全文检索的索引不支持没有单词界定符( delimiter的语言如中文、日语、韩语等。 所以如果有大批量或者专门的全文检索需求还是应该选择专门的全文检索引擎。 创建 创建表时创建全文索引
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 CHARSETutf8;在已存在的表上创建全文索引
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 % 不同全文索引有自己的语法格式使用 match 和 against 关键字比如
select * from fulltext_test where match(content,tag) against(xxx xxx);总结MySQL有哪些索引类型
从数据结构角度可分为B树索引、哈希索引、以及FULLTEXT索引现在MyISAM和InnoDB引擎都支持了和R-Tree索引用于对GIS数据类型创建SPATIAL索引从物理存储角度可分为聚集索引clustered index、非聚集索引non-clustered index从逻辑角度可分为主键索引、普通索引或者单列索引、多列索引、唯一索引、非唯一索引等等。
2.6 密集索引和稀疏索引的区别
密集索引的定义叶子节点保存的不只是键值还保存了位于同一行记录里的其他列的信息由于密集索引决定了表的物理排列顺序一个表只有一个物理排列顺序所以一个表只能创建一个密集索引。 稀疏索引叶子节点仅保存了键位信息以及该行数据的地址有的稀疏索引只保存了键位信息机器主键。 mysam存储引擎不管是主键索引唯一键索引还是普通索引都是稀疏索引innodb存储引擎有且只有一个密集索引。 所以密集索引就是innodb存储引擎里的聚簇索引稀疏索引就是innodb存储引擎里的普通二级索引。
2.7 索引覆盖
即从辅助索引中就可以得到查询的记录而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息故其大小要远小于聚集索引因此可以减少大量的IO操作。所以记住覆盖索引可以视为索引优化的一种方式而并不是索引类型的一种。
三. 索引在查询中的使用
1、一个索引就是一个B树索引让我们的查询可以快速定位和扫描到我们需要的数据记录上加快查询的速度。 2、一个select查询语句在执行过程中一般最多能使用一个二级索引来加快查询即使在where条件中用了多个二级索引。
3.1 索引的代价
1.空间上的代价 这个是显而易见的每建立一个索引都要为它建立一棵B树每一棵B树的每一个节点都是一个数据页一个页默认会占用16KB的存储空间一棵很大的B树由许多数据页组成会占据很多的存储空间。 2.时间上的代价 每次对表中的数据进行增、删、改操作时都需要去修改各个B树索引。而且我们讲过B树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。 而增、删、改操作可能会对节点和记录的排序造成破坏所以存储引擎需要额外的时间进行一些记录移位页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引每个索引对应的B树都要进行相关的维护操作这必然会对性能造成影响。 既然索引这么有用我们是不是创建越多越好既然索引有代价我们还是别创建了吧当然不是按照经验一般来说一张表6-7个索引以下都能够取得比较好的性能权衡。
3.2 高性能的索引创建策略
索引列的类型尽量小
我们在定义表结构的时候要显式的指定列的类型以整数类型为例有TTNYINT、MEDUMNT、INT、BIGTNT这么几种它们占用的存储空间依次递增我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增如果我们想要对某个整数列建立索引的话在表示的整数范围允许的情况下尽量让索引列使用较小的类型比如我们能使用INT就不要使用BIGINT能使用NEDIUMINT就不要使用INT这是因为: ·数据类型越小在查询时进行的比较操作越快CPU层次) ·数据类型越小索引占用的存储空间就越少在一个数据页内就可以放下更多的记录从而减少磁盘/0带来的性能损耗也就意味着可以把更多的数据页缓存在内存中从而加快读写效率。 这个建议对于表的主键来说更加适用因为不仅是聚簇索引中会存储主键值其他所有的二级索引的节点处都会存储一份记录的主键值如果主键适用更小的数据类型也就意味着节省更多的存储空间和更高效的I/0。
创建索引应选择离散性高的列。
索引的选择性/离散性是指不重复的索引值也称为基数cardinality)和数据表的记录总数N)的比值范围从1/N到1之间。索引的选择性越高则查询效率越高因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1这是最好的索引选择性性能也是最好的。 很差的索引选择性就是列中的数据重复度很高比如性别字段不考虑政治正确的情况下只有两者可能男或女。那么我们在查询时即使使用这个索引从概率的角度来说依然可能查出一半的数据出来。
前缀索引
有时候需要索引很长的字符列这会让索引变得大且慢我们可以使用前缀索引这样可以大大节约索引空间从而提高索引效率。但这样会降低索引的选择性一般情况下我们需要保证某个列前缀的选择性也是足够高的以满足查询性能。尤其对于BLOB、TEXT或者很长的VARCHAR类型的列应该使用前缀索引因为MySQL不允许索引这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性同时又不能太长以便节约空间)。前缀应该足够长以使得前缀索引的选择性接近于索引整个列。 阿里规范泰山版建议这个前缀长度为20比较合适可以使用“count(distinct left(列名, 索引长度))/count(*)的区分度来确定”。 前缀索引是一种能使索引更小、更快的有效办法但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY也无法使用前缀索引做覆盖扫描。 有时候后缀索引 (suffix index)也有用途例如找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引但是可以把字符串反转后存储并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。
只为用于搜索、排序或分组的列创建索引
只为出现在WHERE 子句中的列、连接子句中的连接列创建索引而出现在查询列表中的列一般就没必要建立索引了除非是需要使用覆盖索引。又或者为出现在ORDER BY或GROUP BY子句中的列创建索引。 如果有了索引的话恰巧这个分组顺序又和我们索引中的索引列的顺序是一致的而我们的B树索引又是按照索引列排好序的这不正好么所以可以直接使用B树索引进行分组。和使用B树索引进行排序是一个道理分组列的顺序也需要和索引列的顺序一致。
合理设计组合索引
在一个多列B-Tree索引中索引列的顺序意味着索引首先按照最左列进行排序其次是第二列等等。所以索引可以按照升序或者降序进行扫描以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。 所以多列索引的列顺序至关重要。对于如何选择索引的列顺序有一个经验法则将选择性最高的列放到索引最前列。当不需要考虑排序和分组时将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下这样设计的索引确实能够最快地过滤出需要的行对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。 然而性能不只是依赖于索引列的选择性也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序比如排序和分组让这种情况下索引的选择性最高。 同时在优化性能的时候可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。
主键尽量是很少改变的列
行是按照聚集索引物理排序的如果主键频繁改变物理顺序会变mysql要不断调整B树中间可能会出现页面的分裂和合并等会导致性能急剧下降。
处理冗余和重复索引、未使用的索引
MySQL允许在相同列上创建多个索引无论是有意的还是无意的。MySQL需要单独维护重复的索引并且优化器在优化查询的时候也需要逐个地进行考虑这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引发现以后也应该立即移除。
尽量使用三星索引
如果查询使用三星索引一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。 三星索引概念是在《Rrelational Database Index Design and the optimizers》 一书这本书也是《高性能MySQL》作者强烈推荐的一本书中提出来的。原文如下 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. 索引将相关的记录放到一起则获得一星 如果索引中的数据顺序和查找中的排列顺序一致则获得二星 如果索引中的列包含了查询中需要的全部列则获得三星。 二星排序星 在满足一星的情况下当查询需要排序group by、 order by如果查询所需的顺序与索引是一致的索引本身是有序的是不是就可以不用再另外排序了一般来说排序可是影响性能的关键因素。 三星宽索引星 在满足了二星的情况下如果索引中所包含了这个查询所需的所有列包括 where 子句 和 select 子句中所需的列也就是覆盖索引这样一来查询就不再需要回表了减少了查询的步骤和IO请求次数性能几乎可以提升一倍。 一星按照原文稍微有点难以理解其实它的意思就是如果一个查询相关的索引行是相邻的或者至少相距足够靠近的话必须扫描的索引片宽度就会缩至最短也就是说让索引片尽量变窄也就是我们所说的索引的扫描范围越小越好。 这三颗星哪颗最重要第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘随机读回表操作。第一和第二颗星重要性差不多可以理解为第三颗星比重是50%第一颗星为27%第二颗星为23%所以在大部分的情况下会先考虑第一颗星但会根据业务情况调整这两颗星的优先度。
四.总结
本文主要讲了数据库表设计的规范三范式反范式设计及其各自优缺点还介绍了索引包括聚集索引、二级索引、组合索引、倒排索引等以及对索引创建的优化策略利用各种手段提高数据库表设计的性能。
