关于后端:MySQL索引

MySQL 索引

1、MySQL 中 InnoDB 存储引擎索引概述

在 InnoDB 存储引擎中反对以下几种常见的索引

• B+ 树索引
• 全文索引
• 哈希索引

InnoDB 存储引擎反对的索引是自适应的，InnoDB 会依据表的应用状况主动为表生成哈希索引。B+ 树中的 B 不是代表二叉（binary), 而是代表均衡（balance）, 因为 B + 树是从最早的均衡二叉树演变而来，然而 B + 树不是二叉树，B+ 树索引并不能找到一个给定键值得具体行，B+ 树索引能找到的只是被查找数据行所在的页。而后数据库通过把页读入到内存中，再在内存中进行查找，最初找到要查找的数据

2、索引分类

依照索引数据结构分类 : B+ 树索引、全文索引、哈希索引（Hash 索引）

依照索引物理存储分类：聚簇索引（主键索引）、辅助索引（非汇集索引）

依照索引字段个性分类：主键索引、惟一索引、一般索引、前缀索引

依照索引字段个数分类：单列索引、联结索引

1、汇集索引（主键索引）

InnoDB 存储引擎表是索引组织表，即表中数据依照主键程序寄存，而汇集索引（clustered index) 就是依照每张表的主键结构一棵 B + 树，同时叶子节点中寄存的即为整张表的行记录数据，也将汇集索引的叶子节点称为数据页。同 B + 树结构一样，每个数据页都通过一个双向链表来进行链接。

因为理论的数据页只能依照一棵 B + 树进行排序，因而每张表只能领有一个汇集索引，在少数状况下，查问优化器偏向于采纳汇集索引，因为汇集索引可能在 B + 树索引的叶子结点上间接找到数据，此外，因为定义了数据的逻辑构造，汇集索引可能特地快地拜访针对范畴值的查问，查问优化器可能疾速发现某一段范畴的数据页须要扫描。

汇集索引对于主键的排序查找和范畴查找速度十分快，叶子结点的数据就是用户所要查问的数据

汇集索引一个表只能有一个，在创立汇集索引时，InnoDB 通过主键创立汇集索引，如果没有定义主键，InnoDB 会抉择一个非空的惟一索引来建设汇集索引，如果没有这样的索引，InnoDB 会隐式的定义一个主键来作为汇集索引

汇集索引存储记录是物理上间断存在，物理存储依照索引排序，而非汇集索引是逻辑上的间断，物理存储并不间断，物理存储不依照索引排序。

汇集索引 B +Tree 存储如下图

<center> 汇集索引 B +Tree 存储 </center>

2、辅助索引（非汇集索引）

对于辅助索引（非汇集索引），叶子节点并不蕴含行记录的全副数据，叶子节点除了蕴含键值之外，每个叶子节点中的索引行还蕴含了一个书签（bookmark）, 该书签用来通知 InnoDB 存储引擎哪里能够找到与索引绝对应的行数据

3、联结索引

联结索引是指对表上的多个列进行索引，联结索引的创立办法与单个索引创立办法一样，不同之处在于有多个索引列，从实质上来说，联结索引也是一棵 B + 树, 不同的是联结索引的键值的数量不是 1，而是大于等于 2

<center> 多个键值的 B + 树 </center>

若是对表建设 index(a,b), 那么对于查问 select from table where a=xxx and b=xxx, 显然是能够应用（a,b) 这个索引的，那么对于单个的 a 列查问 select from table where a=xxx 也是能够应用（a,b) 这个索引的，然而对于 b 列的查问 select * from table where b=xxx, 则不会走索引（a,b), 因为叶子节点的 b 值不是排序的，因而对于 b 列的查问用不到索引（a,b) 的索引。

联结索引的第二个益处就在于曾经对第二个键值进行了排序解决，以查问用户的购物状况为例，依照工夫进行排序，最初取出最近三次的购买记录，这时应用联结索引能够防止多一次的排序 操作，因为索引自身在叶子节点曾经排序了

创立表 buy_log

CREATE TABLE buy_log (userid INT NOT NULL, buy_date date) ENGINE = INNODB

插入数据

insert into buy_log VALUES(1,'2009-01-01');
insert into buy_log VALUES(2,'2009-01-01');

insert into buy_log VALUES(3,'2009-01-01');
insert into buy_log VALUES(1,'2009-02-01');

insert into buy_log VALUES(3,'2009-02-01');
insert into buy_log VALUES(1,'2009-03-01');
insert into buy_log VALUES(1,'2009-04-01');

建设索引

alter table buy_log add key(userid);

alter table buy_log add key(userid,buy_date);

当只是对 userd 来进行查问时

select * from buy_log where userid=2

执行打算为

