mysql （七）索引及查询原理

一、磁盘中的数据页的大概结构

在说索引之前，我们需要先知道大量的数据页是怎么在磁盘中存储的。

1、数据页之间的结构

首先需要明白的是，大量的数据页是按顺序一页一页存放的，然后两两相邻的数据页直接采用双向链表的格式互相引用。大致如下图：

虽然上面的图用比较形象的方式画出数据页在磁盘文件中的存储格式，但其实数据页在文件中可能是以二进制或其他特殊的格式的数据，然后这段1数据中包含两个指针，一个是指向上一个数据页的物理地址1，一个是指向下一个数据页的物理地址。如下：

该红框中可以大概的表示一个数据页的具体结构（不代表磁盘文件就是这么存的），其中linked_list_pre_pointer和link_list_next_pointer即之前说的两个指针。

2、数据页内部

然后在数据页内部会有多个数据行，每行数据都有指针指向下一行数据的位置，组成一个单向链表。

每个数据页中会自己维护一个页目录，该目录记录着这个数据页中各行数据的主键对应的值。且该页目录中存在主键指向数据行槽位的映射关系。（即页目录有 各个主键的值，主键—》数据行槽位，且槽位中存放着各个数据行）

此时如果我们查询数据，定位某个数据页时，就可以去该目录中根据主键进行二分查找。然后迅速定位到主键对应的槽位，从而获取对应的数据。

这个时候就会有疑问？如果此时我是根据非主键查找呢？

————————innodb使用聚集索引+回表保证使用非主键但是索引字段时，最终还是由主键去找数据，但如果你用的是非索引字段，那只能全表扫描了，那性能就会降低很多。

3、页分裂

在我们往某个表不断的插入数据的时候，会涉及一个页分裂的过程。

假设你现在往一个数据页中插入数据，随着插入的数据越来越多，当这个数据页满了后就需要再弄一个数据页。而这个时候就会出现一个问题，其实在mysql中为什么有索引查询就会快很多，这里很大一部分是因为数据页中各行数据的主键值是递增有序的。这样才能使得二分查找迅速的定位到某个数据页、数据页的某个槽位。

那么这时候回到一个数据页满了要用另一个数据页的时候，就会出现一个问题？因为我们前面说索引的依赖就是主键有序递增的，这样才能保证查询数据快速定位。那么加入我现在数据页A满了且其中存在一个最小主键值10，此时我向数据页B中插入一个主键值为8的值。此时出现了后面的数据页的主键值小于前面数据页的主键值。这不就不符合主键递增的说法了。所有mysql为了解决这个问题，就出现了页分裂的这个过程。

——————此时就会将主键较大的挪到后面的数据页，较小的挪到前面的数据页中。（同一个数据页中主键不递增时也会进行前后移动吗？？？）。这个过程就叫页分裂。（建议使用递增主键有一部分原因就是可以减少页分裂过程，从而提高效率）、

二、索引的设计

我们先来说说没有索引的时候，如果我们要查询一个数据，此时是不是只能定位到某个表空间对应的磁盘文件。那么此时你并不知道他在这个文件的哪个数据页，此时只能对整个文件进行全表扫描，此时会到每个数据页的目录中查找主键一个一个扫。最差情况可能扫到最后一个数据页才找到数据。

此时为了解决这样的一个问题，此时就需要我们所谓的索引了。那么索引是如何设计的？

1、主键索引

针对主键的索引设计，此时会维护一个主键目录，该目录就是把每个数据页的页号，还有数据页中最小的主键值放在一起组成一个索引目录（此时保证各个数据页的主键是递增有序的）。

这个时候我们再来查找id为3的数据：此时id为主键，先回去主键目录中先定位该行数据存在哪个数据页，此时经过判断发现id为3的数据页就是页号为2的数据页（有指向磁盘中该数据页的地址）。此时就去磁盘文件中找到页号2的数据页（通过数据页在磁盘文件的offset偏移量随机读的方式定位到磁盘文件的某个offsett偏移量的位置），然后拿着id为3对该数据页的页目录进行二分查找，最后就可以快速的定位到相应数据行的槽位，此时就将其读取到buffer pool提供使用。

（主键目录的各个数据是有序的吗？？？？）

（聚集索引不是说行数据都存在叶子节点上？？为什么这里使用聚集索引要去读磁盘的数据）

---

上面说了主键索引的大概情况，也就是通过主键目录迅速定位到相应的数据页中去二分查找。那么如果此时数据页太多了，也就是数据目录中存在大量的数据。此时该如何优化主键值在主键目录中的查询定位效率。

