mysql符串怎么加索引,mysql中添加索引语句

怎么MySql添加全文索引

使用索引是数据库性能优化的必备技能之一。在MySQL数据库中，有四种索引：聚集索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FULLTEXT INDEX)。

成都创新互联于2013年开始，是专业互联网技术服务公司，拥有项目成都网站建设、网站制作网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元新丰做网站,已为上家服务,为新丰各地企业和个人服务,联系电话:18982081108

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里，我们就不追根究底其底层实现原理了，现在我们来看看在MySQL中如何创建并使用全文索引。

在MySQL中，创建全文索引相对比较简单。例如，我们有一个文章表(article)，其中有主键ID(id)、文章标题(title)、文章内容(content)三个字段。现在我们希望能够在title和content两个列上创建全文索引，article表及全文索引的创建SQL语句如下：

--创建article表

CREATE TABLE article (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

content TEXT,

FULLTEXT (title, content) --在title和content列上创建全文索引

);

上面就是在创建表的同时建立全文索引的SQL示例。此外，如果我们想要给已经存在的表的指定字段创建全文索引，同样以article表为例，我们可以使用如下SQL语句进行创建：

--给现有的article表的title和content字段创建全文索引

--索引名称为fulltext_article

ALTER TABLE article

ADD FULLTEXT INDEX fulltext_article (title, content)

在MySQL中创建全文索引之后，现在就该了解如何使用了。众所周知，在数据库中进行模糊查询是使用LIKE关键字进行查询，例如：

SELECT * FROM article WHERE content LIKE '%查询字符串%'

那么，我们使用全文索引也是这样用的吗？当然不是，我们必须使用特有的语法才能使用全文索引进行查询。例如，我们想要在article表的title和content列中全文检索指定的查询字符串，可以如下编写SQL语句：

SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

强烈注意：MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表，如果是其他数据引擎，则全文索引不会生效。此外，MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。本站将会在后续文章中对Sphinx以及Coreseek进行介绍。

备注1：目前，使用MySQL自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。

备注2：如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

Mysql建立索引经验

在实际开发中使用数据库时，难免会遇到一些大表数据，对这些数据进行查询时，有时候SQL会查询得特别慢，这时候，有经验的老师傅会告诉你，你看一下哪几个字段查的多，加一个索引就好了。

那么，怎么合理地建立索引呢？这里分享一下我的一些经验，如有不妥之处，欢迎批评指正。

1、不要盲目建立索引 ， 先分析再创建

索引虽然能大幅度提升我们的查询性能，但也要知道，在你进行增删改时，索引树也要同样地进行维护。所以，索引不是越多越好，而是按需建立。最好是在一整块模块开发完成后，分析一下，去针对大多数的查询，建立联合索引。

2、使用联合索引尽量覆盖多的条件

这是说在一个慢sql里假如有五个where ，一个 order by ，那么我们的联合索引尽量覆盖到这五个查询条件，如果有必要，order by 也覆盖上 。

3、小基数字段不需要索引

这个意思是，如果一张表里某个字段的值只有那么几个，那么你针对这个字段建立的索引其实没什么意义，比如说，一个性别字段就两种结果，你建了索引，排序也没什么意思（也就是索引里把男女给分开了）

所以说，索引尽量选择基数大的数据去建立，能最大化地利用索引

4、长字符串可以使用前缀索引

我们建立索引的字段尽量选择字段类型较小的，比如一个varchar(20)和varchar(256)的，我们在20的上面建立的索引和在256上就有明显的差距（字符串那么长排序也不好排呀，唉）。

当然，如果一定是要对varchar(256)建立索引,我们可以选择里面的前20个字符放在索引树里（这里的20不绝对，选择能尽量分辨数据的最小字符字段设计），类似这样KEY index(name(20),age,job) ，索引只会对name的前20个字符进行搜索，但前缀索引无法适用于order by 和 group by。

5、对排序字段设计索引的优先级低

如果一个SQL里我们出现了范围查找，后边又跟着一个排序字段，那么我们优先给范围查找的字段设置索引，而不是优先排序。

6、如果出现慢SQL，可以设计一个只针对该条SQL的联合索引。

不过慢SQL的优化，需要一步步去进行分析，可以先用explain查看SQL语句的分析结果，再针对结果去做相应的改进。explain的东西我们下次再讲。

PS：在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是 执行这条SQL。

MySQL字符串索引amp;脏页刷盘

字符串加索引的方式？

对于字符串进行添加索引，我们除了对整个字符串加索引以外，还可以添加前缀索引。

什么是前缀索引？

前缀索引的好处？

使用前缀索引，定义好长度，可以做到即节省空间，又不用额外增加太多的查询成本。

前缀索引的弊端？

前缀索引会使覆盖索引失效，额外增加回表的消耗，如果前缀索引的长度选择区分度不高，会额外导致扫描行数增加。

其他给字符串加索引的方式？

什么是脏页？

MySQL在更新数据的时候会写redo log并且更新内存以后就会返回，数据文件并不会立即更新，这就是所谓的WAL机制。

当内存被更新以后，内存中的数据页就会和磁盘上的数据页存在不一致的情况，该内存也就被称为 脏页 。

内存中的数据被写入磁盘以后，内容变为一致，此时该内存页就被称为干净页。

什么叫刷脏页？

内存数据页中的内容被写入磁盘数据页中的过程称为刷脏页。

什么时候会刷脏页？

InnoDB如何控制刷脏页的频率？

首先确认InnoDB所在主机的IO能力，此时需要用到数据库的innodb_io_capacity参数，该参数推荐设置为磁盘的IOPS。磁盘的IOPS可以通过fio工具进行测试。

InndoDB刷脏页主要考虑以下两个因素：

MySQL会根据F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

脏页比例

参数innodb_max_dirty_pages_pct是脏页比例的上限，MySQL 8.0中是90%。

当前脏页比例可以通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total计算获得，具体sql计算指令如下：

连坐机制

InnoDB在刷脏页的时候，如果该脏页旁边的是页也是脏页，会同时把相邻的脏页刷掉。

该刷脏页行为由参数innodb_flush_neighbors控制:

对于机械硬盘，开启连坐会减少随机IO的消耗，但对于SSD，没必要开启该参数。