02--MySQL的索引

01、什么是索引

数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。

索引类型

普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。

唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用primaykey创建。 

全文（Fulltext）：只有文本类型的字段才可以创建全文索引，比如char、varchar、text。

全文（Fulltext）使用？

SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')

建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。

02、InnoDB逻辑存储结构

 

03、索引的结构

多路平衡查找树（BTree）（分裂、合并）

B+树（加强版多路平衡查找树）

MySQL中的B+Tree有几个特点：

1、它的关键字的数量是跟路数相等的；

2、B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。

比如我们搜索id=28，虽然在第一层直接命中了，但是全部的数据在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。

3、B+Tree的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构。

4、它是根据左闭右开的区间[   )来检索数据

总结：InnoDB采用的是B+ Tree

04、InnoDB和MyISAM的索引结构

InnoDB的表有两个文件（.frm和.ibd）

MyISAM的表有三个文件（.frm、.MYD、.MYI）

【1】、聚集索引（聚簇索引）？

索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。比如：主键索引是聚集索引，非主键都是非聚集索引。

【2】、辅助索引

 主键之外的索引，比如我们在name字段上面建的普通索引。

【3】、如果一张表没有主键怎么办？

InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

04、索引的使用原则

【1】、列的离散度

count(distinct(column_name)):count(*)。分子越大，列的离散度就越高。

【2】、联合索引最左匹配

按照从左到右的顺序来建立搜索树的

比如：使用where name='xxx' and phone='136xx'去查询数据的时候，B+Tree会优先比较name来确定下一步应该搜索的方向，往左还是往右。如果name相同的时候再比较phone。但是如果查询条件没有name，就不知道第一步应该查哪个节点，因为建立搜索树的时候name是第一个比较因子，所以用不到索引。

【3】、创建联合索引

CREATE INDEX  idx_name_phone on user_innodb(name,phone);

我们创建三个字段的索引index(a,b,c)，相当于创建三个索引：

index(a)

index(a,b)

index(a,b,c)

【4】、覆盖索引

回表：

非主键索引，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据。比主键索引的查询多扫描了一棵索引树。这个过程就叫回表。

如何避免回表？

select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。

如：

SELECT  name,phone  FROM  user_innodb  WHERE  name='XXX'  AND  phone='13666666666';

【5】、索引条件下推

05、索引的总结

建立索引

1、用于where判断order排序和join的（on）字段上创建索引

2、索引的个数不要过多。

3、区分度低的字段，例如性别，不要建索引。

4、组合索引把散列性高（区分度高）的值放在前面。

5、不建议用无序的值（例如身份证、UUID）作为索引

执行过程中不使用索引

1、索引列上使用函数

如：where id+1=4;

2、字符串不加引号，出现隐式转换

where name = 136;

wherename = '136';

3、like条件中前面带%

4、负向查询

NOT LIKE

!=（<>）和NOT IN