​

文章目录

零、前言

今天是学习 SQL 打卡的第 11 天，每天我会提供一篇文章供群成员阅读。

希望大家先自己思考，如果实在没有想法，再看下面的解题思路，自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡，今天的任务就算完成了，养成每天学习打卡的好习惯。

虚竹哥会组织大家一起学习同一篇文章，所以有什么问题都可以在群里问，群里的小伙伴可以迅速地帮到你，一个人可以走得很快，一群人可以走得很远，有一起学习交流的战友，是多么幸运的事情。

我的学习策略很简单，题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍，那意味着 SQL 已经筑基成功了。后面的进阶学习，可以继续跟着我，一起走向架构师之路。

今天的学习内容是：SQL进阶-索引的创建、删除

一、练习题目

题目链接

难度

​​SQL进阶-索引的创建、删除：SQL121 创建索引​​

★★☆☆☆

​​SQL进阶-索引的创建、删除：SQL122 删除索引​​

★★☆☆☆

二、SQL思路

SQL进阶-索引的创建、删除：SQL121 创建索引

【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_sql

初始化数据

drop table if exists examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;

解法

要求处理：

  • 在examination_info表创建以下索引：
  • 在duration列创建普通索引idx_duration
  • 在exam_id列创建唯一性索引uniq_idx_exam_id
  • 在tag列创建全文索引full_idx_tag。

分析：

  • 使用CREATE INDEX创建索引的语法:

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

特别说明：
[ UNIQUE | FULLTEXT | SPATIAL]:索引的类型，分别表示唯一索引、全文索引和空间索引，创建普通索引时，索引类型可以省略。
column_name：需要创建的索引列，可以是数据表中的单个列，也可以是数据表中的多个列（组合索引）。
length：创建索引时，为索引指定的长度，参数可以省略。需要注意的是，只有字符串类型的字段才能为索引指定长度。
[ASC | DESC]：指定以升序或者降序的方式来存储索引值，参数可省略。
  • 使用ALTER TABLE创建索引的语法:

ALTER TABLE table_name
ADD [PRIMARY | UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (column_name [length])
[ASC | DESC]

  特别说明：
  [PRIMARY | UNIQUE | FULLTEXT | SPATIAL]:索引的类型，分别表示主键索引、唯一索引、全文索引和空间索引，跟上面介绍的一样。
  MySQL支持使用 ALTER TABLE语句创建主键索引，但不支持使用 CREATE INDEX语句创建主键索引。
  [INDEX | KEY]：作用基本相同，指定在数据表中创建索引。
  • 普通索引是所有索引类型中最基本的索引类型，没有唯一性等限制，能够加快数据的检索效率。

使用CREATE INDEX 创建普通索引：

CREATE INDEX idx_duration ON examination_info(duration);

使用 ALTER TABLE 创建普通索引：

ALTER TABLE examination_info ADD INDEX idx_duration(duration);

【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_字段_02

  • 创建唯一索引的列值必须唯一，但是允许值为空。如果创建的唯一索引中包含多个字段，也就是组合索引，则索引中包含的多个字段的值的组合必须唯一。
    使用CREATE INDEX 创建唯一索引：
CREATE UNIQUE INDEX  uniq_idx_exam_id ON examination_info(exam_id);

使用 ALTER TABLE 创建唯一索引：

ALTER TABLE examination_info ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);

创建表时，设置了唯一索引

【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_数据库_03

【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_数据库_04


【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_数据_05

注意： 同样的索引不要重复创建，这里只是为了示范如何创建唯一索引。重复的索引要删除。

drop index uniq_idx_exam_id on examination_info;

  • 创建全文索引时，对列的数据类型有一定的限制，只能为定义为CHAR、VARCHAR和TEXT数据类型的列创建全文索引，全文索引不支持对列的局部进行索引。
  • 注意：在MySQL 5.7之前的版本中，只有MyISAM存储类型的数据表支持全文索引。在MySQL 5.7的部分版本和MySQL 8.x版本中，InnoDB存储引擎也支持创建全文索引。

使用CREATE INDEX 创建全文索引：

CREATE FULLTEXT INDEX  full_idx_tag ON examination_info(tag);

使用 ALTER TABLE 创建全文索引：

ALTER TABLE examination_info ADD FULLTEXT INDEX full_idx_tag(tag);

【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_字段_06

扩展

  • 创建表时，也是能直接添加索引的。语法如下：

