MySQL索引相关

MySQL中，有四种索引：主键索引（pk_）、普通索引（idx_）、唯一索引（uk_）、全文索引（ft_）。

查询索引：show index from 表名查询分析器：EXPLAIN或DESC
type=const表示通过索引一次就找到了； 
key=primary的话，表示使用了主键，如果结果中key为空，则索引失效； 
type=all,表示为全表扫描； 
key=null表示没用到索引。

 

索引失效汇总（很多情况有不确定性，实际情况实用explain具体分析）：

1、建立联合索引，where非第一个字段，无索引。

查询语句：

DESC SELECT * FROM tb_ggt_goods WHERE  gg_shoptitle = '夏芙丽旗舰店';

 

2、对索引列运算，运算包括（+、-、*、/、！、<>、%、like'%放在前面'、or、in、exist等），导致索引失效。

DESC SELECT * FROM tb_ggt_goods WHERE gg_title like '%夏芙丽旗舰店';

like '%key'不走索引。

DESC SELECT * FROM tb_ggt_goods WHERE gg_title like '夏芙丽旗舰店%';

like 'key%'走索引。

解决方案：使用MySQL全文索引 或虚拟列

查询全文索引变量，修改相应变量可以设置分词长度：

SHOW VARIABLES LIKE 'ft_%'

一、全文索引：

参考：http://www.cnblogs.com/martinzhang/p/3220345.html

MySQL不支持中文全文索引，先进行分词，再按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。

全文索引不起作用，大多数是由于mysql未开启全文索引引起 ，mysql开启全文索引方法：

1、修改mysql配置文件：window服务器为my.ini，linux服务器为my.cnf，在 [mysqld] 后面加入一行“ft_min_word_len=1”，然后重启Mysql。

单列全文索引：

创建：

ALTER TABLE tb_ggt_collectdata ADD FULLTEXT INDEX ft_gc_title_index(gc_title_index);

使用：

SELECT * FROM tb_ggt_collectdata WHERE MATCH(gc_title_index) AGAINST('22252225' IN BOOLEAN MODE);

联合全文索引：

创建：

ALTER TABLE ggt_goods ADD FULLTEXT INDEX ft_comb_title_index(gg_title_index, gg_shoptitle_index);

使用：

SELECT * FROM tab_name WHERE MATCH ('列名1,列名2...列名n') AGAINST('词1 词2 词3 ... 词m');

SELECT * FROM ggt_goods WHERE MATCH(gg_title_index,gg_shoptitle_index) AGAINST('425427903139' IN BOOLEAN MODE)

检索方式

1、自然语言检索： IN NATURAL LANGUAGE MODE

2、布尔检索： IN BOOLEAN MODE（剔除一半匹配行以上都有的词）

搜索语法规则：
         +   一定要有(不含有该关键词的数据条均被忽略)。 
         -   不可以有(排除指定关键词，含有该关键词的均被忽略)。 
         >   提高该条匹配数据的权重值。 
         <   降低该条匹配数据的权重值。
         ~   将其相关性由正转负，表示拥有该字会降低相关性(但不像 - 将之排除)，只是排在较后面权重值降低。 
         *   万用字，不像其他语法放在前面，这个要接在字符串后面。 
         " " 用双引号将一段句子包起来表示要完全相符，不可拆字。

 

二、虚拟列：

mysql版本需要是5.7及以上版本才支持建立函数索引

函数会阻止索引，建立虚拟列存放函数计算结果，再对虚拟列进行索引。

MySQL 5.7之后增加了对generated column的支持，能够在此列中指定一些预先定义的表达式（predefined expression）或者是结合其他列使用一些函数计算出相应的结果做给该列的值。

generated column有两种类型，分别是virtual(默认值)，stored，前者并不会将值存储到磁盘，后者会，在性能上前者更优。

创建generated column后，insert，update，drop关联的column的时候将有可能报错。

建立时间函数虚拟列：

ALTER TABLE union_member ADD COLUMN virtual_col_date date GENERATED ALWAYS AS (adddate(gmt_modified,INTERVAL 18 YEAR)) VIRTUAL;

建立反转函数虚拟列：

ALTER TABLE union_member ADD COLUMN virtual_col_addr VARCHAR(50) GENERATED ALWAYS AS (reverse(um_addr)) VIRTUAL;

查看表结构：

desc union_member

给虚拟列加索引：

CREATE INDEX idx_virtual_col_date on union_member(virtual_col_date);

查看索引：

show index from union_member;