mysql学习笔记

jackjack_abc

已于 2022-03-26 11:46:39 修改

阅读量576

点赞数

分类专栏： mysql 文章标签： mysql 学习数据库

于 2022-02-26 17:58:02 首次发布

本文链接：https://blog.csdn.net/jjjackjack/article/details/123153321

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql架构图

innoDB架构图

介绍：

Buffer Pool: a linked list of pages以页为元素的链表。采用基于 LRU（least recently used）的算法来管理内存。
Change Buffer：要修改的页，不在内存里时，记到Change Buffer中。只在操作「二级索引」（secondary index）时才使用
   merge：Change Buffer -> Buffer Pool
   内存数据刷到磁盘 purge：Buffer Pool -> Disk
Adaptive Hash Index：自适应哈希索引:MySQL 会自动评估使用自适应索引是否值得，如果观察到建立哈希索引可以提升速度，则建立
Log Buffer 里的 redo log，会被刷到磁盘里
Operating System Cache：高速缓存
Tablespaces：
   The System Tablespace
   File-Per-Table Tablespaces
   General Tablespace
   Undo Tablespaces
   Temporary Tablespaces
Doublewrite Buffer：如果说 Change Buffer 是提升性能，那么 Doublewrite Buffer 就是保证数据页的可靠性。
   MySQL 在刷数据到磁盘之前，要先把数据写到另外一个地方，也就是 Doublewrite Buffer，写完后，再开始写磁盘。Doublewrite Buffer 可以理解为是一个备份（recovery），万一真的发生 crash，就可以利用 Doublewrite Buffer 来修复磁盘里的数据

1，索引

InnoDB 的 B+Tree 索引分为主索引和辅助索引。
聚簇索引(聚集索引): 主索引的叶子节点 data 域记录着完整的数据记录
辅助索引(普通索引, 非聚集索引)：叶子节点的 data 域记录着主键的值。一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。
回表: 使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找
索引覆盖: 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表, 查的字段上建立联合索引。
自适应哈希索引: 在innoDB 存储引擎中，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，查找更快速
全文索引：MyISAM 存储引擎支持全文索引，查找条件使用 MATCH AGAINST，而不是普通的 WHERE。
   InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
空间数据索引：MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储
索引的选择性：不重复的索引值和记录总数的比值，最大值为 1，此时每个记录都有唯一的索引与其对应。
   选择性越高，每个记录的区分度越高，查询效率也越高。
前缀索引：对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，前缀长度的选取需要根据索引选择性来确定。
复合索引设计原则
    将范围查询的列放在复合索引的最后面，例如idx_status_create_time。
    列过滤的频繁越高，选择性越好，应该作为复合索引的前导列，适用于等值查找，例如idx_user_id_status。
跳跃索引
    如果表users有复合索引idx_status_create_time，当单独用create_time去查询，MySQL优化器是不走索引，在MySQL 8.0实现了跳跃索引
    适合复合索引前导列唯一值少，后导列唯一值多的情况，如果前导列唯一值变多了，则MySQL CBO不会选择索引跳跃扫描，取决于索引列的数据分表情况。
索引下推ICP（Index Condition Pushdown）：针对age_name的联合索引，首先通过条件找到所有age>10的数据，根据联合索引中已经存在的name数据进行过滤，找到符合条件的数据，索引下推一定是在联合索引的情况下。
在开启ICP特性后，对于条件where name = 'Lyn' and nickname like '%SK%' 可以利用复合索引 (name,nickname) 减少不必要的数据扫描，提升SQL性能
生成列
   VIRTUAL，即虚拟类型，字段值不实际存储，当读取行时再计算，虚拟列类型不占存储
   STORED，即存储类型，字段值会实际存储起来，当插入或更新时，字段值会计算出来并存储起来
   CREATE TABLE person (first_name VARCHAR(10) NOT NULL COMMENT '名',
       last_name VARCHAR(10) NOT NULL COMMENT '姓',
       full_name VARCHAR(21) GENERATED ALWAYS AS (CONCAT(first_name,' ',last_name)) STORED NOT NULL COMMENT '全名'
   );
   ALTER TABLE person ADD full_name_gc VARCHAR(21)
       GENERATED ALWAYS AS (CONCAT(first_name,'_',last_name)) VIRTUAL NOT NULL COMMENT '全名(虚拟列)';
生成列使用场景：
   模糊匹配优化：
       利用内置reverse函数将like '%风云'反转为like '云风%'，基于此函数添加虚拟生成列。
       在虚拟生成列上创建索引。
       将SQL改写成通过生成列like reverse('%风云')去过滤，走生成列上的索引。

2，explain分析

