【MySQL】索引使用

索引

• 索引
• 聚集索引和非聚集索引
• 索引类型

索引

Mysql索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

Mysql的BTree索引使用的是B数中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

拓展：

• MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

• InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。、

聚集索引和非聚集索引

• 聚集索引：表数据按照索引的顺序来存储，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。【索引指向数据页】

• 非聚集索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶节点包含索引字段及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。【索引指向数据行】

• 总结：聚簇索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针；非聚簇索引是密集索引，数据页的上一级索引页为每一个数据行存储一条索引记录。

使用：

• 聚簇索引：Innodb存储引擎，主索引文件和数据文件是同一份文件叶子节点上的data就是数据本身
  索引项直接对应实际数据记录的存储页，索引项的排序和数据行排序一致，一个表只能有一个聚集索引

• 非聚集索引：MyISAM存储引擎，主要指B+Tree叶子节点的dtat，并不是数据本身，而是数据存放的地址。
  不能直达，链式访问多级页表才能定位数据页，一个表可以有多个非聚集索引。

索引创建语句

CREATE table 表名 [列名 数据类型]
[unique|fulltext] [index|key] [index_name] (列名 [长度]) [asc|desc]

• [unique|fulltest]为可选参数，分别表示唯一索引、全文索引
• index和key为同义词，两者作用线共同，用来指定创建索引
• 列名为需要创建索引的字段列名，该列必须从数据表中该定义的多个列中选择
• index_name为指定索引的名称，为可选参数，如果不指定，默认数据表的列名为索引列名
• length为可选长度，表示索引的长度，只有字符串类型字段才能指定索引长度
• asc|desc为指定升序或降序存储索引

索引类型

普通索引、唯一索引、主键索引、组合索引、全文索引
普通索引、唯一索引、主键索引也是单列索引。

普通索引

最基本的索引，没有任何限制

直接创建索引：

CREATE index 索引名 on 表名(列名(length))

修改表结构的方式添加索引：

Alter Table 表名 Add index 索引名 (列名(length))

创建表同时创建索引：

CREATE table 表名(
id int(11) not null AUTO_INCREMENT,
tittle varcha(255) character not null,
Primery key(id)
index 索引名(tittle(length))
)

删除索引：

drop index 索引名 on 表名

唯一索引

与普通索引类似，不同的是：索引列的值必须唯一，允许有空值，如果是组合索引，则列值的组合必须唯一

创建唯一索引

Create unique index 索引名 on 表名(列名（length）)

修改表结构：

Alter Table 表名 Add unique index 索引名 on 表名(列名(length))

创建表同时创建索引：

CREATE table 表名(
id int(11) not null AUTO_INCREMENT,
tittle varcha(255) character not null,
Primery key(id)
unique index 索引名(tittle(length))
)

主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键， 不允许有空值，一般在创建表时创建主键索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

组合索引

多个字段上创建的索引，只有在查询条件中使用了创建索引时的最左字段，索引才会被使用，即最左前缀

Alter Table 表名 ADD index 表名 on 表名(列名1，列名2)

全文索引

主要用来查找文本的关键字，而不是直接和所引用的值比较。
fulltext缩影配合match against操作使用，而不是使用where语句，可以再create table、alter table、create index中使用，目前只有char、char、text系列上可以创建全文索引。
先将数据放入一个没有全局索引的表中，再用create index 为其创建全文索引fulltext，要比先为一张表创建fulltext索引再添加数据快得多。

建表时创建全文索引：

CREATE TABLE table_name(
    id int(11) NOT NULL AUTO_INCREMENT,
    title varchar(255) CHARACTER NOT NULL,
    content text CHARACTER,
    PRIMARY KEY (id),
    FULLTEXT (content)
);

修改表结构添加全文索引

ALTER TABLE table_name ADD FULLTEXT index_name(col_name);

直接创建全文索引

CREATE FULLTEXT INDEX index_name ON table_name(col_name);

本文地址：https://blog.csdn.net/Danerzz/article/details/110451698