如何选择普通索引和惟一索引《死磕MySQL系列 五》

2021年11月26日 阅读数:3
这篇文章主要向大家介绍如何选择普通索引和惟一索引《死磕MySQL系列 五》,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

系列文章

1、原来一条select语句在MySQL是这样执行的《死磕MySQL系列 一》数据库

2、一辈子挚友redo log、binlog《死磕MySQL系列 二》缓存

3、MySQL强人“锁”难《死磕MySQL系列 三》数据结构

4、S 锁与 X 锁的爱恨情仇《死磕MySQL系列 四》性能

看过前几期文章的伙伴会发现并无聊过关于索引和事务的知识点,这两个大点再以前的文章中已经写过了。学习

这里给你们一个传送门点击直接查看哈!url

揭开MySQL索引神秘面纱spa

上来就问MySQL事务,瑟瑟发抖....net

MVCC:据说有人好奇个人底层实现指针

幻读:据说有人认为我是被MVCC干掉的code

接下来打开普通索引和惟一索引的世界。

1、了解普通索引和惟一索引

普通索引

MySQL中基本索引类型,没有什么限制,容许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。

惟一索引

索引列中的值必须是惟一的,可是容许为空值。

主键索引是一种特殊的惟一索引,不容许有空值。

扩展一下其它两中索引,知识点放在一块儿记忆会更好

全文索引

只能在char,varchar,text类型字段上使用全文索引,介绍了要求,说说什么是全文索引,就是在一堆文字中,经过其中的某个关键字等,就能找到该字段所属的记录行,好比有“你是个靓仔,靓女。。。”经过靓仔,可能就能够找到该条记录。

空间索引

空间索引是对空间数据类型的字段创建的索引,MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。在建立空间索引时,使用SPATIAL关键字。要求,引擎为Myisam,建立空间索引的列,必须将其声明为not null。

索引添加方式

一、 主键索引:alter table table_name add primary key (column)

二、 惟一索引:alter table table_name add unique (column)

三、普通索引:alter table table_name add index index_name (column)

四、全文索引:alter table table_name add fulltext (column)

五、多列索引:alter table table_name add index index_name (column1,column2,column3)

2、应用场景

如今你应该知道普通索引和惟一索引的区别,接下来看看在一些场景下如何选择两个索引。

丁老师文章中提到一个业务场景是市民系统,经过身份证号来查姓名。

这里咔咔也借用这个场景来给你们经过咔咔的思路描述一下这个流程。

执行语句为select name from user where card = '6104301996xxxxxxxx';

这个场景第一反应确定是给card建立一个索引,但建立什么索引呢?主键索引确定不建议使用。

思考:为何不能用身份证号来做为主键索引?

3、为何不能用太大的值做为主键

Innodb存储引擎的主键索引结构以下图

普通索引数据结构以下图

主键索引的叶子节点存储的是对应主键的整行数据。

普通索引的叶子节点存储的是对应的主键值。

若是说B+Tree读取数据的深度是三层,每一个磁盘的大小为16kb。

那在B+Tree中非叶子节点能够存储多少数据呢!通常来讲咱们每一个表都会存在一个主键。

根据三层来计算,第一层跟第二层存储的是key值,也就是主键值。

都知道int类型所占的内存时4Byte(字节),指针的存储就给个6Byte,一共就是10Tybe,那么第一层节点就能够存储16 * 1000 /10 = 1600。

同理第二层每一个节点也是能够存储1600个key。

第三层是叶子节点,每一个磁盘存储大小一样安装BTree的计算同样,每条数据占1kb。

在B+Tree中三层能够存储的数据就是1600 * 1600 * 16 = 40960000

结论:若主键过大会直接影响索引存储的数据量,因此很是不建议使用过大的数据做为主键索引。

4、从查询的角度分析

假设如今要查card = 5 这条记录,查询过程为,先经过B+树从树根开始,按层搜索到叶子节点,而后经过二分法来定位card = 5 的这条记录。

普通索引

对于普通索引来讲当找到card = 5这条记录后,还会继续查找,直到碰到第一个不知足card = 5的记录为止。

惟一索引

对于惟一索引就很是简单的了,惟一索引的特性就是数据惟一性,因此查到card = 5这条记录后就不在查找下一条记录了。

普通索引多查询的一次对性能影响大吗?

这个影响几乎能够忽略,在以前的几期文章中咔咔给你们普及了一个名词“局部性原理”。

