MySQL的普通索引和唯一索引到底什么区别?
1 概念区分
普通索引 V.S 唯一索引
普通索引可重复,唯一索引和主键一样不能重复。 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键)
主键 V.S 唯一索引
主键保证DB的每一行都是唯一、不重复,比如身份证,学号等,不重复。 唯一索引的作用跟主键一样。 但在一张表里面只能有一个主键,不能为空,唯一索引可有多个。唯一索引可有一条记录为null。
比如学生表:
- 在学校,一般用学号做主键,身份证号作为唯一索引
- 在教育局,就把身份证号弄成主键,学号作为唯一索引
所以选谁做主键,取决于业务需求。
2 案例
某居民系统,每人有唯一身份证号。若系统要按身份证号查姓名:
select name from CUser where id_card = 'ooxx';
id_card
字段较大,不推荐做主键。现有如下选择:
- 在
id_card
创建唯一索引 - 创建一个普通索引
假定业务代码已确保不会写入重复身份证号,这两个选择逻辑上都正确。 但性能角度考虑,选择哪个呢?
假设字段 k 上的值都不重复。
InnoDB索引结构:
3 查询性能
select id from T where k=4
通过B+树从root开始层序遍历到叶节点,数据页内部通过二分搜索:
- 普通索引
查找到满足条件的第一个记录(4,400)后,继续查找下个记录,直到碰到第一个不满足
k=4
的记录 - 唯一索引 查到第一个满足条件的,就停止搜索
看起来性能差距很小。
InnoDB数据按数据页单位读写。即读一条记录时,并非将该一个记录从磁盘读出,而以页为单位,将其整体读入内存。
所以普通索引,多了一次“查找和判断下一条记录”的操作,即一次指针寻找和一次计算。
若k=4
记录恰为该数据页的最后一个记录,则此时要取下个记录,还得读取下个数据页。
对整型字段,一个数据页可存近千个key,因此这种情况概率其实也很低。因此计算平均性能差异时,可认为该操作成本对CPU开销忽略不计。
4 更新性能
往表中插入一个新记录(4,400),InnoDB会有什么反应?
这要看该记录要更新的目标页是否在内存:
在内存
- 普通索引 找到3和5之间的位置,插入值,结束。
- 唯一索引
找到3和5之间的位置,
判断到没有冲突
,插入值,结束。
只是一个判断的差别,耗费微小CPU时间。
不在内存
- 唯一索引 将数据页读入内存,判断到没有冲突,插入值,结束。
- 普通索引 将更新记录在change buffer,结束。
将数据从磁盘读入内存涉及随机I/O访问,是DB里成本最高的操作之一。而change buffer可以减少随机磁盘访问,所以更新性能提升明显。
5 索引选择最佳实践
普通索引、唯一索引在查询性能上无差别,主要考虑更新性能。所以,推荐尽量选择普通索引。
若所有更新后面,都紧跟对该记录的查询,就该关闭change buffer。其它情况下,change buffer都能提升更新性能。 普通索引和change buffer的配合使用,对数据量大的表的更新优化还是明显的。
在使用机械硬盘时,change buffer收益也很大。所以,当你有“历史数据”库,且出于成本考虑用机械硬盘,应该关注这些表里的索引,尽量用普通索引,把change buffer开大,确保“历史数据”表的数据写性能。
6 change buffer 和 redo log
WAL 提升性能的核心机制,也是尽量减少随机读写,它们有啥区别?
6.1 插入流程
insert into t(id,k)
values (id1,k1),(id2,k2);
假设当前k索引树的状态,查找到位置后:
- k1所在数据页在内存(buffer pool)
- k2数据页不在内存
看如下流程:
带change buffer的更新流程
图中箭头都是后台操作,不影响更新请求的响应。
该更新做了如下操作:
- Page1在内存,直接更新内存
- Page2不在内存,就往change buffer区,缓存一个“往Page2插一行记录”的信息
- 将前两个动作记入redo log
至此,事务完成。执行该更新语句成本很低,只是写两处内存,然后写一处磁盘(前两次操作合在一起写了一次磁盘),还是顺序写。
6.2 处理之后的读请求
select * from t
where k
in (k1, k2);
读语句紧随更新语句之后,这时内存中的数据都还在,所以此时这俩读操作就与系统表空间和 redo log 无关。
带change buffer的读过程
读Page1时,直接从内存返回。
WAL之后若读数据,是否一定要读盘?一定要从redo log将数据更新后才能返回? 其实不用。看上图状态,虽然磁盘上还是之前的数据,但这里直接从内存返回结果,结果是正确的。
读Page2时,需将Page2从磁盘读入内存,然后应用change buffer里的操作日志,生成一个正确版本并返回结果。所以一直到需要读Page2时,该数据页才会被从磁盘读入内存。
综上,这俩机制的更新性能:
- redo log 主要节省随机写磁盘的I/O消耗(转成顺序写)
- change buffer主要节省随机读磁盘的I/O消耗
7 总结
因为唯一索引用不了change buffer,若业务可以接受,从性能角度,优先考虑非唯一索引。
到底何时使用唯一索引
问题就在于“业务可能无法确保”,而本文前提是“业务代码已保证不会写入重复数据”,才讨论的性能问题。
- 若业务无法保证或业务就是要求数据库来做约束 没有撤退可言,必须创建唯一索引。那本文意义就在于,若碰上大量插入数据慢、内存命中率低时,多提供了一个排查思路
- “归档库”场景,可考虑使用唯一索引 比如线上数据只需保留半年,然后历史数据存在归档库。此时,归档数据已是确保没有唯一键冲突。要提高归档效率,可考虑把表的唯一索引改为普通索引。
若某次写入使用了change buffer,之后主机异常重启,是否会丢失change buffer数据
不会!虽然是只更新内存,但在事务提交时,change buffer的操作也被记录到了redo log。所以崩溃恢复时,change buffer也能找回。
merge时是否会把数据直接写回磁盘
merge流程
- 从磁盘读入数据页到内存(老版本数据页)
- 从change buffer找出该数据页的change buffer 记录(可能多个),依次应用,得到新版数据页
- 写redo log 该redo log包含数据的变更和change buffer的变更
至此merge结束。 这时,数据页和内存中change buffer对应磁盘位置都尚未修改,是脏页,之后各自刷回自己物理数据,就是另外一过程。
在构造第一个例子的过程,通过session A的配合,让session B删除数据后又重新插入一遍数据,然后就发现explain结果中,rows字段从10001变成37000多。 而如果没有session A的配合,只是单独执行delete from t 、call idata()、explain这三句话,会看到rows字段其实还是10000左右。这是什么原因呢?
如果没有复现,检查
- 隔离级别是不是RR(Repeatable Read,可重复读)
- 创建的表t是不是InnoDB引擎
为什么经过这个操作序列,explain的结果就不对了? delete 语句删掉了所有的数据,然后再通过call idata()插入了10万行数据,看上去是覆盖了原来10万行。 但session A开启了事务并没有提交,所以之前插入的10万行数据是不能删除的。这样,之前的数据每行数据都有两个版本,旧版本是delete之前数据,新版本是标记deleted的数据。 这样,索引a上的数据其实有两份。
不对啊,主键上的数据也不能删,那没有使用force index的语句,使用explain命令看到的扫描行数为什么还是100000左右?(潜台词,如果这个也翻倍,也许优化器还会认为选字段a作为索引更合适)
是的,不过这个是主键,主键是直接按照表的行数来估计的。而表的行数,优化器直接用的是show table status
的值。
大家的机器如果IO能力比较差的话,做这个验证的时候,可以把innodb_flush_log_at_trx_commit
和 sync_binlog
都设成0。
参考
- https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html
推荐阅读
-
这篇 MySQL 索引和 B+Tree 文章通俗易懂!-创建索引的几个原则
-
MySQL 和 Hive 存储引擎在表、索引数量上有哪些限制?-MySQL 存储引擎的限制
-
什么是数据库事物?为什么需要数据库事物,事物有哪些特征?事物的隔离级别是什么?-1.什么是数据库事务? 1.事务是作为一个逻辑单元执行的一系列操作。一个逻辑工作单元必须具备四个属性,即ACID(原子性、一致性、隔离性和持久性)属性,只有这样才能成为事务: 原子性 2.事务必须是一个原子工作单元;它的数据修改要么全部执行,要么全部不执行。 一致性 3.事务完成时,所有数据必须保持一致。在相关数据库中,所有规则都必须适用于事务的修改,以保持所有数据的完整性。事务结束时,所有内部数据结构(如 B 树索引或双向链接表)必须正确无误。 隔离 4.并发事务的修改必须与其他并发事务的修改隔离。一个事务会在另一个并发事务修改之前或之后查看某一状态下的数据,而不会查看中间状态下的数据。这就是所谓的可序列化,因为它允许重新加载起始数据和重放一系列事务,从而使数据最终处于与原始事务执行时相同的状态。 持久性 5.事务完成后,它对系统的影响是永久性的。即使在系统发生故障的情况下,修改也会保留。 2. 为什么需要数据库事物,事物有哪些特征? 事物对数据库的作用是对数据进行一系列操作,要么全部成功,要么全部失败,防止出现中间状态,确保数据库中的数据始终处于正确、和谐的状态。 特征:原子性、一致性、隔离性、持久性,以及其他特征 原子性(Atomicity):所有操作在事务开始后,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出现错误时,会回滚到事务开始前的状态,所有操作就像没有发生一样。也就是说,事务是一个不可分割的整体,就像化学中的原子一样,是物质的基本单位。 一致性(Consistency):在事务开始之前和结束之后,数据库的完整性约束都没有被破坏。例如,如果 A 转钱给 B,A 不可能扣除这笔钱,但 B 却没有收到这笔钱。 隔离:在同一时间内,只允许一个事务请求相同的数据,不同事务之间没有干扰。例如,甲正在从一张银行卡上取款,在甲取款过程结束之前,乙不能向这张卡转账。 持久性(耐用性):事务完成后,事务对数据库的所有更新都将保存到数据库中,无法回滚 3.事务的隔离级别有哪些? 数据库事务有四种隔离级别,从低到高分别是未提交读取(Read uncommitted)、已提交读取(Read committed)、可重复读取(Repeatable read)、可序列化(Serializable)。此外,事务的并发操作中可能会出现脏读、不可重复读、幽灵读等情况。事务并发问题 脏读:事务 A 读取事务 B 更新的数据,然后事务 B 回滚操作,那么事务 A 读取的数据就是脏数据。 不可重复读取:事务 A 多次读取同一数据,事务 B 在事务 A 多次读取期间更新并提交数据,导致事务 A 多次读取同一数据时结果不一致。 幻影读取:系统管理员 A 将数据库中所有学生的具体分数改为 ABCDE 等级,但系统管理员 B 在此时插入了具体分数的记录,当系统管理员 A 更改结束后发现仍有一条记录未被更改,仿佛发生了幻觉,这称为幻影读取。 小结:不可重复读和幻读容易混淆,不可重复读侧重于修改,幻读侧重于增删。解决不可重复读问题只需锁定满足条件的行,解决幻读问题则需要锁定表 MySQL 事务隔离级别
-
[MySQL]MySQL索引和B+树的概念
-
Mysql]InnoDB 中的聚类索引、二级索引和联合索引
-
理解MySQL基础:主键、外键、索引与唯一索引的概念详解
-
Grid++Report 锐浪报表开发常见问题解答集锦-报表设计 问:怎样在设计时打印预览报表? 答:为了及时查看报表的设计效果,Grid++Report 报表设计应用程序提供了四种查看视图:普通视图、页面视图、预览视图与查询视图。通过窗口下边的 Tab 按钮可以在四种视图中任意切换。在预览视图中查看报表的打印预览效果,在查询视图中查看报表的查询显示效果。如果在报表的记录集提供了数据源连接串与查询 SQL,在进入预览视图与查询视图时会利用数据源连接串与查询 SQL 从数据源中自动取数,否则 Grid++Report 将自动生成模拟数据进行模拟打印预览与查询显示。注意:在预览视图与查询视图中看到的报表运行结果有可能与在你程序中的最终运行结果有差异,因为在报表的生成过程中我们可以在程序中对报表的生成行为进行一定的控制。 问:怎样用 Grid++Report 设计交叉表? 答:Grid++Report 没有提供专门实现交叉表的功能,其它的报表构件提供的交叉表功能一般也比较死板和功能有限。利用 Grid++Report 的编程接口可以做出灵活多变,功能丰富的交叉表。示例程序 CrossTab 就是一个实现交叉表的例子程序,认真领会此例子程序,你就可以做出自己想要各种交叉表,并能提取一些共用代码,便于重复使用。 问:怎样设置整个报表的缺省字体? 答:设置报表主对象的字体属性,也就是设置了整个报表的缺省字体。如果改变报表主对象的字体属性,则没有专门的设置字体属性的子对象的字体属性也跟随改变。同样每个报表节与明细网格也有字体属性,他们的字体属性也就是其拥有的子对象的缺省字体。 问:怎样在打印时限制一页的输出行数? 答:设定明细网格的内容行的‘每页行数(RowsPerPage)’属性即可。另外要注意‘调节行高(AdjustRowHeight)’属性值:为真时根据页面的输出高度自动调整行的高度,使整个页面的输出区域充满。为假时按设计时的高度输出行。 问:怎样显示中文大写金额? 答:将对象的“格式(Format)”属性设为 “$$” 及可,可以设置格式的对象有:字段(IGRField)、参数(IGRParameter)、系统变量(IGRSystemVarBox)与综合文字框(IGRMemoBox),其中综合文字框是在报表式上设格式。 问:能否实现自定义纸张与票据打印? 答:Grid++Report 完全支持自定义纸张的打印,只要在报表设定时在页面设置中选定自定义纸张,并指定准确的纸张尺寸。当然要在最终输出时得道合适的打印结果,输出打印机必须支持自定义纸张打印。Windows2000/XP/2003 操作系统上可以在打印机上定义自定义纸张,也可以采用这种方式实现自定义纸张打印。 问:怎样实现 0 值不打印? 答:直接设置格式串就可以,在“数字格式”设置对话框中选定“0 不显示”,就会得到合适的格式串。也可以通过直接录入格式串来指定 0 不显示,但格式串必须符合 Grid++Report 的规定格式。另一种实现办法是在报表获取明细记录数据时,在 BeforePostRecord 事件中将值为零的字段设为空,调用字段的 Clear 方法将字段置为空。 问:怎样实现多栏报表? 答:在明细网格上设‘页栏数(PageColumnCount)’属性值大于 1 即可。通过 Grid++Report 的“页栏输出顺序”还可以指定多栏报表的输出顺序是“先从上到下”还是“先从左到右”。 问:如何实现票据套打? 答:Grid++Report 为实现票据套打做了很多专门的安排:报表设计器提供了页面设计模式,按照设定的纸张尺寸显示设计面板,如果将空白票据的扫描图设为设计背景图,在定位报表内容的输出位置会非常方便。报表部件可以设定打印类别,非套打输出的内容在套打打印模式下就不会输出。 问:Grid++Report 有没有横向分页功能? 答:回答是肯定的,在列的总宽度超过打印页面的输出宽度时,Grid++Report 可以另起新页输出剩余的列,如果左边存在锁定列,锁定列可以在后面的新页中重复输出,这样可以保证关键数据列在每一页都有输出。仔细体会 Grid++Report 提供的多种打印适应策略,选用最合适的方式。Grid++Report 的多种打印适应策略为开发动态报表提供了很好的支持。 问:怎样实现报表本页小计功能? 答:定义一个报表分组,将本分组定义为页分组,在本分组的分组头与分组尾上定义统计。页分组就是在每页产生一个分组项,在每页的上端与下端都会分别显示页分组的分组头与分组尾,页分组不用定义分组依据字段。 报表运行 问:怎样与数据库建立连接? 答:如果在设计报表时指定了数据集的数据源连接串与查询 SQL 语句,Grid++Report 采用拉模式直接从数据源取得报表数据,Grid++Report 利用 OLE DB 从数据源取数,OLE DB 提供了广泛的数据源操作能力。如果 Grid++Report 的数据来源采用推模式,即 Grid++Report 不直接与数据库建立连接,各种编程语言/平台都提供了很好的数据库连接方式,并且易于操作,应用程序在报表主对象(IGridppReport)的 FetchRecord 事件中将数据传入,例子程序提供了各种编程语言填入数据的通用方法,对C++Builder 和 Delphi 还进行了专门的包装,直接关联 TDataSet 对象也可以将 TDataSet 对象中的数据传给报表。 问:打印时能否对打印纸张进行自适应?支持表格的折行打印吗? 答:Grid++Report 在打印时采用多种适应策略,通过设置明细网格(IGRDetailGrid)的‘打印策略(PrintAdaptMethod)’属性指定打印策略。(1)丢弃:按设计时列的宽度输出,超出范围的内容不显示。(2)绕行:按设计时列的宽度输出,如果在当前行不能完整输出,则另起新行进行输出。(3)缩放适应:对所有列的输出宽度进行按比例地缩放,使总宽度等于页面的输出宽度。(4)缩小适应:如果列的总宽度小于页面的输出宽度,对所有列的输出宽度进行按比例地缩小,使总宽度等于页面的输出宽度。(5)横向分页:超范围的列在新页中输出。(6)横向分页并重复锁定列。 问:如何改变缺省打印预览窗口的窗口标题? 答:改变报表主对象的‘标题(Title)’属性即可。 问:利用集合对象的编程接口取子对象的接口引用,但不是自己期望的结果。 答:Grid++Report中所有集合对象的下标索引都是从 1 开始,另按对象的名称查找对象的接口引用时,名称字符是不区分大小写的。 问:怎样在运行时控制报表中各个对象的可见性?即怎样在运行时显示或隐藏对象? 答:在报表主对象(GridppReport)的 SectionFormat 事件中设定相应报表子对象的可见(Visible)属性即可。 问:报表主对象重新载入数据,设计器中为什么没有反映新载入的数据? 答:应调用 IGRDesigner 的 Reload 方法。 问:怎样实现不进入打印预览界面,直接将报表打印出来?
-
Mysql中普通索引key 、主键索引(primary key) 、唯一索引(unique key)与index区别
-
选择唯一索引和普通索引之间的MySQL决策
-
MySQL中唯一约束和唯一索引的区别