Mysql索引进阶入门

1. 索引操作

MySQL 索引 菜鸟

2. 索引类型

• PRIMARY
  唯一且不能为空；一张表只能有一个主键索引
• INDEX
  普通索引
• UNIQUE
  唯一性索引
• FULLTEXT
  全文索引：用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以

3. 聚集索引 VS 非聚集索引

3.1 区别

* 聚集索引：主键索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序
* 非聚集索引(非主键索引，也称二级索引)：除主键索引(普通索引、唯一索引、全文索引)，索引的逻辑顺序与磁盘上行的物理存储顺序不同

查询过程：

• 查询聚集索引能直接得到所有数据，
• 查非聚集索引需要先得到聚集索引地址，回表 再得到数据。

3.1 聚集索引规则

1. 如果一个主键被定义了，那么这个主键就是作为聚集索引
2. 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
3. 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

4 索引结构

默认 B+Tree, Hash(key-value的插入以及查询，哈希表的时间复杂度都是O(1)，如果不需要有序的遍历数据，哈希表性能最好。)

B+树 由二叉树演变的m阶树

为什么是B+树(配合磁盘的读写特性，减少单次查询的磁盘访问次数。)

4.1 B+树特点

———— 极客时间 数据结构与算法之美

• 每个节点中子节点的个数不能超过 m，也不能小于 m/2；
• 根节点的子节点个数可以不超过 m/2，这是一个例外；
• m 叉树只存储索引，并不真正存储数据，这个有点儿类似跳表；
• 通过链表将叶子节点串联在一起，这样可以方便按区间查找；
• 一般情况，根节点会被存储在内存中，其他节点存储在磁盘中。

复杂度

• 所有操作(查、插、删) 时间复杂度 O(logm(N))，
• 空间复杂度 最差 O(n)

4.2 m阶怎么计算来？

操作系统按页读取(默认是4k或者8k)，为了提高I/O效率，所以一个索引页和操作系统读取空间保持一致。

m = 数据页大小/索引项大小

所以索引项字段占空空间越小(int 4byte，比bigint 8byte少一半)，一页存的索引数据越多，在优化的时候也要考虑索引字段的长度。

子节点是 双向链表 结构，方便范围查询及排序。

考虑:
1000万数据,树有多高？ InnoDB页的大小默认是16k，16k=16384byte，一般一行数据为1k，单个叶子节点(页)的记录数为16/1 = 16，假设主键id为bigint8字节，指针大小默认为6字节，一页能存放 16384/14 = 1170 高度为2的能存放 1170*16 = 18720 高度为3的能存放 1170*1170*16= 21902400

5. 覆盖索引

select 主键 from table where 普通索引字段 = ** ;

覆盖索引概念：通过索引直接插到结果，不需要回表操作。

例子：身份证号 和 姓名

如果要根据身份证号查询信息，只要在身份证上建立索引，需要建[身份证、姓名] 组合索引吗？

如果有身份证号查询姓名的高频查询，则建立上边的组合索引，则可达到覆盖索引，不需要回表查到整行数据，减少执行时间。

6. 最左前缀原则

两个概念：

• 这个最左前缀可以是 组合索引的最左N个字段
• 也可以是 字符串索引的最左M个字符。

建立组合索引(a,b,c)相当于建立了 (a,b,c) (a,b) (a,c) (a) 四个索引

只要能匹配到最左N个字段，则能使用索引。 如 [a,b,c] [a,c] [a,b] [a] 都能触发索引，内部顺序可变，mysql自动调整。

字符串索引
最左M个字符：如like x% ok, %x，%x% 不行。

7. 索引下推

MySQL 5.6 引入的索引下推优化(index condition pushdown)

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

8. 索引是否生效，优化

可以使用 EXPLAIN 来分析索引是否起效，慢sql做一些索引优化 Explain优化查询检测

• 索引字段为int类型时，条件可用' '包起来 也可以直接是数值比较

• 索引字段为varchar类型时，条件要使用' '包起来

• 能触发range范围索引 >,<, not in , in , != ,BETWEEN AND (5.5后版本 )

9. 常用索引命名规范

唯一 uk_[字段名]_[字段名]...
普通 idx_[字段名]_[字段名]...

githubblog