索引
MyISAM索引
MyISAM索引是与行记录分开存储的,非聚集索引。
连续聚集的区域单独存储行记录;
主键索引的叶子节点存储主键与对应行记录的指针
普通索引的叶子节点存储索引列和对应行记录的指针
InnoDB索引
主键索引与行记录存储在一起,聚集索引
普通索引的叶子节点存储索引列和主键
InnoDB的表中必有聚集索引,有主键时主键为聚集索引,无则第一个唯一索引为聚集索引,否则InnoDB会创建一个隐藏的row_id列作为聚集索引
InnoDB索引建议使用递增值且不宜太长,递增的索引值不会引起索引树分裂,不需要进行行数据的移动。索引过长导致MySQL有限的缓存区存储数据减少,增加相应的IO操作
InnoDB回表查询
使用普通索引进行检索时,会先查询普通索引的索引树找到对应的主键,再根据主键信息到主键索引树中查询相应的行记录,即为回表查询。常见解决回表查询的方法就是索引覆盖,即将要查询的字段建立到联合索引中
索引结构
MySQL索引采用B+树结构,对比哈希和其他的树形结构,B+树的优势分析:
哈希结构查询,修改,插入,删除操作的时间复杂度为O(1),不过哈希结构只适合单条记录的操作,复杂的查询、分组、排序等需求时,该结构时间负责都变为O(n);
二叉树当数据量过多时,索引树的高度太高,查询效率比较低,每个节点只存储一条数据,导致磁盘IO操作增加
B树 m阶数降低了索引树的高度,所有节点均存储数据,中序遍历可以遍历所有的节点
B+树 只在叶子节点上进行数据存储,叶子节点之间增加链表,无需中序遍历即可获取所有的数据,便于范围查询
分库分表
分库分表解决的问题
性能瓶颈(数据量过大导致索引树过大,IO操作增加,查询效率缓慢)、高可用(单机和主备方式已无法满足)、安全性(不同类型的数据进行分类存储)
分库分表常用方案
垂直拆分:将字段较多的一张表中的不同属性拆分到不同的库表中,降低单表操作提高性能。拆分原则(长度较短,高频访问的字段放在同一张表中;长度较长,低频访问的在一张表中)
水平拆分:依据表中的某个字段,按照一定的规则将一张表中的数据拆分到多个库表中,拆分之后,每张表的结构一样,数据不一样,所有表中数据的并集为全量数据
缓存池
InnoDB缓存池
缓存池主要缓存表数据和索引数据,缓存池特点访问快,容量小。磁盘读写并非按需读取,而是按页(4K)读取,所以缓存时一般也是按页缓存数据,预读机制表名了缓存池会预先缓存一些数据
缓存页管理算法
LRU(least recently used)算法:每次将要读取数据所在的页放在缓存池的头部页,如果该页原本存在于缓存池中则结束操作,如果不在,则将缓存池尾部的页移除(引发的问题:预读失效:预读的数据均不在缓存池中,缓存池污染:一次查询大批量数据时会导致热点数据被批量移除缓存区)
InnoDB采用了优化版LRU算法:缓存池分为老年代和新生代,新生代的尾部连接老年代的头部;预读的数据加入到老年代头部,只有被使用时才添加到新生代头部。设置老年代停留时间阈值来解决缓存池污染的问题,只有当数据页在老年代中停留超过阈值时间,该数据页才会被加入到新生代中。
死锁
相关配置
show GLOBAL VARIABLES like '%lock%';
show GLOBAL VARIABLES like '%tx_isolation%';//查询数据库的隔离级别
innodb_deadlock_detect:开启/禁用 innodb的死锁检测,
innodb_lock_wait_timeout:锁申请时的最长等待时间
innodb_rollback_on_timeout:锁超时之后回滚整个事务还是当前语句