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个字符的单词，可自定义），从而高效地在CHAR、VERCHAR或TEXT类型数据中搜索。

 

[理解索引]

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

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

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

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

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

 

使用索引需要注意：

⑴只对WHERE和ORDER BY需要查询的字段设置索引，避免无意义的硬盘开销；

⑵组合索引支持前缀索引；

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