11-- mysql 索引 详解

2021年09月15日 阅读数:1
这篇文章主要向大家介绍11-- mysql 索引 详解,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

1、索引介绍

1.什么是索引node

索引是存储引擎中一种数据结构,或者说数据的组织方式,又称之为键key,是存储引擎用于快速找到记录的一种数据结构。
为数据创建索引就比如是为书建目录,或者说是为字典建立音序表,若是要查某个字,若是不使用音序表,则须要从几百页中逐页去查。

2.为什么要用索引mysql

通常的应用系统,读写比例在10:1左右,并且插入操做和通常的更新操做不多出现性能问题,在生产环境中,咱们遇到最多的、也是最容易出问题的,仍是一些复杂的查询操做,所以对查询语句的优化显然是重中之重。提及加速查询,就不得不提到索引了。
索引优化应该是对查询性能优化最有效的手段了。索引可以轻易将查询性能提升好几个数量级。

3.如何正确看待索引sql

错误的认知:
1.软件上线以后,运行了一段时间,发现软件很卡,想到要加索引
-出现软件上线以后才想着加索引,光把问题定位到索引身上都须要耗费很长的时间,排查成本很高。
-最好是在软件开发之初配合开发人员,定位到经常使用的查询字段。而后为该字段建立索引。

2.索引越多越好
-索引是用于加速查询的,下降写效率。
-若是是某一张表的.ibd文件中建立了不少棵索引树,意味着很小的一个update语句。 		
-就会致使不少棵索引树都须要发生变化,从而提升了硬盘的io。

索引是应用程序设计和开发的一个重要方面。若索引太多,应用程序的性能可能会受到影响。而索引太 少,对查询性能又会产生影响,要找到一个平衡点,这对应用程序的性能相当重要。

4.理解索引的储备知识数据库

1)储备知识1:机械磁盘一次IO的时间性能优化

一次磁盘IO带来的影响
7200转/分钟 ---------》 120转/s
一次的io的延迟时间  == 平均寻道时间(5ms)+平均延迟时间(4ms)  约等于9ms
# 寻道时间
道时间指的是磁臂移动到指定磁道所须要的时间,主流磁盘通常在5ms如下

# 旋转延迟
旋转延迟就是咱们常常据说的磁盘转速,好比一个磁盘7200转,表示每分钟能转7200次,也就是说1秒钟能转120次,旋转延迟就是1/120/2 = 4.17ms;

# 传输时间
传输时间指的是从磁盘读出或将数据写入磁盘的时间,通常在零点几毫秒,相对于前两个时间能够忽略不计
因此访问一次磁盘的时间,即一次磁盘IO的时间约等于5+4.17 = 9ms左右
这9ms对于人来讲可能很是短,但对于计算机来但是很是长的一段时间,长到什么程度呢?
一台500 -MIPS(Million Instructions Per Second)的机器每秒能够执行5亿条指令,由于指令依靠的是电的性质,换句话说执行一次IO的时间能够执行约450万条指令,数据库动辄十万百万乃至千万级数据,每次9毫秒的时间,显然是个灾难。

2)储备知识2:磁盘的预读bash

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

*一页就是一个磁盘块(block块),innodb存储引擎一页16k,即一次io读16k到内存中

3)储备知识3:索引原理精髓提炼数据结构

索引的目的在于提升查询效率
索引的根本原理就是把硬盘的io次数降下来
为一张表中的一行行记录建立索引,就比如是为书的一页页内容建立目录
有了目录结构以后,咱们之后的查询都应该经过目录去查询

本质都是:
经过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件。
也就是说,有了这种索引机制,咱们能够老是用同一种查找方式来锁定数据。

5.索引的优缺点ide

# 优势
一、极大地加速了索引过程,减小IO次数
二、建立惟一索引,保证了数据库表中的惟一性
三、加速了表与表之间的链接
四、针对分组和排序检索时,可以显著减小查询查询中的分组和排序

# 缺点
一、索引表占据物理空间
二、数据表中的数据增长、修改、删除的同时须要去动态维护索引表,下降了数据的维护速度
2、索引的分类

索引模型分为不少种类性能

#===========> B+树索引(等值查询与范围查询都快)
二叉树->平衡二叉树->B树->B+树

#===========> HASH索引(等值查询快,范围查询慢)
将数据打散再去查询

#===========> FULLTEXT:全文索引 (只能够用在MyISAM引擎)
经过关键字的匹配来进行查询,相似于like的模糊匹配
like + %在文本比较少时是合适的
可是对于大量的文本数据检索会很是的慢
全文索引在大量的数据面前能比like快得多,可是准确度很低
百度在搜索文章的时候使用的就是全文索引,但更有多是ES

不一样的存储引擎支持的索引类型也不同大数据

  • InnoDB存储引擎
