MySql索引原理及使用

索引用于快速查找出在某个列中有一特定值的行,不适用索引,MySQL必须从第一条记录开始读完整个表,直到找到相关行。表越大,查询数据花费的时间越多。如果表中查询的列有一个索引,MySQL能快速达到某个位置去搜索文件,而不必查看所有数据。因此使用索引是数据库优化最直接有效的方式,所以了解索引对于开发人员尤为重要。

索引简介

索引是一个单独的、存储在磁盘上的数据库结构,他们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或者多个列中有一特定值的行,所有MySQL列类型都可以被索引,对于相关列使用索引是提高查询操作速度的最佳途径。

索引的优点:

1.通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。

2.可以大大加快数据的查询速度,这也是创建索引的最主要原因。

3.在实现数据的参考完整性方面,可以加速表与表之间的连接。

4.在使用分组和排序字句进行数据查询时,也可以显著减少查询中分组和排序的时间。

索引的缺点:

1.创建和维护索引要耗费时间,数据量越大耗费时间也会越长。

2.索引需要占磁盘空间,除了数据表占数据空间外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。

3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

索引的原理

一 索引原理
索引的目的在于提高查询效率,与我们查阅图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子还有:查字典,查火车车次,飞机航班等

本质都是:通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据。

数据库也是一样,但显然要复杂的多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段......这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。但如果是1千万的记录呢,分成几段比较好?稍有算法基础的同学会想到搜索树,其平均复杂度是lgN,具有不错的查询性能。但这里我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂,一方面数据是保存在磁盘上的,另外一方面为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道访问磁盘的成本大概是访问内存的十万倍左右,所以简单的搜索树难以满足复杂的应用场景。

 二 磁盘IO与预读

考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

三、索引的数据结构
任何一种数据结构都不是凭空产生的,一定会有它的背景和使用场景,我们现在总结一下,我们需要这种数据结构能够做些什么,其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,b+树应运而生。

如上图,是一颗b+树,关于b+树的定义可以参见B+树,这里只说一些重点,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

###b+树的查找过程

如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

###b+树性质
1.索引字段要尽量的小:通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

索引的分类

索引是在存储引擎中实现的,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有的存储引擎支持每个表至少16个索引,总长度至少为256字节,大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种:Btree和hash,具体和表的存储引擎相关;MyISAM和InnoDB存储引擎只支持Btree索引,MEMORY/HEAP存储引擎可以支持hash和Btree索引。

MySQL索引的分类:

1.普通索引:MySQL的基本索引类型,允许索引字段的列插入重复值和空值。

2.唯一索引:索引列字段必须唯一,但允许有空值,如果是组合索引,则组合必须唯一,主键索引是一种特殊的唯一索引,不允许有空值。

3.单列索引和组合索引:单列索引只包含单个列,一个表可以有多个单列索引。组合索引指在表的多个字段组合上创建索引,使用组合索引时遵循最左前缀集合。

4.全文索引:全文索引类型为FullText,在定义索引的列上支持值得全文查找,允许在这些索引列中插入重复值和空置,全文索引可以在char、varchar和text类型的列上创建,MySQL中只有MyISAM存储引擎支持全文索引。

5.空间索引:空间索引是对空间数据类型的字段建立的索引,只能在MyISAM存储引擎的表中创建。

索引创建

创建表的时候创建索引

语法结构:

CREATE TABLE table_name [col_name data_type]

[UNIQUE | FULLTEXT | SPATITAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]

1.创建普通索引

最基本的索引类型,没有唯一性之类的限制,其作用只是加快对数据库的访问速度。

CREATE TABLE book
(
book_id                        INT NOT NULL,
book_name                      VARCHAR(255) NOT NULL,
info                           VARCHAR(255) NOT NULL,
INDEX(info)
);

没有指定索引名称时会默认使用索引字段作为索引名称,此处为给字段info创建名称为info索引。

2.创建唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间,尤其是对比较庞大的数据表,它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

