数据库（4）：MySQL索引

MySQL索引是一种数据结构，它可以提高数据库查询的速度和效率。通过使用索引，MySQL可以更快地查找和检索数据，从而减少查询所需的时间和资源。

MySQL支持多种类型的索引，包括B-tree索引、哈希索引和全文索引。其中，B-tree索引是最常用的一种索引类型，它可以在大多数情况下提供快速的查询性能。

可以将索引理解为数据库中的目录。就像书籍的目录可以帮助读者快速找到所需的内容一样，索引可以帮助数据库快速定位和检索数据。索引存储了数据表中某些列的值和这些值所在的行的位置，通过索引可以直接定位到所需的数据行，从而提高查询效率。

在数据库中，可以使用多个索引，每个索引都相当于一个目录，可以根据不同的查询需求创建不同的索引。但是，与书籍目录不同的是，过多的索引会导致数据库性能下降，因此需要根据实际情况和查询需求进行合理的索引设计。

在MySQL中，可以对表中的一个或多个列创建索引。创建索引的语法如下：

CREATE INDEX index_name ON table_name (column_name);

B-tree索引的特点是，它的每个节点可以包含多个关键字和指向子节点的指针，节点的关键字按照升序排列，并且每个关键字对应的指针指向的子树中的所有关键字都大于前一个关键字，小于后一个关键字。这样，可以通过在B-tree索引中进行一次二分查找，快速定位到需要查找的关键字所在的节点，从而实现快速的查找操作。

需要注意的是，虽然B-tree索引可以提高查询性能，但是过多的索引也会影响数据库的性能。因此，在创建索引时需要谨慎考虑，只对经常被查询的列创建索引，并且尽量避免创建重复的索引。

为什么要设计索引

加快数据访问查询的效率。

在我们的开发过程中，表里面的数据可能有上百万行，为了快速找到查询数据的位置，我们构建索引。

通过使用索引，数据库可以更快地查找和检索数据，从而减少查询所需的时间和资源。因此，设计索引可以带来以下几个好处：

1. 提高查询性能：索引可以帮助数据库快速定位和检索数据，从而提高查询性能。如果没有索引，数据库需要逐行扫描数据表，这样会消耗大量的时间和资源，特别是在处理大量数据时，查询效率会更低。

2. 优化数据表结构：索引可以帮助优化数据表的结构，使得数据表更加紧凑和高效。通过对经常被查询的列创建索引，可以减少数据表的物理存储空间，提高数据的存储效率。

3. 改善并发性能：索引可以提高数据库的并发性能，使得多个用户可以同时访问数据库而不会相互干扰。如果没有索引，多个用户同时查询数据库时，可能会出现锁等待和死锁等并发问题，从而影响数据库的性能和可用性。

需要注意的是，索引也会占用一定的存储空间和计算资源，因此在设计索引时需要谨慎考虑，只对经常被查询的列创建索引，并且尽量避免创建重复的索引。

前置知识：磁盘预读

我们的数据最终都会存储在磁盘当中，但是我们在进行实际数据读取时，并不是直接到磁盘中去读，而是将磁盘数据加载到内存，然后从内存中提取数据。磁盘和内存的读写速度完全不同，内存的读写速度要比磁盘快很多。如果我们把所有数据都加载到内存，那么我们的读写效率会快很多，但同时我们也会面对另一个问题，所有的数据都存在内存当中，一旦断电，所有的操作都会丢失。因此我们还是需要持久化放到磁盘，但是我们可以在取数据的时候把一部分重要的数据优先读取到内存。

如果我要从数据库当中取一行记录，数据库并不是只从磁盘中取一行数据加载到内存，而是将磁盘中某一块或者某一个单位的数据，这个单位我们称之为页，而页的大小是与整个操作系统相关的，一般是4k或8k。主存和磁盘以页为单位发生数据交换。

为什么MySQL选择B+树

B+树更适合范围查询，主要是因为B+树的叶子节点之间有指针连接，并且按照关键字的大小顺序存储，这样可以支持快速的范围查询。

当进行范围查询时，MySQL会从B+树的根节点开始，按照关键字的大小顺序遍历B+树，找到范围查询的起始位置，然后沿着叶子节点之间的指针进行遍历，直到找到范围查询的结束位置。由于B+树的叶子节点之间有指针连接，并且按照关键字的大小顺序存储，因此可以快速地定位到范围查询的起始位置和结束位置，从而支持快速的范围查询。

