MySQL递归查询树结构方法总结

在日常开发中我们经常会遇到树形结构数据的处理,如:组织机构之类的情况。在表结构通常会采用idparent_id这种设计方案。一个常见的需求:查询某个节点下的所有子节点。

为方便后续说明,在此统一约定表名为:t_org,其定义如下:

字段 类型 说明
id bigint(20) NOT NULL 机构编码
parent_id bigint(20) 上级机构编码
desc varchar(200) 备注

查询实现方案

表中现有如下测试数据

测试数据

组织机构层级数确定时

可以采用自关联LEFT JOIN方式进行查询获取结果。

查询结果如下:

查询结果

组织机构层级数不确定时

当组织机构层级数不确定时,无法使用上述方式进行查询

可以通过自定义函数方式实现查询

在上面函数中使用到了两个MySQL函数

GROUP_CONCAT(expr)
该函数会从expr中连接所有非NULL的字符串。如果没有非 NULL 的字符串,那么它就会返回NULL。语法如下:

注意事项:GROUP_CONCAT查询结果默认最大长度限制为1024,该值是系统变量group_concat_max_len的默认值,可以通过SET [GLOBAL | SESSION] group_concat_max_len = val;更改该值。

FIND_IN_SET(str,strlist)
该函数返回一个1~N的值表示strstrlist中的位置。
该函数结合WHERE使用对结果集进行过过滤

函数使用方式

方案缺点

返回结果长度受VARCHAR最大长度限制,特别是当组织机构比较庞大时该方案会失效。下面我们可以使用存储过程结合临时表来解决这个问题。

存储过程+临时表

使用存储过程结合临时表的方案需要创建两个存储过程,一个用于递归查询所有节点并将数据写入临时表中,另一个负责创建临时表、清空临时表数据,触发查询调用动作。
首先,定义第一个存储过程,如下:

如上所示,逻辑比较简单。接下来定义第二个存储过程,如下;

使用方式如下

至此,我们在可以处理无限层级的树形结构数据。

MyBatis调用存储过程

MyBatis中我们可以使用如下方式对存储过程进行调用

需要指定statementTypeCALLABLE表示需要执行的是一个存储过程,statementType默认值为PREPARED

总结

存储过程和函数的方式虽然简化了代码处理逻辑,但是使用函数和存储过程也有其缺点,主要体现在函数和存储过程在线上业务中其性能不容易监控,针对慢查询优化等方面从DBA角度来讲不是那么方便,所以在使用函数和存储过程时需要进行相应的权衡。

0

编写数据库 sql 给表里某列添加唯一约束

假设我们有一个mysql数据库,建立了一个user表,字段分别是id,username,password。

主键是id,username是不可以重复的,所以需要给username添加唯一性约束。

添加语句如下

语句解释

注意事项

当你表中要添加唯一约束的列中有重复的值时会添加约束失败,这时把重复的值删除就可以了。

0

MySQL 全文索引模糊查询 Match Against 与 Like 比较

1.概要

InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性,之前只有MyISAM引擎支持FULLTEXT索引。对于FULLTEXT索引的内容可以使用MATCH()…AGAINST语法进行查询。

为了在InnoDB驱动的表中使用FULLTEXT索引MySQL5.6引入了一些新的配置选项和INFORMATION_SCHEMA表。比如,为了监视一个FULLTEXT索引中文本处理过程的某一方面可以查询INNODB_FT_CONFIG,INNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE,INNODB_FT_DEFAULT_STOPWORD,INNODB_FT_DELETED和INNODB_FT_BEING_DELETED这些表。可以通过innodb_ft_num_word_optimize和innodb_optimize_fulltext_only选项控制OPTIMIZETABLE命令对InnoDB FULLTEXT索引的更新。

2.相关库表

INFORMATION_SCHEMA库中与InnoDB全文索引相关的表如下:

INNODB_SYS_INDEXES:提供了InnoDB索引的状态信息。
INNODB_SYS_TABLES:提供了InnoDB表的状态信息。
INNODB_FT_CONFIG:显示一个InnoDB表的FULLTEXT索引及其相关处理的元数据。
INNODB_FT_INDEX_TABLE:转化后的索引信息用于处理基于InnoDB表FULLTEXT索引的文本搜索。一般用于调试诊断目的。使用该表前需先配置innodb_ft_aux_table配置选项,将其指定为想要查看的含FULLTEXT索引的InnoDB表,选项值的格式为database_name/table_name。配置了该选项后INNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表将被填充与innodb_ft_aux_table配置选项指定的表关联的搜索索引相关信息。
INNODB_FT_INDEX_CACHE:向含FULLTEXT索引的InnoDB表插入数据后新插入数据转后的索引信息。表结构与INNODB_FT_INDEX_TABLE一致。为含FULLTEXT索引的InnoDB表执行DML操作期间重组索引开销很大,因此将新插入的被索引的词单独存储于该表中,当且仅当为InnoDB表执行OPTIMIZE TABLE语句后才将新的转换后的索引信息与原有的主索引信息合并。使用该表前需先配置innodb_ft_aux_table配置选项。
INNODB_FT_DEFAULT_STOPWORD:在InnoDB表上创建FULLTEXT索引所使用的默认停止字表。
INNODB_FT_DELETED:记录了从InnoDB表FULLTEXT索引中删除的行。为了避免为InnoDB的FULLTEXT索引执行DML操作期间重组索引的高开销,新删除的词的信息单独存储于此表。当且仅当为此InnoDB表执行了OPTIMIZE TABLE操作后才会从主搜索索引中移除已删除的词信息。使用该表前需先配置innodb_ft_aux_table选项。
INNODB_FT_BEING_DELETED:为含FULLTEXT索引的InnoDB表执行OPTIMIZE TABLE操作时会根据INNODB_FT_DELETED表中记录的文档ID从InnoDB表的FULLTEXT索引中删除相应的索引信息。而INNOFB_FT_BEING_DELETED表用于记录正在被删除的信息,用于监控和调试目的。

3.相关配置选项

Name Cmd-
Line
Option file System Var Status Var Scope Dynamic
innodb_ft_aux_table:指定包含FULLTEXT索引的InnoDB表的的名称。该变量在运行时设置用于诊断目的。设置该值后INNODB_FT_INDEX_TABLE, INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG,INNODB_FT_DELETED和INNODB_FT_BEING_DELETED表将被填充与innodb_ft_aux_table指定的表关联的搜索索引相关信息。 Yes Yes Yes Global Yes
innodb_ft_cache_size:当创建一个InnoDB FULLTEXT索引时在内存中存储已解析文档的缓存大小。 Yes Yes Yes Global No
innodb_ft_enable_diag_print:是否开启额外的全文搜索诊断输出。 Yes Yes Yes Global Yes
innodb_ft_enable_stopword:是否开启停止字。InnoDB FUllTEXT索引被创建时为其指定一个关联的停止字集。(若设置了innodb_ft_user_stopword_table则停止字由该选项指定的表获取,若没有设置innodb_ft_user_stopword_table而设置了innodb_ft_server_stopword_table则停止字由该选项指定的表获取,否则使用内置的停止字。) Yes Yes Yes Global Yes
innodb_ft_max_token_size:存储在InnoDB的FULLTEXT索引中的最大词长。设置这样一个限制后可通过忽略过长的关键字等有效降低索引大小从而加速查询。 Yes Yes Yes Global No
innodb_ft_min_token_size:存储在InnoDB的FULLTEXT索引中的最小词长。增加该值后会忽略掉一些通用的没有显著意义的词汇从而降低索引大小继而加速查询。 Yes Yes Yes Global No
innodb_ft_num_word_optimize:为InnoDB FULLTEXT索引执行OPTIMIZE操作每次所处理的词数。因为在含有全文搜索索引的表中执行批量的插入或更新操作需要大量的索引维护操作来合并所有的变化。因此,一般会运行一系列OPTIMIZE TABLE语句,每次从上一次的位置开始,处理指定数目的词,知道搜索索引被完全更新。 Yes Yes Yes Global Yes
innodb_ft_server_stopword_table:含有停止字的表,在创建InnoDB FULLTEXT索引时或忽略表中的停止字。停止字表需为InnoDB表,且在指定前应当已存在。 Yes Yes Yes Global Yes
innodb_ft_sort_pll_degree:为较大的表构建搜索索引时用于索引和记号化文本的并行线程数。 Yes Yes Yes Global No
innodb_ft_user_stopword_table:含有停止字的表,在创建InnoDB FULLTEXT索引时或忽略表中的停止字。停止字表需为InnoDB表,且在指定前应当已存在。 Yes Yes Yes Both Yes
innodb_optimize_fulltext_only:改变OPTIMIZE TABLE语句对InnoDB表操作的方式。 Yes Yes Yes Global Yes

4.全文搜索功能

全文搜索的语法:MATCH(col1,col2,…) AGAINST (expr[search_modifier])。其中MATCH中的内容为已建立FULLTEXT索引并要从中查找数据的列,AGAINST中的expr为要查找的文本内容,search_modifier为可选搜索类型。search_modifier的可能取值有:IN NATURAL LANGUAGEMODE、IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION、IN BOOLEAN MODE、WITH QUERY EXPANSION。search_modifier的每个取值代表一种类型的全文搜索,分别为自然语言全文搜索、带查询扩展的自然语言全文搜索、布尔全文搜索、查询扩展全文搜索(默认使用IN NATURAL LANGUAGE MODE)。

