数据库索引详解-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43739146/article/details/109303701

作用

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接 , 实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
全文检索字段进行搜索优化.

说白了就是有索引之前查找数据是遍历，添加索引后就变成了定位。

分类

主键索引（primary key）：不可重复（唯一标识），只能有一个主键。
唯一索引（unique key）：避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引。
常规索引（key/index）：默认的，index。用key关键字设置。
全文索引（fulltext）：特定数据库引擎下才有（MySQL从5.6版本开始，InnoDB也支持全文索引）

主键索引

特点：

最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

唯一索引

作用：避免同一个表中某数据列中的值重复
与主键索引的区别

主键索引只能有一个
唯一索引可能有多个

使用语法：

create table '表名'(
    '字段名' int(11) auto_increment primarykey,
    '字段名' varchar(32) not null unique
    -- 或 unique key `字段名` (`字段名`)
)

常规索引

作用 : 快速定位特定数据

注意 :

index 和 key 关键字都可以设置常规索引
应加在查询找条件的字段
不宜添加太多常规索引,影响数据的插入,删除和修改操作

使用语法：

-- 创建表时添加
create table 表名(
      --创建语句
      index|key 索引名(字段名,字段名,...);
)
-- 创建表后添加
alter table 表名 add index 索引名(字段名,...);

全文索引

作用 : 快速定位特定数据

注意 :

只能用于MyISAM类型的数据表；
只能用于CHAR , VARCHAR , TEXT数据列类型；
适合大型数据集。
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;

测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
使用语法（全部索引）：

#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC])
                );
#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                                                        
#删除索引：
DROP INDEX 索引名 ON 表名字;
#删除主键索引: 
ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: 
SHOW INDEX FROM student;

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
 
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
 
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');