索引的类型分类、用法、区别
引言:
DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就会先去索引列表中一次定位到特定值的行数,大大减少遍历匹配的行数,所以能明显增加查询的速度。
MySQL官方对于索引的定义为:索引是帮助MySQL高效获取数据的数据结构。即可以理解为:索引是数据结构
mysql的索引我们分为三大类来讲单列索引(普通索引,唯一索引,主键索引)、组合索引、全文索引。
一、单列索引
一个索引只包含单个列,但是一个表中可以有多个单列索引
1、普通索引:Mysql中基本索引类型,没有什么限制,允许定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。
2、唯一索引:索引列中的值必须是唯一的,可以为空值
3、主键索引:是一种特殊的唯一索引,不允许为空值
语法:
直接创建索引:
create index index_name on table (column_name);创建普通索引。
create unique index index_name on table (column_name);创建唯一索引
主键索引需要在创建表或者修改表结构的时候创建
删除索引:
drop index index_name on table_name; 删除普通索引(删除唯一索引)
二、组合索引(复合索引)
在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时遵循最左前缀集合
创建索引:
create index index_name on table_name(column_1,column_2,column_3.....)
为便于理解,可以将上述索引理解为(column_1),(column_1,column_2),(column_1,column_2,column_3),必须遵从从左开始,如果出现column_1,column_3 这种断点情况,则column_1生效。
三、全文索引
支持:
- MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
- MySQL 5.6 及以后的版本,MyISAM 和 InnoDB
存储引擎均支持全文索引; - 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
创建:
create fulltext index index_name on table_name (column_1,column_2…);
删除:
同其他索引
使用:
全文索引有自己的语法格式,使用 match 和 against 关键字,比如
select * from fulltext_test where match(column_1,column_2) against('xxx xxx');
注意: match() 函数中指定的列必须和全文索引中指定的列完全相同,否则就会报错,无法使用全文索引,这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引,请单独为该列创建全文索引
搜索关键词最小长度,MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值,如果低于或高于默认值,则搜索为空
// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;
// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;
配置最小搜索长度
全文索引的相关参数都无法进行动态修改,必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1,首先打开 MySQL 的配置文件 /etc/my.cnf,在 [mysqld] 的下面追加以下内容
[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1
然后重启 MySQL 服务器,并修复全文索引。注意,修改完参数以后,一定要修复下索引,不然参数不会生效。
repair table test quick;