MySQL 索引

---

为什么要有索引

索引是帮助MySQL高效获取数据的数据结构

---

索引数据结构

B+树，哈希
不能脱离存储引擎来聊索引
InnoDB B+树，自适应哈希
MEMORY 哈希

建表的时候可以选择存储引擎，因此存储引擎是形容数据库表的

frm文件为InnoDB下的表结构
ibd文件位实际数据和索引
聚集索引：叶子结点包含了完整记录（InnoDB）
非聚集索引：索引和数据分离不在一起（MYISAM）

InnoDB也有非聚集索引（二级索引），不过叶子结点里面存的是那行的主键（回表）

frm为MYISAM的表结构
MYD为数据，MYI是索引
先在MYI中找，找到对应在MYD里面数据存放的地址，然后在MYD文件里面取数据

---

二叉树/红黑树索引

都会因为树的深度过深导致IO次数变多，影响读取效率

---

哈希索引

里面存着索引和数据地址，找到后直接去找那个地址里面的地址

缺点：

1. 利用hash值存储需要将所有数据文件添加到内存，耗费内存空间
2. 查询操作都是等值查询，hash比B+树更快，但是实际应用中查找的数据很多（范围查询），不是等值查询，hash并不适合，哈希不支持范围查询（不用哈希做索引的关键）
3. 哈希碰撞

---

B树

磁盘预读，16k，磁盘块就是预读一次的大小
假设数据为1k，一个磁盘块就可以存16个，可以发现，大量内存浪费在数据上
B树没有双向指针，所以范围查询性能不好

---

B+树

磁盘块依然是16kB
把数据放到叶子结点，叶子结点同时是一个双向链表（B+树支持范围查询的原因），非叶节点只放key，一个磁盘块所承载的key个数大大增加，三层就能实现超大数据量的承载。
B+树的层数3或4不固定，取决于数据量
因此索引的类型越小越好，varchar就因为大不合适

存储引擎InnoDB

为什么建议InnoDB表必须为主键？

• 不建主键，底层依然会维护一个索引来建立B+树（找一个唯一约束的列来做索引，找不到引擎就自己建一个隐藏列）
  一张表只有一个聚集索引。

为何推荐使用整型的自增主键？

• 整型主键占得内存小，一个非叶节点可以存放更多的索引，并且整型比较大小操作更快。
• 自增是因为不自增插入B+树可能会导致叶结点分裂，树平衡结构调整等，效率不高

一、什么是回表查询？

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

聚集索引(clustered index)

普通索引(secondary index)

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

（1）如果表定义了PK，则PK就是聚集索引；

（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；

（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB普通索引的叶子节点存储主键值（涉及回表操作）

三、如何实现索引覆盖？

常见的方法是：将被查询的字段，建立到联合索引里去。就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。 只对InnoDB有用

---

联合索引

多用一点联合索引，单一索引有几个就行了（实际）

按照建联合索引字段的顺序比大小，若第一列相等就比第二列大小的方式建立B+树

---

最左前缀原则

最左前缀原则应用在联合索引中

由于最左前缀原则，只有第4行那条语句会走索引
原则要求：联合索引的使用顺序必须按照建立时的顺序来，不能跳过前面的字段直接使用后面的字段
单纯找联合索引后面的字段，并不是一个排好序的，这相当于要全表扫描

不过现在MySQL有查询优化器可以优先选择按照索引的方式来查表（在联合索引都使用且使用顺序不同于索引顺序时），若只用联合索引的一部分，就不会按照索引来查

注意事项：

1. 范围查询
   mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引

2. like 语句的索引问题
   如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀
   在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描

3. 不要在列上进行运算
   如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描
   例如 select * from user where YEAR(birthday) < 1990
   可以改造成 select * from users where birthday <’1990-01-01′

4. 索引不会包含有 NULL 值的列
   只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL

5. 尽量选择区分度高的列作为索引，区分度的公式是 count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录

6. 覆盖索引的好处
   如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需读表，极大减少数据访问量

---

索引下推

优点：减少IO传输数据的量，数据筛选更多在磁盘上，以前都是在内存中筛选