CREATE TABLE table_name(
column_name1 data_type1 ,
column_name2 data_type2,
…
[PRIMARY | UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (column_name [length])
[ASC | DESC]
)

  • 空间索引：MySQL中支持在GEOMETRY数据类型（地理空间类型）的字段上创建空间索引。语法如下：

coordinate字段是GEOMETRY数据类型；
第一种写法：CREATE SPATIAL INDEX spa_idx_coordinate ON table_name(coordinate);
第二种写法：ALTER TABLE table_name ADD SPATIAL INDEX spa_idx_coordinate(coordinate);

  • 组合索引：在创建的索引中，包含数据表中的多个字段或列。MySQL中，同样支持在一张数据表中创建多个组合索引。在使用组合索引查询数据时，MySQL支持最左匹配原则。
  • 主键索引：特殊类型的唯一索引，与唯一索引不同的是，主键索引不仅具有唯一性，而且不能为空，而唯一索引中的列的数据可能为空。通常在创建表时，设置表主键字段，就会给字段添加主键索引了。
  • 友情提示：索引虽好，但不要贪多噢~

SQL进阶-索引的创建、删除：SQL122 删除索引

【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_字段_07

初始化数据

drop table if exists examination_info;
CREATE TABLE IF NOT EXISTS examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长(分钟数)',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_bin;
CREATE INDEX idx_duration ON examination_info(duration);
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
ALTER TABLE examination_info ADD FULLTEXT full_idx_tag(tag);

解法

要求处理：

  • 删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag。

分析：

  • mysql支持ALTER TABLE语句和DROP INDEX语句删除索引。
  • ALTER TABLE的语法：

ALTER TABLE table_name
DROP INDEX index_name

  • DROP INDEX的语法 ：

DROP INDEX index_name
ON table_name

删除前，索引如下：

【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_字段_08

  • 删除examination_info表上的唯一索引uniq_idx_exam_id
ALTER TABLE examination_info 
DROP INDEX uniq_idx_exam_id;
  • 删除examination_info表上的全文索引full_idx_tag。
DROP INDEX full_idx_tag
ON examination_info;

删除后的效果

【第11天】SQL进阶-索引的创建、删除（SQL 小虚竹)_数据库_09

三、扩展

索引的优点与缺点

优点

  • 所有字段类型都能添加索引；
  • 可以对单个列添加索引，也可以对多个列添加组合索引；
  • 提高查询效率；
  • 提高数据分组与排序的。

缺点

  • 索引会占用存储空间，如果大量使用索引，会导致索引文件占用大量的磁盘空间。
  • 索引的创建和维护是会花费时间的，如果数据量越来越多，花费的时间会越来越长。
  • 对表的数据进行增加，删除和修改操作时，会引起索引的动态维护，会花费一定的维护时间。

索引的创建原则

创建的索引并不是越多越好，要遵循以下几个原则：

  • 尽量使用小的数据类型的列创建索引；
  • 尽量使用简单的数据类型的列创建索引；
  • 尽量不要在NULL值字段上创建索引。

索引的使用场景

适合创建索引的场景

  • 必须为数据表中的主键和外键添加索引。
  • 数据表中的数据达到一定量级时，应当为数据表适当添加索引。
  • 与其他表进行关联的字段，并且经常进行关联查询时，应当为连接字段创建索引。
  • 作为WHERE子句的条件判断字段，并且经常用来进行相等比较操作的字段，应当添加索引。
  • 作为ORDER BY语句的字段，并且经常用来执行排序操作的字段，应当添加索引。
  • 作为搜索一定范围内的字段，并且经常用来执行查询操作，应当添加索引。

不适合创建索引的场景

  • 在查询数据时很少使用的列或字段不适合创建索引。
  • 某个字段包含的数据很少，如标识用户性别的字段，不适合创建索引。
  • 大数据类型的字段，如定义为TEXT、BLOB和BIT等数据类型的字段，不适合创建索引。
  • 当在数据表中修改数据的性能远大于查询数据的性能时，不适合创建索引。
  • 查询数据时不会作为WHERE条件中的字段，并且不会作为ORDER BY语句和GROUP BY语句的字段，不适合创建索引。

索引会失效的场景

虚竹哥教你一句功法口诀：
模 型 数 或 运 最 快

  • 模：like 模糊全匹配（like ‘%内容%’），会导致全表扫描；like模糊前匹配（like ‘%内容’），会导致全表扫描。
  • 型：代表数据类型。例如对字符串name字段加的索引，where条件写name=1，索引会失效。
  • 数：是函数的意思。对索引的字段使用内部函数，索引也会失效。这种情况下应该建立基于函数的索引。

SELECT * FROM ​​user​​​ WHERE DATE(create_time) = ‘2020-09-03’;
这里使用DATE函数

  • 或： 在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描；
  • 运： 对索引的列进行运算，索引失效,例如：WHERE age+1=8;
  • 最：组合索引，查询时的条件列不是联合索引中的第一个列，索引失效（索引的最左原则）。
  • 快： 查询数量是超过表的一部分,mysql30%,oracle 20%（这个数据可能不准确，不是官方说明，仅供参考），导致索引失效；

我是虚竹哥，我们明天见~