Mysql 索引 n-gram分词引擎使用

概述：

　　类似于书籍的目录，找到一本书的特定内容，需要首先找到内容对应页码，定位对应页码

　　存储引擎使用类似方法进行数据查找，先找到索引中对应值，然后根据匹配的索引找到对应行

实现原理：

　　索引的实现通常使用B-Tree 及其变种B+树。（还有HASH）

优缺点：

　　优点：

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因
• 帮助服务器避免排序和临时表

　　缺点：

• 降低写、改速度
• 占用磁盘空间

使用场景：

• 对于非常小的表，全表扫描更快
• 中大型表索引非常有用

使用：

Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

PRIMARY KEY（主键索引）

  ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` ) 

UNIQUE(唯一索引)   

  ALTER TABLE `table_name` ADD UNIQUE (`col`)

INDEX(普通索引)    

ALTER TABLE `table_name` ADD INDEX index_name (`col`)

FULLTEXT(全文索引)    

  ALTER TABLE `table_name` ADD FULLTEXT ( `col` )

组合索引 (复合索引)

 ALTER TABLE `table_name` ADD INDEX index_name (`a`, `b`, `c` ) 

• 遵循前缀原则
• WHERE( `a` = 1,`c` = 1) 不会用到索引
• WHERE（`a` = 1 , `b` =1）可以用到索引

删除索引

ALTER TABLE table_name DROP INDEX index_name

查看索引

mysql> show index from tblname;
mysql> show keys from tblname;

主键索引和唯一索引区别：

• 主键索引只能有一个
• 主键索引一定是唯一索引，唯一索引不一定是主键索引
• 主键索引不能为空

索引创建原作

• 最适合索引的列是 WHERE子句 或者 ON 子句 后的列
• 根据情况适当创建复合索引
• 尽可能选择数据小的列，节约磁盘空间

mysql 常用命令行：

查看表结构

SHOW CREATE TABLE tableName G

修改存储引擎

ALTER TABLE my_table ENGINE = InnoDB;

注意事项：

1.索引不存储null值

2.不适合键值较少的列（重复数据较多的列）

3.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

4.索引失效的几种情况

　　1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

　　2.对于多列索引，不是使用的第一部分，则不会使用索引

　　3.like查询以%开头　　

　　4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

　　5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

全文索引：

分词索引基本使用方法：

 SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

分词，全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符

也就是说

SELECT * FROM `vote_record` where MATCH(`user_id`) AGAINST('aewk'); 

无法检索到数据库中user_id字段为 aewK0F7rGWvxZbNiTqj7 的值

mysql5.7版本之前不支持中文检索

解决方法：

　　1 . 在表中新建一个拼音字段，程序将中文转换拼音后存入这个字段

　　2. mysql5.7.6之后自带有 n-gram parser ，我们可以用他轻松的实现分词功能

使用方法：

注意：
    1 . ngram只支持InooDB引擎
    2.  每次在修改完mysql.ini后需要 OPTIMIZE TABLE TABLE_NAME 对索引重构

1. 在mysql.ini中设置分词大小，默认是2

[mysqld]
ngram_token_size=2

分词的SIZE越大，索引的体积就越大，所以要根据自身情况来设置合适的大小。

2. 创建表

CREATE TABLE articles (
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
            title VARCHAR(200),
            body TEXT,
            auth VARCHAR(30),
            FULLTEXT (title,body) WITH PARSER ngram   //这里声明使用分词引擎n-gram
        ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

如果是已经创建的表可以使用

ALTER TABLE table_name ADD FULLTEXT INDEX ft_name  (`name`, `company_code`, `office_address`) WITH PARSER ngram;

最后记得 OPTIMIZE TABLE table_name 重建索引(需要定期执行)

3. 显式指定全文检索表源 （这是一个非常有用的调试工具。如果我们发现一个包含某个词的文档，没有如我们所期望的那样出现在查询结果中，那么这个词可能是因为某些原因不在全文索引里面。）

mysql> SET GLOBAL innodb_ft_aux_table="new_feature/articles";   //new_feature为数据库名称, articles为表名称
Query OK, 0 rows affected (0.00 sec)

通过系统表，就可以查看到底是怎么划分articles里的数据。

mysql> SELECT *FROM information_schema.INNODB_FT_INDEX_CACHE LIMIT 20,10;

我们可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE和INFORMATION_SCHEMA.INNODB_FT_TABLE_TABLE来查询哪些词在全文索引里面。

查询：

1.自然语言模式下检索：

• 得到符合条件的个数

  mysql>SELECT COUNT(*) FROM articles
  -> WHERE MATCH (title,body) AGAINST ('数据库' IN NATURALLANGUAGE MODE);
  +----------+
  | COUNT(*) |
  +----------+
  |       4  |
  +----------+
  1 row in set (0.05 sec)

• 得到匹配的比率

   mysql>SELECT id, MATCH (title,body) AGAINST ('数据库' IN NATURAL LANGUAGE MODE)
   AS score FROM articles;
  +----+----------------------+
  | id| score                |
  +----+----------------------+
  |  1 | 0.12403252720832825 |
  |  2 | 0.12403252720832825 |
  |  3 |                   0 |
  |  4 | 0.12403252720832825 |
  |  5 | 0.062016263604164124|
  |  6 |                   0 |
  +----+----------------------+
  6rows in set (0.00 sec)

2.布尔模式下搜索，这个就相对于自然模式搜索来的复杂些：

• 匹配既有管理又有数据库的记录

  mysql> SELECT * FROM articles WHERE MATCH (title,body)
          ->     AGAINST ('+数据库 +管理' IN BOOLEAN MODE);
  +----+------------+--------------------------------------+
  | id| title      | body                                  |
  +----+------------+--------------------------------------+
  |  1 | 数据库管理  | 在本教程中我将向你展示如何管理数据库       |
  +----+------------+--------------------------------------+
  1 rowin set (0.00 sec)

• 匹配有数据库，但是没有管理的记录

  mysql> SELECT * FROM articles WHERE MATCH (title,body)
          ->     AGAINST ('+数据库 -管理' IN BOOLEAN MODE);
  +----+------------------+----------------------------+
  | id| title             | body                       |
  +----+------------------+----------------------------+
  |  2 | 数据库应用开发     | 学习开发数据库应用程序         |
  |  4 | 数据库与事务处理   | 系统的学习数据库的事务概论      |
  |  5 | NoSQL 精髓       | 学习了解各种非结构化数据库      |
  +----+------------------+----------------------------+
  3 rows in set (0.00 sec)

• 匹配MySQL，但是把数据库的相关性降低

  mysql> SELECT * FROM articles WHERE MATCH (title,body)
          ->     AGAINST ('>数据库 +MySQL' INBOOLEAN MODE);
  +----+---------------+-----------------+
  | id| title          | body            |
  +----+---------------+-----------------+
  |  3 | MySQL完全手册  |学习MySQL的一切    |
  +----+---------------+-----------------+
  1 rowin set (0.00 sec)

代码参考：https://blog.csdn.net/zwrj1130/article/details/55506179

https://www.cnblogs.com/zhoujinyi/p/5643408.html