MySQL中全文索引的关键字为FULLTEXT,目前可对MyISAM表和InnoDB表的CHAR、VARCHAR、TEXT类型的列创建全文索引。全文索引同其他索引一样,可在创建表是由CREATE TABLE语句创建也可以在表创建之后用ALTER TABLE或者CREATE INDEX命令创建(对于要导入大量数据的表先导入数据再创建FULLTEXT索引比先创建索引后导入数据会更快)。

4.1自然语言全文搜索

自然语言全文搜索是MySQL全文搜索的默认搜索方式,实现从一个文本集合中搜索给定的字符串。这里,文本集合指的是指由FULLTEXT索引的一个或者多个列。

建表,并给title,body字段加FULLTEXT索引

导入数据

例1:

可以看到,语句查找到了包含指定内容的行。实际上,返回的行是按与所查找内容的相关度由高到低的顺序排列的。这个相关度的值由WHERE语句中的MATCH (…) AGAINST (…)计算所得,是一个非负浮点数。该值越大表明相应的行与所查找的内容越相关,0值表明不相关。该值基于行中的单词数、行中不重复的单词数、文本集合中总单词数以及含特定单词的行数计算得出。

例2:

由上例可知MATCH (…) AGAINST (…)实际上会计算一个相关值,可通过下例来验证。

可以看到,所得结果的第二列即为该行与查找内容的相关度。上例1中所得结果的顺序就是按此相关度排列的。

例3:

若想既看到查找到的结果又需要了解具体的相关度,可用下述方法达成。

可以看到,通过在查找部分和条件部分分别使用相同的MATCH(…) AGAINST(…)可以同时获取两方面的内容(不会增加额外开销,优化器知道两个MATCH(…) AGAINST(..)是相同的,只会执行一次该语句)

注意事项

默认情况下全文搜索大小写不敏感,如上例1,查找的内容为‘database’但含有‘DataBase’的行也会返回。可以通过为FULLTEXT索引列所使用的字符集指定一个特定的校对集来改变这种行为。

考虑下述两个SELECT语句:

这两条查询语句均可返回匹配的行数。但第一条语句可以利用基于WHERE从句的索引查找,因此在匹配的行数较少时速度较第二句更快。第二句执行了全表扫描,因此在匹配的行数较多时较第一句更快。

MATCH()函数中的列必须与FULLTEXT索引中的列相同。如MATCH(title,body)与FULLTEXT(title,body)。若要单独搜索某列,如body列,则需另外单独为该列建全文索引FULLTEXT(body),然后用MATCH(body)搜索。

对于InnoDB表MATCH()中的列仅能来自于同一个表,因为索引不能快多张表(MyISAM表的的布尔搜索因为可以不使用索引所以可以跨多张表中的列,但速度很慢)。

全文搜索不仅可以搜索类似例1中‘database’这样的单个的单词,还可以搜索句子(这才是其被称为‘全文搜索‘的关键),如例3。全文搜索把任何数字、字母、下划线序列看作是单词,还可以包含“’”如aaa’bbb备解析为一个单词,但aaa’’bbb备解析为两个单词,FULLTEXT解析器自动移除首尾的“’”,如’aaa’bbb’被解析为aaa’bbb。FULLTEXT解析器用“ ”(空格)、“,”(逗号)“.”(点号)作为默认的单词分隔符,因此对于不使用这些分隔符的语言如汉语来说FULLTEXT解析器不能正确的识别单词,对于这种情况需做额外处理。

全文搜索中一些单词会被忽略。首先是过短的单词,InnoDB全文搜索中默认为3个字符,MyISAM默认4个字符,可通过在创建FULLTEXT索引前改变配置参数来改变默认行为,对于InnoDB该参数为:innodb_ft_min_token_size,对于MyISAM为ft_min_word_len;另外stopword列表中的单词会被忽略。stopword列表包含诸如“the”、“or”、“and”等常用单词,这些词通常被认为没有什么语义价值。MySQL由内建的停止字列表,但是可以所使用自定义的停止字列表来覆盖默认列表。对于InnoDB控制停止字的配置参数为innodb_ft_enable_stopword,innodb_ft_server_stopword_table,  innodb_ft_user_stopword_table对于MyISAM参数为ft_stopword_file。

文本集合和查询语句中的单词的权重由该单词在集合或语句中的重要性确定。单词在越多的行中出现则该单词的权重越低,因为这表明其在文本集合中的语义价值较小。反之权重越高。例1中提到的相关度计算也与此值有关。

4.2布尔全文搜索

如果在AAGAINST()函数中指定了INBOOLEN MODE模式,则MySQL会执行布尔全文搜索。在该搜索模式下,待搜索单词前或后的一些特定字符会有特殊的含义。

例1:

该查询语句中“MySQL”前的“+”表明结果中必须包含“MySQL”而“YourSQL”前的“-”表明所得结果中不能含有“YourSQL”。

除了“+”和“-”外还有其他一些特定的字符。如空字符表明后跟的单词是可选的,但出现的话会增加该行的相关性;“@distance”用于指定两个或多个单词相互之间的距离(以单词度量)需在指定的范围内;“>”用于增加后跟单词对其所在行的相关性的贡献“<”用于降低该贡献;“()”用于将单词分组为子表达式且可以嵌套;“~”是后跟单词对其所在行的相关性的贡献值为负;“*”为普通的通配符,若为单词指定了通配符,那么即使该单词过短或者出现在了停止字列表中它也不会被移除;“””,括在双引号中的短语指明行必须在字面上包含指定的短语,全文搜索将短语分割为词后在FULLTEXT索引中搜索。非字字符无需完全匹配,如”test phrase”可以匹配含”test phrase”和”test phrase”的行,但匹配含”phrase test”的行。

例2:

找到包含MySQL或者YourSQL的行

例3:

找到包含同时MySQL和YourSQL的行

例4:

找到必须包含MySQl的行,YourSQL可有可无,但有YourSQL会增加相关性。

例5:

找到包含必须包含MySQL的行,YourSQL可有可无,若出现了YourSQL则会降低其所在行的相关性。

例6:

找到必须同时包含MySQL以及Security或Optimizing的行Security会增加所在行的相关性,而Optimizing会降低所在行的相关性。

例7:

找到包含da*的行。如包含DataBase、database等。

例8:

找到包含“MySQL Tutorial”短语的行。

布尔全文搜索的一些特点

  • MyISAM全文搜索会忽略至少在一半以上数据行中出现的单词(也即所谓的50%阈值),InnoDB无此限制。而在布尔全文搜索中MyISAM的50%阈值不生效。
  • 停止字列表也适用于布尔全文搜索。
  • 最小和最大词长全文搜索参数也适用于布尔全文搜索
  • MyISAM中的布尔搜索在FULLTEXT索引不存在的时候仍可工作,但速度很慢。而InnoDB表的各类全文搜索必须有FULLTEXT索引,否则会出现找不到与指定列相匹配的FULLTEXT索引的错误
  • InnoDB中的全文搜索不支持在单一搜索单词前使用多个操作符如“++MySQL”。MyISAM中全文搜索可以处理这种情况,但是会忽略除了紧邻单词之外的其他操作符。

4.3查询扩展全文搜索

某些时候我们通过全文搜索来查找包含某方面内容的行,比如我们搜索“database”,实际上我们期望返回结果不仅仅是仅包含“database”单词的行,一些包含“MySQL”、“SQLServer”、“Oracle”、“DB2”、“RDBMS”等的行也期望被返回。这个时候查询扩展全文搜索就能大显身手。

通过在AGAINST()函数中指定WITHQUERY EXPANSION 或者IN NATURAL MODE WITH QUERY EXPANSION可以开启查询扩展全文搜索模式。其工作原理是执行两次搜索,第一次用给定的短语搜索,第二次使用给定的短语结合第一次搜索返回结果中相关性非常高的一些行进行搜索。

例1:

使用自然语言搜索返回了包含“database”的行。

例2:

使用查询扩展全文搜索,不进返回了包含“database”的行,也返回了与例1中返回的行的内容相关的行。

注意事项

因为查询扩展会返回一些不相关的内容,因此会显著的引入噪声。索引仅当要查询的短语较短时才在考虑使用查询扩展全文搜索。

4.4全文搜索的停止字

上文已经简单介绍过了停止字列表,这里做详细介绍。停止字列表用MySQL Server所使用的字符集和校对集(分别由character_set_server和collation_server两个参数控制)载入并执行搜索。若用于全文索引和搜索的停止字文件或者停止字表使用了与MySQL Server不同的字符集和校对集会则导致查找停止字时错误的命中或未命中。

停止字查找的大小写敏感性也依赖于MySQL Server所使用的校对集,例如校对集为latin1_swedish_ci则查找是大小写不敏感的,若校对集为latin1_geberal_cs或者latin1_bin则查找是大小写敏感的。

