【数据库篇】你对MySQL中的索引了解多少？
- 索引是个什么东西？
- 我们可以创建哪些索引？
- 哪些字段适合建立索引呢？
- 索引是不是越多越好呢？
- 为什么我们不建议使用uuid、身份证号等数据做为主键？
- 为什么不建议使用select * from table？
- 我们使用模糊匹配 ’%三‘ ’张%‘ 在前在后会影响索引的使用吗？
什么是索引
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
MySQL中索引有哪些类型
普通索引
没有什么限制，在任何一列上都能进行创建，索引值可重复、也可为空。
主键索引
每张表一般都会有自己的主键，mysql会在主键上建立一个索引，这就是主键索引。
主键是具有唯一性并且不允许为NULL，所以它是一种特殊的唯一索引。一般在建立表的时候选定。
唯一索引
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
复合索引（组合索引/联合索引）
指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立索引，同样的可以建立为普通索引或者是唯一索引。
复合索引的使用符合最左原则。
-- 创建索引的基本语法
CREATE INDEX indexName ON table(column1(length),column2(length));
-- 例子
CREATE INDEX idx_phone_name ON user(phone,name);我们看下面的查询语句：
SELECT * FROM user_innodb where name = '程冯冯';
SELECT * FROM user_innodb where phone = '15100046637';
SELECT * FROM user_innodb where phone = '15100046637' and name = '程冯冯';
SELECT * FROM user_innodb where name = '程冯冯' and phone = '15100046637';三条sql只有 2 、 3、4能使用的到索引idx_phone_name，因为条件里面必须包含索引前面的字段才能够进行匹配。而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为mysql本身就有一层sql优化，它会根据sql识别出来该用哪个索引，我们可以理解为3和4在mysql眼中是等价的。
全文索引
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。
它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。正常情况下我们也不会使用到全文索引，因为这不是mysql的专长。
索引的数据结构
innodb默认索引数据结构是B+Tree，什么是B+Tree呢，它的全名叫做平衡多路查找树PLUS。他是由平衡二叉树查找树（AVL树）演化而来。
树的基本术语和性质
基本术语
结点的度：一个结点的孩子的个数就是结点的度
树的度：树中结点最大的度称为树的度
分支结点：度大于0的结点称分支节点（又称非终端结点）
叶子结点：度为0的结点称叶子结点（又称终端结点）
结点的层次：从树根开始定义，根节点为第一层，它的子节点为第二层，以此类推。
结点的深度：从根节点开始自定向下逐层累加。
结点的高度：从叶节点开始自底向上逐层累加。
树的高度（或深度）：书中结点的最大层数。
有序树：树中结点的各子树从左到右是有次序的，不能互换
无序树：树中结点的各子树没有次序，可以互换
路径：两结点之间所经过的结点序列构成路径
路径长度：是路径上所经过的边的个数
森林：m（m>=0）棵互不相交树的集合。
树的性质
- 树中的结点数 = 所有结点的度数 （边数）+ 1；
- 度为m的树中第i层上最多有 mi-1 个结点；
m叉树第i层至多有mi-1 个结点； - 度为m的树至少有一个结点的度 = m，至少有m + 1个结点
m叉树允许所有结点的度<m，可以是空树 - 高度为h的m叉树至多有（mh - 1 ）/（m - 1）个结点
高度为h的m叉树至少有n个结点
高度为h，度为m的 树至少 h+m-1个结点 - 具有n个结点的m叉树的最小高度为 logm(n(m-1)+1)
二叉查找树（Binary Search Trees）
二叉树查找树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。
插入元素顺序的不同，在极端情况下会导致一个二叉树退化变成一个链表，效率直线下降。如下：
元素：11、25、36、80、110、120、300
第一种插入：80、25、11、36、120、110、300
第二种插入：11、25、36、80、110、120、300那么如何解决掉这种不平衡的问题呢？
AVL Trees (Balanced binary search trees)
什么是AVL树？
在AVL树中任何节点的两个子树的高度最大差别为1，所以它也被称为高度平衡树。增加和删除可能需要通过一次或多次树旋转来重新平衡这个树。
当子树的高度超过1时他会通过自旋的方式重新平衡树，所以这样我们查询数据的时间复杂度就稳定了。
那么，我们使用AVL树作为索引是不是就可以了呢，答案是否定的。我们的索引是存储到磁盘上的，每次进行数据查询会将磁盘里的数据读取到内存中，对磁盘io是非常耗时的，而内存操作非常快。计算机的最小存储单元是块（block）默认4k大小，读取数据是一块一块读取的，而不是随意的读取1/2块数据，对应的我们mysql存储数据也是以页（page）为单位进行存储，默认为16K（16384B），mysql在读取的时候也是一页一页读取的。
如果使用AVL树，我们的一个节点就是一页，但是一个节点是16k，一页就放一个节点肯定是太浪费空间了，而且如果有1000w的数据，那么二叉树深度是55，我们要查找一个数据io的次数就有点太多了，显然这样是不合理的，我们可以怎么做呢？
B-Tree(读作 b树 不是b减树)
为了解决AVL浪费磁盘空间以及IO次数过多的问题，我们在一个节点中多存储一些数据，之前我们放一个，现在我们放多个。如果放int值（4B）我们近乎可以放4096个值，当然索引里面还包含其他的数据，不能够放这么多，但是这也是足够的多了。
这样一个节点的值多了那么树的分叉肯定就多了，假如一个节点可以存储1000的值，那么1000 * 1000 * 1000 = 10亿节点，3层的结构就能存储10亿的数据，这样是不是最多IO3次就足够了呢。
所以AVL的进化体B-Tree出现了，B-Tree的全名是多路平衡查找树，B-tree中，每个结点包含：
- 本结点所含关键字的个数；
- 指向父结点的指针；
- 关键字；
- 指向子结点的指针；
对于一棵m阶B-tree，每个结点至多可以拥有m个子结点。各结点的关键字和可以拥有的子结点数都有限制，规定m阶B-tree中，根结点至少有2个子结点，除非根结点为叶子节点，相应的，根结点中关键字的个数为1~m-1 ；非根结点至少有[m/2]（[]，向上取整）个子结点，相应的，关键字个数为[m/2]-1 ~ m-1。
聚集索引和辅助索引（非聚集索引）
什么是聚集索引（clustered index organize table ），聚集索引中键值的逻辑顺序和表中相应行的物理顺序相同。
聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（联合索引），就像电话簿按姓氏和名字进行组织一样，但是在innodb的设计中聚集索引包含整行的数据，所以innodb中索引就是数据本身，这就是大家常说的索引即数据。
MYSIAM和Innodb的索引区别：
innodb数据和索引在一起（数据即索引，索引即数据），而mysiam是分开存储的 innodb索引是有主次的，也就是区分聚集索引和非聚集索引。而mysiam是不区分主次的。
每个InnoDB表都有一个特殊的索引，称为聚簇索引 ，用于存储行数据。通常，聚簇索引与主键同义 。
非聚集索引的话其实就是一个普通索引，但是非聚集索引不存储全部数据，只存储聚集索引的值（一般为主键id）。
所以我们如果使用B-Tree来作为索引结构的话，如果数据行过大，那么一个页存储的数据就会大大减少，这就违背了我们B-Tree的初衷了——在一个页中尽可能的存储多的数据。像前面说的如果我们存储int类型可以存储几千个，那么如果我们存储整行数据呢，可能只能存储三四个，那么树的深度就会大大增加，而且我们的内存空间是有限的，每次mysql预读进来的索引数量有限，这进一步导致搜索效率变差。
所以我们想要的索引就是只包含索引字段，不应该包含全部的数据 ，看下面的对比图。
B+Tree
为了解决只存储索引的问题，B-Tree的plus版本横空出世，那就是B+树。
B+ 树是一种树数据结构，是一个n叉树，每个节点通常有多个孩子，一颗B+树包含根节点、内部节点和叶子节点，和B-Tree几乎一样，只不过B+Tree不再包含整行的数据了。B+ 树通常用于数据库和操作系统的文件系统中。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。
一个m阶的B树具有如下几个特征：
- 根结点至少有两个子女。
- 每个中间节点都至少包含ceil(m / 2)个孩子，最多有m个孩子。
- 每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m。
- 所有的叶子结点都位于同一层。
- 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。
B+ 树相对于B-Tree最大的变化有三点：
- 数据下移，所有的非叶子节点不再存储数据而将数据全部存储到叶子节点。
- 所有的叶子节点都有一个双向的指针，做了一个双向链表
- 使用B+Tree查询次数相对固定，因为数据都在叶子节点，每一个层级都会被加载扫描。
非聚集索引是怎么查找数据的
上面我们已经了解了聚集索引（一般是主键索引）是如何获取的，那非聚集索引呢？下面我们看一张图。
从这个图我们就可以直观的看到，非聚集索引是怎么查询数据的。每次查非聚集索引都会再次通过主键再次去聚集索引里面查询。
这里我们再引申出一个概念那就是回表，我们上图所描述的流程就是回表。回表的原因是我们需要获取的是整行或者是包含非索引字段的数据，因非聚集索引没有该字段所以需要回表查询。
因此我们建议尽量少用SELECT * FROM TABLE，例如我们查询SELECT * FROM USER WHERE name LIKE '张%'，但是我们其实想要的只是名字的集合而已，那么我们就可以改造成SELECT name FROM USER WHERE name LIKE '张%',前者会回表查询而后者不会，这就减少了数据查询的时间同时也减少了数据库的压力。
HASH索引
Hash索引就是将索引字段进行hash存储，整个hash索引的结构是Hash表+链表（因为会存在hash冲突）。
InnoDB和MyISAM不支持创建HASH索引。
Hash索引的优缺点
由于Hash是基于内存的索引，那么它的检索效率是非常快的，那既然Hash索引效率这个高，我们是不是都需用Hash索引啊。
我觉得hash索引的优点只有一个，那就是快，不需要磁盘io，直接内存一次性搞定。但是要说它的缺点可真的是太多了。
- Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
- Hash索引无法被用来避免数据的排序操作。
- Hash索引不能利用部分索引键查询。
- Hash索引在任何时候都不能避免表扫描。
- Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。
疑问回答环节（主要针对InnoDB）
为什么辅助索引不直接存数据的地址而存主键id呢？
因为数据会不断的变动，所以它的地址会跟着一起变。如果直接存储地址，下次找的数据可能就不是原先的数据了。
索引是不是创建的越多越好呢？
并不是。
-
我们已经知道了索引即数据，那么我们过多的创建索引就会导致数据量的增加。 -
我们知道索引是一颗平衡树，我们在更新数据的同时，索引也在频繁的进行页分裂和合并，非常耗时。
为什么我们推荐使用自增id而不推荐使用uuid或者身份证号等呢？
上面我们提到过B+Tree是自底向上插入的，什么意思呢。我们优先会将数据插入到叶子节点中，然后整个树会根据底部的叶子节点进行变动。
当我们使用的是自增主键呢，我们叶子节点链表会根据当前最后一条的位置，将最新的一条数据顺序的插入到后面，看下图。
但是当你插入一个uuid时，mysql根本不知道他该插入到哪个位置，需要从头开始寻找插入的位置。但是当我们的插入的页满了时，这就造成了页的分裂和合并，极大的影响了效率。
而且我们使用uuid的话，uuid所占字节也比较长，就导致了每一页存储的数据就会变少，也不利于索引的数据查询。
InnoDB一颗B+树可以存放多少行数据？
数据库表中的数据都是存储在页里的，那么一个页可以存放多少条记录呢？
这取决于一行记录的大小是多少，假如一行数据大小是1k，那么理论上一页就可以放16条数据。
当然，查询数据的时候，MySQL也不能把所有的页都遍历一遍，所以就有了索引，InnoDB存储引擎用B+树的方式来构建索引。
聚簇索引就是按照每张表的主键构造一颗B+树，叶子节点存放的是整行记录数据，在非叶子节点上存放的是键值以及指向数据页的指针，同时每个数据页之间都通过一个双向链表来进行链接。
如上图所示，就是一颗聚簇索引树的大致结构。它先将数据记录按照主键排序，放在不同的页中，下面一行是数据页。上面的非叶子节点，存放主键值和一个指向页的指针。
当我们通过主键来查询的时候，比如id=6的条件，就是通过这颗B+树来查找数据的过程。它先找到根页面(page offset=3)，然后通过二分查找，定位到id=6的数据在指针为5的页上。然后进一步的去page offset=5的页面上加载数据。
在这里，我们需要理解两件事：
上图中B+树的根节点(page offset=3)，是固定不会变化的。只要表创建了聚簇索引，它的根节点页号就被记录到某个地方了。还有一点，B+树索引本身并不能直接找到具体的一条记录，只能知道该记录在哪个页上，数据库会把页载入到内存，再通过二分查找定位到具体的记录。
现在我们知道了InnoDB存储引擎最小存储单元是页，在B+树索引结构里，页可以放一行一行的数据(叶子节点)，也可以放主键+指针(非叶子节点)。
上面已经说过，假如一行数据大小是1k，那么理论上一页就可以放16条数据。那一页可以放多少主键+指针呢？
假如我们的主键id为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节。这样算下来就是 16384 / 14 = 1170，就是说一个页上可以存放1170个指针。
一个指针指向一个存放记录的页，一个页里可以放16条数据，那么一颗高度为2的B+树就可以存放 1170 * 16=18720 条数据。同理，高度为3的B+树，就可以存放 1170 * 1170 * 16 = 21902400 条记录。
理论上就是这样，在InnoDB存储引擎中，B+树的高度一般为2-4层，就可以满足千万级数据的存储。查找数据的时候，一次页的查找代表一次IO，那我们通过主键索引查询的时候，其实最多只需要2-4次IO就可以了。
在InnoDB引擎中，页并不是只有一种，我们使用的就是数据页：B-tree Node
哪些列适合添加索引呢？
- 需要经常where的字段
- 需要join连表的字段
- 需要排序的字段
- 需要group by的字段
我们需不需要在性别上加索引呢？
不能够在性别上创建索引，可能会更慢。
因为，在没有索引的情况下，mysql只需遍历底部的链表即可。但是加了索引以后他会查询index(gender)找到合法的索引的主键，然后通过主键再去index(id)里面去找这样一来一回效率自然就直线下降。
那么我们创建索引有什么特别的依据吗，这里就给大家一个公式：count(distinct(column_name)) : count(*),这个可以简单地计算出这个字段的离散值,离散值越高说明建立索引效果更明显。例如我们给手机号加索引，最后计算出来的离散度是1，说明非常有必要加索引。
like '%张’一定不走索引吗？
ICP 索引条件下推
索引条件下推（ICP，Using index condition）是针对MySQL使用索引从表中检索行的情况的一种优化。如果不使用ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给MySQL服务器，后者将评估WHERE行的条件。
启用ICP后，如果WHERE可以仅使用索引中的列来评估部分条件，则MySQL服务器会将这部分条件压入WHERE条件下降到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。
如果表没有主键怎么办，聚集索引怎么建立？
- 默认情况下我们在设置表主键的时候，数据库会默认将其设置为聚集索引。
- 如果没有定义主键，那么mysql会找第一个唯一索引来作为局促索引，前提是聚集索引是NOT NULL
- 如果上面的两个条件都没有满足，那么InnoDB会生成一个隐藏的聚集索引GEN_CLUST_INDEX，每一行都生成一个默认自增的主键id。
|
参考： |