相比之下，B树的叶子节点不一定按照关键字的大小顺序存储，并且叶子节点之间没有指针连接，因此进行范围查询时，需要进行更多的磁盘I/O操作，效率较低。

总之，B+树的叶子节点之间有指针连接，并且按照关键字的大小顺序存储，这使得B+树更适合范围查询。

Hash索引的缺点

1.用hash存储需要将所有数据的文件加载到内存，如果索引列的值非常大，就会占用大量的内存，这是哈希索引的一个局限。

2.不支持范围查询和排序：由于哈希索引是根据哈希函数进行查找的，因此不支持范围查询和排序操作，这是哈希索引的一个重要局限。

3.哈希冲突：哈希函数可能会将多个不同的索引列值映射为相同的哈希值，这就是哈希冲突。哈希冲突会导致哈希表中出现链表，降低了查找效率。

二叉树、红黑树的缺点

无论是二叉树还是红黑树，都会因为树的深度过深而造成I次数变多，影像数据读取的效率。它们在存储的时候存储的就是一个值，但是我们每次从磁盘取数据都是以页为单位，而且MySQL默认每次从磁盘中取16k的数据，每次都取16k的数据，里面存放一个值因此它们的存储空间占用比B+树更大。这对于需要存储大量数据的系统来说，可能会成为一个问题。

B树的原理图

三层b树可以存储16*16？*16=4096条数据

B+树的原理图 

B树的叶子节点存储了关键字和对应的数据指针，而B+树的叶子节点只存储了关键字，而不存储对应的数据指针。依旧按照每个磁盘块16k来计算，假设每个关键字占10b内存，每个磁盘块能存储的数据量可以达到16k/10b=1600个数据。三层b+树可以存储1600*1600*16=40960000条数据。

这种算法并不准确，但是我们可以估算出B+树的存储量相比于B树是指数型增长的

聚簇索引与非聚簇索引

聚簇索引：数据和索引存放在一起，比如Innodb

非聚簇索引：数据和索引不存放在一起，比如myisam

查询过程：

比如：

select * from t where name='lisi';

对于聚集索引，直接定位行记录

对于普通索引，需要遍历两遍索引树，如下图所示，先通过普通索引定位到id，再通过聚集索引定位到行记录。

这种先定位主键值，再定位行记录的查询方法，称为回表查询

 索引覆盖

覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

比如下面两条SQL语句：

SELECT * FROM table WHERE name=？;
SELECT id FROM table WHERE name=?;

相比于第一条语句，第二条语句执行时可以根据二级索引的B+树直接获取id值，不需要进行回表，这就是索引覆盖。

索引下推

索引下推（Index Condition Pushdown）是MySQL数据库的一项优化技术，它可以在执行查询时，将WHERE子句中的条件下推到存储引擎层面进行过滤，减少了从存储引擎中获取数据的数量，从而提高了查询性能。

具体而言，当MySQL执行一条SELECT语句时，首先会根据WHERE子句中的条件从表中获取数据。如果表上有索引，MySQL会先使用索引定位到符合条件的记录，然后再从存储引擎中获取数据。在这个过程中，MySQL会将所有的数据都获取到内存中，然后再进行过滤。而索引下推技术则是将WHERE子句中的条件下推到存储引擎层面进行过滤，只将符合条件的数据返回给MySQL，从而减少了MySQL从存储引擎中获取数据的数量，提高了查询性能。

我们以这样一句SQL举例：

SELECT * FROM table WHERE name=? and age=?;

如果没有索引下推：

MySQL先从存储引擎中拉取数据（根据name筛选的数据），再MySQL sever根据age进行数据筛选

有索引下推：MySQL会在拉取数据的时候直接根据name，age来获取数据，不需要server做任何的数据。

也就是把原本在服务层的数据筛选下推到存储引擎层进行 

需要注意的是，索引下推只对某些类型的查询有效，例如使用B-Tree索引的查询，而不适用于HASH索引或FULLTEXT索引。此外，索引下推在某些情况下可能会导致查询性能下降，因此需要根据具体情况进行评估和优化。

最左匹配原则

