MySQL之高级特性(一)

高级特性

外键约束

InnoDB是目前MySQL中唯一支持外键的内置存储引擎，所以如果需要外键支持那选择就不多了。使用外键是有成本的。比如外键通常都要求每次在修改数据时都要在另一张表中多执行一次查找操作。虽然InnoDB强制外键使用索引，但还是无法消除这种约束检查的开销。如果外键列的选择性很低，则会导致一个非常大且选择性很低的索引。例如，在一个非常大的表上有status列，并希望限制这个状态列的取值，如果该列只能取三个值——虽然这个列本身很小，但是如果主键很大，那么这个索引就会很大——而且这个索引除了做这个外键限制，也没有任何其他的作用了。不过，在某些场景下，外键会提升一些性能。如果想确保两个相关表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高得多，此外，外键在相关数据得删除和更新上，也比在应用中维护要更高效，不过，外键维护操作是逐行进行得，所以这样得更新会比批量删除和更新要慢些。外键约束使得查询需要额外访问一些别的表，这也意味着需要额外的锁。如果向子表中写入一条记录，外键约束会让InnoDB检查对应的父表的记录，也就需要对父表对应记录进行加锁操作，来确保这条记录不会在这个事务完成之时就被删除了。这会导致额外的锁等待，甚至会导致一些死锁。因为没有直接访问这些表，所以这类死锁问题往往难以排查。有时，可以使用触发器来代替外键。对于相关数据的同时更新外键更合适，但是如果外键只是用作数值约束，那么触发器或者显式地限制取值会更好些。如果只是使用外键做约束，那通常在应用程序里实现该约束会更好。外键会带来很大的额外消耗。碰到过很多案例，在对性能进行剖析时发现外键约束就是瓶颈所在，删除外键后性能立即大幅提升。

全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询了。但是，如果你希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。全文索引有着自己独特的语法。没有索引也可以工作，如果有索引效率会更高。用于全文搜索的索引有着独特的结构，帮助这类查询找到匹配某些关键字的记录。
你可能没有在意过全文索引，不过至少应该对一种全文索引技术比较熟悉:互联网搜索引擎。虽然这类搜索引擎的索引对象是超大量的数据，并且通过其背后都不是关系型数据库，不过全文索引的基本原理都是一样的。全文索引可以支持各种字符内容的搜索(包括CHAR、VARCHAR和TEXT类型),也支持自然语言搜索和布尔搜索。在MySQL中全文索引有很多的限制,其实现也很复杂，但是因为它是MySQL内置的功能，而且满足很多基本的搜索需求，所以它的应用仍然非常广泛。
在标准的MySQL中，只有MyISAM引擎支持全文索引。事实上,MyISAM对全文索引的支持有很多的限制，例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等，这使得MyISAM的全文索引对于很多应用场景并不合适。所以，多数情况下建议使用别的解决方案，例如Sphinx、Lucene、Solr、Groonga、Xapian或者Senna。
MyISAM的全文索引作用对象是一个"集合"，这可能是某个数据表的义列，也可能是多个列。具体的，对数据表的某一条记录，MySQL会将需要索引的列全部拼接成一个字符串，然后进行索引，MyISAM的全文索引是一类特殊的B-Tree索引，共有两层。第一层是所有关键字，然后对于每一个关键字的第二层，包含的是一组相关的"文档指针"。全文索引不会索引文档对象中的所有词语，它会根据如下规则过滤一些词语:

1.停用词列表中的词都不会被索引。默认的停用词根据通用英语的使用来设置，可以使用参数ft_stopword_file指定一组外部文件来使用自定义的停用词。
2.对于长度小于ft_min_word_len的词语和长度小大于ft_max_word_len的词语，都不会被索引。
全文索引并不会存储关键字具体匹配在那一列，如果需要根据不同的列来进行组合查询，那么不需要针对每一列来建立多个这类索引。这也意味着不能在MATCH AGAINST子句中指定哪个列的相关行更重要。通常构建一个网站的搜索引擎是需要这样的功能，例如，你可能希望优先搜索出那些在标题中出现过的文档对象。如果需要这样的功能，则需要编写更复杂的查询语句。

自然语言的全文索引

