MYSQL_10高性能表结构及索引设计
2023-04-16 09:56:13 20 举报AI智能生成
高性能表结构及索引设计
作者其他创作
大纲/内容
数据库表设计
范式
使关系满足一定的约束条件，此约束已经形成了规范
范式设计
第一范式
属于第一范式关系的所有属性都不可再分，即数据项不可分
第一范式强调数据表的原子性，是其他范式的基础
例子
name-age列具有两个属性，一个name,一个 age不符合第一范式，把它拆分成两列
第二范式
是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）
要求数据库表中的每个实例或行必须可以被惟一地区分
说要求表中只具有一个业务主键，而且第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性
第三范式
指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主键对主键的传递依赖
例子
例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息
么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中
优缺点
优点
范式化的更新操作通常比反范式化要快
当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
缺点
通常需要关联。稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联，也许更多
范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引
反范式设计
反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反。允许存在少量得冗余
换句话来说反范式化就是使用空间来换取时间
优缺点
优点
反范式设计可以减少表的关联
可以更好的进行索引优化
缺点
存在数据冗余及数据维护异常
对数据的修改需要更多的成本
选择最佳的设计
选择反范式设计场景
缓存表
表示存储那些可以比较简单地从其他表获取（但是每次获取的速度比较慢）数据的表
例子
如果需要显示每个用户发了多少消息
可以每次执行一个对用户发送消息进行count的子查询来计算并显示它
可以在user表用户中建一个消息发送数目的专门列，每当用户发新消息时更新这个值
选择范式则需要汇总，使用GROUP BY语句聚合数据的表
定期重建
选择反范式则可以建立缓存表来提升性能
实时维护数据
计数器表
例子
比如网站点击数、用户的朋友数、文件下载次数等
选择反范式则可以将计数器保存在多行中，每次随机选择一行进行更新
写热点的分散
JDK1.8中新的原子类LongAdder
增加一个槽（slot)字段，然后预先在这张表增加100行或者更多数据，当对计数器更新时，选择一个随机的槽（slot)进行更新即可
选择范式则一个计数器表，只有一行数据，记录网站的点击次数，网站的每次点击都会导致对计数器进行更新
对于任何想要更新这一行的事务来说，这条记录上都有一个全局的互斥锁(mutex)。这会使得这些事务只能串行执行，会严重限制系统的并发能力
InnoDB中的索引
聚集索引/聚簇索引
聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引
数据页上存放的是完整的每行记录
通过过聚集索引能获取完整的整行数据
对于主键的排序查找和范围查找速度非常快
特殊情况
没有定义主键，MySQL会使用唯一性索引
没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键
然后用这个主键来建立聚集索引
辅助索引/二级索引
对于辅助索引，叶子节点并不包含行记录的全部数据
叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了相应行数据的聚集索引键
回表
每张表上可以有多个辅助索引
当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键
然后再通过主键索引（聚集索引）来找到一个完整的行记录
回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引
MRR
多范围读取)的优化措施
即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作
会节省一些IO开销
联合索引/复合索引
将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引
索引的作用
一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度
代价
空间上的代价
每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间
一棵很大的B+树由许多数据页组成会占据很多的存储空间
时间上的代价
每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引
增、删、改操作可能会对节点和记录的排序造成破坏，
存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序
存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序
般来说，一张表6-7个索引以下都能够取得比较好的性能权衡
创建策略
索引列的类型尽量小
数据类型越小，在查询时进行的比较操作越快（CPU层次)
数据类型越小，索引占用的存储空间就越少
在一个数据页内就可以放下更多的记录，从而减少磁盘I/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/0
例子
整数类型为例，有TTNYINT、NEDUMNT、INT、BIGTNT这么几种，它们占用的存储空间依次递增
在表示的整数范围允许的情况下，尽量让索引列使用较小的类型
能使用INT就不要使用BIGINT，能使用NEDIUMINT就不要使用INT
利用索引选择性和前缀索引
创建索引应该选择选择性/离散性高的列
离散性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数（N)的比值，
索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行
前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢
保证某个列前缀的选择性也是足够高的，以满足查询性能
尤其对于BLOB、TEXT或者很长的VARCHAR类型的列，应该使用前缀索引
阿里规范，这个前缀的长度为20比较合适
只为用于搜索、排序或分组的列创建索引
合理设计多列索引
0 条评论
下一页
