MySQL索引类型和索引种类

MySQL索引类型和索引种类

1, MySQL索引类型

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1). FULLTEXT

即为全文索引，MySQL5.6以前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2). HASH

由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3). BTREE

BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始mssql 索引，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4). RTREE

RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE，RTREE的优势在于范围查找。

2, 索引种类

1)普通索引：仅加速查询

2)唯一索引：加速查询 + 列值唯一（可以有null）

3)主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

4)组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

5)全文索引：对文本的内容进行分词，进行搜索

6)函数索引: mysql8以上的版本才支持.

3, 操作索引

3.1 创建索引

创建普通索引: create index index_name on table_name(col_name);

创建唯一索引: create unique index index_name on table_name(col_name);

创建普通组合索引:

create index index_name on table_name(col_name_1,col_name_2);

创建唯一组合索引:

create unique index index_name on table_name(col_name_1,col_name_2);

创建索引遇到字符串需指定长度

create index index_name on table_name(col_name(11));

创建函数索引

create index index_name on table_name(函数(col_name));

创建全文索引

create fulltext index index_name on student(name)

MySQL5.7.6以后创建中文分词的全文索引

create fulltext index index_name on student(name) WITH PARSER ngram;

使用全文索引: MATCH (建立全文索引的字段) AGAINST (‘要查询的字符串’)

SELECT * FROM student WHERE MATCH(name) AGAINST(‘聪’)

SELECT * FROM student WHERE MATCH(name,address) AGAINST(‘聪 广东’)

ngram全文解析器

ngram就是一段文字里面连续的n个字的序列。ngram全文解析器能够对文本进行分词，每个单词是连续的n个字的序列。例如，用ngram全文解析器对“生日快乐”进行分词:

n=1: ‘生’, ‘日’, ‘快’, ‘乐’

n=2: ‘生日’, ‘日快’, ‘快乐’

n=3: ‘生日快’, ‘日快乐’

n=4: ‘生日快乐’

MySQL 中使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为1。在默认值是2的情况下，搜索单字是得不到任何结果的。因为中文单词最少是两个汉字，推荐使用默认值2。

全局变量ngram_token_size的设置方法：

修改MySQL配置文件

[mysqld]

ngram_token_size=2

3.2 通过修改表结构创建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

3.3 创建表时直接指定索引

create table table_name (

id int not null,

col_name varchar(16) not null,index index_name (col_name)

);

3.4 删除索引

直接删除索引: drop index index_name on table_name;

修改表结构删除索引: alter table table_name drop index index_name;

3.5 其他相关命令

说明 sql语句

查看表结构 desc table_name;

查看生成表的SQL show create table table_name;

查看索引 show index from table_name;

查看执行时间 set profiling = 1;

SQL…

show profiles;

查看执行计划explain explain select…

4, 什么时候适合创建索引

4.1 数据量很大,但是查询的比较少时

4.2 总是查询很少做增删改

4.3 在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。

(某些时候的解释: %不能放前面, 因为在以通配符%和_开头作查询时，MySQL不会使用索引)

5, 用了索引也不生效的时候

%放在了前面 select * from tb1 where name like ‘%cn’;

where 子句里对有索引列使用函数 select * from tb1 where reverse(name) = ‘wupeiqi’;

类型不一致,name是字符串 select * from tb1 where name = 999;

!=(主键除外) select * from tb1 where name != ‘alex’

非主键的字符串字段用了>

查询的数据量太大,则索引不生效 select * from tb1 where name > ‘alex’

用索引字段排序,但是select里没有索引字段 select name from tb1 order by age

组合索引时不用第一个索引字段例如:(name,email) select email from tb1 where email =‘zhangfei@163.com’

or条件有未索引字段 select * from tb1 where nid = 1 or name = ‘seven’;

where 子句里对索引列上有数学运算，用不上索引 select * from tb1 where id=id+1;

如果mysql估计使用全表扫描要比使用索引快,则不使用索引 数据量少时不用索引

6, 什么情况下不推荐使用索引

6.1数据唯一性差（一个字段的取值只有几种时）的字段不要使用索引

比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描

6.2 频繁更新的字段不要使用索引

比如logincount登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。

6.3 字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件，不建议使用索引

只有在where语句出现，mysql才会去使用索引

6.4 where 子句里对索引列使用不等于（），使用索引效果一般

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!