CREATE TABLE t1
(
id                        INT NOT NULL,
name                      CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
); 

此处为给id字段创建一个名为UniqIdx的唯一索引。

3.创建单列索引

单列索引是在数据表中某一个字段上创建的索引,一个表中可以创建多个单列索引,前面两个例子创建的都为单列索引

CREATE TABLE t2
(
id                        INT NOT NULL,
name                      CHAR(30) NULL,
INDEX SingleIdx(name(20))
); 
此处为给name字段创建一个名为SingleIdx的单列索引,索引长度为20。

4.创建组合索引

组合索引是在多个字段上创建一个索引

CREATE TABLE t3
(
id                        INT NOT NULL,
name                      CHAR(30) NOT NULL,
age                       INT NOT NULL,                      
info                      VARCHAR(255),
INDEX MultiIdx(id, name, age(100))
);

此处为给id,name和age字段创建一个名为MultiIdx的组合索引。

组合索引可起几个索引的作用,但必须遵从“最左前缀”:利用索引中最左边的列集来匹配行。例如这里又id,name和age 3个字段构成的索引,索引行中按id/name/age的顺序存放,索引可以搜索下面字段组合:(id,name,age)、(id,name) 或者 id。如果列不构成索引最左面的前缀,MySQL不能使用局部索引,如(age)或者(name,age)组合则不能使用索引查询。

5.创建全文索引

FullText(全文索引)可以用于全文搜索。只有MyISAM存储引擎支持FullText索引,并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行,不支持局部(前缀)索引。

CREATE TABLE t4
(
id                        INT NOT NULL,
name                      CHAR(30) NOT NULL,
age                       INT NOT NULL,                      
info                      VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;

此处为给info字段创建一个名为FullTxtIdx的组合索引,索引长度为20,全文索引非常适合于大型数据集,对于小的数据集,它的用处比较小。

6.创建空间索引

空间索引必须在MyISAM类型的表中创建,且空间类型的字段必须为非空。

CREATE TABLE t5
(
g                        GEOMETRY NOT NULL,
SPATIAL                  INDEX spatIdx(g)
) ENGINE=MyISAM;

此处为g字段上创建了名为spatIdx的空间索引,注意创建时指定空间类型字段值的非空约束,并且表的存储引擎为MyISAM。

在已经存在的表上创建索引

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

语法结构:

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]

[index_name] (col_name[length],...) [ASC | DESC]

例如:使用ALTER TABLE在bookname字段上添加名为BkNameIdx索引,SQL语句如下:

ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

其他类型的索引只需将INDEX换成其他类型即可如:UNIQUE INDEX,FULLTEXT INDEX,SPATIAL INDEX。

2.使用 CREATE INDEX 创建索引

语法结构:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

ON table_name (col_name[length],...) [ASC | DESC]

例如:使用CREATE INDEX在bookname字段上添加名为BkNameIdx索引,SQL语句如下:

CREATE INDEX BkNameIdx ON book(bookname)
其他类型的索引只需将INDEX换成其他类型即可如:UNIQUE INDEX,FULLTEXT INDEX,SPATIAL INDEX。

删除索引

1.使用ALTER TABLE删除索引

语法结构:ALTER TABLE table_name DROP INDEX index_name;

2.使用DROP INDEX语句删除索引

语法结构:DROP INDEX index_name ON table_name;

总结

为数据库选择正确的索引是一项复杂的任务,如果索引列较少,则需要的磁盘空间和维护开销都较少。如果在一个大表上创建了多种组合索引,索引文件也会膨胀很快。另一方面,索引较多可覆盖更多的查询,可能需要实验若干不同的设计,才能找到最有效的索引。对字符串类型的字段进行索引,如果可能应该指定一个前缀长度。例如,如果有个CHAR(255)的列,在前10个或者30个字符内,多数值是惟一的,则不需要对整个列进行索引,短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作,因此如何更好的使用索引,还需要我们在实际中多尝试多测验。


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值