mysql索引

索引

索引是一种协助我们快速查找的一种数据结构，在mysql的Inndb引擎中，底层使用的是B+Tree

索引的创建

创建表时创建索引

create table t1_i1(
	id int primary key auto_increment,# 主键索引
	name varchar(20) unique,# 唯一索引
	age int ,
	gender varchar(2) check(gender in ('F','M')),
	email varchar(20),
	extr text,
	index age_i (age) # 普通索引
);

查看表中的索引信息

show index from t1_i1;

删除表中的索引

drop index age_i on t1_i1;
drop index name on t1_i1;

在已经创建好的表中添加索引

添加唯一索引

create unique index name_index on t1_i1(name);

添加组合索引

create index age_email_index on t1_i1(age,email);

添加普通索引

创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

create index extr_index on t1_i1(extr(20));

索引的使用

向表中插入100000条数据

create procedure insert100(in num int)
begin
	declare i int default 1;
	while i<num do
	insert into t1_i1 values(i,'name',i%18,'F','i@qq.com','extra');
	set i = i+1;
	end while;
end$

正确使用索引

索引失效：
     1.全值匹配我最爱
     2.最佳左前缀法则，如果索引了多列，要遵循最左前缀法则，指的是查询从索引最左前列开始，并且不能跳过索引中的列
     3.不在索引列上做任何操作（计算，函数，自动或手动类型转换），会导致索引失效
     4.不能使用索引范围条件中右边的列
     5.尽量使用覆盖索引
     6.在使用不等于(!=或者<>)的时候，会导致索引失效
     7.is null is not null 会导致索引失效
     8.like一通配符开头（‘%..'），会导致索引失效  （解决办法：使用覆盖索引）
     9.字符串不加单引号，会导致索引失效 
     10.少用or，or会致索引失效

示例

explain+sql查询执行计划

通过查询执行计划能干嘛？

​ 1.可以知道表的读取顺序

​ 2.数据读取操作的操作类型

​ 3.哪些索引可以被使用

​ 4.哪些索引被实际使用

​ 5.表之间的引用关系

​ 6.每张表有多少行被优化器查询

执行计划中包含的信息

id |:select的查询序列号，包含一组数字，表示select执行的顺序或者表的操作顺序，id的值越大，优先级越高   
select_type |:
	simple：表示简单的select，不包含子查询或union
	primary：查询中若包含任何子查询，则会被标记为primary
	SUBQUERY:子查询
	DERIVED:在from列表中包含的子查询被标记为DERIVED
	union:若第二个select出现在union后则被标记为union
		  若union包含在from的子查询中，则被标记为DERIVER
	UNION RESULT：union合并后的表
table |:这行数据是哪张表的
partitions | :
type |:ALL INDEX RANGE ref eq_ref const system NULL
	   从最好到最差的顺序依次是 system>const>eq_ref >ref>range>index>all
		system:表中只有一行数据
		const：表示通过一次索引就找到了，const用于比较primary key 或者unique索引，因为只匹				 配一行数据所以很快
		eq_ref：唯一性索引扫描，对于每一个索引键，表中只有一条数据与之匹配
		ref：非唯一性索引扫描，返回匹配某个单独值得所有行
		range：值检索给定范围的行
		index:FULL INDEX scan 全索引扫描 ，只遍历索引树，通常比all快
		 all：FULL Table Scan 全表扫描
possible_keys | :
		显示可能应用在这张表的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引被列出 ，		但不一定被使用
key  | :
		实际使用的索引，如果为null，则没有使用索引
		查询中若使用了复盖索引（查询列要被所建的索引覆盖），则该索引仅出现在key中
key_len | :
		key-len：显示的的字段为索引字段的 最大可能长度，并非实际长度
ref  | :
		索引的哪一列被使用，具体使用了什么值
rows  | :
		rows:根据表统计信息及索引引用情况，大概估算出所需的记录所要读取的行数
filtered | :		
Extra  :包含不适合在其他列出现但十分重要的信息
		1.using filesort：说明mysql会对数据使用一个外部排序，而不是按照表内的的索引顺序进行读取，mysql无法利用索引完成的排序操作叫做文件排序
		2.using temporary:使用了临时表保存中间结果，mysql在对查询结果排序时采用了临时表，常见于order by和分组查询group by
		3.using index：表示响应的select操作使用了复盖索引，避免访问了表的数据行，效果不错
           如果同时出现了using where表明索引被用来执行索引键值的查找
           s如果没有同时出现了using where表明索引被用来读取数据，而非执行查找动作
		 4.using join buffer：使用了连接缓存
		 5.using where

