【MySQL实战】索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

主键索引与普通索引的区别在于，主键索引不需要回表遍历，普通索引在普通索引拿到索引值后，需要根据主键值再去主键索引遍历一次，称为回表。

    InnoDB所有的数据文件idb文件每页的大小是16K，操作系统文件系统块是4K，磁盘扇区是512字节，页可以存放数据或者指针，每个表的索引页所在页存储在表空间信息中，通常索引节点由键值+指针地址组成，默认指针大小是6字节（2^48bit），自增主键bigint大小是8字节（2^64bit）,所以bigint的索引节点（非叶子节点）大小为14字节，16K的页可以存储16384/14=1170条数据的索引，由于是主键索引，叶子节点存放着数据记录，假设每条数据记录大小为1k，每页叶子节点可存放16条记录，索引树B+树会分为两层，树高为2时，该课树可最多存放记录数1170*16=18720条数据；树高为3时，可存放最多记录数为1170*1170*16=21902400条记录，上千万级别了。结合通常说的单表数据超过千万时（单表1G），索引页数、数据页数会很大，需要考虑拆表、拆库，甚至改用其他数据库，如HBase、MyCat、Greenplum、Redis，应该也是磁盘io瓶颈所致，所以Mysql的在B+树索引的索引方式下还是无法绕过磁盘IO的问题。

    参考《mysql b+树能存多少条数据？b+树每层有多少分支》https://blog.csdn.net/csdnlijingran/article/details/102309593

索引维护

    当索引页数据部署顺序时，需要从中间插入、删除索引节点时，需要移动索引数据，特别是数据处于索引页临界点时，会造成索引页分裂成N+1张，或者合并成N-1张。

    所以主键ID建议使用自增长，可以避免一部分这样的问题，而且可以带来一部分其他的收益，主键为自增长的bigint，占据的空间为8字节，比自定义的UUID32、64位都要小，这样普通索引的叶子节点占据的空间就会更小，这样就可以节省普通索引的空间。

    联合索引

        根据索引顺序，逐个索引过滤满足条件的数据，直到所有索引筛选完成，如有index(A,B,C)，假设命中了联合索引（A,B,C），先用A索引的全部记录与条件组筛选得出满足条件A的索引树（A，B，C）的子集合A，逐行回表到主键索引获取数据，得到数据集合A；再拿B的条件与索引树（A，B，C）的子集合A中B字段索引做运算，得到满足A+B条件的索引树（A，B，C）的子集合AB，逐行回表到主键索引树上拿到数据集合A+B；再拿C的条件与索引树（A，B，C）的自己和AB中的C字段索引做运算，得到满足A+B+C条件的索引树（A，B，C）的索引子集ABC，再逐行回表到主键索引中获取数据，得到数据集合ABC，返回查询结果ABC。

    从上述过程可以看出需要多次回表，且存在重复回表的记录，与其这样，不如在可以通过索引包含的字段先做判断筛选，减少回表的次数，在Mysql5.6及其以后的版本有这样的能力，中称作索引下沉/索引下推（index condition pushdown)。

    存在like > < 的判断时根据索引最左前缀匹配原则，最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

    覆盖索引是指需要查询的数据字段包含在索引的叶子节点上，不需要再到数据页上获取数据，减少树的搜索和IO。

普通索引与唯一索引的区别

    在查询上对于命中索引的查询来说，唯一索引在取得满足条件的时候会结束搜索，普通索引还需要继续往下搜索查找和判断下一条记录，但是由于InnoDB的数据是按页为单位存储的，当索引指向的数据需要被读到时，会整页读入内存，对内存的查找和判断还是比较快的，特别是一页只有16K大小的情况；当然如果存在跨页的情况就会复杂一些，主键索引的优势会明显多一些。

    在更新时主键索引需要做唯一性检查，change buffer的情况相对较好，但是如果数据不在内存中，可能导致磁盘IO严重，普通索引则不存在这种情况。