select_type：SIMPLE 简单查询，UNION 联合查询，SUBQUERY 子查询
possible_keys：可以使用哪些索引
key：实际决定使用哪个索引
rows：扫描的行数，基于page进行平均计算得出的结果，和实际行数不一致
filtered：针对符合查询条件的记录数的百分比估算，用rows和filtered相乘可以计算出关联表的行数。
type：
   system：触发条件：表只有一行，这是一个 const type 的特殊情况
   const：触发条件：在使用主键或者唯一索引进行查询的时候只有一行匹配
   eq_ref：触发条件：在进行联接查询的，使用主键或者唯一索引并且只匹配到一行记录的时候
   ref：触发条件：使用非唯一索引
   range：触发条件：只有在使用主键、单个字段的辅助索引、多个字段的辅助索引的最后一个字段进行范围查询才是 range
   index：触发条件：只扫描索引树，跟全表扫描类似，只是扫表是按照索引顺序进行。
   all：触发条件：全表扫描，不走索引

从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

partitions : 分区
Extra：
   using where：查询使用索引的情况下，需要回表去查询所需的数据。
   using index condition：查询使用了索引，但是需要回表查询数据。使用索引下推，索引下推简单来说就是加上了条件筛选，减少了回表的操作。
   using index：查询使用覆盖索引的时候会出现。
   using index & using where：查询使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询数据。

3，锁

锁分类：共享锁（S Lock）, 排他锁（X Lock）
MVCC: 多版本并发控制,InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别
       未提交读隔离级别总是读取最新的数据行，无需使用 MVCC
       可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现
   版本号:
       系统版本号：是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增
       事务版本号：事务开始时的系统版本号
   隐藏的列:
       创建版本号：指示创建一个数据行的快照时的系统版本号
       删除版本号：如果该快照的删除版本号大于当前事务版本号表示该快照有效，否则表示该快照已经被删除了
InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的事务ID（DB_TRX_ID），一个保存了行的回滚指针（DB_ROLL_PT）
   Undo 日志:MVCC 使用到的快照存储在 Undo 日志中，该日志通过回滚指针把一个数据行（Record）的所有快照连接起来
   快照读: MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。
   当前读: MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。
在可重复读（REPEATABLE READ，简称RR）隔离级别下，数据快照版本是在第一个读请求发起时创建的。在读已提交（READ COMMITTED，简称RC）隔离级别下，则是在每次读请求时都会重新创建一份快照。
锁算法：
   Record Lock: 锁定一个记录上的索引，而不是记录本身。
   Gap Lock: 锁定索引之间的间隙，但是不包含索引本身
       SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE; 其它事务就不能在 t.c 中插入 15
   Next-Key Lock: 不仅锁定一个记录上的索引，也锁定索引之间的间隙
   在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，
       而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，
       INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的

记录锁:封锁记录，记录锁也叫行锁，封锁该行的索引记录
   记录锁就是为某行记录加锁，列必须为唯一索引列或主键列，否则加的锁就会变成临键锁，查询语句必须为精准匹配 = ，不能为 >、<、like等，否则也会退化成临键锁。
间隙锁:封锁索引记录中的间隔
   间隙锁基于非唯一索引，它锁定一段范围内的索引记录。比如查询字段区间为1-5，即1-5内的记录行都会被锁住，2、3、4 的数据行的会被阻塞，但是 1 和 5 两条记录行并不会被锁住。
临键锁：
   临键锁可以理解为一种特殊的间隙锁，上面说过了通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。
唯一索引等值查询：
   当查询的记录是存在的，next-key lock 会退化成「记录锁」
   当查询的记录是不存在的，next-key lock 会退化成「间隙锁」。
非唯一索引等值查询：
   当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
   当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。
非唯一索引和主键索引的范围查询的加锁规则不同之处在于：
   唯一索引在满足一些条件的时候，next-key lock 退化为间隙锁和记录锁。
   非唯一索引范围查询，next-key lock 不会退化为间隙锁和记录锁。

eg:   id       b
        0        0    
        4        4    
        8        8
        16        16
        32        32
id查询：
    id=16 记录锁 锁16这一行 
    id=10 间隙锁 (8,16)
    id>=8 and id<9 记录锁 id=8 和间隙锁(8, 16)
b查询：
    b=8 临键锁(4,8]和间隙锁(8,16)
    b=5 间隙锁(8,16)
    b>=8 and b<9 临键锁(4,8]和临键锁(8,16]

4，主从复制

binlog 线程：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程：负责从主服务器上读取- 二进制日志，并写入从服务器的中继日志（Relay log）。
SQL 线程：负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。

5，读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。

6，三大范式

高级别范式的依赖于低级别的范式
第一范式：属性不可分。无重复的列。
   确保每列的原子性(强调的是列的原子性，即列不能够再分成其他几列).
   如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.
