索引的本质

索引的本质

索引基本概念:是数据库管理系统(DBMS)中一个排序的数据结构,以协助快速查询、更新数据库表中数据。

理解要点:首先数据是以文件的形式存放在磁盘上面的,每一行数据都有它的磁盘地址。如果没有索引的话,要从 500 万行数据里面检索一条数据,只能依次遍历这张表的全部数据(全盘扫描),直到找到这条数据。但是有了索引之后,只需要在索引里面去检索这条数据就行了,因为它是一种特殊的专门用来快速检索的数据结构,我们找到数据存放的磁盘地址以后,就可以拿到数据了。

索引类型与创建

  1. 唯一索引(Unique):键值不能重复。主键索引是一种特殊的唯一索引(键值不能为空),用 primay key 创建。

    -- 唯一索引,建表时创建,用unique index或unique key创建:
    -- 普通索引,建表时添加,用index或key关键字创建:
    -- 全文索引,建表时添加,用fulltext index或者fulltext key创建
    CREATE TABLE `user_innodb` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `gender` tinyint(1) DEFAULT NULL,
      `phone` varchar(11) DEFAULT NULL,
      `idcard` varchar(11) DEFAULT NULL,
      PRIMARY KEY (`id`), -- 主键索引
      UNIQUE INDEX(idcard), -- 唯一索引
      KEY `comidx_name_phone` (`name`,`phone`), --联合普通索引
      FULLTEXT INDEX(name) -- 全文索引
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
    
  2. 普通索引(Normal):也叫非唯一索引,是最普通的索引,没有任何的限制。

    -- 普通索引,建表后添加
    ALTER TABLE 表名 ADD INDEX 索引名称(column);
    -- name和phone创建联合索引
    ALTER TABLE user_innodb ADD INDEX comidx_name_phone (name,phone);
    -- 联合索引最左匹配
    EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮' AND phone = '15204661800';
    EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮';
    EXPLAIN SELECT * FROM user_innodb WHERE phone = '15204661800';
    
  3. 全文索引(Fulltext):针对比较大的数据,比如我们存放的是消息内容,有几 KB 的数据的这种情况,如果要解决 like 查询效率低的问题,可以创建全文索引。只有文本类型的字段才可以创建全文索引,比如 char、varchar、text。

    -- 全文索引创建
    create table m3 (name varchar(50), fulltext index(name));
    
    -- 全文索引的使用
    select * from fulltext_test where match(content) against('咕泡学院' IN NATURAL LANGUAGE MODE);
    

删除索引

innodb 数据和索引文件放在一个文件中

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;

注意:创建索引会占用空间(数据文件大小变大),但查询速度会标快许多,以空间换速度

疑问?为什么删除索引后,数据文件大小并没有缩小 488 MB (511,705,088 字节)

上一篇:抽象工厂模式,加工出不可思议的产品!


下一篇:Java学习第七天