MySQL index简介

索引简介

索引用于快速找出在某个列中有一特定值的行

1

2^1

2^2

…

2^32 4294967296

42亿的数据只需要寻找32次

通常我们说的索引不出意外指的就是B树索引，InnoDB的BTREE索引，实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引。

btree索引

B和B+树的区别在于，B+树的非叶子结点只包含导航信息，不包含实际的值

B 树

一颗m阶的B树定义如下：

1）每个结点最多有m-1个关键字。

2）根结点最少可以只有1个关键字。

3）非根结点至少有Math.ceil(m/2)-1个关键字。

4）每个结点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于等于它，而右子树中的所有关键字都大于等于它。

5）所有叶子结点都位于同一层，或者说根结点到每个叶子结点的长度都相同。

hash索引

简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

特点

1. hash索引只是hash值顺序排列，跟表数据没有关系，无法应用于order by；

2. hash索引是对它的所有列计算哈希值，因此在查询时，必须带上所有列，比如有(a, b)哈希索引，查询时必须 where a = 1 and b = 2，少任何一个不行；

3. hash索引只能用于比较查询 = 或 IN，其他范围查询无效，本质还是因不存储表数据；

4. 一旦出现碰撞，hash索引必须遍历所有的hash值，将地址所指向数据一一比较，直到找到所有符合条件的行,所以在有大量重复键值情况下，哈希索引的效率也是极低的。

总结：

优点：可以快速检索，减少I/O次数，加快检索速度；（btree）根据索引分组和排序，可以加快分组和排序

缺点：维护成本、占空间、影响写入速度

添加、删除索引

1、创建表时创建索引

CREATE TABLE article (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
UNIQUE |FULLTEXT|SPATIAL index idx_1(title,body(20)) using btree/hash
) TYPE=innodb;

不重复率：select count(distinct a)/count(*) from table;值越接近1，索引区分度越高

2、通过alter table 添加

ALTER TABLE student ADD UNIQUE|FULLTEXT|SPATIAL INDEX idx_1 (name) using btree/hash

3、通过create index 添加

CREATE UNIQUE|FULLTEXT|SPATIAL INDEX idx_1 ON student (name(20)) using btree/hash

删除索引：

DROP INDEX full_idx_name ON table_name ;

ALTER TABLE table_name drop INDEX ft_email_abcd;

explain

1. id：

包含一组数字，表示查询中执行select子句或操作表的顺序

Example（id相同，执行顺序由上至下）

Example （如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行）

Example（id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行）

2. select_type

表示查询中每个select子句的类型（简单OR复杂）

3.table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集

4.type

依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引
B：const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。
C：eq_ref：唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配
D：ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。
I：range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
J：index_merge：表示索引合并，即查询使用了两个以上的索引，最后取交集或者并集，常见or的条件使用了不同的索引。
K：index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
L：all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
5）、possible_keys

查询可能使用到的索引都会在这里列出来
6）、key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
7）、key_len

用于处理查询的索引长度，可以查看复合索引的使用情况
8）、ref

显示索引的哪一列被使用了，有时候会是一个常量，如果值是func，则使用的值是某个函数的结果
9）、rows

这里是执行计划中估算的扫描行数，不是精确值
10）、extra

这个列可以显示的信息非常多，有几十种，常用的有
A：using index：该值表示相应的select操作中使用了覆盖索引（Covering Index），查询时不需要回表查询，直接通过索引就可以获取查询的数据。
B：using where：在查找使用索引的情况下，需要回表去查询所需的数据
C：using temporary：表示使用了临时表存储中间结果。
D：using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
普通索引

包含一列的索引称为单列索引，多列的称为复合索引，因为BTREE索引是顺序排列的，所以比较适合范围查询，但是在复合索引中，还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响

唯一索引

创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复。唯一索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE，把它定义为一个唯一索引。

alter ignore table test add unique key uk_id (id);

ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘uk_id’）

set old_alter_table = 1

全文索引

旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。

不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引

SELECT * FROM student WHERE MATCH(name) AGAINST(‘聪’)

空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。对于InnoDB和MyISAM表，MySQL可以使用与创建常规索引类似的语法创建空间索引，但是使用空间关键字。空间索引中的列必须声明为NOT NULL。

新增数据类型GEOMETRY等空间类型 GEOMETRY可以存储任何类型的几何值。

具有空间数据类型的列可以具有SRID属性，以便为存储在列中的值显式地指示空间参考系统(SRS)。例如:

CREATE TABLE geom ( p POINT SRID 0, g GEOMETRY NOT NULL SRID 4326 );

如果空间列不为空，并且具有特定的SRID，那么可以在空间列上创建空间索引，因此如果计划索引列，则使用NOT NULL和SRID属性声明列:

CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));

ALTER TABLE geom ADD SPATIAL INDEX(g);

CREATE SPATIAL INDEX g ON geom (g);

使用特定的MBRContains()或MBRWithin()等函数查询

主键索引

主键索引唯一且非空，InnoDB自动对它建立了索引（primary key），对于非主键字段上建立的索引，又称辅助索引，索引排列也是顺序排列，只是它还附带一个本条记录的主键值的数据域，不是指向本数据行的指针，在使用辅助索引查找时，先找到对应这一列的索引值，再根据索引节点上的另一个数据域—主键值，来查找该行记录，即每次查找实际经过查找了两次。

