数据表优化

性能优化-数据表优化

关键字：index(普通) unique（唯一） primary key（主键） fulltext（全文）

 

WITH PARSER ngram

 

索引优化(唯一索引、主键索引、普通索引、全文索引简介、联合索引、空间索引)

1) 什么是索引？为什么要建立索引？

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

　例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。

　如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。

　如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。其中MySQL中的索引的存储类型有两种：BTREE、HASH。 也就是用树或者Hash值来存储该字段，要知道其中详细是如何查找的，就需要会算法的知识了。

2) 、MySQL中索引的优点和缺点和使用原则

　优点：

a）可以保证数据库表中每一行的数据的唯一性【主键、唯一】

b）可以大大加快数据的索引速度

c）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间

　缺点：

　　　　　　　1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

　　　　　　　2、索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

　　　　　　　3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

使用原则：

　　　　　　 并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。

　　　　　　　1、对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，

　　　　　　　2、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

　　　　　　　3、在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

3) 、索引的分类　　

索引四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引(普通索引，唯一索引，主键索引)、全文索引、空间索引、

1、单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

　　　i.普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

创建分三种：创建表的时候创建 、直接添加 、修改表结构

创建表的时候创建

create  table  news(

	id  int  primary  key  auto_increment,

	title  varchar(100),

	content text,

	index(title)

)engine=InnoDB 

解释：index是普通索引的意思 索引类型（字段）

直接创建索引

create index title_index on news(title)

语法  create  索引类型  索引名称   on   数据表（字段（长度））

说明： create 创建 index【索引类型】 title_index 【索引的名称)】 on news[数据表]（字段）

修改表结构

alter  table  news add index title_index(title)

语法：alter  table  数据表名 索引类型  索引名称（字段）

删除索引

drop index title  on  news

解释： drop 删除 index（索引的类型） title（索引的名称） on 数据表名

ii、唯一索引：索引列中的值必须是唯一的，但是允许为空值

创建表的时候创建：

create  table  news01(

    id  int  primary  key  auto_increment,

    title varchar(100),

    content text,

    unique(title)

    )engine=InnoDB;

解释unique 唯一 （字段）

直接添加：

create unique index  title_index on  news(title)

语法： create  unique index  索引名称  on  数据表（字段）

修改表结构的时候创建：

alter  table  news01  add  unique(title);

语法：alter  table 数据表名  add  unique(字段)

删除索引

  drop  index   title_index  on  news

 

　iii、主键索引：是一种特殊的唯一索引，不允许有空值。

创建表的时候创建

create  table  news01(

    id  int  primary  key  auto_increment,

    title varchar(100),

    content text,

    )engine=InnoDB;

说明 primary  key  主键

2、组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

普通索引

创建表的时候创建

create  table news02(

    id  int  primary  key  auto_increment,

    title varchar(100),

    content varchar(100),

    author varchar(30),

    created_at  TIMESTAMP ,

    index(title,content,author)

)engine=INNODB

直接添加

create  index  title_content_author_index on  news02(title,content,author);

简而言之，就是将之前一个字段换成多个字段

唯一索引

 

联合主键

create  table  news03(

    id  int,

    title varchar(100),

    author  varchar(30),

    primary  key (id,title,author))engine=INNODB

保证多个字段在一起是唯一的

示例

解释：其中id ，title，author是联合主键。所以 第四条数据 都是1 的是无法插入的，会报一个

解释最左前缀

组合索引就是遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀，举几个例子就明白了，例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询

首先

create  table  stu(

    stuid  int  primary  key  auto_increment,

    stuname  varchar(30),

    stusex   varchar(10),

    stuage   int(2),

    stueamil  varchar(30),

    stutel    varchar(11),

index(stuname,stusex,stuage))engine=InnoDB;

其次插入数据

 

3、全文索引

创建索引

create table article(

    id int  primary  key  auto_increment,

    title VARCHAR(100),

    content text,

FULLTEXT(title))engine=Myisam;

Myisam 因为MySQL的版本低于5.6 mysql数据库版本低于5.6 只有myisam支持全文索引

之前是模糊查询

全文索引搜索的语句

select  * from  article  where  match(title) against('apple')

select  字段  from  表名  where  match(要搜索的字段) against('要搜索的值') match  和 against 都是关键字

　　全文索引,通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题. 例如: 有title字段,需要查询所有包含 "政府"的记录. 需要 like "%政府%"方式查询,查询速度慢,当查询包含"政府" OR "中国"的需要是,sql难以简单满足.全文索引就可以实现这个功能.

倒排索引（英语：Inverted index），也常被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。

　

SELECT * FROM 表名 WHERE match(字段) against(‘搜索的字符串’);

注意

在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎.在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词.

在使用全文搜索时，需要借助MATCH函数，并且其全文搜索的限制比较多，比如只能通过MyISAM引擎，比如只能在CHAR,VARCHAR,TEXT上设置全文索引。比如搜索的关键字默认至少要4个字符，比如搜索的关键字太短就会被忽略掉。

◆ 任何过于短的词都会被忽略。 全文搜索所能找到的词的默认最小长度为 4个字符。

　　◆ 停止字中的词会被忽略。禁用词就是一个像“the” 或“some” 这样过于平常而被认为是不具语义的词。存在一个内置的停止字， 但它可以通过用户自定义列表被改写。

