一、InnoDB实现原理

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

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

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

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

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

二、优缺点

优点

  1. 可以把相关数据存储在一起，减少数据查询时的磁盘I/O
  2. 数据访问更快，因为聚簇索引就是表，索引和数据保存在一个B+Tree中
  3. 使用索引覆盖的查询时可以直接使用页节点中的主键值

缺点

  1. 插入速度严重依赖插入顺序
  2. 更新聚簇索引列的代价很高，因为会强制InnoDB把更新的列移动到新的位置
  3. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能会导致“页分裂”。当行的主键值要求必须将这一行插入到已满的页中时，存储引擎会将该页分裂为两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的存储空间。

三、 注意&建议

  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)，在此例子上来说明这个问题是从两个方面来说：

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

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

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

  • 询条件的第一个条件需要时有边界的，比如select * from xx where c1=x and c2>x and c3
  • 支持InnoDB和MyISAM存储引擎；
  • where条件的字段需要在索引中；
  • 分表ICP功能5.7开始支持；
  • 使用索引覆盖时，ICP不起作用。

5、分页offset值很大性能问题
在 MySQL中，分页当offset值很大的时候，性能会非常的差，比如limit 100000, 20，需要查询100020条数据，然后取20条，抛弃前100000条，在这个过程中产生了大量的随机I/O,这是性能很差的原因，为了解决这个问题，切入点便是减少无用数据的查询，减少随机I/O

利用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条得数据。
利用范围查询条件来限制取出的数据
select * from t1 where id > 1000000 order by id limit 0, 5;
--即利用条件id > 1000000在扫描索引是跳过1000000条记录，然后取5条即可,这种处理方式的offset值便成为0了，但此种方式通常分页不能用，但是可以用来分批取数据。

作者：稀饭里的米