欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

深入理解MySQL索引

程序员文章站 2024-03-16 20:58:52
...

大家在看书的时候，肯定会注意到“目录”的存在，那目录的用处是什么呢？是的，帮助我们快速找到我们想要看的内容，比如说我要看《高性能MySQL》这本书中关于索引的知识，我通过目录就可以定位到我想看的内容所在的页数。索引就是一种“目录”，是一种加快数据查询的技术。

索引是对数据库中一个或多个值进行排序，以协助加快查询、更新数据表中的数据。

本文将从MySQL的架构层次出发，分析索引的底层实现，索引的使用和优化等等方面讨论索引

一、MySQL架构

1、MySQL的逻辑架构

深入理解MySQL索引

MySQL逻辑架构整体分为三层 :
1> 客户端 : 并非MySQL所独有，大多数基于网络的客户端、服务器的工具或者服务都有类似的架构，比如 : 连接处理、授权认证、安全等功能

2> server层: 包括查询解析、分析、优化、缓存、内置函数(比如 : 时间、数学、加密等函数)，所有的跨存储引擎的功能也在这一层实现 : 存储过程、触发器、视图等

3> 存储引擎 : 负责 MySQL 中的数据存储和提取，和GNU/ Linux 下的文件系统类似，每种存储引擎都有其优势和劣势，中间的服务层通过 API 与存储引擎通信，这些 API接口屏蔽不同存储引擎间的差异。存储引擎的架构师插件式的。

2、MySQL的查询过程

深入理解MySQL索引
MySQL 整个查询执行过程，总的来说分为 5 个步骤 :

1> 客户端向 MySQL 服务器发送一条查询请求

2>查询请求来到MySQL连接器，连接器进行校验账户、连接状态等信息

3> server层首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段

4> sql语句先后进入到分析器、优化器和执行器中，分析器分析语句，优化器优化语句，执行器让执行引擎执行语句

5> MySQL 根据执行计划，调用存储引擎的 API来执行查询

6> 将结果返回给客户端，同时缓存查询结果

其中，主要包括下面几个模块：

1>MySQL连接器：scoketserver，负责接收客户端的连接，校验账户密码、权限，维持连接状态等功能。例如：msql -h *** -p *** -u *** -p

2>MySQL查询缓存：为了提高数据命中率。非命中的数据则需要查询数据库，查询完，存入缓存。注：执行更新操作（增删改）都会清空缓存。

3>MySQL分析器： 分析器分别进行词法和语法分析：词法分析，分析select还是update 还是delete语句；语法分析，分析语法是否满足mysql语法。

4>MySQL优化器：优化器在表里有多个索引时，选择使用哪个索引；或者表（jion in）关联时，决定表的连接顺序。

5>MySQL执行器：执行优化过后的sql语句，执行前检查操作权限。

二、MySQL中索引的使用

创建索引

在创建表的时候添加索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
);
在创建表以后添加索引
ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);
根据索引查询

具体查询：

SELECT * FROM table_name WHERE column_1=column_2;(为column_1建立了索引)

或者模糊查询

SELECT * FROM table_name WHERE column_1 LIKE '三%'
删除索引
DROP INDEX my_index ON tablename；
或者
ALTER TABLE table_name DROP INDEX index_name;
查看表中的索引
SHOW INDEX FROM tablename
查看查询语句使用索引的情况
//explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';

三、索引的优缺点

索引的优点:

  1. 创建索引的主要目的是为了加快查询速度，不需要进行全表扫描，大大减少了服务器需要扫描的数据量；
  2. 通过唯一性索引可以保证数据的唯一性；
  3. 加速表和表之间的连接；
  4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；

索引的缺点：

  1. 占用物理空间。索引是一种数据结构，索引越多占用的空间越大；
  2. 索引的创建和维护都需要消耗时间和系统资源，这个成本随着数据量的增大而增大；
  3. 降低增删改的性能，增删改时会对索引进行增加、维护，影响操作的效率。

