摘要
在MySql数据库中,索引适用于加快数据检索操作的数据结构,
索引帮我数据管理系统快速定位到数据所在行,而无需扫描整个表。
前言
现一张sutdent表,有800万条数据。
CREATE TABLE students(
id INT AUTO_INCREMENT, //id自增
name VARCAHR(255) NOT NULL , //name不为空
age INT NOT NULL, //age不为空,且大于0
CHECK (age>=0)
)
此时students.ibd
(student表在本机文件的大小)数据文件的大小为523M。
现在有一个需求:查询age=50万这条数据
-
普通
SELECT
语句SELECT * FROM students WHERE id=500000; 查询时间=4.35秒
对于计算机来说,查询一条数据需要花费4.35秒是完全不能接受的。此时就可以床关键索引来加快数据的检索能力。
-
索引检索
CREATE INDEX age_index on student(age) //创建id属性的索引 SELECT * FROM students WHERE age=50; 查询时间=0.003秒
创建索引后,此时
students.ibd
数据文件的大小为634M,说明索引占用一定量的空间。
但是使用索引后,在不用加内存、修改程序、调整sql的情况下,查询的效率得到了质的飞跃。
MYSQL只对创建了索引的列进行查询时适用索引。
SELECT * FROM students WHERE age=50;
由于age列没有创建索引,所以查询的时间是4.35秒。
查询时间=4.35秒
索引的机制
表格形式的对比如下:
类型 | 速度 | 机制 | 空间 | 局限 |
---|---|---|---|---|
普通检索 | 慢 | 线性扫描每一行数据 | 不占用空间 | - |
索引检索 | 超快 | 数据索引结构为二叉树 | 需要占用空间 | 对DELETE 、UPDATE 、INSERT 会速度有一定的影响 |
普通检索机制
在没有查询没有索引的数据时,数据库系统查询的机制是线性扫描每一行元素
知道满足查询条件为止,查询速度慢。如下如所示:
索引检索机制
现在对id
属性创建索引,数据库系统会形成一个数据结构,比如二叉树、B+树等等,如下如所示:
根据二叉树的搜索,可以快读的找到目标元素。
因此,如果我们比较了3次,实际上覆盖了表的范围:
2
3
=
8
2^3=8
23=8
局限性
虽然索引对于Select语句的查询速度有着质的飞跃,但是对update、insert、delete
速度有一定的影响。
这里以delete操作为例:
当执行了delete操作后,数据的删除导致索引的结构就会发生改变,导致执行速度收到影响,如下如所示:
索引分类
MySql支持多种类型的索引,以下是常用的索引类型:
以下是表格形式的描述:
类型 | 说明 | 适用范围 |
---|---|---|
主键索引(UNIQUE) | 用于唯一标识表中的每一行,本身就是索引。 | 列的数据没有重复 |
普通索引(INDEX) | 用于普通检索操作。 | 列的数据重复 |
全文索引(FULLTEXT) | 用于全文搜索,适用于MyISAM引擎,但是效率较低,开发中不使用,而是使用ElasticSearch。 | 适用于全文搜索的列和引擎。 |
详细说明如下。
主键索引(UNIQUE)
CREATE TABLE students(
id INT PRIMARY KEY AUTO_INCREMENT, //主键自增长
name VARCAHR(255) NOT NULL , //name不为空
age INT NOT NULL, //age不为空,且大于0
CHECK (age>=0)
)
在创建主键时,主键本身就是一种索引,所以在根据id查询数据时,速度很快。
id
是唯一的,同时也是索引,称为unique
索引。
全文索引(FALLTEXT)
用于全文搜索,一段文章的字段上建立索引。
适用于MyISAM引擎,但是效率较低,开发中不使用,而是使用ElasticSearch。
相关操作
查看表的索引
SHOW INDEX FROM [表名] //查看表索引
SHOW INDEX FROM [表名]
DESC [表名] //查看表的描述
添加索引
- 添加唯一索引
CREATE UNIQUE INDEX id_index on student(id); //id列创建索引
- 添加普通索引
CREATE INDEX id_index on student(id);
- 添加主键索引
CREATE TABLE students( id INT PRIMARY KEY, //主键自增长,指定为主键 )
- alter
ALTER TABLE student ADD INDEX id_index (id):
删除索引
-
使用
DROP INDEX
语句删除索引:DROP INDEX id_index ON student;
-
使用
ALTER TABLE
语句删除索引:ALTER TABLE student DROP INDEX id_index
-
删除主键索引
ALTER TABLE student DROP PRIMARY KEY;
开发中索引的适用范围
适合
- 频繁作为查询条件字段,适合创建索引;
不适合
- 唯一性太差的字段,不适合创建索引;
- 更新十分频繁的字段,不适合创建索引;
- 不会出现在where子句的字段,不适合创建索引;