MySQL索引的坑,谁踩谁知道hell [复制链接]

索引能够说是数据库中的一个大心脏了，若说一个数据库少了索引，辣么数据库本人存在的作用就不大了，和一般的文件没甚么两样。因此说一个好的索引对数据库体系尤为紧张，本日来说说MySQL索引，从细节和现实交易的角度看看在MySQL中B+树索引作用，以及咱们在应用索引时需要留意的常识点。

一、合理行使索引

在工作中，咱们大约判断数据表中的一个字段是不是需要加索引的非常干脆设施即是：这个字段会不会时常发当今咱们的where前提中。从宏观的角度来说，如许思索没有疑问，不过从久远的角度来看，偶然大约需要更详尽的思索，好比咱们是不是不单单需要在这个字段上确立一个索引？多个字段的团结索引是不是更好？以一张用户表为例，用户表中的字段大约会有效户的姓名、用户的身份证号、用户的家庭地点等等。

1、一般索引的短处

当今有个需要需要凭据用户的身份证号找到用户的姓名，这时分非常鲜明想到的第一个设施即是在id_card上确立一个索引，严酷来说是唯独索引，由于身份证号必定是唯独的，辣么当咱们实行如下盘问的时分：

SELECTnameFROMuserWHEREid_card=xxx

它的流程应当是如许的：

先在id_card索引树上搜索，找到id_card对应的主键id

经历id去主键索引上搜索，找到对应的name

从结果上来看，结果是没疑问的，不过从服从上来看，宛若这个盘问有点高昂，由于它检索了两颗B+树，假定一颗树的高度是3，辣么两颗树的高度即是6，由于根节点在内存里（此处两个根节点），因此非常终要在磁盘长进行IO的次数是4次，以一次磁盘随机IO的时间平衡耗时是10ms来说，辣么非常终就需要40ms。这个数字一般，不算迅速。

2、主键索引的圈套

既然疑问是回表，导致了在两颗树都检索了，辣么焦点疑问即是看看能不能够只在一颗树上检索。这里从交易的角度你大约发掘了一个切入点，身份证号是唯独的，辣么咱们的主键是不是能够不消默许的自增id了，咱们把主键配置成咱们的身份证号，如许全部表的只需要一个索引，并且经历身份证号能够查到全部需要的数据包含咱们的姓名，简略一想宛若有事理，只有每次插入数据的时分，指定id是身份证号就行了，不过周密一想宛若有疑问。

这里要从B+树的特色来说，B+树的数据都存在叶子节点上，并数据是页式经管的，一页是16K，这是甚么作用呢？哪怕咱们当今是一行数据，它也要占用16K的数据页，惟有当咱们的数据页写满了往后才会写到一个新的数据页上，新的数据页和老的数据页在物理上不必然是陆续的，并且有一点非常环节，固然数据页物理上是不陆续的，不过数据在逻辑上是陆续的。

也能够你会猎奇，这和咱们说的身份证号当主键ID有甚么干系？这时你应当眷注「陆续」这个环节字，身份证号不是陆续的，这意味着甚么？当咱们插入一条不陆续的数据的时分，为了连结陆续，需要挪动数据，好比本来在一页上的数占有1-5，这时分插入了一条3，辣么就需要把5移到3背面，也能够你会说这也没几许开支，不过若当新的数据3导致这个页A满了，辣么就要看它背面的页B是否有空间，若有空间，这时分页B的首先数据应当是这个从页A溢出来的那条，对应的也要挪动数据。若此时页B也没有充足的空间，辣么就要请求新的页C，而后移一片面数据到这个新页C上，并且会割断页A与页B之间的干系，在两者之间插入一个页C，从代码的层面来说，即是切换链表的指针。

总结来说，不陆续的身份证号当主键大约会导致页数据的挪动、随机IO、频仍请求新页关联的开支。若咱们用的是自增的主键，辣么关于id来说必然是挨次的，不会由于随机IO导致数据挪动的疑问，在插入方面开支必然是相对较小的。

