通过几篇文章我们来讨论下索引相关的内容,本文我们先来简单的了解下索引的相关概念。 数据库的版本是 8.0.2
索引是什么?
现在我们数据库中有一张表,表中的数据有500W条,先在我们没有使用索引的情况下根据name
来查询
select * from user_innodb where name='波波'
我们看到的查询时间是2.214秒。那如果我们给这张表的name
字段加上索引会怎么样呢?
ALTER TABLE `user_innodb`
ADD INDEX `idx_user_name` (`name`) USING BTREE ;
创建索引会比较耗时间,耐心等待会~ 创建成功后我们再次执行相同的查询语句效果如下
通过对比发现,有索引的请求比没有索引的情况,效果相差了几十倍上百倍。通过这个案例大家应该可以非常直观地感受到,索引对于数据检索的性能改善是非常大的。
那么索引到底是什么呢?为什么可以对我们的查询产生这么大的影响?创建索引的时候发生了什么事情?
1.索引的定义
维基百科对数据库索引的定义:
数据库索引,是数据库管理系统(DBMS)中一个
排序
的数据结构
,以协助快速查询、更新数据库表中数据。
数据是以文件的形式存放在磁盘上面的,每一行数据都有它的磁盘地址。如果没有索引的话,我们要从500万行数据里面检索一条数据,只能依次遍历这张表的全部数据,直到找到这条数据。
但是我们有了索引之后,只需要在索引里面去检索这条数据就行了,因为它是一种特殊的专门用来快速检索的数据结构
,我们找到数据存放的磁盘地址以后,就可以拿到数据了。
2.索引的类型
在MySQL的所有中支持三种索引类型
类型 | 描述 |
---|---|
Normal | 普通索引 |
Unique | 唯一索引 |
Fulltext | 全文索引 |
Normal
也叫非唯一索引,是最普通的索引,没有任何的限制
# 直接创建索引
CREATE INDEX index_name ON table(column(length))
# 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
# 创建表的时候同时创建索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(length))
)
# 删除索引
DROP INDEX index_name ON table
Unique
唯一索引要求键值不能重复。另外需要注意的是,主键索引是一种特殊的唯一索引,它还多了一个限制条件,要求键值不能为空。主键索引用primary key创建。
#创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
#修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
#创建表的时候直接指定
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE indexName (title(length))
Fulltext
MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
#创建表的适合添加全文索引
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`time` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);
#修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
#直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)
~好了通过本文的介绍,相信大家对于索引应该有了个基本的认知,那么我们下篇文章就来给大家分析下索引的具体的存储结构!
最后感觉波波给大家分享的文章对你有帮助麻烦点赞加关注哦~ ^ _ ^