JasonLee的专栏

一个写着几行代码的理工科文学青年。

MySQL中的索引

MySQL中的索引

作者:Jason Lee @http://blog.csdn.net/jasonblog

日期:2010-06-04

声明:本文发表在csdn博客,如有转载,请注明出处

 

[引言]

如图书馆等存放大量数据的场合都需要设置索引以方便检索。当数据库中存储的记录逐渐海量化的时候,合理地采用索引能大大改善程序性能。

 

[使用索引]

首先,创建一个用于测试的表:

CREATE TABLE`test`.`books` (

`id` INT NOT NULLAUTO_INCREMENT ,
`title` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`author` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`press` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY ( `id` )

) ENGINE =MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci

以上语句在建表的过程中同时指定了主键,这会创建一个主键索引,即数据库中最常见的索引类型。

 

除了PRIMARY关键字,UNIQUE关键字也会形成索引。不同的是,每个表中主键索引只能有一个,而唯一索引可以有多个。而二者都可以指定多字段索引:

DROP TABLE books;
CREATETABLE `test`.`books` (

`id` INT NOT NULLAUTO_INCREMENT ,
`title` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`author` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
UNIQUE (title, author),
`press` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY ( `id` )

) ENGINE = MYISAM CHARACTER SET utf8 COLLATEutf8_general_ci

以上的UNIQUE关键字用书名和作者的组合形成了唯一索引。

 

如果经常性地需要对作者进行排序,那么可以为该字段创建一个索引;或者,当作者有姓氏和姓名两个不同字段,也可以建立多字段索引。

DROP TABLEbooks;
CREATE TABLE `test`.`books` (

`id` INT NOT NULLAUTO_INCREMENT ,
`title` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`author` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
INDEX ( author ) ,
`press` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY ( `id` )

) ENGINE = MYISAM CHARACTER SET utf8 COLLATEutf8_general_ci

 

最后一种索引是使用FULLTEXT关键字建立的全文索引。MySQL为待搜索的文本进行分词(默认忽略少于4个字符的单词,可自定义),从而高效地在CHARVERCHARTEXT类型数据中搜索。

 

[理解索引]

要理解索引,首先需要了解数据如何存储在硬盘上。不同的存储引擎采取的措施可能不一样,比如MySQL客户端默认采用MyISAM,该引擎为每个表创建单独的文件。

不管是否为每个表创建了单独的文件,操作系统从硬盘读取数据到内存中总是以页为单位的。因此,如果要获取特定记录,就需要把包含该记录的页读入。

MySQL在获取一条记录的时候,可以采取逐条扫描或者索引访问两种不同的方法。假设采取第一种方法,要获取id1234的记录,就需要顺序地、依次地访问过前1233条记录。不仅如此,还需要考虑每次读入数据页的IO开销。而如果采取索引,则可以根据索引指向的页以及记录在页中的位置,迅速地读取目标页进而获取目标记录。

除了在获取特定行的情况下使用hash十分快捷,在其它情况下都(默认)采用B树来构建索引。B树是平衡多叉树,每个节点存放多少个值取决于值所占的空间,这与每一张数据页存放多少条记录与记录信息量有关同理。节点中的值是以非降序进行排列的,节点中的值总是小于等于指向它的结点中的值。

MySQL使用B树构造索引的情况下,是由叶子指向具体的页和记录的。并且一个叶子有一个指针指向下一个叶子。

 

使用索引需要注意:

只对WHEREORDER BY需要查询的字段设置索引,避免无意义的硬盘开销;

组合索引支持前缀索引;

更新表的时候,如增删记录,MySQL会自动更新索引,保持树的平衡;

阅读更多
个人分类: Others About CS
想对作者说点什么? 我来说一句

mysql索引

2018年06月04日 4KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