mysql索引结构4种结构(mysql索引数据结构有哪些)
【mysql】索引类型的划分
了解mysql的索引类型的时候,我觉得按照以下4中方式划分逻辑是比较清晰的。
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.com
mysql索引类型有哪些
普通索引:一个索引只包含一个列,一个表可以有多个单列索引;
唯一索引:索引列的值必须唯一,但允许有空值;
复合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并;
聚簇索引:也可以称为主键索引,是一种数据存储方式,B+树结构,一张表只能有一个聚簇索引;
非聚簇索引:顾名思义,不是聚簇索引。
mysql有几种索引类型?使用索引时都有那些地方要注意?sql优化原则是什么?
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"
mysql有几种索引类型?使用索引时都有那些地方要注意
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的效率,就要考虑建立组合索引。
二:使用索引的注意事项
使用索引时,有以下一些技巧和注意事项:
1.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
select * from users where YEAR(adddate)2007;
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:
select * from users where adddate‘2007-01-01';
6.不使用NOT IN和操作。
三:sql优化原则
常见的简化规则如下:
1.不要有超过5个以上的表连接(JOIN)
2.考虑使用临时表或表变量存放中间结果。
3.少用子查询
4.视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
5.连接的表越多,其编译的时间和连接的开销也越大,性能越不好控制。
6.最好是把连接拆开成较小的几个部分逐个顺序执行。
7.优先执行那些能够大量减少结果的连接。
8.拆分的好处不仅仅是减少SQL Server优化的时间,更使得SQL语句能够以你可以预测的方式和顺序执行。
如果一定需要连接很多表才能得到数据,那么很可能意味着设计上的缺陷。
mysql索引采用什么数据结构
文就是对这两种数据结构做简单的介绍。
1. B-Tree
B-Tree不是“B减树”,而是“B树”。
这里参考了严蔚敏《数据结构》对B-Tree的定义:
一棵m阶的B-Tree,或者为空树,或者满足下列特性:
1.树中每个结点至多有m棵子树;
2.若根结点不是叶子结点,则至少有两棵子树;
3.除根节点之外的所有非终端结点至少有[m/2]棵子树;
4.所有非终端结点中包含下列信息数据:
(n,A0,K1,A1,K2,A2……Kn,An)
其中,n为关键字的数目,K(i)为关键字,且K(i) K(i+1), Ai为指向子树根结点的指针,且指针A(i-1)所指子树中所有结点的关键字均小于Ki,Ai所指子树中所有结点的关键字均大于Ki;
5.所有叶子结点都出现在同一层次上;
下面通过一个例子解释一下B-Tree的查找过程。
这是一棵4阶的B-Tree,深度为4。
假如在该图中查找关键字47,首先从根结点开始,根据根结点指针t找到*a结点,因为47大于 *a 结点的关键字35,所以会去A1指针指向的 *c结点继续寻找,因为 *c的关键字 43 要查找的47 *c结点的关键字78,所以去 *c结点A1指针指向的 *g结点去寻找,结果在 *g结点中找到了关键字47,查找成功。
2. B+Tree
不同的存储引擎可能使用不同的数据结构存储,InnoDB使用的是B+Tree;那什么是B+Tree呢?
B+Tree是应文件系统所需而出的一种B-Tree的变型树,一棵m阶的B+树和m阶的B-树的差异在于:
1.有n棵子树的结点中含有n个关键字;
2.所有的叶子结点中包含了全部关键字的信息,及指向含这些关键字的记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接;
3.所有的非终端结点可以看成是索引部分,结点中仅含有其子树(根结点)中的最大(或最小)关键字;
还是通过一个例子来说明。
这个例子中,所有非终端结点仅含有子树中最大的关键字。
因为叶子节点本身依据关键字的大小自小而大顺序链接,所以可以从最小关键字起顺序查找。也可以从根结点开始,进行随机查找。
在B+树中随机差找和在B-树中类似,以上图为例。假设要查找关键字51,现在根节点中比较,发现5159,因为这里使用的是非终端结点的关键字是子树中最大的关键字,所以进入最大值为59的子结点(15\44\59)中查找,同理,因为445159,所以进入P3指向的结点(51\59)中查找,然后命中关键字51,因为此结点(51\59)是叶子结点,所以查找终止,该结点包含指向数据的指针。
3.索引如何在B+Tree中组织数据存储
假设有如下表:
对于表中的每一行数据,索引中包含了last_name、first_name和dob列的值,下图展示索引是如何组织数据存储的:
索引对多个值进行排序的依据是定义索引时列的顺序。
(Allen Cuba 1960-01-01)结点左侧的指针指向[?,Allen Cuba 1960-01-01)的叶子页,(Allen Cuba 1960-01-01)和(Astaire,Angelina,1980-03-04)之间的指针指向[Allen Cuba 1960-01-01,Astaire Angelina 1980-03-04)的叶子页,以此类推。总之,每个指针指向的结点中的最小值就是该指针左侧的的值。
这种存储结构也说明了在定义多个列组成的多列索引中,为什么需要把重复率最低的列放到最左侧,因为这会减少比较的次数,查找起来更加高效。
4.索引为什么选用B树这种数据结构?
因为使用B树查找时,所用的磁盘IO操作次数比平衡二叉树更少,效率也更高。
为什么使用B树查找所用的磁盘IO操作次数比平衡二叉树更少?
大规模数据存储中,树节点存储的元素数量是有限的(如果元素数量非常多的话,查找就退化成节点内部的线性查找了),这样导致二叉查找树结构由于树的高度过大而造成磁盘I/O读写过于频繁,进而导致查询效率低下。那么我们就需要减少树的高度以提高查找效率。而平衡多路查找树结构B树就满足这样的要求。B树的各种操作能使B树保持较低的高度,从而达到有效减少磁盘IO操作次数。