一文熟悉 MySQL 索引

关注
发布于: 2020 年 08 月 12 日
什么是索引？为什么要使用索引？﻿
索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据
﻿
假设现在要你找到一本书的第三章第二节，你会怎么做，一般都会去目录找到对应章节的页数，然后翻到对应页，其实这就是目录索引，帮助读者快速找到想要的章节。mysql中的索引，就类似一本书的目录。建立索引就可以帮助我们快速的查找到我们想要的内容。如果不使用索引，mysql会扫描整张表中的所有记录，表越大，查询就会越慢。使用索引，mysql就能够快速找到数据的位置，不用扫描全表
﻿
举例，假设有一张联系人表，里边有5w条记录，我现在想通过mobile来查找到某个联系人的信息。如果没有在mobile这个字段上创建索引，那么mysql会扫描整张联系人表。如果在mobile上创建了索引，那么mysql就会走索引，不会扫描全表。之后会分享一篇索引的底层实现
﻿
mysql索引优缺点及何时使用﻿
优点：﻿
所有的MySql字段类型都可以被添加索引
提高查询效率
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
﻿
缺点：﻿
创建索引和维护索引需要耗费时间，且随着数据的增加，时间也会边长
索引需要占物理空间
当对表中的数据进行增、删、改操作时，索引也需要动态的维护，降低了数据的维护速度
﻿
何时使用索引？﻿
对于经常进行更新操作的表，减少创建索引。对于经常用于查询的字段，应该创建索引
对于记录较少的表不要建索引，因为可能遍历索引的时间比扫描全表的时间还长，这样索引就没有起到优化查询速度的效果了
像性别这种字段，只有男女两个不同的值的列，不要建立索引
﻿
我们先熟悉了什么是索引？为什么用索引？以及索引有什么优缺点？何时使用它？下边就说一下索引的种类
﻿
索引分类﻿
说明：因为索引是在mysql的存储引擎中实现的，因此，不同的存储引擎会使用不同的索引
﻿
MyISAM和InnoDB存储引擎：只支持BTREE索引
﻿
MEMORY/HEAP存储引擎：支持HASH和BTREE索引
﻿
mysql索引可分成四类：单例索引、*组合索引*、全文索引、*空间索引*
﻿
单例索引﻿
单例索引包含主键索引、*唯一索引*、普通索引
﻿
主键索引：我们一般在创建数据表的时候都会有一个id字段，并将其设置成主键，添加主键索引的列的值必须唯一且不能有空值
﻿
唯一索引：添加唯一索引的列，值必须是唯一的，但是允许为空
﻿
普通索引：被添加普通索引的列没有什么限制，单纯的为了提高查询速度，可以有重复值，且可以为空
﻿
组合索引﻿
组合索引就是在多个字段上创建的索引。需要说明的是，只有在遵循最左前缀集合时，组合索引才会生效。如果不明白，看完后边的举例就明白了
﻿
全文索引﻿
从mysql5.6版本之后，InnoDB开始支持全文索引，并且在5.7之后通过ngram插件可以支持中文，在5.6之前MyISAM支持全文索引。全文索引就是说我们可以通过关键字查询到对应的记录，后边会举例介绍。
﻿
MySQL允许在char、varchar、text类型上建立全文索引
﻿
空间索引﻿
这个索引应该平时用的都比较少，空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。仅MyISAM存储引擎支持
﻿
索引相关操作﻿
创建索引﻿
创建索引，我们可以在创建表的时候创建索引，也可以在创建完表之后，给表中的某些字段添加索引
﻿
建表时创建索引：
CREATE TABLE `contacts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(11) NOT NULL DEFAULT '0' COMMENT '联系人类型 1-本人，0-其它',
  `status` tinyint(11) NOT NULL DEFAULT '0' COMMENT '联系人是否有效 1-有效，0-无效',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `mobile` bigint(64) NOT NULL DEFAULT '0' COMMENT '手机号',
  `mail` varchar(64) NOT NULL DEFAULT '' COMMENT '邮箱',
  `relation` varchar(32) NOT NULL DEFAULT '' COMMENT '联系人关系',
  `address` varchar(128) NOT NULL DEFAULT '' COMMENT '联系人地址',
  `mobile_location` varchar(20) NOT NULL DEFAULT '' COMMENT '手机归属地',
  `memo` text COMMENT '联系备注',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_m` (`mobile`),
  KEY `idx_n`(`name`),
  KEY `idx_multi` (`id`,`name`,`mobile`),
  FULLTEXT KEY `idx_full`(`memo`) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='联系人表,add by 书旅 2019.04.04';
