三、创建高性能索引
索引基础
- 索引基础
- 索引是存储引擎用于快速查找记录的一种数据结构
- 索引类型
B-Tree索引:
① 它使用的就是B-Tree数据结构来存储数据的
② 大多数MySQL引擎都使用的这种索引结构,但是Archive是一个列外,5.1版本之前Archive是不支持索引的,知道5.1才+开始支持单个自增列的索引
③ 存储引擎以不同的方式使用B-Tree索引,性能也各有优劣:MyISAM使用前缀压缩技术使索引边的更小,但是InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行
④ B-Tree通常意味着所有的值都是按照顺序存储的,并且每一个叶子页到根的距离是相同的
⑤ 叶子节点比较特殊,他们是指针指向的是被索引的数据,而不是其他的节点页
⑥ 索引对多个值进行排序的依据是创建语句中定义索引列时的顺序
所述索引对如下类型有效
① 全职匹配;②匹配最左前缀;③匹配列前缀;④匹配范围值;⑤精确匹配一列并范围匹配另外一列;⑥只访问索引的查询
B-Tree索引的限制
① 如果不是按照索引的最左列开始查找,则无法使用索引
② 不能跳过索引的列
③ 如果查询中有某个列的范围查询,则其优点所有列都无法使用索引
哈希索引
① 哈希索引是基于哈希表实现的,只有精确匹配索引所有的列的查询才有效
② 在MySQL引擎中,只有Memory引擎支持哈希索引。
③ Memory引擎同时也支持B-Tree索引
④ Memory引擎是支持非唯一哈希索引的,这在数据库世界里是比较与众不同的
⑤ 如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中
⑥ 每个哈希槽是有顺序的,但是数据行不是有序的,所以查询不能进行排序
哈希索引的限制
① 哈希索引只包含哈希值和行指针,而不是存储字段值,所以不能使用索引中的值来避免读行
② 哈希索引的数据并不是按照索引值顺序存储的,所以不能用于排序
③ 哈希索引不支持部分索引匹配查找
④ 哈希索引只支持等值查询,也不支持范围查询
⑤ 访问哈希索引的数据非常快,除非有很多的哈希冲突
⑥ 如果哈希冲突很多的话,一些索引的维护 操作的代价也会很高
⑦ InnoDB有一个特殊的功能叫做“自适应哈希索引”,当InnoDB注意到某个二级查询中,某些索引值使用的给长频繁,它会在内存中基于B-Tree索引之上再创建一个哈希索引,这样就让B-Tree索引也具有哈希索引的一些优点,快速哈希查找–这是一个完全自动、内部的行为,用户无法控制或者配置的,不过如果有必要完全可以关闭该功能
空间数据索引(R-Tree)
① MyISAM表支持空间索引,可以用作存储地理数据
② 和B-Tree不同,这类索引无序前缀查询,空间索引会从所有的维度来索引数据
全文索引
① 一种特殊的索引,它查找的是文本中的关键词,而不是直接比较索引中的值
索引的优点
- 索引大大减少了服务器需要扫描的数量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机I/O变为顺序I/O