亲宝软件园·资讯

展开

InnoDB索引实现原理以及注意点和建议

稀饭里的米 人气:0

一、InnoDB实现原理

虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。因为InnoDB支持聚簇索引(主键索引),聚簇索引就是表,所以InnoDB不用像MyISAM那样需要独立的行存储。也就是说,InnoDB的数据文件本身就是索引文件。

聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有的剩余列。假设我们以col1为主键,则下图是一个InnoDB表的聚簇索引(主键索引)(Primary key)示意。

与MyISAM不同的是,InnoDB的二级索引和聚簇索引很不相同。InnoDB的二级索引的叶子节点存储的不是行号(行指针),而是主键列。这种策略的缺点是二级索引需要两次索引查找,第一次在二级索引中查找主键,第二次在聚簇索引中通过主键查找需要的数据行。

画外音:可以通过我们前面提到过的索引覆盖来避免回表查询,这样就只需要一次回表查询,对于InnoDB而言,就是只需要一次索引查找就可以查询到需要的数据记录,因为需要的数据记录已经被索引到二级索引中,直接就可以找到。

因为InnoDB的索引的方式通过主键聚集数据,严重依赖主键。索引如果没有定义主键,那么InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。

二、优缺点

  1. 优点
    1. 可以把相关数据存储在一起,减少数据查询时的磁盘I/O
    2. 数据访问更快,因为聚簇索引就是表,索引和数据保存在一个B+Tree中
    3. 使用索引覆盖的查询时可以直接使用页节点中的主键值
  2. 缺点
    1. 插入速度严重依赖插入顺序
    2. 更新聚簇索引列的代价很高,因为会强制InnoDB把更新的列移动到新的位置
    3. 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能会导致“页分裂”。当行的主键值要求必须将这一行插入到已满的页中时,存储引擎会将该页分裂为两个页面来容纳该行,这就是一次页分裂操作,页分裂会导致表占用更多的存储空间。
    画外音:关于页,我们在上一篇文章中也提到过。页是计算机管理存储器的逻辑块,硬件及操作系统往往将主存和磁盘存储区分割为连续的
    大小相等的块,每个存储块称为一页。存和磁盘以页为单位交换数据。数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设
    为等于一个页,这样每个节点只需要一次磁盘I/O就可以完全载入
    
    基于聚簇索引以上的这些特点,在InnoDB中,我们应该尽量使用和应用无关的主键,例如自增主键,这样可以保证数据行是按照顺序写入的。而不是使用GUID、UUID生成随机的主键。

三、 注意&建议

  1. 主键推荐使用整型,避免索引分裂;
  2. 查询使用索引覆盖能够提升很大的性能,因为避免了回表查询
  3. 选择合适的顺序建立索引,有的场景并非区分度越高的字段放在前边越好,联合索引使用居多
  4. 合理使用in操作将范围查询转换成多个等值查询,但是如果有order by 不同的列来说是不会走索引的
  5. 大批量数据查询任务分解为分批查询
  6. 将复杂查询转换为简单查询
  7. 合理使用inner join,比如分页的时候

四、一些问题的分析

  1. 索引分裂个人理解:在 MySQL插入记录的同时会更新配置的相应索引文件,根据以上的了解,在插入索引时,可能会存在索引的页的分裂,因此会导致磁盘数据的移动。当插入的主键是随机字符串时,每次插入不会是在B+树的最后插入,每次插入位置都是随机的,每次都可能导致数据页的移动,而且字符串的存储空间占用也很大,这样重建索引不仅仅效率低而且 MySQL的负载也会很高,同时还会导致大量的磁盘碎片,磁盘碎片多了也会对查询造成一定的性能开销,因为存储位置不连续导致更多的磁盘I/O,这就是为什么推荐定义主键为递增整型的一个原因

  2. 自增主键的弊端 对于高并发的场景,在InnoDB中按照主键的顺序插入可能会造成明显的争用,主键的上界会成为“热点”,因为所有的插入都发生在此处,索引并发的插入可能会造成间隙锁竞争,何为间隙锁竞争,下个会详细介绍;另外一个原因可能是Auto_increment的锁机制,在 MySQL处理自增主键时,当innodb_autoinc_lock_mode为0或1时,在不知道插入有多少行时,比如insert t1 xx select xx from t2,对于这个statement的执行会进行锁表,只有这个statement执行完以后才会释放锁,然后别的插入才能够继续执行,但是在innodb_autoinc_lock_mode=2时,这种情况不会存在表锁,但是只能保证所有并发执行的statement插入的记录是唯一并且自增的,但是每个statement做的多行插入之间是不连接的

  3. 优化器不使用索引选择全表扫描 比如一张order表中有联合索引(order_id, goods_id),在此例子上来说明这个问题是从两个方面来说:

    1. 查询字段在索引中
    select order_id from order where order_id > 1000;
    --如果查看其执行计划的话,发现是用use index condition,走的是索引覆盖。
    
    1. 查询字段不在索引中
    select * from order where order_id > 1000;
    

    此条语句查询的是该表所有字段,有一部分字段并未在此联合索引中,因此走联合索引查询会走两步,首先通过联合索引确定符合条件的主键id,然后利用这些主键id再去聚簇索引中去查询,然后得到所有记录,利用主键id在聚簇索引中查询记录的过程是无序的,在磁盘上就变成了离散读取的操作,假如当读取的记录很多时(一般是整个表的20%左右),这个时候优化器会选择直接使用聚簇索引,也就是扫全表,因为顺序读取要快于离散读取,这也就是为何一般不用区分度不大的字段单独做索引,注意是单独因为利用此字段查出来的数据会很多,有很大概率走全表扫描。

  4. 范围查询之后的条件不走索引 根据 MySQL的查询原理的话,当处理到where的范围查询条件后,会将查询到的行全部返回到服务器端(查询执行引擎),接下来的条件操作在服务器端进行处理,这也就是为什么范围条件不走索引的原因了,因为之后的条件过滤已经不在存储引擎完成了。但是在 MySQL 5.6以后假如了一个新的功能index condition pushdown(ICP),这个功能允许范围查询条件之后的条件继续走索引,但是需要有几个前提条件:

    1. 查询条件的第一个条件需要时有边界的,比如select * from xx where c1=x and c2>x and c3<x,这样c3是可以走到索引的;
    2. 支持InnoDB和MyISAM存储引擎;
    3. where条件的字段需要在索引中;
    4. 分表ICP功能5.7开始支持;
    5. 使用索引覆盖时,ICP不起作用。
  5. 分页offset值很大性能问题
    在 MySQL中,分页当offset值很大的时候,性能会非常的差,比如limit 100000, 20,需要查询100020条数据,然后取20条,抛弃前100000条,在这个过程中产生了大量的随机I/O,这是性能很差的原因,为了解决这个问题,切入点便是减少无用数据的查询,减少随机I/O

    1. 利用inner join
    select * from t1 inner join (select id from t1 where xxx order by xx limit 1000000,5) as t2 using(id);
    --子查询先走索引覆盖查得id,然后根据得到的id直接取5条得数据。
    
    1. 利用范围查询条件来限制取出的数据
    select * from t1 where id > 1000000 order by id limit 0, 5;
    --即利用条件id > 1000000在扫描索引是跳过1000000条记录,然后取5条即可,这种处理方式的offset值便成为0了,但此种方式通常分页不能用,但是可以用来分批取数据。

加载全部内容

相关教程
猜你喜欢
用户评论