#索引
功能:索引,为了查询更快,但是修改效率降低,因为要更新索引
索引作为文件存储到磁盘中,
磁盘单位为页,4k为1页.
磁盘预读:最好将相邻的数据都加载进内存以备访问,加载的数据一般为一页的整数倍,内存与磁盘以页为单位交换数据
磁盘io为数据库瓶颈
索引是什么
索引是帮助mysql高效查找的数据结构,如某索引记录了当前索引的数据在哪一个文件中,文件中的偏移量
索引存储在文件系统中,索引的文件存储形式与存储引擎有关
索引的数据结构
- b+树(叶子节点不存数据实例,可以存很多索引,底层节点用来存储数据,并将数据进行排序)
- hash(不可以范围查找,数据为无序,使用时需要将所有数据都加载到内存中,适合场景为等值查询)
- 二叉树(树过深)
- b树(叶子节点过大,存储的数据少)
数据越大,索引也就越大,有时内存无法存下这么大的索引,所以就存储一部分,用到时再io查询
存储引擎
表空间:把表所有数据都存放在一起
代表不同的文件格式,不同的存放方式(以下两种都是以本地文件系统)
- innodb
- myisam
不同的存储引擎,数据文件和索引文件存放的位置是不同的,因此有了分类
-
聚簇索引:数据文件和索引文件放一起,innodb、frm文件,存放表结构,ibd文件,存放表数据和索引
-
聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页
-
一般建表会用一个自增主键做*聚簇索引,没有的话MySQL会默认创建*
-
根据实际情况自行添加的索引都是辅助索引,辅助索引就是一个为了需找主键索引的二级索引
-
聚簇索引并不是一种单独的索引类型,而是一种数据存储方式
-
Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。
-
优点:
1.数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
2.聚簇索引对于主键的排序查找和范围查找速度非常快
缺点:
1.插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于InnoDB表,我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB表,我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。
-
-
辅助索引(非聚簇索引):数据文件和索引文件分开存放,myisam、myi 存放索引数据\ myd存放实际数据
- 在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。
innodb存储引擎
mysql的innodb引擎会默认将表数据存放于表空间中,不会为单独的表存放一份数据文件,如果要为单独的表存放数据文件,则需要设置set global innodb_file_per_table = on
存储结构:
- 表空间
- 段
- 区
- 页
- 行
从包含分
表空间-段-区-页-行
索引文件(默认创建主键索引)是b+树的数据结构,每次读取一小块
,找到指定页
b+树先将数据所在的页查询出来,再将页放到内存中查询行
树:左子树必须小于根节点,右子树必须大于根节点
-
二叉树:二分查找法,查找效率高.但是可能会出现左右不平衡的问题
-
avl树:二叉树可能出现左树过长成为链表,于是出现
平衡树
,平衡树会频繁进行旋转,最大层与最小层不会超过1,虽然树已经平衡,但是每插入一个数据需要进行旋转,对性能影响极大 -
红黑树:基于avl树的升级(不是一个严格意义上的平衡树),损失部分查询性能,提升插入性能.而且加入了
变色特性
,满足插入和查询性能的平衡 -
二叉树及其诸多变种无法支撑索引,因为树的深度无法控制
树过深
,插入数据性能拉垮. -
b树:多叉树(平衡多路查找树),一个分支会有多个节点,每个节点对应一个磁盘块(4k的整数倍),磁盘块内有指针指向其他磁盘块地址.,最终如果某指针指向了最底层叶子节点,那么索引指向数据就在其中
左右子树的排序标准为索引的值,左边为小于索引的值,右边为大于索引的值
,b+树可以选择每一个节点,每一个磁盘块可以理解为结构中的一个节点,这个节点内部结构为以下(磁盘块为单位)P1 16 p2 34 p3 pn(可以有非常多个)
data data ----->一个磁盘块
P1,p2,p3(指针)再指向下一层,每一个磁盘块中的指针,是有规定最多几个的,当前这个例子中一个磁盘块最多存放3个下层节点地址
16
与34
为建立索引的键值,如一值24
在16-24
中间,中间是p2指针,于是查找p2指针指向的磁盘块,于是p2块重复查找索引数据的查找性能瓶颈为
磁盘的io
每个磁盘块内都存储着索引对应的数据,如果数据很大则每一个磁盘块中的能存的数据就少,需要非常多的磁盘块才能存储所有索引,
key值过于分散,磁盘读取次数过多,由于树的深度如果很深,则io次数增加,性能越拉垮
-
b+树结构(平衡多路查找树),:在b树上作了优化,因为如果data较大,
则b树无法存过多的key值,key值过于分散,io次数过多...
优化为将存储在非叶子结点上的data数据,全部抽取出来存放到叶子结点上,最终数据全都在叶子结点上,磁盘块只用来计算索引在哪个区间中如 p1-16-p2-38-p3,查找索引为15的值就查看比16小则找p1,再查看叶子结点中的15索引,返回值,如果值等于16,则找p2结点,p1索引值永远小于16!- 降低b树的高度
- 每个非叶子节点可以存放更多的key,p指针变得更多,数据检索更快(每一个非叶子节点对应一个磁盘块)
- 每个磁盘块能放多少主键值(索引)
- 等值查询时可以直接定位目标,范围查询时,叶子节点之间是有指针互相指向的.
- 在b+上有两个指针,一个指向根节点,一个指向索引最小的叶子节点,同时所有叶子节点是一个闭环.于是就有两种查询方式了,一种是从根节点(主键)开始查询,一种是从叶子节点开始随机查询
索引数据:
在b+树中,key为索引列,value为页
- 聚簇索引:聚集索引中存放表的全部信息
- 辅助索引:辅助索引只包含索引列和一个行记录书签
索引:如果是内部列建立索引,则b+树的叶子节点存储列的主键id,得到主键id后再去主键索引树中查找(回表)
innodb有回表操作
mysaim
结构也是b+树
mysaim没有回表,直接找到数据
他的b+树叶子结点存储的是数据地址,地址存储着整条数据信息.
而MyISAM的B+树主键索引和辅助索引的叶子节点是数据文件的地址指针
索引分类
在决定为某个列创建索引时,要思考这个列创建索引时的b+树结构会是什么样的
-
主键索引
- 主键是一种唯一索引,必须指定为primary key
-
唯一索引
- 索引列的所有值只能出现一次,必须唯一,可以为空
-
普通索引
-
基本的索引类型,可以为空
-
覆盖索引:如
select * from table where name = mcj
则先去name索引树中查找,然后再去主键索引树中查找.
select id from table where name = mcj
则先去name索引树中查找,索引树中底层保存着主键,或者唯一键,直接返回(覆盖索引)
-
-
全文索引
- 全文索引的类型为fulltext,群文索引可以在varchar上创建
-
组合索引
-
name与age建立组合索引,多列值组成一个索引
-
name与age一起查询,age单独查询或者name单独查询.优化器会优化查询字段,遵从最左匹配原则,将name或者age放在条件的最左边
-
但是如果
-
select * from table where age = 1;
-
select * from table where age = 1 and name = 'mcj';
组合索引的匹配规则为最左匹配原则,比如建立了name,age的组合索引.那么上面的sql语句则是全表扫描,反之建立age,name的索引后,则会进行扫描索引效率提升.
组合索引的顺序是非常重要的.但是mysql的优化器会进行sql语句的顺序优化,使之符合组合索引
上面的sql建立组合索引有两种选择
-
age,name
- name
- 用户使用age查询,与使用name查询都可以走索引,但是如果不为name建立索引则单查询name是无法走索引的(遵循最左匹配,因为索引的最左为age,如果查询条件没有age则不走索引)
-
name,age
- age
两种方式关键在于单列索引,name的长度比age大每一页存放的数据小,代表数据页会很多,索引树庞大,而age长度小,代表一个数据页内可以维护很多age索引,索引树会很小,io次数会变少
name与age的位置,mysql优化器在优化我们的sql时会尽量吧查询列
按照顺序调换位置匹配上
-
索引下推
给内部列创建索引时会进行回表操作.
索引下推与索引最左匹配原则是为联合索引
服务的,只有多个索引的时候才会使用索引下推,
同时联合索引的b+树也与单列不通,联合索引的b+树每个节点都有索引值存在,所以name、age等属性可以直接匹配上
1,张三,10
2,张三,20
3,张三,10
4,张三,40
select * from table where name = '张三' and age = 10
如上会先去匹配name为张三的数据,id为1,2,3,4.然后再去id索引树中查找4条记录再判断age是否匹配10.
现在会先去先去匹配name为张三的数据,id为1,2,3,4 再匹配age的数据
mysql5.6以前,查询到name为张三的有4条数据,然后进行4次回表操作
Mysql5.7查询到name为张三时,还会匹配age=10的数据,再进行回表
建立name,age,sex的联合索引后会生成一个索引树.key为name,age,sex
根据sql,匹配第一个列为张三的,都匹配上,再匹配第二个列age=10的匹配到张三,10,男 返回1,3.
索引下推是在非主键索引下的优化,可以有效的减少回表的次数
索引维护(插入新值)
索引在插入新值的时候,为了维护索引的有效性,必须对索引的位置进行维护
为什么设置成自增,自增数据添加时主键索引的b+树是往叶子节点追加数据.效率较高
不设置成自增则可能会从中间插入数据,可能会导致一下情况(为了保证有序)
- 插入较大值,直接插入无成本
- 插入中间值,需要移动后续元素空出位置
- 插入页如果满了则需要把一页分解为两页(等值,比如某4k页被分裂,新开辟了一个磁盘页占2k,原本页占2k,总共8k,占用4k,部分空间被浪费)成为页分裂,性能会收到影响,空间使用率也会浪费,比如51、52、54、55中间插入一个53,第一页为51、52,第二页变为53,54,55, 空出了一些空间,申请数据以4k为一个单位,同时有分裂就有合并.可能会合并空间为1页,比如删除了53
- 页合并
mysql执行流程
客户端-连接器-分析器-优化器-执行器-存储引擎-数据
日志
bin-log(mysql)
innodb日志缓冲区
-
redo-log-buffer
-
undo-log-buffer
-
redo-log(innodb)
当数据修改时,会先把记录写到redo-log中,并且更新内存,此时更新就算完成了,同时innodb会在
合适的时机
将redo-log中的数据写入磁盘.因为直接写磁盘是效率低下的,写一次就是一次io.一个例子:饭店需要记账,有一本厚厚的账本,如果每次用户来都需要往账本里查用户并且扣钱则非常耗时.所以在门口挂了一块黑板,把每次用户花的钱都记在黑板上,等黑板满了,就把黑板上的记录放到账本里 ,再擦除黑板.所以理论上不存在数据丢失,因为黑板是持久化的
mysql支持用户自定义使用何种方式刷到磁盘(3种)
- 每次提交写入osbuffer,同时写入磁盘(性能较差,但是非常安全)
- 每次提交写入osbuffer,内核每秒写入磁盘(性能尚可,可能会丢失一秒钟的数据)
- 每秒写入osbuffer,并写入磁盘
建议为每秒调用fsync的方式,默认为第一个
-
undo-log(innodb)
- undo-log用于事物的原子性,在操作任何数据前,会将数据备份到一个地方(undo-log),然后进行数据修改,如果出错了或者用户执行rollback,则利用undo-log中的备份将数据恢复
-
表锁与行锁
数据更新的流程
获取老数据
执行器从存储引擎中找到数据,如果在内存中直接返回.如果不在内存中则从磁盘查询后返回
更改数据
将数据写入内存中(innodb)
记录redo-log(innodb),通知执行器,数据处于prepare阶段
写入bin-log
提交事物,redo-log数据处于commit阶段
redo-log有两个状态是为了保证bin-log与redo-log中的数据一致性
mysql索引优化
mysql执行计划
explain select * from table
type至少是range级别
key
extra
jyperlogler算法计算基数
基数计算当前唯一列的数量
索引优化
- 索引分类
- 索引数据结构
- 索引结构的选择
- 索引覆盖
- 索引下推
- 索引失效
主键索引
创建表时,创建的唯一索引
唯一建
非空唯一索引
全文索引
组合索引
组合索引,如name,age,pos共同组合形成的索引
最左匹配
哈希索引
自适应哈希