mysql聚簇索引，辅助索引，覆盖索引

mysql有聚簇索引，辅助索引，覆盖索引。聚集索引的叶子节点称为数据页，每个数据页通过一个双向链表来进行链接，而且数据页按照主键的顺序进行排列。每个数据页上存放的是完整的行记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。如果定义了主键，InnoDB会自动使用主键来创建聚集索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。辅助索引它叶子节点中没有行记录的全部数据，叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签，该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。覆盖索引先遍历辅助索引，再遍历聚集索引，而如果要查询的字段值在辅助索引上就有，就不用再查聚集索引了，这显然会减少IO操作。除了这三种索引，还有一种联合索引，它是对表上的多个列进行索引，键值都是排序的，通过叶子节点可以顺序的读出所有数据，联合索引的好处在于能起到"一个顶三个"的作用。比如建了一个(a,b,c)的复合索引，那么实际等于建了(a),(a,b),(a,b,c)三个索引，每多一个索引，都会增加写操作的开销和磁盘空间的开销，对于大数据的表，这是不小的开销。另外它还可以避免filesort排序，因为filesort的过程，一行数据会被读两次，第一次是where条件过滤时，第二个是排完序后还得用行指针去读一次。filesort的过程是这样的：第一步先根据表的索引或者全表扫描，读取所有满足条件的记录。第二步，存储每一行排序列，就是order by用到的列值，还有行记录指针，就是指向该行数据的行指针，把这二个存储到缓冲区。第三步，当缓冲区满后，运行一个快速排序来将缓冲区中数据排序，将排序完的数据存储到一个临时文件，保存一个存储块的指针，当然如果缓冲区不满，则不会重建临时文件了。直到将所有行读完，建立相应有序的临时文件。第四步，对块级进行排序，这个类似归并排序算法，只通过两个临时文件的指针来不断交换数据，最终达到两个文件，都是有序的，直到所有的数据都排序完毕。第五步，采取顺序读的方式，将每行数据读入内存，取出数据传到客户端。为什么要说这个filesort呢？举二个场景，第一个，如果order by的条件不在索引列上会产生filesort，第二个，排序的字段不在where的条件中，没有办法走索引排序Index，而是走的文件排序filesort 。这种概率其实还是挺高的。这个时候就需要看文件排序用的是单路排序还是双路排序，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序。单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。另外sort_buffer_size参数修改为2M，减少在排序过程中对须要排序的数据进行分段，尽量不使用临时表来进行交换排序。这个参数如果超过 2M 的时候，就会使用 mmap()，而不是 malloc() 来进行内存分配，会导致效率降低。这个参数如果过小的话，再加上max_length_for_sort_data变大，一次返回的条数过多，那么很可能就会分很多次进行排序，然后最后将每次的排序结果再串联起来，这样就会更慢。最后read_buffer_size参数也可以根据实际情况调整，它是MySQL读入缓冲区大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小，默认是1M。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。