说明：在mysql5.7之后才支持中文的全文索引，在全文索引后边加了WITH PARSER ngram是因为mysql内置了ngram全文解析器，用来支持中文、日文、韩文分词
﻿
﻿
上边的情况是，在创建表的同时给指定字段添加了索引，下边看一下，在一张已经创建完成的表上，给指定字段添加索引
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)
﻿
例如给刚才的contacts表中的mail字段增加一个全文索引：
ALTER TABLE contacts ADD FULLTEXT KEY `idx_multi2`(`mail`) WITH PARSER ngram;  --也可以使用INDEX
﻿
我们也可以通过这样的方式给已创建的表字段添加索引：
CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名)
﻿
删除索引、查看索引﻿
为了后边看见索引是否删除成功，先看一下如何查看一张表 中有哪些索引：
SHOW INDEX FROM 表名;
看一下contacts表中创建了哪些索引：
说明：在后边会说介绍这里每个列的含义
﻿
删除索引有两种方式：
1，ALTER TABLE 表名 DROP KEY/INDEX 索引名
2，DROP KEY|INDEX 索引名 ON 表名
﻿
假设现在要删除mail上的全文索引：
ALTER TABLE contacts DROP INDEX idx_multi2;
﻿
看一下结果：
﻿
我们可以看见，mail上的索引已经成功被删除了
﻿
这里每一列的含义：
﻿
使用索引﻿
知道如何创建索引、查看索引、删除索引之后，那么如何使用索引？如何知道是否使用了索引？
﻿
现在创建一张学生表student：
CREATE TABLE `student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `sex` tinyint(64) NOT NULL DEFAULT '0' COMMENT '性别 1-女，2-男',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `phone` bigint(20) NOT NULL DEFAULT '0' COMMENT '手机号',
  `mail` varchar(32) NOT NULL DEFAULT '' COMMENT '邮箱',
  `detail_info` text COMMENT '详细信息',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`), --主键索引
  UNIQUE KEY `idx_uni`(`phone`), --唯一索引
  KEY `idx_nor`(`mail`), --普通索引
  KEY `idx_multi` (`name`,`sex`,`age`),--联合索引
  FULLTEXT KEY `idx_full`(`detail_info`) WITH PARSER ngram --全文索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表,add by 书旅 2019.04.06';
﻿
通过mysql中的EXPLAIN关键字，我们可以看见一个SQL语句的执行情况，也就是可以看见查询语句是否使用了索引
﻿
我在学生表中插入了三条数据：
﻿
看一下这张表的索引情况：
﻿
然后我们依次通过 id、detail_info、(name、sex、age组合)作为查询条件来查询：
﻿
介绍一下这里每个的含义：
﻿
因为这里边的type特别重要，所以说一下常见的六种type值的含义：
﻿
上面的这些，可以先记住possible_key和key
﻿
使用全文索引
﻿
使用组合索引
﻿
前边说过，组合索引遵循最左前缀。利用索引中最左边的列集来匹配行，这样的列集称为最左前缀，前边创建的student表中，我在(name,sex,age)上创建了组合索引，索引行中就按name/sex/age的顺序存放，索引可以索引下面字段组合(name，sex，age)、(name，sex)或者(name)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，sex或者（sex，age）组合就不会使用索引查询
﻿
总结﻿
到这里基本上mysql索引相关的一些基本东西都说了，知道了索引是什么？是干什么的？为什么使用？怎么使用？后边补充一些什么情况下索引会失效以及索引的实现原理
﻿