Mysql 索引

索引

为什么要有索引？

        假设有一张表，表中有 100 万条数据，这 100 万条数据在硬盘上是存储在数据页上的，一页数据大小为 16K，存储 100 万条数据需要很多数据页，假设其中有一条数据是 id='7900'，如果要查询这条数据，其 SQL 是 SELECT * FROM 表名称 WHERE id = 7900。mysql 需要扫描全表来查找 id=7900 的记录。全表扫描就是从“数据页 1”开始，向后逐页查询。对于少量的数据，查询的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100 万条数据逐页查询的时间是无法被用户接受的。

什么是索引？

索引是帮助 MySQL 高效获取数据的排好序的快速查找的数据结构。

数据库在存储数据本身之外，还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

 左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址.每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

索引原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等.本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，借助索引，执行查询时不必扫描整个表就能够快速地找到所需要的数据。

索引优势

提高数据检索的效率,降低数据库的 IO 成本;通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗;

索引劣势

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用磁盘空间的。虽然索引大大提高了查询速度,同时却会降低更新表的速度,例如对表进行 INSERT,UPDATE 和 DELETE,因为更新表时,MySQL 不仅要保存数据,还要保存 一下索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

索引分类

主键索引

设定为主键后数据库会自动建立索引

ALTER TABLE 表名 add PRIMARY KEY 表名(列名);

删除建主键索引：

ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引,添加了单值索引的列查询速度快。

创建单值索引

CREATE INDEX 索引名 ON 表名(列名);

删除索引：

DROP INDEX 索引名;

唯一索引

索引列的值必须唯一，允许为 null

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

删除索引

DROP INDEX 索引名 ON 表名;

组合索引(复合索引)

即一个索引包含多个列,在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引),当表的行数远大于索引列的数目时可以使用复合索引.

创建复合索引

CREATE INDEX 索引名 ON 表名(列 1,列 2...)

删除索引：

DROP INDEX 索引名 ON 表名

组合索引最左前缀原则

列如表中有 a,b,c 3 列,为 a,b 两列创建组合索引,那么在使用时需要满足最左 侧索引原则.在使用组合索引的列作为条件时,必须要出现最左侧列为条件,否则 组合索引不生效.

列如:

select * from table where a=’’and b=’’索引生效

select * from table where b=’’and a=’’索引生效

select * from table where a=’’and c=’’索引生效

select * from table where b=’’and c=’’索引不生效

总的来说就是 查询条件中必须包含定义组合索引时 写在括号最左边的那个列 索引才会生效。

全文索引

需要模糊查询时（like），一般索引无效，这时候就可以使用全文索引了。

CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;

SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词')

建立索引的原则

哪些情况需要创建索引

主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引(where 后面的语句)

查询中与其它表关联的字段，外键关系建立索引

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度分组中的字段

哪些情况不要创建索引

表记录太少

经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE,因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件

Where 条件里用不到的字段不创建索引

数据重复且分布平均的表字段（比如性别，年龄），因此应该只为最经常查询和最经常排序的数 据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

索引的结构

学习B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树 （B-Tree），B+树即由这些树逐步优化而来。使其更适合实现外存储索引结构，InnoDB 存储引擎就是用B+Tree 实现其索引结构。

二叉查找树

 问题

这样的结构并不能缩短查找时间，而且树的高度会很高。

平衡二叉树

 问题：

插入数据的时候要改变树的结构，有很大的时间开销

红黑树

问题：

在数据特别多的时候，树的高度会很高，查找数据效率低。

B树和B+树

B树就是一个有序的多路查询树（一个根节点有很多分支）

B+树即由这些树逐步优化而来。使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

排好序的,一个节点可以存储多个索引

非叶子节点不存储数据,只存储索引,可以放更多的索引.

数据记录都存放在叶子节点中.

所有叶子节点之间都有一个链指针

Mysql 索引使用的是 B+树，因为索引是用来加快查询的，而 B+树通过对 数据进行排序所以是可以提高查询速度的，然后通过一个节点中可以存储多个元 素，从而可以使得 B+树的高度不会太高.并且叶子节点之间有指针，可以很好的支持全表扫描，范围查找等 SQL 语句。

聚簇索引和非聚簇索引

一个例子

下面我们创建了一个学生表，做三种查询，来说明什么情况下是聚簇索引，什么情况下不是。

CREATE TABLE student (
id BIGINT,
NO VARCHAR (20),
NAME VARCHAR (20),
PRIMARY KEY (`id`),
UNIQUE KEY `idx_no` (`no`)
)

聚簇索引：找到了索引,就找到了数据,例如主键索引,innodb引擎下,数据都在叶子节点存储,通过主键查找,找到了主键,也就找到了数据(属于聚簇式)

SELECT id FROM news WHERE id = 3

非聚簇索引: 索引和数据是分离的,找到了索引,还没有找到数据，需要根据主键,再次回表查询,才能够查询到数据

SELECT NO,NAME FROM student WHERE NO = 123

    根据编号查询编号和名称，编号本身是一个唯一索引，但查询的列包含了学生编号和学生名称，当命中编号索引时，该索引的节点的数据存储的是主键 ID，需要根据主键 ID 重新查询一次，所以这种查询下 no 不是聚簇索引

          还有myisam引擎中,除了只查询主键列以外,查询其他列的都是非聚簇索引

判断是聚簇索引还是非聚簇索引主要是看这次查询中,能够直接命中数据.