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会自动更新索引,保持树的平衡;