将琴存诗
人生 可以不要那么 耀 ,只需要有 一个  平凡的梦想  足以 。—— H.yf. -_^ RSS
Fork me on GitHub

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_[字段名]_[字段名]...

github
blog

posted @ 2019-08-10 10:17  loveincode  阅读(...)  评论(... 编辑 收藏
最简单即最美
有了信仰,自己要坚持努力 2017.07.09 21:34