MySQL之索引（二）

一、索引描述

1.1索引的优点

为什么要创建索引？这是因为，创建索引可以大大提高系统的查询性能。

第一、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二、可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。

第三、可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五、通过使用索引，可以在查询的过程中，使用查询优化器，提高系统的性能。

1.2索引的缺点

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

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

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

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

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

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。 一般来说，应该在具备下述特性的列上创建索引：

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

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

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

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

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

第六、在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。建立索引，一般按照 select 的 where 条件来建立，比如： select 的条件是 where f1 andf2，那么如果我们在字段 f1 或字段 f2 上建立索引是没有用的，只有在字段 f1 和 f2 上同时建立索引才有用等。

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

一般来说，不应该创建索引的这些列具有下述特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

二、索引的存储结构

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引，索引方式的案例如下：

 

为了加快Col2的查找，可以维护一个右边所示的二叉树查找，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

MYSQL官方对索引的定位：索引是帮助mysql高效获取数据的数据结构，即索引的本质就是数据结构（可以简单的理解微“排好序的快速查找数据结构”）。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引，其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树这种类型的索引之外，还有哈希索引（hashindex）等

 

索引结构：BTree索引、Hash索引、full-text全文索引、R-Tree索引，以下我们以BTree索引进行介绍：

 

初始化介绍：

一颗B+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块真实的数据存在于叶子结点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在与数据表中。

查找过程：

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常高。

 

三、索引的语法

3.1普通索引

3.1.1创建索引

#创建索引方式一
CREATE INDEX index_name ON table_name (column(length))
#创建索引方式二
ALTER TABLE table_name ADD INDEX index_name (column(length))
#创建索引方式三
CREATE TABLE table_name (
    id int not null auto_increment,
    title varchar(30) ,
    PRIMARYKEY(id) , 
    INDEX index_name (title(5))
)

3.1.2 查看索引

#查看索引方式一
SHOW INDEX FROM [table_name]
#查看索引方式二
SHOW KEYS FROM [table_name] # 只在 MySQL 中可以使用 keys 关键字。

3.1.3 删除索引

#删除索引方式一
DROP INDEX index_name ON talbe_name
#删除索引方式二
ALTER TABLE table_name DROP INDEX index_name
#删除索引方式三
ALTER TABLE table_name DROP PRIMARY KEY

3.2唯一索引

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

3.2.1 创建索引

#创建索引方式一
CREATE UNIQUE INDEX index_name ON table_name (column(length))
#创建索引方式二
ALTER TABLE table_name ADD UNIQUE index_name (column(length))
#创建索引方式三
CREATE TABLE table_name (
    id int not null auto_increment,
    title varchar(30) ,
    PRIMARY KEY(id) , 
    UNIQUE index_name (title(length))
)

注意：唯一索引创建的语法与普通索引创建的语法稍微有点区别，唯一所以多了一个UNIQUE关键字

3.3 全文索引（FULLTEXT ）

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

3.3.1 创建索引

#创建索引方式一
CREATE FULLTEXT INDEX index_name ON table_name(column(length))
#创建索引方式二
ALTER TABLE table_name ADD FULLTEXT index_name( column)
#创建索引方式三
CREATE TABLE table_name (
    id int not null auto_increment,
    title varchar(30) ,
    PRIMARY KEY(id) , 
    FULLTEXT index_name (title)
)

注意：唯一索引创建的语法与普通索引创建的语法稍微有点区别，唯一所以多了一个FULLTEXT关键字

3.4 组合索引（ 最左前缀 ）

CREATE TABLE article ADD INDEX index_name(column1(length), column1(length));

平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。例如上表中针对 title 和 time 建立一个组合索引：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。

建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：

• –title,time
• –title

为什么没有 time 这样的组合索引呢？这是因为 MySQL 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个 SQL 所示：
1，使用到上面的索引
SELECT * FROM article WHERE title='测试' AND time=1234567890;
SELECT * FROM article WHERE title='测试';
2，不使用上面的索引
SELECT * FROM article WHERE time=1234567890;