Mysql索引

参考博客

• https://www.cnblogs.com/zsql/p/13808417.html
• https://juejin.cn/post/7031712626581356574

索引类别

​ 索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
　　MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了

​ 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，而不是在select的字段中，实际上，索引也是一张“表”，该表保存了主键与索引字段，并指向实体表的记录，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。说白了索引就是用来提高速度的，但是就需要维护索引造成资源的浪费，所以合理的创建索引是必要的。

在https://dev.mysql.com/doc/refman/8.0/en/create-index.html 中，显示了不同数据库引擎不同类别索引的相关信息

按照索引的特性分：

1. Primary Key（聚集索引）：InnoDB存储引擎的表一定存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

2. Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值

3. Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值

4. FULLTEXT（全文索引）：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

5. SPATIAL（空间索引）：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL

按索引列的数量分：

1. 单列索引：单列索引即一个索引只包含单个列

2. 联合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合

按数据结构分：

1. B+ Tree索引
2. Hash索引
3. Full-text索引

按索引的物理存储方式分：

1. 聚簇索引(clustered index)：数据结构是B+树。数据文件是和（主键）索引绑在一起的，即索引 + 数据 = 整个表数据文件，通过主键索引到整个记录，必须要有主键，通过主键索引效率很高

   • 聚集索引的主键索引

     

   • 聚集索引的辅助索引(二级索引，secondary index): 辅助索引需要两次查询，因为辅助索引是以建索引的字段为关键字索引到主键，所以需要两次，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

     

   • 我们看到，这个二级索引是建立在名字上的，那么如果要查询：SELECT * FROM table WHERE name = ALICE, 那么就先去根据二级索引的B+树上查找，得到Alice的主键是18；然后再根据18去主键上的B+树查找，最终可以找到该行的所有数据

1. 非聚集索引：数据结构也是B+ 树，但是索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

   • 非聚集索引的主键索引
     

   • 非聚集索引的二级索引，对于MyISAM来说数据文件和索引文件则是分开的。

     

Mysql中三种引擎的索引

我们看到，只有存放在内存中的表，才可以使用哈希索引，

InnoDB vs Mysiam

聚集索引的优点：

• 可以把相关数据保存在一起，如：实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件，如果没有使用聚集索引，则每封邮件都可能导致一次磁盘IO
• 数据访问更快，聚集索引将索引和数据保存在同一个btree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值

聚集索引的缺点：

• 我们知道InnoDB使用的是聚集索引，根据聚集索引的特性，不建议使用过长的字段作为主键：因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

• 此外，二级索引访问需要两次索引查找(回表)，而不是一次

• 用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。(这点在Mysiam中也是如此)

• 聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了
• 聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

非聚集索引的优点：

我们知道，Myisam的主键索引的叶子节点只存放数据在物理磁盘上的指针，其他次索引也是一样的。因此，当存在大数据列(如varchar(300)),那么如果用聚簇索引会导致主键id排序比较慢，因为主键下存放着所有的数据列。但是Mysiam就不需要扫描数据列，可以直接进行排序。因此，非聚集索引在处理文本类型的数据时更有优势

需要规避的设计

不要使用UUID来作为聚集索引，否则性能会很糟糕。

• 写入的目标页可能已经刷到磁盘上并从缓存中移除，或者是还没有被加载到缓存中，innodb在插入前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO
• 因为写入是乱序的，innodb不得不频繁地做页分裂操作，以便为新的行分配空间，页分裂会导致移动大量数据，一次插入最少需要修改三个页不是一个页
• 由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片

索引的创建管理

索引的创建原则

