首页  思维导图  详情

mysql

2020-11-20 17:44:31   54  举报





AI智能生成

msyql

MySQL主从复制原理

MySQL

作者其他创作

大纲/内容

存储引擎

MYISAM

数据文件和索引文件分开（非聚集）frm+MYD+MYI

不支持事务，不支持行级锁

innodb

表数据文件就是按B+tree组织的索引结构文件(聚集)，frm（表结构）+ibd

支持事务

包含了完整的数据记录

主键索引包含数据，非主键索引不包含数据，只包含主键值，（基于数据一致性与节省空间考虑）

支持事务（TRANSACTION）
支持行级锁

优化

MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
效率高，filesort效率低。

、order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列。
2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

、如果order by的条件不在索引列上，就会产生Using filesort。

单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；

双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行
数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段

能用覆盖索引尽量用覆盖索引

group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
的限定条件就不要去having限定了

分库分表

垂直拆分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据
库上面，这样也就将数据或者说压力分担到不同的库上面

优点

1拆分后业务清晰，拆分规则明确

2. 系统之间整合或扩展容易。

3. 数据维护简单。

缺点

1. 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。

2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。

3. 事务处理复杂。

水平拆分

垂直拆分后遇到单机瓶颈，可以使用水平拆分。相对于垂直拆分的区别是：垂直拆分是把不同的表拆到不同的数据
库中，而水平拆分是把同一个表拆到不同的数据库中。
相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中
包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到
一个数据库，而另外的某些行又切分到其他的数据库中，主要有分表，分库两种模式

优点

1. 不存在单库大数据，高并发的性能瓶颈。

2. 对应用透明，应用端改造较少。

3. 按照合理拆分规则拆分，join操作基本避免跨库。

4. 提高了系统的稳定性跟负载能力。

缺点

1. 拆分规则难以抽象。

2. 分片事务一致性难以解决。

3. 数据多次扩展难度跟维护量极大。

4. 跨库join性能较差。

数据结构

B+Tree

非叶子节点，不存储数据，只存数索引（冗余），存储更多的数据

叶子节点，包含所有索引字段

叶子节点用指针连接，提高区间访问性能

b-tree

叶节点，具有相同的深度，叶节点指针为空

所有索引不重复

节点中的数据索引从左到有一次递增

索引

原则

最左前缀原则

explain执行计划

列：id越大执行优先级越高，一样大谁在前，谁先执行

selectType:,,,

simple:：简单查询

primery:复杂查询中最外层的 select

subquery:包含在 select 中的子查询（不在 from 子句中）

derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为
派生表（derived的英文含义）

type

性能：system > const > eq_ref > ref > range > index > ALL

possible_keys

可能用到的索引

key

实际用的哪些key

key_len

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些
列。

计算规则：

字符串

char(n)=n个字节

varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n
+ 2

数值

tinyint：1字节

smallint：2字节

int：4字节

bigint:8个字节

时间

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半
部分的字符提取出来做索引。

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常
量），字段名（例：film.id）

rows

mysql预估要读取的条数

Extra列

Using index：使用覆盖索引

Using where：使用 where 语句来处理结果，查询的列未被索引覆盖

Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范
围；

Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行
优化的，首先是想到用索引来优化。

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘
完成排序。这种情况下一般也是要考虑使用索引来优化的。

Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引
的某个字段是

锁

性能

乐观

悲观

操作类型

读锁（悲观，共享），同一数据允许多个操作同时进行读，

写锁（悲观，排他），写操作没有完成之前，不允许其他写锁和读锁

颗粒度

表锁

锁整张表，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲
突的概率最高，并发度最低；

加锁：lock table 表名称 read(write),表名称2 read(write);

查看锁：show open tables;

解锁：unlock tables;

读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁
冲突的概率最低，并发度最高。

支持事务

原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执
行,要么全都不执行。
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意
味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束
时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并
发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是
不可见的,反之亦然。
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系
统故障也能够保持。

并发事务处理带来的问题

更新丢失

　当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每
个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其
他事务所做的更新

脏读

事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基
础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求

不可重读

事务A读取到了事务B已经提交的修改数据，不符合隔离性

幻读

事务A读取到了事务B提交的新增数据，不符合隔离性

事务隔离级别

读未提交

可能出现
脏读

可能出现不可重复读

可能出现幻读

读已提交

不能出现脏读

可能出现不可重复读

可能出现幻读

可重复读

不能出现脏读

不能出现不可重复读

可能出现幻读

可串行化

都不能出现

数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔
离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。
同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用
对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。
常看当前数据库的事务隔离级别: show variables like 'tx_isolation';
设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

优化

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql
尽量放在事务最后执行
尽可能低级别事务隔离