[关闭]
@boothsun 2018-02-18T10:01:27.000000Z 字数 4002 阅读 1192

MySQL索引基础知识点

MySQL

什么是索引

索引类似于书本目录，是数据库存储引擎维护的用于快速查找到记录的一种数据结构，它是对查询性能优化的最有效手段。

MySQL索引是在存储引擎层而不是服务器层实现的，不同存储引擎的索引工作方式也不一样，也不是所有的存储引擎都支持所有的索引；即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

索引的作用

其实这也是查询优化的三个方向：减少服务器扫描的数据量；避免排序和临时表的出现；将随机I/O变为顺序I/O。

索引好坏评定标准

索引分类以及场景适用

BTREE索引

BTREE索引是我们很常用的索引，大多数MySQL存储引擎都支持这种索引。大多数存储引擎底层都是使用B-TREE结构来实现这种索引存储结构的，但InnoDB则是使用B+TREE数据结构。

存储引擎以不同的方式使用B-TREE索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如，MyISAM索引通过数据的物理位置引用被索引的行。而InnoDB则根据主键引用被索引的行。且B-TREE的叶子节点比较特别，它们的指针指向的是被索引的数据。

B-TREE对索引列是顺序组织存储的，所以非常适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”，这样的查找效率会非常高。并且由于索引树中的节点是有序的，所以对索引中涉及到的字段排序需求，索引也非常有帮助。

由于BTREE索引对列时顺序组织存储的，因为在查询时不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行往下搜索。这对查询性能是很大的提高。

在BTREE索引中，所有叶子节点都是数据节点。

BTREE索引必须遵循“最左前缀原则”。

缺点：
1. B-TREE 必须遵循“最左前缀原则”。

为什么BTREE只能遵循最左前缀原则？？
因为BTREE底层是使用树结构维护存储的。假设索引字段有A、B、C三个字段，B的有序是建立在A有序的基础上，否则只有一个B字段，还是要全表扫描！！

哈希索引

哈希索引是基于哈希表实现的，针对每行数据，存储引擎都会对所有的索引列计算出一个hash值；哈希索引将所有的hash值存储在索引中，同时在hash表中保存指向每个数据行的指针。因此，只有精确匹配索引所有列的查询才有效。

在MySQL中，只有Memory引擎显示支持哈希索引。这也是Memory引擎表的默认索引类型，Memory引擎同时也支持B-TREE索引。值得一提的是，Memory引擎是支持非唯一哈希索引的，如果多个列的哈希值相同，索引会以链表的方式存存放多个记录指针到同一个哈希条目中。

1. 优点：

2. 缺点：

3. InnoDB自适应哈希索引：

当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再建立一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速哈希查找。这是一个完全自动，内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能。

虽然InnoDB有自适应哈希索引，但是这不代表InnoDB就是支持Hash索引的。实际上，InnoDB是不支持Hash索引的，MyISAM也是不支持HASH索引的。虽然有些版本在创建索引时可以使用USING HASH，但是通过show idnex from tableName语句看到的索引类型还是BTREE。

4. 通过创建伪HASH来优化查询：

  1. SELECT id FROM url WHERE url = "http://www.mysql.com" ;

若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

  1. SELECT id FROM url WHERE url = "http://www.mysql.com" AND url_crc = CRC32("http://www.mysql.com");

相比对完整的URL字符串进行索引查询，这种方式只需要计算进行快速的计算和等值比较就能找到索引条目。

5. Hash索引举例：
1). 假设有如下数据表：

  1. CREATE TABLE testhash (
  2.    fname VARCHAR(50) NOT NULL,
  3.    lname VARCHAR(50) NOT NULL,
  4.    KEY USING HASH(fname)
  5. ) ENGINE=MEMORY;

2). 表中有如下数据：

3). 假设索引使用hash函数f()，如下所示：

  1. f('Arjen') = 2323
  2. f('Baron') = 7437
  3. f('Peter') = 8784
  4. f('Vadim') = 2458

则Hash索引的数据结构如下：

注意每个槽的编号是顺序的，但是数据行不是。现在，来看如下查询：

  1. SELECT lname FROM testhash WHERE fname = 'Peter';

MySQL先计算‘Peter’的哈希值，并使用该值寻找对应的记录指针。因为f('peter')=8784，所以MySQL在索引中查找8784，可以很快找到指向第3行的指针，最后一步是比较第三行的值是否为‘Peter’，以确保就是要查找的行。

全文索引

全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE，ALTER TABLE，CREATE INDEX使用，不过目前只有CHAR、VARCHAR、TEXT列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE "%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的。

个人觉得全文索引在实际应用中并不是特别多，一般情况下都是使用Lucene、Solr这种搜索引擎去代替，故这里不再进行详细介绍。

唯一索引

其实，唯一索引和BTREE索引或者HASH索引并不是同一级别的，但是这里顺带说一下唯一索引。

普通索引的唯一任务就是加快对数据的访问速度，应该只为那些最经常出现在查询条件或排序条件中的数据列创建索引。普通索引允许被索引的数据列包含重复的值，但是唯一索引不允许。

对于唯一索引，MySQL会在有新记录插入数据库表时，自动检查新纪录的这个字段值是否已经在某个记录的这个字段里出现过了。如果是，MySQL将拒绝插入那条新纪录。这样的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入时，自动做唯一性校验。

主键索引

MySQL中主键就是能够唯一标识表中一行记录的属性或者属性组；它是一种特殊的唯一索引。主键值不允许出现NULL值。

其实在MySQL中，主键默认是有索引的，所以即使主键常常在查询条件中，也不必再为主键单独建立普通索引。

主键索引和普通索引的比较：

在MyISAM引擎中，唯一索引除了key值允许存在NULL外，其余的和主键索引没有本质性区别。也就是说，在MyISAM引擎中，不允许存在NULL值的唯一索引，本质上和主键索引是一回事。

而在InnoDB引擎中，主键索引和辅助索引的区别就很大了。主键索引会被选中作为聚集索引，而唯一索引和普通辅助索引间除了唯一性约束外，在存储上没本质区别。

从查询性能上来说，在MyISAM表中主键索引和不允许有NULL的唯一索引的查询性能是相当的，在InnoDB表通过唯一索引查询则需要多一次从辅助索引到主键索引的转换过程。InnoDB表基于普通索引的查找代价更高，因为每次检索到结果后，还需要至少再多检索一次才能确认是否还有更多符合条件的结果，主键索引和唯一索引就不需要这么做了。

经过测试，对100万行数据的MyISAM做随机检索（整数类型），主键和唯一索引的效率基本一样，普通索引的检索效率则慢了30%以上。换成InnoDB表的话，唯一索引比主键索引效率约慢9%，普通索引比主键索引约慢了50%以上。

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注