MySql索引原理与使用大全

林炳文Evankaka原创作品。转载请注明出处http://blog.csdn.net/evankaka     

一、索引介绍

     索引是对数据库表中一列或多列的值进行排序的一种结构。在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。
        索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。在数据库关系图中，可以在选定表的“索引/键”属性页中创建、编辑或删除每个索引类型。当保存索引所附加到的表，或保存该表所在的关系图时，索引将保存在数据库中。

MySQL索引概述

        所有mysql列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

        在MySQL 5.1中，对于MyISAM和InnoDB表，前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

        还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部(前缀)索引。也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R-树。默认情况MEMORY(HEAP)存储引擎使用hash索引，但也支持B-树索引。

btree索引与hash索引

         对于BTREE和HASH索引，当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时，关键元素与常量值的比较关系对应一个范围条件。Hash索引还有一些其它特征：它们只用于使用=或<=>操作符的等式比较(但很快)。优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目）。MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表，会影响一些查询。只能使用整个关键字来搜索一行。(用B-树索引，任何关键字的最左面的前缀可用来找到行)。

        对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时，关键元素与常量值的比较关系对应一个范围条件。“常量值”系指：查询字符串中的常量、同一联接中的const或system表中的列、无关联子查询的结果、完全从前面类型的子表达式组成的表达式。

下面是一些WHERE子句中有范围条件的查询的例子。

下列范围查询适用于 btree索引和hash索引：

SELECT * FROM t1     WHERE key_col = 1     OR key_col IN (15,18,20);

 下列范围查询适用于btree索引

SELECT * FROM t1     WHERE key_col > 1     AND key_col < 10;
  SELECT * FROM t1     WHERE key_col LIKE 'ab%'     OR key_col BETWEEN 'bar' AND 'foo';

Mysql如何使用索引

          索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意如果你需要访问大部分行，顺序读取要快得多，因为此时我们避免磁盘搜索。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

二、使用方法

首先先创建一个表：

[sql]  view plain  copy

1. CREATE TABLE  
2.     t_student  
3.     (  
4.         STU_ID INT NOT NULL,  
5.         STU_NAME CHAR(10) NOT NULL,  
6.         STU_CLASS INT NOT NULL,  
7.         STU_SEX CHAR(2) NOT NULL,  
8.         STU_AGE INT NOT NULL,  
9.         PRIMARY KEY (STU_ID)  
10.     )  
11.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  

1.1 普通索引

创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

[sql]  view plain  copy

1. CREATE INDEX index_id  ON t_student(STU_ID);   

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构

[java]  view plain  copy

1. ALTER TABLE t_student ADD INDEX  index_name(STU_NAME(4)) ;  

创建表的时候直接指定

[sql]  view plain  copy

1. CREATE TABLE  
2.     t_student1  
3.     (  
4.         STU_ID INT NOT NULL,  
5.         STU_NAME CHAR(10) NOT NULL,  
6.         STU_CLASS INT NOT NULL,  
7.         STU_SEX CHAR(2) NOT NULL,  
8.         STU_AGE INT NOT NULL,  
9.         PRIMARY KEY (STU_ID),  
10.         INDEX index_name (STU_NAME(5))  
11.     )  
12.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  

删除索引的语法

[sql]  view plain  copy

1. DROP INDEX index_id  ON t_student;  
2. DROP INDEX index_name  ON t_student;  

结果

1.2、唯一索引

       主键就是唯一索引的一种，主键要求建表时指定，一般用auto_increment列，关键字是primary key。它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引

[sql]  view plain  copy

1. CREATE UNIQUE INDEX indexName ON mytable(username(length))   

修改表结构

[sql]  view plain  copy

1. ALTER mytable ADD UNIQUE [indexName] ON (username(length))   

创建表的时候直接指定

[java]  view plain  copy

1. CREATE TABLE mytable(  
2. ID INT NOT NULL,  
3. username VARCHAR(16) NOT NULL,  
4. UNIQUE [indexName] (username(length))  
5. );   

1. 3、多列索引

创建索引

[sql]  view plain  copy

1. CREATE  INDEX indexName ON mytable(username1(length),username2(length))   

CREATE INDEX index_age_aex ON t_student(STU_AGE,STU_SEX);

修改表结构

[sql]  view plain  copy

1. ALTER mytable ADD  [indexName] ON (username1(length),username2(length))   

创建表的时候直接指定

[java]  view plain  copy

1. CREATE TABLE mytable(  
2. ID INT NOT NULL,  
3. username VARCHAR(16) NOT NULL,  
4. INDEX [indexName] (username1(length),username2(length))  
5. );   