实在不保举用身份证号当主键的另有别的一个缘故：身份证号作为数字来说太大了，得用bigint来存，平常来说一个黉舍的门生用int曾经充足了，咱们晓得一页能够寄放16K，当一个索引本人占用的空间越大时，会导致一页能寄放的数据越少，因此在必然数据量的环境下，应用bigint要比int需要更多的页也即是更多的存储空间。

3、团结索引的矛与盾

由上头两条论断能够得出：

尽管不要去回表

身份证号不适用当主键索引

因此天不过然地想到了团结索引，确立一个的团结索引，留意团结索引的挨次，要合乎非常左准则。如许当咱们一样实行如下SQL时：

selectnamefromuserwhereid_card=xxx

不需要回表就能够获得咱们需要的name字段，不过或是没有办理身份证号本人占用空间过大的疑问，这是交易数据本人的疑问，若你要办理它的话，咱们能够经历少许转换算法将本来大的数据转换成小的数据，好比crc32：

crc32.ChecksumIEEE([]byte(""))

能够将本来需要8个字节存储空间的身份证号用4个字节的crc码替换，因此咱们的数据库需要再加个字段crc_id_card，团结索引也从导致了，团结索引占的空间变小了。不过这种转换也是有价格的：

每次分外的crc，导致需要更多cpu资源

分外的字段，固然让索引的空间变小了，不过本人也要占用空间

crc会存在辩论的概率，这需要咱们盘问出来数据后，再凭据id_card过滤一下，过滤的老本凭据重叠数据的数目而定，重叠越多，过滤越慢。

关于团结索引存储优化，这里有个小细节，假定当今有两个字段A和B，划分占用8个字节和20个字节，咱们在团结索引曾经是[A,B]的环境下，还要支撑B的独自盘问，因此天不过然咱们在B上也确立个索引，辣么两个索引占用的空间为8+20+20=48，当今不管咱们经历A或是经历B盘问都能够用到索引，若在交易容许的前提下，咱们是否能够确立[B,A]和A索引，如许的话，不但知足独自经历A大约B盘问数据用到索引，还能够占用更小的空间：20+8+8=36。

4、前缀索引的短小干练

偶然候咱们需要索引的字段是字符串范例的，并且这个字符串非常长，咱们有望这个字段加上索引，不过咱们又不有望这个索引占用太多的空间，这时能够思量确立个前缀索引，以这个字段的前一片面字符确立个索引，如许既能够享用索引，又能够节减空间，这里需要留意的是在前缀重叠度较高的环境下，前缀索引和一般索引的速率应当是有差异的。

altertablexxaddindex(name(7));#name前7个字符确立索引

selectxxfromxxwherename="JamesBond"

5、唯独索引的迅速与慢

在说唯独索引以前，咱们先打听下一般索引的特色，咱们晓得关于B+树而言，叶子节点的数据是有序的。

假定当今咱们要盘问2这条数据，辣么在经历索引树找到2的时分，存储引擎并无休止搜索，由于大约存在多个2，这阐扬为存储引擎会在叶子节点上接着向后查找，在找到第二个2往后，就休止了吗？谜底是否，由于存储引擎并不晓得背面另有没有更多的2，因此得接着向后查找，直至找到第一个不是2的数据，也即是3，找到3往后，休止检索，这即是一般索引的检索历程。

唯独索引就不一样了，由于唯独性，不大约存在重叠的数据，因此在检索到咱们的指标数据往后干脆回笼，不会像一般索引那样还要向后多查找一次，从这个角度来看，唯独索引是要比一般索引迅速的，不过当一般索引的数据都在一个页内的话，实在也并不会迅速几许。在数据的插入方面，唯独索引大约就稍减色，由于唯独性，每次插入的时分，都需要将判断要插入的数据是否曾经存在，而一般索引不需要这个逻辑，并且非常紧张的一点是唯独索引会用不到changebuffer（见下文）。

6、不要盲目加索引