InnoDB默认的停止字列表相对较短(因为技术上的或者文学等方面的文档常使用较短的词作为关键字或者有其他显著意义)。InnoDB默认的停止字列表存储在information_schema.innodb_ft_default_stopword表中。当然也可以通过自定义与innodb_ft_default_stopword表结构相同的表,填充期望的停止字,然后通过innodb_ft_server_stopword_table选项指定自定义的停止字表db_name/table_name,来改变默认的行为。另外还可以为innodb_ft_user_stopword_table选项指定含停止字的表,若同时指定了innodb_ft_default_stopword和innodb_ft_user_stopword_table则将使用后者指定的停止字表。上述操作改变所使用停止字表的操作需在创建全文索引前完成。且在指定所使用的停止字表时,表必须已经存在。

对于MyISAM可通过 ft_stopword_file选项指定所使用的停止字列表。MyISAM默认的停止字列表可在MySQL源码的 storage/myisam/ft_static.c文件中找到。

4.5全文搜索的限制

  • 目前只有InnoDB和MyISAM引擎支持全文搜索。其中InnodB表对FULLTEXT索引的支持从MySQL5.6.4开始。
  • 分区表不支持全文搜索。
  • 全文索引适用于多数多字节字符集。例外情况是:对于Unicode,utf8字符集可用但ucs2字符集不适用。尽管不能在ucs2列建立FULLTEXT索引,但可以在MyISAM表IN BOOLEAN MODE模式的搜索中搜索没有建立FULLTEXT索引的列。utf8的特性适用于utf8mb4,ucs2的特性适用于utf16、utf16e和utf32。
  • 表意型语言如汉语、日语没有诸如空格之类的单词定界符。因此FULLTEXT解析器不能确定此类语言中词的起止。对于此种情况要特殊处理(比如将中文转换成一种单字节类似英文习惯的存储方式)。
  • 允许在同一表中使用多种字符集,但FULLTEXT索引中的列必须使用同一字符集和校对集。
  • MATCH()函数中的列必须与FULLTEXT索引中定义的列完全一致,除非是在MyISAM表中使用IN BOOLEAN MODE模式的全文搜索(可在没有建立索引的列执行搜索,但速度很慢)。
  • AGAINST()函数中的参数需为在查询评估期间保持不变的字符串常量。
  • FULLTEXT搜索的索引提示比non-FULLTEXT搜索的索引提示要多一些限定:对于自然语言模式的全文搜索,索引提示会被忽略而不给出任何提示,比如虽明确在查询语句中给出了IGNORE INDEX(i)指明不使用i索引,但是该索引提示会被忽略掉,最终的查询中仍会使用索引i;对于布尔模式的全文搜索,FOR ORDER BY和FOR GROUP BY的索引提示会被忽略,FOR JOIN和不带FOR修饰符的索引提示不被忽略。

4.6全文搜索参数调整

仅有少量的用户可调参数用于调整MySQL的全文搜索能力。可以通过修改源码来获取更多对MySQL全文搜索行为的控制。但一般情况下不推荐这么做,除非很清楚自己在做什么,因为这些参数已经针对效率做过调整,修改默认的行为多数情况下反而会带来性能下降。

多数全文搜索相关的变量不能在Server运行的时候修改。需在Server启动时指定这些参数,或者修改完参数之后重新启动Server。另外,某些变量修改后需要重建FULLTEXT索引。

控制最小、最大字长的配置选项对于InnoDB为:innodb_ft_min_token_size和innodb_ft_max_token_size,对于MyISAM为:ft_min_word_len 和 ft_max_word_len。改变这些选项中任意一个的值都需重建FULLTEXT索引并重启Server。

用于停止字列表的配置选项对于InnoDB为:innodb_ft_enable_stopword、innodb_ft_server_stopword_table和innodb_ft_user_stopword_table,对于MyISAM为:ft_stopword_file。可以通过改变这些选项的值来开启/关闭停止字过滤并指定停止字列表。修改了这些选项后需重建索引并在必要的时候重启Server。

ft_stopword_file指定了包含停止字列表的文件,Server默认在数据目录搜索该文件除非用绝对路径指定了文件位置,若文件内容为空,则会关闭MyISAM的停止字过滤功能。停止字文件格式很灵活,可以使用任何非字母或数字的字符来界定停止字,但“_”和“’”例外,它们会被当作字的一部分处理。停止字列表使用Server默认的字符集。

MyISAM全文搜索的50%阈值特性可通过修改源码来关闭,将源码storage/myisam/ftdefs.h中的宏#define GWS_IN_USEGWS_PROB替换为#define GWS_IN_USE GWS_FREQ后重新编译MySQL即可。同样,不推荐上述方式,如果确实需要搜索一些通用的词,可以用布尔模式的全文搜获,此种情况下50%阈值特性不生效。

可以通过修改ft_boolean_syntax选项的值来更改MyISAM布尔全文搜做中默认使用的操作符(InnoDB无此选项)。该选项可动态改变但须超级用户权限,另外,改变了改制后无需重建FULLTEXT索引。

可以通过多种方式更改期望被认作是单词字符成分的字符集合。默认情况下“_”和“’”以及字母和数字被认为是组成单词的字符,其他的被默认为定界符。例如,我们现在想把连字符“-”也作为组成单词的字符处理,那么可以通过如下方式完成:

  • 修改MySQL源码,在storage/myisam/ftdefs.h文件中找到true_word_char()和misc_word_char()两个宏,在任一个宏定义里添加“-”,重新编译MySQL。
  • 修改字符集文件,true_word_char()宏实际上利用“character type”表来从其他字符中区分出字母和数字。可以通过编辑字符集对应的XML文件中<ctype><map>节点中的内容来将“-”指定为“字母“,然后将该字符集用于FULLTEXT索引。此种方式无需重新编译MySQL。对于编辑字符集XML文件,可参阅MySQL参考手册CharacterDefinition Arrays部分。
    http://dev.mysql.com/doc/refman/5.6/en/character-arrays.html
  • 对FULLTEXT索引列使用的字符集添加新的校对集,然后更新该列以使用新添加的校对集。具体参阅MySQL手册Adding a Collation to a Character Set以及Adding a Collation for Full-Text Indexing部分。
    http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html
    http://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

为InnoDB表重建FULLTEXT索引可以通过带DROP INDEX和ADD INDEX从句的ALTER TABLE语句完成,先删除旧的再创建新的。为MyISAM表重建FULLTEXT索引同样可通过上述语句完成,也可以通过QUICK repair操作来重建(但通常第一种方式会更快),如:

需要特别说明的是,若通过repair表的方式来为MyISAM表重建FULLTEXT索引,则通过上述语句进行即可。用myisamchk工具也可以为MyISAM表重建索引,但是容易导致查询产生错误的结果,对表的修改可能使Server认为该表被损坏了。究其原因是因为通过myisamchk工具执行修改MyISAM表的索引的操作时,除非明确指定了要使用的参数值否则使用默认的全文索引参数值(如最小最大词长等)重建FULLTEXT索引。导致这种情况是因为只有Server才知道这些全文索引参数值,MyISAM索引文件中不存储这些值。若更改过了这些值,如设置了ft_min_word_len=2,则在通过myisamchk工具修复表时要明确指定该修改过的参数值如:

当然也可以通过在MySQL配置文件[myisamchk]节中加入同[mysqld]节中与全文搜索相关参数一致的参数来确保myisamchk使用最新的参数值来重建表的FULLTEXT索引。

用myisamchk为MyISAM表修改索引的替代方式是使用REPAIR TABLE、ANALYZE TABLE、 OPTIMIZE TABLE、ALTER TABLE,这些语句是由Server执行的因此可以读取到正确的全文索引参数值,不会引起问题。

4.7为全文搜索添加校对字符集

参考

10.4. Adding a Collation to a Character Set

http://dev.mysql.com/doc/refman/5.6/en/adding-collation.html

12.9.7. Adding a Collation for Full-Text Indexing

http://dev.mysql.com/doc/refman/5.6/en/full-text-adding-collation.html

5.性能对比

词汇量:6个等级,分别用vocab01k、vocab05k、vocab10k、vocab15k,vocab25k、vocab35k标记,每个等级的词汇数如下,1000、5000、10000、15000、25000、35000。(取牛津词典单词部分,去重复后随机打乱顺序,分别截取前1000、5000、10000……作为对应的词汇量)

记录数:20个等级,分别用rec005k、rec010k、rec015k、rec020k、……rec095k、rec100k标记,每个等级的记录数如下,5000、10000、15000、20000、25000、30000、……、95000、100000。

根据词汇量等级和记录数等级分别生成含不同记录数且表中文本列是由对应的词汇量生成的随机文本的表,共6*20=120个。表的存储引擎使用InnoDB。表由id和body两个字段组成,分别为整型和文本型,且在body列创建了FULLTEXT索引。表名的命名规则为vocab01k_rec005k,表示该表中共含有5千条记录,每条记录中的body列由vocab01k对应的词汇量生成的随机单词组成,以此类推。每行记录中的body列定为由50个随机单词组成。

