Facade

MySQL各存储引擎使用了三种类型 (级别) 的锁定机制: 表级锁定, 行级锁定和页级锁定.
1.表级锁定 (table-level)
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制.该锁定机制最大的特点是实现逻辑非常简单, 带来的系统负面影响最小.所以获取锁和释放锁的速度很快.由于表级锁一次会将整个表锁定, 所以可以很好的避免困扰我们的死锁问题.
当然, 锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高, 致使并大度大打折扣.
使用表级锁定的主要是MyISAM, MEMORY, CSV等一些非事务性存储引擎.
2.行级锁定 (row-level)
行级锁定最大的特点就是锁定对象的颗粒度很小, 也是目前各大数据库管理软件所实现的锁定颗粒度最小的.由于锁定颗粒度很小, 所以发生锁定资源争用的概率也最小, 能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能.
虽然能够在并发处理能力上面有较大的优势, 但是行级锁定也因此带来了不少弊端.由于锁定资源的颗粒度很小, 所以每次获取锁和释放锁需要做的事情也更多, 带来的消耗自然也就更大了.此外, 行级锁定也最容易发生死锁.
使用行级锁定的主要是InnoDB存储引擎.
3.页级锁定 (page-level)
页级锁定是MySQL中比较独特的一种锁定级别, 在其他数据库管理软件中也并不是太常见.页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间, 所以获取锁定所需要的资源开销, 以及所能提供的并发处理能力也同样是介于上面二者之间.另外, 页级锁定和行级锁定一样, 会发生死锁.
在数据库实现资源锁定的过程中, 随着锁定资源颗粒度的减小, 锁定相同数据量的数据所需要消耗的内存数量是越来越多的, 实现算法也会越来越复杂.不过, 随着锁定资源颗粒度的减小, 应用程序的访问请求遇到锁等待的可能性也会随之降低, 系统整体并发度也随之提升.

读锁: 也叫共享锁, S锁, 若事务T对数据对象A加上S锁, 则事务T可以读A但不能修改A, 其他事务只能再对A加S锁, 而不能加X锁, 直到T释放A上的S 锁.这保证了其他事务可以读A, 但在T释放A上的S锁之前不能对A做任何修改.

写锁: 又称排他锁, X锁.若事务T对数据对象A加上X锁, 事务T可以读A也可以修改A, 其他事务不能再对A加任何锁, 直到T释放A上的锁.这保证了其他事务在T释放A上的锁之前不能再读取和修改A.

表锁: 操作对象是数据表.Mysql大多数锁策略都支持(常见mysql innodb), 是系统开销最低但并发性最低的一个锁策略.事务t对整个表加读锁, 则其他事务可读不可写, 若加写锁, 则其他事务增删改都不行.

行级锁: 操作对象是数据表中的一行.是MVCC技术用的比较多的, 但在MYISAM用不了, 行级锁用mysql的储存引擎实现而不是mysql服务器.但行级锁对系统开销较大, 处理高并发较好.

Innodb中基本锁有以下4种:
行级锁
1). 共享锁(S Lock) : 允许事务读一行数据
2). 排它锁(X Lock) : 允许事务删除或更新一行数据
表级锁
3). 意向共享锁(IS Lock): 事务想要获得一张表中某几行的共享锁
4). 意向排它锁(IX Lock): 事务想要获得一张表中某几行的排它锁
由于Innodb引擎支持的均为行锁, 所以意向锁其实不会阻塞除全表扫描之外的任何请求

Innodb中行锁有三种基本的算法:
1). record lock : 单个行记录上的锁
2). gap lock: 锁定一个范围, 但不包含记录本身
3). next-key lock : record lock + gap lock锁定一个范围, 并且锁定记录本身.避免幻读

1. 并发控制

1.1. 读写锁

在处理并发读写时, 可以通过实现一个由 2 种类型组成的锁系统来解决问题.

