MySQL 索引介绍（1）

忘是亡心i 2022-11-27 07:29 154阅读 0赞

转载自：[https://www.jianshu.com/p/a1ea26b9ac3a][https_www.jianshu.com_p_a1ea26b9ac3a]

## 叙述 ##

什么是索引呢？可能初学者常常会听到这个词，但是不明白是什么意思。其实简单来说，索引就是一种优化查询速度的方式。

一般的应用使用数据库时，读写数据的比例大概在10：1之间，也就是说，执行查询语句的比例远远大于写入。所以，查询语句的运行效率常常决定着一个应用的性能瓶颈，而索引就是一个牺牲写入效率，提升查询效率的工具。

在没有建立索引的表中，如果要去查询一条数据，那么就必须全盘扫描整张表，知道找到目标数据为止。而建立了索引的表中，索引就好像是字典中的目录，通过特定算法记录着我们存储数据的指针，可以让我们搜索数据的速度大大缩短。目前`MySQL`数据库主要支持的索引类型有**`B-tree`**和**`HASH`**。

### 索引的优劣 ###

**优点**：

1.  能大大的加快数据库中查询的速度。

**缺点**：

1.  索引会占用额外的存储空间，如果创建索引过多，占用空间会很大。
2.  在对建立了索引的表中写入数据时，索引需要动态的维护。也就是说，会影响写入数据的效率。

**注意事项**：

1.  **避免给经常更新的表建立太多索引，以免影响太多写入效率。**
2.  **建立索引的字段，越短越好，数值类型越简单越好。**
3.  **尽量避免字段中出现`NULL`，因为`NULL`值的查询是不走索引的，含空值的列是很难做查询优化的。所以要在建立字段时加上`NOT NULL`的约束条件，空值用`0`或者空字符代替。**
4.  **索引不要盲目建立，一般只需要给在`WHERE`和`JOIN`字句中需要出现的字段建立索引即可。**
5.  **建立索引后，查询的时候不要进行运算，因为运算的话会变成全表查询。只有这些运算符：`<`，`<=`，`=`，`>`，`>=`，`BETWEEN`，`IN`，以及某些时候的`LIKE`会被索引支持。**

### 索引的分类 ###

由于`MySQL`支持的引擎类型较多，而索引是在存储引擎中实现的，所以不同的存储引擎有不同的索引方式。常用的**`InnoDB`**和**`MyISAM`**引擎都只能使用**`B-tree`**索引。

根据索引的使用方式，也分为`单列索引`、`组合索引`、`全文索引`和`空间索引`，这其中单例索引又分为`普通索引`、`唯一索引`和`主键索引`。

### 单列索引 ###

单列索引指的是这个索引只对单个列起作用。

**普通索引**

以下是创建普通索引三种的方式：

CREATE INDEX indexName ON tbname(colname(length));

ALTER table tableName ADD INDEX indexName(colname);

CREATE TABLE tbname(
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX [indexName] (colname(length))
    );

创建索引时，如果字段是`CHAR`，`VARCHAR`类型，`length`可以小于字段实际长度。如果是`BLOB`和`TEXT`类型，必须指定`length`。

同时，索引的名字也是可选的，如果没有指定名字的话，那么`MySQL`会默认用字段名作为索引的名字。

索引建好之后，可以使用以下命令查看：

SHOW INDEX FROM table_name;

同样也可以使用以下命令删除普通索引：

DROP INDEX [indexName] ON table_name;

**唯一索引**

在`MySQL`之中，有一个比较特殊的地方就是，`KEY`和`INDEX`常常被混淆在一起，虽然说本质上`KEY`和`INDEX`不是同一个东西，但是究其原因，`MySQL`中的约束效果是通过索引来实现的，`MySQL`数据库判断是否当前列是否unique就是通过unique索引判断的。

因为这个原因，所以其实我们的`UNIQUE KEY`约束条件其实也就建立了索引，两者被完全绑定到了一起。

以下是建立唯一索引的方式：

CREATE UNIQUE INDEX indexName ON tablename(colname(length));

ALTER TABLE table_name ADD UNIQUE indexName ON (colname(length));

CREATE TABLE table (
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    UNIQUE [indexName] (colname(length))
    );

唯一索引的删除方式与普通索引是一致的。

**主键索引**

主键索引也是类似，与主键这个约束条件基本是相同的，创建的方式与主键通用，就不一一赘述了。

这里主要讲一讲`InnoDB`引擎上主键的选择。在没有特殊需求的情况下，主键都应该使用无关业务逻辑的自增字段。

有人会觉得，使用身份证之类的数据来作为主键也是可以的，但是在算法层面上，如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样会形成一个紧凑的索引结构，提高效率。

而如果使用的是身份证号这种非自增数据的话，那么数据的值就是类似随机的数据，就达不到这个效果了。

### 组合索引 ###

组合索引其实就是用多个字段做一个索引，在查询时如果有多个字段做为索引条件的话，那么组合索引的效率会大大高于每个字段做一个单列索引。因为`MySQL`的查询每次只能使用一个索引，所以多个单列索引只会用到一个索引，其他字段的查询就会变成全文搜索。

建立组合索引的方式：

CREATE INDEX indexname On tbName(colname(length),colname(length),...);

ALTER TABLE tbname ADD INDEX indexname (colname(length),colname(length),...);

最左前缀原则：

举例来说，如果使用`a`, `b` ,`c`三个字段建立组合索引之后，其实等价于建立了`a b c`, `a b`, `a`三个索引，这主要由建立组合索引时的字段顺序决定。

也就是说，如果使用`b c`两个字段查询的话，那么组合索引是无效的，所以建立组合索引的时候，一定要详细考虑字段的顺序。

### 全文索引和空间索引 ###

前面有提到，在建立索引的时候，字段的值越短越好。但全文索引则不然，全文索引可以对字段全文搜索，也就是使用文本的一部分，可以搜索出这一整条记录，但只有`MyISAM`引擎支持全文索引。

空间索引则是对空间数据类型的字段建立的索引。

由于这两种索引使用的比较少，所以就不多赘述了。

[https_www.jianshu.com_p_a1ea26b9ac3a]: https://www.jianshu.com/p/a1ea26b9ac3a

MySQL 索引介绍（1）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL索引优化系列（1）—索引介绍

相关 mysql索引介绍

相关 MySQL 索引介绍（1）

相关 MySQL索引（一）介绍索引

相关 mysql 索引介绍

相关 MySQL索引介绍

相关 MySQL索引介绍

相关 MySQL索引介绍

相关 Mysql—索引介绍

相关 mysql索引介绍