索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是 很重要的。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧 下降。当创建索引带来的好处多过于消耗的时候,才是最优的选择~

# 查看索引
show index from quickchat_user_additional;

索引的类型 (具体设置在Navicat中添加即可)

主键索引 PRIMARY KEY

它是一种特殊的唯一索引,(设置了主键底层就自动设置)了,不允许有空值。一般是在建表的时候同时创建主键索引。

唯一索引 UNIQUE

唯一索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。可以在创建 表的时候指定,也可以修改表结构。

普通索引 NORMAL

这是最基本的索引,它没有任何限制。可以在创建表的时候指定,也可以修改表结构

空间索引 SPATIAL

空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建

全文索引 FULLTEXT

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用分词技术等多种算法 智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要 的搜索结果

组合索引(不是新的索引类型):

索引分单列索引和组合索引(联合索引)。单列索引,即一个索引只包含单个列,一个表可以有多个 单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。

例如:

索引的存储结构

BTree索引(推荐)

MySQL中普遍使用B+Tree做索引,也就是BTREE。

特点: BTREE索引以B+树的结构存储数据 BTREE索引能够加快数据的查询速度 BTREE索引更适合进行行范围查找

使用的场景: 1. 全值匹配的查询,例如根据订单号查询 order_sn=’98764322119900′ 2. 联合索引时会遵循最左前缀匹配的原则,即最左优先 3. 匹配列前缀查询,例如:order_sn like ‘9876%’ 4. 匹配范围值的查找,例如:order_sn > ‘98764322119900’ 5. 只访问索引的查询

哈希索引(适用范围窄,不推荐使用)

Hash索引在MySQL中使用的并不是很多,目前主要是Memory存储引擎使用,在Memory存储引擎中 将Hash索引作为默认的索引类型。所谓Hash索引,实际上就是通过一定的Hash算法,将需要索引的键 值进行Hash运算,然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候,都会将检索条 件进行相同算法的Hash运算,然后再和Hash表中的Hash值进行比较并得出相应的信息。

特点

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

关于索引的一些个人理解

索引这个东西在InnerDB中,是单独存储的。索引也是物理硬盘上存储的文件,建立的索引越多,整个索引文件就越大。所以如果在一些不必要的字段追加了索引,等价于白白浪费存储空间,还会处理这些用不到索引的时间。

相同的表设计中,对比有、无索引的插入操作,有索引的会相对慢一些,索引频繁插入操作。