数据和程序都有汇集成群的倾向,在访问了一条数据以后,在以后有极大的可能再次访问这条数据和这条数据的相邻数据。

因此说MySQL的Innodb存储引擎,在读取数据时也会采起这种局部性原理,每次读取的数据是16kb,也就是一页。

在Innodb存储引擎下每页的大小默认为16kb,这个参数也能够进行调整,参数为innodb_page_size。

但有一种状况虽然说概率很是低,但仍是须要知道的。

当索引为普通索引时,查到的数据正好是一页的最后一个数据,此时就须要读取下一页的数据,这个操做是有点复杂,但对于如今的CPU来讲能够忽略不计。

5、了解change buffer

首先,须要先了解一个新的知识点change buffer。

当须要更新card = 5这条记录时,这条数据所在的数据页在内存中就直接更新,如若不在的话就须要将更新的操做缓存在change buffer中。当下次查询须要访问这个数据页时,将这个数据页读入内存,而后执行change buffer中与这个页有关的操做。

接着,了解另外一个新的知识点merge。

当把change buffer中的数据应用到数据页,获得最新结果的过程成为merge,另外数据库正常关闭的过程当中,也会执行merge操做。

结论:更新操做将记录先记录到change buffer中,能够减小磁盘I/O,语句执行速度会提高。

注意

一、 数据从change buffer读入内存是须要占用buffer pool的,使用change buffer能够避免占用内存。

二、change buffer 也是能够持久化数据的,change buffer 在内存中有拷贝,也会被写入到磁盘。

6、change buffer在什么条件下使用

思考:为何惟一索引使用不到change buffer

惟一索引确定是用不到,对于这个答案若是你感受有点不适,就须要在回到以前几期文章再好好看看。

惟一索引插入一行数据时都会执行一次查询操做判断表中是否已经存在这条记录,判断是否违反惟一约束,既然必须得把数据页的数据读入内存,那还用change buffer个什么劲啊!

所以,只有普通索引可使用。

在上文中知道了将change buffer数据读入内存时是须要占用buffer pool的内存,所以在MySQL中也给了一个参数来设置change buffer的大小。跟其它的数据单位可能有点出入,若设置为30,就表示change buffer只占用buffer pool内存的30%。

思考:在什么场景下不能使用change buffer?

change buffer的做用是将更新的动做缓存下来,因此对一个数据页作merge时,change buffer记录的变动越多,收益就越大。

但也并非全部场景都适用,咔咔目前所开发的是一款帐款软件,大部分更新后都是立马查看,这种状况是否是就违背了上面说的对一个数据页作merge时,change buffer记录的越多,收益越大。

所以,只有写多读少的场景,change buffer才能发挥很是大的做用。

思考:为何更新完立马查询change buffer就没多大用处了呢?

一条记录发起更新操做后,先记录到change buffer 中,接着,当查询的数据在这个数据页时会当即触发merge,这样随机访问的IO的次数不会减小,反而增长了change buffer的维护代价。因此说这种业务模式使用change biffer会起到副作用。

思考:如何关闭change buffer

只须要将参数innodb_change_buffer_max_size = 0 便可。

7、从更新语句性能的影响的角度分析

第一种状况这条数据要更新的数据页在内存中。

惟一索引:在内存中查找是否有这条记录,不存在时则插入这个值。

普通索引:直接更新须要更新的值便可。

结论: 当要更新的数据页在内存中时,惟一索引就比普通索引多一次判断。

第二种状况这条数据要更新的数据页不在内存中。

惟一索引:须要将这条数据所在的数据页读入内存中,查找是否存在这条记录,而后更新数据。

普通索引:将这条要更新的数据记录在change buffer便可。

结论: change buffer 当更新的数据不在数据页中时,若是你的索引是普通索引则能够很显著的提高性能。

注意: 当你把一个索引从普通索引改成惟一索引时必定要注意change buffer的影响,会直接影响内存命中率。

8、总结

回到文章主题如何选择普通索引和惟一索引,在查询方面二者是没有什么差异的,主要是在更新操做上的影响。

若是你的业务跟咔咔的场景同样,更新后立马要对这个记录查询,那么就能够选择直接关闭change buffer。

若不是这种场景,则尽可能选择普通索引,使用change buffer能够很是明显的提高更新性能。

 

坚持学习、坚持写做、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助,我是咔咔,下期见。