在工作中，你大约会碰到如许的环境：这个字段我需不需要加索引？。关于这个疑问，咱们经常使用的判断手法即是：盘问会不会用到这个字段，若这个字段时常在盘问的前提中，咱们大约会思量加个索引。不过若只凭据这个前提判断，你大约会加了一个毛病的索引。咱们来看个例子：假定有张用户表，大约有w的数据，用户表中有性格别字段显露男女，男女差未几各占一半，当今咱们要统计全部男生的消息，而后咱们给性别字段加了索引，并且咱们如许写下了SQL：

select*fromuserwheresex="男"

若不出不测的话，InnoDB是不会选定性别这个索引的。若走性别索引，辣么必然是需要回表的，在数据量非常大的环境下，回表会导致甚么样的结果？我贴一张和上头一样的图想必朋友们都晓得了：

要紧即是大批的IO，一条数据需要4次，辣么50w的数据呢？结果不行思议。因此针对这种环境，MySQL的优化器大约率走全表扫描，干脆扫描主键索引，由于如许机能大约会更高。

7、索引无效那些事

某些环境下，由于咱们本人应用的欠妥，导致MySQL用不到索引，这一般非常轻易产生在范例转换方面，也能够你会说，MySQL不是曾经支撑隐式转换了吗？好比当今有个整型的user_id索引字段，咱们由于盘问的时分没留意，写成了：

selectxxfromuserwhereuser_id=""

留意这里是字符的，当产生这种环境下，MySQL确凿充足伶俐，会把字符的转成数字的，而后兴奋的应用了user_id索引。不过若咱们有个字符型的user_id索引字段，或是由于咱们盘问的时分没留意，写成了：

selectxxfromuserwhereuser_id=

这时分就有疑问了，会用不到索引，也能够你会问，这时MySQL为何不会转换了，把数字的转成字符型的不就行了？这里需要注释下转换的规律了，当发掘字符串和数字相对的时分，要记着：MySQL会把字符串转换成数字。

也能够你又会问：为何把字符型user_id字段转换成数字就用不到索引了?这又要说到B+树索引的布局了，咱们晓得B+树的索引是根据索引的值来分叉和排序的，当咱们把索引字段产生范例转换时会产生值的变更，好比本来是A值，若实行整型转换大约会对应一个B值（int(A)=B）,这时这颗索引树就不能够用了，由于索引树是根据A来组织的，不是B，因此会用不到索引。

两索引优化

1、changebuffer

咱们晓得在更新一条数据的时分，要先判断这条数据的页是否在内存里，若在的话，干脆更新对应的内存页，若不在的话，只能去磁盘把对应的数据页读到内存中来，而后再更新，这会有甚么疑问呢？

去磁盘的读这个行动稍显的有点慢

若同时更新许多数据，辣么即有大约产生许多分离的IO

为打听决这种环境下的速率疑问，changebuffer发掘了，首先不要被buffer这个单词误导，changebuffer除了会在大众的bufferpool里以外，也是会永远化到磁盘的。当有了changebuffer往后，咱们更新的历程中，若发掘对应的数据页不在内存里的话，也不去磁盘读取响应的数据页了，而是把要更新的数据放入到changebuffer中，那changebuffer的数据甚么时候被同步到磁盘上去？若此时产生读行动奈何办？首先背景有个线程会按期把changebuffer的数据同步到磁盘上去的，若线程还没来得及同步，不过又产生了读操纵，辣么也会触发把changebuffer的数据merge到磁盘的事务。

需要留意的是并不是全部的索引都能用到changerbuffer，像主键索引和唯独索引就用不到，由于唯独性，因此它们在更新的时分要判断数据存不存在，若数据页不在内存中，就务必去磁盘上把对应的数据页读到内存里，而一般索引就没干系了，不需要判断唯独性。

changebuffer越大，表面收益就越大，这是由于首先分离的读IO变少了，其次当一个数据页上产生屡次变更，只需merge一次到磁盘上。固然并不是全部的场景都适用changebuffer，若你的交易是更新往后，需要立马去读，changebuffer会拔苗助长，由于需要一直地触发merge行动，导致随机IO的次数不会变少，反而增长了保护changebuffer的开支。

