MySQL学习笔记之索引

我们的数据存储于计算机的磁盘上，以512个字节为一个扇区，操作系统存取数据以一个簇为基本单位，一个簇包含多个扇区，InnoDB的数据页大小是16KB每页，同一个应用程序的数据可能是连续存储的，也可能是分开存储的，如果没有索引，MySQL要查找指定的数据，只能挨个读取各个扇区的数据并进行比较，如果有一种方法能够让我们直接找到数据所在的扇区，那么无疑会大大提高我们查找数据的效率，而这个方法就是使用索引。通过查找索引，能够让我们快速找到数据所在物理地址。

1.索引常用的数据结构

索引常用的数据结构有哈希表、有序数组、搜索树三种。

哈希表是K-V形式的数据结构，通过哈希函数计算key值得到一个数组下标，通过这个下标找到相应的链表，再遍历链表比较值。适用于等值查询，不适用于范围查询。范围查询需要每个值都进行一遍哈希查找。

有序数组将数据按照递增的方式存储，适用于等值查询和范围查询，等值查询可通过二分法查找；范围查询可通过二分法先查到首数据，再向右遍历找到最后一个值，哈希表需要hash计算、取出链表、遍历链表比较大小几个步骤，而有序数组找到第一个数据之后只需要顺序遍历比较大小就可以取到需要的值，效率更高。但有序数组也有个问题，更新效率太差，只适用于静态表。

搜索树，查找和更新效率都属于中间的数据结构。

2.InnoDB的索引数据结构

InnoDB采用B+树数据模型，树高为4的时候就能表示17亿数据，而每个节点都设计成占用一个扇区，所以即使的最坏情况，一次查找也最多访问3次磁盘就能查找到相关的数据页。而B+树每个节点的顺序是递增排列，兄弟节点之间也是递增排列，通过上一个兄弟节点能找到下一个兄弟节点的存储位置。

但B+树也有一个缺点，新增数据的时候如果从中间插入，可能会引起页分裂，而主键索引又存储了所有的数据信息，一旦引起页分裂就会产生很多额外操作，而使用自增字段做主键就可以避免这样的问题。

3.索引相关概念

回表：InnoDB中主键索引包含了当前表的所有字段信息，比如name字段上有普通索引，在name的索引上只包含name和ID两个字段的信息，如果select age from student where name = '小明'，那么执行过程是先去name索引查找小明，再拿到相应的数据去查找主键索引。这个过程就称为回表。

覆盖索引：比如我们select age from student where name = '小明' ，我们为了省略这个回表的过程，就建立(name,age)的联合索引，而这个覆盖了我们所有查询信息的索引，就称为覆盖索引。

最左查询原则：索引的B+树存储结构当中，按照第一个字段的递增顺序排列，第一个字段相同，则按照第二个字段的递增顺序排列，以此类推。比如(name,age)联合索引，我们查找name,或者name and age都可以使用到联合索引(name,age)，但单独查找age就不能用到该索引。

索引下推：select * from student where name like '张%' and age = 10，对于这样的查询语句，(1)、先在(name,age)中查找到姓张的所有数据，(2)、对(1)中查找到的数据进行age = 10的筛选，(3)、进行回表查询。如果没有索引下推，判断age的步骤就在主键索引中完成。

4.普通索引和唯一索引的比较

查询过程：唯一索引在B+树上查到数据就立刻返回，普通索引在B+树上查到数据之后一直向后遍历，直到找到第一个不符合条件的数据，这个过程的性能差别微乎其微。或者刚好查询的数据是在一页的最后一条数据上，要找下一条数据就必须访问磁盘获取下一页，这个操作性能消耗多一点，但一个数据页能存放上千个Key，平均下来这也是可以忽略不计的。

更新过程：普通索引的数据更新，(1)如果内存中没有相应数据页，会将更新内容记录在change buffer中，直到有线程访问相应数据页，才触发merge过程，即将数据写入磁盘中。MySQL后台有线程定时merge，MySQL正常关闭也会merge。而唯一索引的每一次更新，都必须将响应数据页更新到磁盘当中进行唯一性校验，不能够使用change buffer记录。 也就是说，在写多读少的业务场景中，使用普通索引+change buffer可以将随机写磁盘变为顺序写磁盘，提高写磁盘的性能。(2)如果内存中有相应数据页，那么直接在内存中对数据页进行修改，下一个读请求访问的时候，也直接从内存中获取数据进行返回。这一步普通索引和唯一索引是没有区别的。

