MySQL-索引的创建、删除与设计(CREATE INDEX/DROP INDEX)

UndefinedException

已于 2022-07-04 16:31:26 修改

阅读量957

点赞数 2

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-07-03 15:34:55 首次发布

本文链接：https://blog.csdn.net/weixin_62427168/article/details/125584093

版权

MySQL 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

一、索引分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。从功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。

按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。

按照 作用字段个数 进行划分，分成单列索引和联合索引。

1.普通索引：可以创建在任何数据类型中。例如student表的name字段。

2.唯一性索引：限制该索引的值必须是唯一的，但允许有空值、多个唯一性索引。

3.主键索引：一种特殊的唯一性索引，是非空的。一个表只能有一个。

4.单列索引：在表中单个字段上创建索引，可以有多个单列索引。

5.多列索引：在表的多个字段组合上创建索引，但只有在查询条件中使用了这些字段中的第一个字段时才会被使用。例如对id，name，gender创建多列索引，查询条件必须包含id字段时该索引才会使用。

6.全文索引：目前搜索引擎使用的技术，能够分析出文本文字中关键词的频率和重要性，智能筛选出结果。查询数据量较大的字符串型字段时可以提高查询速度。只能创建在char、varchar、TEXT类型字段上。

7.空间索引：只能建立在空间数据类型上。

小结：不同的存储引擎支持的索引类型也不一样

InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive ：不支持 B-tree、Hash、Full-text 等索引；

二、索引的创建

2.1 建表时

1.隐式地创建索引，声明有主键约束、唯一性约束、外键约束的字段上会自动添加索引。

2.显式创建索引：

CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]

- UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；

-INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引；

       -index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；

-col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；         -length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；

        -ASC 或 DESC 指定升序或者降序的索引值存储。

show index from xxx #查看某表中的索引

#1.普通索引
CREATE TABLE book(
    book_id INT ,
    book_name VARCHAR(100),
    authors VARCHAR(100),
    info VARCHAR(100) ,
    comment VARCHAR(100),
    year_publication YEAR,

    INDEX idx_bname(book_name)#创建普通索引
);

#2.唯一索引
CREATE TABLE test1(
    id INT NOT NULL,
    name varchar(30) NOT NULL,

    UNIQUE INDEX uk_idx_id(id)#创建唯一索引
);

#3.主键索引（随表一起建索引）
CREATE TABLE student (
    id INT(10) UNSIGNED AUTO_INCREMENT ,
    student_no VARCHAR(200),
    student_name VARCHAR(200),
    PRIMARY KEY(id) #主键
);

    ALTER TABLE student
    drop PRIMARY KEY ;  #删除主键索引

#4.组合索引
CREATE TABLE test3(
    id INT(11) NOT NULL,
    name CHAR(30) NOT NULL,
    age INT(11) NOT NULL,
    info VARCHAR(255),

    INDEX multi_idx(id ASC,name,age DESC) #id，name，age三列: id升序，age降序
);

#5.全文索引
CREATE TABLE test4(
   id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR (200),
   body TEXT,

   FULLTEXT INDEX futxt_idx(tite(50),body) #给title和body字段添加全文索引，限定索引长度为50
);

#6.空间索引
CREATE TABLE test5(
    geo GEOMETRY NOT NULL,
    SPATIAL INDEX spa_idx_geo(geo) #在空间类型为GEOMETRY的字段上创建空间索引，必须非空

) ENGINE=MyISAM;

注：对于全文索引，查询时应用MATCH关键字：

SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;
#推荐下面形式，效率高
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

2.2 建表后

在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

#1. 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的基本语法如下：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]


#2. 使用CREATE INDEX创建索引 CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，
CREATE INDEX被映射到一个ALTER TABLE语句上，基本语法结构为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]

ALTER TABLE books ADD INDEX idx_cmt(comment)
CREATE INDEX idx_cmt ON books(comment)

三、索引的删除

提示：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

1. 使用ALTER TABLE删除索引

ALTER TABLE table_name DROP INDEX index_name;

2. 使用DROP INDEX语句删除索引

DROP INDEX index_name ON table_name;

四、索引可见性的修改

在MySQL 5.7版本及之前，只能通过显式的方式删除索引。此时，如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大，或者数据表本身比较大，这种操作就会消耗系统过多的资源，操作成本非常高。

从MySQL 8.x开始支持 隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使 查询优化器不再使用这个索引（即使使用force index（强制使用索引），优化器也不会使用该索引），确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。

#1. 创建表时直接创建
CREATE TABLE tablename(
propname1 type1[CONSTRAINT1],
propname2 type2[CONSTRAINT2],
……
propnamen typen,

INDEX [indexname](propname1 [(length)]) INVISIBLE
);

#2. 在已经存在的表上创建
CREATE INDEX indexname
ON tablename(propname[(length)]) INVISIBLE;

#3.通过ALTER TABLE语句创建
ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;

#4.切换可见状态
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引

五、索引的设计原则

5.1 哪些情况适合添加索引

1. 字段数值具有唯一性的限制。如id

2. 频繁作为 WHERE 查询条件的字段

3. 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立 组合索引 。

4.UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT 字段需要创建索引

6. 多表 JOIN 连接操作时，创建索引注意事项：

首先， 连接表的数量尽量不要超过 3 张 ，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。如 id

7.使用类型小的列创建索引

类型小指的就是表示数据范围的大小，越小效率越高，索引占用的空间也越小。

8.使用字符串前缀创建索引

创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引。

索引长度的选择应根据选择度确定：

count(distinct left(列名, 索引长度))/count(*) from table #一般20就够了

9. 区分度高(散列性高)的列适合作为索引

区分度高指的就是不重复数据多，可以用下面公式判断

select count(distinct a)/count(*) from table #超过33%就不错

10. 使用最频繁的列放到联合索引的左侧

清楚联合索引就无需多言。

11. 在多个字段都要创建索引的情况下，联合索引优于单值索引

索引数目也不宜过多，一般一个表不超过6个。

因为索引要占用磁盘空间。

由于表中数据更改的同时，索引也会调整，所以会影响插入、删除、更新的性能。

会增加优化器生成执行计划的时间。

5.2 哪些情况不适合创建索引

1. 在where中使用不到的字段（包括ORDER BY/GROUP BY），不要设置索引。

2. 数据量小的表最好不要使用索引

3. 有大量重复数据的列上不要建立索引

4. 避免对经常更新的表创建过多的索引

5. 不建议用无序的值作为索引，如身份证、UUID

6. 删除不再使用或者很少使用的索引

7. 不要定义冗余或重复的索引

UndefinedException

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL-索引的创建、删除与设计(CREATE INDEX/DROP INDEX)

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。按照物理实现方式，索引可以分为 2 种：聚簇索引和非聚簇索引。按照作用字段个数进行划分，分成单列索引和联合索引。 1.普通索引：可以创建在任何数据类型中。例如student表的name字段。 2.唯一性索引：限制该索引的值必须
复制链接

扫一扫