玩转Mysql 七 （索引的创建与设计原则）

一、索引的创建与使用

1、索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
（1）从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引。
（2）按照 物理实现方式 ，索引可以分为 2 种：聚簇索引和非聚簇索引。
（3）按照 作用字段个数 进行划分，分成单列索引和联合索引。

2、mysql索引的拓扑图

3、mysql索引概述

索引是排好序的数据结构，能够就少I/O次数。
MySQL-INNODB引擎使用B+树数据结构作为索引。
B树和B+树最大的区别是将数据存储到了叶子节点，并且叶子节点之间用指针相连。

• 主键索引PRIMARY KEY：不为空、不能重复
• 唯一索引UNIQUE：可为空，不能重复
• 普通索引INDEX：加速查找
• 联合索引：PRIMARY KEY(id,name)：联合主键索引?UNIQUE(id,name)：联合唯一索引INDEX(id,name)：联合普通索引

二、索引类型的优缺点

1、普通索引

普通索引是数据库中最基本的索引结构，也被称为单列索引或简单索引。它只包含一个列的值和指向该行的指针，用于加速对该列的单列查询。可以对表的任意列创建普通索引，但通常建议对经常进行查询和排序的列创建索引，例如主键列和外键列等。

普通索引的原理是将所需要查询的列作为索引列，按照索引列的值建立索引。当查询该列时，数据库系统会先在索引结构中进行查找，然后根据索引中的指针到数据表中找到对应的行。由于普通索引只包含一个列的值和指向该行的指针，因此查询时需要在数据表中找到其他所需的列的值。

普通索引的优点包括：

（1）可以加速单列查询的速度，特别是对于大型数据表和频繁的查询操作。

（2）可以提高数据的访问效率，从而加快数据的处理速度。

普通索引的缺点包括：

（1）当需要查询的列不在索引列中时，需要进行额外的查找操作，从而降低查询效率。

（2）创建普通索引需要占用额外的磁盘空间和内存空间，可能会对写入操作的性能产生一定的影响。

普通索引的使用场景包括：

1. 经常进行查询和排序操作的列。
2. 需要经常进行连接操作的表的外键列。
3. 数据表中需要保证唯一性的列，如主键列等。

普通索引是数据库中最基本的索引结构，用于加速对单列查询的速度，提高数据的访问效率。在创建普通索引时需要根据具体的业务需求进行选择，避免对写入操作的性能产生过大的影响。

2、唯一索引

唯一索引是一种限制数据库表中列值唯一性的索引，用于保证在指定列上没有重复的数据。与普通索引不同，唯一索引在索引列中的每个值都是唯一的，且不允许插入重复值，包括 NULL 值。

唯一索引的原理与普通索引类似，只是对于唯一索引而言，如果插入或更新操作的列值已经存在，数据库系统会抛出一个唯一性冲突的错误。唯一索引可以用于加速唯一性约束条件的验证，从而提高数据的访问效率。

唯一索引的优点包括：

（1）可以保证数据表中的列值唯一性，避免重复数据的插入。

（2）可以提高数据的访问效率，加速唯一性约束条件的验证。

唯一索引的缺点包括：

（1）创建唯一索引需要占用额外的磁盘空间和内存空间，可能会对写入操作的性能产生

? ? ? ? 一定的影响。

（2）如果需要对多个列进行唯一性约束，需要创建多个唯一索引，会占用更多的磁盘空间和

? ? ? ? ?内存空间。

唯一索引的使用场景包括：

（1）需要对数据表中的某一列或多个列进行唯一性约束的情况。

（2）经常进行查询和排序操作的列需要保证唯一性。

唯一索引是一种用于限制数据库表中列值唯一性的索引，可以保证数据表中的列值唯一性，提高数据的访问效率。在创建唯一索引时需要根据具体的业务需求进行选择，避免对写入操作的性能产生过大的影响。

3、主键索引

主键索引是一种基于数据库表中主键的索引技术，主要用于加速对数据库表中某个记录的查找和访问。主键是一种唯一标识数据库表中每个记录的字段或一组字段，每个记录都应该具有不同的主键值。

主键索引通常使用B+树等数据结构进行实现，以便能够快速地查找和定位某个记录。

