mysql索引详解以及使用案例

在mysql当中，索引的重要性不言而喻，直接影响到项目的效果，那现在，我就和大家分享一下关于索引的知识

什么是索引

索引是一个单独的，存储在磁盘上的数据结构，他们包含着对数据表里所有记录的引用指针，所有mysql列类型都可以被索引，同时也是提高查询速度的主要手段。
索引是在储存引擎中实现的，因此每种储存引擎的索引都不一样，也不是每种储存引擎都支持所有的索引类型。
mysql中索引的储存类型有两种：1,BTREE 2,HASH
MyISAM和InnoDB存储引擎仅支持BTREE索引，而MEMORY和HEAP二种都支持。

索引的优点

1，加快查询速度，这也是最重要的一点
2，在参考数据完整性的方面，可以加速表与表之间的连接
3，在分组与排序查询时，显著减少查询中分组与排序的时间

索引的缺点

1，创建与维护索引都要耗费时间，并且随着数据量的增加所耗费的时间也会增加
2，索引需要占据磁盘的空间，除了数据表占数据空间之外，每一个索引还要占用一定的物理空间，索引文件可能比数据文件更快的达到最大文件尺寸
3，当表中存在索引，对表的增删改操作速度会受到影响，索引也要动态的维护，这样就降低了数据维护的速度

索引的分类

1，普通索引：普通索引是mysql的基本索引类型，允许重复值和空值的存在
2，唯一索引：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。
3，主键索引：主键索引是一种特殊的唯一索引，不允许有空值
4，单列索引：一个索引只包含单个列，一个表可以有多个单列索引
5，组合索引：也称为联合索引，即指在表的多个字段组合上创建的索引，必须要遵循最左前缀原则
6，全文索引：FULLTEXT，支持在定义索引的列上进行全文查找，并允许重复和空值的存在，仅MyISAM存储引擎支持全文索引
7，空间索引：也仅有MyISAM支持，mysql的空间数据类型有四种：GEOMETRY,POINT,LINESTRING,POLYGON,mysql使用SPATIAL关键字进行扩展，创建此索引的列值不能为空

创建索引时应注意的点：

1，索引并不是越多越好，要根据业务场景进行选择性使用
2，常更新的表避免创建过多的索引，常查询的表避免将所有添加到不必要的字段
3，数据量小的表最好不要使用索引，可能遍历索引的时间都比查询的时间长
4，查询的多的字段也不要盲目添加索引，例如性别字段上，没有效果的同时还会影响数据插入的速度
5，对频繁排序和分组的列上要创建索引，如果待排序的列有多个，可以在这些列上建立组合索引

创建索引的方法1，建表时创建

一，创建普通索引

CREATE TABLE myindx(
id INT,
name VARCHAR(20),
INDEX(name)
);

我们来看看表结构

SHOW CREATE TABLE myindx;

CREATE TABLE `myindx` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

可以看到，成功建立了索引
插入一条数据

INSERT myindx set id=1,name='haha';

然后我们用explain关键字来查看一下我们的查询语句

EXPLAIN SELECT * FROM myindx WHERE name='haha';

可能有人看不懂这结果，我来给大家讲一下这个参数
1，select_type：指的是查询的类型，SIMPLE代表简单查询，没有使用union和子查询。除了simple，还有primary，union，subquery等查询类型

2，table：读取的数据库里的名称

3，type：显示查询使用了何种类型。
ref：查找非唯一性索引，返回匹配某一条件的多条数据。属于精确查找、数据返回可能是多条
eq_ref： 查找唯一性索引，返回的数据至多一条。属于精确查找
const：查找主键索引，返回的数据至多一条（0或者1条）。 属于精确查找
system：const的特例，仅返回一条数据的时候。
range：查找某个索引的部分索引，一般在where子句中使用 < 、>、in、between等关键词。只检索给定范围的行，属于范围查找
index：查找所有的索引树，比ALL要快的多，因为索引文件要比数据文件小的多。
all：不使用任何索引，进行全表扫描，性能最差。

4，possible_keys：mysql在搜索时可以选用的各个索引

5，key：mysql实际选用的索引

6，key_len：给出按字节计算的索引长度，值越小，查询速度越快

7，ref：如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

8，row：这是mysql估算的需要扫描的行数（不是精确值），这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

9，extra：EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:
distinct：在select部分使用了distinct关键字
Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
Using index：“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
Using index;Using where：首先存储引擎通过索引检索将检索结果返回（仍然不需要回表），然后在Server层再通过where语句对检索结果进行过滤。例如：select id from test where id > 5;到此我们可以发现，它和Using index的区别是多了一次Server层的where过滤操作。很明显，该过滤不需回表，因而效率很高。
Using where：MySQL将对存储引擎层提取的结果进行过滤，过滤条件字段无索引。例如：select * from test where age > 30; 所以，Using where本身其实和是否使用索引无关，它表示的是Server层对存储引擎层返回的数据所做的过滤。当然该过滤可能会回表，也可能不会回表，取决于查询字段是否被索引覆盖。

现在可以看到，key中有name，证明查询时使用了name索引

二，创建唯一索引

CREATE TABLE myindx(
id INT NOT NULL,
name VARCHAR(20),
UNIQUE INDEX myid(id)
);

查看表结构

CREATE TABLE `myindx` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  UNIQUE KEY `myid` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

可以看到，表中已经创建了名字为‘myid’的唯一索引

三，创建单列索引

CREATE TABLE myindx(
id INT NOT NULL,
name VARCHAR(30),
INDEX myname(name(20))
);

表结构

CREATE TABLE `myindx` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  KEY `myname` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

上面可以看到，已经建立了一个索引长度为20的，名为myname的单列索引，

在这里插入代码片

四，创建组合索引
组合索引是在多个字段上创建一个索引

CREATE TABLE myindx(
id INT,
name VARCHAR(20),
age SMALLINT,
info VARCHAR(255),
INDEX multindx(id,name,age)
);

表结构

CREATE TABLE `myindx` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` smallint(6) DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `multindx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

可以看到，一个名为multindx的组合索引已经创建起来了
插入一条数据

INSERT myindx set id=1,name='haha',age=18,info='sdbanjsbda';

五，创建全文索引

CREATE TABLE myfull(
id INT,
content LONGTEXT,
FULLTEXT INDEX fullindx(content)
)ENGINE=MyISAM;

表结构

CREATE TABLE `myfull` (
  `id` int(11) DEFAULT NULL,
  `content` longtext,
  FULLTEXT KEY `fullindx` (`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

可以看到，全文索引已经被创建，适合大字段使用

六，创建空间索引

CREATE TABLE myspa(
g GEOMETRY NOT NULL,
SPATIAL INDEX myspatial(g)
)ENGINE=MyISAM;

表结构

CREATE TABLE `myspa` (
  `g` geometry NOT NULL,
  SPATIAL KEY `myspatial` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

注意，也只有在MyISAM引擎下才可以创建空间引擎，并注意，字段一定要为非空