在这里插入图片描述

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会搜索，即使不再停用词列表中出现，如果一个词语在超过50%的记录中都出现了，那么自然语言搜索不会搜索这类词语(在测试使用的一个常见错误就是，只是用很小的数据结合进行全文索引，所以总是无法返回结果，原因在于，每隔搜索u干建祠都可能在一半以上的记录里面出现过)。
全文索引的语法和普通查询略有不同。可以根据WHERE子句中的MATCH AGAINST来区分查询是否使用全文索引。我们来看一个示例。在标准的数据库Sakila中，数据表film_text在字段title和description上建立了全文索引:如图所示。下面时一个使用自然语言搜索的查询:

mysql> SELECT film_id, title, RIGHT(description ,25),
    -> MATCH (title, description) AGAINST('factory casualties') AS relevance
    -> FROM sakila.film_text
    -> WHERE MATCH(title, description) AGAINST('factory casualties');
+---------+-----------------------+---------------------------+-------------------+
| film_id | title                 | RIGHT(description ,25)    | relevance         |
+---------+-----------------------+---------------------------+-------------------+
|     831 | SPIRITED CASUALTIES   | a Car in A Baloon Factory | 8.640907287597656 |
|     126 | CASUALTIES ENCINO     | Face a Boy in A Monastery | 6.364917278289795 |
|     193 | CROSSROADS CASUALTIES | a Composer in The Outback | 6.364917278289795 |
|       3 | ADAPTATION HOLES      | rjack in A Baloon Factory | 2.275989532470703 |
|     103 | BUCKET BROTHERHOOD    | rjack in A Baloon Factory | 2.275989532470703 |
|     110 | CABIN FLASH           | Shark in A Baloon Factory | 2.275989532470703 |
|     186 | CRAFT OUTFIELD        | rator in A Baloon Factory | 2.275989532470703 |
|     187 | CRANES RESERVOIR      | ogist in A Baloon Factory | 2.275989532470703 |
|     291 | EVOLUTION ALTER       | lorer in A Baloon Factory | 2.275989532470703 |
|     299 | FACTORY DRAGON        | jack in The Sahara Desert | 2.275989532470703 |
|     345 | GABLES METROPOLIS     |  Chef in A Baloon Factory | 2.275989532470703 |
|     365 | GOLD RIVER            | ntist in A Baloon Factory | 2.275989532470703 |
|     369 | GOODFELLAS SALUTE     | d Cow in A Baloon Factory | 2.275989532470703 |
|     370 | GORGEOUS BINGO        | tress in A Baloon Factory | 2.275989532470703 |

MySQL将搜索词语分成两个独立的关键词进行搜索，搜索在title和description字段组成的全文索引上进行。注意，只有一条记录同时包含全部的两个关键词，查询结果时根据与关键词的相似度来进行排序的。(和普通查询不同，这类查询自动按照相似度进行排序。在使用全文索引进行排序的时候，MySQL无法再使用索引排序。所以如果不想使用文件排序的话，那么就不要在查询中使用ORDER BY 子句)。从上面的例子中可以看到，函数MATCH()将返回关键词匹配的相关度，是一个浮点数字。你可以根据相关度进行匹配，或者将词直接展现给用户。在一个查询中使用两次MATCH()函数并不会有额外的消耗，MySQL会自动识别并只进行一次搜索。不过，如果你将MATCH()函数放在ORDER BY 子句中，MySQL将会使用文件排序。在MATCH()函数中指定的列必须和在全文索引中指定的列完全相同，否则就无法啊使用全文索引。这是因为全文索引不会记录关键字是来自哪一列的。这也意味着无法使用完全索引来查询某个关键字是否在某一列上存在。这里介绍一个绕过该问题的办法:根据关键词在多个不同列的全文索引上的相关度来算出排名值，然后依次来排序。我们可以在某一列上加上如下索引:

mysql>ALTER TABLE film_text ADD FULLTEXT KEY(title);

这样，我们可以将title匹配乘以2来提高它的相似度的权重:

