MySQL 技术内幕阅读笔记

最新推荐文章于 2024-02-19 16:21:13 发布

xiegeshiwo

最新推荐文章于 2024-02-19 16:21:13 发布

阅读量471

点赞数

本文链接：https://blog.csdn.net/xiegeshiwo/article/details/82702104

版权

第一章 mysql 结构体系和存储引擎

查看 mysql 实例进程

ps -ef | grep mysqld

查看 my.cnf 读取路径,如果每个路径都有 my.cnf 以最后一个为准

root@ubuntu:/home/sunrise/soft/mysql/bin# ./mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf /home/sunrise/soft/mysql/my.cnf ~/.my.cnf

datadir 表示数据库文件所在的路径

datadir 默认路径为 /usr/local/mysql/data, 但是一般情况下这个目录是一个连接指向 /opt/mysql_data

show variables like 'datadir';

存储引擎基于表不基于数据库.

innodb 特性

5.5.8 开始 innodb 为默认存储引擎,支持外键,使用行锁,默认读取不加锁.采用 mvcc (多版本并发控制)获取高并发性.实现了标准的4种隔离级别,默认隔离级别为 repeatable(同一个事务可重读).采用聚集方式组织数据,因此每张表的存储都是按主键的顺序进行存放的.如果没有主键,默认会生产一个 6Byte 主键.

myisam 特性

5.5.8 之前 myisam 为默认存储引擎,不支持事务,使用表锁,支持全文索引.表由MYD(data)和MYI(index)组成.

NDB 特性

集群引擎,数据都放在内存中(非索引数据可以放在磁盘上).join 操作在 mysql数据库层面完成.所以 join 操作需要巨大的天网络开销

Memory 特性

只支持表suo,不支持text 和 blob 类型.存储变长字段的时候使用定长字段(varchat 用 char 存储). mysql 使用 memory 作为临时表的存放处,如果临时表的数据大于 memory 可以承受的范围或者零食表含有 text 和 blob类型,会生成一张 myisam 表写入磁盘,效率会大大降低.

Archive 特性

只支持 insert 和 select,支持行锁(但是感觉行锁没有意义),使用压缩算法存储行,压缩比例为 1:10 左右.适合归档类数据,日志等等.

Federated 特性

不存放数据,指向远程 mysql 的表,类似 sql server 的连接服务器.

Maria 特性

有取代 myisam 之意,支持缓存数据和索引文件,支持行锁,事务和 mvcc.以及更好的 blob 字符类型的处理性能.

其他还有 merge csv sphinx infobright.

show engines;(当前支持的引擎)

第二章 innodb 存储引擎

页单位介绍

Page是Innodb存储的最基本结构，也是Innodb磁盘管理的最小单位，与数据库相关的所有内容都存储在Page结构里。Page分为几种类型：数据页（B-Tree Node），Undo页（Undo Log Page），系统页（System Page），事务数据页（Transaction System Page）等；每个数据页的大小为16kb，每个Page使用一个32位（一位表示的就是0或1）的int值来表示，正好对应Innodb最大64TB的存储容量(16kb * 2^32=64tib)

第五章索引

innodb 全文索引每个表只能有一个,由多列组合而成的全文索引列必须使用相同的字符集和排序规则,另外补支持没有单词界定符的语言,如:中文,日语,韩语等,但是有变通的办法，就是将整句的中文分词，并按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。中文分词结果如下:

-- 全文索引
use test_db;
drop table if EXISTS fts_a;
create table fts_a
(
FTS_DOC_ID bigint UNSIGNED auto_increment not null,
customerID int,
body Text,
PRIMARY key(FTS_DOC_ID)
,FULLTEXT index (body)
);

insert into fts_a select null, 1, 'pease porridge in the pot';
insert into fts_a select null, 2, 'pease porridge hot, pease porridge cold';
insert into fts_a select null, 3, 'nine days old';
insert into fts_a select null, 4, 'some like it hot, some like it cold';
insert into fts_a select null, 5, 'some like it in the pot';
insert into fts_a select null, 6, 'nine days old';
insert into fts_a select null, 7, 'i like code days';

