InnoDB 的索引模型
InnoDB中,表狗屎根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。
InnoDB使用了B+树索引模型，所以数据都存储再B+树中
索引类型分为主键索引和非主键索引
主键索引
主键索引又称为聚簇索引.如果没有设置主键索引,InnoDB会生成一个rowid作为主键id
非主键索引
非主键索引又称为二级索引，存储的是主键索引的的主键值
普通索引查询要先搜索普通索引的树，得到主键值，再再主键索引查询得到真正的内容。这个过程叫做回表。
普通索引需要多扫描一棵树，所以我没应该尽量使用主键查询。
索引维护
B+树为了维护索引的有序性，在插入新值的时候需要必要的维护。
如果插在中间的话，且数据页已满，需要申请新的数据页。然后挪动部分数据过去，这个过程称为页分裂。
页分裂，性能会受影响，还会影响数据页的利用率。（50%）
相邻两个页由于删数据，利用率很低之后，会将数据页合并。合并过程可以认为是分裂过程的逆过程。
索引的选择
新增
自增id，每次新增都是追加操作，不会涉及到挪动其他记录，也不会触发叶子节点分裂。所以适合做为唯一索引。
主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
只有一个索引，该索引是唯一索引时可以考虑用业务字段作为唯一索引。
查询
普通索引查询的时候会多做一次”查找判断下一条记录”的操作.但是由于查询是以数据页形式加载到内存中的,多出来的操作对cpu来讲几乎是可以忽略不计.
更新
如果数据页已经加载到内存中:
- 唯一索引,判断冲突,插入值,
- 普通索引都直接更新值.
数据页未加载到内存中:
- 唯一索引:先加载内存,判断冲突,插入值
- 普通索引:记录到change buffer
所以建议优先考虑普通索引
更新之后需要马上要访问这个数据页,会触发merge过程,增加change buffer的维护代价.此时考虑使用唯一索引
业务上需要使用唯一索引
覆盖索引
如果查询的结果为主键，或者是索引，联合索引覆盖了我们的查询需求，称为覆盖索引
覆盖索引，不需要回表，减少了搜索次数，显著提升查询性能
最左前缀原则
B+树这种索引结构,可以利用”最左前缀”,来定位记录.
第一原则:如果通过调整顺序,可以少维护一个索引,那么顺序往往就要优先考虑采用的.
第二原则:空间原则
数据页
当需要读一条记录的时候,并不是将这个记录本身从磁盘读出,而是以页为单位,将其整体读人内存.InnoDB默认内存大小是16K
change buffer
普通索引才有会使用change buffer,存在于内存中
需要更新一个数据的时候,如果这个数据页还没在内存中,在不影响数据一致性的前提下,InnoDB会将这些更新操作缓存在change buffer中.
将数据merge到原数据页:
- 访问这些数据
- 系统后台线程定期执行
- 数据库正常关闭
change buffer 因为减少了随机磁盘访问,所以更新性能会明显提升.
索引基数
mysql的优化器在索引选择的时候,并不能准确的知道满足这个条件的记录有多少条.只能通过采样法来获取索引基数.
索引选择异常的处理
可能的异常索引选择情况:
- 由于事务原因,导致扫描行判断错误,优化器认为全表扫描,因为不需要回表.会比走索引快.
- 由于order by b limit 1 导致系统认为需要对b排序,而破坏了 以小驱大.
解决办法:
- 通过force index指定索引
- 修改语句指导索引
-
给字符串加索引
直接传教索引,缺点:占空间
- 前缀索引:缺点增加扫描次数,不能使用覆盖索引
- 倒序存储,再建前缀索引(用于字符串前缀区分度不高,比方身份证),每次查询需要reverse函数额外开销
-
数据刷新到磁盘
脏页
干净页
刷新脏页（flush）到磁盘
InnoDB的redo log 满了。系统会停止更新操作，把checkpoint往前推。尽量避免
系统内存不足，需要新的内存页。如果淘汰的是脏页，这就触发flush。常态，一个查询淘汰脏页多，日志写满。会导致性能问题。
系统不满的时候就会flush脏页。
MySql正常关闭，MySql会把所以的脏页都flush到磁盘中。Buffer pool
InnoDB刷脏页的控制策略
innodb_io_capacity设置成磁盘的IOPS
InnoDB会根据脏页的比例，和redo log的日志数量值，取较大的 设为R%*设置的innodb_io_capacity来控制脏页的速度刷脏页的连坐机制
刷新脏页的时候会把附近的脏页一起flush。会减少随机IO，但是在SSD磁盘IO不再是瓶颈，关闭会减少sql的响应时间。所以建议关闭连坐机制
InnoDB8.0之前是默认开启的，8.0默认是关闭的
innoBD_flush_neighbors = 0 关闭 1开启
order by 原理
例子:select city,name,age form t where city=’北京’ order by name limit 1000;
条件字段加索引
city 单独索引时
初始化sort_buffer确定放入排序的字段.(内存够的话放入,全部字段,内存不足的时候,只放需要排序的字段和主键id)
- 通过索引找到主键id
- 回表,根据主键查询数据放入sort_buffer
- 重复2,3直到条件不满足
- 对sort_buffer数据进行排序//也有可能通过外部排序来完成,通过sort_buffer_size来定义
- sort_buffer不是全部字段的话,再次回表
-
条件字段+order by字段联合索引
city,name 联合索引
根据索引获取主键id
- 回表获取三个字段返回
-
结果,条件,order by字段联合索引(覆盖索引)
city name age 联合索引
根据索引获取第一条数据
- 获取下一条数据直到获取所有满足条件的数据
函数操作索引会破坏索引值的有序性
显示函数操作:mounth(row) = 7
隐式操作:id+1 = 1000
隐式字符串编码转换.
此时优化器会放弃走树搜索
若有收获，就点个赞吧
0 人点赞
