mysql 不同索引的区别和适用情况总结

MySQL目前主要有以下几种索引类型:

 

  • 普通索引 INDEX 允许出现相同的索引内容 (normal)

 

  • 唯一索引 UNIQUE 不可以出现相同的值,可以有NULL值
  • 主键索引 PROMARY KEY 不允许出现相同的值(唯一性,且只能有一个)
  • 组合索引 实质上是将多个字段建到一个索引里,列值的组合必须唯一

 

  • 全文索引 FULLTEXT INDEX 可以针对值中的某个单词,但效率低(不建议,可利用添加关键词关联列来实现)

 

//建表时建立
CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]
注意:复合索引使用时要与索引名和关联的列一一对应,如果想单独对某个列进行索引操作,需要新建个以独立的索引

说明

1.unique|fulltext为可选参数,分别表示唯一索引、全文索引
2.index和key为同义词,两者作用相同,用来指定创建索引
3.col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择
4.index_name指定索引的名称,为可选参数,如果不指定,默认col_name为索引值
5.length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储

索引的创建、修改、删除

1、使用ALTER TABLE 语句创建索引

应用于表创建完成之后添加索引

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)
//普通索引
alter table table_name add index index_name (column_list) ;
//唯一索引
alter table table_name add unique (column_list) ;
//主键索引
alter table table_name add primary key (column_list) ;

说明:
ALTER TABLE可用于创建普通索引、UNIQUE索引和PRIMARY KEY索引3种索引格式,
table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。
索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以同时创建多个索引。

2、使用CREATE INDEX 对表增加索引

CREATE INDEX可用于对表增加普通索引或UNIQUE索引,可用于建表时创建索引。

CREATE INDEX index_name ON table_name(username(length)); 

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length。
//create只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引

3、删除索引

 删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。DROP INDEX可以在ALTER TABLE内部作为一条语句处理。

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

在前面的两条语句中,都删除了table_name中的索引index_name。而在最后一条语句中,只在删除PRIMARY KEY索引中使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。

如果从表中删除某列,则索引会受影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。

 

4、修改索引

mysql 是没有内置修改索引操作的,需要先执行删除操作在重新建立一个索引

 

5、查看索引

-如果查看索引前,没有使用use db_name等命令指定具体的数据库,则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果查看索引前,没有使用use db_name等命令指定具体的数据库,则必须加上db_name.前缀
SHOW INDEX FROM [db_name.]table_name

--如果查看索引前,使用了use db_name等命令指定具体的数据库
SHOW INDEX FROM table_name

 

组合索引与前缀索引

组合索引和前缀索引是对建立索引技巧的一种称呼,并不是索引的类型。

create table dm_user
(
   ID                   int not null auto_increment comment '主键',
   LOGIN_NAME           varchar(30) not null comment '登录名',
   PASSWORD             varchar(30) not null comment '密码',
   CITY                 varchar(30) not null comment '城市',
   AGE                  int not null comment '年龄',
   SEX                  int not null comment '性别(0:女 1:男)',
   primary key (ID)
);

comment  表示添加注释(注意中文乱码的问题,需要设置表的编码格式为utf-8)

建立组合索引,即将LOGIN_NAME,CITY建到一个索引里

ALTER TABLE md_user ADD INDEX name_city (LOGIN_NAME(16),CITY);

建表时,LOGIN_NAME长度为30,这里用16,是因为一般情况下名字的长度不会超过16,这样会加快索引查询速度,还会减少索引文件的大小,提高INSERT,UPDATE的更新速度。

 

值得一提的是,mysql组合索引是依据“最左前缀”的形式产生索引结果的。简单的理解就是只从最左边的开始组合,并不是只要包含这几列的查询都会用到该组合索引。也就是说index_name(column1(length),column2,column3...)从左到右进行索引,如果没有左前索引,mysql不会执行索引查询

ALTER TABLE table_name ADD INDEX index_name (column1,column2,column3,column4); 

//相当于分别创建了一下几种组合索引
column1,column2,column3,column4
column1,column2,column3
column1,column2
column1

 

如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引,前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。

