Mysql索引来了解一下（超详细）

雨点打透心脏的1/2处 2023-02-26 11:28 75阅读 0赞

# 一、index是什么？ #

## 1. 定义 ##

1.  简述：索引（Index）是一种帮助mysql高效获取数据的一数据结构。
2.  详述：除数据本身外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构叫索引。

![索引数据对应关系示意图][format_png]

> 注意：索引本身也很大，不可能全部存储在内存中，因此索引索引往往以索引文件的形式存储到磁盘中；如果没有特定说明，索引对应的数据结构都是B树。

## 2. B树简介 ##

B树：它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。是对二叉查找树的改进。  
![B树示意图][B]  
有以下三个特点：

1.  一个节点可以容纳多个值。
2.  除非数据已经填满，否则不会增加新的层。
3.  子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点。比如上图中，父节点有两个值（7和16），就对应三个子节点，第一个子节点都是小于7的值，最后一个子节点都是大于16的值，中间的子节点就是7和16之间的值。
4.  是一种排好序的快速查找数据结构。

# 二、index的分类及其使用 #

## 1. 分类 ##

1.  单值索引：即一个索引只包含一个列（一个表可包含多个单值索引）。
2.  复合索引：一个索引包含多个列。
3.  唯一索引：索引列的值必须唯一，可以为null。

## 2. 基本语法 ##

1.  创建：`CREATE [UNIQUE] INDEX indexName ON tableName(columnName);`和`ALERT tableName ADD [UNIQUE] INDEX indexName ON (columnName);`
2.  删除：`DROP INDEX [indexName] ON tableName;`
3.  查看：`SHOW INDEX FROM tableName;`

# 三、index的优缺点？ #

## 1. 优点 ##

1.  提高数据索引效率，降低数据库的io成本。 索引采取BTREE数据结构，这种数据结构，非常有利于减少硬盘的读取次数。假定一个节点可以容纳100个值，那么3层的B树可以容纳100万个数据！假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在100万个数据中查找目标值，只需要读取两次硬盘。
2.  通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗。

## 2. 缺点 ##

1.  索引本身也是一种表,该表保存了主键和索引字段，并指向实体表的记录，索引也需要占用空间。
2.  虽然极大的提高了查询速率，但是会降低表的更新速度（update、delete和insert）,因为更新表时不仅要保存数据，还要对应更新索引的。
3.  索引只是提高效率的一个因素，如果有大数据量的表，需要花时间建立最优秀索引。

# 四、index的使用场景？不适合使用场景？ #

## 1. 适用场景（表的数据量较大） ##

1.  主键自动建立唯一索引
2.  频繁作为查询条件的字段应该创建索引
3.  查询中与其它表关联的字段，外键关系建立索引
4.  查询中排序的字段应该创建索引
5.  查询中统计或分组字段

## 2. 不适用场景 ##

1.  频繁更新的字段或表不适合建立索引（降低更新效率）。
2.  where条件中没有使用到的字段不创建索引（浪费空间）。
3.  表的数据量少，不适合建立索引。
4.  数据列重复率高的不适合建立索引，比如性别：男，女。只有这两个字段，建立索引意义不大。

# 五、性能分析 #

## 1. Mysql query Optimizer(mysql查询优化器) ##

mysql中有专门负责优化SELECT语句的优化器，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提高其默认的最优执行计划（可能和我们认为的最优相违背，这部分耗费时间最长）

## 2. 常见性能瓶颈 ##

1.  CPU：cpu在饱和的时候数据一般发生在装入内存或者从磁盘上读取数据
2.  IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3.  服务器硬件性能瓶颈：top,free,iostat和vmstat来查看定位

## 3. SQL语句问题 ##

Explain解释执行可查看sql运行状态，语法如下：`EXPLAIN sql语句`