支持事务,支持行级别锁定,支持 B-tree(默认)、Full-text 等索引,不支持 Hash 索引;
  • MyISAM存储引擎
不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
  • Memory存储引擎
不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;

分类

# 1.普通索引index :加速查找
create table t2(
		id int ,
		class_name varchar(10) unique,
		name varchar(16),
		age int
	);
	
    create index xxx on t1(name); # 增长索引
    drop index xxx on t1;  #删除索引
    
# 2.主键索引:primary key :加速查找+约束(不为空且惟一)
    
	create table t1(
		id int primary key auto_increment,
		class_name varchar(10),
		name varchar(16),
		age int
	);
	alter table student add primary key t1(id); # 增长索引
	alter table student drop primary key;  #删除索引
	
# 三、惟一索引:unique:加速查找+约束 (惟一)
	alter table country add unique key t1(class_name); # 增长索引
	alter table t1 drop index t1;  #删除索引
	
# 四、全文索引fulltext :用于搜索很长一篇文章的时候,效果最好。

# 五、空间索引spatial :了解就好,几乎不用

两种索引hash与btree

咱们能够在建立上述索引的时候,为其指定索引类型
# hash类型的索引:
查询单条快,范围查询慢 (适合等值查询,不适合范围查询)
#  btree类型的索引:
b+树,层数越多,数据量指数级增加(一般都使用btree,由于innodb默认支持它)

由于mysql5.58版本以后默认的存储引擎是innodb,而innodb存储引擎的索引模型/结构是B+树,因此咱们着重介绍 B+树

三 、索引的数据结构

ps: 建立索引的两大步骤

为某个字段建立索引,即以某个字段的值为基础构建索引结构,那么如何构建呢?为两大步骤

  • 一、提取每行记录中该字段的值,以该值看成key,至于key对的value是什么?每种索引结构各不相 同
  • 二、而后以key值为基础构建索引结构 之后的查询条件中使用了该字段,则会命中索引结构

# 一、为user表的id字段建立索引,会以每条记录的id字段值为基础生成索引结构
create index 索引名 on user(id);

使用索引
select * from user where id = xxx;

# 二、为user表的name字段建立索引,会以每条记录的name字段值为基础生成索引结构
create index 索引名 on user(id);

使用索引
select * from user where name = xxx;

那么索引的结构到底长什么样子,让其可以加速查询呢?

innodb存储引擎默认的索引结构为B+树,而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来 的

1.数据结构

任何一种数据结构都不是凭空产生的,必定会有它的背景和使用场景,咱们如今总结一下,咱们须要这种数据结构可以作些什么,其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么咱们就想到若是一个高度可控的多路搜索树是否能知足需求呢?就这样,b+树应运而生。

11-- mysql 索引 详解_主键

如上图,是一颗b树,关于b树的定义能够参见B树,这里只说一些重点,浅蓝色的块咱们称之为一个磁盘块,能够看到每一个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P一、P二、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即三、五、九、十、1三、1五、2八、2九、3六、60、7五、7九、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如1七、35并不真实存在于数据表中。

2.b树的查找过程

如图所示,若是要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找肯定29在17和35之间,锁定磁盘块1的P2指针,内存时间由于很是短(相比磁盘的IO)能够忽略不计,经过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,经过指针加载磁盘块8到内存,发生第三次IO,同时内存中作二分查找找到29,结束查询,总计三次IO。真实的状况是,3层的b+树能够表示上百万的数据,若是上百万的数据查找只须要三次IO,性能提升将是巨大的,若是没有索引,每一个数据项都要发生一次IO,那么总共须要百万次的IO,显然成本很是很是高。

11-- mysql 索引 详解_数据_02

2.b树的性质

#  1)索引字段要尽可能的小:
经过上面的分析,咱们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每一个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N必定的状况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,若是数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为何每一个数据项,即索引字段要尽可能的小,好比int占4字节,要比bigint8字节少一半。这也是为何b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度降低,致使树增高。当数据项等于1时将会退化成线性表。

# 2)索引的最左匹配特性(即从左往右匹配):
当b+树的数据项是复合的数据结构,好比(name,age,sex)的时候,b+数是按照从左到右的顺序来创建搜索树的,好比当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来肯定下一步的所搜方向,若是name相同再依次比较age和sex,最后获得检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪一个节点,由于创建搜索树的时候name就是第一个比较因子,必需要先根据name来搜索才能知道下一步去哪里查询。好比当(张三,F)这样的数据来检索时,b+树能够用name来指定搜索方向,但下一个字段age的缺失,因此只能把名字等于张三的数据都找到,而后再匹配性别是F的数据了, 这个是很是重要的性质,即索引的最左匹配特性

3.Btree介绍

