什么是索引？

索引

什么是索引？

索引就是一种数据结构，能够帮助我们快速的检索数据库中的数据。

---

索引的优势和劣势

优势：可以提高数据检索的效率，降低数据库的IO成本

劣势：索引会占据磁盘空间

---

索引都有哪些类型？

主键索引：由数据库自动创建，不允许有空值。

单例索引 普通索引：一个索引只包含一个列，一个表可以有多个索引

唯一索引：索引列中的值必须是唯一的，但是允许为空值。

复合索引：同一个索引包含多个列

全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引：字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引

---

索引的创建

1.主键索引 自动创建

create table t_user(id varchar(20) primary key,name varchar(20));

查看索引

show index from t_user;

2.单例索引

建表时创建：create table t_user(id varchar(20) primary key,name varchar(20),key(name));

建表后创建create index nameindex on t_user(name);

3.唯一索引

建表时创建：create table t_user(id varchar(20) primary key,name varchar(20),unique(name));

建表后创建create unique index nameindex on t_user(name);

4.复合索引

建表时创建：create table t_user(id varchar(20) primary key,name varchar(20),key(name，age));

建表后创建create index nameageindex on t_user(name,age);

---

索引的底层原理

索引的数据结构主要有Hash索引和B+ Tree索引，我们使用的是InnoDB引擎，默认的是B+树。

Hash索引

• 哈希索引不支持排序，因为哈希表是无序的。

• 哈希索引不支持范围查找。

• 哈希索引不支持模糊查询及多列索引的最左前缀匹配。

• 因为哈希表中会存在哈希冲突，所以哈希索引的性能是不稳定的，而B+树索引的性能是相对稳定 的，每次查询都是从根节点到叶子节点。

B-Tree索引

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

B+Tree索引

• 是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

• 数据页之间是通过双向链表进行连接的

---

 

聚簇索引和非聚簇索引

聚簇索引：数据和索引存储在一起。聚簇索引不一定是主键索引，主键索引一定是聚簇索引

非聚簇所引：数据和索引没有存储在一起。非聚簇索引叶子节点存储的是主键值，不是物理地址，所以非聚集索引访问数据总是需要二次查找。 如果存储的是物理地址，进行增删改 物理地址会发生该表

聚簇索引（主键索引）的B+树中保存的是一行记录的所有信息，非聚簇索引（非主键索引）仅仅保存索引字段值和主键字段值。

InnoDB 既有局促索引又有非聚簇索引

MyISAM:非聚簇索引

聚簇索引比非聚簇索引的查询速度快，聚簇索引的叶子节点直接就是我们要查询的行数据。非聚簇索引查到的叶子节点是主键的值，再通过主键进行回表查询

主键索引查询只会查一次，而非主键索引需要回表查询多次非主键索引一定会查询多次吗？

通过覆盖索引也可以只查询一次

---

覆盖索引

查询的列包含在索引列中

---

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

select * from table where name='zhangsan'and age='20';

在5.6之前，只能通过最左前缀索引规则对索引中包含的字段先做出判断，找到name，再通过回表找到下一个过滤条件。

---

最左前缀

根据索引创建的顺序从左向右进行匹配

复合索引：遵循最左前缀规则，但引擎在查询过程中会动态调整查询字段的顺序

问：为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？ hash：虽然可以快速定位，但是没有顺序，IO复杂度高。 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。 红黑树：树的高度随着数据量增加而增加，IO代价高。 问：为什么官方建议使用自增长主键作为索引。 结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

---

索引维护

B+ 树为了维护索引有序性，自增主键是追加操作都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

假设插入ID为400的就会出现 要挪动后面的数据，并且出现页分裂。

 

 

建议建表时使用主键索引，但也有不使用主键索引的情况

1. 只有一个索引；

2. 该索引必须是唯一索引。你一定看出来了，这就是典型的 KV 场景。

3. 由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

---

什么是Explain？

Explain可以用来分析SQL语句和表结构的性能。通过explain的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息。

explain 执行计划包含字段信息如下：

type：表示MySQL在表中找到所需行的方式，或者叫访问类型
type=ALL，全表扫描，MySQL遍历全表来找到匹配行
type=index，索引全扫描
type=range，索引范围扫描
type=eq_ref，唯一索引
type=NULL，MySQL不用访问表或者索引，直接就能够得到结果（性能最好）
possible_keys: 表示查询可能使用的索引
key: 实际使用的索引
key_len: 使用索引字段的长度
rows: 扫描行的数量
Extra：
using index：覆盖索引，不回表
using where：回表查询
using filesort：需要额外的排序，不能通过索引得到排序结果

---

索引什么时候会失效？

导致索引失效的情况：

• 对于组合索引，不是使用组合索引最左边的字段，则不会使用索引

• 以%开头的like查询如 %abc ，无法使用索引；非%开头的like查询如 abc% ，相当于范围查询，会使 用索引

• 查询条件中列类型是字符串，没有使用引号，可能会因为类型不同发生隐式转换，使索引失效

• 判断索引列是否不等于某个值时

• 对索引列进行运算

• 查询条件使用or连接，也会导致索引失效