MYSQL 索引----B-Tree和全文索引

一、索引基础
    在MYSQL中，存储引擎用类似的方法是用索引，其先在索引中找到对应的值，然后根据匹配的索引记录找到对应的数据行。
    索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MYSQL只能高效地使用索引的最左前缀列。创建一个包含两列的索引，和创建两个只包含一列的索引是大小不相同的，下面将详细介绍。

二、B-Tree 索引
    B-Tree 是使用最多的一种索引，存储引擎以不同的方式使用B-Tree 索引性能也各有不同。例如，MyISAM 使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置应用被索引的行，而InnoDB则根据主键引用被索引的行。
    B-Tree 对索引是顺序组织存储的，所以很适合查找范围数据。例如“找出所有以I到K开头的名字”，这样的查找效率非常高。
{
    B-Tree 索引的限制：
    假设有如下表
    CREATE TABLE People(
        last_name  varchar(50)   not null,
        first_name varchar(50)   not null,
        dob        date          not null,
        gender     enum('m','f') not null,
        key (last_name,first_name,dob)
    );
    1、如果不是按照索引的最左列开始查找，则无法使用索引。例如无法用例子中的索引查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似的，也无法查找姓氏以某个字母结尾的人。
    2、不能跳过索引中的列。例如，不能查找姓Smith并且在某个日期出生的人。如果不指定名（first_name），则MYSQL只能使用索引的第一列。
    3、如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找。例如，WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob = '1991-11-11',这个查询只能使用索引的前两列，因为这里的LIKE是一个范围条件。
    4、如果查询中的列不是独立的列，则MYSQL就不会使用索引。例如：SELECT actor_id FROM sakila.actor WHERE actor_id+1=5; 这里的actor_id就不能用索引，所以我们始终要记得将索引列单独放在比较符号的一侧。
}
{
    MyISAM 的压缩索引：
    压缩索引：索引块中的第一个值是"perform"，第二个值是"performance"，那么第二个值的前缀压缩后存储的是类似"7,ance"这样的形式。
    对于CPU密集型应用，因为扫描需要随机查找，压缩索引是的MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引可能只需要1/10大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。
}
{
    避免冗余和重复索引
}

三、空间数据索引(R-Tree)
    MyISAM 表支持空间索引，可以用作地理数据存储。MYSQL 的GIS支持并不完善，开源关系数据库系统对GIS的解决方案做的比较好的是PostgreSQL的PostGIS。

四、全文索引
    全文索引是查找文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。
{
    全文索引有着自己独特的语法。没有索引页可以工作，如果有索引效率会更高。用于全文搜索的索引有着独特的结构，帮助这类查询找到匹配的关键字记录。
    在标准的MySql中，只有MyISAM引擎支持全文索引。不过在MySQL5.6中，InnoDB已经实验性质地支持全文索引了。除此，还有第三方的存储引擎，如Groonga，也支持全文索引。
    事实上，MyISAM对全文索引的支持有很多的限制，例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等，这使得MyISAM的全文索引对于很多应用场景并不合适。所以，多数情况下我们建议使用别的解决方案，例如Sphinx、Lucene、Solr、Groonga、Xapian或者Senna。（实际上，本人现在用的MYSQL是5.6以后的版本，这两段话已过时，仅供参考）
}
{
     MyISAM的全文索引是一类特殊的B-Tree索引，共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的“文档指针”。全文索引不会索引文档对象中的所有词语，它会根据如下规则过滤一些词语：
     1、停用词列表的词都不会被索引。
     2、对于大于ft_min_word_len的词语和长度小于ft_max_word_len的词语，都不会被索引。
     全文索引并不会存储关键字具体匹配在哪一列，如果需要根据不同的列来进行组合查询，那么不需要针对每一行来简历这类索引。
     这也意味着不能在MATCH AGAINST子句中指定哪个列的相关性更重要。通常构建一个网站的搜索引擎是需要这样的功能，例如，你可能希望优先搜索出那些在标题中出现过的文档对象。如果需要这样的功能，则需要更复杂的查询语句。
}
{
    自然语言的全文索引：
    自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会搜索，即使不在停用词列表中出现，如果一个词语在超过50%的记录中都出现了，那么自然语言搜索将不会搜索这类词语。
    全文索引根据WHERE子句中的MATCH AGAINST来区分查询是否使用全文索引。函数MATCH()将返回关键词匹配的相关度。例如：
    SELECT film_id,RIGHT(description,25),
    Round(MATCH(title,description) AGAINST('factory casualties'),3) AS full_rel,
    Round(MATCH(title) AGAINST('factory casualties'),3) AS title_rel,
    FROM sakila.film_text
    WHERE MTACH(title,description) AGAINST('factory casualties')
    ORDER BY (2*MATCH(title) AGAINST('factory casualties')) + MATCH(title,description) AGAINST('factory casualties') DESC;
}
{
    布尔全文索引:
    在布尔搜索中，用户可以在查询中自定义某个被搜索的的词语的相关性。布尔搜索通过停用词列表过滤掉那些“噪声”词，除此之外，布尔搜索还要求关键词长度必须大于ft_min_word_len，同时小于ft_max_word_len。搜索返回的结果是未经排序的。
    比尔全文索引通用修饰符：
    dinosaur    包含“dinosaur”的行rank值更高
    ~dinosaur   包含“dinosaur”的行rank值更高
    +dinosaur   行记录必须包含"dinosaur"
    -dinosaur   行记录不可以包含"dinosaur"
    dino*       包含以"dino"开头的单词的行rank值更高

    查询中还可以使用括号进行“短语搜索”，让返回结果精确匹配指定的短语：
    SELECT film_id,title,RIGHT(description,25)
    FROM sakila.film_text
    WHERE MATCH(title,description) AGAINST('"spirited casualties"' IN BOOLEAN MODE);
    通常认为这样做的速度比LIKE操作要快很多。但是，搜索的关键词不能是太常见的词语，否则，效果会相反。
}
{
    全文索引的限制:
    ... WHERE MATCH(content) AGAINST ('High Performance MySQL') AND autor=123;
    这种情况下，因为这里使用了MATCH AGAINST，而且恰好上面有全文索引，所以MySQL优先选择使用全文索引，即先搜索所有的文档，查找是否有包含关键字的文档，所以MYSQL优先选择使用全文索引，然后返回记录看看作者是否是123。所以这里也就没有使用author字段上的索引。
    一个替代的方案是将author列包含到全文索引中。可以在author列的值前面附上一个不常见的前缀，然后将这个带前缀的值放到一个单独的filters列种，并单独维护该列。

    ...WHERE MATCH(content,filters) AGAINST('High Performance MySQL + author_id_123' IN BOOLEAN MODE);

}

转载:   http://www.justforfun.cn:88/