读锁
也称共享锁.
读锁是共享的, 也就是说相互不阻塞的.
多个客户端在同一时刻可以同时读取同一个资源, 而互不干扰.
写锁
也称排它锁.
写锁是排他的, 也就是说一个写锁会阻塞其他的写锁和读锁.
只有这样, 才能确保在给定的时间内, 只有一个用户能执行写入, 并防止其他用户读取正在写入的同一资源.

1.2. 锁粒度

表锁
行级锁

1.3. 死锁

死锁是指两个或多个事务在统一资源上相互占用, 并请求锁定对方占用的资源, 从而导致恶性循环的现象.
当多个事务试图以不同的顺序锁定资源时, 就可能产生死锁.
多个事务同时锁定同一个资源时, 也会产生死锁.

例如:

-- 事务1
START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE  stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE  stock_id = 3 and date = '2002-05-02';
COMMIT;

-- 事务2
START TRANSACTION;
UPDATE StockPrice SET close = 20.12 WHERE  stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET close = 47.20 WHERE  stock_id = 4 and date = '2002-05-01';
COMMIT;

如果凑巧, 两个事务都执行了第一条 UPDATE 语句, 更新了一行数据, 同时也锁定了该行数据, 接着每个书屋都尝试去执行第二条 UPDATE 语句, 却发现该行为已经被对方锁定了, 然后两个事务都等待对方释放锁, 同时又持有对方需要的锁, 则陷入死循环. 除非有外部因素介入才可能解除死锁.

为了解决这个问题, 数据库系统实现了各种死锁检测和死锁超时机制.
越复杂的系统, 比如 InnoDB 存储引擎, 越能检测到死锁的循环依赖, 并立即返回一个错误.
这种解决方式很有效, 否则死锁会导致出现非常慢的查询.

还有一种解决方式就是, 当查询的时间达到锁等待超时的设定后放弃锁请求.
这种方式通常来说不太好.

InnoDB 目前处理死锁的方式是, 将持有最少行级排他锁的事务回滚 (这是相对比较简单的死锁回滚算法).

锁的行为和顺序是和存储引擎相关的.
以同样的顺序执行语句, 有些存储引擎会产生死锁, 有些则不会.
死锁产生的有双重原因:有些是因为真正的数据冲突, 这种请求通常很难避免;但有些则完全是由于存储引擎的实现方式导致的.

死锁发生以后, 只有部分或者完全回滚其中一个事务, 才能打破死锁.
对于事务型的系统, 这是无法避免的, 所以应用程序在设计时必须考虑如何处理死锁, 大多数情况下需要重新执行因死锁回滚的事务即可,

1.4. 多版本并发控制 MVCC

https://blog.csdn.net/zx64881926/article/details/75150421

MySQL 引擎

发表于 2019-01-18 更新于 2020-09-11 分类于 database

使用以下命令可以查看MySQL支持的引擎

1	show engine;

InnoDB

具有提交, 回滚和崩溃恢复能力的事务安全 (ACID兼容) 存储引擎.
基于聚簇索引建立, 聚簇索引对主键查询有很高的性能.
不过它的二级索引(secondary index, 非主键索引)中必须包含主键列, 所以如果主键列很大的话, 其他的所有索引都会很大.
因此表上的索引较多的话, 主键应当尽可能的小.
支持事务和外键.行级锁.
适合在以下几种情况下使用:
1.更新和查询都相当的频繁, 多重并发
2.要求事务, 或者可靠性要求比较高
3.外键约束, MySQL支持外键的存储引擎只有InnoDB
一般来说, 如果需要事务支持, 并且有较高的并发读取频率, InnoDB是不错的选择.

Percona 的 XtraDB 存储引擎是基于 InnoDB 引擎的一个改进版本.
它主要改进集中在性能, 可测量性和操作灵活行方面.

MyISAM

