[Mysql] Mysql索引详解，看这一篇就够了

前言：
今天在这里总结一下Mysql索引，也给自己加个印象。
我的座右铭：即使记性再差，仍要坚持多读多看。

概念

Mysql索引的建立对于Mysql的高效运行是很重要的，索引可以大大提高Mysql的检索速度。
Mysql目前主要有以下5中索引类型：

• 普通索引
• 唯一索引
• 主键索引
• 组合索引
• 全文索引

缺点：
索引也有它的缺点，过多的滥用索引，也会降低更新表的速度。如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

5种索引类型

普通索引

这是最基本的索引，他没有任何限制。可以用如下方式创建：

• 直接创建索引

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 NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

唯一索引

与普通索引类似，不同的是：索引列的值必须唯一，但允许有空值。如果是组合索引，列值的组合必须唯一。

• 创建唯一索引

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 NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。联合索引遵循最左前缀集合。

alter table `table` add index name_city_age(name,city,age)

全文索引

主要用来查找文本中的关键字，而不是直接与索引的值相比较。
fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

存储引擎

什么是存储引擎

与其他数据库例如Oracle 和SQL Server等数据库中只有一种存储引擎不同的是，MySQL有一个被称为“Pluggable Storage Engine Architecture”(可替换存储引擎架构)的特性，也就意味着MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。MySQL数据库在实际的工作中其实分为了语句分析层和存储引擎层，其中语句分析层就主要负责与客户端完成连接并且事先分析出SQL语句的内容和功能，而存储引擎层则主要负责接收来自语句分析层的分析结果，完成相应的数据输入输出和文件操作。简而言之，就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。

存储引擎种类

平常实际开发种，就MyISAM和InnoDB使用比较多。

MyISAM简介

这种引擎是MySQL最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：

• 静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为 数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
• 动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
• 压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。
  当然不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：
• 选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
• 插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。
  MyISAM表是独立于操作系统的，这说明可以轻松地将其从Windows服务器移植到Linux服务器；每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。 例如我创建了一个【test】表，那么就会生成以下三个文件：
  

InnoDB引擎

InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 使用InnoDB是最理想的选择：

• 更新密集的表：InnoDB存储引擎特别适合处理多重并发的更新请求
• 事务：InnoDB存储引擎是支持事务的标准MySQL存储引擎
• 自动灾难恢复：与其它存储引擎不同，InnoDB表能够自动从灾难中恢复
• 外键约束：MySQL支持外键的存储引擎只有InnoDB
• 支持自动增加列AUTO_INCREMENT属性

小结

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

索引方式

聚集索引

聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引（这里不懂先放着，一会举例），每张表只能有一个聚集索引，聚集索引的叶子节点存储了整个行数据(即:一张表只能有一个聚集索引)。

解释：什么叫索引项的排序方式和表中数据记录排序方式一致呢？ 我们把一本字典看做是数据库的表，那么字典的拼音目录就是聚集索引，它按照A-Z排列。实际存储的字也是按A-Z排列的。这就是索引项的排序方式和表中数据记录排序方式一致。
对于Innodb，主键毫无疑问是一个聚集索引。但是当一个表没有主键，或者没有一个索引，Innodb会如何处理呢。请看如下规则:

如果一个主键被定义了，那么这个主键就是作为聚集索引。

如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。

如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

非聚集索引

非聚集索引：非聚集索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，还存储了一个指向改行数据的聚集索引建的书签。

最左原则

索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。 (1)匹配全值(Match the full value)：对索引中的所有列都指定具体的值。例如，上图中索引可以帮助你查找18岁的李安 (2)匹配最左前缀(Match a leftmost prefix)：你可以利用索引查找年龄为21的人，仅仅使用索引中的第1列。 (3)匹配列前缀(Match a column prefix)：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。 (4)匹配值的范围查询(Match a range of values)：可以利用索引查找年龄在21到30之间的人，仅仅使用索引中第1列。 (5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)：可以利用索引查找年龄为21，而姓氏以字母K开始的人。 (6)仅对索引进行查询(Index-only queries)：如果查询的列都位于索引中，则不需要读取元组的值。 由于B-树中的节点都是顺序存储的，所以可以利用索引进行查找(找某些值)，也可以对查询结果进行ORDER BY。当然，使用B-tree索引有以下一些限制： (1) 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。 (2) 不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。 (3) 存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE age=21 AND first_name LIKE ‘李%’ AND Name=‘安’，则该查询只会使用索引中的前两列，因为LIKE是范围查询。