MySQL索引 专题

什么是索引

索引是存储引擎用于快速找到记录的一种数据结构，索引类似一本书的目录，我们可以快速的根据目录查找到我们想要的内容的所在页码，索引的优化应该是对查询性能优化最有效的手段了。

因此，首先你要明白的一点就是，索引它也是一个文件，它是要占据物理空间的。

MySQL的InnoDB的细粒度行锁，是它最吸引人的特性之一。

但是，如《InnoDB，5项最佳实践》所述，如果查询没有命中索引，也将退化为表锁。

InnoDB的细粒度锁，是实现在索引记录上的。

一，InnoDB的索引

InnoDB的索引有两类索引，聚集索引(Clustered Index)与普通索引(Secondary Index)。

InnoDB的每一个表都会有聚集索引：

(1)如果表定义了PK，则PK就是聚集索引；

(2)如果表没有定义PK，则第一个非空unique列是聚集索引；

(3)否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

为了方便说明，后文都将以PK说明。

 

索引的结构是B+树，这里不展开B+树的细节，说几个结论：

(1)在索引结构中，非叶子节点存储key，叶子节点存储value；

(2)聚集索引，叶子节点存储行记录(row)；

画外音：所以，InnoDB索引和记录是存储在一起的，而MyISAM的索引和记录是分开存储的。

B+树：B-Tree、B~树、平衡多路查找树(m叉树)

 

(3)普通索引，叶子节点存储了PK的值；

画外音：

所以，InnoDB的普通索引，实际上会扫描两遍：

第一遍，由普通索引找到PK；

第二遍，由PK找到行记录；

索引结构，InnoDB/MyISAM的索引结构，如果大家感兴趣，未来撰文详述。

 

举个例子，假设有InnoDB表：

t(id PK, name KEY, sex, flag);

表中有四条记录：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

以看到：

(1)第一幅图，id PK的聚集索引，叶子存储了所有的行记录；

(2)第二幅图，name上的普通索引，叶子存储了PK的值；

对于：

select * from t where name=’shenjian’;

(1)会先在name普通索引上查询到PK=1；

(2)再在聚集索引衫查询到(1,shenjian, m, A)的行记录；

 https://mp.weixin.qq.com/s/y_f2qrZvZe_F4_HPnwVjOw

继续回答星球水友提问：

沈老师，我听网上说，MySQL数据表，在数据量比较大的情况下，主键不宜过长，是不是这样呢？这又是为什么呢？ 
结论先行：
 身份证号id_code是一个比较长的字符串，每个索引都存储这个值【innodb中其它非主键索引都存储了主键的值，即没有存储记录详情】，在数据量大，内存珍贵的情况下，MySQL有限的缓冲区，存储的索引与数据会减少，磁盘IO的概率会增加。

这个问题嘛，不能一概而论：
（1）如果是InnoDB存储引擎，主键不宜过长；
（2）如果是MyISAM存储引擎，影响不大； 
先举个简单的栗子说明一下前序知识。 

假设有数据表：

t(id PK, name KEY, sex, flag);

 其中：
（1）id是主键;
（2）name建了普通索引; 

假设表中有四条记录：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

 如果存储引擎是MyISAM，其索引与记录的结构是这样的：

（1）有单独的区域存储记录(record)；
（2）主键索引与普通索引结构相同，都存储记录的指针（暂且理解为指针）；
画外音：
（1）主键索引与记录不存储在一起，因此它是非聚集索引(Unclustered Index)；
（2）MyISAM可以没有PK； MyISAM使用索引进行检索时，会先从索引树定位到记录指针，再通过记录指针定位到具体的记录。
画外音：不管主键索引，还普通索引，过程相同。

 InnoDB则不同，其索引与记录的结构是这样的：

（1）主键索引与记录存储在一起；
（2）普通索引存储主键（这下不是指针了）；
画外音：
（1）主键索引与记录存储在一起，所以才叫聚集索引(Clustered Index)；
（2）InnoDB一定会有聚集索引； 

InnoDB通过主键索引查询时，能够直接定位到行记录。 

但如果通过普通索引查询时，会先查询出主键，再从主键索引上二次遍历索引树。
 回归正题，为什么InnoDB的主键不宜过长呢？ 假设有一个用户中心场景，包含身份证号，身份证MD5，姓名，出生年月等业务属性，这些属性上均有查询需求。

最容易想到的设计方式是：

• 身份证作为主键

• 其他属性上建立索引

user(id_code PK,
id_md5(index),
name(index),
birthday(index));

 

 

此时的索引树与行记录结构如上：

• id_code聚集索引，关联行记录

• 其他索引，存储id_code属性值

 身份证号id_code是一个比较长的字符串，每个索引都存储这个值【innodb中其它非主键索引都存储了主键的值，即没有存储记录详情】，在数据量大，内存珍贵的情况下，MySQL有限的缓冲区，存储的索引与数据会减少，磁盘IO的概率会增加。

画外音：同时，索引占用的磁盘空间也会增加。 此时，应该新增一个无业务含义的id自增列：

• 以id自增列为聚集索引，关联行记录

• 其他索引，存储id值

user(id PK auto inc,
id_code(index),
id_md5(index),
name(index),
birthday(index));

 

 