注：change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

5.遇到MySQL选错索引怎么办？

有时候由于MySQL优化器的一些bug，无法选择到执行速度快的索引。如果是由于索引统计信息不准确导致的，可以使用analyze table解决。如果还是不行，简单粗暴的方法是force index吧，直接人为指定，但如果数据库索引名称改动或者迁移数据库，很可能需要动手修改相应的sql语句。

6.给字符串加索引

前缀索引：比如邮箱这样的字段，我们可以给邮箱加上前缀索引，每个字符串都只取前面6个或者7个字符，好处是可以节省索引存储空间。至于选择多少个字符作为前缀，需要根据业务来进行判断，如果6个能够区分开每个字符串，而5个不行，那么就选择6个。前缀索引的缺点是不能使用到覆盖索引，因为查找到的字符串前缀必须要通过回表进行再次验证，才能够保证所查的数据就是需要的数据。

倒序存储：如果有的字符串前缀前面区分度不高，后面区分度高，可以存储时将使用reverse函数将数据翻转，然后建立前缀索引。

hash存储：给表新增一个字段，将字符串经过hash计算后的值存入，然后给该字段创建索引，可以使用mysql提供的crc32()函数。查询时为了避免计算后的hash值相同，需要把获取到的结果再精确匹配一下。如下代码

select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string'

7.order by排序过程

全字段排序：比如select name,age from student where city = '四川' order by name limit 1000 这样的查询，InnoDB初始化一个sort_buffer，先查city索引上所有符合city='四川'的数据，逐行取出ID到主键索引获取name,age字段，存入每个线程都有的sort_buffer，给sort_buffer放入所有city = '四川'的数据之后按照name排序，取前1000行排序结果返回给客户端。

如果sort_buffer_size大小超过排序所需数据的大小，那么可以在内存中完成排序；如果需要排序的数据过大，那么会使用到磁盘临时表进行排序，那么就会有大量磁盘IO操作。

rowid排序：为了提升性能，使用尽量少的磁盘临时表，可以设置max_length_for_sort_data参数，用于控制sort_buffer中的字段总长度，比如上面的排序需要name,id两个字段，而这两个字段加起来长度为18，那么可以设置max_length_for_sort_data参数为18，这样InnoDB就可以在sort_buffer中对这两个字段的值进行排序，然后拿到前1000行的id值去主键索引当中获取相应数据，返回给客户端。

最后一种方式是使用覆盖索引的排序，因为索引存储本身是按照递增的顺序排列的，如果有(city,name,age)这样的联合索引，那么我们查找该联合索引获取相同城市的数据，就是按照name升序排列的，获取limit 1000的数据也就可以不需要经历排序过程。

优先队列排序：filesort_priority_queue_optimization，比如我们对数据进行limit 3选取，那么该算法不会对所有匹配的结果进行排序，而是会维护一个大小为3的堆，如果我们要取的是最小的三个数，那么会把每一次的新数据拿来和堆中值最大的数进行比较，如果有新的更小的数，就把这个堆中最大的值给替换掉，这样遍历一次所有数据之后，就排出了limit 3的数据。不需要使用临时表，通过sort_buffer就可以搞定了。上面的例子没有使用优先队列排序，是因为limit 1000需要维护一个大小为1000的堆，而1000的堆已经超过了我们sort_buffer_size所设置的大小，所以必须要使用到磁盘临时表。

8.几个隐藏类型转换的坑

(1).给条件字段使用函数：比如select * from user where month(last_date) > 7这样的语句，InnoDB存储的是2017-05-23这样类似的数据，如果要找月份大于7的数据，就必须把索引last_date的所有数据都通过函数month进行一次计算，那么势必会导致全索引扫描，可以使用last_date>'2016-7-1' and last_date<'2017-1-1'等等类似的写法来代替。

(2).隐式类型转换：select * from user where str > 10333，str字段存储的都是字符串，如果遇到了这样的写法，MySQL默认是把字符串转数字进行比较，那么相当于给str字段使用了一个转换函数，就和(1)的情况相同了。

(3).隐式字符编码转换：连表查询的时候，如果两个字段使用的字符串都有索引，但字符集不相同，那么就有可能出现类似(1)的情况，通常把字符集变成相同的就可以了。

9.join排序

select * from t1 straight_join t2 on (t1.a = t2.a);

