【MySQL】索引

目录

为什么要有索引呢？

什么是索引？

索引原理

索引分类

索引创建原则

索引数据结构

聚簇索引和非聚簇索引

---

为什么要有索引呢？

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

什么是索引？

索引是帮助MySQL快速获取数据的一种数据结构。

特点：①排好序 ②快速

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

索引原理

索引的目的是快速高效的查找数据，就像我们阅读书一样。先根据图书的目录找到对应的章节，再从找那个接种查找具体的一小节内容。从整本书到章节再到具体的某一小节，查找范围不断地缩小。但是没有目录的话，我们需要一页一页的翻阅，其查找效率可想而知。而索引就相当于图书的目录，目录中记录着章节的页数，而根据索引也能找到数据的地址。

图书目录----->索引; 页数------->数据地址。

索引优势

1. 提高数据检索的效率,降低数据库的 IO 成本;

2. 通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗;

索引劣势

1. 索引与实体表的地址也是保存在一张表中的，占用磁盘空间。

2. 维护索引的开销较大。

   虽然索引大大提高了查询速度,同时却会降低更新表的速度,例如对表进行INSERT,UPDATE 和 DELETE,因为更新表时,MySQL 不仅要保存数据,还要保存一下索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

索引分类

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

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。

唯一索引:索引列的值必须唯一，允许为 null。

组合索引(复合索引)：一个索引包含多个列（可理解为单值索引的组合）。

如果多个列用多个单值索引，显然，比较浪费（磁盘空间、时间开销）。因此，可以使用一个索引来关联多个列。

组合索引最左前缀原则

列如表中有 a,b,c 3 列,为 a,b 两列创建组合索引,那么在使用时需要满足最左侧索引原则.在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则组合索引不生效.

例如：

select * from table where a=’’and b=’’索引生效 (where条件中有组合索引最左侧列a)
​
select * from table where b=’’and a=’’索引生效 (where条件中有组合索引最左侧列a)
​
select * from table where a=’’and c=’’索引生效 (where条件中有组合索引最左侧列a)

select * from table where b=’’and c=’’索引不生效(where条件中有组合索引最左侧列a,b不是最左侧列)

全文索引

需要模糊查询时，一般索引无效，这时候就可以使用全文索引了。

查看索引：

SHOW INDEX FROM 表名;

索引创建原则

哪些情况需要创建索引

• 主键自动建立唯一索引

• 频繁作为查询条件的字段(where后面的语句)

• 查询中与其他表联的字段,外键关系建立索引

• 查询中排序的字段(索引提高排序效率)

• 分组的字段

哪些情况不建议创建索引

• 表记录太少

• 经常需要增删改的表(需要更新索引,开销较大,效率低)

• where中用不到的字段

• 数据重复且分布平均的表字段(比如性别)

  应该只为最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

索引数据结构

InnoDB引擎采用B+树来实现索引，并且索引与数据是一起存放的。

特点：

• 排好序的,一个节点可以存储多个数据。

• 非叶子节点不存储数据,只存储索引,可以放更多的索引。

• 一个节点可以存储多个元素，B+树的高度较低。

• 数据记录都存放在叶子节点中。

• 所有叶子节点之间都有一个链指针。

聚簇索引和非聚簇索引

聚簇索引 ：找到了索引就找到了需要的数据，那么这个索引就是聚簇索引。

所以主键就是聚簇索引。

非聚簇索引

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

例子

CREATE TABLE student (
    id BIGINT, 
    NO VARCHAR (20), 
    NAME VARCHAR (20), 
    PRIMARY KEY (`id`), 
    UNIQUE KEY `idx_no` (`no`)
)

①直接根据主键查询获取所有字段数据，此时主键是聚簇索引，因为主键对应的索引叶子节点存储了 id=1 的所有字段的值。

②根据编号查询编号和名称，编号本身是一个唯一索引，但查询的列包含了学生编号和学生名称，当命中编号索引时，该索引的节点的数据存储的是主键 ID，需要根据主键 ID 重新查询一次，所以这种查询下不是聚簇索引。

MySQL 中 InnoDB 引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计。

MyISAM 引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中。