此时假设有一万个数据，此时我主键值8299进来查询，难道要对整个主键目录进行全目录扫描？

（注意：这里的页号递增，所以其对应的最小主键值也是递增的）

此时会将所有的主键目录数据存到一个数据页中，此时称为索引页。

那如果此时数据数据实在太多，此时一个索引页不够，就会再建一个索引页（每个索引页都有页号）。那么我们一个主键值怎么定位到属于哪个索引页中？
此时就需要再建一个索引页来记录每个索引页号和索引页里的最小主键值。

依次类推，就会出现叶子节点存在的是我们之前的所有页号+主键值。而非叶子节点则是记录各个叶子节点的具体索引页号+最小主键。

所以其非叶子节点是为了我们主键快速定位到相应的索引页（叶子节点），到了索引页再去定位相应的页号（二分查找），然后再在页目录中二分查找到对应的数据行。

这时候看起来这些索引页是不是构成了数据结构中的B+树。

（这里可以去看看B+树的时间复杂度是多少，为什么不用B树、二叉树、hash、链表等其他数据结构？）

（其中图的叶子节点的索引页中的数据（页号）是指向相应磁盘文件的数据页的（这个叶子节点包含数据页数据）---------innodb的聚集索引（innodb的聚集索引就是主键索引，myIsam是非聚集索引））

且叶子节点间是用双向链表进行链接的。

2、二级索引（辅助索引）

上面对于聚集索引（主键索引）的结构进行了分析，那如果我们要设立非主键的索引、甚至是联合索引？

例如我现在又要为name字段建立一个二级索引，此时就会为他再建立一颗B+树，此时这颗树和主键的那颗B+树不同的是叶子节点的索引页存放的数据不同。

在二级索引中，叶子节点的索引页（数据页）存放的是主键+name数据。

如果我们此时根据name来查找数据，此时就会先在name对应的B+树找到相应的主键，然后拿着这个主键再去主键的索引树找到相应的真实数据。（这个过程就叫回表）

注意：这里的叶子节点（数据页）的name值也是类似主键id进行值得递增存储的。

那如果此时是name+age的联合索引？
————————其实这个也一样，只是叶子节点存放的就是主键+name+age。然后排序先按name进行排，相同则按age进行排。

最后的查找方法和上面一致。

三、建立索引时都需要注意的地方

1、一个表的索引是不是越多越好？

不是的，你要知道，每建立一个索引，此时就会建立对应的索引树，这些索引也太多维护起来也是比较耗性能的。并且如果表的数据量太少有时候用索引的性能可能还低于全表扫描。

2、联合索引的全值匹配规则

where 后的字段名称和顺序都和索引字段一样且都是用等号在做等值匹配，此时走的索引就是全值匹配规则。

3、其他常见的索引使用规则

（1）最左侧列匹配

假设我们有联合索引key（class_name,student_name,subject_name）,此时为了保证其走这个索引，其实不一定要where语句后根据这三个字段来查，其实只要根据最左侧的部分字段来此即可。

例如select  *  from student_score  where  class_name='xxxx'  and student_name='xxx';

——————此时是class_name、student_name来的，符合最左原则。则可以用

（class_name,student_name,subject_name）、（class_name）等都可以走索引。

如果变成class_name,subject_name此时只走class_name，不走subject_name。

如果（student_name,subject_name）因为不符合最左原则，则不会走索引

（2）最左前缀匹配原则

一般用于like

例如select  *  from  student_score  where  class_name  like  '1%'

此时查找所有1开头的，这时是可以用索引的。

如果此时变成了select  *  from  student_score  where  class_name  like  '%班'

此时在最左侧用了模糊匹配符，就不会用到索引了。

（3）范围查找规则

select  *  from  student_score  where class_name>'1班'  and class_name<'5班'

会用到索引，会先找到1班对应的数据页，再找到5班对应的数据页。又因为叶子节点是有序双向链表，此时处于中间的就可以用到索引。

如果变成select  *  from  student_score  where class_name>'1班'  and class_name<'5班'  and  student_name>''。此时只有class_name用到索引，student_name是用不到索引的。

（前面有字段进行范围查找，后面的字段都一定用不到索引）

（4）等值匹配+范围匹配的规则

select  *  from  student_score  where class_name='1班'  and student_name<''  and  subject_name>''

现有联合索引（`class_name`, `student_name`, `subject_name`）