比较两类查询:LIKE从句查询以及使用FULLTEXT索引的MATCH()AGAINST()查询。在每个表上分别执行LIKE查询和MATCH() AGAINST()全文查询,每个表上的每个查询分别执行50次,记录每次所耗费的时间。对于每50个消耗的时间,删除其最大两个值和最小两个值,取剩余值的均值作为查询耗时的最终结果。这样一共可获得120*2 = 240个时间数据,根据这些数据绘图。在每个表上执行的查询如下(其中random_word1、random_word2、random_word3是根据查询时表对应的词汇量生成的随机单词。):

LIKE搜索:

FULLTEXT搜索:

5.3对比结果

LIKE搜索的耗时随着记录数的增加而线性增长,但对于10万行记录以下的表(这里共100000*50个单词)搜索时间基本上能保持在1秒以内,所以like搜索的性能也不是特别差。由不同词汇量生成的文本对LIKE搜索的性能影响不大,不同词汇量对应的搜索时间基本上在一个很小的时间范围内变化。

FULLTEXT搜索耗时也随表中记录数的增长而线性增加。对于10万行记录以下的表(这里共100000*50个单词)搜索时间基本上能保持在0.01秒以内。由不同词汇量生成的随机文本对FULLTEXT搜索性能有相对来说比较显著的影响。每行记录中含同样的单词数,这样,较大的词汇量倾向于生成冗余度更低的文本,相应的搜索耗时倾向于更少。这可能与FULLTEXT索引建立单词索引的机制有关,较大的词汇量倾向于生成范围广但相对较浅的索引,因而能快速确定文本是否匹配。

与LIKE搜索相比,FULLTEXT全文搜索的性能要强很多,对于10万行记录的表,搜索时间都在0.02秒以下。因此可以将基于FULLTEXT索引的文本搜索部署于网站项目中的文本搜索功能中。但是,正如上述提到的,无论是LIKE搜索还是FULLTEXT搜索,其性能都会随着记录数的增长而下降,因此,若网站项目中的文本搜索数据库记录数庞大的一定规模后,可能需要考虑使用MySQL数据库全文搜索以外的文本搜索解决方案了。

0

MySQL中truncate函数自动转型的精度问题

MySQL中truncate函数的精度问题

truncate函数

TRUNCATE(X,D) 是MySQL自带的一个系统函数。
其中,X是数值,D是保留小数的位数。
其作用就是按照小数位数,进行数值截取(此处的截取是按保留位数直接进行截取,没有四舍五入)。

现在有这样一个需求,某个表有一个varchar类型的字段,字段内存了一串浮点数。 现在我想要截取到该字段的小数点后两位。

得到的结果毫无疑问,应该是5.02,结果也确实为5.02

那下面这条SQL的结果,你能猜到么?

你可能会说,这还不简单,肯定还是5.02啊。

可是,神奇的一幕出现了,该SQL的执行结果为5.01。 为什么会少了0.1呢,这时就要说到MySQL的TRUNCATE函数做了什么。 因为TRUNCATE函数会对varchar类型做自动转型,产生了精度损失。 在转型后得到的结果其实是5.019999999。然后再做截取,那么结果自然是5.01了。

下面来执行这条SQL

由于没有使用varchar类型,所以TRUNCATE函数没有做自动转型操作,所以并没有产生精度损失,结果为5.02.

cast函数

cast是一种数据类型转换的函数,函数将任何类型的值转换为具有指定类型的值,语法格式如下所示:

CAST ( expression  AS  data_type)

  • expression:任何有效的MySQL表达式或者一些字符串数据。
  • AS:用于分隔两个参数,在AS之前的是要处理的数据,AS之后是要转换的数据类型。
  • data_type:系统所提供的数据类型,这里不能使用用户定义的数据类型。
  • MySQL所能使用的可以是以下类型之一:CHAR(字符型)、DATE(日期)、TIME(时间)、DATETIME(日期时间型)、DECIMAL(浮点数 float)、SIGNED(整数 int)。

依然用5.02这个数字举例:

看似没什么问题,结果也确实是5.02。 但如果使用的是5.029呢?

你会发现,结果变成5.03了,没错,四舍五入了。 执行出来的结果和truncate函数的结果不尽相同,所以也并不能完美满足一开始提出的需求。

总结

这个问题产生的根本问题是使用了varchar类型来存浮点数,并且还需要在数据库内做运算导致的。
我认为解决这个问题的最好时机是在设计时,使用浮点类型来存储该字段。

0

MySQL 官方Docker镜像的使用

Docker镜像是用来创建容器的,我们可以基于官方提供的镜像或自已构建的镜像来创建容器。https://hub.docker.com/_/mysql/是Docker及MySQL提供、维护的一个官方镜像,我们可以基于该镜像构建自己的MySQL数据库镜像,也可以直接使用这个镜像创建MySQL数据库容器。下面介绍MySQL 官方Docker镜像的使用。

MySQL 官方Docker镜像的使用
MySQL 官方Docker镜像的使用

1. 启动一个mysql服务器实例

使用mysql镜像创建或启动MySQL容器时,可以先将镜像下载到本地:

也可以直接使用以下命令来启动MySQL实例:

这样,我们就创建了一个名为sww-mysql的MySQL数据库服务器容器实例。在创建数据库时,通过环境变量MYSQL_ROOT_PASSWORD设置数据库的root密码,还通过5.7标签指定了所使用的镜像版本。

容器创建完成后,可以通过docker ps命令看到所创建的MySQL容器实例:

2. 在其它Docker容器应用中连接MySQL

在这个镜像中,导出的是MySQL的标准端口3306。这样我们就可以在需要访问MySQL服务器的容器中,使用--link参数通过容器链接的方式,将MySQL服务器容器实例连接到包含了需要使用MySQL的应用容器中。

使用容器连接的示例如下:

注意:以上示例中的application-that-uses-镜像并不存在,仅为操作演示,下同。

除了进行容器连接的方式在其它容器中访问MySQL服务器容器外,还可以通过以下两种方式访问MySQL数据库服务器容器:

  • 在创建MySQL服务器容器实例时通过-p-P参数将数据库服务器端口映射到宿主机,再直接通过宿主机进行访问。这种方式较为简单,但需要向外暴露数据库端口。
  • 能过Docker网络(Networking)进行连接。这种方式操作较为复杂,但更为灵活,可以适用于更加复杂的网络环境。

3. MySQL命令行客户端连接MySQL

在前面创建的MySQL服务器容器中,我们并没有向外暴露访问端口,我们可以通过以下方式启动命令行客户端,并基于命令行客户端对数据库服务器进行管理。

运行另一个MySQL交互式容器,该容器会在运行后启动mysql命令行客户端:

如果需要通过非Docker的方式,或远程访问MySQL服务器容器,就可以在创建容器时通过-p-P与宿主机进行端口绑定,之后就可以像普通MySQL服务器那样进行访问或操作。

4. 在Shell中访问容器及日志查看

docker exec命令使我们可以在Docker容器内部执行命令,我们可以通过以下方式与mysql容器建立一个shell连接:

而MySQL服务器日志,可以直接通过Docker容器日志访问(实时日志查看可以添加-f参数):

5. 使用自定义MySQL配置文件

默认情况下,MySQL的启动配置文件是/etc/mysql/my.cnf,而/etc/mysql/conf.d目录下的存在任何.cnf格式的文件时,都会使用该文件中配置项替换默认配置。

因此,如果要使用自定义配置,可以在宿主机创建一个配置文件,然后在创建容器时通过-v参数,以数据卷的方式将自定义配置挂载到mysql容器的/etc/mysql/conf.d目录下。

如,在宿主机中存在/my/custom/config-file.cnf配置文件,这时就可以通过以下方式启动MySQL容器:

以上示例会启动一个名为sww-mysql的MySQL服务器容器,该文件启动时会同时使用/etc/mysql/my.cnf/etc/mysql/conf.d/config-file.cnf中的配置。

不使用cnf文件的配置方式

除使用.cnf文件进行配置外,还可以在启动容器通过参数的形式将配置传递给mysqld

如,启动一个MySQL服务器容器,并使用UTF-8(utf8mb4)格式的表编码:

详细配置参数可以通过以下命令查看:

6. 环境变量

当启动mysql容器时,我们可以向docker run命令传入一或多个环境变量来调整MySQL实例的配置。可设置的环境变量有:

  • MYSQL_ROOT_PASSWORD:必须。用于设置MySQLroot用户的密码
  • MYSQL_DATABASE:可选。用于指定镜像启动容器时要创建的数据库。如果提供了用户/密码,则会将该用户做为此数据库的超级用户。
  • MYSQL_USERMYSQL_PASSWORD:可选。用于创建一个新用户并设置密码。
  • MYSQL_ALLOW_EMPTY_PASSWORD:可选。设置为yes时,则可以使用空密码登录
  • MYSQL_RANDOM_ROOT_PASSWORD:可选。设置为yes时会为root用户设置一个随机密码(使用pwgen),所生成的随机密码会被输出到stdout
  • MYSQL_ONETIME_PASSWORD:可选。为root用户指定一个一次性密码,该密码会在用户首次登录时强制修改

7. 关于数据存储

