MySQL中文全文检索demoSQL - 苜蓿草网络
网站建设服务
微信接口开发&&&&
企业形象设计
MySQL中文全文检索demoSQL
时间： 12:03&&&&
热度：11852
&&&&来源：博客园
一、概述MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。二、语法MATCH&(col1,col2,...)&AGAINST&(expr&[search_modifier])
search_modifier:&{&IN&BOOLEAN&MODE&|&WITH&QUERY&EXPANSION&}例如：SELECT * FROM tab_name WHERE MATCH ('列名1,列名2...列名n') AGAINST('词1 词2 词3 ... 词m');即：MATCH 相当于要匹配的列，而 AGAINST 就是要找的内容。&&这里的table需要是MyISAM类型的表，col1、col2 必须是char、varchar或text类型，在查询之前需要在 col1 和 col2 上分别建立全文索引(FULLTEXT索引)。三、检索方式1、自然语言检索： IN NATURAL LANGUAGE MODE2、布尔检索： IN BOOLEAN MODE剔除一半匹配行以上都有的词，譬如说，每个行都有this这个字的话，那用this去查时，会找不到任何结果，这在记录条数特别多时很有用，原因是数据库认为把所有行都找出来是没有意义的，这时，this几乎被当作是stopword(中断词)；但是若只有两行记录时，是啥鬼也查不出来的，因为每个字都出现50%（或以上），要避免这种状况，请用IN BOOLEAN MODE。IN BOOLEAN MODE的特色： ·不剔除50%以上符合的row。·不自动以相关性反向排序。&·可以对没有FULLTEXT index的字段进行搜寻，但会非常慢。·限制最长与最短的字符串。·套用Stopwords。搜索语法规则：+&& 一定要有(不含有该关键词的数据条均被忽略)。-&& 不可以有(排除指定关键词，含有该关键词的均被忽略)。&&&& 提高该条匹配数据的权重值。&&& 降低该条匹配数据的权重值。~&& 将其相关性由正转负，表示拥有该字会降低相关性(但不像 - 将之排除)，只是排在较后面权重值降低。*&& 万用字，不像其他语法放在前面，这个要接在字符串后面。& & 用双引号将一段句子包起来表示要完全相符，不可拆字。--&+&表示AND，即必须包含。-&表示NOT，即必须不包含。即：返回记录必需包含&apple，且不能包含&banner。
SELECT&*&FROM&articles&WHERE&MATCH&(title,content)&AGAINST&('+apple&-banana'&IN&BOOLEAN&MODE);
--&apple和banana之间是空格，空格表示OR。即：返回记录至少包含apple、banana中的一个。
SELECT&*&FROM&articles&WHERE&MATCH&(title,content)&AGAINST&('apple&banana'&IN&BOOLEAN&MODE);
--&返回记录必须包含apple，同时banana可包含也可不包含，若包含的话会获得更高的权重。
SELECT&*&FROM&articles&WHERE&MATCH&(title,content)&AGAINST&('+apple&banana'&IN&BOOLEAN&MODE);
--&~&是我们熟悉的异或运算符。返回记录必须包含apple，若也包含了banana会降低权重。
--&但是它没有&+apple&-banana&严格，因为后者如果包含banana压根就不返回。
SELECT&*&FROM&articles&WHERE&MATCH&(title,content)&AGAINST&('+apple&~banana'&IN&BOOLEAN&MODE);
--&返回必须同时包含“apple&banana”或者必须同时包含“apple&orange”的记录。
--&若同时包含“apple&banana”和“apple&orange”的记录，则“apple&banana”的权重高于“apple&orange”的权重。
SELECT&*&FROM&articles&WHERE&MATCH&(title,content)&AGAINST&('+apple&+(&banana&&orange)'&IN&BOOLEAN&MODE);&3、查询扩展检索： WITH QUERY EXPANSION四、MySQL全文检索的条件限制1、在MySQL5.6以下，只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索。2、相应字段建立FULLTEXT索引五、与全文检索相关的系统变量：ft_min_word_len = 全文检索的最小许可字符(默认4，通过 SHOW VARIABLES LIKE 'ft_min_word_len' 可查看)，中文通常是两个字就是一个词，所以做中文的话需要修改这个值为2最好。六、总结事项1、预设搜寻是不分大小写，若要分大小写，columne 的 character set要从utf8改成utf8_bin。2、预设 MATCH...AGAINST 是以相关性排序，由高到低。3、MATCH(title, content)里的字段必须和FULLTEXT(title, content)里的字段一模一样。如果只要单查title或content一个字段，那得另外再建一个 FULLTEXT(title) 或 FULLTEXT(content)，也因为如此，MATCH()的字段一定不能跨table，但是另外两种搜寻方式好像可以。4、MySQL不支持中文全文索引，原因很简单：与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方，截至目前MySQL5.6版本是如此，但是有变通的办法，就是将整句的中文分词，并按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。七、实验部分◆ 步骤1 配置my.ini，在my.ini末尾添加如下：# 修改全文检索的最小许可字符为2个字符或汉字 ft_min_word_len = 2完成后“重启 MySQL 服务”，并用 SHOW VARIABLES LIKE 'ft_min_word_len' 查询下是否得到了正确的结果值为2，如下图：◆ 步骤2 创建数据库（视情况可跳过此步）CREATE&DATABASE&search&DEFAULT&CHARACTER&SET&utf8&COLLATE&utf8_general_◆ 步骤3 创建数据表CREATE&TABLE&`zzx_articles`&(
&&&&`id`&int(10)&unsigned&NOT&NULL&auto_increment,&
&&&&`title`&char(254)&default&NULL&COMMENT&'标题',
&&&&`content`&text&COMMENT&'内容',
&&&&`author`&char(60)&default&NULL&COMMENT&'作者',
&&&&`title_fc`&char(254)&default&NULL&COMMENT&'标题的分词',
&&&&`content_fc`&text&COMMENT&'内容的分词',
&&&&PRIMARY&KEY&&(`id`),
&&&&FULLTEXT&KEY&`zzx_title_fc`&(`title_fc`),
&&&&FULLTEXT&KEY&`zzx_content_fc`&(`content_fc`),
&&&&FULLTEXT&KEY&`zzx_title_con_fc`&(`title_fc`,`content_fc`)
)&ENGINE=MyISAM&DEFAULT&CHARSET=utf8# 如果后期需要添全文加索引可以用如下语句：alter&table&`zzx_articles`&add&fulltext&zzx_title_fc(`title_fc`);&
alter&table&`zzx_articles`&add&fulltext&zzx_con_fc(`content_fc`);&
alter&table&`zzx_articles`&add&fulltext&zzx_title_con_fc(`title_fc`,`content_fc`);◆ 步骤4 插入测试数据INSERT&INTO&`zzx_articles`&(title_fc,content_fc)&VALUES
('MySQL&Tutorial&Linux&red','DBMS&stands&for&DataBase&ok'),
('How&To&Use&MySQL&Well','After&you&went&through&blue'),
('Optimizing&MySQL&ok','In&this&tutorial&we&will&optimizing'),
('MySQL&vs&this&YourSQL&blue&red','1.&Never&run&mysqld&as&root&red'),
('MySQL&Tricks&blue','In&the&following&database'),
('MySQL&Security','When&configured&properly,&MySQL'),
('中华','中华人民共和国&'),
('中华情&和谐','上海&和谐'),
('污染之都','你好&我是&北京&人'),
('北京精神','创新&爱国&包容&厚颜')插入结果如下图：◆ 步骤5 搜索语法规则、排序 实验说明：匹配语句 MATCH (col1,col2,...) AGAINST (expr [search_modifier]) 匹配完成后，会返回此条数据的权重值(权重值1 ≈ 各个词的匹配结果权重值之和)，我们利用此权重值“由高到低”排序可优化查询结果。------------------------------------------------------------------------------------------------------------------------------? 实验1：只对 title_fc 索引字段做全文检索，并显示每条数据的权重值SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：1.当没有加 + - 这样的过滤符号时，这些关键词是“或(or)”的关系，即：要么匹配optimizing，要么匹配ok，要么匹配red，要么匹配blue。2.通过上面实验，发现当某条数据有多个关键词匹配时(如：red blue)，此条数据的权重值会略高：此条数据权重值 ≈ optimizing权重值 + ok权重值 + red权重值 + blue权重值理论上来说，当一条数据能匹配上的关键词越多，则此条数据的权重值越高，排名越靠前。------------------------------------------------------------------------------------------------------------------------------? 实验2：过滤条件：必须包含&red&关键词SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('optimizing&ok&+red'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：使用了过滤符号 + ，表示查询结果中，任一条数据都必须包含&red&这个词，不包含&red&这个词的行均被忽略。------------------------------------------------------------------------------------------------------------------------------? 实验3：过滤条件：必须包含&red&关键词，如果匹配到的行中还含有&blue&关键词，则会对此条数据增加权重：SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('optimizing&ok&+red&blue'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&或下面写法：SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&&blue')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('optimizing&ok&+red&&blue'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：与实验2比较，当包含了red的行中，若也包含blue关键词，权重确实增加了(如：id=4这条)。------------------------------------------------------------------------------------------------------------------------------? 实验4：过滤条件：必须包含&red&关键词，并且不能包含&blue&关键词SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('optimizing&ok&+red&-blue'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：可见 + - 这两个符号是表示“并且(and)”的意思，即：必须包含red关键词 and 不能包含blue关键词。------------------------------------------------------------------------------------------------------------------------------? 实验5：过滤条件：必须包含&red&关键词，如果匹配到的行中还包含&blue&关键词则降低此条数据权重SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&+red&~blue')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('optimizing&ok&+red&~blue'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：这个实验没有看到明显效果，但 ~ 过滤符确实是降低此权重符------------------------------------------------------------------------------------------------------------------------------? 实验6：过滤条件：匹配包含单词“red”和“Linux” 的行，或包含“red” 和“blue”的行(无先后顺序)然而包含 “apple Linux”的行较包含“apple blue”的行有更高的权重值。SELECT&*,MATCH&(title_fc)&AGAINST&('+red&&+(&Linux&&blue)')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('+red&&+(&Linux&&blue)'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& ------------------------------------------------------------------------------------------------------------------------------&? 实验7：过滤条件：匹配关键词以 re 开头，或以 bl 开头的数据行SELECT&*,MATCH&(title_fc)&AGAINST&('re*&bl*')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('re*&bl*'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：注意 * 是写在后面哦，此时相当于 like 模糊匹配，没有权重值了------------------------------------------------------------------------------------------------------------------------------&? 实验8：过滤条件：匹配查找字符串“To Use MySQL”关键词SELECT&*,MATCH&(title_fc)&AGAINST&('&To&Use&MySQL&')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('&To&Use&MySQL&'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：此时是把双引号内的的字符串看成一个关键词，若不用双引号则是将 To Use MySQL 三个关键词去分别匹配，两者有区别；------------------------------------------------------------------------------------------------------------------------------? 实验9：在实验1基础上，将blue的权重值忽视不要(注意与实验1比较)SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：在实验1的基础上，此时去除select字段条件里的blue关键词，但在where里去仍保留blue关键词。我的本意是想正常匹配“optimizing ok red blue”这几个关键词，但不想得到blue的权重值(忽视blue的权重值)。查询的结果是含有blue关键词的数据的权重值会略降低了。通过“降重”——忽略某些关键词权重值的方式可使部分数据权重值减小，进而影响排序。------------------------------------------------------------------------------------------------------------------------------? 实验10：在实验9的基础上，在select字段条件里增加几个red关键词，where里的关键词保持不变(注意与实验1 实验9比较)。SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&red&red')&as&title_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue'&IN&BOOLEAN&MODE)&order&by&title_score&DESC&& 总结：发现只要含有 red 关键词的数据的权重值都增加了，排序也发生了变化。说明通过“提重”——重复多次某些关键词权重值的方式可使部分数据权重值增加，进而影响排序。------------------------------------------------------------------------------------------------------------------------------? 实验11：同时对 title_fc 和 content_fc 两字段做全文检索SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&as&title_score,MATCH&(content_fc)&AGAINST&('optimizing&ok&red&blue')&as&content_score
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc,content_fc)&AGAINST&('optimizing&ok&red&blue'&IN&BOOLEAN&MODE)&order&by&title_score&DESC,content_score&DESC&& 总结：通过实验发现，又成功的取到了 content_fc 字段匹配的权重值，排序方式是首要按title字段权重降序排序，次要按 content_fc 权重降序排序。另外我发现 ok 这个关键词在对“title_fc char(254)”字段匹配时得到匹配值为2.1xxxxxxx，但对“content_fc text”字段匹配时权重值去为0，这是MySQL对各英文单词权重值的给予有自己的算法，我们无权干涉。所以当我们发现有些单词的权重值为零甚至为负时，不用过于纠结，因为MySQL有自己的算法。关于排序，首要按 title_score 字段权重降序排序，次要按 content_score 权重降序排序，这样的排序规则看起来更科学了，但我想优秀的搜索引擎绝不至于简单如此吧，继续下面的实验：-----------------------------------------------------------------------------------------------------------------------------? 实验12：进一步优化 排序规则看一个SQL语句原型，查询“字段1,字段2”两字段，同时将每条数据的“字段1”与“字段2”的求和作为“字段3”字段：select&字段1,字段2,字段1&+&字段2&as&字段3
&&&&&&&from&表名
&&&&&&&where&.....下面将 title_fc 和 content_fc 两权重值求和，放入新字段 score1 中，并按 score1 首要排序，title_score 次之，content_score再次之：SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&as&title_score,MATCH&(content_fc)&AGAINST&('optimizing&ok&red&blue')&as&content_score,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&+&MATCH&(content_fc)&AGAINST&('optimizing&ok&red&blue')&as&score1
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc,content_fc)&AGAINST&('optimizing&ok&red&blue'&IN&BOOLEAN&MODE)&order&by&score1&DESC,title_score&DESC,content_score&DESC总结：相对而言，如果 title_fc 和 content_fc 都匹配上了，应给予靠前的排名吧。所以首要按其 title_fc 和 content_fc 两权重值之和排名，次要再考虑 title_fc、content_fc 排序。------------------------------------------------------------------------------------------------------------------------------? 实验13：另一个角度看排序看一个SQL语句原型，如果性别字段值为“1”显示“男”否则显示“女”select&*,IF(sex=1,&男&,&女&)&as&ssva&from&表名&where&id&=&1&我的新排序思路：如果 title_fc 和 content_fc 同时匹配上的行做首要排序，然后对只匹配上 title_fc 的做次要排序，只匹配上 content_fc 的再次要排序。 (对于实验5的排序不科学之处在于：如果有一个对content_fc关键词的匹配权重很高，导致了求和后 score1 的值也高，但对title_fc的匹配权重去为0，由于score1值高却排到了前面这不一定科学吧？)SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&as&title_score,MATCH&(content_fc)&AGAINST&('optimizing&ok&red&blue')&as&content_score,IF(MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&&&0&AND&MATCH&(content_fc)&AGAINST&('optimizing&ok&red&blue')&&&0,1,0)&as&score1
&&&FROM&zzx_articles&
&&&WHERE&MATCH&(title_fc,content_fc)&AGAINST&('optimizing&ok&red&blue'&IN&BOOLEAN&MODE)&order&by&score1&DESC,title_score&DESC,content_score&DESC总结：如果 title_fc 和 content_fc 都匹配上了，做为优先排序理所当然，但也应考虑局部权重值过高的问题哦。------------------------------------------------------------------------------------------------------------------------------? 实验14：优化实验13，可支持更复杂的条件排序看一个SQL语句原型，CASE WHEN THEN END 结构：CASE&&单值表达式&
&&&&&&&&&&&&&&WHEN&&表达式值&&THEN&&SQL语句或者返回值&
&&&&&&&&&&&&&&WHEN&&表达式值&&THEN&&SQL语句或者返回值&
&&&&&&&&&&&&&&&...
&&&&&&&&&&&&&&WHEN&&表达式值&&THEN&&SQL语句或者返回值&
&&&&&&&&&&&&&&ELSE&&SQL语句或者返回值&
&&&&&&&&&&END有一表查询：大于或等于80表示显示为“优秀”，大于或等于60显示为“及格”，小于60分显示为“不及格”。select&(CASE&WHEN&语文&=80&THEN&'优秀'&WHEN&语文&=60&THEN&'及格'&ELSE&'不及格'&END)&as&语文,
&&&&&&&&&&&&&&(CASE&WHEN&数学&=80&THEN&'优秀'&WHEN&数学&=60&THEN&'及格'&ELSE&'不及格'&END)&as&数学,
&&&&&&&&&&&&&&(CASE&WHEN&英语&=80&THEN&'优秀'&WHEN&英语&=60&THEN&'及格'&ELSE&'不及格'&END)&as&英语
&&&&&&&from&table_name# 实例测试一下select&*,(CASE&WHEN&id&8&THEN&'5'&WHEN&id=8&THEN&'4'&ELSE&'0'&END)&as&newfield
&&&&&&&from&zzx_articles
&&&&&&&where&id&5我的新排序思路：如果 title_fc 和 content_fc 的权重值“同时大于0且相等”为首要排序，“同时大于0且不相等”的为次要排序，“title_fc 大于0的再次要排序”，如果用 IF() 貌似不好实现，看下面语句：SELECT&*,MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&as&title_score,MATCH&(content_fc)&AGAINST&('optimizing&ok&red&blue')&as&content_score,
&&&&&&&&&&&&&(CASE&WHEN&MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&&&0&AND&MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&=&MATCH&(content_fc)&AGAINST&('optimizing&ok&red&blue')&THEN&'3'&WHEN&MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&&&0&AND&MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&&&&MATCH&(content_fc)&AGAINST&('optimizing&ok&red&blue')&THEN&'2'&WHEN&MATCH&(title_fc)&AGAINST&('optimizing&ok&red&blue')&&&0&THEN&'1'&ELSE&'0'&END)&AS&score1
&&&&&&&FROM&zzx_articles&
&&&&&&&WHERE&MATCH&(title_fc,content_fc)&AGAINST&('optimizing&ok&red&blue'&IN&BOOLEAN&MODE)&order&by&score1&DESC,title_score&DESC,content_score&DESC总结：本实验的排序未必合乎科学，但引出一个更复杂规则的排序方式、角度，多种排序结合使用才能做出更合理的排序，才能使你的搜索引擎更加智能。抛砖引玉，或许你有更好的排序，请也分享给我。------------------------------------------------------------------------------------------------------------------------------? 实验15：中文全文检索MySQL不支持中文全文检索，因为中文一句话是连着写的，不像英文单词间有空格分隔。解决办法就是中文分词(关于中文分词请参阅其它文章)，如果你的MySQL是安装在Windows平台上的，可以不用转码直接存储中文就可以使用全文索引，如本例。但是如果你的MySQL是安装在Linux上的则需要进行转编码(urlencode / base64_encode / json_encode / 区位 / 拼音)等方案，具体方案参看其它博文。SELECT&*,MATCH&(title_fc)&AGAINST&('中华&北京&和谐&security')&as&title_score,MATCH&(content_fc)&AGAINST&('中华&北京&和谐&security')&as&content_score,(case&when&MATCH&(title_fc)&AGAINST&('中华&北京&和谐&security')&&&0&and&MATCH&(content_fc)&AGAINST&('中华&北京&和谐&security')&&&0&then&'5'&when&MATCH&(title_fc)&AGAINST&('中华&北京&和谐&security')&&&0&and&MATCH&(content_fc)&AGAINST&('中华&北京&和谐&security')&=&0&then&'4'&else&'0'&end)&as&score1
&&&&&&FROM&zzx_articles&
&&&&&&WHERE&MATCH&(title_fc,content_fc)&AGAINST&('中华&北京&和谐&security'&IN&BOOLEAN&MODE)&order&by&score1&DESC,title_score&DESC,content_score&DESC2059人阅读
mysql（3）
（一）索引的作用
索引通俗来讲就相当于书的目录，当我们根据条件查询的时候，没有索引，便需要全表扫描，数据量少还可以，一旦数据量超过百万甚至千万，一条查询sql执行往往需要几十秒甚至更多，5秒以上就已经让人难以忍受了。
提升查询速度的方向一是提升硬件(内存、cpu、硬盘)，二是在软件上优化（加索引、优化sql；优化sql不在本文阐述范围之内）。
能在软件上解决的，就不在硬件上解决，毕竟硬件提升代码昂贵，性价比太低。代价小且行之有效的解决方法就是合理的加索引。
索引使用得当，能使查询速度提升上万倍，效果惊人。
（二）mysql的索引类型：
mysql的索引有5种：主键索引、普通索引、唯一索引、全文索引、聚合索引（多列索引）。
唯一索引和全文索引用的很少，我们主要关注主键索引、普通索引和聚合索引。
1）主键索引：主键索引是加在主键上的索引，设置主键（primary key）的时候，mysql会自动创建主键索引；
2）普通索引：创建在非主键列上的索引；
3）聚合索引：创建在多列上的索引。
（三）索引的语法：
查看某张表的索引：show index from 表名；
创建普通索引：alter table 表名 add index& 索引名 (加索引的列)&
创建聚合索引：alter table 表名 add index& 索引名 (加索引的列1,加索引的列2)&
删除某张表的索引：drop index 索引名 on 表名;
（四）性能测试
测试环境：博主工作用台式机
处理器为Intel Core i5-GHz;
64位windows。
1：创建一张测试表
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
`id` bigint(20)
PRIMARY key not null AUTO_INCREMENT,
`username` varchar(11) DEFAULT NULL,
`gender` varchar(2) DEFAULT NULL,
`password` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;存储引擎使用MyISAM是因为此引擎没有事务，插入速度极快，方便我们快速插入千万条测试数据，等我们插完数据，再把存储类型修改为InnoDB。
2：使用存储过程插入1千万条数据
create procedure myproc()
set num=1;
while num &=
insert into test_user(username,gender,password) values(num,'保密',PASSWORD(num));
set num=num+1;
endcall myproc();
由于使用的MyISAM引擎，插入1千万条数据，仅耗时246秒，若是InnoDB引擎，插入100万条数据就要花费数小时了。
然后将存储引擎修改回InnDB。使用如下命令：& alter table test_user engine=InnoDB;此命令执行时间大约耗时5分钟，耐心等待。
3：sql测试
select id,username,gender,password from test_user where id=999999
耗时：0.114s。
因为我们建表的时候，将id设成了主键，所以执行此sql的时候，走了主键索引，查询速度才会如此之快。
我们再执行select id,username,gender,password from test_user where username='9000000'
耗时：4.613s。
我们给username列加上普通索引。
ALTER TABLE `test_user` ADD INDEX index_name(username) ;
此过程大约耗时&54.028s,建索引的过程会全表扫描，逐条建索引，当然慢了。
再来执行：selectid,username,gender,password from test_user where username='9000000'
耗时：0.043s。
再用username和password来联合查询
select id,username,gender,password& from test_user where username='9000000' or `password`='*3A70E147E88DD472410EFD9CD890AE'
此时虽然我们队username加了索引，但是password列未加索引，索引执行password筛选的时候，还是会全表扫描，因此此时
查询速度立马降了下来。
耗时：4.492s。
当我们的sql有多个列的筛选条件的时候，就需要对查询的多个列都加索引组成聚合索引：
加上聚合索引：ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
再来执行：
耗时：0.001s。
开篇也说过软件层面的优化一是合理加索引；二是优化执行慢的sql。此二者相辅相成，缺一不可，如果加了索引，还是查询很慢，这时候就要考虑是sql的问题了，优化sql。
实际生产中的sql往往比较复杂，如果数据量过了百万，加了索引后效果还是不理想，使用集群。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：58629次
积分：1276
积分：1276
排名：千里之外
原创：49篇
评论：60条
阅读：3087
阅读：19217
阅读：5022
(3)(10)(2)(5)(3)(7)(4)(6)(10)(4)}