MySQL
不同引擎对比
InnodDB
- 支持行锁
- 支持外键
- 支持在线热备份
- 支持事物
- 5.6之前不支持全文搜索
MyISAM
- 支持全文索引:使用 match against来查找
- 支持空间数据索引
memory
- 支持哈希索引
一条SQL查询语句是怎么执行的?
连接器
- 客户端连接mysql服务器,用户角色权限检查
缓存查询
- 查看sql语句是否执行过,返回缓存结果(返回结果前检查语句的执行权限)(8.0版本已经彻底删除)
分析器
- 对SQL语句进行 “词法分析”、“语法分析”、表是否存在、列是否存在等(要做什么)
优化器
- 索引选择和 join连接顺序优化(怎么做)
执行器
- 检查查询权限,调用 对应表的存储引擎的接口,执行相关操作
存储引擎
- 存储数据,提供读写接口
日志系统
redo log
- 重做日志,InnoDB独有,保证数据库宕机后,能够之前提交的数据(crash-safe)
- 记录的是这个数据页做了什么修改
- 更新操作时,先将记录写到redo log,再更新记录到内存数据页,系统空闲时,再将该操作记录更新到磁盘
- 空间固定,循环写
binlog
-
归档日记,Server层的日志,所有引擎可用
-
记录一个完整的更新SQL命令(Statement模式)或者记录行的更新前和更新后状态(Row模式)
-
追加写
-
使用场景
- 主从复制
- 数据恢复
-
参数
-
max_binlog_size设置每个binlog文件的大小
-
sync_binlog 设置日志刷盘时机
- 0 :系统自行判断何时刷盘
- 1 :每次提交时,都将binlog写入磁盘
- N :每N个事务,才会写入磁盘
-
两阶段提交
- 保证数据库发生宕机时,两个日志文件的逻辑状态能够保持一致
为什么同时需要redo log和binlog
- binlog只适用于归档,没有crash-safe能力
- redo log 是innoDB特有,日志刷盘后会被覆盖
- 二者同时记录,才能保证数据库宕机后,数据不会丢失,提交事务能够重做
事务
事务的特性(ACID)
-
原子性
-
一致性
-
隔离性
- 子主题 1
-
持久性
事务隔离级别
启动参数 transaction-isolation
数据库里面会创建一个视图,访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下,这个视图是在事务启动时创建的,整个事务存在期间都用这个视图。在“读提交”隔离级别下,这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是,“读未提交”隔离级别下直接返回记录上的最新值,没有视图概念;而“串行化”隔离级别下直接用加锁的方式来避免并行访问。
-
读未提交
- 一个事务还没提交,它做的变更就能够被别的事务看到
-
读提交
- 一个事务提交后,它所做的变更才能够被别的事务看到
-
可重复读
(MySQL 默认)
- 一个事务执行的过程中,它所看到的数据总是和事务启动前的数据一致
-
串行化
- 对同一行记录进行加锁,“写”加“写锁”,“读”加“读锁”。当读写锁冲突时,后访问的事务需要等前一个事务完成后,才能够执行
避免长事务
- 通过set autocommit=1 显式开启事务
索引
InnoDB 采用B+树结构
B+ 树能够很好地配合磁盘的读写特性,减少单次查询的磁盘访问次数。
每一个索引在InnoDB里对应一颗B+树
索引类型
-
主键索引(聚簇索引)
- 叶子节点存放整行数据
-
非主键索引(二级索引)
- 叶子节点存放主键的值
回表
- 即普通索引查询方式,搜索非主键索引树,找到主键的值,在搜索主键索引树,找到结果
索引维护
- 采用自增主键,避免插入和删除时,需要对索引树进行“页分裂”和"页合并"
- 尽量使用主键查询
- 主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小
覆盖索引
- 可以通过普通索引查找的记录,不需要在通过主键索引回表查找,减少了树的搜索次数和IO操作
联合索引
根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。
-
最左前缀原则
- 联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符
索引下推
like 'hello%’and age >10 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age<10的数据,再进行回表查询,减少回表率,提升检索速度。
索引选择错误的解决方案
- 通过 force index()的方式显示指定索引
锁
全局锁
- 一般用在全库逻辑备份
- Flush tables with read lock (FTWRL):全库处于只读状态
表级锁
-
表锁:lock tables … read/write
-
MDL(元数据锁)
- 不需要显式使用,在访问表的时候会被自动加上,保证读写正确
- 对表进行增删改查时,加MDL读锁
- 对表的结构进行更改的时候,加MDL写锁
- 读锁之间不互斥,读写之间互斥
行锁
-
两阶段锁协议
在 InnoDB 事务中,行锁是在需要的时候才加上的(加锁阶段),但并不是不需要了就立刻释放,而是要等到事务结束时才释放(解锁阶段)。
-
解决死锁方法
当并发系统中不同线程出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待的状态,称为死锁。
-
超时自动释放
- 通过参数innodb_lock_wait_timeout设置
-
死锁检测,回滚
- 如果并发线程太多,死锁检测的过程就会消耗大量CPU 资源,会导致每秒执行不了几个事务。
- 控制并发度
-
XMind - Trial Version