create fulltext index idx_fts on fts_a(body);
-- ''
show variables like 'innodb_ft_aux_table';
set global innodb_ft_aux_table='test_db/fts_a';
select * from information_schema.INNODB_FT_INDEX_TABLE;
DELETE from fts_a where fts_doc_id=7;
select * from information_schema.INNODB_FT_DELETED;
-- OFF 只删除倒排序中该文档的分词信息
show variables like 'innodb_optimize_fulltext_only';
set global innodb_optimize_fulltext_only=ON;
select * from information_schema.INNODB_FT_DELETED;
optimize table fts_a;
select * from information_schema.INNODB_FT_BEING_DELETED;
-- 已经删除的 id 不允许再次插入(下面语句会抛出异常)
insert into fts_a select 7, 7, 'I like this days';
-- stopword 不需要进行分词统计的单词(默认的不统计的分词)
select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;
-- 设置不统计的分词表(可以设置)
show variables like 'innodb_ft_server_stopword_table';

全文索引的检索,相关性的计算依据,是否在文档中出现,出现的次数,在索引列的数量,多少个文档包含该word.

查询关键字长度是否在区间[innodb_ft_min_token_size(3), innodb_ft_max_token_size(84)]

-- 1 SIMPLE fts_a ALL 7 14.29 Using where
EXPLAIN SELECT * from fts_a where body like '%days%';
SELECT * from fts_a where body like '%days%';
-- 1 SIMPLE fts_a fulltext body body 0 const 1 100 Using where; Ft_hints: sorted
explain select * from fts_a where match(body) AGAINST('porridge' in NATURAL language mode);
-- 结果会对相关性排序
select * from fts_a where match(body) AGAINST('porridge' in NATURAL language mode);
-- 统计(统计信息部排序)
select count(if(match(body) against('porridge'), 1, null)) from fts_a;
-- 相关性统计(计算相关性)
select *, match(body) AGAINST('porridge' in NATURAL language mode) revenue from fts_a;
-- 有并且没有
select * from fts_a where MATCH(body) against('+pease -hot' in boolean mode);
-- 既有又有
select * from fts_a where match(body) against('+pease +hot' in boolean mode);
-- 有或者有
select * from fts_a where match(body) against('pease hot' in boolean mode);
-- pease pot 之间小于 30 个字符
select * from fts_a where match(body) against('"pease pot" @30' in boolean mode);
select * from fts_a where match(body) against('"pease pot" @5' in boolean mode);
-- 包含 like 或者 pot,如果有port 提升相关性
select * from fts_a where match(body) against('like >pot' in boolean mode);
select *, match(body) against('like >pot' in boolean mode) from fts_a;
select *, match(body) against('like pot' in boolean mode) from fts_a;
-- 包含 like 或者 pot 或者 some, 如果有port 提升相关性,如果有some减少相关性(负相关性 != 0 也会出现在结果中)
select * from fts_a where match(body) AGAINST('like >hot <some' in boolean mode);
select *, match(body) against('like hot some' in boolean mode) from fts_a;
select *, match(body) against('like >hot <some' in boolean mode) from fts_a;
-- 以 po 开头
select * from fts_a where MATCH(body) against('po*' in boolean mode);
select *, MATCH(body) against('po*' in boolean mode) from fts_a;
-- 短语
select * from fts_a where match(body) against('"like hot"' in boolean mode);
-- 非短语(没有 " ")
SELECT * from fts_a where match(body) AGAINST('"like hot" like hot' in boolean mode);

扩展查询
-- query expansion
drop table if exists articles;
create table articles
(
id int unsigned not null auto_increment,
title VARCHAR(50),
body text,
primary key (id),
fulltext index (title, body)
)engine = innodb;

insert into articles (title, body)
   values('mysql tutorial', 'dbms stands for database...')
               ,('how to use mysql well', 'after you when through a...')
               ,('optimizing mysql', 'in this tutorial we will show...')
               ,('1001 mysql tricks', '1. never run mysql as root. 2...')
               ,('mysql vs. yoursql', 'in the following database comparison...')
               ,('mysql security', 'when configured properly, mysql ...')
               ,('tuning db2', 'for ibm database ...')
               ,('ibm history', 'db2 history for ibm');

select * from articles where MATCH(title, body) AGAINST('database');
select * from articles where match(title, body) against('database' with query expansion);
select *, match(title, body) against('database' with query expansion) from articles;

-- 最小分词长度(中文建议 4)
show variables like 'ft_min_word_len';
-- [SQL]set ft_min_word_len = 2;
-- [Err] 1238 - Variable 'ft_min_word_len' is a read only variable
set ft_min_word_len = 2;

第六章锁

InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

查看锁和事务状态

