mysql索引的本質

索引的本质

索引基本概念：是数据库管理系统（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;      

1. 普通索引(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';      

1. 全文索引(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);      

1. …

删除索引

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

ALTER TABLE user_innodb DROP INDEX comidx_name_phone;