在我们使用联合索引进行查询的时候，MySQL会先从最左端的索引开始匹配，例如我们建立的索引为（name,age），那么在走索引的时候，MySQL会先根据name进行匹配，name匹配到了，才会去匹配age。如果name没有匹配到，就不会去匹配age

看下面的四条SQL语句：

SELECT * FROM table WHERE name=? and age=?;
SELECT * FROM table WHERE name=? ;
SELECT * FROM table WHERE age=?;
SELECT * FROM table WHERE age=? and name=?;

如果我们建立了（name,age）的联合索引，在这四条查询语句当中，1,2,4会走索引

1,2句很明显先匹配那么，那为什么4也会先匹配name呢？

这是因为MySQL内部有优化器的机制

最左匹配原则的意义

MySQL最左匹配原则的存在是为了优化查询效率。在使用联合索引进行查询时，MySQL会优先匹配最左边的列，然后再依次向右匹配。这意味着，如果查询条件中没有使用最左边的列，那么该列上的索引将不会被使用，会降低查询效率。

最左匹配原则的存在是为了鼓励开发人员设计更加有效的索引，以提高查询效率。通过合理地设计索引，可以使查询条件能够充分利用索引的优势，避免全表扫描，从而提高查询效率。

此外，最左匹配原则还可以帮助开发人员避免不必要的索引创建。如果索引中的列没有被查询条件使用，那么该索引就不会被使用，因此创建该索引就是浪费资源。通过遵循最左匹配原则，可以避免创建不必要的索引，从而提高数据库的性能和可维护性。

MySQL优化器

MySQL的优化器有两种选择模式，CBO（基于成本优化）和RBO（基于规则优化），现在大部分数据库都会选择CBO。就是采用效率最高的方式来进行执行，所以运行第四条SQL语句的时候，MySQL会把我们当前的查询进行优化，会将name和age的顺序进行调整。

索引失效

MySQL索引失效的情况有很多，以下是几种比较常见的情况：

违反了最左匹配原则

这个在上面已经描述过，不再赘述

范围查询右边的列不能使用索引

在范围查询中,右边的列通常不能使用索引进行优化。

主要原因有以下两点:

1. 范围查询一般需要扫描索引树中从起点到终点之间的节点,而非直接定位到某个节点。因此右边列的范围扫描难以利用索引进行优化。
2. MySQL的B+树索引是最左前缀匹配,在范围查询右边的列上进行匹配通常无法利用索引进行查找。

例如:

SELECT * FROM table
WHERE col1 > 10 AND col2 < 20 AND col3 = 30

对col1进行范围查询时可以利用其索引,但是对col2和col3的范围查询和确定值查询通常无法用到索引。

一个例外的情况是,在联合索引(col1, col2, col3)的情况下,如果col1和col2列都能进行确定性查找,那么col3列仍可利用索引进行优化,因为这满足了最左前缀匹配规则。

所以对于范围查询,为了提高性能,应该尽量在左边的索引列上进行范围限定,而在右边列上进行确定性查找。

在索引列上进行运算操作（可能）

比如：

SELECT * FROM tb_user
    WHERE SUBSTRING(name,3,2)='科技';

这样查询就不会走索引，不过并不是所有类型的索引经过运算都会失效，主要可以分为以下几种情况:

1. 针对索引列进行数值运算,索引不会失效。比如对数字类型做加减乘除运算。
2. 对字符串类型索引列做函数转换,索引会失效,如UPPER, LOWER, LENGTH等。
3. 对字符串类型索引列做连接操作,不会失效,可以利用索引。
4. 在联合索引中,对中间列做运算,会导致索引右边部分失效。
5. 对索引进行类型转换,可能会造成部分或全部索引失效。
6. 如果运算结果被当做常量进行比较,那么索引也不会失效。

所以综上,并不是所有运算都会导致索引完全失效,关键要看运算的类型和影响范围。可以通过explain查看语句是否使用上了索引。了解运算对索引的影响可以帮助我们进行查询优化。

字符串不加单引号（可能）

这是因为在查询时，如果没有对字符串加单引号，MySQL的查询优化器会自动进行类型转换，导致索引失效。（只要在索引上发生任何类型转换，都会造成索引失效）

模糊查询（可能）

以%开头的Like模糊查询会导致索引失效。如果仅仅是尾部农户品牌，索引不会失效，如果是头部匹配，索引失效。