四、索引的分类

常见的索引类型有：

  • 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空；
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；
  • 唯一索引：唯一索引不允许两行具有相同的索引值，允许空值；
ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；
  • 单列索引：用表中的一列构建的索引；
ALTER TABLE 'table_name' ADD INDEX index_name('col')；
  • 组合索引：用表中多列组合构建的索引，多列值中不允许有空值；
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

根据底层实现，又可分为：B-Tree索引，hash索引，空间索引（R-Tree），全文索引

五、索引的实现

索引根据底层实现，可分为：B-Tree索引，hash索引，空间索引（R-Tree），全文索引。

1、hash索引

hash索引是继续哈希表实现的，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hashcode），哈希吗是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在MySQL中，只有Memory引擎显示支持哈希索引。

因为索引自身只需存储对应的hash值，索引索引的结构十分紧凑，这也让哈希索引查找速度非常快。

但是，哈希索引有它的限制：

  • 哈希索引数据并不是按索引值顺序存储，所以无法用于排序；
  • 哈希索引只支持等值比较查询，包括=，IN(),<=>。也不支持任何范围查找，例如where age > 20。

2、全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键次，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。全文索引更类似于搜索引擎做的事情，而不是where条件搜索。

3、B-Tree索引

人们谈论索引是，如果没有指明B-Tree索引，它使用B-Tree的数据结构（实际上大多数存储引擎使用的是B+Tree）（B+Tree查找的时间复杂度为lgN)。
深入理解MySQL索引

B+Tree树的叶子节点都有指向下一个叶子节点的指针，是按顺序存储的，所以B-Tree索引列也是是顺序存储的，因此很适合查找范围数据。

B-Tree索引能够加快访问数据的速度，是因为存储引擎不在需要进行全表扫描来获取需要的数据，取而代之从索引的根节点开始搜索（B+Tree查找的时间复杂度为lgN）。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过，比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎在叶子节点中找到对应的值，要么该数据不存在。

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀查找（最左前缀原则）。

4、空间数据索引（R-Tree）

MyISAM表支持空间索引，可以用作地理数据存储。和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时，可以有效地使用任意维度来组合查询。

聚簇索引和非聚簇索引

分析了MySQL的索引结构的实现原理，然后我们来看看具体的存储引擎怎么实现索引结构的，MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序

非聚簇索引的解释是:索引顺序与数据物理排列顺序无关

（这样说起来并不好理解，让人摸不着头脑，清继续看下文，并在插图下方对上述两句话有解释）

首先要介绍几个概念，在索引的分类中，我们可以按照索引的键是否为主键来分为“主索引”和“辅助索引”，使用主键键值建立的索引称为“主索引”，其它的称为“辅助索引”。因此主索引只能有一个，辅助索引可以有很多个。

MyISAM——非聚簇索引

MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址。

非聚簇索引的数据表和索引表是分开存储的。

非聚簇索引中的数据是根据数据的插入顺序保存。因此非聚簇索引更适合单个数据的查询。插入顺序不受键值影响。

只有在MyISAM中才能使用FULLTEXT索引。(mysql5.6以后innoDB也支持全文索引)

InnoDB——聚簇索引

聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值。因此主键的值长度越小越好，类型越简单越好。
聚簇索引的数据和主键索引存储在一起。
聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度。但是也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。
在InnoDB中，如果只需要查找索引的列，就尽量不要加入其它的列，这样会提高查询效率。

*使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

*因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间。

*聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

下图可以形象的说明聚簇索引和非聚簇索引的区别

深入理解MySQL索引

从上图中可以看到聚簇索引的辅助索引的叶子节点的data存储的是主键的值，主索引的叶子节点的data存储的是数据本身，也就是说数据和索引存储在一起，并且索引查询到的地方就是数据（data）本身，那么索引的顺序和数据本身的顺序就是相同的；

