Mysql索引底层原理以及实战

一 InnoDb引擎局部性原理

      在InnoDb中  数据会存储在磁盘中 在真正处理数据的时候先将数据加载到内存  表中读取某些纪录时 innodb引擎不需要把一条一条记录读取出来  innodb采用的方式  将数据划分为若干个也  以页作为磁盘和内存之间交互的基本单位  innodb中页大小一般为16kb大小  也就是说  当需要从磁盘中读取数据时每一次最少读取16kb的内容到内存中 每一次最少也会把内存中16kb数据写到磁盘中 （如果写记录数据过多的话会产生行溢出）

二 索引

     聚簇索引

     在Innodb中会为主键自动生成聚簇索引(如果一个表没有手动定义主键 则会选取一个Unique键作为主键  如果连Unique键都没有的话 则会为表默认添加一个名为row_id的隐藏列作为主键 ) 是一颗B+树  而主键是B+树中的非叶子节点  相当于新华字典前几页目录

   按主键值的大小进行记录和页的排序

        B+树的叶子节点里的记录是按照主键从小到大的排序的一个单向链表

        B+树中同一个的页目录也是按照主键从小到大排序的一个双向链表

 B+树的叶子节点存储的是这个主键下一条完整的记录  就是指存储了所有的列的值  具有这两种特性的B+树称之为聚簇索引  所有的完整的一条数据都放在这颗B+树叶子节点处  再innodb引擎中   聚簇索引就是数据·存储的方式 也就是所谓的索引即数据，数据即索引

   二级索引

      聚簇索引只能在搜索条件是主键时才能够发挥作用   当我们想以别的列作为搜索条件时我们可以多键几颗B+树 不同的B+树采用不同的排序规则

          按照指定的索引列的值来进行排序

          叶子节点存储的不是完整的数据 而是索引列加主键

          非叶子节点目录项中不是主键  而是索引列

          在二级索引进行查询数据  需要根据主键值去聚簇索引中查找一遍完整的数据记录  这个过程叫 回表

   联合索引

        以多个列大小排序规则建立的B+树称之为联合索引   其实也是给二级索引

  索引代价  

    空间上的代价

         一颗索引都对应一颗B+树  树中每一个节点都是一个数据页  一个也默认占用16kb的存储空间 所以一颗B+树也会占磁盘空间

    时间上的代价

          索引是对数据的排序  那么对数据进行 增 删  改 操作 都要去维护去修改内容的涉及到B+树索引  在进行 增 删  改 操作可能会需要额外的时间进行一些记录的移动

 B+树索引实战

      全值匹配
    

       b  c d已经建立联和索引  查询优化器会去分析这些查询条件并按照索引中的列顺序决定先使用那个查询条件

    匹配左边的列

           

           下面这一条sql用不到索引

          

           因为B+树是先按照b列的值排序的  在b列相同情况下才使用c列进行排序  也就是说b列的值不同记录值中的c列可能是无序的 而现在条件跳过b列直接根据c值去查找  这是做不到的

    匹配列前缀

         

        这种情况是用不到索引的  因为字符串中有101字符串并没有排好序 所以只能全表扫描了  有时候匹配字符串后缀的需求  比如说url列 值 www.baidu.com  假设url建立了索引 如果我们以com为后缀查询条件 url LIKE '%com'   这样无法使用到索引  我们可以把后缀查询改成前缀查询  不过就得把表中数据全部逆序存储一下 WHERE url LIKE 'moc%'，这样就可以用到索引了

   匹配范围值

       

       由于B+树中数据也和记录先按b列排序的   找到b值为1的记录  再找到b值为20000的记录  所有记录都是由链表连起来的  所有他们之间记录很容易找出来  找到这些记录的主键值，再到聚簇索引中回表查找完整的记录

      不过在联合查询进行范围查找时候需要注意  如果对多个列进行范围查找的话 只要对索引最左边的那个列范围查询时才能够用到索引

      

      这样子对于联合索引来说  只能用到b列部分 而用不到c列部分  因为b值相同的情况下才能用到c列的值进行排序  而这个查询中通过b范围查找记录可能并不是按照c列进行排序的  所以在搜索条件中继续以c列进行查找时是用不到这个B+树索引的

  精确匹配某一列并范围匹配另外一列

      

      对于同一个联合索引来说   多给列进行范围查找时只能够用到最左边的那个索引列   如果左边的列是精确查找 则右边的列可以进行范围查找

  排序

       

        这个查询结果集先b列值进行排序  如果记录b值相同  则需要按照c值来排序  如果c的值相同 则需要按照d值来排序  因为这个B+树本身就是按照上述规则排好序的  直接从索引值中提取数据  然后进行回表操作

  分组

        

 

      这个查询语句相当于做了3次分组操作

       先把记录按照b值进行分组，所有b值相同的记录划分为一组。

       将每个b值相同的分组里的记录再按照c的值进行分组，将title值相同的记录放到一个分组里。

       再将上一步中产生的分组按照d的值分成更小的分组

       如果没有索引的话  这个分组过程全部需要在内存里实现  而如果有索引的话  正好这个分组顺序又和B+树中的索引列的顺序是一致的   所以可以直接使用B+树索引进行分组

 使用联合索引进行排序或分组的注意事项

       对于联合索引有几个问题需要注意  ORDER BY的子句后边的列顺序也必须按照索引的列给出   如果给出order by c, b, d的顺序，那也是用不了B+树索引

  建立索引几大原则

        索引的列类型尽量小

        利用索引字符串值得前缀

        主键自增

        定位删除表中得重复和冗余索引
        尽量使用覆盖索引进行重新  避免回表带来的性能损耗