6、高性能表结构及索引设计最佳实践

1、六种范式六种范式第一范式1NF、第二范式2NF、第三范式3NF、巴斯-科德范式BCNF、第四范式(4NF和第五范式5NF又称完美范式。满足最低要求的范式是第一范式1NF。在第一范式的基础上进一步满足更多规范要求的称为第二范式2NF其余范式以次类推。一般来说数据库只需满足第三范式(3NF就行了。1.1、第一范式——原子性定义 属于第一范式关系的所有属性都不可再分即数据项不可分。理解 第一范式强调数据表的原子性是其他范式的基础。例如下表name-age列具有两个属性一个name,一个 age不符合第一范式把它拆分成两列上表就符合第一范式关系。但日常生活中仅用第一范式来规范表格是远远不够的依然会存在数据冗余过大、删除异常、插入异常、修改异常的问题此时就需要引入规范化概念将其转化为更标准化的表格减少数据依赖。实际上1NF是所有关系型数据库的最基本要求你在关系型数据库管理系统RDBMS例如SQL ServerOracleMySQL中创建数据表的时候如果数据表的设计不符合这个最基本的要求那么操作一定是不能成功的。也就是说只要在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*FROMusersWHEREnameAlice;如果只有 idx_name(name) 索引流程是先在 idx_name 里找到 name ‘Alice’ 的记录拿到对应的主键 id再用这个 id 去主键索引里查完整行数据。第 3 步就是回表。如果查询只需要索引里已有的字段例如SELECTid,nameFROMusersWHEREnameAlice;这时索引已经能直接返回结果就不需要回表这种情况叫覆盖索引。3.2、MRRMRR全称 Multi-Range Read多范围读取优化可以理解为回表时的“批量排序读取”优化。它解决什么问题前面说回表时如果通过二级索引查到很多主键再逐条回聚簇索引取完整行这些主键在聚簇索引里可能是散开的就会形成大量 随机 IO。MRR 的思路是先不急着回表把要回表的主键值攒起来排好序再按主键顺序批量回表这样随机 IO 就变成了更接近顺序 IO,减少了回表次数。执行流程以这条 SQL 为例SELECT*FROMtWHEREage18ANDnameLIKEC%;假设只有 idx_age_name(age, name) 索引但没有覆盖所有字段需要回表。开启 MRR 后大致流程是扫描二级索引找到满足条件的记录把这些记录对应的主键值放入 read_rnd_buffer缓冲区满或扫描结束后对主键值排序按排序后的主键顺序批量回表读取完整行返回结果。参数作用read_rnd_buffer_sizeMRR 缓冲区大小默认 256KB决定一次能攒多少主键再排序回表optimizer_switch中的mrr是否允许使用 MRRoptimizer_switch中的mrr_cost_based是否让优化器按成本决定是否启用 MRR用 EXPLAIN 看执行计划如果 Extra 列出现Using MRR4、InnoDB中的自适应哈希索引( Adaptive Hash Index,AHI)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。而对于其他查找类型,如范围查找,是不能使用哈希索引的。可以通过观察 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 pool5、InnoDB中的全文检索Full-Text Search5.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.html5.2、案例不推荐使用全文索引创建表时创建全文索引createtablefulltext_test(idint(11)NOTNULLAUTO_INCREMENT,contenttextNOTNULL,tagvarchar(255),PRIMARYKEY(id),FULLTEXTKEYcontent_tag_fulltext(content,tag))DEFAULTCHARSETutf8;在已存在的表上创建全文索引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 index3、从逻辑角度可分为主键索引、普通索引或者单列索引、多列索引、唯一索引、非唯一索引等等。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_notexxxx;前缀索引是一种能使索引更小、更快的有效办法但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY也无法使用前缀索引做覆盖扫描。有时候后缀索引 (suffix index)也有用途例如找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引但是可以把字符串反转后存储并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。