五、MySQL索引
1、索引类型
(1)、主键索引(PRIMARY KEY):加速查询 + 列值唯一(值不允许为Null)+ 表中该索引只能有一个
(2)、唯一索引(UNIQUE):加速查询 + 列值唯一(值允许为Null)
(3)、普通索引(NORMAL):仅加速查询
(4)、组合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
(5)、全文索引(FULLTEXT):对文本的内容进行分词,进行搜索
(6)、空间索引(SPATIAL)
(7)、主键和唯一索引的区别:
①、主键是一种约束,唯一索引是一种索引;
②、一张表只能有一个主键,但可以创建多个唯一索引;
③、主键创建后一定包含一个唯一索引,唯一索引并不一定是主键;
④、主键不能为null,唯一索引可以为null;
⑤、主键可以做为外键,唯一索引不行;
2、索引方法(存储结构)
(1)、BTREE
BTREE又称B+树
①、所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的,只有根节点存储关键字最后树的末梢才有值。
②、非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储数据的数据层,非根节点,存储的其实是指向根节点的索引。
③、不可能在非叶子结点存数据。
④、根节点横向也有链指针。
⑤、Btree类型在我们查询数据时适合用于范围查找,在某一叶子的节点到另一节点的范围。例如<,>,orderby等场景都可使用。
(2)、HASH
Hash索引不像BTREE索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以Hash索引的查询效率要远高于BTREE索引,它会将计算出的Hash值和对对应的行指针信息记录在Hash表中。但是虽然Hash效率很高但是同样也有很多的弊端存在和限制存在。
①、Hash索引仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询。
②、Hash索引无法被用来避免数据的排序操作。
③、Hash索引不能利用部分索引键(组合索引)查询。
④、Hash索引在任何时候都不能避免表扫描。
⑤、Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
3、聚集索引和非聚集索引
(1)、聚簇索引(聚集索引)
数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。MySQL的主键就是聚集索引,SQL Sever默认主键为聚集索引,也可以指定为非聚集索引。聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据。
①、叶子节点存的是整行数据,直接通过这个聚集索引的键值找到某行数据。
②、数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。
③、数据行和相邻的键值紧凑地存储在一起,因为无法同时把数据行存放在两个不同的地方,所以一个表只能有一个聚集索引。
(2)、非聚簇索引(辅助索引/非聚集索引)
该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。除了聚集索引以外的都是非聚集索引,比如普通索引、唯一索引、全文索引等。
①、叶子节点存的是字段的值。
②、需要通过这个非聚集索引的键值找到对应的聚集索引字段的值,再通过聚集索引键值找到表的某行数据。
(3)、聚集索引和非聚集索引的区别
①、聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。
②、聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。
③、聚集索引物理存储按照索引排序,聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序。
④、非聚集索引物理存储不按照索引排序,非聚集索引就是普通索引,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序。
4、组合索引特点
多列值组成一个索引,专门用于组合搜索即组合索引,组合索引是先按照第一列进行排序,然后在第一列排好序的基础上再对第二列排序,如果没有第一列的话,直接访问第二列,那第二列肯定是无序的,直接访问后面的列就用不到索引了。搜索需要从根节点出发,上层节点对应靠左的值,搜索需要从根节点出发,否则不从根节点出发,后面的节点对应下层的值,依旧是乱序的,需要遍历,所以索引就失效了,所以有最左原则。
5、索引优点
(1)、大大加快数据的检索速度。
(2)、创建唯一性索引,保证数据库表中每一行数据的唯一性。
(3)、加速表和表之间的连接。
(4)、在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。
6、索引缺点
(1)、索引需要占物理空间。
(2)、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,降低了数据的维护速度。