InnoDB 索引

1. 聚集索引

聚集索引是按照表的主键构造的一个 B+ 树

• 该树的叶子节点存放整张表的行记录，叶子节点也成为数据页，每个数据页都由一个双向链表进行连接
• 该树的非叶子节点由主键值构成，非叶子节点也叫索引页

聚集索引不仅对随机访问支持的好， 而且能够非常快的针对范围值进行查询，因为叶子节点被链表链接支持顺序访问，查询优化器在某一段的数据页进行扫描，且叶子节点的数据就是用户要查询的数据。

2. 辅助索引

辅助索引的叶子节点不包含整行数据，而是只包含辅助索引键值和对应的主键键值。辅助索引的存在不会影响数据在聚集索引中的组织，因此一个表可以有多个辅助索引。

• 聚簇索引：聚簇索引的 B+ 树， 非叶子节点是主键值， 叶子节点是整行数据。
• 非聚簇索引：将数据存储与索引分开结构，索引结构的叶子节点指向了数据的对应行(innodb中叶子节点给出聚簇索引的值)

3. 联合索引

联合索引指对表上的多个列进行索引。联合索引也是一颗 B+ 树，只是联合索引的 key 值个数大于等于2.

• 联合索引支持最左匹配原则。即比如联合索引为 (a,b,c) ， where 条件中的 a, (a,b) 条件查找都能使用联合索引， 但是 (a,c) 条件则不能。
• 联合索引的第二个好处是，对第二个键值进行了排序，因此可以支持 where 键值1 order by 键值2 的操作

  create table t (uid int,buy_date date);
  alter table t add key (uid);  -- 辅助索引
  alter table t add key (uid,buy_date); -- 联合索引
  
  -- 下面语句使用联合索引(uid,buy_date)，explain 的 extra 列显示 using index，而不是using file sort
  select * from t where uid=3 order by buy_date desc limit 3; 
  

• InnoDB 支持索引覆盖，即如果可以从辅助索引中查到要查找的数据，就不会从聚集索引中查找。因为辅助索引的叶子节点不包含整行数据，所以 IO 量可以大大减小

  -- 假设表 t 由主键 pk1, 和辅助索引 key1, 和联合索引 (key1,key2)
  -- 如下查询都可以仅适用一次辅助索引或联合索引完成 
  select key2 from t where key1 = ...;
  select pk1,key1,key2 from t where key1 = ...;
  select pk1,key1,key2 from t where key1 = ... and key2 = ...;
  
  -- 如下 count 操作，表面上看没有用到索引， 但查询优化器实际使用了辅助索引 key1. 因为辅助索引的叶子节点最小。如果没有key1， 则会选择联合索引  (key1,key2)
  select count(*) from t;
  

4. 查询优化器不适用索引的情况

有些情况下， 使用explain显示通过聚集索引扫描，即直接进行全表扫描查询数据。这种情况多发生在范围查找，join操作等情况。比如针对辅助索引或联合索引进行范围查找时，如果获取的数据量超过全表的20%，则优化器会选择扫描聚集索引（全表扫描）获取结果， 因为顺序度远远快于离散度。如果用户使用的是固态硬盘，随机读速度很快， 可以使用 force index 强制使用某个索引

select * from t force index (key1) where ...

其他和sql书写有关的不走索引:

• 索引列参与计算，不走索引

  SELECT `username` FROM `t_user` WHERE age=20;-- 会使用索引
  SELECT `username` FROM `t_user` WHERE age+10=30;-- 不会使用索引！！因为所有索引列参与了计算
  SELECT `username` FROM `t_user` WHERE age=30-10;-- 会使用索引
  

• 索引列使用了函数，不走索引

  -- 不会使用索引,因为使用了函数运算,原理与上面相同
  SELECT username FROM t_user WHERE concat(username,'1') = 'admin1'; 
  -- 会使用索引
  SELECT username FROM t_user WHERE username = concat('admin','1'); 
  

• 索引列使用了Like %XXX，不走索引
  like 模糊查询 前模糊或者全模糊不走索引

  select * from user where username like '%mysql测试' 
  

• 隐式转换——字符串列与数字直接比较，不走索引

  SELECT * FROM t_user WHERE `age`='23' -- 走索引
  

• 尽量避免 OR 操作，只要有一个字段没有索引，该语句就不走索引，不走索引！

  select * from t_user  where username  = 'mysql测试' or password ='123456'
  

• where id !=2 或者 where id <> 2，不走索引！

  select * from t_user where username <> 'mysql测试'
  

• is null，is not null也无法使用索引，不走索引！

  select * from t_user where username  is not null --is not null 不走索引
  

• 联合索引不满足最左匹配原则不走索引

5. Multi-Range Read优化

6. Index Condition Pushdown 优化

[附]: explain 常用的列

1. possible_keys: 潜在的索引
2. key : 本次查询使用到的索引
3. row : 本次查询结果的预估行数
4. extra ： 显示额外信息， 比如使用 oroder by 查询时， 会显示 file sort