MySQL索引

1、索引类型

　　在Innodb里面，索引有三种类型：普通索引，唯一索引（主键索引是特殊的唯一索引）、全文索引。

　　普通索引（normal）：也叫非唯一索引，是最普通的索引，没有任何限制。

　　唯一索引（unique）：要求键值不能重复。主键索引是一种特殊的唯一索引，还多了一个限制条件，要求键值不能为空。主键索引用primary key创建。

　　全文索引（fulltext）：针对较大的数据，比如存放的消息内容（有几KB），如果要解决like查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，如char、varchar、text。

 

2、InnoDB逻辑存储结构

　　MySQL的存储结构分为5级：表空间、段、簇、页、行。

　　　　　　

（1）表空间 Table Space

　　表空间是Innodb逻辑存储结构的最高层，所有的数据都存放在表空间中。分为：系统表空间、独占表空间、通用表空间、临时表空间、undo表空间。

（2）段 Segment

　　表空间由各个段组成，常见的有数据段、索引段、回滚段等，段是一个逻辑概念。一个ibd文件（独立表空间文件）会包含很多个段。

　　创建一个索引会创建两个段，一个是索引段：leaf node segment，一个是数据段：non-leaf node segment。索引段管理非叶子节点的数据，数据段管理叶子节点的数据。一个表的段数，就是索引的个数乘2。

（3）簇（区） Extent

　　一个段又由很多个簇组成，每个区的大小是1MB（64个连续的页）。每一个段至少会有一个簇，一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。

（4）页 Page

　　为了高效管理物理空间，对簇进一步细分，就得到了页。簇是由连续的页（Page）组成的空间，一个簇中有64个连续的页（1MB／16KB=64）。这些页在物理上和逻辑上都是连续的。

　　每个页默认16KB，页是InnoDB存储引擎磁盘管理的最小单位，通过innodb_page_size设置。一个表空间最多拥有2^32个页，默认情况下一个页的大小为16KB，也就是说一个表空间最多存储64TB的数据。如下：一个页放3行数据

　　　　　　

 　　往表中插入数据时，如果一个页已经被写满，则会产生一个新的页。如果一个簇的所有页都被用完，会从当前页所在的段分配一个新的簇。如果数据是不连续的，往已经写满的页中插入数据，会造成页分裂：以用户Id作为主键，因为用户Id是不连续的，所以在插入数据时可能会频繁产生页分裂。

　　　　                                            　

参考文章：主键与页的分裂与合并之间的关系 https://blog.2014bduck.com/archives/260

（5）行 Row

　　InnoDB 存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。

3、索引的存储结构

 　　用树的结构来存储索引，访问一个节点就要跟磁盘发生一次IO。磁盘IO次数越多，消耗的时间也就越多。接下来分析三种树结构存储数据的优缺点：

（1）平衡二叉树（AVL）

　　Innodb操作磁盘的最小单位是页，大小是16K，所以一个树的节点就是16K的大小。AVL树一个节点只存储一个（键值+数据+引用），存储的数据太少会浪费大量的空间，也意味着树的深度会增加，因此在读取数据时与磁盘的交互次数就会过多。

　　　　　　　　　　

　　解决的方案：

　　　　第一是让每个节点存储更多的数据

　　　　第二是节点上的关键字尽可能多，意味着指针数越多，这样树的深度就会减少。

 

 （2）多路平衡查找树（B Tree）（分裂、合并）

　　与AVL树一样，B树在枝节点和叶子节点存储键值、数据地址、节点引用。B树有一个特点：分叉数（路数）永远比关键字数多1。如下：

　　　　　　　　　　

　　B树通过分裂与合并保持平衡，节点的分裂与合并就是页的分裂与合并。

（3）B+树（加强版多路平衡查找树）

　　　　　　　　　　

 　　B+树有几个特点：

　　　　1） 关键字的数量与路数相等。

　　　　2）B+树的根节点和枝节点不存储数据，只有叶子节点存储数据（这么做也是为了进一步降低树的深度）。举个例子：

　　　　　　假设一条记录大小为1K，一个叶子节点（一页）则可以存储16条记录，非叶子节点中存储的键值为索引的值。如果索引字段是bigint类型，长度为8字节，指针大小在Innodb源码中为6字节，所以一共14字节。非叶子节点可以存储16 * 1024 / 14 = 1170个这样的单元（键值+指　　                     针），代表有1170个指针。树深度为2的时候，有1170^2个叶子节点，可以存储记录为1K的数据1170*1170*16=21902400条。

　　　　　　　　　　

 　　　　　　在查找数据时一次页的查找代表一次 IO，也就是说，一张2000万左右的表，查询数据最多需要访问3次磁盘。所以B+树深度一般为1-3层，就能满足千万级别的数据存储。

