MYSQL架构示意图
MYSQL8.0开始移除了查询缓存这个模块；
查询缓存：适合读多写少的任务；
建议：将 have_query_cache 设置为No，在需要使用查询缓存的语句上显式声明；
select SQL_CACHE * from test;
WAL(Write Ahead Logging): 先写日志，再写磁盘；
MYSQL数据更新流程：先记在redo log中，并更新内存；InnoDB会在适当的时候将这个操作记录更新到磁盘中；采用尽管我们修改了redo log也是位于磁盘的，但是相比记录更新语句以及直接修改数据，记录所消耗的时间明显更少；
crash safe: 使用redo log，使得数据库挂掉后依旧可以很好地恢复；
Update 语句流程
我们可以将数据库恢复到binlog记录到的任意时刻；
两阶段提交：即写完redo log 和 binlog之后才可以提交事务;
可以在 information_schema 库中innodb_trx表中查询长事务；(start transaction with consistent snapshot;)
索引
用于提高查询速度
数据模型结构
| type | 适用范围 |
|---|---|
| 哈希链表 | 适合精确查找，添加删除数据，不适合范围查找； |
| 有序数组 | 适合范围查找和精确查找，不适合添加删除数据 |
| 多叉平衡搜索树 | 均适合 |
- 平衡二叉树：是一颗空树或其左右两个子树高度差的小于等于1
- 二叉搜索树：左节点小于根节点，根节点小于右节点；
索引类型
-
主键索引（聚簇索引）：叶子节点存的是整行数据；
-
非主键索引（非聚簇索引）:叶子节点存储的为主键值，因此存在回表操作；
因此我们应多使用主键索引进行查询； -
主键索引：全覆盖索引
-
覆盖索引：叶子节点保存部分字段的值，这样查询这部分字段时就不用回表；
-
前缀索引: 如果通过调整顺序可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的；
-
索引下推优化: 可以在索引遍历的过程中，对索引中包含的字段做判断，直接过滤掉不满足条件的记录，减少回表次数；
全局锁和表锁
全局锁：对整个数据库实例加锁，通常用于不支持事务的引擎如MyISAM；
# 加全局锁，更新，建表，修改表结构，更新类事务语句阻塞
flush tables with read lock;
# 也可以上全局锁，但由于该变量也用于判断主从数据库，因此不建议使用
set global readonly=true;
表锁：对数据表上锁；
lock tables t1 read, t2 write;
unlock tables;
元数据锁(MDL)：用于对表做结构变更操作，系统默认会在语句执行开始时申请该锁；
如何为表加字段？
查找该表是否含有长事务，如果有的话等待执行完毕或直接kill到这个事务，因为修改表结构需要获取MDL锁；
若事务需要锁多个行，需将最可能造成锁冲突，最可能影响并发的锁尽量往后放；
更新数据都是先读后写，这个读表示当前读；
在一个事务中，单纯读的依旧是事务开始时刻的版本，但我们可以通过显示声明的方式读到最新的数据；
select * from t where id = 3 for update;
select * from t where id =3 lock in share mode;
索引
更新数据：内存 -> 缓存(change buffer) ；等下次查询访问这个数据页时，将数据读入内存，并执行change buffer中的操作；
merge: 将change buffer中的操作应用到原数据页，访问该数据页以及后台线程会定时触发该操作
普通索引
唯一索引
插入数据：判断该操作是否违背唯一性约束，该操作需要将数据页读取到内存中，因此直接更新内存会更快；因此唯一索引不会使用change buffer，只有普通索引可以；使用change buffer我们就可以减少将数据页从磁盘读入内存的次数，可提升更新性能
change buffer的大小可通过参数 'innodb_change_buffer_max_size’设置，若设置为n，表示change buffer的大小最多是buffer pool的50%；
唯一索引的好处看起来似乎只是在查询的时候可以查到就立马返回结果，不用遍历记录，查找其他值相同的记录，但由于数据库查询数据时是以页为单位，因此这点差别对性能来说微乎其微；
change buffer使用场景：在写多读少的时候，由于记录更新写change buffer后被访问的概率小，因此使用效果比较好，常见的业务类型为账单类，日志类系统；
实际测试中若发现explain结果预估的rows值和实际情况差距比较大，可以采用
analyze table t;来处理；
procedure
use test;
delimiter $$
delete from tt where 1$$
drop procedure if exists idata$$
create procedure idata()
begin
declare i int default 0;
set i=1;
while (i<10000)do
insert into tt values(i,i,i);
set i=i+1;
end while;
end $$
delimiter ;
call idata();
delimiter $$
drop procedure if exists test01$$
create procedure test01 (IN from_id int(11),IN to_id int(11), IN val int(11))
begin
declare aa int(11) default val;
declare bb int(11) default val;
if (from_id <> -1 and to_id<>-1)
THEN
select a into aa from tt where `id` = from_id;
select b into bb from tt where `id` = from_id;
end if;
replace into tt (id,a,b) values(to_id,aa*val,bb*val);
end $$
delimiter ;
call test01(-1,2,6);
select * from tt;
如何为字符串加索引
使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。
若覆盖索引已包含所需查找的值，可以直接返回；若使用前缀索引，就需要用主键去再查询一次；
前缀索引的优化
(1) 倒序存储：若查询的字段的前缀的好一部分都是基本一样的，此时这部分的前缀索引性价比就很低；
(2) hash字段：在表中再创建一个整数字段，保存身份证的校验码，同时再该字段创建索引；
‘SQL’ 突然变慢了
脏页：内存数据页和磁盘数据页内容不一致；
干净页：内存数据已经写入到磁盘了；
'SQL’突然变慢了，可能是因为此时MYSQL正在刷脏页；
何时刷脏页：
(1) InnoDB的redo log写满了，系统需要停止所有更新操作，将checkpoint往前推进，将路径上的所有操作flush到磁盘上；
(2) 内存不够了，需要淘汰一些数据页空出一些内存，若淘汰的是脏页，则需要先将脏页写到磁盘；
因此以下2种情况都会导致查询时间明显变长
(1) 一个查询导致要淘汰的脏页的数量太多，会导致查询的响应时间明显变长；
(2) 日志写满，更新全部堵住；
InnoDB刷脏页控制策略
我们尽量不要写满redo log，但又需要控制系统在空闲时间刷脏页，不要影响服务用户请求；
为什么表数据删了一半，但是表文件大小不变
(1) delete 语句只是把记录的位置或数据页标记为"可复用"，因此磁盘大小不会改变；
(2) 插入数据的时候，若数据是按照索引递增顺序插入的，那么索引是紧凑的，若数据是随机插入的，就可能造成数据页分裂；
(3) 更新索引上的值时，可以理解为删除一个旧的
值(造成数据空洞)，再插入一个新值；
“重建表”可以解决数据空洞的问题，把内存中可被复用但未被使用的表真正删除；
- 将旧表中的数据一行一行地插入到新表中；
- alter table A engine=InnoDB
Online DDL: 允许在重建表的过程中，对表进行增删改操作；在实现上，进行了特殊处理，使得获取的MDL不会阻塞增删改操作；
inplace操作：
- alter table t add FULLTEXT(field_name); 会阻塞增删改操作；
analyze table: 对表的索引欣欣做重新统计；
alter table t engine=InnoDB; 重建表
optimize table t; recreate + analyze
如何表的行数
(1) select count(*) from tt;由于InnoDB采用MVCC，因此我们需要遍历每一行来判断当前记录是否在当前事务是可见的，以此来统计行数；
(2) show table status; 此处显示的行数是MYSQL 抽样调查显示，大约有40%，50%的误差；
count(field)：表示遍历每一行，当该field字段不为NULL时返回true
server层要什么我们就给什么；
- count(1): InnoDB遍历整张表，不取值，server层对于返回的每一行，都放一个数字1进去，判断该1不为NULL，因此必定+1
- count(field):
- 若该字段 NOT NULL, 则直接 +1
- 若该字段可为NULL，需要取出值判断不为NULL才 +1
- count(*): 专门做了优化，按行累加；
对于数据的更新操作，我们通常先更新数据，然后在做其他操作，减少该事务占有该锁的时间；
排序
全字段排序：MYSQL专门为排序开辟了一片内存(sort_buffer)，用来排序（快速排序），若待排序额数据量过大，内存放不下，则需要用到外部文件来进行归并排序；排序时会获取所有需要返回的字段，这样排序后可以直接从内存里面返回查询结果；
rowid排序：在排序过程中只读了一遍原表，剩下操作都在sort_buffer和临时文件中执行，但若查询返回的字段很多的话，那么sort_buffer中需要放的字段数太多，导致可放的行数会减少，需要分成多个临时文件，导致排序性能下降；排序时值获取被用于排序的字段，排序结束后会回表将结果返回给用户；
show variables like ‘%max_length_for_sort_data%’;:MySQL中专门用于控制排序的行数据的长度的参数，即若单行的长度超过这个值，Mysql会使用其他排序算法；
set max_length_for_sort_data = 16;
若索引本身有序，那 order by就无需进行排序了；
常见坑
- 在MYSQL中，若字符串和数字作比较时，会将字符串转换为数字；对索引字段做函数操作，优化器会放弃走树搜索功能；
隐式字符编码转换： 当做联合查询时，使用的字段进行比较时，默认字符编码向更加长的字符集转换；
幻读
一个事务在前后2次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行；
在可重复读隔离级别下，普通的查询是快照读；
幻读产生原因：因为基石将所有的记录都加上锁，还是阻止不了新插入的记录；
间隙锁：往记录间隙中插入数据时会被锁住；间隙锁之间不会冲突，即多个事务均可持有间隙锁；
间隙锁和行锁合称next-key lock
原则1：加锁的基本单位是next-key lock
原则2：查找过程中访问到对象才会加锁
优化1：索引上的等值查询，给唯一索引加锁时，next-key lock会退化为行锁
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock退化为间隙锁；
一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止；
在删除数据的时候尽量加limit；
MYSQL排查思路
pager cat - | grep -v Sleep | sort -rn -k 12 | head -n 20
show full processlist;
select * from information_schema.processlist where 1=1 order by TIME desc limit 10;
show slave status\G;
mysqlbinlog -v --base64-output=decode-rows --start-position=exec_master_log_pos relay_master_log_file
perf top `pidof mysqld`
show engine innodb status\G;
1151
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
