目录
一、索引机制的引入
1.索引机制🐂B在哪里？
我们先来创建一张学生表，向表中随意添加一些数据后，利用蠕虫复制（自我复制）将表中的数据量扩展到百万级别，代码如下 :
CREATETABLE IF NOT EXISTS `students`( `s_id` MEDIUMINTUNSIGNEDNOTNULL, `s_name` VARCHAR(64)NOTNULL DEFAULT '', `s_age` SMALLINT UNSIGNEDNOTNULL DEFAULT 0) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;INSERTINTO students VALUES(1,'sdads',22),(1,'Cyan',22),(3,'Raln',22),(1,'sdads',22),(122,'sdads',22),(9,'sdads',22),(1,'NXOl',22),(34,'DANz',22),(76,'AMqwc',22);INSERTINTO students SELECT*FROM students;
经过几轮蠕虫复制后，我们可以利用COUNT函数来查询表中现在一共有多少条记录，如下 :
SELECTCOUNT(*)FROM students;
可以看到，表中现在已经有900多万条数据了，这么大的数据量，在查询数据时需要消耗多长的时间捏🤔 。我们来测试一下 :
先向表中另外添加一条要查询的数据（避开蠕虫复制的id），然后进行查询，如下 :
INSERTINTO students VALUES(233,'Ice',21);SELECT*FROM students WHERE s_id =233;
在无索引机制的情况下，通过id查询数据用了足足4s多，你可以想象下面的场景——月黑风高夜，夜深人静时，你正想趁着此般时机在某网站上大饱眼福，就比如说在CSDN上吧，你想要让无穷无尽的知识映入你的眼帘，但是你发现，你每用鼠标点击一次都得等待4s多才能给你反馈，反复多次以后，你想骂娘了，***流量都准备好了给我整这玩意儿？
没错，如果没有索引机制的加持，当数据量足够大时，比如达到百万级别以上，浏览网站将会是非常痛苦的一件事。那这时候可能就要有p小将(Personable小将，指风度翩翩的人)出来bb问了——你™BB一大堆说了个啥？索引机制呢？这踏🐎不是跑题水博文？
p哥教训的是😭。这就来演示一下——如果我们用了索引机制，在相同的查询条件下，会用多长的时间。但是，演示之前我们先来看一下，目前900多万条的数据占了多大的空间，如下 : (ibd后缀，表示文件为INNODB存储引擎下保存的表数据和索引的文件）。
为s_id字段创建索引（创建索引也需要时间），并进行相同的查询，如下 :
CREATE INDEX id_index ON students(s_id);
SELECT*FROM students WHERE s_id =233;
发现没有，相同的查询语句，添加索引前后的时间之比 = 4.269 / 0.019 ≈ 225，上百倍的性能差距。那么，我们再来看一下建立索引机制后，该表的数据发生了什么变化? 如下 :
编辑
可以发现，索引机制的本质，其实就是——以空间换时间，即索引本身的建立也是需要占用空间的。
2.索引机制提高查询速度的原理 :
以往常规的查询中，不管你查询什么数据，它都是从表头第一条记录开始查找，一直找到表的末尾，即扫描了全表。比方说你要查询一条id = 100的记录，就算在表中找到了一条id = 100的记录，但是仍然不能保证该记录下方的记录中没有id = 100的，因此，就算表中真的只有一条id = 100的记录，最终还是扫描了全表。
那么在表数据量庞大的情况下，全表扫描带来的弊端是相当明显的，我们方才也看到了，查询一次都得4s以上，甲方不得喷死你？
那么索引机制又是如何解决这个问题的呢？
索引机制会根据定义索引的字段建立一个索引的数据结构，这个数据结构可能是二叉树，B+树等等。比方说，我们上文中对s_id字段建立了索引，那么以最简单的二叉树为例，如下图所示 :
编辑
采用“折半”的思想，取中位数为根结点，左子树的结点一定都比根结点小，右子树的结点一定都比根结点大。 那么，当我们要查询id = 233的学生时，只需要先和122判断，233比122大，直接就去122的右子树查询了，122的左子树一个都不需要比较，就大大减少了查询的次数，进而缩短了查询时间，提高了查询性能。
但是，俗话说的好——甘瓜苦蒂，天下物无全美！
索引机制也存在自己的缺点——
①首先最直观的一点，由于索引采用了“以空间换时间”的思想，所以建立索引一定会增大对空间的开销；
②其次，对于所引建立的数据结构，若表中数据出现了诸如"增加，删除，更改"这些DML（Data Manipulation Language) 时，就需要对这个数据结构进行维护，影响了DML的执行效率。
实际上，两害取其轻，由于在日常的项目开发和维护中，DQL（Data Query Language) 的使用频率远远高过DML，两者的使用频率之比接近9 : 1；因此，我们往往还是乐于去建立索引，以极大提高查询语句的性能，毕竟21世纪，时间才是最珍贵嘛😋。
二、索引的创建
1.索引分类 :
1°主键索引：当表中定义了主键(PRIMARY KEY)时，主键自动为主索引，可以说，主键是一个特殊的索引，有主键限制的查询语句，查询速度会很快。
2°唯一索引：当表中定义了UNIQUE约束时，自动建立唯一索引(也可以手动创建），有UNIQUE限制的查询语句，查询速度也很快。
3°普通索引：就是INDEX；虽然普通，但使用频率却是最高的，因为更加灵活；普通索引允许数据重复，比如说name字段。
4°全文索引：FULLTEXT；适用于MyISAM存储引擎。PS ：由于MySQL自带的全文索引比较LOW，没法用，因此实际开发中使用最多的是Solr和ElasticSearch(ES)。
2.使用格式 :
1°创建唯一索引 :
CREATE UNIQUE INDEX index_name ON table_name(field_name);
2°创建普通索引 :
CREATE INDEX index_name ON table_name(field_name);
ALTER TABLE table_name ADD INDEX index_name(field_name);
3°创建主键索引 :
ALTER TABLEtable_name ADD PRIMARY KEY(field_name);
3.代码演示 :
建立一张动物表animals，令动物编号a_no为主键，动物名字a_name为UNIQUE，使用"SHOW INDEXES FROM table_name"指令来查看动物表的索引情况，代码如下 :
CREATETABLE IF NOT EXISTS `animals`( `a_no` MEDIUMINTUNSIGNED PRIMARY KEY, `a_name` VARCHAR(64) UNIQUE NOTNULL, `a_habitat` VARCHAR(64)NOTNULL DEFAULT '') CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;SHOW INDEXES FROM animals;
编辑
可以看到，Non_unique均是0，表示主键索引和唯一索引均不允许数据重复；Column_name则表示当前索引添加在了哪个字段上。
尝试通过手动添加的方式建立唯一索引，如下 :
CREATE UNIQUE INDEX a_nameIndex ON animals(a_name);CREATE UNIQUE INDEX a_nameIndex2 ON animals(a_name);SHOW INDEXES FROM animals;
编辑
可见，MySQL允许在同一字段上创建名称不同的多个索引；当然，主键索引除外，每张表最多只允许存在一个主键。
下面我们另建一张表，演示一下手动创建主键，以及普通索引的创建，代码如下 :
CREATETABLE IF NOT EXISTS `animals_EX`( `no` MEDIUMINTUNSIGNED, `name` VARCHAR(64) UNIQUE NOTNULL, `habitat` VARCHAR(64)NOTNULL DEFAULT '') CHARACTER SET utf8mb3 COLLATE utf8mb3_bin ENGINE INNODB;# 手动添加主键 ALTERTABLE `animals_EX` ADD PRIMARY KEY(`no`);# 手动添加普通索引 ALTERTABLE `animals_EX` ADD INDEX index_nameTest(`name`);CREATE INDEX `index_nameTest2` ON animals_EX(`name`);CREATE INDEX `index_habitatTest` ON animals_EX(habitat);# 查看表的索引信息 SHOW INDEXES FROM animals_EX;
编辑
可以看到，普通索引的Non_unique栏下是1，也就是允许有重复数据。
三、索引的删除
1.格式 :
1°删除非主键索引 :
DROP INDEX index_name ON table_name;
2° 删除主键索引 :
ALTER TABLE table_name DROP PRIMARY KEY;
PS :
若想修改索引——①删除当前索引；②添加新的索引。
2.演示 :
对于上文创建的animals_EX表的索引，如下图所示 :
编辑
要求删除该表的所有索引，如下 :
# 删除主键索引 ALTERTABLE `animals_EX` DROP PRIMARY KEY;# 删除非主键索引 DROP INDEX `name` ON `animals_EX`;DROP INDEX index_nameTest ON animals_EX;DROP INDEX index_nameTest2 ON animals_EX;DROP INDEX index_habitatTest ON animals_EX;SHOW INDEXES FROM animals_EX;
编辑
四、索引的查询
1.格式 :
1°SHOW INDEX FROM table_name;
2°SHOW INDEXES FROM table_name;
3°SHOW KEYS FROM table_name;
4°DESC table_name; （不如前三种方式的信息详细）
2.演示 :
以动物表animals为例，查询其索引的定义情况。
注意，前三种方式得到的结果是一模一样的。
SHOW INDEX FROM animals;SHOW INDEXES FROM animals;SHOW KEYS FROM animals;
编辑
第四种方式DESC table_name，本质上就是查看表的结构，不过也可以看出一些关于索引的信息。如下 :
DESC animals;
编辑
五、索引的使用规则
1°较频繁的作为查询条件的字段应该建立索引
eg : SELECT * FROM emp WHERE eno = 100; （雇员编号）
2°对于唯一性太差的字段，即使频繁作为查询条件也不适合单独建立索引
eg : SELECT * FROM emp WHERE esex = 'male'; (性别往往非男即女，存在大量重复数据）
3°更新较为频繁的字段不适合建立索引
eg : SELECT * FROM emp WHERE attendance_times; (若字段频繁更新，就需要对该字段索引的数据结构进行频繁的维护，会消耗较多性能，维护代价高）。
4°不会出现在WHERE子句中的字段不适合创建索引。（用不上)
System.out.println("END------------------------------------------------------------------------------");