排序优化

order by：尽量使用Index的方式排序，避免使用FileSort

order by满足两种情况会使用index方式排序：

​ 1、order by使用索引最左前列

​ 2、使用where子句与order by字句条件组合成索引最左前列

如果排序字句不在索引上，filesort有两种算法

​ 双路排序：从磁盘读取所要查询的列，在buffer中排序，再从磁盘中读取数据（两次IO）

​ 单路排序：从磁盘读取所要查询的列，在buffer中排序（一次IO），一次将数据读入到buffer，需要更多的空间

聚集索引，非聚集索引，二级索引

聚集索引

create cluster index index_name on table(col1,col2)

聚集索引按表中的索引顺序来存储，也就是谁说，索引项的顺序与表中的顺序是一致的，对于聚集索引，叶子节点存储的数据是真实的数据行，不再有额外的数据页，在一张表中只能存在一个聚集索引。Innodb默认使用的是聚集索引

非聚集索引

create nocluster index index_name on table(col1,col2)

非聚集索引索引的顺序与表中的数据顺序无关，叶子节点存储的是指向数据行的指针

二级索引

存储的是记录的主键值，而不是数据的地址值

在Innodb中，主键是聚集索引，唯一索引，普通索引等都是二级索引

MyISAM使用的是非聚集索引

在使用二级索引查找数据时，首先根据二级索引找到主键值，在根据主键值再到聚集索引中查找数据

示例

下面我们通过一个具体的示例进行演示聚集索引和二级索引

pl_ranking（编程语言排行榜表）

该表包含3个字段，如下：
id:主键
plname：编程语言名称
ranking：排名

id	plname	ranking
15	C	2
16	Java	1
18	Php	6
23	C#	5
26	C++	3
29	Ada	17
50	Go	12
52	Lisp	15
…	…	…

id: 设置主键
plname: 普通索引

聚簇索引(主键索引)

####　聚集索引

从图中我们可以看到，索引数据和存储数据都是在一颗树上，存在一起的。通过定位索引就直接可以查找到数据。

这棵树是根据主键进行创建的。
如果查找id=16的编程语言，
select id, plname, ranking from pl_ranking where id=16;
则只需要读取3个磁盘块，就可以获取到数据。

二级索引（辅助索引）

image.png

从上图中我们发现，该B*tree根据plname列进行构建的，只存储索引数据，plname 和 id 的映射。

比如查找 编程语言为“Java”的数据。
select id, plname, ranking from pl_ranking where plname=’Java’;
首先通过二级索引树中找到 Java 对应的主键id 为 “16”。
然后在去主键索引中查找id为“16” 的数据

问题

1. 相比于叶子节点中存储行指针，二级索引存储主键值会占用更多的空间，那为什么要这样设计呢？

　InnoDB在移动行时，无需维护二级索引，因为叶子节点中存储的是主键值，而不是指针。

2. 那么InnoDB有了聚簇索引，为什么还要有二级索引呢？

   聚簇索引的叶子节点存储了一行完整的数据，而二级索引只存储了主键值，相比于聚簇索引，占用的空间要少。当我们需要为表建立多个索引时，如果都是聚簇索引，那将占用大量内存空间，所以InnoDB中主键所建立的是聚簇索引，而唯一索引、普通索引、前缀索引等都是二级索引

3. 为什么一般情况下，我们建表的时候都会使用一个自增的id来作为我们的主键？

   ​ InnoDB中表中的数据是直接存储在主键聚簇索引的叶子节点中的，每插入一条记录，其实都是增加一个叶子节点，如果主键是顺序的，只需要把新增的一条记录存储在上一条记录的后面，当页达到最大填充因子的时候，下一跳记录就会写入新的页中，这种情况下，主键页就会近似于被顺序的记录填满。

   　　若表的主键不是顺序的id，而是无规律数据，比如字符串，InnoDB无法加单的把一行记录插入到索引的最后，而是需要找一个合适的位置（已有数据的中间位置），甚至产生大量的页分裂并且移动大量数据，在寻找合适位置进行插入时，目标页可能不在内存中，这就导致了大量的随机IO操作，影响插入效率。除此之外，大量的页分裂会导致大量的内存碎片。