聚簇索引(存储结构)

不是一种单独的索引类型，而是一种数据存储方式。innodb的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行。

当表有聚簇索引时，数据行实际上是存储在索引的叶子页中。

聚簇：表示数据行和相邻的键值紧凑地存储在一起。一个表只能有一个聚簇索引

聚簇结构的特点：

根据主键查询条目时,不用回行(数据就在主键节点下)
如果碰到不规则数据插入时,造成频繁的页分裂
页分裂：

页（也可以称为块），是innodb磁盘管理的最小单位

在mysql中，这里的页可理解为块存储空间，即索引的树节点是存放在页中的，每一页（称为逻辑页）有固定大小，InnoDB目前是16kb，一页用完了，当继续插入表生成新的索引节点时，就去新的页中存储这个节点，再有新的节点就继续放在这个新的页的节点后面。一页总要被存满，然后新开一页继续，这种行为被称作页分裂

因为聚簇索引采用的是平衡二叉树算法，而且每个节点都保存了该主键所对应行的数据，假设插入数据的主键是自增长的，那么根据二叉树算法会很快的把该数据添加到某个节点下，而其他的节点不用动；但是如果插入的是不规则的数据，那么每次插入都会改变二叉树之前的数据状态。从而导致了页分裂。

使用auto_increment自增列。这样可以保证数据行是按顺序写入，对于主键做关联操作的性能也会更好

如果非自增或不是整数索引，如非自增整数、类似MD5的字符串，以他们作为索引值时，因为待插入的下一条数据的值不一定比上一条大，甚至比当前页所有值都小，需要跑到前几页去比较而找到合适位置，InnoDB无法简单的把新行插入到上一行后面，而找到并插入索引后，可能导致该页达到分裂因子阀值，需要页分裂，进一步导致后面所有的索引页的分裂和排序，数据量小也许没什么问题，数据量大的话可能会浪费大量时间，产生许多碎片。

分裂规则：

1.按照原页面中50%的数据量进行分裂，针对当前这个分裂操作，3，4记录保留在原有页面，5，6记录，移动到新的页面。最后将新纪录7插入到新的页面中；

2.新的分裂策略，在插入7时，不移动原有页面的任何记录，只是将新插入的记录7写到新页面之中；

在InnoDB的实现中，为每个索引页面维护了一个上次插入的位置，以及上次的插入是递增/递减的标识。根据这些信息，InnoDB能够判断出新插入到页面中的记录，是否仍旧满足递增/递减的约束，若满足约束，则采用优化后的分裂策略；若不满足约束，则退回到50%的分裂策略。

索引有效性：

在BTREE索引的使用上，以下几种情况可以用到该索引或索引的一部分（使用explain简单查看使用情况）：

1.全值匹配

2.匹配最左列，对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列

（a,b,c）a,ab,abc

yes:

select * from test where a=1

select * from test where a=1 and b>1

select * from test where a=1 and b=1 and c=1

no:

select * from test where b=10

select * from test where c=10

差：

select * from test where a=10 order by c

select * from test where a=10 and b>10 and c=1

3.匹配列前缀，即一个索引中列的前一部分

select * from test where a like ‘haha%’;

4.匹配范围

select * from test where a>10

索引覆盖

覆盖索引只是在查询时，要查询的列刚好与使用的索引列完全一致，mysql直接扫描索引，然后就可返回数据，大大提高效率，因为不需再去原表查询、过滤，这种形式下的索引称作覆盖索引

explain select id4,id3,id5 from b where id4>12

explain select id4,id3 from b where id4>12

常见问题：

1.为什么简单的查询没有用到索引？

explain select * from t_desc_all where jiqun=‘bigdata_cloudera’ or port=‘4000’

explain select * from t_desc_all where jiqun=‘bigdata_cloudera’ or port=4000 #书写不规范导致索引失效

2.需要给每个where条件都加索引吗？ no

3.一条语句只能使用一个索引吗？ A:NO

索引合并

index merge 技术如果简单的说，其实就是：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)

4.mysql列书写顺序是否影响 A:没有

explain select * from t_desc_all where jiqun=‘bigdata_cloudera’ and table_name=‘4000’

explain select * from t_desc_all where table_name=‘4000’ and jiqun=‘bigdata_cloudera’

5.不同索引的自动选择？

优化器会自动选择辨识度较高的索引进行

explain select * from t_desc_all where jiqun=‘bigdata_cloudera’ and port=‘4000’

explain select * from t_desc_all where jiqun=‘bigdata_cloudera’ and table_name=‘4000’

6.前缀索引的长度限制？

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

前缀支持和前缀长度依赖于存储引擎。例如，对于InnoDB表，前缀长度可以达到767字节，如果启用innodb_large_prefix选项，前缀长度可以达到3072字节。对于MyISAM表，前缀长度限制为1000字节

7.索引越多越好？ no

维护索引成本，影响写入

8.为什么有的查询应该不会用到索引却实际用了索引

因为可能用到了索引覆盖

9.任何字段都可以加索引吗？ no

只应在区分度高的列上加索引

select count(distinct a)/count(*) from table;

show index from table;