1、存储引擎
常见存储引擎
- Myisam:5.5之前默认引擎,支持表锁,不支持外键和事务,访问速度快
- InnoDB:支持事务,外键,支持行级锁,5.5之后默认存储引擎,5.6之后支持全文索引
- Memory:所有数据置于内存中,拥有极高的效率,但是重启数据会丢失,默认hash索引
- Archive:拥有很快的插入速度,但是查询相对差劲
- Federated:将不同的mysql服务器联合,逻辑形成一个完整的数据库,适合分布式场景
逻辑存储结构
- 表空间:ibd文件
- 段:数据段,索引段,回滚段
- 区:每个区1M,一个区64页
- 页:存储引擎磁盘管理的最小单元,每页16k,
- 行除了定义字段,还包含隐藏字段,事务id,回滚指针,隐藏主键id(没有主键情况下)
- 每次申请四到五个区,以保证页的连续性
2、树的区别
-
二叉树: 可能产生不平衡,顺序数据可能会出现链表结构,层级太深
-
平衡二叉树: 需要频繁自旋,维护结构,性能根据层级而定,性能不稳定
-
b+tree:
-
- 主键聚簇叶子节点存放索引和数据(单向链表维护叶子节点,MySQL优化为了双向链表),非叶子节点存放索引,便于区间查询,排序
- 插入演示:B+ Tree Visualization (usfca.edu)
-
红黑树:
- 层级不确定,无法评估除响应时间
- 不支持范围查询
- 二级索引非叶子节点存放索引,叶子节点存放索引和主键
3、索引
索引概述
- 索引是高效获取数据的数据结构
索引结构
- B+Tree(),两层1.8w,三层可存储2200w左右记录
- Hash(不支持范围查询,无法利用索引完成排序,精准匹配效率极高,只需匹配一次即可定位到数据)
索引优缺点
优点
- 大大加快查询速度
- 使用分组和排序时候可以显著减少分组和排序时间
- 唯一索引可以保证字段唯一
- 可以加速表与表之间的连接
缺点
- 创建和维护索引需要消耗时间,随着数据量增加时间也会增加
- 占用磁盘空间
- 对表进行urd操作时候也要动态维护,urd性能会下降
创建索引原则(我们对哪种数据创建索引)
- 数据量少的没必要创建,全表和用索引可能差不多
- 表层面:数据量大,且查询频繁,更新不频繁
- 字段层面:经常在where groupby orderby后的字段
- 索引层: 唯一的建立唯一索引,尽量联合索引,大文本尽量前缀索引
-
附加原则:
- 区分度较高
- 索引不易过多
- 索引不为null加上非空约束
- 索引长度尽量短
索引分类
按结构
按类型
- 主键索引:唯一且不为null,一个表只能有一个,(聚集索引:叶子节点下存储索引和数据,必须有,且只有一个)
- 唯一索引:唯一且只能有一个Null值(二级索引,叶子节点存储索引和主键)
- 普通索引:没有限制(二级索引,叶子节点存储索引和主键)
- 全文索引:like+%(InnoDB(5.6之后支持)默认3个字符,最大84,MyISam默认4最小1个字符)
按存储形式
聚集索引:必须有,且只有一个,没有会使用唯一索引聚簇,都没有则创建隐藏主键,叶子节点下存放行数据
二级索引:可以多个,叶子节点存放主键id,会回表查询,创建联合索引设计好的话,可避免回表
联合索引
- 对经常查询的多个字段创建组合索引
sql提示
- 多个索引下,可以提醒执行器使用哪个索引,建议使用,忽略使用,强制使用
覆盖索引
- 查询返回字段都在联合索引中会直接拿到数据,无需拿到主键再去回表查询数据
- 针对字段数据库较大的建立索引,缩小索引长度
单列/联合索引
- 避免单列索引在and情况下第二索引不生效,使用联合索引
索引失效
- 索引列进行了函数运算
- 没有遵循最有匹配原则
- 字符串类型索引没有加' ',造成隐士转换,导致索引失效
- 如果联合索引，最左满足，但是使用中间跳过了某个索引字段,会造成后面索引失效
- 范围查询右侧的列会失效,尽量是<= ,>=
- mysql优化器判定全表比用索引块
- or链接索引失效
4、性能分析
数据库的执行频次
- show session status like 'Com_____'; --查询当前会话统计结果
- show global status like 'Com_____'; --查询字数据库上次启动至今的结果
- show status like 'Innodb_rows_%';
慢查询日志
-- 查看慢日志配置信息
show variables like '%slow_query_log%’;
-- 开启慢日志查询
set global slow_query_log=1;
-- 查看慢日志记录SQL的最低阈值时间
show variables like 'long_query_time%’;
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time=4;
profile Sql执行查询
explain/desc执行计划查询
- 字段含义
-
- id 相同表示加载表的顺序是从上到下。
- id 不同id值越大，优先级越高，越先被执行。
- id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
-
type含义
-
extra含义
5、锁机制
锁的分类
按粒度分
-
全局锁:锁定全局,用于数据备份保证数据库完整性
-
表锁(加锁快,并发低,不会死锁):
- 表锁:锁定整张表
- 元数据锁:为了避免DML与DDL冲突,保证读写正确性
- 意向锁:为了避免加表锁时候,全局扫描行锁
-
行锁(加锁慢,锁冲突低,并发高,会死锁)
-
行锁:锁定单行数据
-
间隙锁:防止其他事务插入间隙,间隙锁可以共存,一个事务间隙锁,不影响另一个事务在同一间隙加锁
- 索引上等值查询(唯一索引),给不存在的数据加锁时,会优化为间隙锁
- 索引上等值查询(非唯一索引),向后遍历时最后一个值不满足查询需求时,会退化为间隙锁
-
临键锁:锁定当前数据和间隙(行锁+间隙锁)
- 索引上范围查询(唯一索引),会访问到不满足条件的第一个值为止
-
按类型分
- 读锁(共享):阻塞写,可读
- 写锁(排他):阻塞读写
行锁升级
- 行锁时针对唯一索引进行检索的,对已存在的记录进行等值匹配时,将自动优化为行锁
- 不通过索引条件检索数据时,行锁则会升级为表锁
6、事务
事务特性
- 原子性:要么全部成功,要么全部失败
- 一致性:事务完成后,必须使所有的数据都保持一致状态
- 隔离性:事务之间互不影响
- 持久性:事务一旦提交或者回滚,对数据库中的数据改变时永久的
事务隔离级别
- 读未提交:一个事务可以读取另一个事务未提交的数据(脏读,不可重复读,幻读)
- 读已提交:可读取另一个事务已经提交的事务(不可重复读,幻读)
- 可重复读(默认):事务开启时不在允许修改操作,可避免脏读,不可重复读但是会造成(幻读)
- 串行化:最高事务隔离级别,效率低下
事务原理
- 原子性:undo_log(逻辑日志),通过回滚日志保证事务原子性,不仅回滚需要,快照读也需要,不会立即删除
- 持久性:redo_log(物理日志),缓冲区的脏页刷新到磁盘的过程当中出现问题,通过redo_log进行回滚保证数据的持久性,只在回滚时需要,事务结束可被立即删除
- 一致性:undo_log+redo_log
- 隔离性:锁+mvcc(多版本并发控制)
MVCC
-
作用:快照读时候,通过mvcc来查找对应的历史版本
-
实现组件:
- 记录隐藏字段(最后一次修改事务id,回滚指针)
- undo_log版本链(头部最新记录,尾部最老)
- readView(当前活跃事务id集合,最小活跃事务id,预分配事务id,当前最大事务id+1,readView创建者的事务id)
7、大批量数据插入优化
主键顺序插入
批量插入减少IO,批量最好500左右
load加载数据至数据结构
-- 1、首先，检查一个全局系统变量 'local_infile' 的状态， 如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
-- 3、加载数据
/*
脚本文件介绍 :
sql1.log ----> 主键有序
sql2.log ----> 主键无序
*/
load data local infile 'D:\sql_data\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
关闭唯一性校验,加载后再打开
-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;
truncate table tb_user;
load data local infile 'D:\sql_data\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
SET UNIQUE_CHECKS=1;
