MySQL索引教程（01）：创建索引

MySQL 创建索引

对于一个具有大量数据行的表，如果你根据某个查询条件检索数据时很慢，可能是因为你没有在检索条件相关的列上创建索引。
索引类似于词典中的目次。如果您想要在词典中查询一个词，准确的做法是先检察目次，再根据目次中的指示到指定的页面找到相关的词。准确的索引可以显着进步从数据库表中检索数据行的速度。
MySQL 允许您使用 CREATE INDEX 语句在指定的表上为指定的列创建索引。
索引介绍

索引是一种数据结构，比方 B-Tree，它进步了从表中检索数据行的速度，但需要额外的写入和存储来维护它。
查询优化器可以使用索引来快速定位数据，而不必针对给定查询扫描表中的每一行。
当您使用主键 或唯一键创建表时，MySQL 会主动创建一个名为 PRIMARY 的索引。 该索引称为聚集索引。
PRIMARY 索引是特别的，因为索引本身与数据一起存储在同一个表中。聚集索引强制实行表中行的顺序。
PRIMARY 索引以外的其他索引称为二级索引或非聚集索引。
MySQL CREATE INDEX 语法

您应该按照如下的语法使用 CREATE INDEX 为一个表添加一个索引：

1. CREATE [UNIQUE] INDEX index_name
2. [USING {BTREE | HASH}]
3. ON table_name (column_list)
4. [algorithm_option | lock_option];

复制代码

这里，

• UNIQUE 关键字表明此索引为唯一索引。它是可选的。
  
• index_name 是索引的名字。一个表中不应该出现两个雷同名字的索引。
  
• table_name 是表的名字。
  
• column_list 是表中的列名。多个列名使用逗号分隔。
  
• USING 子句指定索引的类型。可选值：BTREE，HASH。 它是可选的。
  
• algorithm_option 指定删除索引的算法。它使用以下的语法：
  

1. ALGORITHM [=] {DEFAULT | INPLACE | COPY}

复制代码

ALGORITHM 子句是可选的。默认为 INSTANT。如果不支持 INSTANT，则使用 INPLACE。
使用 DEFAULT 和省略 ALGORITHM 子句结果雷同。
以下是对各个算法的说明：

• COPY：对原表的副本举行操作，将原表中的表数据逐行复制到新表中。
  
• 不允许并发 DML。
  
• INPLACE: 操作避免复制表数据，但可能会当场重修表。在操作的准备和实行阶段，可能会短暂地对表举行独占元数据锁定。通常，支持并发 DML。
  
• INSTANT: 操作只修改数据字典中的元数据。在操作的实行阶段，可能会短暂地对表举行独占元数据锁定。表数据不受影响，使操作刹时完成。允许并发 DML。（在 MySQL 8.0.12 中引入）
  

lock_option 指定删除索引的并发控制计谋。它使用以下的语法：

1. LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

复制代码

LOCK 子句是可选的。以下是对各个并发计谋的说明：
DEFAULT
给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的最大并发级别：如果支持，则允许并发读取和写入。如果不是，则允许并发读取（如果支持）。如果不是，则强制实行独占访问。
NONE
如果支持，允许并发读取和写入。否则，会发生错误。
SHARED
如果支持，允许并发读取但制止写入。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发写入，写入也会被制止。如果不支持并发读取，则会发生错误。
EXCLUSIVE
强制实行独占访问。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发读/写，也会这样做。
在 MySQL 内部，CREATE INDEX 语句被映射为 ALTER TABLE … ADD INDEX … 语句。
MySQL 索引类型

默认情况下，如果您不指定索引类型，MySQL 将创建 B-Tree 索引。下面显示了基于表的存储引擎允许的索引类型：

MySQL CREATE INDEX 实例

在以下实例中，我们使用students表举行演示。
创建students表，并添加数据

1. CREATE TABLE `students`  (
2.   `student_id` int(11) NULL DEFAULT NULL,
3.   `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
4.   `age` int(11) NULL DEFAULT NULL,
5.   INDEX `age`(`age` ASC) USING BTREE
6. ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
8. -- ----------------------------
9. -- Records of students
10. -- ----------------------------
11. INSERT INTO `students` VALUES (1, '安靖', 18);
12. INSERT INTO `students` VALUES (2, '平安', 19);
13. INSERT INTO `students` VALUES (3, '总结', 20);
14. INSERT INTO `students` VALUES (3, '周杰伦', 18);
15. INSERT INTO `students` VALUES (4, '张三', 18);
16. INSERT INTO `students` VALUES (5, '李四', 18);
17. INSERT INTO `students` VALUES (6, '钟意', 19);
18. INSERT INTO `students` VALUES (7, '张宇', 18);
19. INSERT INTO `students` VALUES (8, '刘浪', 18);

复制代码

以下语句查找年龄等于18的学生信息：

1. SELECT * FROM students WHERE age = 18;

复制代码

查询结果如下：

您可以是使用 EXPLAIN 来检察以上 SELECT 语句的语句的实行筹划，以了解 MySQL 在内部怎样实行此查询，如下所示：

1. EXPLAIN SELECT * FROM students WHERE age = 18;

复制代码

如您所见，MySQL 必须扫描由9行组成的整个表才能找到具有符合条件的行。
现在，使用以下 CREATE INDEX 语句为该列 age 创建索引 ：

1. CREATE INDEX age ON students(age);

复制代码

创建结果：

要检察索引是否创建成功，请使用以下 SHOW INDEXES 语句显示表 actor 的索引，比方：

1. SHOW INDEXES FROM students;

复制代码

查询结果

然后，再次实行上面的 EXPLAIN 语句：

1. EXPLAIN SELECT * FROM students WHERE age = 18;

复制代码

查询结果如下：

如您所见，MySQL 只需从 age 指示的索引中定位此中的 6 行， 而无需扫描整个表。所以会进步查询速度。
结论

在 MySQL 中，索引能进步从表中查询数据的效率。您可以使用 CREATE INDEX 为表创建索引。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。