主键索引的特点包括：

（1）主键索引是一种唯一索引，要求每个记录的主键值都是唯一的。

（2）主键索引通常是表中的主键字段上创建的索引，可以加快对该字段的查找和访问速度。

（3）主键索引可以用于加速数据库表的连接操作和数据的排序操作。

主键索引的优点包括：

（1）提高了对数据库表中某个记录的查找和访问速度，降低了访问数据库表的成本。

（2）由于主键值是唯一的，可以保证每个记录都能被唯一地标识和访问。

（3）可以用于加速数据库表的连接操作和数据的排序操作，提高了查询和分析数据的效率。

主键索引的缺点包括：

（1）主键索引只适用于基于主键的查询，对于其他类型的查询可能会不够高效。

（2）在插入新记录或更新主键值时，需要重新维护主键索引，可能会对数据库性能产生影响。

（3）如果主键值不够唯一或者数据量较大，主键索引可能会占用较多的磁盘空间和内存空间。

主键索引是一种基于数据库表中主键的索引技术，可以提高对数据库表中某个记录的查找和访问速度，用于加速数据库表的连接操作和数据的排序操作。在使用主键索引时需要考虑到其对于其他类型的查询可能不够高效，以及在插入新记录或更新主键值时需要重新维护主键索引的成本。MySQL 主键索引还是聚簇索引。

3、复合（联合）索引

复合索引是一种将多个列组合在一起来创建的索引，用于优化查询中涉及到多个列的查询效率。相对于单列索引，复合索引可以更有效地支持多列的查询，提高查询效率。

在创建复合索引时，可以选择多个列，并按照顺序依次排列。这样可以使得查询更加高效，因为复合索引的叶子节点存储的是多列的值，可以直接满足多列查询的需求，避免了查询时多次进行索引扫描的开销。

例如，对于一个包含姓名和年龄的表，可以创建一个复合索引，将这三列按照顺序依次排列。这样，当需要查询姓名、性别和年龄都满足一定条件的记录时，可以直接使用复合索引来完成查询，避免了多次索引扫描的开销，从而提高了查询效率。

复合索引的优点包括：

（1）可以提高多列查询的效率，避免多次索引扫描的开销。

（2）可以减小索引的存储空间，适用于需要对多个列进行查询的表。

复合索引的缺点包括：

（1）复合索引的维护成本比单列索引高，因为需要对多个列进行维护。

（2）对于一些单列查询，复合索引可能并不适用，因为需要扫描复合索引的叶子节点，而单列索引可以直接定位到需要的记录。

复合索引的使用场景包括：

（1）需要对多列进行查询的表，例如包含姓名和年龄等多个列的表。

（2）查询操作较多，更新操作较少的表。

4、全文索引

全文索引是一种基于文本内容的索引技术，可以快速地检索出包含指定关键词或短语的文档或记录。相比于传统的索引技术，全文索引更加适用于文本数据的搜索和查询。

全文索引通常使用倒排索引（Inverted Index）的数据结构，将每个单词或短语出现的位置作为索引项，以便进行快速的查找和匹配。倒排索引可以理解为是一张单词词表，每个单词都指向包含该单词的文档或记录的位置。通过倒排索引，可以快速地找到包含指定单词的文档或记录，以及它们出现的位置。

全文索引的优点包括：

（1）可以对文本数据进行高效的搜索和查询，提高数据的访问效率。

（2）可以支持模糊搜索和短语搜索等功能，增强搜索的灵活性和准确性。

全文索引的缺点包括：

（1）创建全文索引需要占用大量的磁盘空间和内存空间，可能会对系统的性能产生影响。

（2）全文索引的更新和维护成本较高，需要花费较多的时间和资源。

全文索引的使用场景包括：

（1）需要对大量文本数据进行搜索和查询的情况，如新闻、博客、社交媒体等应用。

（2）需要支持模糊搜索和短语搜索等高级搜索功能的情况。

全文索引是一种基于文本内容的索引技术，可以高效地对文本数据进行搜索和查询，支持模糊搜索和短语搜索等高级搜索功能。在使用全文索引时需要考虑到其占用的磁盘空间和内存空间，以及更新和维护的成本。

------未完待续----------