Mysql索引

为什么要有索引？

假设有一张表，表中有 100 万条数据，这 100 万条数据在硬盘上是存储在数据页上的，一页数据大小为 16K，存储 100 万条数据需要很多数据页，假设其中有一条数据是 id='7900'，如果要查询这条数据，其 SQL 是 SELECT * FROM 表名称 WHERE id = 7900。mysql 需要扫描全表来查找 id=7900 的记录。全表扫描就是从“数据页 1”开始，向后逐页查询。对于少量的数据，查询 的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100 万条数据逐页查询的时间是无法被用户接受的。

总之就是在数据量很大的情况下，按部就班的去查找想要的数据就太慢了，所以我们可以在存储数据这方面下功夫，不一条一条挨个存数据，而是通过某种数据结构(索引)存储数据，提高我们查找的效率。

索引是什么？

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

索引类似于书的目录，通过目录（索引）快速的定位到数据真实的位置。是排好序的，快速查找的数据结构（B+树）

索引思想：在数据库中单独维护一个树，树中的每一个节点存储主键和数据的物理地址，这样我们可以通过树形结构快速锁定到数据的位置。

注：只有为表中的列添加了索引后，数据存储才会按B+树的方式存储，当然我们一般都会有主键索引，设置主键后会自动建立；

索引的优缺点？

索引优势：快速的定位数据位置，提高了数据检索的效率，同时降低了IO成本；通过索引对数据进行排序，降低了数据排序成本，降低了cpu的消耗；

索引劣势：索引需要占用存储空间。增删改操作时还需要额外的维护索引树，需要消耗时间。

因为索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列是要占用磁盘资源的;虽然索引大大提高了查询速度,同时却会降低更新表的速度,例如对表进行 INSERT,UPDATE 和 DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

索引的分类

注：只有添加了索引的数据列，才会按照B+树的数据结构存储数据

主键索引：

设定为主键后数据库会自动建立索引

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

删除主键索引：

ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引：

为单个的某列添加索引，使用这列作为查询条件时，效率会更高；

创建单值索引：create index 索引名 on 表名(列名)

删除索引：drop index 索引名

唯一索引：

保证列数据不重复，可以为null

create UNIQE INDEX 索引名 on 表名(列名)；

删除索引

DROP INDEX 索引名 ON 表名；

组合索引：

一个索引对应多个列，复合索引比单值索引所需要的 开销更小(对于相同的多个列建索引),当表的行数远大于索引列的数目时可以使用复合索引。

但是在查询这个表时要满足最左前缀原则才能使用索引，我们为a，b，c创建索引，那么查询时必须要有a作为查询条件之一才能够自动使用索引查询，如下：

select * from table where a=’’and b=’’索引生效
select * from table where b=’’and a=’’索引生效
select * from table where a=’’and c=’’索引生效
select * from table where b=’’and c=’’索引不生效
explain关键字可以显示查询sql语句的相关信息

创建复合索引

CREATE INDEX 索引名 ON 表名(列 1,列 2...);

删除索引：

DROP INDEX 索引名 ON 表名;

全文索引

由于模糊匹配时索引会失效，所以我们可以使用全文索引解决。

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')

查看表中索引有哪些：show index from 表名

创建索引的原则

重要的内容

哪些需要创建索引？

• 主键自动建立唯一索引

• 频繁作为查询条件的列建议添加索引（where后的语句）

• 查询中需要排序的列，若通过索引去访问将大大提高排序速度

• 查询中与其他表关联的列，外键关系建立索引

• 分组中的字段(列)

哪些不需要创建索引？

• 表中数据较少

• 经常增删改的表不建议加索引；每次改变表以后，还需要维护索引

• where后不经常使用的列

• 数据重复且分布平均的表字段：例如性别，只有男和女

索引的数据结构

索引是一个树形结构：B+树；

首先排除二叉树、红黑树，AVL树，它们的树的高度较高，会进行多次IO操作，影响查询效率。（为什么不使用红黑树和AVL树）

B -树(自平衡多路搜索树)：可以在一个节点中放多个数据，横向扩展，降低了树的高度。

B+树(自平衡的多路搜索树)：非叶子节点只存储索引，所有数据都存储在叶子节点，所以可能会存在一些数据冗余，叶子结点之间会有一个相互指向的指针（对于自增主键，范围查找非常合适）。

为什么使用B+树？

B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来。使其更适合实现外存储索引结构

InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

Mysql 索引使用的是 B+树，因为索引是用来加快查询的，而 B+树通过对数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元素，从而可以使得 B+树的高度不会太高.并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等 SQL 语句。

聚簇索引和非聚簇索引

Mysql的InnoDB索引数据结构是B+树，主键索引叶子节点的值存储的就是MySQL的数据行，普通索引的叶子节点的值存储的是主键值，这是了解聚簇索引和非聚簇索引的前提。

什么是聚簇索引？

很简单记住一句话：找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引，修改聚簇索引其实就是修改主键。

什么是非聚簇索引？

索引的存储和数据的存储是分离的，也就是说找到了索引但没找到数据，需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

举例：

1.直接通过主键查询所有列数据，此时主键是聚簇索引，因为主键对应的叶子节点存储了这个主键的所有列的值。

2.通过学号（不是主键）查找学号和姓名，先通过学号找到主键，再通过主键查找数据，这种场景是非聚簇的。

3.我们根据编号查询编号（有人会问知道编号了还要查询？要，你可能需要验证该编号在数据库中是否存在），这种查询命中编号索引时，直接返回编号，因为所需要的数据就是该索引，不需要回表查询，这种场景下是聚簇索引

判断是聚簇索引还是非聚簇索引：一次查询能否直接命中数据；

总结：主键一定是聚簇索引，MySQL的InnoDB中一定有主键；当SQL查询的列就是索引本身时，我们称这种场景下该普通索引也可以叫做聚簇索引。

在Innodb引擎中，数据都在B+树的叶子节点存储，是聚簇式的，找到了主键，也就找到了数据。

使用主键作为查询条件，就是聚簇索引，使用非主键的列添加索引查询，就是非聚簇的，因为通过索引找到主键，然后通过主键二次回表查询，再通过主键找到数据。

在MyISAM引擎中，除了只查询主键列以外，查询其他列都是非聚簇索引；因为索引文件与数据文件时分开存放的，所以是非聚簇的。