零碎知识点 -- 20200409
01.索引是帮助mysql高效获取数据的排序号的数据结构。
02.索引数据结构:(https://www.cs.usfca.edu/~galles/visualization/Algorithms.html)
- 二叉树(数据连续增长时候,树会失去平衡,极端情况可能会退化成链表,不会加速查询了)
- 红黑树(二叉树生长过程遇到的极端情况,红黑树可以通过左旋把非叶子结点调整到叶子节点,一定程度的平衡了二叉树)
- b-tree
* 节点中存放多个索引数据,从左到右递增排列
* 叶子节点都具有相同的深度,叶子节点的指针为空
* 所有索引元素不重复
- b+tree
* 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
* 叶子节点包含所有索引字段
* 叶子节点之间用指针连接,提高区间访问的性能(很方便的从当前叶子节点到下一个叶子节点)
* 一个节点通常为16KB(show global status like 'Innodb_page_size')
@ 节点中的索引和其子节点的地址是成对出现的大于占用空间【bigint(8B)+6B(指针)】,也即一个节点中包含16KB/14B=1170
@ 假设树的高度为3,假设叶子节点可以存储16个【索引+数据】
@ 那么当前数据结构可以容纳1170*1170*16结构为2100万个数据。
- hash表
* 主要用在等值查找,不适用于范围查找
* mysql对hash冲突解决的非常好
03.数据库存储引擎是针对数据库表的
04.myisam存储引擎对应文件结构(索引文件和数据文件是分离的-非聚集)
- table_name.frm 文件描述表的结构
- table_name.MYD 存储数据表中的记录
- table_name.MYI 存储索引信息
- 有索引的情况下查询时候,会查完myi,拿到索引后再次查myd(成为回表)
05.innodb存储引擎对应文件结构(innodb行级锁,使用不当变成表级的锁,多用于高并发环境)
- table_name.frm 文件描述表的结构
- table_name.ibd 存放数据和索引的文件
06.innodb索引实现(聚集)
- 表数据文件本身就是按照b+tree组织的一个索引结构文件
- 聚集索引的叶子节点包含了完整的数据记录
- 为什么InnoDB表必须有主键,并推荐使用整型自增主键?
* 数据是B+tree组织的,肯定要包含主键索引
* 使用整型存储空间少
* 使用整型检索排序快,uuid要转换为ascii码逐一比对
- 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省空间)
07.mysql一个select只会使用一个索引,虽然可能建立了多个索引,且索引的类型只有hash/btree两种。
08.联合索引(比单值索引要更常用)的底层存储结构:
- 单值索引里面都是单个列的值,联合索引里面有多个列的值,检索的时候,就会按照where条件一次使用每个节点里面的多列值进行定位。
09.explain工具的复习(https://www.bilibili.com/video/BV1Vs411M7Qi?p=19)
- explain 表的读取顺序,读取操作类型,可能会使用的索引,实际被使用的索引,表之间的引用,多少记录被优化器查询。
- 执行结果包含字段 id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra
- id如果相同:执行顺序从上到下(常见join查询);id不同:id大的优先查询(常见子查询);id相同不同时存在:结合前两个规则。
- select_type:有6个取值。
* derived:在from列表中包含的子查询被标记为derived(衍生)
* primary:sql中包含子查询时候,最外层查询被标记为primary
- type:取值范围:system > const > eq_ref > ref > range > index > all
* system: 表只有一行记录(等于系统表),是const类型的特例
* const: 表示通过索引一次就找到了,常见primary key 或unique出现在where列表中
* eq_ref: 表示唯一索引扫描,每个索引键,对应只有一条记录。常见主键或者唯一索引扫描。
* ref: 非唯一性扫描,返回匹配某个单独值的所有行,本质上是索引访问,可能返回多个结果,所以属于查找和扫描的混合体。
* range: 只检索给定范围的行,使用一个索引来选择行,explain的key列显示了使用的索引,一般sql中where用到了范围会有range,这个至少比全表扫描好。
* index: full index scan,区别与all的是只遍历索引树。
* all: 全表查询(上百万数据all肯定要优化了)
- key_len:越短越好,表示可能的最大长度,并非实际使用长度。根据表定义计算而得,不是通过检索而得。
- row: 表示多少行记录可能被加载到内存,这个值肯定是越少越好。
- extra(危险取值):
* using filesort: 是危险信号。表示排序没有按照索引建立的顺序进行排序。
* using temporary:使用了临时表保存了中间结果,对查询结果排序或分组的时候会使用临时表,常见order by 或group by
* using index: 表示select操作使用了覆盖索引(covering index),效率会不错,
若同时出现了using where表示覆盖索引用来执行索引键值的查找,
若没有出现using where表示通过索引来加载数据,而非执行查找动作。
10.sql优化
- 建立索引
- 避免索引失效
- explain sql,检查索引的使用情况,删除不好的索引,建立可靠的索引。
11.索引失效:
- 尽量使用全值匹配
- 最佳左前缀:查询按照索引的顺序,从最左方用,不要跳过,否则会导致索引失效(结合下面补的图来理解理解)。
- 不在索引列上做任何操作(计算、函数、类型转换(字符串不加引号也会失效索引)),否则失效导致全表扫描
- 尽量使用覆盖索引,尽量不要select *
- != <> 无法使用索引
- is null, is not null 无法使用索引
- like('%abc...'),索引失效,like('abc..%')不会导致失效。
- 用or连接会导致索引失效
12.索引建立原则:
- 排序字段
- where条件字段
- 使用覆盖索引(covering index):
* select列中的数据从索引中就可以获取到,不必读取数据行,查询的列被建立的索引覆盖了。
13.如果锁定数据库中的某一行:
- begin;
- select * from table where a=9 for update; --重点是for update
- 做一些操作
- commit;
14.行锁变成表锁:
- innodb索引失效后行锁会变成表锁,varchar没有添加引号,事务没有进行提交,别的事务会被阻塞。
15.间隙锁危害:
- where锁住了不存在的记录,会影响别的事务对范围内不存在的记录的添加。
16.volatile
- volatile修饰的变量对应的汇编指令前都有一个lock指令
- lock指令的功能
* 将当前处理器缓存行的数据立即写回到系统内存
* 这个写回内存操作会引起在其他cpu里缓存了该内存地址的数据无效(MESI协议,缓存一致性协议)
* 当前处理器处理被修饰的变量的时候会加一把锁,处理完之后会释放锁
- 内存屏障,有序性
17.mysql集群搭建
- io thread 读取主节点的binlog,保存中继binlog
- sql thread 读取中继过来的binlog,并执行
- binlog里面只记录增删改,不记录查询日志
- 常用命令:
* show binary logs;--查看所有的日志文件;
* show master status; --查看当前使用的日志文件,及日志position
* show binlog events in 'mysql-bin.000003'; --查看binlog日志
* start slave
* stop slave
* show slave status