-
2021-01-19 08:26:59
一、索引的类型
mysql索引的四种类型:主键索引、唯一索引、普通索引和全文索引。通过给字段添加索引可以提高数据的读取速度,提高项目的并发能力和抗压能力。索引优化时mysql中的一种优化方式。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。
主键索引:
主键是一种唯一性索引,但它必须指定为PRIMARY KEY,每个表只能有一个主键。
alter table table_name add primary key (`字段名`);
唯一索引:
索引列的所有值都只能出现一次,即必须唯一,值可以为空。
alter table table_name add primary key (`字段名`);
普通索引 :
基本的索引类型,值可以为空,没有唯一性的限制。
alter table table_name add index (`字段名`);
全文索引:
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引,InnoDB在mysql5.6之后支持了全文索引。
全文索引不支持中文需要借sphinx(coreseek)或迅搜技术处理中文。
alter table 表名 add FULLTEXT(`字段名`);
二、查看表的所有索引和删除
#查看:
show indexes from `表名`;
#或
show keys from `表名`;
#删除
alter table `表名` drop index 索引名;
三、索引的机制
1.为什么我们添加完索引后查询速度为变快?
传统的查询方法,是按照表的顺序遍历的,不论查询几条数据,mysql需要将表的数据从头到尾遍历一遍
在我们添加完索引之后,mysql一般通过BTREE算法生成一个索引文件,在查询数据库时,找到索引文件进行遍历(折半查找大幅查询效率),找到相应的键从而获取数据
2.索引的代价
2.1创建索引是为产生索引文件的,占用磁盘空间
2.2索引文件是一个二叉树类型的文件,可想而知我们的dml操作同样也会对索引文件进行修改,所以性能会下降
3.在哪些column上使用索引?
3.1较频繁的作为查询条件字段应该创建索引
3.2唯一性太差的字段不适合创建索引,尽管频繁作为查询条件,例如gender性别字段
3.3更新非常频繁的字段不适合作为索引
3.4不会出现在where子句中的字段不该创建索引
总结: 满足以下条件的字段,才应该创建索引.
a: 肯定在where条经常使用 b: 该字段的内容不是唯一的几个值 c: 字段内容不是频繁变化。
四、查看索引的使用情况
show status like '%Handler_read%' ;
handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。
handler_read_rnd_next:这个值越高,说明查询低效。
更多相关内容 -
mysql系列:全网最全索引类型汇总
2020-09-26 01:45:06今天和大家一起总结mysql中有哪些索引类型。 一、mysql中有哪些索引类型? 聚簇索引 (Clustered Index) 非聚簇索引 主键索引(PRIMARY KEY) 辅助索引(Secondary Indexes) HASH索引 BTREE索引 T-TREE索引 R-Tree索引 ...前言
除了常见的普通索引,唯一索引,组合索引,大家还能说一下mysql中有哪些其他类型的索引吗?
今天和大家一起总结mysql中有哪些索引类型。
一、mysql中有哪些索引类型?
- 聚簇索引 (Clustered Index)
- 非聚簇索引
- 主键索引(PRIMARY KEY)
- 辅助索引(Secondary Indexes)
- HASH索引
- BTREE索引
- T-TREE索引
- R-Tree索引
- 自适应hash索引(Adaptive Hash Index)
- 唯一索引 (UNIQUE Indexs)
- 普通索引 (Normal index)
- 全文索引 (FULLTEXT Indexes)
- 空间索引 (Spatial indexes)
- 组合索引 (Multiple-Column Indexes)
- 覆盖索引
- 倒序索引 (Descending Indexes)
- 不可见索引(Invisible Indexes)
吓了一跳吧,你说出了几个索引类型。
二、索引类型说明
1、按索引是否包含记录数据分类
聚簇索引:
将数据存储与索引放到了一块,找到索引也就找到了数据,不需要根据主键或行号去进行回表查询。非聚簇索引:
非聚簇索引就是指B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。主要用在MyISAM存储引擎中.
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。2、按索引是否建立在主键上分类
主键索引:
在MySQL的主键上创建的索引就是主键索引,主键索引会自动创建,一个表只能有一个主键索引,同时主键索引也是唯一索引。辅助索引:
在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引,innodb中辅助索引叶子节点存储的不再是行的物理位置,而是键值和主键 ID。
注意:
InnoDB引擎支持聚簇索引,MyISAM引擎不支持聚簇索引。
所以,主键索引不一定是聚簇索引。3、按索引是底层的数据结构分类
HASH索引
Hash 索引的特性:
1、等值查询较快,但是不稳定
2、不能使用范围查询
3、不能避免数据排序
4、不能利用组合索引的部分字段进行查询
5、不支持模糊查询
6、避免不了回表查询B+Tree索引
B+Tree索引的特性:
1.所有关键字都出现在叶子结点的链表中(稠密索引),且链表中的关键字恰好是有序的;
2.内节点不存储data,只存储key;叶子节点不存储指针。
3.不可能在非叶子结点命中;
4.非叶子结点相当于是叶子结点的索引(稀疏索引),叶子结点相当于是存储(关键字)数据的数据层;
5、B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能
6.更适合文件索引系统;mysql各个存储引擎支持的索引类型:
4、按索引的常规功能分类
唯一索引 (UNIQUE Indexs)
要求索引列的所有值都只能出现一次,即必须唯一。普通索引 (Normal index)
仅用来提高查询速度,没有其他特性。全文索引 (FULLTEXT Indexes)
MySQL可以通过建立全文索引,利用查询关键字和查询列内容之间的相关度进行检索,可以利用全文索引来提高匹配的速度。比如实现全匹配模糊查询。
但是实际场景测试mysql的全文索引性能非常不稳定,不建议生产环境使用。需要使用全文检索的地方,还是推荐使用Elasticsearch空间索引 (Spatial indexes)
空间索引使用R树,R树是用于索引多维数据的专用数据结构。这4类索引也是我们可以使用Navicat等客户端工具,能够主动创建的4类索引。
5、按索引的是否包含多个字段分类
多字段索引 (Multiple-Column Indexes)
也叫组合索引(composite indexes),即索引中包含多个表字段。样例语句:
CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, 1581Verifying Index Usage first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) );
6、其他特性索引
T-TREE索引
BTREE索引由NDB存储引擎实现为T树索引,算是BTREE索引在NDB存储引擎中的升级实现。R-Tree索引
从MySQL 8.0.12开始,R-Tree索引开始在SPATIAL索引中使用。
MySQL对空间列上的SPATIAL索引使用R-Trees进行二次分割。自适应hash索引(Adaptive Hash Index)
是InnoDB存储引擎中的内存结构的组成部分。
InnoDB存储引擎会监控对表上各索引页的查询,如果观察到建立hash索引可以提高查询速度,则自动建立hash索引。这就是自适应哈希索引(Adaptive Hash Index,AHI)
AHI是通过缓存池的B+树页构造而来,因此建立的速度很快,而且不需要对整张表构建hash索引。
InnoDB存储引擎会自动根据访问的频率和模式来自动的为某些热点也建立hash索引。覆盖索引
如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。
这个概念非常重要,灵活运用对SQL优化非常有帮助。
比如这2个查询语句,同样是全匹配模糊查询,第二个sql却能使用索引。原因是就是利用了覆盖索引的概念,减少了回表查询。降序索引 (Descending Indexes)
从MySQL 8.0开始支持降序索引了。其实,从语法上,MySQL 4就支持了,但正如官方文档所言,“they are parsed but ignored”,实际创建的还是升序索引。
MySQL支持降序索引:不再忽略索引定义中的DESC,而是导致键值的降序存储。降序索引的意义:
如果一个查询,需要对多个列进行排序,且顺序要求不一致。在这种场景下,要想避免数据库额外的排序-“filesort”,只能使用降序索引。CREATE TABLE t ( c1 INT, c2 INT, INDEX idx1 (c1 ASC, c2 ASC), INDEX idx2 (c1 ASC, c2 DESC), INDEX idx3 (c1 DESC, c2 ASC), INDEX idx4 (c1 DESC, c2 DESC) );
ORDER BY c1 ASC, c2 ASC -- optimizer can use idx1 ORDER BY c1 DESC, c2 DESC -- optimizer can use idx4 ORDER BY c1 ASC, c2 DESC -- optimizer can use idx2 ORDER BY c1 DESC, c2 ASC -- optimizer can use idx3
不可见索引
MySQL支持不可见索引; 也就是说,优化器未使用的索引。 该功能适用于除主键(显式或隐式)以外的索引。
不可见的索引可以测试删除索引对查询性能的影响,而无需
进行破坏性的更改,如果最终需要索引,则必须撤消该更改。 下降和
对于大型表,重新添加索引可能会非常昂贵,而使其不可见和可见则是快速的就地操作。简单来说,就是可以使索引不起作用,对查询优化器不可见。一般在调试索引对查询性能影响的时候使用。相当于索引的一个开关。
创建不可见索引的三种方式:
CREATE TABLE t1 ( i INT, j INT, k INT, INDEX i_idx (i) INVISIBLE ) ENGINE = InnoDB; CREATE INDEX j_idx ON t1 (j) INVISIBLE; ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
修改一个索引的可见性:
ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE; ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;
总结
本文比较全面的介绍了mysql中索引类型。如果你还知道什么其他类型的索引,欢迎留言交流。
更多精彩,关注我吧。
-
MySql索引类型
2018-07-04 22:15:19Mysql支持哪几种索引索引是在MySql的存储引擎层中实现的,而不是在服务器层从数据结构角度1、B+树索引(O(log(n))):关于B+树索引,可以参考 MySQL索引背后的数据结构及算法原理BTREE在MyISAM里的形式和Innodb稍有...Mysql支持哪几种索引
索引是在MySql的存储引擎层中实现的,而不是在服务器层
从数据结构角度
1、B+树索引(O(log(n))):关于B+树索引,可以参考 MySQL索引背后的数据结构及算法原理
BTREE在MyISAM里的形式和Innodb稍有不同
在 Innodb里,有两种形态:一是primary key形态,其leaf node里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据。二是secondary index,其leaf node和普通的BTREE差不多,只是还存放了指向主键的信息.
而在MyISAM里,主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里,leaf node里存放的不是主键的信息,而是指向数据文件里的对应数据行的信息.
MyISAM的B+Tree的叶子节点上的data,并不是数据本身,而是数据存放的地址。主索引和辅助索引没啥区别,只是主索引中的key一定得是唯一的。这里的索引都是非聚簇索引。
MyISAM还采用压缩机制存储索引,比如,第一个索引为“her”,第二个索引为“here”,那么第二个索引会被存储为“3,e”,这样的缺点是同一个节点中的索引只能采用顺序查找。
InnoDB的数据文件本身就是索引文件,B+Tree的叶子节点上的data就是数据本身,key为主键,这是聚簇索引。非聚簇索引,叶子节点上的data是主键(所以聚簇索引的key,不能过长)。为什么存放的主键,而不是记录所在地址呢,理由相当简单,因为记录所在地址并不能保证一定不会变,但主键可以保证。myisam引擎的数据在物理磁盘上是按照顺序存储的,而innodb引擎的表数据是随机分布的;
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:这里设表一共有三列,假设我们以Col1为主键,则上图是一个MyISAM表的主索引(Primary key)示意。可以看出 MyISAM的索引文件仅仅保存数据记录的地址 。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示:此图中叶子结点的34的地址应为0x07
同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。MyISAM的索引方式也叫做“ 非聚集 ”的,之所以这么称呼是为了与InnoDB的聚集索引区分。InnoDB索引实现虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。上图是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做 聚集索引 。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL 系统 会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
第二个与MyISAM索引的不同是InnoDB的 辅助索引data域存储相应记录主键的值而不是地址 。换句话说,InnoDB的所有辅助索引都引用主键作为data域。例如,下图为定义在Col3上的一个辅助索引:这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。聚簇索引的优缺点:
- 可以把相关数据保存在一起。例如实现电子邮箱时,可以根据用户ID来聚集数据,这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引,则每封邮件都可能导致一次磁盘I/O.
- 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中,因此从聚簇索引中获取数据通常比非聚簇索引中查找要快。
- 使用覆盖索引扫描的查询可以直接使用页节点的主键值。
同时聚簇索引还有一些缺点:- 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。(这种情况可以用主键auto_increment自增列解决)
- 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置。
- 二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列。
- 二级索引的访问需要两次索引查找,而不是一次。
最后一点可能让人有些疑惑,为什么二级索引需要两次索引查找?答案在于二级索引中保存的“行指针”的实质。要记住,二级索引叶子节点保存的不是指向行的物理位置的指针,而是行的主键值。这意味着通过二级索引查找行,存储引擎需要找到二级索引的叶子节点获得对应的主键值,然后根据这个值去聚簇索引中查找对应的行。这里做了重复的工作:两次B-Tree查找而不是一次。在InnoDB表中按主键顺序插入行
如果正在使用InnoDB并且没有什么数据需要聚集,那么可以定义一个代理键作为主键,这种主键的数据应该和应用无关,最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好。最好避免随机的(不连续且值的分布范围都非常大)聚簇索引,特别是对于I/O密集型的应用。例如,从性能的角度考虑,使用UUID来作为聚簇索引则会很糟糕:它使得聚簇索引的插入变得完全随机,这是最坏的情况,使得数据没有任何的聚集特性。为了说明,我们下面举个例子:两个表:userinfo,自增主键,userinfo_uuid:UUID主键。下图对测试结果进行了比较:
注意到项UUID主键插入行不仅花费的时间更长,而且索引占用的空间也更大。这一方面是由于主键字段更长;另一方面毫无疑问是由于页分裂和碎片导致的。
这是因为主键的值是顺序的,所以InnoDB把每一条记录都存储在上一条记录的后面 。当达到页的最大填充因子后,下一条记录就会在新的页中 。一旦按照这个顺序的方式加载,主键页就会近似于被顺序的记录填满,这也正是所期望的结果(然而,二级索引页可能是不一样的)。对比一下看看UUID举措索引的插入表数据,看看有什么不同:因为新行的主键值不一定比之前插入的大,所以InnoDB无法简单地总是把新行插入到索引的最后,而是需要为新的行寻找合适的位置 ——通常是已有数据的中间位置——并且分配空间 。这会增加很多的额外工作,并导致数据分布不够优化。下面是总结的一些缺点:- 写入的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被加载到缓存中,InnoDB在不得不在插入新行之前先找到并从磁盘读取到内存中。这将导致大量的随机I/O。
- 因为写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便为新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。
- 由于频繁的页分裂,页会变得稀疏并被不规则地填充,所以最终数据会有碎片。
从这个案例可以看出,使用InnoDB时应该尽可能地按主键顺序插入数据,并且尽可能使用单调增加的聚簇键的值来插入新行。
顺序的主键什么时候会造成更坏的结果?
对于高并发工作负载,在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里,所以并发插入可能导致间歇性竞争。另一个热点可能是AUTO_INCREMENT锁机制;如果遇到这个问题,则可能需要考虑下重新设计表或者应用,或者更改innodb_autoinc_lock-mode配置。如果你的服务器版本还不支持innodb_autoinc-lock_mode参数,可以升级到新版本的InnoDB,可能对这种场景工作的更好。总结
a、关于innoDB中索引的使用
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
b、什么时候选用myisam
myisam的主键索引的叶子节点只存放数据在物理磁盘上的指针,其他次索引也是一样的;
innodb的主键索引的叶子节点下面直接存放数据,其他次索引的叶子节点指向主键id;
由此可以挖掘出一个问题,就是如果Innodb有大数据列,比如 varchar(300),这种比较多的话,那么排序的时候用主键id排序会比较慢,因为id主键下面放着所有数据列,而Myisam就不需要扫描数据列,要解决这个问题的话可以再建一个和主键id一起的联合索引;MyISAM表索引在处理文本索引时更具优势,而INNODB表索引在其它类型上更具效率优势。比如全文索引一般在CHAR、VARCHAR或TEXT列上创建,MyISAM表支持而INNODB表不支持,常见主要针对文本进行索引。同时MySQL高并发需要事务场景时,只能使用INNODB表。
c、该如何选用两个存储引擎呢
此处参考链接:MySQL中MyISAM与InnoDB区别及选择
因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多,写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。
如果系统读少,写多的时候,尤其是并发写入高的时候。InnoDB就是首选了。
两种类型都有自己优缺点,选择那个完全要看自己的实际类弄。2、hash索引:
a 仅仅能满足"=","IN"和"<=>"查询,不能使用范围查询
b 其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引
c 只有Memory存储引擎显示支持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值即为通过特定算法由指定列数据计算出来,磁盘地址即为所在数据行存储在硬盘上的地址(也有可能是其他存储地址,其实MEMORY会将hash表导入内存)。
这样,当我们进行WHERE age = 18 时,会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。
所以,每次查询时都要遍历hash表,直到找到对应的hash值,如(4),数据量大了之后,hash表也会变得庞大起来,性能下降,遍历耗时增加,如(5)。
InnoDB存储引擎有一个特别的功能,叫自适应哈希索引。当InnoDB注意到一些索引被很频繁的访问的时候,会在B-Tree索引的顶端为这些值建立起内存中的索引。这个过程是自动的,既不能控制,也不能配置它。
3、FULLTEXT索引(现在MyISAM和InnoDB引擎都支持了)
主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。
- --创建article表
- CREATE TABLE article (
- id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
- title VARCHAR(200),
- content TEXT,
- FULLTEXT (title, content) --在title和content列上创建全文索引
- );
例如,我们想要在
article
表的title
和content
列中全文检索指定的查询字符串,可以如下编写SQL语句:SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')
它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,先将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。 如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高。全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前,这样一个查询语句是要进行遍历数据表操作的,可见,在数据量较大时是极其的耗时的,如果没有异步IO处理,进程将被挟持,很浪费时间。
(1)创建表的适合添加全文索引CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , `content` text CHARACTER NULL , `time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), FULLTEXT (content) );
(2)修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
(3)直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)
4、R-Tree索引(空间索引)(用于对GIS数据类型创建SPATIAL索引)
索引 MyISAM引擎 InnoDB引擎 Memory引擎 B-Tree索引 支持 支持 支持 HASH索引 不支持 不支持 支持 R-Tree索引 支持 不支持 不支持 Full-text索引 支持 暂不支持(现在支持) 不支持 从物理存储角度
1、聚集索引(clustered index)
2、非聚集索引(non-clustered index)
聚集索引:
一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。即:只要索引是相邻的,那么对应的数据一定也是相邻地存放在磁盘上的。
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此 类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节 省成本。
当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。
如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所占空间小,这些操作是需要在内存中完成的。
非聚集索引:
非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容,该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。记录的物理顺序与逻辑顺序没有必然的联系索引是通过B-Tree的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。
备注:每个表只能有一个聚簇索引,因为一个表中的记录只能以一种物理顺序存放。但是,一个表可以有不止一个非聚簇索引。聚集索引一张表只能创建一个,非聚集索引一张表可以创建多个,在mysql中InnoDB引擎是唯一支持聚集索引的存储引擎。InnoDB按照主键(Primary Key)进行聚集,如果没有定义主键,InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引,InnoDB就会定义隐藏的主键然后在上面进行聚集。
非聚簇索引需要大量的硬盘空间和内存。另外,虽然非聚簇索引可以提高从表中取数据的速度,它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时,必须同时更新索引。 如果你预计一个表需要频繁地更新数据,那么不要对它建立太多非聚簇索引。另外,如果硬盘和内存空间有限,也应该限制使用非聚簇索引的数量。
从逻辑角度
1、普通索引或者单列索引
是最基本的索引,它没有任何限制。它有以下几种创建方式:
(1)直接创建索引CREATE INDEX index_name ON table(column(length))
(2)修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
(3)创建表的时候同时创建索引
CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , `content` text CHARACTER NULL , `time` int(10) NULL DEFAULT NULL , PRIMARY KEY (`id`), INDEX index_name (title(length)) )
(4)删除索引
DROP INDEX index_name ON table
2、唯一索引或者非唯一索引
与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
(1)创建唯一索引CREATE UNIQUE INDEX indexName ON table(column(length))
(2)修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
(3)创建表的时候直接指定
CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) CHARACTER NOT NULL , `content` text CHARACTER NULL , `time` int(10) NULL DEFAULT NULL , UNIQUE indexName (title(length)) );
3、主键索引:主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值,一般是在建表的时候同时创建主键索引:
CREATE TABLE `table` ( `id` int(11) NOT NULL AUTO_INCREMENT , `title` char(255) NOT NULL , PRIMARY KEY (`id`) );
4、多列索引(组合索引):
指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
5、空间索引:空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建
CREATE TABLE table_name[col_name data type] [unique|fulltext|spatial][index|key][index_name](col_name[length])[asc|desc]
1、unique|fulltext|spatial为可选参数,分别表示唯一索引、全文索引和空间索引;
2、index和key为同义词,两者作用相同,用来指定创建索引
3、col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择;
4、index_name指定索引的名称,为可选参数,如果不指定,MYSQL默认col_name为索引值;
5、length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;
6、asc或desc指定升序或降序的索引值存储
缺点
1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update和delete。因为更新表时,不仅要保存数据,还要保存一下索引文件。
2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。
索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。注意事项
使用索引时,有以下一些技巧和注意事项:
1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中,复合索引中只要有一列含有null值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个char(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不推荐使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
这将导致索引失效而进行全表扫描,例如SELECT * FROM table_name WHERE YEAR(column_name)<2017;
6.不使用not in和<>操作
-
MySQL索引的概念以及七种索引类型介绍
2021-07-26 14:46:54MySQL索引的概念以及七种索引类型介绍。MySQL索引的概念以及七种索引类型介绍。
1 索引的概念
索引(在MySQL中也叫做“键(key)”)是存储引擎用于快速找到记录的一种数据结构,这也是索引最基本的功能。
索引对于良好的性能非常关键。数据量越大时,索引对性能的影响也越重要,好的索引可以将查询性能提高几个数量级。在数据量较小且负载较低时,不恰当的索引对性能的影响可能还不明显,但是在数据量逐渐增大时,糟糕的索引会使MySQL的性能急剧的下降。
索引优化是查询性能优化最有效的手段。
如果想要在一本书中找到某个特定主题,一般会先看书的目录,找到对应的页码,然后直接翻到对应的页码即可查看。在MySQL中,存储引擎用类似的方法使用索引,首先在索引中找到对应的值,然后根据匹配的索引记录找到对应的数据行。简单的说,数据库索引类似于书前面的目录,能加快数据库的查询速度。
如下查询:
select name from user where user_id = 5
如果user_id列上建有索引,则MySQL将使用该索引找到user_id 为 5的行,即MySQL现在索引上按值进行查找,然后返回包含该值的数据行。
索引可以一个或多个列的值,如果索引包含多个列,那么列的顺序也很重要,因为MySQL只能高效地使用最左前缀列。
2 索引的类型
按照功能逻辑区分,MySQL目前主要有以下索引类型:
- 主键索引
- 数据列不允许重复,不允许为NULL,一个表只能有一个主键。
ALTER TABLE table_name ADD PRIMARY KEY (column);
- 普通索引
- MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和NULL值。一个表允许多个列创建普通索引。
ALTER TABLE table_name ADD INDEX index_name (column);
- 唯一索引
- 索引列中的值必须是唯一的,但是允许NULL值。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性,而不是为了查询效率。一个表允许多个列创建唯一索引。
ALTER TABLE table_name ADD UNIQUE (column);
- 全文索引
- 主要是为了快速检索大文本数据中的关键字的信息。字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引,基于倒排索引,类似于搜索引擎。MyISAM存储引擎支持全文索引,InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
ALTER TABLE table_name ADD FULLTEXT (column);
- 前缀索引
- 在文本类型如BLOB、TEXT或者很长的VARCHAR列上创建索引时,可以使用前缀索引,数据量相比普通索引更小,可以指定索引列的长度,但是数值类型不能指定。
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
- 组合索引
- 指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀原则。
- 主键索引、普通索引、唯一索引等都可以使用多个字段形成组合索引。例如,
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 );
- 空间索引
- MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
参考资料:
- 《 MySQL 技术内幕: InnoDB 存储引擎》
- 《高性能 MySQL》
如有需要交流,或者文章有误,请直接留言。另外希望点赞、收藏、关注,我将不间断更新各种Java学习博客!
- 主键索引
-
看一下MySQL索引类型
2020-10-14 15:51:58MySQL目前主要有以下几种索引类型: 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、语句 CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key][index_name](col_name... -
InnoDB中的索引类型
2020-07-27 19:33:54InnoDB数据引擎使用B+树构造索引结构,其中的索引类型依据参与检索的字段不同可以分为主索引和非主索引;依据B+树叶子节点上真实数据的组织情况又可以分为聚族索引和非聚族索引。每一个索引B+树结构都会有一个独立的... -
mysql常用四种索引类型
2022-04-01 21:33:01唯一索引 (UNIQUE Indexs) 要求索引列的所有值都只能出现一次,即必须唯一。 普通索引 (Normal index) 仅用来提高查询速度,没有其他特性。 全文索引 (FULLTEXT Indexes) MySQL可以通过建立全文 -
oracle查看表索引及索引类型
2019-11-29 15:58:42select b . uniqueness , a . index_name , a . table_name , a . column_name FROM all_ind_columns a , all_indexes b ...可以看到 INDEX_NAME中有两个 名字相同的列,代表这是一个联合索引 -
数据库的五种索引类型
2019-03-15 21:24:13本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方 首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',如果要拿这条数据的话需要些的... -
PG索引类型
2019-01-14 14:58:11PG索引类型 索引类型 CREATE INDEX 在一个指定表或者物化视图的指定列上创建一个索引,索引主要用来提高数据库的效率(尽管不合理的使用将导致较慢的效率) btree 选择性越好(唯一值个数接近记录数)的列,越适合b-... -
Oracle 数据库 各种索引类型 简单介绍
2018-12-29 12:46:35各种Oracle索引类型介绍 逻辑上: Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引 物理上: Partitioned 分区索引 NonPartitioned ... -
mysql索引类型和索引方式
2018-11-24 12:28:52在MySQL中,通常我们所指的索引类型,有以下几种: 主键索引(PRIMARY KEY) 也简称主键。它可以提高查询效率,并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键,但主键不一定是自动... -
PostgreSQL中的几种索引类型
2019-08-07 08:32:25索引是增强数据库性能的利器,在检索某些特定行的时候效率会有很大提升,postgresql中索引类型丰富,每种索引有着不同的应用场景,下面简单介绍一下。 索引类型与场景 BTree • =, >, >=, <, <=、... -
数据库索引类型介绍及其优缺点、区别、适用场景
2020-11-11 22:04:39索引 索引分为主键索引、唯一索引、普通索引、聚集索引、全文索引几种,而索引其实就是在无序的数据中建立索引...索引类型 主键索引 数据列不允许重复,不允许为NULL,可以被引用为外键,一个表只能有一个主键索引 唯 -
MYSQL数据库四种索引类型介绍
2020-04-20 18:24:46MYSQL数据库四种索引类型 主键索引: 主键是一种唯一性索引,但它必须指定为PRIMARY KEY,每个表只能有一个主键。 唯一索引: 索引列的所有值都只能出现一次,即必须唯一,值可以为空。 普通索引 : 基本的... -
MySQL5种索引类型
2018-12-06 13:36:32MySQL目前主要有以下几种索引类型: 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、语句 CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key][index_name](col_name... -
PostgreSQL索引类型
2018-03-09 14:29:52PostgreSQL有很多索引类型。每种索引类型都有各自适用的情景,具体使用哪一种索引取决于数据类型、表中的底层基本数据和执行的查询类型。接下来简单看一下PostgreSQL中可用的索引类型有哪些,以及各种情况下适合使用... -
各种Oracle索引类型介绍
2018-03-03 16:03:27逻辑上:Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domain 域索引物理上:Partitioned 分区索引 NonPartitioned 非分区索引 B-tree: Normal ... -
TypeScript实战-17-TS高级类型-索引类型
2019-09-20 17:53:40本篇介绍TS的另一个高级类型-索引类型 从对象中获取一些属性的值,然后建立一个集合 et obj = { a: 1, b: 2, c: 3 } // 获取对象中的指定属性的值集合 function getValues(obj: any, keys: string[]) { return ... -
mysql索引类型 normal, unique, full text
2021-01-18 18:32:12问题1:mysql索引类型normal,unique,full text的区别是什么?normal:表示普通索引unique:表示唯一的,不允许重复的索引,如果该字段信息保证不会重复例如身份证号用作索引时,可设置为uniquefull textl: 表示 ... -
mysql索引类型
2021-03-01 09:49:04MySQL目前主要有以下几种索引类型: 1.普通索引 2.唯一索引 3.主键索引 4.组合索引 5.全文索引 二、语句 CREATE TABLE table_name[col_name data type] [unique|fulltext][index|key][index_name](col_name[length])... -
“元素隐式具有 “any“ 类型,因为类型为 “string“ 的表达式不能用于索引类型”
2022-04-07 20:39:28“元素隐式具有 “any” 类型,因为类型为 “string” 的表达式不能用于索引类型” 在类型 XXX 上找不到具有类型为 “string” 的参数的索引签名。 搜索了一圈解决方案,没一个特别优雅的。 方案一,修改tsconfig 是... -
聊一聊 InnoDB 引擎中的索引类型
2020-01-21 16:18:21索引对数据库有多重要,我想大家都已经知道了吧,关于索引可能大家会对它多少有一些误解,首先索引是一种数据结构,并且索引不是越多越好。合理的索引可以提高存储引擎对数据的查询效率。 形象一点来说呢,索引跟... -
mysql--------四种索引类型
2019-05-30 19:44:20mysql索引的四种类型:主键索引、唯一索引、普通索引和全文索引。通过给字段添加索引可以提高数据的读取速度,提高项目的并发能力和抗压能力。索引优化时mysql中的一种优化方式。索引的作用相当于图书的目录,可以... -
带你认识Oracle索引类型(全面总结)
2018-11-13 19:48:07当我们对所有索引类型有了了解之后,就可以针对性的写出高效的SQL语句、建立最合适的索引。 那Oracle索引都有哪些类型呢? 首先我们来看一下索引的数据结构:B树 在介绍之前,首先建立一张表 create table tes... -
什么是索引?Mysql目前主要的几种索引类型
2018-02-27 10:11:16一、索引MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。... -
MySQL索引类型(type)分析
2021-01-20 19:10:31type索引类型:ststem > const > eq_ref > ref > range > index > all优化级别从左往右递减,没有索引的一般为’all’,需要对type进行优化前提是有索引。其中’system’和’const’只是理想型,... -
mysql索引类型Normal,Unique,Full Text区别以及索引方法Btree,Hash的区别
2018-12-19 15:12:48mysql索引类型Normal,Unique,Full Text区别 Normal: 表示普通索引,大多数情况下都可以使用 Unique: 约束唯一标识数据库表中的每一条记录,即在单表中不能用每条记录是唯一的(例如身份证就是唯一的),...