Awezome

  • 主页
  • 随笔
所有文章 友链 关于我

Awezome

  • 主页
  • 随笔

MySQL复习笔记:索引

2015-05-15

MySQL 索引:索引(Index)是帮助MySQL高效获取数据的数据结构

主键列(PRIMARY KEY)
表的外键列(FOREIGN KEY)
唯一索引(UNIQUE INDEX || UNIQUE KEY)
普通索引(INDEX || KEY)
全文索引(FULLTEXT)
索引时指定前缀

索引的存储分类
B-Tree 索引:最常见的索引类型,大部分引擎都支持B树索引。B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值,要么该记录不存在。
HASH 索引:只有Memory引擎支持,使用场景简单。
R-Tree 索引(空间索引):空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型。
Full-text (全文索引):全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。全文索引是一种特殊类型的索引,他查找的是文本中的关键词,而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。他有许多需要注意的细节,如停用词、词干和复数、布尔搜索等。全文索引更类似与搜索引擎做的事情,而不是简单的where条件匹配。
在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突,全文索引适用于MATCH AGAINST操作,而不是普通的where条件操作

MySQL高性能的索引策
独立的列作为索引

如果查询中的列不是独立的,则mysql就不会使用索引,独立的列是指列不能是表达式的一部分,也不能是函数的参数。

选择合适的索引列顺序只是用于B-Tree索引。
索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,将选择性最高的列放到索引最前列

前缀索引和索引选择性

有时候需要索引很长的字符串,这会让索引变的大且慢。一个策略是前面提到过的模拟哈希索引。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但是这样也会降低索引的选择性。索引的选择性是指,不重复的索引值。
一般情况下,某个列前缀的选择性也是足够高的,足以满足查询性能。对于Blob,Text或者很长的VARCHAR类型的列,必须使用前缀索引,因为mysql不允许这些列的完整长度。诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以便使得前缀索引的选择性接近于索引整个列。

使用覆盖索引

如果索引包含满足查询的所有数据,就称为覆盖索引。覆盖索引是一种非常强大的工具,能大大提高查询性能。
(1)索引项通常比记录要小,所以MySQL访问更少的数据;
(2)索引都按值的大小顺序存储,相对于随机访问记录,需要更少的I/O;
(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
(4)覆盖索引对于InnoDB表尤其有用,因为InnoDB使用聚集索引组织数据,如果二级索引中包含查询所需的数据,就不再需要在聚集索引中查找了。
覆盖索引不能是任何索引,只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同,并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。mysql只能使用B-Tree索引做覆盖索引
mysql不能在索引中执行like操作。因为如果是通配符开头的like查询,存储引擎就无法比较匹配。这种情况下,mysql服务器只能提取数据行的值而不是索引的值来做比较。

使用索引扫描来做排序

mysql有两种方式可以生成有序的结果:通过排序操作;或者通过索引顺序扫描;如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序(不要和Extra 列的“using index”搞混淆了)。
扫描索引本身是很快的,因为只需从一条记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就得回表查询一次对应的行。这基本上都是随机IO,因此按索引顺序读取数据的速度通常比顺序的全表扫描慢,尤其是在IO密集型的工作负载时。

MySQL非聚簇索引&&二级索引&&辅助索引

mysql中每个表都有一个聚簇索引(clustered index ),除此之外的表上的每个非聚簇索引都是二级索引,又叫辅助索引(secondary indexes)。以InnoDB来说,每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键,该主键索引是聚集索引。如果你不定义为您的表的主键时,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚集索引。如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。

from http://my.oschina.net/xinxingegeya/blog/423632

赏

谢谢你请我吃糖果

  • MySQL
  • MySQL

扫一扫,分享到微信

MySQL复习笔记:锁
CSV 在 UTF 8 编码下 Excel 2007 打开乱码的解决方法
© 2014-2019 Awezome
Hexo Theme Zilia by Awezome