#  索引是如何加速查询的,它的原理是啥?
索引模型/结构从二叉树-》平衡二叉树-》b树最后到b+树,每种树到底有什么问题最终演变成到了b+树
=================================================================================
# 0、建立索引的两个步骤
  create index xxx on user(id);
  一、提取索引字段的值看成key,value就是对应的本行记录
  10 -------------> 10 zs
  7 --------------> 7 ls
  二、以key的为基础比较大小,生成树型结构
  leaf node:叶子节点
  non-leaf node:根节点、树枝节点
# 一、索引究竟是一种什么样的数据结构:B+树
  二叉树、平衡二叉树、B树=》B+树
  
#二叉树:
  左节点的key值小于当前节点的key,而右节点的key大于当前节点,可是不能提速  -- #三次查找
  create index idx_id on use(id);
  select * from user where id =100;
  
 # 平衡二叉树:
    左子树与右子树的高度差不超过1
 # 一次iO就读一次节点,就至关于一辆卡车,只拉一个快递(一个节点只读一个一页到内存)
    每一个节点只放了一条数据,至关于innodb存储引擎共16k的数据只放了一条数据,几个字节,浪费了许多

 # B树:
      一次io读入内存是一页数据,或者叫一个磁盘块的数据,磁盘块里包含了n个节点
      ps:根节点页常驻内存 -- #两次查找
 问题:页中的节点既存放key又存放对应记录值(values --》对应的是一行的完整记录)
		
# B+树:
    一、非叶子节点只放key(根、树枝节点放key),只有叶子节点才放key:value  --》非叶子节点能存放的key个数变多,衍生出指针越多,树会变得更矮更胖
    二、叶子节点也指针指向,是有序排列的,这意味着B+树在排序操做上有自然的优点(范围查询速度快)# (由于提早排好了,再次查找的时候不须要从头再找)--》叶子节点是双向链接的  
    select * from user where id > 18;
     先找到id=19所在的叶子节点,而后只须要根据叶子节点的链表指向向右查找下一个节点(id =1九、id=20...)即不须要在回根节点查找
    三、叶子节点内的key值是单向链表,叶子节点和叶子节点之间是双向链表。即全都排好序了  --》排序会很快   
    四、一个页、一个磁盘块、一个节点固定大小16k,能够存放的数据量就不少
      安装每一个节点存放1000数据来算,三层的B+树能够组织多少条数据1000  *1000 * 1000
    
    # 特色:
       一、B树只擅长等值查询
       二、B+树擅长等值查询、范围查询
				
create index idx_id on user(id);
select * from user where id = 28;
select * from user where id > 18 and id < 28;
# id=19

# 总结:
只要叶子节点才是存放的数据是真实的,其余数据都是虚拟数据的
树的层级越高,所经历的步骤就越多(树越高,查询的层级就越多)
树越低越好,查询速度越快

# 若是存1000个数据,那么三层B+树能够存放上10亿条数据 --------》 树的高度是几,那个n就是几 n的三次方
4、汇集索引与非汇集索引

B+树主要分为两种索引结构:汇集索引和非汇集索引

  1. 汇集索引(又称聚簇索引、主键索引,一张表必须有且只有一个):以innodb做为存储引擎的表, 表中的数据都会有一个主键,即便你不建立主键,系统也会帮你建立一个隐式的主键。这是由于innodb是把数据存放在B+树中的,而B+树的键用的就是主键,在B+树的叶子节点中,存储了表中全部的数据。这种以主键做为B+树索引的键值而构建的B+树索引,咱们称之为汇集索。
# 命中主键索引查询
select * from user where id=2;

汇集索引的好处之一:它对主键的排序查找和范围查找速度很是快,叶子节点的数据就是用户所要查询的数据。如用户须要查找一张表,查询最后的10位用户信息,因为B+树索引是双向链表,因此用户能够快速找到最后一个数据页,并取出10条记录

汇集索引的好处之二:范围查询(range query),即若是要查找主键某一范围内的数据,经过叶子节点的上层中间节点就能够获得页的范围,以后直接读取数据页便可

2.非汇集索引(又称非聚簇索引、辅助索引,一张表能够建立多个辅助索引):以主键之外的列值做为键值构建的B+树索引,咱们称之为非汇集索引。非汇集索引与汇集索引的区别在于非汇集索引的叶子节点不存储表中的数据,而是存储该列对应的主键,想要查找数据咱们还须要根据主键再去汇集索引中进行查找,这个再根据汇集索引查找数据的过程,咱们称为回表。

# 建立辅助索引
create index xxx on user(name);

# 命中辅助索引查询
select * from user where name="yyy";

明白了汇集索引和非汇集索引的定义,咱们应该明白这样一句话:数据即索引,索引即数据。
一张innodb存储引擎表中必须有且只有一个汇集索引,可是能够有多个辅助索引。

