大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
了解mysql的索引类型的时候,我觉得按照以下4中方式划分逻辑是比较清晰的。
我们拥有10年网页设计和网站建设经验,从网站策划到网站制作,我们的网页设计师为您提供的解决方案。为企业提供做网站、成都网站制作、微信开发、小程序开发、手机网站制作设计、H5建站、等业务。无论您有什么样的网站设计或者设计方案要求,我们都将富于创造性的提供专业设计服务并满足您的需求。
1.存储结构 2.物理存储 3.作用字段 4.功能
按照数据存储的结构可以分B树索引和hash索引。
又称为 BTREE 索引,目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构。
基于这种树形数据结构,表中的每一行都会在索引上有一个对应值。因此,在表中进行数据查询时,可以根据索引值一步一步定位到数据所在的行。
查询必须从索引的最左边的列开始。
查询不能跳过某一索引列,必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。
也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。
其中,MEMORY 存储引擎可以支持 B-树索引和 HASH 索引,且将 HASH 当成默认索引。
HASH 索引不是基于树形的数据结构查找数据,而是根据索引列对应的哈希值的方法获取表的记录行。
不能使用 HASH 索引排序。
HASH 索引只支持等值比较,如“=”“IN()”或“=”。
HASH 索引不支持键的部分匹配,因为在计算 HASH 值的时候是通过整个索引值来计算的。
聚集索引是按照所以把数据排好序了,所以一个表只能存在一个聚集索引,其它的都是非聚集索引。
因这个特性,聚集索引是查询数据范围的时候有很大的性能优势。
但是也需要注意的是如果频繁更新的列不适合设置为聚集索引,
原因很简单,每次更新都需要从新排序,频繁的更新给的压力也大。
如果不指定的话,默认主键为聚集索引。
一个表里除了一个聚集索引外其他的都是非聚集索引,虽然不能把数据按照索引排序,但是索引数据是可以排序的。
所以非聚集索引查询范围的时候是先找索引列的范围,再通过这个索引查询行的值。
单列索引即一个索引只包含单个列。
组合索引指在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合
Primary Key(聚集索引):InnoDB存储引擎的表会存在主键(唯一非null),如果建表的时候没有指定主键,则会使用第一非空的唯一索引作为聚集索引,否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
Key(普通索引):是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值
Unique(唯一索引):索引列的值必须唯一,但允许有空值。若是组合索引,则列值的组合必须唯一。
主键索引是一种特殊的唯一索引,不允许有空值。
既不是主键索引也不是唯一索引的一般索引。
FULLTEXT(全文索引):全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。
全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。
空间索引主要用于地理空间数据类型 GEOMETRY。
下面是 mysql官网给出的几个存储引擎和索引之间的关系 。
欢迎大家的意见和交流
email: li_mingxie@163点抗
MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类
特点:B+树叶子节点存储行数据
一个表中,必须有一个聚集索引,只能有一个聚集索引,Innodb通常把一个表的主键索引作为聚集索引,如果没有主键InnoDB会选择一个唯一索引代替。如果没有这样的索引,InnoDB会隐式的定义一个主键来作为聚集索引,这个字段为6个字节,类型为长整形。
利用主键索引查找行数据是最快的,建议使用自增主键原因是利于索引树的构建(主键自增写入时新插入的数据不会影响到原有页,插入效率高;但是如果主键是无序的或者随机的,那每次的插入可能会导致原有页频繁的分裂,影响插入效率)
特点:B+树叶子节点存储主键ID
一个表中可以有多个非聚集索引,每个非聚集索引即是一棵B+树
通过非聚集索引查找数据时,需要先在非聚集索引上找到主键ID,再从聚集索引获取行数据,这个过程就称之为回表
B树索引中的B树实际上是B+树,至于为什么使用B+树而不使用B树或者红黑树的原因在另外的文章中有提及。
特点:
特点:类似JDK中的HashMap,但无法支持范围查询
特点:使用的算法仍然是B树索引,不同的就是索引列的值必须唯一
对于普通索引来说,查找到满足条件的第一个记录后,需要查找下一个记录,直到碰到第一个不满足条件的记录。
对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索,提升索引性能
另外插入行时会构建该唯一索引,假如索引值重复将插入失败,适合业务上做唯一性检验
通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题,但是业务上一般都不采用这种索引,而是使用ES处理全文搜索需求
仅对某个特定字段建立的索引,如(biz_id)
对多个字段建立的索引,如(biz_id,type)
PRIMARY, INDEX, UNIQUE 这3种是一类\x0d\x0aPRIMARY 主键。 就是 唯一 且 不能为空。\x0d\x0aINDEX 索引,普通的\x0d\x0aUNIQUE 唯一索引。 不允许有重复。\x0d\x0aFULLTEXT 是全文索引,用于在一篇文章中,检索文本信息的。\x0d\x0a\x0d\x0a举个例子来说,比如你在为某商场做一个会员卡的系统。\x0d\x0a这个系统有一个会员表\x0d\x0a有下列字段:\x0d\x0a会员编号 INT\x0d\x0a会员姓名 VARCHAR(10)\x0d\x0a会员身份证号码 VARCHAR(18)\x0d\x0a会员电话 VARCHAR(10)\x0d\x0a会员住址 VARCHAR(50)\x0d\x0a会员备注信息 TEXT\x0d\x0a\x0d\x0a那么这个 会员编号,作为主键,使用 PRIMARY\x0d\x0a会员姓名 如果要建索引的话,那么就是普通的 INDEX\x0d\x0a会员身份证号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)\x0d\x0a会员备注信息 , 如果需要建索引的话,可以选择 FULLTEXT,全文搜索。\x0d\x0a\x0d\x0a不过 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。\x0d\x0a用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。
mysql的索引类型及使用索引时的注意事项有:
一、普通索引。这是最基本的索引,它没有任何限制。它有以下几种创建方式:
1、创建索引
代码如下:
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。
2、修改表结构
代码如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
-- 删除索引的语法:
DROP INDEX [indexName] ON mytable;
二、唯一索引。它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
代码如下:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
三、主键索引。它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:
代码如下:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
当然也可以用 ALTER 命令。记住:一个表只能有一个主键。
四、组合索引。为了形象地对比单列索引和组合索引,为表添加多个字段:
代码如下:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:
代码如下:
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]
建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。
如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:usernname,city,age usernname,city usernname 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:
[code]
SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin"