什么是索引
索引是存储引擎快速找到记录的一种数据结构。在mysql中也称为key键。
为什么需要索引(优点)
索引能加快检索速度,提高查询性能。
1.大大减少服务器扫描的数据行数
2.避免服务器进行排序和分组,也因此避免了创建临时表
3.将随机I/O变成顺序I/O(下面会提到B+树的存储物理顺序和逻辑顺序一致)
索引的实现
目前来说大部分索引的都是由B+树实现的。B+树索引是一种按字段排序的树形数据结构。
InnoDB的B+树索引分为主索引和辅助索引。主索引的叶子节点data域记录着完整的数据记录,根节点和分支结点记录着索引。辅助索引的叶子节点记录着数据的主键值,使用辅助索引进行查找时,需要先查找到主键值,然后再到主索引中进行查找。
(在MySQL数据库的InnoDB存储引擎中,主键索引就是聚簇索引,所有数据都会按照主键索引进行组织;而在MyISAM存储引擎中,就没有聚簇索引了,因为MyISAM存储引擎中的数据不是按索引顺序进行存储的)
1.聚簇索引
聚簇索引规定了表中数据的物理存放顺序,数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚簇索引,可以类比于字典中的按拼音目录。
它的优势在于:查询小数据量的表;查询指定范围内的数据。但聚簇索引不适用于频繁删改的数据,这导致整行移动,影响性能,个人认为类似于数组的删除和插入带来的问题。
由于聚簇索引实质上同时包括了完整数据和索引,而数据不可能存在两个地方,因此一个表的聚簇索引只有一个。
简单理解聚簇索引,可以认为查看已知字的解释时,翻看新华字典就是聚簇索引,因为它本身内容就是按顺序的。比如说我们查询一个“李”字,我们直接会直接翻到拼音Li,相当于索引和内容是在一起的。内容本身就是目录,不需要查看其他目录就能直接找到数据的方式就是聚簇索引。
2.非聚簇索引
当我们不认识一个字,需要根据部首去翻阅目录,根据目录找到内容的方式可以认为是非聚簇索引。目录纯粹目录,正文纯粹正文的方式我们认为是非聚簇索引。
Mysql索引分类和比较
B+树索引
是大多数mysql默认的索引类型。
优点:不需要对数据进行全面扫描,只需要对数搜索就好了,查找速度快一点。除了查找外,B+树索引还可以对数据进行排序和分组。
适用情况:全键值,键值范围和键前缀查找。
哈希索引
哈希索引能以O(1)时间进行查找,毕竟哈希码可以直接找到数据,但是失去了有序性。
优缺点:查找速度更快,但较B+树来说,哈希索引不能完成分组和排序。
适用情况:精确查找。
全文索引
全文索引是比较文中的关键字,而不是像B+树或者哈希索引那样直接比较相等。
全文索引使用倒排索引实现,它记录着关键词到其所在文档的映射。
关键词到文件ID的映射,每个关键词都对应着一系列的文件,这些文件中都出现这个关键词。
空间数据索引
MyISAM 存储引擎支持空间数据索引(R-Tree),可以用于地理数据存储。
引起索引失效的情况
(1)以“%”开头的LIKE语句,模糊匹配
(2)OR语句前后没有同时使用索引
(3)数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型)
(4)带非操作符,如<>/!=/not in/not exist
(5)列上作运算
最后总结一下,MySQL只对以下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形),而对于<>,not in是不会使用索引的。