5、覆盖索引与回表操做

1.回表操做
命中了辅助索引,而后要找的字段值不存在与辅助索引的叶子节点上,则须要根据拿到的主键值再去汇集索引中查询一遍,而后再汇集索引的叶子节点找到你想要的内容,这就叫回表操做。
例如:

# 建立辅助索引
create index xxx on user(name);

# 下述语句,命中了辅助索引,可是select须要查询出的除了辅助索引叶子节点有的name字段值外,还想要age字段的值,那么须要进行回表操做
select name,age from user where name="yyy";

2.覆盖索引
命中了某棵索引树,而后在其叶子节点就找到了你想要的值,即不须要回表操做,就是覆盖了索引。
例如:

create index xxx on user(name); 
  
# 下述语句,覆盖了索引 
select name from user where name="yyy";

使用主键字段看成条件,百分百覆盖了索引,效率极高,推荐使用

# 若是id字段是主键,那么下述语句也覆盖了索引 
select * from user where id=3;
6、MySQL索引管理

索引的功能:

1.加速查找;

2.mysql中的primary key,unique,联合惟一也都是索引,这些索引除了加速查找之外,还 有约束的功能

B+树常见的索引分类(innodb存储引擎默认)

汇集索引:即主键索引,primary key

用途:
1. 加速查找
2. 约束(不为空、不能重复)

惟一索引:uniqe

用途:
1. 加速查找
2. 约束(不能重复)

普通索引(即非聚簇索引):index

用途:
1. 加速查找

联合索引:

primary key(id,name): 联合主键索引 
unique(id,name): 联合惟一索引 
index(id,name): 联合普通索引

联合索引与最左前缀匹配原则

create index xxx on t1(id,name,age);
  
查询条件中出现
id name age
id name
id age
id 	

联合索引时指对表上的多个列合起来作一个索引。联合索引的建立方法与单个索引的建立方法同样,不一样之处在仅在于有多个索引列。
索引的最左匹配特性,如上所示,定义联合索引必须含有id字段,查询数据是按照从左到右的顺序来创建搜索树的。

建立/删除索引的语法

# 建表时指定索引
mysql> create table t1(
    -> id int primary key auto_increment,
    -> class_name varchar(16) unique,
    -> name varchar(16),
    -> age int
    -> );
Query OK, 0 rows affected (0.00 sec)

# 添加非聚簇索引指定为name字段
mysql> create index xxx on t1(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查看表结构
mysql> desc t1;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| class_name | varchar(16) | YES  | UNI | NULL    |                |
| name       | varchar(16) | YES  | MUL | NULL    |                |
| age        | int(11)     | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
| PRI | 主键索引
| MUL | 普通索引
| UNI | 惟一键索引

# 删除非聚簇索引
mysql> drop index xxx on t1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 建立一张没有指定任何索引的表
# mysql会默认建立一个非空且惟一的看成隐式主键
mysql> create table t2(
    -> id int,
    -> class_name varchar(16),
    -> name varchar(16),
    -> age int
    -> );
Query OK, 0 rows affected (0.00 sec)

# 添加主键索引
mysql> alter table t2 add primary key t1(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除主键索引
mysql> alter table t2 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0


# 建立惟一索引,不知道索引名,mysql默认会将索引名建立成表名
mysql> alter table t2 add unique key t2(class_name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查看表的详细信息
mysql> show create table t2;
UNIQUE KEY `t2` (`class_name`)

# 删除惟一索引
# 第一个t2是表名,第二个t2是mysql默认建立的索引名
mysql> alter table t2 drop index t2;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0


# # 添加非聚簇索引指定为name字段
mysql> create index xxx on t1(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 删除非聚簇索引
mysql> drop index xxx on t1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

案例

1.建立索引
-在建立表时就建立(须要注意的几点)
    create table s1(
    id int ,#能够在这加primary key
    #id int index #不能够这样加索引,由于index只是索引,没有约束一说,
    #不能像主键,还有惟一约束同样,在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30),
    #primary key(id) #也能够在这加
    index(id) #能够这样加
    );
    
 -在建立表后在建立
    create index name on s1(name); # 添加普通索引
    create unique age on s1(age);  # 添加惟一索引
    alter table s1 add primary key(id); # 添加住建索引,也就是给id字段增长一个主键约束
    create index name on s1(id,name); # 添加普通联合索引

2.删除索引
    drop index id on s1;
    drop index name on s1; #删除普通索引
    alter table t2 drop index t2;#删除惟一索引,就和普通索引同样,不用在index前加unique来删,直接就能够删了
    alter table s1 drop primary key; #删除主键(由于它添加的时候是按照alter来增长的,那么咱们也用alter来删)
    
3.查看索引
	show index from s1;