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 bselect ... from table where a=xxx and b=xxx order by c 然而对于上面语句，联结索引不能间接失去后果，其中还须要执行一次filesort 排序，因为索引（a,c）并未排序
select ... from table where a=xxx order by c4、笼罩索引（索引笼罩）
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个字符进行索引，如果索引整个列，则为NULLPacked:关键字如何被压缩，如果没有被压缩，则为NULLNull:是否索引的列含有NULL值，如果索引整个列，则为NULLIndex_type:索引的类型，InnoDB存储引擎值反对B+树索引，所以这里都是BTREEComment:正文Index_comment：在创立索引时提供的正文2、Multi-Range Read 优化
Multi-Range Read优化的目标就是为了缩小磁盘的随机拜访，并且将随机拜访转化为程序的数据拜访，这对于IO-bound类型的SQL查问语句可带来性能的晋升，Multi-Range Read优化可应用于range、ref、ef_ref的查问