mysql> explain select * from buy_log where userid=2;
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys   | key    | key_len | ref   | rows | Extra |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | buy_log | ref  | userid,userid_2 | userid | 4       | const |    1 | NULL  |
+----+-------------+---------+------+-----------------+--------+---------+-------+------+-------+

能够发现，possible_keys 在这里有两个索引能够应用，然而最终执行器抉择的是索引 userid, 因为该索引上的叶子结点蕴含单个键值，所以实践上一个页能寄存的记录更多

当执行语句

mysql> select * from buy_log where userid=2 order by buy_date desc limit 3；

执行打算为

mysql> explain select * from buy_log where userid=2 order by buy_date desc limit 3;
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys   | key      | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | buy_log | ref  | userid,userid_2 | userid_2 | 4       | const |    1 | Using where |
+----+-------------+---------+------+-----------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

SQL 应用的是 userid_2 索引，因为在这个索引中 buy_date 曾经排好序了，依据该索引取出数据，无需再对 buy_date 做一次额定的排序操作，若强制应用 userid 索引，则执行打算为

mysql> explain select * from buy_log force index (userid)  where userid=2 order by buy_date desc limit 3 ;
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | buy_log | ref  | userid        | userid | 4       | const |    1 | Using where; Using filesort |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

在 Extra 中能够看到应用了 Using fileSort, 即须要额定的一次排序操作能力实现排序，在 SQL 中须要对 buy_date 排序，因为索引 userid 中的 buy_date 是未排序的

联结索引（a,b) 是依据列 a、b 来进行排序的，然而对于联结索引（a,b,c）来说，下列语句同样能够通过联结索引来失去后果

select ... from table where a=xxx order by b

select ... from table where a=xxx and b=xxx order by c 

然而对于上面语句，联结索引不能间接失去后果，其中还须要执行一次 filesort 排序，因为索引（a,c）并未排序

select ... from table where a=xxx order by c

4、笼罩索引（索引笼罩）

InnoDB 存储引擎反对笼罩索引（covering index, 也称索引笼罩），即能够间接从笼罩索引中就能够查问到记录，而不须要查问汇集索引中的记录，应用笼罩索引益处是笼罩索引不蕴含整行记录的所有信息，故其大小要远小于汇集索引，能够大大减少 IO 操作

比方上个查问语句

mysql> explain select count(*) from buy_log;
+----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+
|  1 | SIMPLE      | buy_log | index | NULL          | userid | 4       | NULL |    7 | Using index |
+----+-------------+---------+-------+---------------+--------+---------+------+------+-------------+
1 row in set (0.00 sec)

能够看到彭 possible_keys 列为 NULL，然而执行器抉择的是 userid 索引，而 Extra 的 Using index 示意应用了索引笼罩操作

5、倒排索引

全文索引通常应用倒排索引来实现（inverted index）来实现，倒排索引同 B + 树索引一样，也是一种索引构造，它在辅助表外面存储了单词与单词本身在一个或多个文档中所在位置之间的映射

3、B+ 树索引治理

1、索引治理

索引的创立和删除能够通过两张办法，一种是 alter table, 另一种是 create/drop index。

通过 alter table 创立索引的语法是:

alter table tbl_name add [index_name] [index_type] (index_col_name,...)
alter table tbl_name drop index_name

通过 create /drop index 来创立索引

create [unique] index index_name [index_type] on tbl_name (index_col_name,...)
drop index index_name on tbl_name

若是想要查看表中索引的信息。通过命令 show index

mysql> show index from user;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY         |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | PRIMARY         |            2 | User        | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_user_Host |            1 | Host        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

通过 show index from 能够看到表中建设了 3 个索引，上面对每个字段进行解析

Table: 索引所在的表名
Non_unique：非惟一的索引
Key_name：索引的名字
Seq_in_index：索引中该列的地位
Column_name：索引列的名称
Collation：列以什么形式存储在索引中，能够是 A 或者 NULL,B+ 树索引总是 A。即排序的
Cardinality：该值示意索引中惟一值的数目的估计值，Sub_part：是否是列的局部被索引，如果示意 100，则示意对前 100 个字符进行索引，如果索引整个列，则为 NULL
Packed: 关键字如何被压缩，如果没有被压缩，则为 NULL
Null: 是否索引的列含有 NULL 值，如果索引整个列，则为 NULL
Index_type: 索引的类型，InnoDB 存储引擎值反对 B + 树索引，所以这里都是 BTREE
Comment: 正文
Index_comment：在创立索引时提供的正文 

2、Multi-Range Read 优化

Multi-Range Read 优化的目标就是为了缩小磁盘的随机拜访，并且将随机拜访转化为程序的数据拜访，这对于 IO-bound 类型的 SQL 查问语句可带来性能的晋升，Multi-Range Read 优化可应用于 range、ref、ef_ref 的查问