初识MySQL索引

🏠个人主页：不会写代码的满满
🧑个人简介：大家好，我是满满，一个想要与大家共同进步的男人😉😉
目前状况🎉：开学即将大三，目标就是半年内找到一份实习工作👏👏
💕欢迎大家：这里是CSDN，我总结知识的地方，欢迎来到我的博客，我亲爱的大佬😘

正文开始 ----------

索引这个东西，在我还没接触到MySQL的学习中，就已经听到无数次了，现在正好赶上了MySQL阶段的学习，那就一起来啃一啃索引这个硬骨头!!!

可以毫不夸张的说，系统中 SQL 的好坏，是能直接决定你系统的快慢的。但是在优化之前大家是否想过一个问题？

那就是：我们优化的原则是什么？ 优化SQL的理论基础是什么？

所以说了这么多只想告诉大家，在真正的开始索引优化之前，我们需要彻底搞明白索引的原理。这样再谈优化你将觉得更丝滑~

1.索引的本质

索引的本质是一种排好序的数据结构。这个我相信其实大家并不陌生，因为谈到索引很多人自然而然的就会联想到字典中或者是文章中的目录。

下面谈谈我对索引的看法吧

索引是对数据库表中的字段 （可以是一个字段也可以是多个字段）的值进行排序的数据结构。索引就像一个数的目录一样，可以快速访问我们想要访问的目录，在数据库中就是可以快速访问到数据库表中具有一定特点的数据信息。这个只是索引的表面，实际上索引的底层是一些高级的数据结构，只不过我们看不到，在数据库中已经帮我们写好了，在数据库中其实有很多种类的数据结构，常见的有平衡二叉树，红黑树，Hash表，B+树等，但是在MySQL数据库中主要是B+树。

1️⃣索引的优缺点

索引可以大大提高MySQL的检索速度，为什么不对表中的每一个列创建一个索引呢？

优点

1. 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
2. 索引可以帮助服务器避免排序和创建临时表
3. 索引可以将随机IO变成顺序IO
4. 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
5. 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
6. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
7. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
8. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
9. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点

1. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2. 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那
3. 需要占用的空间会更大
4. 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
5. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
6. 对于非常小的表，大部分情况下简单的全表扫描更高效；

2️⃣创建索引准则

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

应该创建索引的列

• 在经常需要搜索的列上，可以加快搜索的速度
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
• 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
• 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范是连续的
• 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不该创建索引的列

• 对于那些在查询中很少使用或者参考的列不应该创建索引。
• 若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
• 对于那些只有很少数据值或者重复值多的列也不应该增加索引。
• 这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
• 对于那些定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。
• 当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）

2.索引的分类

2.1 主键索引

主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；

 ALTER TABLE TableName ADD PRIMARY KEY(column_list); 
1

2.2 唯一索引

唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX IndexName ON `TableName` (`字段名`(length));
或者
ALTER TABLE TableName ADD UNIQUE (column_list); 
1
2
3

2.3 普通索引

普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

CREATE INDEX IndexName ON `TableName`(`字段名`(length));
或者
ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length));
1
2
3

2.4 全局索引

全文索引：它查找的是文本中的关键词，主要用于全文检索。一般很少用

全文索引：FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持，FULLTEXT索引仅适用于 CHAR， VARCHAR和 TEXT列。

​ 创建全文索引

drop table if exists tc_10;
create table tc_10(
  aaa int primary key,
  bbb varchar(20),
  ccc datetime,
  fulltext i_tc10_a(bbb)
);
alter table tc_10 add fulltext i_tc10_b(bbb);
1
2
3
4
5
6
7
8

​ 删除全文索引，与删除普通索引一致。

drop index i_tc10_b on tc_10;
1

3. 操作索引

创建索引

索引名称 index_name 是可以省略的，省略后，索引的名称和索引列名相同。

-- 创建普通索引 
CREATE INDEX index_name ON table_name(col_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
1
2
3
4
5
6
7
8
9
10
11
12
13

修改表结构创建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);
1

创建表时直接指定索引

CREATE TABLE table_name (
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name (col_name)
);
1
2
3
4
5

删除索引

-- 直接删除索引
DROP INDEX index_name ON table_name;

-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
1
2
3
4
5

删除唯一索引的方法与删除普通索引相同。

其它相关命令

-- 查看表结构
desc table_name;

-- 查看生成表的SQL
show create table table_name;

-- 查看索引信息（包括索引结构等）
show index from  table_name;

-- 查看SQL执行时间（精确到小数点后8位）
set profiling = 1;
SQL...
show profiles;
1
2
3
4
5
6
7
8
9
10
11
12
13