mysql 索引基础

前言：主要讲解以下几个模块： 
MySQL查询过程 ——> 索引类型 ——> 存储结构 ——>强大的Explain ——> 索引失效 
本章主要围绕mysql索引展开讲解，参考来自网上众多博客，以及书籍《mysql高性能》《Innodb存储引擎》，还请各位多多指教。

---

（一）mysql查询过程

1，客户端发送查询请求, 先会权限认证、连接处理等操作。 
这是一个同步的方式。客户端必须接收整个返回结果。 
2，服务器查看是否命中缓存。 
3，服务器端进行sql解析，预处理，优化器生成对应的执行计划。 
4，调用存储引擎api实现执行计划。 
5，返回结果。

---

（二）索引类型

数据结构角度：B+树索引、hash索引、( 空间数据索引R-Tree、TokuDb分树索引) 
实际应用角度：主键、组合、普通、唯一、（全文索引， 只有myIsam支持） 
其他：聚簇cu索引、非聚簇索引（又叫辅助索引，二级索引）、覆盖索引、前缀索引

1）B+树索引

Innodb引擎底层的数据结构为b+树索引。 
根节点和非叶子节点中存的是索引值和下一层节点地址。 
叶子节点存的为（数据或者是主键值）和下一个叶子节点的地址。

思考：b+树，b-树有什么区别？

b+数据结构图 

b-数据结构图 

差别： 
1，数据存储位置 
B+树中的所有值都在叶子节点中有一份。 
B-树所有值存在整个树中。 
2，B+树的叶子节点是一个链表。

2）hash索引

hash索引存储的为列的hash码和行地址。当查询时先获取索引hash值，再去槽中得到行地址。再根据地址值在内存中查找数据。 
优点：查询速度快。 
缺点：1、不能来做范围查找。 
           2、因为按照hash值来存储，无法用来排序。 
           3、有hash冲突问题。

Innodb有一种特殊的功能自适应哈希索引，当Innodb注意到某些key的值频繁的被使用，它会在b树的基础上再建立hash索引。这是Innodb内部的操作，无法操控，可以关闭。

3）聚簇索引

书中介绍： 

1，聚簇索引并不是一种单独的索引，而是一种数据存储方式。 
2，在Innodb中会自动把主键索引设置为聚簇索引，如果没有主键会使用其他索引，如果没有其他索引，会隐式创建一个6字节隐藏列。总而言之，这个聚簇索引会有。并且仅存在一份。

3，个人观点： 索引在磁盘和内存上各有一份， 
1.内存聚簇索引树中的非叶子节点存储的是主键值、下一层节点地址。叶子节点存储的为行数据、主键值、下一个叶子节点地址。 
2.内存非聚簇索引树中的非叶子节点存储的是键值、主键值、下一层节点地址。叶子节点存储的为键值、主键值、下一个叶子节点地址。

1.磁盘上索引大致与内存上的一致，不过在聚簇索引中非叶子节点存储的为主键值、page页号。

2.磁盘索引并不可以通过主键值找到行数据，只能找到相对应的page页号，由key二分法查找，再根据page directory [目录]才能找到具体行数据。

3.当进行读取操作的时候，把索引page从磁盘加载到内存中，仅仅会把查询相关联部分页数据加载到内存中。

---

3）覆盖索引

如果一个索引中包含所有查询字段的值，我们就称之为’覆盖索引’。 
例如：