SELECT COUNT(DISTINCT(LEFT(`column_name`,10)))/COUNT(*) FROM table_name; -- 这个值大于0.31就可以创建前缀索引,Distinct去重复

ALTER TABLE `table_name` ADD INDEX `uname`(column_name(10)); -- 增加前缀索引SQL,将列名的索引建立在10,这样可以减少索引文件大小,加快索引查询速度

Mysql字符串截取函数:left()、right()、substring()、substring_index()

 

索引类型

1、FULLTEXT

全文索引,目前只有MyISAM引擎支持,mysql 5.6之后InnoDB引擎也支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。

注意:在数据量较大时候,先将数据放入一个没有全文索引的表中,然后再用CREATE INDEX创建FULLTEXT索引,要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。

全文索引的出现是为了解决WHERE name LIKE “%keyword%"这类针对文本的模糊查询效率较低的问题。

在数据量较大时是查询是极其的耗时的,如果没有异步IO处理,进程将被挟持,很浪费时间。想了解异步IO的,自行谷歌。

全文索引的使用:

创建ALTER TABLE table ADD INDEX `FULLINDEX` USING FULLTEXT(`cname1`[,cname2…]);

使用SELECT * FROM table WHERE MATCH(cname1[,cname2…]) AGAINST ('keyword' MODE );

其中, MODE为搜寻方式(IN BOOLEAN MODE ,IN NATURAL LANGUAGE MODE ,IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION)。

关于这三种搜寻方式,简单地说分为:

  • 布尔模式,允许word里含一些特殊字符用于标记一些具体的要求,如+表示一定要有,-表示一定没有,*表示通用匹配符,类似正则;
  • 自然语言模式,就是简单的单词匹配;
  • 含表达式的自然语言模式,就是先用自然语言模式处理,对返回的结果,再进行表达式匹配。

FULLTEXT索引也是按照分词原理建立索引的。西文中,大部分为字母文字,分词可以很方便的按照空格进行分割。

但很中文不能按照这种方式进行分词。那又怎么办呢?利用Mysql的中文分词插件Mysqlcft,就可以对中文进行分词,Mysqlcft详情。

 

2、HASH

hash就是一种(key=>value)形式的键值对,如数学中的函数映射,允许多个key对应相同的value,但不允许一个key对应多个value。正是由于这个特性,hash很适合做索引,为某一列或几列建立hash索引,就会利用这一列或几列的值通过一定的算法计算出一个hash值,对应一行或几行数据(这里在概念上和函数映射有区别,不要混淆)。在java语言中,每个类都有自己的hashcode()方法,没有显示定义的都继承自object类,该方法使得每一个对象都是唯一的,在进行对象间equal比较,和序列化传输中起到了很重要的作用。

hash的生成方法有很多种,可以保证hash码的唯一性。如在MongoDB中,每一个document都有系统为其生成的唯一的objectID(包含时间戳,主机散列值,进程PID,和自增ID)也是一种hash的表现。

由于hash索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。

那为什么还需要其他的树形索引呢?

这里简单说下树形索引Btree 与 Hash 索引的区别:

(1)Hash 索引仅能满足"=","IN"和"<=>"查询,不能使用范围查询
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。

(2)Hash 索引无法被用来避免数据的排序操作
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;

(3)Hash 索引不能利用部分索引键查询。
对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。

(4)Hash 索引在任何时候都不能避免表扫描
Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash 索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。

