MySQL中的索引

刺骨的言语ヽ痛彻心扉 2022-05-16 12:23 190阅读 0赞

MySQL 中的索引

作者： Jason Lee @ [http://blog.csdn.net/jasonblog][http_blog.csdn.net_jasonblog]

日期： 2010-06-04

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

\[ 引言 \]

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

\[ 使用索引 \]

首先，创建一个用于测试的表：

CREATE TABLE \`test\`.\`books\` (

\`id\` INT NOT NULL AUTO\_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;  
 CREATE TABLE \`test\`.\`books\` (

\`id\` INT NOT NULL AUTO\_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 COLLATE utf8\_general\_ci

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

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

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

\`id\` INT NOT NULL AUTO\_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 COLLATE utf8\_general\_ci

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

\[ 理解索引 \]

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

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

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

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

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

使用索引需要注意：

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

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

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

[http_blog.csdn.net_jasonblog]: http://blog.csdn.net/jasonblog