如此一来，有限的缓冲区，能够缓冲更多的索引与行数据，磁盘IO的频率会降低，整体性能会增加。 
总结
（1）MyISAM的索引与数据分开存储，索引叶子存储指针，主键索引与普通索引无太大区别；
（2）InnoDB的聚集索引和数据行统一存储，聚集索引存储数据行本身，普通索引存储主键；
（3）InnoDB不建议使用太长字段作为PK（此时可以加入一个自增键PK），MyISAM则无所谓
https://mp.weixin.qq.com/s/JwrXkr0RirqLMRHLtk6INQ

数据库索引，到底是什么做的？

问题1. 数据库为什么要设计索引？

图书馆存了1000W本图书，要从中找到《架构师之路》，一本本查，要查到什么时候去？

于是，图书管理员设计了一套规则：

(1)一楼放历史类，二楼放文学类，三楼放IT类…

(2)IT类，又分软件类，硬件类…

(3)软件类，又按照书名音序排序…

以便快速找到一本书。

 

与之类比，数据库存储了1000W条数据，要从中找到name=”shenjian”的记录，一条条查，要查到什么时候去？

于是，要有索引，用于提升数据库的查找速度。

问题2. 哈希(hash)比树(tree)更快，索引结构为什么要设计成树型？

加速查找速度的数据结构，常见的有两类：

(1)哈希，例如HashMap，查询/插入/修改/删除的平均时间复杂度都是O(1)；

(2)树，例如平衡二叉搜索树，查询/插入/修改/删除的平均时间复杂度都是O(log(n))；

 

可以看到，不管是读请求，还是写请求，哈希类型的索引，都要比树型的索引更快一些，那为什么，索引结构要设计成树型呢？

画外音：80%的同学，面试都答不出来。

 

索引设计成树形，和SQL的需求相关。

对于这样一个单行查询的SQL需求：

select * from t where name=”shenjian”;

确实是哈希索引更快，因为每次都只查询一条记录。

画外音：所以，如果业务需求都是单行访问，例如passport，确实可以使用哈希索引。

 

但是对于排序查询的SQL需求：

• 分组：group by

• 排序：order by

• 比较：<、>

• …

哈希型的索引，时间复杂度会退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率。

任何脱离需求的设计都是耍流氓。

多说一句，InnoDB并不支持哈希索引。

问题3. 数据库索引为什么使用B+树？

为了保持知识体系的完整性，简单介绍下几种树。

第一种：二叉搜索树

 

二叉搜索树，如上图，是最为大家所熟知的一种数据结构，就不展开介绍了，它为什么不适合用作数据库索引？

(1)当数据量大的时候，树的高度会比较高，数据量大的时候，查询会比较慢；

(2)每个节点只存储一个记录，可能导致一次查询有很多次磁盘IO；

画外音：这个树经常出现在大学课本里，所以最为大家所熟知。

 

第二种：B树

 

B树，如上图，它的特点是：

(1)不再是二叉搜索，而是m叉搜索；

(2)叶子节点，非叶子节点，都存储数据；

(3)中序遍历，可以获得所有节点；

画外音，实在不想介绍这个特性：非根节点包含的关键字个数j满足，(┌m/2┐)-1 <= j <= m-1，节点分裂时要满足这个条件。

B树被作为实现索引的数据结构被创造出来，是因为它能够完美的利用“局部性原理”。

 

什么是局部性原理？

局部性原理的逻辑是这样的：

(1)内存读写快，磁盘读写慢，而且慢很多；

(2)磁盘预读：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；

画外音：通常，一页数据是4K。

 

(3)局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO；

 

B树为何适合做索引？

(1)由于是m分叉的，高度能够大大降低；

(2)每个节点可以存储j个记录，如果将节点大小设置为页大小，例如4K，能够充分的利用预读的特性，极大减少磁盘IO；

 

第三种：B+树

B+树，如上图，仍是m叉搜索树，在B树的基础上，做了一些改进：

(1)非叶子节点不再存储数据，数据只存储在同一层的叶子节点上；

画外音：B+树中根到每一个节点的路径长度一样，而B树不是这样。

(2)叶子之间，增加了链表，获取所有节点，不再需要中序遍历；

这些改进让B+树比B树有更优的特性：

(1)范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯；

画外音：范围查询在SQL中用得很多，这是B+树比B树最大的优势。

(2)叶子节点存储实际记录行，记录行是相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储；

(3)非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引；

最后，量化说下，为什么m叉的B+树比二叉搜索树的高度大大大大降低？

大概计算一下：

(1)局部性原理，将一个节点的大小设为一页，一页4K，假设一个KEY有8字节，一个节点可以存储500个KEY，即j=500

(2)m叉树，大概m/2<= j <=m，即可以差不多是1000叉树

(3)那么：

一层树：1个节点，1*500个KEY，大小4K

二层树：1000个节点（第一层的第个Key连接第二层的两个节点），1000*500=50W个KEY，大小1000*4K=4M

三层树：1000*1000个节点，1000*1000*500=5亿个KEY，大小1000*1000*4K=4G

画外音：额，帮忙看下有没有算错。

可以看到，存储大量的数据（5亿），并不需要太高树的深度（高度3），索引也不是太占内存（4G）。

总结

• 数据库索引用于加速查询

• 虽然哈希索引是O(1)，树索引是O(log(n))，但SQL有很多“有序”需求，故数据库使用树型索引

• InnoDB不支持哈希索引

• 数据预读的思路是：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，以便未来减少磁盘IO

• 局部性原理：软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO

• 数据库的索引最常用B+树：

(1)很适合磁盘存储，能够充分利用局部性原理，磁盘预读；

(2)很低的树高度，能够存储大量数据；

(3)索引本身占用的内存很小；

