mysql中主键索引和联合索引的原理解析

mysql中主键索引和联合索引的原理解析

• 一、主键索引
• 二、什么是联合索引? 对应的B+树是如何生成的?
• • 1、建立索引方式
  • 2、什么是最左前缀原则?
  • 3、回表
  • 4、为什么要遵守最左前缀原则才能利用到索引?
  • 5、什么是覆盖索引?
  • 6、索引扫描底层原理
  • 7、order by为什么会导致索引消失？
  • 8、有哪些情况会导致索引失效?
  • 9、索引设计原则
  • • 1、选择合适的列作为索引
    • 2、避免使用过多的索引
    • 三、InnoDB 与MyISAM 的区别

      一、主键索引

      主键索引：按照主键数据从小到大按照从左到右进行排序，叶节点只存储数据区；

      接着将上面的页生成出来，页只存储索引和指针，指针指向数据域，当通过主键查找数据时，从B+树的头部开始寻址数据、读取数据。

      

      上面为索引页

      下面为数据页

      查询select * from table where a=6，会从上到下走法，找索引

      

      查询select * from table where a1:不走联合索引（因为需要回表很多次）

      

      

      拿到主键会到主键索引里面拿数据（回表7次）

      5、什么是覆盖索引?

      查询的字段正好在联合索引字段里面，不需要回表

      select b from t1 where b>1;(不需要回表了，直接从联合索引字段中将该字段取出来)

      

      select a,b,c,d from t1 where b>1;

      同样的也符合联合索引，也不需要回表，因为a字段（主键）在联合索引中。

      

      select a,b,c,d,e from t1 where b>1;

      不走联合索引，需要回表，因为e字段不在联合索引中，需要回表很多次

      

      6、索引扫描底层原理

      从bcd索引的叶子节点开始遍历

      主键索引存储的是8条完整的数据

      联合索引中存储的是8条不完整的数据，并且b字段在联合索引中，不会去回表到主键索引中。

      从叶子节点开始扫描不需要符合最左匹配原则。

      

      

      

      7、order by为什么会导致索引消失？

      因为走全表扫描效率更高，不需要回表

      走索引会回表多次，效率低

      

      

      8、有哪些情况会导致索引失效?

      1、索引列使用!=、not、is null、is not null查询的时候，由于索引数据的检索效率非常低，因此Mysql引擎会判断不走索引。

      2、使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。

      • 但是反过来，如果通配符匹配的是前缀xxx%，符合最左匹配，也会走索引。

        3、对索引列上进行函数运算、导致mysql无法识别索引列，就不会走索引了。

        

        4、使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是索引列的时候，才会生效。

        5、当索引列存在隐式转化的时候， 比如索引列是字符串类型，但是在sql查询中没有使用引号。

        9、索引设计原则

        查询更快、占用空间更小

        1、选择合适的列作为索引

        • 1.经常作为查询条件（where子句）、排序条件（order by子句） 分组条件（group by子句）的列建立索引。
        • 2、区分度低的字段，例如性别，不要建立索引
        • 3、更新频繁字段不适合创建索引
        • 4、对于定义为text、image和bit的数据类型的列不要建立索引
        • 5、定义有外键的数据列一定要建立索引。
        • 6、数据量较小的表，索引效果较差，没有必要在此列建立索引
        • 7、尽量使用扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
        • 8、使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词

          超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。

          2、避免使用过多的索引

          • 1、每个索引都需要占用额外的磁盘空间
          • 2、更新表（update、insert、delete操作时），所有的索引都需要被更新
          • 3、维护索引需要成本

            三、InnoDB 与MyISAM 的区别

            

            

            聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的，即:只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

            非聚簇索引:叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据，这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。

            优势:

            1、查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率要高

            2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的

            3、聚簇索引适合用在排序的场合，非聚簇索引不适合