关于 MySQL 索引的介绍

索引

创建索引的目的就是为了加快查询的速度，如果没有索引，MySQL 在查询时，只能从第一条记录开始然后读完整个表找到匹配的行。MySQL 支持多种存储引擎，不同的引擎对索引的支持也不相同。我这里只会介绍 B树 索引，对应 InnoDB 存储引擎。

索引类型及操作

索引类型

• 普通索引

这是最基本的索引类型，支持单列和多列。可以通过以下的几种方式创建：

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-sql"><span style="color:#36bcd6">CREATE</span> <span style="color:#36bcd6">INDEX</span> 索引名 <span style="color:#36bcd6">ON</span> 表名<span style="color:#999999">(</span>列名<span style="color:#ae81ff">1</span>，列名<span style="color:#ae81ff">2</span><span style="color:#999999">,</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">)</span><span style="color:#999999">;</span>                  <span style="color:#708090">-- 创建索引</span>
<span style="color:#36bcd6">ALTER</span> <span style="color:#36bcd6">TABLE</span> 表名 <span style="color:#36bcd6">ADD</span> <span style="color:#36bcd6">INDEX</span> 索引名 <span style="color:#999999">(</span>列名<span style="color:#ae81ff">1</span>，列名<span style="color:#ae81ff">2</span><span style="color:#999999">,</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">)</span><span style="color:#999999">;</span>            <span style="color:#708090">-- 修改表</span>
<span style="color:#36bcd6">CREATE</span> <span style="color:#36bcd6">TABLE</span> 表名 <span style="color:#999999">(</span> <span style="color:#999999">[</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">]</span><span style="color:#999999">,</span> <span style="color:#36bcd6">INDEX</span> 索引名 <span style="color:#999999">(</span>列名<span style="color:#ae81ff">1</span>，列名 <span style="color:#ae81ff">2</span><span style="color:#999999">,</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">)</span> <span style="color:#999999">)</span><span style="color:#999999">;</span>  <span style="color:#708090">-- 创建表时指定索引</span>
</code></span></span>

• 唯一索引

表示唯一的，不允许重复的索引，支持单列和多列。 注意，如果是多列共同构成唯一索引，代表的是多列的数据组合是唯一的。可以通过以下的几种方式创建：

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-sql"><span style="color:#36bcd6">CREATE</span> <span style="color:#36bcd6">UNIQUE</span> <span style="color:#36bcd6">INDEX</span> 索引名 <span style="color:#36bcd6">ON</span> 表名<span style="color:#999999">(</span>列名<span style="color:#ae81ff">1</span>，列名<span style="color:#ae81ff">2</span><span style="color:#999999">,</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">)</span><span style="color:#999999">;</span>           <span style="color:#708090">-- 创建索引</span>
<span style="color:#36bcd6">ALTER</span> <span style="color:#36bcd6">TABLE</span> 表名 <span style="color:#36bcd6">ADD</span> <span style="color:#36bcd6">UNIQUE</span> 索引名 <span style="color:#999999">(</span>列名<span style="color:#ae81ff">1</span>，列名<span style="color:#ae81ff">2</span><span style="color:#999999">,</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">)</span><span style="color:#999999">;</span>           <span style="color:#708090">-- 修改表</span>
<span style="color:#36bcd6">CREATE</span> <span style="color:#36bcd6">TABLE</span> 表名<span style="color:#999999">(</span> <span style="color:#999999">[</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">]</span><span style="color:#999999">,</span> <span style="color:#36bcd6">UNIQUE</span> 索引名 <span style="color:#999999">(</span>列名<span style="color:#ae81ff">1</span>，列名<span style="color:#ae81ff">2</span><span style="color:#999999">,</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">)</span> <span style="color:#999999">)</span><span style="color:#999999">;</span>   <span style="color:#708090">-- 创建表时指定索引</span>
</code></span></span>

• 主键索引

主键是特殊的唯一索引，同样支持单列和多列，但是必须被指定为 PRIMARY KEY。注意，每个表中只能有一个主键。可以通过以下的几种方式创建：

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-sql"><span style="color:#36bcd6">CREATE</span> <span style="color:#36bcd6">TABLE</span> 表名<span style="color:#999999">(</span> <span style="color:#999999">[</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">]</span><span style="color:#999999">,</span> <span style="color:#36bcd6">PRIMARY</span> <span style="color:#36bcd6">KEY</span> <span style="color:#999999">(</span>列名<span style="color:#ae81ff">1</span>，列名<span style="color:#ae81ff">2</span><span style="color:#999999">,</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">)</span> <span style="color:#999999">)</span><span style="color:#999999">;</span>    <span style="color:#708090">-- 创建表的时候指定</span>
<span style="color:#36bcd6">ALTER</span> <span style="color:#36bcd6">TABLE</span> 表名 <span style="color:#36bcd6">ADD</span> <span style="color:#36bcd6">PRIMARY</span> <span style="color:#36bcd6">KEY</span> <span style="color:#999999">(</span>列名<span style="color:#ae81ff">1</span>，列名<span style="color:#ae81ff">2</span><span style="color:#999999">,</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">)</span><span style="color:#999999">;</span>            <span style="color:#708090">-- 修改表</span>
</code></span></span>

索引操作

• 删除索引

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-sql"><span style="color:#708090">-- 删除 talbe_name 中的索引 index_name</span>
<span style="color:#36bcd6">DROP</span> <span style="color:#36bcd6">INDEX</span> index_name <span style="color:#36bcd6">ON</span> talbe_name
<span style="color:#36bcd6">ALTER</span> <span style="color:#36bcd6">TABLE</span> table_name <span style="color:#36bcd6">DROP</span> <span style="color:#36bcd6">INDEX</span> index_name
<span style="color:#708090">-- 删除主键索引，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名</span>
<span style="color:#36bcd6">ALTER</span> <span style="color:#36bcd6">TABLE</span> table_name <span style="color:#36bcd6">DROP</span> <span style="color:#36bcd6">PRIMARY</span> <span style="color:#36bcd6">KEY</span>
</code></span></span>

• 查看索引

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-sql"><span style="color:#36bcd6">show</span> <span style="color:#36bcd6">index</span> <span style="color:#36bcd6">from</span> table_name<span style="color:#999999">;</span>
<span style="color:#36bcd6">show</span> <span style="color:#36bcd6">keys</span> <span style="color:#36bcd6">from</span> table_name<span style="color:#999999">;</span>

<span style="color:#708090">-- 核心字段的解释</span>
<span style="color:#708090">-- Table：表的名称</span>
<span style="color:#708090">-- Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1</span>
<span style="color:#708090">-- Key_name：索引的名称</span>
<span style="color:#708090">-- Seq_in_index：索引中的列序列号，从1开始</span>
<span style="color:#708090">-- Column_name：列名称</span>
<span style="color:#708090">-- Collation：列以什么方式存储在索引中。在MySQL中，‘A’（升序）或 NULL（无分类）。</span>
<span style="color:#708090">-- Cardinality：索引中唯一值的数目的估计值</span>
<span style="color:#708090">-- Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL</span>
<span style="color:#708090">-- Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL</span>
<span style="color:#708090">-- Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO</span>
<span style="color:#708090">-- Index_type：索引类型（BTREE, FULLTEXT, HASH, RTREE）。</span>
</code></span></span>

索引实现的原理

索引的最核心思想是通过不断的缩小数据的范围来筛选出最终想要的结果，同时把随机事件变成顺序事件（二分查找的核心思想）

InnoDB 存储引擎使用 B+ 树来构造索引，之所以使用 B+ 树构造索引，是因为数据和索引都保存在磁盘中，为了提高性能，每次会把部分数据读入内存来计算。所以，每次查找数据时把磁盘 IO 次数控制在一个很小的数量级是最优的，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，B+树应运而生。

B 树和 B+ 树的特性总结

B 树

B树是一种多路平衡查找树，B是平衡的意思，即Balance，m阶（m >= 2）的B树有以下特性

• 树中的每个节点最多有m个子节点

• 除了根节点和叶子节点之外，其他每个节点至少有 m/2 个子节点

• 所有的叶子节点都在同一层

• 节点中关键字的顺序按照升序排列

结构图如下所示

B+ 树

B+树是B树的一种变体，同样是多路平衡查找树，它与B树主要的不同是

• 非叶子节点不存储数据，只存储索引

• 叶子节点包含了全部的关键字信息，且叶子节点按照关键字顺序相互连接

结构图如下所示

问题与思考

• 你能根据表的索引与数据画出 B+ 树的存储结构吗 ？

• 根据你画出的存储结构，描述下查找某个数据的匹配过程 ？

索引使用的原则

关于索引的使用原则，美团点评技术团队的文章 《MySQL索引原理及慢查询优化》里总结的很好，如下：

1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整；

2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；

3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录；

4. 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。