(4)能够很好的支持单点查询，范围查询，有序性查询；

https://mp.weixin.qq.com/s/D-1_X-TVfeHKAC6zu3qWPw

 
补充下二叉树相关的东西：

BST、AVL、RBT、B-tree都是动态结构，查找时间基本都在O(longN)数量级上。下面做出详细对比。

1. 二叉查找树 (Binary Search Tree)
概念
二叉查找树又称二叉搜索树，二叉排序树，特点如下:
1. 左子树上所有结点值均小于根结点
2. 右子树上所有结点值均大于根结点
3. 结点的左右子树本身又是一颗二叉查找树
4. 二叉查找树中序遍历得到结果是递增排序的结点序列。

BST 的操作代价分析：
(1) 查找代价：
任何一个数据的查找过程都需要从根结点出发，沿某一个路径朝叶子结点前进。因此查找中数据比较次数与树的形态密切相关。
当树中每个结点左右子树高度大致相同时，树高为logN。则平均查找长度与logN成正比，查找的平均时间复杂度在O(logN)数量级上。
当先后插入的关键字有序时，BST退化成单支树结构。此时树高n。平均查找长度为(n+1)/2，查找的平均时间复杂度在O(N)数量级上。

(2) 插入代价：
新结点插入到树的叶子上，完全不需要改变树中原有结点的组织结构。插入一个结点的代价与查找一个不存在的数据的代价完全相同。

(3) 删除代价：
当删除一个结点P，首先需要定位到这个结点P，这个过程需要一个查找的代价。然后稍微改变一下树的形态。如果被删除结点的左、右子树只有一个存在，则改变形态的代价仅为O(1)。如果被删除结点的左、右子树均存在，只需要将当P的左孩子的右孩子的右孩子的…的右叶子结点与P互换，在改变一些左右子树即可。因此删除操作的时间复杂度最大不会超过O(logN)。

BST效率总结 :
查找最好时间复杂度O(logN)，最坏时间复杂度O(N)。
插入删除操作算法简单，时间复杂度与查找差不多。

2. 平衡二叉查找树 ( Balanced Binary Search Tree )
二叉查找树在最差情况下竟然和顺序查找效率相当，这是无法仍受的。事实也证明，当存储数据足够大的时候，树的结构对某些关键字的查找效率影响很大。当然，造成这种情况的主要原因就是BST不够平衡(左右子树高度差太大)。既然如此，那么我们就需要通过一定的算法，将不平衡树改变成平衡树。因此，AVL树就诞生了。

AVL 的操作代价分析：
(1) 查找代价：
AVL是严格平衡的BST（平衡因子不超过1）。那么查找过程与BST一样，只是AVL不会出现最差情况的BST(单支树)。因此查找效率最好，最坏情况都是O(logN)数量级的。

(2) 插入代价：
AVL必须要保证严格平衡(|bf|<=1)，那么每一次插入数据使得AVL中某些结点的平衡因子超过1就必须进行旋转操作。事实上，AVL的每一次插入结点操作最多只需要旋转1次(单旋转或双旋转)。因此，总体上插入操作的代价仍然在O(logN)级别上(插入结点需要首先查找插入的位置)。

(3) 删除代价：
AVL删除结点的算法可以参见BST的删除结点，但是删除之后必须检查从删除结点开始到根结点路径上的所有结点的平衡因子。因此删除的代价稍微要大一些。每一次删除操作最多需要O(logN)次旋转。因此，删除操作的时间复杂度为O(logN)+O(logN)=O(2logN)

AVL 效率总结 :
查找的时间复杂度维持在O(logN)，不会出现最差情况
AVL树在执行每个插入操作时最多需要1次旋转，其时间复杂度在O(logN)左右。
AVL树在执行删除时代价稍大，执行每个删除操作的时间复杂度需要O(2logN)。

3. 红黑树 (Red-Black Tree )
二叉平衡树的严格平衡策略以牺牲建立查找结构(插入，删除操作)的代价，换来了稳定的O(logN) 的查找时间复杂度。但是这样做是否值得呢？
能不能找一种折中策略，即不牺牲太大的建立查找结构的代价，也能保证稳定高效的查找效率呢？
答案就是：红黑树。

RBT 的操作代价分析：
(1) 查找代价：
由于红黑树的性质(最长路径长度不超过最短路径长度的2倍)，可以说明红黑树虽然不像AVL一样是严格平衡的，但平衡性能还是要比BST要好。其查找代价基本维持在O(logN)左右，但在最差情况下(最长路径是最短路径的2倍少1)，比AVL要略逊色一点。

(2) 插入代价：
RBT插入结点时，需要旋转操作和变色操作。但由于只需要保证RBT基本平衡就可以了。因此插入结点最多只需要2次旋转，这一点和AVL的插入操作一样。虽然变色操作需要O(logN)，但是变色操作十分简单，代价很小。

(3) 删除代价：
RBT的删除操作代价要比AVL要好的多，删除一个结点最多只需要3次旋转操作。

RBT 效率总结 :
查找 效率最好情况下时间复杂度为O(logN)，但在最坏情况下比AVL要差一些，但也远远好于BST。
插入和删除操作改变树的平衡性的概率要远远小于AVL（RBT不是高度平衡的）。因此需要的旋转操作的可能性要小，而且一旦需要旋转，插入一个结点最多只需要旋转2次，删除最多只需要旋转3次(小于AVL的删除操作所需要的旋转次数)。虽然变色操作的时间复杂度在O(logN)，但是实际上，这种操作由于简单所需要的代价很小。

