mysql
2023-04-11 23:19:58 1 举报mysql
作者其他创作
大纲/内容
gender
mysql主从复制、分库分表
server.xml
name
server
relaylog相当于是一个mq，避免主数据库大量并行操作，从数据库同步数据的时候造成阻塞
MyCat分片
CBO：cost成本优化RBO：rule规则优化
shardingJDBC：基于AOP原理，在程序中对本地执行的SQL拦截，解析、改写、路由处理，续编码配置，性能较高MyCat：数据库分库分表中间件，不用调整代码，性能弱与前者，MyCat1.6主流
写写：存在线程安全问题，可能会造成更新数据丢失
low_limit_id
DB_ROW_ID
null
表数据是存储在内存中，受硬件或断电问题，只能作为缓存表或临时表使用
特性
表文件
tableA
select
t3
锁
2
4
innodb
man
192.168.200.202:3306
MyCat-Web可视化管理
DB_TRX_ID
trx_list
START TRANSACTION;COMMIT;/ROLLBACK;
两阶段提交
mysql早期默认存储引擎
全局表配置：将字典表设置成全局表
垂直拆分
limit 优化
断电
1
SQL Thread 单线程处理中继日志
tableB
是一种数据结构，提高数据查询效率
1、主库出现问题，可以快速切换到从库；2、实现读写分离，主库写，从库读；3、从库执行备份，避免备份期间影响主库服务。
t1
平衡
事物1插入
0X123
MDL（mate data lock）系统自动控制，用于保护表的数据结构，有活动事务的时候，不能对元数据进行写入；对表增删改查加MDL共享读锁。更改表结构加MDL排他写锁
DB_ROLL_PTR
6
二叉树
show profiles;
redolog如何保证持久性
+1
数据库中数据从一个一致状态同步到另一个一致性状态
binlog
执行计划
水平分库：以字段为依据，按照一定策略，将一个库中数据拆分到多个库表结构一样，数据不一样
undolog
insert优化
表锁
连接器
sql提示
3
col...
count 优化
redolog--prepare
分治处理
用于组合索引；id为主键，name、age为组合索引select * from table where nama = \"zs\" and age = 12 ; 使用索引select * from table where age = 12; 不使用索引select * from table where nama = \"zs\" ; 使用索引select * from table where age = 12 and name = \"zs\"; 使用索引
分析：内存--》磁盘：随机读写 内存--》redolog 顺序写入1、内存向磁盘写数据过程中断电，则磁盘中不会有此次数据，但redolog中会有数据可以用来恢复数据；2、断电的时候磁盘写入、redolog写都没结束，则数据丢失；3、目前还没有说100%保证数据不丢失
1、select ... from table_name use index() ...; 使用某个索引2、select ... from table_name ignore index() ...; 忽略某个索引3、select ... from table_name force index() ...; 强制使用
begin;
lisi
up_limit_id
逻辑库
mysql存储引擎
TRX_ID
192.168.200.201:3306
查询条件是普通索引，先根据普通索引的B+树查找到id值，在根据id的B+树去查找对应记录，这个过程叫做回表；回表效率低，尽量减少回表；id为主键索引、name为普通索引；select * from table where name = \"zhangsan\";
zhangsan
恢复
分片规则
快照读
表示在生成readview的时刻，此时系统正在活跃的事务id
schema.xml
配置用户名、密码及权限<user name=\"root\"> <property name=\"password\">123456</property> <property name=\"schemas\">DB01</property> </user> <user name=\"test\"> <property name=\"password\">123456</property> <property name=\"schemas\">DB01</property> </user>
一种减少数据丢失的机制大数据HBASE中也有使用
优化器
K-V格式
读读：不存在任何问题
内存
mysql事务ACID
减少IO次数减少IO数据量
limit查询越往后查询越慢；一般通过覆盖索引加子查询的形式
count(*)：innodb对其优化，不取值，服务层直接按行累加count(1)：遍历全表，不取值，返回的每一行放一个1，直接按行累加count(主键)：遍历整张表，取主键id，服务层进行累加count(字段)：有not null约束，遍历全表取值，服务层按行累加，无则遍历全表取值，服务层判断是否为null，否则累加
行锁和间隙锁的组合，同事锁住数据和数据前面的间隙
t2
slave
使用load指令大批量插入数据；建立连接：mysql --local-infile -u -p设置全局参数：set global local_infile = 1;执行load指令：load date local infile '地址' into table 'table_name' fields terminated span style=\"font-size: inherit;\
页分裂：当插入一个数据时，主键是乱序插入，插入页没有足够空间，会开辟一个新的页，将原有页后一般数据移入新的页，新插入数据追加到其后，页在原有页、相邻页相互指向；页合并：删除数据时，数据并没有物理删除，会删除数据增加一个标记，当删除数据达到50% 时，会判断前后页是否有同样超过50% 的删除数据，有则进行页之间的合并
分析器
一个事务中的sql要么全部成功要么全部失败undolog记录是历史状态数据，可以把数据恢复过去
select @@TRANSACTION_ISOLATION；
memory
主键自增，id用完了怎么办？主键为int类型的时候，无符号自增的数据量为42亿多，一般情况下够用；在超出，可以修改主键为bigint类型，单占用空间会更大（8字节，多一倍）也可以使用复合主键bigint unsigned一般来说一张表超过500W数据就要考虑分表了
IO：减少IO次数、减少IO数据量
事务1在进行快照读的时候能读取到最新的数据
面试必问的数据库索引及优化技巧1、海量数据下，如何根据执行计划调优SQL？2、mysql索引体系如何应对海量数据存储？3、海量数据下，如何设计性能优良的mysql索引4、mysql的聚簇索引和非聚簇索引全解析5、mysql索引面试必问名词：回表、索引覆盖，最左匹配6、如何针对特定SQL场景，来进行索引的调优
顺序读写：append追加的形式随机读写：找到文件中的写入的位置在读写
垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中表数据量很大，将表按字段拆分，通过主键或外键关联
事务1
至多有两个分支
有序
MyCat
数据仓库
哈希表
client(jdbc、client、sqlyog、Navicat)
分块读取
锁定索引记录间隙（不含该记录），报正索引记录间隙不变，防止其他事物在这个间隙insert，产生幻读;查询条件为不存在的索引时，行锁会优化成间隙锁
隐藏字段
master
K：字段值V：文件名称、文件中的偏移量、读取数据长度
完成
MVCC使用场景
当前读
t4
准备服务器masterslave
从库配置slave
表级锁
慢查询分析
一致性
dataNode1
隔离性
relaylog作用
时刻
extent
语法：lock tables 表名... read/write;unlock tables/客户端断开连接;
dataNode3
age
xxx_123.sdi：存放表结构信息，123自增
磁盘
DML操作遵循ACID原则，支持事物；行级锁，提高并发访问的性能；支持外键Foreign Key约束，保证数据的完整性和正确性
col1
分库分表
粒度小，并发高，应用innodb引擎，通过对索引项加锁来实现；共享锁：与排它锁互斥，兼容共享锁select... lock in share mode排它锁：互斥共享锁、排它锁select ... fro update
主从复制原理
性能分析
IO操作
将主数据库的DDL和DML操作通过二进制日志（binlog）传送到从服务器，从服务器对这些日志重新执行（relaylog），保持主库和从库数据一致。链状复制：一主一从，从为另一从库的主库
内存存放支持hash索引（默认）
临建锁
开放指定端口：firewall-cmd --zone=public --add-port=3306/tcp -permanentfirewall-cmd -reload关闭服务器防火墙：systemctl stop firewalldsysremctl disable firewalld初始化相关mysql
SET AUTOCIMMIT=0;BEGIN;...COMMIT;
先redolog后binlog
mysqloracledb2
using temporary：使用临时表建立适当索引，避免使用临时表
索引失效
explain/desc select...
磁盘预读： 当内存和磁盘进行交互的时候，有一个基本的逻辑单位，称之为页或者datapage，默认大小为4k或者8k，和操作系统有关，在进行数据读取的时候一般读取的是页的整数倍。innodb默认每次读取的大小是16K语法：show varibales like ''innodb_page_size';
slowlog
文件存储形式xxx.idb
ROLL_POINTER
垂直分库：以表为依据，按业务将不同表拆分到不同库中指单台数据库性能达到瓶颈，按业务将表拆分到其他库中
哈希表是散列表，存储数据需要将数据尽可能的分散存储，因此数据是无序的，在进行范围查询的时候需要一个个匹配过去，范围查询效率低。需要设计优秀的hash算法，因为在进行存储的时候的会产生大量的哈希碰撞和哈希冲突。memory存储引擎使用的是哈希表innodb存储引擎中有个特性叫自适应哈希，指innodb存储引擎会根据查询条件来构建B+树或Hash索引
隐藏主键，如果表中没有主键的话，MySQL会默认为表中数据生成一个6字节的rowid
分表：例如日志数据
20
mysql事物
前缀索引
mysql中默认关闭慢查询记录，默认10s为慢sql；进入/etc/my.cnf配置slow_query_log=1，long_query_time=2重启mysql，进入/var/lib/mysql/localhost-slow.log
mysql锁
write ahead log预写日志
dataNode2
AST抽象语法树
不支持外键和事物；支持表锁，不支持行级锁；访问速度快；
索引覆盖
满足业务系统需求，结果快速返回
最左匹配失效：索引覆盖会导致最左匹配失效id、name、ageid为主键，name，age为组合索引select id ，name，age from table where age = 12; 会使用索引查询的时候，可以从组合索引的对应的B+树中获取到所需查询的字段，会走索引覆盖
数据库逻辑备份：flush tables with read lock；mysqldump不在mysql命令行中执行mysqldump -uroot -p1234 dss>dss.sql;unlock tables;mysqldump --single-transaction -uroot -p1234 dss>dss.sql;
可见性算法
update;commit;
元数据锁
主键优化
相关配置
意向锁
回表
对历史数据的进行分析，产生决策性的影响
OLTP
全局锁
K：查询条件的字段值V：整行记录
用于减少行锁和表锁的冲突；意向共享锁（IS）：兼容表锁的共享读锁，互斥表锁独占写锁；意向排它锁（IX）：互斥表锁的读锁、写锁，意向锁之间不互斥
不变
mysql server层
系统尚未分配的下一个事务id
在IO次数相同的情况下，B+树能存储更多的数据；一般情况下，3-4层的B+ 树足以支撑千万级数据量的存储；在选择索引列的时候，key值占用空间越少越好；在满足业务场景下，能选择主键自增尽量选择自增；
mysql索引
并行操作数据库
类比MongoDB
OLAP
联机事物处理
+1操作
间隙锁
是一款兼顾高性能和高可靠性的存储引擎，mysql5.5之后默认使用该引擎
采用单数据库存储数据不能应对指数级增长的数据IO瓶颈：大量磁盘IO，效率低；带宽不够，网络IO瓶颈；CPU瓶颈：大量请求，大量SQL排序、分组等查询消耗CPU资源。
rule.xml
在并发访问时，解决数据访问一致性、有效性的一种机制
sql执行流程
tablespace
一组操作的集合，集合中的sql要么全部成功，要么全部失败
扰动函数？？？在hashmap，避免数据聚集，让高位参与运算1010 01011011 0101地位运算时哈希相同会存入同一列，让高位进行运算可以将数据尽可能的分散
存储数据、建立索引、更新查询数据等技术的实现方法。存储引擎是基于表的，也称为表类型
errorlog
持久性
redolog
具体的规则定义eg：autopartitoin-long.txt
事物id有系统自动生成，默认自增
每张表都会有一个对应的xxx.idb表空间文件，文件中存储的是表结构（frm、sdi）、数据、索引；参数：innodb_file_pre_table=on--一个表一个文件
redolog--commit
16k
方式
relaylog
表当前活跃事务id列表中的最小值
主库配置master
group By优化
binlog、redolog一致性问题
B+树
顺序读写 快随机读写 慢
192.168.200.203:3306
错误日志
MVCC在进行实现的时候包含三部分组件：undolog、readview、隐藏字段
update 优化
语法
前滚日志
存在binlog、redolog，mysql插件式数据库，最开始存储引擎使用的是mysima，后来innodb使用更好，默认引擎设置为innodb，innodb中有redolog，因此需要保持两个日志一致性
mysql专题
1、对索引列做运算符操作；2、like 左侧模糊匹配；3、字符串不加单引号；4、关键字or使用，需前后字段都有索引才会走索引；5、数据分布影响，当查询扫描的数据行超过一定数量时会走全表扫描
事务3
BST
事物2name=lisi
数据库
事务2
redolog+1后增加一个prepare状态恢复数据时，master读取到redolog中+1为prepare状态后，会去binlog中查看是否有+1， 有则+1，redolog中该条记录状态置为commit， 否则丢弃redolog中该条记录；redolog为commit状态，则进行+1恢复。
配置逻辑库、逻辑表分片节点，节点主机数据源相关配置
特性ACID
...
读写：存在线程安全问题，可能会造成脏读、幻读、不可重复读
事务1在t4时刻进行快照读的时候不能读取到最新的数据
快照读指读取的是历史版本的记录，不是最新的select
与存储引擎交互
对整个数据库实例进行加锁，只能处理DQL操作，一般用于对数据库的逻辑备份，保证数据的一致性和正确性；
READ-UNCOMMIT、READ-COMMIT、READ-REPEATABLE、SERIALIZABLE
分片节点
管理连接权限校验
查看执行频次：show [gloable/session] status like 'Com_______';
实验2
5
隔离级别
行锁
经过验证：实验1能读取到事务2修改的数据，实验2事务1不能读取到事务2修改的数据；根据可见性算法，实验2应该要能读取到事务2修改的数据；可见性算法不会变，能变的只有readview;猜测实验2中事务1并没有生成新的readview，而是沿用了t1时刻的readview；也就是说生成readview的时机是不同的，为什么有这种机制？在实验2中，事务1不会出现不可重复读的问题，按照事务隔离级别定义，RR隔离级别解决了不可重复的问题，而RC隔离级别没有解决；通过验证发现解决这个问题的本质在于readview的生成时机，这部分数据底层的实现机制，需要给到用户一个可配置项来决定是否看到更新之后的数据，span style=\
xxx_123.sdi：存放表结构信息，123自增xxx.MYD：存放表数据xxx.MYI：存放表索引
mvcc--Multi-version-concurrency-control：多版本并发控制，用来实现对数据库的并发访问
把磁盘数据加载到内存中
在数据库中，除了显示定义的的字段信息之外，MySQL会为数据添加一些隐藏字段，用户不可见
执行器
联机分析处理
最近修改事物id，创建数据的事物id或者最后一次修改数据的事物id
sql优化
0
1、批量插入，每次插入数据量为500-1000；2、手动提交，start transaction; ... commit;3、主键顺序插入。
索引
mysql体系结构
order By优化
数据格式
是一个重操作，在主从库中，备份从库会导致主从延迟；加参数 -- single-transaction 完成不加锁的一致性数据备份（快照度）
12
set autocommit=0;begin;....commit;或者 start transaction；commit；rollback；
DB_ROLL_PRT
内存不能一次性读取磁盘数据
IO Thread
回滚日志
select..
事物3age=12
hive
7
最左匹配
主从复制
执行sql文件：source 路径
数据库引擎：innodb、myisam、memory
连接层、服务层、引擎层、存储层
page
mysql5.7之后，将SQL Thread改成了多线程处理；MTSrelaylog不会自动删除
using filesort：通过sort buffer排序，效率低using index：直接返回有序结果，效率高尽量使用覆盖索引，可是适当增加sort_buffer_size（默认256k）
加表锁的时候会，逐行检查该表是否有行锁；意向锁的存在可以避免加表锁时逐行检查行锁的性能开销
如何把整块磁盘的数据读取到内存中
中继日志，适用集群、分布式环境
undolog其实是一个历史数据组成的链表，链首是最新的就记录，链尾是最旧的旧记录；undolog中的版本链不会一直增加，在mysql服务中会有一个purge线程对版本链进行清理工作
1、一张mysql表可以有多个索引；2、一个索引对应一颗B+树；3、B+树的叶子节点存储实际的数据值，当表有多个索引的时候，实际的数据存储一份；其他索引的叶子节点存储的是primaryKey，即聚簇索引对应的字段值；4、在innodb存储引擎中，mysql在插入数据的时候，必须要和某一索引列绑定存储，如果有主键，默认选择主键，如果没有，则选择第一个唯一键，没有唯一键，则系统会自动产生一个6字节的rowid来进行存储。聚簇索引：和数据绑定存储的索引非聚簇索引：没有和数据绑定存储的索引id、name、ageid为主键，name为普通索引聚簇索引：id 非聚簇索引：name
水平拆分
实验1
原子性
查看SQL的耗时情况；查看是否支持：select @@have_profiling;查看是否开启：select @@profiling;设置开启：set [gloable|session] profiling = 1;查看query_id对应：show profile for query_id; show profile cpu for query_id;
schema
分片规则：范围分片：auto-sharding-long取模分片：mod-long一致性hash：根据字段哈希值确定，不会因增加节点，导致哈希位置不一致 sharding-by-murmurspan style=\"font-size: inherit;\
create table XXX(...)engine = innodb;show engines;
搭建
1、事务的四个特点，实现原理2、mysql的redolog、undolog、binlog分别有什么作用3、什么是二阶段提交，如何保证宕机是数据的一致性4、MVCC如何实现多版本控制并发，如何解决读写冲突5、mysql中的幻读是什么，如何解决幻读
1M 可以包含64个页
脏读：一个事务读取到了另一个事务未提交的数据；不可重复读：一个事务第一次读取后，另一个事务修改并提交了该数据，当前事务在去读取，前后数据不一致；幻读：一个事务插入数据，先查询没有该数据，另一事务插入同样数据，当前事务再去插入数据时失败。
segment
并发场景
共享读锁：当前客户端和其他客户端，对该表只能读，不能写；独占写锁：当前客户端对该表可读可写，其他客户端不能读不能写
类比Redis
水平分表：以字段为依据，按照一定策略，将一个表数据拆分到多个表中表数据量很大，将表水平拆分
节点主机
数据结构
红黑树
词法分析语法分析
回滚日志，当进行insert、update、delete操作时方便回滚的日志
myisam中会记录总行数，count(*)效率快innodb中需一行行的累计计数
AVL
myisam
主键索引、唯一索引、普通索引、全文索引（FullText）：mysql5.6之后加入，查询的是字段中的关键字
当查询结果只包含主键列和查询索引列，检索只需要在普通索引的B+树上就能找到主键值和查询列，则会触发索引覆盖，无需再去主键的B+树查询数据；id为主键索引、name为普通索引；select id，name from table where name = \"zhangsan\";索引覆盖效率高，尽量使用
并发事务
innodb逻辑存储结构
antlrApache calcite
读视图：表示事务在进行快照读的时候生成的视图，该视图保存的不是完整的数据，而是事物信息
制订分片规则
慢日志，通过配置记录执行慢的sql
readview
mycat大部分的配置，系统配置信息，用户、密码、权限、字符集等
查看idb文件方式：cmdidb2sdi table.idb
1、尽量降低主键的长度；2、顺序插入优于乱序插入，乱序易页分裂；3、尽量不使用uuid，避免对主键的修改。
存储引擎
0X456
可见性算法1、首先比较的是DB_TRX_ID < up_limit_id，如果小于，则当前事务能看到DB_TRX_ID所在的记录，若大于等于则进入下一判断；2、接着判断DB_TRX_ID ≥low_limit_id，若大于等于代表DB_TRX_ID所在的记录是在readview生成之后，肯定是不可见的；若小于进入下一判断；3、判断DB_TRX_ID是否在活跃事务id列表中，在，则说明在readview生成时刻，这个事务还是活跃的，还没有commit，修改的数据，当前事务不可见；如果不在，说明这个事务在生成readview之前就已经commit，修改的数据对当前事务可见。
真实数据索引数据
哈希表、二叉树、BST、AVL、红黑树
innodb的行锁是针对索引加的锁；避免行锁升级表锁，根据索引字段进行更新。
回滚指针，指向的是上一个历史版本的数据
后redolog先binlog
脏读，不可重复度，幻读
锁+mvcc
索引基础
当前读读取的就是最新版本的记录，还要保证其他事物不能修改当前记录update、delete、insert、select...for update（排它锁），select ... lock in share mode（共享锁）
逻辑表
0 条评论
下一页
