MySQL基础之索引

MySQL基础之索引

基础

索引介绍

​ 索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。索引是进行SQL优化时最常用的工具，但是不合理的索引不仅对性能提升没有帮助可能还会造成负担。

索引的分类

• 按功能分类
  • 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL。
  • 外键索引：一张表可以有多个外键，允许重复、允许为 NULL。
  • 唯一索引：一张表可以有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  • 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入。
  • 全文索引：它查找的是文本中的关键词，主要用于全文检索。
  • 空间索引：
    • 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。
• 按列数分类
  • 单例索引：一个索引只包含一个列，一个表可以有多个单例索引。
  • 组合索引：一个组合索引包含两个或两个以上的列。
• 存储方式分类
  • 聚簇索引：聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。
  • 非聚簇索引：非聚簇索引数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录而是主键值。首先通过辅助索引找到主键值，然后到主键索引树中通过主键值找到数据行。
• 数据结构分类：
  • B+Tree：支持范围查询（InnoDB和MyISAM存储引擎都只支持B+Tree）。
  • Hash：Hash结构的优点是只需要经过一次算法即可找到相应的键值，速度比较快，但是不支持范围查询（InnoDB和MyISAM引擎都不支持Hash索引，MEMORY引擎支持）。

创建索引

建表时建立索引