1. 索引并非越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改的同时，索引也会进行调整和更新
2. 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
3. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
4. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。
5. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
6. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
7. 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
8. 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个CHAR(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
9. 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
10. 对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果

索引创建SQL

索引的查询

平时设计的时候很多时候都会用到联合索引，一般很少用单个字段作为索引，这样可以让索引尽量少一些，避免磁盘占用太多，增删改性能太差。

加入有叫学生分数表，包含：班级，姓名，科目名称，成绩分数，一般我们都会根据学生的班级+姓名+科目来查询，这时候就需要建立二级索引KEY（class_name，student_name ，subject_nane ）。这时候索引的数据结构图如下图所示：

那么，在查询过程中，会用到各式各样SELECT语句，它们对联合索引是怎么利用的呢？

等值匹配

如果执行下面的语句：SELECT * FROM student_scope where class_name= '1班' AND student_name ='小明' AND subject_nane ='英语';

因为WHERE条件里的几个字段的名称和顺序跟建立的联合索引一模一样，此时就会等值匹配，从索引页依次往下查找，会定位到：1+小明+英语（id=100）这条数据，然后根据id 从聚簇索引(主键)中回表查询提取所有的字段信息。

注意，由于Mysql有优化器，因此，这边的列如果顺序打乱(但是数量还是要和索引一样)，还是可以走索引的

最左侧列匹配

例如联合索引：KEY（class_name，student_name ，subject_nane ），不一定在where条件中都要写3个字段来才会走索引查询，只要根据最左侧的部分字段来查询也要走索引，下面的3条SQL都会走索引：

SELECT * FROM student_scope where class_name= '1班'  
																	AND student_name ='小明' 
																	AND subject_nane  ='英语'; # 等值匹配
																	
																	
SELECT * FROM student_scope where class_name= '1班' 
																	AND student_name ='小明';
SELECT * FROM student_scope where class_name= '1班' ;

此外，如果查询顺序和索引顺序不一样，也会采取最左侧来匹配，比如当执行下面的SQL，class_name字段会走索引，subject_name 就不会走索引：

SELECT * FROM student_scope where class_name= '1班' AND subject_nane  ='英语';

但是，如果WHERE后面第一个字段不是class_name，那就不符合最左匹配原则，完全不会用到索引

SELECT * FROM student_scope where student_name ='小明' AND subject_nane  ='英语';
SELECT * FROM student_scope where subject_nane  ='英语';

最左前缀匹配原则

在SQL查询的时候，我们经常会根据LIKE语法来查询，比如查询班级为1开头的数据，也可以用到索引，因为你的联合索引的B+树里，都是按照class_name排序的，所以你要是给出class_name的确定的最左前缀就是1，然后在后面给一个模糊匹配符号，那也是可以基于索引来查找。

比如：

SELECT * FROM student_scope where class_name like '1%';

但是执行以班结尾的数据就不会走到索引，因为不知道左侧前缀是多少，无法基于排序来查找。

SELECT * FROM student_scope where class_name like '%班';

范围查找规则

如果SQL查询的字段基于某个索引列采取范围查询，例如下面的SQL，也会走索引

SELECT * FROM student_scope where class_name >= '1班' AND class_name <'10班';

但是当第一个字段是范围查询的时候，后面的第二个字段是没法走索引，例如下面的SQL,class_name 会走索引，后面的student_name 不会走索引。

SELECT * FROM student_scope where class_name >= '1班' 
																	AND class_name <'10班' 
																	AND student_name ='小明';

等值匹配+范围匹配规则

如果要使用下面的SQL进行查询的时候，此时class_name 会走索引，精准定位到一波数据，接下来这波被命中的数据，是按照student_name排序而来，这时候student_name >= ‘小明’也会基于索引来查找，但是后面的student_name <’王五’就不能用索引。

SELECT * FROM student_scope where class_name = '1班' 
														AND student_name >= '小明' AND student_name <'王五';

一般写SQL语句，都是用联合索引的最左侧的多个字段来进行等值匹配+范围搜索，或者是基于最左侧的部分字段来进行最左前缀模糊匹配，或者基于最左侧字段来进行范围搜索，这就要写符合规则的SQL语句，才能用上建立好的联合索引。

排序如何使用索引

当我们的SQL 语句需要使用ORDER BY语句进行排序的时候，似乎应该是通过索引快速筛选出来一波数据，接着把数据放入内存，或者放在一个临时磁盘文件里，然后通过排序算法按照ORDER BY后面的字段进行排序，然后把排序好的数据返回，当排序的数据量比较大的时候，就不能够用内存进行排序，就会基于磁盘文件来排序（filesort），这样的话就比较慢。 因为建立的联合索引是按照一定的顺序已经排序好的，如果order by 后面的排序字段能够命中联合索引的时候，就会走索引，例如下面的排序SQL就会走索引

SELECT * FROM student_scope 
					ORDER BY class_name,student_name ,subject_nane
					LIMIT 10;

因此，在排序的时候尽量按照查询的联合索引去进行order by排序，这样就可以直接使用联合索引树里的数据有序性到索引数里直接按照字段的顺序获取所需要的数据。但是也有一定的限制，因为联合索引里的字段值在索引树里都是从小到大依次排列的 ，所以在ORDER BY里要不然就是每个字段后面什么都不加，直接就是ORDER BY xx1,xx2,xx3，要不然就都加DESC降序排列，就是ORDER BY xx1 DESC,xx2 DESC,xx3 DESC。

当ORDER BY 排序字段既有升序，又有降序，那么是没法走索引进行排序，例如下面的SQL语句没法走索引

SELECT * FROM student_scope
				 ORDER BY class_name DESC,student_name ASC,subject_nane DESC 
				 LIMIT 10;

分组如何使用索引

GROUP BY 语句使用索引的时候跟ORDER BY排序使用索引的规则一样，对于group by后的字段，最好也是按照联合索引里的最左侧的字段开始，按顺序排列开来，这样的话，就可以完美的运用上索引来直接提取一组一组的数据，然后针对每一组的数据执行聚合函数就可以。

索引实现原理

B+树索引

上面我们说过，Primary Key、Unique和Key都是基于B+树实现的。首先，我们可以从我的这篇博客来了解B+树的数据结构。

那么数据库索引为什么要用 B+ 树而不用红黑树呢？

AVL 树和红黑树这些二叉树结构的数据结构可以达到最高的查询效率这是毋庸置疑的。

既然如此，那么数据库索引为什么不用 AVL 树或者红黑树呢？

这就牵扯到一个问题了，不考虑每种数据结构的前提条件而选择数据结构都是在耍流氓。

AVL 数和红黑树基本都是存储在内存中才会使用的数据结构，那磁盘中会有什么不同呢？

这就要牵扯到磁盘的存储原理了

操作系统读写磁盘的基本单位是扇区，而文件系统的基本单位是簇(Cluster)。

也就是说，磁盘读写有一个最少内容的限制，即使我们只需要这个簇上的一个字节的内容，我们也要含着泪把一整个簇上的内容读完。

那么，现在问题就来了

一个父节点只有 2 个子节点，并不能填满一个簇上的所有内容啊？那多余的内容岂不是要浪费了？我们怎么才能把浪费的这部分内容利用起来呢？哈哈，答案就是 B+ 树。

由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅，深度代表什么？代表磁盘 io 次数啊！数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的啊！

所以，涉及到磁盘上查询的数据结构，一般都用 B+ 树啦。

哈希索引

简介

哈希索引自然是基于哈希表实现，只有匹配所有列的查询才有效。对于每一行数据，存储引擎都会对所有索引列计算一个哈希码，哈希码是一个较小的值，不同键值的行计算出的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时保存指向每个数据行的指针。

如果多个列的哈希值相同(哈希冲突)，索引会以链表的方式存放多个记录指针到同一个哈希条目中去。

限制

1. 哈希索引只保存哈希码和指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行速度非常快（因为是MEMORY引擎），所以对性能影响并不大
2. 哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序
3. 哈希索引不支持部分索引列查找，因为哈希索引始终是使用索引列的全部内容来计算哈希码。 如，在数据列（A,B）上建立哈希索引，如果查询只有数据列A，则无法使用该哈希索引
4. 哈希索引只支持等值比较查询，包括= ,IN() ,<=> ;哈希索引不支持范围查询，如where price > 100
5. 哈希冲突（不同索引列会用相同的哈希码）会影响查询速度，此时需遍历索引中的行指针，逐行进行比较。