mysql索引

一、概述

索引实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

索引是数据库性能调优技术的基础，用于实现数据的快速检索。
在mysql中，通常有以下两种方式访问数据库表的行数据：
1）顺序访问
在表中实行全表扫描，逐行遍历。实现方式比较简单，但是当表中有上千万数据量时，效率低下。
2）索引访问
通过遍历索引来直接访问表中记录行的方式。表创建索引后，查找数据可以直接根据列上的索引找到对应记录行的位置，从而快捷地找到数据。

二、索引存储与分类

索引类型与存储引擎有关，每种存储引擎所支持的索引类不一定完全相同。

mysql常用的索引在物理上分为以下两类:

1）B-树索引

BTREE索引，目前大部分的索引都是采用B-树索引方式来存储的。

表中的每一行都会在索引上有一个对应值。
可进行全键值，键值范围和键值前缀查询，也可对查询结果进行order by 排序。
查询必须从索引的最左边的列开始。 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。 存储引擎不能使用索引中范围条件右边的列。

2)哈希索引
mysql仅有memory和heap引擎支持这类索引。

任意长度的输入，通过散列算法变成固定长度输出，得到散列值即哈希值。
依据索引列对应的哈希值的方法获取表的记录行。
访问速度快
散列计算比较耗时，即建立哈希索引耗时。
不能使用哈希索引排序
不支持键的部分匹配
只支持等值比较

三、索引创建

1）使用create index
在已有的表上创建，但是不能创建主键

create index 索引名 on 表名(列名1,...) asc|desc

2）使用create table

在建新表的同时创建该表主键
constraint primary key 索引类型 列名

在建新表的同时创建该表的索引
key | index 索引名 索引类型 列名

在建新表的同时创建该表的唯一性索引
unique index|key 索引名 索引类型 列名

在建新表的同时创建该表的外键
foreign key 索引名 列名

3）使用alter table
add primary key 索引类型 列名
add unique index|key 索引名 索引类型 列名
add foreign key 索引名 列名

四、索引查看

show index from 表名 [from 数据库名]

五、索引删除

drop index 索引名  on 表名

六、索引进阶

在经常搜索的列上创建索引
在经常使用表连接的列创建索引
在经常根据范围进行搜索的列创建索引
在经常使用where子句中的列上创建索引

不需要建索引的情况：
很少查询的列
很少数据值的列（例如性别）
定义为text,image,bit数据类型的列