mysql
2022-01-12 17:33:53 30 举报AI智能生成
登录查看完整内容
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，现在属于Oracle公司。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。MySQL所使用的SQL语言是访问数据库的最常用标准化语言。MySQL软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库。
作者其他创作
大纲/内容
linux yum安装mysql
windows安装mysql
安装mysql
连接器
查询缓存
分析器（对执行的sql进行词法分析和和语法分析）
优化器（控制表的索引使用、表的连接顺序等）
执行器（先判断是否有表的操作权限，然后根据表的引擎定义调用存储引擎接口进行数据操作）
Server层
存储引擎
存储引擎层
分层
在存储引擎层，InnoDB引擎独有，使得数据库拥有crash-safe能力
记录的是物理日志，在某个数据页上做了什么修改
redo log是循环写的，空间固定
1. 将要执行的操作更新到内存完毕后记录redo log日志，此时处于prepare阶段
2. server层写入binlog 日志
3. 存储引擎，提交事物（往redo logo中写入一条commit记录），此时才处于commit状态
redo log日志两阶段提交
两阶段提交两份日志的一致性（事务成功后的操作一定会被写入到binlog），使得通过binlog恢复数据时不会丢失数据
redo log（InnoDB存储引擎独有）
在server层，所有引擎都可以使用，可通过binlog做数据恢复
statement 模式：记录执行的sql语句
row 模式：记录的是行上的变更（写入前和写入后都会被记录）
mixed 模式: 混合模式
记录的是逻辑日志，即DDL和DML语句，有三种记录模式：
binlog日志启用，查看，恢复数据
binlog
日志
原子性
一致性
用来支持读提交和可重复读隔离级别的实现（注意与create view ...语句创建的视图进行区分，后者是个虚拟表）
作用
1. InnoDB里面每个事务有一个唯一的事务ID（transaction id），该id是递增生成
2. 每行数据都是有多个版本（即MVCC），每个版本都有自己的版本号（trx_id）
3. 在更新行时会将更新操作所处事物的transaction id作为行的trx_id生成一个新的版本
4. 在创建视图时会新建一个数组存储当前所有未提交的事物的transaction id
5. 在一致性读的时会判断读取行的trx_id是否小于等于（当前事物自己更新时会等于）当前事物transaction id且不在数组中，不符合条件则往前面的历史版本中继续查找
实现原理
可重复读下：在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图
读提交下：每一个语句执行前都会重新算出一个新的视图
创建时机
一致性读视图（consistent read view）
读取一致性视图中的版本数据，如：可重复读和当前读中普通的查询语句
一致性读
读取当前已提交的最新数据，如：更新语句和select ... lock in share mode 或select ... for update。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待（根据两阶段锁协议，行锁只有在事物提交后才释放）
当前读
名词解释
读未提交
读提交
可重复读（默认隔离级别）
串行化
隔离级别
事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
脏读
事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致
不可重复读
幻读
事务的并发问题
每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制
事物隔离的实现
隔离性
持久性
四大特性（ACID）
1. 自动提交方式（set autocommit=1）// 在未显示启动事物时自动启动及提交事物。显式启动事务语句为begin 或 start transaction。配套的提交语句是commit
2. 不自动提交方式（set autocommit=0）//会将线程的自动提交关掉，意味着如果你只执行一个select语句，这个事务就启动了，而且并不会自动提交
事物的启动方式
事物
全局锁的典型使用场景是，做全库逻辑备份
使用场景
加锁语句：Flush tables with read lock ;
解语句： UNLOCK TABLES ;
方式1：FTWRL（不推荐），会使得整库进入只读状态
示例：mysqldump –single-transaction
方式2：–single-transaction参数方式（推荐，需要引擎支持一致性读，如innodb），会先启动一个事物，由于MVCC的支持，这个过程中数据是可以正常更新的
实现方式
全局锁（锁库）
加锁语句： lock tables … read/write
解锁语句： unlock tables … read/write
相关语法
与FTWRL类似，也可以在客户端断开的时候自动释放
如加读锁，其他线程只能读该表，本线程也只能读该表
如加写锁，其他线程无法读写该表，本线程能读写该表
特点
普通表锁
不需要显示调用，当对一个表做增删改查（DML)操作的时候，加MDL读锁；当要对表做结构变更（DDL）操作的时候，加MDL写锁
MDL读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查
MDL读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性
通过ALTER TABLE tbl_name WAIT N add column 能解决在某些场景下MDL写锁长时间阻塞后续MDL读锁的问题
元数据锁（metadata lock 简称MDL，5.5版本中引入）
表锁
行锁是在引擎层由各个引擎自己实现的，myisam不支持行锁，只支持表锁，导致表中任意一行更新都会锁住整个表
show VARIABLES like '%innodb_deadlock_detect%'; 查看超时时间
控制参数
等待时间设太长可能会导致服务请求阻塞时间过长，太短容易对正常锁等待造成误伤
存在问题
1. 直接进入等待，直到超时回滚事物
show VARIABLES like '%innodb_lock_wait_timeout%'; 为on表示启动
1. 在客户端控制对同一行更新的并发数
2. 在业务层面将这一行拆成多行
解决方案
由于每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级，会导致大量消耗cpu资源
2. 发现死锁后，主动回滚死锁链条中的某一个事务
处理策略
死锁
行锁
锁
基于B+Tree的数据结构，每一个索引就是一颗B+Tree
主键索引叶子节点的data部分存储的是表中除主键的其他列（也叫聚簇索引）
非主键索引叶子节点data部分存储的是主键id，所以主键长度越小，非主键索引占用空间越少
实现
页分裂：在随机插入新值的时候，插入的页刚好满了，需要申请一个新页然后挪动部分数据到新页的过程
页合并：当相邻两个页由于删除了数据，利用率很低之后，将数据页做合并的过程
索引维护
1. myisam 主键索引叶子节点的data部分存放的是数据记录的地址
2. myisam的辅助索引和主键索引结构相同，只是辅助索引的键是可以重复的
myisam和innodb索引的区别
普通索引在查找到符合条件的值会继续查找直到不符合条件的行，会比唯一索引多扫描一行记录，但由于数据库按页读取，这种性能差异微乎其微
查询比较
change buffer机制
普通索引能利用change buffer机制提升更新性能，而唯一索引不能
更新比较
普通索引和唯一索引选择（如业务代码层能保证唯一性，优先使用普通索引）
覆盖索引
最左前缀
索引下推
遍历索引
关联字段类型不一致，如字符类型的索引字段，传入的参数为整型，导致索引字段需要通过类型转换函数转换而无法使用索引
对整型索引列做运算后查询，原理同上
关联字段编码不一致，原理同上
查询条件中使用索引会导致不走索引
不使用索引的情况
具体查看18章的内容
查看sql语句执行时索引的使用情况及扫描行数等信息
explain
当统计信息不准时，手动重新统计，语法：analyze table T
analyze table
当索引使用不符合预期时，强制使用某个索引，语法：select * from T force index(a)
force index
相关语句
索引优化/选择
开启慢sql
set global slow_query_log = 'ON';
慢sql文件存储位置，window默认在“C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Data”
set global slow_query_log_file='xxx'
慢操作的时间设置(单位是秒)
set global long_query_time = 0.5;
是否开启没有使用索引的操作
set global log_queries_not_using_indexes = 'ON';
其中Rows_sent表示返回行数Rows_examined表示扫描行数
# Time: 2021-09-21T03:05:10.616223Z# User@Host: root[root] @ localhost [::1] Id: 10# Query_time: 0.005758 Lock_time: 0.000062 Rows_sent: 3 Rows_examined: 20003SET timestamp=1632193510;select word from words order by rand() limit 3;
慢sql日志结果
慢sql日志
注意这三句要一起执行
SET optimizer_trace=\"enabled=on\";SELECT * from student; --查询语句SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
查询方式
被追踪的SQL语句文本
QUERY
追踪信息，以JSON形式表现
TRACE
当TRACE信息超过了optimizer_trace_max_mem_size 的值时丢失的字节数
MISSING_BYTES_BEYOND_MAX_MEM_SIZE
如果被追踪的SQL使用了没有其权限的view或routines，则TRCE栏位为空且此栏位会显示为1
INSUFFICIENT_PRIVILEGES
返回字段
http://blog.itpub.net/28218939/viewspace-2658978/
TRACE结果分析
sql 优化器日志
这种情况被驱动表数据量的上升会比驱动表数据量上增影响小，所以尽量使用小表作为驱动表
1. 被驱动表有索引的情况下使用“Index Nested-Loop Join”的方式
即将驱动表放入内存（join_buffer_size设置该内存大小），然后用扫描驱动被驱动表逐行去和内存中的数据比较在驱动表比较大的情况下会将驱动表分次放入内存，没放入一次被驱动表扫描比较一次，然后再清空内存这样在驱动表比较大的情况下会增大被驱动表的扫描次数，所以尽量使用小表作为驱动表
2. 被驱动表没有索引的情况会使用”Block Nested-Loop Join“方式（尽量不要用这种方式）
join
索引（innodb）
两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”
用户变量与数据库连接有关，在这个连接中声明的变量，在连接断开的时候，就会消失。在此连接中声明的变量无法在另一连接中使用
set @xxx = xxxVal
设置方式
select @xxx
用户变量@
会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。）
查看方式：show session variables
设置方式：set session xxx=xxxVal（和set @@session.xxx=xxxVal等价）
会话变量
全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改
查看方式：show global variables
设置方式：set global xxx=xxxVal（和set @@global.xxx=xxxVal等价）
全局变量
系统变量@@
比如navicat中新建一个查询切卡会新建一个会话连接，这个切卡中设置的会话变量在另外一个切卡中是无效的
变量
mysql
0 条评论
回复 删除
下一页
