大话Mysql索引

j

... ...

第五个不小的问题：如何新建好的索引？也可以说：索引如何优化？这是一个超大的问题，这里只是列举几个我自己使用并掌握的方法：

r

0

女

槽-2

1

数据以某种结构存储起来，这种结构包含一套逻辑关系，一套操作（CRUD）

3

2

页-1 (Page-1)  => 16k

业务数据的主键，前面说了，mysql的数据会根据主键或者隐式row_id排序存储

g

e

页-6 (Page-6)  

a

第一个小问题：如上图，我们建立了lastname，firstname，dob联合索引后，到底索引的是什么呢？ ->其实我们会将这些列的值索引起来（言外之意：我们会储存实际的数据）。仔细想想，如果不储存数据的话，查询的时候怎么比对呢？肯定是和实际的业务数据比对才对吧，所以肯定是存储业务数据！但是会存储成一种数据结构，比如我们98.74%的场景下都在使用的B+树。

页-2 (Page-2)  

d

一页的数据结构很清晰了，但是mysql不可能就只存储16k的数据吧，数据多了肯定是一页一页的存， 这些页之间是什么关系呢？

l

5

页-5 (Page-5)  

第二个小问题：感觉加了索引如果生效，查询就会变快！这是为啥呢？如果没有索引查找的话就是全表扫描，要一点一点的扫磁盘读文件，当数据多的时候会有想当高的IO(量大，次数多)，自然而然就慢！如果有索引的话，前面说了本身数据是一种B+树的结构存储，查找很快。而且我们只需要读取索引查找，不需要load全量数据，IO低很多(量少，次数少)，那可不就快了吗？快了就快乐！

>

9

k

男

第一组

索引本质是一种排好序的数据结构！

槽-0

第三个小问题：如果我们没有主动新建索引，那么数据是如何存储的呢？这里抛下一个反常识的结论：所有的数据必须全部与索引存储在一起（不论你是否有建立索引）！“大聪明”：啥玩意儿？？？没错，就是这样的！主键索引是聚集索引（说白了索引和数据搁一块），而普通的索引是非聚集的（数据是放到主键索引中，这里只放主键的地址）。

b

页-3 (Page-3)  

引申出聚集和非聚集索引

m

4

废话不多说！

     上面是mysql存储真实的一页（page）数据的结构。可以看到，数据在page里面被分成了很多组，没错！就是这样的，每个组其实就是一个slot（槽，槽本身记录着该组最大的那个record位置），且从第一组开始一直到最后一组，每个组里都是一些record，这些record本身有一个指针指向下一条记录，所以构成了一个单向链表。     其中有两个比较特殊的组，第一组和最后一组，第一组只放了一个元素->infimum占位记录，这个记录代表这这一页的最小数据。最后一组的最后一个record类型是3->supremum，表示这个page数据的结束了，且记录了最大的那条记录的地址。     且mysql规定b style=\

说明

page与page之间组成了一个双向链表，但是还是跟一页一样符合这样的规律：1. 页内部是一个单向链表，主键递增；2. 页与页是一个双向链表，下一页最小值>上一页的最大值；3. 数据整体按主键递增的方式排列。4. 页内部空间是连续的，但是页与页之间不是物理连续的，只需要一个指针保持逻辑连续就行了。没那么多讲究。

我知道，有些老二哥会问：既然索引页最大也是16k，那么如果数据超级多，就算索引record只放一个主键，也放不来多少啊？？

record_type 记录类型：0：真实业务记录，2：infimum：占位记录（当前page最小记录）本身不存储业务数据3：supremum：占位记录（当前page最大记录）本身不存储业务数据

页-1 (Page-1)  

第四个小问题：为啥DBA一直让我们建表的时候建主键且整型自增呢？因为前面说了，innodb的数据全部依附索引，就算你不见主键，mysql也会在表里帮我们找唯一索引，找不到就会整一个隐式的rowid索引！为了少让mysql维护这些东西（多关心查找），所以强推自己建好主键。自增就跟数据结构有关系了，B+树的主键索引中，数据是排好序在叶子节点的，如果是自增，当我们insert的时候就不停地‘append’就行了，不会改变前面已经分好的页，但是如果是非自增类似uuid，那么insert的时候有可能改变前面的页，让树Rebalance（page split页的分裂），这个是要损耗性能的。而且更耗空间（奇怪吗？不奇怪，因为b树是一种数据结构，页分裂的操作本身也会占用一些空间实现，而且分裂后的空间肯定没有之前的连续，所以不可避免的产生内存碎片）

引申出索引的三个优点

槽-1

8

有了这些数据，现在开始进入主题了。根据这样的Page页数据，我们如何建立索引来满足高效的快速查找，范围查找呢？？

上面说了这么多，我感觉不对劲啊！怎么还是这么绕呢？？？？我需要拨开云雾看看实际上mysql到底是怎么玩的！

页-7 (Page-7)  

1. 大大大的减少了mysql服务器需要扫描的数据量

h

3. 可以将随机IO变为顺序IO（B+树数据结构）

1. 首先要说明的是聚集和非聚集（又名聚簇）并不是一种  索引单独的索引类型(就是B树)，而是数据的存储方式

next_record：简单理解成指向下一个记录的指针（实际上page的空间是连续的，所以是到下一个记录的真实数据的空间大小，也可以说是距离）

f

老哥，左边这个索引和数据的结构就不用再说了吧！那么，如果我们建立一个联合索引，真实的数据是如何存储的呢？（滑稽）

问题来了：一个表可以有多个聚集索引吗？回答：不可以，为啥呢？！因为数据只会放到一个地方，不会冗余（主从不算）而聚集是索引数据放一起，所以只有一个！我们甚至可以这么说：innodb的聚集索引就是表！（大胆点）

首先我想说，老二哥，你说的对！但是前面数据页间已经是构成了双向链表了，索引页也是页，整成一样的不就得了嘛，哪儿来的那么多事？？

槽-3

第四组

6

w

为了看页和页的关系，先简化一页的结构

2. 可以帮助mysql服务器避免排序和临时表（order by，group by）

第二组

第三组

第五个小问题：索引这么好，是不是一定要建索引？（除了主键索引）好不好是相对而言的！有大钱好，但是你拿着100块钱买一瓶5毛钱的水，找你一堆钱，你收起来也不方便对吧。所以如果mysql通过索引能实现快速查找带来的好处大于维护索引带来的额外工作，那么就没问题，反之不需要建索引。比如我们通常会有一个表来放配置数据还有一些国际化之类的数据，这种非业务数据全表扫更高效，完全不需要索引。