　　　　3）B+树的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成一个有序链表结构，这样范围查找和排序的效率会大大增强。

　　　　4）根据左闭右开的区间 [ )来检索数据。

　　来分析一下B+树的数据查找过程：

　　　　1）比如我们要查找 28，在根节点就找到了键值，但是因为它不是页子节点，所以会继续往下搜寻，28是[28,66)的左闭右开的区间的临界值，所以会走中间的子节点，然后继续搜索，它又是[28,34)的左闭右开的区间的临界值，所以会走左边的子节点，最后在叶子节点上找到了需要的             数据。

　　　　2）第二个，如果是范围查询，比如要查询从22到60的数据，当找到22之后，只需要顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，这样就极大地提高了区间查询效率（不需要返回上层父节点重复遍历查找）。

（4）为什么不用红黑树？

　　第一：只有两路，第二：不够平衡。

（5）Hash索引

　　以KV的形式检索数据，也就是会根据索引字段生成哈希码和指针，指针执行数据，如下：

　　　　　　　　

 　　哈希索引的特点：

　　　　1）时间复杂度是O（1），查询速度很快。因为哈希索引里面的数据不是按顺序存储的，所以不能用于排序。

　　　　2）在查询数据的时候要根据键值计算哈希码，所以只能支持等值查询（=、in），不支持范围查询。另外一个，如果字段重复值很多，会出现大量的哈希冲突（采用拉链法解决），效率会降低。

　　InnoDB内部使用哈希索引来实现自适应哈希索引特性。InnoDB 只支持显式创建 B+Tree 索引，对于一些热点数据页，InnoDB会自动建立自适应Hash索引，也就是在B+Tree索引基础上建立Hash索引，这个过程对于客户端是不可控制的，隐式的。

4、MySQL数据存储——B+Tree落地形式　　

　　MySQL的数据都是以文件的形式存放在磁盘中的，每个数据库对应一个目录。每张InnoDB 的表有两个文件（.frm和.ibd），MyISAM的表有三个文件（.frm、.MYD、.MYI）。

　　　　　　　　　　　　　　

　　有一个是相同的文件.frm。 .frm是MySQL里面表结构定义的文件，任何一个存储引擎都会生成。

（1）MyISAM

　　在MyISAM里面，另外有两个文件：一个是.MYD文件，D代表Data，是MyISAM的数据文件，存放数据记录。一个是.MYI文件，I代表Index，是MyISAM的索引文件，存放索引。也就是说，在MyISAM里面，索引和数据是两个独立的文件。

　　如何根据索引去找到数据？

　　　　MyISAM的B+Tree 里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。

　　　　　　　　　　　　　　

 　　　　在MyISAM里面，辅助索引也在这个.MYI文件里面，辅助索引跟主键索引存储和检索数据的方式没有任何区别。

（2）InnoDB

　　除了.frm，InnoDB只有一个文件（.ibd文件）。InnoDB是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件。在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据。

　　　　　　　　　　　　　　

　　*  聚集索引（聚簇索引）

　　　　就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。在InnoDB里面，它组织数据的方式叫做叫做（聚集）索引组织表，所以主键索引是聚集索引，非主键都是非聚集索引。

　　*  辅助索引如何存储和检索数据

　　　　辅助索引存储的是辅助索引和主键值（叶子节点存储）。如果使用辅助索引查询，会先获取主键值，再根据主键值在主键索引中查询（也叫回表），最终取得数据。

　　　　　　　　　　　　　　

　　*  如果一张表没有主键怎么办？

　　　　1）如果定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引。

　　　　2）如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键。

　　　　3）如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键自增。

5、索引使用原则

（1）使用离散度高的字段建立索引

　　离散度：count(distinct) / count(*)，该列上不重复值比上行数，重复度越高离散度越低。

　　重复值过度会导致扫全表，所以离散度低的字段不适合建索引。建了索引也不一定会走索引。

（2）联合索引最左匹配

　　联合索引在B+Tree中是复合的数据结构，它是按照从左到右的顺序来建立搜索树的。比如：在user表上面，给name和phone建立了一个联合索引。

　　　　　　　　　　　　　　　　

　　从这张图可以看出来，name是有序的，phone是无序的。当name相等的时候，phone才是有序的。只有查询条件包含name才能用到该联合索引，如果name相同再去比较phone。但是如果查询条件没有name，就不知道第一步应该查询哪个节点，所以用不到索引。

　　*  创建联合索引时，将最常用的字段放在最左边。

