MySQL 之索引
本文标识 : MQ0022
本文编辑 : 长安月下赏美人儿
编程工具 : MySQL、DBeaver
阅读时长 : 9分钟
前言
之所以现在才来分享索引，完全是因为小编的过错。在一开始接触到 MySQL 学习的时候，看到了索引的相关内容。但着实吓人了些许，小编就这样怂怂的被吓跑了，想着先分享一些容易操作的，再来进行系统化的梳理。不然的话，很容易在一开始，就被错综复杂的基础信息吓跑，且还不明白说得是啥。
什么是索引
通俗的讲，索引相当于数据表的目录。譬如，咱们买到一本书，会先看目录，看看都写了啥，都有啥，对啥感兴趣。这是一个道理，拥有了索引，就可以非常快速的找到感兴趣的内容。
建立索引有啥好处
索引的优点是可以提高检索数据的速度，但是会减慢写入速度。毕竟世界上任何事物都具有两面性，哪有完美的事儿！此岸永远是残缺的，否则彼岸就要坍塌。
索引选取原则
不是每个人都适合当皇帝！
欲戴皇冠，必承其重！
且，索引必须“短而精，勿过度！”
1、索引选取类型
- 占用空间较少的数据型优选
- 简单的整型数据优于字符型
- 避开神坑 NULL 以及它的兄弟 NOT NULL
2、索引选取字段
- 数据表的主键、外键
- 常与其他数据表进行连接的字段
- 常出现在 WHERE 子句中的字段
- 常用于排序的字段
3、索引不适用场景
- 数据表中数据未超过 300 条
- 查询中很少使用到的数据列
- 数据值很少的列
- 数据类型为 TEXT 、IMAGE、BIT 的数据
- 字段经常性修改，且很少用于检索数据
- 不常出现在 WHERE 子句及排序的字段
索引的类型
单列索引
1、普通索引（加速查询，无限制，最常用）
#基本语法
CREATE INDEX index_name ON test_table(username(length)); 注意：如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length 。
- 创建添加索引
(1)第一种情况：在创建数据表的同时创建索引
CREATE TABLE test_table (
id int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
name VARCHAR(50) NOT NULL COMMENT '姓名',
email VARCHAR(72) NOT NULL COMMENT '邮箱',
message text DEFAULT NULL COMMENT '个人信息',
INDEX index_name (name) COMMENT '索引name'
) COMMENT = '索引测试表';（2）第二种情况：在已经存在的数据表上创造索引
CREATE INDEX index_name ON test1.test_table1(name);
#注意：创建索引时数据类型为 BLOB 或 TEXT，必须指定length。
CREATE INDEX index_name ON test1.test_table2(message(200));（3）第三种情况：修改表结构添加索引
ALTER TABLE test1.test_table3 ADD INDEX index_name(name);- 查看索引
SHOW INDEX FROM test1.test_table;数据结果：
- 删除索引
DROP INDEX index_name ON test1.test_table;
#或
ALTER TABLE test1.test_table DROP INDEX index_name;输入删除表索引指令数据结果：
2、唯一索引（加速查询，列值唯一，可为 NULL）
#基本语法
CREATE UNIQUE INDEX index_name ON test_table(username(length)) - 创建和添加唯一索引
(1)第一种情况：在创建数据表的同时创建唯一索引
CREATE TABLE test_table (
id int(20) NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
name VARCHAR(50) NOT NULL COMMENT '姓名',
UNIQUE index_name (name) COMMENT '索引name'
) COMMENT = '索引测试表';（2）第二种情况：在已经存在的数据表上创造唯一索引
CREATE UNIQUE INDEX index_name ON test1.test_table1(name);（3）第三种情况：修改表结构添加索引
ALTER TABLE test1.test_table3 ADD UNIQUE index_name(name);3、主键索引（唯一索引的特例，不允许有空值）
注：一个数据表只有一个主键，且会在创建数据表的时候直接创建主键索引
CREATE TABLE table2(
id INT NOT NULL,
name CHAR(10),
PRIMARY KEY(id)
);
输入查看表索引指令数据结果：
组合索引
通俗的讲，就是在多个字段上创建一个索引
(1)第一种情况：创建数据表的同时创建组合索引
CREATE TABLE table3(
id INT NOT NULL,
name CHAR(20) NOT NULL,
age INT NOT NULL,
message VARCHAR(255),
INDEX index_name(id,name,age)
);输入查看表索引指令数据结果：
（2）第二种情况：在已经存在的数据表上创建组合索引
ALTER TABLE table4 ADD INDEX index_name(id, age, message);输入查看表索引指令数据结果：
全文索引
用于全文搜索，只有 MyISAM 存储引擎支持全文索引，且只为 CHAR、VARCHAR 和 TEXT 列服务。索引总是对整个列进行，不支持前缀索引。
- 创建或添加全文索引
(1)第一种情况：创建数据表的同时创建全文索引
CREATE TABLE table5(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX index_name(info)
)ENGINE=MyISAM;（2）第二种情况：在已经存在的数据表上创建全文索引
ALTER TABLE table_name ADD FULLTEXT (column);- 全文搜索实例
在使用全文搜索时，需要借助 MATCH 函数，搜索的关键字默认至少要4个字符，如果搜索的关键字太短就会被忽略掉。
SELECT * FROM table5 WHERE MATCH(info) AGAINST('Zhangsan');数据结果：
空间索引
小编查询了很多资料，也是对这个应用感到困惑。查看了大量的习题，也鲜少遇到相关信息。但还是想放在这里，大家一起简单的学习一下，这样子，今后遇到相关的问题至少有一个印象。
(1)第一种情况：创建数据表的同时创建空间索引
CREATE TABLE table6(
hahaha GEOMETRY NOT NULL,
SPATIAL INDEX index_name(hahaha)
) ENGINE = MyISAM;（2）第二种情况：在已经存在的数据表上创建空间索引
ALTER TABLE table6 ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY] [索引名] (索引字段名)[ASC|DESC];写在后面，关于索引，是一个非常重要且非常有趣的版块，小编目前还在持续挖掘这份快乐，希望你也是！