mysql数据库索引类型和原理

索引初识:

最普通的情况,是为出现在where子句的字段建一个索引。为方便讲述,我们先建立一个如下的表。

CREATE TABLE mytable ( id serial primary key, category_id int not null default 0, user_id int not null default 0, adddate int not null default 0);

很简单吧,不过对于要说明这个问题,已经足够了。如果你在查询时常用类似以下的语句:

SELECT * FROM mytable WHERE category_id=1; 

  最直接的应对之道,是为category_id建立一个简单的索引:

CREATE INDEX mytable_categoryid ON mytable (category_id);

  OK,搞定?先别高兴,如果你有不止一个选择条件呢?例如:

SELECT * FROM mytable WHERE category_id=1 AND user_id=2;

  你的第一反应可能是,再给user_id建立一个索引。不好,这不是一个最佳的方法。你可以建立多重的索引。

CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);

  注意到我在命名时的习惯了吗?我使用"表名_字段1名_字段2名"的方式。你很快就会知道我为什么这样做了。

  现在你已经为适当的字段建立了索引,不过,还是有点不放心吧,你可能会问,数据库会真正用到这些索引吗?测试一下就OK,对于大多数的数据库来说,这是很容易的,只要使用EXPLAIN命令:

按 Ctrl+C 复制代码 EXPLAIN  SELECT * FROM mytable   WHERE category_id=1 AND user_id=2; This is what Postgres 7.1 returns (exactly as I expected)  NOTICE: QUERY PLAN: Index Scan using mytable_categoryid_userid on mytable (cost=0.00..2.02 rows=1 width=16) EXPLAIN 按 Ctrl+C 复制代码

  以上是postgres的数据,可以看到该数据库在查询的时候使用了一个索引(一个好开始),而且它使用的是我创建的第二个索引。看到我上面命名的好处了吧,你马上知道它使用适当的索引了。

  接着,来个稍微复杂一点的,如果有个ORDER BY字句呢?不管你信不信,大多数的数据库在使用order by的时候,都将会从索引中受益。

SELECT * FROM mytable WHERE category_id=1 AND user_id=2 ORDER BY adddate DESC;

  有点迷惑了吧?很简单,就象为where字句中的字段建立一个索引一样,也为ORDER BY的字句中的字段建立一个索引:

CREATE INDEX mytable_categoryid_userid_adddate ON mytable (category_id,user_id,adddate);

  

注意: "mytable_categoryid_userid_adddate" 将会被截短为
"mytable_categoryid_userid_addda"

CREATE EXPLAIN SELECT * FROM mytable  WHERE category_id=1 AND user_id=2   ORDER BY adddate DESC; NOTICE: QUERY PLAN: Sort (cost=2.03..2.03 rows=1 width=16)  -> Index Scan using mytable_categoryid_userid_addda     on mytable (cost=0.00..2.02 rows=1 width=16)EXPLAIN

  看看EXPLAIN的输出,好象有点恐怖啊,数据库多做了一个我们没有要求的排序,这下知道性能如何受损了吧,看来我们对于数据库的自身运作是有点过于乐观了,那么,给数据库多一点提示吧。

  为了跳过排序这一步,我们并不需要其它另外的索引,只要将查询语句稍微改一下。这里用的是postgres,我们将给该数据库一个额外的提示--在ORDER BY语句中,加入where语句中的字段。这只是一个技术上的处理,并不是必须的,因为实际上在另外两个字段上,并不会有任何的排序操作,不过如果加入,postgres将会知道哪些是它应该做的。