4. B~树/B+树 (B-Tree )
对于在内存中的查找结构而言，红黑树的效率已经非常好了(实际上很多实际应用还对RBT进行了优化)。但是如果是数据量非常大的查找呢？将这些数据全部放入内存组织成RBT结构显然是不实际的。实际上，像OS中的文件目录存储，数据库中的文件索引结构的存储…. 都不可能在内存中建立查找结构。必须在磁盘中建立好这个结构。那么在这个背景下，RBT还是一种好的选择吗？
在磁盘中组织查找结构，从任何一个结点指向其他结点都有可能读取一次磁盘数据，再将数据写入内存进行比较。大家都知道，频繁的磁盘IO操作，效率是很低下的(机械运动比电子运动要慢不知道多少)。显而易见，所有的二叉树的查找结构在磁盘中都是低效的。因此，B树很好的解决了这一个问题。

B-Tree的操作代价分析：
(1) 查找代价：
B-Tree作为一个平衡多路查找树(m-叉)。B树的查找分成两种：一种是从一个结点查找另一结点的地址的时候，需要定位磁盘地址(查找地址)，查找代价极高。另一种是将结点中的有序关键字序列放入内存，进行优化查找(可以用折半)，相比查找代价极低。而B树的高度很小，因此在这一背景下，B树比任何二叉结构查找树的效率都要高很多。而且B+树作为B树的变种，其查找效率更高。

(2)插入代价：
B-Tree的插入会发生结点的分裂操作。当插入操作引起了s个节点的分裂时，磁盘访问的次数为h(读取搜索路径上的节点)＋2s(回写两个分裂出的新节点)＋1（回写新的根节点或插入后没有导致分裂的节点）。因此，所需要的磁盘访问次数是h+2s+1，最多可达到3h+1。因此插入的代价是很大的。

(3)删除代价：
B-Tree的删除会发生结点合并操作。最坏情况下磁盘访问次数是3h＝（找到包含被删除元素需要h次读访问）+（获取第2至h层的最相邻兄弟需要h-1次读访问）+（在第3至h层的合并需要h-2次写访问）+（对修改过的根节点和第2层的两个节点进行3次写访问）。

(4)定义：
一颗m阶（m>=3，即一个结点包含的数据和子节点数），3阶B-树有如下特点：
1. 根结点之多3颗子树
2. 定义：

define m 3                 /*B 树的阶*/  
typedef struct Node{  
    int keynum;             /* 结点中关键码的个数，即结点的大小*/  
    int key[m];               /*结点数据数组*/  
    struct Node *parent;  /*指向父节点的指针*/  
    Node*son[m];    /*指向子结点的指针数组*/  
};

B-Tree效率总结：
由于考虑磁盘储存结构，B树的查找、删除、插入的代价都远远要小于任何二叉结构树(读写磁盘次数的降低)。

动态查找树结构的对比：
(1) 平衡二叉树和红黑树 [AVL PK RBT]
AVL 和RBT 都是二叉查找树的优化。其性能要远远好于二叉查找树。他们之间都有自己的优势，其应用上也有不同。
结构对比： AVL的结构高度平衡，RBT的结构基本平衡。平衡度AVL > RBT.
查找对比： AVL 查找时间复杂度最好，最坏情况都是O(logN)。RBT 查找时间复杂度最好为O(logN)，最坏情况下比AVL略差。
插入删除对比：
1. AVL的插入和删除结点很容易造成树结构的不平衡，而RBT的平衡度要求较低。因此在大量数据插入的情况下，RBT需要通过旋转变色操作来重新达到平衡的频度要小于AVL。
2. 如果需要平衡处理时，RBT比AVL多一种变色操作，而且变色的时间复杂度在O(logN)数量级上。但是由于操作简单，所以在实践中这种变色仍然是非常快速的。
3. 当插入一个结点都引起了树的不平衡，AVL和RBT都最多需要2次旋转操作。但删除一个结点引起不平衡后，AVL最多需要logN 次旋转操作，而RBT最多只需要3次。因此两者插入一个结点的代价差不多，但删除一个结点的代价RBT要低一些。
4. AVL和RBT的插入删除代价主要还是消耗在查找待操作的结点上。因此时间复杂度基本上都是与O(logN) 成正比的。
总体评价：大量数据实践证明，RBT的总体统计性能要好于平衡二叉树。

(2) B-树和B+树 [ B-Tree PK B+Tree]
B+树是B-树的一种变体，在磁盘查找结构中，B+树更适合文件系统的磁盘存储结构。
结构对比：
B-树是平衡多路查找树，所有结点中都包含了待查关键字的有效信息(比如文件磁盘指针)。每个结点若有n个关键字，则有n+1个指向其他结点的指针。

B+树相比B-树的特点：
1. 数据只出现在叶子结点，B-树每个结点都包含了数据；
2. 叶子结点之间用指针连接；
3. B+树的高度一般是3；

查找对比：
1. 在相同数量的待查数据下，B+树查找过程中需要调用的磁盘IO操作要少于普通B-树。由于B+树所在的磁盘存储背景下，因此B+树的查找性能要好于B-树。
2. B+树的查找效率更加稳定，因为所有叶子结点都处于同一层中，而且查找所有关键字都必须走完从根结点到叶子结点的全部历程。因此同一颗B+树中，任何关键字的查找比较次数都是一样的。而B树就不一定了，可能查找到某一个非终结点就结束了。
插入删除对比： B+树与B-树在插入删除操作中的效率是差不多的。
总体评价：在应用背景下，特别是文件结构存储中。B+树的应用要更多，其效率也要比B-树好。
---------------------
作者：奔跑的小河
来源：CSDN
原文：https://blog.csdn.net/z702143700/article/details/49079107
版权声明：本文为博主原创文章，转载请附上博文链接！

 https://hxraid.iteye.com/blog/611105

 