此时class_name是等值匹配会走索引，student_name是第一个范围查找会走索引，subject_name位于范围查找后面，不会走索引。

4、sql中进行排序时（order by），怎么才能使用索引

假如我们，select  * from table where xx= xxx  order by xx.（xx为索引字段）

此时会先根据xx= xxx走索引快速找出一部分数据，然后将这些数据放进内存/临时磁盘文件里，然后通过排序算法按某个字段进行排序，最后把排序好的数据返回。

但这样弄会导致速度有点慢，尤其是要排序的数据量比较大的时候，不能用内存来排序，只能基于磁盘文件来进行排序。那这样在mysql有一个术语“filesort”。这样速度就更慢了。

尤其是一些不仅要排序还有进行分页的sql那会更加慢。例如select  *  from  table  by  xx1,xx2,xx3  limit 100

那么我们怎么来优化上面的语句？
——————此时就可以为xx1、xx2、xx3来建立一个联合索引。此时排序就不用在磁盘文件中排序，而是xx1、xx2、xx3先一次排好序，然后建立自己的索引树，此时我们再去查询排序好的数据只需要通过索引即可实现了。大大的提高了性能。

但这里有一些限制，就是要所有排序字段都是升序或者降序。

5、sql使用分组时（group by），怎么才能用到索引？

如果我们没用索引时，此时执行sql: select  count(*)  from table  group by xx;

此时会把所有数据放到临时磁盘文件和部分内存，然后再根据xx将这些数据分成一组一组的。

接着就对每组都执行一个count聚合函数，最后叠加起来就是结果。但这样有大量的磁盘交互，性能会比较差。

为了优化这个问题，此时我们可以将xx配置为走索引，此时我们就不用进行分组了。因为此时由于已经按xx进行排序，所以直接可以取到一组一组的数据。基本的工作只是对每一组执行聚合函数。

所以order by和group by都是利用索引树的有序性，从而减少了自己的磁盘或内存的排序操作。

6、回表查询对性能的影响和覆盖索引

（2）回表查询：

上面已经说明，如果回表前在联合索引查到得数据太多，此时在去聚集索引树查找，此时的性能也是比较差的。所以可以在联合索引进行查找的时候加上where或者limit等条件尽量减少从联合索引筛选出来的数据条目。

（1）覆盖索引：

例如现在有表id、name、sex、age、class字段，其中id为主键索引，另外再建立联合索引（name、sex、age）。

那么如果我们此时进行查询

select  *  from  student  where  name=‘xx’  and  sex=‘男’ and age = '18';

此时就会走联合索引（name、sex、age），然后根据联合索引找到相应的主键，然后再根据主键再去主键索引树找真正的数据（此时要经过回表过程）。那如果此时我们改一下sql

select  name,sex,age  from  student  where  name=‘xx’  and  sex=‘男’ and age = '18';

此时就不会去走回表，因为我们之前说过，辅助索引的叶子节点放的数据是主键+name+sex+age（主键+索引字段）。所以此时要查的id、name、sex、age都在这里，则不需要再去进行一次回表操作。

而查询的字段存在索引中。

四、设计索引考虑的因素+索引案例

一、设计索引时，我们一般要考虑哪些因素？

一般的话建立索引的时机是在我们基本的完成了sql语句编写，为了提高sql语句效率，此时才进行设计索引和优化sql语句。那么怎么尽量的去设计好索引？
1、针对sql语句里的where 、order by、group by的条件字段进行设计索引

（前面已经说过了这些没用索引时是全表扫描、临时文件操作等耗时操作）

注意：要走最左侧匹配原则

2、尽量不要对字段基数低的字段建立索引

例如现在有一个字段有10万行数据，此时这个字段只有两个对应的值1和0。

那么此时对其建立索引的话，索引树只有0和1的两种值。无法进行快速的二分查找，此时还不如进行全表扫描算了。所以这个时候选用基数低的字段建立索引对性能提升不是特别大。

所以尽量选择基数大的，能够尽量的发挥出B+树快速二分查找的优势出来。

3、尽量选择字段类型比较小的列来设计索引

例如tinyint等，因为其字段类型比较小，说明这个字段本身的值占用的磁盘文件也小，搜索的时候性能也小。当然如果大的字段不是也不行，如果对大的字段进行设计索引性能提升多那是必须要设计的。

其次，在设计大的字段为索引时，例如varchar（255），此时可以换一种策略进行，此时你可以针对varchar（255）字段的前20个字符建立索引I（也称前缀索引）。这样就可以减少索引中字段的长度，尽量的提升性能。