EXPLAIN SELECT * FROM mytable WHERE category_id=1 AND user_id=2  ORDER BY category_id DESC,user_id DESC,adddate DESC;NOTICE: QUERY PLAN:Index Scan Backward using  mytable_categoryid_userid_addda on mytable   (cost=0.00..2.02 rows=1 width=16)EXPLAIN

  现在使用我们料想的索引了,而且它还挺聪明,知道可以从索引后面开始读,从而避免了任何的排序。

  以上说得细了一点,不过如果你的数据库非常巨大,并且每日的页面请求达上百万算,我想你会获益良多的。不过,如果你要做更为复杂的查询呢,例如将多张表结合起来查询,特别是where限制字句中的字段是来自不止一个表格时,应该怎样处理呢?我通常都尽量避免这种做法,因为这样数据库要将各个表中的东西都结合起来,然后再排除那些不合适的行,搞不好开销会很大。

  如果不能避免,你应该查看每张要结合起来的表,并且使用以上的策略来建立索引,然后再用EXPLAIN命令验证一下是否使用了你料想中的索引。如果是的话,就OK。不是的话,你可能要建立临时的表来将他们结合在一起,并且使用适当的索引。

  要注意的是,建立太多的索引将会影响更新和插入的速度,因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格,就没有必要为一个很少使用的where字句单独建立索引了,对于比较小的表,排序的开销不会很大,也没有必要建立另外的索引。

  以上介绍的只是一些十分基本的东西,其实里面的学问也不少,单凭EXPLAIN我们是不能判定该方法是否就是最优化的,每个数据库都有自己的一些优化器,虽然可能还不太完善,但是它们都会在查询时对比过哪种方式较快,在某些情况下,建立索引的话也未必会快,

例如索引放在一个不连续的存储空间时,这会增加读磁盘的负担,因此,哪个是最优,应该通过实际的使用环境来检验。

  在刚开始的时候,如果表不大,没有必要作索引,我的意见是在需要的时候才作索引,也可用一些命令来优化表,例如MySQL可用"OPTIMIZE TABLE"。

  综上所述,在如何为数据库建立恰当的索引方面,你应该有一些基本的概念了。

 

---------------------------------------------------------------

 

关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。还是以WordPress来说,其多个数据表都会对经常被查询的字段添加索引,比如wp_comments表中针对5个字段设计了BTREE索引。

一个简单的对比测试

以我去年测试的数据作为一个简单示例,20多条数据源随机生成200万条数据,平均每条数据源都重复大概10万次,表结构比较简单,仅包含一个自增ID,一个char类型,一个text类型和一个int类型,单表2G大小,使用MyIASM引擎。开始测试未添加任何索引。

执行下面的SQL语句:

 SELECT id,FROM_UNIXTIME(time) FROM article WHERE a.title=‘测试标题‘;

查询需要的时间非常恐怖的,如果加上联合查询和其他一些约束条件,数据库会疯狂的消耗内存,并且会影响前端程序的执行。这时给title字段添加一个BTREE索引:

ALTER TABLE article ADD INDEX index_article_title ON title(200);

再次执行上述查询语句,其对比非常明显:

MySQL索引的概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。上述SQL语句,在没有索引的情况下,数据库会遍历全部200条数据后选择符合条件的;而有了相应的索引之后,数据库会直接在索引中查找符合条件的选项。如果我们把SQL语句换成“SELECT * FROM article WHERE id=2000000”,那么你是希望数据库按照顺序读取完200万行数据以后给你结果还是直接在索引中定位呢?上面的两个图片鲜明的用时对比已经给出了答案(注:一般数据库默认都会为主键生成索引)。

索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

MySQL索引的类型

1. 普通索引

这是最基本的索引,它没有任何限制,比如上文中为title字段创建的索引就是一个普通索引,MyIASM中默认的BTREE类型的索引,也是我们大多数情况下用到的索引。

–直接创建索引CREATE INDEX index_name ON table(column(length))–修改表结构的方式添加索引ALTER TABLE table_name ADD INDEX index_name ON (column(length))–创建表的时候同时创建索引CREATE TABLE `table` (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),INDEX index_name (title(length)))–删除索引DROP INDEX index_name ON table

2. 唯一索引

与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。

–创建唯一索引CREATE UNIQUE INDEX indexName ON table(column(length))–修改表结构ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))–创建表的时候直接指定CREATE TABLE `table` (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),UNIQUE indexName (title(length)));

3. 全文索引(FULLTEXT)

MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。////对于较大的数据集,将你的资料输入一个没有FULLTEXT索引的表中,然后创建索引,其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表,生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

