一、什么是索引
1. 概念
- 数据本身之外,数据库还维护着一个满足特定找找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以再这些数据的基础上实现高级查找算法,这种数据结构就是索引。
- MySQL官方对索引的定义:索引是帮助MySQL高效获取数据的数据结构。即索引是一种数据结构。
- 索引的目的在于提高查找效率,它是排好序的。
2.索引的存储
- 一般来说索引本身也很大,因此往往以索引文件的形式存储在磁盘上。
3.索引的优缺点
(1)优点
- 提高了数据检索的效率,降低了数据库的IO成本。
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
(2)缺点
- 索引实际也是一张表,保存了主键与索引字段,并指向实体表的记录,所以索引是占用空间的。
- 索引虽然提高了查找效率,但是降低了表更新速度,在进行更新表操作时,不但要保存数据,还需要保存索引文件每次更新后添加了的索引列的字段。也就是说,表更新不但更新数据,还更新索引信息。
二、mysql索引分类
1. 单值索引
- 即一个索引只包含单个列,一个表可以有多个单值索引。
(1)唯一索引:索引列的值必须唯一,但是允许有空值。
(2)普通索引:允许在定义索引的列中插入重复值和空值。
(3)主键索引:是特殊的唯一索引,不允许有空值。
2. 复合索引
- 在表的多个属性组合上创建的索引,并且列值的组合必须唯一。
- 使用复合索引时,遵循最左前缀集合。
3. 全文索引
- 底层实现为倒排索引。
- 只有MyISAM存储引擎支持全文索引。其类型为FULLTEXT,在定义索引的列上支持值的全文查找。
- 允许在索引列中插入重复值和空值,它可以在CHAR,VARCHAR,TEXT类型的列上创建。
4. 空间索引
- 对空间数据类型的字段建立的索引,使用SPATIAL关键字。
- MySQL中的空间数据类型有四种,GEOMETRY、POINT、LINESTRING、POLYGON。
- 要求引擎为MyISAM,创建空间索引的列,必须将其声明为NOT NULL。
5. 基本语法
-- 创建
-- 方法一
-- UNIQUE表示是唯一索引
-- 如果小括号中只有一个字段,那就是单值索引,否则是复合索引
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(`length`));
-- 方法二
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(`length`));
-- 删除
DROP INDEX [indexName] ON mytable;
-- 查看
SHOW INDEX FROM table_name;
-- 四种方式添加索引
-- 主键索引
ALTER TABLE tbl_name ADD PRIMARY KEY(clnum_list);
-- 唯一索引
ALTER TABLE tbl_name ADD UNIQUE index_neme(clnum_list);
-- 普通索引
ALTER TABLE tbl_name ADD INDEX index_neme(clnum_list);
-- 全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_neme(clnum_list);
三、mysql索引结构
1. BTree索引
- MyISAM和InnoDB存储引擎只支持B+Tree索引,默认使用BTree。
(1)B树结构
- B树是为了磁盘或其他存储设备设计的一种多叉平衡查找树。
- B树高度一般在2-4,树的高度直接影响IO读写的次数。
- 三层树结构支撑数据可达20G,四层树结构支撑数据可达几十T。
(2)B树和B+树的区别
- 最大区别在于:非叶子节点是否存储数据。B树是非叶子节点和叶子节点都会存储数据,而B+树只有叶子节点才存储数据,因为中间节点不保存数据,所以磁盘页能容纳更多节点元素,更“矮胖”,而且这些数据都有指针指向,也就是有顺序的。
- b+树查询必须查找到叶子节点,b树只要匹配到即可不用管元素位置,因此b+树查找更稳定。
- 对于范围查找来说,b+树只需遍历叶子节点链表即可,b树却需要重复地中序遍历。
2. Hash索引
- MEMORY/HEAP存储引擎支持Hash和BTree索引。
(1)优点
- Hash索引的检索可以一次到位,所以Hash索引的查询效率更高。
(2)缺点
- Hash索引只能满足"=",“IN”,"!=",不能使用范围查询。
- Hash值的大小关系不一定个原键值一样,不能做排序操作。
- 联合索引中不能利用部分索引键查询。
- 遇到大量Hash值相等的情况后,性能不一定比B树高。
- 存储引擎会为Hash索引中的每一列都计算hash码,Hash索引中存储的即hash码,所以每次读取都会进行两次查询。
四、聚簇索引与非聚簇索引
1. 非聚集索引(MyISAM)
- 数据和索引不在一起
- data保存的都是地址。
- 主键索引:主键索引要求key是唯一的,叶节点的data域存放的是数据记录的地址。
- 辅助索引(次要索引):辅助索引的key可以重复,data域保存数据记录的地址。MyISAM中索引检索:首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
2. 聚集索引(InnoDB)
- 数据和索引在一起。
- 主键索引:data域保存了完整的数据记录。InnoDB要求表必须有主键,如果没有显式指定,或默认选择一个可以唯一标识的列作为主键,如果没有符合条件的列,MySQL自动为InnoDB表生成一个隐含字段(类型为长整形)作为主键。
- 辅助索引:data域存储主键值。采用回表查询,需要两遍检索:首先检索辅助索引获得主键,然后用主键在主索引中检索获得记录。
五、索引建立原则
1. 适合建立索引的情况:
- 在指定unique或primary key的列上,建立唯一索引。
- 频繁作为查询条件的字段应创建索引。
- 有外键关系的字段适合建立索引。
- 在高并发的情况下更适合创建组合索引。
- 为查询中统计或者分组字段上建立索引。
2. 不适合建立索引的情况:
- 表记录太少。
- 频繁增删改的字段和表不适合创建索引。
- where条件中不需使用的字段不创建索引。
- 如果某个数据列包含许多重复内容,那么建立索引意义不大。
六、常见问题
1. 主键和唯一索引的区别
名称 | 主键 | 唯一索引 |
本质 | 一种约束 | 一种索引 |
一张表中存在的数量 | 一个 | 多个 |
是否可以为null | 不能 | 能 |
是否可以被其他表引用 | 可以 | 不可以 |
是否实际存在 | 不是(逻辑键) | 是(物理键) |
其他 | 主键创建后一定包含唯一性索引,而唯一索引不一定就是主键 |
2.Hash索引和B+树索引的区别
(1)hash索引
- hash索引底层是hash表。
- 进行查询时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据。因此无法避免回表查询。
- 因为hash函数的不可预测,所以它不支持使用索引排序,不支持范围查询,不支持模糊查询以及多列索引的最左前缀匹配。
- hash索引查询不稳定,性能不可预测,当某个键值存在大量重复的时候,会发生hash碰撞,此时查询效率可能极差。
(2)B+树索引
- B+树底层实现原理是多路平衡查找树。
- 每次查询都从根节点出发,查询到叶子节点才能获得所查键值,然后判断是否需要回表查询。在符合某些条件的时候,可以只通过索引完成查询,不需要回表查询。
- B+树的所有节点皆遵循:左节点小于父节点,右节点大于父节点,支持范围查询。
- 查询效率比较稳定,对于查询都是从根节点到叶子节点,且树的高度较低。
3. 如何删除百万级别以上数据
- 首先,删除索引,此过程耗时三分钟左右;
- 其次,删除无用数据,此过程耗时不到两分钟;
- 最后,在删除完成后,重新创建索引,此过程耗时十分钟左右。
4. 何时使用聚簇索引与非聚簇索引