MySQL 之 索引

1、为什么要有索引

​ 对查询语句的优化，加速查询

2、什么是索引

​ 索引在MySQL中也叫是一种‘键’，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。

​ 索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。

​ 索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

3、索引的原理

(1)、 索引原理

​ 本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，就可以总是用同一种查找方式来锁定数据。

(2)、 磁盘IO与预读

​ 磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2= 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17= 9ms左右。但一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行约450万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

​ 考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，还把相邻的数据也都读取到内存缓冲区内，因为由局部预读性原理可知，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

4、索引的数据结构

(1)、树

​ 树状图是一种数据结构，它是由n（n>=1）个有限结点组成一个具有层次关系的集合。把它叫做“树”是因为它看起来像一棵倒挂的树，也就是说它是根朝上，而叶朝下的。

​ 它具有以下的特点：每个节点有零个或多个子节点；没有父节点的节点称为根节点；每一个非根节点有且只有一个父节点；除了根节点外，每个子节点可以分为多个不相交的子树。

(2)、B树

​ 平衡树 balance tree - B树

(3)、B+树

​ B+树是通过二叉查找树，再由平衡二叉树，B树演化而来， 是为了更好的处理范围问题在b树的基础上有所优化。mysql 中innodb存储引擎的所有的索引树都是b+树

5、聚集索引与辅助索引

​ 在数据库中，B+树的高度一般都在2_{4层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，当前一般的机械硬盘每秒至少可以做100次IO，2}4次的IO意味着查询时间只需要0.02~0.04秒。

​ 数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）

（1）、聚集索引与辅助索引的相同点：

​ 聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

（2）、聚集索引与辅助索引的不相同点：

​ 聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息。

<1>、 聚集索引/聚簇索引：叶子节点会存储整行数据 ----- innodb 的主键

# InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。
而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。
聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
    
# 如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。   
# 如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

# 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。
在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。
此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

<2>、 辅助索引/非聚集索引：除了主键之外的普通索引都是辅助索引，一个索引没办法查到整行数据，需要回聚集索引再查一次(回表)

表中除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。
叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark）。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录。

<3>、聚焦索引和非聚焦索引的区别

# 聚集索引
1.纪录的索引顺序与物理顺序相同
   因此更适合between and和order by操作
2.叶子结点直接对应数据
 从中间级的索引页的索引行直接对应数据页
3.每张表只能创建一个聚集索引

# 非聚集索引
1.索引顺序和物理顺序无关
2.叶子结点不直接指向数据页
3.每张表可以有多个非聚集索引，需要更多磁盘和内容
   多个索引会影响insert和update的速度		

6、MySQL索引管理

（1）、索引功能

1. 索引的功能就是加速查找
2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

（2）、MySQL常用的索引

普通索引 INDEX：加速查找

唯一索引：
    -主键索引 PRIMARY KEY：加速查找+约束（不为空、不能重复）
    -唯一索引 UNIQUE:加速查找+约束（不能重复）

联合索引：
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

（3）、各个索引的应用场景

# 举个例子来说，比如你在为某商场做一个会员卡的系统。这个系统有一个会员表，有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(11)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

# 除此之外还有全文索引，即 FULLTEXT
会员备注信息 ， 如果需要建索引的话，可以选择全文搜索。
用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索。

# 其他的如空间索引SPATIAL，了解即可，几乎不用

（4）、索引的两大类型 hash 与 btree

# 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash 类型的索引：查询单条快，范围查询慢
btree 类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不 同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

（5）、操作索引： 创建和删除

<1> 创建：create index 索引名 on 表名(字段名);

create index  id on s1(id);
alter table s1 add index ix_sex(sex);

<2> 删除： drop index 索引名 on 表名;

drop index  id on 表名;

7、测试索引

（1）、准备数据

# 1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

# 2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<30000000)do
        insert into s1 values(i,'cai','male',concat('cai',i,'@yong'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

# 3. 查看存储过程
show create procedure auto_insert1G 

# 4. 调用存储过程
call auto_insert1();

（2）、 在没有索引的前提下测试查询速度

# 无索引：mysql根本就不知道到底是否存在id等于333333333的记录，只能把数据表从头到尾扫描一遍，此时有多少个磁盘块就需要进行多少IO操作，所以查询速度很慢
mysql> select * from s1 where id=333333333;
Empty set (0.33 sec)

（3）、 在表中已经存在大量数据的前提下，为某个字段段建立索引，建立速度会很慢

（4）、 在索引建立完毕后，以该字段为查询条件时，查询速度提升明显

注意：

• mysql先去索引表里根据b+树的搜索原理很快搜索到id等于333333333的记录不存在，IO大大降低，因而速度明显提升
• 可以去mysql的data目录下找到该表，可以看到占用的硬盘空间多大
• 需要注意，如下图

（5）、总结

# 1.一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引
# 2.在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。
#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI
MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.

8、正确使用索引

(1).只有对创建了索引的列进行条件筛选的时候效率才会高

(2).索引对应的列做条件不能参与运算、不能使用函数

(3).当某一列的区分度非常小(重复率高)，不适合创建索引

(4).当范围作为条件的时候，查询结果的范围越大越慢，越小越快

(5).like关键字：如果使用%/ 开头都无法命中索引

(6).多个条件：如果只有一部分创建了索引，条件用and相连，那么可以提高查询效率。（如果用or相连，不能提高查询效率）

and
   select count(*) from s1 where id=1000000  and email = 'eva1000000@oldboy';   # 查询速度加快
or
   select count(*) from s1 where id=1000000  or email = 'eva1000000@oldboy';    

(7).联合索引：联合索引是指对表上的多个列合起来做一个索引。联合索引的创建方法与单个索引的创建方法一样，不同之处仅在于有多个索引列。

creat index ind_mix on s1(id,name,email);
select count(*) from s1 where id=1000000  and email = 'eva1000000@oldboy';  # 快
select count(*) from s1 where id=1000000  or email = 'eva1000000@oldboy';   # 慢   条件不能用or
select count(*) from s1 where id=1000000;                                   # 快
select count(*) from s1 where email = 'eva1000000@oldboy';                  # 慢   要服从最左前缀原则
select count(*) from s1 where id>1000000  and email = 'eva1000000@oldboy';  # 慢   从使用了范围的条件开始之后的索引都失效