Mysql索引知识详谈

原文地址：https://www.t-io.org/1159984867644153856

1. 索引的重要性

1. 索引之于数据表，相当于检字表于汉语字典，设想让你在没有检字表的情况下从字典中找出一个“武”字，你会多么无助，如果用检字表，我们一眼就能找出它在第509页
2. 数据库服务器用索引去找到相应的记录，相当于我们使用检字表去找相应的字
   
   

2. 预置环境

为了更好的描述相关知识，我们先在数据库中创建一张table_demo表，此表仅用于演示后面的相关操作，其设计合理性不在考虑之列，后面所有的操作均以此为基点

DROP TABLE IF EXISTS `table_demo`;
CREATE TABLE `table_demo` (
  `id` int(11) NOT NULL,
  `field_1` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `field_2` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `field_3` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `field_4` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. Mysql索引创建语法

ALTER TABLE 表名
ADD [索引类型]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；

1. 索引类型

1. PRIMARY：主键索引。唯一且不能为空，一张表只能有一个主键索引ALTER TABLE table\_demo DROP PRIMARY KEY, ADD PRIMARY KEY (id, field\_1);ALTER TABLE table\_demo ADD UNIQUE INDEX (field\_1) ;ALTER TABLE table\_demo ADD FULLTEXT INDEX (field\_1) ;ALTER TABLE table\_demo ADD INDEX (field\_1) ;
2. UNIQUE：唯一索引
3. FULLTEXT：全文索引
4. 不指定索引类型，则为普通索引

2. KEY和INDEX

本人查阅了一些资料，发现这个区别对用户来说，有点无聊，目前我们先记住一条：只有创建PRIMARY时，后面跟KEY，其它索引全部是跟INDEX

ALTER TABLE `table_demo`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `field_1`);

关于外键，本人从毕业到现在，从未主动使用过外键，至于原因么：外键带来的好处远小于它带来的坏处

3. ASC | DESC

查阅了一些资料，是MySQL8出来的东西，暂时忽略吧，根据索引原理，这个估计也没啥子用

4. 索引方法

1. HASH：顾名思意，就是点击式搜索，譬如“=”，“<>”ALTER TABLE table\_demo ADD UNIQUE INDEX (field\_1) USING HASH ;ALTER TABLE table\_demo ADD UNIQUE INDEX (field\_1) USING BTREE ;
2. BTREE：非点击式搜索，都用BTREE吧，譬如>，>=，<，<=、BETWEEN、Like

5. 组合索引

创建示范

ALTER TABLE `table_demo`
ADD INDEX (`field_1`, `field_2`, `field_3`, `field_4`) ;

1. 组合索引（别名：联合索引或复合索引），是一个索引中包含多个字段。
2. Mysql从左到右的使用组合索引中的字段，一个查询可能只使用索引中的前部分字段，以刚刚创建的组合索引为例，一个查询可能会使用(field_1)或（field_1, field_2）或(field_1, field_2, field_3)或(field_1, field_2, field_3, field_4)，但不会使用(field_2, field_3, field_4)、(field_3, field_4)等字段作为索引。
3. 前一条规则，可以对我们的索引设计形成很好的指导：Mysql相当于把组合索引的前面的字段全部依次建立了索引，这样我们就没必要再对这些字段进行单一的创建索引，下图很好的演示了组合索引