Mysql索引原理及索引优化

sql语句的实质行为就是从磁盘上的文件中读取数据，数据在磁盘上存储是随机IO，效率较低，所以我们需要“索引”B+tree

数据存储时是以页为单位存储的

为什么不用HashTable、二叉树？

HashTable的存储原理是：HashTable的底层也是数组，根据一定算法将key转化成下标，存储至数组中，查询是直接根据key算出下标从数据中拿到数据，K-V的结果不适合存储复杂数据结构的数据，比如一条数据中有多个字段需要存储，另外HashTable的存储顺序是随机的，不能查询大于、小于，只适合精确查找。

完全对称二叉树为什么不合适？

因为二叉树存储是有序的，完全对称二叉树可以做大于、小于的运算，但是在查找过程中要回去查找父节点中储存的元素，不利于检索

B+tree

B+tree也是有序的，在存储过程中是将所有非叶子节点的数据冗余一份到叶子节点中，在查找过程中只关注同级节点就可以了，所以B+tree的有点就在于区间查询，另外B+tree中一个节点存储了多个元素，有助于节省磁盘IO次数。

聚合索引：新建表是默认分配1页资源，索引与数据存在一起，插入数据达到本页最大限制时（默认16k），将第一页复制一份，并开辟一页新的资源，原第一页修改为目录页，以此类推，B+tree形成 ，建表时的第一页目录通常会被缓存，数据量越大，缓存越多。所以建表时务必指定主键索引，如果没有指定，也没有建其他唯一索引，mysql会自动补全自增隐藏列row_id为主键索引，但此列不可见，不支持查询，作用很小。

那可能有人有这样的疑问，如果我的数据量很大，是不是树就会层级很高，检索起来会不会很慢，那我们简单算一下：

叶子节点：数据 16k/1k（单条数据1k举例）

非叶子节点：主键id+指针

一个主键id我们认为它占用8b空间，指针源码上写的占6b空间，根节点缓存到内存里，我们可以理解为树高为2级可以存储的索引数据量为：

16k/14b*16=18724左右

树高为3级时

1170*1170*16=21907740左右

所以建议mysql分表存储的参考数据就是2000w左右，因为树高在临近这个临界点会增加，检索效率会直线下降，单条数据容量可能有区别，数值为近似量。

主键索引建议采用小内存、自增形式：

数据是累积到一定数值后分页的，但是如果非自增，随着数据增长，各数据页、目录页中数据会有补全、变动，那么会增加IO、降低效率

辅助索引：

辅助索引（非聚合索引）存储中存储了索引列和主键的对应关系，根据索引列查到主键后再去根据主键查询数据，由此可见主键索引的重要性，辅助索引其实就是针对该列单独进行排序，便于查找。

那么联合索引是怎么工作的呢？其实同理，就是两列内容进行一个排序，便于查询，最左后缀原则，如果单独查询组合索引的第二、三列索引效果近乎没有，因为没有优先按照右侧列排序，同理  like XX%是可以走索引的，%XX则不行。

InnoDB支持事务