而非聚簇索引的主索引和辅助索引的叶子节点的data都是存储的数据的物理地址，也就是说索引和数据并不是存储在一起的，数据的顺序和索引的顺序并没有任何关系，也就是索引顺序与数据物理排列顺序无关。

五、高性能索引的策略

  1. 独立的列——索引列不能是表达式的一部分，也不能是函数的参数。例如：SELECT actor_id FROM actor WHERE actor_id + 1 = 5;或者SELECT actor_id FROM actor WHERE f(actor_id) = 5;
  2. 前缀索引和索引选择性——有时候需要索引很长的字符列，这会让索引变得很大且很慢。此时可以有两个策略，一个是自定义哈希索引，另一个就是前缀索引； 前缀索引能大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性（索引选择性——不重复的索引值和数据表记录总数的比值）；索引前缀长度的选择——计算法。例如：
SLELECT COUNT(DISTINCT city)/COUNT(*) AS sel1, COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel2, ...;

如果前缀的选择性接近sel1就可以使用了。有时候只看平均选择型也不靠谱，还需要做进一步判断。
缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描；
有时候也可以使用前缀索引——可将对应列的字符串反序存储，并创建前缀索引。

  1. 多列索引——为多列创建合适的索引
    多列索引。例如：key(col1, col2, col3)；
    MySQL5.0之后的版本引入了“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位表中的行；
    索引合并策略有时候是一种优化后的结果，但实际上更说明表上的索引建得很糟糕。
    当出现服务器对多个索引做相交操作时（多个AND），通常意味着需要一个包含相关列的多列索引，而不是多个独立的单列索引;
    当服务器需要对多个索引做联合操作时（多个OR），通常需要耗费大量的CPU和内存在算法的缓存、排序和合并上。

  2. 选择合适的索引顺序
    正确的索引顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要；
    索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY 、GROUP BY和DISTINCT等子句的查询需求；
    索引列顺序的选择——在不考虑分组和排序的情况下，将选择性最高的列放到索引最前面（经验法则）；
    避免随机I/O和排序；
    对于某些特殊用户和分组，避免其使用普通的索引查询。

  3. 聚簇索引——聚簇索引并不是一种单独的索引类型，而是一种数据存储方式

  4. 覆盖索引–尽可能避免回表问题，保证索引覆盖。索引确实是一种查找数据的高效方法，但是MySQL也可以使用索引来直接获取列的数据，这样就不需要读取数据行。如果索引的叶子节点已经包换要查询的数据，那还有什么必要再根据叶子节点的数据进行回表查询呢？（例如主键作为一级索引，该索引列作为二级索引，根据该索引列查询到数据的主键索引，再通过主键索引查找数据）如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

  5. 使用索引扫描排序——MySQL有两种方式可以生成有序结果：通过排序操作；按照索引顺序扫描。
    只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（升序/降序）都一样时，MySQL才能使用索引来对结果做排序；
    当查询需要关联多张表时，只有当ORDER BY子句引用的字段全部来自第一张表时，才能使用索引排序；
    ORDER BY子句中的字段需要满足索引的最左前缀的要求，才能使用索引排序；
    当索引的前导列为常量时，ORDER BY子句可以不满足索引的最左前缀要求也能使用索引排序。例如：

key(rental_date, inventory_id, customer_id)；... where rental_data='2018-01-08' ORDER BY inventory_id DESC;
  1. 压缩（前缀压缩）索引（略）

索引失效的情况：

1.有or必全有索引;

2.复合索引未用左列字段;

3.like以%开头;

4.需要类型转换;

5.where中索引列有运算;

6.where中索引列使用了函数;

7.如果mysql觉得全表扫描更快时（数据少）;

相关标签： MySQL索引

上一篇： 程序员的乐趣，生成自定义二维码，5行Python代码就搞定

下一篇： Spring-AOP @AspectJ进阶之切点复合运算

推荐阅读