提供高速存储和检索, 以及全文搜索能力.
不支持事务.表级锁.不能在表损坏后安全恢复数据.
适合只读数据, 或者表比较小, 可以忍受修复操作的情况.

适合在以下几种情况下使用:
1.做很多count的计算
2.查询非常频繁

Memory (Heap)

数据保存在内存中, 拥有极高的插入, 更新和查询效率.但是不稳定, 重启以后数据都会丢失.
不支持事务.支持表级锁, 因此并发写入的性能较低.
支持长度不变的数据类型, 不支持BLOB或TEXT长度可变的数据类型. VARCHAR是一种长度可变的类型, 但因为它在MySQL内部当做长度固定不变的CHAR类型, 所以可以使用.
每个表会生成一个.frm文件, 该文件只存储表的结构.
支持HASH索引和B-Tree索引, 擎默认使用HASH索引.
在内存中存放数据, 所以会造成内存的使用, 可以通过参数max_heap_table_size控制MEMORY表的大小.

Archive

拥有很好的压缩机制, 它使用zlib压缩库, 在记录被请求时会实时压缩.
支持最基本的插入和查询两种功能.在MySQL 5.5开始支持索引.
不支持事务.支持行级锁和专用的缓存区, 所以可以实现高并发的插入.
适合存储大量日志, 历史数据.

Blackhole

接受但不存储数据, 但是如果MySQL启用了二进制日志, SQL语句被写入日志 (并被复制到从服务器) .
用于做日志记录或同步归档的中继存储.但这种应用方式会碰到很多问题, 因此并不推荐.
支持事务, 而且支持mvcc的行级锁.

CSV

每个表会生成一个.CSV文件, 将CSV类型的文件当做表进行处理.
把数据以逗号分隔的格式存储在文本文件中, 这种文件是一种普通文本文件, 每个数据行占用一个文本行.
不支持索引, 即使用该种类型的表没有主键列, 也不允许表中的字段为null.

NDB Cluster

NDB 集群存储引擎
分布式, 自动分片, share-nothing, 容灾, 高可用.

Infobright

面向列的存储引擎

Sphinx

高效全文搜索引擎

MySQL 性能优化

发表于 2019-01-18 更新于 2020-09-11 分类于 database

1. 1. Table 结构优化

表优化
控制单表的长度, 使用 3 大范式拆分表.
字段优化
1. 选用合适的字段类型, 如整数类型的处理比字符串处理开销更小
2. 选用何时的字段长度, 固定长度的表会更快
3. 尽可能使用not null定义字段
索引优化
对于索引而言, 字段长度越小越好, 因为数据库的存储单位是页, 一页中能存下的数据越多越好; 字段离散度越大越好.
离散度可以通过 select count(distinct column_name) from table_name 查询. count 值越大, 离散度也越大.
1. 对于频繁查询的列建立索引, 频繁查询的列是指出现在 where, group by, order by, on 中的列
其他
1. 对于会有删除操作的大表, 要定期清理存储空间

2. 2. CRUD SQL 优化

使用连接 (join) 代替子查询
大的查询可以拆分成多个小的查询, 以便利用查询缓存

通过延迟关联优化分页查询
如:

select film_id, description from sakila.film order by title limit 50, 5;
-- 可优化成:
select film.film_id, film.description from sakila.film 
inner join (select film_id from sakila.film order by title limit 50, 5) as lim using(film_id);

3. 3. 命令分析

查看数据库状态

通过类似于 show [session|global] status like @status_name; 命令查询各种状态.
其中 session 是查询当前会话的状态, 默认值; global 是从 MySQL 启动到现在的状态.

status_name 有:

com_select: 查询次数
com_insert: 插入次数
com_update: 更新次数
com_delete: 删除次数
connections: 登录次数
uptime: 运行时长
slow_queries: 慢查询次数
handler_read%: 索引使用情况. handler_read_key: 这个值越高越好, 越高表示使用索引查询到的次数. handler_read_rnd_next: 这个值越高, 说明查询低效.