　　词库和询问中每一个正确的单词根据其在词库和询问中的重要性而被衡量。　 通过这种方式，一个出现在许多文件中的单词具有较低的重要性(而且甚至很多单词的重要性为零),原因是在这个特别词库中其语义价值较低。反之，假如这个单词比较少见，那么它会得到一个较高的重要性。然后单词的重要性被组合，从而用来计算该行的相关性。

使用全文索引

新建数据表

create  table article(

    id   int  PRIMARY  key  auto_increment,

    title varchar(200),

    content text,

    fulltext text(title,content) WITH PARSER ngram

)engine=InnoDB charset=utf8

WITH PARSER ngram 使用内置的ngram全文解析器,用来支持亚洲语种的分词.

MySQL的全文索引查询有多种模式

自然语言搜索

普通【自然语言】的判断是否包含 IN NATURAL LANGUAGE MODE

select  * from article where  match(title,content) against('中国' in NATURAL language MODE)

select  * from article where  match(title,content) against('中国')

提示：in NATURAL language MODE 加不加都可以默认的是自然语言搜索

布尔检索 BOOLEAN MODE

这个模式可以通过一些操作符,来指定搜索词在结果中的包含情况.比如 + 表示必须包含 , -表示必须不包含,默认为误操作符,代表可以出现可以不出现,但是出现时在查询结果集中的排名较高一些.也就是该结果和搜索词的相关性高一些.

 

具体包含的所有操作符可以通过MySQL查询来查看:

select  * from article where  match(title,content) against('+中国 -电话' in  boolean mode)

 

+ 一定要有(不含有该关键词的数据条均被忽略)。

- 不可以有(排除指定关键词，含有该关键词的均被忽略)。

> 提高该条匹配数据的权重值。

< 降低该条匹配数据的权重值。

~ 将其相关性由正转负，表示拥有该字会降低相关性(但不像 - 将之排除)，只是排在较后面权重值降低。

* 万用字，不像其他语法放在前面，这个要接在字符串后面。

" " 用双引号将一段句子包起来表示要完全相符，不可拆字。

+ 和-的案例

这是数据表中的所有数据

使用布尔检索

说明 + 必须有 - 不能有

select  * from article where  match(title,content) against('同巴西总统博索纳罗通' in  boolean mode)

效果讲解

 

使用空格 多个词都能搜索 ，多次搜索的时候，使用空格隔开

检索数据表中的title和content中包含 电话和中华社的内容

4、空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种GEOMETRY、POINT、LINESTRING、POLYGON。

在创建空间索引时，使用SPATIAL关键字。

要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

　　

Explain 的type列

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

（五）、总结

　　　　MySQL的索引到这里差不多就讲完了，总结一下我们到目前为止应该知道哪些东西

　　　　　1、索引是干嘛的？为什么要有索引？

　　　　　2、索引的分类

单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

　　　　　3、索引的操作

　　　　　　　　　　 给表中创建索引，添加索引，删除索引，删除索引　　

 

普通索引

创建表的时候创建索引

表已经有了，直接创建索引

修改表结构的时候创建索引

删除索引

唯一索引

创建数据表的时候创建索引 唯一索引

如果字段创建了唯一索引，则该字段的数据必须唯一，否则报错，如下

直接创建索引

修改表结构的添加索引

删除唯一索引

主键索引

一般是在建表的时候同时创建主键索引

组合索引

联合主键

当数据重复的时候会报错

唯一索引

组合索引是将之前的一个字段改为多个字段，多个字段之间用逗号

创建全文索引

正排索引：

正排索引是指文档ID为key，表中记录每个关键词出现的次数，查找时扫描表中的每个文档中字的信息，直到找到所有包含查询关键字的文档。

假设网页A的局部文档ID是 TA， 网页B的局部文档ID是 TB。那么对TA进行正排索引建立的表结构是下面这样的：

 

 

从上面的介绍可以看出，正排是以 docid 作为索引的，但是在搜索的时候我们基本上都是用关键词来搜索。所以，试想一下，我们搜一个关键字（Tom），当100个网页的10个网页含有Tom这个关键字。但是由于是正排是doc id 作为索引的，所以我们不得不把100个网页都扫描一遍，然后找出其中含有Tom的10个网页。然后再进行rank，sort等。效率就比较低了。尤其当现在网络上的网页数已经远远超过亿这个数量后，这种方式现在并不适合作为搜索的依赖。

不过与之相比的是，正排这种模式容易维护。由于是采用doc 作为key来存储的，所以新增网页的时候，只要在末尾新增一个key，然后把词、词出现的频率和位置信息分析完成后就可以使用了。

所有正排的优点是：易维护；缺点是搜索的耗时太长；

 

倒排索引：

由于正排的耗时太长缺点，倒排就正好相反，是以word作为关键索引。表中关键字所对应的记录表项记录了出现这个字或词的所有文档，一个表项就是一个字表段，它记录该文档的ID和字符在该文档中出现的位置情况。

倒排包含两部分：

1、由不同的索引词（index term）组成的索引表，称为“词典”（lexicon）。其中包含了各种词汇，以及这些词汇的统计信息（如出现频率nDocs），这些统计信息可以直接用于各种排名算法。

2、由每个索引词出现过的文档集合，以及命中位置等信息构成。也称为“记录表”。就是正排索引产生的那张表。当然这部分可以没有。具体看自己的业务需求了。

 

下面是一个简单的倒排索引构建，只包含第一部分的。

 

 

倒排的优缺点和正排的优缺点整好相反。倒排在构建索引的时候较为耗时且维护成本较高，但是搜索耗时短。