CREATE TABLE `index_test` (
  `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '主键',
  `int_hash_key` int DEFAULT NULL COMMENT '数值类型的hash索引',
  `int_tree_key` int DEFAULT NULL COMMENT '数值类型的tree索引',
  `str_hash_key` varchar(32) DEFAULT NULL COMMENT '字符串hash索引',
  `str_tree_key` varchar(32) DEFAULT NULL COMMENT '字符串tree索引',
  `time_hash_key` datetime DEFAULT NULL COMMENT '日期hash索引',
  `time_tree_key` datetime DEFAULT NULL COMMENT '日期tree索引',
  `unite_hash_1` varchar(32) DEFAULT NULL COMMENT '联合索引',
  `unite_hash_2` varchar(32) DEFAULT NULL COMMENT '联合索引',
  `not_index_str` varchar(255) DEFAULT NULL COMMENT '没有索引',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `index_int_hash_key` (`int_hash_key`),
  UNIQUE KEY `index_int_tree_key` (`int_tree_key`) USING BTREE,
  UNIQUE KEY `inde_str_hash_key` (`str_hash_key`),
  UNIQUE KEY `inde_str_tree_key` (`str_tree_key`) USING BTREE,
  UNIQUE KEY `inde_unite` (`unite_hash_1`,`unite_hash_2`) USING BTREE,
  KEY `inde_time_hash_key` (`time_hash_key`) USING BTREE,
  KEY `inde_time_tree_key` (`time_tree_key`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

添加索引语法一

• CREATE INDEX 不能创建主键索引。

-- 完整语法
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] 

-- 示例
CREATE UNIQUE INDEX index_int_hash_key USING BTREE ON index_test(`int_tree_key`);

添加索引语法二

-- 完整语法-添加索引
ALTER TABLE tbl_name ADD {FULLTEXT | SPATIAL | PRIMARY} [INDEX | KEY] [index_name] (key_part,...) [index_option]
-- 完整语法-添加外键
ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

-- 示例-添加主键索引
ALTER TABLE tbl_name ADD PRIMARY KEY (`id`);
-- 示例-添加外键索引
ALTER TABLE tbl_name_child ADD FOREIGN KEY fk_index_name(col_name) REFERENCES tbl_name_father (id);
-- 示例-添加主键索引
ALTER TABLE index_test ADD UNIQUE index_test(`int_tree_key`);

删除索引

-- 删除普通索引
DROP INDEX {index_name} ON {table_name};
-- 删除普通索引
ALTER TABLE {table_name} DROP INDEX {index_name};
-- 删除主键索引
ALTER TABLE {index_name} DROP PRIMARY KEY;

聚簇索引

• 聚簇索引
  • 聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。
  • 优点：
    • 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
    • 聚簇索引对于主键的排序查找和范围查找速度非常快。
  • 缺点：
    • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
    • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
    • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
• 非聚簇索引
  • 非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录而是主键值。首先通过辅助索引找到主键值，然后到主键索引树中通过主键值找到数据行。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

全文索引

​ InnoDB和MyISAM引擎表支持全文索引，全文索引只能加在 CHAR 、VARCHA、TEXT 类型列。MySQL5.7版本提供了一个内置的ngram解析器，支持中文、日文、韩文（MeCab日文解析器可以单独安装）。

创建表

DROP TABLE IF EXISTS `opening_lines`;
CREATE TABLE `opening_lines` (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	opening_line TEXT ( 500 ),
	author VARCHAR ( 200 ),
	title VARCHAR ( 200 ),
	FULLTEXT idx ( opening_line ) WITH PARSER ngram 
) ENGINE = INNODB;

-- 单独创建索引
ALTER TABLE opening_lines ADD FULLTEXT INDEX ft_index (author) WITH PARSER ngram;
-- 单独创建索引
CREATE FULLTEXT INDEX ft_index ON opening_lines (title) WITH PARSER ngram;

插入数据

INSERT INTO opening_lines(`opening_line`,`author`,`title`) VALUES ('床前明月光，疑是地上霜','李白','唐·李白');
INSERT INTO opening_lines(`opening_line`,`author`,`title`) VALUES ('志不立，天下无可成之事','王阳明','理智名言');

搜索匹配

-- 1条数据
SELECT * FROM opening_lines WHERE MATCH(opening_line) AGAINST('志不立');
-- 0条数据
SELECT * FROM opening_lines WHERE (MATCH(opening_line) AGAINST('-志不立' IN boolean MODE));
-- 1条数据
SELECT * FROM opening_lines WHERE (MATCH(opening_line) AGAINST('+志不立' IN boolean MODE));

注意：中文内容需要指定ngram分析器，不然可能匹配不到数据（默认解析器对中文及其不友好）。

建议：如果数据量比较大，并且需要复杂搜索，搜索结果高亮，自定义过滤，自定义停用词，近义词等复杂需求时建议使用ES替代。

空间索引

讲空间索引之前，我们需要先了解一下空间数据类型，MySQL支持的空间数据类型有 point、linestring、polygon、geometry 以及它们的集合形式 multipoint、multilinestring、multipolygon、geometrycollectionn。这些类型支持一些特殊的空间集合函数，可以应用在地图、绘画、用户行为监控等场景。对于InnoDB和MyISAM引擎的表，MySQL可以使用创建普通索引的语法创建空间索引。空间索引的列必须声明为NOT NULL。

创建表

DROP TABLE IF EXISTS `space`;
CREATE TABLE `space` (
  `id` int NOT NULL,
  `geometry` geometry DEFAULT NULL COMMENT '几何',
  `point` point DEFAULT NULL COMMENT '点',
  `linestring` linestring DEFAULT NULL COMMENT '线',
  `polygon` polygon DEFAULT NULL COMMENT '多边形',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO `space`(id,`linestring`) VALUES (1,ST_GeomFromText('linestring(1 1,1 2)'));
INSERT INTO `space`(id,`linestring`) VALUES (2,ST_GeomFromText('linestring(2 1,2 5)'));
INSERT INTO `space`(id,`linestring`) VALUES (3,ST_GeomFromText('linestring(3 3,3 6)'));

数据应用

-- MBRContains函数：查询与参数边界重合的记录。
SELECT * FROM space WHERE MBRContains(ST_GeomFromText('linestring(1 1,1 2)'),`linestring`);

创建空间索引

-- 创建索引-1
CREATE SPATIAL INDEX {index_name} ON {index_name} (col_name);
-- 创建索引-2
ALTER TABLE {index_name} ADD SPATIAL INDEX(col_name);

应用

建立原则

• 建议创建索引的列

  • 当列在 WHERE 子句中经常使用时，建议添加索引。
  • 当列值重复率比较低且比较有规律时，建议添加索引（例如：身份证、手机号、订单编号、优惠券码、创建时间）。
  • 当列经常用来排序时，建议添加索引（索引已经排序，这样在查询时可以利用索引的排序，加快排序查询时间）。

• 不建议创建索引的列

  • 当列很少参与到查询中，不建议加索引（因为有无索引都不会提高查询速度，反而增加了索引维护和存储的开销）。
  • 当列值重复率比较高时，不建议加索引（例如：性别、年龄、民族）。
  • 当列的数据类型为 text 、image、bit 等数据类型时，不建议加索引（这些列一般数据量比较大）。
  • 当列的修改操作远远大于检索性能时，不建议增加索引（增加索引对数据编辑会有性能损耗）。

索引失效

• like：当 % 在后索引生效，在前索引失效。
• 范围查询：当命中的数据范围较小或者需要操作的数据较小时应用索引。
• 负向查询：除主键列外，其它索引列的负向查询都不走索引。
• ORDER BY：对主键索引排序会用到索引，其他的索引失效（覆盖索引除外）。
• 类型不一致：隐式类型转换会引发索引失效，在进行比较是要确保类型正确，严重的情况会直接报错。
• 联合索引：违背最左匹配原则，会导致索引失效。
• OR：索引列与非索引列进行 OR 会造成索引失效。
• Hash索引：Hash类型的索引不支持范围查询，如果使用范围查询则导致索引失效（注意：InnoDB和MyISAM存储引擎不支持Hahs索引）。