MySQL-8.索引

2018-11-26  本文已影响0人  王南北丶

本文地址：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索引。

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

四、单列索引

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

4.1. 普通索引

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

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;

4.2. 唯一索引

在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))
);

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

4.3. 主键索引

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

这里主要讲一讲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引擎支持全文索引。

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

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

上一篇 下一篇

猜你喜欢

热点阅读