首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。
如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。
https://www.cnblogs.com/xxiaoye/p/3679899.html

添加索引的话，首先去索引列表中查询，而我们的索引列表是B+树【BTREE,Balance Tree,B+树是从最早的平衡二叉树演化而来，但B+树不是一个二叉树】的数据结构，查询的时间复杂度为O(log₂N)，定位到特定值得行就会非常快，所以其查询速度就会非常快。
时间复杂度O(log₂N)的来源，因为BTREE是一个平衡二叉树，元素个数是2的（N-1）【N-1，是因为根节点是2的0次方】次方个，时间复杂度是对这个指数求导，即为log₂N-1 

Digest:
索引在文件系统中的体现：
1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找；
2. 索引存在于磁盘中，会占据物理空间。因此不恰当的索引会影响性能

存储引擎与索引类型的区别：
1. 不同的存储引擎可能支持不同的索引类型；
2. 不同的存储引擎对同一种索引类型可能有不同的实现方式。

存储引擎支持的几种常见索引：
1. B-Tree索引使用最广泛，主流引擎都支持。
2. 哈希索引性能高，适用于特殊场合。
3. R-Tree不常用。
4. 全文索引适用于海量数据的关键字模糊搜索。

只不过又有各自的特点。

索引类型和索引算法关系。
Mysql中三种索引类型 Normal、Unique、Full Text都可以选择使用BTREE和HASH算法

索引 在文件系统中的体现：

比如对于MyISAM存储引擎来说:
.frm后缀的文件存储的是表结构；
.myd后缀的文件存储的是表数据；
.myi后缀的文件存储的就是索引文件。
如下图所示:

对于InnoDB 存储引擎来说:
.frm后缀的文件存储的是表结构。
.ibd后缀的文件存放索引文件和数据(需要开启innodb_file_per_table 参数)

如下图所示:

因此，当你对一张表建立索引时，索引文件的大小也会改变，当你数据表中的数据因为增删改变化时，索引文件也会变化的，只不过MySQL会自动维护索引，这个过程不需要你介入，
这也是为什么不恰当的索引会影响MySQL性能的原因。

总结:
1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找；
2. 索引存在于磁盘中，会占据物理空间。

索引的类型
上面说到，索引文件时按照不同的数据结构来存储的，数据结构的不同也产生了不同的索引类型，常见的索引类型包括:

B-Tree索引
哈希索引
空间数据索引(R-Tree)
全文索引
下面做一一介绍:

1. B-Tree索引
B-Tree索引是最常用的一种索引，如果没有指定特定的类型，那么多半就是B-Tree索引，事实上，很多搜索引擎使用的是它的变种B+Tree，这是对B-Tree的一个优化，如果需要详细了解，可以参考数据结构方面的书籍，这里不做详细探讨。
以下统称为B-Tree索引。
绝大多数的存储引擎，比如MyISAM和InnoDB都支持这种索引，因此说它是应用最广泛，最常用的一种索引方式，但是不同的存储引擎在具体实现时会稍有不同，比如MyISAM会使用前缀压缩的方式对索引进行压缩，InnoDB则不会。
下图展示了B-Tree索引是如何存储被索引的数据的:

说明:
左图是一个包含三列的数据表，右图则展示了数据是如何被索引的。
可以看出B-Tree是对索引列是按照顺序存储的，每个叶子节点指向被索引的数据，这也是B-Tree索引支持范围查找数据的原因。

2. 哈希索引
相比于B-Tree索引，哈希索引的实现就比较简单了，它是基于哈希表来实现的，对于要索引的列，存储引擎会计算出一一对应的哈希码，然后把哈希码存放在哈希表中作为key，value值是指向该行数据的指针。
下图是简单的原理展示:

说明:
1、左边紫色图表示一个二列的数据表。
2、中间表示对fname列进行哈希索引，计算出哈希值，因为位置不确定，所以不支持范围查找。
3、右边绿色图表示把生成的哈希值存放于哈希表中。

当我们执行以下查询时:

select * from testTable where fname = "mary";

 

MySQL会首先计算查询条件mary的哈希值，然后到哈希表中去找该哈希值，如果找到了根据对应的指针也就找到了需要寻找的数据行。

哈希表的优势与限制:

优势:
只需比对哈希值，因此速度非常快，性能优势明显；
限制:
不支持任何范围查询，比如where price > 150，因为是基于哈希计算，支持等值比较。
哈希表是无序存储的，因此索引数据无法用于排序。
主流存储引擎不支持该类型，比如MyISAM和InnoDB。哈希索引只有Memory, NDB两种引擎支持。
因此，哈希索引虽然速度快，但其实使用很受限，只适用于某些特殊的场合。

3. 空间数据索引(R-Tree)
空间索引可用于地理数据存储，它需要GIS相关函数的支持，由于MySQL的GIS支持并不完善，所以该索引方式在MySQL中很少有人使用。

4. 全文索引
全文索引主要用于海量数据的搜索，比如淘宝或者京东对商品的搜索，你不可能使用like进行模糊匹配吧，
MySQL从5.6开始支持InnoDB引擎的全文索引，功能没有专业的搜索引擎比如Sphinx或Solr丰富，如果你的需求比较简单，可以尝试一下MySQL的全文索引，否则建议使用专业的搜索引擎。

