MySQL 目录
日期：2014-05-16 浏览次数：20775 次
MySQL 索引
是否创建索引
组合索引：
前缀索引的功能仅仅适用于字段前缀比较随机重复性很小的字段。如果我们需要索引的字段的前缀内容有较多的重复，索引的过滤性自然也会随之降低，通过索引所访问的数据量就会增加，这时候前缀索引虽然能够减少存储空间消耗，但是可能会造成Query 访问效率的极大降低，反而得不偿失。
图示中左边为Clustered 形式存放的Primary Key，右侧则为普通的B-Tree 索引。两种索引在Root Node 和Branch Nodes 方面都还是完全一样的。而Leaf Nodes 就出现差异了。在Primary Key中，Leaf Nodes 存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数据以主键值有序的排列。而Secondary Index 则和其他普通的B-Tree 索引没有太大的差异，只是在Leaf Nodes 出了存放索引键的相关信息外，还存放了Innodb
的主键值。
所以，在Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过Secondary Index 来访问数据的话，Innodb 首先通过Secondary Index 的相关信息，通过相应的索引键检索到Leaf Node之后，需要再通过Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。
MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且MyISAM 存储引擎的索引和Innodb 的Secondary Index 的存储结构也基本相同，主要的区别只是MyISAM 存储引擎在Leaf Nodes 上面出了存放索引键信息之外，再存放能直接定位到MyISAM 数据文件中相应的数据行的信息（如Row Number），但并不会存放主键的键值信息。
Hash 索引在MySQL 中使用的并不是很多，目前主要是Memory 存储引擎使用，而且在Memory 存储引擎中将Hash 索引作为默认的索引类型。所谓Hash 索引，实际上就是通过一定的Hash 算法，将需要索引的键值进行Hash 运算，然后将得到的Hash 值存入一个Hash 表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash 运算，然后再和Hash 表中的Hash 值进行比较并得出相应的信息。
在Memory 存储引擎中，MySQL 还支持非唯一的Hash 索引
R-Tree 索引
R-Tree 索引可能是我们在其他数据库中很少见到的一种索引类型，主要用来解决空间数据检索的问题。
在MySQL 中，支持一种用来存放空间信息的数据类型GEOMETRY，且基于OpenGIS 规范。在MySQL5.0.16 之前的版本中，仅仅MyISAM 存储引擎支持该数据类型，但是从MySQL5.0.16 版本开始，BDB，Innodb，NDBCluster 和Archive 存储引擎也开始支持该数据类型。当然，虽然多种存储引擎都开始支持GEOMETRY 数据类型，但是仅仅之后MyISAM 存储引擎支持R-Tree 索引。在MySQL 中采用了具有二次分裂特性的R-Tree 来索引空间数据信息，然后通过几何对象（MRB）信息来创建索引。
虽然仅仅只有MyISAM 存储引擎支持空间索引（R-Tree
索引的好处：
在数据库中个表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件的时候可以极大的提高检索效率，加快检索时间，降低检索过程中所需要读取的数据量。
降低数据的排序成本（主要消耗的是我们的内存和CPU 资源）
当我们的Query 语句中包含排序分组操作的时候，如果我们的排序字段和索引键字段刚好一致，MySQL Query Optimizer就会告诉mysqld 在取得数据之后不用排序了，因为根据索引取得的数据已经是满足客户的排序要求。
分组操作没办法直接利用索引完成。但是分组操作是需要先进行排序然后才分组的，所以当我们的Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么mysqld 同样可以利用到索引已经排好序的这个特性而省略掉分组中的排序操作。
当我们的Query 语句中包含排序分组操作的时候，如果我们的排序字段和索引键字段刚好一致，MySQL Query Optimizer就会告诉mysqld 在取得数据之后不用排序了，因为根据索引取得的数据已经是满足客户的排序要求。
分组操作没办法直接利用索引完成。但是分组操作是需要先进行排序然后才分组的，所以当我们的Query 语句中包含分组操作，而且分组字段也刚好和索引键字段一致，那么mysqld 同样可以利用到索引已经排好序的这个特性而省略掉分组中的排序操作。
索引的弊端
索引是完全独立于基础数据之外的一部分数据,更新表的同时，也更新索引数据,索引还会带来存储空间资源消耗的增长
是否创建索引
较频繁的作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合创建索引
组合索引：
可能有些朋友会说，那我们可以通过创建多个单键索引啊。确实，我们可以将WHERE 子句中的每一个字段都创建一个单键索引。但是这样真的有效吗？在这样的情况下，MySQLQueryOptimizer 大多数时候都只会选择其中的一个索引，然后放弃其他的索引。即使他选择了同时利用两个或者更多的索引通过INDEX_MERGE 来优化查询，可能所收到的效果并不会比选择其中某一个单键索引更高效。因为如果选择通过INDEX_MERGE 来优化查询，就需要访问多个索引，同时还要将通过访问到的几个索引进行merge操作，所带来的成本可能反而会比选择其中一个最有效的索引来完成查询更高。
前缀索引的功能仅仅适用于字段前缀比较随机重复性很小的字段。如果我们需要索引的字段的前缀内容有较多的重复，索引的过滤性自然也会随之降低，通过索引所访问的数据量就会增加，这时候前缀索引虽然能够减少存储空间消耗，但是可能会造成Query 访问效率的极大降低，反而得不偿失。
我们的Query 由于存在多个过滤条件，而这多个过滤条件可能会存在于两个或者更多的索引中。在这种场景下，MySQL Query Optimizer 一般情况下都能够根据系统的统计信息选择出一个针对该Query 最优的索引完成查询，但是在有些情况下，可能是由于我们的系统统计信息的不够准确完整，也可能是MySQL Query Optimizer 自身功能的缺陷，会造成他并没有选择一个真正最优的索引而选择了其他查询效率较低的索引。在这种时候，我们就不得不通过认为干预，在Query
中增加Hint 提示MySQL Query Optimizer 告诉他该使用哪个索引而不该使用哪个索引，或者通过调整查询条件来达到相同的目的。
force(index)
在Innodb 存储引擎中，存在两种不同形式的索引，一种是Cluster 形式的主键索引（Primary Key），另外一种则是和其他存储引擎（如MyISAM 存储引擎）存放形式基本相同的普通B-Tree 索引，这种索引在Innodb 存储引擎中被称为Secondary Index。下面我们通过图示来针对这两种索引的存放形式做一个比较。
所以，在Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过Secondary Index 来访问数据的话，Innodb 首先通过Secondary Index 的相关信息，通过相应的索引键检索到Leaf Node之后，需要再通过Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。
MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空的键而已。而且MyISAM 存储引擎的索引和Innodb 的Secondary Index 的存储结构也基本相同，主要的区别只是MyISAM 存储引擎在Leaf Nodes 上面出了存放索引键信息之外，再存放能直接定位到MyISAM 数据文件中相应的数据行的信息（如Row Number），但并不会存放主键的键值信息。
Hash 索引在MySQL 中使用的并不是很多，目前主要是Memory 存储引擎使用，而且在Memory 存储引擎中将Hash 索引作为默认的索引类型。所谓Hash 索引，实际上就是通过一定的Hash 算法，将需要索引的键值进行Hash 运算，然后将得到的Hash 值存入一个Hash 表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash 运算，然后再和Hash 表中的Hash 值进行比较并得出相应的信息。
在Memory 存储引擎中，MySQL 还支持非唯一的Hash 索引
Full-text 索引也就是我们常说的全文索引，目前在MySQL 中仅有MyISAM 存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR 和TEXT 这三种数据类型的列可以建Full-text 索引。
一般来说，Fulltext 索引主要用来替代效率低下的LIKE '%***%' 操作。实际上，Full-text 索引并不只是能简单的替代传统的全模糊LIKE 操作，而且能通过多字段组合的Full-text 索引一次全模糊匹配多个字段。
一般来说，Fulltext 索引主要用来替代效率低下的LIKE '%***%' 操作。实际上，Full-text 索引并不只是能简单的替代传统的全模糊LIKE 操作，而且能通过多字段组合的Full-text 索引一次全模糊匹配多个字段。
函数 MATCH() 对照一个文本集(包含在一个 FULLTEXT 索引中的一个或多个列的列集)执行一个自然语言搜索一个字符串。搜索字符串做为 AGAINST() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
当 MATCH() 被使用在一个 WHERE 子句中时，返回的记录行被自动地以相关性从高到低的次序排序。相关性值是非负的浮点数字。零相关性意味着不相似。
当 MATCH() 被使用在一个 WHERE 子句中时，返回的记录行被自动地以相关性从高到低的次序排序。相关性值是非负的浮点数字。零相关性意味着不相似。
R-Tree 索引
R-Tree 索引可能是我们在其他数据库中很少见到的一种索引类型，主要用来解决空间数据检索的问题。
在MySQL 中，支持一种用来存放空间信息的数据类型GEOMETRY，且基于OpenGIS 规范。在MySQL5.0.16 之前的版本中，仅仅MyISAM 存储引擎支持该数据类型，但是从MySQL5.0.16 版本开始，BDB，Innodb，NDBCluster 和Archive 存储引擎也开始支持该数据类型。当然，虽然多种存储引擎都开始支持GEOMETRY 数据类型，但是仅仅之后MyISAM 存储引擎支持R-Tree 索引。在MySQL 中采用了具有二次分裂特性的R-Tree 来索引空间数据信息，然后通过几何对象（MRB）信息来创建索引。
虽然仅仅只有MyISAM 存储引擎支持空间索引（R-Tree
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
