索引介绍及mysql索引类型

目录

1.索引的作用？为什么能够提高查询速度？

2.索引有什么副作用吗？

3.Mysql索引存储方式

4.MySQL索引类型：

总结

1.索引的作用？为什么能够提高查询速度？

索引就是通过事先排好序，从而在查找时可以应用比如二分查找等高效率的算法。
一般的顺序查找，复杂度为O(n)，而二分查找复杂度为O(log2n)。当n很大时，二者的效率相差及其悬殊。

举个例子：
表中有一百万条数据，需要在其中寻找一条特定id的数据。如果顺序查找，平均需要查找50万条数据。而用二分法，至多不超过20次就能找到。二者的效率差了2.5万倍！

在一个或者一些字段需要频繁用作查询条件，并且表数据较多的时候，创建索引会明显提高查询速度，因为可由全表扫描改成索引扫描。（无索引时全表扫描也就是要逐条扫描全部记录，直到找完符合条件的，索引扫描可以直接定位）

2.索引有什么副作用吗？

（1）索引是有大量数据的时候才建立的，没有大量数据反而会浪费时间，因为索引是使用二叉树建立.

（2）当一个系统查询比较频繁，而新建，修改等操作比较少时，可以创建索引，这样查询的速度会比以前快很多，同时也带来弊端，就是新建或修改等操作时，比没有索引或没有建立覆盖索引时的要慢。

（3）索引并不是越多越好，太多索引会占用很多的索引表空间，甚至比存储一条记录更多。
对于需要频繁新增记录的表，最好不要创建索引，没有索引的表，执行insert、append都很快，有了索引以后，会多一个维护索引的操作，一些大表可能导致insert 速度非常慢。

索引实现方式：hash哈希散列法，二叉树，红黑树，b树，b+树，......

B+树的特征：

非叶节点只存储索引信息，不存储数据；

叶子节点最右边的指针指向下一个相邻的叶子节点没所有叶子节点组成了一个有序链表

B+树的优势：

1.单一节点存储更多的元素，使得查询的IO次数更少。

2.所有查询都要查找到叶子节点，查询性能稳定。

3.所有叶子节点形成有序链表，便于范围查询。

3.Mysql索引存储方式

mysql引擎分类：MyISAM（非聚集索引方式），InnoDB（聚集索引方式），......

MyISAM：不支持事务，仅支持表级别的锁；

InnoDB：支持事务，支持行级别的锁。

1) MyISAM

在 MySQL 5.1 版本及之前的版本，MyISAM 是默认的存储引擎。
MyISAM 存储引擎不支持事务和外键，所以访问速度比较快。如果应用主要以读取和写入为主，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择 MyISAM 存储引擎是非常适合的。
MyISAM 是在 Web 数据仓储和其他应用环境下最常使用的存储引擎之一。

2) InnoDB

MySQL 5.5 版本之后默认的事务型引擎修改为 InnoDB。
InnoDB 存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比 MyISAM 存储引擎占用更多的磁盘空间。
如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。
InnoDB 存储引擎除了可以有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

3) MEMORY

MEMORY 存储引擎将所有数据保存在 RAM 中，所以该存储引擎的数据访问速度快，但是安全上没有保障。
MEMORY 对表的大小有限制，太大的表无法缓存在内存中。由于使用 MEMORY 存储引擎没有安全保障，所以要确保数据库异常终止后表中的数据可以恢复。
如果应用中涉及数据比较少，且需要进行快速访问，则适合使用 MEMORY 存储引擎。

可以明显看出，innodb引擎的文件比myisam的大很多，因为innodb引擎方式是聚合的，即把索引和数据放在一起。

MyISAM：

存储文件分为*.MYD（数据文件）和*.MYI（索引文件），查找时会先根据索引查找到数据指针的地址值：

创建一个表：

+----+-------+
| id | name  |
+----+-------+
|  1 |  a    |
|  2 |  b    |
|  3 |  c    |
|  4 |  d    |
 ...

以id为索引查找：

以username为索引查找：存的是内容，同样根据物理地址在数据文件里面查找

这里，如果重复内容比较多，比如

+----+-------+
| id | name  |
+----+-------+
|  1 |  a    |
|  2 |  b    |
|  3 |  c    |
|  4 |  d    |
|  5 |  d    |
|  6 |  d    |
|  6 |  d    |
 ...

以username为索引会导致性能没有完全发挥，不如用主键id为索引

InnoDb：存储文件为*.idb，直接查找到数据：

以id为主键时(主键索引)：

以username为主键时（非主键索引）：

存储的是主键的值（id），然后根据id查找内容

通过主键索引查询数据时，我们只需查找主键索引树便可以获取数据；通过非主键索引查询数据时，我们先通过非主键索引树查找到主键值，然后再在主键索引树搜索一次，这个过程称为回表，也就是说非主键索引查询会比主键查询多搜索一棵树。所以我们应尽可能使用主键查询。

4.MySQL索引类型：

1、普通索引

最基本的索引，它没有任何限制，用于加速查询。

创建方法：

ａ.　建表的时候一起创建

CREATE TABLE mytable (
name VARCHAR(32) ,
INDEX index_mytable_name (name)
);

b. 建表后，直接创建索引

CREATE INDEX index_mytable_name ON mytable(name);

c. 修改表结构

ALTER TABLE mytable ADD INDEX index_mytable_name (name);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

2、唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建方法：

ａ.　建表的时候一起创建

CREATE TABLE mytable (
`name` VARCHAR(32) ,
UNIQUE index_unique_mytable_name (`name`)
);

b. 建表后，直接创建索引

CREATE UNIQUE INDEX index_mytable_name ON mytable(name);

c. 修改表结构

ALTER TABLE mytable ADD UNIQUE INDEX index_mytable_name (name);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

3、主键索引

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

创建方法：

ａ.　建表的时候一起创建

CREATE TABLE mytable (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(32) ,
PRIMARY KEY (`id`)
);

b. 修改表结构

ALTER TABLE test.t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

4、组合索引

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

创建方法：

ａ.　建表的时候一起创建

CREATE TABLE mytable (
`id` int(11) ,
`name` VARCHAR(32) ,
INDEX index_mytable_id_name (`id`,`name`)
);

b. 建表后，直接创建索引

CREATE INDEX index_mytable_id_name ON mytable(id,name);

c. 修改表结构

ALTER TABLE mytable ADD INDEX index_mytable_id_name　(id,name);

5、全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。

它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

创建方法：

ａ. 建表的时候一起创建

CREATE TABLE `article` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(250) NOT NULL ,
`contents` text NULL ,
`create_at` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (contents)
);

b. 建表后，直接创建索引

CREATE FULLTEXT INDEX index_article_contents ON article(contents);

c. 修改表结构

ALTER TABLE article ADD FULLTEXT INDEX index_article_contents　(contents);

总结

虽然索引可以增加查询数据，但对于更新、创建或者删除的时候，需要去维护索引，导致性能会受影响，因此，索引也不能建立太多。