-- lock transaction
show engine innodb status;
-- 查看当前事务状况 trx_weight 事务的权重,一个事务修改和锁住的行.死锁的时候回根据该值确认回滚事务.
select * from information_schema.INNODB_TRX;
-- 锁的相关信息 id 模式类型表锁住的索引等等
select * from information_schema.INNODB_LOCKS;
select * from information_schema.INNODB_LOCK_WAITS

S lock & X lock SS兼容,其他组合均不兼容.

非锁定一致性读取:

read commit & repeatable read 下默认走的非锁定读.read commit 始终读取最新版本,后者每次读取第一次读取的版本.(mvcc).

下图tx1 = repeatable read, tx2=read commit.

	tx1	tx1_result	tx2	tx2_result	tx3	tx3_result	queue
tx_isolation	begin;		begin;		begin;		1
	select * from parent	1	select * from parent	1	update parent set id=2 where id=1;		2
	select * from parent	1	select * from parent	1	commit;		3
	select * from parent	1	select * from parent	2			4

锁定一致性读取:

select ... for update; add X lock;

select ... lock in share mode; add S lock;

但是对于非锁定读取,及时执行了 for update 依旧按照上面的描述进行读取.并且 for update & lock in share mode 必须在事务中才有效.当事务提交锁就释放了.因此在使用以上两个加锁语句的时候务必加上: begin || start trasaction || set autocommit = 0,显示的开启事务.

自增长与锁

每次插入数据的时候,会执行以下语句来得到计数器的值:select max(auto_inc_col) from t for update;auto-inc locking 是一种特殊的表锁.innodb自增长必须是索引,或者索引的第一列.否则会抛出异常.

-- 控制自增插入的性能
show variables like 'innodb_autoinc_lock_mode'

外键与锁

如果外键列没有索引,那么innodb 会自动给外键加一个索引(fk).对子表进行 insert & update 操作的时候,需要先查找父表,而且对父表的select 是用的 lock in share mode.对父表加一个 S lock,如果父表的数据被加了 X lock,那么整个操作会阻塞.

锁的算法

record lock, gap lock, next-key lock,行锁(只包含记录本身), 间隙锁(不包含记录本身), 范围锁(行+间隙).innodb 对于行锁都是采用最后一个.read commit 是 record lock, repeatable 是 next-key lock.

锁的展示:

drop table if EXISTS z;
create table z
(
a int,
b int,
unique index (a),
index (b)
);
insert into z select 1, 1;
insert into z select 30, 10;
insert into z select 50, 30;
insert into z select 70, 60;
insert into z select 100, 80;

tx1----------------------------------------

begin;

select * from z where b=30 for update;

锁定范围为 a=50(X lock) || (b<=10 & b<60)(S lock)

b<=10 为 next-key lock, b<60为 gap lock

以下语句关闭 gap lock

set tc_isolation=1;(read commit)

set innodb_locks_unsafe_for_binlog = 1;

tx2----------------------------------------

select * from z where a=50;

insert into z select 50, 9;

insert into z select 10, 10;

insert into z select 10, 30;

insert into z select 10, 59;

以上任一语句均被阻塞.

tx3----------------------------------------

insert into z select 10, 9;

insert into z select 10, 60;

以上语句均不阻塞.

锁的问题

(read uncommit)脏读, 看似无用其实可以用在 slave 上,在 slave 的查询上不需要很精准的返回,该模式还是可以节约一定的开销.

修改锁等待时间

set innodb_lock_wait_timeout=50;

设置等待超时之后是否回滚数据,默认是不回滚的,该参数不可在运行时修改,默认不回滚,但是死锁异常会回滚另一个事务的所有操作

set innodb_rollback_on_timeout=ON;

回滚方式

选择权重较小的事务进行回滚,目前数据库普遍采用 wait-for graph (等待图)的方式,进行死锁主动检测.

辅助参考资料 https://baijiahao.baidu.com/s?id=1610581108528334819&wfr=spider&for=pc

通常来说，死锁都是应用设计的问题，通过调整业务流程、数据库对象设计、事务大小，以及访问数据库的SQL语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法：

（1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。

（2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

（3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

（4）在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。

（5）当隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁。这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。

（1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、InnoDB_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁，否则，InnoDB将无法自动检测并处理这种死锁。

（2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：

例如，如果需要写表t1并从表t读，可以按如下做：

SET AUTOCOMMIT=0;LOCK TABLES t1 WRITE, t2 READ, ...;[do something with tables t1 and t2 here];COMMIT;UNLOCK TABLES;