总结:
1. B-Tree索引使用最广泛，主流引擎都支持。
2. 哈希索引性能高，适用于特殊场合。
3. R-Tree不常用。
4. 全文索引适用于海量数据的关键字模糊搜索。

索引和存储引擎之间的关系
上面讲述了索引有不同的类型，存储引擎也有不同的类型，那么索引和存储引擎之间有什么关系呢？
首先你需要知道，在MySQL中，索引是在存储引擎中实现的，并不是所有的存储引擎都支持所有的索引类型，比如哈希索引，MyISAM和InnoDB是不支持的；
同样，即使对于同一类型的索引，不同的存储引擎实现的方式也可能是不同的，比如MyISAM和InnoDB对B-Tree索引，具体的实现是有差别的。

总结:
1. 不同的存储引擎可能支持不同的索引类型；
2. 不同的存储引擎对同一种索引类型可能有不同的实现方式。

B-Tree索引与唯一索引，主键索引，普通索引的关系
最开始对B-Tree索引与唯一索引，主键索引，普通索引这几种索引的关系很模糊，网上也没搜索到相关的资料，以为他们的关系是并列的，
其实并不是，B-Tree只是底层的算法实现，唯一索引，主键索引，普通索引都是基于B-Tree索引算法的，只不过又有各自的特点。
通过下图也可看出这种关系:

 

https://segmentfault.com/a/1190000010264071

 

索引的类型

索引是在MYSQL的存储引擎层中实现的，而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持同一种类型的索引，其底层实现也可能不同。

B-Tree索引是最常见的索引类型,也是创建索引时默认的类型。
B-Tree是一种多叉平衡树，B-Tree 结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。一般用于数据库的索引，综合效率较高。

等值匹配 可用于

=
!=
<>
IN
NOT IN
<=>

查询语句的优化 范围匹配

可用于

>
>=
<
<=
BTEWEEN AND

等范围查询语句的优化 匹配最左前缀

name like bai%

这种语句，是可以使用name字段上建立的索引来优化查询的，但是对于

name like %bai

则没有办法使用索引了 

覆盖索引

覆盖索引是指所有需要查询的字段都在索引已经存在了，那么就不需要再去查询数据了，这种查询效率很高。

select id where id >100

排序

BTREE索引还可以用于查询中的order by 操作。

哈希索引基于哈希表实现,只有Memory引擎显示支持哈希索引，使用哈希索引可以一次定位，所以 Hash 索引的查询效率要远高于 B-Tree 索引。但是哈希索引是有很多限制的:

• 只有精确匹配索引所有列的查询才有效，因为哈希索引是利用索引的所有列的字段值来计算哈希值的，
• 只支持等值比较查询，不能用于范围查询。
• 哈希索引的只包含索引字段的哈希值he和指向数据的指针，所以不能使用索引中的值来避免读取行。
• 哈希索引的数据并不是顺序存储的，无法用于排序。

全文索引

聚集索引&非聚集索引

聚集索引并不是一种单独的索引类型，而是一种数据存储方式，Innode的聚集索引实际上是将主键(PRIMARY kEY )与数据行存放在同一个文件的,一张表只能有一个聚集索引。

 

 

InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会用一个唯一且不为空的索引列做为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键。

InnoDB的普通索引(二级索引)的叶子节点中存放的是PRIMARY KEY的值,所以需要先查询普通索引(二级索引)的叶子节点找到对应的主键值，然后再根据主键值去聚集索引中查询到对应的数据。

InnoDB将主键与数据聚集在一起的方式，使得按主键顺序的插入和查询效率会很高，更新主键或者不按主键的顺序插入数据的代价会比较高，
所以主键的选取很重要(使用AUTO INCREMENT字段或者程序自己生成的顺序字段要比无序的UUID好的多)
二级索引会保存主键的值，所以主键的值不要太大。

非聚集索引的索引与数据是存在在不同文件的，对于MyISAM引擎的一张表，会有三种文件，FRM(表结构)、MYD（数据，就是数据库中的每个行）、MYI（索引）。
MySQl使用索引查询数据时，先到MYI文件中找出数据存储的位置指针，然后再到MYD文件中读取数据。

 

 

MyISAM中主键索引和其他索引在结构上没有什么不同，主键索引就是一个名为PRIMARY的唯一非空索引。

索引操作

创建在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

• CREATE TABLE 

• ALTER TABLE

  ALTER TABLE table_name ADD INDEX|KEY index_name (column1,[column2]...)
  ALTER TABLE table_name ADD UNIQUE [KEY|INDEX] (column1,[column2]...)
  ALTER TABLE table_name ADD PRIMARY KEY|INDEX (column1,[column2]...)

   

• CREATE INDEX

  CREATE KEY|INDEX index_name ON table_name (column_list)
  CREATE UNIQUE KEY|INDEX index_name ON table_name (column_list)

    

查看 

SHOW INDEX FROM tableName

 

高效索引策略

参考文献

MySQL索引背后的数据结构及算法原理 MYSQL-索引 Mysql索引分析

http://www.tuicool.com/articles/zmiM3mB

 

MyISAM存储引擎

