MySQL索引要点

索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看作是一种排序好的数据结构，包含了对数据表里所有数据的引用指针。

优点：减少数据查询时间
缺点：创建索引和维护索引需要耗费时间

在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。（Hash表不支持顺序和范围查询，二叉查找树在有序情况会退化为线性链表，平衡二叉树需要频繁进行旋转操作保持平衡，会有较大的计算开销从而降低数据库写操作的性能，红黑树平衡性较弱，可能导致树的高度较高，可能导致一些数据需要进行多次磁盘IO操作才能查询到）

B 树& B+树两者有何异同呢？

• B树的所有节点既存放键也存放数据，B+树只有叶子节点存放键和数据，其它节点只存放键
• B+树的叶子节点中有一条指向相邻叶子节点的链接
• B树的检索过程相当于对范围内每个节点的关键字做二分查找，可能没到叶子节点就查完了。而B+树任何查找都是从根节点到叶子节点的过程。
• B树上进行范围查询，需要先找到要查找的下限，然后中序遍历，直到找到要查找的上限。而B+树的范围查询，只需要通过叶子节点间的链接，对链表进行遍历即可。

综上，B+树具备更少的IO次数，更稳定的查询效率，更适合范围查询。

主键索引

一张数据表只能有一个主键，并且不能为null，不能重复。
在MySQL的InnoDB的表中，如果没有显式地指定表的主键，InnoDB会自动检查表中是否有唯一索引且不允许存在null值的字段，如果有，则选择该字段为默认的主键，否则InnoDB自动创建一个6Byte的自增主键。

二级索引

二级索引的叶子节点存储的数据是主键的值，通过二级索引可以定位主键的位置。二级索引又称为辅助索引或非主键索引。

聚簇索引和非聚簇索引

• 聚簇索引即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB中的主键索引就属于聚簇索引。对于InnoDB引擎表来说，该表的索引（B+树）的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
• 非聚簇索引即索引结构和数据分开存放的索引。二级索引就属于非聚簇索引。非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。InnoDB的非聚簇索引的叶子结构存储的是主键（聚簇索引）的值。MySQL的MyISAM引擎，不管主键还是非主键，使用的都是非聚簇索引。

聚簇索引的叶子节点包含了实际的数据行。这意味着索引结构本身就是数据的物理存储结构。
非聚簇索引的叶子节点不包含完整的数据行，而是包含指向数据行的指针或主键值。数据行本身存储在聚簇索引中。

聚簇索引优缺点

优点

• 查询速度快，定位到索引的节点，就相当于定位到了数据，比非聚簇索引少了读取数据的IO操作。
• 对主键的排序查找和范围查找速度非常快

缺点

• 依赖于有序的数据。如果数据不是有序的，就需要在插入的时候排序，影响效率。
• 更新代价大。如果对应索引列的数据被修改，对应的索引也要被修改。

非聚簇索引优缺点

优点

• 更新代价小

缺点

• 依赖有序数据。
• 可能会有二次查询(回表)。使用非聚簇索引查到索引对应的指针或主键值后，要根据指针或者主键回到聚簇索引里去查询数据，这就叫回表查询。扫描了两次索引树，所以效率相对较低。

非聚簇索引一定会回表查询吗？

不一定。
例如：

 SELECT name FROM table WHERE name='guang19';

查询用户名，而用户名字段正好建立了索引，索引的key本身就是name，因为只查询name，所以直接返回即可，无需回表查询。这种情况称为覆盖索引。

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称之为覆盖索引。
如主键索引，如果一条 SQL 需要查询主键，那么正好根据主键索引就可以查到主键。再如普通索引，如果一条 SQL 需要查询 name，name 字段正好有索引， 那么直接根据这个索引就可以查到数据，也无需回表。

联合索引

使用表中的多个字段创建索引，就是联合索引，也叫组合索引或复合索引。
以 score 和 name 两个字段建立联合索引：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

• 最左匹配原则会一直向右匹配，直到遇到范围查询（>、<、between、like）为止。
• 尽量将区分度高的字段放在最左边。
• 如果不遵循最左匹配原则，联合索引就会失效。例如：联合索引为(a, b, c)，a必须要出现在查询条件中，出现顺序不重要，否则就会失效。

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

是有效的。

where b=2；
where c=3；
where b=2 and c=3；

这种情况联合索引会失效。

联合索引范围查询

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询，也就是可能存在部分字段用到联合索引的 B+Tree，部分字段没有用到联合索引的 B+Tree 的情况。这种特殊情况就发生在范围查询。

联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是范围查询字段的后面的字段无法用到联合索引。

范围查询有很多种，那到底是哪些范围查询会导致联合索引的最左匹配原则会停止匹配呢？
例子

• [待考究] 结论：联合索引的最左匹配原则，在遇到范围查询（如>、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

索引下推

索引下推是MySQL5.6版本提供的一项索引优化功能，它允许存储引擎在索引遍历过程中，执行部分where字句的判断条件，直接过滤掉不满足条件的记录，从而减少回表次数，提高效率。

假设我们有一个名为 user 的表，其中包含 id, username, zipcode和 birthdate 4 个字段，创建了联合索引(zipcode, birthdate)。

SELECT * FROM user WHERE zipcode = '431200' AND MONTH(birthdate) = 3;

• 没有索引下推之前，即使 zipcode 字段利用索引可以帮助我们快速定位到 zipcode = '431200' 的用户，但我们仍然需要对每一个找到的用户进行回表操作，获取完整的用户数据，再去判断 MONTH(birthdate) = 3
• 有了索引下推之后，存储引擎会在使用zipcode 字段索引查找zipcode = '431200' 的用户时，同时判断MONTH(birthdate) = 3。这样，只有同时满足条件的记录才会被返回，减少了回表次数。

MySQL 可以简单分为 Server 层和存储引擎层这两层。Server 层处理查询解析、分析、优化、缓存以及与客户端的交互等操作，而存储引擎层负责数据的存储和读取，MySQL 支持 InnoDB、MyISAM、Memory 等多种存储引擎。索引下推的下推其实就是指将部分上层（Server 层）负责的事情，交给了下层（存储引擎层）去处理。

索引失效

1. 使用!=或者<>
2. 类型不一致
3. 对索引列使用了函数，运算符
4. 使用OR，连接的是不同的字段
5. 模糊搜索，当%放在匹配字段前面不会走索引，只放在后面才会走索引
6. 使用了NOT IN或者NOT EXISTS
7. 使用顺序不遵循最左匹配原则
   具体可以看索引失效场景