在使用 mysql镜像创建MySQL容器时,数据库数据存储可能会有以下两种方式:

  • 数据卷容器:使用Docker默认的数据管理方式来管理数据库的数据存储,在这种方式下,数据库文件会被写入数据库的内部。这种方式对于用户非常简单,缺点是很在宿主机上找到所存储的数据。
  • 外部数据卷:在宿主机创建一个数据目录,再将数据目录挂载到容器内部。这种方式可以很方便的在宿主机上找到并进行数据管理,但需要确保数据目录的存在。

当使用外部数据卷时,假在宿主机有/my/own/datadir目录,我们可以像下面这样启动mysql容器,并将目录挂载到容器内:

其中,-v /my/own/datadir:/var/lib/mysql是数据卷的挂载,表示将宿主机的/my/own/datadir目录挂载到容器内的/var/lib/mysql目录。这个目录是MySQL的默认数据目录,当使用自定义配置时,应该也做相应的修改。

数据库备份

在非Docker环境下使用的MySQL备份工具,大多数在容器环境下仍然可用,只要其能访问mysqld服务器即可。

下面是一个通过docker exec来对mysql容器中的数据库执行备份的示例:

0

Redis集群迁移工具redis-migrate-tool使用介绍

Redis集群迁移工具redis-migrate-tool,基于redis复制实现,有快速,稳定的特点。

特点

  • 快速
  • 多线程
  • 基于redis复制
  • 实时迁移
  • 迁移过程中,源集群不影响对外提供服务
  • 异构迁移
  • 支持Twemproxy集群,redis cluster集群,rdb文件 和 aof文件
  • 过滤功能
  • 当目标集群是Twemproxy,数据会跳过Twemproxy直接导入到后端的redis
  • 迁移状态显示
  • 完善的数据抽样校验

redis-migrate-tool迁移工具的数据来源可以是:单独的redis实例,twemproxy集群,redis cluster,rdb文件,aof文件。

redis-migrate-tool迁移工具的目标可以是:单独的redis实例,twemproxy集群,redis cluster,rdb文件。

版本说明

https://github.com/vipshop/redis-migrate-tool #仅支持redis3及以下版本

https://github.com/tanruixing88/redis-migrate-tool #基于上述版本修改,支持redis4及以上版本

依赖


安装


配置文件rmt.conf

配置文件示例:从redis cluster集群迁移数据到twemproxy集群


配置文件示例:从redis cluster集群迁移数据到另外一个cluster集群


配置文件示例:从rdb文件恢复数据到redis cluster集群


运行


状态

通过redis-cli连接redis-migrate-tool监控的端口,运行info命令


数据校验


 

0

mysql全文模糊搜索MATCH AGAINST方法

mysql中模糊搜索通常用like ‘%keyword%’ 来进行模糊搜索,例如

select * from table where title like ‘%唠吧小站%’

MySQL 4.x以上提供了全文检索支持 MATCH ……AGAINST 模式(不区分大小写)

建立全文索引的表的存储引擎类型必须为MyISAM

问题是match   against对中文模糊搜索支持不是太好

新建一个utf8 MyISAM类型的表并建立一个全文索引  :

其中FULLTEXT(title, body) 给title和body这两列建立全文索引,之后检索的时候注意必须同时指定这两列。

给这个表添加点测试数据

3. 全文检索测试

注意 MATCH (title,body) 里面的值必须是前面建立全文索引的两个字段不能少。

 

mysql 默认支持全文检索的字符长度是4,可以用SHOW VARIABLES LIKE 'ft_min_word_len' 来查看指定的字符长度,也可以在mysql配置文件my.ini 更改最小字符长度,方法是在my.ini 增加一行 比如:ft_min_word_len = 2,改完后重启mysql即可。

另外,MySQL还会计算一个词的权值,以决定是否出现在结果集中,具体如下:

mysql在集和查询中的对每个合适的词都会先计算它们的权重,一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重),因为在这个特定的集中,它有较低的语义值。否则,如果词是较少的,它将得到一个较高的权重,mysql默认的阀值是50%,上面‘you’在每个文档都出现,因此是100%,只有低于50%的才会出现在结果集中。

全文检索语法

+ 表示AND,即必须包含。- 表示NOT,即不包含。

apple和banana之间是空格,空格表示OR,即至少包含apple、banana中的一个。

必须包含apple,但是如果同时也包含banana则会获得更高的权重。

~ 是我们熟悉的异或运算符。返回的记录必须包含apple,但是如果同时也包含banana会降低权重。但是它没有 +apple -banana 严格,因为后者如果包含banana压根就不返回。

返回同时包含apple和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。

0

Redis面试题:为什么Redis很快?

面试官经常会问到单线程的Redis为什么这么快?
为了阐明这个问题, 可以分三部分讲解:
(1) 第一部分: Redis到底有多快
(2) 第二部分: 详细讲解Redis高性能原因
(3) 第三部分: 影响Redis性能的因素

Redis到底有多快

  1. 可以使用redis-benchmark对Redis的性能进行评估,命令行提供了普通/流水线方式、不同压力评估特定命令的性能的功能。
  2. redis性能卓越,作为key-value系统最大负载数量级为10W/s, set和get耗时数量级为10ms和5ms。使用流水线的方式可以提升redis操作的性能。

redis-benchmark实用程序可模拟N个客户端同时发送M个总查询的运行命令(类似于Apache的ab实用程序)。

支持以下选项:

测试数据示例

上例截取了SET/GET/INCR的测试结果。

测试结果包括测试的环境参数(请求量、client数量、有效载荷)以及请求耗时的TP值。

redis-benchmark默认使用10万请求量, 50个clinet,有效载荷为3字节进行测试。

返回结果可以看出SET/GET/INCR命令在10万的请求量下,总的请求耗时均低于0.1s以内。 以QPS=10W为例, 计算出来的平均耗时为2ms左右(1/(10W/50))。

Redis测试经验数据

硬件环境和软件配置

Redis系统负载

  1. 不使用流水线测试结果

  1. 使用流水线测试结果

从以上可以看出Redis作为key-value系统读写负载大致在10W+QPS, 使用流水线技术能够显著提升读写性能。

耗时情况

  1. 不使用流水线测试结果

所有set操作均在10ms内完成, get操作均在5ms以下。

Redis为什么那么快

Redis是一个单线程应用,所说的单线程指的是Redis使用单个线程处理客户端的请求。
虽然Redis是单线程的应用,但是即便不通过部署多个Redis实例和集群的方式提升系统吞吐, 从官网给出的数据可以看出,Redis处理速度非常快。

Redis性能非常高的原因主要有以下几点:

  • 内存存储:Redis是使用内存(in-memeroy)存储,没有磁盘IO上的开销
  • 单线程实现:Redis使用单个线程处理请求,避免了多个线程之间线程切换和锁资源争用的开销
  • 非阻塞IO:Redis使用多路复用IO技术,在poll,epool,kqueue选择最优IO实现
  • 优化的数据结构:Redis有诸多可以直接应用的优化数据结构的实现,应用层可以直接使用原生的数据结构提升性能

下面详细介绍非阻塞IO和优化的数据结构

多路复用IO

在《unix网络编程 卷I》中详细讲解了unix服务器中的5种IO模型。

一个IO操作一般分为两个步骤:

  1. 等待数据从网络到达, 数据到达后加载到内核空间缓冲区
  2. 数据从内核空间缓冲区复制到用户空间缓冲区

按照两个步骤是否阻塞线程,分为阻塞/非阻塞, 同步/异步。

五种IO模型分类:

阻塞 非阻塞
同步 阻塞IO 非阻塞IO,IO多路复用,信号驱动IO
异步IO 异步IO

阻塞IO

在linux中,默认情况下所有的socket都是blocking,一个典型的读操作流程大概是这样:


非阻塞IO

Linux下,可以通过设置socket使其变为non-blocking。当对一个non-blocking socket执行读操作时,流程是这个样子:

IO多路复用

IO multiplexing这个词可能有点陌生,但是如果我说select/epoll,大概就都能明白了。有些地方也称这种IO方式为事件驱动IO(event driven IO)。我们都知道,select/epoll的好处就在于单个process就可以同时处理多个网络连接的IO。它的基本原理就是select/epoll这个function会不断的轮询所负责的所有socket,当某个socket有数据到达了,就通知用户进程。它的流程如图:

信号驱动IO

异步IO

Linux下的asynchronous IO其实用得不多,从内核2.6版本才开始引入。先看一下它的流程:

介绍完unix或者类unix系统IO模型之后, 我们看下redis怎么处理客户端连接的?

Reids的IO处理

总的来说Redis使用一种封装多种(select,epoll, kqueue等)实现的Reactor设计模式多路复用IO处理客户端的请求。

Reactor设计模式

 

Reactor设计模式常常用来实现事件驱动。除此之外, Redis还封装了不同平台多路复用IO的不同的库。处理过程如下:

IO库封装

因为 Redis 需要在多个平台上运行,同时为了最大化执行的效率与性能,所以会根据编译平台的不同选择不同的 I/O 多路复用函数作为子模块。

具体选择过程如下:
io多路复用的函数选择

Redis 会优先选择时间复杂度为 O(1) 的 I/O 多路复用函数作为底层实现,包括 Solaries 10 中的 evport、Linux 中的 epoll 和 macOS/FreeBSD 中的 kqueue,上述的这些函数都使用了内核内部的结构,并且能够服务几十万的文件描述符。