• MyISAM 的索引不论是 Primary Key 还是普通 Index，存储结构都基本一样，基本结构都是B-Tree；
• MyISAM一个表生成3个文件，分别是（如表：test）：test.frm(表结构)、test.MYD（数据，就是数据库中的每个行）、test.MYI（索引）；
• mysql服务器会先到test.MYI文件中找出数据存储的位置指针 (主键和普通索引结构一致，也就是说在索引文件的所有结构中的所有结点都不存真实数据，分支结点存索引主键或普通索引，叶子结点存 索引值+数据物理地址指针)，根据索引文件中叶子存放的指针地址再到test.MYD中取出数据。

• MyISAM主键索引原理：

  
  

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

• MyISAM普通索引原理: 如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

•  

  
• MyISAM-2
  • 同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

• MyISAM的索引方式也叫做 非聚集 的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB存储引擎

• 虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
• InnoDB的数据文件本身就是索引文件
• 表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
• InnoDB主健索引原理：

 

InnoDB-1

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

 

 

• InnoDB-2
  • 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引（普通索引）搜索需要 检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录

什么是聚集索引和非聚集索引？

数据记录本身被存于索引（一颗B+Tree）的叶子节点上，这种索引叫聚集索引，InnoDB使用聚集索引。
反之则为非聚集索引，MyISAM使用非聚集索引，它的叶子节点的data域指向数据记录的地址，而不是存储数据记录本身；

聚集索引还需注意：

1. 如果声明了主键(primary key)，则这个列会被做为聚集索引。
2. 如果没有声明主键，则会用一个唯一且不为空的索引列做为主键，成为此表的聚集索引。
3. 上面二个条件都不满足，InnoDB会自己产生一个虚拟的字段作为聚集索引，这个字段长度为6个字节，类型为长整形。

MySql有几种索引？

• 主键索引；
• 唯一索引；
• 普通索引；
• 联合索引；
• 全文索引。

参考资料

MySQL索引背后的数据结构及算法原理

http://www.jianshu.com/p/31eb5e4483c8

 

InnoDB存储

表空间是逻辑存放所有数据的地方，默认情况下会共享一个表空间——ibdata1，但如果把innodb_file_per_table=ON后每张表可以单独放到一个表空间内，但还是有很多数据保存在共享的表ibdata1中，如undo信息等。

表空间由各种段(segment)组成，常见的段有数据段、索引段等。Innodb是索引组织的，数据段就是clustered index的叶结点。需要注意的是，不是每个对象都有段。

区(extend)是由64个连续的页组成，每个页（page）固定为16KB，所以每个区总共为1M。页是InnoDB最小的磁盘管理单位。

InnoDB是按行进行存放的，每个区最少可以保存2条记录，否则就成链式结构了。每行数据除了自定义列以外，还会增加事务id和回滚指针列。如果没有定义primary key也没有not null的unique,则会增加6字节的RowId列作为主键。

        

            图片来自：http://www.cnblogs.com/chjw8016/archive/2011/03/08/1976891.html

 

InnoDB表的限制

一个表不能包含超过1000列。

内部最大键长度是3500字节，但MySQL自己限制这个到1024字节。

除了VARCHAR, BLOB和TEXT列，最大行长度稍微小于数据库页的一半。即，最大行长度大约8000字节。LONGBLOB和LONGTEXT列必须小于4GB, 总的行长度，页包括BLOB和TEXT列，必须小于4GB。InnoDB在行中存储VARCHAR，BLOB或TEXT列的前768字节，余下的存储的分散的页中。

虽然InnoDB内部地支持行尺寸大于65535，你不能定义一个包含VARCHAR列的，合并尺寸大于65535的行。

mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
    -> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
    -> f VARCHAR(10000), g VARCHAR(10000));

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

 在一些更老的操作系统上，数据文件必须小于2GB。

 InnoDB日志文件的合并尺寸必须小于4GB。

最小的表空间尺寸是10MB。最大的表空间尺寸是4,000,000,000个数据库页（64TB）。这也是一个表的最大尺寸。

 InnoDB表不支持FULLTEXT索引

 InnoDB索引

默认情况下Memory使用存储hash索引，但也支持b+tree索引。
Hash索引只用于=或者<=>的等式比较，不能用来加速order by操作，只能通过关键字来搜索一行。innodb只支持b+树索引，进一步分为clustered index与secondary index。
在一次查询中，只能使用一个索引。

InnoDB是索引组织表，clustered index的叶结点保存着整行的数据。
如果，定义了primary key，则clustered index就是primary key的索引；
如果没有定义primary key mysql会选中第一个仅有not null列的unique索引作为主键，并把此索引当作clustered index使用；
如果没找到这样的列，innodb会创建一个6字节的RowId作为主键。所以每张表有且只有一个clustered index。

Secondary index的叶结点不包括行的全部数据，包含键值以外还包括一个bookmark，可以告诉innodb到什么地方可以找到相对应的完整行数据，还保存了主键的健值。
Secondary index包含主键，但不包含完整的行数据，所以innodb总是会先从secondary index的叶节点判断是否能得到所需的数据。
如:

Create table t(a int, b varchar(20), primary key(a), key(b));

Explain select * from t;

会发现mysql选择了索引b，而不是a.

复合索引

复合索引是在多列（>=2）上建立的索引，又叫多列索引或联合索引。Innodb中的复合索引也是b+ tree结构。索引的数据包含多列(col1, col2, col3…)，在索引中依次按照col1, col2, col3排序。如(1, 2), (1, 3),(2,0)…

使用复合索引要充分利用最左前缀原则，顾名思义，就是最左优先。
如创建索引ind_col1_col2(col1, col2)，那么在查询where col1 = xxx and col2 = xx或者where col1 = xxx都可以走ind_col1_col2索引。

