MYSQL-索引

摘要

在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)      
)
1
2
3
4
5
6

此时students.ibd（student表在本机文件的大小）数据文件的大小为523M。

现在有一个需求：查询age=50万这条数据

1. 普通SELECT语句

   SELECT * FROM students WHERE id=500000;
   
   查询时间=4.35秒
   1
   2
   3

   对于计算机来说，查询一条数据需要花费4.35秒是完全不能接受的。此时就可以床关键索引来加快数据的检索能力。

2. 索引检索

   CREATE INDEX age_index on student(age)   //创建id属性的索引
   
   SELECT * FROM students WHERE age=50;
   
   查询时间=0.003秒
   1
   2
   3
   4
   5

   创建索引后，此时students.ibd数据文件的大小为634M，说明索引占用一定量的空间。
   但是使用索引后，在不用加内存、修改程序、调整sql的情况下，查询的效率得到了质的飞跃。

MYSQL只对创建了索引的列进行查询时适用索引。
SELECT * FROM students WHERE age=50;
由于age列没有创建索引，所以查询的时间是4.35秒。

查询时间=4.35秒

索引的机制

表格形式的对比如下：

普通检索机制

在没有查询没有索引的数据时，数据库系统查询的机制是线性扫描每一行元素
知道满足查询条件为止，查询速度慢。如下如所示：

索引检索机制

现在对id属性创建索引，数据库系统会形成一个数据结构，比如二叉树、B+树等等，如下如所示：

根据二叉树的搜索，可以快读的找到目标元素。
因此，如果我们比较了3次，实际上覆盖了表的范围：$2^3=8$

局限性

虽然索引对于Select语句的查询速度有着质的飞跃，但是对update、insert、delete速度有一定的影响。

这里以delete操作为例：

当执行了delete操作后，数据的删除导致索引的结构就会发生改变，导致执行速度收到影响，如下如所示:

索引分类

MySql支持多种类型的索引，以下是常用的索引类型：
以下是表格形式的描述：

详细说明如下。

主键索引（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)      
)
1
2
3
4
5
6

在创建主键时，主键本身就是一种索引，所以在根据id查询数据时，速度很快。

id是唯一的，同时也是索引，称为unique索引。

全文索引（FALLTEXT）

用于全文搜索，一段文章的字段上建立索引。

适用于MyISAM引擎，但是效率较低，开发中不使用，而是使用ElasticSearch。

相关操作

查看表的索引

SHOW INDEX FROM [表名]  //查看表索引

SHOW INDEX FROM [表名]

DESC [表名]      //查看表的描述
1
2
3
4
5

添加索引

1. 添加唯一索引

   CREATE UNIQUE INDEX id_index on student(id);   //id列创建索引
   1

2. 添加普通索引

   CREATE INDEX id_index on student(id);
   1

3. 添加主键索引

   CREATE TABLE students(
   	id INT PRIMARY KEY,   //主键自增长，指定为主键
   )
   1
   2
   3

4. alter

   ALTER TABLE student ADD INDEX id_index (id)：
   1

删除索引

1. 使用DROP INDEX语句删除索引：

   DROP INDEX id_index ON student;
   1

2. 使用ALTER TABLE语句删除索引：

   ALTER TABLE student DROP INDEX id_index
   1

3. 删除主键索引

   ALTER TABLE student DROP PRIMARY KEY;
   1

开发中索引的适用范围

适合

1. 频繁作为查询条件字段，适合创建索引；

不适合

1. 唯一性太差的字段，不适合创建索引；
2. 更新十分频繁的字段，不适合创建索引；
3. 不会出现在where子句的字段，不适合创建索引；