第二范式: 属性完全依赖于主键。确保表中的每列都和主键相关。加pk
第三范式: 属性不依赖于其它非主属性.
   确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键，不能存在传递依赖)
   eg: 顾客姓名和顾客编号相关,顾客编号和订单编号又相关,经过传递依赖，顾客姓名也和订单编号相关。
       为了满足第三范式，应去掉顾客姓名列，放入客户表中。

7，数据归档

查询 Innodb中的碎片

select table_schema, table_name, ENGINE, round(DATA_LENGTH/1024/1024+ INDEX_LENGTH/1024/1024) total_mb, TABLE_ROWS,
        round(DATA_LENGTH/1024/1024) data_mb, round(INDEX_LENGTH/1024/1024) index_mb, round(DATA_FREE/1024/1024) free_mb, 
        round(DATA_FREE/DATA_LENGTH*100,2) free_ratio
from information_schema.TABLES where TABLE_SCHEMA= 'ceshi' and TABLE_NAME= 't_table';

#1. 创建中间表
CREATE TABLE `ota_order_bak` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_id` varchar(255) DEFAULT NULL COMMENT '订单id',
  `ota_id` varchar(255) DEFAULT NULL COMMENT 'ota',
  `check_in_date` varchar(255) DEFAULT NULL COMMENT '入住日期',
  `check_out_date` varchar(255) DEFAULT NULL COMMENT '离店日期',
  `hotel_id` varchar(255) DEFAULT NULL COMMENT '酒店ID',
  `guest_name` varchar(255) DEFAULT NULL COMMENT '顾客',
  `purcharse_time` timestamp NULL DEFAULT NULL COMMENT '购买时间',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `create_user` varchar(255) DEFAULT NULL,
  `update_user` varchar(255) DEFAULT NULL,
  `status` int(4) DEFAULT '1' COMMENT '状态 ：1 正常 ， 0 删除',
  `hotel_name` varchar(255) DEFAULT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  `remark` longtext,
  PRIMARY KEY (`id`),
  KEY `IDX_order_id` (`order_id`) USING BTREE,
  KEY `hotel_name` (`hotel_name`) USING BTREE,
  KEY `ota_id` (`ota_id`) USING BTREE,
  KEY `IDX_purcharse_time` (`purcharse_time`) USING BTREE,
  KEY `IDX_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (to_days(create_time)) ( 
PARTITION p201808 VALUES LESS THAN (to_days('2018-09-01')), 
PARTITION p201809 VALUES LESS THAN (to_days('2018-10-01')), 
PARTITION p201810 VALUES LESS THAN (to_days('2018-11-01')), 
PARTITION p201811 VALUES LESS THAN (to_days('2018-12-01')), 
PARTITION p201812 VALUES LESS THAN (to_days('2019-01-01')), 
PARTITION p201901 VALUES LESS THAN (to_days('2019-02-01')), 
PARTITION p201902 VALUES LESS THAN (to_days('2019-03-01')), 
PARTITION p201903 VALUES LESS THAN (to_days('2019-04-01')), 
PARTITION p201904 VALUES LESS THAN (to_days('2019-05-01')), 
PARTITION p201905 VALUES LESS THAN (to_days('2019-06-01')), 
PARTITION p201906 VALUES LESS THAN (to_days('2019-07-01')), 
PARTITION p201907 VALUES LESS THAN (to_days('2019-08-01')), 
PARTITION p201908 VALUES LESS THAN (to_days('2019-09-01')), 
PARTITION p201909 VALUES LESS THAN (to_days('2019-10-01')), 
PARTITION p201910 VALUES LESS THAN (to_days('2019-11-01')), 
PARTITION p201911 VALUES LESS THAN (to_days('2019-12-01')), 
PARTITION p201912 VALUES LESS THAN (to_days('2020-01-01')));

#2. 插入原表中有效的数据，如果数据量在100W左右可以在业务低峰期直接插入，如果比较大，建议采用dataX来做，可以控制频率和大小，之前我这边用Go封装了dataX可以实现自动生成json文件，自定义大小去执行。
insert into ota_order_2020 select * from ota_order where create_time between '2020-08-01 00:00:00' and '2020-08-31 23:59:59';

#3. 表重命名
alter table ota_order rename to ota_order_bak;  
alter table ota_order_2020 rename to ota_order;
#4. 插入差异数据
insert into ota_order select * from ota_order_bak a where not exists (select 1 from ota_order b where a.id = b.id);
#5. ota_order_bak改造成分区表，如果表比较大不建议直接改造，可以先创建好分区表，通过dataX把导入进去即可。

#6. 后续的归档方法
#创建中间普遍表
create table ota_order_mid like ota_order;
#交换原表无效数据分区到普通表
alter table ota_order exchange partition p201808 with table ota_order_mid; 
##交换普通表数据到归档表的相应分区
alter table ota_order_bak exchange partition p201808 with table ota_order_mid;