mysql> SELECT film_id, RIGHT ( description, 25 ), ROUND( MATCH ( title, description ) AGAINST ( 'factor casualties' ), 3 ) AS full_rel, ROUND( MATCH ( title ) AGAINST ( 'factory
casualties' ), 3 ) AS title_rel FROM sakila.film_text  WHERE MATCH ( title, description ) AGAINST ( 'factory casualties' )  ORDER BY (2 * MATCH ( title ) AGAINST ( 'factory casualties' )) DESC;
+---------+---------------------------+----------+-----------+
| film_id | RIGHT ( description, 25 ) | full_rel | title_rel |
+---------+---------------------------+----------+-----------+
|     299 | jack in The Sahara Desert |    0.000 |     9.000 |
|     831 | a Car in A Baloon Factory |    6.365 |     6.365 |
|     126 | Face a Boy in A Monastery |    6.365 |     6.365 |
|     193 | a Composer in The Outback |    6.365 |

因为上面的查询需要做文件排序，所以这并不是一个高效的做法

布尔全文索引

在布尔搜索中，用户可以查询中自定义某个被搜索的词语的相关性。布尔搜索通过停用词列表过滤掉那些"噪声"词，除此之外，布尔搜索还要求搜索关键词长度必须大于ft_min_word_len，同时小于ft_max_word_len（事实上，全文索引根本不会对太短或者太长的词语进行索引，但是这里说的不是一回事。一般地，MySQL本身并不会因为搜索关键词过长或过短而忽略这些词语，但是查询优化器的某些部分却可能这样做）。搜索返回的结果是未经排序的。当编写一个布尔搜索查询时，可以通过一些前缀修改时符来定制搜索，表中列出了最常用的修饰符。在这里插入图片描述
如表所示。还可以使用其他的操作，例如使用括号分组。基于此，就可以构造出一些复杂的搜索查询。还是继续使用sakila.film_text来举例，现在我们需要搜索既包含词"factory"又包含"casualties"的记录。在前面我们已经使用自然语言搜索查询实现找到这两个词中的任何一个SQL写法。使用布尔搜索查询，我们可以指定返回结果必须同时包含"factory"和"casualties":

mysql> SELECT film_id, title, RIGHT(description, 25) FROM sakila.film_text WHERE MATCH(title, description) AGAINST ('+factory +casualties' IN BOOLEAN MODE);
+---------+---------------------+---------------------------+
| film_id | title               | RIGHT(description, 25)    |
+---------+---------------------+---------------------------+
|     831 | SPIRITED CASUALTIES | a Car in A Baloon Factory |
+---------+---------------------+---------------------------+
1 row in set (0.00 sec)

查询中还可以使用括号进行"短语搜索"，让返回结果精确匹配指定的短语:

mysql> SELECT film_id,title, RIGHT(description, 25) FROM sakila.film_text WHERE MATCH(title, description) AGAINST ('"spirited casualties"' IN BOOLEAN MODE);
+---------+---------------------+---------------------------+
| film_id | title               | RIGHT(description, 25)    |
+---------+---------------------+---------------------------+
|     831 | SPIRITED CASUALTIES | a Car in A Baloon Factory |
+---------+---------------------+---------------------------+
1 row in set (0.00 sec)

短语搜索的速度会比较慢。只使用全文索引是无法判断是否精确匹配短语的，通常还需要查询原文确定记录中是否包含完整的短语。由于需要进行回表过滤，所以速度会笔记哦哎慢。要完成上面的查询，MySQL需要先从索引中找出所有同时包含"spirited"和"casualties"的索引条目，然后取出这些记录再判断是否精确匹配短语，因为这个操作会先从索引中过滤出一些记录，所以通常认为这样做的速度是很快的——比LIKE操作要快很多。事实上，这样做的确很快，但是搜索的关键词不能是太常见的词语。如果搜索的关键词太常见，因为前一步的过滤会返回太多的记录需要判断，因此LIKE操作反而更快。这种情况下LIKE操作是完全的顺序读，相比索引返回值的随机读。会快很多。只有MyISAM引擎才能使用布尔全文索引，但并不是一定要有全文索引才能使用布尔全文搜索。当没有全文搜索的时候，MySQL就通过全表扫描来实现。所以，你甚至还可以在多表上使用布尔全文索引，例如在一个关联结果上进行。只不过，因为是全表扫描速度可能会很慢