Mysql索引总结

最新推荐文章于 2024-03-10 17:56:15 发布

徒步凉城-Jasper

最新推荐文章于 2024-03-10 17:56:15 发布

阅读量136

点赞数

分类专栏：数据库文章标签： Mysql 索引

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，
本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

MyISAM索引

MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
下图是MyISAM索引的原理图：

图 1

这里设表一共有三列，假设我们以Col1为主键，则图1是一个MyISAM表的主索引（Primary key）示意。
可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

图 2

InnoDB索引

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，
如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB索引实现虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，
索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，
这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，
因此InnoDB表数据文件本身就是主索引。

图 3

图3是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，
则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，
则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，
InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

图 4

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，
但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，
就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，
过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，
因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的
分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。下一章将具体讨论这些与索引有关的优化策略。
索引使用策略及优化MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。
本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础，
实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。
示例数据库为了讨论索引策略，需要一个数据量不算小的数据库作为示例。

InnoDB的主键选择与插入优化在使用InnoDB存储引擎时，如果没有特别的需要，
请永远使用一个与业务无关的自增字段作为主键。经常看到有帖子或博客讨论主键选择问题，
有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。
不论支持哪种论点，大多数论据都是业务层面的。如果从数据库索引优化角度看，
使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，
数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）
的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，
如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。如果表使用自增主键，
那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。
如下图所示：

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，
因此效率很高，也不会增加很多开销在维护索引上。如果使用非自增主键（如果身份证号或学号等），
由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，
此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，
得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

B树与B+树

看了很多讲B树和B+树的文章，大多都是围绕各自的特性讲的，
第一，树中每个结点最多含有m个孩子（m>=2）；第二，……我也是从这些文章里弄懂了各种树的联系与区别，
要真写，我可能还不如人家写得好。所以就在这里简明扼要的用几张图记录一下主要区别吧。

为了便于说明，我们先定义一条数据记录为一个二元组[key,data]，key为记录的键值，key唯一；data为数据记录除key外的数据。

B树

每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。
B+树

只有叶子节点存储data，叶子节点包含了这棵树的所有键值，叶子节点不存储指针。

后来，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针，这样一棵树成了数据库系统实现索引的首选数据结构。

原因有很多，最主要的是这棵树矮胖，呵呵。一般来说，索引很大，往往以索引文件的形式存储的磁盘上，索引查找时产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的时间复杂度。树高度越小，I/O次数越少。

那为什么是B+树而不是B树呢，因为它内节点不存储data，这样一个节点就可以存储更多的key。

在MySQL中，最常用的两个存储引擎是MyISAM和InnoDB，它们对索引的实现方式是不同的。

MyISAM

data存的是数据地址。索引是索引，数据是数据。
InnoDB

data存的是数据本身。索引也是数据。

了解了数据结构再看索引，一切都不费解了，只是顺着逻辑推而已。另加两种存储引擎的区别：

1、MyISAM是非事务安全的，而InnoDB是事务安全的
2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁
3、MyISAM支持全文类型索引，而InnoDB不支持全文索引
4、MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM
5、MyISAM表保存成文件形式，跨平台使用更加方便
6、MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择
7、InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。

原文

后记

索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，
那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，
如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。
短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，
那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；
尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%”
不会使用索引而like “aaa%”可以使用索引。

不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，
这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
关于这一点可以围观：一个单引号引发的MYSQL性能损失。