但是如果当前编译环境没有上述函数,就会选择 select 作为备选方案,由于其在使用时会扫描全部监听的描述符,所以其时间复杂度较差 O(n),并且只能同时服务 1024 个文件描述符,所以一般并不会以 select 作为第一方案使用。

丰富高效的数据结构

Redis提供了丰富的数据结构,并且不同场景下提供不同实现。

Redis作为key-value系统,不同类型的key对应不同的操作或者操作对应不同的实现,相同的key也会有不同的实现。Redis对key进行操作时,会进行类型检查,调用不同的实现。

为了解决以上问题, Redis 构建了自己的类型系统, 这个系统的主要功能包括:

redisObject 对象。
基于 redisObject 对象的类型检查。
基于 redisObject 对象的显式多态函数。
对 redisObject 进行分配、共享和销毁的机制。

redisObject定义:

type 、 encoding 和 ptr 是最重要的三个属性。

Redis支持4种type, 8种编码, 分别为:

redis的type

有了redisObject之后, 对于特定key的操作过程就可以很容易的实现:

Redis命令的调用过程

Redis除了提供丰富的高效的数据结构外, 还提供了如HyperLogLog, Geo索引这样高效的算法。

影响 Redis 性能的 5 大因素

  1. Redis 内部的阻塞式操作;
  2. CPU 核和 NUMA 架构的影响;
  3. Redis 关键系统配置;
  4. Redis 内存碎片;
  5. Redis 缓冲区。

一、Redis 内部的阻塞式操作

1.1 有哪些阻塞点?
看看要与哪些对象交互以及有什么操作:

逐个分析

1.1.1 和客户端交互时的阻塞点
Redis 使用了 IO 多路复用机制,网络IO将不是阻塞点(只考虑Redis本身而非网络环境)。

键值对的增删改查操作是 Redis 和客户端交互的主要部分,复杂度高的增删改查操作肯定会阻塞 Redis。O(N)级别的操作肯定会阻塞了。

Redis 中涉及集合的操作复杂度通常为 O(N),例如集合元素全量查询操作 HGETALL、SMEMBERS,以及集合的聚合统计操作,例如求交、并和差集。

第一个阻塞点:集合全量查询和聚合操作。

其实,删除操作的本质是要释放键值对占用的内存空间。释放内存只是第一步,为了更加高效地管理内存空间,在应用程序释放内存时,操作系统需要把释放掉的内存块插入一个空闲内存块的链表,以便后续进行管理和再分配。这个过程本身需要一定时间,而且会阻塞当前释放内存的应用程序,所以,如果一下子释放了大量内存,空闲内存块链表操作时间就会增加,相应地就会造成 Redis 主线程的阻塞。

删除大量键值对数据的时候,最典型的就是删除包含了大量元素的集合,也称为 bigkey 删除。

不同元素数量的集合在进行删除操作时所消耗的时间:

很显然,Redis 的第二个阻塞点是 bigkey 删除操作

频繁删除键值对都是潜在的阻塞点了,那么 FLUSHDB 和 FLUSHALL 操作必然也是一个潜在的阻塞风险,这就是 Redis 的第三个阻塞点:清空数据库。

1.1.2 和磁盘的交互的阻塞点
磁盘 IO 一般都是比较费时费力的,需要重点关注。

其实Redis 进一步设计为采用子进程的方式生成 RDB 快照文件,以及执行 AOF 日志重写操作,这样慢速的磁盘 IO 就不会阻塞主线程了。

但Redis 直接记录 AOF 日志时,同步写磁盘的操作的耗时大约是 1~2ms,如果有大量的写操作需要记录在 AOF 日志中,并同步写回的话,就会阻塞主线程了。

Redis 的第四个阻塞点了:AOF 日志同步写。

1.1.3 主从节点交互时的阻塞点
主从同步时,主库复制创建+传输RDB文件都是子进程完成并不阻塞,但从库接收RDB更新时必定flushdb清库,形成上面讲的第三个阻塞点。

另外从库加载RDB到内存和RDB大小有关,越大越慢,Redis 的第五个阻塞点:加载 RDB 文件

1.1.4 切片集群交互的阻塞点
每个 Redis 实例上分配的哈希槽信息需要在不同实例间进行传递,同时,当需要进行负载均衡或者有实例增删时,数据会在不同的实例间进行迁移。不过,哈希槽的信息量不大,而数据迁移是渐进式执行的,所以,一般来说,这两类操作对 Redis 主线程的阻塞风险不大。

如果你使用了 Redis Cluster 方案,而且同时正好迁移的是 bigkey 的话,就会造成主线程的阻塞,因为 Redis Cluster 使用了同步迁移。

1.2 解决办法、异步执行一下?
总结下五个阻塞点:

  1. 集合全量查询和聚合操作;
  2. bigkey 删除;
  3. 清空数据库;
  4. AOF 日志同步写;
  5. 从库加载 RDB 文件。

Redis 提供了异步线程机制,这五大阻塞式操作都可以被异步执行吗?

1.2.1 异步执行对操作的要求
如果一个操作能被异步执行,就意味着它并不是 Redis 主线程的关键路径上的操作。

关键路径上的操作:客户端把请求发送给 Redis 后,就干等着 Redis 返回数据结果的操作。

1.2.2 集合查询聚合操作
读操作是典型的关键路径操作,客户端发送了读操作之后,就会等待读取的数据返回,以便进行后续的数据处理。

而 Redis 的第一个阻塞点“集合全量查询和聚合操作”都涉及到了读操作,所以,它们是不能进行异步操作了。

1.2.3 删除操作
删除操作并不需要给客户端返回具体的数据结果,所以不算是关键路径操作。第二个阻塞点“bigkey 删除”,和第三个阻塞点“清空数据库”,都是对数据做删除,并不在关键路径上。

1.2.4 AOF同步写操作
为了保证数据可靠性,Redis 实例需要保证 AOF 日志中的操作记录已经落盘,这个操作虽然需要实例等待,但它并不会返回具体的数据结果给实例。所以,我们也可以启动一个子线程来执行 AOF 日志的同步写,而不用让主线程等待 AOF 日志的写完成。

1.2.5 加载RDB文件
从库要想对客户端提供数据存取服务,就必须把 RDB 文件加载完成。所以,这个操作也属于关键路径上的操作,我们必须让从库的主线程来执行

综上,所以,我们可以使用 Redis 的异步子线程机制来实现 bigkey 删除,清空数据库,以及 AOF 日志同步写。

1.3 异步的子线程机制
Redis 主线程启动后,会使用操作系统提供的 pthread_create 函数创建 3 个子线程,分别由它们负责 AOF 日志写操作、键值对删除以及文件关闭的异步执行。

主线程通过一个链表形式的任务队列和子线程进行交互。当收到键值对删除和清空数据库的操作时,主线程会把这个操作封装成一个任务,放入到任务队列中,然后给客户端返回一个完成信息,表明删除已经完成。

但实际上,这个时候删除还没有执行,等到后台子线程从任务队列中读取任务后,才开始实际删除键值对(Redis 4.0+),并释放相应的内存空间。因此,我们把这种异步删除也称为惰性删除(lazy free)。此时,删除或清空操作不会阻塞主线程,这就避免了对主线程的性能影响。

和惰性删除类似,当 AOF 日志配置成 everysec 选项后,主线程会把 AOF 写日志操作封装成一个任务,也放到任务队列中。后台子线程读取任务后,开始自行写入 AOF 日志,这样主线程就不用一直等待 AOF 日志写完了。

二、CPU 核和 NUMA 架构的影响

要了解 CPU 对 Redis 具体有什么影响,我们得先了解一下 CPU 架构。

2.1 主流的 CPU 架构
2.1.1 简介
一个 CPU 处理器中一般有多个运行核心,我们把一个运行核心称为一个物理核。每个物理核都可以运行应用程序。

2.1.2 缓存
每个物理核都拥有私有的一级缓存(Level 1 cache,简称 L1 cache),包括一级指令缓存和一级数据缓存,以及私有的二级缓存(Level 2 cache,简称 L2 cache)。

当数据或指令保存在 L1、L2 缓存时,物理核访问它们的延迟不超过 10 纳秒,速度非常快。

但其他的物理核无法对这个核的缓存空间进行数据存取,且只有KB级大小。

所以,不同的物理核还会共享一个共同的三级缓存(Level 3 cache,简称为 L3 cache)。L3 缓存能够使用的存储资源比较多,所以一般比较大,能达到几 MB 到几十 MB,这就能让应用程序缓存更多的数据。当 L1、L2 缓存中没有数据缓存时,可以访问 L3,尽可能避免访问内存。

2.1.3 线程
现在主流的 CPU 处理器中,每个物理核通常都会运行两个超线程,也叫作逻辑核。同一个物理核的逻辑核会共享使用 L1、L2 缓存。总结如图:

2.1.4 多CPU架构
主流的服务器上,一个 CPU 处理器(也称 CPU Socket)会有 10 到 20 多个等几十个物理核。不同处理器间通过总线连接。CPU Socket 的架构:

在多 CPU 架构上,应用程序可以在不同的处理器上运行。