通过从InnoDB存储空间分布，delete对性能的影响可以看到，delete物理删除既不能释放磁盘空间，而且会产生大量的碎片，导致索引频繁分裂，影响SQL执行计划的稳定性；
同时在碎片回收时，会耗用大量的CPU，磁盘空间，影响表上正常的DML操作。
在业务代码层面，应该做逻辑标记删除，避免物理删除；为了实现数据归档需求，可以用采用MySQL分区表特性来实现，都是DDL操作，没有碎片产生。

8 binlog redolog undolog

MySQL的原子性是怎么保证的：redoLog
binlog: 记录了数据库表结构和表数据变更；主要有两个作用：复制(主从复制)和恢复数据；
redolog: 当我们修改的时候，写完内存了，但数据还没真正写到磁盘的时候。此时我们的数据库挂了，我们可以根据redo log来对数据进行恢复。因为redo log是顺序IO，所以写入的速度很快，并且redo log记载的是物理变化（xxxx页做了xxx修改），文件的体积很小，恢复速度很快
区别： 1,redo log 记录的是数据的物理变化，binlog 记录的是数据的逻辑变化
   2,redo log的作用是为持久化而生的, binlog的作用是复制和恢复而生的。redo log 存储的是物理数据的变更，如果我们内存的数据已经刷到了磁盘了，那redo log的数据就无效了。
   3,redo log是MySQL的InnoDB引擎所产生的。binlog无论MySQL用什么引擎，都会有的。
   4,redo log事务开始的时候，就开始记录每次的变更信息，而binlog是在事务提交的时候才记录。
redo log和binlog的数据一致性：两阶段提交
   阶段1：InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态
   阶段2：binlog 写盘，InooDB 事务进入 commit 状态
   每个事务binlog的末尾，会记录一个 XID event，标志着事务是否提交成功，也就是说，恢复过程中，binlog 最后一个 XID event 之后的内容都应该被 purge。
undolog作用：回滚和多版本控制(MVCC)   存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。
select count(*)是否造成全表扫描：
   在有多个索引的情况下，在查询数据前，MySQL 会选择成本最小原则来选择使用对应的索引

9，存储过程

CREATE PROCEDURE ceshi_logon()
BEGIN
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE v_id VARCHAR(50);
	DECLARE v_real_name VARCHAR(50);
	DECLARE cur1 CURSOR FOR SELECT id v_id, real_name v_real_name FROM base_user;
    -- 游标中的内容执行完后将done设置为true
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	OPEN cur1;
	REPEAT
		FETCH cur1 INTO v_id, v_real_name;
		IF not done THEN
			INSERT INTO base_user_login (Id, User_Id, User_Name, Expires_Date, Last_Logon_Date) 
			VALUES (uuid(), v_id, v_real_name, date_add(NOW(), interval 15 day), NOW());
		END IF;
		UNTIL done
	END REPEAT;
  CLOSE cur1;
END;

CREATE PROCEDURE useCursor()  
  BEGIN  
    DECLARE oneAddr varchar(4000) default '';  
		DECLARE tmpSqlStr varchar(4000);
    DECLARE done INT DEFAULT 0;  
    DECLARE curl CURSOR FOR select table_name from information_schema.tables where table_name like 'config_info_%';  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
    OPEN curl;  
    REPEAT  
      FETCH curl INTO oneAddr;  
      IF not done THEN  
				set tmpSqlStr = "";
				set tmpSqlStr = concat(tmpSqlStr ,"  select * from " , oneAddr);
				set tmpSqlStr = concat(tmpSqlStr ," ;" );
        set @sql= tmpSqlStr;
				-- 预处理语句 执行语句
        PREPARE stmt from @sql; 
        execute stmt;
      END IF;  
    UNTIL done END REPEAT;
    CLOSE curl;   	
  END;  

drop PROCEDURE sp_demo_in_parameter;
CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
BEGIN
	DECLARE p_id int unsigned default 4000000;
	SELECT p_in; -- 查询输入参数
	SET p_in=2; -- 修改
	select p_in;-- 查看修改后的值
	select p_id;
END;
set @p_id=1;
call sp_demo_in_parameter(@p_id);
select @p_id;

条件语句: if var = 0 then else end if;
        case var when 0 then else end case;
循环语句：while var <6 do set var=var+1; end while; -- 执行前进行检查
        set v=0; repeat set v=v+1; until v >5 end repeat;    -- 执行后检查
        set v=0; loop_lable:loop set v=v+1; if v>=5 then leave loop_lable; end if; end loop; -- 不需要初始条件,不需要结束条件,leave离开循环
ITERATE: 相当于循环中的continue
Cursor: 游标
    声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)
    打开定义的游标:open 游标名称;
    获得下一行数据:FETCH 游标名称 into field_1, field_2;
    需要执行的语句(增删改查):这里视具体情况而定
    释放游标:CLOSE 游标名称;