查看数据库参数

通过类似于 show variables like @variables_name; 命令查询各种状态.

查看 SQL 执行时间

show variables like 'PROFILING';
set profiling ='ON';
show profiles;
set profiling ='OFF';

set @query_id=1;
select
    state,
    sum(duration) as total_r,
    ROUND(
        100 * sum(duration) / (
            select sum(duration)
            from information_schema.profiling
            where query_id = @quer_id
        ), 2
    ) as pct_r,
    count(*) as calls,
    sum(duration) / count(*) as "r/call"
from information_schema.profiling
where query_id = @query_id
group by state
order by total_r desc;

表结构优化建议

以下 SQL 语句会给出每一列的优化建议.

1	SELECT column_name FROM table_name procedure analyse();

定期回收闲置的数据库空间

1
2
3

optimize table table_name;
alter table table_name engine = innodb;
alter table table_name force;

optimize table的支持表类型: innodb,myisam, archive, ndb; 会重组表数据和索引的物理页, 对于减少所占空间和在访问表时优化io有效果.

对于MyISAM表, 当表上的数据行被删除时, 所占据的磁盘空间并没有立即被回收, 使用命令后这些空间将被回收, 并且对磁盘上的数据行进行重排(注意: 是磁盘上, 而非数据库).

在InnoDB表中等价 ALTER TABLE … FORCE, ALTER TABLE ENGINE= INNODB,会重新整理在聚簇索引上的数据和索引;

只需在批量删除数据行之后, 或定期(每周一次或每月一次)进行一次数据表优化操作即可, 只对那些特定的表运行.

需要有足够的空间才能进行OPTIMIZE TABLE.如果没有磁盘空间, MySQL将不能进行优化, 表也无法使用.

查看慢查询

-- 查看慢查询是否开始, 存储文件位置
show variables like 'slow_query%';
-- 查询超过多少秒才记录
show variables like 'long_query_time';

-- 开启慢查询
set global slow_query_log='ON'; 
-- 设置慢查询时间, 默认为 10 秒
set long_query_time = 1;

-- 测试慢查询, 之后可以在慢查询日志文件中找到
select sleep(2)

也可以使用 pt-query-digest 工具分析慢查询日志.
pt-query-digest是用于分析mysql慢查询的一个工具, 它可以分析binlog, General log, slowlog, 也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析.
可以把分析结果输出到文件中, 分析过程是先对查询语句的条件进行参数化, 然后对参数化以后的查询进行分组统计, 统计出各查询的执行时间, 次数, 占比等, 可以借助分析结果找出问题进行优化.

EXPLAIN 分析查询 SQL

table: 表名
type: 连接的类型
- all: 全表扫描
- index: 索引的扫描
  如果在 Extra 列看到 Using index, 表示正在使用覆盖索引, 只宿醉索引中的数据, 并不按索引扫描整个表.
- range: 索引的范围查找
- ref: 索引查找
  ref_or_null 是 ref 的变体, 表示需要在初次筛选结果中再次查找出 NULL 条目.
- eq_ref: 主键或唯一索引的等值查找, 只会返回一条记录
- const, system: 主键, 索引
possible_keys: 可能用到的索引
key: 实际使用的索引
key_len: 索引的长度, 越短越好
ref: 索引的哪一列被使用了, 常数较好
rows: mysql认为必须检查的用来返回请求数据的行数
extra:
- Using index: 使用覆盖索引
- Using where: MySQL 服务器将在存储引擎检索后再进行过滤
  不是所有 where 条件都会显示该值.
  有时候该值表示查询将受益于不同的索引
- Using filesort: 需要额外排序
  无法按索引次序从表里读出行, 需要在内存或磁盘上进行排序
- Using temporary: 需要使用临时表
  会使用内存或磁盘上幅度临时表, 通常用于排序
- Range checked for each record (index map: N): 没有好的索引