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索引)。