CREATE TABLE ‘test’( 
id int(11) unsigned NOT NULL AUTO_INCREMENT, 
name varchar(20) DEFAULT NULL, 
PRIMARY KEY (id), 
KEY name_index (name) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

select id from test where name = ‘1’; 
# 在name索引树中查找到id。 ✅

select * from test where id = 1; 
# 在聚簇索引树种查找到多有列值。 ✅

select * from test where name = ‘1’； ✅ 
# 在name树中查找1的叶子节点中的主键值，再用主键值去查主键树，获取行数据。

---

（三）存储结构

在磁盘上存在一个个扇区（sector），大小为512字节[机械硬盘]（固态硬盘为4k）。 
文件系统将扇区映射为块（block）。 
对于磁盘来说最小的单位为块。但是对于Innodb来说操作的最小单位为页。每页大小默认为16k，在页的基础上逻辑的分为表、段、区。在申请空间时，申请一个区，一个区为1Mb，64个连续的页。好处是：1不用频繁的申请空间。2若顺序存储，数据在一个连续的地址中，避免磁头旋转耗时。

页中共有几个部分： 
1，file head（文件头）：表空间Id，4字节页唯一id，前一页id，后一页id，页类型 
- 在查找的时候，根据表空间id和页唯一id确定页的具体位置。 
- id值为4字节。由此可推算出表中的最大数据为2^32 * 16k = 64TB（如果有溢出则不止）。 
- file head中存有前一页id和后一页id值。由此可知页之间是一个双向链表结构。 
2，page head（页头）：页的状态 
3，System record [infimum，superman（开始标记、结束标记）]：用户数据行的开始、结束标记位 
4，User record（用户记录）：用户记录。记录之间为单链表结构，物理上不是顺序存储，逻辑上顺序。 
索引页分为4种情况：

• 主键树叶子节点：数据行
• 主键树非叶子节点：主键值、page号
• 非主键树叶子节点：主键值、非主键值
• 非主键树非叶子节点：主键值、非主键值、page号 
  对于BLOB page采用数据溢出，存放到bolb页中，数据行中存放指针。
• 行存储结构：http://blog.csdn.net/d57893269/article/details/53907974

5，Free Space：空闲空间。当数据被删除后进入空闲空间。 
6，page directory：页目录。由槽组成，每个槽占两个字节，每个槽对应一个记录，这里采用稀疏法，每个记录都有字段n_owned代表控制链表前几位 
7，File Trailer：页尾，与叶头中LSN字段值一致，用来判断是否为完整的页

---

（四）强大的Explain

id: 执行计划中查询的序列号。Id越大优先级越高。id相同，执行顺序由上至下[sql语句，执行计划]。 
select_type:查询类型

simple :简单查询，没有连表和子查询。 
primary :主查询语句，最外层。 
uncacheable subquery :结果集不能被缓存的子查询。 
union :union中的第二个select语句，不依赖外部语句。 
dependent union :union中的第二个select语句，依赖外部语句。 
subquery :子查询的第一个select语句。 
dependent subquery: 子查询中的第一个 select 查询,依赖于外部 查询的结果集 
deriver:用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。 
uncacheable union:UNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询

table: 表名 
type[ 从优到差 ]:

system :表只有一行。是const的特例，一般不会出现。 
const :主键或者唯一键的时候，只匹配一行。 
eq_ref : 对于每个来自于前面的表的行组合，从表中读取一行。在表连接的时候用唯一键或主键。
ref :非主键和唯一键的索引的使用，匹配出来的非一行。 
ref_or_null : 键值默认为null， mysql会对它做特殊处理，提示避免索引字段为空。

———————————以上都是可以接受的索引使用情况——————————— 
index_merge : 表明索引被合并优化。key列包含了使用的索引，key_len显示最长索引长度。 
unique_subquery :该联接类型用于替换value IN (SELECT primary_key FROM single_table WHERE some_expr)这样的子查询的ref。注意ref列，其中第二行显示的是func，表明unique_subquery是一个函数，而不是一个普通的ref。 
index_subquery : 
range :范围检索。 > < 
index :统计时出现，扫描整个索引树。仅仅比all快。（count语句） 
ALL : 扫全表。

possible_key : 有可能用到的索引 
key : 查询中使用的key 
key_len : 使用索引的长度 
ref : 索引的哪一列被使用了 
rows : 返回请求数据的行数。

select * from zx_comment_records where comment_id > 1 limit 10; // 43 
select * from zx_comment_records where comment_id > 1 limit 10, 20; // 43

Extra: 该列包含mysql解决查询的详细信息。

Distinct : mysql发现第一个匹配后，停止搜索。 
Not exitsts 
range checked for each record ： 没有找到合适的索引 
Using file sort :文件排序，没有利用到索引，直接在内存或者磁盘上排序。 
Using temporary : 排序时使用临时表

参考来自：

• http://blog.chinaunix.net/uid-540802-id-3419311.html

• http://blog.csdn.net/zhuxineli/article/details/14455029

• http://www.jb51.net/article/38357.htm

---

（五）索引失效

1，独立的列，索引列不能为表达式的一部分，也不能是函数的参数。 
select actor_id from sakila.actor where actor_id + 1 = 5;

2，使用or时， 除非全部为索引，否则失效。

3，使用组合索引时，不靠左匹配。 
index （name, id）

4， like以‘%’开始

5，列为字符串时，需要使用引号。 
select * from table where name_index = 1; // 不会使用 
select * from table where name_index = ‘1’; // 会使用

6，如果mysql估计使用全表扫描要比使用索引快, 则不使用索引