MySQL之索引

推荐博客：
https://blog.youkuaiyun.com/wangfeijiu/article/details/113409719
https://blog.youkuaiyun.com/qq_35190492/article/details/109257302

1.简述

索引是对数据库表中一列或多列的值进行排序的一种结构。
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
同时会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要更新数据，还要更新索引文件。
建立索引会占用磁盘空间的索引文件。

2.创建标准

• 应该创建索引
  • 在经常需要搜索的列，可以加快搜索的速度。
  • 在经常使用在WHERE子句中的列，加快条件的判断速度。
  • 在经常用在连接（JOIN）的列，这些列主要是外键，可以加快连接的速度。
  • 在经常需要排序（order by）的列，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  • 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列，因为索引已经排序，其指定的范围是连续的。
• 不适合创建索引
  • 查询中很少使用到的列，加索引并不能提高查询速度，反而降低了系统的维护速度和增大了空间需求
  • 数据值大部分为null、空值或者重复值的列，查询的结果集中数据行占了表中数据行的很大比例，增加索引了，并不能明显加快检索速度。
  • 定义为text, image和bit数据类型的列，这些列的数据量要么相当大，要么取值很少。
  • 修改性能要求远远高于检索性能的列

3.索引类型

主键索引
索引列中的值必须是唯一的，不允许有空值。全表只能有一个主键索引。

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

普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

全文索引
只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。

组合索引(唯一、普通)
多列值组成一个索引，专门用于组合搜索，其效率大于多个单列的索引组合一起搜索。

4.主键索引

建表时创建

create table table_pk(   
id int primary key auto_increment,
name varchar(20)
);

删除主键索引

alter table table_pk DROP PRIMARY KEY;

建表后添加

alter table table_pk add primary key (id);

5.唯一索引

建表时创建

create table table_uq(   
id int primary key,
name varchar(20) not null,
UNIQUE uq_name (name)
);

删除唯一索引

alter table table_uq DROP index uq_name;

建表后添加

alter table table_uq add UNIQUE uq_name2 (name);

或者

create UNIQUE INDEX uq_name3 ON table_uq(name); 

6.普通索引

建表时创建

create table table_ix(   
id int primary key,
name varchar(20) not null,
age int not null,
UNIQUE uq_name (name),
INDEX ix_age (age)
);

删除普通索引

alter table table_ix DROP index ix_age;

建表后添加

alter table table_ix add INDEX ix_age2 (age);

或者

create INDEX ix_age3 ON table_ix (age); 

7.全文索引

建表时创建

create table table_ft(   
id int primary key,
content text not null,
FULLTEXT KEY ft_content(content)
);

删除全文索引

alter table table_ft DROP index ft_content;

或者

drop index ft_content on table_ft;

建表后添加

alter table table_ft add FULLTEXT INDEX ft_content2 (content);

或者

create FULLTEXT INDEX ft_content2 ON table_ft (content); 

插入数据

insert into table_ft values
(1, "a"),(2, "aa"),(3, "aaa"),(4, "aaaa"),(5, "I love you"),
(6, "you are lucky dog"),(7, "i have a car"),(8, "hello world"),
(9, "I come from China"),(10, "china is a great country"),(11, "come on baby");

使用全文索引需要用到 match 和 against 关键字。

select * from table_ft where match(content) against('a');
select * from table_ft where match(content) against('aa');
select * from table_ft where match(content) against('aaa');
select * from table_ft where match(content) against('aaaa');

全文索引是有最小搜索长度和最大搜索长度要求的，长度不在这两个变量之间的的词语，都不会被索引。
查看变量

show variables like '%ft%';

innodb的最小搜索长度和最大搜索长度分别是：3和84
修改最小搜索长度：/etc/my.cnf

[mysqld]
innodb_ft_min_token_size=1
ft_min_word_len=1

重启mysql服务

还需要修复全文索引，不然参数不会生效。

repair table test quick;

该命令对引擎MyISAM有效，Innodb无效，所以只能删除索引再重新添加

全文索引的内置修饰符：

ft_boolean_syntax：+ -><()~*:""&|
基本单位：单词

+：表示文本中必须包含该词

查询文本中包含单词 you 的数据

select * from table_ft where match(content) against('+you' in boolean mode);

-：表示文本中不能包含该词，不能单独使用，需要其他修饰符组合使用

查询文本中包含单词 you 但不能包含 love 的数据

select * from table_ft where match(content) against('+you -love' in boolean mode);

：空格(也就是默认情况)，表示可选的，包含该词的顺序较高

select * from table_ft where match(content) against('+come' in boolean mode);

查询包含单词 come 的数据，如果数据集中还包含了 baby 则排序更靠前

select * from table_ft where match(content) against('+come baby' in boolean mode);

~ :将其相关性由正转负，表示拥有该字会降低相关性

暂时没试出效果，添加了两条记录

*：通配符，这个只能接在字符串后面

select * from table_ft where match(content) against('+you*' in boolean mode);

>：提高相关性，查询的结果会排在比较靠前的位置。

<：降低相关性，查询的结果会排在比较靠后的位置。

insert into table_ft values(13,"my name is tony."),(14,"his name is lusy");

select * from table_ft where match(content) against('name>tony' in boolean mode);

select * from table_ft where match(content) against('name<lusy' in boolean mode);

单独使用 <> 都是比没有的要靠前，< 降低相关性是和 > 配合使用时体现的

select * from table_ft where match(content) against('name>lusy<tony>baby' in boolean mode);

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

( )：可以通过括号来使用字条件

select * from table_ft where match(content) against('+(name | come)' in boolean mode);

select * from table_ft where match(content) against('+(come & you)' in boolean mode);