(5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。

 

HASH索引的过程,当我们为某一列或某几列建立hash索引时(目前就只有MEMORY引擎显式地支持这种索引),会在硬盘上生成类似如下的文件:

hash值 存储地址
1db54bc745a1 77#45b5
4bca452157d4 76#4556,77#45cc…

hash值即为通过特定算法由指定列数据计算出来,磁盘地址即为所在数据行存储在硬盘上的地址(也有可能是其他存储地址,其实MEMORY会将hash表导入内存)。

当进行WHERE num= 18 查询时,会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。

所以,每次查询时都要遍历hash表,直到找到对应的hash值,如第(4)区别中描述的,数据量大了之后,hash表也会变得庞大起来,性能下降,遍历耗时增加,如第(5)区别。

 

3、BTREE

BTREE树形索引就是一种将索引值按一定的算法,存入一个树形的数据结构中,学过数据结构的对于二叉树这种数据结构应该不陌生吧。如二叉树一样,每次查询都是从树的入口root开始,依次遍历node,获取leaf。

BTREE在MyISAM里的形式和Innodb稍有不同(虽然两者都使用B+Tree作为索引结构,但还是有些区别的,数据库优化-mysql中INNODB和MYIASM引擎的区别

在 Innodb里,有两种形态:一是primary key形态,其leaf node里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据。二是secondary index,其leaf node和普通的BTREE差不多,只是还存放了指向主键的信息.

而在MyISAM里,主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里,leaf node里存放的不是主键的信息,而是指向数据文件里的对应数据行的地址信息.

 

4、RTREE

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

相对于BTREE,RTREE的优势在于范围查找.

 

索引的缺点:

  • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete。因为更新表时,不仅要保存数据,还要保存一下索引文件。
  • 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。
    索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

 

注意事项和设计技巧:

1.索引不会包含有null值的列

只要列中包含有null值都将不会被包含在索引中,组合索引中只要有一列含有null值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

2.使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个char(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

4.like语句操作
一般情况下不推荐使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5.不要在列上进行运算

这将导致索引失效而进行全表扫描

6.不使用not in和<>判断操作

NOT IN 、<>、!=不使用索引,但<,<=,=,>,>=,BETWEEN,IN是可以用到索引的。

not in和<> 这将导致索引失效,可以考虑使用exists 或 not exists 来代替in 和 not in (注意 in操作 是可以利用索引的)。

mysql中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高,这种说法其实是不准确的。这个是要区分环境的。

简而言之就是,内查询循环次数少(即内查询表数据少于外查询表或相差不大的情况)使用exists  效率高;反之,外查询循环次数少(即外查询表远少于内查询表数据)使用in 效率高。

not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快

使用between and  区间 来代替<>判断操作。

7、EXPLAIN可以帮助开发人员分析SQL问题.

explain显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。使用方法,在select语句前加上Explain就可以。

8、索引要建立在值比较唯一的字段上

9、索引要建立在经常进行select操作的字段上。

这是因为,如果这些列很少用到,那么有无索引并不能明显改变查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

10、对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大,要么取值很少。

11、在where和join中出现的列需要建立索引

12、如果where字句的查询条件里使用了函数(如:where YEAR(column)=…),mysql将无法使用索引

13、在join操作中(需要从多个数据表提取数据时),mysql只有在主键和外键的数据类型相同时才能使用索引,否则及时建立了索引也不会使用。

14、当where条件中存在 字符串与数字比较时(数据类型相同)不使用索引,用到OR 关键词时也不会使用索引

 

 

各种索引的使用情况

(1)对于BTREE这种Mysql默认的索引类型,具有普遍的适用性

(2)由于FULLTEXT对中文支持不是很好,在没有插件的情况下,最好不要使用。在小的博客应用,在数据采集时,为其建立关键字列表,通过关键字索引也可以起到类型的作用。

(3)对于一些搜索引擎级别的应用来说,FULLTEXT同样不是一个好的处理方法,Mysql的全文索引建立的文件还是比较大的,而且效率不是很高。Apache的Lucene或许是你的选择。

(4)正是因为hash表在处理较小数据量时具有无可比拟的素的优势,所以hash索引很适合做缓存(内存数据库)。如mysql数据库的内存版本Memsql,使用量很广泛的缓存工具Mencached,NoSql数据库redis等,都使用了hash索引这种形式。Mysql的MEMORY引擎也是可以满足这种需求的。

 

在实际操作过程中,应该选取表中哪些字段作为索引?

为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索引和创建什么类型的索引,有7大原则:

1.选择唯一性索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.限制索引的数目
5.尽量使用数据量少的索引
6.尽量使用前缀来索引
7.删除不再使用或者很少使用的索引

 

转载的https://www.cnblogs.com/zwesy/p/9347684.html

版权声明:
作者:黯然gg
链接:https://3zi.cn/archives/659
来源:阿龙爱吃肉
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
< <上一篇
下一篇>>
文章目录
关闭
目 录