　　*  创建index(a,b,c)也相当于创建了index(a)，index(a,b)两个索引。用 where b=? 和 where b=? and c=? 和where a=? and c=?是不能使用到索引的。不能不用第一个字段，不能中断。

（3）覆盖索引

　　回表：非主键索引，先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

　　在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列就是索引值，不必回表从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。例如：创建了联合索引index(name,phone)，以下查询就使用了覆盖索引。

　　　　　　　　　　　　

 　　覆盖索引减少了IO次数，减少了数据的访问量，可以大大地提升查询效率。

（4）索引条件下推 ICP（index_condition_pushdown，默认开启）

　　索引条件下推（IndexConditionPushdown），5.6以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作。创建以下表：

 CREATETABLE`employees`( `emp_no`int(11)NOTNULL, `birth_date`date NULL, `first_name`varchar(14)NOTNULL,
`last_name`varchar(16)NOTNULL, `gender`enum('M','F')NOTNULL, `hire_date`date NULL, PRIMARYKEY(`emp_no`) )ENGINE=InnoDBDEFAULTCHARSET=latin1;
​
altertableemployeesaddindexidx_lastname_firstname(last_name,first_name);
​
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(1, NULL,'698','liu','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(2, NULL,'d99','zheng','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(3, NULL,'e08','huang','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(4, NULL,'59d','lu','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(5, NULL,'0dc','yu','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(6, NULL,'989','wang','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(7, NULL,'e38','wang','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(8, NULL,'0zi','wang','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(9, NULL,'dc9','xie','F',NULL); 
INSERTINTO`employees`(`emp_no`,`birth_date`,`first_name`,`last_name`,`gender`,`hire_date`)VALUES(10, NULL,'5ba','zhou','F',NULL);

*  关闭ICP（setoptimizer_switch='index_condition_pushdown=off'）

select*fromemployeeswherelast_name='wang'andfirst_nameLIKE'%zi';

　　这条SQL有两种执行方式：

　　　　1）根据联合索引查出所有姓wang的二级索引数据，然后回表，到主键索引上查询全部符合条件的数据（3 条数据）。然后返回给 Server 层，在Server 层过滤出名字以zi结尾的员工。

　　　　2）根据联合索引查出所有姓wang的二级索引数据（3个索引），然后从二级索引中筛选出first_name以zi结尾的索引（1个索引），然后再回表，到主键索引上查询全部符合条件的数据（1条数据），返回给Server 层。

　　很明显，第二种方式到主键索引上查询的数据更少。注意，索引的比较是在存储引擎进行的，数据记录的比较，是在Server层进行的。

　　通过explain执行以上SQL，如下：

　　　　　　

 

　　Using Where代表从存储引擎取回的数据不全部满足条件，需要在Server 层过滤。

 *  开启ICP（setoptimizer_switch='index_condition_pushdown=on'）

　　开启ICP后，再通过explain执行以上SQL，如下：

　　　　　　

　　ICP开启后把first_name LIKE '%zi'下推给存储引擎后，只会从数据表读取所需的1条记录再返回给Server层。

6、索引的创建与使用

*  索引的创建

　　（1）在用于where判断order排序和join的（on）字段上创建索引。

　　（2）索引的个数不要过多，浪费空间，更新变慢（记录更新也要跟新索引）

　　（3）离散度低的字段不要建立索引，如性别。

　　（4）频繁更新的字段，不要作为主键或者索引。会引起页分裂（插入）与合并（删除）。

　　（5）组合索引把离散度高的值放在前面。

　　（6）为什么不建议用无序的值（如身份证号、UUID）作为主键索引？

　　　　　　使用自增ID作为主键，插入新纪录时会顺序添加到当前索引节点的后续位置，形成一个紧凑的索引结构，也不会移动已有的数据。非自增主键，会造成频繁的数据页分裂与合并。

*  什么情况会导致索引用不到？

　　（1）索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）。

　　（2）字符串不加引号，出现隐式转换　

SELECT*FROM`user_innodb`wherename =136; 
SELECT*FROM`user_innodb`wherename ='136';

　　（3）like条件中前面带%，但是如果过滤的开销太大，也无法使用索引。这个时候可以用全文索引。

select*fromuser_innodbwherename like'wang%'; 
select*fromuser_innodbwherename like'%wang';

　　（4）负向查询，如not like不能使用，!= 和NOT IN 在某些情况下可以。

7、说明

　　一个SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

　　其实用不用索引，最终都是优化器决定的，由于InnoDB的优化器是基于开销（cost）的，不是基于规则，也不是基于语义的，所以怎么样开销小就会怎么来。