-
索引的分类及含义
-
索引创建的方式
-
在已经存在的表上创建索引
-
删除索引
索引的分类及含义:
索引的含义和特点:索引是一个单独的、存储在磁盘上的数据库结构,包含着对数据表里所有记录的引用指针。 # 通俗意思就是有些列查的数据量很大查询比较耗时,索引能减少耗时
特点: mysql 有2种存储引擎,btree和hash , 我们常用的引擎MyISAM和InnoDB存储引擎只支持btree;MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
索引的优缺点 mysql索引 索引优缺点 - 丶焦人 - 博客园
分类及含义:
1.普通索引:是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值。
2.唯一索引:要求索引列的值必须唯一,但允许有空值。组合索引,则列值的组合必须唯一。主键索引是一种特殊的唯一索引,不允许有空值。
3.单列索引:即一个索引只包含单个列,一个表可以有多个单列索引
4.组合索引:是指在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合。
5.全文索引:类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。
5.1:支持的类型:全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
5.2:支持的表的引擎:MySQL中只有MyISAM存储引擎支持全文索引
6.空间索引:空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING和POLYGON
6.1:创建的条件:建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MyISAM的表中创建。
索引创建的方式
1.在创建表的定义语句CREATE TABLE中指定索引列
创建表的时候创建索引 例子:
1.普通索引:在book表中的year_publication字段上建立普通索引
CREATE TABLE book
(
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication) # 最基本的索引,没有唯一性限制,只是加快查询速度
);
>show create table book; # 查看表结构
显示包含: KEY `year_publication` (`year_publication`) # 表示索引创建成功
explain(执行计划) : 能模拟优化器执行SQL查询语句,并不会去真正的执行这条SQL,从而知道MySQL 是如何处理你的SQL语句的。 可用来分析你的查询语句或是表结构的性能瓶颈
返回参数查看:https://blog.csdn.net/weixin_41244495/article/details/120687708
2.创建唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
优点:减少索引列的查询时间
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
# UniqIdx 索引名字 id 是创建索引的列
3. 创建单列索引: 在表中一个字段创建,上面2中都是单列索引
CREATE TABLE t2
(
id INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);
# 20 是索引长度
4. 创建组合索引:
CREATE TABLE t3
(
id INT NOT NULL,
name VARCHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, aga)
);
# MultiIdx 组合索引的name, 可以理解成是一个集合name
explain select * from t3 where id = 1 and name="job"; # id条件值不在就不会执行组合索引
explain select * from t3 where id = 1;
5.创建全文索引:FULLTEXT全文索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引,并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行,不支持局部(前缀)索引
CREATE TABLE t4
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;
#全文索引非常适合于大型数据集,对于小的数据集,它的用处比较小。
6. 创建空间索引:空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。
CREATE TABLE t5
(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
)ENGINE=MyISAM;
2.使用ALTER TABLE语句在存在的表上创建索引
语法:
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY]
[index_name] (col_name[length],…) [ASC | DESC]
查看指定表创建的索引:show index from table_name;
ALTER TABLE book ADD INDEX BkNameIdx ( bookname(30) );
SHOW INDEX FROM book;
其他的都差不多
3.或者使用CREATE INDEX语句在已存在的表上添加索引
后面再写
参考书籍《mysql 8 从入门到精通》