MySQL
2021-04-14 09:22:47 10 举报AI智能生成
登录查看完整内容
MySQL全记录，包括索引、事务、并发访问控制、主从复制等
作者其他创作
大纲/内容
事务
ACID
原子性Atomicity
要么都成功，要么都失败，一个事务内的所有sql语句要保持同步的进行
实现原理：依靠undo.log日志，记录事务执行的sql，在事务失败时进行反向补偿，回滚数据
一致性Consistency
事务前后总量不变，数据库的完整性约束没有被破坏
实现原理
从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性
从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据
隔离性Isolation
一个事务的执行不被其他事务干扰
写-写操作
通过锁来实现
写-读操作
MVCC
持久性Durability
事务一旦提交，结果便是永久性的
主要依靠redo.log日志实现，在执行修改操作时，sql会先写入到redo.log日志，再写入缓存，最后更新数据库，这样即使断电，也能保证数据库不丢失数据
Innodb_flush_log_at_trx_commit
扩展
如果在commit前将内存中的数据写入到磁盘会有什么问题？
采用redo log的好处？
并发事务带来的问题
外框
脏写/丢失更新
A事务提交或者撤销时，把B事务更新的数据给覆盖了
解决方案
悲观锁
select xx for update;
严重影响并发，不推荐
乐观锁
获取数据时不加锁，update时再去检查冲突
可使用版本号或者时间戳来判断是否修改过
读一致性
脏读：读到其他事务未提交的数据
不可重复读：读到其他事务已经提交的数据，导致两次查询的结果不同
幻读：读到其他事务已提交的数据行
不可重复读和幻读的区别？
前者读的是其他事务修改或删除的数据而幻读读到的是其它事务新插入的数据
隔离级别
读未提交 READ UNCOMMITTED：解决了脏写，会出现脏读
读已提交 READ COMMITTED：解决了脏读，但会出现不可重复读
可重复读 REPEATABLE READ：解决了不可重复读，但会出现幻读（InnoDB不会，默认隔离级别）
串行化 SERIALIZABLE：使用表锁，降低并发性能
多版本并发控制，通过维护数据历史版本（版本链），从而解决并发访问情况下的读一致性问题
通过 ReadView + UndoLog 实现，UndoLog 保存了历史快照，ReadView 规则帮助判断当前版本的数据是否可见
核心
事务版本号
每次事务开启前都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序
表的隐藏列
trx_id：数据事务ID（记录操作该数据事务的事务ID）
roll_pointer：回滚指针（指向上一个版本数据在 undo log 里位置的指针）
row_id：隐藏ID（当创建表没有合适的索引作为聚集索引时，会用该隐藏ID创建聚集索引）
delete mark：删除标识
ReadView
重要属性
trx_ids: 当前系统正在活跃(未提交)事务版本号集合
min_trx_id：创建当前read view 时“系统正处于活跃事务最小版本号”
max_trx_id：创建当前read view 时“当前系统最大事务版本号+1”
creator_trx_id：创建当前read view的事务版本号
匹配条件
数据事务ID < read view 中的最小活跃事务ID，该数据在readview中可见
数据事务ID >= read view 中的最大事务ID，数据不可见
数据事务ID介于两者之间
如果事务ID不存在于trx_ids 集合
可见
事务ID存在trx_ids 且等于creator_trx_id
事务ID存在trx_ids 不等于creator_trx_id
不可见
不满足read view条件（数据不可见时），从undo log中获取数据
undo log
记录数据被修改之前的日志，表信息修改之前先会把数据拷贝到 undo log 里
用途
保证事务进行rollback时的原子性和一致性
用于MVCC快照读
insert undo：事务提交即释放
update undo：需要支持MVCC，不能立即删除
主要解决
如果隔离级别是 读已提交：每次读取数据前都会生成一个ReadView，保证每次都能读到其它事务已提交的数据
如果隔离级别是 可重复读：只在第一次读取数据时生成一个ReadView，这样就能保证后续读取的结果完全一致
InnoDB默认可重复读的隔离级别是否存在幻读问题？
不存在，基于MVCC的快照读可避免幻读问题
当前读的情况下MySQL默认加锁来解决（间隙锁）
读未提交
读取版本链中最新版本的记录即可
串行化
事务之间是加锁执行的，不存在读不一致的问题
锁
事务并发访问
读-读
由于两个事务都进行只读操作，不会对记录造成任何影响，因此并发读完全允许
写-写
可能会产生脏写，并发事务同时修改一行数据，只能加锁，事务执行完后释放锁
读-写
可能会产生脏读、不可重复读、幻读
读操作利用多版本并发控制（MVCC）
写操作进行加锁
锁的粒度
行锁
作用在数据行上，锁的粒度小，并发度高
开销大，加锁慢；会出现死锁
默认搜索引擎：InnoDB
InnoDB的行锁，是通过锁住索引来实现的
范围
记录锁（Record Lock）：锁定一个行记录
间隙锁（Gap Lock）：锁定一个区间（不包括边界）
Next-key Lock（默认）
临键锁：记录锁和间隙锁的结合（包括边界）
除了锁住记录本身，还要再锁住索引之间的间隙
表锁
作用在整张数据表上，锁的粒度大，并发度低
开销小，加锁快；不会出现死锁
默认搜索引擎：MyISAM
锁的分类
互斥
共享锁
在事务要读取一条记录时，需要先获取该记录的 S 锁，S 锁可以在同一时刻被多个事务同时持有
排他锁
在事务要改动一条记录时，需要先获取该记录的 X 锁。X 锁在同一时刻最多只能被一个事务持有
意向锁
意向锁是由数据库自己维护的加锁标识，可以快速判断表中是否有记录被上锁，避免遍历，提高加锁效率
mysql默认根据实际场景自动选择加锁方式，当然也可以通过 innodb_autoinc_lock_mode 强制指定只使用其中一种
上锁与排查
上锁
隐式上锁（默认）
select（快照读，不加锁）
insert、update、delete（当前读，排他锁）
显式上锁
select * from table_name lock in share mode;（当前读，共享锁）
select * from table_name for update;（当前读，排他锁）
解锁/释放锁
提交事务（commit）
回滚事务（rollback）
kill 阻塞进程
排查
show status like 'innodb_row_lock%';
innodb_row_lock_current_waits：当前等待锁的数量
innodb_row_lock_time：锁定总时长
innodb_row_lock_time_avg：平均等待时长
innodb_row_lock_time_max：最长一次等待时间
innodb_row_lock_waits：系统启动到现在总共等待的次数
select（共享锁）
insert、update、delete（排他锁）
显式上锁（手动）
lock table table_name read;（共享锁）
lock table table_name write;（排他锁）
解锁（手动）
unlock table table_name;（单表）
unlock tables;（所有表）
show open tables;（查看）
show status like 'table%';（分析）
table_locks_waited：因表级锁争用而等待的次数
table_locks_immediate：产生表级锁定的次数
数据库死锁
概念
指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象
产生条件
请求与保持
不可剥夺
循环等待
解除死锁
查看：show engine innodb status \\G;
查看是否锁表，查看进程，杀死进程
查看当前锁定和等待的事务，杀死进程
如何避免
加锁顺序一致，尽可能一次锁定所需数据行
保持简短的事务，单次操作数量不宜过多
使用较低的隔离级别
合理使用索引，减少不必要的索引
主从复制
原理
从库连接到主库后，从库有个IO线程，将主库的binlog日志拷贝到自己本地，写入relay日志中
接着从库中有一个SQL线程会从relay日志中读取，然后执行其中的内容，使主从数据保持一致
主备延迟怎么解决？
分库，把一个主库拆分成多个主库，降低主库的写并发，使得延迟时间可以忽略不计
不查询，插入数据成功后直接修改数据，不要查询（因为修改数据在主库，查询在从库）
还可以直连主库，不推荐，失去读写分离的意义
配置
修改配置
一主一丛
主机：vim/etc/my.cnf如有必要可关闭防火墙
主服务器唯一ID：server-id=1（双主切忌冲突）
启用二进制日志：log-bin=mysql-bin
设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
设置需要复制的数据库：binlog-do-db=testdb（自定义）
设置binlog格式：binlog_format=STATEMENT
从机：vim/etc/my.cnf
从服务器唯一ID：server-id=2
启用中继日志：relay-log=mysql-relay
双主双从
两台主机需额外配置（一主一从请忽略）
在作为从库时，有写入操作也要更新二进制日志：log-slave-updates
设置自增长字段每次递增的步长：auto-increment-increment=2
设置自增长字段的起始值：auto-increment-offset=1 和 2
双主相互复制，分别执行
start slave;
show slave status\\G;
重启服务使配置生效：systemctl restart mysqld
设置权限
主机
mysql -uroot -proot
mysql> ：GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
mysql> ：show master status;
查看File(日志名)和Position(切入点)，用来配置从机
从机
Slave_IO_Running：Yes
Slave_SQL_Running：Yes
如之前配置过主从，需重置
mysql> ：stop slave;
停止从服务器的复制功能
mysql> ：reset master;
重新配置主从
日志
undo log：回滚日志
作用：用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读
事务开始之前产生，事务提交之后不是立马删除，而是放入待清理链表，由清除线程判断
redo log：重做日志
作用：用于奔溃恢复，记录事务对数据库做了哪些修改，确保事务的持久性
事务开始之后产生，当对应事务的脏页写入到磁盘之后释放
binlog：二进制日志
作用：记录对数据库执行更改的所有操作
事务提交的时候一次性将事务中的sql记录到binlog中，在生成时间超过expire_logs_days配置的天数后自动删除
errorlog：错误日志
记录出错信息，也记录一些警告信息和正确信息
slow query log：慢查询日志
设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中
general log：一般查询日志
记录所有对数据请求的信息，不论这些请求是否被正确执行
relay log：中继日志
中继日志也是二进制日志，用来给slave库恢复
问题
大表数据查询，怎么优化？
优化SQL语句，加索引
加缓存，memcached，redis
主从复制，读写分离
分库分表（垂直水平拆分）
超大分页怎么处理？
使用覆盖索引优化
使用ES优化
MySQL
语法
三范式
第一范式：列不可分（确保每一列的原子性）
第二范式：要有主键（非主字段必须依赖于主字段）
第三范式：消除传递依赖（避免冗余数据，比如 总价 = 单价 * 数量）
反三范式
基于第三范式所调整，适当保留冗余数据
为什么会有反三范式的设计？
提高查询效率
减少计算和查询次数
保留历史信息
比如记录订单表的收货地址信息历史订单不会因为用户修改而变更
join 连接
左连接：以左表为驱动表，左边是全数据，右边可能为空
右连接：以右表为驱动表，右边是全数据，左边可能为空
内连接：自动选择驱动表，相互匹配的才会查出来
order by 排序
根据指定字段进行排序（默认升序）
支持单条件排序
支持多条件排序
在联合查询中，order by需要借助括号和 limit 使用limit 使用任意一个大于数据的记录即可
group by 分组
定义
将若干行数据，分成了若干组，每组为一行数据
一行数组代表一组，是集合概念
使用举例
一般结合聚合函数使用
统计函数聚合函数
count(字段)，统计行数
max(字段)，求出一组中该字段的最大值
min(字段)，求出一组中该字段的最小值
avg(字段)，求出一组中该字段的平均值
sum(字段)，求出一组中该字段的累加和
with rollup 回溯统计
任何一个分组后都会有一个小组，根据当前分组字段进行汇报统计
回溯统计时将分组字段置空
having 筛选条件
只对 group by 分组结果进行条件筛选（分组之后对结果集的筛选 where 做不到）
having 能使用字段别名，where不能
where是从磁盘中读取，而别名是在内存中产生的
因为where要生成结果集，而order by是对结果集的操作
where是在生成结果集前的操作，别名是生成结果集才产生
如何避免SQL注入？
采用预处理对象，使用 Preparedstatement 对象，而不是 Statement 对象
存储引擎
使用Hash索引
使用B+树索引（ Innodb支持自适应hash，用户不可以干扰）
数据在磁盘上的不同组织形式
InnoDB
文件格式
聚集索引
frm：表结构文件
ibd：数据文件+索引文件
支持事务、支持外键、支持行锁和表锁、并发好
应用场景：需要事务和更新操作频繁的场景
MyISAM
MYD：数据文件
MYI：索引文件
非聚集索引
不支持事务、不支持外键、只支持表锁、查询快
应用场景：适用于少量插入，大量查询的场景
Memory
数据存储在内存，表结构存储在磁盘，访问效率高
服务关闭，表中数据丢失
应用场景：MySQL内存表做数据缓存
索引
概述
建立索引就是维护一棵 B+ 树，联合索引就是由多个字段组成，且满足最左匹配
索引列可以是主键，也可以是唯一键，还可以是6字节的rowid
优缺点
优点
提高数据的检索效率，减少IO次数
加快分组和排序，降低CPU消耗
将随机IO变成顺序IO
缺点
创建、维护索引要耗费时间，所以，索引数量不能过多
索引是一种数据结构，会占据磁盘空间
对表进行更新操作时，索引也要动态维护
数据结构角度
B+Tree
一个节点上可存多个元素（减少IO次数，提高查询效率）
B+树有单向指针，mysql优化为双向（利于范围查找）
数据全在叶子节点，非叶子节点只存索引的键（查询性能稳定）
Hash表
对索引的 key 进行一次 hash 计算就可以定位出数据存储的位置
优点：查询效率高
不支持范围查找和排序
hash算法不合适会影响查询效率
需要大量的内存空间
二叉树、AVL树、红黑树为什么不行？
分支少且高度不可控
为什么没有使用B-Tree？
Innodb_page_size=16384
一页一页的读取
每次读取16kb的数据
B树非叶子节点也存储数据，IO次数比B+树多
物理存储角度
聚集索引：索引和数据存储在一块
主键索引的叶子结点存储的是键值对应的数据本身
辅助索引的叶子结点存储的是键值对应的主键键值
非聚集索引：索引和数据分开存储
主索引和辅助索引类似，只是主索引不允许重复，不允许空值
他们的叶子结点的key都存储指向键值对应的数据的物理地址
逻辑角度
主键索引
特殊的唯一索引，也叫主索引，根据主键建立的索引，不允许重复，不允许空值
唯一索引
索引列的值必须唯一，允许有空值
普通索引
没有唯一性限制的索引
全文索引
ALTER TABLE table_name ADD FULLTEXT (column);
组合索引
最左优先，以最左边的为起点任何连续的索引都能匹配上
遇到范围查询(>、<、between、like%)后面的字段就会停止匹配
原理：先按照第一个字段排序，第一个字段相同就按照第二个排序
重要概念
回表
从某一个索引的叶子节点中获取聚簇索引的id值，根据id再去聚簇索引中获取全量记录
主键索引或者聚簇索引叶子节点就存储着数据，不需要回表
索引覆盖
从索引的叶子节点中能获取到全量查询列的过程
比如把查询的列作为联合索引的列值（优化点）
最左匹配
根据索引的顺序判断查询是否走索引
特殊情况
mysql内部有优化器，选择合适的顺序来执行（索引列不断开且有头）
当表中的全部字段都是索引列的时候，无论怎么查询都会用到索引
索引下推
在没有索引下推之前，先根据name去存储引擎拿到全量数据到server层，在根据age做数据过滤
mysql5.7后有了索引下推，则根据name，age两个索引去存储引擎筛选数据，将最终结果返回给客户端
索引下推指的是本应该在server层做的过滤操作，下推到存储引擎执行，提升数据到检索效率
client ➡️ server ➡️ 存储引擎
server连接器：管理连接，验证权限分析器：词法分析，语法分析，ast优化器：优化执行过程（cbo：基于成本的优化 rbo：基于规则的优化）执行器：跟执行引擎交互，执行具体的sql语句
避免索引失效
1、全值匹配、最佳左前缀法则2、范围查询以后的索引字段会失效3、尽量使用覆盖索引，减少select *的使用4、mysql在使用不等于(!=或<>时索引失效)5、is null，is not null 也无法使用索引6、like不要以通配符开头('%abc...')7、少用 or，字符串记得加单引号8、不在索引列上做任何操作(计算、函数、类型转换)
哪些情况需要创建索引？
主键、外键
经常需要搜索的列
查询中作为排序、分组的字段
哪些情况不需要创建索引？
表数据太少
经常增删的列
有大量重复值的列
一个表最多16个索引，一般一张表不建议超过6个索引字段，最大索引长度256字节
B+树有两种查找方式
第一种是从上往下通过索引查找
第二种是从根节点开始随机查找
无法使用索引时会进行全表扫描（第二种）
注意
最左前缀匹配原则
尽量选择区分度高的列作为索引
索引列不能参与计算
尽量的扩展索引，不要新建索引
explain
id：查询的序列号
select_type：查询的类型，主要是区别普通查询、联合查询、子查询之类的复杂查询
type：访问类型，保证查询至少达到range级别，最好能达到 ref
possible_keys：可能用到的索引
key：实际用到的索引
key_len：索引中使用的字节数（越短越好）
ref：显示索引的哪一列被使用了（最好是个常数）
rows：找到所需记录需要读取的行数（越少越好）
Extra
Using filesort：使用了文件排序，不好
Using temporary：使用了临时表，非常耗性能
Using index：使用了【覆盖索引】，效果不错
Using where｜impossible where：前者用到了where条件，后者没有
数据库优化
优化器
基于成本的优化
成本
IO成本
CPU成本
单表查询优化
基于索引统计数据的成本计算
多表连接的成本
基于规则的优化
条件简化
外连接消除
子查询优化
IN 子查询优化
ANY/ALL 子查询优化
转为max()、min()查询
[NOT]EXISTS 子查询优化
创建时优化
设计要合理，比如使用最合适的数据类型和长度保存数据
合理的创建索引
多建联合索引
查询时优化
优化经验
SQL优化
阿里规约
order by
尽可能在索引列上完成排序操作，根据最佳左前缀法则，否则会产生filesort
内部原理
优化策略
order by 时不要使用 select *，容易把sort_buffer占满
增大 sort_buffer_size 参数的设置
增大 max_length_for_sort_data 参数的设置
group by
group by 的优化和order by大体相同
group by 实质是先排序后进行分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大 max_length_for_sort_data 参数的设置+增大 sort_ buffer_ size 参数的设置
where 高于 having，能写在 where 限定的条件就不要写 having 了
id用完
bigint
如果有可能用尽，则一开始应该创建成8个字节的bigint
表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误
row_id
没设置主键的时候，InnoDB会自动创建一个长度为6个字节的row_id
row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据
thread_id
系统保存了一个全局变量 thread_id_counter，每新建一个连接，就将 thread_id_counter 赋值给这个新连接的线程变量
thread_id_counter 定义的大小是 4 个字节，因此达到 2^32-1 后，它就会重置为 0，然后继续增加
分库分表
在架构设计时做分库，根据不通的业务模块进行划分，业务之间不会影响性能
单表超过500万时读写性能明显下降，此时需要考虑分表
主键ID不是使用的自增ID，而是使用单独数据表分批生成
路由策略
根据主键ID对机器数量取模
采用分组+切片来解决高并发数据量过大的问题
触发条件
分库原则
分表原则
分表规范（阿里规约）
慢查询
查看
mysql> show variables like 'slow_query_log%';
查看慢查询日志的开启状态和慢查询日志保存位置
mysql> show variables like 'long_query_time';
查看慢SQL阈值，默认值是10s，可根据需求调整
日志分析工具
mysqldumpslow
[root@lishaojie]# mysqldumpslow /var/lib/mysql/mysql-slow.log
使用
mysqldumpslow --help
显示返回记录集最多的10个SQL
显示访问次数最多的10个SQL
显示按照时间排序的前10条里面含有左连接的查询语句
建议在使用这些命令时结合 | more 使用，否则有可能出现爆屏情况
mysqlsla（需安装）
0 条评论
回复 删除
下一页
