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

时间: 2024-10-31 21:35:01

MySQL中的索引的相关文章

mysql中字符串索引问题与例子

  字符串索引与数字索引有一些方面如果没做好会非常的慢了,今天我们就一起来看看小编整理的一些mysql中字符串索引问题与例子了,因为字符索引相对来说也简单文章就简单的整理了一些例子,希望对各位有帮助. 事情的起因是线上日志发现的mysql慢查询.100万数据量的标准,联合查询全部走索引的情况下,尽然要600多毫秒.很不解,但是将索引列由varchar(50)型改为bigint型后,数据提升了30倍.究其原因就索引树上搜索时要进行大量的比较操作,而字符串的比较比整数的比较耗时的多. 所以建议一般情

MySQL中对于索引的基本增删查改操作总结_Mysql

创建索引 MySQL创建索引的语法如下: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name,...) 其中对应的语法变量信息如下: [UNIQUE|FULLTEXT|SPATIAL] 中括号中的这三个关键字表示创建的索引类型,它们分别表示唯一索引.全文索引.空间索引三种不同的索引类型.如果我们不指定任何关键字,则默认为普通索引. index_name

MySQL中利用索引对数据进行排序的基础教程_Mysql

MySQL中,有两种方式生成有序结果集:一是使用filesort,二是按索引顺序扫描.利用索引进行排序操作是非常快的,而且可以利用同一索引同时进行查找和排序操作.当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时,可以使用索引来排序.如果查询是连接多个表,仅当ORDER BY中的所有列都是第一个表的列时才会使用索引.其它情况都会使用filesort. MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度.

mysql中数据库索引与优化

一.索引的概念索引就是加快检索表中数据的方法.数据库的索引类似于书籍的索引.在书籍中,索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息.在数据库中,索引也允许数据库程序迅速地找到表中的数据,而不必扫描整个数据库. 二.索引的特点 1.索引可以加快数据库的检索速度 2.索引降低了数据库插入.修改.删除等维护任务的速度 3.索引创建在表上,不能创建在视图上 4.索引既可以直接创建,也可以间接创建 5.可以在优化隐藏中,使用索引 6.使用查询处理器执行SQL语句,在一个表上,一次只能使用一个索引

通过实例认识MySQL中前缀索引的用法_Mysql

今天在测试环境中加一个索引时候发现一警告 root@test 07:57:52>alter table article drop index ind_article_url; Query OK, 144384 rows affected (16.29 sec) Records: 144384 Duplicates: 0 Warnings: 0 root@test 07:58:40>alter table article add index ind_article_url(url); Query

MySQL和Lucene索引对比分析

MySQL和Lucene都可以对数据构建索引并通过索引查询数据,一个是关系型数据库,一个是构建搜索引擎(Solr.ElasticSearch)的核心类库.两者的索引(index)有什么区别呢?以前写过一篇<Solr与MySQL查询性能对比>,只是简单的对比了下查询性能,对于内部原理却没有解释,本文简单分析下两者的索引区别. MySQL索引实现 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式. M

mysql中key 、primary key 、unique key 与index区别_Mysql

mysql中索引是非常重要的知识点,相比其他的知识点,索引更难掌握,并且mysql中的索引种类也有很多,比如primary key .unique key 与index等等,本文章向大家介绍mysql中key .primary key .unique key 与index区别.  一.key与primary key区别 CREATE TABLE wh_logrecord ( logrecord_id int(11) NOT NULL auto_increment, user_name varch

python-关于mysql中的位图索引和位片索引问题

问题描述 关于mysql中的位图索引和位片索引问题 老师给了一个作业要求利用其他语言例如Python或者C++来实现位图索引,但是一直教的就是mysql,没要办法建位图索引啊.不知道怎么办才好了,求大神指导... 解决方案 Mysql 索引 解决方案二: http://www.cnblogs.com/yuerdongni/p/4255395.html

MySQL中管理表和索引的相关语句

1. 创建表 MySQL中创建表是通过create table语句实现的,语法这里就不介绍了,非常复杂,可以去官网上查询,所幸的是, 我们并不需要记住所有的选项,因为大部分都采用默认即可,下面介绍几个比较常用的选项. 1)指定存储引擎 默认的存储引擎由default-storage-engine指定,如果没有指定,则为MyISAM,如果建表时,你不想使用默认的存储 引擎,可以通过如下语句实现: create table table_name ( ... ) engine=engine_name;