MySQL数据库-索引

最新推荐文章于 2024-07-11 10:15:00 发布

讲不出再见

最新推荐文章于 2024-07-11 10:15:00 发布

阅读量178

点赞数

分类专栏： MySQL数据库文章标签：索引索引分类创建索引删除索引索引设计原则

本文链接：https://blog.csdn.net/dgh112233/article/details/84668714

版权

MySQL数据库专栏收录该内容

17 篇文章 3 订阅

订阅专栏

1. 概念

索引是一种具有独立物理空间的数据库结构。使用索引的目的就是提高查询数据的速度。比如，不用索引，那么查询数据就要一个个遍历数据库记录去查询，直到查询到，如果有了索引，那么就会根据索引去查，大大提高速度。

优点：1、大大提高查询速度，这是主要原因； 2、使用排序和分组查询的时候，明显减少分组和排序的时间。

缺点：1、索引要独立占据空间，所以消耗存储空间； 2、如果数据表的数据频繁的变动，那么索引也会变动，这样维护索引的时间开销增大。

索引是在存储引擎中实现的，每种存储引擎的索引不一定相同，且支持每张表的最大索引数和最大索引长度都不一定相同。mysql中的索引的存储类型：BTREE 和 HASH。

2. 索引分类

2.1 聚集索引和非聚集索引

MySQL的存储引擎InnoDB 支持聚集索引和非聚集索引，而MyISAM引擎不支持聚集索引。什么是聚集索引呢？就需要先了解B+树，聚集索引就是基于B+树实现的，非叶子节点只存储索引值，而叶子节点存储索引值和对应的行数据，因此聚集索引是包含了表数据的，直接在聚集索引里就可以读写数据了。什么是非聚集索引呢？也是基于B+树的，但是它的所有节点都只存索引值，只不过叶子节点还存储了一个指针，指向聚集索引中对应行的主键，若是在非聚集索引里查询数据，要查询2遍，第一遍在非聚集索引里查询，获取聚集索引中对应数据的索引值（主键），第二遍在聚集索引中查询一次，获取该行数据。

2.2 普通索引和唯一索引

普通索引（属于非聚集索引），允许在定义了索引的列中插入重复值和NULL。

唯一索引（属于非聚集索引），允许在定义了唯一索引的列中存在空值，但是所有的值必须唯一。

如何选择呢？在查询数据时，两者的性能差不多。

如果业务要求某个字段唯一，但是代码不能完全保证写入唯一值，则添加唯一索引。

如果代码确定某个字段不会有重复的数据写入，则可以选择添加普通索引，数据修改时，普通索引优于唯一索引，毕竟还要检查唯一性。普通索引可以使用 Change Buffer，并且出现死锁的概率比唯一索引低。

2.3 单列索引和多列索引（组合索引）

单列索引即一个索引只包含单个列，一个表可以有多个单列索引。

多列索引值在表的多个字段组合上建立一个组合索引，一个多列索引的查询效率高于多个单列索引，但是多列索引查询时，必须查询的条件值中最左边的字段有值，否则多列索引不起作用。

为什么了组合索引比多个单列索引更好？

因为多个单列索引的话，当多条件查询时，mysql只会选取其中它认为最合适的一列索引来查询（只有一列索引起作用）。而组合索引更适合多条件查询。另外组合索引遵循最左匹配原则，比如(列1，列2，列3)定了组合索引，那么在查询时，必须保证最左边有值，否则组合索引不起作用，比如（，23,45）没用，就是从左边开始，运行只有左边值，比如（12，，，），（12,23，）都行，因为在组合索引中查询时，你只有给定了最左边列的值，它才能知道下面的查询方向。

2.3 全文索引

全文索引不是全文去索引，而是将非结构化的数据称为全文，所以叫全文索引，全文索引是搜索引擎使用的，所以全文索引是根据查询的关键字去索引列查询，是否有关键字，包含关键字都行，就像搜索引擎一样。

这样理解，对于数据库中的一列，总记录数是N，这列就有N个内容（数据，字符串等等），如果定义了单列索引，在查询的时候，数据库会在索引中根据查询的内容去和N个内容中查找，每个内容就比较一次，是内容与内容比较，直到找到一模一样的内容为止。而全文索引不是这样的，全文索引特别针对于字符、字符串、文本类型，比如字符串列被定义了一个全文索引，列有三个值（'abd'，'hello'，'world'），当查询值为'el'字符串时，会匹配到'hello'。

2.4 空间索引

3. 索引设计原则

4. 创建索引

4.1 创建普通索引

创建表格的时候

create table 表名 (列1，列2，...，列n, 索引类型 [索引名] (列名 [索引长度]) [ASC或DESC]);

[ ]里的内容可选，唯一索引（unique），全文索引（fulltext），空间索引（spatial），普通索引（index 或者 key），索引名可以自己定义一个，如果没有定义，mysql默认将列名作为索引名，索引长度只有字符串类型的字段才能指定索引长度，ASC 或 DESC指定升序或者降序的索引值存储。

指定某列或者某几列为普通索引，如果没有指定索引名，那么mysql会自动以列名为索引名；举个例子如下：

之前看过代码规范，有提到说SQL中的关键字还是用全大写吧。

CREATE TABLE index_test(
    id INT PRIMARY KEY,
    `name` VARCHAR(20),
    age SMALLINT,
    type TINYINT,
    INDEX my_index(id, `name`(500)) DESC
    --INDEX (id, `name`(500)) DESC
);

4.2 创建唯一索引

和普通索引的创建方式一样，只是关键字是UNIQUE [ 索引名 ]（列名）；有没有注意到，主键也是唯一的，其实列被声明为主键，mysql就为其创建了唯一索引。

4.3 创建单列索引和多列索引

和普通索引一样，关键字为INDEX，只是列名是一个和多个的区别；

4.4 创建全文索引和空间索引

全文索引关键字FULLTEXT；空间索引关键字SPATIAL；

4.5 在已有的表上建立索引

alter table table名 add index 索引名(列)；其他的索引类似；

5. 删除索引

两种删除写法。

ALTER TABLE 表名 DROP 索引类型索引名；

DROP 索引类型索引名 ON 表名；

注意：AUTO_INCREMENT 约束的字段的唯一索引不能被删除。

如果删除一个列，该列为组合索引的一部分，那么就会从索引中将该列删除。

讲不出再见

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库-索引

概念索引是一种具有独立物理空间的数据库结构。使用索引的目的就是提高查询数据的速度。比如，不用索引，那么查询数据就要一个个遍历数据库记录去查询，直到查询到，如果有了索引，那么就会根据索引去查，大大提高速度。优点：1、大大提高查询速度，这是主要原因； 2、使用排序和分组查询的时候，明显减少分组和排序的时间。缺点：1、索引要独立占据空间，所以消耗存储空间； 2、...
复制链接

扫一扫

专栏目录