数据库基础（三）——数据库索引

数据库基础（三）——数据库索引

前言

数据库索引，很多人可能不知道他是什么东西，只是知道这是面试的高频考点（有很多前端工程师面试的时候也问数据库索引，虽然我也不知道是因为啥，大家自己体会吧）。

概述

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语句。同时还说明了数据索引的相关规则以及使用索引的原因。