MYSQL 索引

  所有MySQL列类型可以被索引。根据存储引擎定义每个表的最大索引数和最大索引长度。

所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。索引的存储类型目前只有两种（btree和hash），具体和存储引擎模式相关

MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都 无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务

一：MySQL的btree索引和hash索引的区别

hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像btree(B-Tree)索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 hash 索引的查询效率要远高于 btree(B-Tree) 索引。

虽然hash索引效率高，但是hash索引本身由于其特殊性带来了很多的限制和弊端，如下：

a:hash索引仅仅能满足=,<=>,IN,IS NULL或者IS NOT NULL查询，不能使用范围查询。

b:hash索引无法被用来避免数据的排序操作

c:hash索引不能利用部分索引键查询

d:hash索引在任何时候都不能避免表扫描

e:hash索引遇到大量hash值相等的情况后性能并不一定会比B-Tree索引高

B-Tree索引是MySQL数据库中使用最频繁的索引类型，这主要是因为B-Tree索引的存储结构在数据库的数据检索中有非常尤异的表现。

    一般来说， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node ，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。

如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree ，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息，这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。 

    在 Innodb 存储引擎中，存在两种不同形式的索引，一种是 Cluster 形式的主键索引（ Primary Key ），另外一种则是和其他存储引擎（如 MyISAM 存储引擎）存放形式基本相同的普通 B-Tree 索引，这种索引在 Innodb 存储引擎中被称为 Secondary Index 。

    在 Innodb 中如果通过主键来访问数据效率是非常高的，而如果是通过 Secondary Index 来访问数据的话， Innodb 首先通过 Secondary Index 的相关信息，通过相应的索引键检索到 Leaf Node之后，需要再通过 Leaf Node 中存放的主键值再通过主键索引来获取相应的数据行。

MyISAM 存储引擎的主键索引和非主键索引差别很小，只不过是主键索引的索引键是一个唯一且非空 的键而已。而且 MyISAM 存储引擎的索引和 Innodb 的 Secondary Index 的存储结构也基本相同，主要的区别只是 MyISAM 存储引擎在 Leaf Nodes 上面出了存放索引键信息之外

再存放能直接定位到 MyISAM 数据文件中相应的数据行的信息（如 Row Number ），但并不会存放主键的键值信息。

二：MySQL索引类型包括

（1）普通索引

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

a:创建索引
CREATE INDEX indexName ON mytable(username(10));         -- 单列索引
CREATE INDEX indexName ON mytable(username(10),city(10));   -- 组合索引
indexName为索引名，mytable表名，username和city为列名，10为前缀长度，即索引在该列从最左字符开始存储的信息长度，单位字节
如果是CHAR，VARCHAR类型，前缀长度可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 前缀长度，下同。

b:修改表结构来创建索引
ALTER TABLE mytable ADD INDEX indexName (username(10));
ALTER TABLE mytable ADD INDEX indexName (username(10),city(10));
此处 indexName 索引名可不写，系统自动赋名 username ，username_2 ，username_3，...

c:创建表的时候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
INDEX indexName (username(10)) INDEX indexName (username(10),city(10))
);

（2）唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式（仅仅在创建普通索引时关键字 INDEX 前加 UNIQUE）：

a:创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(10));

b:修改表结构来创建索引
ALTER TABLE mytable ADD UNIQUE INDEX indexName (username(10)); 也可简写成

ALTER TABLE mytable ADD UNIQUE indexName (username(10));

c:创建表的时候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
UNIQUE INDEX indexName (username(10))  也可简写成 UNIQUE indexName (username(10))
);

（3）主键索引

它是一种特殊的唯一索引，不允许有空值。在建表的时候同时创建的主键即为主键索引
主键索引无需命名，一个表只能有一个主键。主键索引同时可是唯一索引或者全文索引，但唯一索引或全文索引不能共存在同一索引

a：修改表结构来创建索引
ALTER TABLE mytable ADD PRIMARY KEY (id);

b:创建表的时候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
PRIMARY KEY(id) 
);

（4）全文索引

InnoDB存储引擎不支持全文索引

a:创建索引
CREATE FULLTEXT INDEX indexName ON mytable(username(10));

b:修改表结构来创建索引
ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10));也可简写成 

ALTER TABLE mytable ADD FULLTEXT indexName (username(10));

c:创建表的时候直接指定
CREATE TABLE mytable(
id INT,
username VARCHAR(16),
city VARCHAR(16),
FULLTEXT INDEX indexName (username(10)) 也可简写成 FULLTEXT indexName (username(10))
)ENGINE=MYISAM;

-- 建表时创建全文索引，要设置该表的存储引擎为MYISAM,新版mysql默认InnoDB存储引擎不支持全文索引

(5)删除索引

DROP INDEX indexName ON mytable;

注：
（1）MyISAM表的数据文件和索引文件是自动分开的

（2）InnoDB的数据和索引是存储在同一个表空间里面，但可以有多个文件组成

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。