《每日一学系列》mysql索引的一些理解（1）

mysql索引的一些理解（1）

---

---

索引的分类

• 普通索引 NORMAL
• 唯一索引 UNIQUE
• 空间索引 SPATIAL （空间索引只能在存储引擎为MYISAM的表中创建）
• 主键索引 PRIMARY KEY
• 全文索引 FULLTEXT

NORMAL

表示普通索引，大多数情况下都可以使用

UNIQUE

表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证号用作索引时，可设置为unique

PRIMARY KEY

Primary Key是拥有自动定义的Unique约束，但是每个表中可以有多个Unique约束，但是只能有一个Primary Key约束。

FULLTEXT

FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

---

索引的方式

• BTREE （B树（可以是多叉树））（常用）
• HASH （key,value）

两者的区别：
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。

---

但是Hash也有明显的缺点（计算hash值所带来的缺陷）：

• Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询
• Hash 索引无法被用来避免数据的排序操作
• Hash 索引不能利用部分索引键查询
• Hash 索引在任何时候都不能避免表扫描（hash值有可能相同）
• Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

---

添加索引

方法之一

ALTER TABLE 表名
ADD INDEX 命名(字段名) USING BTREE 或者 HASH;

---

EXPLAIN

mysql为我们提供了很有用的辅助武器explain，它向我们展示了mysql接收到一条sql语句的执行计划

因此我们需要看懂EXPLAIN SELECT输出的结果

主要需要看懂三个属性：type，key，rows

key：表明的是这次查找中所用到的索引
rows：指这次查找数据所扫描的行数（这里可以先这样理解，但实际上是内循环的次数）
type：本文要详细记录的连接类型，在mysql5.7中type的类型达到了14种之多

---

type

这里只记录和理解最重要且经常遇见的六种类型，它们分别是：

• all 全局扫面
• index 有序的全局扫描
• range 有范围的索引扫描
• ref 不唯一的索引扫描
• eq_ref 唯一索引扫描
• const 主键扫描

all

全局扫描，没有使用到索引，或者索引没有生效的情况

id虽然为主键，但是因为他的类型是vachar，但是我没有+引用符号，所以索引失效，使用全局索引。

index

此处查询所有信息并根据非唯主键字段排序

此处查询所有信息并根据主键字段排序

可以看到非主键的type是all，Extra使用到了filesort，而主键则是index类型。

这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序，所以排序的时候index类型比all更快。

range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。range是基于索引字段的，其中相关范围关键字是：between，and，’>’,’<’，in和or属于索引范围扫描。

ref

基于非唯一索引（查找条件列使用了索引而且不为主键和unique），查询到还会进行小范围的查询，直到没有相同属性为止。但是如果使用了范围关键字，则仍是range。

eq_ref

基于唯一索引，查到即停止，因为没有重复属性。
但是在单个表中，曾尝试了很多方法想出现ref_eq的连接类型，然而很多时候出现的都是const

const

通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。

---

总结

初步了解一下mysql索引，以及explain 的使用，其中很多特殊的例子，以及细节没有详细了解，也没这么简单，需要对操作系统以及数据库的底层查询和运行原理要有一个清楚的理解。