mysql的text/blob类型性能问题分析

text/blob类型介绍为存储很大的数据而被设计的字符串数据类型是BLOB和TEXT, 它们呢, 分别是以二进制和以字符方式进行存储。实际上它们分别属于两组不同的数据类型家族TEXT字符类型是,,TEXT,,BLOB 对应的二进制类型是,,BLOB,,。BLOB是的同义词TEXT是的同义词。与别的类型不一样, MySQL 将每一个BLOB以及TEXT值视作一个个別的对象予以处理, 存储引擎于存储之时通常会加以特殊处理, 当BLOB和TEXT值过大时, 便会运用专门的“外部”存储区域去实施存储, 在这个时候, 每一个值于行内需要1至4个字节去存储一个指针, 接着在外部存储区域存储实际的值。Blob家族与Text家族之间, 唯一存在的不同之处在于, Blob类型所存储的是二进制数据, 其并无排序规则, 也不存在字符集, 然而Text类型却有着字符集, 还有排序规则。MySQL针对BLOB以及TEXT列开展排序, 跟其他类型存在差异, 它仅仅依据每个列的最前面字节来进行排序, 并非对整个字符串进行排序, 要是仅仅需要对前面一小部分字符予以排序, 那么能够减小配置, 或者运用ORDER BY (,)。MySQL没办法对BLOB以及TEXT列当中全部长度的那种字符串去进行索引, 并且也根本就无法借助这些索引来实现消除排序这样的操作。text/blob容易引起性能问题的原因当BLOB值太大之时, 会运用专门的“外部”存储区域予以存储, 此时每个该值在行内需要1至4个字节来存储一个指针, 之后在外部存储区域存储实际的值 , 当TEXT值太大时, 情况亦是如此, 会在外部存储区域存储实际的值。进行存储操作时运用了“外部”存储区域, 如此一来, 会采用磁盘临时表来存储, 而磁盘临时表会引发极为严重的性能开销。解决方案要防止运用BLOB以及TEXT此类类型, 所有涉及到Blob种类的地方利用(,)把列值转变为字符形式, 采用内存临时表。有时, mysql会出现慢查询情况, 然而数据量并非特别大, 且在使用了BLOB和TEXT类型时, 查看执行计划却毫无头绪, 此时可从这方面思索一番。以前在.0版本, 使用大json存储工作流内容时, 此问题十分明显, 至2.0版本往后, 进行了sql拆分, 理应是已将其解决。参考文献为《高性能mysql》。WWw.blog.bufdnv.cN/Article/details/424497.sHtMLWWw.blog.bufdnv.cN/Article/details/149286.sHtMLWWw.blog.bufdnv.cN/Article/details/230981.sHtMLWWw.blog.bufdnv.cN/Article/details/651713.sHtMLWWw.blog.bufdnv.cN/Article/details/998536.sHtMLWWw.blog.bufdnv.cN/Article/details/609976.sHtMLWWw.blog.bufdnv.cN/Article/details/408080.sHtMLWWw.blog.bufdnv.cN/Article/details/604660.sHtMLWWw.blog.bufdnv.cN/Article/details/752620.sHtMLWWw.blog.bufdnv.cN/Article/details/618995.sHtMLWWw.blog.bufdnv.cN/Article/details/951551.sHtMLWWw.blog.bufdnv.cN/Article/details/329285.sHtMLWWw.blog.bufdnv.cN/Article/details/806796.sHtMLWWw.blog.bufdnv.cN/Article/details/231697.sHtMLWWw.blog.bufdnv.cN/Article/details/871119.sHtMLWWw.blog.bufdnv.cN/Article/details/139532.sHtMLWWw.blog.bufdnv.cN/Article/details/997371.sHtMLWWw.blog.bufdnv.cN/Article/details/836742.sHtMLWWw.blog.bufdnv.cN/Article/details/202633.sHtMLWWw.blog.bufdnv.cN/Article/details/524584.sHtMLWWw.blog.bufdnv.cN/Article/details/364279.sHtMLWWw.blog.bufdnv.cN/Article/details/733603.sHtMLWWw.blog.bufdnv.cN/Article/details/884053.sHtMLWWw.blog.bufdnv.cN/Article/details/598274.sHtMLWWw.blog.bufdnv.cN/Article/details/289372.sHtMLWWw.blog.bufdnv.cN/Article/details/782506.sHtMLWWw.blog.bufdnv.cN/Article/details/594673.sHtMLWWw.blog.bufdnv.cN/Article/details/172727.sHtMLWWw.blog.bufdnv.cN/Article/details/312403.sHtMLWWw.blog.bufdnv.cN/Article/details/122128.sHtMLWWw.blog.bufdnv.cN/Article/details/400765.sHtMLWWw.blog.bufdnv.cN/Article/details/472988.sHtMLWWw.blog.bufdnv.cN/Article/details/623455.sHtMLWWw.blog.bufdnv.cN/Article/details/304371.sHtMLWWw.blog.bufdnv.cN/Article/details/923252.sHtMLWWw.blog.bufdnv.cN/Article/details/658419.sHtMLWWw.blog.bufdnv.cN/Article/details/917782.sHtMLWWw.blog.bufdnv.cN/Article/details/724376.sHtMLWWw.blog.bufdnv.cN/Article/details/976524.sHtMLWWw.blog.bufdnv.cN/Article/details/143652.sHtMLWWw.blog.bufdnv.cN/Article/details/658200.sHtMLWWw.blog.bufdnv.cN/Article/details/383891.sHtMLWWw.blog.bufdnv.cN/Article/details/514126.sHtMLWWw.blog.bufdnv.cN/Article/details/430822.sHtMLWWw.blog.bufdnv.cN/Article/details/995321.sHtMLWWw.blog.bufdnv.cN/Article/details/606050.sHtMLWWw.blog.bufdnv.cN/Article/details/479180.sHtMLWWw.blog.bufdnv.cN/Article/details/793083.sHtMLWWw.blog.bufdnv.cN/Article/details/383106.sHtMLWWw.blog.bufdnv.cN/Article/details/669487.sHtMLWWw.blog.bufdnv.cN/Article/details/188134.sHtMLWWw.blog.bufdnv.cN/Article/details/701681.sHtMLWWw.blog.bufdnv.cN/Article/details/129212.sHtMLWWw.blog.bufdnv.cN/Article/details/111083.sHtMLWWw.blog.bufdnv.cN/Article/details/337007.sHtMLWWw.blog.bufdnv.cN/Article/details/860831.sHtMLWWw.blog.bufdnv.cN/Article/details/947424.sHtMLWWw.blog.bufdnv.cN/Article/details/268595.sHtMLWWw.blog.bufdnv.cN/Article/details/256944.sHtMLWWw.blog.bufdnv.cN/Article/details/798789.sHtMLWWw.blog.bufdnv.cN/Article/details/514678.sHtMLWWw.blog.bufdnv.cN/Article/details/838222.sHtMLWWw.blog.bufdnv.cN/Article/details/163435.sHtMLWWw.blog.bufdnv.cN/Article/details/198803.sHtMLWWw.blog.bufdnv.cN/Article/details/253400.sHtMLWWw.blog.bufdnv.cN/Article/details/984288.sHtMLWWw.blog.bufdnv.cN/Article/details/402008.sHtMLWWw.blog.bufdnv.cN/Article/details/948068.sHtMLWWw.blog.bufdnv.cN/Article/details/475865.sHtMLWWw.blog.bufdnv.cN/Article/details/766496.sHtMLWWw.blog.bufdnv.cN/Article/details/576532.sHtMLWWw.blog.bufdnv.cN/Article/details/525244.sHtMLWWw.blog.bufdnv.cN/Article/details/220058.sHtMLWWw.blog.bufdnv.cN/Article/details/545811.sHtMLWWw.blog.bufdnv.cN/Article/details/696384.sHtMLWWw.blog.bufdnv.cN/Article/details/498763.sHtMLWWw.blog.bufdnv.cN/Article/details/945107.sHtMLWWw.blog.bufdnv.cN/Article/details/822914.sHtMLWWw.blog.bufdnv.cN/Article/details/739489.sHtMLWWw.blog.bufdnv.cN/Article/details/415737.sHtMLWWw.blog.bufdnv.cN/Article/details/605029.sHtMLWWw.blog.bufdnv.cN/Article/details/407420.sHtMLWWw.blog.bufdnv.cN/Article/details/151356.sHtMLWWw.blog.bufdnv.cN/Article/details/932079.sHtMLWWw.blog.bufdnv.cN/Article/details/296629.sHtMLWWw.blog.bufdnv.cN/Article/details/305707.sHtMLWWw.blog.bufdnv.cN/Article/details/902864.sHtMLWWw.blog.bufdnv.cN/Article/details/819508.sHtMLWWw.blog.bufdnv.cN/Article/details/334928.sHtMLWWw.blog.bufdnv.cN/Article/details/868843.sHtMLWWw.blog.bufdnv.cN/Article/details/119691.sHtMLWWw.blog.bufdnv.cN/Article/details/633664.sHtMLWWw.blog.bufdnv.cN/Article/details/557932.sHtMLWWw.blog.bufdnv.cN/Article/details/284266.sHtMLWWw.blog.bufdnv.cN/Article/details/120091.sHtMLWWw.blog.bufdnv.cN/Article/details/501308.sHtMLWWw.blog.bufdnv.cN/Article/details/777139.sHtMLWWw.blog.bufdnv.cN/Article/details/139872.sHtMLWWw.blog.bufdnv.cN/Article/details/886742.sHtMLWWw.blog.bufdnv.cN/Article/details/857776.sHtMLWWw.blog.bufdnv.cN/Article/details/586388.sHtMLWWw.blog.bufdnv.cN/Article/details/971963.sHtMLWWw.blog.bufdnv.cN/Article/details/184046.sHtMLWWw.blog.bufdnv.cN/Article/details/628028.sHtMLWWw.blog.bufdnv.cN/Article/details/760488.sHtMLWWw.blog.bufdnv.cN/Article/details/335017.sHtMLWWw.blog.bufdnv.cN/Article/details/714331.sHtMLWWw.blog.bufdnv.cN/Article/details/556415.sHtMLWWw.blog.bufdnv.cN/Article/details/630920.sHtMLWWw.blog.bufdnv.cN/Article/details/696085.sHtMLWWw.blog.bufdnv.cN/Article/details/710780.sHtMLWWw.blog.bufdnv.cN/Article/details/817862.sHtMLWWw.blog.bufdnv.cN/Article/details/415367.sHtMLWWw.blog.bufdnv.cN/Article/details/499487.sHtMLWWw.blog.bufdnv.cN/Article/details/296071.sHtMLWWw.blog.bufdnv.cN/Article/details/851260.sHtMLWWw.blog.bufdnv.cN/Article/details/778908.sHtMLWWw.blog.bufdnv.cN/Article/details/867308.sHtMLWWw.blog.bufdnv.cN/Article/details/118784.sHtMLWWw.blog.bufdnv.cN/Article/details/186491.sHtMLWWw.blog.bufdnv.cN/Article/details/445858.sHtMLWWw.blog.bufdnv.cN/Article/details/177367.sHtMLWWw.blog.bufdnv.cN/Article/details/699032.sHtMLWWw.blog.bufdnv.cN/Article/details/928507.sHtMLWWw.blog.bufdnv.cN/Article/details/504118.sHtMLWWw.blog.bufdnv.cN/Article/details/170841.sHtMLWWw.blog.bufdnv.cN/Article/details/813841.sHtMLWWw.blog.bufdnv.cN/Article/details/199714.sHtMLWWw.blog.bufdnv.cN/Article/details/454721.sHtMLWWw.blog.bufdnv.cN/Article/details/197717.sHtMLWWw.blog.bufdnv.cN/Article/details/796895.sHtMLWWw.blog.bufdnv.cN/Article/details/257484.sHtMLWWw.blog.bufdnv.cN/Article/details/887368.sHtMLWWw.blog.bufdnv.cN/Article/details/331976.sHtMLWWw.blog.bufdnv.cN/Article/details/195973.sHtMLWWw.blog.bufdnv.cN/Article/details/972543.sHtMLWWw.blog.bufdnv.cN/Article/details/641992.sHtMLWWw.blog.bufdnv.cN/Article/details/372898.sHtMLWWw.blog.bufdnv.cN/Article/details/388741.sHtMLWWw.blog.bufdnv.cN/Article/details/925324.sHtMLWWw.blog.bufdnv.cN/Article/details/932417.sHtMLWWw.blog.bufdnv.cN/Article/details/955114.sHtMLWWw.blog.bufdnv.cN/Article/details/526033.sHtMLWWw.blog.bufdnv.cN/Article/details/269583.sHtMLWWw.blog.bufdnv.cN/Article/details/310342.sHtMLWWw.blog.bufdnv.cN/Article/details/652777.sHtMLWWw.blog.bufdnv.cN/Article/details/932054.sHtMLWWw.blog.bufdnv.cN/Article/details/283615.sHtMLWWw.blog.bufdnv.cN/Article/details/647079.sHtMLWWw.blog.bufdnv.cN/Article/details/136378.sHtMLWWw.blog.bufdnv.cN/Article/details/748866.sHtMLWWw.blog.bufdnv.cN/Article/details/907491.sHtMLWWw.blog.bufdnv.cN/Article/details/861621.sHtMLWWw.blog.bufdnv.cN/Article/details/558857.sHtMLWWw.blog.bufdnv.cN/Article/details/169789.sHtMLWWw.blog.bufdnv.cN/Article/details/607809.sHtMLWWw.blog.bufdnv.cN/Article/details/497185.sHtMLWWw.blog.bufdnv.cN/Article/details/728590.sHtMLWWw.blog.bufdnv.cN/Article/details/257813.sHtMLWWw.blog.bufdnv.cN/Article/details/637546.sHtMLWWw.blog.bufdnv.cN/Article/details/200998.sHtMLWWw.blog.bufdnv.cN/Article/details/760347.sHtMLWWw.blog.bufdnv.cN/Article/details/680805.sHtMLWWw.blog.bufdnv.cN/Article/details/305957.sHtMLWWw.blog.bufdnv.cN/Article/details/528048.sHtMLWWw.blog.bufdnv.cN/Article/details/441941.sHtMLWWw.blog.bufdnv.cN/Article/details/442746.sHtMLWWw.blog.bufdnv.cN/Article/details/281607.sHtMLWWw.blog.bufdnv.cN/Article/details/969763.sHtMLWWw.blog.bufdnv.cN/Article/details/312000.sHtMLWWw.blog.bufdnv.cN/Article/details/412012.sHtMLWWw.blog.bufdnv.cN/Article/details/548127.sHtMLWWw.blog.bufdnv.cN/Article/details/925362.sHtMLWWw.blog.bufdnv.cN/Article/details/692570.sHtMLWWw.blog.bufdnv.cN/Article/details/157705.sHtMLWWw.blog.bufdnv.cN/Article/details/725219.sHtMLWWw.blog.bufdnv.cN/Article/details/289338.sHtMLWWw.blog.bufdnv.cN/Article/details/487492.sHtMLWWw.blog.bufdnv.cN/Article/details/749159.sHtMLWWw.blog.bufdnv.cN/Article/details/469147.sHtMLWWw.blog.bufdnv.cN/Article/details/245284.sHtMLWWw.blog.bufdnv.cN/Article/details/679604.sHtMLWWw.blog.bufdnv.cN/Article/details/285475.sHtMLWWw.blog.bufdnv.cN/Article/details/299869.sHtMLWWw.blog.bufdnv.cN/Article/details/549363.sHtMLWWw.blog.bufdnv.cN/Article/details/387271.sHtMLWWw.blog.bufdnv.cN/Article/details/194063.sHtMLWWw.blog.bufdnv.cN/Article/details/380031.sHtMLWWw.blog.bufdnv.cN/Article/details/729636.sHtMLWWw.blog.bufdnv.cN/Article/details/850910.sHtMLWWw.blog.bufdnv.cN/Article/details/659217.sHtMLWWw.blog.bufdnv.cN/Article/details/222238.sHtMLWWw.blog.bufdnv.cN/Article/details/820982.sHtMLWWw.blog.bufdnv.cN/Article/details/945502.sHtMLWWw.blog.bufdnv.cN/Article/details/957431.sHtMLWWw.blog.bufdnv.cN/Article/details/228859.sHtMLWWw.blog.bufdnv.cN/Article/details/196302.sHtMLWWw.blog.bufdnv.cN/Article/details/355504.sHtMLWWw.blog.bufdnv.cN/Article/details/988548.sHtMLWWw.blog.bufdnv.cN/Article/details/496951.sHtMLWWw.blog.bufdnv.cN/Article/details/321635.sHtMLWWw.blog.bufdnv.cN/Article/details/848832.sHtMLWWw.blog.bufdnv.cN/Article/details/420102.sHtMLWWw.blog.bufdnv.cN/Article/details/122498.sHtMLWWw.blog.bufdnv.cN/Article/details/712132.sHtMLWWw.blog.bufdnv.cN/Article/details/157647.sHtMLWWw.blog.bufdnv.cN/Article/details/849831.sHtMLWWw.blog.bufdnv.cN/Article/details/698730.sHtMLWWw.blog.bufdnv.cN/Article/details/905845.sHtMLWWw.blog.bufdnv.cN/Article/details/127573.sHtMLWWw.blog.bufdnv.cN/Article/details/507072.sHtMLWWw.blog.bufdnv.cN/Article/details/663908.sHtMLWWw.blog.bufdnv.cN/Article/details/181276.sHtMLWWw.blog.bufdnv.cN/Article/details/125318.sHtMLWWw.blog.bufdnv.cN/Article/details/470230.sHtMLWWw.blog.bufdnv.cN/Article/details/211324.sHtMLWWw.blog.bufdnv.cN/Article/details/582464.sHtMLWWw.blog.bufdnv.cN/Article/details/983845.sHtMLWWw.blog.bufdnv.cN/Article/details/984129.sHtMLWWw.blog.bufdnv.cN/Article/details/302631.sHtMLWWw.blog.bufdnv.cN/Article/details/985363.sHtMLWWw.blog.bufdnv.cN/Article/details/357412.sHtMLWWw.blog.bufdnv.cN/Article/details/724831.sHtMLWWw.blog.bufdnv.cN/Article/details/210736.sHtMLWWw.blog.bufdnv.cN/Article/details/926354.sHtMLWWw.blog.bufdnv.cN/Article/details/260525.sHtMLWWw.blog.bufdnv.cN/Article/details/547182.sHtMLWWw.blog.bufdnv.cN/Article/details/141797.sHtMLWWw.blog.bufdnv.cN/Article/details/901513.sHtMLWWw.blog.bufdnv.cN/Article/details/232581.sHtMLWWw.blog.bufdnv.cN/Article/details/720205.sHtMLWWw.blog.bufdnv.cN/Article/details/167916.sHtMLWWw.blog.bufdnv.cN/Article/details/435325.sHtMLWWw.blog.bufdnv.cN/Article/details/268820.sHtMLWWw.blog.bufdnv.cN/Article/details/332635.sHtMLWWw.blog.bufdnv.cN/Article/details/523224.sHtMLWWw.blog.bufdnv.cN/Article/details/111186.sHtMLWWw.blog.bufdnv.cN/Article/details/982551.sHtMLWWw.blog.bufdnv.cN/Article/details/415827.sHtMLWWw.blog.bufdnv.cN/Article/details/856948.sHtMLWWw.blog.bufdnv.cN/Article/details/930654.sHtMLWWw.blog.bufdnv.cN/Article/details/386689.sHtMLWWw.blog.bufdnv.cN/Article/details/311245.sHtMLWWw.blog.bufdnv.cN/Article/details/266749.sHtMLWWw.blog.bufdnv.cN/Article/details/220575.sHtMLWWw.blog.bufdnv.cN/Article/details/846581.sHtMLWWw.blog.bufdnv.cN/Article/details/701874.sHtMLWWw.blog.bufdnv.cN/Article/details/515654.sHtMLWWw.blog.bufdnv.cN/Article/details/566590.sHtMLWWw.blog.bufdnv.cN/Article/details/848468.sHtMLWWw.blog.bufdnv.cN/Article/details/698983.sHtMLWWw.blog.bufdnv.cN/Article/details/291285.sHtMLWWw.blog.bufdnv.cN/Article/details/273057.sHtMLWWw.blog.bufdnv.cN/Article/details/937251.sHtMLWWw.blog.bufdnv.cN/Article/details/323155.sHtMLWWw.blog.bufdnv.cN/Article/details/453321.sHtMLWWw.blog.bufdnv.cN/Article/details/683869.sHtMLWWw.blog.bufdnv.cN/Article/details/525276.sHtMLWWw.blog.bufdnv.cN/Article/details/192657.sHtMLWWw.blog.bufdnv.cN/Article/details/718958.sHtMLWWw.blog.bufdnv.cN/Article/details/603783.sHtMLWWw.blog.bufdnv.cN/Article/details/556857.sHtMLWWw.blog.bufdnv.cN/Article/details/631925.sHtMLWWw.blog.bufdnv.cN/Article/details/583617.sHtMLWWw.blog.bufdnv.cN/Article/details/772099.sHtMLWWw.blog.bufdnv.cN/Article/details/490031.sHtMLWWw.blog.bufdnv.cN/Article/details/925564.sHtMLWWw.blog.bufdnv.cN/Article/details/597975.sHtMLWWw.blog.bufdnv.cN/Article/details/385760.sHtMLWWw.blog.bufdnv.cN/Article/details/973381.sHtMLWWw.blog.bufdnv.cN/Article/details/286667.sHtMLWWw.blog.bufdnv.cN/Article/details/722068.sHtMLWWw.blog.bufdnv.cN/Article/details/769971.sHtMLWWw.blog.bufdnv.cN/Article/details/322758.sHtMLWWw.blog.bufdnv.cN/Article/details/191000.sHtMLWWw.blog.bufdnv.cN/Article/details/708293.sHtMLWWw.blog.bufdnv.cN/Article/details/375173.sHtMLWWw.blog.bufdnv.cN/Article/details/852374.sHtMLWWw.blog.bufdnv.cN/Article/details/519475.sHtMLWWw.blog.bufdnv.cN/Article/details/809254.sHtMLWWw.blog.bufdnv.cN/Article/details/605723.sHtMLWWw.blog.bufdnv.cN/Article/details/480610.sHtMLWWw.blog.bufdnv.cN/Article/details/811535.sHtMLWWw.blog.bufdnv.cN/Article/details/100097.sHtMLWWw.blog.bufdnv.cN/Article/details/466148.sHtMLWWw.blog.bufdnv.cN/Article/details/619822.sHtMLWWw.blog.bufdnv.cN/Article/details/529980.sHtMLWWw.blog.bufdnv.cN/Article/details/965130.sHtMLWWw.blog.bufdnv.cN/Article/details/839687.sHtMLWWw.blog.bufdnv.cN/Article/details/783677.sHtMLWWw.blog.bufdnv.cN/Article/details/576814.sHtMLWWw.blog.bufdnv.cN/Article/details/123351.sHtMLWWw.blog.bufdnv.cN/Article/details/688072.sHtMLWWw.blog.bufdnv.cN/Article/details/601625.sHtMLWWw.blog.bufdnv.cN/Article/details/450888.sHtMLWWw.blog.bufdnv.cN/Article/details/249871.sHtMLWWw.blog.bufdnv.cN/Article/details/241120.sHtMLWWw.blog.bufdnv.cN/Article/details/576625.sHtMLWWw.blog.bufdnv.cN/Article/details/815185.sHtMLWWw.blog.bufdnv.cN/Article/details/225447.sHtML