–创建表的适合添加全文索引CREATE TABLE `table` (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,`time` int(10) NULL DEFAULT NULL ,PRIMARY KEY (`id`),FULLTEXT (content));–修改表结构添加全文索引ALTER TABLE article ADD FULLTEXT index_content(content)–直接创建索引CREATE FULLTEXT INDEX index_content ON article(content)

4. 单列索引、多列索引

多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

5. 组合索引(最左前缀)

平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引:ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引:

–title,time

–title

为什么没有time这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引,如下面的几个SQL所示:

–使用到上面的索引SELECT * FROM article WHREE title=‘测试‘ AND time=1234567890;SELECT * FROM article WHREE utitle=‘测试‘;–不使用上面的索引SELECT * FROM article WHREE time=1234567890;

MySQL索引的优化

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。

1. 何时使用聚集索引或非聚集索引?

动作描述使用聚集索引使用非聚集索引
列经常被分组排序使用使用
返回某范围内的数据使用不使用
一个或极少不同值不使用不使用
小数目的不同值使用不使用
大数目的不同值不使用使用
频繁更新的列不使用使用
外键列使用使用
主键列使用使用
频繁修改索引列不使用使用

事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。其实这个具体用法我还不是很理解,只能等待后期的项目开发中慢慢学学了。

2. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3. 使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序

MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

5. like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

6. 不要在列上进行运算

例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。关于这一点可以围观:一个单引号引发的MYSQL性能损失。

最后总结一下,MySQL只对一下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引,不过除非是数据量真的很多,否则过多的使用索引也不是那么好玩的,比如我刚才针对text类型的字段创建索引的时候,系统差点就卡死了。

----------------------------------------------------------------------

 

建立索引的优缺点:

为什么要创建索引呢?

        这是因为,创建索引可以大大提高系统的性能。 
        第一、通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 
        第二、可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。 
        第三、可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 
        第四、在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 
        第五、通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

        也许会有人要问:增加索引有如此多的优点,为什么不对表中的每一个列创建一个索引呢?这种想法固然有其合理性,然而也有其片面性。虽然,索引有许多优点, 但是,为表中的每一个列都增加索引,是非常不明智的。

       这是因为,增加索引也有许多不利的一个方面:

        第一、创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。 

        第二、索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。如果要建立聚簇索引,那么需要的空间就会更大。 

        第三、当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

什么样的字段适合创建索引:

 索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。

       一般来说,应该在这些列上创建索引,例如:

       第一、在经常需要搜索的列上,可以加快搜索的速度; 

       第二、在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; 

       第三、在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度; 

       第四、在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的; 

       第五、在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 

       第六、在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

 

       建立索引,一般按照select的where条件来建立,比如: select的条件是where f1 and f2,那么如果我们在字段f1或字段f2上简历索引是没有用的,只有在字段f1和f2上同时建立索引才有用等。

什么样的字段不适合创建索引:

同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:

  第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,

并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 
       第二,对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,

在查询的结果中,结果集的数据行占了表中数据行的很大比 例,即需要在表中搜索的数据行的比例很大。

增加索引,并不能明显加快检索速度。 
       第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。 
       第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。

当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。

因此,当修改性能远远大于检索性能时,不应该创建索引。

创建索引的方法::

1、创建索引,例如 create index <索引的名字> on table_name (列的列表); 
      2、修改表,例如 alter table table_name add index[索引的名字] (列的列表); 
      3、创建表的时候指定索引,例如create table table_name ( [...], INDEX [索引的名字] (列的列表) );

查看表中索引的方法:

show index from table_name; 查看索引

索引的类型及创建例子::

1.PRIMARY KEY (主键索引)

MySQL> alter table table_name add primary key ( `column` )

2.UNIQUE 或 UNIQUE KEY (唯一索引)

mysql> alter table table_name add unique (`column`)

3.FULLTEXT (全文索引)

mysql> alter table table_name add fulltext (`column` )

4.INDEX (普通索引)

mysql> alter table table_name add index index_name ( `column` )

5.多列索引 (聚簇索引)

 mysql> alter table `table_name` add index index_name ( `column1`, `column2`, `column3` )

相关文章