在创建多列索引时，要根据业务需求，where子句中使用最频繁且过滤效果好的的一列放在最左边。

索引操作

可以通过DML语句操作innodb索引。
因为innodb是索引组织的表，对索引的操作会造成锁表，先生成一张临时表，将数据从原始表中写到临时表，再将原始表删除，最后将临时表表名改为原始表表名！
因增加、删除、修改字段会对主索引产生影响，所以也会锁表。
对secondary index从Innodb plugin开始，支持快速索引创建的方法，在创建的过程中不需要重建表，所以速度会很快，同时引擎会在表上加S锁，在创建过程中只能进行读操作。

索引设计原则

1.搜索的索引列，不一定是所要选择的列。也就是说，最适合索引的列是出现在where子句中的列，或者连接子句中指定的列，而不是出现在select关键字后的选择列表中的列。
2.使用唯一索引。考虑某列的分布，索引的列的基数越大，索引的效果越好。例如，对性别M/F列做索引没多大用处。
3.使用短索引。如果是对字符串进行索引，如果有可能应该指定前缀长度。
4.利用最左前缀。尽量将使用频繁且过滤效果好的字段放“左边”
5.不要过度索引。
6.Innodb默认会按照一定的顺序保存数据，如果明确定义了主键，则按照主键顺序保存。如果没有主键，但有唯一索引，就按照唯一索引的顺序保存。

如果有几个列都是唯一的，都可以作为主键的时候，为了提高查询效率，应选择最常用访问的列作为主键。

另外，innodb的secondary index都会保存主键的键值，所有主键要尽可能选择较短的数据类型。可以看出，应当尽量避免对主键的修改。经过dba的测试，保证主键的递增可以提高插入性能。

Mysql如何使用索引

1.对于创建的多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用。

2.对于使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能被使用。

3.如果对大文本进行搜索，应该使用全文索引，而不是使用like ‘%...%’. 但不幸的是innodb不支持全文索引。

4.如果列名是索引，使用 index_column is null将使用索引。Oracle是不行的。

5.如果mysql估计使用索引比全表扫描更慢，最不会使用索引。

6.如果使用memory/head表并且where条件中不使用”=”进行索引列，那么不会用到索引。Head表只有在”=”的时候才会使用索引。

7.用or分割开的条件，如果or前的条件中的列有索引，而后面列中没有索引，那么涉及到的索引都不会被用到。

8.不是多列索引的第一部分不会走索引。

9.以%开始的like不会走索引

10.如果列是字符串，那么一定要在where条件中把字符串常量值用引号引起来，否则不能走索引。因为，mysql默认把输入的常量值进行转换以后才进行检索。

11.经过普通运算或函数运算后的索引字段不能使用索引

12.Order by 优化：某些情况下，mysql可以使用一个索引满足order by,而不需要额外的排序。

Where条件与order by 使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

但是以下情况不使用索引：

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ；

--order by 的字段混合 ASC 和 DESC

SELECT * FROM t1 WHERE key2=constant ORDER BY key1 ；

-- 用于查询行的关键字与 ORDER BY 中所使用的不相同

SELECT * FROM t1 ORDER BY key1, key2 ；

-- 对不同的关键字使用 ORDER BY    

可以使用explain查看sql的执行计划。

http://www.blogjava.net/happyenjoylife/archive/2011/12/17/366639.html

 

MySQL Index--NOT IN和不等于两类操作无法走索引？

经常被问，NOT IN和<>操作就无法走索引？
真想只有一个：具体问题具体分析，没有前提的问题都是耍流氓。

准备测试数据：

## 删除测试表
DROP TABLE IF EXISTS tb2001;

## 创建测试表
CREATE TABLE `tb2001` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `c1` int(11) DEFAULT NULL,
    `c2` int(11) DEFAULT NULL,
    INDEX idx_c1(c1)
) ENGINE = InnoDB CHARSET = utf8;

## 插入测试数据(10万左右)
## 如果information_schema.columns数据较少，可以重复多次
INSERT INTO tb2001 (c1,c2)
SELECT 1,1 from information_schema.columns;

INSERT INTO tb2001 (c1,c2)
SELECT 2,2 from information_schema.columns
limit 10;
INSERT INTO tb2001 (c1,c2)
SELECT 3,3 from information_schema.columns
limit 10;

表中tb2001上C1列上有索引，全表数据10万条，但c1<>1的数据仅为20条。

查询c1不为1的10条数据，NOT IN 方式执行计划为：

desc select * from tb2001 where c1 not in(1) limit 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb2001
   partitions: NULL
         type: range
possible_keys: idx_c1
          key: idx_c1
      key_len: 5
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using index condition

查询c1不为1的10条数据，<>方式执行计划为：

desc select * from tb2001 where c1 <> 1 limit 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb2001
   partitions: NULL
         type: range
possible_keys: idx_c1
          key: idx_c1
      key_len: 5
          ref: NULL
         rows: 11
     filtered: 100.00
        Extra: Using index condition

从上面两个例子可以看出，NOT INT和<>操作都可以走索引，且执行性能极佳。
俗话说苍蝇不叮无缝的丹，因为在很多场景下，NOT IN或<>两类操作使用二级索引的成本远超于全表扫描的成本，查询优化器按照成本选择"最优执行计划"，导致查询不走二级索引。但不能因此就彻底判断NOT IN或<>两类操作不能走索引。

https://www.cnblogs.com/gaogao67/p/11046902.html