2、索引下推

前方咱们说了团结索引，团结索引要知足非常左准则，即在团结索引是[A,B]的环境下，咱们能够经历如下的sql用到索引：

select*fromtablewhereA="xx"

select*fromtablewhereA="xx"ANDB="xx"

实在团结索引也能够应用非常左前缀的准则，即：

select*fromtablewhereAlike"赵%"ANDB="上海市"

不过这里需要留意的是，由于应用了A的一片面，在MySQL5.6以前，上头的sql在检索出全部A是“赵”开首的数据往后，就立马回表（应用的select*），而后再比拟B是不是“上海市”这个判断，这里是不是有点懵？为何B这个判断不干脆在团结索引上判断，如许的话回表的次数不就少了吗？导致这个疑问的缘故或是由于应用了非常左前缀的疑问，导致索引固然能应用片面A，不过彻底用不到B，看起来是有点“傻”，因而在MySQL5.6往后，就发掘了索引下推这个优化（IndexConditionPushdown）,有了这个功效往后，固然应用的是非常左前缀，不过也能够在团结索引上搜索出合乎A%的同时也过滤非B的数据，大大削减了回表的次数。

3、革新毗邻页

在说革新毗邻页以前，咱们先说下脏页，咱们晓得在更新一条数据的时分，得先判断这条数据地点的页是否在内存中，若不在的话，需要把这个数据页先读到内存中，而后再更新内存中的数据，这时会发掘内存中的页有非常新的数据，不过磁盘上的页却仍然是老数据，辣么此时这条数据地点的内存中的页即是脏页，需要刷到磁盘上来连结同等。

因此疑问来了，甚么时候刷？每次刷几许脏页才适宜？若每次变更就刷，辣么机能会非常差，若非常久才刷，脏页就会聚积许多，导致内存池中可用的页变少，进而影响平常的功效。因此刷的速率不能够太迅速但要实时，MySQL有个清算线程会按期实行，包管了不会太迅速，当脏页太多大约redolog曾经迅速满了，也会登时触发刷盘，包管了实时。

在脏页刷盘的历程中，InnoDB这里有个优化：若要刷的脏页的朋友页也脏了，辣么就顺带一路刷，如许的作用即是能够削减随机IO，在机器磁盘的环境下，优化应当挺大，不过这里大约会有坑，若目前脏页的朋友脏页在被一路刷入后，朋友页立马由于数据的变更又变脏了，那此时是不是有种画蛇添足的感受，并且反而铺张了时间和开支。更倒霉的是若朋友页的朋友也是脏页...，辣么这个连锁反馈大约会发掘瞬间的机能疑问。

4、MRR

在现实交易中，咱们大约会被见知尽管应用笼盖索引，不要回表，由于回表需要更多IO，耗时更长，不过偶然候咱们又不得不回表，回表不单单会导致过量的IO，更紧张的是过量的分离IO。

select*fromuserwheregradebetween60and70

当今要盘问结果在60-70之间的用户消息，因而咱们的sql写成上头的那样，固然咱们的grade字段是有索引的，根据常理来说，会先在grade索引上找到grade=60这条数据，而后再凭据grade=60这条数据对应的id去主键索引上找，非常后再次回到grade索引上，一直的重叠一样的行动...。

假定当今grade=60对应的id=1，数据是在page_no_1上，grade=61对应的id=10，数据是在page_no_2上，grade=62对应的id=2，数据是在page_no_1上，因此确凿环境即是先在page_no_1上找数据，而后切到page_no_2，非常后又切回page_no_1上，但实在id=1和id=2彻底能够归并，读一次page_no_1即可，不但节减了IO，同时以免了随机IO，这即是MRR。当应用MRR往后，帮助索引不会登时去回表，而是将获得的主键id，放在一个buffer中，而后再对其排序，排序后再去挨次读主键索引，大大削减了分离的IO。