前言
关于索引,是一个非常重要且非常有趣的版块。在一开始接触到 MySQL 学习的时候,看到了索引的相关内容。但着实吓人了些许,小编就这样怂怂的被吓跑了,想着先分享一些容易操作的,再来进行系统化的梳理。不然的话,很容易在一开始,就被错综复杂的基础信息吓跑,且还不明白说得是啥。
什么是索引
通俗的讲,索引相当于数据表的目录。譬如,咱们买到一本书,会先看目录,看看都写了啥,都有啥,对啥感兴趣。这是一个道理,拥有了索引,就可以非常快速的找到感兴趣的内容。
建立索引有啥好处
索引的优点是可以提高检索数据的速度,但是会减慢写入速度。毕竟世界上任何事物都具有两面性,哪有完美的事儿!此岸永远是残缺的,否则彼岸就要坍塌。
索引选取原则
占用空间较少的数据型优选 简单的整型数据优于字符型 避开神坑 NULL 以及它的兄弟 NOT NULL
数据表的主键、外键 常与其他数据表进行连接的字段 常出现在 WHERE 子句中的字段 常用于排序的字段
3、索引不适用场景
数据表中数据未超过 300 条
查询中很少使用到的数据列
数据值很少的列
数据类型为 TEXT 、IMAGE、BIT 的数据
字段经常性修改,且很少用于检索数据
不常出现在 WHERE 子句及排序的字段
索引的类型
单列索引
1、普通索引(加速查询,无限制,最常用)
#基本语法 CREATE INDEX index_name ON test_table(username(length));
创建添加索引
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))
创建和添加唯一索引
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);
输入查看表索引指令数据结果:
全文索引
创建或添加全文索引
(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];
发表评论