MySql系列07：索引原理

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

1、聚簇索引和非聚簇索引

”聚簇索引”不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起。

聚簇索引代表是Innodb引擎，索引和数据在一起存放

非聚簇索引代表是MylSam，索引和数据不在一起存放，索引和数据地址在一起存放

优点:

• 1、可以把相关数据保存在一起
• 2、数据访问更快，因为索引和数据保存在同一个树中
• 3、使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点:

• 1、聚簇数据最大限度地提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
• 2、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式
• 3、更新聚簇索引列的代价很高，因为会强制将每个被更新的行移动到新的位置
• 4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
• 5、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

注意：

• 1.InnDB是通过B+Tree结构对主键创建索引，然后叶子节点存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id来作为主键
• 2.如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫回表

2、Mysql数据结构B+树

hash、二叉树、avl树（平衡树）、红黑树、b树都可以做mysql数据结构，为什么要采用B+树呢？

1、Hash

哈希表可以完成索引的存储，每次添加索引的时候需要计算指定列的hash值，取模运算后计算出下标，将元素插入下标位置即可。适合等值查询

缺点：

• 1、而实际工作中多数查询是范围查询，因为hash表中数据是无序数据，范围查找浪费时间，需要挨个进行遍历。
• 2、hash表使用时需要将全部数据加载到内存，比较耗费内存的空间

2、二叉树

二叉树容易变成是列表，这样树会很深，影响查询性能

3、avl平衡树

AVL树是一颗严格意义上的平衡树，最高子树和最低子树高度差不能超过1，因此在进行元素插入的时候，会进行1到N次的旋转，严重影响插入性能

4、红黑树

红黑树是基于AVL树的升级，损失了部分查询性能，来提升插入性能，在红黑树中最低子树和最高子树之差能小于2倍，在插入时，不需要进行N次旋转操作，加入了变色的特性，来满足插入和查询性能的平衡。因为其最高子树和最低子树倍数原因，很可能会导致树的深度无法控制，形成二叉树一样的列表，影响插入性能

5、B树

B树特点：

• 1、所有键值分布在整颗树上
• 2、搜素有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找
• 3、每个节点最多拥有m个子树
• 4、根节点至少又2个子树
• 5、分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
• 6、所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列

缺点：

• 每个节点都有key，同时也包含data，而每个页存储空间是有限的，如果data比较大的话，会导致每个节点存储的key数量变少
• 当存储的数据量很大的时候，会导致树深度较大，增大查询时磁盘io次数，进而影响查询性能
• Mysql读取数据是磁盘预读都是16k也就是4页，而如果像上图一样，每个磁盘块上保存data，那么这样大部分的空间会被data占用，会造成非常频繁的IO，那么如果我们不存data，磁盘块中只存指针和key值，这样就会大大减少IO的次数，来提高查询效率

6、B+树

B+树是在B树的基础上做的优化，变化如下

• 1、B+树每个节点可以包含更多节点，降低树的高度，也将数据范围变为多个区间，区间越多，数据检索越快
• 2、非叶子节点存储key，叶子节点存储key和data
• 3、叶子节点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高

注意：在B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+树进行两种查找运算，一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。B+树是矮短型

3、MySQL索引分类

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

1、简介

MySQL目前主要有以下几种索引类型：
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引

2、语法介绍

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引

2.index和key为同义词，两者作用相同，用来指定创建索引

3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择

4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值

5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度

6.asc或desc指定升序或降序的索引值存储

1、普通索引

是最基本的索引，它没有任何限制。它有以下几种创建方式：

• 直接创建索引

CREATE INDEX index_name ON table(column(length))

• 修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

• 创建表的时候同时创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`),
	INDEX index_name (title(length))
)

• 删除索引

DROP INDEX index_name ON table

2、唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。唯一索引不会回表，普通索引会有回表情况

• 创建唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length)) 

• 修改表结构

ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))

• 创建表时同时创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`),
	UNIQUE indexName (title(length))
);

3、组合索引

#随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
#单独建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 
#删除索引：
DROP INDEX idx_no_name  on customer ;

4、面试点

1、mysql索引回表

先通过普通索引查询出对应数据表中的具体行，再通过主键ID从具体的行中获取到索引中未能提供的数据，即为回表

举个栗子

1、建表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use demo;
Database changed
mysql> create table user(     #创建表
    -> id int primary key auto_increment, 
    -> age int not null, 
    -> name varchar(16),
    -> index (age))engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user(age,name) VALUES(10,'zhangsan');  #插数据
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(age,name) VALUES(12,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user(age,name) VALUES(15,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(age,name) VALUES(10,'lisi');
Query OK, 1 row affected (0.00 sec)

2、需求：查找出年龄是12的用户

2.1、方式一：通过主键查询，只需要搜索 ID 这棵 B+ 树；

mysql> select * from user where id = 2;
+----+-----+----------+
| id | age | name     |
+----+-----+----------+
|  2 |  12 | zhangsan |
+----+-----+----------+
1 row in set (0.00 sec)

mysql> 

2.2、方式二：通过普通索引查询

普通索引因为无法直接定位行记录，所以查询过程需要扫描两遍索引树

需要先搜索 name索引树，得到 ID 的值为 1、2、3，再到 ID 索引树搜索一次id2。这个过程称为回表

mysql> select * from user where name = 'zhangsan';
+----+-----+----------+
| id | age | name     |
+----+-----+----------+
|  1 |  10 | zhangsan |
|  2 |  12 | zhangsan |
|  3 |  15 | zhangsan |
+----+-----+----------+
3 rows in set (0.00 sec)

数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据主键再次到数据块里获取数据

mysql> select * from user where id = 2;
+----+-----+----------+
| id | age | name     |
+----+-----+----------+
|  2 |  12 | zhangsan |
+----+-----+----------+
1 row in set (0.00 sec)

mysql> 

2、覆盖索引

select id from emp；当我们查找的数据是id，而id刚好是主键索引，那么我们就不用回表，也就称为覆盖索引。

• 1、如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引
• 2、不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值
• 3、不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引

优势:

• 1、索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql就会极大的较少数据访问量
• 2、因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
• 3、一些存储引擎如MYISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用，这可能会导致严重的性能问题
• 4、由于INNODB的聚簇索引，覆盖索引对INNODB表特别有用

3、最左匹配

假设我们为 name age设置组合索引，此时我们有一条查询语句select * from emp where nam e = ？ and age = ？ 会先匹配name再去匹配age ，但是如果select * from emp where age = ？；我们把name跳过去了 此时我们就不能用索引了

4、 索引下推

select * from emp where name = 'zhangsan' and age= 10;

在回表前，就已经用age=10做了筛选，每次都需要回表查询,而下推的是在先过滤好结果集，回表拿select * 的数据

5、组合索引

当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要

案例：建立索引a,b,c

MyISAM和innoDB引擎对比

	MyISAM	innoDB
索引类型	非聚簇	聚簇
支持事务	是	否
支持表锁	是	是
支持行锁	否	是（默认）
支持外键	否	是
支持全文索引	是	是（5.6以后支持）
适用操作类型	大量select下使用	大量insert、delete和update下使用