数据库基础(三)——数据库索引
前言
数据库索引,很多人可能不知道他是什么东西,只是知道这是面试的高频考点(有很多前端工程师面试的时候也问数据库索引,虽然我也不知道是因为啥,大家自己体会吧)。
概述
MySQL官方对索引的定义为:索引是帮助MySQL高校获取数据的数据结构。索引可以通过提取句子的主干。索引主要包括以下几种:
- 主键索引(Primary key):
- 唯一的标识,主键不可重复;
- 只能有一个字段作为主键(原则上来说是有联合主键的操作,但是真心不建议这么做,数据库逻辑复杂的话会对数据库的负载加重,且效率与空间无法兼得,尽量使物理层面的结构更符合当前设计方向的大多数业务);
- 唯一索引(Unique key):
- 唯一索引不唯一,不可以重复(有些版本说唯一索引可以重复,那是因为除了唯一索引以外还对其设定了其他的约束,导致可以实现唯一索引重复的情况,但那不属于基础的范畴,所以不会涉及到这些);
- 避免重复的列出现;
- 多个列都可以被标识为唯一索引。
- 常规索引(Key):数据表中所有的字段如果没有相关约束,则默认为index(key),即常规索引;
- 全文索引(FullText):
- 在特定数据库引擎下——MyISAM下才会支持(据说最新版本的INNODB也支持全文索引,但博主比较忙没有查证,所以暂时不太确定,感兴趣的同学可以去看一看);
- 快速定位数据。
索引
索引的使用
先说明一下普通索引的使用(index/key),常规索引一般是在表中有大量数据的优化中使用。数据量过大,会导致查询等操作的效率变低,所以将某些经常用作条件的字段设定为常规索引,可以理解为对该字段定义了一个Map结构,字段中每个值都对应独一无二的key,根据该字段作为条件时查询,MySQL会快速的通过设定好的索引key来查找到对应的数据,效率提升特别快,下面上代码:
-- 首先先准备百万级别的数据来准备测试
-- MySQL也是可以编程的,不过我不想说明MySQL的编程规则,感兴趣的同学可以自己去学
-- 该行起到的是分割的作用,定义并执行MySQL函数的时候必须用到(标准格式)
DELIMITER $$
-- 先声明一个函数,并规定它的返回值类型
CREATE FUNCTION mock_data()
RETURNS INT
-- 开始定义函数体,函数体以 begin为开头(标准格式)
BEGIN
-- 定义整型变量 num ,并给该变量赋值为 1百万(注意该数值,一定不要定义千万级以上,会执行好久好久)
DECLARE num INT DEFAULT 1000000;
-- 定义循环计数器
DECLARE i INT DEFAULT 0;
-- 开始循环
WHILE i < num DO
-- 设定每次循环都会自增
SET i = i +1;
-- 在循环中执行插入语句
INSERT INTO student VALUES(CONCAT('1000',i),CONCAT('Reman',i),i,'001');
-- 结束循环
END WHILE;
-- 函数结束,返回数据
RETURN i;
-- 函数结束
END;
在准备好百万级的数据后,可以尝试根据除主键外的字段为条件进行查询,这个时候你回发现速度很慢。如果业务中经常以非主键的某个字段查询数据的话,我们可以对该字段设定为常规索引,具体如下:
-- 语法结构为 create index 索引名 on 表名(想要设定为常规索引的字段)
CREATE INDEX sname_index_name ON `student`(`name`)
当字段设定为常规索引的时候,我们在根据这个字段为条件进行查询筛选的时候,速度会特别快,大家可以试一下。
具体原因:
在简单的按条件查询单条数据时,如果没有设定常规索引的时候,我们在执行查询语句的时候是按照该字段对应的值逐行查询,且就算查到对应数据后也会将整表遍历完毕后才会结束插叙。
在建立常规索引之后,是将建立索引字段的所有值存储为一个类似于Map结构一样的键值对,每个值对应的都是独一无二的键,这时我们在根据该字段进行查询时,会快速的找到对应的值,然后返回表中对应的数据,相对来说是查询了一次就会得到结果。
常规索引的存储结构并不是Map,Innodb默认的是BTree,一种树型结构,现在不会说明,只需要记住是键值对的结构即可。
索引原则
- 索引不是越多越好,大量的索引字段会造成数据库冗余,降低效率;
- 不要对进程变动的数据加索引;
- 小数据量不需要加索引(数据量不多的时候,数据库的执行速度可以胜任工作,在大量数据中加入索引是利用空间换时间);
- 索引一般设定在常作为查询条件的字段上。
索引建立
一般来说我们在数据表中建立的索引只有三种,主键、外键、索引,下面使用一段代码来说明:
-- 在创建表的时候建立约束索引
CREATE TABLE `student` (
`sid` varchar(32) NOT NULL,
`sname` varchar(50) DEFAULT NULL,
`sage` int(11) DEFAULT NULL,
`tid` varchar(32) DEFAULT NULL,
-- 定义 sid 字段为主键
PRIMARY KEY (`sid`),
-- 定义 sname 字段为常规索引
KEY `sname_index_name` (`sname`),
-- 定义 tid 为常规索引
KEY `FK_student` (`tid`),
-- 定义 tid 为外键 与 teacher表中的 tid 字段对应
CONSTRAINT `FK_student` FOREIGN KEY (`tid`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `teacher` (
`tid` varchar(32) NOT NULL,
`tname` varchar(50) DEFAULT NULL,
-- 定义 tid 字段为常规索引
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
总结
本章笔记中记录了索引的类型以及相关区别,同时标注了索引设定的相关SQL语句。同时还说明了数据索引的相关规则以及使用索引的原因。