当前位置：首页 > news >正文

Mysql索引学习

news 来源：原创 2024/5/20 13:03:14

mysql索引-自学版

1 索引语法
2 索引类别
3 索引原理
- 磁盘IO与预读
- 索引数据结构 B+树
- - B+树的前生今世
  - B+ 树代码（进阶）
4 索引使用策略及优化
- 优化索引的几种方法
索引常见面试题
- 面经实战

1 索引语法

索引的语法：创建、修改、增加、删除等操作，查询此链接：
菜鸟教程-MySQL 索引
or
Mysql索引（一篇就够le）

2 索引类别

mysql使用较多的存储引擎：InnoDB、MyISAM等，其中InnoDB支持的索引是B+树索引、Hash索引、全文索引，MyISAM使用的是Hash索引。
有关 mysql 所有的索引类型参考官网：
官网

我们可以按照四个角度来分类索引。

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、联合索引。

3 索引原理

以下内容（磁盘IO与预读、索引数据结构 B+树、索引使用策略及优化）学自此文章的前半部分内容：
MySQL索引原理及慢查询优化

数据库数据保存在磁盘上，且为了提高性能，每次只将部分数据读入内存，众所周知，访问磁盘的成本是访问内存的十万倍左右，因此，我们需要一种高效的搜索方式。

磁盘IO与预读

为什么访问磁盘的成本很高？访问磁盘的操作有哪几步？它们所用的时间？
考虑到磁盘IO是非常高昂的操作，操作系统做了哪些优化？

索引数据结构 B+树

根据磁盘IO的特点，我们的诉求：每次查找数据时将磁盘的IO次数控制在很小的数量级，最好是常数数量级，于是 B+ 树应运而生。

B+树的前生今世

想要介绍 B+ 树，就不得不提起二叉查找/排序树（BST）、平衡二叉树(AVL) 和 B 树这三种数据结构。B+ 树就是从它们三个演化而来的。见此文章。
二叉树、平衡二叉树、B-Tree、B+Tree 说明

此文章也讲了一系列树的演变，并且还讲解了B树的结点定义、插入、删除等的过程，以及R树，可补充看：
从B树、B+树、B*树谈到R 树

B+ 树代码（进阶）

B+树重要性不用多说，一起来研究B+树的源码

4 索引使用策略及优化

创建索引的几大原则：
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)的索引，那么只需要修改原来的索引即可。