MySQL索引 作者：马育民 • 2020-02-03 23:17 • 阅读：10087 # 提出问题 - 查询速度慢 当 `user` 表中数据较多时，查询速度慢，原因如下： [](/upload/0/0/1IX3aTcGKB37.png) - 查询 年龄 `34`，逐行比对时，比对 **第1次** 就查询到 - 查询 年龄 `22`，逐行比对时，比对 **第9998次** 才能查询到，查询速度慢的原因 ### 解决 使用 **索引** # 访问表的两种方式 ### 顺序访问 [](/upload/0/0/1IX3aTcGKB37.png) 顺序访问是在表中进行 **全表扫描**，从头到尾逐行遍历，直到找到符合条件的数据。 **优点：**实现比较简单 **缺点：**当表中有大量数据的时候，**效率非常低下** ### 索引访问 索引访问是通过 **遍历索引来直接访问表中记录行** 的方式。 索引类似字典目录： [](/upload/0/0/1IX3eTb35cST.jpg) 使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。 # 索引介绍 索引是 MySQL 数据库中的重要对象之一，用于快速找出某个列中有某一特定值的行。 ### 索引的意义 索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。 [](/upload/0/0/1IX3eTVg9uBL.png) 如下图：根据 `age` 列建立的索引： 1. 索引 按照 `age` 大小排序 2. 索引与记录对应，通过查找索引，就能找到记录 # 根据引擎分类 索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。 ### B+树索引 详见 [mysql 索引原理与B+Tree](https://www.malaoshi.top/show_1IX38GULUJ0D.html "mysql 索引原理与B+Tree") ### hash索引 哈希（Hash）一般翻译为“散列”，也音译成“哈希”，就是把任意长度的数据，通过散列算法，得到固定长度的字符串，即：散列值 MySQL **MEMORY 存储引擎** 支持这类索引。 哈希索引优点：**访问速度快** **hash索引缺点：** - MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。 - 不能使用 HASH 索引排序。 - HASH 索引只支持等值比较，如：`=`、`IN()` 或 `<=>` - HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。 # 根据用途分类 根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类： ### 普通索引 普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 `INDEX` 或 `KEY`。 ### 唯一性索引 唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 `UNIQUE` 把它定义为一个唯一性索引。 创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。 ### 主键索引 主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 `PRIMARY KEY`。 注意：主键是数据库考察的重点。注意每个表只能有一个主键。 ### 单列索引和组合索引 - 单列索引就是索引只包含原表的一个列。 - 组合索引也称为复合索引或多列索引，相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。 提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。 为了提高索引的应用性能，MySQL中的索引可以根据具体应用采用不同的索引策略。这些索引策略所对应的索引类型有聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引等。 # 索引的缺点 虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端： - 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 - 除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。 - 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。 **注意：**索引可以在一些情况下加速查询，但是在某些情况下，会降低效率。 # 索引使用原则 ### 建议使用索引情况 索引只是提高效率的一个因素，因此在建立索引的时候应该遵循以下原则： - 在经常需要搜索的列上建立索引，可以加快搜索的速度。 - 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。 - 在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。 - 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。 - 在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。 - 在经常使用 `WHERE` 子句的列上创建索引，加快条件的判断速度。 ### 不建议使用索引情况 在某些应用场合下建立索引不能提高 MySQL 的工作效率，甚至在一定程度上还带来负面效应，降低了数据库的工作效率，一般来说不适合创建索引的环境如下： - 对于那些在查询中很少使用或参考的列不应该创建索引。 因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。 - 对于那些只有很少数据值的列也不应该创建索引。 因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。 - 对于那些定义为 `TEXT`、`IMAGE` 和 `BIT` 数据类型的列不应该创建索引。 因为这些列的数据量要么相当大，要么取值很少。 原文出处：http://www.malaoshi.top/show_1EF4uz5d3l26.html