1. 什么是数据库索引
索引,被称之为数据库的目录,可以让我们快速地找到对应的数据。索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。索引的本质就是空间换时间。
2. 索引种类
索引的实现通常采用B树或B+树,加快查询速度也消耗更多空间;
2.1. 唯一索引: 不允许任何两行具相同值
2.2. 主键索引 :唯一索引的一种
2.3. 聚集索引: 行的物理顺序和键值的索引顺序相同
2.4. 普通索引: 无限制
2.5. 全文索引: 针对较大的数据生成全文索引很耗时间空间
2.6. 组合索引: 最左前缀原则:若对多列建立组合索引,若第二列未使用索引,则第三列也不会使用
2.7. InnoDB主索引: InnoDB的数据文件本身;辅助索引:相应记录主键的值
2.8. MyISAM 索引与数据分离;辅助索引:与主索引无区别;
3.索引的创建方式
3.1.普通索引
3.1.1. 创建索引:CREATE INDEX indexName ON tableName(username(length));
3.1.2. 修改表结构:ALTER tableName ADD INDEX [indexName] ON (username(length))
3.1.3.创建表的时候直接指定:
CREATE TABLE tableName (
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
3.3.唯一索引
3.3.1. 创建索引: CREATE UNIQUE INDEX indexName ON tableName (username(length))
3.4.主键索引:一般在创建表时创建,PRIMARY KEY(ID)
3.5.组合索引:ALTER TABLE tableName ADD INDEX zuhe (name,age,sex);
根据MySQL组合索引“最左前缀”原则,建立这样一个索引相当于建立如下三个索引:
3.5.1.name,age,sex
3.5.2.name,age
3.5.3. name
4.索引原理
4.1.聚焦索引
主流的RDBMS都是把 平衡多路搜索树B树(B-tree) 当做数据表默认的索引数据结构的。如果给表加上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,整个表变成了一个索引,也就是所谓的「聚集索引」。主键的作用就是把「表」的数据格式转换成「索引(平衡树)」的格式放置。
(此处画的是二叉树,但实际并不是二叉树。而是平衡多路搜索树)
假如数据库的一张表上有一亿条数据,如果采用顺序查询的方式进行查询,那得花费很长的时间才能查询完成;但是如果采用聚焦索引来查询数据那就会快上特别多,因为查找方式是一层一层的查找的(父到子再到子,依次向下)。
索引能让数据库查询数据的速度上升, 而使写入数据的速度下降, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销。
4.2.聚焦索引
每个非聚焦索引,都会独立生成一个B-树,树上的节点数据复制自表中该索引列。
非聚集索引和聚集索引一样, 同样是采用平衡树作为索引的数据结构。索引树结构中各节点的值来自于表中的索引字段,每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。
因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。
非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据,不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径(覆盖索引除外(复合或组合索引))。
如:create index suoyin on user(age);
select name from user where age= '19'
这条SQL的执行过程如下, 首先通过 非聚焦索引 suoyin 查找索引 age= '19' 的主键ID值,然后通过主键ID值执行聚焦索引查找,找到主键ID对应的真实数据行的存储位置,最后取得 name 的值返回。
但是,我们建立组合索引:
create index suoyin on user(age,name);
则SQL语句 ,select name from user where age= '19'
执行过程如下:
通过非聚集索引suoyin 查找 age= '19' 的叶节点的内容,然而, 叶节点中除了有 name表主键ID的值以外, name字段的值也在里面, 因此不需要通过主键ID值的查找数据行的真实所在, 直接取得叶节点中name的值返回即可。