Index Nested-Loop Join：简称NLJ，执行过程是先从驱动表t1找出一行R，然后拿到R中的a去被驱动表b做索引扫描，符合条件的就放在结果集里面等待返回。整个过程对驱动表（记为N行）做全表扫描，对被驱动表（记为M行）做索引查询，整个执行复杂度是N + N*2*log2M。 所以应该总是使用小表做驱动表，而且使用该join，性能也比拆成多个单表查询更好。

Simple Nested-Loop Join：对驱动表做全表扫描，被驱动表没有索引也做全表扫描，扫描行数M*N。

Block Nested-Loop Join：将驱动表t1所有需要的数据放入join_buffer中，然后逐行从磁盘中获取被驱动表t2的行，在内存中进行N*M次判断。需要扫描行数N+M。 如果join_buffer的内存不够存放所有t1的数据，那么会分多次获取t1的数据。比较次数同样是N*M次，但扫描行数N+K*N*M。 通过设置join_buffer_size参数的大小可以调整join_buffer的大小，分段次数越少扫描行数越少。

总结：应该总是使用小表（指需要的数据量总和更少的表）驱动大表，并且总是使用到被驱动表的索引。

10.join排序的优化

(1).Multi-Range Read：简称MRR优化，比如select * from t1 where a>=1 and a<=100;正常情况下是去t1表的a索引查找数据，然后拿到主键ID回表去查主键索引，MRR优化的思路是针对这个回表过程，增加一个中间操作，将从a中获取到的数据存储在read_rnd_buffer当中，然后在read_rnd_buffer中将数据按照主键ID递增排序，然后再去回表。这样可以将之前的随机读变为顺序读，提高访存效率。read_rnd_buffer_size参数可以调整read_rnd_buffer的大小，如果read_rnd_buffer过小，则分多次回表。

set optimizer_switch="mrr_cost_based=off"可以让优化器稳定使用MRR优化，因为优化器的选择策略是倾向于不使用MRR的。 

(2).Batched Key Access：简称BKA算法，是对NLJ的优化，BKA在查询驱动表的时候，取出批量驱动表数据放入join_buffer中，按照主键排序之后去扫描被驱动表的相应索引列进行匹配，应用了MRR思想。使用BKA之前进行如下设置可以启用BKA，因为BKA要使用MRR优化，所以参数中需要把MRR也开启了。

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

(3).对于一些不适合创建索引的表，比如业务只涉及低频且数据量小的sql，我们可以创建临时表，把需要的数据（数据量小）存储到临时表当中，并且给相应字段添加索引，join查询就使用临时表来进行辅助，这样可以提升效率。比如

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

可以使用

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

另一种思路是将数据从大表中查出来，在java层做内存join，将被驱动表数据存到hashmap当中，提高查询效率。因为MySQL不支持hash join。

11.union执行流程

(select 1000 as f) union (select id from t1 order by id desc limit 2);使用内存临时表暂存数据，先执行第一个子查询得到1000放入临时表，再执行第二个子查询将数据放入临时表。因为union排重，所以遇到有相同数据的就不插入。插入所有结果后返回给server层。

12.group by执行流程

select id%10 as m, count(*) as c from t1 group by m;创建内存临时表，表里有两个字段m,c；从t1主键索引中取一行id计算id%10存入内存临时表，如果是有重复id的将c值+1，如果没有则插入到临时表当中；顺序遍历主键索引，重复上述过程。遍历完成之后，再根据m字段排序，内存临时表的排序过程借用了sort_buffer。排序结束之后返回结果给客户端。

注：如果不需要排序，可以在末尾增加order by null。

参数tmp_table_size可以控制内存临时表的大小，内存临时表不够的时候，会转成使用磁盘临时表。

13.group by优化

(1).建立有序索引：在上面的例子当中，因为主键索引id是有序的，而id%10的结果不是有序的，所以使用到了内存临时表来进行辅助，而如果我们新建一个结果为id%10的索引，在该索引当中id%10的结果也就是有序的了，MySQL只需要遍历这个索引，遇到相同值+1，就可以直接返回结果了，不需要使用临时表，也不需要排序。

(2).直接使用磁盘临时表：遇到有的业务场景不适合使用索引的，而且分组数据量又比较大，我们明明知道MySQL会创建磁盘临时表，但MySQL还是会先使用内存临时表，内存不够之后再使用磁盘临时表。这会浪费存数据到内存临时表的那个过程，这种情况下我们可以使用SQL_BIG_RESULT这个提示，让MySQL直接使用磁盘临时表来处理这条数据。

select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;