4、尽量不要用一些函数、计算等操作

例如where function（a）= xx。此时你给索引a套了一个函数，这样就会用不上索引了。

（还有就是能用自增主键一般就不要用uuid之类的无序索引，因为主键自增，最起码你的聚集索引不会频繁的分裂，主键值有序就会自然的新增一个页而已，如果是无序的uuid之类的，此时就会导致聚集索引的频繁的页分裂现象）

二、索引设计案例

假设我们现在来设计一个陌生交友app。

1、此时来设计用户表

此时大概需要包含（省份、城市、性别、年龄、身高、体重、兴趣爱好、性格特点、照片、最近一次上线时间）。其中有些用于搜索、有些用于推荐。

当然如果有评价功能还需要有一个个人综合评分等。

这里要说明一个场景：where和order by很多情况下无法都用到索引

例如：select  xx  from user_info  where  age between 20  and 25  order by score。

此时我们设计一个索引（age、score），此时前面进行了范围查找，后面的score就用不到索引了。

如果分别给age、score设立自己的索引，此时sql先对age进行筛选，那也没办法利用另一个score索引进行排序（同一个sql）而是对age筛选出来的数据进行filesort（order by和group by经常会涉及到对磁盘文件的操作filesort之类的）（注意：同一个select查询中，如果前面用了范围查找，此时后面是不能走索引的）

2、上面知道，一般where和order by冲突时，这个时候要用哪个索引？？？

一般的话还是建议使用where条件去使用索引，然后筛选出来的数据去内存或者磁盘文件中进行排序、limit取数据等。因为where往往可以筛选出少部分符合的数据，然后排序和分页等就不用基于太大的基数进行计算。当然这是基于筛选出来的数据不多的情况。如果数据太大的话，其实怎么弄效率也不会提升太多。

3、好、我们决定了要基于where去设计这个索引。

那么你想想我现在去搜索一个陌生人，此时应该会用上哪些条件？
根据常识，此时首先应该在联合索引中包含省份、城市、性别这三个比较重要的字段。

此时可能就有疑问，之前不是说基数比较低的不要用索引吗？而这里比较特殊，基本每次的查询都是需要用到这三个字段的，虽然基数低的字段进行索引查询性能提升的不高，但是如果大量的查询都需要用到这三个，那这就必须的上索引了。

4、除了加入最常用的字段，还要加其他哪些字段？

上面建立了最常用得字段后，下面来加入一些其次的字段索引

此时我们想是不是得加个年龄,这里要注意，因为如果年龄搜索得话，那你一般是不是都是范围查询的，而我们前面说过，同一个select前面的用范围查找了后面的就用不了索引了。这个时候就可以把age放在索引的最后面（province，city，sex，age）。

这样我进行where  province = xx and city = xx and sex in（xx，xxx）and age>= xx and age < = xxx

此时所有字段都用得到索引了。（age 因为索引字段，索引树顺序排好，所以可以进行范围查询）

-----加完年龄后，我们再往其中加入一个爱好的字段。此时爱好不就太多了？此时可以学着age的方式，将各个爱好用枚举值进行表示，然后用in来判断是否在这些枚举值中。这样其他的字段使用索引就不会受到影响了。

where  province = xx and city = xx and sex in（xx，xxx） and hobby in（xxx，xx，xx，xx） and age>= xx and age < = xxx

注意这里的age也要放在索引字段中的最后一个（province，city，hobby，sex，age）（范围查找字段放最后）

5、多个范围查找字段，此时该怎么办？
例如我现在要再加一个字段latest_login_time表示最近几天没登录了。此时你会发现，age和latest_login_time两个都是范围查找，那么不就冲突了。（前面用了范围查找后面就用不了索引了）

此时我们换一种方式来表示用户最近登录时间。此时我们可以换成does_login_in_latest_7_days,也就是说换成最近7天内是否登录过。这样就可以用1或0来代表了。从而解决了两个范围查找字段的冲突了。

6、联合索引太长，不适用某些场景的查询怎么办？
由于上面设立的联合索引太长，如果我现在有个场景只需要根据评分排序查询所有女性的数据。此时用到的字段是（sex，score）。那么进行查询，由于最左匹配原则，此时是走不了这个索引的，此时就需要我们建立另一个辅助索引了。where sex =xx order by score limit xx，xxx

（多字段联合索引+简单的辅助索引）

下面将执行计划+explain的使用