多列索引的一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用fname_lname_age索引： 
firstname，lastname，age
firstname，lastname
firstname
从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引： 
Select peopleid FROM people Where firstname='Mike' AND lastname='Sullivan' AND age='17'; 
Select peopleid FROM people Where firstname='Mike' AND lastname='Sullivan'; 
Select peopleid FROM people Where firstname='Mike'; 
下面这些查询不能够使用这个fname_lname_age索引： 
Select peopleid FROM people Where lastname='Sullivan'; 
Select peopleid FROM people Where age='17'; 
Select peopleid FROM people Where lastname='Sullivan' AND age='17'; 

1.4、全文索引

        全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里，我们就不追根究底其底层实现原理了，现在我们来看看在MySQL中如何创建并使用全文索引。
        在MySQL中，创建全文索引相对比较简单。例如，我们有一个文章表(article)，其中有主键ID(id)、文章标题(title)、文章内容(content)三个字段。现在我们希望能够在title和content两个列上创建全文索引，article表及全文索引的创建SQL语句如下：
--创建article表

[sql]  view plain  copy

1. CREATE TABLE article (  
2.  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,   
3.  title VARCHAR(200),  
4.  content TEXT,  
5.  FULLTEXT (title, content)  
6.  )ENGINE=MyISAM DEFAULT CHARSET=utf8;  

 

看看索引

上面就是在创建表的同时建立全文索引的SQL示例。此外，如果我们想要给已经存在的表的指定字段创建全文索引，同样以article表为例，我们可以使用如下SQL语句进行创建：
--给现有的article表的title和content字段创建全文索引
--索引名称为fulltext_article

[sql]  view plain  copy

1. ALTER TABLE article ADD FULLTEXT INDEX fulltext_article (title, content)  

     在MySQL中创建全文索引之后，现在就该了解如何使用了。我们必须使用特有的语法才能使用全文索引进行查询。例如，我们想要在article表的title和content列中全文检索指定的查询字符串，可以如下编写SQL语句：

[sql]  view plain  copy

1. SELECT * FROM article WHERE MATCH(title, content) AGAINST ('查询字符串');  

注意事项

搜索必须在类型为fulltext的索引列上，match中指定的列必须在fulltext中指定过
仅能应用在表引擎为MyIsam类型的表中（MySQL 5.6以后也可以用在Innodb表引擎中了）
仅能再char、varchar、text类型的列上面创建全文索引
像普通索引一样，可以在定义表时指定，也可以在创建表后添加或者修改
对于一个大数量级记录插入，向没有索引的表中插入数据后创建索引比向有索引的数据表中插入的过程要快很多
搜索字符串必须是一个常量字符串，不能是表的列名
在搜索记录的选择性超过50%的时候，认为没有匹配（只在自然搜索中限制）

1.5、验证是否使用是索引

些处接1.3.这里可以接上面多列索引，在这里我已经加了一些数据进去，如下

可以用语句 EXPLAIN SELECT * FROM t_student WHERE STU_AGE = 12;来验证是否使用到了索引

下面说明用到了索引

如果没用到索引，结果应该是如下：

上面只是验证是否使用了索引，接下来看来看看使用了索引和没使用索引的结果：

使用了索引的结果：（注意，这里添加了CREATE INDEX index_age_aex ON t_student(STU_AGE,STU_SEX);）

结果按SEX和AGE来进行排序

把索引给删除了，执行同样的语句：

结果直接按ID进行排序

1.6、使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

[sql]  view plain  copy

1. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。  
2. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。  
3. ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。  
4. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。  

以下实例为在表中添加索引。

[sql]  view plain  copy

1. ALTER TABLE testalter_tbl ADD INDEX (c);  

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

[sql]  view plain  copy

1. ALTER TABLE testalter_tbl DROP INDEX (c);  

使用 ALTER 命令添加和删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

[sql]  view plain  copy

1. ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;  
2. LTER TABLE testalter_tbl ADD PRIMARY KEY (i);  

你也可以使用 ALTER 命令删除主键：

[sql]  view plain  copy

1. ALTER TABLE testalter_tbl DROP PRIMARY KEY;  

删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例:

[sql]  view plain  copy

1. SHOW INDEX FROM table_name\G  

三、索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

四、使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

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

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

2.使用短索引

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

3.索引列排序

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

4.like语句操作

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

5.不要在列上进行运算

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:

select * from users where adddate<‘2007-01-01';

6.不使用NOT IN和<>操作