Mysql索引是什么

一 介绍

一般刚开始写代码的时候，是不太会注意索引的，甚至是意识不到sql优化，查询性能的。

一方面索引能极大的提高查询性能，另一方面索引建的过多或不合理，会对应用程序造成影响，所以程序员很有必要了解它!有些开发人员在DBA反馈慢sql的时候，才意识到建索引，甚至觉得是DBA优化的工作，这其实是一个误区，我们应该再一开始设计的时候，根据可能的数据流添加索引。

我们接下来探讨一下Mysql的索引。由于InnoDB支持事物，是很多OLTP应用的首选，而InnoDB支持3种常见索引：

1.B+索引

2.全文索引

3.哈希索引

其中B+索引时最常见也是最有效的关系型数据库索引，而全文索引只支持英文，对中文的分词效果并不好；哈希索引属于数据库InnoDB引擎层面的，用户干预不了

二 数据结构

三 B+索引

数据库中的B+索引时基于B+树的，其实就是B+树在数据库中的实现；其中又分为

聚集索引：

内部是B+树，叶子节点存放所有数据，并且是完整的一整行信息

InnoDB是索引组织表，即表中数据按照主键顺序存放。而聚集索引是按照每张表的主键构造B+树，同时叶子节点存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。实际上一张表只能有一个聚集索引，而查询优化器页倾向与采用聚集索引，因为能够再B+树叶子节点上直接找到数据。

而且由于B+树是主键排序的，能够很快的进行范围查找。

那可能就有疑惑，如果表沒有主键呢？这就涉及到InnoDB对聚集索引的选择

1.如果有主键，自动选择主键

2.如果无主键，选择第一个唯一非空索引

3.如果1，2都不满足，默认生成一个6字节的隐藏自增序列

辅助索引：

内部是B+树，叶子节点存放所有数据，但不是完整的一整行信息。那么到底存放什么呢，一般辅助索引存放的是主键+索引列的值。比如表t

CREATE TABLE test1(
id int not null,
age int not null,
PRIMARY key (id),
INDEX idx_age(age)
) ENGINE=INNODB CHARSET=utf8

列age有一个辅助索引(普通索引),那么索引上存放的是test1表的主键id值+列age的值。当通过idx_age来查找数据时，InnoDB会遍历索引idx_age并通过叶级别的指针找到主键索引的主键id，然后再通过主键索引找到一个完整的行记录，所以辅助索引查找效率一般比聚集索引低。(也有例外的情况，就是覆盖索引，后面再介绍)

四 索引的管理

方式1：alter table

ALTER TABLE tb_name
ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option]

ALTER TABLE tb_name
DROP PRIMARY key
| DROP {INDEX|KEY} index_name

看一个例子

首先建一个表test2，不添加任何索引

CREATE TABLE test2(
id INT NOT NULL,
name VARCHAR(255) not null,
age int not null
) ENGINE=INNODB CHARSET=utf8;

添加一个主键索引到id上

ALTER TABLE test2
ADD PRIMARY KEY idx_name (id);

查看一下索引

说明创建成功

删除索引

ALTER TABLE test2 
DROP PRIMARY key;

方式2：create /drop index

建表时创建索引

CREATE TABLE test2(
id INT NOT NULL,
name VARCHAR(255) not null,
age int not null,
PRIMARY KEY (id) -- 默认主键索引
) ENGINE=INNODB CHARSET=utf8;

CREATE INDEX idx_name  ON test2(name);

添加联合索引

CREATE INDEX idx_name_age ON test2(name, age);

删除索引

DROP INDEX idx_name ON test2;

索引的查看

SHOW INDEX FROM tb;

查看索引里面会有以下几个属性

Table:索引所在的表名

Non_unique:是否唯一索引，0-是，1-否

Key_name:索引名称，PRIMARY-主键索引，用户可以通过这个名称来drop index

Seq_in_index:列在索引中的位置;对于联合索引idx_name_age,name在前，所以是1，age在后，为2

Column_name:索引的列名称

Collation:列在索引中的存储方式。B+索引是A，Hash索引是NULL

Cardinality:索引中唯一值的数目估计值，这个值应尽可能接近1。这个值很关键！

Sub_part:是否列的部分被索引;如果整个列被索引，为NULL，否则为索引的字符长度。

比如现在建一个索引，只对列name前100索引

CREATE INDEX idx_name  ON test2(name(100));

查看一下索引

发现name列的Sub_part为100了

Packed:关键字如何压缩；无压缩，则为NULL

Null:是否索引的列含有NULL值。

Index_type:索引的类型。InnoDB只支持B+索引，所以都显示BTREE

Comment:注释

如果添加索引是，有注释

CREATE INDEX idx_name  ON test2(name(100)) COMMENT 'name普通索引';

看下索引

五 索引的使用

前面讲了聚集索引，辅助索引，它们是B+索引在数据库的实现与本质，那么怎么去用呢？一般有2种索引方式

联合索引

对表中多个列进行索引，即创建一个索引时，有多个列，比如上面的

CREATE INDEX idx_name_age ON test2(name, age);

现在向表里面插入几条数据

INSERT INTO test2(id,name,age) VALUES(1,'tw',20);
INSERT INTO test2(id,name,age) VALUES(2,'tw',21);
INSERT INTO test2(id,name,age) VALUES(3,'tw',20);
INSERT INTO test2(id,name,age) VALUES(4,'tw',24);

现在创建一个联合索引idx_id_age

CREATE INDEX idx_id_age ON test2(id,age) COMMENT '联合索引';

比如现在有如下sql

SELECT * FROM test2 WHERE
id = xx AND age=xx

SELECT * FROM test2 WHERE
id = xx 

查看一下执行计划

会发现在都走了联合索引idx_id_age

但是这个sql

SELECT * FROM test2 WHERE
age = xx

却不能走索引，查看一下执行计划验证下，的确如此？

EXPLAIN SELECT * FROM test2 WHERE
age = 20 

这是为什么呢？因为联合索引是也排序的，内部类似(1,20),(2,21),(3,20),(4,24),数据按(id,age)排序，单个id列也排序，但是单个age列并不排序，因此无法使用到联合索引， 这被称为索引的最左原则，也是面试中很容易问到的

覆盖索引

InnoDB支持覆盖索引，即从辅助索引中就可以取出查询的记录，而不用查询聚集索引。这样的好处是，辅助索引没有存放整行的记录，占用空间更小，可以大大减少IO操作。

所以如果我们要查询主键的一些信息，就可以通过辅助索引，这也是有些Mysql分页优化的常用手段，在大数据下规制limit的一个选择