如果应用程序先在一个 Socket 上运行,并且把数据保存到了内存,然后被调度到另一个 Socket 上运行,此时,应用程序再进行内存访问时,就需要访问之前 Socket 上连接的内存,这种访问属于远端内存访问。和访问 Socket 直接连接的内存相比,远端内存访问会增加应用程序的延迟。

多 CPU 架构下,一个应用程序访问所在 Socket 的本地内存和访问远端内存的延迟并不一致,所以,我们也把这个架构称为非统一内存访问架构(Non-Uniform Memory Access,NUMA 架构)

2.2 CPU多核到底是怎么影响Redis的
在多核 CPU 的场景下,一旦应用程序需要在一个新的 CPU 核上运行,那么,运行时信息就需要重新加载到新的 CPU 核上。而且,新的 CPU 核的 L1、L2 缓存也需要重新加载数据和指令,这会导致程序的运行时间增加。

而且,Redis 实例需要等待这个重新加载的过程完成后,才能开始处理请求,所以,这也会导致一些请求的处理时间增加。

2.2.1 案例
当时,项目需求是要对 Redis 的 99% 尾延迟进行优化,要求 GET 尾延迟小于 300 微秒,PUT 尾延迟小于 500 微秒。

99% 的请求延迟小于的值就是 99% 尾延迟。比如说,我们有 1000 个请求,假设按请求延迟从小到大排序后,第 991 个请求的延迟实测值是 1ms,而前 990 个请求的延迟都小于 1ms,所以,这里的 99% 尾延迟就是 1ms。

避免了许多延迟增加的情况,后来,仔细检测了 Redis 实例运行时的服务器 CPU 的状态指标值,这才发现,CPU 的 context switch (上下文切换)次数比较多。

可以使用 taskset 命令把一个程序绑定在一个核上运行。比如说,我们执行下面的命令,就把 Redis 实例绑在了 0 号核上,其中,“-c”选项用于设置要绑定的核编号。

taskset -c 0 ./redis-server

Redis 实例的 GET 和 PUT 的 99% 尾延迟一下子就分别降到了 260 微秒和 482 微秒

2.3 CPU 的 NUMA 架构对 Redis 性能的影响
网络中断程序是要和 Redis 实例进行网络数据交互的,网络中断处理程序从网卡硬件中读取数据,并把数据写入到操作系统内核维护的一块内存缓冲区。

内核会通过 epoll 机制触发事件,通知 Redis 实例,Redis 实例再把数据从内核的内存缓冲区拷贝到自己的内存空间,如下图所示:

如果网络中断处理程序和 Redis 实例各自所绑的 CPU 核不在同一个 CPU Socket 上,那么,Redis 实例读取网络数据时,就需要跨 CPU Socket 访问内存,这个过程会花费较多时间

网上测试显示,和访问 CPU Socket 本地内存相比,跨 CPU Socket 的内存访问延迟增加了 18%

为了避免 Redis 跨 CPU Socket 访问网络数据,我们最好把网络中断程序和 Redis 实例绑在同一个 CPU Socket 上

三、Redis 关键系统配置

3.1 文件系统:AOF 模式
为了保证数据可靠性,Redis 会采用 AOF 日志或 RDB 快照。其中,AOF 日志提供了三种日志写回策略:no、everysec、always。这三种写回策略依赖文件系统的两个系统调用完成,也就是 write 和 fsync,

write 只要把日志记录写到内核缓冲区,就可以返回了,并不需要等待日志实际写回到磁盘;而 fsync 需要把日志记录写回到磁盘后才能返回,时间较长。

AOF 重写会对磁盘进行大量 IO 操作,同时,fsync 又需要等到数据写到磁盘后才能返回,所以,当 AOF 重写的压力比较大时,就会导致 fsync 被阻塞。虽然 fsync 是由后台子线程负责执行的,但是,主线程会监控 fsync 的执行进度。

当主线程使用后台子线程执行了一次 fsync,需要再次把新接收的操作记录写回磁盘时,如果主线程发现上一次的 fsync 还没有执行完,那么它就会阻塞。

可以检查下 Redis 配置文件中的 appendfsync 配置项,该配置项的取值表明了 Redis 实例使用的是哪种 AOF 日志写回策略,如下:

如果 AOF 写回策略使用了 everysec 或 always 配置,请先确认下业务方对数据可靠性的要求,明确是否需要每一秒或每一个操作都记日志。

在有些场景中(例如 Redis 用于缓存),数据丢了还可以从后端数据库中获取,并不需要很高的数据可靠性。

3.2 操作系统:swap
Redis 是内存数据库,内存使用量大,如果没有控制好内存的使用量,或者和其他内存需求大的应用一起运行了,就可能受到 swap 的影响,而导致性能变慢。

触发 swap 的原因主要是物理机器内存不足,对于 Redis 而言,有两种常见的情况:

  • Redis 实例自身使用了大量的内存,导致物理机器的可用内存不足;
  • 和 Redis 实例在同一台机器上运行的其他进程,在进行大量的文件读写操作。文件读写本身会占用系统内存,这会导致分配给 Redis 实例的内存量变少,进而触发 Redis 发生 swap。

操作系统本身会在后台记录每个进程的 swap 使用情况,即有多少数据量发生了 swap。你可以先通过下面的命令查看 Redis 的进程号,这里是 5332。

进入 Redis 所在机器的 /proc 目录下的该进程目录中:

3.3 操作系统:内存大页
Linux 内核从 2.6.38 开始支持内存大页机制,该机制支持 2MB 大小的内存页分配,而常规的内存页分配是按 4KB 的粒度来执行的。

为了提供数据可靠性保证,需要将数据做持久化保存。

客户端的写请求可能会修改正在进行持久化的数据。在这一过程中,Redis 就会采用写时复制机制,也就是说,一旦有数据要被修改,Redis 并不会直接修改内存中的数据,而是将这些数据拷贝一份,然后再进行修改。

如果采用了内存大页,那么,即使客户端请求只修改 100B 的数据,Redis 也需要拷贝 2MB 的大页。相反,如果是常规内存页机制,只用拷贝 4KB。

关闭内存大页

四、Redis 内存碎片

经常会遇到这样一个问题:明明做了数据删除,数据量已经不大了,为什么使用 top 命令查看时,还会发现 Redis 占用了很多内存呢?

这是因为,当数据删除后,Redis 释放的内存空间会由内存分配器管理,并不会立即返回给操作系统。所以,操作系统仍然会记录着给 Redis 分配了大量内存。

Redis 释放的内存空间可能并不是连续的,那么,这些不连续的内存空间很有可能处于一种闲置的状态。

4.1 是什么
类似于,一个饭店有十张桌子,之前有十个人客人,每人占了一桌(空出很多单独的小座位),此时来了一对夫妇,那么他们就没得一起坐的位置了。很多单独的小座位就是内存碎片。

4.2 是如何形成的?
4.2.1 内因:内存分配器的分配策略
内存分配器的分配策略就决定了操作系统无法做到“按需分配”。这是因为,内存分配器一般是按固定大小来分配内存,而不是完全按照应用程序申请的内存空间大小给程序分配。

Redis 可以使用 libc、jemalloc、tcmalloc 多种内存分配器来分配内存,默认使用 jemalloc。

jemalloc 的分配策略之一,是按照一系列固定的大小划分内存空间,例如 8 字节、16 字节、32 字节、48 字节,…, 2KB、4KB、8KB 等。

这样的分配方式本身是为了减少分配次数。例如,Redis 申请一个 20 字节的空间保存数据,jemalloc 就会分配 32 字节,此时,如果应用还要写入 10 字节的数据,Redis 就不用再向操作系统申请空间了

如果 Redis 每次向分配器申请的内存空间大小不一样,这种分配方式就会有形成碎片的风险,而这正好来源于 Redis 的外因

4.2.2 外因是 Redis 的负载特征
应用 A 保存 6 字节数据,jemalloc 按分配策略分配 8 字节。如果应用 A 不再保存新数据,那么,这里多出来的 2 字节空间就是内存碎片了,如下图所示:

第二个外因是,这些键值对会被修改和删除,这会导致空间的扩容和释放。

如果应用 E 想要一个 3 字节的连续空间,显然是不能得到满足的。因为,虽然空间总量够,但却是碎片空间,并不是连续的。

4.3 判断是否有内存碎片?
Redis 自身提供了 INFO 命令,可以用来查询内存使用的详细信息

这里有一个 mem_fragmentation_ratio 的指标,它表示的就是 Redis 当前的内存碎片率。

知道了这个指标,我们该如何使用呢?提供一些经验阈值:

mem_fragmentation_ratio 大于 1 但小于 1.5。这种情况是合理的。这是因为,刚才我介绍的那些因素是难以避免的。毕竟,内因的内存分配器是一定要使用的,分配策略都是通用的,不会轻易修改;而外因由 Redis 负载决定,也无法限制。所以,存在内存碎片也是正常的。

mem_fragmentation_ratio 大于 1.5 。这表明内存碎片率已经超过了 50%。一般情况下,这个时候,我们就需要采取一些措施来降低内存碎片率了。

4.4 如何清理
当 Redis 发生内存碎片后,一个“简单粗暴”的方法就是重启 Redis 实例。这不是一个“优雅”的方法,毕竟,重启 Redis 会带来两个后果:

如果 Redis 中的数据没有持久化,那么,数据就会丢失;

即使 Redis 数据持久化了,我们还需要通过 AOF 或 RDB 进行恢复,恢复时长取决于 AOF 或 RDB 的大小,如果只有一个 Redis 实例,恢复阶段无法提供服务。

幸运的是,从 4.0-RC3 版本以后,Redis 自身提供了一种内存碎片自动清理的方法,我们先来看这个方法的基本机制。

需要注意的是:碎片清理是有代价的,操作系统需要把多份数据拷贝到新位置,把原有空间释放出来,这会带来时间开销。因为 Redis 是单线程,在数据拷贝时,Redis 只能等着,这就导致 Redis 无法及时处理请求,性能就会降低。

Redis 需要启用自动内存碎片清理,可以把 activedefrag 配置项设置为 yes,命令如下:

这个命令只是启用了自动清理功能,但是,具体什么时候清理,会受到下面这两个参数的控制。

  • active-defrag-ignore-bytes 100mb:表示内存碎片的字节数达到 100MB 时,开始清理;
  • active-defrag-threshold-lower 10:表示内存碎片空间占操作系统分配给 Redis 的总空间比例达到 10% 时,开始清理。

五、Redis 缓冲区

5.1 是什么
缓冲区的功能其实很简单,主要就是用一块内存空间来暂时存放命令数据,以免出现因为数据和命令的处理速度慢于发送速度而导致的数据丢失和性能问题。

5.2 风险
缓冲区的内存空间有限,如果往里面写入数据的速度持续地大于从里面读取数据的速度,就会导致缓冲区需要越来越多的内存来暂存数据。当缓冲区占用的内存超出了设定的上限阈值时,就会出现缓冲区溢出。如果发生了溢出,就会丢数据了。那是不是不给缓冲区的大小设置上限,就可以了呢?显然不是,随着累积的数据越来越多,缓冲区占用内存空间越来越大,一旦耗尽了 Redis 实例所在机器的可用内存,就会导致 Redis 实例崩溃。

5.3 总结
从缓冲区溢出对 Redis 的影响的角度,我再把这四个缓冲区分成两类做个总结。

  • 缓冲区溢出导致网络连接关闭:普通客户端、订阅客户端,以及从节点客户端,它们使用的缓冲区,本质上都是 Redis 客户端和服务器端之间,或是主从节点之间为了传输命令数据而维护的。这些缓冲区一旦发生溢出,处理机制都是直接把客户端和服务器端的连接,或是主从节点间的连接关闭。网络连接关闭造成的直接影响,就是业务程序无法读写 Redis,或者是主从节点全量同步失败,需要重新执行。
  • 缓冲区溢出导致命令数据丢失:主节点上的复制积压缓冲区属于环形缓冲区,一旦发生溢出,新写入的命令数据就会覆盖旧的命令数据,导致旧命令数据的丢失,进而导致主从节点重新进行全量复制。

从本质上看,缓冲区溢出,无非就是三个原因:命令数据发送过快过大;命令数据处理较慢;缓冲区空间过小。明白了这个,我们就可以有针对性地拿出应对策略了。

  • 针对命令数据发送过快过大的问题,对于普通客户端来说可以避免 bigkey,而对于复制缓冲区来说,就是避免过大的 RDB 文件。
  • 针对命令数据处理较慢的问题,解决方案就是减少 Redis 主线程上的阻塞操作,例如使用异步的删除操作。
  • 针对缓冲区空间过小的问题,解决方案就是使用 client-output-buffer-limit 配置项设置合理的输出缓冲区、复制缓冲区和复制积压缓冲区大小。当然,我们不要忘了,输入缓冲区的大小默认是固定的,我们无法通过配置来修改它,除非直接去修改 Redis 源码。

 

0

使用sql查询分组后每组的第一条数据

 

使用sql查询分组后每组的第一条数据,借助一个连接查询搞定

 

 

0

MySQL分库分表的分页查询解决方案

问题的提出

我们知道,当我们的数据量达到一定数量时,需要将数据表进行水平拆分,从而满足大量数据的存储和查询,保证系统的可用性,但同时会出现另外一个问题就是,如果业务要查询“最近注册的第3页用户”,该如何实现呢?单库上,可以通过简单的sql实现分页查询。

分库分表后变成两个库后,分库依据是user_id,排序依据是time,单个分数据库层失去了time排序的全局视野,如果同样需要实现分页查询时该怎么办呢?有什么比较好的MySQL分库分表的分页查询解决方案呢?

全局视野法

MySQL分库分表的分页查询解决方案

正常来讲,不管哪一个分库的第3页都不一定有全局第3页的所有数据,例如一下三种情况:

情况一:两个分库按照时间排序,数据各占一半,则每页取offset和limit的一般数据回来合并就可以了
MySQL分库分表的分页查询解决方案
情况二:所有数据都在一个库上,则取一个库的所有数据回来就可以了
MySQL分库分表的分页查询解决方案
情况三,那么一般情况是,每个分库的数据数据是随机的,但是一定是在全局offset=600之内
MySQL分库分表的分页查询解决方案

由于不清楚到底是哪种情况,所以必须每个库都返回3页数据,所得到的6页数据在服务层进行内存排序,得到数据全局视野,再取第3页数据,便能够得到想要的全局分页数据。

这种方法缺点是:当查询的页数增大时,每个分库所需返回的数据也越来成倍增加,降低了查询的性能

业务折中

第一种折中的方案是

对全局视野法的一种优化,即禁用制定页数的分页查询,必须通过下一页来实现分页查询的页数跳转,并且在每次查询下一页时将上一页的最大排序字段的值带上(这里就是时间time),这样在每个分库查询数据时待上这个条件,可以优化查询速率。

第二种折中的方案是

数据库分库-数据均衡原理

使用patition key进行分库,在数据量较大,数据分布足够随机的情况下,各分库所有非patition key属性,在各个分库上的数据分布,统计概率情况是一致的。

例如,在uid随机的情况下,使用uid取模分两库,db0和db1:

(1)性别属性,如果db0库上的男性用户占比70%,则db1上男性用户占比也应为70%

(2)年龄属性,如果db0库上18-28岁少女用户比例占比15%,则db1上少女用户比例也应为15%

(3)时间属性,如果db0库上每天10:00之前登录的用户占比为20%,则db1上应该是相同的统计规律

利用这一原理,要查询全局100页数据,offset 9900 limit 100改写为offset 4950 limit 50,每个分库偏移4950(一半),获取50条数据(半页),得到的数据集的并集,基本能够认为,是全局数据的offset 9900 limit 100的数据,当然,这一页数据的精度,并不是精准的。

根据实际业务经验,用户都要查询第100页网页、帖子、邮件的数据了,这一页数据的精准性损失,业务上往往是可以接受的,但此时技术方案的复杂度便大大降低了,既不需要返回更多的数据,也不需要进行服务内存排序了。

二次查找法

有没有一种方法既能满足业务要求,并且不需要折中,性能还高的方法呢?
接下来介绍一种“二次查找法”,不知道能不能讲的明白,我尽量吧。

为了方便举例,假设一页只有5条数据,查询第200页的SQL语句为select * from T order by time offset 1000 limit 5;

分五步:

1. 将select * from T order by time offset 1000 limit 5; 优化成select * from T order by time offset 500 limit 5,注意这里的500=1000/分表数量,并将这个sql下发至每个分库分表中执行,每个分库返回这个sql执行的结果。

2. 找到所有分库返回结果的time的最小值

MySQL分库分表的分页查询解决方案
第一个库,5条数据的time最小值是1487501123
第二个库,5条数据的time最小值是1487501223

故,三页数据中,time最小值来自第一个库,time_min=1487501123,这个过程只需要比较各个分库第一条数据,时间复杂度很低

3. 查询二次改写

第一次改写的SQL语句是select * from T order by time offset 500 limit 5

第二次要改写成一个between语句,between的起点是time_min,between的终点是原来每个分库各自返回数据的最大值:

第一个分库,第一次返回数据的最大值是1487501523
所以查询改写为select * from T order by time where time between time_min and 1487501523

第二个分库,第一次返回数据的最大值是1487501699
所以查询改写为select * from T order by time where time between time_min and 1487501699

MySQL分库分表的分页查询解决方案

从上面图片可以看出,DB1比第一次查出来的数据多了两行,应为查询的范围扩大了

4. 计算time_min这条记录在全局的offset

根据第一步查询的sqlselect * from T order by time offset 500 limit ,我们知道每个库的offset值了,将DB0中的最小time的数据虚拟到DB1中推算在DB1中的offset值=497
MySQL分库分表的分页查询解决方案
从而我们得知time_min这条记录在全局的offset值=500+497=997

5. 根据第二次查询出来的结果集,在内存中作排序,已知time_min在全局中的offset=997,那么结果集排序之后也能推算出offset=1000所在的记录,从而获得sqlselect * from T order by time offset 1000 limit 5的分页查询记录(图片黄色部分)

MySQL分库分表的分页查询解决方案

总结:可以精确的返回业务所需数据,每次返回的数据量都非常小,不会随着翻页增加数据的返回量。

+3